DBIx::...eTable(3pm)

✨ NAME ✨

DBIx::Admin::CreateTable — that's the star of this page, aka the DBIx::Admin::CreateTable(3pm) manual, bestie 💖 — and what she does is Create and drop tables, primary indexes, and sequences. Periodt.

---

🎀 SYNOPSIS 🎀

#!/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

Also go peek at xt/author/fk.t in DBIx::Admin::TableInfo. 👀

---

🦋 DESCRIPTION 🦋

DBIx::Admin::CreateTable is a pure Perl module. No C, no XS, just vibes. 💅

Database vendors supported: MySQL, Oracle, Postgres, SQLite. Four icons, zero drama.

Assumptions (aka the rules of the house, no exceptions):

• Every table has a primary key
• The primary key is a unique, non-null, integer
• The primary key is a single column
• The primary key column is called 'id'
• If a primary key has a corresponding auto-created index, the index is called 't_pkey'
  • 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.
• If a table 't' (with primary key 'id') has an associated sequence, the sequence is called 't_id_seq'
  • 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.

---

🌸 CONSTRUCTOR AND INITIALIZATION 🌸

new(...) returns an object of type DBIx::Admin::CreateTable. Iconic behavior, honestly.

This is the class constructor.

Usage: DBIx::Admin::CreateTable -> new().

This method takes a set of parameters. Only the dbh parameter is mandatory. Everything else is optional, she's chill like that.

For each parameter you wish to use, call new as new(param_1 => value_1, ...).

• dbh — This is a database handle, returned from the DBI connect() call.
  • This parameter is mandatory. No dbh, no party.
  • There is no default.
• verbose — This is 0 or 1, to turn off or on printing of progress statements to STDERR.
  • This parameter is optional.
  • The default is 0.

---

💜 METHOD: CREATE_TABLE($SQL, $ARG) 💜

Returns '' (empty string) if successful and DBI errstr() if there is an error. So like, empty string = slay, errstr() = not slay. 😭

$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...

• no_sequence

$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).

• MySQL — {no_sequence => 0}: Create table / {no_sequence => 1}: Create table
• Oracle — {no_sequence => 0}: Create sequence before table / {no_sequence => 1}: Create table
• Postgres — {no_sequence => 0}: Create sequence before table / {no_sequence => 1}: Create table
• SQLite — {no_sequence => 0}: Create table / {no_sequence => 1}: Create table

🔥 SQL 🔥

Method: create_table($table_name, $arg). Comment: SQL generated. Sequence: See generate_primary_sequence_name($table_name).

MySQL with {no_sequence => 0}:

create table $table_name
(id integer primary key
      auto_increment,
   data varchar(255) )

MySQL with {no_sequence => 1}:

create table $table_name
(id integer auto_increment
      primary key,
   data varchar(255) )

Oracle with {no_sequence => 0}:

create sequence ${table_name}_id_seq &
create table $table_name
(id integer primary key,
   data varchar(255) )

Oracle with {no_sequence => 1}:

create table $table_name
(id integer primary key,
   data varchar(255) )

Postgres with {no_sequence => 0}:

create sequence ${table_name}_id_seq &
create table $table_name
(id integer primary key
default nextval("${table_name}_id_seq"),
   data varchar(255) )

Postgres with {no_sequence => 1}:

create table $table_name
(id integer primary key
default nextval("${table_name}_id_seq"),
   data varchar(255) )

SQLite with {no_sequence => 0}:

create table $table_name
(id integer primary key
      autoincrement,
   data varchar(255) )

SQLite with {no_sequence => 1}:

create table $table_name
(id integer autoincrement
      primary key,
   data varchar(255) )

---

👑 METHOD: DB_VENDOR() 👑

Returns an upper-case string identifying the database vendor. Like a little name tag but SCREAMING. 📣

Method: db_vendor(db_vendor). Comment: Value returned.

• MySQL → MYSQL
• Oracle → ORACLE
• Postgres → POSTGRESQL
• SQLite → SQLITE

Vibe check passed: every vendor gets her own uppercase string, no cap. 💖

💅 Method: drop_table($table_name, $arg)

Returns '' — an empty string. That's it. That's the whole return value. 💀

• $table_name — the name of the table you're yeeting.
• $arg — optional hash ref of options, the exact same ones as for create_table().

✨ The action, explained

Method: drop_table($table_name, $arg). Comment: Deletion of tables and sequences. Sequence: see generate_primary_sequence_name($table_name).

So here's what goes down, depending on what $$arg{$table_name} is holding:

• MySQL
  • {no_sequence => 0} → Drop table
  • {no_sequence => 1} → Drop table
• Oracle
  • {no_sequence => 0} → Drop sequence before table
  • {no_sequence => 1} → Drop table
• Postgres
  • {no_sequence => 0} → Drop sequence after table
  • {no_sequence => 1} → Drop table
• SQLite
  • {no_sequence => 0} → Drop table
  • {no_sequence => 1} → Drop table

🧾 The SQL it actually spits out

Method: drop_table($table_name, $arg). Comment: SQL generated. Sequence: see generate_primary_sequence_name($table_name).

• MySQL
  • {no_sequence => 0} → drop table $table_name
  • {no_sequence => 1} → drop table $table_name
• Oracle
  • {no_sequence => 0} → drop sequence ${table_name}_id_seq & drop table $table_name
  • {no_sequence => 1} → drop table $table_name
