[WebERP-developers] schema weberp tables with index key duplicated

[gilberto d. s. a. <gs...@gm...>]

Hi for all!

After revise schema for weberp, we find inside files default.sql, demo.sql,
weberpchine.sql following tables, with duplicated function index key:

    banktrans,
    gltrans,
    mrpdemandtypes,
    salescatprod,
    stockrequest,
    storequestitemns.

These are commands executed to drop these keys, run on your database will
clean unnecessary index.


alter table banktrans drop key ref_10, drop key ref_9, drop key ref_8, drop
key ref_7;
alter table banktrans drop key ref_6, drop key ref_5, drop key ref_4, drop
key ref_3, drop key ref_2;
alter table gltrans drop key tag_2, drop key tag_3, drop key tag_4;
alter table mrpdemandtypes drop key mrpdemandtype;
alter table salescatprod drop key manufacturers_id_2;
alter table salescatprod drop key manufacturers_id;
alter table stockrequest drop key departmentid_2;
alter table stockrequest drop key loccode_2;
alter table stockrequestitems drop key stockid_2, drop key dispatchid_2;


Following revised tables contain entries commented and identified by:

1 - revised-081-,
2 - revised-082-,
3 - revised-083-,
4 - revised-084-,
5 - revised-085-,
6 - revised-086-,

All this are on pastebin [1]

[1] http://pastebin.com/EgXjpqb1

Please Phil could you verify and confirm these changes for next release?

Best regards!




