View Issue Details
| ID | Project | Category | View Status | Date Submitted | Last Update |
|---|---|---|---|---|---|
| 0001427 | Database Comparer utility | General | public | 2018-06-04 11:29 | 2019-04-18 19:44 |
| Reporter | shirokov | Assigned To | barry | ||
| Priority | high | Severity | major | Reproducibility | always |
| Status | closed | Resolution | fixed | ||
| Product Version | 7.0.0.1670 | ||||
| Target Version | 7.0.0.1670 | ||||
| Summary | 0001427: Problems with parsing of computed fields of NUMERIC (nn, xx) type, ALTER RDB$ in scripts. | ||||
| Description | The problem apear when computed field are the type numeric(nn, xx). Another problem when master database is an script, reappear erors like this: ALTER TABLE RDB$RELATIONS ADD CONSTRAINT RDB$INDEX_0 UNIQUE (RDB$RELATION_NAME) ALTER TABLE RDB$GENERATORS ADD CONSTRAINT RDB$INDEX_11 UNIQUE (RDB$GENERATOR_NAME) ALTER TABLE RDB$RELATION_CONSTRAINTS ADD CONSTRAINT RDB$INDEX_12 UNIQUE (RDB$CONSTRAINT_NAME) ALTER TABLE RDB$REF_CONSTRAINTS ADD CONSTRAINT RDB$INDEX_13 UNIQUE (RDB$CONSTRAINT_NAME) See the attached scripts. | ||||
| Tags | No tags attached. | ||||
|
|
test.sql (4,032 bytes)
/******************************************************************************/
/**** Generated by IBExpert 2016.12.8.1 31.05.2018 15:17:18 ****/
/******************************************************************************/
/******************************************************************************/
/**** Following SET SQL DIALECT is just for the Database Comparer ****/
/******************************************************************************/
SET SQL DIALECT 3;
/******************************************************************************/
/**** Domains ****/
/******************************************************************************/
CREATE DOMAIN DTEST AS
VARCHAR(100);
CREATE DOMAIN DTEST200 AS
VARCHAR(200);
/******************************************************************************/
/**** Stored procedures ****/
/******************************************************************************/
SET TERM ^ ;
CREATE OR ALTER PROCEDURE GET_VALOARE (
ID INTEGER)
RETURNS (
VALOARE VARCHAR(10))
AS
BEGIN
SUSPEND;
END^
CREATE OR ALTER PROCEDURE TEST_DATE_ADD (
A DATE)
RETURNS (
B DATE)
AS
BEGIN
SUSPEND;
END^
CREATE OR ALTER PROCEDURE TEST_POSITION (
A VARCHAR(100))
RETURNS (
B INTEGER)
AS
BEGIN
SUSPEND;
END^
SET TERM ; ^
/******************************************************************************/
/**** Tables ****/
/******************************************************************************/
CREATE TABLE TEST2 (
ID INTEGER NOT NULL,
NUME VARCHAR(20),
VALOARE NUMERIC(18,4)
);
CREATE TABLE TEST (
ID INTEGER NOT NULL,
IBE$TEST INTEGER,
TEST DTEST200,
TEST2 DTEST
);
CREATE TABLE TEST3 (
ID INTEGER NOT NULL
);
ALTER TABLE TEST ADD CLC_NUME COMPUTED BY ((SELECT NUME FROM TEST2 WHERE TEST.ID=TEST2.ID));
ALTER TABLE TEST ADD CLC_VALOARE NUMERIC(18,4) COMPUTED BY ((SELECT VALOARE FROM TEST2 WHERE TEST2.ID=TEST.ID));
ALTER TABLE TEST3 ADD CLC_VALOARE COMPUTED BY ((SELECT VALOARE FROM GET_VALOARE(TEST3.ID)));
/******************************************************************************/
/**** Primary keys ****/
/******************************************************************************/
ALTER TABLE TEST ADD CONSTRAINT PK_TEST PRIMARY KEY (ID);
ALTER TABLE TEST2 ADD CONSTRAINT PK_TEST2 PRIMARY KEY (ID);
ALTER TABLE TEST3 ADD CONSTRAINT PK_TEST3 PRIMARY KEY (ID);
/******************************************************************************/
/**** Stored procedures ****/
/******************************************************************************/
SET TERM ^ ;
CREATE OR ALTER PROCEDURE GET_VALOARE (
ID INTEGER)
RETURNS (
VALOARE VARCHAR(10))
AS
BEGIN
FOR SELECT T.CLC_VALOARE
FROM TEST T
WHERE T.ID = :ID
INTO :VALOARE
DO
SUSPEND;
END^
CREATE OR ALTER PROCEDURE TEST_DATE_ADD (
A DATE)
RETURNS (
B DATE)
AS
BEGIN
/* */
B = DATEADD(3 MONTH TO :A);
SUSPEND;
END^
CREATE OR ALTER PROCEDURE TEST_POSITION (
A VARCHAR(100))
RETURNS (
B INTEGER)
AS
BEGIN
/* */
B = POSITION(';', :A);
SUSPEND;
END^
SET TERM ; ^
/******************************************************************************/
/**** Privileges ****/
/******************************************************************************/
/* Privileges of users */
GRANT EXECUTE ON PROCEDURE GET_VALOARE TO ION;
GRANT EXECUTE ON PROCEDURE GET_VALOARE TO REPL;
test_dbcomp.sql (6,796 bytes)
SET SQL DIALECT 3;
/*
CREATE DATABASE 'y:\ib.gdb\TEST1.FDB'
USER 'sysdba' PASSWORD 'masterkey' DEFAULT CHARACTER SET NONE;
*/
CREATE DOMAIN DTEST AS VARCHAR(100);
CREATE DOMAIN DTEST200 AS VARCHAR(200);
CREATE TABLE TEST(ID INTEGER NOT NULL,
IBE$TEST INTEGER,
TEST DTEST200,
TEST2 DTEST,
CLC_NUME COMPUTED BY ((SELECT NUME FROM TEST2 WHERE TEST.ID=TEST2.ID)),
CLC_VALOARE COMPUTED BY ((SELECT VALOARE FROM TEST2 WHERE TEST2.ID=TEST.ID)));
CREATE TABLE TEST2(ID INTEGER NOT NULL,
NUME VARCHAR(20),
VALOARE NUMERIC(18,4));
CREATE TABLE TEST3(ID INTEGER NOT NULL,
CLC_VALOARE COMPUTED BY ((SELECT VALOARE FROM GET_VALOARE(TEST3.ID))));
/* PK_TEST */
ALTER TABLE TEST ADD CONSTRAINT PK_TEST PRIMARY KEY (ID);
/* PK_TEST2 */
ALTER TABLE TEST2 ADD CONSTRAINT PK_TEST2 PRIMARY KEY (ID);
/* PK_TEST3 */
ALTER TABLE TEST3 ADD CONSTRAINT PK_TEST3 PRIMARY KEY (ID);
/* RDB$INDEX_0 */
ALTER TABLE RDB$RELATIONS ADD CONSTRAINT RDB$INDEX_0 UNIQUE (RDB$RELATION_NAME);
/* RDB$INDEX_11 */
ALTER TABLE RDB$GENERATORS ADD CONSTRAINT RDB$INDEX_11 UNIQUE (RDB$GENERATOR_NAME);
/* RDB$INDEX_12 */
ALTER TABLE RDB$RELATION_CONSTRAINTS ADD CONSTRAINT RDB$INDEX_12 UNIQUE (RDB$CONSTRAINT_NAME);
/* RDB$INDEX_13 */
ALTER TABLE RDB$REF_CONSTRAINTS ADD CONSTRAINT RDB$INDEX_13 UNIQUE (RDB$CONSTRAINT_NAME);
/* RDB$INDEX_15 */
ALTER TABLE RDB$RELATION_FIELDS ADD CONSTRAINT RDB$INDEX_15 UNIQUE (RDB$FIELD_NAME, RDB$RELATION_NAME);
/* RDB$INDEX_17 */
ALTER TABLE RDB$FILTERS ADD CONSTRAINT RDB$INDEX_17 UNIQUE (RDB$INPUT_SUB_TYPE, RDB$OUTPUT_SUB_TYPE);
/* RDB$INDEX_18 */
ALTER TABLE RDB$PROCEDURE_PARAMETERS ADD CONSTRAINT RDB$INDEX_18 UNIQUE (RDB$PACKAGE_NAME, RDB$PROCEDURE_NAME, RDB$PARAMETER_NAME);
/* RDB$INDEX_19 */
ALTER TABLE RDB$CHARACTER_SETS ADD CONSTRAINT RDB$INDEX_19 UNIQUE (RDB$CHARACTER_SET_NAME);
/* RDB$INDEX_2 */
ALTER TABLE RDB$FIELDS ADD CONSTRAINT RDB$INDEX_2 UNIQUE (RDB$FIELD_NAME);
/* RDB$INDEX_20 */
ALTER TABLE RDB$COLLATIONS ADD CONSTRAINT RDB$INDEX_20 UNIQUE (RDB$COLLATION_NAME);
/* RDB$INDEX_21 */
ALTER TABLE RDB$PROCEDURES ADD CONSTRAINT RDB$INDEX_21 UNIQUE (RDB$PACKAGE_NAME, RDB$PROCEDURE_NAME);
/* RDB$INDEX_22 */
ALTER TABLE RDB$PROCEDURES ADD CONSTRAINT RDB$INDEX_22 UNIQUE (RDB$PROCEDURE_ID);
/* RDB$INDEX_23 */
ALTER TABLE RDB$EXCEPTIONS ADD CONSTRAINT RDB$INDEX_23 UNIQUE (RDB$EXCEPTION_NAME);
/* RDB$INDEX_24 */
ALTER TABLE RDB$EXCEPTIONS ADD CONSTRAINT RDB$INDEX_24 UNIQUE (RDB$EXCEPTION_NUMBER);
/* RDB$INDEX_25 */
ALTER TABLE RDB$CHARACTER_SETS ADD CONSTRAINT RDB$INDEX_25 UNIQUE (RDB$CHARACTER_SET_ID);
/* RDB$INDEX_26 */
ALTER TABLE RDB$COLLATIONS ADD CONSTRAINT RDB$INDEX_26 UNIQUE (RDB$COLLATION_ID, RDB$CHARACTER_SET_ID);
/* RDB$INDEX_32 */
ALTER TABLE RDB$TRANSACTIONS ADD CONSTRAINT RDB$INDEX_32 UNIQUE (RDB$TRANSACTION_ID);
/* RDB$INDEX_39 */
ALTER TABLE RDB$ROLES ADD CONSTRAINT RDB$INDEX_39 UNIQUE (RDB$ROLE_NAME);
/* RDB$INDEX_44 */
ALTER TABLE RDB$BACKUP_HISTORY ADD CONSTRAINT RDB$INDEX_44 UNIQUE (RDB$BACKUP_LEVEL, RDB$BACKUP_ID);
/* RDB$INDEX_45 */
ALTER TABLE RDB$FILTERS ADD CONSTRAINT RDB$INDEX_45 UNIQUE (RDB$FUNCTION_NAME);
/* RDB$INDEX_46 */
ALTER TABLE RDB$GENERATORS ADD CONSTRAINT RDB$INDEX_46 UNIQUE (RDB$GENERATOR_ID);
/* RDB$INDEX_47 */
ALTER TABLE RDB$PACKAGES ADD CONSTRAINT RDB$INDEX_47 UNIQUE (RDB$PACKAGE_NAME);
/* RDB$INDEX_5 */
ALTER TABLE RDB$INDICES ADD CONSTRAINT RDB$INDEX_5 UNIQUE (RDB$INDEX_NAME);
/* RDB$INDEX_53 */
ALTER TABLE RDB$FUNCTIONS ADD CONSTRAINT RDB$INDEX_53 UNIQUE (RDB$FUNCTION_ID);
/* RDB$INDEX_7 */
ALTER TABLE RDB$SECURITY_CLASSES ADD CONSTRAINT RDB$INDEX_7 UNIQUE (RDB$SECURITY_CLASS);
/* RDB$INDEX_8 */
ALTER TABLE RDB$TRIGGERS ADD CONSTRAINT RDB$INDEX_8 UNIQUE (RDB$TRIGGER_NAME);
/* RDB$INDEX_9 */
ALTER TABLE RDB$FUNCTIONS ADD CONSTRAINT RDB$INDEX_9 UNIQUE (RDB$PACKAGE_NAME, RDB$FUNCTION_NAME);
SET TERM ^ ;
CREATE PROCEDURE GET_VALOARE(ID INTEGER)
RETURNS(VALOARE VARCHAR(10))
AS
BEGIN
FOR SELECT T.CLC_VALOARE
FROM TEST T
WHERE T.ID = :ID
INTO :VALOARE
DO
SUSPEND;
END
^
CREATE PROCEDURE TEST_DATE_ADD(A DATE)
RETURNS(B DATE)
AS
BEGIN
/* */
B = DATEADD(3 MONTH TO :A);
SUSPEND;
END
^
CREATE PROCEDURE TEST_POSITION(A VARCHAR(100))
RETURNS(B INTEGER)
AS
BEGIN
/* */
B = POSITION(';', :A);
SUSPEND;
END
^
SET TERM ; ^
GRANT EXECUTE ON PROCEDURE GET_VALOARE TO ION;
GRANT EXECUTE ON PROCEDURE GET_VALOARE TO REPL;
GRANT RDB$ADMIN TO ION;
GRANT SELECT ON MON$ATTACHMENTS TO PUBLIC;
GRANT SELECT ON MON$CALL_STACK TO PUBLIC;
GRANT SELECT ON MON$CONTEXT_VARIABLES TO PUBLIC;
GRANT SELECT ON MON$DATABASE TO PUBLIC;
GRANT SELECT ON MON$IO_STATS TO PUBLIC;
GRANT SELECT ON MON$MEMORY_USAGE TO PUBLIC;
GRANT SELECT ON MON$RECORD_STATS TO PUBLIC;
GRANT SELECT ON MON$STATEMENTS TO PUBLIC;
GRANT SELECT ON MON$TABLE_STATS TO PUBLIC;
GRANT SELECT ON MON$TRANSACTIONS TO PUBLIC;
GRANT SELECT ON RDB$AUTH_MAPPING TO PUBLIC;
GRANT SELECT ON RDB$BACKUP_HISTORY TO PUBLIC;
GRANT SELECT ON RDB$CHARACTER_SETS TO PUBLIC;
GRANT SELECT ON RDB$CHECK_CONSTRAINTS TO PUBLIC;
GRANT SELECT ON RDB$COLLATIONS TO PUBLIC;
GRANT SELECT ON RDB$DATABASE TO PUBLIC;
GRANT SELECT ON RDB$DB_CREATORS TO PUBLIC;
GRANT SELECT ON RDB$DEPENDENCIES TO PUBLIC;
GRANT SELECT ON RDB$EXCEPTIONS TO PUBLIC;
GRANT SELECT ON RDB$FIELD_DIMENSIONS TO PUBLIC;
GRANT SELECT ON RDB$FIELDS TO PUBLIC;
GRANT SELECT ON RDB$FILES TO PUBLIC;
GRANT SELECT ON RDB$FILTERS TO PUBLIC;
GRANT SELECT ON RDB$FORMATS TO PUBLIC;
GRANT SELECT ON RDB$FUNCTION_ARGUMENTS TO PUBLIC;
GRANT SELECT ON RDB$FUNCTIONS TO PUBLIC;
GRANT SELECT ON RDB$GENERATORS TO PUBLIC;
GRANT SELECT ON RDB$INDEX_SEGMENTS TO PUBLIC;
GRANT SELECT ON RDB$INDICES TO PUBLIC;
GRANT SELECT ON RDB$LOG_FILES TO PUBLIC;
GRANT SELECT ON RDB$PACKAGES TO PUBLIC;
GRANT SELECT ON RDB$PAGES TO PUBLIC;
GRANT SELECT ON RDB$PROCEDURE_PARAMETERS TO PUBLIC;
GRANT SELECT ON RDB$PROCEDURES TO PUBLIC;
GRANT SELECT ON RDB$REF_CONSTRAINTS TO PUBLIC;
GRANT SELECT ON RDB$RELATION_CONSTRAINTS TO PUBLIC;
GRANT SELECT ON RDB$RELATION_FIELDS TO PUBLIC;
GRANT SELECT ON RDB$RELATIONS TO PUBLIC;
GRANT SELECT ON RDB$ROLES TO PUBLIC;
GRANT SELECT ON RDB$SECURITY_CLASSES TO PUBLIC;
GRANT SELECT ON RDB$TRANSACTIONS TO PUBLIC;
GRANT SELECT ON RDB$TRIGGER_MESSAGES TO PUBLIC;
GRANT SELECT ON RDB$TRIGGERS TO PUBLIC;
GRANT SELECT ON RDB$TYPES TO PUBLIC;
GRANT SELECT ON RDB$USER_PRIVILEGES TO PUBLIC;
GRANT SELECT ON RDB$VIEW_RELATIONS TO PUBLIC;
GRANT SELECT ON SEC$DB_CREATORS TO PUBLIC;
GRANT SELECT ON SEC$GLOBAL_AUTH_MAPPING TO PUBLIC;
GRANT SELECT ON SEC$USER_ATTRIBUTES TO PUBLIC;
GRANT SELECT ON SEC$USERS TO PUBLIC;
|
|
|
(?) System indices presents in source script: test_dbcomp.sql. |
|
|
Database comparer version is 7.0.0.1670 For the RDB issue the steps are: 1. Use as master script the new attached file test_rdb.sql (from which I removed problem with NUMERIC and Computed BY fields) 2. target is an Firebird 3.0 (WI-V6.3.3.32900) empty databases. 3. Database comparer Switch: DB Objects - ALL objects is checked, FB/IB - Firebird 3.0, ALL checkbox is checked. test_rdb.sql (4,018 bytes)
/******************************************************************************/
/**** Generated by IBExpert 2016.12.8.1 04.06.2018 13:23:03 ****/
/******************************************************************************/
/******************************************************************************/
/**** Following SET SQL DIALECT is just for the Database Comparer ****/
/******************************************************************************/
SET SQL DIALECT 3;
/******************************************************************************/
/**** Domains ****/
/******************************************************************************/
CREATE DOMAIN DTEST AS
VARCHAR(100);
CREATE DOMAIN DTEST200 AS
VARCHAR(200);
/******************************************************************************/
/**** Stored procedures ****/
/******************************************************************************/
SET TERM ^ ;
CREATE OR ALTER PROCEDURE GET_VALOARE (
ID INTEGER)
RETURNS (
VALOARE VARCHAR(10))
AS
BEGIN
SUSPEND;
END^
CREATE OR ALTER PROCEDURE TEST_DATE_ADD (
A DATE)
RETURNS (
B DATE)
AS
BEGIN
SUSPEND;
END^
CREATE OR ALTER PROCEDURE TEST_POSITION (
A VARCHAR(100))
RETURNS (
B INTEGER)
AS
BEGIN
SUSPEND;
END^
SET TERM ; ^
/******************************************************************************/
/**** Tables ****/
/******************************************************************************/
CREATE TABLE TEST2 (
ID INTEGER NOT NULL,
NUME VARCHAR(20),
VALOARE NUMERIC(18,4)
);
CREATE TABLE TEST (
ID INTEGER NOT NULL,
IBE$TEST INTEGER,
TEST DTEST200,
TEST2 DTEST
);
CREATE TABLE TEST3 (
ID INTEGER NOT NULL
);
ALTER TABLE TEST ADD CLC_NUME COMPUTED BY ((SELECT NUME FROM TEST2 WHERE TEST.ID=TEST2.ID));
ALTER TABLE TEST ADD CLC_VALOARE COMPUTED BY ((SELECT VALOARE FROM TEST2 WHERE TEST2.ID=TEST.ID));
ALTER TABLE TEST3 ADD CLC_VALOARE COMPUTED BY ((SELECT VALOARE FROM GET_VALOARE(TEST3.ID)));
/******************************************************************************/
/**** Primary keys ****/
/******************************************************************************/
ALTER TABLE TEST ADD CONSTRAINT PK_TEST PRIMARY KEY (ID);
ALTER TABLE TEST2 ADD CONSTRAINT PK_TEST2 PRIMARY KEY (ID);
ALTER TABLE TEST3 ADD CONSTRAINT PK_TEST3 PRIMARY KEY (ID);
/******************************************************************************/
/**** Stored procedures ****/
/******************************************************************************/
SET TERM ^ ;
CREATE OR ALTER PROCEDURE GET_VALOARE (
ID INTEGER)
RETURNS (
VALOARE VARCHAR(10))
AS
BEGIN
FOR SELECT T.CLC_VALOARE
FROM TEST T
WHERE T.ID = :ID
INTO :VALOARE
DO
SUSPEND;
END^
CREATE OR ALTER PROCEDURE TEST_DATE_ADD (
A DATE)
RETURNS (
B DATE)
AS
BEGIN
/* */
B = DATEADD(3 MONTH TO :A);
SUSPEND;
END^
CREATE OR ALTER PROCEDURE TEST_POSITION (
A VARCHAR(100))
RETURNS (
B INTEGER)
AS
BEGIN
/* */
B = POSITION(';', :A);
SUSPEND;
END^
SET TERM ; ^
/******************************************************************************/
/**** Privileges ****/
/******************************************************************************/
/* Privileges of users */
GRANT EXECUTE ON PROCEDURE GET_VALOARE TO ION;
GRANT EXECUTE ON PROCEDURE GET_VALOARE TO REPL;
|
| Date Modified | Username | Field | Change |
|---|---|---|---|
| 2018-06-04 11:29 | shirokov | New Issue | |
| 2018-06-04 11:29 | shirokov | Status | new => assigned |
| 2018-06-04 11:29 | shirokov | Assigned To | => barry |
| 2018-06-04 11:29 | shirokov | File Added: test.sql | |
| 2018-06-04 11:29 | shirokov | File Added: test_dbcomp.sql | |
| 2018-06-04 11:52 | barry | Note Added: 0003776 | |
| 2018-06-04 20:39 | shirokov | File Added: test_rdb.sql | |
| 2018-06-04 20:39 | shirokov | Note Added: 0003778 | |
| 2019-04-11 23:52 | barry | Status | assigned => resolved |
| 2019-04-11 23:52 | barry | Resolution | open => fixed |
| 2019-04-18 19:44 | barry | Status | resolved => closed |
