public override ConstraintSchemaCollection GetTableConstraints(TableSchema table)
{
ConstraintSchemaCollection constraints = new ConstraintSchemaCollection();
using (IPooledDbConnection conn = connectionPool.Request()) {
using (IDbCommand command = conn.CreateCommand(string.Format(@"select
sysobjects.name,
sysobjects.xtype
from sysobjects
inner join sysobjects sysobjectsParents ON
sysobjectsParents.id = sysobjects.parent_obj
where
sysobjectsParents.name = '{0}' and
sysobjects.xtype in ('C', 'UQ', 'F','PK','CK')" ,
table.Name)))
try {
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 "C":
case "CK": //check constraint
constraint = new CheckConstraintSchema(this);
break;
case "UQ":
constraint = new UniqueConstraintSchema(this);
break;
default:
break;
}
if (constraint != null)
{
constraint.Name = r.GetString(0);
constraints.Add(constraint);
}
}
r.Close();
}
} catch (Exception e) {
QueryService.RaiseException(e);
}finally {
conn.Release();
}
}
return(constraints);
}
protected virtual void AddClicked(object sender, EventArgs e)
{
UniqueConstraintSchema uni = schemaProvider.GetNewUniqueConstraintSchema("uni_new");
int index = 1;
while (constraints.Contains(uni.Name))
{
uni.Name = "uni_new" + (index++);
}
constraints.Add(uni);
AddConstraint(uni);
EmitContentChanged();
}
protected virtual void AddClicked(object sender, EventArgs e)
{
ForeignKeyConstraintSchema fk = schemaProvider.GetNewForeignKeyConstraintSchema("fk_new");
int index = 1;
while (constraints.Contains(fk.Name))
{
fk.Name = "fk_new" + (index++);
}
constraints.Add(fk);
AddConstraint(fk);
EmitContentChanged();
}
protected virtual void AddClicked(object sender, System.EventArgs e)
{
PrimaryKeyConstraintSchema pk = schemaProvider.GetNewPrimaryKeyConstraintSchema("pk_new");
int index = 1;
while (constraints.Contains(pk.Name))
{
pk.Name = "pk_new" + (index++);
}
constraints.Add(pk);
AddConstraint(pk);
EmitContentChanged();
}
protected virtual void AddClicked(object sender, EventArgs e)
{
CheckConstraintSchema check = schemaProvider.GetNewCheckConstraintSchema("check_new");
int index = 1;
while (constraints.Contains(check.Name))
{
check.Name = "check_new" + (index++);
}
constraints.Add(check);
AddConstraint(check);
EmitContentChanged();
}
protected virtual void AddClicked(object sender, EventArgs e)
{
UniqueConstraintSchema uni = schemaProvider.CreateUniqueConstraintSchema(string.Concat(table.Name,
"_",
"uni_new"));
int index = 1;
while (constraints.Contains(uni.Name))
{
uni.Name = string.Concat(table.Name, "_", "uni_new", (index++).ToString());
}
constraints.Add(uni);
AddConstraint(uni);
EmitContentChanged();
}
protected virtual void AddClicked(object sender, EventArgs e)
{
ForeignKeyConstraintSchema fk = schemaProvider.CreateForeignKeyConstraintSchema(string.Concat(table.Name,
"_",
"fk_new"));
int index = 1;
while (constraints.Contains(fk.Name))
{
fk.Name = string.Concat(table.Name, "_", "fk_new", (index++).ToString());
}
constraints.Add(fk);
AddConstraint(fk);
EmitContentChanged();
}
public override ConstraintSchemaCollection GetColumnConstraints(TableSchema table, ColumnSchema column)
{
ConstraintSchemaCollection constraints = new ConstraintSchemaCollection();
using (IPooledDbConnection conn = connectionPool.Request()) {
using (IDbCommand command = conn.CreateCommand(String.Format("DESCRIBE {0}", table.Name))) {
try {
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 = CreatePrimaryKeyConstraintSchema("pk_" + column.Name);
}
else if (key.Contains("UNI"))
{
constraint = CreateUniqueConstraintSchema("uni_" + column.Name);
}
else
{
continue;
}
constraint.IsColumnConstraint = true;
constraint.OwnerName = r.GetString(0);
constraints.Add(constraint);
}
r.Close();
}
} catch (Exception e) {
QueryService.RaiseException(e);
} finally {
conn.Release();
}
}
}
return(constraints);
}
protected virtual void AddClicked(object sender, System.EventArgs e)
{
PrimaryKeyConstraintSchema pk =
schemaProvider.CreatePrimaryKeyConstraintSchema(
string.Concat(
table.Name,
"_",
AddinCatalog.GetString("pk_new")));
int index = 1;
while (constraints.Contains(pk.Name))
{
pk.Name = string.Concat(table.Name, "_", AddinCatalog.GetString("pk_new"), (index++).ToString());
}
constraints.Add(pk);
AddConstraint(pk);
EmitContentChanged();
}
protected virtual void AddClicked(object sender, EventArgs e)
{
CheckConstraintSchema check = schemaProvider.CreateCheckConstraintSchema(string.Concat(table.Name,
"_",
"check_new"));
int index = 1;
while (constraints.Contains(check.Name))
{
check.Name = string.Concat(table.Name,
"_",
"check_new",
(index++).ToString());
}
constraints.Add(check);
AddConstraint(check);
EmitContentChanged();
}
public override ConstraintSchemaCollection GetTableConstraints(TableSchema table)
{
ConstraintSchemaCollection constraints = new ConstraintSchemaCollection();
using (IPooledDbConnection conn = connectionPool.Request()) {
using (IDbCommand command = conn.CreateCommand(string.Concat("SHOW TABLE STATUS FROM `",
table.SchemaName, "`;"))) {
try {
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) {
// Don't raise error, if the table doesn't exists return an empty collection
} finally {
conn.Release();
}
}
}
return(constraints);
}
private void PkToggled(object sender, ToggledArgs args)
{
TreeIter iter;
if (storeColumns.GetIterFromString(out iter, args.Path))
{
bool val = (bool)storeColumns.GetValue(iter, colPKIndex);
storeColumns.SetValue(iter, colPKIndex, !val);
if (val)
{
// Remove Constraint
ColumnSchema column = storeColumns.GetValue(iter, colObjIndex) as ColumnSchema;
ConstraintSchema delConstraint = null;
foreach (ConstraintSchema c in constraints)
{
if (c is PrimaryKeyConstraintSchema)
{
foreach (ColumnSchema col in c.Columns)
{
if (col.Name == column.Name)
{
c.Columns.Remove(col);
delConstraint = c;
break;
}
}
}
}
// If PK doesn't have any columns, delete it.
if (delConstraint != null)
{
if (delConstraint.Columns.Count < 1)
{
constraints.Remove(delConstraint);
}
}
}
else
{
// Add Constraint
ColumnSchema column = storeColumns.GetValue(iter, colObjIndex) as ColumnSchema;
// Add the column for an existing PK
foreach (ConstraintSchema c in constraints)
{
if (c is PrimaryKeyConstraintSchema)
{
c.Columns.Add(column);
// Fire the Primary Key Changed Event to tell the other widget that "Primary Key Constraint"
// are changed in the Column Editor.
OnPrimaryKeyChanged(this, new EventArgs());
EmitContentChanged();
return;
}
}
PrimaryKeyConstraintSchema pk =
schemaProvider.CreatePrimaryKeyConstraintSchema(string.Concat(
table.Name, "_",
AddinCatalog.GetString("pk_new")
));
pk.Columns.Add(column);
constraints.Add(pk);
}
OnPrimaryKeyChanged(this, new EventArgs());
EmitContentChanged();
}
}
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;
}
//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 ();
using (IPooledDbConnection conn = connectionPool.Request ()) {
using (IDbCommand command = conn.CreateCommand (string.Concat ("SHOW TABLE STATUS FROM `",
table.SchemaName, "`;"))) {
try {
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) {
// Don't raise error, if the table doesn't exists return an empty collection
} finally {
conn.Release ();
}
}
}
return constraints;
}
public override ConstraintSchemaCollection GetColumnConstraints (TableSchema table, ColumnSchema column)
{
ConstraintSchemaCollection constraints = new ConstraintSchemaCollection ();
using (IPooledDbConnection conn = connectionPool.Request ()) {
using (IDbCommand command = conn.CreateCommand (String.Format ("DESCRIBE {0}", table.Name))) {
try {
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 = CreatePrimaryKeyConstraintSchema ("pk_" + column.Name);
else if (key.Contains ("UNI"))
constraint = CreateUniqueConstraintSchema ("uni_" + column.Name);
else
continue;
constraint.IsColumnConstraint = true;
constraint.OwnerName = r.GetString (0);
constraints.Add (constraint);
}
r.Close ();
}
} catch (Exception e) {
QueryService.RaiseException (e);
} finally {
conn.Release ();
}
}
}
return constraints;
}
public override ConstraintSchemaCollection GetTableConstraints (TableSchema table)
{
ConstraintSchemaCollection constraints = new ConstraintSchemaCollection ();
using (IPooledDbConnection conn = connectionPool.Request ()) {
using (IDbCommand command = conn.CreateCommand (string.Format (@"select
sysobjects.name,
sysobjects.xtype
from sysobjects
inner join sysobjects sysobjectsParents ON
sysobjectsParents.id = sysobjects.parent_obj
where
sysobjectsParents.name = '{0}' and
sysobjects.xtype in ('C', 'UQ', 'F','PK','CK')",
table.Name)))
try {
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 "C":
case "CK": //check constraint
constraint = new CheckConstraintSchema (this);
break;
case "UQ":
constraint = new UniqueConstraintSchema (this);
break;
default:
break;
}
if (constraint != null) {
constraint.Name = r.GetString (0);
constraints.Add (constraint);
}
}
r.Close ();
}
} catch (Exception e) {
QueryService.RaiseException (e);
} finally {
conn.Release ();
}
}
return constraints;
public override ConstraintSchemaCollection GetTableConstraints (TableSchema table)
{
ConstraintSchemaCollection constraints = new ConstraintSchemaCollection ();
using (IPooledDbConnection conn = connectionPool.Request ()) {
using (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 (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) {
// Don't raise error, if the table doesn't exists return an empty collection
} finally {
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;
}