OpenSER Database Schema creation and extension

To avoid the maintainance of different schemas for Mysql, PostgreSQL and other databases, the tables for the database setup are generated from a common definition. This should lead to fewer bugs, in the past the postgres and dbtext scripts were quite a few times out of sync with the mysql tables.

The db schemas source of the tables are in XML format in the “db/schema” directory, the system uses the same schema for mysql, postgres, dbtext (and oracle, deactivated at the moment). This format of the files is fairly simple to understand, look into the “template.xml” file for some comments about the structure.

Simply call “make dbschema” in the kamailio openser root directory to generate the tables for openser standard, extra, serweb and presence modules for all databases. For the generation you'll need the xsltproc function. The SQL code of the tables are copied into the “utils/kamctl/” subfolders.

It is also possible to generate docbook documentation from the XML schema files, this is untested at the moment.

The generated tables are used from the different database setup scripts. After removal of the embedded SQL code the scripts should be then fare more shorter and extensible.

In the entities.xml files are some defines for common datatype names and - sizes, this file gets included in every XML table definition. Every table uses for example the “table_id_len” define, to specify the length of the unique id. This common datatypes makes this easy to maintain consistency for the database schemas. Please try to use this definitions also for new tables.

Every OpenSER module and every table has a own XML definition, e.g. the “openser-presence.xml” file combines the “pr_presentity.xml”, “pr_pua.xml” and other presence related definitions. Used prefixes at the moment are “pr” for PRESENCE related schemas, and “sw” for SERWeb tables.

For generating the html docs, iirc, the commands are: 1. in lib/srdb1/schema: make docbook-xml 2. in doc/databases/kamailio: make html

A tables.html should be generated.

In the directory “doc/dbschema” are the DTD and XSL files for the table generation located. XSL is not really pretty, but this the right tool for these kind of jobs. The file “catalog.xml” is used among other things to resolve the Document type reference in the XML header to the local filesystem, e.g. “dbschema.dtd”. In the “common.xsl” script are common definitions for string normalization and datatype handling located. The “sql.xsl” script creates the tables and provides some standard SQL functions. All other database specific SQL functions are location in the “$dbname.xsl” files.

Custom tables could be easily added with the creation of additional XML files in the “db/dbschema” directory. One table can specified in the “extra.xml” file, this get then included into the “openser-extensions.sql” file in scripts/$dbname table. If you want to add more then one table, you must add further files and also add them to the “openser-extensions.xml” file.

New datatypes or SQL flags must be added to the XSL scripts in the “doc/dbschema/xsl” directory. If you simply need one database specific addition to a table, it should be easier to use the the “db=$dbname” functionality to specify the raw SQL code, please refer to the template.xml file or the other table definition for examples.

In order to install additional tables with the “openserdbctl” tool it's necessary to append them to the appropriate section in the openserctlrc file. New tables that gets included into the openser repository (e.g. tables for new modules) should be added to the database module section in the “openserctl.base” file.

All generated SQL tables and the dbtext database are installed into the “/usr/share/openser” directory. The XML source is not installed in the system. The dbtext related tables gets included into the main openser debian package, the MySQL and PostgreSQL SQL tables are packaged into the respective debian module packages.