Esempio n. 1
0
        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();
                }
            }
        }
Esempio n. 2
0
        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();
        }
Esempio n. 3
0
        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);
        }
Esempio n. 4
0
        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);
        }
Esempio n. 5
0
        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);
        }
Esempio n. 7
0
        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();
     }
 }
Esempio n. 9
0
 //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();
     }
 }
Esempio n. 10
0
 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();
         }
     }
 }
Esempio n. 11
0
        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();
            }
        }
Esempio n. 12
0
        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();
                }
            }
        }
Esempio n. 15
0
 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();
         }
     }
 }
Esempio n. 16
0
        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();
            }
        }
Esempio n. 17
0
        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);
        }
Esempio n. 18
0
        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);
        }
Esempio n. 20
0
        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();
            }
        }