/// <summary>
/// Delete property values
/// </summary>
private void DeleteProperties(DataContext context, String path, IDatamartSchemaPropertyContainer container)
{
foreach (var p in container.Properties.Where(o => o.Type == SchemaPropertyType.Object))
{
context.ExecuteNonQuery(context.CreateSqlStatement($"DELETE FROM {path}_{p.Name} WHERE entity_uuid NOT IN (SELECT uuid FROM {path})"));
this.DeleteProperties(context, String.Format("{0}_{1}", path, p.Name), p);
}
}
/// <summary>
/// Delete property values
/// </summary>
private void DeleteProperties(IDbTransaction tx, String path, IDatamartSchemaPropertyContainer container)
{
foreach (var p in container.Properties.Where(o => o.Type == SchemaPropertyType.Object))
{
using (var dbc = this.CreateCommand(tx, string.Format("DELETE FROM {0}_{1} WHERE entity_uuid NOT IN (SELECT uuid FROM {0})", path, p.Name)))
dbc.ExecuteNonQuery();
this.DeleteProperties(tx, String.Format("{0}_{1}", path, p.Name), p);
}
}
/// <summary>
/// Insert specified object
/// </summary>
private Guid InsertObject(IDbTransaction tx, String path, IDatamartSchemaPropertyContainer pcontainer, dynamic obj, Guid?scope = null)
{
// Conver to expando
lock (this.m_lock)
{
IDictionary <String, Object> tuple = new ExpandoObject();
foreach (var pi in obj.GetType().GetProperties())
{
tuple.Add(pi.Name, pi.GetValue(obj, null));
}
tuple.Add("uuid", Guid.NewGuid());
tuple.Add("cont_id", scope);
tuple.Add("extraction_time", DateTime.Now.ToUniversalTime().Subtract(this.m_epoch).TotalSeconds);
// Create the properties
List <Object> parameters = new List <object>()
{
tuple["uuid"]
};
if (scope != null)
{
parameters.Add(scope);
}
parameters.Add(tuple["extraction_time"]);
foreach (var p in pcontainer.Properties.Where(o => o.Type != SchemaPropertyType.Object))
{
parameters.Add(tuple[p.Name]);
}
// Now time to store
StringBuilder sbQuery = new StringBuilder("INSERT INTO ");
sbQuery.Append(path);
sbQuery.Append(" VALUES (");
foreach (var itm in parameters)
{
sbQuery.Append("?, ");
}
sbQuery.Remove(sbQuery.Length - 2, 2);
sbQuery.Append(")");
// Database command
using (var dbc = this.CreateCommand(tx, sbQuery.ToString(), parameters.ToArray()))
dbc.ExecuteNonQuery();
// Sub-properties
foreach (var p in pcontainer.Properties.Where(o => o.Type == SchemaPropertyType.Object))
{
this.InsertObject(tx, String.Format("{0}_{1}", path, p.Name), p, obj[p.Name], (Guid)tuple["uuid"]);
}
return((Guid)tuple["uuid"]);
}
}
/// <summary>
/// Insert specified object
/// </summary>
private Guid InsertObject(DataContext context, String path, IDatamartSchemaPropertyContainer pcontainer, dynamic obj, Guid?scope = null)
{
// Conver to expando
IDictionary <String, Object> tuple = new ExpandoObject();
if (obj is IDictionary <String, object> )
{
foreach (var kv in obj as IDictionary <String, object> )
{
tuple.Add(kv.Key, kv.Value);
}
}
else
{
foreach (var pi in obj.GetType().GetProperties())
{
tuple.Add(pi.Name, pi.GetValue(obj, null));
}
}
tuple.Add("uuid", Guid.NewGuid());
//tuple.Add("cont_id", scope);
//tuple.Add("ext_time", DateTime.Now);
// Now time to store
SqlStatement sbQuery = context.CreateSqlStatement("INSERT INTO "),
sbValues = context.CreateSqlStatement();
sbQuery.Append(path);
sbQuery.Append("(");
foreach (var p in tuple.Where(o => pcontainer.Properties.FirstOrDefault(p => p.Name == o.Key)?.Type != SchemaPropertyType.Object))
{
sbQuery.Append(p.Key);
sbValues.Append("?", p.Value);
if (p.Key != tuple.Last().Key)
{
sbQuery.Append(",");
sbValues.Append(",");
}
}
sbQuery = sbQuery.Append(") VALUES (").Append(sbValues).Append(")");
context.ExecuteNonQuery(sbQuery);
// Sub-properties
foreach (var p in pcontainer.Properties.Where(o => o.Type == SchemaPropertyType.Object))
{
this.InsertObject(context, String.Format("{0}_{1}", path, p.Name), p, obj[p.Name], (Guid)tuple["uuid"]);
}
return((Guid)tuple["uuid"]);
}
/// <summary>
/// Create properties for the specified container
/// </summary>
private void CreateProperties(String pathName, IDbTransaction tx, IDatamartSchemaPropertyContainer container)
{
List <String> indexes = new List <string>();
// Create the property container table
StringBuilder createSql = new StringBuilder();
createSql.AppendFormat("CREATE TABLE {0} (", pathName);
createSql.Append("uuid blob(16) not null primary key, extraction_time bigint not null default current_timestamp, ");
if (container is DatamartSchemaProperty)
{
createSql.Append("entity_uuid blob(16) not null, ");
}
// Create the specified dm_<<name>>_table
foreach (var itm in container.Properties)
{
itm.Id = itm.Id == default(Guid) ? Guid.NewGuid() : itm.Id;
using (var dbc = this.CreateCommand(tx, "INSERT INTO dw_properties VALUES (?, ?, ?, ?, ?)", itm.Id.ToByteArray(), container.Id.ToByteArray(), itm.Name, (int)itm.Type, (int)itm.Attributes))
dbc.ExecuteNonQuery();
if (itm.Properties?.Count > 0)
{
this.CreateProperties(String.Format("{0}_{1}", pathName, itm.Name), tx, itm);
}
var typeString = "";
switch (itm.Type)
{
case SchemaPropertyType.Binary:
typeString = "blob";
break;
case SchemaPropertyType.Boolean:
typeString = "boolean";
break;
case SchemaPropertyType.Date:
typeString = "bigint";
break;
case SchemaPropertyType.Decimal:
typeString = "decimal";
break;
case SchemaPropertyType.Float:
typeString = "float";
break;
case SchemaPropertyType.Integer:
typeString = "integer";
break;
case SchemaPropertyType.String:
typeString = "varchar(128)";
break;
case SchemaPropertyType.Uuid:
typeString = "blob(16)";
break;
}
// Add property to the table
if (!String.IsNullOrEmpty(typeString))
{
String attributeString = "";
if (itm.Attributes.HasFlag(SchemaPropertyAttributes.NotNull))
{
attributeString += "not null ";
}
if (itm.Attributes.HasFlag(SchemaPropertyAttributes.Unique))
{
attributeString += "unique ";
}
createSql.AppendFormat("\r\n\t{0} {1} {2},", itm.Name, typeString, attributeString);
if (itm.Attributes.HasFlag(SchemaPropertyAttributes.Indexed))
{
indexes.Add(String.Format("CREATE INDEX {0}_{1}_idx ON {0}({1})", pathName, itm.Name));
}
}
}
createSql.Remove(createSql.Length - 1, 1);
createSql.AppendFormat(")");
// Now execute SQL create statement
if (!(container is DatamartStoredQuery))
{
lock (this.m_lock)
{
using (var dbc = this.CreateCommand(tx, createSql.ToString())) dbc.ExecuteNonQuery();
foreach (var idx in indexes)
{
using (var dbc = this.CreateCommand(tx, idx)) dbc.ExecuteNonQuery();
}
}
}
}
/// <summary>
/// Create properties for the specified container
/// </summary>
private void CreateProperties(DataContext context, String pathName, DatamartSchema schema, IDatamartSchemaPropertyContainer container)
{
List <SqlStatement> indexes = new List <SqlStatement>();
// Create the property container table
SqlStatement createSql = context.CreateSqlStatement("CREATE TABLE ")
.Append(pathName)
.Append("(")
.Append("uuid UUID NOT NULL PRIMARY KEY DEFAULT uuid_generate_v4(),");
if (container is DatamartSchemaProperty)
{
createSql = createSql.Append("entity_uuid UUID NOT NULL, ");
}
// Create the specified dm_<<name>>_table
foreach (var itm in (container ?? schema).Properties)
{
itm.Id = itm.Id == default(Guid) ? Guid.NewGuid() : itm.Id;
// Insert
context.Insert(new AdhocProperty()
{
Attributes = (int)itm.Attributes,
TypeId = (int)itm.Type,
ContainerId = (container as DatamartSchemaProperty)?.Id,
SchemaId = schema.Id,
Name = itm.Name,
PropertyId = itm.Id
});
if (itm.Properties?.Count > 0)
{
this.CreateProperties(context, String.Format("{0}_{1}", pathName, itm.Name), schema, itm);
}
// get datatype
var typeString = context.GetDataType(itm.Type);;
// Add property to the table
if (!String.IsNullOrEmpty(typeString))
{
String attributeString = "";
if (itm.Attributes.HasFlag(SchemaPropertyAttributes.NotNull))
{
attributeString += "NOT NULL ";
}
if (itm.Attributes.HasFlag(SchemaPropertyAttributes.Unique))
{
attributeString += "UNIQUE ";
}
createSql = createSql.Append($"{itm.Name} {typeString} {attributeString}");
// HACK:
createSql.Append(",");
if (itm.Attributes.HasFlag(SchemaPropertyAttributes.Indexed))
{
indexes.Add(context.CreateSqlStatement($"CREATE INDEX {pathName}_{itm.Name}_idx ON {pathName}({itm.Name})"));
}
}
}
createSql.RemoveLast();
createSql = createSql.Append(")");
// Now execute SQL create statement
if (!(container is DatamartStoredQuery))
{
context.ExecuteNonQuery(createSql);
foreach (var idx in indexes)
{
context.ExecuteNonQuery(idx);
}
}
}
