PHP CreateTable函数代码示例

<?php

CreateTable('reports', "CREATE TABLE `reports` (\n  `id` int(5) NOT NULL AUTO_INCREMENT,\n  `reportname` varchar(30) NOT NULL DEFAULT '',\n  `reporttype` char(3) NOT NULL DEFAULT 'rpt',\n  `groupname` varchar(9) NOT NULL DEFAULT 'misc',\n  `defaultreport` enum('1','0') NOT NULL DEFAULT '0',\n  `papersize` varchar(15) NOT NULL DEFAULT 'A4,210,297',\n  `paperorientation` enum('P','L') NOT NULL DEFAULT 'P',\n  `margintop` int(3) NOT NULL DEFAULT '10',\n  `marginbottom` int(3) NOT NULL DEFAULT '10',\n  `marginleft` int(3) NOT NULL DEFAULT '10',\n  `marginright` int(3) NOT NULL DEFAULT '10',\n  `coynamefont` varchar(20) NOT NULL DEFAULT 'Helvetica',\n  `coynamefontsize` int(3) NOT NULL DEFAULT '12',\n  `coynamefontcolor` varchar(11) NOT NULL DEFAULT '0,0,0',\n  `coynamealign` enum('L','C','R') NOT NULL DEFAULT 'C',\n  `coynameshow` enum('1','0') NOT NULL DEFAULT '1',\n  `title1desc` varchar(50) NOT NULL DEFAULT '%reportname%',\n  `title1font` varchar(20) NOT NULL DEFAULT 'Helvetica',\n  `title1fontsize` int(3) NOT NULL DEFAULT '10',\n  `title1fontcolor` varchar(11) NOT NULL DEFAULT '0,0,0',\n  `title1fontalign` enum('L','C','R') NOT NULL DEFAULT 'C',\n  `title1show` enum('1','0') NOT NULL DEFAULT '1',\n  `title2desc` varchar(50) NOT NULL DEFAULT 'Report Generated %date%',\n  `title2font` varchar(20) NOT NULL DEFAULT 'Helvetica',\n  `title2fontsize` int(3) NOT NULL DEFAULT '10',\n  `title2fontcolor` varchar(11) NOT NULL DEFAULT '0,0,0',\n  `title2fontalign` enum('L','C','R') NOT NULL DEFAULT 'C',\n  `title2show` enum('1','0') NOT NULL DEFAULT '1',\n  `filterfont` varchar(10) NOT NULL DEFAULT 'Helvetica',\n  `filterfontsize` int(3) NOT NULL DEFAULT '8',\n  `filterfontcolor` varchar(11) NOT NULL DEFAULT '0,0,0',\n  `filterfontalign` enum('L','C','R') NOT NULL DEFAULT 'L',\n  `datafont` varchar(10) NOT NULL DEFAULT 'Helvetica',\n  `datafontsize` int(3) NOT NULL DEFAULT '10',\n  `datafontcolor` varchar(10) NOT NULL DEFAULT 'black',\n  `datafontalign` enum('L','C','R') NOT NULL DEFAULT 'L',\n  `totalsfont` varchar(10) NOT NULL DEFAULT 'Helvetica',\n  `totalsfontsize` int(3) NOT NULL DEFAULT '10',\n  `totalsfontcolor` varchar(11) NOT NULL DEFAULT '0,0,0',\n  `totalsfontalign` enum('L','C','R') NOT NULL DEFAULT 'L',\n  `col1width` int(3) NOT NULL DEFAULT '25',\n  `col2width` int(3) NOT NULL DEFAULT '25',\n  `col3width` int(3) NOT NULL DEFAULT '25',\n  `col4width` int(3) NOT NULL DEFAULT '25',\n  `col5width` int(3) NOT NULL DEFAULT '25',\n  `col6width` int(3) NOT NULL DEFAULT '25',\n  `col7width` int(3) NOT NULL DEFAULT '25',\n  `col8width` int(3) NOT NULL DEFAULT '25',\n  `col9width` int(3) NOT NULL DEFAULT '25',\n  `col10width` int(3) NOT NULL DEFAULT '25',\n  `col11width` int(3) NOT NULL DEFAULT '25',\n  `col12width` int(3) NOT NULL DEFAULT '25',\n  `col13width` int(3) NOT NULL DEFAULT '25',\n  `col14width` int(3) NOT NULL DEFAULT '25',\n  `col15width` int(3) NOT NULL DEFAULT '25',\n  `col16width` int(3) NOT NULL DEFAULT '25',\n  `col17width` int(3) NOT NULL DEFAULT '25',\n  `col18width` int(3) NOT NULL DEFAULT '25',\n  `col19width` int(3) NOT NULL DEFAULT '25',\n  `col20width` int(3) NOT NULL DEFAULT '25',\n  `table1` varchar(25) NOT NULL DEFAULT '',\n  `table2` varchar(25) DEFAULT NULL,\n  `table2criteria` varchar(75) DEFAULT NULL,\n  `table3` varchar(25) DEFAULT NULL,\n  `table3criteria` varchar(75) DEFAULT NULL,\n  `table4` varchar(25) DEFAULT NULL,\n  `table4criteria` varchar(75) DEFAULT NULL,\n  `table5` varchar(25) DEFAULT NULL,\n  `table5criteria` varchar(75) DEFAULT NULL,\n  `table6` varchar(25) DEFAULT NULL,\n  `table6criteria` varchar(75) DEFAULT NULL,\n  PRIMARY KEY (`id`),\n  KEY `name` (`reportname`,`groupname`)\n)", $db);

