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);
}
private void AddConstraint(PrimaryKeyConstraintSchema pk)
{
System.Text.StringBuilder pk_cols = new System.Text.StringBuilder();
foreach (ColumnSchema col in pk.Columns)
{
if (pk_cols.Length > 0)
{
pk_cols.Append(",");
}
pk_cols.Append(col.Name);
}
TreeIter iter = store.AppendValues(pk.Name, pk_cols.ToString(), pk);
}
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, 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 RemoveClicked(object sender, System.EventArgs e)
{
TreeIter iter;
if (listPK.Selection.GetSelected(out iter))
{
PrimaryKeyConstraintSchema pk = store.GetValue(iter, colObjIndex) as PrimaryKeyConstraintSchema;
if (MessageService.Confirm(
AddinCatalog.GetString("Are you sure you want to remove constraint '{0}'?", pk.Name),
AlertButton.Remove
))
{
store.Remove(ref iter);
constraints.Remove(pk);
EmitContentChanged();
}
}
}
public virtual void FillSchemaObjects()
{
TreeIter iter;
if (store.GetIterFirst(out iter))
{
do
{
PrimaryKeyConstraintSchema pk = store.GetValue(iter, colObjIndex) as PrimaryKeyConstraintSchema;
pk.Name = store.GetValue(iter, colNameIndex) as string;
string colstr = store.GetValue(iter, colColumnsIndex) as string;
string[] cols = colstr.Split(',');
foreach (string col in cols)
{
ColumnSchema column = columns.Search(col);
pk.Columns.Add(column);
}
table.Constraints.Add(pk);
} while (store.IterNext(ref iter));
}
}
public virtual void FillSchemaObjects()
{
TreeIter iter;
if (storeColumns.GetIterFirst(out iter))
{
do
{
ColumnSchema column = storeColumns.GetValue(iter, colObjIndex) as ColumnSchema;
column.Name = storeColumns.GetValue(iter, colNameIndex) as string;
column.DataTypeName = storeColumns.GetValue(iter, colTypeIndex) as string;
column.DataType.LengthRange.Default = int.Parse(storeColumns.GetValue(iter, colLengthIndex) as string);
column.IsNullable = (bool)storeColumns.GetValue(iter, colNullableIndex);
column.Comment = storeColumns.GetValue(iter, colCommentIndex) as string;
if ((bool)storeColumns.GetValue(iter, colPKIndex))
{
PrimaryKeyConstraintSchema pk = schemaProvider.GetNewPrimaryKeyConstraintSchema("pk_" + column.Name);
column.Constraints.Add(pk);
}
} while (storeColumns.IterNext(ref iter));
}
}
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;
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;
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();
}
}
/// <summary>
/// Get a collection of constraints within a a table.
/// </summary>
public override ConstraintSchema[] GetTableConstraints(TableSchema table)
{
if (IsOpen == false && Open () == false)
throw new InvalidOperationException ("Invalid connection");
ArrayList collection = new ArrayList ();
NpgsqlCommand command = new NpgsqlCommand ();
command.Connection = connection;
command.CommandText = 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);
NpgsqlDataReader r = command.ExecuteReader ();
while (r.Read ()) {
ConstraintSchema constraint = null;
// XXX: Add support for Check constraints.
switch (r.GetString(2)) {
case "f":
string match = @".*REFERENCES (.+)\(.*\).*";
constraint = new ForeignKeyConstraintSchema ();
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 ();
break;
case "p":
default:
constraint = new PrimaryKeyConstraintSchema ();
break;
}
constraint.Name = r.GetString (0);
constraint.Definition = r.GetString (1);
collection.Add (constraint);
}
r.Close ();
return (ConstraintSchema[]) collection.ToArray (typeof(ConstraintSchema));
}
private void AddConstraint(PrimaryKeyConstraintSchema pk)
{
store.AppendValues(pk.Name, String.Empty, pk);
}
//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);
}
/// <summary>
/// Get a collection of constraints within a a table.
/// </summary>
public override ConstraintSchema[] GetTableConstraints(TableSchema table)
{
if (IsOpen == false && Open () == false)
throw new InvalidOperationException ("Invalid connection.");
ArrayList collection = new ArrayList ();
OracleCommand command = new OracleCommand ();
command.Connection = connection;
command.CommandText =
"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'";
OracleDataReader r = command.ExecuteReader ();
while (r.Read ()) {
ConstraintSchema constraint = null;
switch (r.GetString(4)) {
case "P":
default:
constraint = new PrimaryKeyConstraintSchema();
break;
}
constraint.Name = r.GetString (3);
constraint.Definition = "";
collection.Add (constraint);
}
return (ConstraintSchema[]) collection.ToArray (typeof(ConstraintSchema));
}
//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;
}
