DBIx::Admin::CreateTable - Create and drop tables, primary indexes, and sequences
#!/usr/bin/env perl use strict; use warnings; use DBI; use DBIx::Admin::CreateTable; # ---------------- my($dbh) = DBI -> connect(...); my($creator) = DBIx::Admin::CreateTable -> new(dbh => $dbh, verbose => 1); my($table_name) = 'test'; $creator -> drop_table($table_name); my($primary_key) = $creator -> generate_primary_key_sql($table_name); $creator -> create_table(<<SQL); create table $table_name ( id $primary_key, data varchar(255) ) SQL
See also xt/author/fk.t in DBIx::Admin::TableInfo.
DBIx::Admin::CreateTable is a pure Perl module.
DBIx::Admin::CreateTable
Database vendors supported: MySQL, Oracle, Postgres, SQLite.
Assumptions:
This is true for Postgres, where declaring a column as a primary key automatically results in the creation of an associated index for that column. The index is named after the table, not after the column.
This is true for both Oracle and Postgres, which use sequences to populate primary key columns. The sequences are named after both the table and the column.
new(...) returns an object of type DBIx::Admin::CreateTable.
This is the class contructor.
Usage: DBIx::Admin::CreateTable -> new().
This method takes a set of parameters. Only the dbh parameter is mandatory.
For each parameter you wish to use, call new as new(param_1 => value_1, ...).
This is a database handle, returned from the DBI connect() call.
This parameter is mandatory.
There is no default.
This is 0 or 1, to turn off or on printing of progress statements to STDERR.
This parameter is optional.
The default is 0.
Returns '' (empty string) if successful and DBI errstr() if there is an error.
$sql is the SQL to create the table.
$arg is an optional hash ref of options per table.
The keys are table names. The only sub-key at the moment is...
$arg = {$table_name_1 => {no_sequence => 1}, $table_name_2 => {no_sequence => 1} };
can be used to tell create_table not to create a sequence for the given table.
You would use this on a CGI::Session-type table called 'sessions', for example, when using Oracle or Postgres. With MySQL there would be no sequence anyway.
You would also normally use this on a table called 'log'.
The reason for this syntax is so you can use the same hash ref in a call to reset_all_sequences.
Usage with CGI::Session:
my($creator) = DBIx::Admin::CreateTable -> new(dbh => $dbh, verbose => 1); my($table_name) = 'sessions'; my($type) = $creator -> db_vendor() eq 'ORACLE' ? 'long' : 'text'; $creator -> drop_table($table_name); $creator -> create_table(<<SQL, {$table_name => {no_sequence => 1} }); create table $table_name ( id char(32) primary key, a_session $type not null ) SQL
Typical usage:
my($creator) = DBIx::Admin::CreateTable -> new(dbh => $dbh, verbose => 1); my($table_name) = 'test'; my($primary_key) = $creator -> generate_primary_key_sql($table_name); $creator -> drop_table($table_name); $creator -> create_table(<<SQL); create table $table_name ( id $primary_key, data varchar(255) ) SQL
The SQL generated by this call to create_table() is spelled-out in the (SQL) table below.
Action:
Method: create_table($table_name, $arg). Comment: Creation of tables and sequences. Sequence: See generate_primary_sequence_name($table_name). +----------|---------------------------------------------------+ | | Action for $$arg{$table_name} | | Vendor | {no_sequence => 0} | {no_sequence => 1} | +----------|------------------------------|--------------------+ | MySQL | Create table | Create table | +----------|------------------------------|--------------------+ | Oracle | Create sequence before table | Create table | +----------|------------------------------|--------------------+ | Postgres | Create sequence before table | Create table | +----------|------------------------------|--------------------+ | SQLite | Create table | Create table | +----------|------------------------------|--------------------+
SQL:
Method: create_table($table_name, $arg). Comment: SQL generated. Sequence: See generate_primary_sequence_name($table_name). +----------|-------------------------------------------------------------------------------------+ | | SQL for $$arg{$table_name} | | Vendor | {no_sequence => 0} | {no_sequence => 1} | +----------|------------------------------------------|------------------------------------------+ | MySQL | create table $table_name | create table $table_name | | | (id integer primary key | (id integer auto_increment | | | auto_increment, | primary key, | | | data varchar(255) ) | data varchar(255) ) | +----------|------------------------------------------|------------------------------------------+ | Oracle | create sequence ${table_name}_id_seq & | | | | create table $table_name | create table $table_name | | | (id integer primary key, | (id integer primary key, | | | data varchar(255) ) | data varchar(255) ) | +----------|------------------------------------------|------------------------------------------+ | Postgres | create sequence ${table_name}_id_seq & | | | | create table $table_name | create table $table_name | | | (id integer primary key | (id integer primary key | | | default nextval("${table_name}_id_seq"), | default nextval("${table_name}_id_seq"), | | | data varchar(255) ) | data varchar(255) ) | +----------|------------------------------------------|------------------------------------------+ | SQLite | create table $table_name | create table $table_name | | | (id integer primary key | (id integer autoincrement | | | autoincrement, | primary key, | | | data varchar(255) ) | data varchar(255) ) | +----------|------------------------------------------|------------------------------------------+
Returns an upper-case string identifying the database vendor.
Return string:
Method: db_vendor(db_vendor). Comment: Value returned. +----------|------------+ | Vendor | String | +----------|------------+ | MySQL | MYSQL | +----------|------------+ | Oracle | ORACLE | +----------|------------+ | Postgres | POSTGRESQL | +----------|------------+ | SQLite | SQLITE | +----------|------------+
Returns '' (empty string).
$table_name is the name of the table to drop.
$arg is an optional hash ref of options, the same as for create_table().
create_table()
Method: drop_table($table_name, $arg). Comment: Deletion of tables and sequences. Sequence: See generate_primary_sequence_name($table_name). +----------|-------------------------------------------------+ | | Action for $$arg{$table_name} | | Vendor | {no_sequence => 0} | {no_sequence => 1} | +----------|----------------------------|--------------------+ | MySQL | Drop table | Drop table | +----------|----------------------------|--------------------+ | Oracle | Drop sequence before table | Drop table | +----------|----------------------------|--------------------+ | Postgres | Drop sequence after table | Drop table | +----------|----------------------------|--------------------+ | SQLite | Drop table | Drop table | +----------|----------------------------|--------------------+
Method: drop_table($table_name, $arg). Comment: SQL generated. Sequence: See generate_primary_sequence_name($table_name). +----------|---------------------------------------------------------------+ | | SQL for $$arg{$table_name} | | Vendor | {no_sequence => 0} | {no_sequence => 1} | +----------|--------------------------------------|------------------------+ | MySQL | drop table $table_name | drop table $table_name | +----------|--------------------------------------|------------------------+ | Oracle | drop sequence ${table_name}_id_seq & | | | | drop table $table_name | drop table $table_name | +----------|--------------------------------------|------------------------+ | Postgres | drop table $table_name & | drop table $table_name | | | drop sequence ${table_name}_id_seq | | +----------|--------------------------------------|------------------------+ | SQLite | drop table $table_name | drop table $table_name | +----------|--------------------------------------|------------------------+
Note: drop_table() turns off RaiseError so we do not error if the sequence and table being deleted do not exist. This is new in V 2.00.
Returns the name of the index corresponding to the primary key for the given table.
The module does not call this method.
Method: generate_primary_index_name($table_name). Comment: Generation of name of the index for the primary key. +----------|--------------------+ | Vendor | SQL | +----------|--------------------+ | MySQL | | +----------|--------------------+ | Oracle | | +----------|--------------------+ | Postgres | ${table_name}_pkey | +----------|--------------------+ | SQLite | | +----------|--------------------+
Returns partial SQL for declaring the primary key for the given table.
See the Synopsis for how to use this method.
Method: generate_primary_key_sql($table_name). Comment: Generation of partial SQL for primary key. Sequence: See generate_primary_sequence_name($table_name). +----------|-----------------------------------------------------+ | Vendor | SQL | +----------|-----------------------------------------------------+ | MySQL | integer primary key auto_increment | +----------|-----------------------------------------------------+ | Oracle | integer primary key | +----------|-----------------------------------------------------+ | Postgres | integer primary key default nextval($sequence_name) | +----------|-----------------------------------------------------+ | SQLite | integer primary key autoincrement | +----------|-----------------------------------------------------+
Returns the name of the sequence used to populate the primary key of the given table.
Method: generate_primary_sequence_name($table_name). Comment: Generation of name for sequence. +----------|----------------------+ | Vendor | SQL | +----------|----------------------+ | MySQL | | +----------|----------------------+ | Oracle | ${table_name}_id_seq | +----------|----------------------+ | Postgres | ${table_name}_id_seq | +----------|----------------------+ | SQLite | | +----------|----------------------+
Returns nothing.
Resets the primary key sequence for all tables, except those marked by $arg as not having a sequence.
Note: This method only works if called against an object which knows the names of all tables and sequences. This means you must have called at least one of these, for each table:
Summary:
Method: reset_all_sequences($arg). Comment: Reset all sequences. +----------|-------------------------------------------------------+ | Vendor | Action | +----------|-------------------------------------------------------+ | MySQL | Do nothing | +----------|-------------------------------------------------------+ | Oracle | Call reset_sequence($table_name, $arg) for all tables | +----------|-------------------------------------------------------+ | Postgres | Call reset_sequence($table_name, $arg) for all tables | +----------|-------------------------------------------------------+ | SQLite | Do nothing | +----------|-------------------------------------------------------+
Resets the primary key sequence for the given table, except if it is marked by $arg as not having a sequence.
Method: reset_sequence($table_name, $arg). Comment: Reset one sequence. Sequence: The value of the sequence is set to the number of records in the table. +----------|-----------------------------------------+ | | Action for $$arg{$table_name} | | Vendor | {no_sequence => 0} | {no_sequence => 1} | +----------|--------------------|--------------------+ | MySQL | Do nothing | Do nothing | +----------|--------------------|--------------------+ | Oracle | Set sequence value | Do nothing | +----------|--------------------|--------------------+ | Postgres | Set sequence value | Do nothing | +----------|--------------------|--------------------+ | SQLite | Do nothing | Do nothing | +----------|--------------------|--------------------+
Versions as at 2014-03-07 +----------|------------+ | Vendor | V | +----------|------------+ | MariaDB | 5.5.36 | +----------|------------+ | Oracle | 10.2.0.1.0 | (Not tested for years) +----------|------------+ | Postgres | 9.1.12 | +----------|------------+ | SQLite | 3.7.17 | +----------|------------+
No. You have been warned.
References for 'Create table': MySQL. Postgres. SQLite.
Consider these:
create table one ( id integer primary key autoincrement, data varchar(255) ) $engine create table two ( id integer primary key autoincrement, one_id integer not null, data varchar(255), foreign key(one_id) references one(id) ) $engine
Putting the 'foreign key' clause at the end makes it a table constraint. Some database servers, e.g. MySQL and Postgres, allow you to attach it to a particular column, as explained next.
The creates work as given, where $engine eq 'engine = innodb'.
Further, you can re-order the clauses in the 2nd create:
create table two ( id integer primary key autoincrement, one_id integer not null, foreign key(one_id) references one(id), data varchar(255) ) $engine
This also works, where $engine eq 'engine = innodb'.
However, if you use:
create table two ( id integer primary key autoincrement, one_id integer not null references one(id), data varchar(255) ) $engine
Then the 'references' (foreign key) clause is parsed but discarded, even with 'engine = innodb'.
The creates work as given, where $engine = ''.
And you can re-order the clauses, as in the first example for MySQL.
But if you re-order the clauses:
Then you get a syntax error.
Then the 'references' (foreign key) clause is parsed, and it does create a foreign key relationship.
Do not forget this when using SQLite:
$dbh -> do('pragma foreign_keys = on') if ($dsn =~ /SQLite/i);
Depends on the server. Some databases, e.g. Postgres, do not want the name of the primary key in the insert statement if the server is to generate a value for a column.
SQL for insert:
Comment: SQL for insertion of rows containing auto-populated values. Sequence: See generate_primary_sequence_name($table_name). +----------|-----------------------------------------------------------------------+ | Vendor | SQL | +----------|-----------------------------------------------------------------------+ | MySQL | insert into $table_name (data) values (?) | +----------|-----------------------------------------------------------------------+ | Oracle | insert into $table_name (id, data) values ($sequence_name.nextval, ?) | +----------|-----------------------------------------------------------------------+ | Postgres | insert into $table_name (data) values (?) | +----------|-----------------------------------------------------------------------+ | SQLite | insert into $table_name (id, data) values (undef, ?) | +----------|-----------------------------------------------------------------------+
Well, no, actually. See next question.
By including the name and the value in the insert statement.
Comment: SQL for insertion of rows overriding auto-populated values. +----------|--------------------------------------------------+ | Vendor | SQL | +----------|--------------------------------------------------+ | MySQL | insert into $table_name (id, data) values (?, ?) | +----------|--------------------------------------------------+ | Oracle | insert into $table_name (id, data) values (?, ?) | +----------|--------------------------------------------------+ | Postgres | insert into $table_name (id, data) values (?, ?) | +----------|--------------------------------------------------+ | SQLite | insert into $table_name (id, data) values (?, ?) | +----------|--------------------------------------------------+
Yes. All servers document primary key as meaning both non null and unique.
DBIx::Admin::DSNManager.
DBIx::Admin::TableInfo.
Version numbers < 1.00 represent development versions. From 1.00 up, they are production versions.
https://github.com/ronsavage/DBIx-Admin-CreateTable
Bugs should be reported via the CPAN bug tracker at
https://github.com/ronsavage/DBIx-Admin-CreateTable/issues
DBIx::Admin::CreateTable was written by Ron Savage <ron@savage.net.au> in 2006.
http://savage.net.au/
Australian copyright (c) 2006, Ron Savage. All Programs of mine are 'OSI Certified Open Source Software'; you can redistribute them and/or modify them under the terms of the Artistic or the GPL licences, copies of which is available at: http://www.opensource.org/licenses/index.html
To install DBIx::Admin::CreateTable, copy and paste the appropriate command in to your terminal.
cpanm
cpanm DBIx::Admin::CreateTable
CPAN shell
perl -MCPAN -e shell install DBIx::Admin::CreateTable
For more information on module installation, please visit the detailed CPAN module installation guide.