protected FbCommand BuildCommand(FbConnection connection, string collectionName, string[] restrictions)
{
string filter = String.Format("CollectionName='{0}'", collectionName);
StringBuilder builder = this.GetCommandText(restrictions);
DataRow[] restriction = connection.GetSchema(DbMetaDataCollectionNames.Restrictions).Select(filter);
FbTransaction transaction = connection.InnerConnection.ActiveTransaction;
FbCommand command = new FbCommand(builder.ToString(), connection, transaction);
if (restrictions != null && restrictions.Length > 0)
{
int index = 0;
for (int i = 0; i < restrictions.Length; i++)
{
string rname = restriction[i]["RestrictionName"].ToString();
if (restrictions[i] != null)
{
// Catalog, Schema and TableType are no real restrictions
if (!rname.EndsWith("Catalog") && !rname.EndsWith("Schema") && rname != "TableType")
{
string pname = String.Format(CultureInfo.CurrentUICulture, "@p{0}", index++);
command.Parameters.Add(pname, FbDbType.VarChar, 255).Value = restrictions[i];
}
}
}
}
return command;
}
override internal void LoadForTable()
{
try
{
FbConnection cn = new FirebirdSql.Data.FirebirdClient.FbConnection(this._dbRoot.ConnectionString);
cn.Open();
DataTable metaData = cn.GetSchema("Columns", new string[] { null, null, this.Table.Name });
DataColumn c;
if (!metaData.Columns.Contains("IS_AUTO_KEY"))
{
c = metaData.Columns.Add("IS_AUTO_KEY", typeof(Boolean)); c.DefaultValue = false;
}
if (!metaData.Columns.Contains("AUTO_KEY_SEED"))
{
c = metaData.Columns.Add("AUTO_KEY_SEED"); c.DefaultValue = 0;
}
if (!metaData.Columns.Contains("AUTO_KEY_INCREMENT"))
{
c = metaData.Columns.Add("AUTO_KEY_INCREMENT"); c.DefaultValue = 0;
}
if (!metaData.Columns.Contains("AUTO_KEY_SEQUENCE"))
{
c = metaData.Columns.Add("AUTO_KEY_SEQUENCE"); c.DefaultValue = string.Empty;
}
PopulateArray(metaData);
LoadExtraData(cn, this.Table.Name, "T");
cn.Close();
}
catch (Exception ex)
{
string m = ex.Message;
}
}
override internal void LoadAll()
{
try
{
using (FbConnection cn = new FirebirdSql.Data.FirebirdClient.FbConnection(this._dbRoot.ConnectionString))
{
cn.Open();
DataTable idxMetaData = cn.GetSchema("Indexes", new string[] { null, null, this.Table.Name });
if (!idxMetaData.Columns.Contains("CARDINALITY"))
{
idxMetaData.Columns.Add("CARDINALITY");
}
if (!idxMetaData.Columns.Contains("COLUMN_NAME"))
{
idxMetaData.Columns.Add("COLUMN_NAME");
}
idxMetaData.Columns["IS_UNIQUE"].ColumnName = "UNIQUE";
idxMetaData.Columns["INDEX_TYPE"].ColumnName = "TYPE";
DataTable metaData = idxMetaData.Clone();
metaData.Clear();
foreach (DataRow row in idxMetaData.Rows)
{
string indexName = (string)row["INDEX_NAME"];
DataTable metaDataColumns = cn.GetSchema("IndexColumns", new string[] { null, null, null, indexName });
metaDataColumns.DefaultView.Sort = "ORDINAL_POSITION ASC";
foreach (DataRowView vrow in metaDataColumns.DefaultView)
{
DataRow newrow = metaData.Rows.Add(row.ItemArray);
newrow["CARDINALITY"] = vrow["ORDINAL_POSITION"];
newrow["COLUMN_NAME"] = vrow["COLUMN_NAME"];
}
}
cn.Close();
PopulateArray(metaData);
}
}
catch (Exception ex)
{
string m = ex.Message;
}
}
private static List<TableSchema> GetTables(FbConnection con)
{
string[] restrict3 = {null, null, null};
string[] restrict4 = {null, null, null, null};
var aStore = new List<TableSchema>();
restrict4[0] = null;
restrict4[1] = null;
restrict4[2] = null;
restrict4[3] = "TABLE";
var dtTables = con.GetSchema("Tables", restrict4);
for (var i = 0; i < dtTables.Rows.Count; i++)
{
var tRow = dtTables.Rows[i];
var eTable = new TableSchema {Name = tRow["TABLE_NAME"].ToString()};
// Columns
restrict3[0] = null;
restrict3[1] = null;
restrict3[2] = eTable.Name;
var dtShema = con.GetSchema("Columns", restrict3);
if (dtShema.Rows.Count > 0)
eTable.Columns = new TableColumnSchema[dtShema.Rows.Count];
for (var j = 0; j < dtShema.Rows.Count; j++)
{
var cRow = dtShema.Rows[j];
var eColumn = new TableColumnSchema
{
Name = cRow["COLUMN_NAME"].ToString(),
Size = Convert.ToInt32(cRow["COLUMN_SIZE"], CultureInfo.InvariantCulture),
Type = TypeFbToDbsm(cRow["COLUMN_DATA_TYPE"].ToString()),
Nullable = Convert.ToBoolean(cRow["IS_NULLABLE"], CultureInfo.InvariantCulture)
};
eColumn.DefaultValue = HelpDbscColumnDefault(con, eColumn.Name, eTable.Name);
eColumn.DefaultValue = string.IsNullOrEmpty(eColumn.DefaultValue) ? null : eColumn.DefaultValue;
eColumn.DecimalPrecision = cRow["NUMERIC_PRECISION"] == DBNull.Value
? 0
: Convert.ToInt32(cRow["NUMERIC_PRECISION"], CultureInfo.InvariantCulture);
eColumn.DecimalScale = Convert.ToInt32(cRow["NUMERIC_SCALE"], CultureInfo.InvariantCulture);
eTable.Columns[j] = eColumn;
}
aStore.Add(eTable);
}
return aStore;
}
override internal void LoadAll()
{
try
{
using (FbConnection cn = new FirebirdSql.Data.FirebirdClient.FbConnection(this._dbRoot.ConnectionString))
{
cn.Open();
DataTable idxMetaData = cn.GetSchema("Indexes", new string[] { null, null, this.Table.Name });
if (!idxMetaData.Columns.Contains("CARDINALITY")) idxMetaData.Columns.Add("CARDINALITY");
if (!idxMetaData.Columns.Contains("COLUMN_NAME")) idxMetaData.Columns.Add("COLUMN_NAME");
idxMetaData.Columns["IS_UNIQUE"].ColumnName = "UNIQUE";
idxMetaData.Columns["INDEX_TYPE"].ColumnName = "TYPE";
DataTable metaData = idxMetaData.Clone();
metaData.Clear();
foreach (DataRow row in idxMetaData.Rows)
{
string indexName = (string)row["INDEX_NAME"];
DataTable metaDataColumns = cn.GetSchema("IndexColumns", new string[] { null, null, null, indexName });
metaDataColumns.DefaultView.Sort = "ORDINAL_POSITION ASC";
foreach (DataRowView vrow in metaDataColumns.DefaultView)
{
DataRow newrow = metaData.Rows.Add(row.ItemArray);
newrow["CARDINALITY"] = vrow["ORDINAL_POSITION"];
newrow["COLUMN_NAME"] = vrow["COLUMN_NAME"];
}
}
cn.Close();
PopulateArray(metaData);
}
}
catch(Exception ex)
{
string m = ex.Message;
}
}
internal override void LoadAll()
{
try
{
var cn = new FbConnection(_dbRoot.ConnectionString);
cn.Open();
DataTable metaData1 = cn.GetSchema("ForeignKeys", new[] {null, null, Table.Name});
DataTable metaData2 = cn.GetSchema("ForeignKeys", new[] {null, null, null, null, null, Table.Name});
cn.Close();
DataRowCollection rows = metaData2.Rows;
int count = rows.Count;
for (int i = 0; i < count; i++)
{
metaData1.ImportRow(rows[i]);
}
PopulateArrayNoHookup(metaData1);
ForeignKey key = null;
string keyName = "";
foreach (DataRow row in metaData1.Rows)
{
keyName = row["FK_NAME"] as string;
key = GetByName(keyName);
key.AddForeignColumn(null, null, (string) row["PK_TABLE_NAME"], (string) row["PK_COLUMN_NAME"], true);
key.AddForeignColumn(null, null, (string) row["FK_TABLE_NAME"], (string) row["FK_COLUMN_NAME"],
false);
}
}
catch (Exception ex)
{
string m = ex.Message;
}
}
override internal void LoadAll()
{
try
{
FbConnection cn = new FirebirdSql.Data.FirebirdClient.FbConnection(this._dbRoot.ConnectionString);
cn.Open();
DataTable metaData = cn.GetSchema("Procedures", new string[] {this.Database.Name});
cn.Close();
PopulateArray(metaData);
}
catch(Exception ex)
{
string m = ex.Message;
}
}
override internal void LoadAll()
{
try
{
FbConnection cn = new FirebirdSql.Data.FirebirdClient.FbConnection(this._dbRoot.ConnectionString);
cn.Open();
DataTable metaData = cn.GetSchema("Procedures", new string[] { this.Database.Name });
cn.Close();
PopulateArray(metaData);
}
catch (Exception ex)
{
string m = ex.Message;
}
}
internal override void LoadAll()
{
try
{
var cn = new FbConnection(_dbRoot.ConnectionString);
cn.Open();
DataTable metaData = cn.GetSchema("Procedures", new[] {Database.Name});
cn.Close();
PopulateArray(metaData);
}
catch (Exception ex)
{
string m = ex.Message;
}
}
internal override void LoadForTable()
{
try
{
var cn = new FbConnection(_dbRoot.ConnectionString);
cn.Open();
DataTable metaData = cn.GetSchema("Columns", new[] {null, null, Table.Name});
PopulateArray(metaData);
LoadExtraData(cn, Table.Name, "T");
cn.Close();
}
catch (Exception ex)
{
string m = ex.Message;
}
}
override internal void LoadAll()
{
try
{
FbConnection cn = new FirebirdSql.Data.FirebirdClient.FbConnection(this._dbRoot.ConnectionString);
cn.Open();
DataTable metaData = cn.GetSchema("Views", new string[] { null, null, null });
cn.Close();
metaData.Columns["VIEW_NAME"].ColumnName = "TABLE_NAME";
PopulateArray(metaData);
}
catch (Exception ex)
{
string m = ex.Message;
}
}
override internal void LoadAll()
{
try
{
string type = this.dbRoot.ShowSystemData ? "SYSTEM TABLE" : "TABLE";
FbConnection cn = new FirebirdSql.Data.FirebirdClient.FbConnection(this._dbRoot.ConnectionString);
cn.Open();
DataTable metaData = cn.GetSchema("Tables", new string[] {null, null, null, type});
cn.Close();
PopulateArray(metaData);
}
catch(Exception ex)
{
string m = ex.Message;
}
}
override internal void LoadForView()
{
try
{
FbConnection cn = new FirebirdSql.Data.FirebirdClient.FbConnection(this._dbRoot.ConnectionString);
cn.Open();
DataTable metaData = cn.GetSchema("Columns", new string[] {null, null, this.View.Name});
PopulateArray(metaData);
LoadExtraData(cn, this.View.Name, "V");
cn.Close();
}
catch(Exception ex)
{
string m = ex.Message;
}
}
internal override void LoadAll()
{
try
{
string type = dbRoot.ShowSystemData ? "SYSTEM TABLE" : "TABLE";
var cn = new FbConnection(_dbRoot.ConnectionString);
cn.Open();
DataTable metaData = cn.GetSchema("Tables", new[] {null, null, null, type});
cn.Close();
PopulateArray(metaData);
}
catch (Exception ex)
{
string m = ex.Message;
}
}
override internal void LoadAll()
{
try
{
string type = this.dbRoot.ShowSystemData ? "SYSTEM TABLE" : "TABLE";
FbConnection cn = new FirebirdSql.Data.FirebirdClient.FbConnection(this._dbRoot.ConnectionString);
cn.Open();
DataTable metaData = cn.GetSchema("Tables", new string[] { null, null, null, type });
cn.Close();
PopulateArray(metaData);
}
catch (Exception ex)
{
string m = ex.Message;
}
}
override internal void LoadForView()
{
try
{
FbConnection cn = new FirebirdSql.Data.FirebirdClient.FbConnection(this._dbRoot.ConnectionString);
cn.Open();
DataTable metaData = cn.GetSchema("Columns", new string[] { null, null, this.View.Name });
PopulateArray(metaData);
LoadExtraData(cn, this.View.Name, "V");
cn.Close();
}
catch (Exception ex)
{
string m = ex.Message;
}
}
override internal void LoadAll()
{
try
{
FbConnection cn = new FirebirdSql.Data.FirebirdClient.FbConnection(this._dbRoot.ConnectionString);
cn.Open();
DataTable metaData = cn.GetSchema("Views", new string[] {null, null, null});
cn.Close();
metaData.Columns["VIEW_NAME"].ColumnName = "TABLE_NAME";
PopulateArray(metaData);
}
catch(Exception ex)
{
string m = ex.Message;
}
}
internal override void LoadAll()
{
try
{
var cn = new FbConnection(_dbRoot.ConnectionString);
cn.Open();
DataTable metaData = cn.GetSchema("Indexes", new[] {null, null, Table.Name});
cn.Close();
metaData.Columns["IS_UNIQUE"].ColumnName = "UNIQUE";
metaData.Columns["INDEX_TYPE"].ColumnName = "TYPE";
metaData.Columns["ORDINAL_POSITION"].ColumnName = "CARDINALITY";
PopulateArray(metaData);
}
catch (Exception ex)
{
string m = ex.Message;
}
}
override internal void LoadAll()
{
try
{
FbConnection cn = new FirebirdSql.Data.FirebirdClient.FbConnection(this._dbRoot.ConnectionString);
cn.Open();
DataTable metaData = cn.GetSchema("Domains", null);
cn.Close();
if (metaData.Columns.Contains("DOMAIN_DATA_TYPE"))
{
metaData.Columns["DOMAIN_DATA_TYPE"].ColumnName = "DATA_TYPE";
}
PopulateArray(metaData);
LoadExtraData(cn);
}
catch (Exception ex)
{
string m = ex.Message;
}
}
override internal void LoadForTable()
{
try
{
FbConnection cn = new FirebirdSql.Data.FirebirdClient.FbConnection(this._dbRoot.ConnectionString);
cn.Open();
DataTable metaData = cn.GetSchema("Columns", new string[] { null, null, this.Table.Name });
DataColumn c;
if (!metaData.Columns.Contains("IS_AUTO_KEY")) { c = metaData.Columns.Add("IS_AUTO_KEY", typeof(Boolean)); c.DefaultValue = false; }
if (!metaData.Columns.Contains("AUTO_KEY_SEED")) { c = metaData.Columns.Add("AUTO_KEY_SEED"); c.DefaultValue = 0; }
if (!metaData.Columns.Contains("AUTO_KEY_INCREMENT")) { c = metaData.Columns.Add("AUTO_KEY_INCREMENT"); c.DefaultValue = 0; }
if (!metaData.Columns.Contains("AUTO_KEY_SEQUENCE")) { c = metaData.Columns.Add("AUTO_KEY_SEQUENCE"); c.DefaultValue = string.Empty; }
PopulateArray(metaData);
LoadExtraData(cn, this.Table.Name, "T");
cn.Close();
}
catch(Exception ex)
{
string m = ex.Message;
}
}
override internal void LoadAll()
{
try
{
FbConnection cn = new FirebirdSql.Data.FirebirdClient.FbConnection(this._dbRoot.ConnectionString);
cn.Open();
DataTable metaData = cn.GetSchema("Domains", null);
cn.Close();
if(metaData.Columns.Contains("DOMAIN_DATA_TYPE"))
{
metaData.Columns["DOMAIN_DATA_TYPE"].ColumnName = "DATA_TYPE";
}
PopulateArray(metaData);
LoadExtraData(cn);
}
catch(Exception ex)
{
string m = ex.Message;
}
}
override internal void LoadAll()
{
try
{
using (FbConnection cn = new FirebirdSql.Data.FirebirdClient.FbConnection(this._dbRoot.ConnectionString))
{
cn.Open();
if (allFkData == null)
{
allFkData = cn.GetSchema("ForeignKeys");
allFkData.Columns.Add("COLUMN_NAME");
allFkData.Columns.Add("REFERENCED_COLUMN_NAME");
allFkData.Columns.Add("ORDINAL_POSITION");
allFkData.Columns.Add("DEFERRABILITY");
mappingHash = new NameValueCollection();
mappingHash["FK_TABLE_CATALOG"] = "TABLE_CATALOG";
mappingHash["FK_TABLE_SCHEMA"] = "TABLE_SCHEMA";
mappingHash["FK_TABLE_NAME"] = "TABLE_NAME";
mappingHash["PK_TABLE_CATALOG"] = "REFERENCED_TABLE_CATALOG";
mappingHash["PK_TABLE_SCHEMA"] = "REFERENCED_TABLE_SCHEMA";
mappingHash["PK_TABLE_NAME"] = "REFERENCED_TABLE_NAME";
mappingHash["ORDINAL"] = "ORDINAL_POSITION";
mappingHash["UPDATE_RULE"] = "UPDATE_RULE";
mappingHash["DELETE_RULE"] = "DELETE_RULE";
mappingHash["PK_NAME"] = "INDEX_NAME";
mappingHash["FK_NAME"] = "CONSTRAINT_NAME";
mappingHash["DEFERRABILITY"] = "DEFERRABILITY";
}
DataTable metaData = allFkData.Clone();
metaData.Clear();
foreach (DataRow row in allFkData.Rows)
{
if ((this.Table.Name == (string)row["TABLE_NAME"]) ||
(this.Table.Name == (string)row["REFERENCED_TABLE_NAME"]))
{
string indexName = (string)row["INDEX_NAME"];
string refTableName = (string)row["REFERENCED_TABLE_NAME"];
string isDef = (string)row["IS_DEFERRABLE"];
string initDef = (string)row["INITIALLY_DEFERRED"];
row["DEFERRABILITY"] = (isDef == "NO" ? 3 : (initDef == "YES" ? 2 : 1));
DataTable metaDataColumns = cn.GetSchema("IndexColumns", new string[] { null, null, null, indexName });
metaDataColumns.DefaultView.Sort = "ORDINAL_POSITION ASC";
DataTable metaDataPKIndex = cn.GetSchema("Indexes", new string[] { null, null, refTableName });
metaDataPKIndex.DefaultView.RowFilter = "IS_PRIMARY = True";
string refPkIndexName = (string)metaDataPKIndex.DefaultView[0]["INDEX_NAME"];
DataTable metaDataColumnsRefPk = cn.GetSchema("IndexColumns", new string[] { null, null, null, refPkIndexName });
metaDataColumnsRefPk.DefaultView.Sort = "ORDINAL_POSITION ASC";
if (metaDataColumnsRefPk.Rows.Count == metaDataColumns.Rows.Count)
{
for (int i = 0; i < metaDataColumnsRefPk.Rows.Count; i++)
{
DataRow newrow = metaData.Rows.Add(row.ItemArray);
newrow["ORDINAL_POSITION"] = metaDataColumnsRefPk.DefaultView[i]["ORDINAL_POSITION"];
newrow["COLUMN_NAME"] = metaDataColumns.DefaultView[i]["COLUMN_NAME"];
newrow["REFERENCED_COLUMN_NAME"] = metaDataColumnsRefPk.DefaultView[i]["COLUMN_NAME"];
}
}
}
}
cn.Close();
PopulateArrayNoHookup(metaData, mappingHash);
ForeignKey key = null;
string keyName = "";
foreach (DataRow row in metaData.Rows)
{
keyName = row["CONSTRAINT_NAME"] as string;
key = this.GetByName(keyName);
key.AddForeignColumn(null, null, (string)row["TABLE_NAME"], (string)row["COLUMN_NAME"], false);
key.AddForeignColumn(null, null, (string)row["REFERENCED_TABLE_NAME"], (string)row["REFERENCED_COLUMN_NAME"], true);
}
}
}
catch (Exception ex)
{
string m = ex.Message;
}
}
private void LoadExtraData(FbConnection cn, string name, string type)
{
try
{
int dialect = 1;
object o = null;
short scale = 0;
try
{
FbConnectionStringBuilder cnString = new FbConnectionStringBuilder(cn.ConnectionString);
dialect = cnString.Dialect;
}
catch {}
// AutoKey Data
Dictionary<string, object[]> autoKeyFields = new Dictionary<string, object[]>();
DataTable triggers = cn.GetSchema("Triggers", new string[] {null, null, name});
foreach (DataRow row in triggers.Rows)
{
int isSystemTrigger = Convert.ToInt32(row["IS_SYSTEM_TRIGGER"]);
int isInactive = Convert.ToInt32(row["IS_INACTIVE"]);
int triggerType = Convert.ToInt32(row["TRIGGER_TYPE"]);
if ((isSystemTrigger == 0) && (isInactive == 0) && (triggerType == 1))
{
string source = row["SOURCE"].ToString();
int end = 0;
do
{
string field = null, generatorName = string.Empty;
int tmp, increment = 1, seed = 0;
end = source.IndexOf("gen_id(", end, StringComparison.CurrentCultureIgnoreCase);
if (end >= 0)
{
string s = source.Substring(0, end);
int start = s.LastIndexOf(".");
if (start >= 0 && start < end)
{
field = s.Substring(start).Trim(' ', '.', '=').ToUpper();
}
int end2 = source.IndexOf(")", end);
string s2 = source.Substring(0, end2);
int start2 = s2.LastIndexOf(",");
if (start2 >= 0 && start2 < end2)
{
if (int.TryParse(s2.Substring(start2 + 1).Trim(' ', ','), out tmp))
increment = tmp;
s2 = s2.Substring(0, start2);
generatorName = s2.Substring(end + 7).Trim();
}
if (field != null)
{
autoKeyFields[field] = new object[] { increment, seed, generatorName };
}
end += 7;
}
} while (end != -1);
}
}
string select = "select r.rdb$field_name, f.rdb$field_scale AS SCALE, f.rdb$computed_source AS IsComputed, f.rdb$field_type as FTYPE, f.rdb$field_sub_type AS SUBTYPE, f.rdb$dimensions AS DIM from rdb$relation_fields r, rdb$types t, rdb$fields f where r.rdb$relation_name='" + name + "' and f.rdb$field_name=r.rdb$field_source and t.rdb$field_name='RDB$FIELD_TYPE' and f.rdb$field_type=t.rdb$type order by r.rdb$field_position;";
// Column Data
FbDataAdapter adapter = new FbDataAdapter(select, cn);
DataTable dataTable = new DataTable();
adapter.Fill(dataTable);
// Dimension Data
string dimSelect = "select r.rdb$field_name AS Name , d.rdb$dimension as DIM, d.rdb$lower_bound as L, d.rdb$upper_bound as U from rdb$fields f, rdb$field_dimensions d, rdb$relation_fields r where r.rdb$relation_name='" + name + "' and f.rdb$field_name = d.rdb$field_name and f.rdb$field_name=r.rdb$field_source order by d.rdb$dimension;";
FbDataAdapter dimAdapter = new FbDataAdapter(dimSelect, cn);
DataTable dimTable = new DataTable();
dimAdapter.Fill(dimTable);
if(this._array.Count > 0)
{
Column col = this._array[0] as Column;
f_TypeName = new DataColumn("TYPE_NAME", typeof(string));
col._row.Table.Columns.Add(f_TypeName);
f_TypeNameComplete = new DataColumn("TYPE_NAME_COMPLETE", typeof(string));
col._row.Table.Columns.Add(f_TypeNameComplete);
this.f_IsComputed = new DataColumn("IS_COMPUTED", typeof(bool));
col._row.Table.Columns.Add(f_IsComputed);
short ftype = 0;
short dim = 0;
DataRowCollection rows = dataTable.Rows;
int count = this._array.Count;
Column c = null;
for( int index = 0; index < count; index++)
{
c = (Column)this[index];
if (autoKeyFields.ContainsKey(c.Name.ToUpper()))
{
c._row["IS_AUTO_KEY"] = true;
c._row["AUTO_KEY_INCREMENT"] = autoKeyFields[c.Name][0];
c._row["AUTO_KEY_SEED"] = autoKeyFields[c.Name][1];
c._row["AUTO_KEY_SEQUENCE"] = autoKeyFields[c.Name][2];
}
if(!c._row.IsNull("DOMAIN_NAME"))
{
// Special Hack, if there is a domain
c._row["TYPE_NAME"] = c._row["DOMAIN_NAME"] as string;
c._row["TYPE_NAME_COMPLETE"] = c._row["DOMAIN_NAME"] as string;
o = rows[index]["IsComputed"];
if(o != DBNull.Value)
{
c._row["IS_COMPUTED"] = true;
}
scale = (short)rows[index]["SCALE"];
if(scale < 0)
{
c._row["NUMERIC_SCALE"] = Math.Abs(scale);
}
continue;
}
try
{
string bigint = c._row["COLUMN_DATA_TYPE"] as string;
if(bigint.ToUpper() == "BIGINT")
{
c._row["TYPE_NAME"] = "BIGINT";
c._row["TYPE_NAME_COMPLETE"] = "BIGINT";
continue;
}
}
catch {}
// public const int blr_text = 14;
// public const int blr_text2 = 15;
// public const int blr_short = 7;
// public const int blr_long = 8;
// public const int blr_quad = 9;
// public const int blr_int64 = 16;
// public const int blr_float = 10;
// public const int blr_double = 27;
// public const int blr_d_float = 11;
// public const int blr_timestamp = 35;
// public const int blr_varying = 37;
// public const int blr_varying2 = 38;
// public const int blr_blob = 261;
// public const int blr_cstring = 40;
// public const int blr_cstring2 = 41;
// public const int blr_blob_id = 45;
// public const int blr_sql_date = 12;
// public const int blr_sql_time = 13;
// Step 1: DataTypeName
ftype = (short)rows[index]["FTYPE"];
switch(ftype)
{
case 7:
c._row["TYPE_NAME"] = "SMALLINT";
break;
case 8:
c._row["TYPE_NAME"] = "INTEGER";
break;
case 9:
c._row["TYPE_NAME"] = "QUAD";
break;
case 10:
c._row["TYPE_NAME"] = "FLOAT";
break;
case 11:
c._row["TYPE_NAME"] = "DOUBLE PRECISION";
break;
case 12:
c._row["TYPE_NAME"] = "DATE";
break;
case 13:
c._row["TYPE_NAME"] = "TIME";
break;
case 14:
c._row["TYPE_NAME"] = "CHAR";
break;
case 16:
c._row["TYPE_NAME"] = "NUMERIC";
break;
case 27:
c._row["TYPE_NAME"] = "DOUBLE PRECISION";
break;
case 35:
if(dialect > 2)
{
c._row["TYPE_NAME"] = "TIMESTAMP";
}
else
{
c._row["TYPE_NAME"] = "DATE";
}
break;
case 37:
c._row["TYPE_NAME"] = "VARCHAR";
break;
case 40:
c._row["TYPE_NAME"] = "CSTRING";
break;
case 261:
short subtype = (short)rows[index]["SUBTYPE"];
switch(subtype)
{
case 0:
c._row["TYPE_NAME"] = "BLOB(BINARY)";
break;
case 1:
c._row["TYPE_NAME"] = "BLOB(TEXT)";
break;
default:
c._row["TYPE_NAME"] = "BLOB(UNKNOWN)";
break;
}
break;
}
scale = (short)rows[index]["SCALE"];
if(scale < 0)
{
c._row["TYPE_NAME"] = "NUMERIC";
c._row["NUMERIC_SCALE"] = Math.Abs(scale);
}
o = rows[index]["IsComputed"];
if(o != DBNull.Value)
{
c._row["IS_COMPUTED"] = true;
}
// Step 2: DataTypeNameComplete
string s = c._row["TYPE_NAME"] as string;
switch(s)
{
case "VARCHAR":
case "CHAR":
c._row["TYPE_NAME_COMPLETE"] = s + "(" + c.CharacterMaxLength + ")";
break;
case "NUMERIC":
switch((int)c._row["COLUMN_SIZE"])
{
case 2:
c._row["TYPE_NAME_COMPLETE"] = s + "(4, " + c.NumericScale.ToString() + ")";
break;
case 4:
c._row["TYPE_NAME_COMPLETE"] = s + "(9, " + c.NumericScale.ToString() + ")";
break;
case 8:
c._row["TYPE_NAME_COMPLETE"] = s + "(15, " + c.NumericScale.ToString() + ")";
break;
default:
c._row["TYPE_NAME_COMPLETE"] = "NUMERIC(18,0)";
break;
}
break;
case "BLOB(TEXT)":
case "BLOB(BINARY)":
c._row["TYPE_NAME_COMPLETE"] = "BLOB";
break;
default:
c._row["TYPE_NAME_COMPLETE"] = s;
break;
}
s = c._row["TYPE_NAME_COMPLETE"] as string;
dim = 0;
o = rows[index]["DIM"];
if(o != DBNull.Value)
{
dim = (short)o;
}
if(dim > 0)
{
dimTable.DefaultView.RowFilter = "Name = '" + c.Name + "'";
dimTable.DefaultView.Sort = "DIM";
string a = "[";
bool bFirst = true;
foreach(DataRowView vrow in dimTable.DefaultView)
{
DataRow row = vrow.Row;
if(!bFirst) a += ",";
a += row["L"].ToString() + ":" + row["U"].ToString();
bFirst = false;
}
a += "]";
c._row["TYPE_NAME_COMPLETE"] = s + a;
c._row["TYPE_NAME"] = c._row["TYPE_NAME"] + ":A";
}
}
}
}
catch(Exception ex)
{
string e = ex.Message;
}
}
private static List<DBGenerator> GetGenerators(FbConnection con)
{
using (var cmd = con.CreateCommand())
{
var generators = new List<DBGenerator>();
string[] restrict4 = {null, null, null, null};
restrict4[0] = null;
restrict4[1] = null;
restrict4[2] = null;
restrict4[3] = null;
var dtGenerators = con.GetSchema("Generators", restrict4);
for (var i = 0; i < dtGenerators.Rows.Count; i++)
{
var trRow = dtGenerators.Rows[i];
if (Convert.ToBoolean(trRow["IS_SYSTEM_GENERATOR"], CultureInfo.InvariantCulture))
continue;
var eGenerator = new DBGenerator
{
Name = trRow["GENERATOR_NAME"].ToString()
};
cmd.CommandText = $"SELECT gen_id(\"{eGenerator.Name}\", 0) FROM rdb$database";
eGenerator.StartValue = Convert.ToInt32(cmd.ExecuteScalar(), CultureInfo.InvariantCulture);
generators.Add(eGenerator);
}
return generators;
}
}
public static bool DatabaseHelperTableExists(string tableName)
{
FbConnection connection = new FbConnection(GetConnectionString());
string[] restrictions = new string[4];
restrictions[2] = tableName;
connection.Open();
DataTable table = connection.GetSchema("Tables", restrictions);
connection.Close();
if (table != null)
{
return (table.Rows.Count > 0);
}
return false;
}
public override void RecreateDataBase()
{
// ConnectionString Builder
FbConnectionStringBuilder csb = new FbConnectionStringBuilder();
csb.DataSource = "localhost";
csb.Dialect = 3;
csb.Charset = "UTF8";
csb.Pooling = false;
csb.UserID = "SYSDBA"; // default user
csb.Password = "******"; // default password
string serverConnectionString = csb.ToString();
csb.Database = csb.Database = FQDBFile;
string databaseConnectionString = csb.ToString();
Console.WriteLine("-------------------------");
Console.WriteLine("Using Firebird Database ");
Console.WriteLine("-------------------------");
base.RecreateDataBase();
// Create simple user
FbSecurity security = new FbSecurity();
security.ConnectionString = serverConnectionString;
var userData = security.DisplayUser(FbUserName);
if (userData == null)
{
userData = new FbUserData();
userData.UserName = FbUserName;
userData.UserPassword = FbUserPass;
security.AddUser(userData);
}
// Try to shutdown & delete database
if (File.Exists(FQDBFile))
{
FbConfiguration configuration = new FbConfiguration();
configuration.ConnectionString = databaseConnectionString;
try
{
configuration.DatabaseShutdown(FbShutdownMode.Forced, 0);
Thread.Sleep(1000);
}
finally
{
File.Delete(FQDBFile);
}
}
// Create the new DB
FbConnection.CreateDatabase(databaseConnectionString, 4096, true, true);
if (!File.Exists(FQDBFile)) throw new Exception("Database failed to create");
// Create the Schema
string script = @"
CREATE TABLE Users(
UserId integer PRIMARY KEY NOT NULL,
Name varchar(200),
Age integer,
DateOfBirth timestamp,
Savings decimal(10,5),
Is_Male smallint,
UniqueId char(38),
TimeSpan time,
TestEnum varchar(10),
HouseId integer,
SupervisorId integer
);
CREATE TABLE ExtraUserInfos(
ExtraUserInfoId integer PRIMARY KEY NOT NULL,
UserId integer NOT NULL,
Email varchar(200),
Children integer
);
CREATE TABLE Houses(
HouseId integer PRIMARY KEY NOT NULL,
Address varchar(200)
);
CREATE TABLE CompositeObjects(
Key1ID integer PRIMARY KEY NOT NULL,
Key2ID integer NOT NULL,
Key3ID integer NOT NULL,
TextData varchar(512),
DateEntered timestamp NOT NULL,
DateUpdated timestamp
);
CREATE GENERATOR USERS_USERID_GEN;
CREATE GENERATOR EXTRAUSERINFOS_ID_GEN;
CREATE GENERATOR HOUSES_HOUSEID_GEN;
SET TERM ^ ;
CREATE TRIGGER BI_USERS_USERID FOR USERS
ACTIVE BEFORE INSERT
POSITION 0
AS
BEGIN
IF (NEW.USERID IS NULL) THEN
NEW.USERID = GEN_ID(USERS_USERID_GEN, 1);
END^
CREATE TRIGGER BI_EXTRAUSERINFOS_ID1 FOR EXTRAUSERINFOS
ACTIVE BEFORE INSERT
POSITION 0
AS
BEGIN
IF (NEW.EXTRAUSERINFOID IS NULL) THEN
NEW.EXTRAUSERINFOID = GEN_ID(EXTRAUSERINFOS_ID_GEN, 1);
END^
CREATE TRIGGER BI_HOUSES_HOUSEID FOR HOUSES
ACTIVE BEFORE INSERT
POSITION 0
AS
BEGIN
IF (NEW.HOUSEID IS NULL) THEN
NEW.HOUSEID = GEN_ID(HOUSES_HOUSEID_GEN, 1);
END^
SET TERM ; ^
CREATE ROLE %role%;
GRANT SELECT, UPDATE, INSERT, DELETE ON Users TO ROLE %role%;
GRANT SELECT, UPDATE, INSERT, DELETE ON ExtraUserInfos TO ROLE %role%;
GRANT SELECT, UPDATE, INSERT, DELETE ON Houses TO ROLE %role%;
GRANT SELECT, UPDATE, INSERT, DELETE ON CompositeObjects TO ROLE %role%;
GRANT %role% TO %user%;
".Replace("%role%", FbRole).Replace("%user%", FbUserName);
/*
* Using new connection so that when a transaction is bound to Connection if it rolls back
* it doesn't blow away the tables
*/
using (var conn = new FbConnection(databaseConnectionString))
{
FbScript fbScript = new FbScript(script);
fbScript.Parse();
FbBatchExecution fbBatch = new FbBatchExecution(conn, fbScript);
fbBatch.Execute(true);
conn.Open();
Console.WriteLine("Tables (CreateDB): " + Environment.NewLine);
var dt = conn.GetSchema("Tables", new[] {null, null, null, "TABLE"});
foreach (DataRow row in dt.Rows)
{
Console.WriteLine(row[2]);
}
conn.Close();
}
}
override internal void LoadAll()
{
try
{
using (FbConnection cn = new FirebirdSql.Data.FirebirdClient.FbConnection(this._dbRoot.ConnectionString))
{
cn.Open();
if (allFkData == null)
{
allFkData = cn.GetSchema("ForeignKeys");
allFkData.Columns.Add("COLUMN_NAME");
allFkData.Columns.Add("REFERENCED_COLUMN_NAME");
allFkData.Columns.Add("ORDINAL_POSITION");
allFkData.Columns.Add("DEFERRABILITY");
mappingHash = new NameValueCollection();
mappingHash["FK_TABLE_CATALOG"] = "TABLE_CATALOG";
mappingHash["FK_TABLE_SCHEMA"] = "TABLE_SCHEMA";
mappingHash["FK_TABLE_NAME"] = "TABLE_NAME";
mappingHash["PK_TABLE_CATALOG"] = "REFERENCED_TABLE_CATALOG";
mappingHash["PK_TABLE_SCHEMA"] = "REFERENCED_TABLE_SCHEMA";
mappingHash["PK_TABLE_NAME"] = "REFERENCED_TABLE_NAME";
mappingHash["ORDINAL"] = "ORDINAL_POSITION";
mappingHash["UPDATE_RULE"] = "UPDATE_RULE";
mappingHash["DELETE_RULE"] = "DELETE_RULE";
mappingHash["PK_NAME"] = "INDEX_NAME";
mappingHash["FK_NAME"] = "CONSTRAINT_NAME";
mappingHash["DEFERRABILITY"] = "DEFERRABILITY";
}
DataTable metaData = allFkData.Clone();
metaData.Clear();
foreach (DataRow row in allFkData.Rows)
{
if ((this.Table.Name == (string)row["TABLE_NAME"]) ||
(this.Table.Name == (string)row["REFERENCED_TABLE_NAME"]))
{
string indexName = (string)row["INDEX_NAME"];
string refTableName = (string)row["REFERENCED_TABLE_NAME"];
string isDef = (string)row["IS_DEFERRABLE"];
string initDef = (string)row["INITIALLY_DEFERRED"];
row["DEFERRABILITY"] = (isDef == "NO" ? 3 : (initDef == "YES" ? 2 : 1));
DataTable metaDataColumns = cn.GetSchema("IndexColumns", new string[] { null, null, null, indexName });
metaDataColumns.DefaultView.Sort = "ORDINAL_POSITION ASC";
DataTable metaDataPKIndex = cn.GetSchema("Indexes", new string[] { null, null, refTableName });
metaDataPKIndex.DefaultView.RowFilter = "IS_PRIMARY = True";
string refPkIndexName = (string)metaDataPKIndex.DefaultView[0]["INDEX_NAME"];
DataTable metaDataColumnsRefPk = cn.GetSchema("IndexColumns", new string[] { null, null, null, refPkIndexName });
metaDataColumnsRefPk.DefaultView.Sort = "ORDINAL_POSITION ASC";
if (metaDataColumnsRefPk.Rows.Count == metaDataColumns.Rows.Count)
{
for (int i = 0; i < metaDataColumnsRefPk.Rows.Count; i++)
{
DataRow newrow = metaData.Rows.Add(row.ItemArray);
newrow["ORDINAL_POSITION"] = metaDataColumnsRefPk.DefaultView[i]["ORDINAL_POSITION"];
newrow["COLUMN_NAME"] = metaDataColumns.DefaultView[i]["COLUMN_NAME"];
newrow["REFERENCED_COLUMN_NAME"] = metaDataColumnsRefPk.DefaultView[i]["COLUMN_NAME"];
}
}
}
}
cn.Close();
PopulateArrayNoHookup(metaData, mappingHash);
ForeignKey key = null;
string keyName = "";
foreach (DataRow row in metaData.Rows)
{
keyName = row["CONSTRAINT_NAME"] as string;
key = this.GetByName(keyName);
key.AddForeignColumn(null, null, (string)row["TABLE_NAME"], (string)row["COLUMN_NAME"], false);
key.AddForeignColumn(null, null, (string)row["REFERENCED_TABLE_NAME"], (string)row["REFERENCED_COLUMN_NAME"], true);
}
}
}
catch(Exception ex)
{
string m = ex.Message;
}
}
