#pragma warning restore 649
public override void CreateDatabase(string constr)
{
var csb = new FbConnectionStringBuilder(constr) { Pooling = false };
FbConnection.CreateDatabase(csb.ConnectionString, 16384, false, true);
using (var con = new FbConnection(csb.ConnectionString))
using (var cmd = con.CreateCommand())
{
con.Open();
#region bug drug block
//cmd.CommandText = @"CREATE TABLE crdb (tid INTEGER, name CHAR(120));";
//cmd.ExecuteScalar();
//cmd.CommandText = @"DROP TABLE crdb;";
//cmd.ExecuteScalar();
#endregion
#region init actions: register udf functions
cmd.CommandText =
@"
DECLARE EXTERNAL FUNCTION strlen
CSTRING(32767)
RETURNS INTEGER BY VALUE
ENTRY_POINT 'IB_UDF_strlen' MODULE_NAME 'ib_udf';";
cmd.ExecuteScalar();
#endregion
}
}
private List<object[]> QueryFb(string connectionstring, string query)
{
try
{
using (FbConnection dbConn = new FbConnection(connectionstring))
{
Console.WriteLine("Consultando Control Escolar");
dbConn.Open();
using (var command = dbConn.CreateCommand())
{
command.CommandText = query;
using (var reader = command.ExecuteReader())
{
var rows = new List<object[]>();
while (reader.Read())
{
var columns = new object[reader.FieldCount];
reader.GetValues(columns);
rows.Add(columns);
}
return rows;
}
}
}
}catch(System.Exception oe)
{
throw new Exception(oe.Message);
}
}
private FbOptions GetSettings(string connectionString)
{
if (string.IsNullOrWhiteSpace(connectionString))
{
connectionString = Settings.Connection.ConnectionString;
}
if (ServerVersion != null)
{
return(this);
}
try
{
using (var connection = new Firebird.FbConnection(connectionString))
{
connection.Open();
ServerVersion = Data.FbServerProperties.ParseServerVersion(connection.ServerVersion);
using (var cmd = connection.CreateCommand())
{
cmd.CommandText = "SELECT MON$SQL_DIALECT FROM MON$DATABASE";
IsLegacyDialect = Convert.ToInt32(cmd.ExecuteScalar()) == 1;
}
connection.Close();
}
}
catch (Exception)
{
throw;
}
return(this);
}
//1 - exclusivo 0 - compartilhado
public static void VerificaProprietario(string nomeEntidade, FbConnection conn, ref bool empresa, ref bool filial)
{
ConfigurarDicionario();
if(conn.State == ConnectionState.Closed)
{
conn.Open();
}
using(FbCommand comando = conn.CreateCommand())
{
string nomeTabela = dicionario[nomeEntidade];
comando.CommandText = "SELECT ACESSOEMPRESA, ACESSOFILIAL FROM SYS_TABLES WHERE ENTIDADE = @ENTIDADE";
comando.Parameters.AddWithValue("@ENTIDADE", nomeTabela);
using (FbDataReader leitor = comando.ExecuteReader())
{
if (leitor.HasRows)
{
int indexEmpresa = leitor.GetOrdinal("ACESSOEMPRESA");
int indexFilial = leitor.GetOrdinal("ACESSOFILIAL");
while (leitor.Read())
{
empresa = Convert.ToBoolean(leitor.GetInt32(indexEmpresa));
filial = Convert.ToBoolean(leitor.GetInt32(indexFilial));
}
}
}
}
}
private string GetJson(string CommandText, string ParentNodeName, params FbParameter[] pars)
{
FbConnectionStringBuilder connString = TeeboaConnection.ConnectionString;
using (FbConnection conn = new FbConnection(connString.ToString()))
{
using (FbCommand cmd = conn.CreateCommand())
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = CommandText;
for (int i = 0; i < pars.Length; i++)
{
cmd.Parameters.Add(pars[i]);
}
try
{
conn.Open();
using (FbDataReader dr = cmd.ExecuteReader())
{
return ParentNodeName == string.Empty ? stringify(dr) : stringify(dr, ParentNodeName);
}
}
catch (Exception ex)
{
return string.Format("Message: {1}{0}Method: {2}{0}", "<br />", ex.Message, ex.TargetSite);
}
}
}
}
private void CreateTestTablesFb2(string connectionString)
{
using (var con = new FbConnection(connectionString))
using (var command = con.CreateCommand())
{
var sb = new StringBuilder();
sb.Append("recreate table test(");
sb.Append("int_test integer default 0 not null primary key,");
sb.Append("bigint_test bigint,");
sb.Append("blob_test blob,");
sb.Append("char_test char(20),");
sb.Append("date_test date,");
sb.Append("decimal_test decimal,");
sb.Append("double_test double precision,");
sb.Append("float_test float,");
sb.Append("numeric_test numeric,");
sb.Append("smallint_test smallint,");
sb.Append("time_test time,");
sb.Append("timestamp_test timestamp,");
sb.Append("varchar_test varchar(100)");
sb.Append(")");
command.CommandText = sb.ToString();
con.Open();
command.ExecuteNonQuery();
}
}
public IEnumerable<ColumInfo> GetColums(FbConnection con)
{
using (var command = con.CreateCommand())
{
command.CommandText =
$"select trim(rf.rdb$field_name) Name, f.rdb$field_type Type, f.rdb$field_sub_type SubType , f.rdb$character_length CharSize, trim(rf.rdb$field_source) FieldSource, rf.rdb$null_flag NullFlag, f.rdb$field_precision FieldPrecision, f.rdb$field_scale FieldScale, f.rdb$field_length FieldLength " +
"from rdb$relation_fields rf " +
"join rdb$relations r on rf.rdb$relation_name = r.rdb$relation_name " +
"and r.rdb$view_blr is not null " +
"and r.rdb$relation_type = 1 and r.rdb$system_flag = 0 " +
"join rdb$fields f on f.rdb$field_name = rf.rdb$field_source " +
$"where rf.rdb$relation_name = '{this.ViewName}' " +
"order by rf.rdb$field_position; ";
var reader = command.ExecuteReader();
while (reader.Read())
{
var size = (reader["CharSize"] == DBNull.Value) ? null : (short?)reader["CharSize"];
var subType = (reader["SubType"] == DBNull.Value) ? null : (short?)reader["SubType"];
var nullFlag = reader["NullFlag"] == DBNull.Value;
var precision = (reader["FieldPrecision"] == DBNull.Value) ? null : (short?)reader["FieldPrecision"];
var scale = (reader["FieldScale"] == DBNull.Value) ? null : (short?)reader["FieldScale"];
var fieldLength = (reader["FieldLength"] == DBNull.Value) ? null : (short?)reader["FieldLength"];
var type = new FieldType((short)reader["Type"], subType, size, precision, scale, fieldLength);
yield return new ColumInfo((string)reader["Name"], type, null, (string)reader["FieldSource"], nullFlag, true, "");
}
}
}
public IEnumerable<DomainInfo> GetDomain(FbConnection con)
{
using (var command = con.CreateCommand())
{
command.CommandText =
$"select distinct f.rdb$field_type Type, f.rdb$field_sub_type SubType , f.rdb$character_length CharSize, trim(f.rdb$field_name) FieldName, f.rdb$field_precision FieldPrecision, f.rdb$field_scale FieldScale, f.rdb$field_length FieldLength, coalesce(f.rdb$validation_source, '') ValidationSource, coalesce(f.rdb$default_source, '') DefaultSource, f.rdb$null_flag NullFlag " +
"from rdb$fields f " +
$"where f.rdb$FIELD_NAME not starting with 'RDB$' and f.rdb$FIELD_NAME not starting with 'MON$' and f.rdb$FIELD_NAME not starting with 'SEC$' " +
"order by f.rdb$field_name; ";
var reader = command.ExecuteReader();
while (reader.Read())
{
var name = (string)reader["FieldName"];
var size = (reader["CharSize"] == DBNull.Value) ? null : (short?)reader["CharSize"];
var subType = (reader["SubType"] == DBNull.Value) ? null : (short?)reader["SubType"];
var precision = (reader["FieldPrecision"] == DBNull.Value) ? null : (short?)reader["FieldPrecision"];
var scale = (reader["FieldScale"] == DBNull.Value) ? null : (short?)reader["FieldScale"];
var fieldLength = (reader["FieldLength"] == DBNull.Value) ? null : (short?)reader["FieldLength"];
var type = new FieldType((short)reader["Type"], subType, size, precision, scale, fieldLength);
var validationSource = (string)reader["ValidationSource"];
var defaultSource = (string)reader["DefaultSource"];
var nullFlag = reader["NullFlag"] == DBNull.Value;
yield return new DomainInfo(name, type, validationSource, defaultSource, nullFlag);
}
}
}
private static void Main(string[] args)
{
if (File.Exists("Data/database.fdb"))
{
File.Delete("Data/database.fdb");
}
FbConnection.CreateDatabase(GetConnectionString());
using (var conn = new FbConnection(GetConnectionString()))
{
conn.Open();
if (File.Exists("Data/database.sql"))
{
var file = new FileInfo("Data/database.sql");
string script = file.OpenText().ReadToEnd();
using (FbCommand createTable = conn.CreateCommand())
{
createTable.CommandText = script;
createTable.ExecuteNonQuery();
}
}
}
}
static void Main(string[] args)
{
var builder = new FbConnectionStringBuilder();
builder.DataSource = "localhost";
builder.Database = @"D:\DB\FB3_EXTERNAL_TEST.FDB";
builder.Charset = FbCharset.Utf8.ToString();
builder.UserID = "SYSDBA";
builder.ServerType = FbServerType.Embedded;
builder.ClientLibrary = @"fb\fbclient.dll";
//DB作成
if (!File.Exists(builder.Database))
{
FbConnection.CreateDatabase(builder.ConnectionString);
}
//create procesure
var dllPath = new Uri(new Uri(Assembly.GetEntryAssembly().Location), @"../../Fb/plugins/FbExternalSample.dll").LocalPath;
var createSqls = new FbHelper().GetCreateStatements(dllPath);
using (var con = new FbConnection(builder.ConnectionString))
using (var command = con.CreateCommand())
{
con.Open();
foreach (var sql in createSqls)
{
command.CommandText = sql;
command.ExecuteNonQuery();
}
}
//実行
var sqls = new[] { "SELECT * FROM HellowWorld('Taro')", "SELECT * FROM GetNumbers(5)", "SELECT * FROM GetDemo('やったぜ！')" };
using (var con = new FbConnection(builder.ConnectionString))
using (var command = con.CreateCommand())
{
con.Open();
foreach (var sql in sqls)
{
Console.WriteLine("- SQL -");
Console.WriteLine(sql);
Console.WriteLine("- 実行結果 -");
command.CommandText = sql;
var reader = command.ExecuteReader();
while (reader.Read())
{
var result = "";
for (var i = 0; i < reader.FieldCount; i++)
{
result += $" {reader[i]}";
}
Console.WriteLine(result);
}
Console.WriteLine("------------------------------------");
}
}
Console.Read();
}
public string GetDefaultCharSet(FbConnection con)
{
using (var command = con.CreateCommand())
{
command.CommandText = @"select RDB$CHARACTER_SET_NAME CharSet from RDB$DATABASE";
return command.ExecuteScalar() as string ?? "UTF8";
}
}
public IEnumerable<IndexInfo> GetIndex(FbConnection con)
{
using (var command = con.CreateCommand())
{
command.CommandText =
"select trim(idx.rdb$index_name) Name, trim(seg.rdb$field_name) FiledName, rel.rdb$constraint_type ConstraintType, trim(rdb$foreign_key) ForeignKey, trim(ref.rdb$update_rule) UpdateRule, trim(ref.rdb$delete_rule) DeleteRule " +
"from rdb$indices idx " +
"left outer join rdb$index_segments seg on idx.rdb$index_name = seg.rdb$index_name " +
"left outer join rdb$relation_constraints rel on idx.rdb$index_name = rel.rdb$index_name " +
"left outer join rdb$ref_constraints ref on ref.rdb$constraint_name = rel.rdb$constraint_name " +
$"where idx.rdb$relation_name = '{this.TableName}' and idx.rdb$system_flag = 0 order by seg.rdb$field_position";
var reader = command.ExecuteReader();
var tmpName = "";
IndexInfo tmpInf = null;
while (reader.Read())
{
if (tmpName == (string)reader["Name"])
{
tmpInf.FieldNames.Add((string)reader["FiledName"]);
}
else
{
if (tmpInf != null)
{
tmpName = "";
yield return tmpInf;
}
tmpInf = new IndexInfo();
tmpName = (string)reader["Name"];
tmpInf.Name = tmpName;
var constraintType = reader["ConstraintType"] == DBNull.Value ? "" : (string)(reader["ConstraintType"]);
tmpInf.Kind = GetConstraintType(constraintType);
tmpInf.TableName = this.TableName;
if (tmpInf.Kind == ConstraintsKind.Foreign)
{
tmpInf.ForigenKeyName = (string)reader["ForeignKey"];
tmpInf.UpdateRule = (string)reader["UpdateRule"] == "RESTRICT" ? "" : (string)reader["UpdateRule"];
tmpInf.DeleteRule = (string)reader["DeleteRule"] == "RESTRICT" ? "" : (string)reader["DeleteRule"];
}
tmpInf.FieldNames.Add((string)reader["FiledName"]);
}
}
if (tmpInf != null)
{
yield return tmpInf;
}
}
}
public IEnumerable<Tuple<BlockchainKey, BlockchainMetadata>> ListBlockchains()
{
CheckDatabaseFolder();
foreach (var file in Directory.EnumerateFiles(this.dbFolderPath, "Blockchain_*.fdb"))
{
var results = new List<Tuple<BlockchainKey, BlockchainMetadata>>();
try
{
var connString = @"ServerType=1; DataSource=localhost; Database={0}; Pooling=false; User=SYSDBA; Password=NA;".Format2(file);
using (var conn = new FbConnection(connString))
{
conn.Open();
using (var cmd = conn.CreateCommand())
{
cmd.CommandText = @"
SELECT Guid, RootBlockHash, TotalWork
FROM BlockchainMetadata
WHERE IsComplete = 1";
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
var guid = new Guid(reader.GetCharBytes(0));
var rootBlockHash = reader.GetUInt256(1);
var totalWork = reader.GetBigInteger(2);
results.Add(Tuple.Create(new BlockchainKey(guid, rootBlockHash), new BlockchainMetadata(guid, rootBlockHash, totalWork)));
}
}
}
}
}
catch (Exception e)
{
Debug.WriteLine("Error reading blockchain database: {0}: {1}".Format2(Path.GetFileName(file), e.Message));
}
foreach (var result in results)
yield return result;
}
}
public IEnumerable<ColumInfo> GetColums(FbConnection con)
{
var constraints = GetConstrains(con);
using (var command = con.CreateCommand())
{
command.CommandText =
$"select trim(rf.rdb$field_name) Name, f.rdb$field_type Type, f.rdb$field_sub_type SubType , f.rdb$character_length CharSize, trim(rf.rdb$field_source) FieldSource, rf.rdb$null_flag NullFlag, f.rdb$null_flag fieldNullFlag, f.rdb$field_precision FieldPrecision, f.rdb$field_scale FieldScale, f.rdb$field_length FieldLength, coalesce(rf.rdb$default_source, '') DefaultSource " +
"from rdb$relation_fields rf " +
"join rdb$relations r on rf.rdb$relation_name = r.rdb$relation_name " +
"and r.rdb$view_blr is null " +
$"and r.rdb$relation_type = 0 and r.rdb$system_flag = {SystemFlag} " +
"join rdb$fields f on f.rdb$field_name = rf.rdb$field_source " +
$"where rf.rdb$relation_name = '{this.TableName}' " +
"order by rf.rdb$field_position; ";
var reader = command.ExecuteReader();
while (reader.Read())
{
var name = (string)reader["Name"];
var size = (reader["CharSize"] == DBNull.Value) ? null : (short?)reader["CharSize"];
var subType = (reader["SubType"] == DBNull.Value) ? null : (short?)reader["SubType"];
var nullFlag = reader["NullFlag"] == DBNull.Value;
var fieldNullFlag = reader["FieldNullFlag"] == DBNull.Value;
var precision = (reader["FieldPrecision"] == DBNull.Value) ? null : (short?)reader["FieldPrecision"];
var scale = (reader["FieldScale"] == DBNull.Value) ? null : (short?)reader["FieldScale"];
var fieldLength = (reader["FieldLength"] == DBNull.Value) ? null : (short?)reader["FieldLength"];
var type = new FieldType((short)reader["Type"], subType, size, precision, scale, fieldLength);
var defaultSource = (string)reader["DefaultSource"];
var constraintInfo = new ColumConstraintsInfo();
if (constraints.ContainsKey(name))
{
constraintInfo = constraints[name];
}
yield return new ColumInfo(name, type, constraintInfo, (string)reader["FieldSource"], nullFlag, fieldNullFlag, defaultSource);
}
}
}
protected static bool IsInDatabase(Action<FbCommand> adjustCommand)
{
var result = false;
using (var connection = new FbConnection(IntegrationTestOptions.Firebird.ConnectionString))
{
connection.Open();
using (var tx = connection.BeginTransaction())
{
using (var cmd = connection.CreateCommand())
{
cmd.Transaction = tx;
adjustCommand(cmd);
using (var reader = cmd.ExecuteReader())
{
result = reader.Read();
}
}
tx.Commit();
}
connection.Close();
}
return result;
}
private bool ForeignKeyExists(string connectionString, string withName)
{
using (var connection = new FbConnection(connectionString))
{
connection.Open();
var keyQuery = String.Format(@"
SELECT rc.RDB$CONSTRAINT_NAME AS constraint_name,
i.RDB$RELATION_NAME AS table_name,
s.RDB$FIELD_NAME AS field_name,
i.RDB$DESCRIPTION AS description,
rc.RDB$DEFERRABLE AS is_deferrable,
rc.RDB$INITIALLY_DEFERRED AS is_deferred,
refc.RDB$UPDATE_RULE AS on_update,
refc.RDB$DELETE_RULE AS on_delete,
refc.RDB$MATCH_OPTION AS match_type,
i2.RDB$RELATION_NAME AS references_table,
s2.RDB$FIELD_NAME AS references_field,
(s.RDB$FIELD_POSITION + 1) AS field_position
FROM RDB$INDEX_SEGMENTS s
LEFT JOIN RDB$INDICES i ON i.RDB$INDEX_NAME = s.RDB$INDEX_NAME
LEFT JOIN RDB$RELATION_CONSTRAINTS rc ON rc.RDB$INDEX_NAME = s.RDB$INDEX_NAME
LEFT JOIN RDB$REF_CONSTRAINTS refc ON rc.RDB$CONSTRAINT_NAME = refc.RDB$CONSTRAINT_NAME
LEFT JOIN RDB$RELATION_CONSTRAINTS rc2 ON rc2.RDB$CONSTRAINT_NAME = refc.RDB$CONST_NAME_UQ
LEFT JOIN RDB$INDICES i2 ON i2.RDB$INDEX_NAME = rc2.RDB$INDEX_NAME
LEFT JOIN RDB$INDEX_SEGMENTS s2 ON i2.RDB$INDEX_NAME = s2.RDB$INDEX_NAME
WHERE rc.RDB$CONSTRAINT_TYPE = 'FOREIGN KEY'
ORDER BY s.RDB$FIELD_POSITION", MigrationWhichCreatesTwoRelatedTables.ForeignKeyName);
var cmd = connection.CreateCommand();
cmd.CommandText = keyQuery;
var result = false;
using (var rdr = cmd.ExecuteReader())
{
while (rdr.Read())
{
try
{
var constraintName = rdr["CONSTRAINT_NAME"];
if (constraintName == null) continue;
if (constraintName is DBNull) continue;
if (constraintName.ToString().Trim() == withName)
{
result = true;
break;
}
}
catch { }
}
}
connection.Close();
FbConnection.ClearPool(connection);
return result;
}
}
public override void CreateUninitializedItem(HttpContext context, string id, int timeout)
{
using (FbConnection conn = new FbConnection(this.connectionString))
{
conn.Open();
using (FbCommand cmd = conn.CreateCommand())
{
cmd.CommandText = "INSERT INTO SESSIONS(SESSIONID, APPLICATIONNAME, CREATED, EXPIRES, " +
"LOCKDATE, LOCKID, TIMEOUT, LOCKED, SESSIONITEMS, FLAGS) " +
"VALUES (@SESSIONID, @APPLICATIONNAME, @CREATED, @EXPIRES, @LOCKDATE, @LOCKID, " +
"@TIMEOUT, @LOCKED, @SESSIONITEMS, @FLAGS)";
cmd.Parameters.Add("@SESSIONID", FbDbType.VarChar, 80).Value = id;
cmd.Parameters.Add("@APPLICATIONNAME", FbDbType.VarChar, 100).Value = ApplicationName;
cmd.Parameters.Add("@CREATED", FbDbType.TimeStamp).Value = DateTime.Now;
cmd.Parameters.Add("@EXPIRES", FbDbType.TimeStamp).Value = DateTime.Now.AddMinutes((Double)timeout);
cmd.Parameters.Add("@LOCKDATE", FbDbType.TimeStamp).Value = DateTime.Now;
cmd.Parameters.Add("@LOCKID", FbDbType.Integer).Value = 0;
cmd.Parameters.Add("@TIMEOUT", FbDbType.Integer).Value = timeout;
cmd.Parameters.Add("@LOCKED", FbDbType.SmallInt).Value = false;
cmd.Parameters.Add("@SESSIONITEMS", FbDbType.Text, 0).Value = "";
cmd.Parameters.Add("@FLAGS", FbDbType.Integer).Value = 1;
cmd.ExecuteNonQuery();
}
}
}
public IEnumerable<ViewInfo> GetViews(FbConnection con)
{
using (var command = con.CreateCommand())
{
command.CommandText = @"select trim(rdb$relation_name) AS Name, rdb$view_source Source from rdb$relations where rdb$relation_type = 1 and rdb$system_flag = 0 order by rdb$relation_name asc";
var reader = command.ExecuteReader();
while (reader.Read())
{
yield return new ViewInfo((string)reader["Name"], ((string)reader["Source"]).Trim(' ', '\r', '\n'));
}
}
}
public IEnumerable<TableInfo> GetTables(FbConnection con)
{
using (var command = con.CreateCommand())
{
command.CommandText = @"select trim(rdb$relation_name) AS Name from rdb$relations where rdb$relation_type = 0 and rdb$system_flag = 0 order by rdb$relation_name asc";
var reader = command.ExecuteReader();
while (reader.Read())
{
yield return new TableInfo((string)reader["Name"]);
}
}
}
public IEnumerable<SequenceInfo> GetSequences(FbConnection con)
{
using (var command = con.CreateCommand())
{
command.CommandText =
"execute block " +
"returns(GeneratorName char(31), CurrentValue bigint) " +
"as " +
"begin " +
"for select rdb$generator_name from rdb$generators where rdb$system_flag = 0 into GeneratorName do " +
"begin " +
"execute statement 'select gen_id(' || GeneratorName || ', 0) from rdb$database' into CurrentValue; " +
"suspend; " +
"end " +
"end; ";
var reader = command.ExecuteReader();
while (reader.Read())
{
var name = (string)reader["GeneratorName"];
var value = (long)reader["CurrentValue"];
yield return new SequenceInfo(name, value);
}
}
}
public int GetActiveConnections()
{
var csb = BuildConnectionStringBuilder(_fbServerType);
csb.Pooling = false;
using (var conn = new FbConnection(csb.ToString()))
{
conn.Open();
using (var cmd = conn.CreateCommand())
{
cmd.CommandText = "select count(*) from mon$attachments where mon$attachment_id <> current_connection";
return (int)cmd.ExecuteScalar();
}
}
}
private static string HelpDbscColumnDefault(FbConnection con, string cname, string tname)
{
using (var cmd = con.CreateCommand())
using (var result = new DataTable())
{
result.Locale = CultureInfo.InvariantCulture;
cmd.CommandText =
$@"
SELECT
rfr.rdb$default_source AS DEFAULT_SOURCE
FROM
rdb$relation_fields rfr
WHERE
rfr.rdb$relation_name = '{tname}' AND rfr.rdb$field_name='{cname}';";
using (var adapter = new FbDataAdapter(cmd))
adapter.Fill(result);
if (result.Rows.Count > 1)
throw new DBSchemaException("Ambiguous column");
return result.Rows[0]["DEFAULT_SOURCE"].ToString();
}
}
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 override void ResetItemTimeout(HttpContext context, string id)
{
using (FbConnection conn = new FbConnection(this.connectionString))
{
conn.Open();
using (FbCommand cmd = conn.CreateCommand())
{
cmd.CommandText = "UPDATE SESSIONS SET EXPIRES = @EXPIRES WHERE SESSIONID = @SESSIONID AND " +
"APPLICATIONNAME = @APPLICATIONNAME";
cmd.Parameters.Add("@EXPIRES", FbDbType.TimeStamp).Value = DateTime.Now.AddMinutes(this.config.Timeout.Minutes);
cmd.Parameters.Add("@SESSIONID", FbDbType.VarChar, 80).Value = id;
cmd.Parameters.Add("@APPLICATIONNAME", FbDbType.VarChar, 100).Value = ApplicationName;
cmd.ExecuteNonQuery();
}
}
}
private Dictionary<string, ColumConstraintsInfo> GetConstrains(FbConnection con)
{
var dic = new Dictionary<string, ColumConstraintsInfo>();
using (var command = con.CreateCommand())
{
command.CommandText =
"select trim(seg.rdb$field_name) FieldName, rel.rdb$constraint_type Type, trim(idx.foreign_key_table) ForeignKeyTable " +
"from rdb$relation_constraints rel " +
"left outer join( " +
"select idx.rdb$index_name, idx.rdb$relation_name, idx2.rdb$relation_name foreign_key_table " +
"from rdb$indices idx " +
"left outer join rdb$indices idx2 on idx.rdb$foreign_key = idx2.rdb$index_name) idx " +
"on rel.rdb$index_name = idx.rdb$index_name " +
"left outer join rdb$index_segments seg on idx.rdb$index_name = seg.rdb$index_name " +
$"where rel.rdb$relation_name = '{this.TableName}' and seg.rdb$field_name != '' ";
var reader = command.ExecuteReader();
while (reader.Read())
{
var name = (string)reader["FieldName"];
ColumConstraintsInfo inf;
if (!dic.TryGetValue(name, out inf))
{
inf = new ColumConstraintsInfo();
dic.Add(name, inf);
}
var type = GetConstraintType((string)reader["Type"]);
inf.SetKind(type);
if (type == ConstraintsKind.Foreign)
{
inf.ForeignKeyTableName = (string)reader["ForeignKeyTable"];
}
}
}
return dic;
}
public IEnumerable<TriggerInfo> GetTrigger(FbConnection con)
{
using (var command = con.CreateCommand())
{
command.CommandText = $"select rdb$trigger_name Name, rdb$relation_name TableName, rdb$trigger_source Source from rdb$triggers where rdb$relation_name = '{this.TableName}' and rdb$system_flag = 0";
var reader = command.ExecuteReader();
while (reader.Read())
{
yield return new TriggerInfo((string)reader["Name"], (string)reader["TableName"], (string)reader["Source"]);
}
}
}
private SessionStateStoreData GetSessionStoreItem(bool lockRecord, HttpContext context, string id,
out bool locked, out TimeSpan lockAge, out object lockId, out SessionStateActions actionFlags)
{
locked = false;
lockAge = TimeSpan.Zero;
lockId = null;
actionFlags = 0;
SessionStateStoreData item = null;
using (FbConnection conn = new FbConnection(this.connectionString))
{
DateTime expires;
string serializedItems = "";
bool foundRecord = false;
bool deleteData = false;
int timeout = 0;
conn.Open();
if (lockRecord)
{
using (FbCommand cmd = conn.CreateCommand())
{
cmd.CommandText = "UPDATE SESSIONS SET LOCKED = @LOCKED, LOCKDATE = @LOCKDATE " +
"WHERE SESSIONID = @SESSIONID AND APPLICATIONNAME = @APPLICATIONNAME AND " +
"LOCKED = @LOCKED2 AND EXPIRES > @EXPIRES";
cmd.Parameters.Add("@LOCKED", FbDbType.SmallInt).Value = true;
cmd.Parameters.Add("@LOCKDATE", FbDbType.TimeStamp).Value = DateTime.Now;
cmd.Parameters.Add("@SESSIONID", FbDbType.VarChar, 80).Value = id;
cmd.Parameters.Add("@APPLICATIONNAME", FbDbType.VarChar, 100).Value = ApplicationName;
cmd.Parameters.Add("@LOCKED2", FbDbType.Integer).Value = false;
cmd.Parameters.Add("@EXPIRES", FbDbType.TimeStamp).Value = DateTime.Now;
if (cmd.ExecuteNonQuery() == 0)
{
locked = true;
}
else
{
locked = false;
}
}
}
using (FbCommand cmd = conn.CreateCommand())
{
cmd.CommandText = "SELECT EXPIRES, SESSIONITEMS, LOCKID, LOCKDATE, FLAGS, TIMEOUT " +
"FROM SESSIONS WHERE SESSIONID = @SESSIONID AND APPLICATIONNAME = @APPLICATIONNAME";
cmd.Parameters.Add("@SESSIONID", FbDbType.VarChar, 80).Value = id;
cmd.Parameters.Add("@APPLICATIONNAME", FbDbType.VarChar, 100).Value = ApplicationName;
using (FbDataReader reader = cmd.ExecuteReader(CommandBehavior.SingleRow))
{
while (reader.Read())
{
expires = reader.GetDateTime(0);
if (expires < DateTime.Now)
{
locked = false;
deleteData = true;
}
else
{
foundRecord = true;
}
serializedItems = reader.GetString(1);
lockId = reader.GetInt32(2);
lockAge = DateTime.Now.Subtract(reader.GetDateTime(3));
actionFlags = (SessionStateActions)reader.GetInt32(4);
timeout = reader.GetInt32(5);
}
if (!foundRecord)
{
locked = false;
}
}
}
if (deleteData)
{
using (FbCommand cmd = conn.CreateCommand())
{
cmd.CommandText = "DELETE FROM SESSIONS WHERE SESSIONID = @SESSIONID AND APPLICATIONNAME = @APPLICATIONNAME";
cmd.Parameters.Add("@SESSIONID", FbDbType.VarChar, 80).Value = id;
cmd.Parameters.Add("@APPLICATIONNAME", FbDbType.VarChar, 100).Value = ApplicationName;
cmd.ExecuteNonQuery();
}
}
if (foundRecord && !locked)
{
lockId = (int)lockId + 1;
using (FbCommand cmd = conn.CreateCommand())
{
cmd.CommandText = "UPDATE SESSIONS SET LOCKID = @LOCKID, FLAGS = 0 " +
"WHERE SESSIONID = @SESSIONID AND APPLICATIONNAME = @APPLICATIONNAME";
cmd.Parameters.Add("@LOCKID", FbDbType.Integer).Value = lockId;
cmd.Parameters.Add("@SESSIONID", FbDbType.VarChar, 80).Value = id;
cmd.Parameters.Add("@APPLICATIONNAME", FbDbType.VarChar, 100).Value = ApplicationName;
cmd.ExecuteNonQuery();
}
if (actionFlags == SessionStateActions.InitializeItem)
{
item = CreateNewStoreData(context, this.config.Timeout.Minutes);
}
else
{
item = Deserialize(context, serializedItems, timeout);
}
}
}
return item;
}
private int ActiveConnections()
{
using (FbConnection conn = new FbConnection(this.BuildConnectionString()))
{
conn.Open();
using (FbCommand cmd = conn.CreateCommand())
{
cmd.CommandText = "select count(*) from mon$attachments where mon$attachment_id <> current_connection";
return (int)cmd.ExecuteScalar();
}
}
}
public IEnumerable<ProcedureInfo> GetProcedures(FbConnection con)
{
using (var command = con.CreateCommand())
{
command.CommandText =
$"select rdb$procedure_name Name, rdb$procedure_source Source " +
"from rdb$procedures " +
"where rdb$procedure_source is not null ";
var reader = command.ExecuteReader();
while (reader.Read())
{
var name = (string)reader["Name"];
var source = (string)reader["Source"];
yield return new ProcedureInfo(name, source);
}
}
}
private int LogRowsCount(FbConnection conn)
{
using (FbCommand cmd = conn.CreateCommand())
{
cmd.CommandText = "select count(*) from log where text = 'on connect'";
return (int)cmd.ExecuteScalar();
}
}
public async Task BulkAddSystem(List<EddbSystem> systems)
{
if (systems == null)
{
Logger.Debug("Null system list passed to InjectSystemsToSql!");
return;
}
Logger.Info("Injecting " + systems.Count + " systems to SQL.");
Status = "Bulk inserting...";
FbConnection con2 = new FbConnection(
_fbConStr);
try
{
con2.Open();
}
catch (Exception ex)
{
Show(ex.ToString());
}
try
{
using (FbCommand insertSystem = con2.CreateCommand())
{
insertSystem.CommandText =
"INSERT INTO eddb_systems values (@id, @name, @x, @y, @z, @faction, @population, @government, @allegiance, @state, @security, @primary_economy, @power, @power_state, @needs_permit, @updated_at, @simbad_ref, @lowercase_name)";
insertSystem.Parameters.Clear();
insertSystem.Parameters.Add("@id", FbDbType.Integer);
insertSystem.Parameters.Add("@name", FbDbType.VarChar, 150);
insertSystem.Parameters.Add("@x", FbDbType.Double);
insertSystem.Parameters.Add("@y", FbDbType.Double);
insertSystem.Parameters.Add("@z", FbDbType.Double);
insertSystem.Parameters.Add("@faction", FbDbType.VarChar, 150);
insertSystem.Parameters.Add("@population", FbDbType.BigInt);
insertSystem.Parameters.Add("@government", FbDbType.VarChar, 130);
insertSystem.Parameters.Add("@allegiance", FbDbType.VarChar, 130);
insertSystem.Parameters.Add("@state", FbDbType.VarChar, 130);
insertSystem.Parameters.Add("@security", FbDbType.VarChar, 150);
insertSystem.Parameters.Add("@primary_economy", FbDbType.VarChar, 130);
insertSystem.Parameters.Add("@power", FbDbType.VarChar, 130);
insertSystem.Parameters.Add("@power_state", FbDbType.VarChar, 130);
insertSystem.Parameters.Add("@needs_permit", FbDbType.Integer);
insertSystem.Parameters.Add("@updated_at", FbDbType.BigInt);
insertSystem.Parameters.Add("@simbad_ref", FbDbType.VarChar, 150);
insertSystem.Parameters.Add("@lowercase_name", FbDbType.VarChar, 150);
FbTransaction tx = insertSystem.Connection.BeginTransaction();
insertSystem.Transaction = tx;
insertSystem.Prepare();
int i = 0;
foreach (EddbSystem system in systems)
{
if (_eddbworker != null)
_eddbworker.SystemCounter++;
insertSystem.Parameters["@id"].Value = system.id;
insertSystem.Parameters["@name"].Value = system.name;
insertSystem.Parameters["@x"].Value = system.x;
insertSystem.Parameters["@y"].Value = system.y;
insertSystem.Parameters["@z"].Value = system.z;
insertSystem.Parameters["@faction"].Value = system.faction;
insertSystem.Parameters["@population"].Value = system.population;
insertSystem.Parameters["@government"].Value = system.government;
insertSystem.Parameters["@allegiance"].Value = system.allegiance;
insertSystem.Parameters["@state"].Value = system.state;
insertSystem.Parameters["@security"].Value = system.security;
insertSystem.Parameters["@primary_economy"].Value = system.primary_economy;
insertSystem.Parameters["@power"].Value = system.power;
insertSystem.Parameters["@power_state"].Value = system.power_state;
insertSystem.Parameters["@needs_permit"].Value = system.needs_permit;
insertSystem.Parameters["@updated_at"].Value = system.updated_at;
insertSystem.Parameters["@simbad_ref"].Value = system.simbad_ref;
insertSystem.Parameters["@lowercase_name"].Value = system.name.ToLower();
await insertSystem.ExecuteNonQueryAsync();
i++;
if (i % 10000 == 0)
{
tx.Commit();
tx = insertSystem.Connection.BeginTransaction();
insertSystem.Transaction = tx;
}
}
Status = "Committing transaction...";
tx.Commit();
con2.Close();
}
Logger.Info("Completed injection.");
Status = "Ready!";
}
catch (Exception ex)
{
Logger.Debug("Exception in InjectSystemsToSql: " + ex.Message + "@" + ex.Source);
}
}