<?php

CreateTable('taxprovinces', "CREATE TABLE `taxprovinces` (\n  `taxprovinceid` tinyint(4) NOT NULL AUTO_INCREMENT,\n  `taxprovincename` varchar(30) NOT NULL DEFAULT '',\n  PRIMARY KEY (`taxprovinceid`)\n)", $db);

<?php

CreateTable('tenderitems', "CREATE TABLE `tenderitems` (\n  `tenderid` int(11) NOT NULL DEFAULT '0',\n  `stockid` varchar(20) NOT NULL DEFAULT '',\n  `quantity` varchar(40) NOT NULL DEFAULT '',\n  `units` varchar(20) NOT NULL DEFAULT 'each',\n  PRIMARY KEY (`tenderid`,`stockid`)\n)", $db);

<?php

CreateTable('recurringsalesorders', "CREATE TABLE `recurringsalesorders` (\n  `recurrorderno` int(11) NOT NULL AUTO_INCREMENT,\n  `debtorno` varchar(10) NOT NULL DEFAULT '',\n  `branchcode` varchar(10) NOT NULL DEFAULT '',\n  `customerref` varchar(50) NOT NULL DEFAULT '',\n  `buyername` varchar(50) DEFAULT NULL,\n  `comments` longblob,\n  `orddate` date NOT NULL DEFAULT '0000-00-00',\n  `ordertype` char(2) NOT NULL DEFAULT '',\n  `shipvia` int(11) NOT NULL DEFAULT '0',\n  `deladd1` varchar(40) NOT NULL DEFAULT '',\n  `deladd2` varchar(40) NOT NULL,\n  `deladd3` varchar(40) NOT NULL,\n  `deladd4` varchar(40) NOT NULL,\n  `deladd5` varchar(20) NOT NULL DEFAULT '',\n  `deladd6` varchar(15) NOT NULL DEFAULT '',\n  `contactphone` varchar(25) DEFAULT NULL,\n  `contactemail` varchar(25) DEFAULT NULL,\n  `deliverto` varchar(40) NOT NULL DEFAULT '',\n  `freightcost` double NOT NULL DEFAULT '0',\n  `fromstkloc` varchar(5) NOT NULL DEFAULT '',\n  `lastrecurrence` date NOT NULL DEFAULT '0000-00-00',\n  `stopdate` date NOT NULL DEFAULT '0000-00-00',\n  `frequency` tinyint(4) NOT NULL DEFAULT '1',\n  `autoinvoice` tinyint(4) NOT NULL DEFAULT '0',\n  PRIMARY KEY (`recurrorderno`),\n  KEY `debtorno` (`debtorno`),\n  KEY `orddate` (`orddate`),\n  KEY `ordertype` (`ordertype`),\n  KEY `locationindex` (`fromstkloc`),\n  KEY `branchcode` (`branchcode`,`debtorno`),\n  CONSTRAINT `recurringsalesorders_ibfk_1` FOREIGN KEY (`branchcode`, `debtorno`) REFERENCES `custbranch` (`branchcode`, `debtorno`)\n)", $db);

