X-Git-Url: http://wagnertech.de/git?a=blobdiff_plain;ds=sidebyside;f=mysql.sql;h=a3c1cbc037aa0c54cc73474fe97ee2a234a319f2;hb=c138ab042a599beb780895e5a5f54a1b287d67ae;hp=a69387b384c487758539321d6aed43c77a4b7262;hpb=2dbf96e395377914a401aa3a635fce1120780b8f;p=timetracker.git diff --git a/mysql.sql b/mysql.sql index a69387b3..a3c1cbc0 100644 --- a/mysql.sql +++ b/mysql.sql @@ -38,6 +38,37 @@ CREATE TABLE `tt_teams` ( ); +# +# 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); + +# Insert site-wide roles - site administrator and top manager. +INSERT INTO `tt_roles` (`team_id`, `name`, `rank`, `rights`) VALUES (0, 'Site administrator', 1024, 'administer_site'); +INSERT INTO `tt_roles` (`team_id`, `name`, `rank`, `rights`) VALUES (0, 'Top manager', 512, 'track_own_time,track_own_expenses,view_own_reports,view_own_charts,view_own_invoices,manage_own_settings,view_users,track_time,track_expenses,view_reports,view_charts,override_punch_mode,swap_roles,approve_timesheets,manage_users,manage_projects,manage_tasks,manage_custom_fields,manage_clients,manage_invoices,manage_features,manage_basic_settings,manage_advanced_settings,manage_roles,export_data,manage_subgroups'); + + # # Structure for table tt_users. This table is used to store user properties. # @@ -49,6 +80,7 @@ CREATE TABLE `tt_users` ( `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") + `role_id` int(11) default NULL, # future replacement of role `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 @@ -61,7 +93,7 @@ create unique index login_idx on tt_users(login, status); # Create admin account with password 'secret'. Admin is a superuser, who can create teams. DELETE from `tt_users` WHERE login = 'admin'; -INSERT INTO `tt_users` (`login`, `password`, `name`, `team_id`, `role`) VALUES ('admin', md5('secret'), 'Admin', '0', '1024'); +INSERT INTO `tt_users` (`login`, `password`, `name`, `team_id`, `role`, `role_id`) VALUES ('admin', md5('secret'), 'Admin', '0', '1024', (select id from tt_roles where rank = 1024)); # @@ -383,27 +415,16 @@ ALTER TABLE `tt_monthly_quotas` # -# Structure for table tt_roles. This table stores customized team roles. +# Structure for table tt_site_config. This table stores configuration data +# for Time Tracker site as a whole. +# For example, database version, code version, site language, etc. # -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 TABLE `tt_site_config` ( + `param_name` varchar(32) NOT NULL, # parameter name + `param_value` text default NULL, # parameter value + `created` datetime default NULL, # creation time + `updated` datetime default NULL, # modification time + PRIMARY KEY (`param_name`) ); -# 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); +INSERT INTO `tt_site_config` (`param_name`, `param_value`, `created`) VALUES ('version_db', '1.17.43', now()); # TODO: change when structure changes.