GNOME.org
 

[libgda] Added new Report example

commit 661e59a451787bb3166b07a42dcf8564a57c80dc
Author: Vivien Malerba <malerba gnome-db org>
Date:   Thu Jun 25 21:03:27 2009 +0200

    Added new Report example
    
    Creates a report of the schemas, tables and views along with their
    properties

 Makefile.am                           |    2 +
 samples/Report/Makefile               |   10 ++-
 samples/Report/README                 |   12 ++-
 samples/Report/schema-report-spec.xml |  254 +++++++++++++++++++++++++++++++++
 samples/Report/schema-report.c        |  107 ++++++++++++++
 5 files changed, 383 insertions(+), 2 deletions(-)
---
diff --git a/Makefile.am b/Makefile.am
index a0d2360..10885ab 100644
--- a/Makefile.am
+++ b/Makefile.am
@@ -58,6 +58,8 @@ example_files = \
 	samples/Report/customers-report-rml.c \
 	samples/Report/customers-report-rml.rml \
 	samples/Report/customers-report-spec.xml \
+	samples/Report/schema-report.c \
+	samples/Report/schema-report-spec.xml \
 	samples/Report/Makefile
 	samples/Report/README
 	samples/Virtual/Makefile \
diff --git a/samples/Report/Makefile b/samples/Report/Makefile
index e3d3b25..ed9227a 100644
--- a/samples/Report/Makefile
+++ b/samples/Report/Makefile
@@ -4,7 +4,7 @@ LDFLAGS = `pkg-config --libs libgda-report-4.0`
 HTML_STYLESHEET = /usr/share/xml/docbook/stylesheet/nwalsh/html/docbook.xsl
 FO_STYLESHEET = /usr/share/xml/docbook/stylesheet/nwalsh/fo/docbook.xsl
 
-all: customers-report customers-report-rml customers-report-docbook
+all: customers-report customers-report-rml customers-report-docbook schema-report
 
 customers-report: customers-report.c
 	$(CC) -o customers-report customers-report.c $(CFLAGS) $(LDFLAGS)
@@ -15,8 +15,15 @@ customers-report-rml: customers-report-rml.c
 customers-report-docbook: customers-report-docbook.c
 	$(CC) -o customers-report-docbook customers-report-docbook.c $(CFLAGS) $(LDFLAGS)
 
+schema-report: schema-report.c
+	$(CC) -o schema-report schema-report.c $(CFLAGS) $(LDFLAGS)
+
 html: customers-report.xml
 	xsltproc --output customers-report.html --stringparam use.extensions 0 $(HTML_STYLESHEET) customers-report.xml
+
+schema-html: schema-report.xml
+	xsltproc --output schema-report.html --stringparam use.extensions 0 $(HTML_STYLESHEET) schema-report.xml
+
 pdf: customers-report.xml
 	fop -xml customers-report.xml -xsl $(FO_STYLESHEET) -pdf customers-report.pdf
 
@@ -27,3 +34,4 @@ clean:
 	rm -f customers-report.xml customers-report.html customers-report.pdf
 	rm -f customers-report-rml.pdf customers-report-rml.html
 	rm -f customers-report-docbook.pdf customers-report-docbook.html
+	rm -f schema-report schema-report.xml
diff --git a/samples/Report/README b/samples/Report/README
index 7f9f939..9b519ba 100644
--- a/samples/Report/README
+++ b/samples/Report/README
@@ -5,7 +5,7 @@ Description:
 ------------
 
 The examples in this directory illustrate the GDA report engine, in the libgda-report component,
-part of libgda as of versions >= 3.2.
+part of libgda as of versions >= 4.0.
 
 In these examples the 'SalesTest' (this data source needs to be defined,
 which is automatically done when the first Libgda application is run, and uses a small SQLite database)
@@ -19,6 +19,10 @@ The second example (customers-report-rml.c) creates a GdaReportRmlDocument objec
 a higher level than the GdaReportEngine to work on RML files. It loads the "customers-report-rml.rml"
 RML file, and generates a PDF file from it.
 
