public void LoadColumns(SqlDataConnection connection, params DBTable[] tables)
        {
            foreach (DBTable table in tables)
            {
                if (table.Name == "Categories" && table.Columns.Count == 0)
                {
                    DBColumn categoryIdColumn = new DBColumn {
                        Name = "CategoryID", ColumnType = DBColumnType.Int32
                    };
                    table.AddColumn(categoryIdColumn);
                    DBColumn categoryNameColumn = new DBColumn {
                        Name = "CategoryName", ColumnType = DBColumnType.String
                    };
                    table.AddColumn(categoryNameColumn);
                }
                if (table.Name == "Products" && table.Columns.Count == 0)
                {
                    DBColumn categoryIdColumn = new DBColumn {
                        Name = "CategoryID", ColumnType = DBColumnType.Int32
                    };
                    table.AddColumn(categoryIdColumn);
                    DBColumn productNameColumn = new DBColumn {
                        Name = "ProductName", ColumnType = DBColumnType.String
                    };
                    table.AddColumn(productNameColumn);

                    DBForeignKey foreignKey = new DBForeignKey(
                        new[] { categoryIdColumn },
                        "Categories",
                        CustomDBSchemaProvider.CreatePrimaryKeys("CategoryID"));
                    table.ForeignKeys.Add(foreignKey);
                }
            }
        }
Exemplo n.º 2
0
 static Action <ConnectionProviderSql> CreateForeighKey(XPMemberInfo xpMemberInfo)
 {
     return(sql => {
         var dbForeignKey = new DBForeignKey(new StringCollection {
             xpMemberInfo.Name
         }, xpMemberInfo.ReferenceType.TableName, new StringCollection {
             xpMemberInfo.ReferenceType.KeyProperty.Name
         });
         sql.CreateForeignKey(xpMemberInfo.Owner.Table, dbForeignKey);
     });
 }
Exemplo n.º 3
0
        public void LoadColumns(SqlDataConnection connection, params DBTable[] tables)
        {
            foreach (DBTable table in tables)
            {
                if (table.Name == "Categories" && table.Columns.Count == 0)
                {
                    DBColumn categoryIdColumn = new DBColumn {
                        Name = "CategoryID"
                    };
                    table.AddColumn(categoryIdColumn);
                    DBColumn categoryNameColumn = new DBColumn {
                        Name = "CategoryName"
                    };
                    table.AddColumn(categoryNameColumn);
                }
                if (table.Name == "Products" && table.Columns.Count == 0)
                {
                    DBColumn categoryIdColumn = new DBColumn {
                        Name = "CategoryID"
                    };
                    table.AddColumn(categoryIdColumn);
                    DBColumn supplierIdColumn = new DBColumn {
                        Name = "SupplierID"
                    };
                    table.AddColumn(supplierIdColumn);
                    DBColumn productNameColumn = new DBColumn {
                        Name = "ProductName"
                    };
                    table.AddColumn(productNameColumn);

                    DBForeignKey foreignKey1 = new DBForeignKey(
                        new[] { categoryIdColumn },
                        "Categories",
                        CustomDBSchemaProvider.CreatePrimaryKeys("CategoryID"));
                    table.ForeignKeys.Add(foreignKey1);
                    DBForeignKey foreignKey2 = new DBForeignKey(
                        new[] { supplierIdColumn },
                        "Suppliers",
                        CustomDBSchemaProvider.CreatePrimaryKeys("SupplierID"));
                    table.ForeignKeys.Add(foreignKey2);
                }
                if (table.Name == "Suppliers" && table.Columns.Count == 0)
                {
                    DBColumn supplierIdColumn = new DBColumn {
                        Name = "SupplierID"
                    };
                    table.AddColumn(supplierIdColumn);
                    DBColumn companyNameColumn = new DBColumn {
                        Name = "CompanyName"
                    };
                    table.AddColumn(companyNameColumn);
                }
            }
        }
Exemplo n.º 4
0
        TemplateType GetTemplateType(TemplateType refTemplateType, ClassGeneratorInfo classGeneratorInfo)
        {
            bool selfReference = classGeneratorInfo.DbTable.Name == _dbTable.Name;

            if (!selfReference)
            {
                DBForeignKey oneToOne = classGeneratorInfo.DbTable.ForeignKeys.FirstOrDefault(key => key.PrimaryKeyTable == _dbTable.Name);
                if (oneToOne != null)
                {
                    return(TemplateType.XPOneToOnePropertyMember);
                }
            }
            return(refTemplateType);
        }
