public IDictionary<string, ConstraintDefinition> GetConstraints()
{
IDictionary<string, ConstraintDefinition> Res = new Dictionary<string, ConstraintDefinition>();
//Claves foráneas
string SQL;
if (this.AccessMode == AccessModes.Npgsql) {
SQL = @"SELECT INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_NAME, INFORMATION_SCHEMA.TABLE_CONSTRAINTS.TABLE_NAME, INFORMATION_SCHEMA.TABLE_CONSTRAINTS.TABLE_NAME, INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_TYPE,
INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE.COLUMN_NAME, INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE.table_name AS REFERENCED_TABLE_NAME, INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE.column_name AS REFERENCED_COLUMN_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
ON INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_NAME=INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE.CONSTRAINT_NAME
AND INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_CATALOG=INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE.CONSTRAINT_CATALOG
WHERE INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_CATALOG='" + this.DbConnection.Database + @"'
AND INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_TYPE='FOREIGN KEY'
ORDER BY INFORMATION_SCHEMA.TABLE_CONSTRAINTS.TABLE_NAME";
} else {
SQL = @"SELECT INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_NAME, INFORMATION_SCHEMA.TABLE_CONSTRAINTS.TABLE_NAME, INFORMATION_SCHEMA.TABLE_CONSTRAINTS.TABLE_NAME, INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_TYPE,
INFORMATION_SCHEMA.KEY_COLUMN_USAGE.COLUMN_NAME, INFORMATION_SCHEMA.KEY_COLUMN_USAGE.REFERENCED_TABLE_NAME, INFORMATION_SCHEMA.KEY_COLUMN_USAGE.REFERENCED_COLUMN_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS, INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_NAME=INFORMATION_SCHEMA.KEY_COLUMN_USAGE.CONSTRAINT_NAME
AND INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_SCHEMA=INFORMATION_SCHEMA.KEY_COLUMN_USAGE.CONSTRAINT_SCHEMA
AND INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_SCHEMA='" + this.DbConnection.Database + @"'
AND INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_TYPE='FOREIGN KEY'
ORDER BY INFORMATION_SCHEMA.TABLE_CONSTRAINTS.TABLE_NAME, INFORMATION_SCHEMA.KEY_COLUMN_USAGE.ORDINAL_POSITION";
}
System.Data.DataTable Constraints = this.Select(SQL);
foreach (System.Data.DataRow Constraint in Constraints.Rows) {
switch (Constraint["CONSTRAINT_TYPE"].ToString().ToUpper()) {
case "FOREIGN KEY":
Data.ConstraintDefinition NewKey = new ConstraintDefinition(Constraint["TABLE_NAME"].ToString());
NewKey.Name = Constraint["CONSTRAINT_NAME"].ToString();
NewKey.Column = Constraint["COLUMN_NAME"].ToString();
NewKey.ReferenceTable = Constraint["REFERENCED_TABLE_NAME"].ToString();
NewKey.ReferenceColumn = Constraint["REFERENCED_COLUMN_NAME"].ToString();
if (Res.ContainsKey(NewKey.Name))
Res[NewKey.Name] = NewKey;
else
Res.Add(NewKey.Name, NewKey);
break;
default:
throw new NotImplementedException("GetConstraints: " + Constraint["CONSTRAINT_TYPE"].ToString().ToUpper() + " " + Constraint["CONSTRAINT_NAME"].ToString() + " no reconocida");
}
}
return Res;
}
public Data.TableStructure GetTableStructure(string tableName, bool withConstraints)
{
Data.TableStructure TableDef = new Data.TableStructure();
TableDef.Name = tableName;
//requiere INFORMATION_SCHEMA. No compatible con MySql < 5 ni PostgreSQL < 7.4
string Sql;
if (this.AccessMode == AccessModes.Npgsql)
Sql = "SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA='public' AND TABLE_CATALOG='" + this.DbConnection.Database + "' AND TABLE_NAME='" + tableName + "' ORDER BY ORDINAL_POSITION";
else
Sql = "SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA='" + this.DbConnection.Database + "' AND TABLE_NAME='" + tableName + "' ORDER BY ORDINAL_POSITION";
System.Data.DataTable Columnas = this.Select(Sql);
if (Columnas.Rows.Count == 0)
return TableDef;
foreach (System.Data.DataRow Columna in Columnas.Rows) {
Data.ColumnDefinition FieldDef = new Data.ColumnDefinition();
FieldDef.Name = Columna["COLUMN_NAME"].ToString();
FieldDef.FieldType = Lfx.Data.Types.FromSqlType(Columna["DATA_TYPE"].ToString());
switch (FieldDef.FieldType) {
case Lfx.Data.DbTypes.VarChar:
FieldDef.Lenght = System.Convert.ToInt32(Columna["CHARACTER_MAXIMUM_LENGTH"]);
break;
case Lfx.Data.DbTypes.Numeric:
FieldDef.Lenght = System.Convert.ToInt32(Columna["NUMERIC_PRECISION"]);
if (FieldDef.Lenght == 0)
FieldDef.Lenght = 15;
FieldDef.Precision = System.Convert.ToInt32(Columna["NUMERIC_SCALE"]);
if (FieldDef.Precision == 0)
FieldDef.Precision = 4;
break;
}
string COLUMN_TYPE = (this.AccessMode == AccessModes.Npgsql) ? "DATA_TYPE" : "COLUMN_TYPE";
if (Columna[COLUMN_TYPE].ToString().ToLower().IndexOf("unsigned") >= 0)
FieldDef.Unsigned = true;
if (Columna["IS_NULLABLE"].ToString() == "NO")
FieldDef.Nullable = false;
else
FieldDef.Nullable = true;
if (!(Columna["COLUMN_DEFAULT"] == null || Columna["COLUMN_DEFAULT"] is DBNull)) {
FieldDef.DefaultValue = Columna["COLUMN_DEFAULT"].ToString();
switch (FieldDef.FieldType) {
case DbTypes.Integer:
case DbTypes.SmallInt:
case DbTypes.MediumInt:
case DbTypes.Currency:
case DbTypes.Numeric:
if (Lfx.Types.Parsing.ParseDecimal(FieldDef.DefaultValue) == 0)
FieldDef.DefaultValue = "0";
break;
case DbTypes.DateTime:
if (FieldDef.DefaultValue == "0000-00-00 00:00:00")
FieldDef.DefaultValue = "NULL";
break;
}
//Quito castings de PostgreSQL
if (FieldDef.DefaultValue != null) {
if (FieldDef.DefaultValue.EndsWith("::character varying"))
FieldDef.DefaultValue = FieldDef.DefaultValue.Substring(0, FieldDef.DefaultValue.Length - 19);
else if (FieldDef.DefaultValue.EndsWith("::text"))
FieldDef.DefaultValue = FieldDef.DefaultValue.Substring(0, FieldDef.DefaultValue.Length - 6);
if (FieldDef.DefaultValue.StartsWith("'") && FieldDef.DefaultValue.EndsWith("'"))
FieldDef.DefaultValue = FieldDef.DefaultValue.Substring(1, FieldDef.DefaultValue.Length - 2); //Quito comillas
}
} else {
if (FieldDef.Nullable == false) {
// null es sin default value, "NULL" es default to NULL
FieldDef.DefaultValue = null;
} else {
switch (FieldDef.FieldType) {
case DbTypes.Text:
case DbTypes.Blob:
case DbTypes.DateTime:
// No pueden tener default value
FieldDef.DefaultValue = null;
break;
default:
FieldDef.DefaultValue = "NULL";
break;
}
}
}
//Es la clave autonumérica?
if (this.SqlMode == qGen.SqlModes.MySql && Columna["EXTRA"].ToString() == "auto_increment") {
FieldDef.FieldType = DbTypes.Serial;
} else if (this.AccessMode == AccessModes.Npgsql && Columna["COLUMN_DEFAULT"].ToString().IndexOf("nextval(") >= 0) {
FieldDef.FieldType = DbTypes.Serial;
}
if (this.SqlMode == qGen.SqlModes.MySql) {
//Particularidades de MySQL
if (Columna["COLUMN_KEY"].ToString() == "PRI")
FieldDef.PrimaryKey = true;
}
TableDef.Columns.Add(FieldDef.Name, FieldDef);
}
//Indices
if (this.AccessMode == AccessModes.Npgsql) {
/*
Sql = @"SELECT a.table_catalog, a.table_schema, a.table_name, a.constraint_name AS INDEX_NAME, a.constraint_type, array_to_string(array(SELECT column_name::varchar FROM information_schema.key_column_usage WHERE constraint_name = a.constraint_name ORDER BY ordinal_position), ', ') as column_list, c.table_name, c.column_name
FROM information_schema.table_constraints a
INNER JOIN information_schema.key_column_usage b
ON a.constraint_name = b.constraint_name
LEFT JOIN information_schema.constraint_column_usage c
ON a.constraint_name = c.constraint_name AND
a.constraint_type = 'FOREIGN KEY'
WHERE a.table_catalog='" + this.DbConnection.Database + @"' AND a.table_schema='public' AND a.table_name='" + tableName + @"'
GROUP BY a.table_catalog, a.table_schema, a.table_name,
a.constraint_name, a.constraint_type,
c.table_name, c.column_name
ORDER BY a.table_catalog, a.table_schema, a.table_name,
a.constraint_name"; */
Sql = @"SELECT pg_attribute.attname AS COLUMN_NAME, pg_attribute.attnum,
pg_class.relname AS TABLE_NAME,
(CASE pg_index.indisunique WHEN 't' THEN 0 ELSE 1 END) AS NON_UNIQUE,
(CASE pg_index.indisprimary WHEN 't' THEN 'PRIMARY KEY' ELSE '' END) AS CONSTRAINT_TYPE,
(SELECT relname FROM pg_class WHERE pg_class.oid=pg_index.indexrelid) AS INDEX_NAME,
pg_index.indexrelid,pg_class.relfilenode
FROM pg_index
LEFT JOIN pg_class
ON pg_index.indrelid = pg_class.oid
LEFT JOIN pg_attribute
ON pg_attribute.attrelid = pg_class.oid
AND pg_attribute.attnum = ANY(indkey)
WHERE pg_class.relname = '" + tableName + @"'";
} else {
Sql = @"SELECT NON_UNIQUE, INDEX_NAME, seq_in_index, COLUMN_NAME, collation, cardinality, sub_part, packed, nullable, index_type, comment
FROM information_schema.STATISTICS
WHERE table_schema = '" + this.DbConnection.Database + @"'
AND table_name = '" + tableName + @"'
ORDER BY index_name, seq_in_index";
}
System.Data.DataTable Indexes = this.Select(Sql);
if (Indexes.Rows.Count > 0) {
TableDef.Indexes = new Dictionary<string, IndexDefinition>();
foreach (System.Data.DataRow Index in Indexes.Rows) {
string IndexName = Index["INDEX_NAME"].ToString();
if (TableDef.Indexes.ContainsKey(IndexName)) {
//Es un índice existente... agrego el campo
string ColName = Index["COLUMN_NAME"].ToString();
TableDef.Indexes[IndexName].Columns.Add(ColName);
// Y marco la columna como primaria en la definición de la tabla
switch (this.AccessMode) {
case AccessModes.MySql:
if (IndexName.ToUpperInvariant() == "PRIMARY")
TableDef.Columns[ColName].PrimaryKey = true;
break;
case AccessModes.Npgsql:
if (Index["CONSTRAINT_TYPE"].ToString() == "PRIMARY KEY")
TableDef.Columns[ColName].PrimaryKey = true;
break;
}
} else {
//Es un índice nuevo
Data.IndexDefinition NewIndex = new IndexDefinition(tableName);
NewIndex.Name = Index["INDEX_NAME"].ToString();
NewIndex.Columns = new List<string>(Index["COLUMN_NAME"].ToString().Split(new string[] { "," }, StringSplitOptions.RemoveEmptyEntries));
NewIndex.Unique = System.Convert.ToInt32(Index["NON_UNIQUE"]) == 0;
switch (this.AccessMode) {
case AccessModes.MySql:
if (IndexName.ToUpperInvariant() == "PRIMARY")
NewIndex.Primary = true;
break;
case AccessModes.Npgsql:
if (Index["CONSTRAINT_TYPE"].ToString() == "PRIMARY KEY")
NewIndex.Primary = true;
break;
}
TableDef.Indexes.Add(NewIndex.Name, NewIndex);
if (NewIndex.Primary) {
// Pongo las columnas como primarias en la definición de la tabla
for (int i = 0; i < NewIndex.Columns.Count; i++) {
TableDef.Columns[NewIndex.Columns[i]].PrimaryKey = true;
}
}
}
}
}
if (withConstraints) {
//Claves foráneas
TableDef.Constraints = new Dictionary<string, ConstraintDefinition>();
System.Data.DataTable Constraints = this.Select(@"SELECT INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_NAME, INFORMATION_SCHEMA.TABLE_CONSTRAINTS.TABLE_NAME, INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_TYPE,
INFORMATION_SCHEMA.KEY_COLUMN_USAGE.COLUMN_NAME, INFORMATION_SCHEMA.KEY_COLUMN_USAGE.REFERENCED_TABLE_NAME, INFORMATION_SCHEMA.KEY_COLUMN_USAGE.REFERENCED_COLUMN_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS, INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_NAME=INFORMATION_SCHEMA.KEY_COLUMN_USAGE.CONSTRAINT_NAME
AND INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_SCHEMA=INFORMATION_SCHEMA.KEY_COLUMN_USAGE.CONSTRAINT_SCHEMA
AND INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_SCHEMA='" + this.DbConnection.Database + @"' AND INFORMATION_SCHEMA.TABLE_CONSTRAINTS.TABLE_NAME='" + tableName + @"'
AND INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_TYPE='FOREIGN KEY'
ORDER BY INFORMATION_SCHEMA.KEY_COLUMN_USAGE.ORDINAL_POSITION");
foreach (System.Data.DataRow Constraint in Constraints.Rows) {
switch (Constraint["CONSTRAINT_TYPE"].ToString().ToUpper()) {
case "FOREIGN KEY":
Data.ConstraintDefinition NewKey = new ConstraintDefinition(tableName);
NewKey.Name = Constraint["CONSTRAINT_NAME"].ToString();
NewKey.Column = Constraint["COLUMN_NAME"].ToString();
NewKey.ReferenceTable = Constraint["REFERENCED_TABLE_NAME"].ToString();
NewKey.ReferenceColumn = Constraint["REFERENCED_COLUMN_NAME"].ToString();
TableDef.Constraints.Add(NewKey.Name, NewKey);
break;
}
}
}
return TableDef;
}
