Yii2如何使用mssql(sql server)数据库(包括sql2000)

博客 · 本文由 · 谁偷了汝的凶兆 · 发布于 29天前 · 244 阅读

有些旧版的cms是asp.net做的,而我们现在需要把用到它使用的数据库,那么问题来了,目前yii2支持sql只到2008,如何使用mssql连接更早版本的sql呢,这时候我我们就需要对yii2的mssql里的schema进行修改,使他支持2000的sql。开始我们的教程吧:

1.首先找到yii2解析mssql的scheme:

vendor/yiisoft/yii2/db/mssql/Schema.php

2.更改解析代码:

1> 找到findColumns 的函数 大概在232行左右

2>替代解析 代码如下 :

 /**
         * Collects the metadata of table columns.
         * @param TableSchema $table the table metadata
         * @return boolean whether the table exists in the database
         */
        protected function findColumns($table)
        {
            $pdo = $this->db->getSlavePdo();
            $version = explode('.', $pdo->getAttribute(\PDO::ATTR_SERVER_VERSION));
            //var_dump($version);exit;
            if (intval($version[0]<9)) {
                $sql = <<<SQL2000
    SELECT 
        column_name     = a.name,
        is_identity       = case when exists(SELECT 1 FROM sysobjects where xtype='PK' and parent_obj=a.id and name in (
                         SELECT name FROM sysindexes WHERE indid in(
                            SELECT indid FROM sysindexkeys WHERE id = a.id AND colid=a.colid))) then 1 else 0 end,
        data_type       =  case 
                            when b.name in ('ntext','datetime') then b.name 
                            when isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0)>0 then 
                            b.name + '(' + convert(varchar(20), COLUMNPROPERTY(a.id,a.name,'PRECISION')) + ','+convert(varchar(10), COLUMNPROPERTY(a.id,a.name,'Scale'))+')'
                            else b.name + '(' + convert(varchar(20), COLUMNPROPERTY(a.id,a.name,'PRECISION')) + ')' end ,
        is_nullable     = case when a.isnullable=1 then 'Yes' else 'No' end,
        column_default     = isnull(e.text,''),
        comment   = isnull(g.[value],'')
    FROM 
        syscolumns a
    left join 
        systypes b 
    on 
        a.xusertype=b.xusertype
    inner join 
        sysobjects d 
    on 
        a.id=d.id  and d.xtype='U' and  d.name<>'dtproperties'
    left join 
        syscomments e 
    on 
        a.cdefault=e.id
    left join 
        sysproperties g 
    on 
        a.id=g.id and a.colid=g.smallid  
    left join 
        sysproperties f 
    on 
        d.id=f.id and f.smallid=0
    where 
        d.name='{$table->fullName}'
    order by 
        a.id,a.colorder
    SQL2000;
            }
            else {

            $columnsTableName = 'INFORMATION_SCHEMA.COLUMNS';
            $whereSql = "[t1].[table_name] = '{$table->name}'";
            if ($table->catalogName !== null) {
                $columnsTableName = "{$table->catalogName}.{$columnsTableName}";
                $whereSql .= " AND [t1].[table_catalog] = '{$table->catalogName}'";
            }
            if ($table->schemaName !== null) {
                $whereSql .= " AND [t1].[table_schema] = '{$table->schemaName}'";
            }
            $columnsTableName = $this->quoteTableName($columnsTableName);

            $sql = <<<SQL
    SELECT
        [t1].[column_name], [t1].[is_nullable], [t1].[data_type], [t1].[column_default],
        COLUMNPROPERTY(OBJECT_ID([t1].[table_schema] + '.' + [t1].[table_name]), [t1].[column_name], 'IsIdentity') AS is_identity,
        CONVERT(VARCHAR, [t2].[value]) AS comment
    FROM {$columnsTableName} AS [t1]
    LEFT OUTER JOIN [sys].[extended_properties] AS [t2] ON
        [t2].[minor_id] = COLUMNPROPERTY(OBJECT_ID([t1].[TABLE_SCHEMA] + '.' + [t1].[TABLE_NAME]), [t1].[COLUMN_NAME], 'ColumnID') AND
        OBJECT_NAME([t2].[major_id]) = [t1].[table_name] AND
        [t2].[class] = 1 AND
        [t2].[class_desc] = 'OBJECT_OR_COLUMN' AND
        [t2].[name] = 'MS_Description'
    WHERE {$whereSql}
    SQL;
            }
            try {
                $columns = $this->db->createCommand($sql)->queryAll();
                if (empty($columns)) {
                    return false;
                }
            } catch (\Exception $e) {
                return false;
            }
            foreach ($columns as $column) {
                $column = $this->loadColumnSchema($column);
                foreach ($table->primaryKey as $primaryKey) {
                    if (strcasecmp($column->name, $primaryKey) === 0) {
                        $column->isPrimaryKey = true;
                        break;
                    }
                }
                if ($column->isPrimaryKey && $column->autoIncrement) {
                    $table->sequenceName = '';
                }
                $table->columns[$column->name] = $column;
            }

            return true;
        }

3.分析这段代码:

1>获取数据库的版本:

 $pdo = $this->db->getSlavePdo();
  $version = explode('.', $pdo->getAttribute(\PDO::ATTR_SERVER_VERSION));

2>根据版本解析:

 if (intval($version[0]<9)) { }else{}

3> 解析sql2000:

  $sql = <<<SQL2000
    SELECT 
        column_name     = a.name,
        is_identity       = case when exists(SELECT 1 FROM sysobjects where xtype='PK' and parent_obj=a.id and name in (
                         SELECT name FROM sysindexes WHERE indid in(
                            SELECT indid FROM sysindexkeys WHERE id = a.id AND colid=a.colid))) then 1 else 0 end,
        data_type       =  case 
                            when b.name in ('ntext','datetime') then b.name 
                            when isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0)>0 then 
                            b.name + '(' + convert(varchar(20), COLUMNPROPERTY(a.id,a.name,'PRECISION')) + ','+convert(varchar(10), COLUMNPROPERTY(a.id,a.name,'Scale'))+')'
                            else b.name + '(' + convert(varchar(20), COLUMNPROPERTY(a.id,a.name,'PRECISION')) + ')' end ,
        is_nullable     = case when a.isnullable=1 then 'Yes' else 'No' end,
        column_default     = isnull(e.text,''),
        comment   = isnull(g.[value],'')
    FROM 
        syscolumns a
    left join 
        systypes b 
    on 
        a.xusertype=b.xusertype
    inner join 
        sysobjects d 
    on 
        a.id=d.id  and d.xtype='U' and  d.name<>'dtproperties'
    left join 
        syscomments e 
    on 
        a.cdefault=e.id
    left join 
        sysproperties g 
    on 
        a.id=g.id and a.colid=g.smallid  
    left join 
        sysproperties f 
    on 
        d.id=f.id and f.smallid=0
    where 
        d.name='{$table->fullName}'
    order by 
        a.id,a.colorder
    SQL2000;

4> else 就是原来的解析方式 3.打开sql的数据库scheme的缓存,在db.php文件中添加 :'enableSchemaCache'=>true 既可以

这样我们就能使用sql2000数据库了,快去试试吧

成为第一个本话题的爱慕者吧

评论数量:3

修改vendor里面的源码好像不太好吧,建议用其他方式

28天前 回复

你好我是微软的sql server2000的销售代表,麻烦您把sql server2000注册费用缴一下,否则我将代表微软中国向您提交律师函

27天前 回复

@加班中途去撩妹 您好 我是315 有人投诉您造假

23天前 回复