#
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`)
);
# 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`)
);
`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`)
);
`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`)
);
`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`)
);
# 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`)
);
`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`)
);
# 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
);
`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`)
);
`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`)
);
`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`)
);
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`)
);
`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`)
);
`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`)
);
`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);
projects / timetracker.git / blobdiff