Databases - Firebird sql2gdb Datapump

sql2gdb is an open source application that

Converts MS Access tables to Firebird
Automatically fixes names if they are reserved words or if they are too long

I do have an issue with the lack of a report listing which names were modified ... particularly since it may affect my code.

Generated SQL - Example

These are examples related to a single table

CREATE TABLE Barcodes_tbl (
  ID INTEGER NOT NULL,
  Barcode VARCHAR(50),
  Barcode_Type_ID VARCHAR(5),
  Description VARCHAR(50),
  Comment BLOB SUB_TYPE TEXT,
  User_ID VARCHAR(5),
  Date_Assigned DATE,
  BC_Database VARCHAR(50),
  BC_Table VARCHAR(50),
  Record_ID VARCHAR(50)
)
;
ALTER TABLE Barcodes_tbl
ADD PRIMARY KEY (
  ID
)
;
CREATE INDEX Barcode7
ON Barcodes_tbl(
  Barcode
)
;
CREATE INDEX Barcode_Type_LUBarcodes_tbl8
ON Barcodes_tbl(
  Barcode_Type_ID
)
;
CREATE INDEX ID9
ON Barcodes_tbl(
  ID
)
;
CREATE INDEX Record_ID10
ON Barcodes_tbl(
  Record_ID
)
;
CREATE INDEX User_ID11
ON Barcodes_tbl(
  User_ID
)
;
CREATE GENERATOR G_Barcodes_tblIDGen1
;
SET GENERATOR G_Barcodes_tblIDGen1 TO 1
;
CREATE TRIGGER GetNextG_Barcodes_tblIDGen1 FOR Barcodes_tbl
BEFORE INSERT
AS
BEGIN
  NEW.ID = GEN_ID(G_Barcodes_tblIDGen1, 1);
END
;
ALTER TABLE Barcodes_tbl
ADD CONSTRAINT Barcodes_tblKey0
FOREIGN KEY (
  Barcode_Type_ID
) REFERENCES Barcode_Type_LU(
  BarcodeType_ID
)
ON UPDATE NO ACTION
;

Automatic Name Shortening - Example

This was automatically generated

CREATE TRIGGER tstrument_Metrology_Log_tblIDGe FOR Instrument_Metrology_Log_tbl
BEFORE INSERT
AS
BEGIN
  NEW.ID = GEN_ID(strument_Metrology_Log_tblIDGen, 1);
END
;
CREATE TRIGGER GetNextG_Barcodes_tblIDGen1 FOR Barcodes_tbl
BEFORE INSERT
AS
BEGIN
  NEW.ID = GEN_ID(G_Barcodes_tblIDGen1, 1);
END
;

This is how the names were modified to not exceed 31 characters (one name was not shortened).

tstrument_Metrology_Log_tblIDGe
strument_Metrology_Log_tblIDGen
GetNextG_Barcodes_tblIDGen1
123456789 123456789 123456789 ^23456789

Data Copy

The SQL script actually has all the data created using this syntax - however, it appears that blob data is not copied (need to verify this).

INSERT INTO Sample_Timeline_tbl (
  Blob_Data,
  Comment,
  Data_Time,
  ID,
  Instrument_ID,
  Procedure_ID,
  Procedure_Version,
  Sample_ID,
  SOP_ID,
  Text_Data,
  User_ID
) VALUES (
  NULL,
  NULL,
  '4/15/2004 5:25:29 PM',
  163,
  NULL,
  'SIRS',
  NULL,
  163,
  0,
  NULL /* text field */,
  'J_Jas'
)
;

Datapump Comparision

Move this to a new page

As you can see, the tables are almost identical.

sql2gdb	ibdatapump
CREATE TABLE Barcodes_tbl ( ID INTEGER NOT NULL, Barcode VARCHAR(50), Barcode_Type_ID VARCHAR(5), Description VARCHAR(50), Comment BLOB SUB_TYPE TEXT, User_ID VARCHAR(5), Date_Assigned DATE, BC_Database VARCHAR(50), BC_Table VARCHAR(50), Record_ID VARCHAR(50) )	/* Original table name is "Barcodes_tbl" / CREATE TABLE Barcodes_tbl ( ID INTEGER NOT NULL / "ID" / , Barcode VARCHAR(50) / "Barcode" / , Barcode_Type_ID CHAR(5) / "Barcode_Type_ID" / , Description VARCHAR(50) / "Description" / , Comment BLOB SUB_TYPE 1 / "Comment" / , User_ID CHAR(5) / "User_ID" / , Date_Assigned DATE / "Date_Assigned" / , BC_Database VARCHAR(50) / "BC_Database" / , BC_Table VARCHAR(50) / "BC_Table" / , Record_ID VARCHAR(50) / "Record_ID" */ );

