平台SQL配置规范 零、目录

 1. 数据库设计规范：建库建表要求及平台内置表的字段要求；

 2. 语句格式要求：平台列表、图表、树等工具中使用SQL时的格式要求及实例；

 3. 开发代码中对SQL的规范：后台开发时SQL基本设置时的参数说明； 

4. 注意：小细节点的要求注意

 一、数据库设计规范

• 1 建库建表 
• 1 为了兼容 oracle数据库，表名字段名，一律采用 全部大写，单词之间加"_"分割 
• 2 表中包含创建时间(CREATE_TIME)，修改时间(MODIFY_TIME)、创建者(CREATE_USER_ID)、修改者(MODIFY_USER_ID)字段，通过平台设置可以记录操作数据 
• 3 平台的表一律采用 ES开头

• 2 备份表
  平台的自动备份功能，需要在数据库中建表同时包含以下字段

  ES_ID_3AE7F1565B82 ： 唯一标示的id 
• ES_ID_GROUP_3AE7F1565B82：操作分组id，同一次操作多个表，这个字段的值是一样的 
• ES_OP_TIME_3AE7F1565B82：操作时间 
• ES_OP_USER_3AE7F1565B82：操作用户 
• ES_OP_TYPE_3AE7F1565B82：操作类型：A：添加、更新<=>UA：更新后、UB：更新前、D：删除

• 3 操作日志表
  平台的操作日志功能，需要在数据库中建表同时包含以下字段

  ID:主键 
• MODULE:模块 
• OP_TYPE：操作类型：增删改 
• OP_TABLE：操作的表 
• KEY_VALUE:操作的键值信息 
• BACKUP_GROUP_ID:备份信息的组id（如果启用备份的话） 
• CONTENT：操作内容 
• OP_URL：操作时的url地址 
• REMARKS:备注 
• USER_ID：操作用户ID 
• LOG_TIME：操作时间

• 4 EXCEL导入

  • 4.1 平台EXCEL的导入功能，新建临时表时需要包含如下字段 
  • ES_ID_BCABBD66393F ： 唯一标示的id 
  • ES_ID_GROUP_BCABBD66393F：操作分组id，同一次导入的值相同 
  • ES_ROWNUM_BCABBD66393F：excel中的行号 
  • ES_ERR_FLAG_BCABBD66393F：状态标记 0 无错误,1 有错误 
  • ES_ERR_MSG_BCABBD66393F：错误信息 
  • ES_OP_TIME_BCABBD66393F：操作时间 
  • ES_OP_USER_BCABBD66393F：操作用户
  • 4.2 平台EXCEL的导入功能，当使用【忽略空表头】功能时，需按照以下的规则建表 
  • 1 表名:ES_EXCEL_IMPORT_IGNORE_COLS 
  • 2 必需字段：
  • EXCEL_IMPORT_ID varchar(36) 
  • EXCEL_IMPORT_GROUP_ID varchar(36) IGNORE_COLS_NAME nvarchar(500)

二、语句格式要求

• 1 FROM子句
  最外层的FROM子句需要单独占一行
• 2 WHERE 1=1
  必需存在，单独占一行，不能包含其他任何非空白字符；
• 3 HAVING 1=1
  如果需要使用HAVING子句(GROUP BY 之后需要条件筛选的)，也要保证存在一行HAVING 1=1子句以便系统可以进行查询优化。
• 4 FROMHOLDER
  当SQL较复杂，含有多个FROM 子句，若平台无法正确分析最外层FROM，可手动在最外层的FROM前面单独加一行FROMHOLDER，用来标记下面的FROM是否为最外层；
• 5 WHEREHOLDER
  条件占位符，查询条件动态插入的位置，大部分情况下系统会自动分析出该位置，如果不正确时，可以使用这种方式来指定插入位置。

SELECT TYPE,VALID_FLAG 

FROMHOLDER --FROMHOLDER的使用 

FROM (SELECT T.TYPE,T.VALID_FLAG 

,COUNT(1) AS C_NUM 

FROM EED_DEPARTMENT T 

LEFT JOIN EED_DEPARTMENT CT 

ON T.ID=CT.PID 

GROUP BY T.TYPE,T.VALID_FLAG 

HAVING 1=1 --HAVING 1=1的使用 

AND T.VALID_FLAG=1 

)T 

WHERE 1=1 --WHERE 1=1的使用 

WHEREHOLDER --WHEREHOLDER的使用

• 6 可选WHERE条件
  condition2 为条件的名称，同时支持单行、多行模式，在平台中直接配置【可选where名称】和【启用条件】即可调用 
• 1 单行模式： 
• WHERE_condition1= and f1 = 3 
• 2 多行模式： 
• WHERE_BEGIN_condtion2 
• and f2 = 5 
• and f3 = 6 
• WHERE_END 
• 注意：
• 出现多个 select 采用第一个select作为 字段解析
• 出现多个 from 采用第一个from
• 出现多个where 采取最后一个
• 出现多个group by 采取最后一个
• 出现多个having 采取最后一个
• 出现多个order by采取最后一个（order by还有个注意点，就是要写在一行里，不能换行，否则也会解析错误）
  上面说的这些情况都是指最外层的，子查询中如果出现冲突，要采取不换行的方式，就是关键字写在上一行里
  保证解析出来的 上面的关键字，都是最外层的

二、开发代码中对SQL的规范

• 1 EXEC_TYPE
  指定SQL执行类型，不写时默认为Auto,支持Anto,RawSelect,Raw模式

  1 Auto:系统自动分析条件的插入位置，生成count sql 
