Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

ISQL extract metadata one file per object [CORE3685] #4034

Open
firebird-automations opened this issue Dec 2, 2011 · 3 comments
Open

ISQL extract metadata one file per object [CORE3685] #4034

firebird-automations opened this issue Dec 2, 2011 · 3 comments

Comments

@firebird-automations
Copy link
Collaborator

Submitted by: Valdir Stiebe Junior (ogecrom)

Votes: 1

It would be very helpful if we could extract the metadata on separated files for each object.

The command line could be: isql <database> -xf <target folder> -u sysdba -p masterkey
The result would be text files on target folder with a file for each table/stored procedure

TargetFolder/Domain DOMAIN_1.sql
TargetFolder/Table TABLE_1.sql
TargetFolder/Table TABLE_2.sql
TargetFolder/Trigger TABLE_1_BI.sql
TargetFolder/Procedure STP_1.sql
TargetFolder/Grants.sql

An extended version could use a folder structure to represent namespaces and types of objects.

TargetFolder/Domains/DOMAIN_1.sql
TargetFolder/Tables/TABLE_1.sql

And so on.

I see two main use cases for this feature.
1. Allow store DDL on SVN (or any source code versioning system) repositories;
2. Allow easy database metadata comparison with tools like beyond compare, tortoise merge etc;

Please point me to the right direction if this is already possible with ISQL.

@firebird-automations
Copy link
Collaborator Author

Commented by: Frank Schlottmann-Goedde (fsg)

Not sure whether it is still helpful, but I have an ancient patch (4 years old) that exactly did this (and a bit more). I never found the time to integrate it into the main tree. Anyway here it is:

? isql.diff
? isql.patch
Index: extract.epp

RCS file: /cvsroot/firebird/firebird2/src/isql/extract.epp,v
retrieving revision 1.80
diff -u -w -r1.80 extract.epp
--- extract.epp 28 Mar 2007 04:20:06 -0000 1.80
+++ extract.epp 22 Aug 2007 15:09:11 -0000
@@ -1,3 +1,4 @@
+
/*
* PROGRAM: Interactive SQL utility
* MODULE: extract.epp
@@ -68,6 +69,8 @@
static void list_all_grants();
static processing_state list_all_grants2(bool, const SCHAR*);
static void list_all_procs();
+static void list_all_proc_stubs();
+static void list_all_proc_bodies();
static void list_all_tables(LegacyTables flag, SSHORT);
static void list_all_triggers();
static void list_check();
@@ -192,6 +195,7 @@
list_functions();
list_generators();
list_domains(default_char_set_id);
+ list_all_proc_stubs();
list_all_tables(flag, default_char_set_id);
list_index();
list_foreign();
@@ -199,6 +203,7 @@
list_check();
list_exception();
list_all_procs();
+ list_all_proc_bodies();
list_all_triggers();
list_all_grants();
SHOW_comments(false); // Let's make this an option later.
@@ -398,9 +403,11 @@
{
ISQL_copy_SQL_id (fb_utils::exact_name(RFR.RDB$FIELD_NAME),
SQL_identifier, DBL_QUOTE);
+ if (!isqlGlob.cvs || FLD.RDB$COMPUTED_BLR.NULL)
isqlGlob.printf("%s ", SQL_identifier);
}
else
+ if (!isqlGlob.cvs || FLD.RDB$COMPUTED_BLR.NULL)
isqlGlob.printf("%s ", fb_utils::exact_name(RFR.RDB$FIELD_NAME));

	/\*

@@ -412,9 +419,15 @@

	if \(\!FLD\.RDB$COMPUTED\_BLR\.NULL\)
	\{

+ //FSG: there may be computed fields that depend on a table that will be extracted later
+ //so postpone them all.
+ if (isqlGlob.cvs)
+ isqlGlob.printf("/* %s ", fb_utils::exact_name(RFR.RDB$FIELD_NAME));
isqlGlob.printf("COMPUTED BY ");
if (!FLD.RDB$COMPUTED_SOURCE.NULL)
ISQL_print_validation (isqlGlob.Out, &FLD.RDB$COMPUTED_SOURCE, true, gds_trans);
+ if (isqlGlob.cvs)
+ isqlGlob.printf("%s */ --","(postponed to computed.sql)");
}
else if (!(fb_utils::implicit_domain(FLD.RDB$FIELD_NAME) && FLD.RDB$SYSTEM_FLAG != 1))
{
@@ -693,7 +706,77 @@
{
isqlGlob.printf(")");
}
+
+ isqlGlob.printf(")%s%s", isqlGlob.global_Term, NEWLINE);
+ return FINI_OK;
+}
+
+int list_computed(const SCHAR* relation_name,
+ const SCHAR* new_name)
+{
+/**************************************
+ *
+ * l i s t _ c o m p u t e d
+ *
+ **************************************
+ *
+ * Functional description
+ * Shows computed columns for a given table name
+ * Use a GDML query to get the info and print it.
+ * If a new_name is passed, substitute it for relation_name
+ *
+ * relation_name -- Name of table to investigate
+ * new_name -- Name of a new name for a replacement table
+ * default_char_set_id -- character set def to supress
+ *
+ **************************************/
+
+ bool first = true;
+
+
+// Query to obtain relation detail information
+
+ FOR REL IN RDB$RELATIONS CROSS
+ RFR IN RDB$RELATION_FIELDS CROSS
+ FLD IN RDB$FIELDS WITH
+ RFR.RDB$FIELD_SOURCE EQ FLD.RDB$FIELD_NAME AND
+ RFR.RDB$RELATION_NAME EQ REL.RDB$RELATION_NAME AND
+ REL.RDB$RELATION_NAME EQ relation_name
+ SORTED BY RFR.RDB$FIELD_POSITION, RFR.RDB$FIELD_NAME
+
+ if (!FLD.RDB$COMPUTED_BLR.NULL)
+ {
+ SSHORT collation = 0;
+ SSHORT char_set_id = 0;
+ fb_utils::exact_name(REL.RDB$OWNER_NAME);
+ if (isqlGlob.db_SQL_dialect > SQL_DIALECT_V6_TRANSITION)
+ {
+ if (new_name)
+ ISQL_copy_SQL_id (new_name, SQL_identifier, DBL_QUOTE);
+ else
+ ISQL_copy_SQL_id (relation_name, SQL_identifier, DBL_QUOTE);
+ isqlGlob.printf("ALTER TABLE %s ", SQL_identifier);
+ }
+ else
+ isqlGlob.printf("ALTER TABLE %s ",
+ new_name ? new_name : relation_name);
+ if (isqlGlob.db_SQL_dialect > SQL_DIALECT_V6_TRANSITION)
+ {
+ ISQL_copy_SQL_id (fb_utils::exact_name(RFR.RDB$FIELD_NAME),
+ SQL_identifier, DBL_QUOTE);
+ isqlGlob.printf("ADD %s ", SQL_identifier);
+ }
+ else
+ isqlGlob.printf("%s ", fb_utils::exact_name(RFR.RDB$FIELD_NAME));
+ isqlGlob.printf("COMPUTED BY ");
+ ISQL_print_validation (isqlGlob.Out, &FLD.RDB$COMPUTED_SOURCE, true, gds_trans);
isqlGlob.printf("%s%s", isqlGlob.global_Term, NEWLINE);
+ }
+ END_FOR
+ ON_ERROR
+ ISQL_errmsg(gds_status);
+ return FINI_ERROR;
+ END_ERROR;
return FINI_OK;
}

