private string PrepareSql()
{
const string sqlPattern = @"
SELECT e.{0}, e.TflKey{1}
FROM {2} e WITH (NOLOCK);
";
var selectKeys = new FieldSqlWriter(_entity.PrimaryKey).AddDeleted(_entity).Alias(_connection.L, _connection.R).Write(", e.", false);
return(string.Format(sqlPattern, selectKeys, PrepareVersion(), _connection.Enclose(_entity.OutputName())));
}
public List <string> SelectKeys(AbstractConnection connection)
{
var selectKeys = new List <string>();
foreach (var field in PrimaryKey.WithInput())
{
selectKeys.Add(
field.Alias.Equals(field.Name)
? connection.Enclose(field.Name)
: string.Format("{0} AS {1}", connection.Enclose(field.Name), connection.Enclose(field.Alias))
);
}
return(selectKeys);
}
public string WriteTemporary(AbstractConnection connection, string name, Fields fields, bool useAlias = true)
{
var safeName = connection.Enclose(name.TrimStart("@".ToCharArray()));
var defs = useAlias ? new FieldSqlWriter(fields).Alias(connection.L, connection.R).DataType(new MySqlDataTypeService()).Write() : new FieldSqlWriter(fields).Name(connection.L, connection.R).DataType(new MySqlDataTypeService()).Write();
return(string.Format(@"CREATE TEMPORARY TABLE IF NOT EXISTS {0}({1}) ENGINE = MEMORY; TRUNCATE TABLE {0};", safeName, defs));
}
public override void Create(AbstractConnection connection, Process process, Entity entity)
{
if (EntityExists != null && EntityExists.Exists(connection, entity))
{
process.Logger.EntityWarn(entity.Name, "Trying to create entity that already exists! {0}", entity.Name);
return;
}
var keyType = entity.IsMaster() ? FieldType.MasterKey : FieldType.PrimaryKey;
var writer = process.StarEnabled && keyType == FieldType.MasterKey ?
new FieldSqlWriter(entity.Fields, entity.CalculatedFields, process.CalculatedFields, GetRelationshipFields(process.Relationships, entity)) :
new FieldSqlWriter(entity.Fields, entity.CalculatedFields);
var primaryKey = writer.FieldType(keyType).Alias(connection.L, connection.R).Asc().Values();
var defs = new List <string>();
defs.AddRange(writer
.Reload()
.AddBatchId(entity.Index)
.AddDeleted(entity)
.AddSurrogateKey(entity.Index)
.Output()
.Alias(connection.L, connection.R)
.DataType(new SqlServerDataTypeService())
.AppendIf(" NOT NULL", keyType)
.Values());
var rowVersion = entity.Fields.WithSimpleType("rowversion").WithoutInput().WithoutOutput();
if (rowVersion.Any())
{
var alias = rowVersion.First().Alias;
defs.Add(connection.Enclose(alias) + " [ROWVERSION] NOT NULL");
}
var createSql = connection.TableQueryWriter.CreateTable(entity.OutputName(), defs);
_logger.EntityDebug(entity.Name, createSql);
var indexSql = connection.TableQueryWriter.AddUniqueClusteredIndex(entity.OutputName());
_logger.EntityDebug(entity.Name, indexSql);
var keySql = connection.TableQueryWriter.AddPrimaryKey(entity.OutputName(), primaryKey);
_logger.EntityDebug(entity.Name, keySql);
using (var cn = connection.GetConnection()) {
cn.Open();
cn.Execute(createSql);
cn.Execute(indexSql);
cn.Execute(keySql);
_logger.EntityInfo(entity.Name, "Initialized {0} in {1} on {2}.", entity.OutputName(), connection.Database, connection.Server);
}
}
public SqlServerBulkLoadOperation(AbstractConnection connection, Entity entity)
: base(connection, connection.Enclose(entity.OutputName())) {
_entity = entity;
_batchSize = connection.BatchSize;
UseTransaction = false;
TurnOptionOn(SqlBulkCopyOptions.TableLock);
TurnOptionOn(SqlBulkCopyOptions.UseInternalTransaction);
TurnOptionOff(SqlBulkCopyOptions.CheckConstraints);
TurnOptionOff(SqlBulkCopyOptions.FireTriggers);
TurnOptionOn(SqlBulkCopyOptions.KeepNulls);
}
public SqlServerBulkLoadOperation(AbstractConnection connection, Entity entity)
: base(connection, connection.Enclose(entity.OutputName()))
{
_entity = entity;
_batchSize = connection.BatchSize;
UseTransaction = false;
TurnOptionOn(SqlBulkCopyOptions.TableLock);
TurnOptionOn(SqlBulkCopyOptions.UseInternalTransaction);
TurnOptionOff(SqlBulkCopyOptions.CheckConstraints);
TurnOptionOff(SqlBulkCopyOptions.FireTriggers);
TurnOptionOn(SqlBulkCopyOptions.KeepNulls);
}
public override void Create(AbstractConnection connection, Process process, Entity entity) {
if (EntityExists != null && EntityExists.Exists(connection, entity)) {
process.Logger.EntityWarn(entity.Name,"Trying to create entity that already exists! {0}", entity.Name);
return;
}
var keyType = entity.IsMaster() ? FieldType.MasterKey : FieldType.PrimaryKey;
var writer = process.StarEnabled && keyType == FieldType.MasterKey ?
new FieldSqlWriter(entity.Fields, entity.CalculatedFields, process.CalculatedFields, GetRelationshipFields(process.Relationships, entity)) :
new FieldSqlWriter(entity.Fields, entity.CalculatedFields);
var primaryKey = writer.FieldType(keyType).Alias(connection.L, connection.R).Asc().Values();
var defs = new List<string>();
defs.AddRange(writer
.Reload()
.AddBatchId(entity.Index)
.AddDeleted(entity)
.AddSurrogateKey(entity.Index)
.Output()
.Alias(connection.L, connection.R)
.DataType(new SqlServerDataTypeService())
.AppendIf(" NOT NULL", keyType)
.Values());
var rowVersion = entity.Fields.WithSimpleType("rowversion").WithoutInput().WithoutOutput();
if (rowVersion.Any()) {
var alias = rowVersion.First().Alias;
defs.Add(connection.Enclose(alias) + " [ROWVERSION] NOT NULL");
}
var createSql = connection.TableQueryWriter.CreateTable(entity.OutputName(), defs);
_logger.EntityDebug(entity.Name, createSql);
var indexSql = connection.TableQueryWriter.AddUniqueClusteredIndex(entity.OutputName());
_logger.EntityDebug(entity.Name, indexSql);
var keySql = connection.TableQueryWriter.AddPrimaryKey(entity.OutputName(), primaryKey);
_logger.EntityDebug(entity.Name, keySql);
using (var cn = connection.GetConnection()) {
cn.Open();
cn.Execute(createSql);
cn.Execute(indexSql);
cn.Execute(keySql);
_logger.EntityInfo(entity.Name, "Initialized {0} in {1} on {2}.", entity.OutputName(), connection.Database, connection.Server);
}
}
public string WriteTemporary(AbstractConnection connection, string name, Fields fields, bool useAlias = true) {
var safeName = connection.Enclose(name.TrimStart("@".ToCharArray()));
var defs = useAlias ? new FieldSqlWriter(fields).Alias(connection.L, connection.R).DataType(new MySqlDataTypeService()).Write() : new FieldSqlWriter(fields).Name(connection.L, connection.R).DataType(new MySqlDataTypeService()).Write();
return string.Format(@"CREATE TEMPORARY TABLE IF NOT EXISTS {0}({1}) ENGINE = MEMORY; TRUNCATE TABLE {0};", safeName, defs);
}
public Fields Read(string name, string schema)
{
var result = new Fields();
using (var cn = _connection.GetConnection()) {
cn.Open();
var cmd = cn.CreateCommand();
cmd.CommandText = string.Format("select * from {0}{1} where 1=2;", schema.Equals(string.Empty) ? string.Empty : _connection.Enclose(schema) + ".", _connection.Enclose(name));
var reader = cmd.ExecuteReader(CommandBehavior.KeyInfo | CommandBehavior.SchemaOnly);
var table = reader.GetSchemaTable();
if (table != null)
{
var keys = table.PrimaryKey.Any() ? table.PrimaryKey.Select(c => c.ColumnName).ToArray() : Enumerable.Empty <string>().ToArray();
foreach (DataRow row in table.Rows)
{
var columnName = row["ColumnName"].ToString();
var field = new Field(keys.Contains(columnName) ? FieldType.PrimaryKey : FieldType.NonKey)
{
Name = columnName,
Type = Common.ToSimpleType(row["DataType"].ToString())
};
if (field.Type.Equals("string"))
{
field.Length = row["ColumnSize"].ToString();
}
else
{
field.Precision = Convert.ToInt32(row["NumericPrecision"]);
field.Scale = Convert.ToInt32(row["NumericScale"]);
}
if (Convert.ToBoolean(row["IsRowVersion"]))
{
field.Length = "8";
field.Type = "rowversion";
}
result.Add(field);
}
}
};
return(result);
}
