JLH/JLHHJSvr/DBA/ParkDBVersion.cs

using LJLib.DBOption;
//CREATE TABLE st_serial
//( 
//   tubeCode varchar(20) NOT NULL CONSTRAINT DF_st_serial_tubeCode DEFAULT(''),
//   claimCode varchar(20) NOT NULL CONSTRAINT DF_st_serial_claimCode DEFAULT(''),
//   activationCode varchar(20) NULL,
//   innerCode varchar(20) NOT NULL CONSTRAINT DF_st_serial_innerCode DEFAULT(''),
//   ifBP tinyint NOT NULL CONSTRAINT DF_st_serial_ifBP DEFAULT(0),
//   bpLotNumber varchar(20) NOT NULL CONSTRAINT DF_st_serial_bpLotNumber DEFAULT(''),
//   lotNumber varchar(30) NOT NULL CONSTRAINT DF_st_serial_lotNumber DEFAULT(''),
//   solFillDate datetime NULL,
//   returnMailerTrackingNumber varchar(255) NOT NULL CONSTRAINT DF_st_serial_returnMailerTrackingNumber DEFAULT(''),
//   ifFG tinyint NOT NULL CONSTRAINT DF_st_serial_ifFG DEFAULT(0),
//   containerNumber varchar(20) NOT NULL CONSTRAINT DF_st_serial_containerNumber DEFAULT(''),
//   packCartonID varchar(255) NOT NULL CONSTRAINT DF_st_serial_packCartonID DEFAULT(''),
//   FGCarton varchar(255) NOT NULL CONSTRAINT DF_st_serial_FGCarton DEFAULT(''),
//   packPalletID varchar(255) NOT NULL CONSTRAINT DF_st_serial_packPalletID DEFAULT(''),
//   FGPallet varchar(255) NOT NULL CONSTRAINT DF_st_serial_FGPallet DEFAULT(''),
//   FGmanufacturedDate datetime NULL,
//   gNum int NOT NULL CONSTRAINT DF_st_serial_gNum DEFAULT(0),
//   cartonNum int NOT NULL CONSTRAINT DF_st_serial_cartonNum DEFAULT(0),
//   syncBP tinyint NOT NULL CONSTRAINT DF_st_serial_syncBP DEFAULT(0),
//   syncFG tinyint NOT NULL CONSTRAINT DF_st_serial_syncFG DEFAULT(0),
//   syncASN tinyint NOT NULL CONSTRAINT DF_st_serial_syncASN DEFAULT(0),
//   CONSTRAINT PK_ST_SERIAL PRIMARY KEY (tubeCode)
//)
namespace JLHHJSvr.DBA
{
    public sealed class ParkDbVersion : DBVersionSql
    {
        protected override string currentVersion
        {
            get { return "1.0.180104"; }
        }

        protected override string dbname
        {
            get { return "Park"; }
        }

