CLOBs

Firebird has a BLOB

Which has a "segment size" The default is 80 bytes.. but it is generally a good idea to set the segment size to some "average" size if known..

Any guesses on this..?? Or leave as default..???

Also there is two SUB_TYPEs (binary and text) For what I have seen so far that should be "text"

Foreign Keys

Events_log table has a foreign key reference to to the events table.. (a "constraint")

In Firebird there is generally an...

ON DELETE
ON UPDATE

...action.. Default is "NO ACTION"

Options are [NO ACTION, CASCADE, SET NULL, SET DEFAULT]

What is expected here in code..???

I took a look at http://firebird.sourceforge.net/manual/migration-mssql-data-types.html and figured that the following should be used...

For BOOLEAN/BIT use CHAR(1)
For BLOB/TEXT use BLOB SUB_TYPE TEXT

As for foreign key references, it looks like the existing statement should work without any additional ON DELETE or ON UPDATE.

So maybe the following?

CREATE SEQUENCE events_log_log_id_seq;

CREATE TABLE events_log (
log_id INTEGER PRIMARY KEY,
event_id INTEGER REFERENCES events(event_id) NOT NULL ,
entered TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL ,
status INTEGER,
message BLOB SUB_TYPE TEXT
);

Thanks for reviewing that.. I figured as much but wanted to make sure..

I'll be back on this friday.. (today's swamped...). Obviously as I wade through the first couple it will be slow but then it should speed up..

John..

Oh, one other thing... You indicated that you changed the way the code handles "sequences".. How are you retrieving the sequence?? On that document you quoted above is the common technique used (stored procedure retrieving the generator value) for Firebird. If that is the case I will need to include the creation of those stored procs to retrieve the sequences..

Let me know.. (also naming convention for those stored procs)

John..

The SEQUENCE code should be transparent as long as you use the sequence names already defined and used in the scripts.

In the following example, a sequence "events_log_log_id_seq" is created and will be used by the events_log.log_id column. The application will make sure to retrieve the next value of the sequence before an insert. A stored procedure does not need to be used.

CREATE SEQUENCE events_log_log_id_seq;

CREATE TABLE events_log (
log_id INTEGER PRIMARY KEY,
event_id INTEGER REFERENCES events(event_id) NOT NULL ,
entered TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL ,
status INTEGER,
message BLOB SUB_TYPE TEXT
);

This might not seem like a big deal (it shouldn't), but PostgreSQL and MS SQL have methods that allow for auto-numbers to be retrieved AFTER an insert. Whereas Oracle, DaffodilDB, and Firebird need to retrieve the auto-number BEFORE the insert.

In Centric CRM 3.1 both ways are now acceptable.

Matt Rajkowski wrote:
In the following example, a sequence "events_log_log_id_seq" is created and will be used by the events_log.log_id column. The application will make sure to retrieve the next value of the sequence before an insert. A stored procedure does not need to be used.

Ok, I looked at the code and it looks like you handled that.. (yeah, I know, I should have looked first...)

Matt,

RE: new_cdb.sql

I'm getting an error on a index creation:

CREATE INDEX contactlist_namecompany ON contact (namelast, namefirst, company)

..and...

CREATE INDEX contactlist_namecompany ON contact (company, namelast, namefirst)

Firebird is complaining that the "key size too big for index..."

..removing the "company" from the index definition and it works..

doing (as a test)..

CREATE INDEX contactlist_company ON contact ( company )

also fails.. (same error)

CREATE INDEX contactlist_namecompany ON contact (namelast, namefirst)

works.... (VARCHAR(80), VARCHAR(80)

The company field is a VARCHAR(255) so it seems Firebird can not create an index on a field 255 or greater... but ok on 160...

How do you want me to proceed on these two indexes..???

Matt,

RE: new_cdb.sql

There seemes to be a (Sequence) Generator name that is too long for Firebird

CREATE GENERATOR contact_lead_skipped_map_map_id_seq;

The error from Firebird is..

"Name longer than database column size"

Limit is Char(31)..

What do you suggest.. as a shorter name... (it will have to be changed in the code..??)

More of same issue with Generator names to long.. (in the file new_cdb.sql)

contact_lead_read_map_map_id_seq
lookup_delivery_option_code_seq
organization_add_address_id_seq
contact_imaddress_address_id_seq
contact_textmessageaddress_address_id_seq
report_criteria_parameter_parameter_id_seq
report_queue_criteria_criteria_id_seq
lookup_relationship_types_type_id_seq
relationship_relationship_id_seq

I have not gotten to the other scripts yet so I would assume there is more..

Also.. I found a number of generator names that has "double underscores" as part of their name.. (example: contact_email_emailaddressseq)

I assume that is a "typo"..???