[Koha-devel] SQL schema of Koha 3.0.3

[Zeno Tajoli]

Hi to all,

as I say in previous post and on IRC, CILEA has sponsor writing 
documentation of  Koha logical SQL schema.
So CILEA has partial done the RFC 
<http://wiki.koha.org/doku.php?id=en:development:rfcs3.2:rfc32_koha_api_uml_diagrams_and_e-r_diagrams>Koha 
E-R diagrams and tables information

If you go to http://wiki.koha.org/doku.php?id=en:development:dbschema 
you can see now two new links about
Koha 3.0.3 logical schema

In fact the SQL documentation is the same, the second has a proposal 
how to keep this documentation.

 From the README:

Firstly, to automatize as much as possible the documentation task we 
used a tool called SchemaSpy <http://schemaspy.sourceforge.net/> 
which allows one to explore and produce a diagram representation of 
the MYSQL DB.

Secondly, to store the produced documentation we leveraged the 
capability of MYSQL of associating comments to both tables and 
columns. Even though there are some (but not critical, in this case) 
drawbacks in using MYSQL comments to document the DB, such as limited 
length of table-level comments, the main benefit is that once entered 
they become part of the DB itself and thus they can be inherited, no 
matters if table or column definitions change with subsequent 
releases of the softwares.

Documentation information is added by using an SQL script called 
"documentation patch". This patch has to be created dynamically, 
starting from an existing installation of KOHA because in order to 
add a comment is necessary to change the table/columns definition. 
Since the table/column definition can change at any time during the 
development process, we want to be able to "desume" this definition 
by looking at the DB structure.

To do so, we leverage the XML file containing a representation the DB 
structure created by SchemaSpy when it runs on the DB of a Koha 
installation (note: the file will be called "koha.xml" if the DB name 
is "koha"). By transforming this XML file through an XSLT stylesheet 
we obtain dynamically an SQL patch. This SQL file can now be edited, 
for example to add new table/columns comments, and then it should be 
run again against the DB in order to update it. It's a good practice 
to re-generate the SQL patch every time we want to modify the 
documentation in order to avoid that in the meanwhile the DB structure changed.

Example of commands needed to create the SQL patch:

Step 1: Running SchemaSpy
 > java -jar schemaSpy_4.1.1.jar -dp <path_to_mysql_java_connector> 
-o <output_folder> -t mysql -host <host> -u <user> -p <password> -db 
<db_to_describe>

Step 2: Creating the SQL patch:
 > java -jar saxon9.jar -s:<path_to_XML_file_generated_by_SchemaSpy> 
-xsl:generate_sql_dbdoc_patch.xsl -o:autogen_dbdoc_patch.sql

Step 3: Update autogen_dbdoc_patch.sql
vi autogen_dbdoc_patch.sql
Insert the comments about new column, fix errors, etc.

Step 4: Running the SQL script:
 > mysql -u <user> -p -h <host> < autogen_dbdoc_patch.sql

So the work of Matteo Romanello is done.
The questions are:
-Do you like the selection of those tools ?
-Do you think this is a good workflow to keep updated the 
documentation of MySQL level ?

Bye to all



Zeno Tajoli
CILEA - Segrate (MI)
tajoliAT_SPAM_no_prendiATcilea.it
(Indirizzo mascherato anti-spam; sostituisci quanto tra AT con @)