NAME

CellBIS::SQL::Abstract - SQL Query Generator

SYNOPSIS

  use CellBIS::SQL::Abstract

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

  # IF create table SQLite
  my $sql_abstract = CellBIS::SQL::Abstract->new(db_type => 'sqlite');

  # Create Table
  my $table_name = 'my_table_name'; # Table name.
  my $col_list = []; # List of column table
  my $col_attr = {}; # Attribute column table.

  # insert
  my $table_name = 'my_table_name'; # Table name.
  my $column = []; # List of column in the table (array ref data type)
  my $value = []; # Value of column (array ref data type)
  $sql_abstract->insert($table_name, $column, $value);

  # update
  my $table_name = 'my_table_name'; # Table name.
  my $column = []; # List of column in the table (array ref data type)
  my $value = []; # Value of column (array ref data type)
  my $clause = {}; # Clause of SQL Query, like where, order by, group by, and etc.
  $sql_abstract->update($table_name, $column, $value, $clause);

  # delete
  my $table_name = 'my_table_name'; # Table name.
  my $clause = {}; # Clause of SQL Query, like where, order by, group by, and etc.
  $sql_abstract->delete($table_name, $clause);

  # select
  my $table_name = 'my_table_name'; # Table name.
  my $column = []; # List of column in the table (array ref data type)
  my $clause = {}; # Clause of SQL Query, like where, order by, group by, and etc.
  $sql_abstract->select($table_name, $column, $clause);

  # select_join
  my $table_list = []; # List of table. (array ref data type)
  my $column = []; # List of column to select. (array ref data type)
  my $clause = {}; # Clause of SQL Query.
  $sql_abstract->select_join($table_list, $column, $clause);

DESCRIPTION

The purpose of this module is to generate SQL Query. General queries has covered insert, delete, update, select, and select with join - (select_join). And the additional query has covered to create table.

METHODS

CellBIS::SQL::Abstract inherits all methods from Mojo::Base. General methods available for insert, update, select, and select_join.

For additional method, only available for create_table. Currently, only supports MariaDB/MySQL and SQLite Syntax

The following are the methods available from this module:

