public virtual PrimaryKeyConstraintSchema GetNewPrimaryKeyConstraintSchema(string name)
{
PrimaryKeyConstraintSchema schema = new PrimaryKeyConstraintSchema(this);
schema.Name = name;
return(schema);
}
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 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);
}
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;
public PrimaryKeyConstraintSchema (PrimaryKeyConstraintSchema constraint)
: base (constraint)
{
}
private void AddConstraint (PrimaryKeyConstraintSchema pk)
{
store.AppendValues (pk.Name, String.Empty, pk);
}
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);
}
public PrimaryKeyConstraintSchema(PrimaryKeyConstraintSchema constraint)
: base(constraint)
{
}
public virtual PrimaryKeyConstraintSchema GetNewPrimaryKeyConstraintSchema (string name)
{
PrimaryKeyConstraintSchema schema = new PrimaryKeyConstraintSchema (this);
schema.Name = name;
return schema;
}
//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);
}
//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 void TogglePrimaryKey()
{
PrimaryKeyConstraintSchema pkConstraint = null;
PrimaryKeyConstraintSchema pkColumnConstraint = null;
PrimaryKeyConstraintSchema pkTemp = null;
bool tableLevel=false;
//Table have table, columna level or none primary key
if (columnModel.Parent is TableSchema) {
foreach (ConstraintSchema item in (columnModel.Parent as TableSchema).Constraints) {
if (item is PrimaryKeyConstraintSchema) {
pkTemp = item as PrimaryKeyConstraintSchema;
foreach (ColumnSchema column in item.Columns) {
if (column.Name == columnModel.Name) {
pkConstraint = item as PrimaryKeyConstraintSchema;
}
}
}
}
//Table have a column level primary key
int count=0;
foreach (ColumnSchema column in (columnModel.Parent as TableSchema).Columns) {
foreach (ConstraintSchema constraint in column.Constraints) {
if (constraint is PrimaryKeyConstraintSchema) {
if(pkColumnConstraint==null && column.Name!=columnModel.Name){
pkColumnConstraint=constraint as PrimaryKeyConstraintSchema;
count++;
}
}
}
}
if(count>1)
throw new NotImplementedException ();
}
if(pkTemp!=null && pkTemp.Columns.Count == 0){ //A pk without columns a table level isn't valid
(columnModel.Parent as TableSchema).Constraints.Remove (pkTemp);
pkTemp=null;
}
if(pkTemp!=null || pkConstraint!=null)
tableLevel=true;
if(tableLevel && pkColumnConstraint!=null)
throw new NotImplementedException ();
if(tableLevel){
//Remove this column because belong yet to table level pk
if(pkConstraint!=null){
pkConstraint.Columns.Remove (columnModel);
primaryKey = false;
Console.WriteLine("Remove a table level this column");
if (pkConstraint.Columns.Count == 1){ //only one column left then change to column level constraint
(columnModel.Parent as TableSchema).Constraints.Remove (pkConstraint);
pkConstraint.Columns[0].Constraints.Add(pkConstraint);
Console.WriteLine("Last column remain move from table level to column level a:"+pkConstraint.Columns[0].Name);
}
}else{ //Add this column because don't belong yet
primaryKey = true;
Console.WriteLine("Addforce a table level this column " + pkTemp.Columns.Count );
pkTemp.Columns.Add (ColumnModel);
pkTemp = null;
}
}else{
if (pkConstraint == null && pkTemp == null) {
Console.WriteLine("Taban ambos NULL en one pk column level");
pkConstraint = columnModel.Constraints.GetConstraint (ConstraintType.PrimaryKey) as PrimaryKeyConstraintSchema;
if (pkConstraint != null) {
Console.WriteLine("Quito uno solo a nivel de column");
primaryKey = false;
ColumnModel.Constraints.Remove (pkConstraint);
} else {
if(pkColumnConstraint!=null){
Console.WriteLine("Constraint a nivel de columna existe en otra col lo convierto a nivel de tabla");
primaryKey = true;
if(pkColumnConstraint.Columns.Count==1)
pkColumnConstraint.Columns[0].Constraints.Remove ((pkColumnConstraint as ConstraintSchema));
else
throw new NotImplementedException ();
pkColumnConstraint.Columns.Add (ColumnModel);
(columnModel.Parent as TableSchema).Constraints.Add (pkColumnConstraint);
}else{
Console.WriteLine("Nuevo Constraint a nivel de esta columna");
primaryKey = true;
pkConstraint = new PrimaryKeyConstraintSchema (ColumnModel.SchemaProvider);
pkConstraint.Columns.Add (ColumnModel);
ColumnModel.Constraints.Add (pkConstraint);
}
}
}else{
throw new NotImplementedException ();
}
}
Console.WriteLine("Tipos dentro de la figura");
foreach(IFigure f in (tableFigureOwner as TableFigure).FiguresEnumerator){
Console.WriteLine("TIPO: " + f.GetType());
}
//TODO: fix when multiple fk between same tables
if(tableFigureOwner is TableFigure){
//TODO: VERY IMPORTANT KIND OPTIONALITY IS RIGHT NOW FIXED, FIND A WAY OF GET REAL VALUE
(tableFigureOwner as TableFigure).RefreshRelationships(true,false,tableFigureOwner as TableFigure,kindOptionality.optional,false,null);
}else if (tableFigureOwner==null){
throw new NotImplementedException ();
}
}
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;
