protected override void PopulateTables(DbObjectCollection <Table> tablesCollection)
{
var tables = Connection.GetSchema("Tables", new[] { null, Connection.Database });
foreach (DataRow row in tables.Rows)
{
if ((string)row["TABLE_TYPE"] == "BASE TABLE")
{
var table = new MySqlTable(this)
{
Name = row["TABLE_NAME"].ToString()
};
tablesCollection.Add(table);
}
}
var views = Connection.GetSchema("Views", new[] { null, Connection.Database });
foreach (DataRow row in views.Rows)
{
var table = new MySqlTable(this)
{
Name = row["TABLE_NAME"].ToString()
};
table.SetView(true);
tablesCollection.Add(table);
}
}
protected override void PopulateTables(DbObjectCollection <Table> tablesCollection)
{
string[] restrictions = new string[4];
restrictions[3] = "Table";
var tables = this.Connection.GetSchema("Tables", restrictions);
foreach (DataRow row in tables.Rows)
{
var table = new AccessTable(this);
table.Name = row["TABLE_NAME"].ToString();
tablesCollection.Add(table);
}
restrictions[3] = "View";
var views = this.Connection.GetSchema("Tables", restrictions);
foreach (DataRow row in views.Rows)
{
var table = new AccessTable(this);
table.Name = row["TABLE_NAME"].ToString();
table.SetView(true);
tablesCollection.Add(table);
}
}
protected override void PopulateTables(DbObjectCollection <Table> tablesCollection)
{
using (CreateConnectionScope()) {
using (var tables = Connection.GetSchema("Tables", new[] { null, null, null, "Base Table" })) {
foreach (DataRow row in tables.Rows)
{
var table = new SqlTable(this,
(string)row["TABLE_SCHEMA"],
(string)row["TABLE_NAME"]);
tablesCollection.Add(table);
}
}
using (var dataTable = ExecuteTable(
"SELECT " +
" t.name AS TableName, " +
" s.name AS SchemaName, " +
" p.rows AS RowCounts, " +
" (SUM(a.total_pages) * 8) AS TotalSpaceKB, " +
" (SUM(a.used_pages) * 8) AS UsedSpaceKB " +
"FROM sys.tables t " +
"INNER JOIN sys.indexes i ON t.OBJECT_ID = i.object_id " +
"INNER JOIN sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id " +
"INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id " +
"LEFT OUTER JOIN sys.schemas s ON t.schema_id = s.schema_id " +
"WHERE " +
" t.NAME NOT LIKE 'dt%' " +
" AND t.is_ms_shipped = 0 " +
" AND i.OBJECT_ID > 255 " +
"GROUP BY " +
" t.Name, s.Name, p.Rows")) {
foreach (DataRow row in dataTable.Rows)
{
var schema = (string)row["SchemaName"];
var name = (string)row["TableName"];
foreach (SqlTable table in tablesCollection)
{
if (table.SchemaName == schema && table.TableName == name)
{
table.TotalRows = Convert.ToInt64(row["RowCounts"]);
table.TotalSizeKB = Convert.ToDecimal(row["TotalSpaceKB"]);
table.TotalUsedKB = Convert.ToDecimal(row["UsedSpaceKB"]);
break;
}
}
}
}
using (var views = Connection.GetSchema("Tables", new[] { null, null, null, "View" })) {
foreach (DataRow row in views.Rows)
{
var table = new SqlTable(this,
(string)row["TABLE_SCHEMA"],
(string)row["TABLE_NAME"]);
table.SetView(true);
tablesCollection.Add(table);
}
}
}
}
protected override void PopulateTables(DbObjectCollection <Table> tablesCollection)
{
var tables = Connection.GetSchema("Tables");
foreach (DataRow row in tables.Rows)
{
var table = new SQLiteTable(this)
{
Name = row["TABLE_NAME"].ToString()
};
tablesCollection.Add(table);
}
var views = Connection.GetSchema("Views");
foreach (DataRow row in views.Rows)
{
var table = new SQLiteTable(this)
{
Name = row["TABLE_NAME"].ToString()
};
table.SetView(true);
tablesCollection.Add(table);
}
}
protected override void PopulateUserDefinedFunctions(DbObjectCollection <UserDefinedFunction> userDefinedFunctionsCollection)
{
using (var command = Connection.CreateCommand()) {
command.CommandText =
"SELECT n.nspname as \"Schema\", " +
" p.proname as \"Name\", " +
" pg_catalog.pg_get_function_result(p.oid) as \"Result data type\", " +
" pg_catalog.pg_get_function_arguments(p.oid) as \"Argument data types\", " +
"CASE " +
" WHEN p.proisagg THEN 'agg' " +
" WHEN p.proiswindow THEN 'window' " +
" WHEN p.prorettype = 'pg_catalog.trigger'::pg_catalog.regtype THEN 'trigger' " +
" ELSE 'normal' " +
"END as \"Type\" " +
"FROM pg_catalog.pg_proc p " +
" LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace " +
"WHERE pg_catalog.pg_function_is_visible(p.oid) " +
" AND n.nspname <> 'pg_catalog' " +
" AND n.nspname <> 'information_schema' " +
" AND n.nspname = :schema " +
"ORDER BY 1, 2, 4; ";
command.Parameters.AddWithValue(":schema", "public");
using (var reader = command.ExecuteReader()) {
while (reader.Read())
{
var fn = new PgSqlUserDefinedFunction(this, reader.GetString(1));
fn.ReturnType = reader.GetString(2);
userDefinedFunctionsCollection.Add(fn);
}
}
}
}
public override DbObjectCollection CreateCollection(IConnectionInformation info)
{
var collection = new DbObjectCollection(info);
var con = collection.Context.Database.Connection;
if (con.State != System.Data.ConnectionState.Open)
{
con.Open();
}
collection.DataTypes = new Dictionary <string, DbObjectCollection.DataType>();
foreach (DataRow row in GetDataTypes().Rows)
{
var dt = new DbObjectCollection.DataType(row);
collection.DataTypes.Add(dt.Name, dt);
}
collection.RegisterObjects(con.GetSchema("Tables"), true, "TABLE_SCHEMA NOT IN('information_schema','pg_catalog')");
collection.RegisterObjects(con.GetSchema("Views"), false, "TABLE_SCHEMA NOT IN('information_schema','pg_catalog')");
var creator = new DbObjectCollection.ColumnCreator();
collection.RegisterMembers(con.GetSchema("Columns"), creator);
collection.RegisterPrimaryKey();
return(collection);
}
protected override void PopulateStoredProcedures(DbObjectCollection <StoredProcedure> storedProceduresCollection)
{
var procedures = this.Connection.GetSchema("Procedures", new string[] { null, this.connection.Database });
foreach (DataRow row in procedures.Rows)
{
var sp = new MySqlStoredProcedure(this, row);
storedProceduresCollection.Add(sp);
}
}
protected override void PopulateTables(DbObjectCollection <Table> tablesCollection)
{
using (var tables = Connection.GetSchema("Tables")) {
foreach (DataRow row in tables.Rows)
{
var table = new SqlCeTable(this, (string)row["TABLE_NAME"]);
tablesCollection.Add(table);
}
}
}
protected override void PopulateStoredProcedures(DbObjectCollection <StoredProcedure> storedProceduresCollection)
{
var procedures = Connection.GetSchema("Procedures", new[] { null, Connection.Database, null, "PROCEDURE" });
foreach (DataRow row in procedures.Rows)
{
var name = (string)row["SPECIFIC_NAME"];
var sp = new MySqlStoredProcedure(this, name);
storedProceduresCollection.Add(sp);
}
}
protected override void PopulateStoredProcedures(DbObjectCollection <StoredProcedure> storedProceduresCollection)
{
var procedures = this.Connection.GetSchema("Procedures", new string[] { null, null, null, "PROCEDURE" });
foreach (DataRow row in procedures.Rows)
{
var sp = new SqlStoredProcedure(this);
sp.Schema = (string)row["SPECIFIC_SCHEMA"];
sp.Name = (string)row["SPECIFIC_NAME"];
storedProceduresCollection.Add(sp);
}
}
public override DbObjectCollection CreateCollection(IConnectionInformation info)
{
var collection = new DbObjectCollection(info);
var con = collection.Initialization();
var creator = new DbObjectCollection.ColumnCreator();
collection.RegisterObjects(con.GetSchema("Tables"), true, "TABLE_TYPE = 'TABLE'");
collection.RegisterMembers(con.GetSchema("Columns"), creator);
collection.RegisterPrimaryKey();
return(collection);
}
protected override void PopulateTables(DbObjectCollection <Table> tablesCollection)
{
using (var tables = this.Connection.GetSchema("Tables")) {
foreach (DataRow row in tables.Rows)
{
var table = new SqlCeTable(this);
table.Schema = row["TABLE_SCHEMA"] as string;
table.Name = row["TABLE_NAME"] as string;
tablesCollection.Add(table);
}
}
}
protected override void PopulateUserDefinedFunctions(DbObjectCollection <UserDefinedFunction> userDefinedFunctions)
{
using (var functions = connection.GetSchema("Functions")) {
foreach (DataRow row in functions.Rows)
{
if ((short)row["IS_SYSTEM_FUNCTION"] == 0)
{
var udf = new FbUserDefinedFunction(this, (string)row["FUNCTION_NAME"]);
userDefinedFunctions.Add(udf);
}
}
}
}
protected override void PopulateStoredProcedures(DbObjectCollection <StoredProcedure> storedProceduresCollection)
{
using (var procedures = connection.GetSchema("Procedures")) {
foreach (DataRow row in procedures.Rows)
{
if ((short)row["IS_SYSTEM_PROCEDURE"] == 0)
{
var sp = new FbStoredProcedure(this, (string)row["PROCEDURE_NAME"]);
storedProceduresCollection.Add(sp);
}
}
}
}
public override DbObjectCollection CreateCollection(IConnectionInformation info)
{
var collection = new DbObjectCollection(info);
collection.Names.DataTypeKey = "NativeDataType";
var con = collection.Initialization();
var table = con.GetSchema("Tables");
var creator = new DbObjectCollection.ColumnCreator();
var rows = table.AsEnumerable().Where(a => !a.Field <string>("TABLE_NAME").EndsWith("$")).ToArray();
collection.RegisterObjects(rows, true);
collection.RegisterMembers(con.GetSchema("Columns"), creator);
return(collection);
}
protected override void PopulateUserDefinedFunctions(DbObjectCollection <UserDefinedFunction> userDefinedFunctionsCollection)
{
var functions = Connection.GetSchema("Procedures", new[] { null, Connection.Database, null, "FUNCTION" });
foreach (DataRow row in functions.Rows)
{
var name = (string)row["SPECIFIC_NAME"];
var returnType = (string)row["DTD_IDENTIFIER"];
var fn = new MySqlUserDefinedFunction(this, name)
{
ReturnType = returnType
};
userDefinedFunctionsCollection.Add(fn);
}
}
protected override void PopulateTables(DbObjectCollection <Table> tablesCollection)
{
var tables = this.Connection.GetSchema("Tables", new string[] { this.connection.Database, "public" });
foreach (DataRow row in tables.Rows)
{
var table = new PgSqlTable(this);
table.Name = row["table_name"] as string;
tablesCollection.Add(table);
if (row["table_type"] as string == "VIEW")
{
table.SetView(true);
}
}
}
public override DbObjectCollection CreateCollection(IConnectionInformation info)
{
var collection = new DbObjectCollection(info);
var con = collection.Initialization();
var creator = new ColumnCreator();
collection.RegisterObjects(con.GetSchema("Tables"), true);
collection.RegisterObjects(con.GetSchema("Views"), false);
collection.RegisterMembers(con.GetSchema("Columns"), creator);
foreach (var obj in collection.Objects.Values
.Where(a => a.Kind == EObjectKind.Table && a.Columns.Values.Count(b => b.IsKey) == 1))
{
obj.Columns.Values.Where(a => a.IsKey).Single().ColumnIndex = null;
}
return(collection);
}
protected override void PopulateTables(DbObjectCollection <Table> tablesCollection)
{
using (var tables = Connection.GetSchema("Tables", new[] { Connection.Database, "public" })) {
foreach (DataRow row in tables.Rows)
{
var table = new PgSqlTable(this, (string)row["table_name"]);
tablesCollection.Add(table);
}
}
using (var views = Connection.GetSchema("Views", new[] { Connection.Database, "public" })) {
foreach (DataRow row in views.Rows)
{
var table = new PgSqlTable(this, (string)row["table_name"],
isView: true, isReadOnly: (string)row["is_updatable"] == "NO");
tablesCollection.Add(table);
}
}
}
public override DbObjectCollection CreateCollection(IConnectionInformation info)
{
var collection = new DbObjectCollection(info);
collection.Names.DataType = "DATATYPE";
var con = collection.Initialization();
collection.RegisterObjects(collection.GetDataTable(
@"SELECT OWNER TABLE_SCHEMA, TABLE_NAME
FROM ALL_TABLES
WHERE OWNER NOT IN
('SYS', 'SYSTEM', 'SYSMAN','CTXSYS', 'MDSYS', 'OLAPSYS', 'ORDSYS', 'OUTLN','WKSYS', 'WMSYS', 'XDB', 'ORDPLUGINS')
AND OWNER NOT LIKE 'APEX%'
ORDER BY OWNER, TABLE_NAME"), true);
collection.RegisterObjects(collection.GetDataTable(
@"SELECT OWNER TABLE_SCHEMA, VIEW_NAME TABLE_NAME
FROM ALL_VIEWS
WHERE OWNER NOT IN
('SYS', 'SYSTEM', 'SYSMAN','CTXSYS', 'MDSYS', 'OLAPSYS', 'ORDSYS', 'OUTLN','WKSYS', 'WMSYS', 'XDB', 'ORDPLUGINS')
AND OWNER NOT LIKE 'APEX%'
ORDER BY OWNER, VIEW_NAME"), false);
var creator = new DbObjectCollection.ColumnCreator();
creator.Names.TrueValue = "Y";
creator.Names.CharName = "";
collection.RegisterMembers(collection.GetDataTable(
@"SELECT OWNER TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, COLUMN_ID AS ID, DATA_TYPE AS DataType,
DATA_LENGTH AS CHARACTER_MAXIMUM_LENGTH, DATA_PRECISION AS NUMERIC_PRECISION, DATA_SCALE AS NUMERIC_SCALE,
NULLABLE AS IS_NULLABLE, CHAR_USED, CHAR_LENGTH AS LengthInChars
FROM ALL_TAB_COLUMNS WHERE OWNER NOT IN
('SYS', 'SYSTEM', 'SYSMAN','CTXSYS', 'MDSYS', 'OLAPSYS', 'ORDSYS', 'OUTLN','WKSYS', 'WMSYS', 'XDB', 'ORDPLUGINS')
AND OWNER NOT LIKE 'APEX%'
ORDER BY OWNER, TABLE_NAME, ID"), creator);
var table = collection.GetDataTable(
@"select b.OWNER TABLE_SCHEMA,b.TABLE_NAME,b.COLUMN_NAME,b.POSITION ORDINAL_POSITION from all_constraints a
inner join all_cons_columns b ON a.OWNER=b.OWNER and a.CONSTRAINT_NAME=b.CONSTRAINT_NAME
where a.constraint_type='P'");
collection.RegisterPrimaryKey(table);
return(collection);
}
protected override void PopulateUserDefinedFunctions(DbObjectCollection <UserDefinedFunction> userDefinedFunctionsCollection)
{
using (var select = this.Connection.CreateCommand()) {
select.CommandText =
"SELECT b.Name AS [Schema], a.Name, type_name(p.system_type_id) " +
"FROM sys.objects a " +
"INNER JOIN sys.schemas b ON a.schema_id = b.schema_id " +
"INNER JOIN sys.parameters p ON p.object_id = a.object_id " +
"WHERE a.type in ('FN', 'IF', 'TF') AND p.parameter_id = 0";
using (var reader = select.ExecuteReader()) {
while (reader.Read())
{
var fn = new SqlUserDefinedFunction(this);
fn.Schema = reader.GetString(0);
fn.Name = reader.GetString(1);
fn.ReturnType = reader.GetString(2);
userDefinedFunctionsCollection.Add(fn);
}
}
}
}
protected override void PopulateTables(DbObjectCollection <Table> tablesCollection)
{
var tables = this.Connection.GetSchema("Tables", new string[] { null, this.connection.Database });
foreach (DataRow row in tables.Rows)
{
var table = new MySqlTable(this);
table.Name = row["TABLE_NAME"].ToString();
tablesCollection.Add(table);
}
var views = this.Connection.GetSchema("Views", new string[] { null, this.connection.Database });
foreach (DataRow row in views.Rows)
{
var table = new MySqlTable(this);
table.Name = row["TABLE_NAME"].ToString();
table.SetView(true);
tablesCollection.Add(table);
}
}
protected override void PopulateTables(DbObjectCollection <Table> tablesCollection)
{
using (var tables = connection.GetSchema("Tables")) {
foreach (DataRow row in tables.Rows)
{
if ((short)row["IS_SYSTEM_TABLE"] == 0)
{
var tableType = ((string)row["TABLE_TYPE"]).ToUpper();
var table = new FbTable(this, tableType == "VIEW")
{
TableName = (string)row["TABLE_NAME"]
};
var schema = row["TABLE_SCHEMA"] as string;
if (!string.IsNullOrEmpty(schema))
{
table.TableSchema = schema;
}
table.Name = table.TableName;
tablesCollection.Add(table);
}
}
}
}
protected override void PopulateTables(DbObjectCollection <Table> tablesCollection)
{
var tables = Connection.GetSchema("Tables", new[] { null, null, null, "Table" });
foreach (DataRow row in tables.Rows)
{
var schema = row["TABLE_SCHEMA"] as string;
var name = row["TABLE_NAME"] as string;
var table = CreateTableInstance(schema, name);
tablesCollection.Add(table);
}
var views = Connection.GetSchema("Tables", new[] { null, null, null, "View" });
foreach (DataRow row in views.Rows)
{
var schema = row["TABLE_SCHEMA"] as string;
var name = row["TABLE_NAME"] as string;
var view = CreateTableInstance(schema, name);
view.SetView(true);
tablesCollection.Add(view);
}
}
public override DbObjectCollection CreateCollection(IConnectionInformation info)
{
var collection = new DbObjectCollection(info);
collection.Names.DataTypeKey = "NativeDataType";
var con = collection.Initialization();
var creator = new DbObjectCollection.ColumnCreator();
collection.RegisterObjects(con.GetSchema("Tables"), true, "TABLE_TYPE = 'TABLE'");
collection.RegisterObjects(con.GetSchema("Views"), false);
collection.RegisterMembers(con.GetSchema("Columns"), creator);
var table = con.GetSchema("Indexes");
var rows = table.Select("PRIMARY_KEY=0");
foreach (var row in rows)
{
table.Rows.Remove(row);
}
collection.RegisterPrimaryKey(table);
return(collection);
}
protected override void PopulateStoredProcedures(DbObjectCollection <StoredProcedure> storedProceduresCollection)
{
// TODO: Populate PostgreSQL stored procedures
}
protected override void PopulateStoredProcedures(DbObjectCollection <StoredProcedure> storedProceduresCollection)
{
}
protected override void PopulateStoredProcedures(DbObjectCollection <StoredProcedure> storedProceduresCollection)
{
throw new NotSupportedException("Stored Procedures are not supported.");
}
protected override void PopulateTables(DbObjectCollection <Table> tablesCollection)
{
using (this.CreateConnectionScope()) {
var restrictions = new string[4];
restrictions[3] = "Base Table";
using (var tables = this.Connection.GetSchema("Tables", restrictions)) {
foreach (DataRow row in tables.Rows)
{
var table = new SqlTable(this);
table.Schema = (string)row["TABLE_SCHEMA"];
table.Name = (string)row["TABLE_NAME"];
tablesCollection.Add(table);
}
}
var dataTable = this.ExecuteTable(@"
SELECT
t.NAME AS TableName,
s.Name AS SchemaName,
p.rows AS RowCounts,
(SUM(a.total_pages) * 8) AS TotalSpaceKB,
(SUM(a.used_pages) * 8) AS UsedSpaceKB
FROM
sys.tables t
INNER JOIN
sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN
sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN
sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN
sys.schemas s ON t.schema_id = s.schema_id
WHERE
t.NAME NOT LIKE 'dt%'
AND t.is_ms_shipped = 0
AND i.OBJECT_ID > 255
GROUP BY
t.Name, s.Name, p.Rows");
foreach (DataRow row in dataTable.Rows)
{
var schema = (string)row["SchemaName"];
var name = (string)row["TableName"];
foreach (SqlTable table in tablesCollection)
{
if (table.Schema == schema && table.Name == name)
{
table.TotalRows = Convert.ToInt64(row["RowCounts"]);
table.TotalSizeKB = Convert.ToDecimal(row["TotalSpaceKB"]);
table.TotalUsedKB = Convert.ToDecimal(row["UsedSpaceKB"]);
break;
}
}
}
restrictions[3] = "View";
using (var views = this.Connection.GetSchema("Tables", restrictions)) {
foreach (DataRow row in views.Rows)
{
var table = new SqlTable(this);
table.Schema = (string)row["TABLE_SCHEMA"];
table.Name = (string)row["TABLE_NAME"];
table.SetView(true);
tablesCollection.Add(table);
}
}
}
}
