Project

General

Profile

Feature #8893

Make DDL code generated by FWD matching the SQL dump for respective dialect

Added by Ovidiu Maxiniuc 12 days ago. Updated 12 days ago.

Status:
New
Priority:
Normal
Assignee:
-
Target version:
-
Start date:
Due date:
% Done:

0%

billable:
No
vendor_id:
GCD
version_reported:
version_resolved:

History

#1 Updated by Ovidiu Maxiniuc 12 days ago

The idea is to generate the code as much as possible identical with the DDLs generated by the RDBMSs. The main reason for this is in case that
  • the original DDL is lost or
  • the database evolved (we noticed this lately) by manually adding tables and columns,

having the DDL statements more compatible would make the compare operation much simpler and faster.

This should have no changes in the semantics of the statements, just make sure the casing and order of options of each statement are the same. In some cases, we generate synonyms or equivalent for data types, too. These are simple operations of replacing some string or the order in which they are emitted and should be, generally, risk free.

Here is some differences I noticed:
FWD generated PostgreSQL dump Observations/Questions
create table abc ( CREATE TABLE public.abc ( Keywords are capitalized;
Should we also use the public schema?
recid int8 not null, recid bigint NOT NULL, Keywords are capitalized;
Usage of bigint instead of int8 datatype;
f1 int4 not null, f1 integer NOT NULL, see above;
f2 bool not null, f2 boolean NOT NULL, see above;
f3 timestamp, f3 timestamp without time zone, Explicit without time zone for timestap
timestamp text, "timestamp" text, timestamp seems to be a keyword although not enforced. Probably it's best to avoid using it as column identifier;
position int4, "position" integer NOT NULL, same as above for position;
primary key (recid) - This puzzles me a bit. Does PSQL assume the first column (recid) to be the PK by default? 
create index fkidx__abc__keyindex on \
abc__keyindex (parent__id);
 
CREATE INDEX fkidx__abc__keyindex ON \
public.abc__keyindex USING btree (parent__id);
 see above;
both single-lines, spit to make compare operation easier
psql uses explicit USING btree option;		 
create index idx__abc_idx_f1_f2_f3 \
on activities \
(f1, f2, upper(rtrim(f3)));
 
CREATE INDEX idx__abc_f1_f2_f3 \
ON public.activities USING btree \
(f1, f2, upper(rtrim(f3)));
 see above;
The name of the index seems to be altered/regenerated different by the import utility; 
alter table abc__keyindex
   add constraint fk__abc__keyindex
   foreign key (parent__id)
   references abc
   on delete cascade;
 
ALTER TABLE ONLY public.abc__keyindex
    ADD CONSTRAINT fk__abc__keyindex \
FOREIGN KEY (parent__id) \
REFERENCES public.abc(recid) \
ON DELETE CASCADE;
 see above;
the recid column for reference is explicitly mentioned; 
create sequence seq1 \
increment 1 \
minvalue 1 \
maxvalue 999999999 \
start 1 \
cycle \
cache 1;
 
CREATE SEQUENCE public.seq1
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    MAXVALUE 999999999
    CACHE 1
    CYCLE;
 - the capitalization is different;
- the order of options is different;
- the options have additional 'tokens' like: start / START WITH or increment / INCREMENT BY;
- PSQL is multiline (I split FWD variant to make them easier to compare);
- minvalue 1 is converted to NO MINVALUE. Actually this is the only one which may have semantic differences;
- maxvalue 9223372036854775807 is equivalent to NO MAXVALUE.

I will add similar tables with differences for other dialects only if this task is NOT rejected.

#2 Updated by Ovidiu Maxiniuc 12 days ago

Ovidiu Maxiniuc wrote:

primary key (recid) - This puzzles me a bit. Does PSQL assume the first column (recid) to be the PK by default?

I found the cause. The PKs are defined in the dump file, at the end, after all data, for all tables was processed:

ALTER TABLE ONLY public.abc
    ADD CONSTRAINT abc_pkey PRIMARY KEY (recid);
This raises a new question: if psql uses this syntax, it might have performance implications, in the same way we also apply the indices after all data is imported. After all, PK is also an UNIQUE index, so it makes sense to skip it when the tables are declared and emit the DDL for it together with the other indices.

Also available in: Atom PDF