X-Git-Url: http://wagnertech.de/git?a=blobdiff_plain;f=mysql.sql;h=0f3f00c9b10a5123dfaf55039f7e5e0d2e02b3df;hb=2da2031951c852b4620c173e21c24f148fdf4999;hp=14fa2aaba884ab830e9b25f653d9765ae5ec68b9;hpb=208b036ea536d9d0e96e008c524a52fa3626dcdb;p=timetracker.git diff --git a/mysql.sql b/mysql.sql index 14fa2aab..0f3f00c9 100644 --- a/mysql.sql +++ b/mysql.sql @@ -17,6 +17,7 @@ CREATE TABLE `tt_groups` ( `parent_id` int(11) default NULL, # parent group id `org_id` int(11) default NULL, # organization id (id of top group) `name` varchar(80) default NULL, # group name + `description` varchar(255) default NULL, # group description `currency` varchar(7) default NULL, # currency symbol `decimal_mark` char(1) NOT NULL default '.', # separator in decimals `lang` varchar(10) NOT NULL default 'en', # language @@ -177,6 +178,7 @@ CREATE TABLE `tt_project_task_binds` ( # Indexes for tt_project_task_binds. create index project_idx on tt_project_task_binds(project_id); create index task_idx on tt_project_task_binds(task_id); +create unique index project_task_idx on tt_project_task_binds(project_id, task_id); # @@ -253,6 +255,8 @@ CREATE TABLE `tt_fav_reports` ( `id` int(11) NOT NULL auto_increment, # favorite report id `name` varchar(200) NOT NULL, # favorite report name `user_id` int(11) NOT NULL, # user id favorite report belongs to + `group_id` int(11) default NULL, # group id + `org_id` int(11) default NULL, # organization id `report_spec` text default NULL, # future replacement field for all report settings `client_id` int(11) default NULL, # client id (if selected) `cf_1_option_id` int(11) default NULL, # custom field 1 option id (if selected) @@ -293,6 +297,7 @@ CREATE TABLE `tt_fav_reports` ( CREATE TABLE `tt_cron` ( `id` int(11) NOT NULL auto_increment, # entry id `group_id` int(11) NOT NULL, # group id + `org_id` int(11) default NULL, # organization id `cron_spec` varchar(255) NOT NULL, # cron specification, "0 1 * * *" for "daily at 01:00" `last` int(11) default NULL, # UNIX timestamp of when job was last run `next` int(11) default NULL, # UNIX timestamp of when to run next job @@ -329,13 +334,16 @@ create unique index client_name_idx on tt_clients(group_id, name, status); # Structure for table tt_client_project_binds. This table maps clients to assigned projects. # CREATE TABLE `tt_client_project_binds` ( - `client_id` int(11) NOT NULL, # client id - `project_id` int(11) NOT NULL # project id + `client_id` int(11) NOT NULL, # client id + `project_id` int(11) NOT NULL, # project id + `group_id` int(11) default NULL, # group id + `org_id` int(11) default NULL # organization id ); # Indexes for tt_client_project_binds. create index client_idx on tt_client_project_binds(client_id); create index project_idx on tt_client_project_binds(project_id); +create unique index client_project_idx on tt_client_project_binds(client_id, project_id); # @@ -344,6 +352,8 @@ create index project_idx on tt_client_project_binds(project_id); # CREATE TABLE `tt_config` ( `user_id` int(11) NOT NULL, # user id + `group_id` int(11) default NULL, # group id + `org_id` int(11) default NULL, # organization id `param_name` varchar(32) NOT NULL, # parameter name `param_value` varchar(80) default NULL # parameter value ); @@ -360,6 +370,7 @@ create unique index param_idx on tt_config(user_id, param_name); CREATE TABLE `tt_custom_fields` ( `id` int(11) NOT NULL auto_increment, # custom field id `group_id` int(11) NOT NULL, # group id + `org_id` int(11) default NULL, # organization id `type` tinyint(4) NOT NULL default 0, # custom field type (text or dropdown) `label` varchar(32) NOT NULL default '', # custom field label `required` tinyint(4) default 0, # whether this custom field is mandatory for time records @@ -373,8 +384,11 @@ CREATE TABLE `tt_custom_fields` ( # CREATE TABLE `tt_custom_field_options` ( `id` int(11) NOT NULL auto_increment, # option id + `group_id` int(11) default NULL, # group id + `org_id` int(11) default NULL, # organization id `field_id` int(11) NOT NULL, # custom field id `value` varchar(32) NOT NULL default '', # option value + `status` tinyint(4) default 1, # option status PRIMARY KEY (`id`) ); @@ -384,7 +398,9 @@ CREATE TABLE `tt_custom_field_options` ( # This table supplements tt_log and contains custom field values for records. # CREATE TABLE `tt_custom_field_log` ( - `id` bigint NOT NULL auto_increment, # cutom field log id + `id` bigint NOT NULL auto_increment, # custom field log id + `group_id` int(11) default NULL, # group id + `org_id` int(11) default NULL, # organization id `log_id` bigint NOT NULL, # id of a record in tt_log this record corresponds to `field_id` int(11) NOT NULL, # custom field id `option_id` int(11) default NULL, # Option id. Used for dropdown custom fields. @@ -405,6 +421,7 @@ CREATE TABLE `tt_expense_items` ( `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 + `org_id` int(11) default NULL, # organization 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) @@ -437,6 +454,7 @@ create index invoice_idx on tt_expense_items(invoice_id); CREATE TABLE `tt_predefined_expenses` ( `id` int(11) NOT NULL auto_increment, # predefined expense id `group_id` int(11) NOT NULL, # group id + `org_id` int(11) default NULL, # organization id `name` varchar(255) NOT NULL, # predefined expense name, such as mileage `cost` decimal(10,2) default '0.00', # cost for one unit PRIMARY KEY (`id`) @@ -470,4 +488,4 @@ CREATE TABLE `tt_site_config` ( PRIMARY KEY (`param_name`) ); -INSERT INTO `tt_site_config` (`param_name`, `param_value`, `created`) VALUES ('version_db', '1.18.15', now()); # TODO: change when structure changes. +INSERT INTO `tt_site_config` (`param_name`, `param_value`, `created`) VALUES ('version_db', '1.18.26', now()); # TODO: change when structure changes.