salix - Salix Babylonica: advices about relational database schemas.
salix (create|report|show|drop) (pg|my) <database connection options...>
salix gives design, style, consistency, version and system advices about
relational database schemas by querying the standard information schema.
The results are put in a salix schema which can be queried with
your favorite database relation browsing tool.
This command is a simple front-end to run Salix Babylonica SQL scripts
over the target database.
When started without any argument, the salix command displays a short help message and the running version. Otherwise, two arguments are expected.
The first argument is the operation to perform.
Creates the Salix Babylonica salix schema which holds the advices,
as well as salix_information_schema, a normalized copy of the
information schema.
Generate a simple text report for the raised advices, sufficient to identify where the issues are in the schemas. This operation requires a prior run of create on the database.
Show a basic summary of the advices.
Use you favorite database browser to explore the salix schema
for detailed information, and possibly to fix the database schemas.
This operation requires a prior run of create on the database.
Use report for more textual details about the raised advices.
Drop Salix Babylonica-related schemas.
The second argument is the target database engine.
PostgreSQL does have real catalogs named DATABASEs, which contain several
SCHEMAs. Catalogs are isolated one from the other, thus it is necessary to
run the tool separately on each catalog.
Other arguments can be used to select PostgreSQL: psql, pgsql, postgres, postgresql.
MySQL does not have catalogs, as the DATABASE is really a SCHEMA
(a kind of directory which stores tables), thus it is enough to run the tool
only once against an installation.
Argument mysql can be used to select MySQL as well.
The remainder arguments are directly passed to the database connection command. The underlying permissions of the connection must allow to read the information schema meta-data system tables, and to create the two Salix Babylonica-related PostgreSQL schemas/MySQL databases.
The rational for the argument order is that from the database selection
argument onward this is the full connection command to be used by salix.
With PostgreSQL:
sh> salix create pg -U calvin -h server erp
sh> salix report pg -U calvin -h server erp <look at issues...>
sh> salix drop pg -U calvin -h server erp
With MySQL:
sh> salix create my -u calvin -h server
sh> salix report my -u calvin -h server <look at issues...>
sh> salix drop my -u calvin -h server
The command returns the 0 status code if all was okay.
Other error codes are returned by the script:
expecting at least 2 arguments
error on operation name, salix first argument
error on database name, salix second argument
directory for SQL scripts not found
cannot cd to SQL script directory
script for operation on database not found
Finally, error codes can also be returned by the database client command when running the SQL script. See the corresponding documentation for details.
A summary of all the advices raised can be found in table
salix.summary_of_advices: For each concerned_schema
(PostgreSQL schema or MySQL database) and advice title, it shows
the advice category, severity, level, abstract, description,
holding relation view_name, and nb the total number of occurrences.
The detailed list of Salix Babylonica advices ordered by severity, category and level is:
Advice in relation salix.da_schema_wo_FK
Why use a relational database if data are not related at all? Well, that might happen...
Advice in relation salix.da_no_attribute_table
There must be something in a table.
Advice in relation salix.da_tables_wo_p_key_and_unique
All tuples must be uniquely defined to be consistant with the set theory. There is no unique subset of attribute which can be promoted as a PK.
Advice in relation salix.da_f_key_match_b_diff_sizes
A Foreign Key should have matching referencing and referenced type sizes.
Advice in relation salix.da_f_key_type_dont_match
A Foreign Key should have matching referencing and referenced types.
Advice in relation salix.dbva_engine_inconsistency
Different backends are used in the same database. It may be legitimate to do so if a particular feature of one backend is needed, for instance full text indexes.
Advice in relation salix.sa_trivial_password_superuser
SuperUser with empty or username password.
Advice in relation salix.da_nullable_attribute_rate_too_high
Warning: Most of the time, attributes should be NOT NULL. Too high a rate of nullable attribute may reveal that some fields are lacking a NOT NULL.
Advice in relation salix.da_attribute_count_per_table_too_many
Having so many attributes in the same table may reveal the need for additional relations.
Advice in relation salix.da_tables_w_composite_f_key
As for primary keys, simple foreign keys are usually better design, and make updates easier.
Advice in relation salix.da_f_key_not_ref_p_key
A Foreign Key should rather reference a Primary Key.
Advice in relation salix.da_int_pk_but_no_other_key
A simple integer primary key suggests that some other key must exist in the table.
Advice in relation salix.da_isolated_table
In a database design, tables are usually linked together.
Advice in relation salix.da_large_p_keys_referenced_by_f_key
Having large primary keys referenced by a foreign key may reveal data duplication, as the primary key is likely to contain relevant information.
Advice in relation salix.da_tables_wo_primary_key_but_unique
All tables should have a primary key to be consistant with the set theory. A unique constraint may be promoted as the primary key.
Advice in relation salix.da_attribute_pseudo_null_default
Possibly the NULL value was intended instead of the 'NULL' text.
Advice in relation salix.da_unique_nullable_attribute
A unique nullable attribute may be a bad design if NULL does not have a particular semantic.
Advice in relation salix.ca_cross_schema_dst_constraints
A constraint and its destination table are usually in the same schema.
Advice in relation salix.ca_cross_schema_src_constraints
A constraint and its source table should be in the same schema.
Advice in relation salix.ca_cross_schema_indexes
An index and its table should be in the same schema.
Advice in relation salix.dbva_schema_engine_is_unsafe
An unsafe backend (e.g. MyISAM) used at least once lacks referential integrity, transaction support, and is not crash safe.
Advice in relation salix.da_schema_identifiers_length_too_short
A schema name with less than 3 characters is really too short.
Advice in relation salix.da_table_identifiers_length_too_short
A table name with less than 2 characters is really too short.
Advice in relation salix.da_attribute_identifiers_length_too_short
An attribute name with 1 character is really too short.
Advice in relation salix.da_attribute_same_name_diff_types
Better avoid using the same attribute name with different types on different tables in the same application, as it may confuse the developer.
Advice in relation salix.sa_redundant_foreign_keys
Redundant Foreign Keys are costly to maintain.
Advice in relation salix.sa_redundant_indexes
Redundant indexes are costly to maintain.
Advice in relation salix.sa_trivial_password_user
User with empty or username password.
Advice in relation salix.da_nullable_attribute_rate_quite_high
Notice: Most of the time, attributes should be NOT NULL. Too high a rate of nullable attribute may reveal that some fields are lacking a NOT NULL.
Advice in relation salix.da_attribute_count_per_table_many
Having many attributes in the same table may suggest the need for additional relations.
Advice in relation salix.da_non_integer_p_key
Having integer primary keys without specific application semantics make updates easier.
Advice in relation salix.da_single_attribute_table
Possibly some more attributes are needed to have a semantic.
Advice in relation salix.da_tables_w_composite_p_key
A simple primary key, without specific semantics, is usually a better design, and references through foreign keys are simpler.
Advice in relation salix.ca_cross_schema_tables
Linked tables are usually in the same schema.
Advice in relation salix.dbva_database_is_mysql
MySQL lacks important features of the SQL standard, including missing set operators.
Advice in relation salix.da_table_names_mixed_style
Better use homogeneous table names.
Advice in relation salix.da_schema_identifiers_length_quite_short
A schema name with 4 characters is quite short.
Advice in relation salix.da_attribute_names_mixed_style
Better use homogeneous attribute names.
Advice in relation salix.da_table_identifiers_length_quite_short
A table name with 3 characters is quite short.
Advice in relation salix.da_attribute_identifiers_length_quite_short
An attribute name of 2 characters is quite short (but "id" and "pk").
Advice in relation salix.da_attribute_with_table_prefix
An attribute contains the name of its table, which is redundant.
Advice in relation salix.sa_table_without_index
Not a single index on a table.
Advice in relation salix.da_nullable_attribute_rate_high
Information: Most of the time, attributes should be NOT NULL. Too high a rate of nullable attribute may reveal that some fields are lacking a NOT NULL.
Advice in relation salix.dbva_table_engine_is_unsafe
An unsafe backend (e.g. MyISAM) lacks referential integrity, transaction support, and is not crash safe.
Advice in relation salix.da_schema_identifiers_length_short
A schema name with 5 characters is short.
Advice in relation salix.da_table_identifiers_length_short
A table name with 4 characters is short.
Advice in relation salix.da_attribute_identifiers_length_short
An attribute name with 3 characters is short.
Some environment variables have a direct impact on the sotfware:
Where to look for Salix Babylonica SQL files. A default is set when installing Salix, so this variable should not be necessary but when testing or if the files have been moved after the installation.
Salix Babylonica launches either the mysql or psql database clients
to execute some SQL scripts. They are found from the default search path.
Other environment variables might affect the database-specific scripting command launched by the tool. Check the database command documentation for details.
All softwares have bugs, this is a software, hence...
Beware that this software may destroy all your data, make you lose your hairs, or have any other unexpected side effect.
Do not run the software if you have a MySQL database or a PostgreSQL schema
named salix or salix_information_schema, as it could be messed up
or even destroyed in the process.
There is now way to restrict the script to report issues only on a subset of schemas from the command line. Filter out the results instead.
The software is named after the Latin word for the weeping willow tree, a.k.a. saule pleureur in French. It has nothing to do with the numerous companies with salix in their names.
Copyright 2008-2012 Alexandre Aillos, Samuel Pilot, Shamil Valeev, Fabien Coelho <salix dot babylonica at coelho dot net>
Salix Babylonica is free software, both inexpensive and with sources. The GNU General Public Licence v3 applies, see http://www.gnu.org/copyleft/gpl.html for details.
The summary is: you get as much as you paid for, and we are not responsible for anything.
The Salix software has been used to survey relational schemas in open-source software. The survey covers 512 schemas. The results of this study have been published in:
On the Quality of Relational Database Schemas in Open-source Software by Fabien Coelho, Alexandre Aillos, Samuel Pilot and Shamil Valeev. In International Journal on Advances in Software, 2011 vol 4, no 3&4, February 2012. Online at http://www.iariajournals.org/software/soft_v4_n34_2011_paged.pdf.
The above paper is an extended version of:
A Field Analysis of Relational Database Schemas in Open-source Software by Fabien Coelho, Alexandre Aillos, Samuel Pilot and Shamil Valeev. In Third International Conference on Advances in Databases, Knowledge and Data Applications (DBKDA), pp 9-15, St Maarten, The Netherlands Antilles, January 2011. ISBN: 978-1-61208-002-4. Copyright IARIA 2011. Online at http://www.thinkmind.org/index.php.
Note that this tool is inspired by pg-advisor, a proof-of-concept
PostgreSQL-specific prototype developed in 2004-2005 by Fabien Coelho.
The latest version of the software should be available from http://www.coelho.net/salix/#download.
Download this version as http://www.coelho.net/salix/slxbbl-1.0.0.tgz.
History of changes and versions:
Improved documentation. Comments added and improved error handling in salix script. Exchange salix arguments order, so that the operation is the first argument, as it seems more logical. The arguments are possibly silently exchanged for backward compatibility. Derive copyright year automatically from svn commit. Nicer report output for PostgreSQL.
Minor improvements in some advices. Add explanation string to all advices. Abort on error in PostgreSQL scripts. Add report command. Improved documentation.
More advices, including 3 from SchemaSpy http://schemaspy.sourceforge.net/. Partial work around small issue in PostgreSQL information_schema.
One more advice. Minor fix on one advice for excluding some schemas.
More advices, about weak passwords, indexes and object naming styles. Some bug fixes.
Check more MySQL backend engines. Improved documentation.
Fix string literal escapes for MySQL. Improved documentation.
Add two system advices about weak passwords for PostgreSQL. Add Id keyword to all source files. Use version in tar directory.
A few bug fixes. One new advice.
Better documentation. Bug fixes.
Greatly improved documentation. Better schema names.
Improved documentation. Rely on sh instead of bash.
Initial version.