db_suit_rails - Database conversion to suit Ruby-on-Rails¶ ↑
Summary¶ ↑
Script to create a SQL file to inject a table that follows the convention of Rails.
Install¶ ↑
This script requires Ruby Version 2.0 or above, and Rails library (called gem) of version 5.0 or above. The standard way to install Rails gem is from the command-line
Prompt% gem install rails
Put the main-script file mk_sqlskelton
in any of your command-line search paths and give it the executable permission.
You may need to modify the first line (Shebang line) of the script to suit your environment (it should be unnecessary for Linux and MacOS), or run it explicitly with your Ruby command as
Prompt% /YOUR/ENV/ruby /YOUR/INSTALLED/mk_sqlskelton
Prior to it, make sure the RUBYLIB environment variable contains the library directory to this gem, which is
/THIS/GEM/LIBRARY/PATH/db_suit_rails/lib
Simple Examples¶ ↑
All you need to do is to run
mk_sqlskelton [OPTIONS] OriginalSQL.sql
You may start with
mk_sqlskelton --help
to see the available options, which are described below in detail.
Description¶ ↑
This script
-
pluralizes all the table names,
-
changes table names containing `_[0-9]` into `_n[0-9]` ,
-
changes table names `id` into `id_cli` ,
-
changes table names containing `*_id` into `*_id_cli` ,
-
removes all the PRIMARY KEY constraints (n.b., those individual columns are not UNIQUE, either, any more),
-
creates the new PRIMARY KEY named 'id' in each table,
-
sets sets of composite PRIMARY KEY as equivalent UNIQUE keys,
-
changes the reference settings, reflecting all these changes,
-
removes all the triggers.
The script is far from perfect, but is useful enough for practical uses.
For example, the script assumes a certain format for the input SQL file. Crucially, CREATE TABLE statements should have newlines at usual locations, and other statements should not have newlines inside a single sentence.
It tries to handle references. However, there are many forms to specify references in PostgreSQL, and this script handles not all of them.
Also, its support for composite unique keys and their references are also limited. If you encounter an error like
there is no unique constraint matching given keys for referenced table "...."
then chances are, unique constrants are not set appropriately.
SEQUENCEs are preserved, though probably they should not be.
Essentially, not all the unique keys in the current table may be applicable in the generated table for Rails, depending on the specification of the table! For example, if a column references the primary key in another table, would it be still valid, after the primary key is changed into a normal column, that is the ex-primary-key should really be unique after the migration? If a new table combines entries from multiple tables, the column that used to be unique in a single table man not be unique any more. It entirely depends on what you wnat in the newly generated database and tables.
Command-line options¶ ↑
USAGE: mk_sqlskelton [options] OriginalSQL.sql
- -e
-
Regular expression for the tables to convert
- -o, –output
-
Output SQL file. In default, OriginalSQL_ror.sql
- -i, –index
-
Index SQL file to list old and new. In default, ./index.sql
- -n, –[no-]dryrun
-
Dryrun (Def: false). It still attempts to access the files, and then you can tell which files exist and which do not.
Examples¶ ↑
Developer's note¶ ↑
Algorithm¶ ↑
-
The components for the output SQL filename and components (table and column names) are represented by a hash in the current imprementation as in Ver.0.3, but it had better be by a class.
Tests¶ ↑
Ruby codes under the directory test/
are the test scripts. You can run them from the top directory as ruby test/test_****.rb
or simply run make test
.
Known bugs¶ ↑
None.
Copyright¶ ↑
- Author
-
Masa Sakano < info a_t wisebabel dot com >
- Versions
-
The versions of this package follow Semantic Versioning (2.0.0) semver.org/