override internal void LoadForView()
{
try
{
string query = "select * from information_schema.columns where table_catalog = '" +
this.View.Database.Name + "' and table_schema = '" + this.View.Schema +
"' and table_name = '" + this.View.Name + "' order by ordinal_position";
IDbConnection cn = ConnectionHelper.CreateConnection(this.dbRoot, this.View.Database.Name);
DataTable metaData = new DataTable();
DbDataAdapter adapter = PostgreSQLDatabases.CreateAdapter(query, cn);
adapter.Fill(metaData);
cn.Close();
metaData.Columns["udt_name"].ColumnName = "TYPE_NAME";
metaData.Columns["data_type"].ColumnName = "TYPE_NAMECOMPLETE";
if (metaData.Columns.Contains("TYPE_NAME"))
{
f_TypeName = metaData.Columns["TYPE_NAME"];
}
if (metaData.Columns.Contains("TYPE_NAMECOMPLETE"))
{
f_TypeNameComplete = metaData.Columns["TYPE_NAMECOMPLETE"];
}
PopulateArray(metaData);
}
catch {}
}
static public IDbConnection CreateConnection(Root dbRoot, string database)
{
IDbConnection cn = PostgreSQLDatabases.CreateConnection(dbRoot.ConnectionString);
cn.Open();
cn.ChangeDatabase(database);
return(cn);
}
override internal void LoadAll()
{
string query =
"select datname as CATALOG_NAME, s.usename as SCHEMA_OWNER, current_schema() as SCHEMA_NAME from pg_database d " +
"INNER JOIN pg_user s on d.datdba = s.usesysid where datistemplate = 'f' ORDER BY datname";
DbDataAdapter adapter = PostgreSQLDatabases.CreateAdapter(query, this.dbRoot.ConnectionString);
DataTable metaData = new DataTable();
adapter.Fill(metaData);
PopulateArray(metaData);
}
override internal void LoadAll()
{
try
{
string select = @"SELECT current_database() as table_catalog, tab.relname AS table_name, " +
"n.nspname as TABLE_NAMESPACE, cls.relname as INDEX_NAME, idx.indisunique as UNIQUE, " +
"idx.indisclustered as CLUSTERED, a.amname as TYPE, indkey AS columns FROM pg_index idx " +
"JOIN pg_class cls ON cls.oid=indexrelid " +
"JOIN pg_class tab ON tab.oid=indrelid AND tab.relname = '" + this.Table.Name + "' " +
"JOIN pg_namespace n ON n.oid=tab.relnamespace AND n.nspname = '" + this.Table.Schema + "' " +
"JOIN pg_am a ON a.oid = cls.relam " +
"LEFT JOIN pg_depend dep ON (dep.classid = cls.tableoid AND dep.objid = cls.oid AND dep.refobjsubid = '0') " +
"LEFT OUTER JOIN pg_constraint con ON (con.tableoid = dep.refclassid AND con.oid = dep.refobjid) " +
"WHERE con.conname IS NULL ORDER BY cls.relname;";
IDbConnection cn = ConnectionHelper.CreateConnection(this.dbRoot, this.Table.Tables.Database.Name);
DbDataAdapter adapter = PostgreSQLDatabases.CreateAdapter(select, cn);
DataTable metaData = new DataTable();
adapter.Fill(metaData);
cn.Close();
PopulateArrayNoHookup(metaData);
for (int i = 0; i < this.Count; i++)
{
Index index = this[i] as Index;
if (null != index)
{
string s = index._row["columns"] as string;
string[] colIndexes = s.Split(' ');
foreach (string colIndex in colIndexes)
{
if (colIndex != "0")
{
int id = Convert.ToInt32(colIndex);
Column column = this.Table.Columns[id - 1] as Column;
index.AddColumn(column.Name);
}
}
}
}
}
catch {}
}
override internal void LoadAll()
{
try
{
string query = "select * from information_schema.views where table_schema = '" + this.Database.SchemaName + "'";
IDbConnection cn = ConnectionHelper.CreateConnection(this.dbRoot, this.Database.Name);
DataTable metaData = new DataTable();
DbDataAdapter adapter = PostgreSQLDatabases.CreateAdapter(query, cn);
adapter.Fill(metaData);
cn.Close();
PopulateArray(metaData);
}
catch {}
}
override internal void LoadAll()
{
try
{
string query = "SELECT routine_definition as PROCEDURE_DEFINITION, specific_name, " +
"routine_name as PROCEDURE_NAME, routine_schema as PROCEDURE_SCHEMA, routine_catalog as PROCEDURE_CATALOG " +
"from information_schema.routines where routine_schema = '" + this.Database.SchemaName +
"' and routine_catalog = '" + this.Database.Name + "'";
IDbConnection cn = ConnectionHelper.CreateConnection(this.dbRoot, this.Database.Name);
DataTable metaData = new DataTable();
DbDataAdapter adapter = PostgreSQLDatabases.CreateAdapter(query, cn);
adapter.Fill(metaData);
cn.Close();
PopulateArray(metaData);
}
catch {}
}
internal override void LoadAll()
{
string query = "select * from information_schema.domains where domain_catalog = '" + this.Database.Name +
"' and domain_schema = '" + this.Database.SchemaName + "'";
IDbConnection cn = ConnectionHelper.CreateConnection(this.dbRoot, this.Database.Name);
DataTable metaData = new DataTable();
DbDataAdapter adapter = PostgreSQLDatabases.CreateAdapter(query, cn);
adapter.Fill(metaData);
cn.Close();
metaData.Columns["udt_name"].ColumnName = "DATA_TYPE";
metaData.Columns["data_type"].ColumnName = "TYPE_NAMECOMPLETE";
if (metaData.Columns.Contains("TYPE_NAMECOMPLETE"))
{
f_TypeNameComplete = metaData.Columns["TYPE_NAMECOMPLETE"];
}
PopulateArray(metaData);
}
override internal void LoadForTable()
{
IDbConnection cn = null;
try
{
string query = "select * from information_schema.columns where table_catalog = '" +
this.Table.Database.Name + "' and table_schema = '" + this.Table.Schema +
"' and table_name = '" + this.Table.Name + "' order by ordinal_position";
cn = ConnectionHelper.CreateConnection(this.dbRoot, this.Table.Database.Name);
DataTable metaData = new DataTable();
DbDataAdapter adapter = PostgreSQLDatabases.CreateAdapter(query, cn);
adapter.Fill(metaData);
metaData.Columns["udt_name"].ColumnName = "TYPE_NAME";
metaData.Columns["data_type"].ColumnName = "TYPE_NAMECOMPLETE";
if (metaData.Columns.Contains("TYPE_NAME"))
{
f_TypeName = metaData.Columns["TYPE_NAME"];
}
if (metaData.Columns.Contains("TYPE_NAMECOMPLETE"))
{
f_TypeNameComplete = metaData.Columns["TYPE_NAMECOMPLETE"];
}
PopulateArray(metaData);
// IsAutoKey logic
query = @"SELECT a.attname AS column_name, substring(pg_get_expr(ad.adbin, c.oid) " +
@"FROM '[\'""]+(.+?)[\'""]+') AS seq_name " +
"FROM pg_class c, pg_namespace n, pg_attribute a, pg_attrdef ad " +
"WHERE n.nspname = '" + this.Table.Schema + "' AND c.relname = '" + this.Table.Name + "' " +
"AND c.relnamespace = n.oid " +
"AND a.attrelid = c.oid AND a.atthasdef = true " +
"AND ad.adrelid = c.oid AND ad.adnum = a.attnum " +
@"AND pg_get_expr(ad.adbin, c.oid) LIKE 'nextval(%'";
DataTable seqData = new DataTable();
adapter = PostgreSQLDatabases.CreateAdapter(query, cn);
adapter.Fill(seqData);
DataRowCollection rows = seqData.Rows;
if (rows.Count > 0)
{
string colName;
for (int i = 0; i < rows.Count; i++)
{
colName = rows[i]["column_name"] as string;
PostgreSQLColumn col = this[colName] as PostgreSQLColumn;
col._isAutoKey = true;
// col.AutoKeyText = col.Default.Replace("nextval", "currval").Replace("\"", "\"\"");
query = "SELECT min_value, increment_by FROM \"" + rows[i]["seq_name"] + "\"";
adapter = PostgreSQLDatabases.CreateAdapter(query, cn);
DataTable autokeyData = new DataTable();
adapter.Fill(autokeyData);
Int64 a;
a = (Int64)autokeyData.Rows[0]["min_value"];
col._autoInc = Convert.ToInt32(a);
a = (Int64)autokeyData.Rows[0]["increment_by"];
col._autoSeed = Convert.ToInt32(a);
}
}
cn.Close();
}
catch
{
if (cn != null)
{
if (cn.State == ConnectionState.Open)
{
cn.Close();
}
}
}
}
public System.Data.IDbConnection CreateConnection()
{
return(PostgreSQLDatabases.CreateConnection(""));
}
private void _LoadAll(string query1, string query2)
{
IDbConnection cn = null;
try
{
cn = ConnectionHelper.CreateConnection(this.dbRoot, this.Table.Database.Name);
DataTable metaData1 = new DataTable();
DataTable metaData2 = new DataTable();
DbDataAdapter adapter = PostgreSQLDatabases.CreateAdapter(query1, cn);
adapter.Fill(metaData1);
adapter = PostgreSQLDatabases.CreateAdapter(query2, cn);
adapter.Fill(metaData2);
DataRowCollection rows = metaData2.Rows;
int count = rows.Count;
for (int i = 0; i < count; i++)
{
metaData1.ImportRow(rows[i]);
}
PopulateArrayNoHookup(metaData1);
if (metaData1.Rows.Count > 0)
{
string catalog = this.Table.Database.Name;
string schema;
string table;
string[] cols = null;
string q;
string query =
"SELECT c.conname AS constraint_name, " +
"t.relname AS table_name, " +
"array_to_string(c.conkey, ' ') AS constraint_key, " +
"t2.relname AS references_table, " +
"array_to_string(c.confkey, ' ') AS fk_constraint_key " +
"FROM pg_constraint c " +
"LEFT JOIN pg_class t ON c.conrelid = t.oid " +
"LEFT JOIN pg_class t2 ON c.confrelid = t2.oid " +
"WHERE c.contype = 'f' and c.conname = ";
foreach (ForeignKey key in this)
{
//------------------------------------------------
// Primary
//------------------------------------------------
schema = key._row["PK_TABLE_SCHEMA"] as string;
table = key._row["PK_TABLE_NAME"] as string;
string keyName = string.Empty;
try
{
keyName = key.Name.Split('.')[1];
}
catch
{
keyName = key.Name;
}
q = query;
q += "'" + keyName + "'";
DataTable metaData = new DataTable();
adapter = PostgreSQLDatabases.CreateAdapter(q, cn);
adapter.Fill(metaData);
string[] ordinals = ((string)metaData.Rows[0][4]).Split(' ');
foreach (string ordinal in ordinals)
{
int c = key.PrimaryTable.Columns.Count;
string colName = key.PrimaryTable.Columns[Convert.ToInt32(ordinal) - 1].Name;
key.AddForeignColumn(catalog, "", table, colName, true);
}
//for (int i = 0; i < cols.GetLength(0); i++)
//{
// key.AddForeignColumn(catalog, "", table, metaData.Rows[i]["COLUMN"] as string, true);
//}
//------------------------------------------------
// Foreign
//------------------------------------------------
schema = key._row["FK_TABLE_SCHEMA"] as string;
table = key._row["FK_TABLE_NAME"] as string;
ordinals = ((string)metaData.Rows[0][2]).Split(' ');
foreach (string ordinal in ordinals)
{
int c = key.ForeignTable.Columns.Count;
string colName = key.ForeignTable.Columns[Convert.ToInt32(ordinal) - 1].Name;
key.AddForeignColumn(catalog, "", table, colName, false);
}
}
}
}
catch (Exception ex)
{
string s = ex.Message;
}
cn.Close();
}