CREATE TABLE IF NOT EXISTS `banktrans` (
  `banktransid` bigint(20) NOT NULL AUTO_INCREMENT,
  `type` smallint(6) NOT NULL DEFAULT '0',
  `transno` bigint(20) NOT NULL DEFAULT '0',
  `bankact` varchar(20) NOT NULL DEFAULT '0',
  `ref` varchar(50) NOT NULL DEFAULT '',
  `amountcleared` double NOT NULL DEFAULT '0',
  `exrate` double NOT NULL DEFAULT '1' COMMENT 'From bank account currency
to payment currency',
  `functionalexrate` double NOT NULL DEFAULT '1' COMMENT 'Account currency
to functional currency',
  `transdate` date NOT NULL DEFAULT '0000-00-00',
  `banktranstype` varchar(30) NOT NULL DEFAULT '',
  `amount` double NOT NULL DEFAULT '0',
  `currcode` char(3) NOT NULL DEFAULT '',
  PRIMARY KEY (`banktransid`),
  KEY `BankAct` (`bankact`,`ref`),
  KEY `TransDate` (`transdate`),
  KEY `TransType` (`banktranstype`),
  KEY `Type` (`type`,`transno`),
  KEY `CurrCode` (`currcode`),
  KEY `ref` (`ref`)
  /* revised-081-begin by gsavix08-jul-2014
  KEY `ref_2` (`ref`),
  KEY `ref_3` (`ref`),
  KEY `ref_4` (`ref`),
  KEY `ref_5` (`ref`),
  KEY `ref_6` (`ref`),
  KEY `ref_7` (`ref`),
  KEY `ref_8` (`ref`),
  KEY `ref_9` (`ref`),
  KEY `ref_10` (`ref`)
  revised-081-end by gsavix08-jul-2014 */
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;


CREATE TABLE IF NOT EXISTS `gltrans` (
  `counterindex` int(11) NOT NULL AUTO_INCREMENT,
  `type` smallint(6) NOT NULL DEFAULT '0',
  `typeno` bigint(16) NOT NULL DEFAULT '1',
  `chequeno` int(11) NOT NULL DEFAULT '0',
  `trandate` date NOT NULL DEFAULT '0000-00-00',
  `periodno` smallint(6) NOT NULL DEFAULT '0',
  `account` varchar(20) NOT NULL DEFAULT '0',
  `narrative` varchar(200) NOT NULL DEFAULT '',
  `amount` double NOT NULL DEFAULT '0',
  `posted` tinyint(4) NOT NULL DEFAULT '0',
  `jobref` varchar(20) NOT NULL DEFAULT '',
  `tag` tinyint(4) NOT NULL DEFAULT '0',
  PRIMARY KEY (`counterindex`),
  KEY `Account` (`account`),
  KEY `ChequeNo` (`chequeno`),
  KEY `PeriodNo` (`periodno`),
  KEY `Posted` (`posted`),
  KEY `TranDate` (`trandate`),
  KEY `TypeNo` (`typeno`),
  KEY `Type_and_Number` (`type`,`typeno`),
  KEY `JobRef` (`jobref`),
  KEY `tag` (`tag`)
  /* revised-082-begin by gsavix08-jul-2014
  KEY `tag_2` (`tag`),
  KEY `tag_3` (`tag`),
  KEY `tag_4` (`tag`)
  revised-082-end by gsavix08-jul-2014 */
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;



CREATE TABLE IF NOT EXISTS `mrpdemandtypes` (
  `mrpdemandtype` varchar(6) NOT NULL DEFAULT '',
  `description` char(30) NOT NULL DEFAULT '',
  PRIMARY KEY (`mrpdemandtype`)
  /* revised-083-begin by gsavix08-jul-2014
  KEY `mrpdemandtype` (`mrpdemandtype`)
  revised-083-end by gsavix08-jul-2014 */
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `salescatprod` (
  `salescatid` tinyint(4) NOT NULL DEFAULT '0',
  `stockid` varchar(20) NOT NULL DEFAULT '',
  `manufacturers_id` int(11) NOT NULL,
  `featured` tinyint(4) NOT NULL DEFAULT '0',
  PRIMARY KEY (`salescatid`,`stockid`),
  KEY `salescatid` (`salescatid`),
  KEY `stockid` (`stockid`),
  KEY `manufacturer_id` (`manufacturers_id`)
  /* revised-084-begin by gsavix08-jul-2014
  KEY `manufacturers_id` (`manufacturers_id`)
  KEY `manufacturers_id_2` (`manufacturers_id`)
  revised-084-end by gsavix08-jul-2014 */
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE IF NOT EXISTS `stockrequest` (
  `dispatchid` int(11) NOT NULL AUTO_INCREMENT,
  `loccode` varchar(5) NOT NULL DEFAULT '',
  `departmentid` int(11) NOT NULL DEFAULT '0',
  `despatchdate` date NOT NULL DEFAULT '0000-00-00',
  `authorised` tinyint(4) NOT NULL DEFAULT '0',
  `closed` tinyint(4) NOT NULL DEFAULT '0',
  `narrative` text NOT NULL,
  PRIMARY KEY (`dispatchid`),
  KEY `loccode` (`loccode`),
  KEY `departmentid` (`departmentid`)
  /* revised-085-begin by gsavix08-jul-2014
  KEY `loccode_2` (`loccode`),
  KEY `departmentid_2` (`departmentid`)
  revised-085-end by gsavix08-jul-2014 */
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;


CREATE TABLE IF NOT EXISTS `stockrequestitems` (
  `dispatchitemsid` int(11) NOT NULL DEFAULT '0',
  `dispatchid` int(11) NOT NULL DEFAULT '0',
  `stockid` varchar(20) NOT NULL DEFAULT '',
  `quantity` double NOT NULL DEFAULT '0',
  `qtydelivered` double NOT NULL DEFAULT '0',
  `decimalplaces` int(11) NOT NULL DEFAULT '0',
  `uom` varchar(20) NOT NULL DEFAULT '',
  `completed` tinyint(4) NOT NULL DEFAULT '0',
  PRIMARY KEY (`dispatchitemsid`,`dispatchid`),
  KEY `dispatchid` (`dispatchid`),
  KEY `stockid` (`stockid`)
  /* revised-086-begin by gsavix08-jul-2014
  KEY `dispatchid_2` (`dispatchid`),
  KEY `stockid_2` (`stockid`)
  revised-086-end by gsavix08-jul-2014 */
) ENGINE=InnoDB DEFAULT CHARSET=utf8;





-- 
gilberto dos santos alves
+55.11.98646-5049
sao paulo - sp - brasil