protected void FillForeignKeyData(DBSchemaForeignKey fk, DataRow dtFKRow, out DBSchemaIndex related_pk)
        {
            DataColumn catalog = GetColumn(dtFKRow.Table, "foreign_key_catalog", false);
            DataColumn schema  = GetColumn(dtFKRow.Table, "foreign_key_owner", false);
            DataColumn name    = GetColumn(dtFKRow.Table, "foreign_key_constraint_name", true);
            DataColumn table   = GetColumn(dtFKRow.Table, "foreign_key_table_name", false);

            DataColumn pk_table      = GetColumn(dtFKRow.Table, "primary_key_table_name", false);
            DataColumn pk_owner      = GetColumn(dtFKRow.Table, "primary_key_owner", false);
            DataColumn pk_constraint = GetColumn(dtFKRow.Table, "primary_key_constraint_name", false);

            fk.Catalog = GetColumnStringValue(dtFKRow, catalog);
            fk.Schema  = GetColumnStringValue(dtFKRow, schema);
            fk.Name    = GetColumnStringValue(dtFKRow, name);

            string fk_table_value = GetColumnStringValue(dtFKRow, table);

            fk.ForeignKeyTable = new DBSchemaItemRef(DBSchemaTypes.Table, fk.Schema, fk_table_value);

            string pk_table_value      = GetColumnStringValue(dtFKRow, pk_table);
            string pk_owner_value      = GetColumnStringValue(dtFKRow, pk_owner);
            string pk_constraint_value = GetColumnStringValue(dtFKRow, pk_constraint);


            related_pk = new DBSchemaIndex(pk_owner_value, pk_constraint_value);
            related_pk.TableReference = new DBSchemaItemRef(DBSchemaTypes.Table, pk_owner_value, pk_table_value);
            fk.PrimaryKeyTable        = related_pk.TableReference;
        }
Example #2
0
        /// <summary>
        /// Populates the anindex from the meta data dataRow
        /// </summary>
        /// <param name="anindex"></param>
        /// <param name="dataRow"></param>
        protected override void FillIndexData(DBSchemaIndex anindex, DataRow dataRow)
        {
            DataTable  dt     = dataRow.Table;
            DataColumn name   = GetColumn(dt, "INDEX_NAME", true);
            DataColumn pk     = GetColumn(dt, "PRIMARY_KEY", false);
            DataColumn unique = GetColumn(dt, "UNIQUE", false);
            DataColumn col    = GetColumn(dt, "COLUMN_NAME", true);
            DataColumn tbl    = GetColumn(dt, "TABLE_NAME", false);

            anindex.Name         = GetColumnStringValue(dataRow, name);
            anindex.IsPrimaryKey = GetColumnBoolValue(dataRow, pk);
            anindex.IsUnique     = GetColumnBoolValue(dataRow, unique);

            string table = GetColumnStringValue(dataRow, tbl);

            if (string.IsNullOrEmpty(table) == false)
            {
                anindex.TableReference = new DBSchemaItemRef(DBSchemaTypes.Table, table);
            }

            string column = GetColumnStringValue(dataRow, col);

            if (string.IsNullOrEmpty(column) == false)
            {
                anindex.Columns.Add(new DBSchemaIndexColumn(column));
            }
        }
Example #3
0
        protected override void FillIndexData(DBSchemaIndex anindex, DataRow dataRow)
        {
            DataColumn catalog = GetColumn(dataRow.Table, "index_catalog", false);
            DataColumn schema  = GetColumn(dataRow.Table, "index_schema", false);
            DataColumn name    = GetColumn(dataRow.Table, "index_name", true);
            //The SQLite Connection GetSchema() method may incorrectly report this as true
            DataColumn ixPK   = GetColumn(dataRow.Table, "primary_key", false);
            DataColumn unique = GetColumn(dataRow.Table, "unique", false);

            DataColumn tblcatalog = GetColumn(dataRow.Table, "table_catalog", false);
            DataColumn tblschema  = GetColumn(dataRow.Table, "table_schema", false);
            DataColumn tblname    = GetColumn(dataRow.Table, "table_name", true);

            anindex.Catalog = GetColumnStringValue(dataRow, catalog);
            anindex.Schema  = GetColumnStringValue(dataRow, schema);
            anindex.Name    = GetColumnStringValue(dataRow, name);

            DBSchemaItemRef tblref = new DBSchemaItemRef(DBSchemaTypes.Table,
                                                         GetColumnStringValue(dataRow, tblcatalog), GetColumnStringValue(dataRow, tblschema), GetColumnStringValue(dataRow, tblname));

            anindex.TableReference = tblref;

            anindex.IsPrimaryKey = GetColumnBoolValue(dataRow, ixPK, false);
            anindex.IsUnique     = GetColumnBoolValue(dataRow, unique, false);
        }
