/// <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));
}
}
}
public void CompileTest()
{
var classToGenerate = "GeneratedUser";
string generateClass = generator.GenerateClass("Users", classToGenerate);
Assembly assembly = DbCompilerUtils.Compile(generateClass);
Type type = assembly.GetType(generator.Namespace + "." + classToGenerate);
ulong count = DbGateway.Instance.LoadCount(type);
Assert.AreEqual(1, count);
DbRecordInfo info1 = DbAttributesManager.GetRecordInfo(typeof(User));
DbRecordInfo info2 = DbAttributesManager.GetRecordInfo(type);
Assert.AreEqual(info1.TableName, info2.TableName, "TableName");
DbFieldInfo pk = (info1 as DbIdentityRecordInfo).PrimaryKey;
Assert.AreEqual(pk.FieldType, info2.Fields[0].FieldType);
Assert.AreEqual(pk.Name, info2.Fields[0].Name);
Assert.AreEqual(typeof(short), info2.Fields[1].FieldType);
for (int i = 1; i < info2.Fields.Length - 1; i++)
{
Assert.AreEqual(info1.Fields[i], info2.Fields[i + 1], "Fields");
}
}
/// <summary>
/// Loads limited array of Objects
/// </summary>
/// <typeparam name="T">Type which has DbAttriibutes</typeparam>
/// <param name="limit">Objects Count Limit</param>
/// <param name="offset">Objects Offset</param>
/// <param name="args">Filter values</param>
/// <returns>Array of requested objects</returns>
/// <example>
/// <code>
/// WorkLog [] list = DbGateway.Instance.LoadList{WorkLog}(7, 7, "Type", WorkLogType.Default);
///
/// Type Definition:
///
/// [DbRecord]
/// public class WorkLog
/// {
/// [DbPrimaryKeyField]
/// public ulong Id;
///
/// [DbField]
/// public WorkLogType Type;
///
/// ...
/// }
///
/// public enum WorkLogType : uint
/// {
/// Default,
/// ...
/// }
/// </code>
/// </example>
public T[] LoadListLimited <T>(int limit, int offset, params object[] args) where T : new()
{
DbRecordInfo recordInfo = DbAttributesManager.GetRecordInfo(typeof(T));
string query = Accessor.BuildWhere(new DbQueryBuilder(Accessor).BuildSelect(recordInfo), args);
return(LoadRecords <T>(query, limit, offset, args));
}
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);
}
}
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>
/// Remove records filtered by all field marked this DbFieldAttribute (use exact matching)
/// </summary>
/// <param name="data">an object with DbAttributes</param>
/// <returns>Removed Records Count</returns>
public uint DeleteByAllFields(object data)
{
var info = DbAttributesManager.GetRecordInfo(data.GetType());
object[] values = info.GetValues(data);
return(Accessor.Delete(info.TableName, values));
}
/// <summary>
/// Loads the result.
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="LoadTotalCount">if set to <c>true</c> will load total count.</param>
/// <returns></returns>
public DbQueryResult <T> LoadResult <T>(bool LoadTotalCount) where T : new()
{
var result = new DbQueryResult <T>();
if (!LoadTotalCount)
{
result.Records = Load <T>();
}
else
{
//TODO: (high priority) refactor
DbRecordInfo recordInfo = DbAttributesManager.GetRecordInfo(typeof(T));
var sb = new StringBuilder();
new DbQueryBuilder(Gateway.Accessor).BuildSelect(sb, recordInfo);
object[] args = buildWhere(sb, Gateway.Accessor);
buildOrderBy(sb);
sb.Insert(7, "SQL_CALC_FOUND_ROWS ");
result.Records = Gateway.LoadRecords <T>(sb.ToString(), limit, offset, args);
result.TotalRecordsCount = Gateway.LoadResult <long>("SELECT FOUND_ROWS()");
}
return(result);
}
/// <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));
}
}
}
/// <summary>
/// Loads Array of objects
/// </summary>
/// <returns></returns>
public T[] Load <T>() where T : new()
{
DbRecordInfo recordInfo = DbAttributesManager.GetRecordInfo(typeof(T));
var sb = new StringBuilder();
new DbQueryBuilder(Gateway.Accessor).BuildSelect(sb, recordInfo);
object [] args = buildWhere(sb, Gateway.Accessor);
buildOrderBy(sb);
return(Gateway.LoadRecords <T>(sb.ToString(), limit, offset, args));
}
/// <summary>
/// Records count.
/// </summary>
/// <returns></returns>
public ulong LoadCount <T>()
{
DbRecordInfo recordInfo = DbAttributesManager.GetRecordInfo(typeof(T));
var sb = new StringBuilder();
new DbQueryBuilder(Gateway.Accessor).BuildSelectCount(sb, recordInfo);
object [] args = buildWhere(sb, Gateway.Accessor);
buildOrderBy(sb);
return(Gateway.LoadResult <ulong>(sb.ToString(), args));
}
/// <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));
}
}
/// <summary>
/// Loads parent record
/// </summary>
/// <typeparam name="TParentType"></typeparam>
/// <param name="data"></param>
/// <returns></returns>
/// <example>
/// <code>
/// User user = DbGateway.Instance.LoadParent{User}(TestData.WorkLog);
///
/// Types Definitions:
///
/// [DbRecord]
/// public class WorkLog
/// {
/// [DbPrimaryKeyField]
/// public ulong Id;
///
/// [DbForeignKeyField(typeof(User))]
/// public ulong UserId;
///
/// ...
/// }
///
/// [DbRecord]
/// public class User
/// {
/// [DbPrimaryKeyField]
/// public ulong Id;
///
/// ...
/// }
/// </code>
/// </example>
public TParentType LoadParent <TParentType>(object data) where TParentType : new()
{
Type childType = data.GetType();
Type parentType = typeof(TParentType);
DbRecordInfo childRecordInfo = DbAttributesManager.GetRecordInfo(childType);
if (!childRecordInfo.ForeignKeys.ContainsKey(parentType))
{
throw new NdbException(string.Format(
"The type '{0}' doesn't contains DbForeignKeyFieldAttribute to type '{1}'", childType, parentType));
}
object primaryKey = childRecordInfo.ForeignKeys[parentType].GetValue(data);
return(Load <TParentType>(primaryKey));
}
/// <summary>
/// Remove object from database
/// </summary>
/// <param name="data">an object with DbAttributes</param>
/// <returns>true if one object has been removed</returns>
public bool Delete(object data)
{
var info = DbAttributesManager.GetRecordInfo(data.GetType());
var identityRecordInfo = info as DbIdentityRecordInfo;
if (identityRecordInfo != null)
{
DbFieldInfo primaryKey = identityRecordInfo.PrimaryKey;
return(0 < Accessor.Delete(
info.TableName,
primaryKey.Name,
primaryKey.GetValue(data)));
}
return(1 < DeleteByAllFields(data));
}
/// <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 an object from database
/// </summary>
/// <param name="data">Object which has DbAttributes</param>
/// <param name="args">Filter values</param>
/// <returns>true if requested object found in database</returns>
/// <example>
/// <code>
/// WorkLog workLog = new WorkLog();
/// bool success = DbGateway.Instance.Load(workLog, "Date", DateTime.Now);
///
/// Type Definition:
///
/// [DbRecord]
/// public class WorkLog
/// {
/// [DbPrimaryKeyField]
/// public ulong Id;
///
/// [DbField]
/// public DateTime Date;
/// ...
/// }
/// </code>
/// </example>
public bool Load(object data, params object[] args)
{
DbRecordInfo info = DbAttributesManager.GetRecordInfo(data.GetType());
using (IDataReader reader = Accessor.ExecuteReaderEx(new DbQueryBuilder(Accessor).BuildSelect(info), args))
{
if (reader.Read())
{
Bind(data, reader, info);
if (reader.Read())
{
throw new NdbException(
"There are several records in database which match the specifyed filter");
}
return(true);
}
}
return(false);
}
/// <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));
}
public void GetRecordInfoTest()
{
var info = DbAttributesManager.GetRecordInfo(typeof(TestWorkLogItem)) as DbIdentityRecordInfo;
Assert.IsNotNull(info);
Assert.IsNotNull(info.PrimaryKey);
Assert.AreEqual(1, info.ForeignKeys.Count);
Assert.IsNotNull(info.ForeignKeys[typeof(User)]);
Assert.AreEqual("WorkLogs", info.TableName);
Assert.AreEqual("SpentMinutes", info.Fields[2].Name);
Assert.AreEqual("CreationDate", info.Fields[3].Name);
Assert.AreEqual(6, info.Fields.Length);
Assert.AreEqual(0, info.Childs.Count);
Assert.AreEqual(0, info.Parents.Count);
info = DbAttributesManager.GetRecordInfo(typeof(User)) as DbIdentityRecordInfo;
Assert.IsNotNull(info);
Assert.AreEqual(2, info.Childs.Count);
var info2 = DbAttributesManager.GetRecordInfo(typeof(TasksAssignment));
Assert.IsNotNull(info2);
Assert.AreEqual(2, info2.Parents.Count);
}
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;
*/
}
/// <summary>
/// Creates associated table and all foreign keys tables
/// </summary>
/// <param name="type">Returns true if table created otherwise false</param>
public bool CreateTableEx(Type type)
{
DbRecordInfo info = DbAttributesManager.GetRecordInfo(type);
return(CreateTableEx(info));
}
/// <summary>
/// Creates associated table
/// </summary>
/// <param name="type"></param>
public void CreateTable(Type type)
{
DbRecordInfo info = DbAttributesManager.GetRecordInfo(type);
accessor.CreateTable(info);
}
/// <summary>
/// Update database table to match passed Type
///
/// Extended features:
/// - creates\updates foreign key tables
/// - create table if it doesn't exists
/// </summary>
/// <param name="type"></param>
public void AlterTableEx(Type type)
{
DbRecordInfo info = DbAttributesManager.GetRecordInfo(type);
AlterTableEx(info);
}
/// <summary>
/// Removes Table from Database
/// </summary>
/// <param name="type"></param>
/// <returns></returns>
public bool DropTable(Type type)
{
DbRecordInfo info = DbAttributesManager.GetRecordInfo(type);
return(accessor.DropTableSafe(info.TableName));
}
/// <summary>
/// Updates all entities of the specifyed type which match the passed args
/// </summary>
/// <param name="type"></param>
/// <param name="fieldsToUpdate"></param>
/// <param name="args"></param>
/// <returns>Count of updated objects</returns>
public int Update(Type type, object [] fieldsToUpdate, params object[] args)
{
var info = DbAttributesManager.GetRecordInfo(type);
return(Accessor.Update(info.TableName, fieldsToUpdate, args));
}
/// <summary>
/// Updates objects in database by specifyed filter
/// </summary>
/// <param name="data">object with data to set</param>
/// <param name="args">filter</param>
/// <returns></returns>
public int Update(object data, params object[] args)
{
var info = DbAttributesManager.GetRecordInfo(data.GetType());
return(Accessor.Update(info.TableName, info.GetValues(data), args));
}
private static void Bind(object data, IDataRecord row, Type type)
{
DbRecordInfo info = DbAttributesManager.GetRecordInfo(type);
Bind(data, row, info);
}
/// <summary>
/// Load an object by Primary Key from Database
/// </summary>
/// <param name="data">Object which has DbAttributes</param>
/// <returns>true if requested object found in database</returns>
/// <example>
/// <code>
/// WorkLog workLog = new WorkLog();
/// workLog.Id = workLogId;
/// bool success = DbGateway.Instance.Load(workLog);
///
/// Type Definition:
///
/// [DbRecord]
/// public class WorkLog
/// {
/// [DbPrimaryKeyField]
/// public ulong Id;
///
/// ...
/// }
/// </code>
/// </example>
public bool Load(object data)
{
DbRecordInfo info = DbAttributesManager.GetRecordInfo(data.GetType());
return(load(data, info));
}
/// <summary>
/// Low level records loader
/// </summary>
/// <typeparam name="T">Type which has DbAttriibutes</typeparam>
/// <param name="query">SQL query</param>
/// <param name="limit">Objects Count Limit</param>
/// <param name="offset">Objects Offset</param>
/// <param name="args">filter values</param>
/// <returns>Array of requested objects</returns>
/// <example>
/// <code>
/// ViewRecordType[] list = DbGateway.Instance.LoadRecords{ViewRecordType}("SELECT ***.... ", 7, 7, "UserId", UserId)
/// </code>
/// </example>
public T[] LoadRecords <T>(string query, int limit, int offset, params object[] args) where T : new()
{
DbRecordInfo info = DbAttributesManager.GetRecordInfo(typeof(T));
return(loadRecords <T>(info, query, limit, offset, args));
}