Yii2中mssql和sql server数据库如何使用
本篇文章给大家分享的是有关Yii2中mssql和sql server数据库如何使用，小编觉得挺实用的，因此分享给大家学习，希望大家阅读完这篇文章后可以有所收获，话不多说，跟着小编一起来看看吧。
1.首先找到yii2解析mssql的scheme：
vendor/yiisoft/yii2/db/mssql/Schema.php
2.更改解析代码：
⑴找到findColumns 的函数 大概在232行左右
⑵替代解析 代码如下 ：
/**
* 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 = << 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 = << 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.分析这段代码：
⑴获取数据库的版本：
$pdo = $this->db->getSlavePdo();
$version = explode('.', $pdo->getAttribute(\PDO::ATTR_SERVER_VERSION));
⑵根据版本解析：
if (intval($version[0]<9)) { }else{} ⑶解析sql2000： $sql = << 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 就是原来的解析方式 3.打开sql的数据库scheme的缓存，在db.php文件中添加 ：'enableSchemaCache'=>true 既可以。
以上就是Yii2中mssql和sql server数据库如何使用，小编相信有部分知识点可能是我们日常工作会见到或用到的。希望你能通过这篇文章学到更多知识。更多详情敬请关注行业资讯频道。