@@ -1009,11 +1092,26 @@
**************************************/
bool first_role = true;
TEXT prev_owner[44];
-
+ FILE* const holdout = isqlGlob.Out;
// Process GRANT roles
if (isqlGlob.major_ods >= ODS_VERSION9 && show_role_list)
{
prev_owner[0] = '\0';
+ if (isqlGlob.cvs)
+ {
+
+ //FSG: put all grant statements into one file
+ isqlGlob.printf("%s/* Grant roles for this database */%s",
+ NEWLINE,
+ NEWLINE);
+ first_role = false;
+ isqlGlob.printf("INPUT GRANTS.SQL;%s",
+ NEWLINE);
+ TEXT outname[MAXPATHLEN];
+ strcpy(outname, isqlGlob.OutPath);
+ strcat(outname,"GRANTS.SQL");
+ isqlGlob.Out = fopen(outname,"w");
+ }

	FOR XX IN RDB$ROLES
		SORTED BY XX\.RDB$ROLE\_NAME

@@ -1116,6 +1214,13 @@
return OBJECT_NOT_FOUND;
END_ERROR;

+
+
+ if (isqlGlob.cvs)
+ {
+ fclose(isqlGlob.Out);
+ isqlGlob.Out=holdout;
+ }
return first_role && first ? OBJECT_NOT_FOUND : SKIP;
}

