salix - Salix Babylonica: advices about relational database schemas.
salix (pg|my) (create|show|drop) <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 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.
MySQL does not have catalogs, as the DATABASE is really a SCHEMA
(a directory which stores tables), thus it is enough to run the tool
only once against an installation.
The second 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.
Show a summary of the advices.
Use you favorite database browser to explore the salix schema
for detailed information, and possibly to fix the database schemas.
Drop Salix Babylonica-related schemas.
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.
With PostgreSQL:
sh> salix pg create -U calvin -h server erp
sh> salix pg show -U calvin -h server erp ...
sh> salix pg drop -U calvin -h server erp
With MySQL:
sh> salix my create -u calvin -h server
sh> salix my show -u calvin -h server ...
sh> salix my drop -u calvin -h server
The command returns the 0 status code if all was okay. Other error codes are returned by the script or by the database client command.
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_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_over_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_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_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.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.
where to look for Salix Babylonica SQL files.
Salix Babylonica launches either the mysql or psql database clients
to execute some SQL scripts. They are found from the default search path.
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.
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-2010 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.
It 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-0.7.1.tgz.
History of changes:
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.