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; }