<?php

CreateTable('modules', "CREATE TABLE `modules` (\n  `secroleid` int(11) NOT NULL DEFAULT '15',\n  `modulelink` varchar(10) NOT NULL DEFAULT '',\n  `reportlink` varchar(4) NOT NULL DEFAULT '',\n  `modulename` varchar(25) NOT NULL DEFAULT '',\n  `sequence` int(11) NOT NULL DEFAULT '1',\n  PRIMARY KEY (`secroleid`,`modulelink`),\n  CONSTRAINT `modules_ibfk_1` FOREIGN KEY (`secroleid`) REFERENCES `securityroles` (`secroleid`)\n)", $db);

<?php

CreateTable('lastcostrollup', "CREATE TABLE `lastcostrollup` (\n  `stockid` char(20) NOT NULL DEFAULT '',\n  `totalonhand` double NOT NULL DEFAULT '0',\n  `matcost` decimal(20,4) NOT NULL DEFAULT '0.0000',\n  `labcost` decimal(20,4) NOT NULL DEFAULT '0.0000',\n  `oheadcost` decimal(20,4) NOT NULL DEFAULT '0.0000',\n  `categoryid` char(6) NOT NULL DEFAULT '',\n  `stockact` varchar(20) NOT NULL DEFAULT '0',\n  `adjglact` varchar(20) NOT NULL DEFAULT '0',\n  `newmatcost` decimal(20,4) NOT NULL DEFAULT '0.0000',\n  `newlabcost` decimal(20,4) NOT NULL DEFAULT '0.0000',\n  `newoheadcost` decimal(20,4) NOT NULL DEFAULT '0.0000'\n)", $db);

<?php

CreateTable('bankaccountusers', "CREATE TABLE `bankaccountusers` (\n  `accountcode` varchar(20) NOT NULL DEFAULT '',\n  `userid` varchar(20) NOT NULL DEFAULT '',\n  PRIMARY KEY (`accountcode`,`userid`)\n)", $db);

<?php

CreateTable('accountsection', "CREATE TABLE `accountsection` (\n  `sectionid` int(11) NOT NULL DEFAULT '0',\n  `sectionname` text NOT NULL,\n  PRIMARY KEY (`sectionid`)\n)", $db);

<?php

CreateTable('woitems', "CREATE TABLE `woitems` (\n  `wo` int(11) NOT NULL,\n  `stockid` char(20) NOT NULL DEFAULT '',\n  `qtyreqd` double NOT NULL DEFAULT '1',\n  `qtyrecd` double NOT NULL DEFAULT '0',\n  `stdcost` double NOT NULL,\n  `nextlotsnref` varchar(20) DEFAULT '',\n  PRIMARY KEY (`wo`,`stockid`),\n  KEY `stockid` (`stockid`),\n  CONSTRAINT `woitems_ibfk_1` FOREIGN KEY (`stockid`) REFERENCES `stockmaster` (`stockid`),\n  CONSTRAINT `woitems_ibfk_2` FOREIGN KEY (`wo`) REFERENCES `workorders` (`wo`)\n)", $db);

<?php

CreateTable('edi_orders_segs', "CREATE TABLE `edi_orders_segs` (\n  `id` int(11) NOT NULL AUTO_INCREMENT,\n  `segtag` char(3) NOT NULL DEFAULT '',\n  `seggroup` tinyint(4) NOT NULL DEFAULT '0',\n  `maxoccur` tinyint(4) NOT NULL DEFAULT '0',\n  PRIMARY KEY (`id`),\n  KEY `SegTag` (`segtag`),\n  KEY `SegNo` (`seggroup`)\n)", $db);

