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 @)