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
