您现在的位置是:网站首页> 内容页

CYQ.Data 支持 PostgreSQL 数据库

  • BBO娱乐
  • 2019-04-28
  • 31人已阅读
简介前言:很久之前,就有同学问我CYQ.Data能不能支持下PostgreSQL,之后小做了下调查,发现这个数据库用的人少,加上各种因素,就一直没动手。前两天,不小心看了一下Github上

前言:

很久之前,就有同学问我CYQ.Data能不能支持下PostgreSQL,之后小做了下调查,发现这个数据库用的人少,加上各种因素,就一直没动手。

前两天,不小心看了一下Github上的消息:

看到这个问题又被重新提了出来了,于是,闹吧!

下面分享一下支持该数据库要处理的过程,让大伙明白CYQ.Data要支持一种新的数据库,需要花多少功夫。

1、找到数据库的驱动程序:Npgsql.dll

网上查找了点相关知识,发现.NET 里操作PostgreSQL有两种提供的dll,一种是正规的收费的,另一种是开源的Npgsql.dll,因此这里选择了开源的。

在Nuget上可以搜索Npgsql,不过上面的版本要求依赖的版本很高,于是我找了最早的版本开始支持,毕竟CYQ.Data 是从支持最低2.0及以上的。

这里是找到的下载低版本支持的网址:http://pgfoundry.org/frs/?group_id=1000140&release_id=1889

同时,下载的两个2.0和4.0两个版本,也一并上传到:https://github.com/cyq1162/cyqdata/tree/master/文档

2、创建PostgreDal.cs,实现动态加载DLL

添加动态加载的代码:

using System;using System.Collections.Generic;using System.Text;using System.Reflection;using System.Data.Common;using CYQ.Data.Cache;using System.IO;namespace CYQ.Data{ internal class PostgreDal : DbBase { public PostgreDal(ConnObject co) : base(co) { } internal static Assembly GetAssembly() { object ass = CacheManage.LocalInstance.Get("Postgre_Assembly"); if (ass == null) { try { string name = string.Empty; if (File.Exists(AppConst.RunFolderPath + "Npgsql.dll")) { name = "Npgsql"; } else { name = "Can"t find the Npgsql.dll"; Error.Throw(name); } ass = Assembly.Load(name); CacheManage.LocalInstance.Set("Postgre_Assembly", ass, 10080); } catch (Exception err) { string errMsg = err.Message; Error.Throw(errMsg); } } return ass as Assembly; } protected override DbProviderFactory GetFactory(string providerName) { object factory = CacheManage.LocalInstance.Get("Postgre_Factory"); if (factory == null) { Assembly ass = GetAssembly(); factory = ass.GetType("Npgsql.NpgsqlFactory").GetField("Instance").GetValue(null); // factory = ass.CreateInstance("Npgsql.NpgsqlFactory.Instance"); if (factory == null) { throw new System.Exception("Can"t Create NpgsqlFactory in Npgsql.dll"); } else { CacheManage.LocalInstance.Set("Postgre_Factory", factory, 10080); } } return factory as DbProviderFactory; } protected override bool IsExistsDbName(string dbName) { try { IsAllowRecordSql = false; bool result = ExeScalar("select 1 from pg_catalog.pg_database where datname="" + dbName + """, false) != null; IsAllowRecordSql = true; return result; } catch { return true; } } public override char Pre { get { return ":"; } } public override void AddReturnPara() { } }}

几点说明:

1、GetFactory方法,其它dll框架提供的都是直接实例化,而Npgsql.dll提供却是单例属性,所以代码有点变化。2、Npgsql操作参数化的符号是“:”号。

3、DalCreate.cs追加PostgreSql类型及数据库链接解析

 

这里重点发现postgresql和mssql两者的数据库链接格式都一致:

server=...;uid=xxx;pwd=xxx;database=xxx;

因此从单纯的语句上,根本无法判断从属于哪种数据库。

经过小小的思考,解决方案出来了:

