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