• 2 RawSelect:系统按照配置的模式执行，不会进行任何优化，如果没有指定SQL_COUNT语句，系统会自动的进行包裹进行count(count时会自动去掉order by 部分) 
• 3 Raw:系统不会进行任何自动化分析，包裹count时，也不会自动的去掉where 
• 注意：当不包含FROMHOLDER/WHEREHOLDE时
  1 系统自动分析FROM的规则：第一个 首行以 FROM（不区分大小写） 开头的子句，就是from开始，在count的时候，系统会自动把 select 和 from 之间的部分替换为 count(1);
  2 系统自动分析 WHERE 的规则：最后一个以 where 1=1 的行未 where 子句，系统会自动在后面插入相关的where子句

• 2 SQL_COUNT
  占位符，表示下面的sql是数量查询的sql。当分页自动生成的count(总条数)性能不好或错误的时候，手动优化用。

• 3 分页参数
  以下为内置的分页参数，不可被占用

  1 esStartRow：开始的行号（包含） 
• 2 esEndRow：结束的行号（包含） 
• 3 esPageSize：分页大小(从1开始不得超过100) 
• 4 esPageIndex：页码（从1开始）

• 4 关键字
  编写SQL语句不能把列as成以下关键字

  1 esrnw_cfab 
• 2 subgrid 
• 3 cb 
• 4 rn

• 5 可选where条件：EnabledWhere(string key)

  1 以WHERE_开头，注意全部大小写； 
• 2 可通过SetParam的方式设置参数值，通过EnabledWhere('xxx')启用该条件，key为条件的名称 
• 3 多行模式：
• WHERE_BEGIN_condtion2 
• and f2 = 5 
• and f3 = 6 
• WHERE_END 
• 4 单行模式：
• WHERE_condition1= and f1 = 3

• 6 ESPrimaryKeyType
  实现自增功能

  [ESPrimaryKey(ESPrimaryKeyType.AutoIncrement, "ID")] 
• [ESPrimaryKey(ESPrimaryKeyType.AutoIncrement, "ID", "S_USERID")] 
• 注意：
  当oracle中存在自增列时，一定不要创建实现自增的触发器，平台会处理，可以自己创建。当oracle中序列没指定时，自动指定格式为“S表名列名”，如果长度超过30截取前30个字符。

三、ESDataBase.config 文件说明

• 1 不同数据库配置链接字符串示例

1 sql server: 

<add name="SQLSERVER" connectionString="Database=ESTRE_KT_20;server=.;uid=sa;pwd=***" providerName="MSSqlServer2008" showSql="true" schema="dbo" throwInitError="false" /> 

2 MYSQL: 

<add name="MYSQL" connectionString="Database=estre_kt_20;Data Source=localhost;User Id=root;Password=***;pooling=false;CharSet=utf8;port=3306" providerName="MySql55Inno" schema="estre_kt_20" showSql="true"/> 

3 SQLLITE: 

<add name="SQLLITE" dbFilePath="~/db/estre_kt_20.db" connectionString="Pooling=true;FailIfMissing=false" providerName="SQLite" schema="" showSql="true" /> 

4 POSTGRESQL: 

<add name="POSTGRESQL" connectionString="Host=localhost;Port=5432;Username=postgres;Password=***;Database=ESTRE_KT_20;" providerName="PostgreSql" schema="public" showSql="true"/> 

5 ORACLE: 

<add name="ORACLE" connectionString="Data Source=ORCL;User Id=ESTEST;Password=***;" providerName="Oracle10g" schema="ESTEST" showSql="true"/>

 

说明：
1 showSql:值为true/false,是否在日志中输出sql语句，用来在调试时排查错误，特殊注意，生产环境时要禁用！
2 schema:不同数据库schema配置方式如下
2.1 sql server:所有者(如:dbo)
2.2 mysql:数据库名(如：estre_kt_20)
2.3 sqlite:空
2.4 postgresql:模式(如：public)
2.5 oracle:所有者(如：ESTEST)
3 同一数据库可以配置多个实例名链接，此时需要采用ref的配置方式来防止有事务时数据库死锁的现象。

 

• 2 数据库与实例的对应 当平台库与系统库在一个数据库里，为了业务逻辑划分到一个实例的情况时使用 与直接配置两份的区别是，获取的ISession对象不同 如果配置不通过引用，通过配置链接字符串一样的情况下，获取到的 ISession 对象不同 如果通过引用的方式配置，获得到的ISession对象是一样的 ISession session = ESSessionFactoryManager.Instance.GetCurrentSession("test"); ISession session1 = ESSessionFactoryManager.Instance.GetCurrentSession("testRef1"); ISession session2 = ESSessionFactoryManager.Instance.GetCurrentSession("testSame"); 这时：session==session1；session!=session2

四、注意

• 1 针对mssqlserver varchar字段类型的优化

  平台新增了一个类型 ESAnsiString，调用SetParam 时转为该类型即可 例如： sqlQuery.SetParam("xyz", (ESAnsiString)"1");

• 2 AddDynamicWhere

  AddDynamicWhere(string field, ESDynamicWhereType whereType, object value)中当第二个参数为“ESDynamicWhereType.In”时，第三个参数要求为集合类型。 eg1： string[] list ={ "李勇", "钱小丽", "" };sqlQuery.AddDynamicWhere("sname", ESDynamicWhereType.In,list ); eg2： List<string> list = new List<string>() { "李勇", "钱小丽","" };sqlQuery.AddDynamicWhere("sname", ESDynamicWhereType.In,list );

• 3 limit

  对于mySQL最外层不得包含limit

• 4 setParam

  1 SQL语句中setParam的参数，即“:变量名”后边不得有SQL语句，如有必须换行; 2 setParam设置参数时，当传来的参数值为空时会报出异常，这样可以防止条件消失，整表更新或删除的问题。

• 5 like

  like和参数":参数名"之间必须有空格