void CreateDb(FbConnection connection)
{
var assembly = Assembly.GetExecutingAssembly();
var stream = assembly.GetManifestResourceStream(typeof(FirebirdProvider).Namespace + ".struct.sql");
string script = new StreamReader(stream, System.Text.Encoding.ASCII).ReadToEnd();
FbScript fbs = new FbScript(script);
fbs.Parse();
FbConnection.CreateDatabase(connection.ConnectionString);
using (var c = new FbConnection(connection.ConnectionString))
{
c.Open();
try
{
using (FbTransaction myTransaction = c.BeginTransaction())
{
FbBatchExecution fbe = new FbBatchExecution(c, fbs);
fbe.CommandExecuting += (sender, args) => args.SqlCommand.Transaction = myTransaction;
fbe.Execute(true);
}
}
finally
{
c.Close();
}
}
}
private static void FbScriptFB2_5Example(FbCommand cmd)
{
string script = @"
create table withgeneratedid(
id integer primary key,
column2 varchar(100)
);
create sequence seq_withgeneratedid;
set term #;
create trigger withgeneratedid_bi before insert on withgeneratedid
as
begin
if (new.id is null) then new.id = next value for seq_withgeneratedid;
end#
set term ;#
";
FbScript fbScript = new FbScript(script);
fbScript.Parse();
FbBatchExecution exec = new FbBatchExecution(cmd.Connection);
exec.AppendSqlStatements(fbScript);
exec.Execute();
}
public static bool ExecuteBatchScript(
string connectionString,
string pathToScriptFile)
{
// http://stackoverflow.com/questions/9259034/the-type-of-the-sql-statement-could-not-be-determinated
//FbScript script = new FbScript(pathToScriptFile);
FbScript script;
using (StreamReader sr = File.OpenText(pathToScriptFile))
{
script = new FbScript(sr.ReadToEnd());
}
if (script.Parse() > 0)
{
using (FbConnection connection = new FbConnection(connectionString))
{
connection.Open();
try
{
FbBatchExecution batch = new FbBatchExecution(connection, script);
batch.Execute(true);
}
catch (FbException ex)
{
//log.Error(ex);
throw new Exception(pathToScriptFile, ex);
}
}
}
return(true);
}
public static bool UpgradeThis(string sqlfilename)
{
Assembly _assembly;
StreamReader _textStreamReader;
string resname = "KlonsF.SQL." + sqlfilename + ".txt";
_assembly = Assembly.GetExecutingAssembly();
_textStreamReader = new StreamReader(
_assembly.GetManifestResourceStream(resname));
string sql = _textStreamReader.ReadToEnd();
FbConnection conn = DataSetHelper.GetFbConnection(KlonsData.St.KlonsTableAdapterManager.ParamsTableAdapter);
if (conn == null)
{
return(false);
}
FbScript script = new FbScript(sql);
script.Parse();
FbBatchExecution fbe = new FbBatchExecution(conn);
foreach (var cmd in script.Results)
{
fbe.Statements.Add(cmd);
}
fbe.Execute();
return(true);
}
public static bool ExecuteBatchScript(
string connectionString,
string pathToScriptFile)
{
FbScript script = new FbScript(pathToScriptFile);
if (script.Parse() > 0)
{
using (FbConnection connection = new FbConnection(connectionString))
{
connection.Open();
try
{
FbBatchExecution batch = new FbBatchExecution(connection, script);
batch.Execute(true);
}
catch (FbException ex)
{
log.Error(ex);
throw new Exception(pathToScriptFile, ex);
}
finally
{
connection.Close();
}
}
}
return(true);
}
public override IDatabase Execute()
{
var db = Database;
FbScript script;
using (var s = GetType().Assembly.GetManifestResourceStream(ScriptResourceName))
{
using (var r = new StreamReader(s, Encoding.UTF8))
{
script = new FbScript(r.ReadToEnd());
script.Parse();
}
}
try
{
using (var con = new FbConnection(db.ConnectionString))
{
con.Open();
}
}
catch
{
FbConnection.CreateDatabase(db.ConnectionString);
}
using (var con = new FbConnection(db.ConnectionString))
{
var be = new FbBatchExecution(con);
be.AppendSqlStatements(script);
be.Execute();
}
return(db);
}
private static void CreateDatabaseSchema(String Schema, string CSB)
{
using (FbConnection db = new FbConnection(CSB))
{
FbScript fbs = new FbScript(Schema);
fbs.Parse();
FbBatchExecution fbe = new FbBatchExecution(db, fbs);
fbe.Execute(true);
}
}
private static void ExecuteDbCreationScript()
{
using (var connection = new FbConnection(GetConnectionString()))
{
var batchExecution = new FbBatchExecution(connection);
var script = new FbScript(DbCreationScript ?? Resources.create_db);
script.Parse();
batchExecution.AppendSqlStatements(script);
batchExecution.Execute();
}
}
//teste para bloco de codigo
public static void ExecuteCommandBloco(string ibge, string scripttext)
{
using (var conn = new FbConnection(ibge))
{
conn.Open();
FbScript script = new FbScript(scripttext);
script.Parse();
FbBatchExecution fbe = new FbBatchExecution(conn);
fbe.AppendSqlStatements(script);
fbe.Execute();
}
}
private static void ExecuteScript(TargetVersion version, DatabaseLocation location, FbScript script)
{
using (var connection = new FbConnection(GetConnectionString(version, location)))
{
script.Parse();
if (script.Results.Any())
{
var be = new FbBatchExecution(connection);
be.AppendSqlStatements(script);
be.Execute();
}
}
}
protected override void DbCreateDatabase(DbConnection connection, int?commandTimeout,
#pragma warning disable 3001
StoreItemCollection storeItemCollection)
#pragma warning restore 3001
{
FbConnection.CreateDatabase(connection.ConnectionString);
string script = DbCreateDatabaseScript(GetDbProviderManifestToken(connection), storeItemCollection);
FbScript fbScript = new FbScript(script);
fbScript.Parse();
using (var fbConnection = new FbConnection(connection.ConnectionString))
{
var execution = new FbBatchExecution(fbConnection, fbScript);
execution.Execute();
}
}
public static void Install(FbConnection connection)
{
if (connection == null)
{
throw new ArgumentNullException("connection");
}
Log.Info("Start installing Hangfire SQL objects...");
int version = 1;
bool scriptFound = true;
do
{
try
{
var script = GetStringResource(
typeof(FirebirdObjectsInstaller).Assembly,
string.Format("Hangfire.Firebird.Install.v{0}.sql",
version.ToString(CultureInfo.InvariantCulture)));
if (!VersionAlreadyApplied(connection, version))
{
FbScript fbScript = new FbScript(script);
fbScript.Parse();
FbBatchExecution fbBatch = new FbBatchExecution(connection);
fbBatch.AppendSqlStatements(fbScript);
fbBatch.Execute(true);
UpdateVersion(connection, version);
}
}
catch (FbException)
{
throw;
}
catch (Exception)
{
scriptFound = false;
}
version++;
} while (scriptFound);
Log.Info("Hangfire SQL objects installed.");
}
public static void CleanTables(FbConnection connection)
{
if (connection == null)
{
throw new ArgumentNullException("connection");
}
var script = GetStringResource(
typeof(FirebirdTestObjectsInitializer).Assembly,
"Hangfire.Firebird.Tests.Clean.sql");
FbScript fbScript = new FbScript(script);
fbScript.Parse();
FbBatchExecution fbBatch = new FbBatchExecution(connection, fbScript);
fbBatch.Execute(true);
}
protected override void DbCreateDatabase(DbConnection connection, int?commandTimeout,
#pragma warning disable 3001
StoreItemCollection storeItemCollection)
#pragma warning restore 3001
{
FbConnection.CreateDatabase(connection.ConnectionString, pageSize: 16384);
var script = DbCreateDatabaseScript(GetDbProviderManifestToken(connection), storeItemCollection);
var fbScript = new FbScript(script);
fbScript.Parse();
if (fbScript.Results.Any())
{
using (var fbConnection = new FbConnection(connection.ConnectionString))
{
var execution = new FbBatchExecution(fbConnection);
execution.AppendSqlStatements(fbScript);
execution.Execute();
}
}
}
private static void ExecuteScript(TargetVersion version, DatabaseLocation location, FbScript script)
{
// NETProvider#1026
script.UnknownStatement += (sender, e) =>
{
var match = e.Statement.Text.StartsWith("ALTER EXTERNAL FUNCTION ", StringComparison.OrdinalIgnoreCase);
if (match)
{
e.Handled = true;
e.NewStatementType = SqlStatementType.AlterFunction;
}
};
using (var connection = new FbConnection(GetConnectionString(version, location)))
{
script.Parse();
if (script.Results.Any())
{
var be = new FbBatchExecution(connection);
be.AppendSqlStatements(script);
be.Execute();
}
}
}
private void ExecuteDDL(FbConnection db, String fbDDL)
{
MemoryStream memStream = new MemoryStream();
byte[] data = Encoding.ASCII.GetBytes(fbDDL);
memStream.Write(data, 0, data.Length);
memStream.Position = 0;
TextReader txtReader = new StreamReader(memStream);
// parse the SQL script
FbScript script = new FbScript(txtReader);
int i = script.Parse();
FbBatchExecution fbe = new FbBatchExecution(db);
foreach (string cmd in script.Results)
{
fbe.SqlStatements.Add(cmd);
}
if (fbe.SqlStatements.Count > 0)
{
fbe.Execute();
}
}
public Result ExecuteScript(ConnectionData conectionData, string queryText)
{
var result = new Result();
var access = new DataAccess(conectionData);
var connectionString = access.CreateConnectionString();
try
{
FbScript fbs = new FbScript(queryText);
fbs.Parse();
// execute all statements
FbBatchExecution fbe = new FbBatchExecution(new FbConnection(connectionString), fbs);
fbe.Execute();
result.Message = "Ejecutado";
}
catch (Exception ex)
{
result.Message = ex.Message;
}
return(result);
}
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();
}
}
/// <summary>
/// Executes SQL Update Scripts
/// </summary>
/// <param name="SQLFile">Script File to process</param>
/// <returns>true if succesful, otherwise false</returns>
public bool UpdateDatabase(string connectionString, string sqlFile, int newVersion, ref bool tableUpdated)
{
bool Result = false;
if (tableUpdated)
{
tableUpdated = true;
}
else
{
tableUpdated = false;
}
FbConnectionStringBuilder cb = new FbConnectionStringBuilder(connectionString);
cb.Pooling = false;
//connect to local DB
FbConnection database = new FbConnection(cb.ToString());
try
{
database.Open();
FbScript script = new FbScript(Shared.Utilities.FileRead(sqlFile, false));
script.Parse();
int idx = 0;
foreach (FbStatement cmd in script.Results)
{
try
{
if (cmd.Text == "COMMIT" || cmd.Text == "ROLLBACK")
{
continue;
}
if (!tableUpdated &&
(
cmd.Text.ToUpper().Contains("ALTER TABLE") || // any table changes
cmd.Text.ToUpper().Contains("CREATE TABLE") || // any new tables
cmd.Text.ToUpper().Contains("REPLICATE$TABLES") // anything relating to replicate tables
)
)
{
tableUpdated = true;
}
if (OnUpdateDatabase != null)
{
OnUpdateDatabase(null, new FileProgressArgs(String.Empty, script.Results.Count, idx));
idx++;
}
FbBatchExecution fbe = new FbBatchExecution(database);
try
{
fbe.Statements.Add(cmd);
fbe.Execute();
fbe.Statements.Clear();
}
finally
{
fbe = null;
}
}
catch (Exception err)
{
if ((!err.Message.Contains("unsuccessful metadata update") &&
!err.Message.Contains("does not exist")) &&
!err.Message.ToUpper().Contains("ATTEMPT TO STORE DUPLICATE VALUE") &&
!err.Message.ToUpper().Contains("ALREADY EXISTS") &&
!err.Message.Contains("violation of PRIMARY or UNIQUE") &&
!err.Message.Contains("violation of FOREIGN KEY constraint") &&
!err.Message.Contains("GRANT USAGE ON "))
{
Shared.EventLog.Add(err, cmd.Text);
throw;
}
}
}
if (newVersion > 0)
{
//update the version
string SQL = String.Format("UPDATE REPLICATE$OPTIONS SET DATABASE_VERSION = {0}", newVersion);
FbTransaction tran = database.BeginTransaction();
try
{
FbCommand cmdUpdate = new FbCommand(SQL, database, tran);
try
{
cmdUpdate.ExecuteNonQuery();
}
finally
{
cmdUpdate.Dispose();
cmdUpdate = null;
}
}
finally
{
tran.Commit();
tran.Dispose();
}
}
Result = true;
}
catch (Exception e)
{
Shared.EventLog.Add(e);
Result = false;
}
finally
{
database.Close();
database.Dispose();
database = null;
}
return(Result);
}
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,
Version timestamp,
VersionInt int default '0' NOT NULL,
YorN char(1),
Address__Street varchar(50),
Address__City varchar(50)
);
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(
Key1_ID integer PRIMARY KEY NOT NULL,
Key2ID integer NOT NULL,
Key3ID integer NOT NULL,
TextData varchar(512),
DateEntered timestamp NOT NULL,
DateUpdated timestamp
);
CREATE TABLE ComplexMap(
Id integer PRIMARY KEY NOT NULL,
Name varchar(50),
Nested__Id integer,
Nested__Nested2__Id integer,
Nested2__Id integer
);
CREATE TABLE RecursionUser(
Id integer PRIMARY KEY NOT NULL,
Name varchar(50),
CreatedById integer,
SupervisorId integer
);
CREATE TABLE GuidFromDb(
Id varchar(32) PRIMARY KEY,
Name varchar(30)
);
CREATE TABLE Ones(
OneId integer PRIMARY KEY NOT NULL,
Name varchar(50)
);
CREATE TABLE Manys(
ManyId integer PRIMARY KEY NOT NULL,
OneId integer NOT NULL,
AValue integer,
Currency varchar(50)
);
CREATE TABLE UserWithAddress(
Id integer PRIMARY KEY NOT NULL,
Name varchar(100) ,
Address BLOB SUB_TYPE TEXT
);
CREATE TABLE JustPrimaryKey(
Id integer PRIMARY KEY NOT NULL
);
CREATE TABLE NoPrimaryKey(
Name varchar(50)
);
CREATE GENERATOR USERS_USERID_GEN;
CREATE GENERATOR EXTRAUSERINFOS_ID_GEN;
CREATE GENERATOR HOUSES_HOUSEID_GEN;
CREATE GENERATOR COMPLEXMAP_ID_GEN;
CREATE GENERATOR RECURSIONUSER_ID_GEN;
CREATE GENERATOR ONES_ID_GEN;
CREATE GENERATOR MANYS_ID_GEN;
CREATE GENERATOR USERWITHADDRESS_ID_GEN;
CREATE GENERATOR JUSTPRIMARYKEY_ID_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^
CREATE TRIGGER BI_COMPLEXMAP_ID FOR COMPLEXMAP
ACTIVE BEFORE INSERT
POSITION 0
AS
BEGIN
IF (NEW.ID IS NULL) THEN
NEW.ID = GEN_ID(COMPLEXMAP_ID_GEN, 1);
END^
CREATE TRIGGER BI_RECURSIONUSER_ID FOR RECURSIONUSER
ACTIVE BEFORE INSERT
POSITION 0
AS
BEGIN
IF (NEW.ID IS NULL) THEN
NEW.ID = GEN_ID(RECURSIONUSER_ID_GEN, 1);
END^
CREATE TRIGGER BI_ONES_ID FOR ONES
ACTIVE BEFORE INSERT
POSITION 0
AS
BEGIN
IF (NEW.ONEID IS NULL) THEN
NEW.ONEID = GEN_ID(ONES_ID_GEN, 1);
END^
CREATE TRIGGER BI_MANYS_ID FOR MANYS
ACTIVE BEFORE INSERT
POSITION 0
AS
BEGIN
IF (NEW.MANYID IS NULL) THEN
NEW.MANYID = GEN_ID(MANYS_ID_GEN, 1);
END^
CREATE TRIGGER BI_USERWITHADDRESS_ID FOR USERWITHADDRESS
ACTIVE BEFORE INSERT
POSITION 0
AS
BEGIN
IF (NEW.ID IS NULL) THEN
NEW.ID = GEN_ID(USERWITHADDRESS_ID_GEN, 1);
END^
CREATE TRIGGER BI_JUSTPRIMARYKEY_ID FOR JUSTPRIMARYKEY
ACTIVE BEFORE INSERT
POSITION 0
AS
BEGIN
IF (NEW.ID IS NULL) THEN
NEW.ID = GEN_ID(JUSTPRIMARYKEY_ID_GEN, 1);
END^
CREATE PROCEDURE GET_HEX_UUID
RETURNS(
REAL_UUID CHAR(16) CHARACTER SET OCTETS,
HEX_UUID VARCHAR(32))
AS
DECLARE VARIABLE i INTEGER;
DECLARE VARIABLE c INTEGER;
BEGIN
real_uuid = GEN_UUID();
hex_uuid = '';
i = 0;
while (i < 16) do
begin
c = ascii_val(substring(real_uuid from i+1 for 1));
if (c < 0) then c = 256 + c;
hex_uuid = hex_uuid
|| substring('0123456789abcdef' from bin_shr(c, 4) + 1 for 1)
|| substring('0123456789abcdef' from bin_and(c, 15) + 1 for 1);
i = i + 1;
end
suspend;
END^
CREATE TRIGGER GUIDFROMDB_BI FOR GUIDFROMDB
ACTIVE BEFORE INSERT
POSITION 0
AS
DECLARE VARIABLE HEX_UUID VARCHAR(32);
BEGIN
IF ((NEW.ID IS NULL) OR CHAR_LENGTH(NEW.ID)=0) THEN
BEGIN
FOR SELECT FIRST 1 HEX_UUID
FROM GET_HEX_UUID
INTO :hex_uuid DO
BEGIN
new.id = hex_uuid;
END
END
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 SELECT, UPDATE, INSERT, DELETE ON ComplexMap TO ROLE %role%;
GRANT SELECT, UPDATE, INSERT, DELETE ON RecursionUser TO ROLE %role%;
GRANT SELECT, UPDATE, INSERT, DELETE ON Ones TO ROLE %role%;
GRANT SELECT, UPDATE, INSERT, DELETE ON Manys TO ROLE %role%;
GRANT SELECT, UPDATE, INSERT, DELETE ON UserWithAddress TO ROLE %role%;
GRANT SELECT, UPDATE, INSERT, DELETE ON GuidFromDb TO ROLE %role%;
GRANT SELECT, UPDATE, INSERT, DELETE ON JustPrimaryKey TO ROLE %role%;
GRANT SELECT, UPDATE, INSERT, DELETE ON NoPrimaryKey TO ROLE %role%;
GRANT EXECUTE ON PROCEDURE GET_HEX_UUID 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);
fbBatch.AppendSqlStatements(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();
}
}