CYQ.Data 支持 PostgreSQL 数据库

前言：

很久之前，就有同学问我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 @"select
a.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 DefaultValue
from pg_class c 
left join pg_attribute a on c.oid=a.attrelid
left join pg_description d on a.attrelid=d.objoid AND a.attnum = d.objsubid
left join pg_type t on a.atttypid = t.oid
left join information_schema.columns i on i.table_schema='public' and i.table_name=c.relname and i.column_name=a.attname
left join pg_constraint o on a.attnum = o.conkey[1] and o.contype='p'
where c.relname =:TableName
and a.attnum > 0 and a.atttypid>0
ORDER 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、其它细节。