Exemplo n.º 5
0
 public TableEditorReferenceEventArgs(DBForeignKey relation)
 {
     this.relation = relation;
 }
        /// <summary>
        /// Создание ссылки на другую таблицу
        /// </summary>
        /// <param name="table">Таблица со ссылкой</param>
        /// <param name="fk">Ссылка на другую таблицу</param>
        public override void CreateForeignKey(DBTable table, DBForeignKey fk)
        {
            // Если ссылка на таблицу с настраиваемой модификацией, то пропускаем
            DBTableEx foreignTableEx = XPDictionaryInformer.Schema.GetTable(fk.PrimaryKeyTable);
            if (foreignTableEx.IsCustom) return;

            // Если ссылка на таблицу из другой схемы, то необходимо дать разрешение
            string tableSchema = FormatRealSchemaName(table.Name);
            string foreignSchema = FormatRealSchemaName(fk.PrimaryKeyTable);
            if (tableSchema != foreignSchema)
            {
                string foreignTable = FormatTable(foreignSchema, ComposeSafeTableName(fk.PrimaryKeyTable));
                ExecuteSqlSchemaUpdate("ForeignKey", GetForeignKeyName(fk, table), table.Name,
                    String.Format(CultureInfo.InvariantCulture, "grant references on {0} to {1}", foreignTable, tableSchema));
            }

            base.CreateForeignKey(table, fk);
        }
 private bool IsForeignKeyExists(DBTable table, DBForeignKey foreignKey)
 {
     foreach (DBForeignKey fk in table.ForeignKeys)
     {
         if (string.Compare(ComposeSafeTableName(foreignKey.PrimaryKeyTable), ComposeSafeTableName(fk.PrimaryKeyTable), true) == 0 &&
             string.Compare(ComposeSafeSchemaName(foreignKey.PrimaryKeyTable), ComposeSafeSchemaName(fk.PrimaryKeyTable), true) == 0 &&
             IsColumnsEqual(fk.Columns, foreignKey.Columns) &&
             IsColumnsEqual(fk.PrimaryKeyTableKeyColumns, foreignKey.PrimaryKeyTableKeyColumns))
             return true;
     }
     return false;
 }
 private void GetForeignKeys(DBTable table)
 {
     string schema = ComposeSafeSchemaName(table.Name);
     string safeTableName = ComposeSafeTableName(table.Name);
     Query query;
     if (schema == string.Empty)
         query = new Query(
             "select tc.position, tc.column_name, fc.column_name, fc.table_name from user_constraints c " +
                 "inner join user_cons_columns tc on tc.constraint_name = c.constraint_name and tc.table_name = c.table_name " +
                 "inner join user_cons_columns fc on c.r_constraint_name = fc.constraint_name and tc.position = fc.position " +
             "where c.table_name = :p0 order by c.constraint_name, tc.position",
         new QueryParameterCollection(new OperandValue(safeTableName)), new string[] { ":p0" });
     else
         query = new Query(
             "select tc.position, tc.column_name, fc.column_name, fc.owner||'.'||fc.table_name from all_constraints c " +
                 "inner join all_cons_columns tc on tc.constraint_name = c.constraint_name and tc.owner = c.owner and tc.table_name = c.table_name " +
                 "inner join all_cons_columns fc on c.r_constraint_name = fc.constraint_name and tc.position = fc.position and fc.owner = c.r_owner " +
             "where c.owner = :p0 and c.table_name = :p1 order by c.constraint_name, tc.position",
         new QueryParameterCollection(new OperandValue(schema), new OperandValue(safeTableName)), new string[] { ":p0", ":p1" });
     SelectStatementResult data = SelectData(query);
     DBForeignKey fk = null;
     foreach (SelectStatementResultRow row in data.Rows)
     {
         if (Convert.ToDecimal(row.Values[0]) == decimal.One)
         {
             StringCollection pkc = new StringCollection();
             StringCollection fkc = new StringCollection();
             pkc.Add((string)row.Values[2]);
             fkc.Add((string)row.Values[1]);
             fk = new DBForeignKey(fkc, (string)row.Values[3], pkc);
             table.ForeignKeys.Add(fk);
         }
         else
         {
             fk.Columns.Add((string)row.Values[1]);
             fk.PrimaryKeyTableKeyColumns.Add((string)row.Values[2]);
         }
     }
 }
 /// <inheritdoc/>
 protected override string GetForeignKeyName(DBForeignKey cons, DBTable table)
 {
     if (cons.Name != null) return cons.Name;
     StringBuilder sb = new StringBuilder();
     sb.Append("fk_");
     sb.Append(ComposeSafeTableName(table.Name));
     foreach (string col in cons.Columns) sb.Append(col);
     return sb.ToString();
 }
        public PostOfficeClient()
        {
            this.DataStore = new InMemoryDataStore(AutoCreateOption.DatabaseAndSchema, false);
            this.Mappings  = new Dictionary <Type, DataStoreMapping>();
            var mAccount = new DataStoreMapping();

            mAccount.Table = new DBTable("Accounts");
            mAccount.Table.AddColumn(new DBColumn("UserName", true, null, 255, DBColumnType.String));
            mAccount.Table.AddColumn(new DBColumn("PublicName", false, null, 1024, DBColumnType.String));
            mAccount.Create = () => new Account();
            mAccount.Load   = (obj, values, omap) => {
                ((Account)obj).SetKey((string)values[0]);
                ((Account)obj).PublicName = (string)values[1];
            };
            mAccount.Save = (obj, values) => {
                values[0] = ((Account)obj).UserName;
                values[1] = ((Account)obj).PublicName;
            };
            mAccount.GetKey     = (obj) => ((Account)obj).UserName;
            mAccount.RefColumns = Enumerable.Empty <DataStoreMapping.Column>();
            Mappings.Add(typeof(Account), mAccount);
            var mMessage = new DataStoreMapping();

            mMessage.Table = new DBTable("Messages");
            var mMessageKey = new DBColumn("ID", true, null, 0, DBColumnType.Int32);

            mMessageKey.IsIdentity = true;
            mMessage.Table.AddColumn(mMessageKey);
            mMessage.Table.AddColumn(new DBColumn("Subject", false, null, 1024, DBColumnType.String));
            mMessage.Table.AddColumn(new DBColumn("Body", false, null, -1, DBColumnType.String));
            mMessage.Table.AddColumn(new DBColumn("Sender", false, null, 255, DBColumnType.String));
            mMessage.Table.AddColumn(new DBColumn("Recepient", false, null, 255, DBColumnType.String));
            mMessage.Table.PrimaryKey = new DBPrimaryKey(new object[] { mMessageKey });
            var fkSender = new DBForeignKey();

            fkSender.Columns.Add("Sender");
            fkSender.PrimaryKeyTable = "Accounts";
            fkSender.PrimaryKeyTableKeyColumns.Add("UserName");
            mMessage.Table.AddForeignKey(fkSender);
            var fkRecepient = new DBForeignKey();

            fkRecepient.Columns.Add("Recepient");
            fkRecepient.PrimaryKeyTable = "Accounts";
            fkRecepient.PrimaryKeyTableKeyColumns.Add("UserName");
            mMessage.Table.AddForeignKey(fkRecepient);
            mMessage.Create = () => new Message();
            mMessage.SetKey = (obj, key) => {
                ((Message)obj).SetKey((int)key);
            };
            mMessage.GetKey = (obj) => ((Message)obj).ID;
            mMessage.Load   = (obj, values, omap) => {
                var o = (Message)obj;
                o.SetKey((int)values[0]);
                o.Subject   = (string)values[1];
                o.Body      = (string)values[2];
                o.Sender    = GetReference <Account>(omap, values[3]);
                o.Recepient = GetReference <Account>(omap, values[4]);
            };
            mMessage.Save = (obj, values) => {
                var o = (Message)obj;
                values[0] = o.ID;
                values[1] = o.Subject;
                values[2] = o.Body;
                values[3] = o.Sender?.UserName;
                values[4] = o.Recepient?.UserName;
            };
            mMessage.RefColumns = new DataStoreMapping.Column[] {
                new DataStoreMapping.Column()
                {
                    Index = 3, Type = typeof(Account)
                },
                new DataStoreMapping.Column()
                {
                    Index = 4, Type = typeof(Account)
                }
            };
            Mappings.Add(typeof(Message), mMessage);
            DataStore.UpdateSchema(false, mAccount.Table, mMessage.Table);
            CreateDemoData((InMemoryDataStore)DataStore);
        }
