internal override string GetForeignKeyForeignTableName(string tableName, string constraintName)
{
var schemaName = GetSchema(tableName);
return(Dapper.ExecuteScalar <string>(
@"
SELECT
KCU2.TABLE_NAME
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS RC
JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU1 ON
KCU1.CONSTRAINT_NAME = RC.CONSTRAINT_NAME AND
KCU1.TABLE_NAME = RC.TABLE_NAME AND
KCU1.CONSTRAINT_CATALOG = RC.CONSTRAINT_CATALOG AND
KCU1.CONSTRAINT_SCHEMA = RC.CONSTRAINT_SCHEMA
JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU2 ON
KCU2.CONSTRAINT_NAME = UNIQUE_CONSTRAINT_NAME AND
KCU2.TABLE_NAME = RC.REFERENCED_TABLE_NAME AND
KCU2.CONSTRAINT_CATALOG = UNIQUE_CONSTRAINT_CATALOG AND
KCU2.CONSTRAINT_SCHEMA = UNIQUE_CONSTRAINT_SCHEMA
WHERE
RC.CONSTRAINT_NAME = @constraintName AND
RC.CONSTRAINT_SCHEMA = @schemaName AND
KCU1.TABLE_NAME = @tableName
ORDER BY
KCU2.ORDINAL_POSITION
",
new { constraintName, tableName, schemaName }));
}
internal override bool GetColumnAutoIncrement(string tableName, string columnName)
{
const string Suffix = "_seq";
var maxLength = MaxIdentifierLength - Suffix.Length;
var sequenceName = $"{StringHelper.Truncate($"{tableName}_{columnName}", maxLength)}{Suffix}";
return(Dapper.ExecuteScalar <int>("SELECT COUNT(*) FROM pg_class WHERE relkind = 'S' AND relname = @sequenceName", new { sequenceName }) == 1);
}
internal override string GetSchema(string tableName)
{
if (tableName == null || tableName.Contains('.') == false)
{
return(Dapper.ExecuteScalar <string>("SELECT SCHEMA_NAME()"));
}
return(tableName.Split('.').Last());
}
internal override string[] GetForeignKeyNames(string tableName)
{
var createTableSqlStatement = Dapper.ExecuteScalar <string>("SELECT sql FROM sqlite_master WHERE tbl_name = @tableName", new { tableName });
var matches = CreateTableSqlParser.Matches(createTableSqlStatement);
return(matches
.Cast <Match>()
.Select(match => UnescapeIdentifier(match.Groups[1].Value))
.ToArray());
}
internal override bool GetColumnAutoIncrement(string tableName, string columnName)
{
return(Dapper.ExecuteScalar <bool>(
@"
SELECT
EXTRA LIKE '%auto_increment%'
FROM INFORMATION_SCHEMA.COLUMNS WHERE
TABLE_NAME = @tableName AND
COLUMN_NAME = @columnName
",
new { tableName, columnName }));
}
internal override string[] GetForeignKeyForeignColumnNames(string tableName, string foreignKeyName)
{
var createTableSqlStatement = Dapper.ExecuteScalar <string>("SELECT sql FROM sqlite_master WHERE tbl_name = @tableName", new { tableName });
var matches = CreateTableSqlParser.Matches(createTableSqlStatement);
return(matches
.Cast <Match>()
.Where(match => UnescapeIdentifier(match.Groups[1].Value) == foreignKeyName)
.SelectMany(match => match.Groups[4].Value.Split(',').Select(columnName => UnescapeIdentifier(columnName.Trim())))
.ToArray());
}
internal override bool GetColumnAutoIncrement(string tableName, string columnName)
{
return(Dapper.ExecuteScalar <bool>(
@"
SELECT
is_identity
FROM sys.columns WHERE
object_id = OBJECT_ID(@tableName) AND
name = @columnName
",
new { tableName, columnName }));
}
internal override bool GetIndexType(string tableName, string indexName)
{
return(Dapper.ExecuteScalar <bool>(
@"
SELECT
is_unique
FROM sys.indexes
WHERE
name = @indexName AND
object_id = OBJECT_ID(@tableName) AND
[type] = 2
",
new { indexName, tableName }));
}
internal override bool GetIndexType(string tableName, string indexName)
{
var schemaName = GetSchema(tableName);
return(Dapper.ExecuteScalar <bool>(
@"
SELECT DISTINCT
1 - NON_UNIQUE AS IS_UNIQUE
FROM INFORMATION_SCHEMA.STATISTICS WHERE
INDEX_NAME = @indexName AND
TABLE_NAME = @tableName AND
TABLE_SCHEMA = @schemaName
",
new { indexName, tableName, schemaName }));
}
internal override bool GetIndexType(string tableName, string indexName)
{
return(Dapper.ExecuteScalar <bool>(
@"
SELECT
ix.indisunique
FROM
pg_class ic,
pg_index ix
WHERE
ic.relname = @indexName AND
ic.oid = ix.indexrelid
",
new { indexName }));
}
