view release on metacpan or  search on metacpan

lib/CellBIS/SQL/Abstract.pod  view on Meta::CPAN

        size => 50,
      },
      is_null => 0,
    },
    'other_col_name' => {
      type => {
        name => 'varchar',
        size => 60,
      },
      is_null => 0,
    }
  };
  $create_table = $sql_abstract->create_table($table_name, $col_list, $col_attr);

SQL equivalent :

  CREATE TABLE IF NOT EXISTS users(
      id SERIAL NOT NULL PRIMARY KEY,
      first_name VARCHAR(50) NOT NULL,
      last_name VARCHAR(50) NOT NULL,
      other_col_name VARCHAR(60) NOT NULL
  )

=head2 create_table with foreign key - SQLite

  use CellBIS::SQL::Abstract

  my $sql_abstract = CellBIS::SQL::Abstract->new(db_type => 'sqlite');

  my $table_name = 'my_companies';
  my $col_list = [
    'id_company',
    'id_company_users',
    'company_name',
  ];
  my $col_attr = {
    'id_company'       => {
      type          => { name => 'integer' },
      is_primarykey => 1,
      is_autoincre  => 1,
    },
    'id_company_users' => {
      type    => { name => 'integer' },
      is_null => 0,
    },
    'company_name'     => {
      type    => {
        name => 'varchar',
        size => '200',
      },
      is_null => 0,
    }
  };
  my $table_attr = {
    fk      => {
      name         => 'user_companies_fk',
      col_name     => 'id_company_users',
      table_target => 'users',
      col_target   => 'id',
      attr         => {
        onupdate => 'cascade',
        ondelete => 'cascade'
      }
    },
    charset => 'utf8',
    engine  => 'innodb',
  };
  $create_table = $sql_abstract->create_table($table_name, $col_list, $col_attr, $table_attr);

SQL equivalent :

  CREATE TABLE IF NOT EXISTS company(
      id_company INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
      id_company_users INTEGER NOT NULL,
      company_name VARCHAR NOT NULL,
      CONSTRAINT user_company_fk FOREIGN KEY (id_company_users) REFERENCES users (id)
      ON DELETE CASCADE ON UPDATE CASCADE
  )

=head2 create_table with foreign key - MariaDB/MySQL

  use CellBIS::SQL::Abstract

  my $sql_abstract = CellBIS::SQL::Abstract->new(db_type => 'mariadb');

  my $table_name = 'my_companies';
  my $col_list = [
    'id_company',
    'id_company_users',
    'company_name',
  ];
  my $col_attr = {
    'id_company'       => {
      type          => {
        name => 'int',
        size => 11
      },
      is_primarykey => 1,
      is_autoincre  => 1,
    },
    'id_company_users' => {
      type    => {
        name => 'int',
        size => 11
      },
      is_null => 0,
    },
    'company_name'     => {
      type    => {
        name => 'varchar',
        size => '200',
      },
      is_null => 0,
    }
  };
  my $table_attr = {
    fk      => {
      name         => 'user_companies_fk',
      col_name     => 'id_company_users',
      table_target => 'users',
      col_target   => 'id',
      attr         => {
        onupdate => 'cascade',
        ondelete => 'cascade'
      }
    },
    charset => 'utf8',
    engine  => 'innodb',
  };
  $create_table = $sql_abstract->create_table($table_name, $col_list, $col_attr, $table_attr);

SQL equivalent :

  CREATE TABLE IF NOT EXISTS company(
      id_company INT(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
      id_company_users INT(11) NOT NULL,
      company_name VARCHAR(200) NOT NULL,
      KEY user_company_fk (id_company_users),
      CONSTRAINT user_company_fk FOREIGN KEY (id_company_users) REFERENCES users (id)
      ON DELETE CASCADE ON UPDATE CASCADE
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8

=head2 create_table with foreign key - PostgreSQL

  use CellBIS::SQL::Abstract

  my $sql_abstract = CellBIS::SQL::Abstract->new(db_type => 'pg');

  my $table_name = 'my_companies';
  my $col_list = [
    'id_company',
    'id_company_users',
    'company_name',
  ];
  my $col_attr = {
    'id_company' => {
      type => {
        name => 'serial'
      },
      is_primarykey => 1
    },
    'id_company_users' => {
      type => {
        name => 'int',
        size => 11
      },
      is_null => 0,
    },
    'company_name' => {
      type => {
        name => 'varchar',
        size => '200',
      },
      is_null => 0,
    }
  };
  my $table_attr = {
    fk => {
      name         => 'user_companies_fk',
      col_name     => 'id_company_users',
      table_target => 'users',
      col_target   => 'id',
      attr         => {
        onupdate => 'cascade',
        ondelete => 'cascade'
      }
    },
    charset => 'utf8',
    engine  => 'innodb',
  };
  $create_table = $sql_abstract->create_table($table_name, $col_list, $col_attr, $table_attr);

SQL equivalent :

  CREATE TABLE IF NOT EXISTS company(
      id_company serial NOT NULL PRIMARY KEY,
      id_company_users INT(11) NOT NULL,
      company_name VARCHAR(200) NOT NULL,
      CONSTRAINT user_company_fk FOREIGN KEY (id_company_users) REFERENCES users (id)
      ON DELETE CASCADE ON UPDATE CASCADE
  )

=head2 insert

  use CellBIS::SQL::Abstract
  my $sql_abstract = CellBIS::SQL::Abstract->new;

  my $table_name = 'my_users';
  my $column = [
    'first_name',
    'last_name',
    'date_create',
    'date_update'
  ];
  my $value = [
    'my_name',
    'my_last_name',
    ['NOW()'],
    ['NOW()']
  ];

  # If no Prepare Statement
  my $insert_no_pre_st = $sql_abstract->insert($table_name, $column, $value);

  # IF Prepare Statement
  my $insert = $sql_abstract->insert($table_name, $column, $value, 'pre-st');

SQL equivalent :

  # No Prepare Statement :
  INSERT INTO my_users(first_name, last_name, date_create, date_update) VALUES('my_name', 'my_last_name', NOW(), NOW());

  # Prepare Statement :
  INSERT INTO my_users(first_name, last_name, date_create, date_update) VALUES(?, ?, NOW(), NOW());

=head2 insert_bulk

  use CellBIS::SQL::Abstract
  my $sql_abstract = CellBIS::SQL::Abstract->new;

  my $table_name = 'my_users';
  my $column     = ['first_name', 'last_name', 'date_create', 'date_update'];
  my $values     = [
    ['my_name0', 'my_last_name0', ['NOW()'], ['NOW()']],
    ['my_name1', 'my_last_name1', ['NOW()'], ['NOW()']],