X-Git-Url: http://wagnertech.de/git?a=blobdiff_plain;f=mysql.sql;h=bfa26a0daa69ecde868789449938a988593275bf;hb=ee95ed0e72258b76ed0280f04d6846d802aefa12;hp=ba798632d8d81b6377e2f5dc6c0f4cdf51845986;hpb=223f24a8202940aa5a72661a83be26aa6bc8a560;p=timetracker.git diff --git a/mysql.sql b/mysql.sql index ba798632..bfa26a0d 100644 --- a/mysql.sql +++ b/mysql.sql @@ -73,7 +73,7 @@ create unique index role_idx on tt_roles(group_id, rank, status); # Insert site-wide roles - site administrator and top manager. INSERT INTO `tt_roles` (`group_id`, `name`, `rank`, `rights`) VALUES (0, 'Site administrator', 1024, 'administer_site'); -INSERT INTO `tt_roles` (`group_id`, `name`, `rank`, `rights`) VALUES (0, 'Top manager', 512, 'track_own_time,track_own_expenses,view_own_reports,view_own_charts,view_own_invoices,view_own_projects,view_own_tasks,manage_own_settings,view_users,track_time,track_expenses,view_reports,view_charts,view_own_clients,override_punch_mode,override_own_punch_mode,override_date_lock,override_own_date_lock,swap_roles,approve_timesheets,manage_own_account,manage_users,manage_projects,manage_tasks,manage_custom_fields,manage_clients,manage_invoices,override_allow_ip,manage_basic_settings,view_all_reports,manage_features,manage_advanced_settings,manage_roles,export_data,manage_subgroups'); +INSERT INTO `tt_roles` (`group_id`, `name`, `rank`, `rights`) VALUES (0, 'Top manager', 512, 'track_own_time,track_own_expenses,view_own_reports,view_own_charts,view_own_invoices,view_own_projects,view_own_tasks,manage_own_settings,view_users,track_time,track_expenses,view_reports,view_charts,view_own_clients,override_punch_mode,override_own_punch_mode,override_date_lock,override_own_date_lock,swap_roles,approve_timesheets,manage_own_account,manage_users,manage_projects,manage_tasks,manage_custom_fields,manage_clients,manage_invoices,override_allow_ip,manage_basic_settings,view_all_reports,manage_features,manage_advanced_settings,manage_roles,export_data,manage_subgroups,delete_group'); # @@ -85,6 +85,7 @@ CREATE TABLE `tt_users` ( `password` varchar(50) default NULL, # password hash `name` varchar(100) default NULL, # user name `group_id` int(11) NOT NULL, # group id + `org_id` int(11) default NULL, # organization id `role_id` int(11) default NULL, # role id `client_id` int(11) default NULL, # client id for "client" user role `rate` float(6,2) NOT NULL default '0.00', # default hourly rate @@ -104,7 +105,7 @@ CREATE TABLE `tt_users` ( # Create an index that guarantees unique active and inactive logins. create unique index login_idx on tt_users(login, status); -# Create admin account with password 'secret'. Admin is a superuser, who can create groupd. +# Create admin account with password 'secret'. Admin is a superuser who can create groups. DELETE from `tt_users` WHERE login = 'admin'; INSERT INTO `tt_users` (`login`, `password`, `name`, `group_id`, `role_id`) VALUES ('admin', md5('secret'), 'Admin', '0', (select id from tt_roles where rank = 1024)); @@ -178,6 +179,7 @@ create index task_idx on tt_project_task_binds(task_id); CREATE TABLE `tt_log` ( `id` bigint NOT NULL auto_increment, # time record id `user_id` int(11) NOT NULL, # user id + `group_id` int(11) default NULL, # group id `date` date NOT NULL, # date the record is for `start` time default NULL, # record start time (for example, 09:00) `duration` time default NULL, # record duration (for example, 1 hour) @@ -201,6 +203,7 @@ CREATE TABLE `tt_log` ( # Create indexes on tt_log for performance. create index date_idx on tt_log(date); create index user_idx on tt_log(user_id); +create index group_idx on tt_log(group_id); create index client_idx on tt_log(client_id); create index invoice_idx on tt_log(invoice_id); create index project_idx on tt_log(project_id); @@ -265,8 +268,11 @@ CREATE TABLE `tt_fav_reports` ( `show_end` tinyint(4) NOT NULL default 0, # whether to show end field `show_note` tinyint(4) NOT NULL default 0, # whether to show note column `show_custom_field_1` tinyint(4) NOT NULL default 0, # whether to show custom field 1 + `show_work_units` tinyint(4) NOT NULL default 0, # whether to show work units `show_totals_only` tinyint(4) NOT NULL default 0, # whether to show totals only - `group_by` varchar(20) default NULL, # group by field + `group_by1` varchar(20) default NULL, # group by field 1 + `group_by2` varchar(20) default NULL, # group by field 2 + `group_by3` varchar(20) default NULL, # group by field 3 `status` tinyint(4) default 1, # favorite report status PRIMARY KEY (`id`) ); @@ -377,6 +383,8 @@ CREATE TABLE `tt_custom_field_log` ( PRIMARY KEY (`id`) ); +create index log_idx on tt_custom_field_log(log_id); + # # Structure for table tt_expense_items. @@ -386,6 +394,7 @@ CREATE TABLE `tt_expense_items` ( `id` bigint NOT NULL auto_increment, # expense item id `date` date NOT NULL, # date the record is for `user_id` int(11) NOT NULL, # user id the expense item is reported by + `group_id` int(11) default NULL, # group id `client_id` int(11) default NULL, # client id `project_id` int(11) default NULL, # project id `name` text NOT NULL, # expense item name (what is an expense for) @@ -405,6 +414,7 @@ CREATE TABLE `tt_expense_items` ( # Create indexes on tt_expense_items for performance. create index date_idx on tt_expense_items(date); create index user_idx on tt_expense_items(user_id); +create index group_idx on tt_expense_items(group_id); create index client_idx on tt_expense_items(client_id); create index project_idx on tt_expense_items(project_id); create index invoice_idx on tt_expense_items(invoice_id); @@ -449,4 +459,4 @@ CREATE TABLE `tt_site_config` ( PRIMARY KEY (`param_name`) ); -INSERT INTO `tt_site_config` (`param_name`, `param_value`, `created`) VALUES ('version_db', '1.17.87', now()); # TODO: change when structure changes. +INSERT INTO `tt_site_config` (`param_name`, `param_value`, `created`) VALUES ('version_db', '1.18.07', now()); # TODO: change when structure changes.