FrontAccounting forum — DB Schema anomalies

Re: DB Schema anomalies

null@example.com (apmuthu) — Sun, 17 Dec 2017 17:28:13 +0000

MySQL Bugs #13794 TEXT cannot use empty string as default value

(It is) not a bug.
It is caused by the configuration file automatically generated by the configuration tool under Windows.
In the my.ini, sql-mode is set to STRICT_TRANS_TABLES by default, which causes this error.

Re: DB Schema anomalies

null@example.com (apmuthu) — Mon, 20 Nov 2017 16:33:18 +0000

Why was the index:

UNIQUE KEY `name` (`name`,`rate`)

removed from the 0_tax_types table in FA 2.4 while it was there in FA 2.3?

Do we allow the same named tax with the same rate to appear more than once?

If so, how would we distinguish them in the select drop-down boxes?

@joe: what say you?

Re: DB Schema anomalies

null@example.com (apmuthu) — Tue, 31 Oct 2017 14:08:46 +0000

Schema update / synch scripts added to my repo - FA23, FA24.

FA 2.4 Schema optimisations consolidated

- @joe/@itronics can vet it for inclusion in the core.

Re: DB Schema anomalies

null@example.com (apmuthu) — Tue, 31 Oct 2017 09:35:09 +0000

In MySQL 5.0 and later, TEXT/BLOB fields cannot have default values.

The only case in FA where it occurs is in the sys_prefs table and that too only in FA 2.4.x:

`value` TEXT NOT NULL DEFAULT '',

It should be:

`value` TEXT NOT NULL,

In FA 2.3.x it was:

`value` tinytext,

Re: DB Schema anomalies

null@example.com (apmuthu) — Tue, 31 Oct 2017 09:14:12 +0000

After cleaning up the entire standard Chart of Accounts, we are left with the following CHAR fields (whilst all else have been mostly changed to VARCHAR):

0_bank_accounts:  `bank_curr_code` char(3) NOT NULL DEFAULT '',
0_crm_persons:  `lang` char(5) DEFAULT NULL,
0_currencies:  `curr_abrev` char(3) NOT NULL DEFAULT '',
0_debtors_master:  `curr_code` char(3) NOT NULL DEFAULT '',
0_exchange_rates:  `curr_code` char(3) NOT NULL DEFAULT '',
0_journal:  `currency` char(3) NOT NULL DEFAULT '',
0_prices:  `curr_abrev` char(3) NOT NULL DEFAULT '',
0_stock_category:  `dflt_mb_flag` char(1) NOT NULL DEFAULT 'B',
0_stock_master:  `mb_flag` char(1) NOT NULL DEFAULT 'B',
0_stock_master:  `depreciation_method` char(1) NOT NULL DEFAULT 'S',
0_suppliers:  `curr_code` char(3) DEFAULT NULL,

DB Schema anomalies

null@example.com (apmuthu) — Sat, 28 Oct 2017 05:31:17 +0000

The PRIMARY KEY of the 0_bom table in both FA 2.3 and FA 2.4 is part of the list below:

  PRIMARY KEY  (`parent`,`component`,`workcentre_added`,`loc_code`),
  KEY `component` (`component`),
  KEY `id` (`id`),
  KEY `loc_code` (`loc_code`),
  KEY `parent` (`parent`,`loc_code`),
  KEY `workcentre_added` (`workcentre_added`)

It can be re-arranged like this to save an index that would then have become redundant:

  PRIMARY KEY  (`parent`,`loc_code`,`component`,`workcentre_added`),
  KEY `component` (`component`),
  KEY `id` (`id`),
  KEY `loc_code` (`loc_code`),
  KEY `workcentre_added` (`workcentre_added`)

Also why have the erstwhile VARCHAR fields like loc_code and some other fields now become CHAR in FA 2.4?