private void AddConstraint (UniqueConstraintSchema uni) { string colstr = GetColumnsString (uni.Columns); store.AppendValues (uni.Name, uni.IsColumnConstraint, colstr, uni); }
public virtual UniqueConstraintSchema GetNewUniqueConstraintSchema (string name) { UniqueConstraintSchema schema = new UniqueConstraintSchema (this); schema.Name = name; return schema; }
public UniqueConstraintSchema (UniqueConstraintSchema constraint) : base (constraint) { }
//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); }
public UniqueConstraintSchema(UniqueConstraintSchema constraint) : base(constraint) { }
//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;