@@ -1129,19 +1234,43 @@
**************************************
*
* Functional description
- * Shows text of a stored procedure given a name.
- * or lists procedures if no argument.
+ * Lists all procedures.
* Since procedures may reference each other, we will create all
* dummy procedures of the correct name, then alter these to their
* correct form.
* Add the parameter names when these procedures are created.
*
- * procname -- Name of procedure to investigate
*
**************************************/
+ list_all_proc_stubs();
+ list_all_proc_bodies();
+}
+
+
+
+static void list_all_proc_stubs()
+{
+/*****************************************
+ *
+ * l i s t _ a l l _ p r o c _ s t u b s
+ *
+ *****************************************
+ *
+ * Functional description
+ * Create all procedure declarations
+ * with empty body. This will allow us to create tables
+ * that depend on them. The alteration to their
+ * correct form is postponed to list_all_proc_bodies.
+ * Add the parameter names when these procedures are created.
+ *
+ *
+ *
+ *****************************************/
+
bool header = true;
- static const SCHAR* create_procedure_str1 = "CREATE PROCEDURE %s ";
+ static const SCHAR* create_procedure_str1 = "CREATE OR ALTER PROCEDURE %s ";
static const SCHAR* create_procedure_str2 = "BEGIN EXIT; END %s%s";
+ FILE* const holdout = isqlGlob.Out;

//  First the dummy procedures

@@ -1149,6 +1278,21 @@

FOR PRC IN RDB$PROCEDURES
	SORTED BY PRC\.RDB$PROCEDURE\_NAME

+
+ fb_utils::exact_name(PRC.RDB$PROCEDURE_NAME);
+ if (isqlGlob.cvs)
+ {
+ isqlGlob.printf("INPUT %s.SPS;%s",
+ PRC.RDB$PROCEDURE_NAME,
+ NEWLINE);
+ TEXT outname[MAXPATHLEN];
+
+ strcpy(outname, isqlGlob.OutPath);
+ strcat(outname,PRC.RDB$PROCEDURE_NAME);
+ strcat(outname,".SPS");
+ isqlGlob.Out = fopen(outname,"w");
+ header = true;
+ }
if (header)
{
isqlGlob.printf("COMMIT WORK;%s", NEWLINE);
@@ -1157,7 +1301,6 @@
isqlGlob.printf("%s/* Stored procedures */%s", NEWLINE, NEWLINE);
header = false;
}
- fb_utils::exact_name(PRC.RDB$PROCEDURE_NAME);
if (isqlGlob.db_SQL_dialect > SQL_DIALECT_V6_TRANSITION)
{
ISQL_copy_SQL_id (PRC.RDB$PROCEDURE_NAME, SQL_identifier, DBL_QUOTE);
@@ -1171,26 +1314,84 @@

	get\_procedure\_args \(PRC\.RDB$PROCEDURE\_NAME\);
	isqlGlob\.printf\(create\_procedure\_str2, Procterm, NEWLINE\);

+ if (isqlGlob.cvs)
+ {
+ if (!header)
+ {
+ isqlGlob.printf("SET TERM %s %s%s", isqlGlob.global_Term, Procterm, NEWLINE);
+ isqlGlob.printf("COMMIT WORK %s%s", isqlGlob.global_Term, NEWLINE);
+ isqlGlob.printf("SET AUTODDL ON;%s", NEWLINE);
+ }
+ fclose(isqlGlob.Out);
+ isqlGlob.Out=holdout;
+ }

END\_FOR
ON\_ERROR
	ISQL\_errmsg\(gds\_status\);
	return;
END\_ERROR;

+ // Only reset the terminators is there were procs to print
+ if (!isqlGlob.cvs)
+ {
+ if (!header)
+ {
+ isqlGlob.printf("SET TERM %s %s%s", isqlGlob.global_Term, Procterm, NEWLINE);
+ isqlGlob.printf("COMMIT WORK %s%s", isqlGlob.global_Term, NEWLINE);
+ isqlGlob.printf("SET AUTODDL ON;%s", NEWLINE);
+ }
+ fclose(isqlGlob.Out);
+ isqlGlob.Out=holdout;
+ }
+}
+
+static void list_all_proc_bodies()
+{
+/**************************************
+ *
+ * l i s t _ a l l _ p r o c _ b o d i e s
+ *
+ **************************************
+ *
+ * Functional description
+ * Fill them with life.
+ *
+ **************************************/
+ bool header = true;

// This query gets the procedure name and the source\.  We then nest a query
// to retrieve the parameters\. Alter is used, because the procedures are already there
TEXT msg\[MSG\_LENGTH\];

+ FILE* const holdout = isqlGlob.Out;

+ isqlGlob.printf("%s/* Stored procedure bodies */%s", NEWLINE, NEWLINE);
FOR PRC IN RDB$PROCEDURES
SORTED BY PRC.RDB$PROCEDURE_NAME

	fb\_utils::exact\_name\(PRC\.RDB$PROCEDURE\_NAME\);

-
+ if (isqlGlob.cvs)
+ {
+ isqlGlob.printf("INPUT %s.SPB;%s",
+ PRC.RDB$PROCEDURE_NAME,
+ NEWLINE);
+ TEXT outname[MAXPATHLEN];
+ strcpy(outname, isqlGlob.OutPath);
+ strcat(outname,PRC.RDB$PROCEDURE_NAME);
+ strcat(outname,".SPB");
+ isqlGlob.Out = fopen(outname,"w");
+ header=true;
+ }
+ if (header)
+ {
+ isqlGlob.printf("COMMIT WORK;%s", NEWLINE);
+ isqlGlob.printf("SET AUTODDL OFF;%s", NEWLINE);
+ isqlGlob.printf("SET TERM %s %s%s", Procterm, isqlGlob.global_Term, NEWLINE);
+ header = false;
+ }
if (isqlGlob.db_SQL_dialect > SQL_DIALECT_V6_TRANSITION)
{
ISQL_copy_SQL_id (PRC.RDB$PROCEDURE_NAME, SQL_identifier, DBL_QUOTE);
- isqlGlob.printf("%sALTER PROCEDURE %s ", NEWLINE,
+ isqlGlob.printf("%sCREATE OR ALTER PROCEDURE %s ", NEWLINE,
SQL_identifier);
}
else
@@ -1205,6 +1406,17 @@

	isqlGlob\.printf\(" %s%s", Procterm, NEWLINE\);

+ if (isqlGlob.cvs)
+ {
+ if (!header)
+ {
+ isqlGlob.printf("SET TERM %s %s%s", isqlGlob.global_Term, Procterm, NEWLINE);
+ isqlGlob.printf("COMMIT WORK %s%s", isqlGlob.global_Term, NEWLINE);
+ isqlGlob.printf("SET AUTODDL ON;%s", NEWLINE);
+ }
+ fclose(isqlGlob.Out);
+ isqlGlob.Out=holdout;
+ }
END_FOR
ON_ERROR
ISQL_msg_get(GEN_ERR, msg, SafeArg() << isc_sqlcode(gds_status));
@@ -1212,15 +1424,16 @@
ISQL_errmsg(gds_status);
return;
END_ERROR;
-
- // Only reset the terminators is there were procs to print
+ if (!isqlGlob.cvs)
+ {
if (!header)
{
+ isqlGlob.printf("COMMIT WORK %s%s", Procterm, NEWLINE);
isqlGlob.printf("SET TERM %s %s%s", isqlGlob.global_Term, Procterm, NEWLINE);
- isqlGlob.printf("COMMIT WORK %s%s", isqlGlob.global_Term, NEWLINE);
isqlGlob.printf("SET AUTODDL ON;%s", NEWLINE);
}
}
+}

static void list_all_tables(LegacyTables flag,
@@ -1242,7 +1455,7 @@

/* This version of cursor gets only sql tables identified by security class
and misses views, getting only null view_source */
-
+ FILE* const holdout = isqlGlob.Out;
FOR REL IN RDB$RELATIONS WITH
(REL.RDB$SYSTEM_FLAG NE 1 OR REL.RDB$SYSTEM_FLAG MISSING) AND
REL.RDB$VIEW_BLR MISSING
@@ -1254,16 +1467,60 @@
// Null terminate name string

	fb\_utils::exact\_name\(REL\.RDB$RELATION\_NAME\);

-
+ if (isqlGlob.cvs)
+ {
+ isqlGlob.Out=holdout;
+ isqlGlob.printf("INPUT %s.TBL;%s",
+ REL.RDB$RELATION_NAME,
+ NEWLINE);
+ TEXT outname[MAXPATHLEN];
+ strcpy(outname, isqlGlob.OutPath);
+ strcat(outname,REL.RDB$RELATION_NAME);
+ strcat(outname,".TBL");
+ isqlGlob.Out = fopen(outname,"w");
+ }
if (flag || !strncmp (REL.RDB$SECURITY_CLASS, "SQL$", 4))
EXTRACT_list_table (REL.RDB$RELATION_NAME, NULL, false, default_char_set_id);
+ if (isqlGlob.cvs)
+ {
+ fclose(isqlGlob.Out);
+ isqlGlob.Out=holdout;
+ }
END_FOR
ON_ERROR
ISQL_errmsg(gds_status);
ROLLBACK;
return;
END_ERROR;
-
+ if (isqlGlob.cvs)
+ {
+ isqlGlob.Out=holdout;
+ isqlGlob.printf("INPUT COMPUTED.SQL;%s",
+ NEWLINE);
+ TEXT outname[MAXPATHLEN];
+ strcpy(outname, isqlGlob.OutPath);
+ strcat(outname,"COMPUTED.SQL");
+ isqlGlob.Out = fopen(outname,"w");
+ FOR REL IN RDB$RELATIONS WITH
+ (REL.RDB$SYSTEM_FLAG NE 1 OR REL.RDB$SYSTEM_FLAG MISSING) AND
+ REL.RDB$VIEW_BLR MISSING
+ SORTED BY REL.RDB$RELATION_NAME
+ // If this is not an SQL table and we aren't doing ALL objects
+ if ((REL.RDB$FLAGS.NULL || !(REL.RDB$FLAGS & REL_sql)) && (flag != ALL_objects) )
+ continue;
+ // Null terminate name string
+ fb_utils::exact_name(REL.RDB$RELATION_NAME);
+ if (flag || !strncmp (REL.RDB$SECURITY_CLASS, "SQL$", 4))
+ list_computed (REL.RDB$RELATION_NAME, NULL);
+ END_FOR
+ ON_ERROR
+ ISQL_errmsg(gds_status);
+ ROLLBACK;
+ return;
+ END_ERROR;
+ fclose(isqlGlob.Out);
+ isqlGlob.Out=holdout;
+ }
}

@@ -1283,58 +1540,8 @@
bool header = true;

// Query gets the trigger info for non\-system triggers with

- // source that are not part of an SQL constraint.
-
- FOR TRG IN RDB$TRIGGERS
- WITH (TRG.RDB$SYSTEM_FLAG EQ 0 OR TRG.RDB$SYSTEM_FLAG MISSING) AND
- TRG.RDB$RELATION_NAME MISSING
- SORTED BY TRG.RDB$TRIGGER_TYPE, TRG.RDB$TRIGGER_SEQUENCE, TRG.RDB$TRIGGER_NAME
-
- if (header)
- {
- isqlGlob.printf("SET TERM %s %s%s", Procterm, isqlGlob.global_Term, NEWLINE);
- isqlGlob.printf(
- "%s/* Triggers only will work for SQL triggers */%s",
- NEWLINE,
- NEWLINE);
- header = false;
- }
- fb_utils::exact_name(TRG.RDB$TRIGGER_NAME);
-
- if (TRG.RDB$TRIGGER_INACTIVE.NULL)
- TRG.RDB$TRIGGER_INACTIVE = 0;
-
- // If trigger is not SQL put it in comments
- if (!(TRG.RDB$FLAGS & TRG_sql))
- isqlGlob.printf("/* ");
-
- if (isqlGlob.db_SQL_dialect > SQL_DIALECT_V6_TRANSITION)
- ISQL_copy_SQL_id (TRG.RDB$TRIGGER_NAME, SQL_identifier, DBL_QUOTE);
- else
- strcpy (SQL_identifier, TRG.RDB$TRIGGER_NAME);
-
- isqlGlob.printf("CREATE TRIGGER %s %s%s %s POSITION %d %s",
- SQL_identifier, NEWLINE,
- (TRG.RDB$TRIGGER_INACTIVE ? "INACTIVE" : "ACTIVE"),
- trigger_action (TRG.RDB$TRIGGER_TYPE), TRG.RDB$TRIGGER_SEQUENCE,
- NEWLINE);
-
- if (!TRG.RDB$TRIGGER_SOURCE.NULL)
- SHOW_print_metadata_text_blob (isqlGlob.Out, &TRG.RDB$TRIGGER_SOURCE);
-
- isqlGlob.printf(" %s%s", Procterm, NEWLINE);
- isqlGlob.printf(NEWLINE);
-
- if (!(TRG.RDB$FLAGS & TRG_sql))
- {
- isqlGlob.printf("*/%s", NEWLINE);
- }
-
- END_FOR
- ON_ERROR
- ISQL_errmsg(gds_status);
- return;
- END_ERROR;
+ // source that are not part of an SQL constraint
+ FILE* const holdout = isqlGlob.Out;

FOR TRG IN RDB$TRIGGERS CROSS REL IN RDB$RELATIONS OVER RDB$RELATION\_NAME
	//WITH \(REL\.RDB$SYSTEM\_FLAG NE 1 OR REL\.RDB$SYSTEM\_FLAG MISSING\) AND

@@ -1344,13 +1551,25 @@
SORTED BY TRG.RDB$RELATION_NAME, TRG.RDB$TRIGGER_TYPE,
TRG.RDB$TRIGGER_SEQUENCE, TRG.RDB$TRIGGER_NAME

+
+ fb_utils::exact_name(TRG.RDB$TRIGGER_NAME);
+ fb_utils::exact_name(TRG.RDB$RELATION_NAME);
+ if (isqlGlob.cvs)
+ {
+ isqlGlob.Out=holdout;
+ isqlGlob.printf("INPUT %s.TRG;%s",
+ TRG.RDB$TRIGGER_NAME,
+ NEWLINE);
+ TEXT outname[MAXPATHLEN];
+ strcpy(outname, isqlGlob.OutPath);
+ strcat(outname,TRG.RDB$TRIGGER_NAME);
+ strcat(outname,".TRG");
+ isqlGlob.Out = fopen(outname,"w");
+ header=true;
+ }
if (header)
{
isqlGlob.printf("SET TERM %s %s%s", Procterm, isqlGlob.global_Term, NEWLINE);
- isqlGlob.printf(
- "%s/* Triggers only will work for SQL triggers */%s",
- NEWLINE,
- NEWLINE);
header = false;
}
fb_utils::exact_name(TRG.RDB$TRIGGER_NAME);
@@ -1391,18 +1610,30 @@
isqlGlob.printf("*/%s", NEWLINE);
}

+ if (isqlGlob.cvs)
+ {
+ if (!header)
+ {
+ isqlGlob.printf("COMMIT WORK %s%s", Procterm, NEWLINE);
+ isqlGlob.printf("SET TERM %s %s%s", isqlGlob.global_Term, Procterm, NEWLINE);
+ }
+ fclose(isqlGlob.Out);
+ isqlGlob.Out=holdout;
+ }
END_FOR
ON_ERROR
ISQL_errmsg(gds_status);
return;
END_ERROR;
-
+ if (!isqlGlob.cvs)
+ {
if (!header)
{
isqlGlob.printf("COMMIT WORK %s%s", Procterm, NEWLINE);
isqlGlob.printf("SET TERM %s %s%s", isqlGlob.global_Term, Procterm, NEWLINE);
}
}
+}

