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

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

please have you received this email? please confirm. thanks.


2014-07-08 13:32 GMT-03:00 gilberto dos santos alves <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
>
>
>
>
>


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