public void Start()
{
var db = new DatabaseSchema(new MySqlSchemaProvider(), Common.MySqlConnectionString);
var tables = db.Tables;
var table = tables.FirstOrDefault();
var desc = table.Columns[0].Description;
}
public override DatabaseSchemaCollection GetDatabases ()
{
DatabaseSchemaCollection databases = new DatabaseSchemaCollection ();
using (IPooledDbConnection conn = connectionPool.Request ()) {
using (IDbCommand command = conn.CreateCommand ("SHOW DATABASES;")) {
try {
using (IDataReader r = command.ExecuteReader()) {
while (r.Read ()) {
DatabaseSchema db = new DatabaseSchema (this);
db.Name = r.GetString (0);
databases.Add (db);
}
r.Close ();
}
} catch (Exception e) {
QueryService.RaiseException (e);
} finally {
conn.Release ();
}
}
}
return databases;
}
public override IDatabaseSchema Load(DbConfiguration cfg)
{
var databaseSchema = new DatabaseSchema();
ColumnInfo[] allColumns = null;
ForeignKeyInfo[] allFks = null;
using (var ctx = cfg.CreateDbContext())
{
InitConnection(ctx.Connection);
using (var reader = ctx.DbHelper.ExecuteReader(AllColumnsSql))
allColumns = reader.ToList<ColumnInfo>().ToArray();
using (var reader = ctx.DbHelper.ExecuteReader(AllFKsSql))
allFks = reader.ToList<ForeignKeyInfo>().ToArray();
}
Dictionary<string, TableSchema> tables = new Dictionary<string,TableSchema>();
foreach (var c in allColumns)
{
TableSchema table = null;
if (!tables.TryGetValue(c.TableName, out table))
{
table = new TableSchema { TableName = c.TableName, IsView = c.IsView };
tables[c.TableName] = table;
}
var key = allFks.FirstOrDefault(p => p.Type == "P"
&& p.ThisTableName == c.TableName
&& p.ThisKey == c.ColumnName);
c.IsPrimaryKey = key != null;
var column = ToColumn(c);
table.AddColumn(column);
}
foreach(var item in allFks.Where(p=>p.OtherTableName.HasValue()))
{
TableSchema thisTable = tables[item.OtherTableName];
TableSchema otherTable = tables[item.ThisTableName];
IColumnSchema thisKey = thisTable.AllColumns.FirstOrDefault(p=>p.ColumnName == item.OtherKey);
IColumnSchema otherKey = otherTable.AllColumns.FirstOrDefault(p => p.ColumnName == item.ThisKey);
thisTable.AddFK(new ForeignKeySchema
{
ThisTable = thisTable,
Name = item.Name ,
ThisKey = thisKey,
OtherTable = otherTable,
OtherKey = otherKey
});
}
databaseSchema.Tables = tables.Values.Where(p => !p.IsView).ToArray();
databaseSchema.Views = tables.Values.Where(p => p.IsView).ToArray();
return databaseSchema;
}
public EntityContext Generate(DatabaseSchema databaseSchema)
{
// only DeepLoad when in ignore mode
databaseSchema.DeepLoad = !Settings.InclusionMode;
var entityContext = new EntityContext();
entityContext.DatabaseName = databaseSchema.Name;
string dataContextName = StringUtil.ToPascalCase(databaseSchema.Name) + "Context";
dataContextName = _namer.UniqueClassName(dataContextName);
entityContext.ClassName = dataContextName;
foreach (TableSchema t in databaseSchema.Tables)
{
if (Settings.IsIgnored(t.FullName))
{
Debug.WriteLine("Skipping Table: " + t.FullName);
}
else if (IsManyToMany(t))
{
CreateManyToMany(entityContext, t);
}
else
{
Debug.WriteLine("Getting Table Schema: " + t.FullName);
GetEntity(entityContext, t);
}
OnSchemaItemProcessed(t.FullName);
}
return entityContext;
}
public CommandSchema[] GetCommands(string connectionString, DatabaseSchema database)
{
string str = string.Format("SELECT ROUTINE_NAME, '' OWNER, CREATED FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA = '{0}' AND ROUTINE_TYPE = 'PROCEDURE' ORDER BY 1", database.Name);
List<CommandSchema> list = new List<CommandSchema>();
using (DbConnection connection = CreateConnection(connectionString))
{
connection.Open();
DbCommand command = connection.CreateCommand();
command.CommandText = str;
command.Connection = connection;
using (IDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection))
{
while (reader.Read())
{
list.Add(new CommandSchema(database, reader.GetString(0), reader.GetString(1), reader.GetDateTime(2)));
}
if (!reader.IsClosed)
{
reader.Close();
}
}
if (connection.State != ConnectionState.Closed)
{
connection.Close();
}
}
return list.ToArray();
}
public override void SaveDatabaseSchema(DatabaseSchema databaseSchema)
{
var connectionString = RepositoryLocation;
using (var connection = new System.Data.SqlClient.SqlConnection(connectionString))
{
connection.Open();
using (var command = connection.CreateCommand())
{
command.CommandText = "DatabaseSchema_Save";
command.CommandType = System.Data.CommandType.StoredProcedure;
command.Parameters.Add(new System.Data.SqlClient.SqlParameter(){
ParameterName = "@databaseName",
SqlDbType = System.Data.SqlDbType.NVarChar,
Direction = System.Data.ParameterDirection.Input,
Value = databaseSchema.Name
});
command.Parameters.Add(new System.Data.SqlClient.SqlParameter(){
ParameterName = "@databaseVersion",
SqlDbType = System.Data.SqlDbType.NVarChar,
Direction = System.Data.ParameterDirection.Input,
Value = databaseSchema.Version.ToString()
});
command.Parameters.Add(new System.Data.SqlClient.SqlParameter(){
ParameterName = "@storedProcedures",
SqlDbType = System.Data.SqlDbType.Structured,
TypeName = "dbo.StoredProcedures",
Value = GetStoredProcedureTable(databaseSchema.StoredProcedures),
});
Int32 returnValue = command.ExecuteNonQuery();
}
}
}
public override void SaveDatabaseSchema(DatabaseSchema databaseSchema)
{
XmlSerializer serializer = new XmlSerializer(typeof (DatabaseSchema));
using (var fileStream = new System.IO.FileStream(RepositoryLocation,FileMode.OpenOrCreate))
{
serializer.Serialize(fileStream, databaseSchema);
}
}
public DatabaseManager(IDbConnection connection, IDatabaseImplementation implementation, IDatabaseSchemaFactory databaseSchemaFactory, string product)
{
_connection = connection;
_implementation = implementation;
_product = product;
_databaseSchemaFactory = databaseSchemaFactory;
_schema = null;
}
public void Check_ForeignKeys()
{
var dbSchema = new DatabaseSchema(_provider, ConnectionString);
var keys = _provider.GetTableKeys(ConnectionString,
new TableSchema(dbSchema, "Album", string.Empty, DateTime.Today));
keys.Length.ShouldBe(1, "Invalid number of keys returned");
var keyNames = keys.Select(k => k.Name).ToArray();
keyNames.ShouldContain("FK_AlbumArtistId", "In correct key name");
}
public virtual IDatabaseSchema Load(DbConfiguration cfg)
{
var databaseSchema = new DatabaseSchema();
ColumnInfo[] allColumns = null;
ConstraintInfo[] allConstraints = null;
ForeignKeyInfo[] allFks = null;
using (var ctx = cfg.CreateDbContext())
{
InitConnection(ctx.Connection);
using (var reader = ctx.DbHelper.ExecuteReader(AllColumnsSql))
allColumns = reader.ToList<ColumnInfo>().ToArray();
using (var reader = ctx.DbHelper.ExecuteReader(AllConstraintsSql))
allConstraints = reader.ToList<ConstraintInfo>().ToArray();
using (var reader = ctx.DbHelper.ExecuteReader(AllFKsSql))
allFks = reader.ToList<ForeignKeyInfo>().ToArray();
}
PopulateConstraints(allColumns, allConstraints);
Dictionary<string, TableSchema> tables = new Dictionary<string, TableSchema>();
PopulateTables(allColumns, tables);
foreach (var item in allFks)
{
TableSchema thisTable = null;
TableSchema otherTable = null;
IColumnSchema thisKey = null;
IColumnSchema otherKey = null;
var key = string.Format("{0}.{1}", item.ThisSchema, item.ThisTableName);
if (tables.TryGetValue(key, out thisTable))
thisKey = thisTable.AllColumns.FirstOrDefault(p => p.ColumnName == item.ThisKey);
key = string.Format("{0}.{1}", item.OtherSchema, item.OtherTableName);
if(tables.TryGetValue(key, out otherTable))
otherKey = otherTable.AllColumns.FirstOrDefault(p => p.ColumnName == item.OtherKey);
thisTable.AddFK(new ForeignKeySchema
{
ThisTable = thisTable
, Name = item.Name
, ThisKey = thisKey
, OtherTable = otherTable
, OtherKey = otherKey });
}
databaseSchema.Tables = tables.Values.Where(p => !p.IsView).ToArray();
databaseSchema.Views = tables.Values.Where(p => p.IsView).ToArray();
return databaseSchema;
}
public override void SaveDatabaseSchema(DatabaseSchema databaseSchema)
{
if (!Directory.Exists(RepositoryLocation))
{
Directory.CreateDirectory(RepositoryLocation);
}
var dbDir = RepositoryLocation + @"\" + databaseSchema.Name;
if (!Directory.Exists(dbDir))
{
Directory.CreateDirectory(dbDir);
}
var versionDir = dbDir + @"\" + databaseSchema.Version.ToString();
if (Directory.Exists(versionDir))
{
Directory.Delete(versionDir,true);
}
Directory.CreateDirectory(versionDir);
var repoDir = versionDir;
var dbInfo = repoDir + @"\DatabaseSchema.info";
var dbSerializer = new XmlSerializer(typeof(DatabaseSchema));
using (var fileStream = new FileStream(dbInfo, FileMode.OpenOrCreate))
{
dbSerializer.Serialize(fileStream,databaseSchema);
fileStream.Position = 0;
}
//Ugly
XmlDocument doc = new XmlDocument();
doc.Load(dbInfo);
var sprocsNode = doc.DocumentElement.SelectSingleNode("StoredProcedures");
doc.DocumentElement.RemoveChild(sprocsNode);
doc.Save(dbInfo);
var sprocDir = repoDir + @"\StoredProcedures";
Directory.CreateDirectory(sprocDir);
var sprocSerializer = new XmlSerializer(typeof(StoredProcedure));
foreach (var sproc in databaseSchema.StoredProcedures)
{
var sprocInfo = sprocDir + @"\" + sproc.Name + ".info";
using (var fileStream = new FileStream(sprocInfo, FileMode.OpenOrCreate))
{
sprocSerializer.Serialize(fileStream,sproc);
}
}
}
public override DatabaseSchema LoadDatabaseSchema(string databaseName, DatabaseVersion version)
{
DatabaseSchema result = new DatabaseSchema();
var connectionString = RepositoryLocation;
using (var connection = new System.Data.SqlClient.SqlConnection(connectionString))
{
connection.Open();
using (var command = connection.CreateCommand())
{
command.CommandText = "DatabaseSchema_Load";
command.CommandType = System.Data.CommandType.StoredProcedure;
command.Parameters.Add(new System.Data.SqlClient.SqlParameter(){
ParameterName = "@databaseName",
SqlDbType = System.Data.SqlDbType.NVarChar,
Direction = System.Data.ParameterDirection.Input,
Value = databaseName
});
command.Parameters.Add(new System.Data.SqlClient.SqlParameter(){
ParameterName = "@databaseVersion",
SqlDbType = System.Data.SqlDbType.NVarChar,
Direction = System.Data.ParameterDirection.Input,
Value = version.ToString()
});
command.Parameters.Add(new System.Data.SqlClient.SqlParameter(){
ParameterName = "@ReturnValue",
Direction = System.Data.ParameterDirection.ReturnValue,
});
using(var reader = command.ExecuteReader())
{
while (reader.Read())
{
result.Name = reader["Name"].ToString();
result.Version = new DatabaseVersion(reader["Version"].ToString());
}
reader.NextResult();
while (reader.Read())
{
Materialize(result, reader);
}
}
}
}
return result;
}
public override DatabaseSchemaCollection GetDatabases()
{
Console.WriteLine("GetDatabases :D");
DatabaseSchemaCollection databases = new DatabaseSchemaCollection ();
using (IPooledDbConnection conn = connectionPool.Request ()) {
Console.WriteLine("conn:"+conn.ToString());
MongoDbConnection connection = (MongoDbConnection) conn.DbConnection;
Console.WriteLine("connection:"+connection.ToString());
foreach(string dbName in connection.getDatabases()){
DatabaseSchema db = new DatabaseSchema(this);
Console.WriteLine("db add:"+dbName);
db.Name = dbName;
databases.Add(db);
}
}
return databases;
}
public override TableSchema[] GetTables(string connectionString, DatabaseSchema database)
{
List<TableSchema> tableSchemas = new List<TableSchema>();
var oleConn = new NpgsqlConnection();
oleConn.ConnectionString = connectionString;
using (var conn = new NpgsqlConnection(connectionString))
{
this.ConnectionString = connectionString;
string[] restrictions = new string[4];
restrictions[0] = conn.Database;
restrictions[1] = "public";
conn.ConnectionString = connectionString;
conn.Open();
//var s = conn.GetSchema("Tables",new string[] { conn.Database, "public", null, null });
List<string> tableNames = new List<string>();
Dictionary<string, Dictionary<string, ForeignKeyInfo>> fkMap = null;
DataTable tt = conn.GetSchema("Tables", restrictions);
foreach (DataRow tRow in tt.Rows)
{
tableNames.Add(tRow[2].ToString());
}
fkMap = this.GetForeignKeys(
conn, tableNames);
foreach (DataRow tRow in tt.Rows)
{
var tableName = tRow[2].ToString();
tableSchemas.Add(this.GetTable(tableName, conn, oleConn, fkMap.ContainsKey(tableName) ? fkMap[tableName] : null));
}
oleConn.Close();
//tt.WriteXml("1.xml");
}
return tableSchemas.ToArray();
}
public override TableSchema[] GetTables(string connectionString, DatabaseSchema database)
{
List<TableSchema> tableSchemas = new List<TableSchema>();
OleDbConnection oleConn = new OleDbConnection();
oleConn.ConnectionString = "Provider=SQLOLEDB;" + connectionString;
using (SqlConnection conn = new SqlConnection())
{
this.ConnectionString = connectionString;
string[] restrictions = new string[4];
restrictions[1] = "dbo";
conn.ConnectionString = connectionString;
conn.Open();
List<string> tableNames = new List<string>();
Dictionary<string, Dictionary<string, ForeignKeyInfo>> fkMap = null;
DataTable tt = conn.GetSchema("Tables", restrictions);
foreach (DataRow tRow in tt.Rows)
{
tableNames.Add(tRow[2].ToString());
}
fkMap = this.GetForeignKeys(
conn, tableNames);
foreach (DataRow tRow in tt.Rows)
{
var tableName = tRow[2].ToString();
tableSchemas.Add(this.GetTable(tableName, conn, oleConn, fkMap.ContainsKey(tableName) ? fkMap[tableName] : null));
}
oleConn.Close();
//tt.WriteXml("1.xml");
}
return tableSchemas.ToArray();
}
public static void Main()
{
string path = System.IO.Path.Combine(AppDomain.CurrentDomain.BaseDirectory, @"..\..\StoredProcedures.cst");
var engine = new TemplateEngine(new DefaultEngineHost(System.IO.Path.GetDirectoryName(path)));
CompileTemplateResult result = engine.Compile(path);
if (result.Errors.Count == 0) {
var database = new DatabaseSchema(new SqlSchemaProvider(), @"Server=.;Database=PetShop;Integrated Security=True;");
TableSchema table = database.Tables["Inventory"];
CodeTemplate template = result.CreateTemplateInstance();
template.SetProperty("SourceTable", table);
template.SetProperty("IncludeDrop", false);
template.SetProperty("InsertPrefix", "Insert");
template.Render(Console.Out);
} else {
foreach (var error in result.Errors)
Console.Error.WriteLine(error.ToString());
}
Console.WriteLine("\r\nPress any key to continue.");
Console.ReadKey();
}
public ExpressionFormatter(ICommandBuilder commandBuilder, DatabaseSchema schema) : base(() => schema.Operators)
{
_commandBuilder = commandBuilder;
_schema = schema;
_simpleReferenceFormatter = new SimpleReferenceFormatter(_schema, _commandBuilder);
}
protected override void OnReset()
{
DatabaseSchema.ClearCache();
_schema = DatabaseSchema.Get(_connectionProvider, _providerHelper);
}
private static DatabaseStoredProcedure FindStoredProcedure(DatabaseSchema schema, string name)
{
return(schema.StoredProcedures.Find(delegate(DatabaseStoredProcedure x) { return x.Name.Equals(name, StringComparison.OrdinalIgnoreCase); }));
}
private static DatabaseView FindView(DatabaseSchema schema, string name)
{
return(schema.Views.Find(delegate(DatabaseView x) { return x.Name.Equals(name, StringComparison.OrdinalIgnoreCase); }));
}
public IEnumerable <ExplorerItem> GetItemsAndCode(string nameSpace, string typeName)
{
typeName = ConvertToCompilable(typeName, false);
var connectionString = _cxInfo.DatabaseInfo.CustomCxString;
var provider = ProviderHelper.GetProvider(ProviderName).GetDataProvider(connectionString);
using (var db = new DataConnection(provider, connectionString))
{
db.CommandTimeout = CommandTimeout;
_dataProvider = db.DataProvider;
_sqlBuilder = _dataProvider.CreateSqlBuilder();
var options = new GetSchemaOptions();
var includeSchemas = (string)_cxInfo.DriverData.Element("includeSchemas");
if (includeSchemas != null)
{
options.IncludedSchemas = includeSchemas.Split(',', ';');
}
var excludeSchemas = (string)_cxInfo.DriverData.Element("excludeSchemas");
if (excludeSchemas != null)
{
options.ExcludedSchemas = excludeSchemas.Split(',', ';');
}
var includeCatalogs = (string)_cxInfo.DriverData.Element("includeCatalogs");
if (includeCatalogs != null)
{
options.IncludedCatalogs = includeCatalogs.Split(',', ';');
}
var excludeCatalogs = (string)_cxInfo.DriverData.Element("excludeCatalogs");
if (excludeCatalogs != null)
{
options.ExcludedCatalogs = excludeCatalogs.Split(',', ';');
}
options.GetProcedures = (string)_cxInfo.DriverData.Element("excludeRoutines") != "true";
_schema = _dataProvider.GetSchemaProvider().GetSchema(db, options);
ConvertSchema(typeName);
}
Code
.AppendLine("using System;")
.AppendLine("using System.Collections;")
.AppendLine("using System.Collections.Generic;")
.AppendLine("using System.Data;")
.AppendLine("using System.Reflection;")
.AppendLine("using System.Linq;")
.AppendLine("using LinqToDB;")
.AppendLine("using LinqToDB.Common;")
.AppendLine("using LinqToDB.Data;")
.AppendLine("using LinqToDB.Mapping;")
.AppendLine("using System.Net.NetworkInformation;")
;
if (_schema.Procedures.Any(_ => _.IsAggregateFunction))
{
Code
.AppendLine("using System.Linq.Expressions;")
;
}
if (_schema.ProviderSpecificTypeNamespace.NotNullNorWhiteSpace())
{
Code.AppendLine($"using {_schema.ProviderSpecificTypeNamespace};");
}
var providerInfo = ProviderHelper.GetProvider(ProviderName);
References.AddRange(providerInfo.GetAssemblyLocation(connectionString));
if (providerInfo.Provider.AdditionalNamespaces != null)
{
foreach (var ns in providerInfo.Provider.AdditionalNamespaces)
{
Code.AppendLine($"using {ns};");
}
}
Code
.AppendLine($"namespace {nameSpace}")
.AppendLine("{")
.AppendLine($" public class {typeName} : LinqToDB.LINQPad.LINQPadDataConnection")
.AppendLine(" {")
.AppendLine($" public {typeName}(string provider, string connectionString)")
.AppendLine(" : base(provider, connectionString)")
.AppendLine(" {")
.AppendLine($" CommandTimeout = {CommandTimeout};")
.AppendLine(" }")
.AppendLine($" public {typeName}()")
.AppendLine($" : base({CSharpTools.ToStringLiteral(ProviderName)}, {CSharpTools.ToStringLiteral(connectionString)})")
.AppendLine(" {")
.AppendLine($" CommandTimeout = {CommandTimeout};")
.AppendLine(" }")
;
if (ProviderName == LinqToDB.ProviderName.PostgreSQL)
{
PreprocessPostgreSQLSchema();
}
var schemas =
(
from t in
(
from t in _schema.Tables
select new { t.IsDefaultSchema, t.SchemaName, Table = t, Procedure = (ProcedureSchema)null }
)
.Union
(
from p in _schema.Procedures
select new { p.IsDefaultSchema, p.SchemaName, Table = (TableSchema)null, Procedure = p }
)
group t by new { t.IsDefaultSchema, t.SchemaName } into gr
orderby !gr.Key.IsDefaultSchema, gr.Key.SchemaName
select new
{
gr.Key,
Tables = gr.Where(t => t.Table != null).Select(t => t.Table).ToList(),
Procedures = gr.Where(t => t.Procedure != null).Select(t => t.Procedure).ToList(),
}
)
.ToList();
foreach (var s in schemas)
{
var items = new List <ExplorerItem>();
if (s.Tables.Any(t => !t.IsView && !t.IsProcedureResult))
{
items.Add(GetTables("Tables", ExplorerIcon.Table, s.Tables.Where(t => !t.IsView && !t.IsProcedureResult)));
}
if (s.Tables.Any(t => t.IsView))
{
items.Add(GetTables("Views", ExplorerIcon.View, s.Tables.Where(t => t.IsView)));
}
if (!_cxInfo.DynamicSchemaOptions.ExcludeRoutines && s.Procedures.Any(p => p.IsLoaded && !p.IsFunction))
{
items.Add(GetProcedures(
"Stored Procs",
ExplorerIcon.StoredProc,
s.Procedures.Where(p => p.IsLoaded && !p.IsFunction).ToList()));
}
if (s.Procedures.Any(p => p.IsLoaded && p.IsTableFunction))
{
items.Add(GetProcedures(
"Table Functions",
ExplorerIcon.TableFunction,
s.Procedures.Where(p => p.IsLoaded && p.IsTableFunction).ToList()));
}
if (s.Procedures.Any(p => p.IsFunction && !p.IsTableFunction))
{
items.Add(GetProcedures(
"Scalar Functions",
ExplorerIcon.ScalarFunction,
s.Procedures.Where(p => p.IsFunction && !p.IsTableFunction).ToList()));
}
if (schemas.Count == 1)
{
foreach (var item in items)
{
yield return(item);
}
}
else
{
yield return(new ExplorerItem(
s.Key.SchemaName.IsNullOrEmpty() ? s.Key.IsDefaultSchema ? "(default)" : "empty" : s.Key.SchemaName,
ExplorerItemKind.Schema,
ExplorerIcon.Schema)
{
Children = items
});
}
}
Code
.AppendLine(" }")
.AppendLine(_classCode.ToString())
.AppendLine("}")
;
#if DEBUG
Debug.WriteLine(Code.ToString());
#endif
}
public void ExportSchemaToJSON(string outputJsonSchema)
{
Console.WriteLine("Reading Database...");
using (var cn = CreateDbConnection())
{
var tables = cn.Query <Table>(@"
SELECT
t.TABLE_CATALOG as [Database],
t.TABLE_SCHEMA as [TableSchema],
t.TABLE_NAME as [TableName],
CASE WHEN t.TABLE_TYPE='VIEW' THEN 'VIEW' ELSE 'TABLE' END as [TableType],
ep.value as [TableDescription],
pk.[name] as [PrimaryKeyName],
CASE WHEN pk.[type]=1 THEN 1 ELSE 0 END as [PrimaryKeyIsClustered]
FROM INFORMATION_SCHEMA.TABLES t
INNER JOIN sys.schemas sc ON t.TABLE_SCHEMA = sc.[name]
LEFT OUTER JOIN sys.objects so ON so.schema_id = so.schema_id AND so.[name] = t.TABLE_NAME AND so.[Type] IN ('U','V')
LEFT OUTER JOIN sys.extended_properties ep ON ep.name='MS_Description' AND ep.class = 1 AND ep.major_id = so.object_id AND ep.minor_id = 0
LEFT OUTER JOIN sys.indexes pk ON so.object_id = pk.object_id AND pk.is_primary_key = 1
WHERE t.TABLE_TYPE='BASE TABLE' OR t.TABLE_TYPE='VIEW'
ORDER BY t.TABLE_SCHEMA, t.TABLE_TYPE, t.TABLE_NAME
").AsList();
// Based on PetaPoco T4 Templates (https://github.com/CollaboratingPlatypus/PetaPoco/blob/development/T4Templates/PetaPoco.Core.ttinclude)
var allColumns = cn.Query <ColumnTmp>(@"
IF OBJECT_ID('tempdb..#PrimaryKeyColumns') IS NOT NULL DROP TABLE #PrimaryKeyColumns;
SELECT cu.TABLE_SCHEMA, cu.TABLE_NAME, cu.COLUMN_NAME, cu.ORDINAL_POSITION INTO #PrimaryKeyColumns
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE cu INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc ON cu.TABLE_SCHEMA COLLATE DATABASE_DEFAULT = tc.CONSTRAINT_SCHEMA COLLATE DATABASE_DEFAULT AND cu.TABLE_NAME = tc.TABLE_NAME AND cu.CONSTRAINT_NAME = tc.CONSTRAINT_NAME
WHERE tc.CONSTRAINT_TYPE = 'PRIMARY KEY';
IF OBJECT_ID('tempdb..#ForeignKeyColumns') IS NOT NULL DROP TABLE #ForeignKeyColumns;
SELECT DISTINCT cu.TABLE_SCHEMA, cu.TABLE_NAME, cu.COLUMN_NAME INTO #ForeignKeyColumns
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE cu INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc ON cu.TABLE_SCHEMA COLLATE DATABASE_DEFAULT = tc.CONSTRAINT_SCHEMA COLLATE DATABASE_DEFAULT AND cu.TABLE_NAME = tc.TABLE_NAME AND cu.CONSTRAINT_NAME = tc.CONSTRAINT_NAME
WHERE tc.CONSTRAINT_TYPE = 'FOREIGN KEY';
SELECT
c.TABLE_CATALOG as [Database],
c.TABLE_SCHEMA as [TableSchema],
c.TABLE_NAME as TableName,
c.COLUMN_NAME as ColumnName,
c.ORDINAL_POSITION as OrdinalPosition,
c.COLUMN_DEFAULT as DefaultSetting,
sc.is_nullable as IsNullable, -- c.IS_NULLABLE as IsNullable,
c.DATA_TYPE as SqlDataType,
c.CHARACTER_MAXIMUM_LENGTH as MaxLength,
c.DATETIME_PRECISION as DateTimePrecision,
c.NUMERIC_SCALE as [NumericScale],
c.NUMERIC_PRECISION as [NumericPrecision],
sc.is_identity as IsIdentity,
sc.is_computed as IsComputed,
sc.is_rowguidcol as IsRowGuid,
CONVERT( bit, ISNULL( pk.ORDINAL_POSITION, 0 ) ) as [IsPrimaryKeyMember],
pk.ORDINAL_POSITION as [PrimaryKeyOrdinalPosition],
CONVERT( bit, CASE WHEN fk.COLUMN_NAME IS NOT NULL THEN 1 ELSE 0 END ) as [IsForeignKeyMember],
ep.value as [ColumnDescription]
FROM INFORMATION_SCHEMA.COLUMNS c
INNER JOIN sys.schemas ss ON c.TABLE_SCHEMA = ss.[name]
LEFT OUTER JOIN sys.tables st ON st.schema_id = ss.schema_id AND st.[name] = c.TABLE_NAME
LEFT OUTER JOIN sys.views sv ON sv.schema_id = ss.schema_id AND sv.[name] = c.TABLE_NAME
INNER JOIN sys.all_columns sc ON sc.object_id = COALESCE( st.object_id, sv.object_id ) AND c.COLUMN_NAME = sc.[name]
LEFT OUTER JOIN sys.extended_properties ep ON ep.name='MS_Description' AND ep.class = 1 AND ep.major_id = st.object_id AND ep.minor_id = sc.column_id
LEFT OUTER JOIN #PrimaryKeyColumns pk ON c.TABLE_SCHEMA = pk.TABLE_SCHEMA AND c.TABLE_NAME = pk.TABLE_NAME AND c.COLUMN_NAME = pk.COLUMN_NAME
LEFT OUTER JOIN #ForeignKeyColumns fk ON c.TABLE_SCHEMA = fk.TABLE_SCHEMA AND c.TABLE_NAME = fk.TABLE_NAME AND c.COLUMN_NAME = fk.COLUMN_NAME
ORDER BY 1,2,3,OrdinalPosition ASC
").AsList();
var fks = cn.Query <ForeignKey>(@"
SELECT
i.name as PrimaryKeyName,
pksch.name as PKTableSchema,
pk.name as PKTableName,
f.name as [ForeignKeyConstraintName],
ep.value as [ForeignKeyDescription],
fksch.name as FKTableSchema,
fk.name as FKTableName,
f.delete_referential_action_desc as [OnDeleteCascade], -- NO_ACTION, CASCADE, SET_NULL, SET_DEFAULT
f.update_referential_action_desc as [OnUpdateCascade], -- NO_ACTION, CASCADE, SET_NULL, SET_DEFAULT
f.is_system_named as [IsSystemNamed],
f.is_disabled as [IsNotEnforced]
--,k.constraint_column_id as PKColumnOrdinalPosition,
--pkCols.name as PKColumnName,
--fkCols.name as FKColumnName
FROM
sys.objects pk
INNER JOIN sys.foreign_keys as f ON pk.object_id = f.referenced_object_id
--INNER JOIN sys.foreign_key_columns as k ON k.constraint_object_id = f.object_id
INNER JOIN sys.indexes as i ON f.referenced_object_id = i.object_id AND f.key_index_id = i.index_id
INNER JOIN sys.objects fk ON f.parent_object_id = fk.object_id
--INNER JOIN sys.columns as pkCols ON f.referenced_object_id = pkCols.object_id AND k.referenced_column_id = pkCols.column_id
--INNER JOIN sys.columns as fkCols ON f.parent_object_id = fkCols.object_id AND k.parent_column_id = fkCols.column_id
INNER JOIN sys.schemas pksch ON pk.schema_id = pksch.schema_id
INNER JOIN sys.schemas fksch ON fk.schema_id = fksch.schema_id
LEFT OUTER JOIN sys.extended_properties ep ON ep.name='MS_Description' AND ep.class = 1 AND ep.major_id = f.object_id AND ep.minor_id = 0
").AsList();
var fkCols = cn.Query <ForeignKeyMemberTmp>(@"
SELECT
f.name as [ForeignKeyConstraintName],
fksch.name as FKTableSchema,
k.constraint_column_id as PKColumnOrdinalPosition,
pkCols.name as PKColumnName,
fkCols.name as FKColumnName
FROM
sys.objects pk
INNER JOIN sys.foreign_keys as f ON pk.object_id = f.referenced_object_id
INNER JOIN sys.foreign_key_columns as k ON k.constraint_object_id = f.object_id
INNER JOIN sys.indexes as i ON f.referenced_object_id = i.object_id AND f.key_index_id = i.index_id
INNER JOIN sys.objects fk ON f.parent_object_id = fk.object_id
INNER JOIN sys.columns as pkCols ON f.referenced_object_id = pkCols.object_id AND k.referenced_column_id = pkCols.column_id
INNER JOIN sys.columns as fkCols ON f.parent_object_id = fkCols.object_id AND k.parent_column_id = fkCols.column_id
INNER JOIN sys.schemas pksch ON pk.schema_id = pksch.schema_id
INNER JOIN sys.schemas fksch ON fk.schema_id = fksch.schema_id
").AsList();
var indexes = cn.Query <IndexTmp>(@"
SELECT
sch.name as [TableSchema],
t.name as [TableName],
ind.name as [IndexName],
ind.index_id as [IndexId],
ind.type_desc as [PhysicalType],
CASE
when ind.is_primary_key=1 THEN 'PRIMARY_KEY'
-- Unique constraints are maintained through a unique index (IsUnique will always be true, so we have to check first)
when ind.is_unique_constraint=1 THEN 'UNIQUE_CONSTRAINT'
when ind.is_unique=1 THEN 'UNIQUE_INDEX'
ELSE 'NON_UNIQUE_INDEX'
END as [LogicalType],
ind.is_primary_key as [IsPrimaryKey],
ind.is_unique as [IsUnique],
ind.is_unique_constraint as [IsUniqueConstraint],
ep.value as [IndexDescription]
FROM
sys.tables t
INNER JOIN sys.schemas sch ON t.schema_id = sch.schema_id
INNER JOIN sys.indexes ind ON ind.object_id = t.object_id
LEFT OUTER JOIN sys.extended_properties ep ON ep.name='MS_Description' AND ep.class = 7 AND ep.major_id = ind.object_id AND ep.minor_id = ind.index_id
WHERE
t.is_ms_shipped = 0 -- omit system tables
ORDER BY
sch.name, t.name, ind.name, ind.index_id;
");
var indexesCols = cn.Query <IndexMemberTmp>(@"
SELECT
sch.name as [TableSchema],
t.name as [TableName],
ind.name as [IndexName],
ind.index_id as [IndexId],
col.name as [ColumnName],
ic.index_column_id as [IndexOrdinalPosition],
ic.is_descending_key as [IsDescendingKey],
ic.is_included_column as [IsIncludedColumn]
FROM
sys.tables t
INNER JOIN sys.schemas sch ON t.schema_id = sch.schema_id
INNER JOIN sys.indexes ind ON ind.object_id = t.object_id
INNER JOIN sys.index_columns ic ON ind.object_id = ic.object_id and ind.index_id = ic.index_id
INNER JOIN sys.columns col ON ic.object_id = col.object_id and ic.column_id = col.column_id
WHERE
t.is_ms_shipped = 0 -- omit system tables
ORDER BY
sch.name, t.name, ind.name, ind.index_id, ic.index_column_id;
");
foreach (var fk in fks)
{
fk.Columns = fkCols.Where(c => c.ForeignKeyConstraintName == fk.ForeignKeyConstraintName && c.FKTableSchema == fk.FKTableSchema)
.OrderBy(c => c.PKColumnOrdinalPosition)
.Select(c => Map <ForeignKeyMember, ForeignKeyMemberTmp>(c))
.ToList();
}
foreach (var index in indexes)
{
index.Columns = indexesCols.Where(c => c.TableSchema == index.TableSchema && c.TableName == index.TableName && c.IndexName == index.IndexName)
.OrderBy(c => c.IndexOrdinalPosition)
.Select(c => Map <IndexMember, IndexMemberTmp>(c))
.ToList();
}
foreach (var table in tables)
{
table.Columns = allColumns.Where(c => c.TableSchema == table.TableSchema && c.TableName == table.TableName).Select(c => Map <Column, ColumnTmp>(c)).ToList();
foreach (var column in table.Columns)
{
column.ClrType = GetClrType(table, column);
}
// We copy FKs and remove redundant properties of the parent object (table) which we're attaching this FK into
table.ForeignKeys = Clone(fks.Where(fk => fk.FKTableSchema == table.TableSchema && fk.FKTableName == table.TableName).ToList());
table.ForeignKeys.ForEach(fk => { fk.FKTableSchema = null; fk.FKTableName = null; });
// We copy FKs and remove redundant properties of the parent object (table) which we're attaching this FK into
table.ChildForeignKeys = Clone(fks.Where(fk => fk.PKTableSchema == table.TableSchema && fk.PKTableName == table.TableName).ToList());
table.ChildForeignKeys.ForEach(fk => { fk.PKTableSchema = null; fk.PKTableName = null; });
table.Indexes = indexes.Where(i => i.TableSchema == table.TableSchema && i.TableName == table.TableName)
.Select(i => Map <Index, IndexTmp>(i))
.ToList();
}
DatabaseSchema schema = new DatabaseSchema()
{
LastRefreshed = DateTimeOffset.Now,
Tables = tables,
};
Console.WriteLine($"Saving into {outputJsonSchema}...");
File.WriteAllText(outputJsonSchema, JsonConvert.SerializeObject(schema, Newtonsoft.Json.Formatting.Indented));
}
Console.WriteLine("Success!");
}
public void UpdateArgumentsOracleTest()
{
//arrange
var converter = new SchemaProcedureConverter();
var schema = new DatabaseSchema(null, null);
#region deserialize datatable
//from Oracle HR. Captured from var datatable.WriteXml(StringWriter)
const string data = @"<DocumentElement>
<ProcedureParameters>
<Schema>HR</Schema>
<Procedure>GET_CURRENT_TIME</Procedure>
<Name>P_DATE</Name>
<Position>1</Position>
<Sequence>1</Sequence>
<DataType>DATE</DataType>
<Direction>IN/OUT</Direction>
</ProcedureParameters>
<ProcedureParameters>
<Schema>HR</Schema>
<Procedure>ADD_JOB_HISTORY</Procedure>
<Name>P_EMP_ID</Name>
<Position>1</Position>
<Sequence>1</Sequence>
<DataType>NUMBER</DataType>
<Direction>IN</Direction>
<Length>22</Length>
<Precision>6</Precision>
</ProcedureParameters>
<ProcedureParameters>
<Schema>HR</Schema>
<Procedure>ADD_JOB_HISTORY</Procedure>
<Name>P_START_DATE</Name>
<Position>2</Position>
<Sequence>2</Sequence>
<DataType>DATE</DataType>
<Direction>IN</Direction>
</ProcedureParameters>
<ProcedureParameters>
<Schema>HR</Schema>
<Procedure>ADD_JOB_HISTORY</Procedure>
<Name>P_END_DATE</Name>
<Position>3</Position>
<Sequence>3</Sequence>
<DataType>DATE</DataType>
<Direction>IN</Direction>
</ProcedureParameters>
<ProcedureParameters>
<Schema>HR</Schema>
<Procedure>ADD_JOB_HISTORY</Procedure>
<Name>P_JOB_ID</Name>
<Position>4</Position>
<Sequence>4</Sequence>
<DataType>VARCHAR2</DataType>
<Direction>IN</Direction>
<Length>10</Length>
<Charset>CHAR_CS</Charset>
</ProcedureParameters>
<ProcedureParameters>
<Schema>HR</Schema>
<Procedure>ADD_JOB_HISTORY</Procedure>
<Name>P_DEPARTMENT_ID</Name>
<Position>5</Position>
<Sequence>5</Sequence>
<DataType>NUMBER</DataType>
<Direction>IN</Direction>
<Length>22</Length>
<Precision>4</Precision>
</ProcedureParameters>
</DocumentElement>
";
var sr = new StringReader(data);
var ds = new DataSet();
ds.ReadXml(sr);
var dataTable = ds.Tables[0];
#endregion
//act
converter.UpdateArguments(schema, dataTable);
//assert
var sprocs = schema.StoredProcedures;
Assert.AreEqual(2, sprocs.Count);
var addHistory = sprocs.Find(x => x.Name == "ADD_JOB_HISTORY");
Assert.AreEqual(5, addHistory.Arguments.Count);
var empId = addHistory.Arguments.Find(x => x.Name == "P_EMP_ID");
Assert.AreEqual(1, empId.Ordinal);
Assert.AreEqual("NUMBER", empId.DatabaseDataType);
Assert.AreEqual(true, empId.In);
Assert.AreEqual(false, empId.Out);
var currentTime = sprocs.Find(x => x.Name == "GET_CURRENT_TIME");
var date = currentTime.Arguments.Find(x => x.Name == "P_DATE");
Assert.AreEqual(1, date.Ordinal);
Assert.AreEqual("DATE", date.DatabaseDataType);
//inout type!
Assert.AreEqual(true, date.In);
Assert.AreEqual(true, date.Out);
}
//http://dev.mysql.com/doc/refman/5.1/en/drop-database.html
public override void DropDatabase(DatabaseSchema database)
{
ExecuteNonQuery(string.Concat("DROP DATABASE IF EXISTS ", database.Name, ";"));
}
public ImportCsvForm(string filePath, DatabaseSchema schema, NotebookManager manager)
{
InitializeComponent();
_filePath = filePath;
_databaseSchema = schema;
_manager = manager;
_optionsControl = new ImportCsvOptionsControl(schema)
{
AutoSize = true, AutoSizeMode = AutoSizeMode.GrowAndShrink
};
_optionsPanel.Controls.Add(_optionsControl);
_columnsControl = new ImportColumnsControl(allowDetectTypes: true)
{
Dock = DockStyle.Fill
};
_columnsLoadControl = new LoadingContainerControl {
ContainedControl = _columnsControl, Dock = DockStyle.Fill
};
_columnsPanel.Controls.Add(_columnsLoadControl);
_inputPreviewControl = new ImportCsvPreviewControl {
Dock = DockStyle.Fill
};
_inputPreviewLoadControl = new LoadingContainerControl {
ContainedControl = _inputPreviewControl, Dock = DockStyle.Fill
};
_originalFilePanel.Controls.Add(_inputPreviewLoadControl);
Ui ui = new(this, 170, 45);
ui.Init(_table);
ui.Init(_outerSplit, 0.48);
ui.InitHeader(_originalFileLabel);
ui.Init(_lowerSplit, 0.52);
ui.InitHeader(_optionsLabel);
ui.InitHeader(_columnsLabel);
ui.Init(_buttonFlow1);
ui.MarginTop(_buttonFlow1);
ui.Init(_previewButton);
ui.Init(_buttonFlow2);
ui.MarginTop(_buttonFlow2);
ui.Init(_okBtn);
ui.Init(_cancelBtn);
Load += async(sender, e) => {
ValidateOptions();
await UpdateControls(inputChange : true);
_optionsControl.SelectTableCombo();
};
var o = _optionsControl;
Bind.OnChange(new Slot[] { o.TargetTableName },
async(sender, e) => {
ValidateOptions();
await UpdateControls(columnsChange: true);
});
Bind.OnChange(new Slot[] { o.FileEncoding },
async(sender, e) => await UpdateControls(inputChange: true));
Bind.OnChange(new Slot[] { o.IfTableExists, o.SkipLines, o.HasColumnHeaders, o.Separator },
async(sender, e) => await UpdateControls(columnsChange: true));
Bind.BindAny(new[] { _columnsLoadControl.IsOverlayVisible, _inputPreviewLoadControl.IsOverlayVisible },
x => _okBtn.Enabled = !x);
Text = $"Import {Path.GetFileName(_filePath)}";
o.TargetTableName.Value = Path.GetFileNameWithoutExtension(_filePath);
}
internal AdoAdapter(IConnectionProvider connectionProvider)
{
_connectionProvider = connectionProvider;
_schema = DatabaseSchema.Get(_connectionProvider);
_relatedFinder = new Lazy <AdoAdapterRelatedFinder>(CreateRelatedFinder);
}
internal DatabaseSchema GetSchema()
{
return(DatabaseSchema.Get(_connectionProvider));
}
public SimpleReferenceFormatter(DatabaseSchema schema, ICommandBuilder commandBuilder, IFunctionNameConverter functionNameConverter)
{
_schema = schema;
_commandBuilder = commandBuilder;
_functionNameConverter = functionNameConverter ?? new FunctionNameConverter();
}
public SimpleReferenceFormatter(DatabaseSchema schema, ICommandBuilder commandBuilder) : this(schema, commandBuilder, null)
{
}
public DeleteHelper(DatabaseSchema schema)
{
_schema = schema;
_commandBuilder = new CommandBuilder(schema.SchemaProvider);
_expressionFormatter = new ExpressionFormatter(_commandBuilder, _schema);
}
public Joiner(DatabaseSchema schema)
: this(JoinType.Outer, schema)
{
}
public abstract TableSchema[] GetTables(string connectionString, DatabaseSchema database);
public void CreateSerializedDatabaseFile(DatabaseSchema target)
{
var sb = new StringBuilder();
int indentLevel = 0;
string className = RemoveWhitespace(target.name) + "Database";
void NewLine()
{
sb.Append(Environment.NewLine);
if (indentLevel > 0)
{
sb.Append(" ".PadLeft(indentLevel * 4));
}
}
sb.Append($"using {typeof(IDObject).Namespace};");
NewLine();
sb.Append("using UnityEngine;");
NewLine();
sb.Append("#if UNITY_EDITOR");
NewLine();
sb.Append("using Unity.EditorCoroutines.Editor;");
NewLine();
sb.Append("#endif");
NewLine();
NewLine();
//sb.Append(string.Format("[CreateAssetMenu(fileName = \"{0}\", menuName = \"Notion API/Databases/{1}\")]", className, className));
//NewLine();
sb.Append("public class ");
sb.Append(className);
sb.Append(" : ScriptableObject");
NewLine();
sb.Append("{");
indentLevel++;
NewLine();
//PROPERTIES
sb.Append("[System.Serializable]");
NewLine();
sb.Append("public class Definition");
NewLine();
sb.Append("{");
indentLevel++;
NewLine();
//TITLE
sb.Append("public TitleProperty Name;");
for (int i = 0; i < target.fieldNames.Count; i++)
{
var notionType = GetPropertyDefinitionFromNotionType(target.fieldTypes[i]);
if (notionType == null)
{
continue;
}
var field = target.fieldNames[i];
NewLine();
sb.Append("public ");
sb.Append(notionType.Name);
sb.Append(" ");
sb.Append(field);
sb.Append(";");
}
indentLevel--;
NewLine();
sb.Append("}");
NewLine();
NewLine();
//PAGES
sb.Append("[System.Serializable]");
NewLine();
sb.Append("public class Properties");
NewLine();
sb.Append("{");
indentLevel++;
bool hasPeopleProperty = false;
for (int i = 0; i < target.fieldNames.Count; i++)
{
var notionType = GetPropertyTypeFromNotionType(target.fieldTypes[i]);
if (notionType == null)
{
continue;
}
if (notionType == typeof(PeopleProperty))
{
hasPeopleProperty = true;
}
var field = target.fieldNames[i];
NewLine();
sb.Append("public ");
sb.Append(notionType.Name);
sb.Append(" ");
sb.Append(field);
sb.Append(";");
}
indentLevel--;
NewLine();
sb.Append("}");
NewLine();
NewLine();
sb.Append("public DatabaseSchema databaseSchema;");
NewLine();
sb.Append("public Database<Definition> database;");
NewLine();
sb.Append("public Page<Properties>[] pages;");
if (hasPeopleProperty)
{
NewLine();
sb.Append("public DatabaseUsers users;");
}
NewLine();
NewLine();
sb.Append("#if UNITY_EDITOR");
NewLine();
sb.Append("[EditorButton(\"Fetch Data From Notion\", \"SyncEditor\")]");
NewLine();
sb.Append("public bool doSync;");
NewLine();
NewLine();
sb.Append("public void SyncEditor()");
NewLine();
sb.Append("{");
indentLevel++;
NewLine();
sb.Append("var api = new NotionAPI(databaseSchema.apiKey);");
NewLine();
sb.Append("EditorCoroutineUtility.StartCoroutine(api.GetDatabase<Definition>(databaseSchema.database_id, (db) => { database = db; }), this);");
NewLine();
sb.Append("EditorCoroutineUtility.StartCoroutine(api.QueryDatabase<Properties>(databaseSchema.database_id, (pages) => { this.pages = pages.results; }), this);");
if (hasPeopleProperty)
{
NewLine();
sb.Append("EditorCoroutineUtility.StartCoroutine(api.GetUsers((users) => { this.users = users; }), this);");
}
NewLine();
sb.Append("UnityEditor.EditorUtility.SetDirty(this);");
indentLevel--;
NewLine();
sb.Append("}");
NewLine();
sb.Append("#endif");
indentLevel--;
NewLine();
sb.Append("}");
var path = Directory.GetParent(AssetDatabase.GetAssetPath(target));
var scriptPath = Path.Combine(path.FullName, className + ".cs");
using (var w = File.CreateText(scriptPath))
{
w.Write(sb);
}
scriptPath = "Assets" + scriptPath.Substring(Application.dataPath.Length);
AssetDatabase.ImportAsset(scriptPath);
EditorPrefs.SetString("NotionAPI_DatabaseName", className);
EditorPrefs.SetString("NotionAPI_DatabasePath", Path.Combine(path.ToString(), className));
EditorPrefs.SetInt("NotionAPI_DatabaseSchemaId", target.GetInstanceID());
}
public Database Create(DatabaseSchema databaseSchema)
{
if (File.Exists(settings.MappingFile))
_database = Dbml.CopyWithFilledInDefaults(Dbml.FromFile(settings.MappingFile));
else
_database = new Database();
Database.Name = databaseSchema.Name;
CreateContext(databaseSchema);
_enumDatabase = new DbmlEnum.Database { Name = databaseSchema.Name };
_existingEnumDatabase = DbmlEnum.Database.DeserializeFromFile(EnumXmlFileName) ?? new DbmlEnum.Database();
foreach (TableSchema t in databaseSchema.Tables)
{
if (Settings.IsIgnored(t.FullName))
{
Debug.WriteLine("Skipping Table: " + t.FullName);
}
else if (Settings.IsEnum(t))
{
Debug.WriteLine("Getting Enum Table: " + t.FullName);
GetEnum(t);
}
else
{
Debug.WriteLine("Getting Table Schema: " + t.FullName);
GetTable(t);
}
OnSchemaItemProcessed(t.FullName);
}
if (Settings.IncludeViews)
{
foreach (ViewSchema v in databaseSchema.Views)
{
if (Settings.IsIgnored(v.FullName))
{
Debug.WriteLine("Skipping View: " + v.FullName);
}
else
{
Debug.WriteLine("Getting View Schema: " + v.FullName);
CreateView(v);
}
OnSchemaItemProcessed(v.FullName);
}
}
if (Settings.IncludeFunctions)
{
foreach (CommandSchema c in databaseSchema.Commands)
{
if (Settings.IsIgnored(c.FullName))
{
Debug.WriteLine("Skipping Function: " + c.FullName);
}
else
{
Debug.WriteLine("Getting Function Schema: " + c.FullName);
CreateFunction(c);
}
OnSchemaItemProcessed(c.FullName);
}
}
//sync tables
RemoveExtraMembers(_database);
_database.Tables.Sort();
Dbml.ToFile(Dbml.CopyWithNulledOutDefaults(_database),
settings.MappingFile);
if (_enumDatabase.Enums.Count > 0 || File.Exists(EnumXmlFileName))
_enumDatabase.SerializeToFile(EnumXmlFileName);
return _database;
}
public UnitTestWriter(DatabaseSchema schema, CodeWriterSettings codeWriterSettings)
{
_codeWriterSettings = codeWriterSettings;
_schema = schema;
_cb = new ClassBuilder();
}
public void UpdateArgumentsSqlServerTest()
{
//arrange
var converter = new SchemaProcedureConverter();
var schema = new DatabaseSchema(null, null);
#region deserialize datatable
//from SqlServer Northwind. Captured from var datatable.WriteXml(StringWriter)
const string data = @"<DocumentElement>
<ProcedureParameters>
<SPECIFIC_CATALOG>Northwind</SPECIFIC_CATALOG>
<SPECIFIC_SCHEMA>dbo</SPECIFIC_SCHEMA>
<SPECIFIC_NAME>GetWeekDay</SPECIFIC_NAME>
<ORDINAL_POSITION>0</ORDINAL_POSITION>
<PARAMETER_MODE>OUT</PARAMETER_MODE>
<IS_RESULT>YES</IS_RESULT>
<AS_LOCATOR>NO</AS_LOCATOR>
<PARAMETER_NAME />
<DATA_TYPE>int</DATA_TYPE>
<NUMERIC_PRECISION>10</NUMERIC_PRECISION>
<NUMERIC_PRECISION_RADIX>10</NUMERIC_PRECISION_RADIX>
<NUMERIC_SCALE>0</NUMERIC_SCALE>
</ProcedureParameters>
<ProcedureParameters>
<SPECIFIC_CATALOG>Northwind</SPECIFIC_CATALOG>
<SPECIFIC_SCHEMA>dbo</SPECIFIC_SCHEMA>
<SPECIFIC_NAME>GetWeekDay</SPECIFIC_NAME>
<ORDINAL_POSITION>1</ORDINAL_POSITION>
<PARAMETER_MODE>IN</PARAMETER_MODE>
<IS_RESULT>NO</IS_RESULT>
<AS_LOCATOR>NO</AS_LOCATOR>
<PARAMETER_NAME>@Date</PARAMETER_NAME>
<DATA_TYPE>datetime</DATA_TYPE>
<DATETIME_PRECISION>3</DATETIME_PRECISION>
</ProcedureParameters>
<ProcedureParameters>
<SPECIFIC_CATALOG>Northwind</SPECIFIC_CATALOG>
<SPECIFIC_SCHEMA>dbo</SPECIFIC_SCHEMA>
<SPECIFIC_NAME>GetCurrentDate</SPECIFIC_NAME>
<ORDINAL_POSITION>1</ORDINAL_POSITION>
<PARAMETER_MODE>INOUT</PARAMETER_MODE>
<IS_RESULT>NO</IS_RESULT>
<AS_LOCATOR>NO</AS_LOCATOR>
<PARAMETER_NAME>@p1</PARAMETER_NAME>
<DATA_TYPE>datetime2</DATA_TYPE>
<DATETIME_PRECISION>7</DATETIME_PRECISION>
</ProcedureParameters>
<ProcedureParameters>
<SPECIFIC_CATALOG>Northwind</SPECIFIC_CATALOG>
<SPECIFIC_SCHEMA>dbo</SPECIFIC_SCHEMA>
<SPECIFIC_NAME>Sales by Year</SPECIFIC_NAME>
<ORDINAL_POSITION>1</ORDINAL_POSITION>
<PARAMETER_MODE>IN</PARAMETER_MODE>
<IS_RESULT>NO</IS_RESULT>
<AS_LOCATOR>NO</AS_LOCATOR>
<PARAMETER_NAME>@Beginning_Date</PARAMETER_NAME>
<DATA_TYPE>datetime</DATA_TYPE>
<DATETIME_PRECISION>3</DATETIME_PRECISION>
</ProcedureParameters>
<ProcedureParameters>
<SPECIFIC_CATALOG>Northwind</SPECIFIC_CATALOG>
<SPECIFIC_SCHEMA>dbo</SPECIFIC_SCHEMA>
<SPECIFIC_NAME>Sales by Year</SPECIFIC_NAME>
<ORDINAL_POSITION>2</ORDINAL_POSITION>
<PARAMETER_MODE>IN</PARAMETER_MODE>
<IS_RESULT>NO</IS_RESULT>
<AS_LOCATOR>NO</AS_LOCATOR>
<PARAMETER_NAME>@Ending_Date</PARAMETER_NAME>
<DATA_TYPE>datetime</DATA_TYPE>
<DATETIME_PRECISION>3</DATETIME_PRECISION>
</ProcedureParameters>
</DocumentElement>";
var sr = new StringReader(data);
var ds = new DataSet();
ds.ReadXml(sr);
var dataTable = ds.Tables[0];
#endregion
//act
converter.UpdateArguments(schema, dataTable);
//assert
var sprocs = schema.StoredProcedures;
Assert.AreEqual(2, sprocs.Count); //function GetWeekDay is ignored
Assert.AreEqual(1, schema.Functions.Count); //here's where GetWeekDay is
var salesByYear = sprocs.Find(x => x.Name == "Sales by Year");
Assert.AreEqual(2, salesByYear.Arguments.Count);
var date = salesByYear.Arguments.Find(x => x.Name == "@Beginning_Date");
Assert.AreEqual(1, date.Ordinal);
Assert.AreEqual("datetime", date.DatabaseDataType);
Assert.AreEqual(true, date.In);
Assert.AreEqual(false, date.Out);
var currentTime = sprocs.Find(x => x.Name == "GetCurrentDate");
Assert.AreEqual(1, currentTime.Arguments.Count);
var p1 = currentTime.Arguments.Find(x => x.Name == "@p1");
Assert.AreEqual(1, p1.Ordinal);
Assert.AreEqual("datetime2", p1.DatabaseDataType);
//in-out type!
Assert.AreEqual(true, p1.In);
Assert.AreEqual(true, p1.Out);
var fun = schema.Functions[0];
Assert.AreEqual("int", fun.ReturnType);
Assert.AreEqual(1, fun.Arguments.Count); //even though we had 2 in the data table
var input = fun.Arguments[0];
Assert.AreEqual("@Date", input.Name);
Assert.AreEqual("datetime", input.DatabaseDataType);
Assert.AreEqual(true, input.In);
}
private static DatabaseFunction FindFunction(DatabaseSchema schema, string name)
{
return(schema.Functions.Find(delegate(DatabaseFunction x) { return x.Name.Equals(name, StringComparison.OrdinalIgnoreCase); }));
}
//http://dev.mysql.com/doc/refman/5.1/en/drop-database.html
public override void DropDatabase (DatabaseSchema database)
{
ExecuteNonQuery (string.Concat("DROP DATABASE IF EXISTS ", database.Name, ";"));
}
public static void AddSources(DatabaseSchema schema, DataTable dt)
{
DatabaseStoredProcedure matchProcedure;
DatabaseFunction matchFunction;
//oracle sources come in lines; assume in order, so we can just concatenate
//if they already have source, we don't want to overwrite, so we use a cache
var functionCache = new Dictionary <string, DatabaseFunction>();
foreach (DataRow row in dt.Rows)
{
string owner = row["OWNER"].ToString();
string name = row["NAME"].ToString();
string type = row["TYPE"].ToString().Trim();
string text = row["TEXT"].ToString();
switch (type)
{
case "PACKAGE": //oracle package
var package = FindPackage(schema, name, owner);
//text will have a newline but not cReturn
package.Definition += text;
break;
case "PACKAGE BODY": //oracle package body
var package2 = FindPackage(schema, name, owner);
//text will have a newline but not cReturn
package2.Body += text;
break;
case "PROCEDURE": //oracle procedure
matchProcedure = FindStoredProcedure(schema, name);
if (matchProcedure == null)
{
continue;
}
//text will have a newline but not cReturn
matchProcedure.Sql += text;
break;
case "FUNCTION": //oracle function
var function = FindFunction(name, schema, functionCache);
if (function == null)
{
continue;
}
function.Sql += text;
break;
case "P": //sql server procedure
matchProcedure = FindStoredProcedure(schema, name);
if (matchProcedure == null)
{
continue;
}
matchProcedure.Sql = text;
break;
case "TF": //sql server table-valued function
case "FN": //sql server scalar function
matchFunction = FindFunction(schema, name);
if (matchFunction == null)
{
continue;
}
matchFunction.Sql = text;
break;
case "V": //sql server view
DatabaseView matchView = FindView(schema, name);
if (matchView == null)
{
continue;
}
matchView.Sql = text;
break;
}
}
}
public TableSchema[] GetTables(string connectionString, DatabaseSchema database)
{
if (_cachedConnectionString != connectionString)
{
_cachedConnectionString = connectionString;
_doc.Load(connectionString);
}
XmlNodeList tables = _doc.SelectNodes("dcl:schema/dcl:table", _manager);
int tableCount = tables.Count;
TableSchema[] ret = new TableSchema[tableCount];
for (int i = 0; i < tableCount; ++i)
{
XmlNode currentTable = tables[i];
string tableName = "";
XmlAttribute nameNode = currentTable.Attributes["name"];
if (nameNode != null)
{
tableName = nameNode.Value; //nameNode.Value; //
}
ret[i] = new TableSchema(database, tableName, database.Name, _creationDate);
}
return ret;
}
public ViewSchema[] GetViews(string connectionString, DatabaseSchema database)
{
return new ViewSchema[0];
}
public ViewSchema[] GetViews(string connectionString, DatabaseSchema database)
{
//Get only the 'tables' of type 'view'. The 'Tables' catlog also includes
//system tables, and regular tables
DataTable dt = GetSchemaData(connectionString,
Tables,
null /*restrictions[0] - catalog*/,
null /*restrictions[1] - unused*/,
null /*restrictions[2] - table*/,
ViewType /*restrictions[3] - type*/);
ViewSchema[] views = new ViewSchema[dt.Rows.Count];
int tableSchemaIdx = 0;
foreach (DataRow dr in dt.Rows)
{
ViewSchema view = new ViewSchema(database,
(String)dr[TableNameColumn],
String.Empty,
DateTime.MinValue);
views[tableSchemaIdx++] = view;
}
return views;
}
//http://dev.mysql.com/doc/refman/5.1/en/rename-database.html
public override void RenameDatabase(DatabaseSchema database, string name)
{
ExecuteNonQuery(string.Concat("RENAME DATABASE ", database.Name, " TO ", name, ";"));
connectionPool.ConnectionContext.ConnectionSettings.Database = name;
database.Name = name;
}
/// <summary>
/// Finds the object in the specified database schema, or NULL
/// </summary>
/// <param name="databaseSchema">The database schema.</param>
/// <returns></returns>
/// <exception cref="System.ArgumentNullException">databaseSchema</exception>
/// <exception cref="System.InvalidOperationException">
/// Name required
/// or
/// TableName required
/// </exception>
/// <exception cref="System.ArgumentOutOfRangeException"></exception>
public NamedObject Find(DatabaseSchema databaseSchema)
{
if (databaseSchema == null)
{
throw new ArgumentNullException("databaseSchema");
}
if (string.IsNullOrEmpty(Name))
{
throw new InvalidOperationException("Name required");
}
DatabaseTable table;
switch (SchemaObjectType)
{
case SchemaObjectType.Table:
return(databaseSchema.FindTableByName(Name, SchemaOwner));
case SchemaObjectType.View:
return(databaseSchema.Views.Find(v => Name.Equals(v.Name, StringComparison.OrdinalIgnoreCase) &&
string.Equals(v.SchemaOwner, SchemaOwner, StringComparison.OrdinalIgnoreCase)));
case SchemaObjectType.Column:
if (string.IsNullOrEmpty(TableName))
{
throw new InvalidOperationException("TableName required");
}
table = databaseSchema.FindTableByName(TableName, SchemaOwner);
return(table.FindColumn(Name));
case SchemaObjectType.Constraint:
if (string.IsNullOrEmpty(TableName))
{
throw new InvalidOperationException("TableName required");
}
table = databaseSchema.FindTableByName(TableName, SchemaOwner);
if (table.PrimaryKey != null && table.PrimaryKey.Name == Name)
{
return(table.PrimaryKey);
}
var constraint = table.ForeignKeys.FindByName(Name);
if (constraint != null)
{
return(constraint);
}
constraint = table.CheckConstraints.FindByName(Name);
if (constraint != null)
{
return(constraint);
}
constraint = table.UniqueKeys.FindByName(Name);
if (constraint != null)
{
return(constraint);
}
//shouldn't fall through to here
return(null);
case SchemaObjectType.Index:
if (string.IsNullOrEmpty(TableName))
{
throw new InvalidOperationException("TableName required");
}
table = databaseSchema.FindTableByName(TableName, SchemaOwner);
return(table.Indexes.Find(x => Name.Equals(x.Name, StringComparison.OrdinalIgnoreCase)));
case SchemaObjectType.Trigger:
if (string.IsNullOrEmpty(TableName))
{
throw new InvalidOperationException("TableName required");
}
table = databaseSchema.FindTableByName(TableName, SchemaOwner);
return(table.Triggers.Find(x => Name.Equals(x.Name, StringComparison.OrdinalIgnoreCase)));
case SchemaObjectType.StoredProcedure:
return(databaseSchema.StoredProcedures.Find(x => Name.Equals(x.Name, StringComparison.OrdinalIgnoreCase) &&
string.Equals(x.SchemaOwner, SchemaOwner, StringComparison.OrdinalIgnoreCase)));
case SchemaObjectType.Function:
return(databaseSchema.Functions.Find(x => Name.Equals(x.Name, StringComparison.OrdinalIgnoreCase) &&
string.Equals(x.SchemaOwner, SchemaOwner, StringComparison.OrdinalIgnoreCase)));
case SchemaObjectType.Sequence:
return(databaseSchema.Sequences.Find(x => Name.Equals(x.Name, StringComparison.OrdinalIgnoreCase) &&
string.Equals(x.SchemaOwner, SchemaOwner, StringComparison.OrdinalIgnoreCase)));
case SchemaObjectType.Package:
return(databaseSchema.Packages.Find(x => Name.Equals(x.Name, StringComparison.OrdinalIgnoreCase) &&
string.Equals(x.SchemaOwner, SchemaOwner, StringComparison.OrdinalIgnoreCase)));
default:
throw new ArgumentOutOfRangeException();
}
}
public override TableSchema[] GetTables(string connectionString, DatabaseSchema database)
{
//Util.AccessHelper.ExecuteSql(connectionString,
throw new NotImplementedException();
}
public override IDatabaseSchema Load(DbConfiguration cfg)
{
var databaseSchema = new DatabaseSchema();
ColumnInfo[] allColumns = null;
ForeignKeyInfo[] allFks = null;
using (var ctx = cfg.CreateDbContext())
{
InitConnection(ctx.Connection);
using (var reader = ctx.DbHelper.ExecuteReader(AllColumnsSql))
allColumns = reader.ToList <ColumnInfo>().ToArray();
using (var reader = ctx.DbHelper.ExecuteReader(AllFKsSql))
allFks = reader.ToList <ForeignKeyInfo>().ToArray();
}
Dictionary <string, TableSchema> tables = new Dictionary <string, TableSchema>();
foreach (var c in allColumns)
{
TableSchema table = null;
if (!tables.TryGetValue(c.TableName, out table))
{
table = new TableSchema {
TableName = c.TableName, IsView = c.IsView
};
tables[c.TableName] = table;
}
var key = allFks.FirstOrDefault(p => p.Type == "P" &&
p.ThisTableName == c.TableName &&
p.ThisKey == c.ColumnName);
c.IsPrimaryKey = key != null;
var column = ToColumn(c);
table.AddColumn(column);
}
foreach (var item in allFks.Where(p => p.OtherTableName.HasValue()))
{
TableSchema thisTable = tables[item.OtherTableName];
TableSchema otherTable = tables[item.ThisTableName];
IColumnSchema thisKey = thisTable.AllColumns.FirstOrDefault(p => p.ColumnName == item.OtherKey);
IColumnSchema otherKey = otherTable.AllColumns.FirstOrDefault(p => p.ColumnName == item.ThisKey);
thisTable.AddFK(new ForeignKeySchema
{
ThisTable = thisTable,
Name = item.Name,
ThisKey = thisKey,
OtherTable = otherTable,
OtherKey = otherKey
});
}
databaseSchema.Tables = tables.Values.Where(p => !p.IsView).ToArray();
databaseSchema.Views = tables.Values.Where(p => p.IsView).ToArray();
return(databaseSchema);
}
public SimpleReferenceFormatter(DatabaseSchema schema, ICommandBuilder commandBuilder)
{
_schema = schema;
_commandBuilder = commandBuilder;
}
protected override void OnBeforeDatabaseCreation(DatabaseSchema schema)
{
createDBWidget.SetDatabaseOptions((SqlServerDatabaseSchema)schema);
base.OnBeforeDatabaseCreation(schema);
}
private void CreateContext(DatabaseSchema databaseSchema)
{
if (!string.IsNullOrEmpty(Settings.ContextNamespace))
Database.ContextNamespace = Settings.ContextNamespace;
if (!string.IsNullOrEmpty(Settings.EntityNamespace))
Database.EntityNamespace = Settings.EntityNamespace;
if (!string.IsNullOrEmpty(Settings.EntityBase))
Database.EntityBase = Settings.EntityBase;
if (string.IsNullOrEmpty(Database.Class))
if (string.IsNullOrEmpty(Settings.DataContextName))
Database.Class = StringUtil.ToPascalCase(databaseSchema.Name) + "DataContext";
else
Database.Class = Settings.DataContextName;
if (Database.Connection == null)
Database.Connection = new Connection("System.Data.SqlClient");
if (Database.Connection.Mode == ConnectionMode.ConnectionString
&& string.IsNullOrEmpty(Database.Connection.ConnectionString))
Database.Connection.Mode = ConnectionMode.AppSettings;
if (string.IsNullOrEmpty(Database.Connection.SettingsObjectName))
Database.Connection.SettingsObjectName = "Properties.Settings";
if (string.IsNullOrEmpty(Database.Connection.SettingsPropertyName))
Database.Connection.SettingsPropertyName = ToLegalName(databaseSchema.Name) + "ConnectionString";
if (string.IsNullOrEmpty(Database.Connection.ConnectionString))
Database.Connection.ConnectionString = databaseSchema.ConnectionString;
}
public TablesGenerator(DatabaseSchema schema)
: base(schema)
{
}
//http://dev.mysql.com/doc/refman/5.1/en/create-database.html
public override void CreateDatabase (DatabaseSchema database)
{
MySqlDatabaseSchema schema = (MySqlDatabaseSchema)database;
StringBuilder sql = new StringBuilder ();
sql.AppendFormat ("CREATE DATABASE {0} ", schema.Name);
if (schema.CharacterSetName != string.Empty)
sql.AppendFormat ("CHARACTER SET {0} ", schema.CharacterSetName);
if (schema.CollationName != string.Empty)
sql.AppendFormat ("COLLATE {0}", schema.CollationName);
ExecuteNonQuery (sql.ToString ());
}
private void WriteSerializableClassMember(XmlClassMember member, DatabaseSchema schema)
{
if (member.type == Constants.TypeInt)
{
WriteLine("public int " + member.name + ";");
}
else if (member.type == Constants.TypeFloat)
{
WriteLine("public float " + member.name + ";");
}
else if (member.type == Constants.TypeBool)
{
WriteLine("public bool " + member.name + ";");
}
else if (member.type == Constants.TypeString)
{
WriteLine("[DefaultValue(\"\")]");
WriteLine("public string " + member.name + ";");
}
else if (member.type == Constants.TypeColor)
{
WriteLine("[DefaultValue(\"\")]");
WriteLine("public string " + member.name + ";");
}
else if (member.type == Constants.TypeImage)
{
WriteLine("[DefaultValue(\"\")]");
WriteLine("public string " + member.name + ";");
}
else if (member.type == Constants.TypeAudioClip)
{
WriteLine("[DefaultValue(\"\")]");
WriteLine("public string " + member.name + ";");
}
else if (member.type == Constants.TypePrefab)
{
WriteLine("[DefaultValue(\"\")]");
WriteLine("public string " + member.name + ";");
}
else if (member.type == Constants.TypeLayout)
{
WriteLine("[DefaultValue(\"\")]");
WriteLine("public string " + member.name + ";");
}
else if (member.type == Constants.TypeVector)
{
WriteLine("public Vector2 " + member.name + ";");
}
else if (member.type == Constants.TypeEnum)
{
if (!schema.HasEnum(member.typeid))
{
throw new InvalidSchemaException("Unknown enum type in class member " + member.name);
}
WriteLine("public " + member.typeid + " " + member.name + ";");
}
else if (member.type == Constants.TypeObject)
{
if (!schema.HasObject(member.typeid))
{
throw new InvalidSchemaException("Unknown object type in class member " + member.name);
}
WriteLine("public int " + member.name + ";");
}
else if (member.type == Constants.TypeObjectList)
{
if (!schema.HasObject(member.typeid))
{
throw new InvalidSchemaException("Unknown object type in class member " + member.name);
}
WriteLine("public int[] " + member.name + ";");
}
else if (member.type == Constants.TypeStruct)
{
if (!schema.HasStruct(member.typeid))
{
throw new InvalidSchemaException("Unknown struct type in class member " + member.name);
}
WriteLine("public " + Utils.SerializableClassName(member.typeid) + " " + member.name + ";");
}
else if (member.type == Constants.TypeStructList)
{
if (!schema.HasStruct(member.typeid))
{
throw new InvalidSchemaException("Unknown struct type in class member " + member.name);
}
WriteLine("public " + Utils.SerializableClassName(member.typeid) + "[] " + member.name + ";");
}
else if (member.type == Constants.TypeEnumFlags)
{
if (!schema.HasEnum(member.typeid))
{
throw new InvalidSchemaException("Unknown enum type in class member " + member.name);
}
WriteLine("public " + member.typeid + "[] " + member.name + ";");
}
else
{
throw new InvalidSchemaException("Invalid class member type - " + member.type);
}
}
//http://dev.mysql.com/doc/refman/5.1/en/rename-database.html
public override void RenameDatabase (DatabaseSchema database, string name)
{
ExecuteNonQuery (string.Concat("RENAME DATABASE ", database.Name, " TO ", name, ";"));
connectionPool.ConnectionContext.ConnectionSettings.Database = name;
database.Name = name;
}
private void WriteDataClassMember(XmlClassMember member, DatabaseSchema schema)
{
//var obsolete = member.options.Contains(Constants.OptionObsolete);
var memberName = !string.IsNullOrEmpty(member.alias) ? member.alias : member.name;
//if (obsolete) memberName = PrivateMemberName(memberName);
var prefix = "public "; //obsolete ? "private readonly " : "public ";
var suffix = ";"; //obsolete ? ";" : " { get; private set; }";
if (member.type == Constants.TypeInt)
{
member.MinMaxInt(out var minvalue, out var maxvalue);
WriteLine(prefix + "NumericValue<int> " + memberName + " = new NumericValue<int>(0, " + minvalue + ", " + maxvalue + ");");
}
else if (member.type == Constants.TypeFloat)
{
member.MinMaxFloat(out var minvalue, out var maxvalue);
WriteLine(prefix + "NumericValue<float> " + memberName + " = new NumericValue<float>(0, " + minvalue + "f, " + maxvalue + "f);");
}
else if (member.type == Constants.TypeBool)
{
WriteLine(prefix + "bool " + memberName + suffix);
}
else if (member.type == Constants.TypeString)
{
WriteLine(prefix + "string " + memberName + suffix);
}
else if (member.type == Constants.TypeColor)
{
WriteLine(prefix + "System.Drawing.Color " + memberName + suffix);
}
else if (member.type == Constants.TypeImage)
{
WriteLine(prefix + "string " + memberName + suffix);
}
else if (member.type == Constants.TypeAudioClip)
{
WriteLine(prefix + "string " + memberName + suffix);
}
else if (member.type == Constants.TypePrefab)
{
WriteLine(prefix + "string " + memberName + suffix);
}
else if (member.type == Constants.TypeLayout)
{
WriteLine(prefix + "Layout " + memberName + suffix);
}
else if (member.type == Constants.TypeVector)
{
WriteLine(prefix + "Vector2 " + memberName + suffix);
}
else if (member.type == Constants.TypeEnum)
{
if (!schema.HasEnum(member.typeid))
{
throw new InvalidSchemaException("Unknown enum type in class member " + member.name);
}
WriteLine(prefix + member.typeid + " " + memberName + suffix);
}
else if (member.type == Constants.TypeObject)
{
if (!schema.HasObject(member.typeid))
{
throw new InvalidSchemaException("Unknown object type in class member " + member.name);
}
var dataClass = schema.GetObject(member.typeid);
WriteLine(prefix + "ItemId<" + Utils.DataClassName(dataClass) + "> " + memberName + " = ItemId<" + Utils.DataClassName(dataClass) + ">.Empty" + suffix);
}
else if (member.type == Constants.TypeObjectList)
{
if (!schema.HasObject(member.typeid))
{
throw new InvalidSchemaException("Unknown object type in class member " + member.name);
}
var dataClass = schema.GetObject(member.typeid);
WriteLine(prefix + "Wrapper<" + Utils.DataClassName(dataClass) + ">[] " + memberName + suffix);
}
else if (member.type == Constants.TypeStruct)
{
if (!schema.HasStruct(member.typeid))
{
throw new InvalidSchemaException("Unknown struct type in class member " + member.name);
}
var dataClass = schema.GetStruct(member.typeid);
WriteLine(prefix + Utils.DataClassName(dataClass) + " " + memberName + " = new " + Utils.DataClassName(dataClass) + "()" + suffix);
}
else if (member.type == Constants.TypeStructList)
{
if (!schema.HasStruct(member.typeid))
{
throw new InvalidSchemaException("Unknown struct type in class member " + member.name);
}
var dataClass = schema.GetStruct(member.typeid);
WriteLine(prefix + Utils.DataClassName(dataClass) + "[] " + memberName + suffix);
}
else if (member.type == Constants.TypeEnumFlags)
{
if (!schema.HasEnum(member.typeid))
{
throw new InvalidSchemaException("Unknown enum type in class member " + member.name);
}
WriteLine(prefix + "ValueWrapper<" + member.typeid + ">[] " + memberName + suffix);
}
else
{
throw new InvalidSchemaException("Invalid class member type - " + member.type);
}
}
public CommandSchema[] GetCommands(string connectionString, DatabaseSchema database)
{
// NetTiers shouldn't be using this, so unimplemented right now
return new CommandSchema[0];
}
private void WriteSerializationCode(XmlClassMember member, DatabaseSchema schema)
{
var memberName = !string.IsNullOrEmpty(member.alias) ? member.alias : member.name;
//if (member.options.Contains(Constants.OptionObsolete)) memberName = PrivateMemberName(memberName);
var notnull = member.options.Contains(Constants.OptionNotNull);
if (member.type == Constants.TypeObject)
{
var dataClass = schema.GetObject(member.typeid);
WriteLine("serializable." + member.name + " = " + memberName + ".Value;");
//TODO: if (notnull)
}
else if (member.type == Constants.TypeObjectList)
{
var dataClass = schema.GetObject(member.typeid);
WriteLine("if (" + memberName + " == null || " + memberName + ".Length == 0)");
WriteLine(" serializable." + member.name + " = null;");
WriteLine("else");
WriteLine(" serializable." + member.name + " = " + memberName + ".Select(wrapper => wrapper.Item.Value).ToArray();");
}
else if (member.type == Constants.TypeStruct)
{
var dataClass = schema.GetStruct(member.typeid);
WriteLine("serializable." + member.name + " = " + memberName + "." + Utils.StructSerializationMethodName + "();");
}
else if (member.type == Constants.TypeStructList)
{
var dataClass = schema.GetStruct(member.typeid);
WriteLine("if (" + memberName + " == null || " + memberName + ".Length == 0)");
WriteLine(" serializable." + member.name + " = null;");
WriteLine("else");
WriteLine(" serializable." + member.name + " = " + memberName + ".Select(item => item." +
Utils.StructSerializationMethodName + "()).ToArray();");
}
else if (member.type == Constants.TypeEnumFlags)
{
WriteLine("if (" + memberName + " == null || " + memberName + ".Length == 0)");
WriteLine(" serializable." + member.name + " = null;");
WriteLine("else");
WriteLine(" serializable." + member.name + " = " + memberName + ".Select(item => item.Value).ToArray();");
}
else if (member.type == Constants.TypeInt)
{
WriteLine("serializable." + member.name + " = " + memberName + ".Value;");
}
else if (member.type == Constants.TypeFloat)
{
WriteLine("serializable." + member.name + " = " + memberName + ".Value;");
}
else if (member.type == Constants.TypeColor)
{
WriteLine("serializable." + member.name + " = Helpers.ColorToString(" + memberName + ");");
}
else if (member.type == Constants.TypeLayout)
{
WriteLine("serializable." + member.name + " = " + memberName + ".Data;");
}
else
{
WriteLine("serializable." + member.name + " = " + memberName + ";");
}
}
public TableSchema[] GetTables(string connectionString, DatabaseSchema database)
{
//Get only the 'tables' of type 'table'. The 'Tables' catlog also includes
//system tables, and the views
DataTable dt = GetSchemaData(connectionString,
Tables,
null /*restrictions[0] - catalog*/,
null /*restrictions[1] - unused*/,
null /*restrictions[2] - table*/,
TableType /*restrictions[3] - type*/);
TableSchema[] tables = new TableSchema[dt.Rows.Count];
int tableSchemaIdx = 0;
foreach (DataRow dr in dt.Rows)
{
TableSchema table = new TableSchema(database,
(String)dr[TableNameColumn],
String.Empty,
DateTime.MinValue);
tables[tableSchemaIdx++] = table;
}
return tables;
}
public void UpdateArguments(DatabaseSchema databaseSchema, DataTable arguments)
{
if (arguments.Columns.Count == 0)
{
return; //empty datatable
}
var argumentsKeyMap = new ArgumentsKeyMap(arguments);
var argumentGrouping =
arguments.Rows.OfType <DataRow>()
.ToLookup(
x =>
(x[argumentsKeyMap.OwnerKey] ?? string.Empty) + "." +
(x[argumentsKeyMap.SprocName] ?? string.Empty));
bool hasPackage = !string.IsNullOrEmpty(argumentsKeyMap.PackageKey);
//project the sprocs (which won't have packages) into a distinct view
DataTable sprocTable;
arguments.DefaultView.Sort = argumentsKeyMap.SprocName;
if (!hasPackage)
{
sprocTable = arguments.DefaultView.ToTable(true, argumentsKeyMap.SprocName, argumentsKeyMap.OwnerKey); //distinct
}
else
{
sprocTable = arguments.DefaultView.ToTable(true, argumentsKeyMap.SprocName, argumentsKeyMap.OwnerKey, argumentsKeyMap.PackageKey);
}
var sprocFilter = StoredProcedureFilter;
var packFilter = PackageFilter;
//go thru all sprocs with arguments- if not in sproc list, add it
foreach (DataRow row in sprocTable.Rows)
{
string name = row[argumentsKeyMap.SprocName].ToString();
//a procedure without a name?
if (string.IsNullOrEmpty(name))
{
continue;
}
if (sprocFilter != null && sprocFilter.Exclude(name))
{
continue;
}
string owner = row[argumentsKeyMap.OwnerKey].ToString();
if (argumentsKeyMap.IsDb2)
{
//ignore db2 system sprocs
if (IsDb2SystemSchema(owner))
{
continue;
}
}
string package = null; //for non-Oracle, package is always null
if (hasPackage)
{
package = row[argumentsKeyMap.PackageKey].ToString();
if (string.IsNullOrEmpty(package))
{
package = null; //so we can match easily
}
else if (packFilter != null && packFilter.Exclude(package))
{
continue;
}
}
var rows = argumentGrouping[owner + "." + name];
if (!string.IsNullOrEmpty(argumentsKeyMap.OrdinalKey))
{
rows = rows.OrderBy(r => r[argumentsKeyMap.OrdinalKey]);
}
List <DatabaseArgument> args = StoredProcedureArguments(rows, argumentsKeyMap);
DatabaseStoredProcedure sproc = FindStoredProcedureOrFunction(databaseSchema, name, owner, package);
if (sproc == null) //sproc in a package and not found before?
{
sproc = CreateProcedureOrFunction(databaseSchema, args);
sproc.Name = name;
sproc.SchemaOwner = owner;
sproc.Package = package;
}
else
{
//clear any existing arguments before we add them
sproc.Arguments.Clear();
}
sproc.Arguments.AddRange(args);
}
}
public CommandSchema[] GetCommands(string connectionString, DatabaseSchema database)
{
//SQLite doesn't support stored commands eg stored procs
return new CommandSchema[0];
}
public SqlServerDb(DatabaseSchema schema)
{
this.executor = new SqlServerExecutor(schema);
}
