Hello,

I've found the reason for the error in #2 of the initial message. It was caused by different version of the ICU library -> http://www.firebirdfaq.org/faq358/
I had created the database with FB 2.5.0 and was doing backup and resotre with FB 2.5.1 on another machine.

While the "error" in #1 was caused by the IBExpert option "Commit after each table" and it works otherwise, I would still like to know, if anything is wrong with the ROLES I'm creating in my script?

Also after some more redading I've modified the script a bit. The database is being created with a page size of 16384 with default character set UTF8 and default collation UNICODE_CI_AI. Because of that I've removed "COLLATE UNICODE" from all varchar columns.

I'm also creating a custom collation for numeric sort and assign it to two fields where I need it.

--------------------------------------
create database "<MY_DATABASE_FILE>" page_size 16384 user "<DB_USER>" password "<DB_PASSWORD>" default character set UTF8 collation UNICODE_CI_AI;

[...]

CREATE ROLE ADMINS;
GRANT ADMINS TO <DB_USER>;
CREATE ROLE NORIGHTS;
GRANT NORIGHTS TO SYSDBA;
CREATE ROLE ONLYREAD;
GRANT ONLYREAD TO <DB_USER_ALLREAD>;
CREATE ROLE ONLYVIEW;
GRANT ONLYVIEW TO <DB_USER_ONLYVIEW>;

CREATE DOMAIN BOOLEAN AS SMALLINT DEFAULT 0 CHECK (VALUE IN (-1, 0)) NOT NULL;

CREATE COLLATION UNICODE_NUM_CI_AI FOR UTF8 FROM UNICODE_CI_AI 'NUMERIC-SORT=1';

CREATE TABLE PRODUCTS
(
ID INTEGER NOT NULL,
LOCATION SMALLINT,
INSERTUSER SMALLINT DEFAULT 0,
INSERTDATE TIMESTAMP,
EDITUSER SMALLINT DEFAULT 0,
EDITDATE TIMESTAMP,
PRODUCTNO1 VARCHAR( 100) COLLATE UNICODE_NUM_CI_AI,
PRODUCTNO2 VARCHAR( 255) COLLATE UNICODE_NUM_CI_AI,
PTYPE SMALLINT,
PCATEGORY SMALLINT DEFAULT 0,
DESCRIPTION1 BLOB SUB_TYPE 0 SEGMENT SIZE 80,
DESCRIPTION2 BLOB SUB_TYPE 0 SEGMENT SIZE 80,
DESCRIPTION3 BLOB SUB_TYPE 0 SEGMENT SIZE 80,
DESCRIPTION4 BLOB SUB_TYPE 0 SEGMENT SIZE 80,
DESCRIPTION5 BLOB SUB_TYPE 0 SEGMENT SIZE 80,
PLAIN_DESCRIPTION1 BLOB SUB_TYPE 1 SEGMENT SIZE 80,
PLAIN_DESCRIPTION2 BLOB SUB_TYPE 1 SEGMENT SIZE 80,
PLAIN_DESCRIPTION3 BLOB SUB_TYPE 1 SEGMENT SIZE 80,
PLAIN_DESCRIPTION4 BLOB SUB_TYPE 1 SEGMENT SIZE 80,
PLAIN_DESCRIPTION5 BLOB SUB_TYPE 1 SEGMENT SIZE 80,
SHORT_DESCRIPTION1 VARCHAR( 50),
SHORT_DESCRIPTION2 VARCHAR( 50),
SHORT_DESCRIPTION3 VARCHAR( 50),
SHORT_DESCRIPTION4 VARCHAR( 50),
SHORT_DESCRIPTION5 VARCHAR( 50),
VCODE SMALLINT,
ECODE SMALLINT DEFAULT 0,
PICTURE1 BLOB SUB_TYPE 0 SEGMENT SIZE 80,
PICTURE2 BLOB SUB_TYPE 0 SEGMENT SIZE 80,
IS_ACTIVE BOOLEAN DEFAULT 0,
LOCKED SMALLINT DEFAULT 0,
DEFPRICE1 DOUBLE PRECISION,
DEFPRICE2 DOUBLE PRECISION,
WEIGHT1 DOUBLE PRECISION,
WEIGHT2 DOUBLE PRECISION,
VOLUME DOUBLE PRECISION,
DISCOUNT_POSSIBLE BOOLEAN DEFAULT 0,
QSCALE INTEGER,
NOTES BLOB SUB_TYPE 1 SEGMENT SIZE 80,
ADDITIONAL1 VARCHAR( 50),
ADDITIONAL2 VARCHAR( 50),
ADDITIONAL3 VARCHAR( 50),
ADDITIONAL4 VARCHAR( 50),
ADDITIONAL5 VARCHAR( 50)
);

ALTER TABLE PRODUCTS ADD CONSTRAINT PK_PRODUCTS PRIMARY KEY (ID);
ALTER TABLE PRODUCTS ADD CONSTRAINT UK_PRODUCTS UNIQUE (PRODUCTNO);

CREATE GENERATOR PRODUCTS_PRIMARYKEY;