sql2gdb

ibdatapump

CREATE TABLE Barcodes_tbl (
  ID INTEGER NOT NULL,
  Barcode VARCHAR(50),
  Barcode_Type_ID VARCHAR(5),
  Description VARCHAR(50),
  Comment BLOB SUB_TYPE TEXT,
  User_ID VARCHAR(5),
  Date_Assigned DATE,
  BC_Database VARCHAR(50),
  BC_Table VARCHAR(50),
  Record_ID VARCHAR(50)
)

/* Original table name is "Barcodes_tbl" */
CREATE TABLE Barcodes_tbl (
  ID INTEGER NOT NULL /* "ID" */ ,
  Barcode VARCHAR(50) /* "Barcode" */ ,
  Barcode_Type_ID CHAR(5) /* "Barcode_Type_ID" */ ,
  Description VARCHAR(50) /* "Description" */ ,
  Comment BLOB SUB_TYPE 1 /* "Comment" */ ,
  User_ID CHAR(5) /* "User_ID" */ ,
  Date_Assigned DATE /* "Date_Assigned" */ ,
  BC_Database VARCHAR(50) /* "BC_Database" */ ,
  BC_Table VARCHAR(50) /* "BC_Table" */ ,
  Record_ID VARCHAR(50) /* "Record_ID" */
);

But the initial generator value is different, and only one of the triggers includes a null test. There is also an issue with the terminating character.

sql2gdb	CREATE GENERATOR G_Barcodes_tblIDGen1 ; SET GENERATOR G_Barcodes_tblIDGen1 TO 1 ; CREATE TRIGGER GetNextG_Barcodes_tblIDGen1 FOR Barcodes_tbl BEFORE INSERT AS BEGIN NEW.ID = GEN_ID(G_Barcodes_tblIDGen1, 1); END ;
ibdatapump	/* Generators for AutoInc fields for table "Barcodes_tbl" / / SELECT max([Barcodes_tbl].[ID]) FROM [Barcodes_tbl] */ CREATE GENERATOR GEN_Barcodes_tbl_ID; SET GENERATOR GEN_Barcodes_tbl_ID TO 0; SET TERM ^; CREATE TRIGGER TRIG_Barcodes_tbl_BI FOR Barcodes_tbl BEFORE INSERT AS BEGIN IF(NEW.ID IS NULL) THEN NEW.ID = GEN_ID(GEN_Barcodes_tbl_ID,1); END ^ SET TERM ;^

sql2gdb

CREATE GENERATOR G_Barcodes_tblIDGen1
;
SET GENERATOR G_Barcodes_tblIDGen1 TO 1
;
CREATE TRIGGER GetNextG_Barcodes_tblIDGen1 FOR Barcodes_tbl
BEFORE INSERT
AS
BEGIN
  NEW.ID = GEN_ID(G_Barcodes_tblIDGen1, 1);
END
;

ibdatapump

    /* Generators for AutoInc fields for table "Barcodes_tbl" */
    /* SELECT  max([Barcodes_tbl].[ID]) FROM [Barcodes_tbl] */

    CREATE GENERATOR GEN_Barcodes_tbl_ID;
        SET GENERATOR GEN_Barcodes_tbl_ID TO 0;

    SET TERM ^;
    CREATE TRIGGER TRIG_Barcodes_tbl_BI FOR Barcodes_tbl BEFORE INSERT
    AS BEGIN
      IF(NEW.ID IS NULL) THEN NEW.ID = GEN_ID(GEN_Barcodes_tbl_ID,1);
    END ^
    SET TERM ;^

Also note that there are no indices with ibdatapump but 6 with sql2gdb.

Author: Robert Clemenzi - clemenzi@cpcug.org