        protected override DBVersionSql.Script[] scripts
        {
            get
            {
                return new Script[]
                {
new Script("1.0.171204", @"CREATE TABLE st_user 
( 
   userid int NOT NULL, 
   usercode varchar(20) NOT NULL CONSTRAINT DF_st_user_usercode DEFAULT(''), 
   username varchar(20) NOT NULL CONSTRAINT DF_st_user_username DEFAULT(''), 
   psw varchar(255) NOT NULL CONSTRAINT DF_st_user_psw DEFAULT(''), 
   sex varchar(10) NOT NULL CONSTRAINT DF_st_user_sex DEFAULT(''), 
   tel varchar(50) NOT NULL CONSTRAINT DF_st_user_tel DEFAULT(''), 
   dscrp varchar(255) NOT NULL CONSTRAINT DF_st_user_dscrp DEFAULT(''), 
   opemp varchar(20) NOT NULL CONSTRAINT DF_st_user_opemp DEFAULT(''), 
   opdate datetime NOT NULL CONSTRAINT DF_st_user_opdate DEFAULT(GETDATE()), 
   modemp varchar(20) NOT NULL CONSTRAINT DF_st_user_modemp DEFAULT(''), 
   moddate datetime NULL, 
   CONSTRAINT PK_ST_USER PRIMARY KEY (userid)
)", ""),

new Script("1.0.171204", @"CREATE UNIQUE NONCLUSTERED INDEX IX_st_user ON st_user 
(
   usercode ASC
)", ""),

new Script("1.0.171204", @"CREATE TABLE st_user_power 
( 
   userid int NOT NULL, 
   funid int NOT NULL, 
   CONSTRAINT PK_ST_USER_POWER PRIMARY KEY (userid, funid)
)", ""),

new Script("1.0.171204", @"CREATE TABLE st_road 
( 
   roadid int NOT NULL, 
   roadname varchar(255) NOT NULL CONSTRAINT DF_st_road_roadname DEFAULT(''), 
   groupname varchar(255) NOT NULL CONSTRAINT DF_st_road_groupname DEFAULT(''), 
   manager varchar(20) NOT NULL CONSTRAINT DF_st_road_manager DEFAULT(''), 
   man_tel varchar(50) NOT NULL CONSTRAINT DF_st_road_man_tel DEFAULT(''), 
   cnt int NOT NULL CONSTRAINT DF_st_road_cnt DEFAULT(0), 
   dscrp varchar(255) NOT NULL CONSTRAINT DF_st_road_dscrp DEFAULT(''), 
   opemp varchar(20) NOT NULL CONSTRAINT DF_st_road_opemp DEFAULT(''), 
   opdate datetime NOT NULL CONSTRAINT DF_st_road_opdate DEFAULT(GETDATE()), 
   modemp varchar(20) NOT NULL CONSTRAINT DF_st_road_modemp DEFAULT(''), 
   moddate datetime NULL, 
   CONSTRAINT PK_ST_ROAD PRIMARY KEY (roadid)
)", ""),

new Script("1.0.171204", @"CREATE UNIQUE NONCLUSTERED INDEX IX_st_road ON st_road 
(
   roadname ASC
)", ""),

new Script("1.0.171204", @"CREATE TABLE st_car 
(
   carnum varchar(20) NOT NULL, 
   OWNER varchar(20) NOT NULL CONSTRAINT DF_st_car_OWNER DEFAULT(''), 
   owner_tel varchar(50) NOT NULL CONSTRAINT DF_st_car_owner_tel DEFAULT(''), 
   dscrp varchar(255) NOT NULL CONSTRAINT DF_st_car_dscrp DEFAULT(''), 
   opemp varchar(20) NOT NULL CONSTRAINT DF_st_car_opemp DEFAULT(''), 
   opdate datetime NOT NULL CONSTRAINT DF_st_car_opdate DEFAULT(GETDATE()), 
   modemp varchar(20) NOT NULL CONSTRAINT DF_st_car_modemp DEFAULT(''), 
   moddate datetime NULL, 
   ifinput tinyint NOT NULL CONSTRAINT DF_st_car_ifinput DEFAULT(0), 
   CONSTRAINT PK_ST_CAR PRIMARY KEY (carnum)
)", ""),

new Script("1.0.171204", @"CREATE TABLE st_permit 
( 
   pid int NOT NULL, 
   carnum varchar(20) NOT NULL CONSTRAINT DF_st_permit_carnum DEFAULT(''), 
   OWNER varchar(20) NOT NULL CONSTRAINT DF_st_permit_OWNER DEFAULT(''), 
   owner_tel varchar(50) NOT NULL CONSTRAINT DF_st_permit_owner_tel DEFAULT(''), 
   begindate datetime NOT NULL CONSTRAINT DF_st_permit_begindate DEFAULT(GETDATE()), 
   enddate datetime NOT NULL CONSTRAINT DF_st_permit_enddate DEFAULT(GETDATE()), 
   iftmp tinyint NOT NULL CONSTRAINT DF_st_permit_iftmp DEFAULT(0), 
   inuse tinyint NOT NULL CONSTRAINT DF_st_permit_inuse DEFAULT(0), 
   roadid int NOT NULL CONSTRAINT DF_st_permit_roadid DEFAULT(0), 
   dscrp varchar(255) NOT NULL CONSTRAINT DF_st_permit_dscrp DEFAULT(''), 
   opemp varchar(20) NOT NULL CONSTRAINT DF_st_permit_opemp DEFAULT(''), 
   opdate datetime NOT NULL CONSTRAINT DF_st_permit_opdate DEFAULT(GETDATE()), 
   modemp varchar(20) NOT NULL CONSTRAINT DF_st_permit_modemp DEFAULT(''), 
   moddate datetime NULL, 
   paytype tinyint NOT NULL CONSTRAINT DF_st_permit_paytype DEFAULT(0), 
   payamt decimal(18,5) NOT NULL CONSTRAINT DF_st_permit_payamt DEFAULT(0), 
   CONSTRAINT PK_ST_PERMIT PRIMARY KEY (pid)
)", ""),

new Script("1.0.171204", @"CREATE TABLE cd_idfactory 
( 
   idkey varchar(255) NOT NULL, 
   idvalue int NOT NULL CONSTRAINT DF_cd_idfactory_idvalue DEFAULT(0), 
   CONSTRAINT PK_CD_IDFACTORY PRIMARY KEY (idkey)
)", ""),

new Script("1.0.171204", @"CREATE TABLE st_bill 
( 
   billid int NOT NULL, 
   carnum varchar(20) NOT NULL CONSTRAINT DF_st_bill_carnum DEFAULT(''), 
   OWNER varchar(20) NOT NULL CONSTRAINT DF_st_bill_OWNER DEFAULT(''), 
   owner_tel varchar(50) NOT NULL CONSTRAINT DF_st_bill_owner_tel DEFAULT(''), 
   dscrp varchar(255) NOT NULL CONSTRAINT DF_st_bill_dscrp DEFAULT(''), 
   flag tinyint NOT NULL CONSTRAINT DF_st_bill_flag DEFAULT(0), 
   auditemp varchar(20) NOT NULL CONSTRAINT DF_st_bill_auditemp DEFAULT(''), 
   auditdate datetime NULL, 
   opemp varchar(20) NOT NULL CONSTRAINT DF_st_bill_opemp DEFAULT(''), 
   opdate datetime NOT NULL CONSTRAINT DF_st_bill_opdate DEFAULT(GETDATE()), 
   modemp varchar(20) NOT NULL CONSTRAINT DF_st_bill_modemp DEFAULT(''), 
   moddate datetime NULL, 
   paytype tinyint NOT NULL CONSTRAINT DF_st_bill_paytype DEFAULT(0), 
   payamt decimal(18,5) NOT NULL CONSTRAINT DF_st_bill_payamt DEFAULT(0), 
   CONSTRAINT PK_ST_BILL PRIMARY KEY (billid)
)", ""),

new Script("1.0.171204", @"CREATE TABLE st_file 
( 
   filemd5 varchar(255) NOT NULL, 
   filedata image NOT NULL, 
   CONSTRAINT PK_ST_FILE PRIMARY KEY (filemd5)
)", ""),

new Script("1.0.171204", @"CREATE TABLE st_bill_mx 
( 
   billid int NOT NULL, 
   printid int NOT NULL, 
   filemd5 varchar(255) NOT NULL CONSTRAINT DF_st_bill_mx_filemd5 DEFAULT(''), 
   CONSTRAINT PK_ST_BILL_MX PRIMARY KEY (billid, printid)
)", ""),
  new Script("1.0.171209", @"CREATE TABLE st_option
(
    optionid int NOT NULL,
    optionname varchar(200) NOT NULL CONSTRAINT DF_st_option_optionname DEFAULT(''),
    optionvalue varchar(200) NOT NULL CONSTRAINT DF_st_option_optionvalue DEFAULT(''),
    optiondscrp varchar(200) NOT NULL CONSTRAINT DF_st_option_optiondscrp DEFAULT(''),
    CONSTRAINT PK_st_option PRIMARY KEY
    (
        optionid
    )
)", ""),
new Script("1.0.171211", @"
ALTER TABLE st_option ADD modemp varchar(20) NOT NULL CONSTRAINT DF_st_option_modemp DEFAULT('')
ALTER TABLE st_option ADD moddate datetime NULL

ALTER TABLE st_file ADD fileType varchar(20) NOT NULL CONSTRAINT DF_st_file_fileType DEFAULT('')
", ""),
 new Script("1.0.171215", @"
ALTER TABLE st_bill ADD roadid int NOT NULL CONSTRAINT DF_st_bill_roadid DEFAULT(0)
", ""),
  new Script("1.0.180104", @"
CREATE TABLE fx_user_dwlayout(
	userid int NOT NULL,
	dwname varchar(50) NOT NULL,
	column_visible text NOT NULL,
	column_width text NOT NULL,
	column_x text NOT NULL,
	column_autosize int NOT NULL,
	column_detail_height int NOT NULL,
 CONSTRAINT PK_fx_user_dwlayout PRIMARY KEY CLUSTERED 
(
	userid ASC,
	dwname ASC
)
)

ALTER TABLE fx_user_dwlayout ADD  CONSTRAINT DF_fx_user_dwlayout_column_visible  DEFAULT ('') FOR column_visible
ALTER TABLE fx_user_dwlayout ADD  CONSTRAINT DF_fx_user_dwlayout_column_width  DEFAULT ('') FOR column_width
ALTER TABLE fx_user_dwlayout ADD  CONSTRAINT DF_fx_user_dwlayout_column_x  DEFAULT ('') FOR column_x
ALTER TABLE fx_user_dwlayout ADD  CONSTRAINT DF_fx_user_dwlayout_column_autosize  DEFAULT (-1) FOR column_autosize
ALTER TABLE fx_user_dwlayout ADD  CONSTRAINT DF_fx_user_dwlayout_column_detail_height  DEFAULT (0) FOR column_detail_height
", ""),
                };
            }
        }
    }
}