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"..???

Ok..

I finished the first pass.. (about 12 hours of work over three days..)..

The following issues need to be resolved before I can continue..

1) Key size to big.. (new_cdb.sql - discussed above)

2) Generator (Sequence) names to big (limit char(31) also discussed above..)

.. I also encountered the same issue with table names and I would imagine index names as well (though I don't imagine index names would be referenced in code)

3) Table and Index names to big (limit char(31))....

I would be glad to provide a detailed listing of which names are to large if needed..

I await your feedback...

John Moore
SonicSpider LLC

Thanks for pointing out these issues... let's see what we can do for support...

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

I would somehow like to keep the indexes, as they are configured to speed up access to the data. However, we can test without the index and see if the speed is acceptable on a large data set... so I would comment them out for now.

To keep the index, reducing the field size could be an option, but we would need to take a look at the columns in question.

> There seems to be a (Sequence) Generator name that is too long for Firebird -- Limit is Char(31).

I counted 45 sequences that would cause a problem then...

If we can shorten the existing names programmatically (for those that are longer than 31 characters) and then use that in the DDL scripts, then the application's database layer can be updated specifically for Firebird.

So, if a SEQUENCE is more than 31 characters then the SEQUENCE name can be reduced so that only 4 characters are used per node name...

For example,

report_criteria_parameter_parameter_id_seq becomes repo_crit_para_para_id_seq AND
contact_textmessageaddress_address_id_seq becomes cont_text_addr_id_seq

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

These sequence names are intended and were most likely generated by PostgreSQL. Early versions of PostgreSQL had a limit on the sequence length so they were automatically truncated... fortunately these won't cause a problem because they are already 31 characters.

Matt Rajkowski wrote:

I would somehow like to keep the indexes, as they are configured to speed up access to the data. However, we can test without the index and see if the speed is acceptable on a large data set... so I would comment them out for now.

To keep the index, reducing the field size could be an option, but we would need to take a look at the columns in question.

As indicated it was only the one field that pushed it over the edge.. Does leaving out that field still make it a useful index..??

> There seems to be a (Sequence) Generator name that is too long for Firebird -- Limit is Char(31).

I counted 45 sequences that would cause a problem then...

If we can shorten the existing names programmatically (for those that are longer than 31 characters) and then use that in the DDL scripts, then the application's database layer can be updated specifically for Firebird.

So, if a SEQUENCE is more than 31 characters then the SEQUENCE name can be reduced so that only 4 characters are used per node name...

For example,

report_criteria_parameter_parameter_id_seq becomes repo_crit_para_para_id_seq AND
contact_textmessageaddress_address_id_seq becomes cont_text_addr_id_seq

OK, that solves the generator/sequence problems.. (and I assume that is what you want me to do..)

do you want me to create a list of the generator/sequence names I change and send that to you..???

Next...

You did not comment on the table name issue.. (to large) for example in the new_workflow.sql script the table..

business_process_component_library

is 34 characters.. 3 over the limit..

There are about 9 tables that are more than 31 characters... for this change I'm looking for suggestions. It looks like we should accommodate Firebird by permanently changing the table names... but how will that affect the application?

The following would be affected:

business_process_component_library
business_process_component_parameter
business_process_component_result_lookup
business_process_parameter_library
document_store_department_member
lookup_document_store_permission
lookup_document_store_permission_category
lookup_opportunity_event_compelling
lookup_project_permission_category

Yeah, that is a problem...

One thing to consider is that the next version of Firebird (2) now in alpha will allow for longer table names. Sooo.. maybe a "short term" solution may be adequate.. Unfortunately both are a task.. Off hand maybe a property file that looks up the current table name and if that name exists it returns the shorter version. That way the very existance of those names in the property file wil cause a translation.. If the file is "empty" with regard to these names (doing nothing does not break the app) the old names are used.. I do something like this in a number of web apps I build..

I have not checked if you have any property files you are using... (or xml files)..

John...

PS (Later)..

I just found out from a key developer on the Firebird team that this feature probably will not make it out for V2 (bad rumors I guess).. Anyway, it will continue to be an issue for a while.. That may change how you want to approach it..??

Oopss.. some how it double posted the last message..

Matt Rajkowski wrote:

So, if a SEQUENCE is more than 31 characters then the SEQUENCE name can be reduced so that only 4 characters are used per node name...

I have encountered a number of sequence names that are still to long using your suggested format...

lookup_document_store_permission_category_code_seq = look_docu_stor_perm_cate_code_seq

order_product_status_order_product_status_id_seq = orde_prod_stat_orde_prod_stat_id_seq

order_product_options_order_product_option_id_seq = orde_prod_opti_orde_prod_opti_id_seq

quote_product_options_quote_product_option_id_seq = quot_prod_opti_quot_prod_opti_id_seq

How do you want to handle these..???? Reduce it to three per node..??

i.e.

order_product_status_order_product_status_id_seq = ord_pro_sta_ord_pro_sta_id_seq

Ok..

Guess I'm on hold until..

1) It is decided how to do the Sequence name (from 4 to 3 per node..??)

2) Table name "short" format

Let me know.. (love to keep this ball rolling...<G>)

Attached is my sequence and table list of problem names...

Matt,

What is the status (sequence names, table names)...????

John Moore
SonicSpider LLC