John, looks like we have some agreement...

1. take sequences to 3 per node
2. we'll use a lookup for using alternative table names as you suggested... for all future tables we will keep them to 31 characters or less

Thanks for being patient and we look forward to testing out your scripts!

You know what they say..

"No good deed goes unpunished..."

Well, the following sequences resolve to the exact same name

lookup_orgaddress_type_code_seq
lookup_orgmail_types_code_seq
lookup_orgphone_types_code_seq

loo_org_typ_cod_seq

After reviewing the code in a bit more detail I found that either the name was "hard coded" or the following contcatination occured

tablename+"_"+primaryKey+"_seq" (or "_id_seq")

Assuming a that the "_id" is actually part of the primary key then "_seq" (4) is the last component concatinated..

that means that the "tableName" and the "primarykey" could be "substring"ed to 13 each (plus the underscore) (Also those names that are less than 13 would remain unchanged)

The above sequences would resolve to..

lookup_orgadd_type_code_seq
lookup_orgmai_types_code_seq
lookup_orgpho_types_code_seq

which remains unique...

This also seems it would be a smaller change in the code. I believe I reviewed every single instance of DatabaseUtils.getNextSeq(...) and found the use of this concatentation in 4 classes..

Category.java
CustomLookupElement.java
ImportLookupLists.java
TicketCategoryDraft.java

In all other cases it is a basic search and replace.. So.. To summarize..

I recommend that sequences be named by using the first 13 characters of the table name, the first 13 characters of the primary key, and then the standard "_seq" ending.. Those table names or primary keys that are less than 13 characters will remain unchanged.

Let me know ASAP so I can continue with the scripts...

John Moore
SonicSpider LLC

I like the idea of just truncating these sequences in someway... to make the change easiest, and without conflict, we would just update DatabaseUtils.getNextSeq() specifically for Firebird.

However, getNextSeq() takes a single String as input (the complete original sequence name). So we don't have the luxury of operating on the table name + key without making additional code changes.

It would be nice if the new sequence names are somewhat legible and ordered too, which makes sense to keep part of the table name and primary key.

Expanding on your idea...

// Take the first 13 characters
// _ separator
// Take the last 17 characters

// Sample code...

if (seq.length() > 31)
seqPart1 = seq.substring(0, 13);
seqPart2 = seq.substring(14);
seq = seqPart1 + "_" + seqPart2.substring(seqPart2.length() - 17);
}

Benefits...
- Doesn't conflict with existing sequences (I believe)
- Doesn't conflict with truncated sequences (I believe)
- Somewhat legible sequences
- One code change

lookup_document_store_permission_category_code_seq =
lookup_docume_category_code_seq

Doable?

Matt Rajkowski wrote:

Benefits...
- Doesn't conflict with existing sequences (I believe)
- Doesn't conflict with truncated sequences (I believe)
- Somewhat legible sequences
- One code change

lookup_document_store_permission_category_code_seq =
lookup_docume_category_code_seq

Doable?

I like that it does not touch any sequences that are less than 31 characters, and that you can make the code change in one location.. so..

Works for me... Let's do it....<G>

(Obviously as I test the scripts, Firebird will keep us honest...<G>)

John..

Matt,

In the file "new_project.sql" the table definition:

CREATE TABLE lookup_project_permission (
code INT PRIMARY KEY,
category_id INTEGER REFERENCES lookup_project_permission_category(code),
permission VARCHAR(300) UNIQUE NOT NULL,
description VARCHAR(300) NOT NULL,
default_item CHAR(1) DEFAULT '0',
"level" INTEGER DEFAULT 0,
enabled CHAR(1) DEFAULT '0',
group_id INTEGER DEFAULT 0 NOT NULL,
default_role INTEGER REFERENCES lookup_project_role(code)
);

and ... in the file "new_tms.sql" there were several