<?php

CreateTable('mailgroups', "CREATE TABLE `mailgroups` (\n\tid int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,\n\tgroupname varchar(100) NOT NULL,\n\tunique (groupname))", $db);
CreateTable('mailgroupdetails', "CREATE TABLE `mailgroupdetails` (\n\tgroupname varchar(100) NOT NULL,\n\tuserid varchar(20) NOT NULL,\n\tCONSTRAINT FOREIGN KEY (`groupname`) REFERENCES `mailgroups` (`groupname`),\n\tCONSTRAINT FOREIGN KEY (`userid`) REFERENCES `www_users`(`userid`),\n\tINDEX(`groupname`))", $db);
NewScript('MailingGroupMaintenance.php', '15', $db);
UpdateDBNo(basename(__FILE__, '.php'), $db);

<?php

/* New database stuff to move the page security levels to a
 * database table.
 */
CreateTable('pagesecurity', "CREATE TABLE `pagesecurity` (\n\t`script` varchar(78) NOT NULL DEFAULT '',\n\t`security` int(11) NOT NULL DEFAULT 1,\n\tPRIMARY KEY  (`script`)\n) ENGINE=InnoDB DEFAULT CHARSET=utf8", $db);
InsertRecord('pagesecurity', array('script', 'security'), array('AccountGroups.php', 10), array('script', 'security'), array('AccountGroups.php', 10), $db);
InsertRecord('pagesecurity', array('script', 'security'), array('AccountSections.php', 10), array('script', 'security'), array('AccountSections.php', 10), $db);
InsertRecord('pagesecurity', array('script', 'security'), array('AddCustomerContacts.php', 3), array('script', 'security'), array('AddCustomerContacts.php', 3), $db);
InsertRecord('pagesecurity', array('script', 'security'), array('AddCustomerNotes.php', 3), array('script', 'security'), array('AddCustomerNotes.php', 3), $db);
InsertRecord('pagesecurity', array('script', 'security'), array('AddCustomerTypeNotes.php', 3), array('script', 'security'), array('AddCustomerTypeNotes.php', 3), $db);
InsertRecord('pagesecurity', array('script', 'security'), array('AgedDebtors.php', 2), array('script', 'security'), array('AgedDebtors.php', 2), $db);
InsertRecord('pagesecurity', array('script', 'security'), array('AgedSuppliers.php', 2), array('script', 'security'), array('AgedSuppliers.php', 2), $db);
InsertRecord('pagesecurity', array('script', 'security'), array('Areas.php', 3), array('script', 'security'), array('Areas.php', 3), $db);
InsertRecord('pagesecurity', array('script', 'security'), array('AuditTrail.php', 15), array('script', 'security'), array('AuditTrail.php', 15), $db);
InsertRecord('pagesecurity', array('script', 'security'), array('BankAccounts.php', 10), array('script', 'security'), array('BankAccounts.php', 10), $db);
InsertRecord('pagesecurity', array('script', 'security'), array('BankMatching.php', 7), array('script', 'security'), array('BankMatching.php', 7), $db);
InsertRecord('pagesecurity', array('script', 'security'), array('BankReconciliation.php', 7), array('script', 'security'), array('BankReconciliation.php', 7), $db);
InsertRecord('pagesecurity', array('script', 'security'), array('BOMExtendedQty.php', 2), array('script', 'security'), array('BOMExtendedQty.php', 2), $db);
InsertRecord('pagesecurity', array('script', 'security'), array('BOMIndented.php', 2), array('script', 'security'), array('BOMIndented.php', 2), $db);
InsertRecord('pagesecurity', array('script', 'security'), array('BOMIndentedReverse.php', 2), array('script', 'security'), array('BOMIndentedReverse.php', 2), $db);
InsertRecord('pagesecurity', array('script', 'security'), array('BOMInquiry.php', 2), array('script', 'security'), array('BOMInquiry.php', 2), $db);
InsertRecord('pagesecurity', array('script', 'security'), array('BOMListing.php', 2), array('script', 'security'), array('BOMListing.php', 2), $db);
InsertRecord('pagesecurity', array('script', 'security'), array('BOMs.php', 9), array('script', 'security'), array('BOMs.php', 9), $db);
InsertRecord('pagesecurity', array('script', 'security'), array('COGSGLPostings.php', 10), array('script', 'security'), array('COGSGLPostings.php', 10), $db);
InsertRecord('pagesecurity', array('script', 'security'), array('CompanyPreferences.php', 10), array('script', 'security'), array('CompanyPreferences.php', 10), $db);
InsertRecord('pagesecurity', array('script', 'security'), array('ConfirmDispatchControlled_Invoice.php', 11), array('script', 'security'), array('ConfirmDispatchControlled_Invoice.php', 11), $db);
InsertRecord('pagesecurity', array('script', 'security'), array('ConfirmDispatch_Invoice.php', 2), array('script', 'security'), array('ConfirmDispatch_Invoice.php', 2), $db);
InsertRecord('pagesecurity', array('script', 'security'), array('ContractBOM.php', 6), array('script', 'security'), array('ContractBOM.php', 6), $db);
InsertRecord('pagesecurity', array('script', 'security'), array('ContractCosting.php', 6), array('script', 'security'), array('ContractCosting.php', 6), $db);
InsertRecord('pagesecurity', array('script', 'security'), array('ContractOtherReqts.php', 4), array('script', 'security'), array('ContractOtherReqts.php', 4), $db);