create_table - SQLite

  use CellBIS::SQL::Abstract

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

  my $table_name = 'my_users';
  my $col_list = [ 'id', 'first_name', 'last_name', 'other_col_name' ];
  my $col_attr = {
    'id'             => {
      type          => { name => 'integer' },
      is_primarykey => 1,
      is_autoincre  => 1,
    },
    'first_name'     => {
      type    => {
        name => 'varchar',
        size => 50,
      },
      is_null => 0,
    },
    'last_name'      => {
      type    => {
        name => 'varchar',
        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 INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
      first_name VARCHAR NOT NULL,
      last_name VARCHAR NOT NULL,
      other_col_name VARCHAR(60) NOT NULL
  )

create_table - MariaDB/MySQL

  use CellBIS::SQL::Abstract

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

  my $table_name = 'my_users';
  my $col_list = [ 'id', 'first_name', 'last_name', 'other_col_name' ];
  my $col_attr = {
    'id'             => {
      type          => {
        name => 'int',
        size => 11
      },
      is_primarykey => 1,
      is_autoincre  => 1,
    },
    'first_name'     => {
      type    => {
        name => 'varchar',
        size => 50,
      },
      is_null => 0,
    },
    'last_name'      => {
      type    => {
        name => 'varchar',
        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 INT(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
      first_name VARCHAR(50) NOT NULL,
      last_name VARCHAR(50) NOT NULL,
      other_col_name VARCHAR(60) NOT NULL
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8

create_table - PostgreSQL

  use CellBIS::SQL::Abstract

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

  my $table_name = 'my_users';
  my $col_list = [ 'id', 'first_name', 'last_name', 'other_col_name' ];
  my $col_attr = {
    'id' => {
      type => {
        name => 'serial'
      },
      is_primarykey => 1
    },
    'first_name' => {
      type => {
        name => 'varchar',
        size => 50,
      },
      is_null => 0,
    },
    'last_name' => {
      type => {
        name => 'varchar',
        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
  )

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
  )

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

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
  )

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

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()']],
    ['my_name2', 'my_last_name2', ['NOW()'], ['NOW()']],
    ['my_name3', 'my_last_name3', ['NOW()'], ['NOW()']],
    ['my_name4', 'my_last_name4', ['NOW()'], ['NOW()']],
  ];

  # If no Prepare Statement
  my $insert_no_pre_st = $sql_abstract->insert_bulk($table_name, $column, $values)->[0];

  # IF Prepare Statement
  my $insert = $sql_abstract->insert_bulk($table_name, $column, $values, 'pre-st')->[0];

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()), ('my_name1', 'my_last_name1', NOW(), NOW()), ('my_name2', 'my_last_name2', NOW(), NOW()), ('my_name3', 'my_last_name3', NOW(), NOW()), ('my_name4', 'my_last_name4', NOW(), NOW())

  # With prepare statement
  INSERT INTO my_users(first_name, last_name, date_create, date_update) VALUES (?, ?, NOW(), NOW()), (?, ?, NOW(), NOW()), (?, ?, NOW(), NOW()), (?, ?, NOW(), NOW()), (?, ?, NOW(), NOW())

update

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

  my $table_name = 'my_users'; # Table name.
  my $column = [
    'first_name',
    'last_name',
    'date_update'
  ];
  my $value = [
    'Achmad Yusri',
    'Afandi',
    ['NOW()']
  ];
  my $clause = {
    where => 'id = 2'
  };

  # If no Prepare Statement
  my $update_no_pre_st = $sql_abstract->update($table_name, $column, $value, $clause);

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

SQL equivalent :

  # Preare Statement :
  UPDATE my_users SET first_name=?, last_name=? WHERE id = 2;

  # No Prepare Statement :
  UPDATE my_users SET first_name='Achmad Yusri', last_name='Afandi' date_update=NOW() WHERE id = 2;

update - with simple

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

  my $table_name = 'my_users'; # Table name.
  my $col_val = {
    'first_name' => 'Achmad Yusri',
    'last_name' => 'Afandi'
  };
  my $clause = {
    where => 'id = 2'
  };
  my $update = $sql_abstract->update($table_name, $col_val, $clause);

SQL equivalent :

  # No Prepare Statement :
  UPDATE my_users SET first_name='Achmad Yusri', last_name='Afandi' WHERE id = 2;

delete

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

  my $table_name = 'my_users';
  my $clause = {
    where => 'id = 2'
  };
  my $delete = $sql_abstract->delete($table_name, $clause);

SQL equivalent :

  DELETE FROM my_users WHERE id = 2

select

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

  my $table_name = 'my_users';
  my $column = [];
  my $clause = {
    where => 'id = 2'
  };
  my $select = $sql_abstract->select($table_name, $column, $clause);

SQL equivalent :

  SELECT * FROM my_users WHERE id = 2;

select_join

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

  my $table_list = [
    { name => 'my_users', 'alias' => 't1', primary => 1 },
    { name => 'my_companies', 'alias' => 't2' }
  ];
  my $column = [
    't1.first_name',
    't1.last_name',
    't2.company_name',
  ];
  my $clause = {
    'typejoin' => {
      'my_companies' => 'inner',
    },
    'join'     => [
      {
        name   => 'my_companies',
        onjoin => [
          't1.id', 't2.id_company_users',
        ]
      }
    ],
    'where'    => 't1.id = 2 AND t2.id_company_users = 1',
    'orderby'  => 't1.id',
    'order'    => 'desc', # asc || desc
    'limit'    => '10'
  };
  my $select_join = $sql_abstract->select_join($table_list, $column, $clause);

SQL equivalent :

  SELECT t1.first_name, t1.last_name, t2.company_name
  FROM my_users AS t1
  INNER JOIN my_companies AS t2
  ON t1.id = t2.id_company_users
  WHERE t1.id = 2 AND t2.id_company_users = 1 ORDER BY t1.id DESC LIMIT 10

AUTHOR

Achmad Yusri Afandi, <yusrideb@cpan.org>

COPYRIGHT AND LICENSE

This program is free software, you can redistribute it and/or modify it under the terms of the Artistic License version 2.0.

To install CellBIS::SQL::Abstract, copy and paste the appropriate command in to your terminal.

cpanm

cpanm CellBIS::SQL::Abstract

CPAN shell

perl -MCPAN -e shell
install CellBIS::SQL::Abstract

For more information on module installation, please visit the detailed CPAN module installation guide.

	Global
`s`	Focus search bar
`?`	Bring up this help dialog

	GitHub
`g` `p`	Go to pull requests
`g` `i`	go to github issues (only if github is preferred repository)

	POD
`g` `a`	Go to author
`g` `c`	Go to changes
`g` `i`	Go to issues
`g` `d`	Go to dist
`g` `r`	Go to repository/SCM
`g` `s`	Go to source
`g` `b`	Go to file browse

	Search terms
module: (e.g. module:Plugin)
distribution: (e.g. distribution:Dancer auth)
author: (e.g. author:SONGMU Redis)
version: (e.g. version:1.00)

NAME

SYNOPSIS

DESCRIPTION

METHODS

create_table - SQLite

create_table - MariaDB/MySQL

create_table - PostgreSQL

create_table with foreign key - SQLite

create_table with foreign key - MariaDB/MySQL

create_table with foreign key - PostgreSQL

insert

insert_bulk

update

update - with simple

delete

select

select_join

AUTHOR

COPYRIGHT AND LICENSE

Module Install Instructions