Example #4
0
        /// <summary>
        /// Loads specific information about an index
        /// </summary>
        /// <param name="con"></param>
        /// <param name="idxref"></param>
        /// <returns></returns>
        protected override DBSchemaIndex LoadAnIndex(DbConnection con, DBSchemaItemRef idxref)
        {
            DataTable     dtIdxs  = this.GetIndexData(con, idxref);
            DBSchemaIndex anindex = null;

            if (null != dtIdxs && dtIdxs.Rows.Count > 0)
            {
                anindex = new DBSchemaIndex();
                this.FillIndexData(anindex, dtIdxs.Rows[0]);
                if (dtIdxs.Rows.Count > 1)
                {
                    DataColumn col = GetColumn(dtIdxs, "COLUMN_NAME", true);
                    //DataColumn tbl = GetColumn(dtIdxs, "TABLE_NAME", false);

                    for (int i = 1; i < dtIdxs.Rows.Count; i++)
                    {
                        string column = GetColumnStringValue(dtIdxs.Rows[i], col);
                        if (string.IsNullOrEmpty(column) == false)
                        {
                            anindex.Columns.Add(new DBSchemaIndexColumn(column));
                        }
                    }
                }

                //DataTable dtIdxColumns = this.GetIndexColumns(con, idxref);
                //if (null != dtIdxColumns)
                //    this.FillIndexColuns(anindex, dtIdxColumns);
            }
            return(anindex);
        }
        protected override void FillIndexData(DBSchemaIndex anindex, DataRow dataRow)
        {
            DataColumn catalog = GetColumn(dataRow.Table, "CATALOG", false);
            DataColumn schema  = GetColumn(dataRow.Table, "OWNER", false);
            DataColumn name    = GetColumn(dataRow.Table, "INDEX_NAME", true);

            DataColumn tblcatalog = GetColumn(dataRow.Table, "TABLE_CATALOG", false);
            DataColumn tblschema  = GetColumn(dataRow.Table, "TABLE_OWNER", false);
            DataColumn tblname    = GetColumn(dataRow.Table, "TABLE_NAME", true);

            anindex.Catalog = GetColumnStringValue(dataRow, catalog);
            anindex.Schema  = GetColumnStringValue(dataRow, schema);
            anindex.Name    = GetColumnStringValue(dataRow, name);

            DBSchemaItemRef tblref = new DBSchemaItemRef(DBSchemaTypes.Table,
                                                         GetColumnStringValue(dataRow, tblcatalog), GetColumnStringValue(dataRow, tblschema), GetColumnStringValue(dataRow, tblname));

            anindex.TableReference = tblref;
        }
Example #6
0
        protected override void FillIndexData(DBSchemaIndex anindex, DataRow dataRow)
        {
            DataColumn catalog = GetColumn(dataRow.Table, "index_catalog", false);
            DataColumn schema  = GetColumn(dataRow.Table, "index_schema", false);
            DataColumn name    = GetColumn(dataRow.Table, "index_name", true);

            DataColumn tblcatalog = GetColumn(dataRow.Table, "table_catalog", false);
            DataColumn tblschema  = GetColumn(dataRow.Table, "table_schema", false);
            DataColumn tblname    = GetColumn(dataRow.Table, "table_name", true);
            DataColumn unique     = GetColumn(dataRow.Table, "unique", false);
            DataColumn pk         = GetColumn(dataRow.Table, "primary", false);

            anindex.Catalog      = GetColumnStringValue(dataRow, catalog);
            anindex.Schema       = GetColumnStringValue(dataRow, schema);
            anindex.Name         = GetColumnStringValue(dataRow, name);
            anindex.IsPrimaryKey = GetColumnBoolValue(dataRow, pk);
            anindex.IsUnique     = GetColumnBoolValue(dataRow, unique);


            DBSchemaItemRef tblref = new DBSchemaItemRef(DBSchemaTypes.Table,
                                                         GetColumnStringValue(dataRow, tblcatalog), GetColumnStringValue(dataRow, tblschema), GetColumnStringValue(dataRow, tblname));

            anindex.TableReference = tblref;
        }
