public bool TableExists(IDataContext db, string tableName)
{
if (db == null)
{
throw new ArgumentNullException("session");
}
if (tableName == null)
{
throw new ArgumentNullException("tableName");
}
if (!NamingRule.IsValidSqlName(tableName))
{
throw new ArgumentOutOfRangeException("tableName");
}
//检查连接
var sql = new SqlString(
"select coalesce(count(table_name), 0) ",
"from information_schema.tables ",
"where table_type='BASE TABLE' and table_schema='public' and table_name=",
Parameter.Placeholder);
var n = (long)db.QueryValue(sql, tableName);
return(n > 0);
}
public void AddFK(IDataContext db, string columnName, string refTable, OnDeleteAction act)
{
if (db == null)
{
throw new ArgumentNullException("session");
}
if (string.IsNullOrEmpty(columnName))
{
throw new ArgumentNullException("columnName");
}
if (!NamingRule.IsValidSqlName(columnName))
{
throw new ArgumentOutOfRangeException("columnName");
}
if (string.IsNullOrEmpty(refTable))
{
throw new ArgumentNullException("refTable");
}
var onDelete = OnDeleteMapping[act];
var fkName = this.GenerateFkName(columnName);
var sql = string.Format(CultureInfo.InvariantCulture,
"alter table \"{0}\" add constraint \"{1}\" foreign key (\"{2}\") references \"{3}\" on delete {4}",
this.Name, fkName, columnName, refTable, onDelete);
db.Execute(SqlString.Parse(sql));
}
public void AlterColumnNullable(IDataContext db, string columnName, bool nullable)
{
if (db == null)
{
throw new ArgumentNullException("session");
}
if (string.IsNullOrEmpty(columnName))
{
throw new ArgumentNullException("columnName");
}
if (!NamingRule.IsValidSqlName(columnName))
{
throw new ArgumentOutOfRangeException("columnName");
}
//TODO
//DO nothing
/*
* var sql = string.Format(CultureInfo.InvariantCulture,
* "alter table [{0}] alter column [{1}] {2}",
* this.Name, columnName, sqlType);
* db.Execute(SqlString.Parse(sql));
*/
}
private void LoadColumns(IDataContext db, string tableName)
{
if (db == null)
{
throw new ArgumentNullException("session");
}
if (string.IsNullOrEmpty(tableName))
{
throw new ArgumentNullException("tableName");
}
if (!NamingRule.IsValidSqlName(tableName))
{
throw new ArgumentOutOfRangeException("tableName");
}
var sql = new SqlString(
"select column_name, data_type, is_nullable, character_maximum_length ",
"from information_schema.columns ",
"where table_name=", Parameter.Placeholder, " ",
"order by ordinal_position");
var records = db.QueryAsDictionary(sql, tableName);
this.columns.Clear();
foreach (var r in records)
{
var column = new PgColumnMetadata(r);
this.columns.Add(column.Name, column);
}
}
public void CreateTable(IDataContext db, string tableName, string label)
{
if (db == null)
{
throw new ArgumentNullException("session");
}
if (string.IsNullOrEmpty(tableName))
{
throw new ArgumentNullException("tableName");
}
if (!NamingRule.IsValidSqlName(tableName))
{
throw new ArgumentOutOfRangeException("tableName");
}
LoggerProvider.EnvironmentLogger.Debug(String.Format("Creating Table [{0}]...", tableName));
tableName = tableName.SqlEscape();
var sb = new SqlStringBuilder();
sb.Add("create table [");
sb.Add(tableName);
sb.Add("] (");
sb.Add("[_id] bigint not null identity(1,1), ");
sb.Add("primary key([_id]))");
var sql = sb.ToSqlString();
db.Execute(sql);
//TODO
// SetTableComment(db, tableName, label);
}
private string GenerateFkName(string columnName)
{
if (string.IsNullOrEmpty(columnName))
{
throw new ArgumentNullException("columnName");
}
if (!NamingRule.IsValidSqlName(columnName))
{
throw new ArgumentOutOfRangeException("columnName");
}
return(string.Format(CultureInfo.InvariantCulture, "{0}_{1}_fkey", this.Name, columnName));
}
public IColumnMetadata GetColumn(string columnName)
{
if (string.IsNullOrEmpty(columnName))
{
throw new ArgumentNullException("columnName");
}
if (!NamingRule.IsValidSqlName(columnName))
{
throw new ArgumentOutOfRangeException("columnName");
}
return(this.columns[columnName]);
}
public bool ColumnExists(string columnName)
{
if (string.IsNullOrEmpty(columnName))
{
throw new ArgumentNullException("columnName");
}
if (!NamingRule.IsValidSqlName(columnName))
{
throw new ArgumentOutOfRangeException("columnName");
}
return(this.columns.ContainsKey(columnName));
}
public void DeleteFK(IDataContext db, string columnName)
{
if (db == null)
{
throw new ArgumentNullException("session");
}
if (string.IsNullOrEmpty(columnName))
{
throw new ArgumentNullException("columnName");
}
if (!NamingRule.IsValidSqlName(columnName))
{
throw new ArgumentOutOfRangeException("columnName");
}
var fkName = this.GenerateFkName(columnName);
this.DeleteConstraint(db, fkName);
}
public PgTableContext(IDataContext db, string tableName)
{
if (db == null)
{
throw new ArgumentNullException("session");
}
if (string.IsNullOrEmpty(tableName))
{
throw new ArgumentNullException("tableName");
}
if (!NamingRule.IsValidSqlName(tableName))
{
throw new ArgumentOutOfRangeException("tableName");
}
this.Name = tableName;
this.LoadColumns(db, tableName);
}
public void DeleteColumn(IDataContext db, string columnName)
{
if (db == null)
{
throw new ArgumentNullException("session");
}
if (string.IsNullOrEmpty(columnName))
{
throw new ArgumentNullException("columnName");
}
if (!NamingRule.IsValidSqlName(columnName))
{
throw new ArgumentOutOfRangeException("columnName");
}
var sql = string.Format(CultureInfo.InvariantCulture,
"alter table \"{0}\" drop column \"{1}\"",
this.Name, columnName);
db.Execute(SqlString.Parse(sql));
}
public void CreateTable(IDataContext db, string tableName, string label)
{
if (db == null)
{
throw new ArgumentNullException("session");
}
if (string.IsNullOrEmpty(tableName))
{
throw new ArgumentNullException("tableName");
}
if (!NamingRule.IsValidSqlName(tableName))
{
throw new ArgumentOutOfRangeException("tableName");
}
LoggerProvider.EnvironmentLogger.Debug(String.Format("Creating Table [{0}]...", tableName));
tableName = tableName.SqlEscape();
var sb = new SqlStringBuilder();
sb.Add("create table ");
sb.Add('"' + tableName + '"');
sb.Add("(");
sb.Add("_id");
sb.Add(" ");
sb.Add("bigserial not null, ");
sb.Add("primary key(");
sb.Add("_id");
sb.Add(")) without oids");
var sql = sb.ToSqlString();
db.Execute(sql);
SetTableComment(db, tableName, label);
}
public bool FKExists(IDataContext db, string columnName)
{
if (db == null)
{
throw new ArgumentNullException("session");
}
if (string.IsNullOrEmpty(columnName))
{
throw new ArgumentNullException("columnName");
}
if (!NamingRule.IsValidSqlName(columnName))
{
throw new ArgumentOutOfRangeException("columnName");
}
var sql = SqlString.Parse(@"
select coalesce(count(constraint_name), 0)
from information_schema.key_column_usage
where constraint_schema='public' and table_name=? and column_name=?
");
var n = (long)db.QueryValue(sql, this.Name, columnName);
return(n > 0);
}
public void AlterColumnNullable(IDataContext db, string columnName, bool nullable)
{
if (db == null)
{
throw new ArgumentNullException("session");
}
if (string.IsNullOrEmpty(columnName))
{
throw new ArgumentNullException("columnName");
}
if (!NamingRule.IsValidSqlName(columnName))
{
throw new ArgumentOutOfRangeException("columnName");
}
var action = nullable ? "drop not null" : "set not null";
var sql = string.Format(CultureInfo.InvariantCulture,
"alter table \"{0}\" alter column \"{1}\" {2}",
this.Name, columnName, action);
db.Execute(SqlString.Parse(sql));
}