use strict;
=pod
=head Generate MySQL DDL code
=head Methods
=cut
# TODO use DBI quotes
=item load types
Adds MySQL types map to the object
=cut
# Mappings for DB datatypes
sub load_types {
my $self = shift;
$self->{data_types} = {
# Numbers
# range -2147483648 to 2147483647
int => 'INT',
# range -32768 to 32767
smallint => 'SMALLINT',
# range -9223372036854775808 to 9223372036854775807
bigint => 'BIGINT',
# range -3.402823466E+38 to -1.175494351E-38, 0, and 1.175494351E-38 to 3.402823466E+38
real => 'FLOAT',
# range -1.7976931348623157E+308 to -2.2250738585072014E-308, 0, and 2.2250738585072014E-308 to 1.7976931348623157E+308
double => 'DOUBLE',
# tinyint -128 to 128
# mediumint -8388608 to 8388607
# Dates
# range '1000-01-01' to '9999-12-31'
date => 'DATE',
# range '-838:59:59' to '838:59:59'
time => 'TIME',
# range '1000-01-01 00:00:00' to '9999-12-31 23:59:59'
# rationale timestamp only had range '1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07'
# format YYYY-MM-DD HH:MM:SS or YY-MM-DD HH:MM:SS
timestamp => 'DATETIME',
## Characters
#
## range 1 through 255
## precision 0 to 255
## default 1
#char => 'CHAR',
## range 1 through 65,535
## precision 0 to 65,535
#varchar => 'VARCHAR',
## range 65,535 (2E16 – 1)
## precision none
#text => 'TEXT',
# Unicode Characters
# range 1 through 255
# precision 0 to 255
# default 1
char => 'NATIONAL CHAR',
# range 1 through 21,844
# precision 0 through 21,844
varchar => 'NATIONAL VARCHAR',
# range 4,294,967,295 or 4GB (232 – 1)
# precision none
text => 'LONGTEXT',
# Special
# serials
# range 1 to 4294967295
serial => 'INT NOT NULL AUTO_INCREMENT UNIQUE',
# range 1 to 18446744073709551615
bigserial => 'SERIAL',
};
}#sub
=item create_table
Creates the table code
=cut
sub create_table {
my $self = shift;
my ( $table, $details ) = @_;
my $tablecode = 'CREATE TABLE ' . $self->qid($table) . " (\n";
my $column_count = 0;
foreach my $column ( @{ $details->{columns} } ) {
$tablecode .= ",\n" if $column_count > 0;
$tablecode .= "\t" . $self->create_column($column);
$column_count++;
}#foreach
$tablecode .= ",\n\t" . $self->create_primary_key( $details->{primary_key} ) if $details->{primary_key};
$tablecode .= "\n) ENGINE = InnoDB CHARACTER SET utf8 COLLATE utf8_unicode_ci";
return $tablecode;
}#sub
=item drop_index
Creates the drop index code
=cut
sub drop_index {
my $self = shift;
my ( $table, $index ) = @_;
# my $indexcode = q~DELIMITER $$
#DROP PROCEDURE IF EXISTS `util`.`DropIndex` $$
#CREATE PROCEDURE `util`.`DropIndex` (tblSchema VARCHAR(64), tblName VARCHAR(64), ndxName VARCHAR(64))
#BEGIN
# DECLARE IndexColumnCount INT;
# DECLARE SQLStatement VARCHAR(256);
#
# SELECT COUNT(1) INTO IndexColumnCount
# FROM information_schema.statistics
# WHERE table_schema = tblSchema
# AND table_name = tblName
# AND index_name = ndxName;
#
# IF IndexColumnCount > 0 THEN
# SET SQLStatement = CONCAT('ALTER TABLE `',tblSchema,'`.`',tblName,'` DROP INDEX `',ndxName,'`');
# SET @SQLStmt = SQLStatement;
# PREPARE s FROM @SQLStmt;
# EXECUTE s;
# DEALLOCATE PREPARE s;
# END IF;
#END $$
#DELIMITER ;
#CALL DropIndex()~;
my $indexcode = 'ALTER IGNORE TABLE ' . $self->qid($table) .
' DROP INDEX ' . $self->qid( $index->{name} );
return $indexcode;
}#sub
=item drop_constraint_foreign
Creates the drop foreign constraint code
=cut
sub drop_constraint_foreign {
my $self = shift;
my ( $table, $constraint ) = @_;
# my $constraintcode = q~DELIMITER $$
#DROP PROCEDURE IF EXISTS `util`.`DropFK`$$
#CREATE PROCEDURE `DropFK`()
#BEGIN
#IF EXISTS (SELECT NULL FROM information_schema.TABLE_CONSTRAINTS
#WHERE CONSTRAINT_SCHEMA = DATABASE() AND CONSTRAINT_NAME = ~ . $self->{dbh}->quote( $constraint->{name} ) .
#q~) THEN
# ALTER TABLE ~ . $self->qid($table) . q~ DROP FOREIGN KEY ~ . $self->qid( $constraint->{name} ) . q~;
#END IF;
#END$$
#
#DELIMITER ;
#CALL DropFK()~;
my $constraintcode = 'ALTER IGNORE TABLE ' . $self->qid($table) .
' DROP FOREIGN KEY ' . $self->qid( $constraint->{name} );
return $constraintcode;
}#sub
1;