Example #7
0
        //
        // Table overrides
        //

        #region protected override DBSchemaTable LoadATable(DbConnection con, DBSchemaItemRef tableref)
        /// <summary>
        /// Loads the info on a specific table
        /// </summary>
        /// <param name="con"></param>
        /// <param name="tableref"></param>
        /// <returns></returns>
        protected override DBSchemaTable LoadATable(DbConnection con, DBSchemaItemRef tableref)
        {
            DataTable dtTable = GetTableData(con, tableref);

            DBSchemaTable atable = null;

            if (null != dtTable && dtTable.Rows.Count > 0)
            {
                atable = new DBSchemaTable();
                this.FillTableData(atable, dtTable.Rows[0]);

                DataTable dtColumns = this.GetTableColumns(con, tableref);
                if (null != dtColumns)
                {
                    this.FillTableColumns(atable.Columns, dtColumns);
                }

                DBSchemaItemRefCollection idxs = new DBSchemaItemRefCollection();
                DBSchemaItemRefCollection fks  = new DBSchemaItemRefCollection();

                this.LoadForeignKeyRefs(con, fks, tableref);
                this.LoadIndexRefs(con, idxs, tableref);

                DBSchemaIndexCollection indexes = new DBSchemaIndexCollection();
                foreach (DBSchemaItemRef idx in idxs)
                {
                    DBSchemaIndex same;
                    DBSchemaIndex anindex = this.LoadAnIndex(con, idx);
                    if (indexes.TryGetIndex(idx, out same))
                    {
                        same.Columns.AddRange(anindex.Columns);
                    }
                    else
                    {
                        indexes.Add(anindex);
                    }
                }
                foreach (DBSchemaIndex idx in indexes)
                {
                    if (idx.IsPrimaryKey)
                    {
                        foreach (DBSchemaIndexColumn idxcol in idx.Columns)
                        {
                            DBSchemaTableColumn tblcol;
                            if (atable.Columns.TryGetColumn(idxcol.ColumnName, out tblcol))
                            {
                                tblcol.PrimaryKey = true;
                            }
                        }
                    }
                }
                DBSchemaForeignKeyCollection foreignkeys = new DBSchemaForeignKeyCollection();
                foreach (DBSchemaItemRef fk in fks)
                {
                    DBSchemaForeignKey aForeignKey = this.LoadAForeignKey(con, fk);
                    foreignkeys.Add(aForeignKey);
                }

                atable.ForeignKeys = foreignkeys;
                atable.Indexes     = indexes;
            }
            return(atable);
        }
Example #8
0
 /// <summary>
 /// Ignored
 /// </summary>
 /// <param name="anindex"></param>
 /// <param name="dtIdxColumns"></param>
 protected override void FillIndexColuns(DBSchemaIndex anindex, DataTable dtIdxColumns)
 {
     //Do nothing - this is done on the FillIndexData method
 }
        public override void GenerateCreateIndexScript(DBSchemaIndex schemaIndex)
        {
            if (null == schemaIndex)
            {
                throw new ArgumentNullException("schemaIndex");
            }

            if (string.IsNullOrEmpty(schemaIndex.Name))
            {
                throw new ArgumentNullException("schemaIndex.Name");
            }

            if (schemaIndex.Type != DBSchemaTypes.Index)
            {
                throw new ArgumentOutOfRangeException("schemaIndex.Type");
            }

            if (null == schemaIndex.TableReference)
            {
                throw new ArgumentNullException("schemaIndex.TableReference");
            }

            if (schemaIndex.Columns == null)
            {
                throw new ArgumentNullException("schemaIndex.Columns");
            }

            else if (schemaIndex.Columns.Count == 0)
            {
                throw new ArgumentOutOfRangeException("schemaIndex.Columns");
            }

            if (schemaIndex.IsPrimaryKey)
            {
                throw new NotSupportedException("Cannot create a primary key on a table using the CREATE INDEX statement with MySQL. Use the alter table methods instead");
            }

            this.BeginCreate(DBSchemaTypes.Index, schemaIndex.IsUnique ? "UNIQUE" : "");

            this.BeginIdentifier();
            this.WriteObjectName(schemaIndex.Name);
            this.EndIdentifier();
            this.WriteRawSQLString(" ON ");
            DBSchemaItemRef tbl = schemaIndex.TableReference;

            this.WriteSourceTable(tbl.Catalog, tbl.Schema, tbl.Name, null);
            this.BeginBlock();
            DBSchemaIndexColumnCollection sorted = schemaIndex.Columns;

            int index = 0;

            foreach (DBSchemaIndexColumn col in sorted)
            {
                if (string.IsNullOrEmpty(col.ColumnName))
                {
                    throw new ArgumentNullException("DBSchemaIndexColumn.Name");
                }

                this.BeginOrderClause(col.SortOrder);
                this.BeginIdentifier();
                this.WriteObjectName(col.ColumnName);
                this.EndIdentifier();
                this.EndOrderClause(col.SortOrder);

                index++;
                if (index < sorted.Count)
                {
                    this.WriteReferenceSeparator();
                }
            }

            this.EndBlock();
        }