private void AlterTableEx(DbRecordInfo info) { foreach (KeyValuePair <Type, DbFieldInfo> key in info.ForeignKeys) { DbIdentityRecordInfo primaryInfo = DbAttributesManager.GetRecordInfo(key.Key) as DbIdentityRecordInfo; if (primaryInfo == null) { throw new NdbNotIdentityException(string.Format( "Records without primary kes can't be used as foreign keys.\r\nRecord {0}.\r\nPrimary record type {1}" , info.TableName, key.Key)); } if (primaryInfo.PrimaryKey.FieldType != key.Value.FieldType) { throw new NdbException( "Primary key {0} in {1} is {2}. But Foreign key {3} in {4} is {5}", primaryInfo.PrimaryKey.Name, primaryInfo.TableName, primaryInfo.PrimaryKey.FieldType, key.Value.Name, info.TableName, key.Value.FieldType); } AlterTableEx(primaryInfo); } if (IsTableExists(info.TableName)) { if (!IsValid(info.RecordType)) { AlterTable(info.RecordType); } } else { CreateTable(info.RecordType); } }
/// <summary> /// Saves the specified items to database. /// </summary> /// <param name="items">The items.</param> public void Save(object [] items) { if (items == null || items.Length == 0) { return; } var info = DbAttributesManager.GetRecordInfo(items[0].GetType()); DbIdentityRecordInfo identityInfo = info as DbIdentityRecordInfo; if (identityInfo != null) { foreach (object item in items) { save(item, identityInfo); } } else { foreach (object item in items) { Accessor.Insert(info.TableName, info.GetValues(item)); } } }
internal override void CreateTable(DbRecordInfo info) { StringBuilder sb = new StringBuilder("CREATE TABLE " + info.TableName + "("); DbIdentityRecordInfo identityRecordInfo = info as DbIdentityRecordInfo; if (identityRecordInfo != null) { DbFieldInfo key = identityRecordInfo.PrimaryKey; if (key.FieldType == typeof(Guid)) { sb.Append(GetDefinition(key) + " NOT NULL"); } else { sb.Append(GetDefinition(key) + " NOT NULL IDENTITY(1,1)"); } sb.Append(','); } foreach (DbFieldInfo field in info.Fields) { sb.Append(GetDefinition(field)); sb.Append(','); } string[] keys = getPrimaryKeys(info); sb.AppendFormat("PRIMARY KEY ({0})", String.Join(",", keys)); DbIndexesInfo indexes = DbAttributesManager.GetIndexes(info.Fields); ProcessIndexes(sb, indexes.Unique, ",UNIQUE ({0})"); //TODO: (MSSQL) CREATE FULLTEXT INDEX //TODO: (MSSQL) CREATE INDEX // ProcessIndexes(sb, indexes.Indexes, ",KEY {1} ({0})"); // ProcessIndexes(sb, indexes.FullText, ",FULLTEXT KEY {1} ({0})"); //process foreign keys foreach (KeyValuePair <Type, DbFieldInfo> key in info.ForeignKeys) { DbIdentityRecordInfo ri = DbAttributesManager.GetRecordInfo(key.Key) as DbIdentityRecordInfo; if (ri == null) { throw new NdbException("Only DbIdentityRecord objects can be used as Foreign Keys"); } sb.AppendFormat( ",FOREIGN KEY ([{1}]) REFERENCES [{0}] ([{2}]) ON DELETE CASCADE ON UPDATE CASCADE" , ri.TableName , key.Value.Name , ri.PrimaryKey.Name); } sb.Append(")"); string query = sb.ToString(); ExecuteNonQuery(query); }
/// <summary> /// Imports all fields of passed objects to mapped tables\columns in database (Primary Keys and other generated content). /// </summary> /// <param name="records">The records.</param> public void Import(object [] records) { if (records == null || records.Length == 0) { return; } var info = DbAttributesManager.GetRecordInfo(records[0].GetType()); for (int i = 0; i < records.Length; i++) { object record = records[i]; DbIdentityRecordInfo identityRecordInfo = info as DbIdentityRecordInfo; if (identityRecordInfo != null) { object[] values = info.GetValues(record, identityRecordInfo.PrimaryKey.Name, identityRecordInfo.PrimaryKey.GetValue(record)); if (Accessor.IsMsSql) { Accessor.ExecuteNonQuery(DbTextFileGenerator.MsSql.SetIdentityInsertOn(info.TableName)); Accessor.Insert(info.TableName, values); Accessor.ExecuteNonQuery(DbTextFileGenerator.MsSql.SetIdentityInsertOff(info.TableName)); } else { Accessor.Insert(info.TableName, values); } } else { Accessor.Insert(info.TableName, info.GetValues(record)); } } }
private void insert(DbIdentityRecordInfo info, object data) { DbFieldInfo primaryKey = info.PrimaryKey; if (primaryKey.FieldType == typeof(Guid)) { Guid guid = Guid.NewGuid(); primaryKey.SetValue(data, guid); object[] values = info.GetValues(data, primaryKey.Name, guid); Accessor.Insert(info.TableName, values); } else if (!info.IsDbGeneratedPrimaryKey) { object[] values = info.GetValues(data, primaryKey.Name, primaryKey.GetValue(data)); Accessor.Insert(info.TableName, values); } else { object[] values = info.GetValues(data); object newId = Accessor.InsertIdentity(info.TableName, primaryKey.Name, values); primaryKey.SetValue(data, Convert.ChangeType(newId, primaryKey.FieldType)); } }
private bool load(object data, DbIdentityRecordInfo info) { if (!info.IsPrimaryKeyValid(data)) { throw new NdbException("Primary Key wasn't set for object " + data.GetType()); } return(Load(data, info.PrimaryKey.Name, info.PrimaryKey.GetValue(data))); }
private bool load(object data, DbRecordInfo info) { DbIdentityRecordInfo identityRecordInfo = info as DbIdentityRecordInfo; if (identityRecordInfo != null) { return(load(data, identityRecordInfo)); } throw new NdbNotIdentityException("Can't load record"); }
private void save(object data, DbIdentityRecordInfo info) { if (!info.IsPrimaryKeyValid(data)) { insert(info, data); } else { update(info, data); } }
private int update(DbIdentityRecordInfo info, object data) { if (!info.IsPrimaryKeyValid(data)) { throw new NdbException(string.Format( "Primary Key wasn't set for the {0} object", data.GetType())); } return(Accessor.Update(info.TableName, info.GetValues(data), info.PrimaryKey.Name, info.PrimaryKey.GetValue(data))); }
/// <summary> /// Updates object in database. /// </summary> /// <param name="data"></param> /// <returns>true if one object was updated</returns> public bool Update(object data) { var info = DbAttributesManager.GetRecordInfo(data.GetType()); DbIdentityRecordInfo identityRecordInfo = info as DbIdentityRecordInfo; if (identityRecordInfo != null) { return(1 == update(identityRecordInfo, data)); } throw new NdbException(string.Format( "DbPrimaryKeyField attribute wasn't specifyed on {0} type", data.GetType())); }
/// <summary> /// Insert new object to database (if primary key was set, it will be overwrited) /// </summary> /// <param name="data"></param> public void Insert(object data) { var info = DbAttributesManager.GetRecordInfo(data.GetType()); DbIdentityRecordInfo identityRecordInfo = info as DbIdentityRecordInfo; if (identityRecordInfo != null) { insert(identityRecordInfo, data); } else { Accessor.Insert(info.TableName, info.GetValues(data)); } }
private static string[] getPrimaryKeys(DbRecordInfo info) { DbIdentityRecordInfo identityRecordInfo = info as DbIdentityRecordInfo; if (identityRecordInfo != null) { return(new[] { identityRecordInfo.PrimaryKey.Name }); } List <string> list = new List <string>(info.Fields.Length); foreach (DbFieldInfo field in info.Fields) { list.Add(field.Name); } return(list.ToArray()); }
/// <summary> /// Loads child records /// </summary> /// <typeparam name="TChildType">Type of the child objects</typeparam> /// <param name="data">Parent object</param> /// <param name="args">Filter</param> /// <returns>Array of childs</returns> /// <example> /// <code> /// Event[] events = DbGateway.Instance.LoadChilds{Event}(TestData.User); /// /// Types Definitions: /// /// [DbRecord] /// public class Event /// { /// [DbPrimaryKeyField] /// public ulong Id; /// /// ... /// } /// /// [DbRecord] /// public class User /// { /// [DbPrimaryKeyField] /// public ulong Id; /// /// ... /// } /// </code> /// </example> public TChildType[] LoadChilds <TChildType>(object data, params object[] args) where TChildType : new() { Type primaryType = data.GetType(); DbRecordInfo childRecordInfo = DbAttributesManager.GetRecordInfo(typeof(TChildType)); DbIdentityRecordInfo primaryRecordInfo = DbAttributesManager.GetRecordInfo(primaryType) as DbIdentityRecordInfo; if (primaryRecordInfo == null) { throw new NdbNotIdentityException("Only DbIdentityRecord objects can have childs"); } object [] _args = UnionArgs(args, childRecordInfo.ForeignKeys[primaryType].Name, primaryRecordInfo.PrimaryKey.GetValue(data)); return(LoadList <TChildType>(_args)); }
/// <summary> /// Load an object which has DbAttributes from database /// </summary> /// <typeparam name="T">Object Type</typeparam> /// <param name="primaryKey">Object Primary Key</param> /// <returns>object</returns> /// <example> /// <code> /// WorkLog workLog = DbGateway.Instance.Load{WorkLog}(workLogId); /// /// Type Definition: /// /// [DbRecord] /// public class WorkLog /// { /// [DbPrimaryKeyField] /// public ulong Id; /// /// ... /// } /// </code> /// </example> /// <exception cref="NdbException">If Primary Key not found</exception> public T Load <T>(object primaryKey) where T : new() { DbIdentityRecordInfo info = DbAttributesManager.GetRecordInfo(typeof(T)) as DbIdentityRecordInfo; if (info == null) { throw new NdbNotIdentityException("Can't load record of type " + typeof(T)); } T data = new T(); info.PrimaryKey.SetValue(data, primaryKey); if (!load(data, info)) { return(default(T)); } return(data); }
/// <summary> /// Load records using association table /// </summary> /// <typeparam name="TTargetType">Type we want to Load</typeparam> /// <typeparam name="TAssociationType">Type which contains associations</typeparam> /// <param name="data">Data object</param> /// <returns>Array of Requested Objects</returns> /// <example> /// <code> /// Task []tasks = DbGateway.Instance.LoadAssociated{Task, TasksAssignment}(TestData.User); /// /// Types Definitions: /// /// [DbRecord] /// public class TasksAssignment /// { /// [DbForeignKeyField(typeof(Task))] /// public ulong TaskId; /// /// [DbForeignKeyField(typeof(User))] /// public ulong UserId; /// /// ... /// } /// /// [DbRecord] /// public class Task /// { /// [DbPrimaryKeyField] /// public ulong Id; /// /// ... /// } /// /// [DbRecord] /// public class User /// { /// [DbPrimaryKeyField] /// public ulong Id; /// /// ... /// } /// </code> /// </example> public TTargetType[] LoadAssociated <TTargetType, TAssociationType>(object data) where TTargetType : new() { Type primaryType = data.GetType(); DbIdentityRecordInfo targetRecordInfo = DbAttributesManager.GetRecordInfo(typeof(TTargetType)) as DbIdentityRecordInfo; if (targetRecordInfo == null) { throw new NdbNotIdentityException("Only DbIdentityRecord objects can have related records"); } DbRecordInfo associationRecordInfo = DbAttributesManager.GetRecordInfo(typeof(TAssociationType)); DbIdentityRecordInfo sourceRecordInfo = DbAttributesManager.GetRecordInfo(primaryType) as DbIdentityRecordInfo; if (sourceRecordInfo == null) { throw new NdbNotIdentityException("Only DbIdentityRecord objects can have related records"); } object primaryKey = sourceRecordInfo.PrimaryKey.GetValue(data); DbQueryBuilder queryBuilder = new DbQueryBuilder(Accessor); string select = queryBuilder.BuildSelect(targetRecordInfo); // below is a self documented query? :) string sql = string.Format( @"{5} INNER JOIN {1} ON {0}.{3}={1}.{2} AND {1}.{4}=@PrimaryKey" // @"SELECT * FROM {0} INNER JOIN {1} ON {0}.{3}={1}.{2} AND {1}.{4}=@PrimaryKey" , targetRecordInfo.TableName , associationRecordInfo.TableName , associationRecordInfo.ForeignKeys[typeof(TTargetType)].Name , targetRecordInfo.PrimaryKey.Name , associationRecordInfo.ForeignKeys[primaryType].Name , select ); return(loadRecords <TTargetType>(targetRecordInfo, sql, "PrimaryKey", primaryKey)); }
internal override void CreateTable(DbRecordInfo info) { StringBuilder sb = new StringBuilder("CREATE TABLE " + info.TableName + "("); DbIdentityRecordInfo identityRecordInfo = info as DbIdentityRecordInfo; if (identityRecordInfo != null) { DbFieldInfo key = identityRecordInfo.PrimaryKey; if (key.FieldType == typeof(Guid)) { sb.Append(GetDefinition(key) + " NOT NULL PRIMARY KEY UNIQUE"); } else { sb.Append(GetDefinition(key) + " NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE"); } sb.Append(','); } foreach (DbFieldInfo field in info.Fields) { sb.Append(GetDefinition(field)); sb.Append(','); } sb.Remove(sb.Length - 1, 1); sb.Append(")"); string query = sb.ToString(); ExecuteNonQuery(query); createTriggers(info); }
internal override void CreateTable(DbRecordInfo info) { string postQueries = ""; /*; * CREATE TABLE tablename ( * colname integer NOT NULL DEFAULT nextval('tablename_colname_seq') * ); * ALTER SEQUENCE tablename_colname_seq OWNED BY tablename.colname;*/ StringBuilder sb = new StringBuilder("CREATE TABLE " + info.TableName + " ("); DbIdentityRecordInfo identityRecordInfo = info as DbIdentityRecordInfo; if (identityRecordInfo != null) { DbFieldInfo key = identityRecordInfo.PrimaryKey; if (key.FieldType == typeof(Guid)) { sb.Append(GetDefinition(key) + " NOT NULL"); sb.Append(','); } else { string sequenceName = string.Format("{0}_{1}_seq", info.TableName, key.Name); sb.Insert(0, "CREATE SEQUENCE " + sequenceName + ";"); postQueries = string.Format("ALTER SEQUENCE {0}_{1}_seq OWNED BY {0}.{1};", info.TableName, key.Name); sb.Append(GetDefinition(key) + " NOT NULL DEFAULT nextval('" + sequenceName + "'::regclass)"); sb.Append(','); } } foreach (DbFieldInfo field in info.Fields) { sb.Append(GetDefinition(field)); sb.Append(','); } string[] keys = getPrimaryKeys(info); sb.AppendFormat("PRIMARY KEY ({0}), UNIQUE ({0})", String.Join(",", keys)); // sb.AppendFormat("CONSTRAINT \"PK_{1}\" PRIMARY KEY ({0})", String.Join(",", keys), info.TableName); // sb.AppendFormat("CONSTRAINT \"U_{1}\" UNIQUE ({0})", String.Join(",", keys), info.TableName); //Process indexes DbIndexesInfo indexes = DbAttributesManager.GetIndexes(info.Fields); ProcessIndexes(sb, indexes.Unique, ",UNIQUE ({0})"); // ProcessIndexes(sb, indexes.Indexes, ",KEY {1} ({0})"); // ProcessIndexes(sb, indexes.FullText, ",FULLTEXT KEY {1} ({0})"); //process foreign keys foreach (KeyValuePair <Type, DbFieldInfo> key in info.ForeignKeys) { DbIdentityRecordInfo ri = DbAttributesManager.GetRecordInfo(key.Key) as DbIdentityRecordInfo; if (ri == null) { throw new NdbException("Only DbIdentityRecord objects can be used as Foreign Keys"); } sb.AppendFormat( ",FOREIGN KEY ({1}) REFERENCES {0} ({2}) ON DELETE CASCADE ON UPDATE CASCADE" , ri.TableName , key.Value.Name , ri.PrimaryKey.Name); } sb.Append(");"); sb.Append(postQueries); string query = sb.ToString(); ExecuteNonQuery(query); }
private void createTriggers(DbRecordInfo info) { foreach (KeyValuePair <Type, DbFieldInfo> key in info.ForeignKeys) { DbIdentityRecordInfo keyInfo = DbAttributesManager.GetRecordInfo(key.Key) as DbIdentityRecordInfo; if (keyInfo == null) { throw new NdbException("Only Identity records supported"); } string TriggerBase = string.Format(@"_{0}_{1}_{2}_{3}" , info.TableName, key.Value.Name , keyInfo.TableName, keyInfo.PrimaryKey.Name); string TriggerName = "fki" + TriggerBase; dropTrigger(TriggerName); ExecuteNonQuery(string.Format(@"CREATE TRIGGER {0} BEFORE INSERT ON [{1}] FOR EACH ROW BEGIN SELECT RAISE(ROLLBACK, 'insert on table ""{1}"" violates foreign key constraint ""{0}""') WHERE NEW.{2} IS NOT NULL AND (SELECT {3} FROM {4} WHERE {3} = NEW.{2}) IS NULL; END;" , TriggerName , info.TableName , key.Value.Name , keyInfo.PrimaryKey.Name , keyInfo.TableName )); TriggerName = "fku" + TriggerBase; dropTrigger(TriggerName); ExecuteNonQuery(string.Format(@"CREATE TRIGGER {0} BEFORE UPDATE ON [{1}] FOR EACH ROW BEGIN SELECT RAISE(ROLLBACK, 'update on table ""{1}"" violates foreign key constraint ""{0}""') WHERE NEW.{2} IS NOT NULL AND (SELECT {3} FROM {4} WHERE {3} = NEW.{2}) IS NULL; END;" , TriggerName , info.TableName , key.Value.Name , keyInfo.PrimaryKey.Name , keyInfo.TableName )); TriggerName = "fkdc" + TriggerBase; dropTrigger(TriggerName); ExecuteNonQuery(string.Format(@"CREATE TRIGGER {0} BEFORE DELETE ON {1} FOR EACH ROW BEGIN DELETE FROM {2} WHERE {3} = OLD.{4}; END;" , TriggerName , keyInfo.TableName , info.TableName , key.Value.Name , keyInfo.PrimaryKey.Name )); } /* * -- Drop Trigger * DROP TRIGGER fki_bar_fooId_foo_id; * * -- Foreign Key Preventing insert * CREATE TRIGGER fki_bar_fooId_foo_id * BEFORE INSERT ON [bar] * FOR EACH ROW BEGIN * SELECT RAISE(ROLLBACK, 'insert on table "bar" violates foreign key constraint "fki_bar_fooId_foo_id"') * WHERE NEW.fooId IS NOT NULL AND (SELECT id FROM foo WHERE id = NEW.fooId) IS NULL; * END; * * -- Drop Trigger * DROP TRIGGER fku_bar_fooId_foo_id; * * -- Foreign key preventing update * CREATE TRIGGER fku_bar_fooId_foo_id * BEFORE UPDATE ON [bar] * FOR EACH ROW BEGIN * SELECT RAISE(ROLLBACK, 'update on table "bar" violates foreign key constraint "fku_bar_fooId_foo_id"') * WHERE NEW.fooId IS NOT NULL AND (SELECT id FROM foo WHERE id = NEW.fooId) IS NULL; * END; * * -- Drop Trigger * DROP TRIGGER fkd_bar_fooId_foo_id; * * -- Foreign key preventing delete * CREATE TRIGGER fkd_bar_fooId_foo_id * BEFORE DELETE ON foo * FOR EACH ROW BEGIN * SELECT RAISE(ROLLBACK, 'delete on table "foo" violates foreign key constraint "fkd_bar_fooId_foo_id"') * WHERE (SELECT fooId FROM bar WHERE fooId = OLD.id) IS NOT NULL; * END; * * -- Drop Trigger * DROP TRIGGER fki_bar_fooId2_foo_id; * * -- Foreign Key Preventing insert * CREATE TRIGGER fki_bar_fooId2_foo_id * BEFORE INSERT ON [bar] * FOR EACH ROW BEGIN * SELECT RAISE(ROLLBACK, 'insert on table "bar" violates foreign key constraint "fki_bar_fooId2_foo_id"') * WHERE (SELECT id FROM foo WHERE id = NEW.fooId2) IS NULL; * END; * * -- Drop Trigger * DROP TRIGGER fku_bar_fooId2_foo_id; * * -- Foreign key preventing update * CREATE TRIGGER fku_bar_fooId2_foo_id * BEFORE UPDATE ON [bar] * FOR EACH ROW BEGIN * SELECT RAISE(ROLLBACK, 'update on table "bar" violates foreign key constraint "fku_bar_fooId2_foo_id"') * WHERE (SELECT id FROM foo WHERE id = NEW.fooId2) IS NULL; * END; * * -- Drop Trigger * DROP TRIGGER fkdc_bar_fooId2_foo_id; * * -- Cascading Delete * CREATE TRIGGER fkdc_bar_fooId2_foo_id * BEFORE DELETE ON foo * FOR EACH ROW BEGIN * DELETE FROM bar WHERE bar.fooId2 = OLD.id; * END; */ }