static void list_check()
@@ -1419,6 +1650,16 @@
**************************************/

// Query gets the check clauses for triggers stored for check constraints 

+ FILE* const holdout = isqlGlob.Out;
+ if (isqlGlob.cvs)
+ {
+ isqlGlob.printf("INPUT CHECK.SQL;%s",
+ NEWLINE);
+ TEXT outname[MAXPATHLEN];
+ strcpy(outname, isqlGlob.OutPath);
+ strcat(outname,"CHECK.SQL");
+ isqlGlob.Out = fopen(outname,"w");
+ }

FOR TRG IN RDB$TRIGGERS CROSS
	CHK IN RDB$CHECK\_CONSTRAINTS WITH

@@ -1469,8 +1710,13 @@
ISQL_errmsg(gds_status);
return;
END_ERROR;
+ if (isqlGlob.cvs)
+ {
+ fclose(isqlGlob.Out);
+ isqlGlob.Out=holdout;
}

+}

static void list_collations()
{
@@ -1488,6 +1734,18 @@
if (isqlGlob.major_ods < ODS_VERSION11)
return;

+
+ FILE* const holdout = isqlGlob.Out;
+ if (isqlGlob.cvs)
+ {
+ isqlGlob.printf("INPUT COLLATIONS.SQL;%s",
+ NEWLINE);
+ TEXT outname[MAXPATHLEN];
+ strcpy(outname, isqlGlob.OutPath);
+ strcat(outname,"COLLATIONS.SQL");
+ isqlGlob.Out = fopen(outname,"w");
+ }
+
bool first = true;

FOR CL IN RDB$COLLATIONS CROSS

@@ -1553,8 +1811,13 @@
ISQL_errmsg(gds_status);
return;
END_ERROR;
-
isqlGlob.printf(NEWLINE);
+ if (isqlGlob.cvs)
+ {
+ fclose(isqlGlob.Out);
+ isqlGlob.Out=holdout;
+ }
+
}

