Hello,

we are working on a new release of our application, which will support unicode.

When we started with that, my colleague did some research and "ported" the script for the currently used ISO8859_1 database to UTF8.
We then created the UTF8 database and were developing using it all the time. Now some testing shall be done and I've discovered some issues:

1. Doing a restore with "commit after each table" option active (using IBExpert) leads to the error
"Unsuccessful execution caused by system error that does not
preclude successful execution of subsequent statements.
action cancelled by trigger (0) to preserve data integrity.
could not find table/procedure for GRANT."

2. When I turn off this option, the error doesn't occur anymore, but another one does:
"This operation is not defined for system tables.
unsuccessful metadata update.
MY_PRODUCTS.
COLLATION UNICODE for CHARACTER SET UTF8 is not installed."

There wasn't a problem with that before the restore attempt - I could / can access that database and work with it.


Unfortunately my colleague is not available, so I don't know how well his research was, if there were some Firebird related changes inbetween etc.

I would like to post a small part of the database script and to ask you to check it.
The current application / database ship with Firebord 2.1.3. The new one is supposed to ship with Firebird 2.5.1

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

[...]

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 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,
PRODUCTNO2 VARCHAR( 255) COLLATE UNICODE,
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) COLLATE UNICODE,
SHORT_DESCRIPTION2 VARCHAR( 50) COLLATE UNICODE,
SHORT_DESCRIPTION3 VARCHAR( 50) COLLATE UNICODE,
SHORT_DESCRIPTION4 VARCHAR( 50) COLLATE UNICODE,
SHORT_DESCRIPTION5 VARCHAR( 50) COLLATE UNICODE,
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) COLLATE UNICODE,
ADDITIONAL2 VARCHAR( 50) COLLATE UNICODE,
ADDITIONAL3 VARCHAR( 50) COLLATE UNICODE,
ADDITIONAL4 VARCHAR( 50) COLLATE UNICODE,
ADDITIONAL5 VARCHAR( 50) COLLATE UNICODE
);

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;

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

There are some more fields for the description etc. but I removed them to make the list a bit shorter, only leaving 5 of each there.

- Is anything wrong with the ROLES (because of the error in #1)?
- What is wrong with the COLLATION (because of the error in #2)? Does something have to be installed? Is it the wrong one for the UTF8 character set? Is a collation needed at all for UTF8?
- Any potential improvements you see? (regarding indexes etc.)


Best regards,
Patrick