From b36d1978a980173bae78818be6254041533a4d98 Mon Sep 17 00:00:00 2001 From: Nik Okuntseff Date: Fri, 16 Nov 2018 01:21:24 +0000 Subject: [PATCH] Changed collation to utf8mb4_bin to support 4-byte UTF-8 characters. --- WEB-INF/templates/footer.tpl | 2 +- dbinstall.php | 15 ++++++--- mysql.sql | 60 ++++++++++++++++++------------------ 3 files changed, 42 insertions(+), 35 deletions(-) diff --git a/WEB-INF/templates/footer.tpl b/WEB-INF/templates/footer.tpl index cb9bed06..e6154271 100644 --- a/WEB-INF/templates/footer.tpl +++ b/WEB-INF/templates/footer.tpl @@ -12,7 +12,7 @@
- - - + +
 Anuko Time Tracker 1.18.13.4430 | Copyright © Anuko | +  Anuko Time Tracker 1.18.14.4431 | Copyright © Anuko | {$i18n.footer.credits} | {$i18n.footer.license} | {$i18n.footer.improve} diff --git a/dbinstall.php b/dbinstall.php index 8a6d66fd..f81e31f7 100644 --- a/dbinstall.php +++ b/dbinstall.php @@ -956,7 +956,7 @@ if ($_POST) { print "Updated $tt_expense_items_updated tt_expense_items records...
\n"; } - if ($_POST["convert11797to11813"]) { + if ($_POST["convert11797to11814"]) { setChange("ALTER TABLE `tt_fav_reports` CHANGE `group_by` `group_by1` varchar(20) default NULL"); setChange("ALTER TABLE `tt_fav_reports` ADD `group_by2` varchar(20) default NULL AFTER `group_by1`"); setChange("ALTER TABLE `tt_fav_reports` ADD `group_by3` varchar(20) default NULL AFTER `group_by2`"); @@ -990,6 +990,13 @@ if ($_POST) { setChange("ALTER TABLE `tt_project_task_binds` ADD `org_id` int(11) default NULL AFTER `group_id`"); setChange("UPDATE `tt_site_config` SET param_value = '1.18.12', modified = now() where param_name = 'version_db' and param_value = '1.18.11'"); setChange("UPDATE `tt_site_config` SET param_value = '1.18.13', modified = now() where param_name = 'version_db' and param_value = '1.18.12'"); + setChange("ALTER TABLE `tt_users` MODIFY `login` varchar(50) COLLATE utf8mb4_bin NOT NULL"); + setChange("ALTER TABLE `tt_projects` MODIFY `name` varchar(80) COLLATE utf8mb4_bin NOT NULL"); + setChange("ALTER TABLE `tt_tasks` MODIFY `name` varchar(80) COLLATE utf8mb4_bin NOT NULL"); + setChange("ALTER TABLE `tt_invoices` MODIFY `name` varchar(80) COLLATE utf8mb4_bin NOT NULL"); + setChange("ALTER TABLE `tt_clients` MODIFY `name` varchar(80) COLLATE utf8mb4_bin NOT NULL"); + setChange("UPDATE `tt_site_config` SET param_value = '1.18.14', modified = now() where param_name = 'version_db' and param_value = '1.18.13'"); + // TODO: this does not work as we just introduced group_id and it is NULL. Same for tt_project_task_binds. Improve. // setChange("UPDATE `tt_user_project_binds` inner join `tt_site_config` sc on (sc.param_name = 'version_db' and sc.param_value = '1.18.12') set org_id = group_id where org_id is null"); } @@ -1037,7 +1044,7 @@ if ($_POST) {

DB Install

-
Create database structure (v1.18.13) + Create database structure (v1.18.14)
(applies only to new installations, do not execute when updating)
@@ -1082,8 +1089,8 @@ if ($_POST) {
Update database structure (v1.17.97 to v1.18.13)Update database structure (v1.17.97 to v1.18.14)
diff --git a/mysql.sql b/mysql.sql index 1b510a73..14a8aa0f 100644 --- a/mysql.sql +++ b/mysql.sql @@ -82,7 +82,7 @@ INSERT INTO `tt_roles` (`group_id`, `name`, `rank`, `rights`) VALUES (0, 'Top ma # 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 @@ -115,13 +115,13 @@ INSERT INTO `tt_users` (`login`, `password`, `name`, `group_id`, `role_id`) VALU # 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 - `org_id` int(11) default NULL, # organization 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`) ); @@ -133,12 +133,12 @@ create unique index project_idx on tt_projects(group_id, name, status); # 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 - `org_id` int(11) default NULL, # organization 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`) ); @@ -222,13 +222,13 @@ create index task_idx on tt_log(task_id); # 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 - `org_id` int(11) default NULL, # organization 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`) ); @@ -310,14 +310,14 @@ CREATE TABLE `tt_cron` ( # 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 - `org_id` int(11) default NULL, # organization 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`) ); @@ -469,4 +469,4 @@ CREATE TABLE `tt_site_config` ( PRIMARY KEY (`param_name`) ); -INSERT INTO `tt_site_config` (`param_name`, `param_value`, `created`) VALUES ('version_db', '1.18.13', now()); # TODO: change when structure changes. +INSERT INTO `tt_site_config` (`param_name`, `param_value`, `created`) VALUES ('version_db', '1.18.14', now()); # TODO: change when structure changes. -- 2.20.1