@@ -1642,7 +1905,7 @@

if \(nodb\)
\{

- isqlGlob.printf(" */%s", NEWLINE);
+ isqlGlob.printf(" %s*/%s", isqlGlob.global_Term, NEWLINE);
}
else
{
@@ -1948,6 +2211,7 @@
**************************************/
bool first = true;
SCHAR char_sets[86];
+ FILE* const holdout = isqlGlob.Out;

FOR FLD IN RDB$FIELDS WITH
	FLD\.RDB$FIELD\_NAME NOT MATCHING "RDB$\+" USING "\+=\[0\-9\]\[0\-9\]\* \*"

@@ -1960,6 +2224,19 @@
first = false;
}
fb_utils::exact_name(FLD.RDB$FIELD_NAME);
+ if (isqlGlob.cvs)
+ {
+ isqlGlob.printf("INPUT %s.DOM;%s",
+ FLD.RDB$FIELD_NAME,
+ NEWLINE);
+ TEXT outname[MAXPATHLEN];
+
+ strcpy(outname, isqlGlob.OutPath);
+ strcat(outname,FLD.RDB$FIELD_NAME);
+ strcat(outname,".DOM");
+ isqlGlob.Out = fopen(outname,"w");
+
+ }

	if \(isqlGlob\.db\_SQL\_dialect \> SQL\_DIALECT\_V6\_TRANSITION\)
	\{

@@ -2105,6 +2382,12 @@
}

	isqlGlob\.printf\("%s%s", isqlGlob\.global\_Term, NEWLINE\);

+
+ if (isqlGlob.cvs)
+ {
+ fclose(isqlGlob.Out);
+ isqlGlob.Out=holdout;
+ }
END_FOR
ON_ERROR
ISQL_errmsg(gds_status);
@@ -2128,7 +2411,7 @@
*
**************************************/
bool first = true;
-
+ FILE* const holdout = isqlGlob.Out;
FOR EXC IN RDB$EXCEPTIONS
SORTED BY EXC.RDB$EXCEPTION_NAME

