public void Add (TableStructure table)
{
List.Add (table);
}
private void ProcessDataTableElement (XmlSchemaElement el)
{
string tableName = XmlConvert.DecodeName (el.QualifiedName.Name);
// If it is already registered, just ignore.
if (dataset.Tables.Contains (tableName))
return;
DataTable table = new DataTable (tableName);
table.Namespace = el.QualifiedName.Namespace;
currentTable = new TableStructure (table);
dataset.Tables.Add (table);
// Find Locale
if (el.UnhandledAttributes != null) {
foreach (XmlAttribute attr in el.UnhandledAttributes) {
if (attr.LocalName == "Locale" &&
attr.NamespaceURI == XmlConstants.MsdataNamespace)
table.Locale = new CultureInfo (attr.Value);
}
}
// Handle complex type (NOTE: It is or should be
// impossible the type is other than complex type).
XmlSchemaComplexType ct = (XmlSchemaComplexType) el.ElementType;
// Handle attributes
foreach (DictionaryEntry de in ct.AttributeUses)
ImportColumnAttribute ((XmlSchemaAttribute) de.Value);
// Handle content type particle
if (ct.ContentTypeParticle is XmlSchemaElement)
ImportColumnElement (el, (XmlSchemaElement) ct.ContentTypeParticle);
else if (ct.ContentTypeParticle is XmlSchemaGroupBase)
ImportColumnGroupBase (el, (XmlSchemaGroupBase) ct.ContentTypeParticle);
// else if null then do nothing.
// Handle simple content
switch (ct.ContentType) {
case XmlSchemaContentType.TextOnly:
// case XmlSchemaContentType.Mixed:
// LAMESPEC: When reading from XML Schema, it maps to "_text", while on the data inference, it is mapped to "_Text" (case ignorant).
string simpleName = el.QualifiedName.Name + "_text";
DataColumn simple = new DataColumn (simpleName);
simple.Namespace = el.QualifiedName.Namespace;
simple.AllowDBNull = (el.MinOccurs == 0);
simple.ColumnMapping = MappingType.SimpleContent;
simple.DataType = ConvertDatatype (ct.Datatype);
currentTable.NonOrdinalColumns.Add (simple);
break;
}
// add columns to the table in specified order
// (by msdata:Ordinal attributes)
SortedList sd = new SortedList ();
foreach (DictionaryEntry de in currentTable.OrdinalColumns)
sd.Add (de.Value, de.Key);
foreach (DictionaryEntry de in sd)
table.Columns.Add ((DataColumn) de.Value);
foreach (DataColumn dc in currentTable.NonOrdinalColumns)
table.Columns.Add (dc);
}
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;
}