NAME

SQL::AlterTable::SQLite - Generate SQL statements to alter a SQLite table

VERSION

This document describes version 0.003 of SQL::AlterTable::SQLite (from Perl distribution SQL-AlterTable-SQLite), released on 2016-07-10.

SYNOPSIS

 use SQL::AlterTable::SQLite qw(gen_sql_alter_table);

 my $sql_stmts = gen_sql_alter_table(
     dbh            => $dbh,
     table          => 't',
     delete_columns => ['b'],
     modify_columns => ['a', 'INT NOT NULL'],
     rename_columns => ['a', 'a2'],
     add_columns    => ['c', 'TEXT'],
     rename_table   => 't2',
 );

The result:

 [
     'CREATE TABLE "_t_tmp" ("a2" INT NOT NULL)',
     'INSERT INTO "_t_tmp" ("a2") SELECT "a" FROM "t"',
     'DROP TABLE "t"',
     'ALTER TABLE "_t_tmp" RENAME TO "t2"',
     'ALTER TABLE "t2" ADD COLUMN "c" TEXT',
 ]

DESCRIPTION

FUNCTIONS

gen_sql_alter_table(%args) -> array[str]

Generate SQL statements to alter a SQLite table.

Performing ALTER TABLE on a SQLite table can be a bit cumbersome. SQLite only supports a limited ALTER TABLE functionality, i.e. rename a table or add new columns. For the other functionality, e.g. rename a column or delete/modify columns, a common technique is to construct a new table with the desired structure, fill it with rows from the old table, then delete the old table and rename the new table to the old.

This routine can help. You specify the operations you want, the table information, and the routine will generate a series of SQL statements.

This function is not exported by default, but exportable.

Arguments ('*' denotes required arguments):

• add_columns => array[str]

  Add columns.

  Value should be an arrayref of pairs of name-definition.

• dbh* => obj

  DBI database handle.

• delete_columns => array[str]

  Delete columns.

  Value should be an arrayref of column names to delete.

• modify_columns => array[str]

  Modify columns.

  Value should be an arrayref of pairs of name-new definition.

• rename_columns => array[str]

  Rename columns.

  Value should be an arrayref of pairs of old column name-new column name.

• rename_table => str

  New table name.

• table* => str

  Table name.

Return value: (array[str])

HOMEPAGE

Please visit the project's homepage at https://metacpan.org/release/SQL-AlterTable-SQLite.

SOURCE

Source repository is at https://github.com/perlancar/perl-SQL-AlterTable-SQLite.

BUGS

Please report any bugs or feature requests on the bugtracker website https://rt.cpan.org/Public/Dist/Display.html?Name=SQL-AlterTable-SQLite

When submitting a bug or request, please include a test-file or a patch to an existing test-file that illustrates the bug or desired feature.

SEE ALSO

• SQL::Schema::Versioned

  You can feed the result of gen_sql_alter_table() to SQL::Schema::Versioned's create_or_update_db_schema.

AUTHOR

perlancar <perlancar@cpan.org>

COPYRIGHT AND LICENSE

This software is copyright (c) 2016 by perlancar@cpan.org.

This is free software; you can redistribute it and/or modify it under the same terms as the Perl 5 programming language system itself.

cpanm SQL::AlterTable::SQLite

perl -MCPAN -e shell
install SQL::AlterTable::SQLite