@@ -2138,6 +2421,19 @@
}
first = false;
fb_utils::exact_name(EXC.RDB$EXCEPTION_NAME);
+ if (isqlGlob.cvs)
+ {
+ isqlGlob.printf("INPUT %s.EXC;%s",
+ EXC.RDB$EXCEPTION_NAME,
+ NEWLINE);
+ TEXT outname[MAXPATHLEN];
+
+ strcpy(outname, isqlGlob.OutPath);
+ strcat(outname,EXC.RDB$EXCEPTION_NAME);
+ strcat(outname,".EXC");
+ isqlGlob.Out = fopen(outname,"w");
+
+ }

	ISQL\_copy\_SQL\_id \(EXC\.RDB$MESSAGE, SQL\_identifier2, SINGLE\_QUOTE\);
	if \(isqlGlob\.db\_SQL\_dialect \> SQL\_DIALECT\_V6\_TRANSITION\)

@@ -2150,6 +2446,11 @@
isqlGlob.printf("CREATE EXCEPTION %s %s%s%s",
EXC.RDB$EXCEPTION_NAME, SQL_identifier2, isqlGlob.global_Term, NEWLINE);

+ if (isqlGlob.cvs)
+ {
+ fclose(isqlGlob.Out);
+ isqlGlob.Out=holdout;
+ }
END_FOR
ON_ERROR
ISQL_errmsg(gds_status);
@@ -2218,11 +2519,23 @@
*
**************************************/
SCHAR collist[BUFFER_LENGTH512 * 2];
+ FILE* const holdout = isqlGlob.Out;

/* Static queries for obtaining foreign constraints, where RELC1 is the
foreign key constraints, RELC2 is the primary key lookup and REFC
is the join table */

+ if (isqlGlob.cvs)
+ {
+ isqlGlob.printf("INPUT FK.SQL;%s",
+ NEWLINE);
+ TEXT outname[MAXPATHLEN];
+ strcpy(outname, isqlGlob.OutPath);
+ strcat(outname,"FK.SQL");
+ isqlGlob.Out = fopen(outname,"w");
+ }
+
+
FOR RELC1 IN RDB$RELATION_CONSTRAINTS CROSS
RELC2 IN RDB$RELATION_CONSTRAINTS CROSS
REFC IN RDB$REF_CONSTRAINTS WITH
@@ -2308,6 +2621,11 @@
ISQL_errmsg(gds_status);
return;
END_ERROR;
+ if (isqlGlob.cvs)
+ {
+ fclose(isqlGlob.Out);
+ isqlGlob.Out=holdout;
+ }
}

@@ -2333,7 +2651,7 @@
char return_buffer[BUFFER_LENGTH128];

bool first = true;

-
+ FILE* const holdout = isqlGlob.Out;
FOR FUN IN RDB$FUNCTIONS
WITH FUN.RDB$SYSTEM_FLAG NE 1 OR FUN.RDB$SYSTEM_FLAG MISSING
SORTED BY FUN.RDB$FUNCTION_NAME
@@ -2349,6 +2667,20 @@
first = false;
}

+
+ if (isqlGlob.cvs)
+ {
+ isqlGlob.Out=holdout;
+ isqlGlob.printf("INPUT %s.UDF;%s",
+ FUN.RDB$FUNCTION_NAME,
+ NEWLINE);
+ TEXT outname[MAXPATHLEN];
+
+ strcpy(outname, isqlGlob.OutPath);
+ strcat(outname,FUN.RDB$FUNCTION_NAME);
+ strcat(outname,".UDF");
+ isqlGlob.Out = fopen(outname,"w");
+ }
// Start new function declaration
isqlGlob.printf("DECLARE EXTERNAL FUNCTION %s %s",
FUN.RDB$FUNCTION_NAME,
@@ -2511,7 +2843,11 @@
isqlGlob.global_Term,
NEWLINE,
NEWLINE);
-
+ if (isqlGlob.cvs)
+ {
+ fclose(isqlGlob.Out);
+ isqlGlob.Out=holdout;
+ }
END_FOR
ON_ERROR
ISQL_errmsg(gds_status);
@@ -2536,6 +2872,7 @@
**************************************/

bool first = true;

+ FILE* const holdout = isqlGlob.Out;

FOR GEN IN RDB$GENERATORS WITH
	GEN\.RDB$GENERATOR\_NAME NOT MATCHING "RDB$\+" USING "\+=\[0\-9\]\[0\-9\]\* \*" AND

@@ -2550,6 +2887,19 @@
NEWLINE,
NEWLINE);
first = false;
+
+ if (isqlGlob.cvs)
+ {
+
+ isqlGlob.Out=holdout;
+ isqlGlob.printf("INPUT GENERATORS.SQL;%s",
+ NEWLINE);
+ TEXT outname[MAXPATHLEN];
+ strcpy(outname, isqlGlob.OutPath);
+ strcat(outname,"GENERATORS.SQL");
+ isqlGlob.Out = fopen(outname,"w");
+ }
+
}

	fb\_utils::exact\_name\(GEN\.RDB$GENERATOR\_NAME\);

@@ -2570,6 +2920,12 @@
END_ERROR;

isqlGlob\.printf\(NEWLINE\);

+ if (isqlGlob.cvs)
+ {
+ fclose(isqlGlob.Out);
+ isqlGlob.Out=holdout;
+ }
+
}

@@ -2589,8 +2945,8 @@
*
**************************************/
char collist[BUFFER_LENGTH512 * 2];
-
bool first = true;
+ FILE* const holdout = isqlGlob.Out;

FOR IDX IN RDB$INDICES CROSS RELC IN RDB$RELATIONS
	OVER RDB$RELATION\_NAME

@@ -2606,6 +2962,19 @@
NEWLINE,
NEWLINE);
first = false;
+
+ if (isqlGlob.cvs)
+ {
+
+ isqlGlob.Out=holdout;
+ isqlGlob.printf("INPUT INDICES.SQL;%s",
+ NEWLINE);
+ TEXT outname[MAXPATHLEN];
+ strcpy(outname, isqlGlob.OutPath);
+ strcat(outname,"INDICES.SQL");
+ isqlGlob.Out = fopen(outname,"w");
+ }
+
}

	// Strip trailing blanks

