# 2) Then, execute this script from command prompt with a command like this:
# mysql -h host -u user -p -D db_name < mysql.sql
-# create database timetracker character set = 'utf8';
+# create database timetracker character set = 'utf8mb4';
# use timetracker;
#
CREATE TABLE `tt_users` (
`id` int(11) NOT NULL auto_increment, # user id
- `login` varchar(50) COLLATE utf8_bin NOT NULL, # user login
+ `login` varchar(50) COLLATE utf8mb4_bin NOT NULL,# user login
`password` varchar(50) default NULL, # password hash
`name` varchar(100) default NULL, # user name
`group_id` int(11) NOT NULL, # group id
# Structure for table tt_projects.
#
CREATE TABLE `tt_projects` (
- `id` int(11) NOT NULL auto_increment, # project id
- `group_id` int(11) NOT NULL, # group id
- `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
+ `id` int(11) NOT NULL auto_increment, # project id
+ `group_id` int(11) NOT NULL, # group id
+ `org_id` int(11) default NULL, # organization id
+ `name` varchar(80) COLLATE utf8mb4_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
PRIMARY KEY (`id`)
);
# Structure for table tt_tasks.
#
CREATE TABLE `tt_tasks` (
- `id` int(11) NOT NULL auto_increment, # task id
- `group_id` int(11) NOT NULL, # group 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
+ `id` int(11) NOT NULL auto_increment, # task id
+ `group_id` int(11) NOT NULL, # group id
+ `org_id` int(11) default NULL, # organization id
+ `name` varchar(80) COLLATE utf8mb4_bin NOT NULL, # task name
+ `description` varchar(255) default NULL, # task description
+ `status` tinyint(4) default 1, # task status
PRIMARY KEY (`id`)
);
`id` int(11) NOT NULL auto_increment, # bind id
`user_id` int(11) NOT NULL, # user 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
`rate` float(6,2) default '0.00', # rate for this user when working on this project
`status` tinyint(4) default 1, # bind status
PRIMARY KEY (`id`)
# Structure for table tt_project_task_binds. This table maps projects to assigned tasks.
#
CREATE TABLE `tt_project_task_binds` (
- `project_id` int(11) NOT NULL, # project id
- `task_id` int(11) NOT NULL # task id
+ `project_id` int(11) NOT NULL, # project id
+ `task_id` int(11) NOT NULL, # task id
+ `group_id` int(11) default NULL, # group id
+ `org_id` int(11) default NULL # organization id
);
# Indexes for tt_project_task_binds.
`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
+ `org_id` int(11) default NULL, # organization 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)
# Structure for table tt_invoices. Invoices are issued to clients for billable work.
#
CREATE TABLE `tt_invoices` (
- `id` int(11) NOT NULL auto_increment, # invoice id
- `group_id` int(11) NOT NULL, # group id
- `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
+ `id` int(11) NOT NULL auto_increment, # invoice id
+ `group_id` int(11) NOT NULL, # group id
+ `org_id` int(11) default NULL, # organization id
+ `name` varchar(80) COLLATE utf8mb4_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
PRIMARY KEY (`id`)
);
# Structure for table tt_clients. A client is an entity for whom work is performed and who may be invoiced.
#
CREATE TABLE `tt_clients` (
- `id` int(11) NOT NULL AUTO_INCREMENT, # client id
- `group_id` int(11) NOT NULL, # group id
- `name` varchar(80) COLLATE utf8_bin NOT NULL, # client name
- `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
+ `id` int(11) NOT NULL AUTO_INCREMENT, # client id
+ `group_id` int(11) NOT NULL, # group id
+ `org_id` int(11) default NULL, # organization id
+ `name` varchar(80) COLLATE utf8mb4_bin NOT NULL, # client name
+ `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
PRIMARY KEY (`id`)
);
#
CREATE TABLE `tt_monthly_quotas` (
`group_id` int(11) NOT NULL, # group id
+ `org_id` int(11) default NULL # organization id
`year` smallint(5) UNSIGNED NOT NULL, # quota year
`month` tinyint(3) UNSIGNED NOT NULL, # quota month
`minutes` int(11) default NULL, # quota in minutes in specified month and year
PRIMARY KEY (`param_name`)
);
-INSERT INTO `tt_site_config` (`param_name`, `param_value`, `created`) VALUES ('version_db', '1.18.08', now()); # TODO: change when structure changes.
+INSERT INTO `tt_site_config` (`param_name`, `param_value`, `created`) VALUES ('version_db', '1.18.15', now()); # TODO: change when structure changes.