View Issue Details

IDProjectCategoryView StatusLast Update
0000355Database ComparerGeneralpublic2012-11-29 09:06
Reporterbarry Assigned Tobarry  
PrioritynormalSeverityminorReproducibilityhave not tried
Status closedResolutionfixed 
Summary0000355: Выгрузка структуры БД в отдельные файлы.
Descriptionhttp://www.clevercomponents.com/dev/dbc/support/dbscriptseparated.txt
Реализация для утилиты DB Comparer.
TagsNo tags attached.

Activities

barry

2012-07-18 19:05

administrator  

dbscriptseparated.txt (3,813 bytes)   
I will use the files to store my database DDL on svn and compare
versions with beyond compare.
It is much easier to use BC folder and file compare instead one single
file with many megabytes (complete extract).
So we will compare database versions, database instances (each
customer has an instance of our database).
It is also easier to database procedure developers to post on svn only
the files their are working on using a dump/downloader from their
development database.
But ISQL produces only a very large single file.
http://tracker.firebirdsql.org/browse/CORE-3685

I've tested your compare database utility. It looks awesome, but for
us is a bit of unusable because it doesn't use the CREATE OR ALTER
VIEW command for FB 2.5. And dropping all view dependencies would
create a too huge script file.
Our database make heavy use of editable view and procedures to enforce
business rules and on some tests a change on a single procedure with a
dependent view lead to lots of dropping and recreating.

Today I already use my own procedures to achieve the DDL on SVN feature.
procedure_PROCEDURENAME_header.sql
procedure_PROCEDURENAME_body.sql
trigger_TRIGGERNAME_header.sql
trigger_TRIGGERNAME_body.sql
view_VIEWNAME_body.sql

Take a look. The field PROCEDURE_EMPTY is saved in a file
XXX_header.sql and the field PROCEDURE_FULL is saved in a file
XXX_body.

create or alter procedure DYPEDEV_PROCEDURE_DOWNLOAD (
 PROCEDURE_TO_DOWNLOAD varchar(255))
returns (
 PROCEDURE_NAME varchar(255),
 PROCEDURE_EMPTY blob sub_type 1 segment size 80,
 PROCEDURE_FULL blob sub_type 1 segment size 80)
AS
declare PARAM varchar(255);
declare PARAM_NO integer;
declare PARAM_QTD integer;
declare PARAM_TYPE varchar(255);
declare PROCEDURE_TYPE smallint;
begin
 PROCEDURE_NAME = trim(PROCEDURE_TO_DOWNLOAD);
 PARAM_QTD = coalesce((select count(*) from RDB$PROCEDURE_PARAMETERS
PP where PP.RDB$PROCEDURE_NAME = :PROCEDURE_NAME and
PP.RDB$PARAMETER_TYPE = 0), 0);
 PROCEDURE_EMPTY = 'create or alter procedure ' || PROCEDURE_NAME;
 if (PARAM_QTD > 0) then
 begin
 for select
 PARAMETER_NAME,
 PARAMETER_NUMBER,
 PARAMETER_SOURCE
 from
 DYPEDEV_PROCEDURE_GET_PARAM(:PROCEDURE_NAME, 0)
 order by
 PARAMETER_NUMBER
 into
 :PARAM,
 :PARAM_NO,
 :PARAM_TYPE
 do
 begin
 if (PARAM_NO = 0) then
 PROCEDURE_EMPTY = PROCEDURE_EMPTY || ' (
';
 PROCEDURE_EMPTY = PROCEDURE_EMPTY || ' ' || PARAM || ' ' || PARAM_TYPE;
 if (PARAM_NO <> PARAM_QTD - 1) then
 PROCEDURE_EMPTY = PROCEDURE_EMPTY || ',
';
 if (PARAM_NO = PARAM_QTD - 1) then
 PROCEDURE_EMPTY = PROCEDURE_EMPTY || ')';
 end
 end
 PROCEDURE_EMPTY = PROCEDURE_EMPTY || '
';
 PARAM_QTD = coalesce((select count(*) from RDB$PROCEDURE_PARAMETERS
PP where PP.RDB$PROCEDURE_NAME = :PROCEDURE_NAME and
PP.RDB$PARAMETER_TYPE = 1), 0);
 if (PARAM_QTD > 0) then
 begin
 PROCEDURE_EMPTY = PROCEDURE_EMPTY || 'returns (
';
 for select
 PARAMETER_NAME,
 PARAMETER_NUMBER,
 PARAMETER_SOURCE
 from
 DYPEDEV_PROCEDURE_GET_PARAM(:PROCEDURE_NAME, 1)
 order by
 PARAMETER_NUMBER
 into
 :PARAM,
 :PARAM_NO,
 :PARAM_TYPE
 do
 begin
 PROCEDURE_EMPTY = PROCEDURE_EMPTY || ' ' || PARAM || ' ' || PARAM_TYPE;
 if (PARAM_NO <> PARAM_QTD - 1) then
 PROCEDURE_EMPTY = PROCEDURE_EMPTY || ',
';
 if (PARAM_NO = PARAM_QTD - 1) then
 PROCEDURE_EMPTY = PROCEDURE_EMPTY || ')
';
 end
 end

 PROCEDURE_EMPTY = PROCEDURE_EMPTY || 'as
';

 select
 P.RDB$PROCEDURE_TYPE,
 P.RDB$PROCEDURE_SOURCE
 from
 RDB$PROCEDURES P
 where
 P.RDB$PROCEDURE_NAME = :PROCEDURE_NAME
 into
 :PROCEDURE_TYPE,
 :PROCEDURE_FULL;

 PROCEDURE_FULL = PROCEDURE_EMPTY || PROCEDURE_FULL || ';';
 PROCEDURE_EMPTY = PROCEDURE_EMPTY || 'begin
' || decode(PROCEDURE_TYPE, 2, '', 'suspend;
') || 'end;';

 suspend;
end;
dbscriptseparated.txt (3,813 bytes)   

barry

2012-08-07 00:28

administrator   ~0000753

I've tested the new functionality and it works as expected.

Some thoughts for consideration:
* In order to extract metadata works, I must set a target database on
compare tab. I tried only open and click the new menu option.
* On new alias screen the labels for DB Charset and Role are overlapping.
* It would be nice to allow extraction to different folders, for
different objects.

For procedures, triggers and views extraction I've upgraded my
procedures and created a little console application which extracts
this objects for me.
But I will order a personal license for your stand alone version. It
is way more handy for comparing and creating delta scripts.

I found a bug on comparing metadata where a computed by field depends
on a selectable stored procedure. But couldn't yet create a smaller
test case.

Thanks a lot!

Valdir

Issue History

Date Modified Username Field Change
2012-07-18 19:04 barry New Issue
2012-07-18 19:05 barry File Added: dbscriptseparated.txt
2012-07-18 19:06 barry Description Updated View Revisions
2012-08-07 00:28 barry Note Added: 0000753
2012-11-29 09:06 barry Status new => closed
2012-11-29 09:06 barry Assigned To => barry
2012-11-29 09:06 barry Resolution open => fixed