protected virtual string GetConstraintString(ConstraintSchema constraint, bool isTableConstraint)
{
StringBuilder sb = new StringBuilder();
sb.Append("CONSTRAINT ");
sb.Append(constraint.Name);
sb.Append(' ');
switch (constraint.ConstraintType)
{
case ConstraintType.PrimaryKey:
sb.Append("PRIMARY KEY ");
if (isTableConstraint)
{
sb.Append(GetColumnsString(constraint.Columns, true));
}
break;
case ConstraintType.Unique:
sb.Append("UNIQUE ");
if (isTableConstraint)
{
sb.Append(GetColumnsString(constraint.Columns, true));
}
break;
case ConstraintType.ForeignKey:
sb.Append("FOREIGN KEY ");
sb.Append(GetColumnsString(constraint.Columns, true));
sb.Append(" REFERENCES ");
ForeignKeyConstraintSchema fk = constraint as ForeignKeyConstraintSchema;
sb.Append(fk.ReferenceTable);
sb.Append(' ');
if (fk.ReferenceColumns != null)
{
sb.Append(GetColumnsString(fk.ReferenceColumns, true));
}
break;
case ConstraintType.Check:
sb.Append("CHECK (");
sb.Append((constraint as CheckConstraintSchema).Source);
sb.Append(")");
break;
default:
throw new NotImplementedException();
}
return(sb.ToString());
}
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 override ICollection <ConstraintSchema> GetTableConstraints(TableSchema table)
{
CheckConnectionState();
List <ConstraintSchema> constraints = new List <ConstraintSchema> ();
IDbCommand command = connectionProvider.CreateCommand(
"SELECT k.owner, k.table_name, k.constraint_name, " +
" k.constraint_type, k.status, k.validated " +
"FROM all_constraints k " +
"WHERE k.owner = '" + table.OwnerName + "' " +
"AND k.table_name = '" + table.Name + "' " +
"and k.constraint_type = 'P'"
);
using (command) {
using (IDataReader r = command.ExecuteReader()) {
while (r.Read())
{
ConstraintSchema constraint = null;
switch (r.GetString(4))
{
case "P":
default:
constraint = new PrimaryKeyConstraintSchema(this);
break;
}
constraint.Name = r.GetString(3);
constraint.Definition = "";
constraints.Add(constraint);
}
r.Close();
}
connectionProvider.Close(command.Connection);
}
return(constraints);
}
protected virtual string GetConstraintString(ConstraintSchema constraint)
{
//PRIMARY KEY [sort-order] [ conflict-clause ] [AUTOINCREMENT]
//UNIQUE [ conflict-clause ]
//CHECK ( expr )
//COLLATE collation-name
StringBuilder sb = new StringBuilder();
sb.Append("CONSTRAINT ");
sb.Append(constraint.Name);
sb.Append(' ');
switch (constraint.ConstraintType)
{
case ConstraintType.PrimaryKey:
//PrimaryKeyConstraintSchema pk = constraint as PrimaryKeyConstraintSchema;
sb.Append("PRIMARY KEY"); //TODO: auto inc + sort
break;
case ConstraintType.Unique:
//UniqueConstraintSchema u = constraint as UniqueConstraintSchema;
sb.Append("UNIQUE");
break;
case ConstraintType.Check:
CheckConstraintSchema chk = constraint as CheckConstraintSchema;
sb.Append("CHECK (");
sb.Append(chk.Source);
sb.Append(")");
break;
default:
throw new NotImplementedException();
}
return(sb.ToString());
}
protected ConstraintSchema(ConstraintSchema constraint)
: base(constraint)
{
isColumnConstraint = constraint.isColumnConstraint;
constraintType = constraint.constraintType;
}
protected ConstraintSchema (ConstraintSchema constraint)
: base (constraint)
{
isColumnConstraint = constraint.isColumnConstraint;
constraintType = constraint.constraintType;
}
//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);
}
protected virtual string GetConstraintString (ConstraintSchema constraint)
{
//PRIMARY KEY [sort-order] [ conflict-clause ] [AUTOINCREMENT]
//UNIQUE [ conflict-clause ]
//CHECK ( expr )
//COLLATE collation-name
StringBuilder sb = new StringBuilder ();
sb.Append ("CONSTRAINT ");
sb.Append (constraint.Name);
sb.Append (' ');
switch (constraint.ConstraintType) {
case ConstraintType.PrimaryKey:
//PrimaryKeyConstraintSchema pk = constraint as PrimaryKeyConstraintSchema;
sb.Append ("PRIMARY KEY"); //TODO: auto inc + sort
break;
case ConstraintType.Unique:
//UniqueConstraintSchema u = constraint as UniqueConstraintSchema;
sb.Append ("UNIQUE");
break;
case ConstraintType.Check:
CheckConstraintSchema chk = constraint as CheckConstraintSchema;
sb.Append ("CHECK (");
sb.Append (chk.Source);
sb.Append (")");
break;
default:
throw new NotImplementedException ();
}
return sb.ToString ();
}
protected virtual string GetConstraintString (ConstraintSchema constraint, bool isTableConstraint)
{
StringBuilder sb = new StringBuilder ();
sb.Append ("CONSTRAINT ");
sb.Append (constraint.Name);
sb.Append (' ');
switch (constraint.ConstraintType) {
case ConstraintType.PrimaryKey:
sb.Append ("PRIMARY KEY ");
if (isTableConstraint)
sb.Append (GetColumnsString (constraint.Columns, true));
break;
case ConstraintType.Unique:
sb.Append ("UNIQUE ");
if (isTableConstraint)
sb.Append (GetColumnsString (constraint.Columns, true));
break;
case ConstraintType.ForeignKey:
sb.Append ("FOREIGN KEY ");
sb.Append (GetColumnsString (constraint.Columns, true));
sb.Append (" REFERENCES ");
ForeignKeyConstraintSchema fk = constraint as ForeignKeyConstraintSchema;
sb.Append (fk.ReferenceTable);
sb.Append (' ');
if (fk.ReferenceColumns != null)
sb.Append (GetColumnsString (fk.ReferenceColumns, true));
break;
case ConstraintType.Check:
sb.Append ("CHECK (");
sb.Append ((constraint as CheckConstraintSchema).Source);
sb.Append (")");
break;
default:
throw new NotImplementedException ();
}
return sb.ToString ();