Exemplo n.º 11
0
 static Action<ConnectionProviderSql> CreateForeighKey(XPMemberInfo xpMemberInfo) {
     return sql => {
         var dbForeignKey = new DBForeignKey(new StringCollection { xpMemberInfo.Name }, xpMemberInfo.ReferenceType.TableName, new StringCollection { xpMemberInfo.ReferenceType.KeyProperty.Name });
         sql.CreateForeignKey(xpMemberInfo.Owner.Table, dbForeignKey);
     };
 }
        public ActionResult AddForeignKey(string appName, DBForeignKey model)
        {
            DBApp app = new DBApp()
            {
                Name = appName,
                ConnectionString = (new Entities()).Database.Connection.ConnectionString
            };
            DBTable sTable = app.GetTable(model.sourceTable.tableName);

            foreach (DBForeignKey foreignKey in sTable.foreignKeys) //constraint name control in table
            {
                if (foreignKey.name == "FK_" + appName + "_" + model.sourceTable.tableName + "_" + model.name)
                {
                    TempData["message-error"] = "Foreign key with name " + foreignKey.name + " is already exist.";
                    return RedirectToAction("Index", new { @appName = appName });
                }
            }
            DBTable tTable = app.GetTable(model.targetTable.tableName);
            DBColumn sColumn = sTable.columns.SingleOrDefault(x => x.Name == model.sourceColumn);
            DBColumn tColumn = tTable.columns.SingleOrDefault(x => x.Name == model.targetColumn);

            if (sColumn.type != tColumn.type) //columns must have equal data types
            {
                TempData["message-error"] = "Keys have different data types.";
                return RedirectToAction("CreateForeignKey", new { @appName = appName, @tableName = sTable.tableName });
            }
            sTable.foreignKeys.AddToDB(model);
            app.SaveChanges();

            TempData["message-success"] = "Foreign key " + model.name + " of table " + sTable.tableName + " was successfully created.";
            return RedirectToAction("Index", new { @appName = appName });
        }