Salix Babylonica

Weeping Willow Tree

NAME
SYNOPSIS
DESCRIPTION
ARGUMENTS
EXAMPLES
EXIT STATUS
ADVICES
ENVIRONMENT
CAVEATS
AUTHORS
LICENCE
DOWNLOAD

NAME

slxbbl - Salix Babylonica: advices about relational database schemas.

SYNOPSIS

slxbbl (pg|my) (create|show|delete) <database connection options...>

slxbbl gives design, style, consistency and version advices about relational database schemas by querying the standard information schema. The results are put in a slxbbl 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.

ARGUMENTS

The first argument is the target database engine.

pg - PostgreSQL: 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.
my - MySQL: 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.

create: Creates the Salix Babylonica slxbbl schema which holds the advices, as well as slxbbl_information_schema, a normalized copy of the information schema.
show: Show a summary of the advices.
delete: Delete 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.

EXAMPLES

With PostgreSQL:

  sh> slxbbl pg create -U calvin -h server erp

  sh> slxbbl pg show -U calvin -h server erp
  ...

  sh> slxbbl pg delete -U calvin -h server erp

With MySQL:

  sh> slxbbl my create -u calvin -h server

  sh> slxbbl my show -u calvin -h server
  ...

  sh> slxbbl my delete -u calvin -h server

EXIT STATUS

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.

ADVICES

A summary of all the advices raised can be found in table slxbbl.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:

Schema without any FK (schema design error)

Advice in relation slxbbl.da_schema_wo_FK

Why use a relational database if data are not related at all? Well, that might happen...

Tables without PK nor Unique (table design error)

Advice in relation slxbbl.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.

FK length mismatch (table consistency error)

Advice in relation slxbbl.da_f_key_match_b_diff_sizes

A Foreign Key should have matching referencing and referenced type sizes.

FK type mismatch (table consistency error)

Advice in relation slxbbl.da_f_key_type_dont_match

A Foreign Key should have matching referencing and referenced types.

Engine inconsistency (schema version error)

Advice in relation slxbbl.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.

Nullable attribute rate over 80% (schema design warning)

Advice in relation slxbbl.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.

Attribute count per table over 40 (table design warning)

Advice in relation slxbbl.da_attribute_count_per_table_over_too_many

Having so many attributes in the same table may reveal the need for additional relations.

Composite Foreign Key (table design warning)

Advice in relation slxbbl.da_tables_w_composite_f_key

As for primary keys, simple foreign keys are usually better design, and make updates easier.

FK not referencing a PK (table design warning)

Advice in relation slxbbl.da_f_key_not_ref_p_key

A Foreign Key should rather reference a Primary Key.

Integer PK but no other key (table design warning)

Advice in relation slxbbl.da_int_pk_but_no_other_key

A simple integer primary key suggests that some other key must exist in the table.

Isolated Tables (table design warning)

Advice in relation slxbbl.da_isolated_table

In a database design, tables are usually linked together.

Large PK referenced by a FK (table design warning)

Advice in relation slxbbl.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.

Tables without PK but with Unique (table design warning)

Advice in relation slxbbl.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.

Unique nullable attributes (attribute design warning)

Advice in relation slxbbl.da_unique_nullable_attribute

A unique nullable attribute may be a bad design if NULL does not have a particular semantic.

MyISAM is used in schema (schema version warning)

Advice in relation slxbbl.dbva_schema_engine_is_myisam

The MyISAM backend used at least once lacks referential integrity and transaction support.

Attribute name length too short (attribute style warning)

Advice in relation slxbbl.da_attribute_identifiers_length_too_short

An attribute name with 1 character is really too short.

Homonymous heterogeneous attributes (attribute style warning)

Advice in relation slxbbl.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.

Table name length too short (attribute style warning)

Advice in relation slxbbl.da_table_identifiers_length_too_short

A table name with less than 2 characters is really too short.

Nullable attribute rate in 50-80% (schema design notice)

Advice in relation slxbbl.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.

Attribute count per table over 20 (table design notice)

Advice in relation slxbbl.da_attribute_count_per_table_many

Having many attributes in the same table may suggest the need for additional relations.

Non-integer Primary Key (table design notice)

Advice in relation slxbbl.da_non_integer_p_key

Having integer primary keys without specific application semantics make updates easier.

Tables with Composite PK (table design notice)

Advice in relation slxbbl.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.

MySQL is used (schema version notice)

Advice in relation slxbbl.dbva_database_is_mysql

MySQL lacks important features of the SQL standard, including missing set operators.

Table name length quite short (table style notice)

Advice in relation slxbbl.da_table_identifiers_length_quite_short

A table name with 3 characters is quite short.

Attribute name length quite short (attribute style notice)

Advice in relation slxbbl.da_attribute_identifiers_length_quite_short

An attribute name of 2 characters is quite short (but ``id'' and ``pk'').

Nullable attribute rate in 20-50% (schema design information)

Advice in relation slxbbl.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.

MyISAM is used on table (table version information)

Advice in relation slxbbl.dbva_table_engine_is_myisam

The MyISAM backend lacks referential integrity and transaction support.

Table name length short (table style information)

Advice in relation slxbbl.da_table_identifiers_length_short

A table name with 4 characters is short.

Attribute name length short (attribute style information)

Advice in relation slxbbl.da_attribute_identifiers_length_short

An attribute name with 3 characters is short.

ENVIRONMENT

SLXBBL_HOME: where to look for Salix Babylonica SQL files.
PATH: Salix Babylonica launches either the mysql or psql database clients to execute some SQL scripts. They are found from the default search path.

CAVEATS

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 slxbbl or slxbbl_information_schema, as it could be messed up or even deleted 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.