View Issue Details
| ID | Project | Category | View Status | Date Submitted | Last Update |
|---|---|---|---|---|---|
| 0000355 | Database Comparer | General | public | 2012-07-18 19:04 | 2012-11-29 09:06 |
| Reporter | barry | Assigned To | barry | ||
| Priority | normal | Severity | minor | Reproducibility | have not tried |
| Status | closed | Resolution | fixed | ||
| Summary | 0000355: Выгрузка структуры БД в отдельные файлы. | ||||
| Description | http://www.clevercomponents.com/dev/dbc/support/dbscriptseparated.txt Реализация для утилиты DB Comparer. | ||||
| Tags | No tags attached. | ||||
|
|
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; |
|
|
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 |
| 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 |