upgrade2.9b-3.0.sql

来自「java开源项目源代码」· SQL 代码 · 共 904 行 · 第 1/5 页

ALTER TABLE `stockmoves` CHANGE TransNo transno int(11)  NOT NULL default '0';
ALTER TABLE `stockmoves` CHANGE LocCode loccode varchar(5)  NOT NULL default '';
ALTER TABLE `stockmoves` CHANGE TranDate trandate date  NOT NULL default '0000-00-00';
ALTER TABLE `stockmoves` CHANGE DebtorNo debtorno varchar(10)  NOT NULL default '';
ALTER TABLE `stockmoves` CHANGE BranchCode branchcode varchar(10)  NOT NULL default '';
ALTER TABLE `stockmoves` CHANGE Price price decimal(20,4)  NOT NULL default '0.0000';
ALTER TABLE `stockmoves` CHANGE Prd prd smallint(6)  NOT NULL default '0';
ALTER TABLE `stockmoves` CHANGE Reference reference varchar(40)  NOT NULL default '';
ALTER TABLE `stockmoves` CHANGE Qty qty double(16,4)  NOT NULL default '1.0000';
ALTER TABLE `stockmoves` CHANGE DiscountPercent discountpercent double(16,4)  NOT NULL default '0.0000';
ALTER TABLE `stockmoves` CHANGE StandardCost standardcost double(16,4)  NOT NULL default '0.0000';
ALTER TABLE `stockmoves` CHANGE Show_On_Inv_Crds show_on_inv_crds tinyint(4)  NOT NULL default '1';
ALTER TABLE `stockmoves` CHANGE NewQOH newqoh double  NOT NULL default '0';
ALTER TABLE `stockmoves` CHANGE HideMovt hidemovt tinyint(4)  NOT NULL default '0';
ALTER TABLE `stockmoves` CHANGE TaxRate taxrate float  NOT NULL default '0';
ALTER TABLE `stockmoves` CHANGE Narrative narrative text  NOT NULL;
ALTER TABLE `stockserialitems` CHANGE StockID stockid varchar(20)  NOT NULL default '';
ALTER TABLE `stockserialitems` CHANGE LocCode loccode varchar(5)  NOT NULL default '';
ALTER TABLE `stockserialitems` CHANGE SerialNo serialno varchar(30)  NOT NULL default '';
ALTER TABLE `stockserialitems` CHANGE Quantity quantity float  NOT NULL default '0';
ALTER TABLE `stockserialmoves` CHANGE StkItmMoveNo stkitmmoveno int(11)  NOT NULL auto_increment;
ALTER TABLE `stockserialmoves` CHANGE StockMoveNo stockmoveno int(11)  NOT NULL default '0';
ALTER TABLE `stockserialmoves` CHANGE StockID stockid varchar(20)  NOT NULL default '';
ALTER TABLE `stockserialmoves` CHANGE SerialNo serialno varchar(30)  NOT NULL default '';
ALTER TABLE `stockserialmoves` CHANGE MoveQty moveqty float  NOT NULL default '0';
ALTER TABLE `suppallocs` CHANGE ID id int(11)  NOT NULL auto_increment;
ALTER TABLE `suppallocs` CHANGE Amt amt float(20,2)  NOT NULL default '0.00';
ALTER TABLE `suppallocs` CHANGE DateAlloc datealloc date  NOT NULL default '0000-00-00';
ALTER TABLE `suppallocs` CHANGE TransID_AllocFrom transid_allocfrom int(11)  NOT NULL default '0';
ALTER TABLE `suppallocs` CHANGE TransID_AllocTo transid_allocto int(11)  NOT NULL default '0';
ALTER TABLE `supptrans` CHANGE TransNo transno int(11)  NOT NULL default '0';
ALTER TABLE `supptrans` CHANGE Type type smallint(6)  NOT NULL default '0';
ALTER TABLE `supptrans` CHANGE SupplierNo supplierno varchar(10)  NOT NULL default '';
ALTER TABLE `supptrans` CHANGE SuppReference suppreference varchar(20)  NOT NULL default '';
ALTER TABLE `supptrans` CHANGE TranDate trandate date  NOT NULL default '0000-00-00';
ALTER TABLE `supptrans` CHANGE DueDate duedate date  NOT NULL default '0000-00-00';
ALTER TABLE `supptrans` CHANGE Settled settled tinyint(4)  NOT NULL default '0';
ALTER TABLE `supptrans` CHANGE Rate rate double(16,6)  NOT NULL default '1.000000';
ALTER TABLE `supptrans` CHANGE OvAmount ovamount double(16,4)  NOT NULL default '0.0000';
ALTER TABLE `supptrans` CHANGE OvGST ovgst double(16,4)  NOT NULL default '0.0000';
ALTER TABLE `supptrans` CHANGE DiffOnExch diffonexch double(16,4)  NOT NULL default '0.0000';
ALTER TABLE `supptrans` CHANGE Alloc alloc double(16,4)  NOT NULL default '0.0000';
ALTER TABLE `supptrans` CHANGE TransText transtext longblob;
ALTER TABLE `supptrans` CHANGE Hold hold tinyint(4)  NOT NULL default '0';
ALTER TABLE `supptrans` CHANGE ID id int(11)  NOT NULL auto_increment;
ALTER TABLE `suppliercontacts` CHANGE SupplierID supplierid varchar(10)  NOT NULL default '';
ALTER TABLE `suppliercontacts` CHANGE Contact contact varchar(30)  NOT NULL default '';
ALTER TABLE `suppliercontacts` CHANGE Position position varchar(30)  NOT NULL default '';
ALTER TABLE `suppliercontacts` CHANGE Tel tel varchar(30)  NOT NULL default '';
ALTER TABLE `suppliercontacts` CHANGE Fax fax varchar(30)  NOT NULL default '';
ALTER TABLE `suppliercontacts` CHANGE Mobile mobile varchar(30)  NOT NULL default '';
ALTER TABLE `suppliercontacts` CHANGE Email email varchar(55)  NOT NULL default '';
ALTER TABLE `suppliercontacts` CHANGE OrderContact ordercontact tinyint(1)  NOT NULL default '0';
ALTER TABLE `suppliers` CHANGE SupplierID supplierid char(10)  NOT NULL default '';
ALTER TABLE `suppliers` CHANGE SuppName suppname char(40)  NOT NULL default '';
ALTER TABLE `suppliers` CHANGE Address1 address1 char(40)  NOT NULL default '';
ALTER TABLE `suppliers` CHANGE Address2 address2 char(40)  NOT NULL default '';
ALTER TABLE `suppliers` CHANGE Address3 address3 char(40)  NOT NULL default '';
ALTER TABLE `suppliers` CHANGE Address4 address4 char(50)  NOT NULL default '';
ALTER TABLE `suppliers` CHANGE CurrCode currcode char(3)  NOT NULL default '';
ALTER TABLE `suppliers` CHANGE SupplierSince suppliersince date  NOT NULL default '0000-00-00';
ALTER TABLE `suppliers` CHANGE PaymentTerms paymentterms char(2)  NOT NULL default '';
ALTER TABLE `suppliers` CHANGE LastPaid lastpaid double(16,4)  NOT NULL default '0.0000';
ALTER TABLE `suppliers` CHANGE LastPaidDate lastpaiddate datetime  default NULL;
ALTER TABLE `suppliers` CHANGE BankAct bankact char(16)  NOT NULL default '';
ALTER TABLE `suppliers` CHANGE BankRef bankref char(12)  NOT NULL default '';
ALTER TABLE `suppliers` CHANGE BankPartics bankpartics char(12)  NOT NULL default '';
ALTER TABLE `suppliers` CHANGE Remittance remittance tinyint(4)  NOT NULL default '1';
ALTER TABLE `suppliers` CHANGE TaxAuthority taxauthority tinyint(4)  NOT NULL default '1';
ALTER TABLE `systypes` CHANGE TypeID typeid smallint(6)  NOT NULL default '0';
ALTER TABLE `systypes` CHANGE TypeName typename char(50)  NOT NULL default '';
ALTER TABLE `systypes` CHANGE TypeNo typeno int(11)  NOT NULL default '1';
ALTER TABLE `taxauthlevels` CHANGE TaxAuthority taxauthority tinyint(4)  NOT NULL default '1';
ALTER TABLE `taxauthlevels` CHANGE DispatchTaxAuthority dispatchtaxauthority tinyint(4)  NOT NULL default '1';
ALTER TABLE `taxauthlevels` CHANGE Level level tinyint(4)  NOT NULL default '0';
ALTER TABLE `taxauthlevels` CHANGE TaxRate taxrate double  NOT NULL default '0';
ALTER TABLE `taxauthorities` CHANGE TaxID taxid tinyint(4)  NOT NULL default '0';
ALTER TABLE `taxauthorities` CHANGE Description description char(20)  NOT NULL default '';
ALTER TABLE `taxauthorities` CHANGE TaxGLCode taxglcode int(11)  NOT NULL default '0';
ALTER TABLE `taxauthorities` CHANGE PurchTaxGLAccount purchtaxglaccount int(11)  NOT NULL default '0';
ALTER TABLE `www_users` CHANGE UserID userid varchar(20)  NOT NULL default '';
ALTER TABLE `www_users` CHANGE Password password varchar(20)  NOT NULL default '';
ALTER TABLE `www_users` CHANGE RealName realname varchar(35)  NOT NULL default '';
ALTER TABLE `www_users` CHANGE CustomerID customerid varchar(10)  NOT NULL default '';
ALTER TABLE `www_users` CHANGE Phone phone varchar(30)  NOT NULL default '';
ALTER TABLE `www_users` CHANGE Email email varchar(55)  default NULL;
ALTER TABLE `www_users` CHANGE DefaultLocation defaultlocation varchar(5)  NOT NULL default '';
ALTER TABLE `www_users` CHANGE FullAccess fullaccess int(11)  NOT NULL default '1';
ALTER TABLE `www_users` CHANGE LastVisitDate lastvisitdate datetime  default NULL;
ALTER TABLE `www_users` CHANGE BranchCode branchcode varchar(10)  NOT NULL default '';
ALTER TABLE `www_users` CHANGE PageSize pagesize varchar(20)  NOT NULL default 'A4';
ALTER TABLE `www_users` CHANGE ModulesAllowed modulesallowed varchar(20)  NOT NULL default '';
ALTER TABLE `www_users` CHANGE Blocked blocked tinyint(4)  NOT NULL default '0';
ALTER TABLE `www_users` CHANGE DisplayRecordsMax displayrecordsmax int(11)  NOT NULL default '0';
ALTER TABLE `www_users` CHANGE Theme theme varchar(30)  NOT NULL default 'fresh';
ALTER TABLE `www_users` CHANGE Language language char(2)  NOT NULL default 'en';
ALTER TABLE `workcentres` CHANGE Code code char(5)  NOT NULL default '';
ALTER TABLE `workcentres` CHANGE Location location char(5)  NOT NULL default '';
ALTER TABLE `workcentres` CHANGE Description description char(20)  NOT NULL default '';
ALTER TABLE `workcentres` CHANGE Capacity capacity double(16,4)  NOT NULL default '1.0000';
ALTER TABLE `workcentres` CHANGE OverheadPerHour overheadperhour decimal(20,4)  NOT NULL default '0.0000';
ALTER TABLE `workcentres` CHANGE OverheadRecoveryAct overheadrecoveryact int(11)  NOT NULL default '0';
ALTER TABLE `workcentres` CHANGE SetUpHrs setuphrs decimal(20,4)  NOT NULL default '0.0000';
ALTER TABLE `worksorders` CHANGE WORef woref char(20)  NOT NULL default '';
ALTER TABLE `worksorders` CHANGE LocCode loccode char(5)  NOT NULL default '';
ALTER TABLE `worksorders` CHANGE UnitsReqd unitsreqd smallint(6)  NOT NULL default '1';
ALTER TABLE `worksorders` CHANGE UnitsRecd unitsrecd double  NOT NULL default '0';
ALTER TABLE `worksorders` CHANGE StockID stockid char(20)  NOT NULL default '';
ALTER TABLE `worksorders` CHANGE StdCost stdcost decimal(20,4)  NOT NULL default '0.0000';
ALTER TABLE `worksorders` CHANGE RequiredBy requiredby date  NOT NULL default '0000-00-00';
ALTER TABLE `worksorders` CHANGE ReleasedDate releaseddate date  NOT NULL default '1800-01-01';
ALTER TABLE `worksorders` CHANGE AccumValueIssued accumvalueissued decimal(20,4)  NOT NULL default '0.0000';
ALTER TABLE `worksorders` CHANGE AccumValueTrfd accumvaluetrfd decimal(20,4)  NOT NULL default '0.0000';
ALTER TABLE `worksorders` CHANGE Closed closed tinyint(4)  NOT NULL default '0';
ALTER TABLE `worksorders` CHANGE Released released tinyint(4)  NOT NULL default '0';
ALTER TABLE `taxauthorities` CHANGE taxid taxid tinyint NOT NULL auto_increment;
ALTER TABLE `salesorders` ADD `quotation` TINYINT DEFAULT '0' NOT NULL ;
ALTER TABLE `salesorders` ADD INDEX ( `quotation` ) ;
CREATE TABLE `recurringsalesorders` (
  `recurrorderno` int(11) NOT NULL auto_increment,
  `debtorno` varchar(10) NOT NULL default '',
  `branchcode` varchar(10) NOT NULL default '',
  `customerref` varchar(50) NOT NULL default '',
  `buyername` varchar(50) default NULL,
  `comments` longblob,
  `orddate` date NOT NULL default '0000-00-00',
  `ordertype` char(2) NOT NULL default '',
  `shipvia` int(11) NOT NULL default '0',
  `deladd1` varchar(40) NOT NULL default '',
  `deladd2` varchar(20) NOT NULL default '',
  `deladd3` varchar(15) NOT NULL default '',
  `deladd4` varchar(15) default NULL,
  `contactphone` varchar(25) default NULL,
  `contactemail` varchar(25) default NULL,
  `deliverto` varchar(40) NOT NULL default '',
  `freightcost` float(10,2) NOT NULL default '0.00',
  `fromstkloc` varchar(5) NOT NULL default '',
  `lastrecurrence` date NOT NULL default '0000-00-00',
  `stopdate` date NOT NULL default '0000-00-00',
  `frequency` tinyint(4) NOT NULL default '1',
  `autoinvoice` tinyint(4) NOT NULL default '0',
  PRIMARY KEY  (`recurrorderno`),
  KEY `debtorno` (`debtorno`),
  KEY `orddate` (`orddate`),
  KEY `ordertype` (`ordertype`),
  KEY `locationindex` (`fromstkloc`),
  KEY `branchcode` (`branchcode`,`debtorno`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

ALTER TABLE `recurringsalesorders`
  ADD CONSTRAINT `recurringsalesorders_ibfk_1` FOREIGN KEY (`branchcode`, `debtorno`) REFERENCES `custbranch` (`branchcode`, `debtorno`);
  
  CREATE TABLE `recurrsalesorderdetails` (
  `recurrorderno` int(11) NOT NULL default '0',
  `stkcode` varchar(20) NOT NULL default '',
  `unitprice` double(16,4) NOT NULL default '0.0000',
  `quantity` double(16,4) NOT NULL default '0.0000',
  `discountpercent` double(16,4) NOT NULL default '0.0000',
  `narrative` text NOT NULL,
  PRIMARY KEY  (`recurrorderno`,`stkcode`),
  KEY `orderno` (`recurrorderno`),
  KEY `stkcode` (`stkcode`),
  CONSTRAINT `recurrsalesorderdetails_ibfk_2` FOREIGN KEY (`stkcode`) REFERENCES `stockmaster` (`stockid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

ALTER TABLE `recurrsalesorderdetails`
  ADD CONSTRAINT `recurrsalesorderdetails_ibfk_1` FOREIGN KEY (`recurrorderno`) REFERENCES `recurringsalesorders` (`recurrorderno`);
    
 ALTER TABLE `scripts` CHANGE `pageid` `pageid` SMALLINT( 4 ) NOT NULL AUTO_INCREMENT ;
 
CREATE TABLE accountsection (
    sectionid integer NOT NULL,
    sectionname text NOT NULL,
    PRIMARY KEY (sectionid)
)ENGINE=InnoDB;

INSERT INTO accountsection (sectionid, sectionname) VALUES (1,'Income');
INSERT INTO accountsection (sectionid, sectionname) VALUES (2,'Cost Of Sales');
INSERT INTO accountsection (sectionid, sectionname) VALUES (5,'Overheads');
INSERT INTO accountsection (sectionid, sectionname) VALUES (10, 'Fixed Assets');
INSERT INTO accountsection (sectionid, sectionname) VALUES (20, 'Amounts Receivable');