public override ConstraintSchemaCollection GetTableConstraints(TableSchema table)
{
ConstraintSchemaCollection constraints = new ConstraintSchemaCollection();
IPooledDbConnection conn = connectionPool.Request();
IDbCommand command = conn.CreateCommand("select name, xtype from sysobjects where xtype in ('F','PK','CK')"); //TODO: unique
try {
using (command) {
using (IDataReader r = command.ExecuteReader()) {
while (r.Read())
{
ConstraintSchema constraint = null;
switch (r.GetString(1))
{
case "F": //foreign key
constraint = new ForeignKeyConstraintSchema(this);
break;
case "PK": //primary key
constraint = new PrimaryKeyConstraintSchema(this);
break;
case "CK": //check constraint
constraint = new CheckConstraintSchema(this);
break;
default:
break;
}
if (constraint != null)
{
constraint.Name = r.GetString(0);
constraints.Add(constraint);
}
}
r.Close();
}
}
} catch (Exception e) {
QueryService.RaiseException(e);
}
conn.Release();
return(constraints);
}
public override ConstraintSchemaCollection GetColumnConstraints(TableSchema table, ColumnSchema column)
{
ConstraintSchemaCollection constraints = new ConstraintSchemaCollection();
IPooledDbConnection conn = connectionPool.Request();
IDbCommand command = conn.CreateCommand(String.Format("DESCRIBE {0}", table.Name));
try {
using (command) {
using (IDataReader r = command.ExecuteReader()) {
while (r.Read())
{
if (r.IsDBNull(3) || String.Compare(r.GetString(0), column.Name, true) != 0)
{
continue;
}
string key = r.GetString(3).ToUpper();
ConstraintSchema constraint = null;
if (key.Contains("PRI"))
{
constraint = GetNewPrimaryKeyConstraintSchema("pk_" + column.Name);
}
else if (key.Contains("UNI"))
{
constraint = GetNewUniqueConstraintSchema("uni_" + column.Name);
}
else
{
continue;
}
constraint.IsColumnConstraint = true;
constraint.OwnerName = r.GetString(0);
constraints.Add(constraint);
}
r.Close();
};
}
} catch (Exception e) {
QueryService.RaiseException(e);
}
conn.Release();
return(constraints);
}
public virtual ConstraintSchemaCollection GetTableConstraints(TableSchema table)
{
ConstraintSchemaCollection collection = new ConstraintSchemaCollection();
IPooledDbConnection conn = connectionPool.Request();
try {
//restrictions: database, schema, table, name
DataTable dt = conn.GetSchema(foreignKeysCollectionString, null, connectionPool.ConnectionContext.ConnectionSettings.Database);
for (int r = 0; r < dt.Rows.Count; r++)
{
DataRow row = dt.Rows[r];
collection.Add(GetTableConstraint(row, table));
}
} catch (Exception e) {
QueryService.RaiseException(e);
}
conn.Release();
return(collection);
}
public override ConstraintSchemaCollection GetTableConstraints(TableSchema table)
{
ConstraintSchemaCollection constraints = new ConstraintSchemaCollection();
IPooledDbConnection conn = connectionPool.Request();
IDbCommand command = conn.CreateCommand("SHOW TABLE STATUS FROM `" + table.SchemaName + "`;");
try {
using (command) {
using (IDataReader r = command.ExecuteReader()) {
while (r.Read())
{
string[] chunks = ((string)r["Comment"]).Split(';');
//the values we are looking for are in the format (`table`) REFER `database\table2` (`table2`)
foreach (string chunk in chunks)
{
if (constraintRegex.IsMatch(chunk))
{
MatchCollection matches = constraintRegex.Matches(chunk);
ForeignKeyConstraintSchema constraint = new ForeignKeyConstraintSchema(this);
constraint.ReferenceTableName = matches[1].Groups[1].ToString();
constraint.Name = matches[0].Groups[1].ToString();
constraints.Add(constraint);
}
}
}
r.Close();
}
}
} catch (Exception e) {
QueryService.RaiseException(e);
}
conn.Release();
return(constraints);
}
//http://www.sqlite.org/pragma.html
public virtual ConstraintSchemaCollection GetConstraints(TableSchema table, ColumnSchema column)
{
if (table == null)
{
throw new ArgumentNullException("table");
}
string columnName = column == null ? null : column.Name;
ConstraintSchemaCollection constraints = new ConstraintSchemaCollection();
IPooledDbConnection conn = connectionPool.Request();
//fk and unique
IDbCommand command = conn.CreateCommand("SELECT name, tbl_name FROM sqlite_master WHERE sql IS NULL AND type = 'index'");
try {
using (command) {
using (IDataReader r = command.ExecuteReader()) {
while (r.Read())
{
ConstraintSchema constraint = null;
if (r.IsDBNull(1) || r.GetString(1) == null)
{
constraint = new UniqueConstraintSchema(this);
}
else
{
ForeignKeyConstraintSchema fkc = new ForeignKeyConstraintSchema(this);
fkc.ReferenceTableName = r.GetString(1);
constraint = fkc;
}
constraint.Name = r.GetString(0);
constraints.Add(constraint);
}
r.Close();
}
}
//pk, column
if (columnName != null)
{
command = conn.CreateCommand(
"PRAGMA table_info('" + table.Name + "')"
);
using (command) {
using (IDataReader r = command.ExecuteReader()) {
while (r.Read())
{
if (r.GetInt32(5) == 1 && r.GetString(1) == columnName)
{
PrimaryKeyConstraintSchema constraint = new PrimaryKeyConstraintSchema(this);
ColumnSchema priColumn = new ColumnSchema(this, table);
priColumn.Name = r.GetString(1);
constraint.Columns.Add(priColumn);
constraint.IsColumnConstraint = true;
constraint.Name = "pk_" + table.Name + "_" + priColumn.Name;
constraints.Add(constraint);
}
}
r.Close();
}
}
}
} catch (Exception e) {
QueryService.RaiseException(e);
}
conn.Release();
return(constraints);
}
public override ConstraintSchemaCollection GetTableConstraints(TableSchema table)
{
ConstraintSchemaCollection constraints = new ConstraintSchemaCollection();
IPooledDbConnection conn = connectionPool.Request();
IDbCommand command = conn.CreateCommand(String.Format(
"SELECT "
+ "pc.conname, "
+ "pg_catalog.pg_get_constraintdef(pc.oid, true) AS consrc, "
+ "pc.contype, "
+ "CASE WHEN pc.contype='u' OR pc.contype='p' THEN ( "
+ " SELECT "
+ " indisclustered "
+ " FROM "
+ " pg_catalog.pg_depend pd, "
+ " pg_catalog.pg_class pl, "
+ " pg_catalog.pg_index pi "
+ " WHERE "
+ " pd.refclassid=pc.tableoid "
+ " AND pd.refobjid=pc.oid "
+ " AND pd.objid=pl.oid "
+ " AND pl.oid=pi.indexrelid "
+ ") ELSE "
+ " NULL "
+ "END AS indisclustered "
+ "FROM "
+ "pg_catalog.pg_constraint pc "
+ "WHERE "
+ "pc.conrelid = (SELECT oid FROM pg_catalog.pg_class WHERE relname='{0}' "
+ " AND relnamespace = (SELECT oid FROM pg_catalog.pg_namespace "
+ " WHERE nspname='{1}')) "
+ "ORDER BY "
+ "1;", table.Name, table.SchemaName
));
try {
using (command) {
using (IDataReader r = command.ExecuteReader()) {
while (r.Read())
{
ConstraintSchema constraint = null;
//TODO: Add support for Check constraints.
switch (r.GetString(2))
{
case "f":
string match = @".*REFERENCES (.+)\(.*\).*";
constraint = new ForeignKeyConstraintSchema(this);
if (Regex.IsMatch(r.GetString(1), match))
{
(constraint as ForeignKeyConstraintSchema).ReferenceTableName
= Regex.Match(r.GetString(1), match).Groups[0].Captures[0].Value;
}
break;
case "u":
constraint = new UniqueConstraintSchema(this);
break;
case "p":
default:
constraint = new PrimaryKeyConstraintSchema(this);
break;
}
constraint.Name = r.GetString(0);
constraint.Definition = r.GetString(1);
int parenOpen = constraint.Definition.IndexOf('(');
if (parenOpen > 0)
{
int parenClose = constraint.Definition.IndexOf(')');
string colstr = constraint.Definition.Substring(parenOpen + 1, parenClose - parenOpen - 1);
foreach (string col in colstr.Split(','))
{
ColumnSchema column = new ColumnSchema(this, table);
column.Name = col.Trim();
constraint.Columns.Add(column);
}
}
constraints.Add(constraint);
}
r.Close();
}
}
} catch (Exception e) {
QueryService.RaiseException(e);
}
conn.Release();
return(constraints);
}