X-Git-Url: http://wagnertech.de/git?a=blobdiff_plain;f=mysql.sql;h=a69387b384c487758539321d6aed43c77a4b7262;hb=ad98b13400fdc2fa2299a22c9bd54c4cd27960c3;hp=b7cd700241d5b412a820a77c48d214750130acf2;hpb=faab3aece19a445dd7e2e42d82f47e701d32366f;p=timetracker.git diff --git a/mysql.sql b/mysql.sql index b7cd7002..a69387b3 100644 --- a/mysql.sql +++ b/mysql.sql @@ -14,26 +14,26 @@ # CREATE TABLE `tt_teams` ( `id` int(11) NOT NULL auto_increment, # team id - `timestamp` timestamp NOT NULL, # modification timestamp + `timestamp` timestamp default CURRENT_TIMESTAMP, # modification timestamp `name` varchar(80) default NULL, # team name - `address` varchar(255) default NULL, # team address, used in invoices `currency` varchar(7) default NULL, # team currency symbol `decimal_mark` char(1) NOT NULL default '.', # separator in decimals `lang` varchar(10) NOT NULL default 'en', # language `date_format` varchar(20) NOT NULL default '%Y-%m-%d', # date format `time_format` varchar(20) NOT NULL default '%H:%M', # time format - `week_start` smallint(2) NOT NULL DEFAULT '0', # Week start day, 0 == Sunday. - `tracking_mode` smallint(2) NOT NULL DEFAULT '1', # tracking mode ("time", "projects" or "projects and tasks") - `project_required` smallint(2) NOT NULL DEFAULT '0', # whether a project selection is required or optional - `task_required` smallint(2) NOT NULL DEFAULT '0', # whether a task selection is required or optional - `record_type` smallint(2) NOT NULL DEFAULT '0', # time record type ("start and finish", "duration", or both) - `uncompleted_indicators` smallint(2) NOT NULL DEFAULT '0', # whether to show indicators for users with uncompleted time entries + `week_start` smallint(2) NOT NULL default 0, # Week start day, 0 == Sunday. + `tracking_mode` smallint(2) NOT NULL default 1, # tracking mode ("time", "projects" or "projects and tasks") + `project_required` smallint(2) NOT NULL default 0, # whether a project selection is required or optional + `task_required` smallint(2) NOT NULL default 0, # whether a task selection is required or optional + `record_type` smallint(2) NOT NULL default 0, # time record type ("start and finish", "duration", or both) + `bcc_email` varchar(100) default NULL, # bcc email to copy all reports to `plugins` varchar(255) default NULL, # a list of enabled plugins for team `lock_spec` varchar(255) default NULL, # Cron specification for record locking, # for example: "0 10 * * 1" for "weekly on Mon at 10:00". - `workday_hours` smallint(6) DEFAULT '8', # number of work hours in a regular day - `custom_logo` tinyint(4) default '0', # whether to use a custom logo or not - `status` tinyint(4) default '1', # team status + `workday_minutes` smallint(4) default 480, # number of work minutes in a regular working day + `custom_logo` tinyint(4) default 0, # whether to use a custom logo or not + `config` text default NULL, # miscellaneous team configuration settings + `status` tinyint(4) default 1, # team status PRIMARY KEY (`id`) ); @@ -42,17 +42,17 @@ CREATE TABLE `tt_teams` ( # Structure for table tt_users. This table is used to store user properties. # CREATE TABLE `tt_users` ( - `id` int(11) NOT NULL auto_increment, # user id - `timestamp` timestamp NOT NULL, # modification timestamp - `login` varchar(50) COLLATE utf8_bin NOT NULL, # user login - `password` varchar(50) default NULL, # password hash - `name` varchar(100) default NULL, # user name - `team_id` int(11) NOT NULL, # team id - `role` int(11) default '4', # user role ("manager", "co-manager", "client", or "user") - `client_id` int(11) default NULL, # client id for "client" user role - `rate` float(6,2) NOT NULL default '0.00', # default hourly rate - `email` varchar(100) default NULL, # user email - `status` tinyint(4) default '1', # user status + `id` int(11) NOT NULL auto_increment, # user id + `timestamp` timestamp default CURRENT_TIMESTAMP, # modification timestamp + `login` varchar(50) COLLATE utf8_bin NOT NULL, # user login + `password` varchar(50) default NULL, # password hash + `name` varchar(100) default NULL, # user name + `team_id` int(11) NOT NULL, # team id + `role` int(11) default 4, # user role ("manager", "co-manager", "client", or "user") + `client_id` int(11) default NULL, # client id for "client" user role + `rate` float(6,2) NOT NULL default '0.00', # default hourly rate + `email` varchar(100) default NULL, # user email + `status` tinyint(4) default 1, # user status PRIMARY KEY (`id`) ); @@ -73,7 +73,7 @@ CREATE TABLE `tt_projects` ( `name` varchar(80) COLLATE utf8_bin NOT NULL, # project name `description` varchar(255) default NULL, # project description `tasks` text default NULL, # comma-separated list of task ids associated with this project - `status` tinyint(4) default '1', # project status + `status` tinyint(4) default 1, # project status PRIMARY KEY (`id`) ); @@ -89,7 +89,7 @@ CREATE TABLE `tt_tasks` ( `team_id` int(11) NOT NULL, # team id `name` varchar(80) COLLATE utf8_bin NOT NULL, # task name `description` varchar(255) default NULL, # task description - `status` tinyint(4) default '1', # task status + `status` tinyint(4) default 1, # task status PRIMARY KEY (`id`) ); @@ -105,7 +105,7 @@ CREATE TABLE `tt_user_project_binds` ( `user_id` int(11) NOT NULL, # user id `project_id` int(11) NOT NULL, # project id `rate` float(6,2) default '0.00', # rate for this user when working on this project - `status` tinyint(4) default '1', # bind status + `status` tinyint(4) default 1, # bind status PRIMARY KEY (`id`) ); @@ -131,19 +131,20 @@ create index task_idx on tt_project_task_binds(task_id); # If you use custom fields, additional info for each record may exist in tt_custom_field_log. # CREATE TABLE `tt_log` ( - `id` bigint NOT NULL auto_increment, # time record id - `timestamp` timestamp NOT NULL, # modification timestamp - `user_id` int(11) NOT NULL, # user 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) - `client_id` int(11) default NULL, # client id - `project_id` int(11) default NULL, # project id - `task_id` int(11) default NULL, # task id - `invoice_id` int(11) default NULL, # invoice id - `comment` text, # user provided comment for time record - `billable` tinyint(4) default '0', # whether the record is billable or not - `status` tinyint(4) default '1', # time record status + `id` bigint NOT NULL auto_increment, # time record id + `timestamp` timestamp default CURRENT_TIMESTAMP, # modification timestamp + `user_id` int(11) NOT NULL, # user 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) + `client_id` int(11) default NULL, # client id + `project_id` int(11) default NULL, # project id + `task_id` int(11) default NULL, # task id + `invoice_id` int(11) default NULL, # invoice id + `comment` text, # user provided comment for time record + `billable` tinyint(4) default 0, # whether the record is billable or not + `paid` tinyint(4) default 0, # whether the record is paid + `status` tinyint(4) default 1, # time record status PRIMARY KEY (`id`) ); @@ -165,7 +166,7 @@ CREATE TABLE `tt_invoices` ( `name` varchar(80) COLLATE utf8_bin NOT NULL, # invoice name `date` date NOT NULL, # invoice date `client_id` int(11) NOT NULL, # client id - `status` tinyint(4) default '1', # invoice status + `status` tinyint(4) default 1, # invoice status PRIMARY KEY (`id`) ); @@ -177,9 +178,9 @@ create unique index name_idx on tt_invoices(team_id, name, status); # Structure for table tt_tmp_refs. Used for reset password mechanism. # CREATE TABLE `tt_tmp_refs` ( - `timestamp` timestamp NOT NULL, # creation timestamp - `ref` char(32) NOT NULL default '', # unique reference for user, used in urls - `user_id` int(11) NOT NULL # user id + `timestamp` timestamp default CURRENT_TIMESTAMP, # creation timestamp + `ref` char(32) NOT NULL default '', # unique reference for user, used in urls + `user_id` int(11) NOT NULL # user id ); @@ -190,29 +191,32 @@ 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 + `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) `project_id` int(11) default NULL, # project id (if selected) `task_id` int(11) default NULL, # task id (if selected) `billable` tinyint(4) default NULL, # whether to include billable, not billable, or all records `invoice` tinyint(4) default NULL, # whether to include invoiced, not invoiced, or all records + `paid_status` tinyint(4) default NULL, # whether to include paid, not paid, or all records `users` text default NULL, # Comma-separated list of user ids. Nothing here means "all" users. `period` tinyint(4) default NULL, # selected period type for report `period_start` date default NULL, # period start `period_end` date default NULL, # period end - `show_client` tinyint(4) NOT NULL default '0', # whether to show client column - `show_invoice` tinyint(4) NOT NULL default '0', # whether to show invoice column - `show_project` tinyint(4) NOT NULL default '0', # whether to show project column - `show_start` tinyint(4) NOT NULL default '0', # whether to show start field - `show_duration` tinyint(4) NOT NULL default '0', # whether to show duration field - `show_cost` tinyint(4) NOT NULL default '0', # whether to show cost field - `show_task` tinyint(4) NOT NULL default '0', # whether to show task column - `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_totals_only` tinyint(4) NOT NULL default '0', # whether to show totals only + `show_client` tinyint(4) NOT NULL default 0, # whether to show client column + `show_invoice` tinyint(4) NOT NULL default 0, # whether to show invoice column + `show_paid` tinyint(4) NOT NULL default 0, # whether to show paid column + `show_project` tinyint(4) NOT NULL default 0, # whether to show project column + `show_start` tinyint(4) NOT NULL default 0, # whether to show start field + `show_duration` tinyint(4) NOT NULL default 0, # whether to show duration field + `show_cost` tinyint(4) NOT NULL default 0, # whether to show cost field + `show_task` tinyint(4) NOT NULL default 0, # whether to show task column + `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_totals_only` tinyint(4) NOT NULL default 0, # whether to show totals only `group_by` varchar(20) default NULL, # group by field - `status` tinyint(4) default '1', # favorite report status + `status` tinyint(4) default 1, # favorite report status PRIMARY KEY (`id`) ); @@ -228,8 +232,10 @@ CREATE TABLE `tt_cron` ( `next` int(11) default NULL, # UNIX timestamp of when to run next job `report_id` int(11) default NULL, # report id from tt_fav_reports, a report to mail on schedule `email` varchar(100) default NULL, # email to send results to + `cc` varchar(100) default NULL, # cc email to send results to + `subject` varchar(100) default NULL, # email subject `report_condition` varchar(255) default NULL, # report condition, "count > 0" for sending not empty reports - `status` tinyint(4) default '1', # entry status + `status` tinyint(4) default 1, # entry status PRIMARY KEY (`id`) ); @@ -244,7 +250,7 @@ CREATE TABLE `tt_clients` ( `address` varchar(255) default NULL, # client address `tax` float(6,2) default '0.00', # applicable tax for this client `projects` text default NULL, # comma-separated list of project ids assigned to this client - `status` tinyint(4) default '1', # client status + `status` tinyint(4) default 1, # client status PRIMARY KEY (`id`) ); @@ -287,10 +293,10 @@ 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 `team_id` int(11) NOT NULL, # team id - `type` tinyint(4) NOT NULL default '0', # custom field type (text or dropdown) + `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 - `status` tinyint(4) default '1', # custom field status + `required` tinyint(4) default 0, # whether this custom field is mandatory for time records + `status` tinyint(4) default 1, # custom field status PRIMARY KEY (`id`) ); @@ -316,7 +322,7 @@ CREATE TABLE `tt_custom_field_log` ( `field_id` int(11) NOT NULL, # custom field id `option_id` int(11) default NULL, # Option id. Used for dropdown custom fields. `value` varchar(255) default NULL, # Text value. Used for text custom fields. - `status` tinyint(4) default '1', # custom field log entry status + `status` tinyint(4) default 1, # custom field log entry status PRIMARY KEY (`id`) ); @@ -334,7 +340,8 @@ CREATE TABLE `tt_expense_items` ( `name` text NOT NULL, # expense item name (what is an expense for) `cost` decimal(10,2) default '0.00', # item cost (including taxes, etc.) `invoice_id` int(11) default NULL, # invoice id - `status` tinyint(4) default '1', # item status + `paid` tinyint(4) default 0, # whether the item is paid + `status` tinyint(4) default 1, # item status PRIMARY KEY (`id`) ); @@ -367,9 +374,36 @@ CREATE TABLE `tt_monthly_quotas` ( `team_id` int(11) NOT NULL, # team id `year` smallint(5) UNSIGNED NOT NULL, # quota year `month` tinyint(3) UNSIGNED NOT NULL, # quota month - `quota` smallint(5) UNSIGNED NOT NULL, # number of work hours in specified month and year + `minutes` int(11) default NULL, # quota in minutes in specified month and year PRIMARY KEY (`team_id`,`year`,`month`) ); ALTER TABLE `tt_monthly_quotas` ADD CONSTRAINT `FK_TT_TEAM_CONSTRAING` FOREIGN KEY (`team_id`) REFERENCES `tt_teams` (`id`) ON DELETE CASCADE ON UPDATE CASCADE; + + +# +# Structure for table tt_roles. This table stores customized team roles. +# +CREATE TABLE `tt_roles` ( + `id` int(11) NOT NULL auto_increment, # Role id. Identifies roles for all groups on the server. + `team_id` int(11) NOT NULL, # Team id the role is defined for. + `name` varchar(80) default NULL, # Role name - custom role name. In case we are editing a + # predefined role (USER, etc.), we can rename the role here. + `description` varchar(255) default NULL, # Role description. + `rank` int(11) default 0, # Role rank, an integer value between 0-324. Predefined role ranks: + # USER - 4, CLIENT - 16, COMANAGER - 68, MANAGER - 324. + # Rank is used to determine what "lesser roles" are in each group + # for sutuations such as "manage_users". + # It also identifies a role within a team (by its "rank"). + # Value of rank is to be used in role field in tt_users table, + # just like standard roles now. + `rights` text default NULL, # Comma-separated list of rights assigned to a role. + # NULL here for predefined roles (4, 16, 68, 324 - manager) + # means a hard-coded set of default access rights. + `status` tinyint(4) default 1, # Role status. + PRIMARY KEY (`id`) +); + +# Create an index that guarantees unique active and inactive role ranks in each group. +create unique index role_idx on tt_roles(team_id, rank, status);