CREATE TABLE ticket_level (
CREATE TABLE ticket_severity (
CREATE TABLE lookup_ticketsource (
CREATE TABLE lookup_ticket_status (
CREATE TABLE ticket_priority (

I had to remove the 'UNIQUE' descriptor for the field "permission" in the first case and "description" in the later cases, because that causes a index to be created on a VARCHAR(300) which is over the limit for Firebird..

What impact will removing the UNIQUE setting this have???

John..

Matt,

First could you comment on my previous inquiry...???

Next I have a question about the tables in the new_custom_field.sql..

It appears that a two field primay key is needed. My problem is that looking at the ddls for all of the currently supported db, this is not clear.. Example:

/* Each category can have multiple groups of fields */
CREATE TABLE custom_field_group (
category_id INTEGER NOT NULL REFERENCES custom_field_category(category_id),
group_id INT IDENTITY PRIMARY KEY,
....<snip>....

If the comment is correct this would require both the category_id and the group_id in the primary key. Not being used to the data definition languages for Daffodil, MySql and ProgressSQL (Very familar with Firebird obviously..) I would have expected something explicitly indicating both fields.. i.e.

PRIMARY KEY (category_id, group_id);

Is my interpretation correct.. Please advise...

John Moore

BTW.. Major HD crash this last week got me sidetracked restoring backups to get some key systems back up.. I had planned to be done with this already, but now I'm a week behind.. <sigh>

(Extension of previous message)

RE: new_custom_fields.sql

I see another issue/confusion, I'm having a problem "interpreting" the ddls so I can translate them to Firebird (the current structures at face value will not run in Firebird because FK are required to be a primay key from another table)

Therefore, between the previous post (about multi primary key fields) and my inquiry here, maybe you can explain...

In the table.. (abbreviated..)

CREATE TABLE module_field_categorylink (
id INTEGER PRIMARY KEY,
module_id INTEGER REFERENCES permission_category(category_id) NOT NULL,
category_id INT UNIQUE ,
..<snip>...
);

The "module_id" gets it's value from the category_id field of "permission_category" and yet there is also a "category_id" value. My confusion is with the next table were:...

CREATE TABLE custom_field_category (
module_id INTEGER REFERENCES module_field_categorylink(category_id) NOT NULL,
category_id INT PRIMARY KEY,
category_name VARCHAR(255) NOT NULL,
..<snip>...
);

... the module_id again gets a "category_id" but this time from the previous tables "category_id" and again there is a new "category_id"..

Anyway, the naming convention of having a "category_id" become a "module_id" is probably confusing my understanding of what is needed here.. (hopefully I have articulated my confusion..<G>)

A bit of an explaination would help...

Thank you!!

John Moore

Hello John,

There won't be any impact by removing the UNIQUE of lookup_project_permission. This was added as a helpful constraint for the developer... the application does not depend on this constraint.

The custom_field_group table looks different because the primary key field is declared 2nd instead of 1st... but this is still just 1 primary key. This appears to work in Firebird as-is if you add: group_id INTEGER NOT NULL PRIMARY KEY.

Let me take a look at new_custom_fields.sql to see what's going on...

Looking forward to testing this...

- Matt

Naming convention confusion here!

This came about by merging a few concepts...

The custom fields approach allows users to create groups of fields to be used throughout the application. We later grouped these groups of fields and called them a category, which relates to a specific module, creating the confusion.

The PermissionCategory table reflects the modules in the system, but the CustomFieldCategory reflects the grouping of a series of custom fields... both using category_id in a different way (not sure which came first).

I'm not sure how to resolve the creation of [custom_field_category] as it references module_field_categorylink(category_id) which is not the primary key, but is also unique.

I tried:
CREATE UNIQUE INDEX module_field_categor_cat_idx ON module_field_categorylink(category_id);
but that didn't help, as the creation of [custom_field_category] fails.

If we remove this 1 reference here in [custom_field_category], then I don't see any application issue.

Maybe the rest of the custom_field tables will work?

Matt Rajkowski wrote:

This came about by merging a few concepts...
The PermissionCategory table reflects the modules in the system, but the CustomFieldCategory reflects the grouping of a series of custom fields... both using category_id in a different way (not sure which came first).

Ok.. I guess most users of CentricCRM never look at the table structures...<G>

What I do suggest though is for us "data geeks" that this one script have a few more "notes" in it... My first look at it and "red flags" went off like fireworks...<G>

I'm not sure how to resolve the creation of [custom_field_category] as it references module_field_categorylink(category_id) which is not the primary key, but is also unique.

I found that the following worked..

CREATE TABLE module_field_categorylink (
id INTEGER NOT NULL,
module_id INTEGER NOT NULL REFERENCES permission_category(category_id),
category_id INTEGER NOT NULL UNIQUE ,
..<snip>...
PRIMARY KEY (ID, MODULE_ID, CATEGORY_ID)
);

This creates a different "data structure" because it will allow for mulitiple combinations of "id/module_id/category_id".. whereas having only the "id" as the primary key insures that there is only one record with that "id" This might be risky, "unless" the application code carefully restricts this..

If we remove this 1 reference here in [custom_field_category], then I don't see any application issue.

Maybe the rest of the custom_field tables will work?

Well actually no..

We encounter the same problem between "custom_field_category" and "custom_field_group".. and then again between "custom_field_group" and "custom_field_info".. and etc down to "custom_field_record". Each time the FK requirement is on a field that is not part of a Unique primary key field (which as I understand relational theory, has to be both Unique AND part of the primary key to be a FK) Firebird is more "anal" about adhereing to this model.. hence the problem..

I got these four tables to work with the following structure.. (snipped out the extraneous fields for brevity..):

CREATE TABLE module_field_categorylink (
id INTEGER NOT NULL,
module_id INTEGER NOT NULL REFERENCES permission_category(category_id),
category_id INTEGER NOT NULL UNIQUE ,
<snip>
PRIMARY KEY (ID, MODULE_ID, CATEGORY_ID)
);

CREATE TABLE custom_field_category (
module_id INTEGER NOT NULL REFERENCES module_field_categorylink(category_id),
category_id INTEGER NOT NULL UNIQUE,
<snip>
PRIMARY KEY (MODULE_ID, CATEGORY_ID)
);

CREATE TABLE custom_field_group (
category_id INTEGER NOT NULL REFERENCES custom_field_category(category_id),
group_id INTEGER NOT NULL UNIQUE,
<snip>
PRIMARY KEY (CATEGORY_ID, GROUP_ID)
);

CREATE TABLE custom_field_info (
group_id INTEGER NOT NULL REFERENCES custom_field_group(group_id),
field_id INTEGER NOT NULL UNIQUE,
<snip>
PRIMARY KEY (GROUP_ID, FIELD_ID)
);

CREATE TABLE custom_field_lookup (
field_id INTEGER NOT NULL REFERENCES custom_field_info(field_id),
code INTEGER NOT NULL,
<snip>
PRIMARY KEY (FIELD_ID, CODE)
);

CREATE TABLE custom_field_record (
link_module_id INTEGER NOT NULL,
link_item_id INTEGER NOT NULL,
category_id INTEGER NOT NULL REFERENCES custom_field_category(category_id),
record_id INTEGER NOT NULL UNIQUE,
<snip>
PRIMARY KEY (LINK_MODULE_ID, LINK_ITEM_ID, CATEGORY_ID, RECORD_ID)
);

======

The question is: Though this is more relationally correct, Would this work "application" wise...???

Let me know what you think...

John..

PS.. down to the last 5 scripts....