apptable.txt

跨平台,多数据库查看编辑和查询工具,平台支持windows,linux,数据库支持oracle,odbc,mysql,slite等

	create table ApplyClass
	(
	AppID		int				not null,		
	AppDesc    	varchar2(128)     		not null,		
	OrderCode	varchar2(10)			not null,		
	BaseName   varchar2(128)			null,			
	IsUse		int				null			
	);
create unique index PK_ApplyClass on ApplyClass (AppID);
grant select on ApplyClass to public;
insert into ApplyClass(AppID,AppDesc,OrderCode,BaseName,IsUse) values(1000,'应用','A','',1);
insert into ApplyClass(AppID,AppDesc,OrderCode,BaseName,IsUse) values(1001,'系统','B','zxd',1);
insert into ApplyClass(AppID,AppDesc,OrderCode,BaseName,IsUse) values(1002,'用户','C','zxd',1);
insert into ApplyClass(AppID,AppDesc,OrderCode,BaseName,IsUse) values(1003,'客户','D','zxd',1);

	create table TableClass
	(
	ClassID			int		not null,			--分类id
	AppID			int      not null,			--应用ID
	ClassDesc		varchar2(128)   not null,			--分类描述
	OrderCode		varchar2(10)		null				-- 排序码
	);
create unique index PK_TableClass on TableClass (ClassID);

insert into TableClass(ClassID,AppID,ClassDesc,OrderCode) values(1000,1000,'北京','A');
insert into TableClass(ClassID,AppID,ClassDesc,OrderCode) values(1001,1000,'上海','B');
insert into TableClass(ClassID,AppID,ClassDesc,OrderCode) values(1002,1000,'合肥','C');
insert into TableClass(ClassID,AppID,ClassDesc,OrderCode) values(1003,1000,'深圳','D');
insert into TableClass(ClassID,AppID,ClassDesc,OrderCode) values(1004,1001,'天津','A');
insert into TableClass(ClassID,AppID,ClassDesc,OrderCode) values(1005,1001,'广州','B');
insert into TableClass(ClassID,AppID,ClassDesc,OrderCode) values(1006,1001,'西安','C');
insert into TableClass(ClassID,AppID,ClassDesc,OrderCode) values(1007,1001,'云南','D');

	create table tabledesc
	(
	tableid				int		not null,		--表ID
	tableName    				varchar2(128)	not null,		--表名
	DisplayName				varchar2(128)		null,		--表名别名
	ClassID 				int 			null,		--分类ID
	OrderCode				varchar2(10) 			null,		--分类显示序号，表名在tree中的显示序号
	HeadField				varchar2(64)	not null,		--记录列表域名 表头字段
	FieldOrder				varchar2(64)		null,		--排序域名  记录的排序字段
	IsClassIndex			int			null,		--是否分类索引	
	IndexField				varchar2(64)		null,		--索引域名
	AssociateTab			varchar2(64)		null,		--被索引表名  关联的表
	AssociateField			varchar2(64)		null,		--被索引域名  关联表的字段
	AssociateName			varchar2(64)		null,		--被索引显示域名  关联表的显示字段
	IsUse				int			null		--是否使用
	);
create unique index PK_tabledesc on tabledesc(tableid);


insert into 
tabledesc(tableid,tableName,DisplayName,ClassID,OrderCode,HeadField,FieldOrder,IsClassIndex,IndexField,AssociateTab,AssociateField,AssociateName,IsUse)
values(1000,'ALARMDEF','报警定义',1000,'a','numberid','','','','','','',1);

insert into 
tabledesc(tableid,tableName,DisplayName,ClassID,OrderCode,HeadField,FieldOrder,IsClassIndex,IndexField,AssociateTab,AssociateField,AssociateName,IsUse)
values(1001,'CMDNAME','命令名称',1000,'b','cmd_no','','','','','','',1);

insert into 
tabledesc(tableid,tableName,DisplayName,ClassID,OrderCode,HeadField,FieldOrder,IsClassIndex,IndexField,AssociateTab,AssociateField,AssociateName,IsUse)
values(1002,'DEVNAME','设备名称',1001,'c','dev_no','','','','','','',1);

insert into 
tabledesc(tableid,tableName,DisplayName,ClassID,OrderCode,HeadField,FieldOrder,IsClassIndex,IndexField,AssociateTab,AssociateField,AssociateName,IsUse)
values(1003,'DEVTYPE','设备类型',1001,'d','devtype_no','','','','','','',1);

insert into 
tabledesc(tableid,tableName,DisplayName,ClassID,OrderCode,HeadField,FieldOrder,IsClassIndex,IndexField,AssociateTab,AssociateField,AssociateName,IsUse)
values(1004,'DOTNAME','节点名称',1004,'e','dot_no','','','','','','',1);

insert into 
tabledesc(tableid,tableName,DisplayName,ClassID,OrderCode,HeadField,FieldOrder,IsClassIndex,IndexField,AssociateTab,AssociateField,AssociateName,IsUse)
values(1005,'EVENTNAME','事件名称',1004,'f','event_no','','','','','','',1);

insert into 
tabledesc(tableid,tableName,DisplayName,ClassID,OrderCode,HeadField,FieldOrder,IsClassIndex,IndexField,AssociateTab,AssociateField,AssociateName,IsUse)
values(1006,'FACNAME','厂站名称',1005,'g','fac_no','','','','','','',1);

insert into 
tabledesc(tableid,tableName,DisplayName,ClassID,OrderCode,HeadField,FieldOrder,IsClassIndex,IndexField,AssociateTab,AssociateField,AssociateName,IsUse)
values(1007,'GROUPGRANT','用户分组',1005,'h','groupid','','','','','','',1);
select * from GROUPGRANT


create table tableindex
(
   tableid		number   not null,	-- 表名ID		
   FieldID    	number   not null,	-- 字段名ID	
   IdxLevel		number	null,	-- 索引等级				
   IsUse		number	null,	-- 是否使用		
constraint pk_tableindex primary key(tableid,FieldID)
);

insert into tableindex(tableid,FieldID,IdxLevel,IsUse) values(4,401,1,1);
insert into tableindex(tableid,FieldID,IdxLevel,IsUse) values(4,402,2,1);
insert into tableindex(tableid,FieldID,IdxLevel,IsUse) values(4,403,3,1);
insert into tableindex(tableid,FieldID,IdxLevel,IsUse) values(4,404,4,1);



	create table FieldDesc
	(
	FieldID				number	not null,			-- 字段ID
	tableid				number	not null,			-- 表ID
	FieldName    			char(64)	not null,			-- 字段名
	DisplayName	  			char(64)	null,			-- 显示名
	DisplayType	  			number	null,			-- 显示类型
	DecimalNum	  			number	null,			-- 小数位数
	FillType	  			number	null,			-- 填充方式
	AssociateTab	  		char(128)  null,			-- 外键表名
	AssoDisplayName 		           char(64)	null,			-- 外键显示域名
	AssoField 				char(64)	null,			-- 外键赋值域名
	ConfigID				number	null,			-- 字段配置项ID
	IsUse		  		number	null,			-- 删除标志 
	constraint pk_FieldDesc primary key(FieldID,tableid)
	);
	
insert into FieldDesc(fieldid,tableid,fieldname) values(401,4,'Fac_No');
insert into FieldDesc(fieldid,tableid,fieldname) values(402,4,'Dev_No');
insert into FieldDesc(fieldid,tableid,fieldname) values(403,4,'Dot_No');
insert into FieldDesc(fieldid,tableid,fieldname) values(404,4,'Devtype_No');