+The 3rd example (schema-report.c) takes the "schema-report-spec.xml" XML file and created the "schema-report.xml"
+file (a valid DocBook document) which lists all the database schemas, the tables and views in the default schema
+along with their properties.
+
 Compiling and running:
 ----------------------
 
@@ -37,3 +41,9 @@ result is in the customers-report.xml file, which can be converted to HTML (in a
 or, for the second example:
 > ./customers-report-rml
 > evince customers-report-rml.pdf
+
+and for the 3rd:
+> ./schema-report
+> make schema-html
+(make sure you set the correct path to the stylesheet in the Makefile)
+> firefox schema-report.html
diff --git a/samples/Report/schema-report-spec.xml b/samples/Report/schema-report-spec.xml
new file mode 100644
index 0000000..8a5f467
--- /dev/null
+++ b/samples/Report/schema-report-spec.xml
@@ -0,0 +1,254 @@
+<?xml version="1.0" encoding="UTF-8"?>
+<!DOCTYPE article PUBLIC "-//OASIS//DTD DocBook XML V4.1.2//EN"
+          "http://www.oasis-open.org/docbook/xml/4.1.2/docbookx.dtd";>
+<article>
+  <articleinfo>
+    <title>Schema report</title>
+  </articleinfo>
+
+  <!-- Schemas in the database -->
+  <gda_report_section query_name="schemas" cnc_name="meta_store_cnc"> 
+    <sect1>
+      <title>Database's schemas list</title>
+      
+      <para>Here is the list of database's schemas (<gda_report_param_value param_name="schemas|?nrows"/> found)</para>
+      <table frame="all"><title>List of schemas</title>
+	<tgroup cols="2" align="left" colsep="1" rowsep="1">
+	  <colspec colname="Name"/>
+	  <colspec colname="Internal?"/>
+	  <thead>
+	    <row>
+	      <entry>Schema</entry>
+	      <entry>Internal ?</entry>
+	    </row>
+	  </thead>
+	  <tbody>
+	    <gda_report_iter>
+	      <row>
+		<entry><gda_report_param_value param_name="schemas|@schema_name"/></entry>
+		<entry>
+		  <gda_report_if expr="##schemas|@schema_internal = TRUE">
+		    <gda_report_if_true>Internal</gda_report_if_true>
+		    <gda_report_if_false>No</gda_report_if_false>
+		  </gda_report_if>
+		</entry>
+	      </row>
+	    </gda_report_iter>
+	  </tbody>
+	</tgroup>
+      </table>
+    </sect1>
+
+    <sect1>
+      <title>In the default schema</title>
+      <sect2>
+	<!-- Tables -->
+	<title>Tables</title>
+	<gda_report_section>
+	  <gda_report_query query_name="direct_tables" cnc_name="meta_store_cnc">
+	    select * from _tables where table_short_name != table_full_name AND table_type LIKE '%TABLE%'
+	  </gda_report_query>
+	  
+	  <gda_report_iter>
+	    <sect3>
+	      <title><gda_report_param_value param_name="direct_tables|@table_name"/> table</title>
+
+	      <!-- Table's columns -->
+	      <gda_report_section>
+		<gda_report_query query_name="columns" cnc_name="meta_store_cnc">
+		  select * from _columns WHERE
+		  table_name = ##direct_tables|@table_name::string AND
+		  table_schema = ##direct_tables|@table_schema::string
+		  ORDER BY ordinal_position
+		</gda_report_query>
+		
+		<para>
+		  <informaltable frame="all">
+		    <tgroup cols="5" align="left" colsep="1" rowsep="1">
+		      <colspec colname="Column name"/>
+		      <colspec colname="Type"/>
+		      <colspec colname="Can be NULL"/>
+		      <colspec colname="Default"/>
+		      <colspec colname="Comments"/>
+		      <thead>
+			<row>
+			  <entry>Column name</entry>
+			  <entry>Type</entry>
+			  <entry>Can be NULL</entry>
+			  <entry>Default</entry>
+			  <entry>Comments</entry>
+			</row>
+		      </thead>
+		      <tbody>
+			<gda_report_iter>
+			  <row>
+			    <entry><gda_report_param_value param_name="columns|@column_name"/></entry>
+			    <entry><gda_report_param_value param_name="columns|@data_type"/></entry>
+			    <entry>
+			      <gda_report_if expr="##columns|@is_nullable::boolean = TRUE">
+				<gda_report_if_true>Yes</gda_report_if_true>
+				<gda_report_if_false>No</gda_report_if_false>
+				<gda_report_param_value param_name="columns|@is_nullable"/>
+			      </gda_report_if>
+			    </entry>
+			    <entry><gda_report_param_value param_name="columns|@column_default"/></entry>
+			    <entry><gda_report_param_value param_name="columns|@column_comments"/></entry>
+			  </row>
+			</gda_report_iter>
+		      </tbody>
+		    </tgroup>
+		  </informaltable>
+		</para>
+	      </gda_report_section>
+
+	      <itemizedlist>
+
+		<!-- Table's constraints, not FK -->
+		<gda_report_section>
+		  <gda_report_query query_name="constraints" cnc_name="meta_store_cnc">
+		    select constraint_name, constraint_type, check_clause FROM _table_constraints WHERE
+		    table_name = ##direct_tables|@table_name::string AND
+		    table_schema = ##direct_tables|@table_schema::string AND
+		    constraint_type != 'FOREIGN KEY'
+		    ORDER BY constraint_type
+		  </gda_report_query>
+		  <gda_report_iter>
+		    <listitem><para><gda_report_param_value param_name="constraints|@constraint_type"/>:
+			<gda_report_param_value param_name="constraints|@constraint_name"/>
+			
+			<!-- Involved columns -->
+			<gda_report_if expr="##constraints|@constraint_type::string = 'CHECK'">
+			  <gda_report_if_true>
+			    <programlisting>
+<gda_report_param_value param_name="constraints|@check_clause"/>
+			    </programlisting>
+			  </gda_report_if_true>
+			  <gda_report_if_false>
+			    <gda_report_section>
+			      <gda_report_query query_name="key_columns" cnc_name="meta_store_cnc">
+				SELECT k.column_name FROM _key_column_usage k WHERE
+				table_name = ##direct_tables|@table_name::string AND
+				table_schema = ##direct_tables|@table_schema::string AND
+				constraint_name = ##constraints|@constraint_name
+				ORDER BY ordinal_position
+			      </gda_report_query>
+			      <itemizedlist>
+				<gda_report_iter>
+				  <listitem><para><gda_report_param_value param_name="key_columns|@column_name"/></para></listitem>
+				</gda_report_iter>
+			      </itemizedlist>
+			    </gda_report_section>
+			  </gda_report_if_false>
+			</gda_report_if>
+		    </para></listitem>
+		  </gda_report_iter>
+		</gda_report_section>
+		
+		<!-- Table's FK constraints -->
+		<gda_report_section>
+		  <gda_report_query query_name="constraints" cnc_name="meta_store_cnc">
+		    SELECT tc.constraint_name, r.ref_table_schema, r.ref_table_name, tc.constraint_type, r.update_rule, r.delete_rule FROM
+                    _table_constraints tc INNER JOIN _referential_constraints r ON
+                    (tc.table_schema=r.table_schema AND tc.table_name=r.table_name AND tc.constraint_name=r.constraint_name)
+                    WHERE
+                    tc.table_name = ##direct_tables|@table_name::string AND
+                    tc.table_schema = ##direct_tables|@table_schema::string AND
+                    constraint_type = 'FOREIGN KEY'
+                    ORDER BY constraint_type
+		  </gda_report_query>
+		  <gda_report_iter>
+		    <listitem><para><gda_report_param_value param_name="constraints|@constraint_type"/>:
+			<gda_report_param_value param_name="constraints|@constraint_name"/>
+			=&gt; '<gda_report_param_value param_name="constraints|@ref_table_schema"/>.<gda_report_param_value param_name="constraints|@ref_table_name"/>'
+			<!-- Involved columns -->
+			<gda_report_section>
+			  <gda_report_query query_name="key_columns" cnc_name="meta_store_cnc">
+			    select * from _detailled_fk WHERE
+			    fk_table_name = ##direct_tables|@table_name::string AND
+			    fk_table_schema = ##direct_tables|@table_schema::string AND
+			    fk_constraint_name = ##constraints|@constraint_name
+			    ORDER BY ordinal_position
+			  </gda_report_query>
+			  <itemizedlist>
+			    <gda_report_iter>
+			      <listitem><para>
+				  <gda_report_param_value param_name="key_columns|@fk_column"/> =&gt;
+				  <gda_report_param_value param_name="key_columns|@ref_column"/>
+			      </para></listitem>
+			    </gda_report_iter>
+			  </itemizedlist>
+			</gda_report_section>
+			
+		    </para>
+		    <para>ON UPDATE: <gda_report_param_value param_name="constraints|@update_rule"/></para>
+		    <para>ON DELETE: <gda_report_param_value param_name="constraints|@delete_rule"/></para>
+		    </listitem>
+		  </gda_report_iter>
+		</gda_report_section>
+
+	      </itemizedlist>
+
+	    </sect3>
+	  </gda_report_iter>
+	  </gda_report_section>
+      </sect2>
+
+      <!-- Views -->
+      <sect2>
+	<title>Views</title>
+	<gda_report_section>
+	  <gda_report_query query_name="direct_views" cnc_name="meta_store_cnc">
+	    SELECT v.table_name, v.table_schema, v.view_definition FROM _tables t
+	    INNER JOIN _views v ON (v.table_name = t.table_name AND v.table_schema = t.table_schema) WHERE
+	    table_short_name != table_full_name AND table_type LIKE 'VIEW'
+	  </gda_report_query>
+	  <itemizedlist>
+	    <gda_report_iter>
+	      <listitem><para><gda_report_param_value param_name="direct_views|@table_name"/>:
+		  <!-- Views's columns -->
+		  <gda_report_section>
+		    <gda_report_query query_name="columns" cnc_name="meta_store_cnc">
+		      select * from _columns WHERE
+		      table_name = ##direct_views|@table_name::string AND
+		      table_schema = ##direct_views|@table_schema::string
+		      ORDER BY ordinal_position
+		    </gda_report_query>
+		    
+		      <informaltable frame="all">
+			<tgroup cols="3" align="left" colsep="1" rowsep="1">
+			  <colspec colname="Column name"/>
+			  <colspec colname="Type"/>
+			  <colspec colname="Comments"/>
+			  <thead>
+			    <row>
+			      <entry>Column name</entry>
+			      <entry>Type</entry>
+			      <entry>Comments</entry>
+			    </row>
+			  </thead>
+			  <tbody>
+			    <gda_report_iter>
+			      <row>
+				<entry><gda_report_param_value param_name="columns|@column_name"/></entry>
+				<entry><gda_report_param_value param_name="columns|@data_type"/></entry>
+				<entry><gda_report_param_value param_name="columns|@column_comments"/></entry>
+			      </row>
+			    </gda_report_iter>
+			  </tbody>
+			</tgroup>
+		      </informaltable>
+		  </gda_report_section>
+
+		  <!-- view's definition -->
+		  <programlisting>
+<gda_report_param_value param_name="direct_views|@view_definition"/>
+		  </programlisting>
+	      </para></listitem>
+	    </gda_report_iter>
+	  </itemizedlist>
+	  </gda_report_section>
+      </sect2>
+    </sect1>
+
+  </gda_report_section>
+</article>
diff --git a/samples/Report/schema-report.c b/samples/Report/schema-report.c
new file mode 100644
index 0000000..cd8ddc0
--- /dev/null
+++ b/samples/Report/schema-report.c
@@ -0,0 +1,107 @@
+#include <libgda/libgda.h>
+#include <gda-report-engine.h>
+#include <sql-parser/gda-sql-parser.h>
+
+GdaConnection *open_connection (void);
+GSList        *create_queries (GdaConnection *cnc);
+
+int 
+main (int argc, char **argv)
+{
+	gchar *spec;
+	GdaReportEngine *eng;
+	GdaConnection *cnc, *store_cnc;
+	GdaHolder *param;
+	GdaMetaStore *store;
+	GError *error = NULL;
+
+	gda_init ();
+
+	/* Engine object */
+	g_assert (g_file_get_contents ("schema-report-spec.xml", &spec, NULL, NULL));
+	eng = gda_report_engine_new_from_string (spec);
+	g_free (spec);
+
+	/* GdaConnection */
+	cnc = open_connection ();
+	gda_report_engine_declare_object (eng, G_OBJECT (cnc), "main_cnc");
+	
+	g_print ("Collecting meta data...");
+	if (! gda_connection_update_meta_store (cnc, NULL, &error)) {
+                g_print ("Could not collect meta data: %s\n",
+                         error && error->message ? error->message : "No detail");
+                exit (1);
+        }
+	g_print ("done\n");
+	store = gda_connection_get_meta_store (cnc);
+	store_cnc = gda_meta_store_get_internal_connection (store);
+	gda_report_engine_declare_object (eng, G_OBJECT (store_cnc), "meta_store_cnc");
+
+	/* define parameters */
+	param = gda_holder_new_string ("abstract", "-- This text is from a parameter set in the code, not in the spec. file --");
+	gda_report_engine_declare_object (eng, G_OBJECT (param), "abstract");
+	g_object_unref (param);
+
+	/* create queries */
+	GSList *queries, *list;
+	queries = create_queries (cnc);
+	for (list = queries; list; list = list->next) {
+		gda_report_engine_declare_object (eng, G_OBJECT (list->data),
+						  g_object_get_data (G_OBJECT (list->data), "name"));
+		g_object_unref (G_OBJECT (list->data));
+	}
+	g_slist_free (queries);
+
+	/* run the report */
+	xmlDocPtr doc;
+	doc = gda_report_engine_run_as_doc (eng, &error);
+	if (!doc) {
+		g_print ("gda_report_engine_run error: %s\n",
+			 error && error->message ? error->message : "No detail");
+		exit (1);
+	}
+	else {
+		g_print ("Report generated in schema-report.xml.\n");
+		xmlSaveFile ("schema-report.xml", doc);
+		xmlFreeDoc (doc);
+	}
+
+	g_object_unref (eng);
+	g_object_unref (cnc);
+
+	return 0;
+}
+
+GdaConnection *
+open_connection (void)
+{
+        GdaConnection *cnc;
+        GError *error = NULL;
+        cnc = gda_connection_open_from_dsn ("SalesTest", NULL,
+					    GDA_CONNECTION_OPTIONS_NONE,
+					    &error);
+        if (!cnc) {
+                g_print ("Could not open connection to DSN 'SalesTest': %s\n",
+                         error && error->message ? error->message : "No detail");
+                exit (1);
+        }
+        return cnc;
+}
+
+GSList *
+create_queries (GdaConnection *cnc)
+{
+	GdaSqlParser *parser;
+	GdaStatement *stmt;
+	GSList *list = NULL;
+
+	parser = gda_connection_create_parser (cnc);
+
+	stmt = gda_sql_parser_parse_string (parser, "SELECT * FROM _schemata", NULL, NULL);
+	g_object_set_data ((GObject*) stmt, "name", "schemas");
+	list = g_slist_prepend (list, stmt);
+	
+	g_object_unref (parser);
+
+	return list;
+}
[Date Prev][Date Next]   [Thread Prev][Thread Next]   [Thread Index] [Date Index] [Author Index]