• Postgres
  • {no_sequence => 0} → drop table $table_name & drop sequence ${table_name}_id_seq
  • {no_sequence => 1} → drop table $table_name
• SQLite
  • {no_sequence => 0} → drop table $table_name
  • {no_sequence => 1} → 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.

---

🦋 Method: generate_primary_index_name($table_name)

Returns the name of the index that corresponds to the primary key for the table you hand it. So helpful.

Important tea: the module does not call this method. It's just sitting there, existing, being available. Do with that what you will. 💅

Method: generate_primary_index_name($table_name). Comment: Generation of name of the index for the primary key.

• MySQL — (nothing, bestie)
• Oracle — (also nothing)
• Postgres — ${table_name}_pkey
• SQLite — (nothing)

---

👑 Method: generate_primary_key_sql($table_name)

Returns partial SQL for declaring the primary key of the given table.

Wanna know how to actually use it? Go peek at the Synopsis. 👀

Method: generate_primary_key_sql($table_name). Comment: Generation of partial SQL for primary key. Sequence: see generate_primary_sequence_name($table_name).

• MySQL — integer primary key auto_increment
• Oracle — integer primary key
• Postgres — integer primary key default nextval($sequence_name)
• SQLite — integer primary key autoincrement

---

🌸 Method: generate_primary_sequence_name($table_name)

Returns the name of the sequence that's used to populate the primary key of the given table. Naming things, iconic behavior.

Method: generate_primary_sequence_name($table_name). Comment: Generation of name for sequence.

• MySQL — (crickets)
• Oracle — ${table_name}_id_seq
• Postgres — ${table_name}_id_seq
• SQLite — (crickets)

---

🔥 Method: reset_all_sequences($arg)

Returns nothing. Like, genuinely nothing. It just does the thing and leaves.

It resets the primary key sequence for all tables — except the ones that $arg marks as not having a sequence. Respect the flags, periodt.

Note: This method only works if you call it against an object that already knows the names of all the tables and sequences. That means you must have called at least one of these, for each table: - create_table - drop_table - generate_primary_key_sql - generate_primary_sequence_name

$arg is an optional hash ref of options, same as for create_table().

📋 The summary

Method: reset_all_sequences($arg). Comment: Reset all sequences.

• 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

---

🧚 Method: reset_sequence($table_name, $arg)

Returns nothing again. Our lowkey silent king. 🤫

It resets the primary key sequence for the given table — unless $arg has marked it as not having a sequence.

$arg is an optional hash ref of options, same as for create_table().

📋 The summary

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.

Now for $$arg{$table_name}:

• MySQL
  • {no_sequence => 0} → Do nothing
  • {no_sequence => 1} → Do nothing
• Oracle
  • {no_sequence => 0} → Set sequence value
  • {no_sequence => 1} → Do nothing
• Postgres
  • {no_sequence => 0} → Set sequence value
  • {no_sequence => 1} → Do nothing
• SQLite
  • {no_sequence => 0} → Do nothing
  • {no_sequence => 1} → Do nothing

💖 FAQ 💖

✨ Which versions of the servers did you test? ✨

Versions as at 2014-03-07

• MariaDB — 5.5.36
• Oracle — 10.2.0.1.0 (Not tested for years)
• Postgres — 9.1.12
• SQLite — 3.7.17

---

🚨 Do all database servers accept the same 'create table' commands? 🚨

No. You have been warned. 😭

References for 'Create table': MySQL https://dev.mysql.com/doc/refman/5.7/en/create-table.html. Postgres http://www.postgresql.org/docs/9.3/interactive/sql-createtable.html. SQLite https://sqlite.org/lang_createtable.html.

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.

💅 MySQL

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'.

🌸 Postgres

The creates work as given, where $engine = ''.

And you can re-order the clauses, as in the first example for MySQL.

🦋 SQLite

The creates work as given, where $engine = ''.

But if you re-order the clauses:

create table two
(
        id      integer primary key autoincrement,
        one_id  integer not null,
        foreign key(one_id) references one(id),
        data    varchar(255)
) $engine

Then you get a syntax error.

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, 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);

---

🙈 Do I include the name of an auto-populated column in an insert statement? 🙈

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).

• 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, ?)

---

🌈 Do I have to use a sequence to populate a primary key? 🌈

Well, no, actually. See next question.

---

💖 How to I override the auto-populated value for a primary key column? 💖

By including the name and the value in the insert statement.

SQL for insert:

Comment: SQL for insertion of rows overriding auto-populated values.

• 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 (?, ?)

---

👑 Are primary keys always not null and unique? 👑

Yes. All servers document primary key as meaning both non null and unique.

---

🎀 See Also 🎀

• DBIx::Admin::DSNManager.
• DBIx::Admin::TableInfo.

---

✨ Version Numbers ✨

Version numbers < 1.00 represent development versions. From 1.00 up, they are production versions.

---

🌈 Repository 🌈

https://github.com/ronsavage/DBIx-Admin-CreateTable

---

🫶 Support 🫶

Bugs should be reported via the CPAN bug tracker at

https://github.com/ronsavage/DBIx-Admin-CreateTable/issues

---

👑 Author 👑

"DBIx::Admin::CreateTable" was written by Ron Savage <ron@savage.net.au> in 2006.

http://savage.net.au/

---

💜 Copyright 💜

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

---

Okay bestie, this page is from perl v5.36.0, dated 2023-01-22, and it's the DBIx::Admin::CreateTable(3pm) man page. Go slay some tables! 💅✨