From: Nik Okuntseff Date: Fri, 28 Jun 2019 16:37:21 +0000 (+0000) Subject: Introduced tt_entity_custom_fields table. X-Git-Tag: timetracker_1.19-1~8 X-Git-Url: http://wagnertech.de/git?p=timetracker.git;a=commitdiff_plain;h=066efce7ef712710e229308b6099f5af410aecb8 Introduced tt_entity_custom_fields table. --- diff --git a/WEB-INF/templates/footer.tpl b/WEB-INF/templates/footer.tpl index 23519575..9888b867 100644 --- a/WEB-INF/templates/footer.tpl +++ b/WEB-INF/templates/footer.tpl @@ -12,7 +12,7 @@
- - - + +
 Anuko Time Tracker 1.19.6.5021 | Copyright © Anuko | +  Anuko Time Tracker 1.19.7.5022 | Copyright © Anuko | {$i18n.footer.credits} | {$i18n.footer.license} | {$i18n.footer.improve} diff --git a/cf_custom_field_edit.php b/cf_custom_field_edit.php index 0bf5818d..c5dd0904 100644 --- a/cf_custom_field_edit.php +++ b/cf_custom_field_edit.php @@ -51,10 +51,10 @@ $form = new Form('fieldForm'); if ($err->no()) { $form->addInput(array('type'=>'text','maxlength'=>'100','name'=>'name','value'=>$field['label'])); $form->addInput(array('type'=>'hidden','name'=>'id','value'=>$cl_id)); - $form->addInput(array('type'=>'checkbox','name'=>'required','value'=>$field['required'])); $form->addInput(array('type'=>'combobox','name'=>'type','value'=>$field['type'], 'data'=>array(CustomFields::TYPE_TEXT=>$i18n->get('label.type_text'), CustomFields::TYPE_DROPDOWN=>$i18n->get('label.type_dropdown')))); + $form->addInput(array('type'=>'checkbox','name'=>'required','value'=>$field['required'])); $form->addInput(array('type'=>'submit','name'=>'btn_save','value'=>$i18n->get('button.save'))); } diff --git a/dbinstall.php b/dbinstall.php index 6a65336a..c2ce2618 100644 --- a/dbinstall.php +++ b/dbinstall.php @@ -1161,7 +1161,7 @@ if ($_POST) { ttExecute("UPDATE `tt_site_config` SET param_value = '1.19.0', modified = now() where param_name = 'version_db' and param_value = '1.18.61'"); } - if ($_POST["convert11900to11906"]) { + if ($_POST["convert11900to11907"]) { ttExecute("CREATE TABLE `tt_work_currencies` (`id` int(10) unsigned NOT NULL,`name` varchar(10) NOT NULL,PRIMARY KEY (`id`))"); ttExecute("create unique index currency_idx on tt_work_currencies(`name`)"); ttExecute("INSERT INTO `tt_work_currencies` (`id`, `name`) VALUES ('1', 'USD'), ('2', 'CAD'), ('3', 'AUD'), ('4', 'EUR'), ('5', 'NZD')"); @@ -1177,6 +1177,9 @@ if ($_POST) { ttExecute("update `tt_custom_fields` inner join `tt_site_config` sc on (sc.param_name = 'version_db' and sc.param_value = '1.19.4') set entity_type = 1 where entity_type = 'time'"); ttExecute("ALTER TABLE `tt_custom_fields` modify entity_type tinyint(4) NOT NULL default '1'"); ttExecute("UPDATE `tt_site_config` SET param_value = '1.19.6', modified = now() where param_name = 'version_db' and param_value = '1.19.4'"); + ttExecute("CREATE TABLE `tt_entity_custom_fields` (`id` int(10) unsigned NOT NULL auto_increment,`group_id` int(10) unsigned NOT NULL,`org_id` int(10) unsigned NOT NULL,`entity_type` tinyint(4) NOT NULL,`entity_id` int(10) unsigned NOT NULL,`field_id` int(10) unsigned NOT NULL,`option_id` int(10) unsigned default NULL,`value` varchar(255) default NULL,`created` datetime default NULL,`created_ip` varchar(45) default NULL,`created_by` int(10) unsigned default NULL,`modified` datetime default NULL,`modified_ip` varchar(45) default NULL,`modified_by` int(10) unsigned default NULL,`status` tinyint(4) default 1,PRIMARY KEY (`id`))"); + ttExecute("create unique index entity_idx on tt_entity_custom_fields(entity_type, entity_id, field_id)"); + ttExecute("UPDATE `tt_site_config` SET param_value = '1.19.7', modified = now() where param_name = 'version_db' and param_value = '1.19.6'"); } if ($_POST["cleanup"]) { @@ -1226,7 +1229,7 @@ if ($_POST) {

DB Install

-
Create database structure (v1.19.6) + Create database structure (v1.19.7)
(applies only to new installations, do not execute when updating)
@@ -1275,8 +1278,8 @@ if ($_POST) {
Update database structure (v1.19 to v1.19.6)Update database structure (v1.19 to v1.19.7)
diff --git a/mysql.sql b/mysql.sql index fc67b19f..b8d7e134 100644 --- a/mysql.sql +++ b/mysql.sql @@ -423,6 +423,36 @@ CREATE TABLE `tt_custom_field_log` ( create index log_idx on tt_custom_field_log(log_id); +# +# Structure for table tt_entity_custom_fields. +# This table stores custom field values for entities such as users and projects +# except for "time" entity (and possibly "expense" in future). +# "time" custom fields are kept separately in tt_custom_field_log +# because tt_log (and tt_custom_field_log) can grow very large. +# +CREATE TABLE `tt_entity_custom_fields` ( + `id` int(10) unsigned NOT NULL auto_increment, # record id in this table + `group_id` int(10) unsigned NOT NULL, # group id + `org_id` int(10) unsigned NOT NULL, # organization id + `entity_type` tinyint(4) NOT NULL, # entity type + `entity_id` int(10) unsigned NOT NULL, # entity id this record corresponds to + `field_id` int(10) unsigned NOT NULL, # custom field id + `option_id` int(10) unsigned default NULL, # Option id. Used for dropdown custom fields. + `value` varchar(255) default NULL, # Text value. Used for text custom fields. + `created` datetime default NULL, # creation timestamp + `created_ip` varchar(45) default NULL, # creator ip + `created_by` int(10) unsigned default NULL, # creator user_id + `modified` datetime default NULL, # modification timestamp + `modified_ip` varchar(45) default NULL, # modifier ip + `modified_by` int(10) unsigned default NULL, # modifier user_id + `status` tinyint(4) default 1, # record status + PRIMARY KEY (`id`) +); + +# Create an index that guarantees unique custom fields per entity. +create unique index entity_idx on tt_entity_custom_fields(entity_type, entity_id, field_id); + + # # Structure for table tt_expense_items. # This table lists expense items. @@ -611,4 +641,4 @@ CREATE TABLE `tt_site_config` ( PRIMARY KEY (`param_name`) ); -INSERT INTO `tt_site_config` (`param_name`, `param_value`, `created`) VALUES ('version_db', '1.19.6', now()); # TODO: change when structure changes. +INSERT INTO `tt_site_config` (`param_name`, `param_value`, `created`) VALUES ('version_db', '1.19.7', now()); # TODO: change when structure changes. diff --git a/plugins/CustomFields.class.php b/plugins/CustomFields.class.php index f36a4263..125e100b 100644 --- a/plugins/CustomFields.class.php +++ b/plugins/CustomFields.class.php @@ -254,7 +254,7 @@ class CustomFields { $res = $mdb2->query($sql); if (!is_a($res, 'PEAR_Error')) { while ($val = $res->fetchRow()) { - $fields[] = $val; // array('id'=>$val['id'],'type'=>$val['type'],'label'=>$val['label']); + $fields[] = $val; } return $fields; }