<?php

CreateTable('currencies', "CREATE TABLE `currencies` (\n  `currency` char(20) NOT NULL DEFAULT '',\n  `currabrev` char(3) NOT NULL DEFAULT '',\n  `country` char(50) NOT NULL DEFAULT '',\n  `hundredsname` char(15) NOT NULL DEFAULT 'Cents',\n  `decimalplaces` tinyint(3) NOT NULL DEFAULT '2',\n  `rate` double NOT NULL DEFAULT '1',\n  `webcart` tinyint(1) NOT NULL DEFAULT '1',\n  PRIMARY KEY (`currabrev`),\n  KEY `Country` (`country`)\n)", $db);

<?php

CreateTable('salesman', "CREATE TABLE `salesman` (\n  `salesmancode` varchar(4) NOT NULL,\n  `salesmanname` char(30) NOT NULL DEFAULT '',\n  `salesarea` char(3) NOT NULL DEFAULT '',\n  `manager` int(1) NOT NULL DEFAULT '0',\n  `smantel` char(20) NOT NULL DEFAULT '',\n  `smanfax` char(20) NOT NULL DEFAULT '',\n  `commissionrate1` double NOT NULL DEFAULT '0',\n  `breakpoint` decimal(10,0) NOT NULL DEFAULT '0',\n  `commissionrate2` double NOT NULL DEFAULT '0',\n  `current` tinyint(4) NOT NULL COMMENT 'Salesman current (1) or not (0)',\n  PRIMARY KEY (`salesmancode`),\n  KEY `fk_salesman_1` (`salesarea`),\n  CONSTRAINT `fk_salesman_1` FOREIGN KEY (`salesarea`) REFERENCES `areas` (`areacode`)\n)", $db);

<?php

CreateTable('chartdetails', "CREATE TABLE `chartdetails` (\n  `accountcode` varchar(20) NOT NULL DEFAULT '0',\n  `period` smallint(6) NOT NULL DEFAULT '0',\n  `budget` double NOT NULL DEFAULT '0',\n  `actual` double NOT NULL DEFAULT '0',\n  `bfwd` double NOT NULL DEFAULT '0',\n  `bfwdbudget` double NOT NULL DEFAULT '0',\n  PRIMARY KEY (`accountcode`,`period`),\n  KEY `Period` (`period`),\n  CONSTRAINT `chartdetails_ibfk_1` FOREIGN KEY (`accountcode`) REFERENCES `chartmaster` (`accountcode`),\n  CONSTRAINT `chartdetails_ibfk_2` FOREIGN KEY (`period`) REFERENCES `periods` (`periodno`)\n)", $db);