DBIO-MySQL

 view release on metacpan or  search on metacpan

lib/DBIO/MySQL/DDL.pm  view on Meta::CPAN

      $foreign_name = $$foreign_name if ref $foreign_name eq 'SCALAR';

      push @col_defs, sprintf '  FOREIGN KEY (%s) REFERENCES %s(%s)',
        join(', ', map { _quote_ident($_) } @from),
        _quote_ident($foreign_name),
        join(', ', map { _quote_ident($_) } @to);
    }

    my $engine  = ($result_class->can('mysql_engine')  && $result_class->mysql_engine)  || 'InnoDB';
    my $charset = ($result_class->can('mysql_charset') && $result_class->mysql_charset) || 'utf8mb4';
    my $collate = ($result_class->can('mysql_collate') && $result_class->mysql_collate) || 'utf8mb4_unicode_ci';

    push @stmts, sprintf "CREATE TABLE %s (\n%s\n) ENGINE=%s DEFAULT CHARSET=%s COLLATE=%s;",
      _quote_ident($table_name),
      join(",\n", @col_defs),
      $engine, $charset, $collate;

    # MySQL-specific indexes via mysql_indexes (parallel to pg_indexes)
    if ($result_class->can('mysql_indexes')) {
      my $indexes = $result_class->mysql_indexes;
      for my $idx_name (sort keys %$indexes) {

lib/DBIO/MySQL/DDL.pm  view on Meta::CPAN

=head1 DESCRIPTION

C<DBIO::MySQL::DDL> generates a MySQL DDL script from a L<DBIO::Schema>
class hierarchy. It is the desired-state side of the
test-deploy-and-compare strategy used by L<DBIO::MySQL::Deploy>.

    my $ddl = DBIO::MySQL::DDL->install_ddl($schema);

The generated DDL is plain SQL, one C<CREATE TABLE> per source. Tables
default to C<ENGINE=InnoDB> and C<CHARSET=utf8mb4 COLLATE
utf8mb4_unicode_ci>. Result classes can override per-table via
C<mysql_engine>, C<mysql_charset>, C<mysql_collate> attributes.

=head1 METHODS

=head2 install_ddl

    my $ddl = DBIO::MySQL::DDL->install_ddl($schema);

Returns the full installation DDL as a single string.

lib/DBIO/MySQL/Result.pm  view on Meta::CPAN

Load it with:

    package MyApp::DB::Result::User;
    use base 'DBIO::Core';
    __PACKAGE__->load_components('MySQL::Result');

    __PACKAGE__->table('users');
    __PACKAGE__->mysql_engine('InnoDB');
    __PACKAGE__->mysql_charset('utf8mb4');
    __PACKAGE__->mysql_collate('utf8mb4_unicode_ci');

    __PACKAGE__->mysql_index('idx_users_name' => {
        columns => ['name'],
    });

    __PACKAGE__->mysql_index('idx_users_fulltext' => {
        using   => 'FULLTEXT',
        columns => ['bio'],
    });

lib/DBIO/MySQL/Result.pm  view on Meta::CPAN

=head2 mysql_charset

    __PACKAGE__->mysql_charset('utf8mb4');
    my $c = $class->mysql_charset;

Get or set the default character set for this table. Defaults to
C<utf8mb4> in generated DDL when unset.

=head2 mysql_collate

    __PACKAGE__->mysql_collate('utf8mb4_unicode_ci');
    my $c = $class->mysql_collate;

Get or set the default collation for this table. Defaults to
C<utf8mb4_unicode_ci> in generated DDL when unset.

=head2 mysql_index

    __PACKAGE__->mysql_index('idx_users_name' => {
        columns => ['name'],
    });
    __PACKAGE__->mysql_index('idx_users_fulltext' => {
        using   => 'FULLTEXT',
        columns => ['bio'],
    });

share/skills/dbio-mysql-database/SKILL.md  view on Meta::CPAN

Detection: `($version =~ /MariaDB/i)`

## Storage Engines

InnoDB = default, row-level locking, ACID, FK. MyISAM = legacy, table-lock, no transactions. MEMORY = temp, lost on restart. Aria = MariaDB crash-safe MyISAM replacement.

Always assume InnoDB for DBIO driver.

## Character Sets

`utf8mb4` only — MySQL's `utf8` is 3-byte broken. Connection: `SET NAMES utf8mb4`. Table: `CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci` (MySQL 8.0+) or `utf8mb4_general_ci` (MariaDB default).

## Type System

Numeric: TINYINT(1), SMALLINT(2), MEDIUMINT(3), INT(4), BIGINT(8), DECIMAL, FLOAT, DOUBLE.
String: VARCHAR(65535), TEXT, MEDIUMTEXT(16MB), LONGTEXT(4GB), ENUM, SET, JSON(1GB).
Date: DATE, TIME, DATETIME, TIMESTAMP (1970-2038, UTC).

## Auto-Increment

`LAST_INSERT_ID()` per-connection. Perl: `$dbh->last_insert_id` or `$dbh->{mysql_insertid}`.