@@ -2648,6 +3017,11 @@
ISQL_errmsg(gds_status);
return;
END_ERROR;
+ if (isqlGlob.cvs)
+ {
+ fclose(isqlGlob.Out);
+ isqlGlob.Out=holdout;
+ }
}

@@ -2667,7 +3041,7 @@
**************************************/

// If this is a view, use print_blob to print the view text
-
+ FILE* const holdout = isqlGlob.Out;
FOR REL IN RDB$RELATIONS WITH
(REL.RDB$SYSTEM_FLAG NE 1 OR REL.RDB$SYSTEM_FLAG MISSING) AND
REL.RDB$VIEW_BLR NOT MISSING AND
@@ -2683,6 +3057,19 @@
strcpy (SQL_identifier, REL.RDB$RELATION_NAME);

	fb\_utils::exact\_name\(REL\.RDB$OWNER\_NAME\);

+ if (isqlGlob.cvs)
+ {
+ isqlGlob.Out=holdout;
+ isqlGlob.printf("INPUT %s.VW;%s",
+ REL.RDB$RELATION_NAME,
+ NEWLINE);
+ TEXT outname[MAXPATHLEN];
+ strcpy(outname, isqlGlob.OutPath);
+ strcat(outname,REL.RDB$RELATION_NAME);
+ strcat(outname,".VW");
+ isqlGlob.Out = fopen(outname,"w");
+ }
+

	isqlGlob\.printf\("%s/\* View: %s, Owner: %s \*/%s",
			 NEWLINE,

@@ -2717,7 +3104,11 @@
SHOW_print_metadata_text_blob (isqlGlob.Out, &REL.RDB$VIEW_SOURCE);

	isqlGlob\.printf\("%s%s", isqlGlob\.global\_Term, NEWLINE\);

-
+ if (isqlGlob.cvs)
+ {
+ fclose(isqlGlob.Out);
+ isqlGlob.Out=holdout;
+ }
END_FOR
ON_ERROR
ISQL_errmsg(gds_status);
Index: isql.epp

RCS file: /cvsroot/firebird/firebird2/src/isql/isql.epp,v
retrieving revision 1.222
diff -u -w -r1.222 isql.epp
--- isql.epp 1 Jul 2007 02:40:15 -0000 1.222
+++ isql.epp 22 Aug 2007 15:09:12 -0000
@@ -51,6 +51,7 @@
#⁠include <stdio.h>
#⁠include "../dsql/keywords.h"
#⁠include "../jrd/gds_proto.h"
+
#⁠include <stdlib.h>
#⁠include <stdarg.h>
#⁠include <string.h>
@@ -112,6 +113,7 @@
#⁠include "../jrd/perf_proto.h"
#⁠include "../jrd/utl_proto.h"
#⁠include "../jrd/gdsassert.h"
+#⁠include "../jrd/os/path_utils.h"
#⁠include "../isql/Extender.h"
#⁠include "../isql/PtrSentry.h"
#⁠include "../common/classes/UserBlob.h"
@@ -165,6 +167,7 @@
SWITCH_NOAUTOCOMMIT,
SWITCH_NODBTRIGGERS,
SWITCH_NOWARN,
+ SWITCH_NOHEADINGS,
SWITCH_OUTPUT,
SWITCH_PAGE,
SWITCH_PASSWORD,
@@ -178,6 +181,7 @@
#⁠endif
SWITCH_USER,
SWITCH_VERSION,
+ SWITCH_CVS,
#⁠ifdef DEV_BUILD
SWITCH_EXTRACTTBL,
#⁠endif
@@ -212,6 +216,7 @@
{ SWITCH_NOAUTOCOMMIT, "noautocommit", 1, SWARG_NONE, 129 },
{ SWITCH_NODBTRIGGERS, "nodbtriggers", 3, SWARG_NONE, 154 },
{ SWITCH_NOWARN, "nowarnings", 3, SWARG_NONE, 130 },
+ { SWITCH_NOHEADINGS, "noheadings", 3, SWARG_NONE, 147 },
{ SWITCH_OUTPUT, "output", 1, SWARG_STRING, 131 },
{ SWITCH_PAGE, "pagelength", 3, SWARG_INTEGER, 132 },
{ SWITCH_PASSWORD, "password", 1, SWARG_STRING, 133 },
@@ -226,7 +231,9 @@
#⁠endif
{ SWITCH_USER, "user", 1, SWARG_STRING, 139 },
{ SWITCH_EXTRACT, "x", 1, SWARG_NONE, 140 },
- { SWITCH_VERSION, "z", 1, SWARG_NONE, 141 }
+ { SWITCH_VERSION, "z", 1, SWARG_NONE, 141 },
+ { SWITCH_CVS, "cvs", 2, SWARG_STRING, 141 }
+
};

@@ -322,6 +329,7 @@
const SCHAR* ri_action_print_mixed;
};

+static void safe_concat_path(TEXT* destbuf, const TEXT* srcbuf);
static processing_state add_row(TEXT*);
static processing_state blobedit(const TEXT*, const TEXT* const*);
static processing_state bulk_insert_hack(const char* command, XSQLDA** sqldap);
@@ -364,6 +372,7 @@
static processing_state passthrough(const char* cmd);
#⁠endif
static SSHORT print_item(TEXT**, XSQLVAR*, const SLONG);
+static processing_state print_item_blob(FILE*, const XSQLVAR*, FB_API_HANDLE);
static void print_item_numeric(SINT64, SSHORT, SSHORT, TEXT*);
static processing_state print_line(XSQLDA*, const SLONG pad[], TEXT line[]);
static void print_performance(perf* perf_before);
@@ -620,6 +629,9 @@
isqlGlob.Out = stdout;
isqlGlob.Errfp = stderr;