CREATE ASC INDEX PRODUCTS_PCATEGORY ON PRODUCTS (PCATEGORY);
CREATE ASC INDEX PRODUCTS_PTYPE ON PRODUCTS (PTYPE);
CREATE ASC INDEX PRODUCTS_IS_ACTIVE ON PRODUCTS (IS_ACTIVE);
CREATE ASC INDEX PRODUCTS_PRODUCTNO1 ON PRODUCTS (PRODUCTNO1);
CREATE ASC INDEX PRODUCTS_PRODUCTNO2 ON PRODUCTS (PRODUCTNO2);
CREATE ASC INDEX PRODUCTS_SHORT_DESCR1 ON PRODUCTS (SHORT_DESCRIPTION1);
CREATE ASC INDEX PRODUCTS_SHORT_DESCR2 ON PRODUCTS (SHORT_DESCRIPTION2);
CREATE ASC INDEX PRODUCTS_SHORT_DESCR3 ON PRODUCTS (SHORT_DESCRIPTION3);
CREATE ASC INDEX PRODUCTS_SHORT_DESCR4 ON PRODUCTS (SHORT_DESCRIPTION4);
CREATE ASC INDEX PRODUCTS_SHORT_DESCR5 ON PRODUCTS (SHORT_DESCRIPTION5);
CREATE ASC INDEX PRODUCTS_LOCATION ON PRODUCTS (LOCATION);
CREATE ASC INDEX PRODUCTS_ECODE ON PRODUCTS (ECODE);
CREATE ASC INDEX PRODUCTS_VCODE ON PRODUCTS (VCODE);
CREATE ASC INDEX PRODUCTS_INDIV01 ON PRODUCTS (ADDITIONAL1);
CREATE ASC INDEX PRODUCTS_INDIV02 ON PRODUCTS (ADDITIONAL2);
CREATE ASC INDEX PRODUCTS_INDIV03 ON PRODUCTS (ADDITIONAL3);
CREATE ASC INDEX PRODUCTS_INDIV04 ON PRODUCTS (ADDITIONAL4);
CREATE ASC INDEX PRODUCTS_INDIV05 ON PRODUCTS (ADDITIONAL5);

CREATE TABLE PRODUCTS_PRICES
(
ID INTEGER NOT NULL,
LOCATION SMALLINT,
INSERTUSER SMALLINT DEFAULT 0,
INSERTDATE TIMESTAMP,
EDITUSER SMALLINT DEFAULT 0,
EDITDATE TIMESTAMP,
PRODUCTID INTEGER,
PRICELISTID INTEGER DEFAULT 0,
QUANTITY NUMERIC( 8, 2),
PRICE_GROSS DOUBLE PRECISION,
PRICE_NET DOUBLE PRECISION
);

ALTER TABLE PRODUCTS_PRICES ADD CONSTRAINT PK_PRODUCTS_PRICES PRIMARY KEY (ID);
CREATE GENERATOR PRODUCTS_PRICES_PRIMARYKEY;

CREATE ASC INDEX PRODUCTS_PRICES_PRODUCTID ON PRODUCTS_PRICES (PRODUCTID);
CREATE ASC INDEX PRODUCTS_PRICES_LOCATION ON PRODUCTS_PRICES (LOCATION);
CREATE ASC INDEX PRODUCTS_PRICES_PRICELISTID ON PRODUCTS_PRICES (PRICELISTID);

[...]

ALTER TABLE PRODUCTS ADD CONSTRAINT FK_PRODUCTS_INSERTUSER FOREIGN KEY (INSERTUSER) REFERENCES USER_TABLE(ID) ON UPDATE CASCADE ON DELETE SET DEFAULT;
ALTER TABLE PRODUCTS ADD CONSTRAINT FK_PRODUCTS_EDITUSER FOREIGN KEY (EDITUSER) REFERENCES USER_TABLE(ID) ON UPDATE CASCADE ON DELETE SET DEFAULT;
ALTER TABLE PRODUCTS ADD CONSTRAINT FK_PRODUCTS_PRODCATEGORY FOREIGN KEY (PCATEGORY) REFERENCES SUP_PRODUCT_CATEGORIES(ID) ON UPDATE CASCADE ON DELETE SET DEFAULT;
ALTER TABLE PRODUCTS ADD CONSTRAINT FK_PRODUCTS_ECODE FOREIGN KEY (ECODE) REFERENCES SUP_PRODUCT_ENTITIES(ID) ON UPDATE CASCADE ON DELETE SET DEFAULT;

ALTER TABLE PRODUCTS_PRICES ADD CONSTRAINT FK_PRODUCTS_PRICES_INSERTUSER FOREIGN KEY (INSERTUSER) REFERENCES USER_TABLE(ID) ON UPDATE CASCADE ON DELETE SET DEFAULT;
ALTER TABLE PRODUCTS_PRICES ADD CONSTRAINT FK_PRODUCTS_PRICES_EDITUSER FOREIGN KEY (EDITUSER) REFERENCES USER_TABLE(ID) ON UPDATE CASCADE ON DELETE SET DEFAULT;
ALTER TABLE PRODUCTS_PRICES ADD CONSTRAINT FK_PRODUCTS_PRICES_PRICELISTID FOREIGN KEY (PRICELISTID) REFERENCES PRICELIST_TABLE(ID) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE PRODUCTS_PRICES ADD CONSTRAINT FK_PRODUCTS_PRICES_PRODUCTID FOREIGN KEY (PRODUCTID) REFERENCES PRODUCTS(ID) ON DELETE CASCADE ON UPDATE CASCADE;

------------------------------------------

- If I take a look at the result, IBExpert shows "UTF8" as collation for BLOB SUB_TYPE 1 columns, so that the default collation seems not to apply to such columns. Is that correct? Do I have to set it manually?
- Any potential improvements you see? (regarding indexes, my latest modifications etc.)


Best regards,
Patrick