v032_to_v033.sql

来自「CRM源码This file describes some issues tha」· SQL 代码 · 共 49 行

CREATE VIEW dbo.QW_DD_SOLUTIONTOPIC_VW AS
	SELECT qw_enumvalue AS pkey, qw_enumlabel AS value
	FROM QW_FIELDINFO
	WHERE (qw_tablename = 'qw_solution')
		  AND (qw_columnname = 'qw_topic')
go

CREATE VIEW dbo.QW_DD_SOLUTIONSTATUS_VW AS
	SELECT qw_enumvalue AS pkey, qw_enumlabel AS value
	FROM QW_FIELDINFO
	WHERE (qw_tablename = 'qw_solution')
		  AND (qw_columnname = 'qw_solutionstatus')
go

-- QW_SOLUTION
CREATE TABLE dbo.QW_SOLUTION (
    QW_SOLUTIONID                  INT NOT NULL,
    QW_TOPIC                       NUMERIC(2,0),
    QW_SOLUTIONSTATUS              NUMERIC(2,0),
    QW_PROBLEM                     VARCHAR(255) NOT NULL,
    QW_SUMMARY                     VARCHAR(255) NOT NULL,
    QW_CREATOR                     INT NOT NULL,
--    lastused                       DATE,
    QW_LASTMODIFIED                DATETIME NULL,
    QW_DATECREATED                 DATETIME NOT NULL,
    QW_PUBLICATIONDATE             DATETIME NULL,
    QW_DOCUMENTID                  VARCHAR(100),
    QW_TEXT                        text NULL,
  CONSTRAINT PK_SOLUTION PRIMARY KEY (QW_SOLUTIONID)
)
go

/* solution topics */
INSERT INTO QW_FIELDINFO (qw_tablename,qw_columnname,qw_columntype,qw_enumvalue,qw_enumlabel) VALUES ('qw_solution','qw_topic',14,0,'Hardware');
INSERT INTO QW_FIELDINFO (qw_tablename,qw_columnname,qw_columntype,qw_enumvalue,qw_enumlabel) VALUES ('qw_solution','qw_topic',14,1,'Network');
INSERT INTO QW_FIELDINFO (qw_tablename,qw_columnname,qw_columntype,qw_enumvalue,qw_enumlabel) VALUES ('qw_solution','qw_topic',14,2,'Operations');
INSERT INTO QW_FIELDINFO (qw_tablename,qw_columnname,qw_columntype,qw_enumvalue,qw_enumlabel) VALUES ('qw_solution','qw_topic',14,3,'Products');
INSERT INTO QW_FIELDINFO (qw_tablename,qw_columnname,qw_columntype,qw_enumvalue,qw_enumlabel) VALUES ('qw_solution','qw_topic',14,4,'Servises');
INSERT INTO QW_FIELDINFO (qw_tablename,qw_columnname,qw_columntype,qw_enumvalue,qw_enumlabel) VALUES ('qw_solution','qw_topic',14,5,'Software');

/* solution status */
INSERT INTO QW_FIELDINFO (qw_tablename,qw_columnname,qw_columntype,qw_enumvalue,qw_enumlabel) VALUES ('qw_solution','qw_solutionstatus',14,0,'Draft');
INSERT INTO QW_FIELDINFO (qw_tablename,qw_columnname,qw_columntype,qw_enumvalue,qw_enumlabel) VALUES ('qw_solution','qw_solutionstatus',14,1,'Published');
INSERT INTO QW_FIELDINFO (qw_tablename,qw_columnname,qw_columntype,qw_enumvalue,qw_enumlabel) VALUES ('qw_solution','qw_solutionstatus',14,2,'Retired');
INSERT INTO QW_FIELDINFO (qw_tablename,qw_columnname,qw_columntype,qw_enumvalue,qw_enumlabel) VALUES ('qw_solution','qw_solutionstatus',14,3,'Reviewed');
go

-- all new update scripts must include a line like this:
UPDATE QX_SYS_PROP SET PROP_VAL = '033' WHERE (PROP_ID = 'DB_APP_VERSION');