public override ITableDesc GetTableByName(string[] names, bool useCache)
{
if (useCache)
{
var t = Get(names);
if (t != null)
{
return(t);
}
}
string tn;
string sh;
if (names.Length == 2)
{
sh = names[0];
tn = names[1];
}
else
if (names.Length == 1)
{
sh = DefaultSchema();
tn = names[0];
}
else
{
throw new Exception("Incorrect table name");
}
using (var con = GetConnection())
{
con.Open();
string sql = @"SELECT EXISTS (
SELECT 1
FROM pg_catalog.pg_class c
JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE n.nspname = @schema
AND c.relname = @name
);";
DbCommand cmd = con.CreateCommand();
cmd.CommandText = sql;
ParserDbUtils.AddParam(cmd, "@schema", sh);
ParserDbUtils.AddParam(cmd, "@name", tn);
using (var reader = cmd.ExecuteReader())
{
if (!reader.Read())
{
throw new Exception("Table " + string.Join(".", names) + " is not found");
}
}
}
using (var con = GetConnection())
{
con.Open();
string sql = @"SELECT
attname AS column_name
, atttypid::regtype AS data_type
-- more attributes?
FROM pg_attribute
WHERE attrelid = @schemaTable::regclass -- table name, optionally schema-qualified
AND attnum > 0
AND NOT attisdropped
ORDER BY attnum";
DbCommand cmd = con.CreateCommand();
cmd.CommandText = sql;
string st = "";
if (!string.IsNullOrEmpty(sh))
{
st += "\"" + sh + "\"";
}
if (!string.IsNullOrEmpty(tn))
{
if (st != "")
{
st += ".";
}
st += "\"" + tn + "\"";
}
ParserDbUtils.AddParam(cmd, "@schemaTable", st);
TableDesc td = new TableDesc();
td.PhysicalTableName = tn;
td.LogicalTableName = tn;
td.PhysicalSchema = sh;
td.LogicalSchema = sh;
//td.DbDriver = Driver;
td.TableColumns = new List <Column>();
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
Column ci = new Column(
reader.GetString(reader.GetOrdinal("column_name")),
ColumnSqlTypeToSimpleType(reader.GetString(reader.GetOrdinal("data_type")))
);
td.TableColumns.Add(ci);
}
}
Set(names, td);
return(td);
}
}
public override ITableDesc GetTableByName(string[] names, bool useCache)
{
if (useCache)
{
var t = Get(names);
if (t != null)
{
return(t);
}
}
string tn;
string sh;
if (names.Length == 2)
{
sh = names[0];
tn = names[1];
}
else
if (names.Length == 1)
{
sh = DefaultSchema();
tn = names[0];
}
else
{
throw new Exception("Incorrect table name");
}
using (var con = GetConnection())
{
con.Open();
string sql = "select TABLE_SCHEMA, TABLE_NAME from INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA = @schema and TABLE_NAME = @name";
DbCommand cmd = con.CreateCommand();
cmd.CommandText = sql;
ParserDbUtils.AddParam(cmd, "@schema", sh);
ParserDbUtils.AddParam(cmd, "@name", tn);
using (var reader = cmd.ExecuteReader())
{
if (!reader.Read())
{
throw new Exception("Table " + string.Join(".", names) + " is not found");
}
}
}
using (var con = GetConnection())
{
con.Open();
string sql = @"select
COLUMN_NAME,
DATA_TYPE
from INFORMATION_SCHEMA.COLUMNS where TABLE_SCHEMA = @schema and TABLE_NAME = @name";
DbCommand cmd = con.CreateCommand();
cmd.CommandText = sql;
ParserDbUtils.AddParam(cmd, "@schema", sh);
ParserDbUtils.AddParam(cmd, "@name", tn);
TableDesc td = new TableDesc();
td.PhysicalTableName = tn;
td.LogicalTableName = tn;
td.PhysicalSchema = sh;
td.LogicalSchema = sh;
td.TableColumns = new List <Column>();
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
Column ci = new Column(
reader.GetString(reader.GetOrdinal("COLUMN_NAME")),
ColumnSqlTypeToSimpleType(reader.GetString(reader.GetOrdinal("DATA_TYPE"))));
td.TableColumns.Add(ci);
}
}
Set(names, td);
return(td);
}
}