else { //postgre和mssql的链接语句一样,这里用database=和uid=顺序来决定;database写在后面的,为postgre int dbIndex = connString.IndexOf("database=", StringComparison.OrdinalIgnoreCase); int uid = connString.IndexOf("uid=", StringComparison.OrdinalIgnoreCase); if (uid > 0 && uid < dbIndex && File.Exists(AppConfig.RunPath + "Npgsql.dll")) { return PostgreClient; } return SqlClient; }

简的说:只有满足引用了npgsql.dll以及database写在uid之后两种条件下,判断为postgresql,其它的都回归到mssql。

4、处理表结构语句:获取数据库表以及表的结构语句:

这一块花的时间比较多,网上也费了点时间查了不少资料,最后自己写了语句:

获取数据库所有表:

internal static string GetPostgreTables(string dbName) { return string.Format("select table_name as TableName,cast(obj_description(relfilenode,"pg_class") as varchar) as Description from information_schema.tables t left join pg_class p on t.table_name=p.relname where table_schema="public" and table_catalog="{0}"", dbName); }

获取某表的结构:

internal static string GetPostgreColumns() { return @"selecta.attname AS ColumnName,case t.typname when "int4" then "int" when "int8" then "bigint" else t.typname end AS SqlType,coalesce(character_maximum_length,numeric_precision,-1) as MaxSize,numeric_scale as Scale,case a.attnotnull when "true" then 0 else 1 end AS IsNullable,case when position("nextval" in column_default)>0 then 1 else 0 end as IsAutoIncrement, case when o.conname is null then 0 else 1 end as IsPrimaryKey,d.description AS Description,i.column_default as DefaultValuefrom pg_class c left join pg_attribute a on c.oid=a.attrelidleft join pg_description d on a.attrelid=d.objoid AND a.attnum = d.objsubidleft join pg_type t on a.atttypid = t.oidleft join information_schema.columns i on i.table_schema="public" and i.table_name=c.relname and i.column_name=a.attnameleft join pg_constraint o on a.attnum = o.conkey[1] and o.contype="p"where c.relname =:TableNameand a.attnum > 0 and a.atttypid>0ORDER BY a.attnum"; }

5、处理关键字符号

由于PostgreSQL的大小写敏感,而且关键字加需要用双引号包含(这点和SQLite一致):

这里在原有的基础上加上case即可。

6、处理差异化的SQL语句:SqlCreate.cs

A、获取插入后的自增值,这里可以借用一下自增列产生的默认值:

这里用默认值,替换一下nextval序列为currval序列即可。

else if (_action.dalHelper.dalType == DalType.PostgreSQL) { string key = Convert.ToString(primaryCell.Struct.DefaultValue); if (!string.IsNullOrEmpty(key)) { key = key.Replace("nextval", "currval"); sql = sql + "; select " + key + " as OutPutValue"; } }

 B、需要引用关键字的地方:

略。。。。

7、处理分页语句:SqlCreateForPager.cs

这里PostgreSQL和分页和sqlite及mysql是一致的,因此只要在相关的地方补上case即可:

public static string GetSql(DalType dalType, string version, int pageIndex, int pageSize, object objWhere, string tableName, int rowCount, string columns, string primaryKey, bool primaryKeyIsIdentity) { if (string.IsNullOrEmpty(columns)) { columns = "*"; } pageIndex = pageIndex == 0 ? 1 : pageIndex; string where = SqlFormat.GetIFieldSql(objWhere); if (string.IsNullOrEmpty(where)) { where = "1=1"; } if (pageSize == 0) { return string.Format(top1Pager, columns, tableName, where); } if (rowCount > 0)//分页查询。 { where = SqlCreate.AddOrderBy(where, primaryKey); } int topN = pageIndex * pageSize;//Top N 最大数 int max = (pageIndex - 1) * pageSize; int rowStart = (pageIndex - 1) * pageSize + 1; int rowEnd = rowStart + pageSize - 1; string orderBy = string.Empty; if (pageIndex == 1 && dalType != DalType.Oracle)//第一页(oracle时 rownum 在排序条件为非数字时,和row_number()的不一样,会导致结果差异,所以分页统一用row_number()。) { switch (dalType) { case DalType.Access: case DalType.MsSql: case DalType.Sybase: return string.Format(top1Pager, "top " + pageSize + " " + columns, tableName, where); //case DalType.Oracle: // return string.Format(top1Pager, columns, tableName, "rownum<=" + pageSize + " and " + where); case DalType.SQLite: case DalType.MySql: case DalType.PostgreSQL: return string.Format(top1Pager, columns, tableName, where + " limit " + pageSize); } } else { switch (dalType) { case DalType.Access: case DalType.MsSql: case DalType.Sybase: int leftNum = rowCount % pageSize; int pageCount = leftNum == 0 ? rowCount / pageSize : rowCount / pageSize + 1;//页数 if (pageIndex == pageCount && dalType != DalType.Sybase) // 最后一页Sybase 不支持双Top order by { return string.Format(top2Pager, pageSize+" "+columns, "top " + (leftNum == 0 ? pageSize : leftNum) + " * ", tableName, ReverseOrderBy(where, primaryKey), GetOrderBy(where, false, primaryKey));//反序 } if ((pageCount > 1000 || rowCount > 100000) && pageIndex > pageCount / 2) // 页数过后半段,反转查询 { orderBy = GetOrderBy(where, false, primaryKey); where = ReverseOrderBy(where, primaryKey);//事先反转一次。 topN = rowCount - max;//取后面的 int rowStartTemp = rowCount - rowEnd; rowEnd = rowCount - rowStart; rowStart = rowStartTemp; } break; } } switch (dalType) { case DalType.MsSql: case DalType.Oracle: if (version.StartsWith("08")) { goto temtable; // goto top3;//sql 2000 } int index = tableName.LastIndexOf(")"); if (index > 0) { tableName = tableName.Substring(0, index + 1); } string v = dalType == DalType.Oracle ? "" : " v"; string onlyWhere = "where " + SqlCreate.RemoveOrderBy(where); onlyWhere = SqlFormat.RemoveWhereOneEqualsOne(onlyWhere); return string.Format(rowNumberPager, GetOrderBy(where, false, primaryKey), (columns == "*" ? "t.*" : columns), tableName, onlyWhere, v, rowStart, rowEnd); case DalType.Sybase: temtable: if (primaryKeyIsIdentity) { bool isOk = columns == "*"; if (!isOk) { string kv = SqlFormat.NotKeyword(primaryKey); string[] items = columns.Split(","); foreach (string item in items) { if (string.Compare(SqlFormat.NotKeyword(item), kv, StringComparison.OrdinalIgnoreCase) == 0) { isOk = true; break; } } } else { columns = "t.*"; index = tableName.LastIndexOf(")"); if (index > 0) { tableName = tableName.Substring(0, index + 1); } tableName += " t "; } if (isOk) { return string.Format(tempTablePagerWithIdentity, DateTime.Now.Millisecond, topN, primaryKey, tableName, where, pageSize, columns, rowStart, rowEnd, orderBy); } } return string.Format(tempTablePager, DateTime.Now.Millisecond, pageIndex * pageSize + " " + columns, tableName, where, pageSize, rowStart, rowEnd, orderBy); case DalType.Access: top3: if (!string.IsNullOrEmpty(orderBy)) // 反转查询 { return string.Format(top4Pager,columns, (rowCount - max > pageSize ? pageSize : rowCount - max), topN, tableName, where, GetOrderBy(where, true, primaryKey), GetOrderBy(where, false, primaryKey), orderBy); } return string.Format(top3Pager, (rowCount - max > pageSize ? pageSize : rowCount - max),columns, topN, tableName, where, GetOrderBy(where, true, primaryKey), GetOrderBy(where, false, primaryKey)); case DalType.SQLite: case DalType.MySql: case DalType.PostgreSQL: if (max > 500000 && primaryKeyIsIdentity && Convert.ToString(objWhere) == "" && !tableName.Contains(" "))//单表大数量时的优化成主键访问。 { where = string.Format("{0}>=(select {0} from {1} limit {2}, 1) limit {3}", primaryKey, tableName, max, pageSize); return string.Format(top1Pager, columns, tableName, where); } return string.Format(top1Pager, columns, tableName, where + " limit " + pageSize + " offset " + max); } return (string)Error.Throw("Pager::No Be Support:" + dalType.ToString()); }

总结:

一个数据库的基本支持、写到这里就完成了增删改查及分页。

当然,对于CYQ.Data而言,还差一些未处理:

1、多种数据库转换互通处理:DataType.cs。

2、对表的创建修改操作:SqlCreateForSchema.cs。

3、支持多数据库兼容性写法:SqlCompatible.cs。

4、其它细节。

 

, 1, 0, 9);

文章评论

Top