public TableSchema (TableSchema table)
: base (table)
{
isSystemTable = table.isSystemTable;
tableSpaceName = table.tableSpaceName;
columns = new ColumnSchemaCollection (table.columns);
constraints = new ConstraintSchemaCollection (table.constraints);
triggers = new TriggerSchemaCollection (table.triggers);
}
public TableSchema (ISchemaProvider schemaProvider, string name)
: base (schemaProvider)
{
Name = name;
columns = new ColumnSchemaCollection ();
constraints = new ConstraintSchemaCollection ();
triggers = new TriggerSchemaCollection ();
}
public void Initialize(TableSchema table, TriggerSchemaCollection triggers)
{
if (table == null)
{
throw new ArgumentNullException("table");
}
if (triggers == null)
{
throw new ArgumentNullException("triggers");
}
this.table = table;
this.triggers = triggers;
if (action == SchemaActions.Alter)
{
foreach (TriggerSchema trigger in triggers)
{
AddTrigger(trigger);
}
}
}
public override TriggerSchemaCollection GetTableTriggers(TableSchema table)
{
if (table == null)
{
throw new ArgumentNullException("table");
}
TriggerSchemaCollection triggers = new TriggerSchemaCollection();
using (IPooledDbConnection conn = connectionPool.Request()) {
string sql = string.Format(@"SELECT
Tables.Name TableName,
Triggers.name TriggerName,
Triggers.crdate TriggerCreatedDate,
Comments.Text TriggerText
FROM sysobjects Triggers
INNER JOIN sysobjects Tables On
Triggers.parent_obj = Tables.id
INNER JOIN syscomments Comments On
Triggers.id = Comments.id
WHERE
Triggers.xtype = 'TR'
AND Tables.xtype = 'U'
AND Tables.Name = '{0}'
ORDER BY
Tables.Name,
Triggers.name" , table.Name);
using (IDbCommand command = conn.CreateCommand(sql)) {
using (IDataReader r = command.ExecuteReader()) {
while (r.Read())
{
System.Text.RegularExpressions.Regex parseRegEx = new System.Text.RegularExpressions.Regex
(string.Concat(
@"((CREATE\s*(Temp|Temporary)?\s*TRIGGER){1}\s?(\w+)\s?(IF NOT",
@" EXISTS)?\s?(BEFORE|AFTER|INSTEAD OF){1}\s?(\w+)\s*ON(\s+\w*",
@")\s*(FOR EACH ROW){1}\s*(BEGIN){1})\s+(\w|\W)*(END)"));
TriggerSchema trigger = new TriggerSchema(this);
trigger.TableName = table.Name;
trigger.Name = r.GetString(r.GetOrdinal("TriggerName"));
sql = r.GetString(r.GetOrdinal("TriggerText"));
System.Text.RegularExpressions.MatchCollection matchs = parseRegEx.Matches(sql);
if (matchs.Count > 0)
{
trigger.TriggerFireType = TriggerFireType.ForEachRow;
switch (matchs[0].Groups[7].Value.ToLower())
{
case "insert":
trigger.TriggerEvent = TriggerEvent.Insert;
break;
case "update":
trigger.TriggerEvent = TriggerEvent.Update;
break;
case "delete":
trigger.TriggerEvent = TriggerEvent.Delete;
break;
default:
throw new NotImplementedException();
}
switch (matchs[0].Groups[7].Value.ToLower())
{
case "before":
trigger.TriggerType = TriggerType.Before;
break;
case "after":
trigger.TriggerType = TriggerType.After;
break;
default:
throw new NotImplementedException();
}
StringBuilder sbSource = new StringBuilder();
foreach (System.Text.RegularExpressions.Capture c in matchs[0].Groups[11].Captures)
{
sbSource.Append(c.Value);
}
trigger.Source = sbSource.ToString();
}
triggers.Add(trigger);
}
}
}
conn.Release();
}
return(triggers);
}
private void InitializeThreaded (object state)
{
tables = schemaProvider.GetTables ();
dataTypes = schemaProvider.GetDataTypes ();
columns = originalTable.Columns;
constraints = originalTable.Constraints;
triggers = originalTable.Triggers;
//TODO: indices
indexes = new IndexSchemaCollection ();
System.Text.StringBuilder builder = new System.Text.StringBuilder ();
builder.Append ("Loading editor for TABLE ");
builder.Append (originalTable.Name);
builder.AppendLine ();
builder.Append (" columns = ");
builder.Append (columns.Count);
builder.AppendLine ();
builder.Append ("constraints = ");
builder.Append (constraints.Count);
builder.AppendLine ();
try {
foreach (ColumnSchema col in columns) {
int dummy = col.Constraints.Count; //get column constraints
builder.Append ("CONSTRAINTS ");
builder.Append (col.Name);
builder.Append (" ");
builder.Append (dummy);
builder.AppendLine ();
}
LoggingService.LogDebug (builder.ToString ());
} catch (Exception ee) {
LoggingService.LogDebug (builder.ToString ());
LoggingService.LogError (ee.ToString ());
}
if (action == SchemaActions.Alter) //make a duplicate if we are going to alter the table
this.table = originalTable.Clone () as TableSchema;
DispatchService.GuiDispatch (delegate () {
InitializeGui ();
});
}
public override TriggerSchemaCollection GetTableTriggers (TableSchema table)
{
if (table == null)
throw new ArgumentNullException ("table");
TriggerSchemaCollection triggers = new TriggerSchemaCollection ();
using (IPooledDbConnection conn = connectionPool.Request ()) {
using (IDbCommand command = conn.CreateCommand (string.Format (
@"SELECT * FROM
information_schema.triggers
WHERE event_object_table = '{0}' order by trigger_name",
table.Name))) {
try {
using (IDataReader r = command.ExecuteReader ()) {
while (r.Read ()) {
TriggerSchema trigger = new TriggerSchema (this);
trigger.Name = r.GetString (r.GetOrdinal ("trigger_name"));
trigger.Source = r.GetString (r.GetOrdinal ("action_statement"));
trigger.TriggerType = (TriggerType)Enum.Parse (typeof(TriggerType),
r.GetString (r.GetOrdinal ("condition_timing")));
trigger.TriggerEvent = (TriggerEvent)Enum.Parse (typeof(TriggerEvent),
r.GetString (r.GetOrdinal ("event_manipulation")));
trigger.TriggerFireType = TriggerFireType.ForEachRow;
triggers.Add (trigger);
}
}
} catch (NpgsqlException e) {
QueryService.RaiseException (e);
} finally {
conn.Release ();
}
}
}
return triggers;
public override TriggerSchemaCollection GetTableTriggers (TableSchema table)
{
if (table == null)
throw new ArgumentNullException ("table");
TriggerSchemaCollection triggers = new TriggerSchemaCollection ();
using (IPooledDbConnection conn = connectionPool.Request ()) {
string sql = string.Format(@"SELECT
Tables.Name TableName,
Triggers.name TriggerName,
Triggers.crdate TriggerCreatedDate,
Comments.Text TriggerText
FROM sysobjects Triggers
INNER JOIN sysobjects Tables On
Triggers.parent_obj = Tables.id
INNER JOIN syscomments Comments On
Triggers.id = Comments.id
WHERE
Triggers.xtype = 'TR'
AND Tables.xtype = 'U'
AND Tables.Name = '{0}'
ORDER BY
Tables.Name,
Triggers.name", table.Name);
using (IDbCommand command = conn.CreateCommand (sql)) {
using (IDataReader r = command.ExecuteReader ()) {
while (r.Read ()) {
System.Text.RegularExpressions.Regex parseRegEx = new System.Text.RegularExpressions.Regex
(string.Concat (
@"((CREATE\s*(Temp|Temporary)?\s*TRIGGER){1}\s?(\w+)\s?(IF NOT",
@" EXISTS)?\s?(BEFORE|AFTER|INSTEAD OF){1}\s?(\w+)\s*ON(\s+\w*",
@")\s*(FOR EACH ROW){1}\s*(BEGIN){1})\s+(\w|\W)*(END)"));
TriggerSchema trigger = new TriggerSchema (this);
trigger.TableName = table.Name;
trigger.Name = r.GetString (r.GetOrdinal ("TriggerName"));
sql = r.GetString (r.GetOrdinal ("TriggerText"));
System.Text.RegularExpressions.MatchCollection matchs = parseRegEx.Matches (sql);
if (matchs.Count > 0) {
trigger.TriggerFireType = TriggerFireType.ForEachRow;
switch (matchs[0].Groups[7].Value.ToLower ()) {
case "insert":
trigger.TriggerEvent = TriggerEvent.Insert;
break;
case "update":
trigger.TriggerEvent = TriggerEvent.Update;
break;
case "delete":
trigger.TriggerEvent = TriggerEvent.Delete;
break;
default:
throw new NotImplementedException ();
}
switch (matchs[0].Groups[7].Value.ToLower ()) {
case "before":
trigger.TriggerType = TriggerType.Before;
break;
case "after":
trigger.TriggerType = TriggerType.After;
break;
default:
throw new NotImplementedException ();
}
StringBuilder sbSource = new StringBuilder ();
foreach (System.Text.RegularExpressions.Capture c in matchs[0].Groups[11].Captures)
sbSource.Append (c.Value);
trigger.Source = sbSource.ToString ();
}
triggers.Add (trigger);
}
}
}
conn.Release ();
}
return triggers;
private void InitializeThreaded (object state)
{
tables = schemaProvider.GetTables ();
dataTypes = schemaProvider.GetDataTypes ();
columns = originalTable.Columns;
constraints = originalTable.Constraints;
triggers = originalTable.Triggers;
//TODO: indices
indexes = new IndexSchemaCollection ();
Runtime.LoggingService.Error ("TABLE " + originalTable.Name);
Runtime.LoggingService.Error (" columns = " + columns.Count);
Runtime.LoggingService.Error (" constraints = " + constraints.Count);
try {
foreach (ColumnSchema col in columns) {
int dummy = col.Constraints.Count; //get column constraints
Runtime.LoggingService.Error ("CONSTRAINTS " + col.Name + " " + dummy);
}
} catch (Exception ee) {
Runtime.LoggingService.Error (ee);
Runtime.LoggingService.Error (ee.StackTrace);
}
if (action == SchemaActions.Alter) //make a duplicate if we are going to alter the table
this.table = originalTable.Clone () as TableSchema;
DispatchService.GuiDispatch (delegate () {
InitializeGui ();
});
}
public void Initialize (TableSchema table, TriggerSchemaCollection triggers)
{
if (table == null)
throw new ArgumentNullException ("table");
if (triggers == null)
throw new ArgumentNullException ("triggers");
this.table = table;
this.triggers = triggers;
if (action == SchemaActions.Alter)
foreach (TriggerSchema trigger in triggers)
AddTrigger (trigger);
}
public virtual TriggerSchemaCollection GetTableTriggers (TableSchema table)
{
TriggerSchemaCollection collection = new TriggerSchemaCollection ();
IPooledDbConnection conn = connectionPool.Request ();
try {
//restrictions: database, schema, name, EventObjectTable
DataTable dt = conn.GetSchema (triggersCollectionString, null, table.SchemaName, null, table.Name);
for (int r = 0; r < dt.Rows.Count; r++) {
DataRow row = dt.Rows[r];
collection.Add (GetTableTrigger (row, table));
}
} catch (Exception e) {
QueryService.RaiseException (e);
}
conn.Release ();
return collection;
}
public override TriggerSchemaCollection GetTableTriggers(TableSchema table)
{
if (table == null)
{
throw new ArgumentNullException("table");
}
TriggerSchemaCollection triggers = new TriggerSchemaCollection();
IPooledDbConnection conn = connectionPool.Request();
IDbCommand command = conn.CreateCommand(string.Format(@"SELECT * FROM sqlite_master
WHERE type = 'trigger' and tbl_name = '{0}'" ,
table.Name));
try {
// Parse Trigger Sql - Needed for alter a table
System.Text.RegularExpressions.Regex parseRegEx = new System.Text.RegularExpressions.Regex
(string.Concat(
@"((CREATE\s*(Temp|Temporary)?\s*TRIGGER){1}\s?(\w+)\s?(IF NOT",
@" EXISTS)?\s?(BEFORE|AFTER|INSTEAD OF){1}\s?(\w+)\s*ON(\s+\w*",
@")\s*(FOR EACH ROW){1}\s*(BEGIN){1})\s+(\w|\W)*(END)"));
using (IDataReader r = command.ExecuteReader()) {
while (r.Read())
{
string sql = r.GetString(r.GetOrdinal("sql"));
System.Text.RegularExpressions.MatchCollection matchs = parseRegEx.Matches(sql);
TriggerSchema trigger = new TriggerSchema(this);
trigger.Name = r.GetString(r.GetOrdinal("name"));
trigger.TableName = r.GetString(r.GetOrdinal("tbl_name"));
trigger.Definition = sql;
if (matchs.Count > 0)
{
trigger.TriggerFireType = TriggerFireType.ForEachRow;
switch (matchs[0].Groups[7].Value.ToLower())
{
case "insert":
trigger.TriggerEvent = TriggerEvent.Insert;
break;
case "update":
trigger.TriggerEvent = TriggerEvent.Update;
break;
case "delete":
trigger.TriggerEvent = TriggerEvent.Delete;
break;
default:
throw new NotImplementedException();
}
switch (matchs[0].Groups[7].Value.ToLower())
{
case "before":
trigger.TriggerType = TriggerType.Before;
break;
case "after":
trigger.TriggerType = TriggerType.After;
break;
default:
throw new NotImplementedException();
}
StringBuilder sbSource = new StringBuilder();
foreach (System.Text.RegularExpressions.Capture c in matchs[0].Groups[11].Captures)
{
sbSource.Append(c.Value);
}
trigger.Source = sbSource.ToString();
}
triggers.Add(trigger);
}
}
} catch (SqliteException e) {
QueryService.RaiseException(e);
}
return(triggers);
}
/// <summary>
/// Refresh the information associated with this table.
/// </summary>
public override void Refresh()
{
// TODO: Update Name, etc.
columns = null;
constraints = null;
triggers = null;
definition = null;
}
public override TriggerSchemaCollection GetTableTriggers (TableSchema table)
{
if (table == null)
throw new ArgumentNullException ("table");
TriggerSchemaCollection triggers = new TriggerSchemaCollection ();
IPooledDbConnection conn = connectionPool.Request ();
IDbCommand command = conn.CreateCommand (string.Format (@"SELECT * FROM sqlite_master
WHERE type = 'trigger' and tbl_name = '{0}'",
table.Name));
try {
// Parse Trigger Sql - Needed for alter a table
System.Text.RegularExpressions.Regex parseRegEx = new System.Text.RegularExpressions.Regex
(string.Concat (
@"((CREATE\s*(Temp|Temporary)?\s*TRIGGER){1}\s?(\w+)\s?(IF NOT",
@" EXISTS)?\s?(BEFORE|AFTER|INSTEAD OF){1}\s?(\w+)\s*ON(\s+\w*",
@")\s*(FOR EACH ROW){1}\s*(BEGIN){1})\s+(\w|\W)*(END)"));
using (IDataReader r = command.ExecuteReader ()) {
while (r.Read ()) {
string sql = r.GetString (r.GetOrdinal ("sql"));
System.Text.RegularExpressions.MatchCollection matchs = parseRegEx.Matches (sql);
TriggerSchema trigger = new TriggerSchema (this);
trigger.Name = r.GetString (r.GetOrdinal ("name"));
trigger.TableName = r.GetString (r.GetOrdinal ("tbl_name"));
trigger.Definition = sql;
if (matchs.Count > 0) {
trigger.TriggerFireType = TriggerFireType.ForEachRow;
switch (matchs[0].Groups[7].Value.ToLower ()) {
case "insert":
trigger.TriggerEvent = TriggerEvent.Insert;
break;
case "update":
trigger.TriggerEvent = TriggerEvent.Update;
break;
case "delete":
trigger.TriggerEvent = TriggerEvent.Delete;
break;
default:
throw new NotImplementedException ();
}
switch (matchs[0].Groups[7].Value.ToLower ()) {
case "before":
trigger.TriggerType = TriggerType.Before;
break;
case "after":
trigger.TriggerType = TriggerType.After;
break;
default:
throw new NotImplementedException ();
}
StringBuilder sbSource = new StringBuilder ();
foreach (System.Text.RegularExpressions.Capture c in matchs[0].Groups[11].Captures)
sbSource.Append (c.Value);
trigger.Source = sbSource.ToString ();
}
triggers.Add (trigger);
}
}
} catch (SqliteException e) {
QueryService.RaiseException (e);
}
return triggers;
}
