一、demo脚本
-- 1、增加 {地址信息} 区块
SET @modulename='CustomForm01'; -- 所在模块可根据需要修改
SET @trantable='ld_blocks';
SET @blocklabel='LBL_ADDRESS_INFORMATION';
SET @zh='地址信息';
SET @en='Address Information';
SET @tabid=(SELECT tabid FROM ld_tab WHERE `name`=@modulename LIMIT 1);
SET @maxid=IFNULL((SELECT MAX(blockid) FROM ld_blocks),0);
SET @maxseq=IFNULL((SELECT MAX(sequence) FROM ld_blocks WHERE tabid=@tabid),0);
INSERT INTO ld_blocks(blockid,tabid,blocklabel,sequence,visible)
SELECT @maxid+1,@tabid,@blocklabel,@maxseq+1,0 FROM DUAL
WHERE NOT EXISTS (SELECT 1 FROM ld_blocks WHERE tabid=@tabid AND blocklabel=@blocklabel);UPDATE ld_blocks_seq SET id=(SELECT MAX(blockid) FROM ld_blocks);
SET @blockid=(SELECT blockid FROM ld_blocks WHERE tabid=@tabid AND blocklabel=@blocklabel LIMIT 1);
INSERT INTO ld_tranlabel(languagetype,trantable,tabid,relateid,fieldlabel)
SELECT 'zh',@trantable,@tabid,@blockid,@zh FROM DUAL
WHERE NOT EXISTS (SELECT 1 FROM ld_tranlabel WHERE languagetype = 'zh' AND trantable =@trantable AND tabid = @tabid AND relateid=@blockid);INSERT INTO ld_tranlabel(languagetype,trantable,tabid,relateid,fieldlabel)
SELECT 'en',@trantable,@tabid,@blockid,@en FROM DUAL
WHERE NOT EXISTS (SELECT 1 FROM ld_tranlabel WHERE languagetype = 'en' AND trantable =@trantable AND tabid = @tabid AND relateid=@blockid);
-- 2、增加国家 省份 城市 区域
SET @tablename=(SELECT tablename FROM ld_field WHERE tabid=@tabid AND columnname='smcreatorid' LIMIT 1);
SET @columnname='bill_country';
SET @fieldname='bill_country';
SET @zh='国家';
SET @en='Country';
SET @uitype=15;
SET @readonly=1;
SET @presence=2;
SET @defaultvalue=NULL;
SET @displaytype=1;
SET @typeofdata='V~O';
SET @quickcreate=1;
SET @quickcreatesequence=NULL;
SET @info_type='BAS';
SET @masseditable=1;
SET @seq=(SELECT sequence FROM ld_field WHERE tabid=@tabid AND block=@blockid AND columnname='bill_city' LIMIT 1);
UPDATE ld_field SET sequence=sequence+1 WHERE tabid=@tabid AND block=@blockid AND sequence>@seq;SET @fieldid=(SELECT MAX(fieldid) FROM ld_field);
INSERT INTO ld_field(tabid,fieldid,columnname,tablename,generatedtype,uitype,fieldname,fieldlabel,readonly,presence,defaultvalue,maximumlength,sequence,block,displaytype,typeofdata,quickcreate,quickcreatesequence,info_type,masseditable,helpinfo,fieldonly)
SELECT @tabid,@fieldid+1,@columnname,@tablename,1,@uitype,@fieldname,@zh,@readonly,@presence,@defaultvalue,100,@seq+1,@blockid,@displaytype,@typeofdata,@quickcreate,@quickcreatesequence,@info_type,@masseditable,NULL,0 from dual
WHERE NOT EXISTS (SELECT 1 FROM ld_field WHERE tabid=@tabid AND tablename=@tablename AND columnname=@columnname);SET @fieldid=(SELECT fieldid FROM ld_field WHERE tabid=@tabid AND columnname=@columnname LIMIT 1);
INSERT INTO ld_tranlabel(languagetype,trantable,tabid,relateid,fieldlabel)
SELECT 'zh','ld_field',@tabid,@fieldid,@zh FROM dual
WHERE NOT EXISTS (SELECT 1 FROM ld_tranlabel WHERE languagetype = 'zh' AND trantable ='ld_field' AND tabid = @tabid AND relateid=@fieldid);INSERT INTO ld_tranlabel(languagetype,trantable,tabid,relateid,fieldlabel)
SELECT 'en','ld_field',@tabid,@fieldid,@en FROM dual
WHERE NOT EXISTS (SELECT 1 FROM ld_tranlabel WHERE languagetype = 'en' AND trantable ='ld_field' AND tabid = @tabid AND relateid=@fieldid);SET @i=0;
UPDATE ld_field SET sequence=(@i:=@i+1) WHERE tabid=@tabid AND block=@block ORDER BY sequence;CALL AddColumn(@tablename,@columnname,'VARCHAR','100',NULL);
SET @columnname='bill_state';
SET @fieldname='bill_state';
SET @zh='省份';
SET @en='State';
SET @uitype=15;
SET @readonly=1;
SET @presence=2;
SET @defaultvalue=NULL;
SET @displaytype=1;
SET @typeofdata='V~O';
SET @quickcreate=1;
SET @quickcreatesequence=NULL;
SET @info_type='BAS';
SET @masseditable=1;
SET @seq=(SELECT sequence FROM ld_field WHERE tabid=@tabid AND block=@blockid AND columnname='bill_city' LIMIT 1);
UPDATE ld_field SET sequence=sequence+1 WHERE tabid=@tabid AND block=@blockid AND sequence>@seq;SET @fieldid=(SELECT MAX(fieldid) FROM ld_field);
INSERT INTO ld_field(tabid,fieldid,columnname,tablename,generatedtype,uitype,fieldname,fieldlabel,readonly,presence,defaultvalue,maximumlength,sequence,block,displaytype,typeofdata,quickcreate,quickcreatesequence,info_type,masseditable,helpinfo,fieldonly)
SELECT @tabid,@fieldid+1,@columnname,@tablename,1,@uitype,@fieldname,@zh,@readonly,@presence,@defaultvalue,100,@seq+1,@blockid,@displaytype,@typeofdata,@quickcreate,@quickcreatesequence,@info_type,@masseditable,NULL,0 from dual
WHERE NOT EXISTS (SELECT 1 FROM ld_field WHERE tabid=@tabid AND tablename=@tablename AND columnname=@columnname);SET @fieldid=(SELECT fieldid FROM ld_field WHERE tabid=@tabid AND columnname=@columnname LIMIT 1);
INSERT INTO ld_tranlabel(languagetype,trantable,tabid,relateid,fieldlabel)
SELECT 'zh','ld_field',@tabid,@fieldid,@zh FROM dual
WHERE NOT EXISTS (SELECT 1 FROM ld_tranlabel WHERE languagetype = 'zh' AND trantable ='ld_field' AND tabid = @tabid AND relateid=@fieldid);INSERT INTO ld_tranlabel(languagetype,trantable,tabid,relateid,fieldlabel)
SELECT 'en','ld_field',@tabid,@fieldid,@en FROM dual
WHERE NOT EXISTS (SELECT 1 FROM ld_tranlabel WHERE languagetype = 'en' AND trantable ='ld_field' AND tabid = @tabid AND relateid=@fieldid);SET @i=0;
UPDATE ld_field SET sequence=(@i:=@i+1) WHERE tabid=@tabid AND block=@block ORDER BY sequence;CALL AddColumn(@tablename,@columnname,'VARCHAR','100',NULL);
SET @columnname='bill_city';
SET @fieldname='bill_city';
SET @zh='城市';
SET @en='City';
SET @uitype=15;
SET @readonly=1;
SET @presence=2;
SET @defaultvalue=NULL;
SET @displaytype=1;
SET @typeofdata='V~O';
SET @quickcreate=1;
SET @quickcreatesequence=NULL;
SET @info_type='BAS';
SET @masseditable=1;
SET @seq=(SELECT sequence FROM ld_field WHERE tabid=@tabid AND block=@blockid AND columnname='bill_city' LIMIT 1);
UPDATE ld_field SET sequence=sequence+1 WHERE tabid=@tabid AND block=@blockid AND sequence>@seq;SET @fieldid=(SELECT MAX(fieldid) FROM ld_field);
INSERT INTO ld_field(tabid,fieldid,columnname,tablename,generatedtype,uitype,fieldname,fieldlabel,readonly,presence,defaultvalue,maximumlength,sequence,block,displaytype,typeofdata,quickcreate,quickcreatesequence,info_type,masseditable,helpinfo,fieldonly)
SELECT @tabid,@fieldid+1,@columnname,@tablename,1,@uitype,@fieldname,@zh,@readonly,@presence,@defaultvalue,100,@seq+1,@blockid,@displaytype,@typeofdata,@quickcreate,@quickcreatesequence,@info_type,@masseditable,NULL,0 from dual
WHERE NOT EXISTS (SELECT 1 FROM ld_field WHERE tabid=@tabid AND tablename=@tablename AND columnname=@columnname);SET @fieldid=(SELECT fieldid FROM ld_field WHERE tabid=@tabid AND columnname=@columnname LIMIT 1);
INSERT INTO ld_tranlabel(languagetype,trantable,tabid,relateid,fieldlabel)
SELECT 'zh','ld_field',@tabid,@fieldid,@zh FROM dual
WHERE NOT EXISTS (SELECT 1 FROM ld_tranlabel WHERE languagetype = 'zh' AND trantable ='ld_field' AND tabid = @tabid AND relateid=@fieldid);INSERT INTO ld_tranlabel(languagetype,trantable,tabid,relateid,fieldlabel)
SELECT 'en','ld_field',@tabid,@fieldid,@en FROM dual
WHERE NOT EXISTS (SELECT 1 FROM ld_tranlabel WHERE languagetype = 'en' AND trantable ='ld_field' AND tabid = @tabid AND relateid=@fieldid);SET @i=0;
UPDATE ld_field SET sequence=(@i:=@i+1) WHERE tabid=@tabid AND block=@block ORDER BY sequence;CALL AddColumn(@tablename,@columnname,'VARCHAR','100',NULL);
SET @columnname='bill_area';
SET @fieldname='bill_area';
SET @zh='区域';
SET @en='Area';
SET @uitype=15;
SET @readonly=1;
SET @presence=2;
SET @defaultvalue=NULL;
SET @displaytype=1;
SET @typeofdata='V~O';
SET @quickcreate=1;
SET @quickcreatesequence=NULL;
SET @info_type='BAS';
SET @masseditable=1;
SET @seq=(SELECT sequence FROM ld_field WHERE tabid=@tabid AND block=@blockid AND columnname='bill_city' LIMIT 1);
UPDATE ld_field SET sequence=sequence+1 WHERE tabid=@tabid AND block=@blockid AND sequence>@seq;SET @fieldid=(SELECT MAX(fieldid) FROM ld_field);
INSERT INTO ld_field(tabid,fieldid,columnname,tablename,generatedtype,uitype,fieldname,fieldlabel,readonly,presence,defaultvalue,maximumlength,sequence,block,displaytype,typeofdata,quickcreate,quickcreatesequence,info_type,masseditable,helpinfo,fieldonly)
SELECT @tabid,@fieldid+1,@columnname,@tablename,1,@uitype,@fieldname,@zh,@readonly,@presence,@defaultvalue,100,@seq+1,@blockid,@displaytype,@typeofdata,@quickcreate,@quickcreatesequence,@info_type,@masseditable,NULL,0 from dual
WHERE NOT EXISTS (SELECT 1 FROM ld_field WHERE tabid=@tabid AND tablename=@tablename AND columnname=@columnname);SET @fieldid=(SELECT fieldid FROM ld_field WHERE tabid=@tabid AND columnname=@columnname LIMIT 1);
INSERT INTO ld_tranlabel(languagetype,trantable,tabid,relateid,fieldlabel)
SELECT 'zh','ld_field',@tabid,@fieldid,@zh FROM dual
WHERE NOT EXISTS (SELECT 1 FROM ld_tranlabel WHERE languagetype = 'zh' AND trantable ='ld_field' AND tabid = @tabid AND relateid=@fieldid);INSERT INTO ld_tranlabel(languagetype,trantable,tabid,relateid,fieldlabel)
SELECT 'en','ld_field',@tabid,@fieldid,@en FROM dual
WHERE NOT EXISTS (SELECT 1 FROM ld_tranlabel WHERE languagetype = 'en' AND trantable ='ld_field' AND tabid = @tabid AND relateid=@fieldid);SET @i=0;
UPDATE ld_field SET sequence=(@i:=@i+1) WHERE tabid=@tabid AND block=@block ORDER BY sequence;CALL AddColumn(@tablename,@columnname,'VARCHAR','100',NULL);
CALL getallprofile(@modulename);
CALL fieldprofile(@modulename);UPDATE ld_field SET defaultvalue='中国' WHERE tabid=@tabid AND columnname='bill_country';
-- 3、设置级联关系
DELETE FROM ld_picklist_dependency WHERE tabid=@tabid AND sourcefield IN ('bill_country','bill_state','bill_city');
SET @i=(SELECT MAX(id) FROM ld_picklist_dependency);
INSERT INTO ld_picklist_dependency(id,tabid,sourcefield,targetfield,sourcevalue,targetvalues)
SELECT (@i:=@i+1),@tabid,sourcefield,targetfield,sourcevalue,targetvalues FROM ld_picklist_dependency
WHERE tabid=6 AND sourcefield IN ('bill_country','bill_state','bill_city');UPDATE ld_picklist_dependency_seq SET id=(SELECT MAX(id) FROM ld_picklist_dependency);
delimiter;