public void Parse(DBSchema schema, DBTable table)
{
QResult list = schema.Connection.ExecuteQResult(Filter);
int iName = list.GetIndex(Name);
int iSchema = list.GetIndex(Schema);
int iTable = list.GetIndex(Table);
int iColumn = list.GetIndex(Column);
int iRefSchema = list.GetIndex(ReferenceSchema);
int iRefTable = list.GetIndex(ReferenceTable);
int iRefColumn = list.GetIndex(ReferenceColumn);
foreach (object[] item in list.Values)
{
var name = item[iName].ToString();
var tab = table ?? schema.ParseTable(item[iTable].ToString());
var col = tab?.ParseColumn(item[iName].ToString());
var rtab = schema.ParseTable(item[iRefTable].ToString());
var rcol = rtab?.ParseColumn(item[iRefColumn].ToString());
if (col != null && rcol != null)
{
var reference = col.Table.Foreigns.GetByColumns(col, rcol);
if (reference == null)
{
reference = new DBForeignKey
{
Column = col,
Reference = rcol
};
col.Table.Foreigns.Add(reference);
}
reference.Name = name;
}
}
}
public override void Format(StringBuilder ddl, DBSchema schema, DDLType ddlType)
{
string tsname = $"ts{schema.Name}";
if (ddlType == DDLType.Create)
{
ddl.Append($"begin\n");
ddl.Append($" execute immediate 'create tablespace {tsname} datafile ''{tsname}.dat'' size 10M autoextend on';\n");
ddl.Append($" execute immediate 'create temporary tablespace {tsname}_temp tempfile ''{tsname}_temp.dat'' size 5M autoextend on';\n");
ddl.Append($" execute immediate 'create user {schema.Name} identified by {schema.Name} default tablespace {tsname} temporary tablespace {tsname}_temp';\n");
ddl.Append($" execute immediate 'grant create session to {schema.Name}';\n");
ddl.Append($" execute immediate 'grant create table to {schema.Name}';\n");
ddl.Append($" execute immediate 'grant create view to {schema.Name}';\n");
ddl.Append($" execute immediate 'grant create sequence to {schema.Name}';\n");
ddl.Append($" execute immediate 'grant create any procedure to {schema.Name}';\n");
ddl.Append($" execute immediate 'grant unlimited tablespace to {schema.Name}';\n");
if (!schema.Sequences.Contains("db_lob_seq"))
{
var lobSequence = new DBSequence("db_lob_seq");
schema.Sequences.Add(lobSequence);
Format(ddl, lobSequence, DDLType.Create);
}
ddl.AppendLine($"create table db_lob(oid number(18) not null primary key, lob_data blob);");
ddl.Append($"end;");
}
else if (ddlType == DDLType.Drop)
{
ddl.Append($"begin\n");
ddl.Append($" execute immediate 'drop user {schema.Name} CASCADE';\n");
ddl.Append($" execute immediate 'drop tablespace {tsname} including contents and datafiles';\n");
ddl.Append($" execute immediate 'drop tablespace {tsname}_temp including contents and datafiles';\n");
ddl.Append($"end;");
}
}
public override void Format(StringBuilder ddl, DBSchema schema, DDLType ddlType)
{
string tsname = $"ts{schema.Name}";
if (ddlType == DDLType.Create)
{
ddl.Append($"begin\n");
ddl.Append($" execute immediate 'create tablespace {tsname} datafile ''{tsname}.dat'' size 10M autoextend on';\n");
ddl.Append($" execute immediate 'create temporary tablespace {tsname}_temp tempfile ''{tsname}_temp.dat'' size 5M autoextend on';\n");
ddl.Append($" execute immediate 'create user {schema.Name} identified by {schema.Name} default tablespace {tsname} temporary tablespace {tsname}_temp';\n");
ddl.Append($" execute immediate 'grant create session to {schema.Name}';\n");
ddl.Append($" execute immediate 'grant create table to {schema.Name}';\n");
ddl.Append($" execute immediate 'grant create view to {schema.Name}';\n");
ddl.Append($" execute immediate 'grant create sequence to {schema.Name}';\n");
ddl.Append($" execute immediate 'grant create any procedure to {schema.Name}';\n");
ddl.Append($" execute immediate 'grant unlimited tablespace to {schema.Name}';\n");
ddl.Append($"end;");
}
else if (ddlType == DDLType.Drop)
{
ddl.Append($"begin\n");
ddl.Append($" execute immediate 'drop user {schema.Name} CASCADE';\n");
ddl.Append($" execute immediate 'drop tablespace {tsname} including contents and datafiles';\n");
ddl.Append($" execute immediate 'drop tablespace {tsname}_temp including contents and datafiles';\n");
ddl.Append($"end;");
}
}
private void GenerateBasic(DBSchema schema)
{
Schema = schema ?? throw new ArgumentNullException(nameof(schema));
if (TableGroup == null)
{
TableGroup = new DBTableGroup(Attribute.GroupName)
{
Schema = Schema,
DisplayName = Attribute.GroupName
};
Schema.TableGroups.Add(TableGroup);
}
if (Table == null)
{
Table = CreateTable();
}
if (Table.DisplayName.Equals(Table.Name, StringComparison.Ordinal))
{
Table.DisplayName = ItemType.Name;
}
Table.Generator = this;
Table.Group = TableGroup;
Table.Type = Attribute.TableType;
Table.BlockSize = Attribute.BlockSize;
Table.Sequence = Table.GenerateSequence(Attribute.SequenceName);
}
public override void Format(StringBuilder ddl, DBSchema schema, DDLType ddlType)
{
if (ddlType == DDLType.Create)
{
var dataFile = Path.Combine(schema.Connection.Path, $"{schema.DataBase}.mdf");
ddl.AppendLine($"create database {schema.DataBase}");
ddl.AppendLine("on");
ddl.AppendLine($"(name = {schema.DataBase}_dat,");
ddl.AppendLine($"filename = '{dataFile}',");
ddl.AppendLine("size = 10, maxsize = unlimited, filegrowth = 5MB);");
ddl.AppendLine($"alter database {schema.DataBase} set recovery simple;");
if (!schema.Sequences.Contains("db_lob_seq"))
{
var lobSequence = new DBSequence("db_lob_seq");
schema.Sequences.Add(lobSequence);
Format(ddl, lobSequence, DDLType.Create);
}
ddl.AppendLine($"create table [db_lob]([oid] bigint not null primary key, [lob_data] varbinary(max));");
}
else if (ddlType == DDLType.Drop)
{
//ddl.AppendLine($"alter database {schema.DataBase} remove file {schema.DataBase}_dat;");
ddl.AppendLine($"drop database {schema.DataBase};");
}
}
public virtual DBTable Generate(DBSchema schema)
{
Schema = schema;
if (Table == null)
{
Table = CreateTable();
}
Table.Query = $"a.{TableAttribute.Table.ItemTypeKey.SqlName} = {TableAttribute.Table.GetTypeIndex(Type)}";
if (!Schema.Tables.Contains(Type.Name))
{
Schema.Tables.Add(Table);
}
Table.TableAttribute = TableAttribute;
Table.ItemTypeIndex = Attribute.Id;
VirtualTable.BaseTable = TableAttribute.Table;
foreach (var columnAttribute in TableAttribute.Columns)
{
var virtualColumn = Table.ParseColumn(columnAttribute.ColumnName);
if (virtualColumn != null)
{
if (columnAttribute.DefaultValues != null && columnAttribute.DefaultValues.TryGetValue(Type, out var defaultValue))
{
virtualColumn.DefaultValue = defaultValue;
}
if (virtualColumn.DisplayName.Equals(virtualColumn.Name, StringComparison.Ordinal) ||
(virtualColumn.DisplayName.Equals(columnAttribute.PropertyInfo.Name, StringComparison.Ordinal)))
{
virtualColumn.DisplayName = columnAttribute.DisplayName;
}
}
}
return(Table);
}
public static void Compiler(DBSchema schema, bool check = true)
{
Helper.SetDirectory(Environment.SpecialFolder.LocalApplicationData);
//var appDir = Tool.GetDirectory();
Helper.Logs.Add(new StateInfo("Startup", "Cache Sources", "", StatusType.Information));
var groups = schema.Procedures.Select(DBProcedureProcedureTypeInvoker.Instance,
CompareType.Equal,
ProcedureTypes.Source).GroupBy((p) => p.DataName);
foreach (var group in groups)
{
if (check && IsCompiled(group))
{
Assembly TempAssembly = Assembly.LoadFrom(group.Key);
foreach (var procedure in group)
{
procedure.TempAssembly = TempAssembly;
}
}
else
{
Compile(group.Key, group, out var result, false);
if (result.Errors.Count > 0)
{
throw new Exception(DBProcedure.CompilerError(result));
}
}
}
}
public static DBSchema Generate(Assembly assembly, string schemaName)
{
var schema = new DBSchema(schemaName);
schema.Generate(new[] { assembly });
DBService.Schems.Add(schema);
return(schema);
}
public override void Format(StringBuilder ddl, DBSchema schema, DDLType ddlType)
{
base.Format(ddl, schema, ddlType);
if (ddlType == DDLType.Create)
{
ddl.AppendLine($"use {schema.DataBase};");
ddl.AppendLine($"create table db_sequence(name varchar(512) not null primary key, seq long);");
}
}
private void GenerateVirtualTables(DBSchema schema)
{
foreach (var itemType in cacheItemTypes)
{
Table.ItemTypes[itemType.Attribute.Id] = new DBItemType {
Type = itemType.Type
};
itemType.Generate(schema);
}
}
public override void DropDatabase(DBSchema schema)
{
var file = schema.Connection.DataBase;
if (File.Exists(file))
{
try { File.Delete(file); }
catch (Exception ex) { Helper.OnException(ex); }
}
}
public override void Format(StringBuilder ddl, DBSchema schema, DDLType ddlType)
{
if (ddlType == DDLType.Create)
{
ddl.AppendLine($"create table db_sequence(name varchar(512) not null primary key, seq long);");
}
else
{
ddl.AppendLine($"drop table db_sequence;");
}
}
public void FormatTablespace(StringBuilder ddl, DBSchema schema, DDLType ddlType)
{
if (ddlType == DDLType.Create)
{
ddl.AppendLine($"create tablespace ts_{schema.DataBase} owner {schema.Connection.User} location '{schema.Connection.Path}';");
}
else if (ddlType == DDLType.Drop)
{
ddl.AppendLine($"drop tablespace ts_{schema.DataBase};");
}
}
public override void Format(StringBuilder ddl, DBSchema schema, DDLType ddlType)
{
if (ddlType == DDLType.Create)
{
ddl.AppendLine($@"create database {schema.DataBase}
with owner {schema.Connection.User}
encoding = 'UTF8'
tablespace = ts_{schema.DataBase};");
}
else if (ddlType == DDLType.Drop)
{
ddl.AppendLine($"drop database {schema.DataBase};");
}
}
public virtual DBTable Generate(DBSchema schema)
{
GenerateBasic(schema);
GenerateColumns();
if (!Schema.Tables.Contains(Table.Name))
{
Schema.Tables.Add(Table);
}
GenerateReferences();
Generateindexes();
GenerateVirtualTables(schema);
Table.IsLoging = Attribute.IsLoging;
return(Table);
}
public DBTable <T> ExecuteTable <T>(string tableName, string query) where T : DBItem, new()
{
var schema = new DBSchema()
{
Name = "temp", Connection = this
};
var table = new DBTable <T>(tableName)
{
Schema = schema
};
using (var transaction = new DBTransaction(this, null, true))
{
table.Load(transaction.AddCommand(query));
}
return(table);
}
public override void CreateDatabase(DBSchema schema, DBConnection connection)
{
//DropDatabase(schema);
connection.DataBase = "postgres";
var ddl = new StringBuilder();
FormatTablespace(ddl, schema, DDLType.Create);
connection.ExecuteGoQuery(ddl.ToString(), true);
ddl.Clear();
Format(ddl, schema, DDLType.Create);
connection.ExecuteGoQuery(ddl.ToString(), true);
connection.DataBase = schema.Name;
CreateSchema(schema, connection);
}
public override void DropDatabase(DBSchema schema)
{
var tempDatabase = schema.Connection.DataBase;
try
{
schema.Connection.DataBase = "postgres";
var ddl = new StringBuilder();
Format(ddl, schema, DDLType.Drop);
schema.Connection.ExecuteQuery(ddl.ToString(), true, DBExecuteType.NoReader);
ddl.Clear();
FormatTablespace(ddl, schema, DDLType.Drop);
schema.Connection.ExecuteQuery(ddl.ToString(), true, DBExecuteType.NoReader);
}
catch (Exception ex) { Helper.OnException(ex); }
finally { schema.Connection.DataBase = tempDatabase; }
}
public override void Format(StringBuilder ddl, DBSchema schema, DDLType ddlType)
{
if (ddlType == DDLType.Create)
{
var dataFile = Path.Combine(schema.Connection.Path, $"{schema.DataBase}.mdf");
ddl.AppendLine($"create database {schema.DataBase}");
ddl.AppendLine("on");
ddl.AppendLine($"(name = {schema.DataBase}_dat,");
ddl.AppendLine($"filename = '{dataFile}',");
ddl.AppendLine("size = 10, maxsize = unlimited, filegrowth = 5MB);");
ddl.AppendLine($"alter database {schema.DataBase} set recovery simple;");
}
else if (ddlType == DDLType.Drop)
{
//ddl.AppendLine($"alter database {schema.DataBase} remove file {schema.DataBase}_dat;");
ddl.AppendLine($"drop database {schema.DataBase};");
}
}
public static void CommitChanges(DBSchema schema, StringBuilder builder)
{
foreach (var command in schema.Connection.SplitGoQuery(builder.ToString()))
{
try
{
schema.Connection.ExecuteQuery(command);
}
catch (Exception ex)
{
if (ex.Message.IndexOf("already exist", StringComparison.OrdinalIgnoreCase) >= 0 ||
ex.Message.IndexOf("42701:") >= 0 ||
ex.Message.IndexOf("42P07:") >= 0 ||
ex.Message.IndexOf("42710:") >= 0)
{
continue;
}
throw ex;
}
}
builder.Clear();
}
public override void CreateDatabase(DBSchema schema, DBConnection connection)
{
//DropDatabase(schema);
var ddl = new StringBuilder();
Format(ddl, schema, DDLType.Create);
connection.ExecuteGoQuery(ddl.ToString(), true);
if (connection.Schema?.Length > 0)
{
connection.User = schema.Name;
}
if (string.IsNullOrEmpty(connection.DataBase))
{
connection.DataBase = schema.Name;
}
ddl.Clear();
Format(ddl, schema);
connection.ExecuteGoQuery(ddl.ToString(), true);
}
public static string BuildChangesQuery(DBSchema schema)
{
var builder = new StringBuilder();
foreach (var item in Changes.ToList())
{
if (schema != null && item.Item.Schema != schema)
{
continue;
}
string val = item.Generate();
if (item.Check && !string.IsNullOrEmpty(val))
{
builder.Append("-- ");
builder.AppendLine(item.ToString());
builder.AppendLine(val);
builder.AppendLine("go");
builder.AppendLine();
}
item.Item.OldName = null;
Changes.Remove(item);
}
return(builder.ToString());
}
public DBTableGroupList(DBSchema schema) : base(schema)
{
Indexes.Add(DBTableGroupGroupNameInvoker.Instance);
}
public DBProcedureList(DBSchema schema) : base(schema)
{
Indexes.Add(DBProcedure.GroupNameInvoker.Instance);
Indexes.Add(DBProcedure.DataNameInvoker.Instance);
Indexes.Add(DBProcedure.ProcedureTypeInvoker.Instance);
}
public DBTransaction GetSubTransaction(DBSchema schema, bool checkSelf = true)
{
return(GetSubTransaction(schema.Connection, checkSelf));
}
public static void LoadRefrences(DBSchema schema, DBTable table = null)
{
var info = SMReference.Generate(schema, table);
info.Parse(schema, table);
}
public DBTableList(DBSchema schema) : base(schema)
{
Indexes.Add(DBTable.GroupNameInvoker.Instance);
ApplyDefaultSort();
}
public ExecuteEventArgs(DBSchema schema, DBTable table, string query)
{
Schema = schema;
Table = table;
Query = query;
}
public static SMReference Generate(DBSchema schema, DBTable table)
{
var info = new SMReference
{
SourceTable = "",
Name = "constraint_name",
Schema = "schema_name",
Table = "table_name",
Column = "column_name",
ReferenceSchema = "reference_schema_name",
ReferenceTable = "reference_table_name",
ReferenceColumn = "reference_column_name",
Filter = @"SELECT
KCU1.CONSTRAINT_NAME AS constraint_name
,KCU1.CONSTRAINT_SCHEMA AS schema_name
,KCU1.TABLE_NAME AS table_name
,KCU1.COLUMN_NAME AS column_name
,KCU2.CONSTRAINT_NAME AS reference_constraint_name
,KCU1.CONSTRAINT_SCHEMA AS reference_schema
,KCU2.TABLE_NAME AS reference_table_name
,KCU2.COLUMN_NAME AS reference_column_name
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS AS RC
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS KCU1
ON KCU1.CONSTRAINT_CATALOG = RC.CONSTRAINT_CATALOG
AND KCU1.CONSTRAINT_SCHEMA = RC.CONSTRAINT_SCHEMA
AND KCU1.CONSTRAINT_NAME = RC.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS KCU2
ON KCU2.CONSTRAINT_CATALOG = RC.UNIQUE_CONSTRAINT_CATALOG
AND KCU2.CONSTRAINT_SCHEMA = RC.UNIQUE_CONSTRAINT_SCHEMA
AND KCU2.CONSTRAINT_NAME = RC.UNIQUE_CONSTRAINT_NAME
AND KCU2.ORDINAL_POSITION = KCU1.ORDINAL_POSITION "
};
if (schema.Connection.Schema != null || table != null)
{
var schemaFilter = schema.Connection.Schema == null ? null : string.Format("RC.CONSTRAINT_SCHEMA='{0}'", schema.Connection.Schema);
var tableFilter = table == null ? null : string.Format("KCU1.TABLE_NAME='{0}'", table.Name);
info.Filter += string.Format("\nWHERE {0}{1}{2}", schemaFilter, schemaFilter != null && tableFilter != null ? " AND " : " ", tableFilter);
}
if (schema.Connection.System == DBSystem.SQLite)
{
info.Filter = null;//TODO
}
if (schema.Connection.System == DBSystem.Oracle)
{
info.Filter = @"SELECT
a.constraint_name,
c.owner as schema_name,
a.table_name,
a.column_name,
c_pk.constraint_name as reference_constraint_name
c.r_owner as reference_schema,
c_pk.table_name as rererence_table_name,
c_pk.column_name as rererence_column_name
FROM all_cons_columns a
JOIN all_constraints c ON a.owner = c.owner
AND a.constraint_name = c.constraint_name
JOIN all_constraints c_pk ON c.r_owner = c_pk.owner
AND c.r_constraint_name = c_pk.constraint_name
WHERE c.constraint_type = 'R'";
}
return(info);
}
public DBSchemaItemList(DBSchema schema)
: base()
{
Indexes.Add(DBSchemaItem.NameInvoker <T> .Instance);
Schema = schema;
}