X-Git-Url: http://wagnertech.de/git?a=blobdiff_plain;f=mysql.sql;h=d8294b2c4bea24068c0b7abb8cfdb39cbca7c513;hb=e1703495130c8ed88fe210f50a2a6b453b3c566b;hp=1734852d5c7205656ace4e5492e135ac9be7c241;hpb=b76e427286701104c84a869461ceb7339b1b943a;p=timetracker.git diff --git a/mysql.sql b/mysql.sql index 1734852d..d8294b2c 100644 --- a/mysql.sql +++ b/mysql.sql @@ -13,23 +13,25 @@ # This table stores settings common to all team members such as language, week start day, etc. # CREATE TABLE `tt_teams` ( - `id` int(11) NOT NULL auto_increment, # team id - `timestamp` timestamp NOT NULL, # 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 ("projects" or "projects and tasks") - `record_type` smallint(2) NOT NULL DEFAULT '0', # time record type ("start and finish", "duration", or both) - `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". - `custom_logo` tinyint(4) default '0', # whether to use a custom logo or not - `status` tinyint(4) default '1', # team status + `id` int(11) NOT NULL auto_increment, # team id + `timestamp` timestamp NOT NULL, # 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 ("projects" or "projects and tasks") + `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 + `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 PRIMARY KEY (`id`) ); @@ -137,7 +139,7 @@ CREATE TABLE `tt_log` ( `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` blob, # user provided comment for time record + `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 PRIMARY KEY (`id`) @@ -325,7 +327,7 @@ CREATE TABLE `tt_expense_items` ( `user_id` int(11) NOT NULL, # user id the expense item is reported by `client_id` int(11) default NULL, # client id `project_id` int(11) default NULL, # project id - `name` varchar(255) NOT NULL, # expense item name (what is an expense for) + `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 @@ -339,14 +341,31 @@ 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); + # -# Structure for table tt_monthly_quota. -# This table lists expense items. +# Structure for table tt_predefined_expenses. +# This table keeps names and costs for predefined expenses. # +CREATE TABLE `tt_predefined_expenses` ( + `id` int(11) NOT NULL auto_increment, # predefined expense id + `team_id` int(11) NOT NULL, # team 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`) +); -CREATE TABLE `tt_monthly_quota` ( - `year` SMALLINT UNSIGNED NOT NULL , # year we'setting monthly quota for - `month` TINYINT UNSIGNED NOT NULL , # month we're settng monthly quota for - `quota` SMALLINT UNSIGNED NOT NULL , # the monthly quota - PRIMARY KEY (`year`, `month`) + +# +# Structure for table tt_monthly_quotas. +# This table keeps monthly work hour quotas for teams. +# +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 + 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;