v005_to_v006.sql

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

CREATE TABLE dbo.QW_ADDRESS (
	[qw_addressid] [int] NOT NULL ,
	[qw_addresstype] [int] NOT NULL ,
	[qw_objectid] [int] NOT NULL ,
	[qw_objecttype] [int] NOT NULL ,
	[qw_address1] [varchar] (255) NULL ,
	[qw_address2] [varchar] (255) NULL ,
	[qw_city] [varchar] (100) NULL ,
	[qw_state] [int] NULL ,
	[qw_country] [varchar] (255) NULL ,
	[qw_zip] [varchar] (40) NULL ,
	[qw_phone] [varchar] (30) NULL ,
	[qw_fax] [varchar] (30) NULL ,
	[qw_other] [varchar] (255) NULL ,
	[qw_notes] [text] NULL ,
	CONSTRAINT [pk_qw_address] PRIMARY KEY CLUSTERED ([qw_addressid]) WITH FILLFACTOR = 80 ON [PRIMARY],
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

INSERT INTO QW_FIELDINFO_TYPE(qw_fieldinfo_type_id, qw_name, qw_description) VALUES (7, 'qw_addresstype', 'Required field');
INSERT INTO QW_FIELDINFO (qw_tablename,qw_fieldinfo_type_id,qw_columntype,qw_enumvalue,qw_enumlabel) VALUES ('qw_address',7,14,0,'Primary');
INSERT INTO QW_FIELDINFO (qw_tablename,qw_fieldinfo_type_id,qw_columntype,qw_enumvalue,qw_enumlabel) VALUES ('qw_address',7,14,1,'Secondary');
GO

CREATE VIEW dbo.QW_DD_ADDRESSTYPE_VW AS
SELECT qw_enumvalue AS pkey, qw_enumlabel AS value
FROM QW_FIELDINFO
JOIN QW_FIELDINFO_TYPE ON QW_FIELDINFO_TYPE.qw_fieldinfo_type_id=QW_FIELDINFO.qw_fieldinfo_type_id
WHERE (qw_tablename = 'qw_address')
  AND (QW_FIELDINFO_TYPE.qw_name = 'qw_addresstype')
GO

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