View Issue Details

IDProjectCategoryView StatusLast Update
0000709PostgreSQL[All Projects] Generalpublic2019-04-19 14:53
ReportershirokovAssigned To 
PrioritynormalSeverityminorReproducibilityalways
Status newResolutionopen 
PlatformWin32OSWindowsOS Version7
Product Version 
Target VersionFixed in Version 
Summary0000709: Database Comparer 6.1 x Postgre SQL problem
DescriptionDBComparer не всё парсит, в архиве файлик todo.txt, в нём подробно написано, еще в архиве два скрипта - один из них (pgmetadata.sql) у меня парсится без ошибок, а другой (dbcomparer.sql) выдает ошибки, также ошибки, но немного другие появляются если создать базу этим вторым проблемным скриптом и потом компарить - во всех случаях в качестве мастера и таргета используется один и тот же скрипт, а в последнем случае - одна и та же база.
TagsNo tags attached.

Activities

shirokov

2014-09-23 01:07

administrator  

dbc_postgresql.zip (11,852 bytes)

shirokov

2014-09-23 01:11

administrator   ~0001730

Информация по поводу верси сервера:

1) Problem in PostgreSQL – version higher than 8.2.23 (ex. 8.4.18). Like e-mails before, probably cannot connect to server db. So app, stops to work.

2) Trying compare metadata PostgreSQL (master and target files are the same file)

a. Command CREATE SEQUENCE couldn’t be extract (connect to server) and when extracted (metadata file) is not recognized.

b. Command CREATE TYPE there is no information in schema (if is “public “ or not):

                                                               i. Ex: CREATE TYPE “schema_name”.type_name AS…

c. Command CREATE RULE cannot send the schema name together with rule name, must be together only with the object name that rule will use.

                                                               i. Ex: CREATE RULE schema_name.rule_name AS ON operation TO schema_name.view_name (wrong)

                                                             ii. CREATE RULE rule_name AS ON operation TO schema_name.view_name… (ok, is right)

d. Command DROP RULE, the same condition letter “C”, the sintax right is: DROP_RULE rule_name ON schema_name.view_name

e. Command CREATE FUNCTION that has input parameters with word-key “IN” was not working, are identify “sintax error”

f. Command CREATE FUNCTION that has return (RETURN [SETOF]….) a type (CREATE TYPE) before created, are identify “sintax error”

g. Command CREATE FUNCTION (sometimes has a problem, but sometimes not) that has a language together declare (CREATE FUNCTION …. RETURNS… LANGUAGE plpgsql AS $$ …$$’), are identify “sintax error” (probably when found the word-key “AS”)

h. Command ALTER FUNCTION has “sintax error” too. Command ALTER FUNCTION that wants change the function must be CREATE OR REPLACE FUNCTION, because the sintax ALTER FUNCTION is used to the other situation. (about that, please check http://www.postgresql.org/docs/8.4/static/sql-alterfunction.html).

shirokov

2014-09-29 13:51

administrator  

metadata.zip (3,953 bytes)

shirokov

2014-09-29 13:53

administrator   ~0001741

Скрипт из архива metadata.zip парсится без ошибок, однако если создать им базу и попробовать скомпарить, появляются ошибки. Оригинальный текст письма:

Test in: Dabase Comparer 6.1.0.1651

File used to create a database (in annex): metadata.sql

============================================================================

DOMAINs:

* (1) cannot extract the standard value of domains (CREATE DOMAIN ... DEFAULT ...)

Ex: CREATE DOMAIN dm_numero integer default 0;


RULEs:

* (2) cannot extract the rules (CREATE RULE ...)

Ex: CREATE RULE rl_teste_insert AS ON INSERT TO vi_teste DO INSTEAD INSERT INTO tb_teste (id, info) VALUES ( <http://new.id> new.id, <http://new.info> new.info);

Obs 1: the command CREATE RULE is not say the squema name together a rule name, but together at object that rules belong. Sintax: CREATE RULE rule_name AS ON operation TO schema_name.object_name ...

Obs 2: the same sintax must be use to command DROP RULE. Sintax: DROP RULE rule_name ON schema_name.object_name


TYPEs:

* (3) the command CREATE TYPE is not declare the squema name

Ex: CREATE TYPE teste.tp_teste AS (cod integer, nome varchar(200));

Obs: if need not declare the squema together with command CREATE TYPE, must execute before the command SET search_path

Ex: SET search_path = teste, pg_catalog; CREATE TYPE tp_teste AS (cod integer, nome varchar(200));


SEQUENCEs:

* (4) the command CREATE SEQUENCE is not being extract

Ex: CREATE SEQUENCE sq_teste START WITH 1 INCREMENT BY 1 CACHE 1;


FUNCTIONs:

* (5) is not extract the parameter that was before another parameter with operator “IN” at declare (CREATE FUNCTION e ALTER FUNCTION)

Ex 1: CREATE FUNCTION fn_teste_in(IN iid integer, OUT oid integer, OUT oinfo character varying) RETURNS SETOF record ... está retornando CREATE FUNCTION teste.fn_teste_in(in iid integer) RETURNS SETOF record ...

Ex 2: ALTER FUNCTION teste.fn_teste_in(IN iid integer, OUT oid integer, OUT oinfo character varying) OWNER TO postgres;


* (6) commands CREATE FUNCTION that returns (RETURNS [SETOF] ...) a type (CREATE TYPE) is returning "SETOF ? datatype"

Ex: CREATE FUNCTION teste.fn_teste_type(iid integer) RETURNS SETOF tp_teste ... está retornando CREATE FUNCTION teste.fn_teste_type(iid integer) RETURNS SETOF ? datatype ...


* (7) error in command ALTER FUNCTION. Commands ALTER FUNCTION that change the rules of function must be used the sintax CREATE OR REPLACE FUNCTION, because the sintax ALTER FUNCTION is used to another case (more information at: <http://www.postgresql.org/docs/8.4/static/sql-alterfunction.html> http://www.postgresql.org/docs/8.4/static/sql-alterfunction.html)

Ex: CREATE OR REPLACE FUNCTION teste.fn_teste_type(iid integer) ... and not ALTER FUNCTION teste.fn_teste_type(iid integer) ...


Priority order: (2), (7), (1), (5), (6), (3), (4)

Issue History

Date Modified Username Field Change
2014-09-23 01:07 shirokov New Issue
2014-09-23 01:07 shirokov File Added: dbc_postgresql.zip
2014-09-23 01:11 shirokov Note Added: 0001730
2014-09-29 13:51 shirokov File Added: metadata.zip
2014-09-29 13:53 shirokov Note Added: 0001741
2019-04-19 14:53 barry Project Database Comparer utility => PostgreSQL