CYQ.Data 支持 PostgreSQL 数据库

前言：

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

前两天，不小心看了一下github上的消息：

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

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

1、找到数据库的驱动程序：npgsql.dll

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

在nuget上可以搜索npgsql，不过上面的版本要求依赖的版本很高，于是我找了最早的版本开始支持，毕竟cyq.data 是从支持最低2.0及以上的。

这里是找到的下载低版本支持的网址：

同时，下载的两个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、其它细节。