+ //usually we don't need a cvs-friendly extract
+
+ isqlGlob.cvs = false;
const processing_state ret = parse_arg(argc, argv, tabname, NULL);

// Detect if stdin is redirected

@@ -1901,6 +1913,39 @@
}

+
+
+static void safe_concat_path(TEXT *resultString, const TEXT *appendString)
+{
+/**************************************
+ *
+ * s a f e _ c o n c a t _ p a t h
+ *
+ **************************************
+ *
+ * Functional description
+ * Safely appends appendString to resultString using paths rules.
+ * resultString must be at most MAXPATHLEN size.
+ * Thread/signal safe code.
+ *
+ **************************************/
+ int len = strlen(resultString);
+ if (resultString[len - 1] != PathUtils::dir_sep && len < MAXPATHLEN - 1 && len >0) {
+ resultString[len++] = PathUtils::dir_sep;
+ resultString[len] = 0;
+ }
+ int alen = strlen(appendString);
+ if (len + alen > MAXPATHLEN - 1)
+ alen = MAXPATHLEN - 1 - len;
+ fb_assert(alen >= 0);
+ memcpy(&resultString[len], appendString, alen);
+ resultString[len + alen] = 0;
+}
+
+
+
+
+
static processing_state add_row(TEXT* tabname)
{
/**************************************
@@ -6276,21 +6321,21 @@
if (Interactive) {
if (local_usr[0] != '\0') {
if (local_sql_role[0] != '\0') {
- isqlGlob.printf("Database: %s, User: %s, Role: %s%s",
+ isqlGlob.printf("/*Database: %s, User: %s, Role: %s*/%s",
dbname, local_usr, local_sql_role, NEWLINE);
}
else {
- isqlGlob.printf("Database: %s, User: %s%s",
+ isqlGlob.printf("/*Database: %s, User: %s*/%s",
dbname, local_usr, NEWLINE);
}
}
else {
if (local_sql_role[0] != '\0') {
- isqlGlob.printf("Database: %s, Role: %s%s",
+ isqlGlob.printf("/*Database: %s, Role: %s*/%s",
dbname, local_sql_role, NEWLINE);
}
else {
- isqlGlob.printf("Database: %s%s", dbname, NEWLINE);
+ isqlGlob.printf("/*Database: %s*/%s", dbname, NEWLINE);
}
}
}
@@ -6581,7 +6626,7 @@
processing_state ret = SKIP;

TEXT errbuf\[MSG\_LENGTH\];

-
+ TEXT outname[MAXPATHLEN];
// Initialize database name

isqlGlob\.global\_Db\_name\[0\] = '\\0';

@@ -6744,6 +6789,10 @@
Nodbtriggers = true;
break;

+ case SWITCH_NOHEADINGS:
+ Heading = false;
+ break;
+
case SWITCH_NOWARN:
Warnings = false;
break;
@@ -6754,6 +6803,21 @@
}
break;

+ case SWITCH_CVS:
+
+ //FSG: Create a cvs-friendly metadata extract
+ //We will create a file named main.sql that will mainly
+ //consist of lots of include statements for all matadata objects.
+
+ safe_concat_path(isqlGlob.OutPath, swarg_str);
+ safe_concat_path(isqlGlob.OutPath, "");
+ strcpy(outname,isqlGlob.OutPath);
+ safe_concat_path(outname,"main.sql");
+ isqlGlob.Out = fopen(outname,"w");
+ isqlGlob.cvs = true;
+ break;
+
+
case SWITCH_INPUT:
if (newinput(swarg_str) == SKIP) {
Interactive = false;
Index: show.epp

RCS file: /cvsroot/firebird/firebird2/src/isql/show.epp,v
retrieving revision 1.116
diff -u -w -r1.116 show.epp
--- show.epp 4 Apr 2007 08:46:31 -0000 1.116
+++ show.epp 22 Aug 2007 15:09:13 -0000
@@ -2043,9 +2043,19 @@
// ddl_database, ddl_domain, ddl_relation, ddl_view, ddl_procedure, ddl_trigger,
// ddl_udf, ddl_blob_filter, ddl_exception, ddl_generator, ddl_index, ddl_role,
// ddl_charset, ddl_collation//, ddl_sec_class
-
+ FILE* const holdout = isqlGlob.Out;
bool first = true;

+ if (isqlGlob.cvs)
+ {
+ isqlGlob.printf("INPUT COMMENTS.SQL;%s",
+ NEWLINE);
+ TEXT outname[MAXPATHLEN];
+ strcpy(outname, isqlGlob.OutPath);
+ strcat(outname,"COMMENTS.SQL");
+ isqlGlob.Out = fopen(outname,"w");
+ }
+
FOR FIRST 1 DT IN RDB$DATABASE
WITH DT.RDB$DESCRIPTION NOT MISSING

@@ -2303,6 +2313,11 @@
ISQL_errmsg(isc_status);
return ps_ERR;
END_ERROR
+ if (isqlGlob.cvs)
+ {
+ fclose(isqlGlob.Out);
+ isqlGlob.Out=holdout;
+ }

return first ? OBJECT\_NOT\_FOUND : SKIP;

}

@firebird-automations
Copy link
Collaborator Author

Commented by: @asfernandes

I don't think the 'enhancement' should be put in Firebird.

VCS works great with full-script files.

@firebird-automations
Copy link
Collaborator Author

Commented by: Frank Schlottmann-Goedde (fsg)

Not really. It is much easier to find changes to e.g. a procedure if they are in a single file and not cluttered round global changes to a single script. And at the time I wrote this patch it was not possible to recreate a database from the script isql creates without manual reordering. Not sure whether this is still the fact.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Development

No branches or pull requests

1 participant