Manipulate SQLite (Connect/Create Table/Insert/Select/Drop/Transaction)

Contents

Output

expected result is following one(regex expression)

r"1\tname1\tmale\t\d+/\d+/\d+ \d+:\d+:\d+\t\d+/\d+/\d+ \d+:\d+:\d+\n2\tname2\tfemale\t\d+/\d+/\d+ \d+:\d+:\d+\t\d+/\d+/\d+ \d+:\d+:\d+$"

example

1	name1	male	2019/10/29 18:04:45	2019/10/29 18:04:45
2	name2	female	2019/10/29 18:04:45	2019/10/29 18:04:45

Programming code

JavaScript

const fs = require('fs');
const fsExtra = require('fs-extra');

class SQLiteManipulator {
	constructor(opt={}) {
    this.dbname = '/tmp/example.sqlite';
  }

  async beginTransaction() {
    const sql = "BEGIN"
    await this.executeSQL(sql)
  }

  async createExampleData() {
    const datas = [
      ['name1', 'male'],
      ['name2', 'female']
    ];
 
    this.rdbh.serialize(() => {
      for (const data of datas) {
        const sql = 'INSERT INTO example (name, sex) VALUES (?, ?)';
        const params = [data[0], data[1]];
        this.rdbh.run(sql, params, (err) => {
          if (err) {
            return console.log(err.message);
          }
        }
        );
      }
    });
  }

  async createExampleTable() {
    const sqls = [`
    CREATE TABLE example (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      name VARCHAR(255) NOT NULL,
      sex VARCHAR(15) DEFAULT NULL,
      created_at TEXT NOT NULL DEFAULT (DATETIME('now', 'localtime')),
      updated_at TEXT NOT NULL DEFAULT (DATETIME('now', 'localtime'))
    )`,
    'CREATE INDEX created_at_idx ON example(created_at)',
    'CREATE INDEX name_idx ON example(name)',
    'CREATE INDEX updated_at_idx ON example(updated_at)'
    ];
    this.rdbh.serialize(() => {
      for(const sql of sqls){
        this.executeSQL(sql);
      }
    });
  }

	async dbclose() {
    this.rdbh.serialize(() => {
      if (typeof(this.rdbh) !== "undefined") {
        this.rdbh.close();
      }
    });
  }

	async dbconnect() {
    if(fs.existsSync(this.dbname )) { 
      await fsExtra.remove(this.dbname );
    }
    var sqlite3 = require('sqlite3').verbose();
    this.rdbh = new sqlite3.Database(this.dbname);
  }

  async commit() {
    const sql = "END"
    this.rdbh.serialize(() => {
      this.executeSQL(sql)
    });
  }

  async executeSQL(sql) {
//    console.log(sql);
    try {
      const res = await this.rdbh.run(sql);
    }
    catch(err){
      const sql = "ROLLBACK"
      await this.rdbh.run(sql)
      console.log(err.name + ': ' + err.message);
    }
  }

  async selectFromExampleTable() {
    const sql = "SELECT id, name, sex, created_at, updated_at FROM example";
    try {
//      console.log(sql);
      this.rdbh.each(sql, function(err, row) {
        console.log([row.id, row.name, row.sex, row.created_at, row.updated_at].join("\t"))
      });
    }
    catch(err) {
      const sql = "ROLLBACK"
      await this.executeSQL(sql)
      console.log(err.name + ': ' + err.message)
      process.exit(-1)
    }
  }
};
module.exports = SQLiteManipulator

if(!module.parent) {
  (async () => {
    const rdb = new SQLiteManipulator()
    await rdb.dbconnect()
    await rdb.beginTransaction()
    await rdb.createExampleTable()
    await rdb.createExampleData()
    await rdb.selectFromExampleTable()
    await rdb.commit()
    await rdb.dbclose()
  })()
}

Perl

package SqliteManipulator;
use strict;
use DBI;

sub new() {
  my $self = {};
  return bless $self;
}

sub init(){
  my $self = shift;
  $self->{'database'} = "/tmp/example.sqlite";
}

sub beginTransaction(){
  my $self = shift;
  my $sql = "BEGIN";
  $self->executeSQL($sql);
}

sub createExampleData(){
  my $self = shift;
  my @datas = (
    ['name1', 'male'],
    ['name2', 'female']
  );
  my $sth = $self->{'rdbh'}->prepare("INSERT INTO example (name, sex) VALUES (?, ?)");
  foreach my $data_ref (@datas) {
    $sth->execute($data_ref->[0], $data_ref->[1]);
  }
  $sth->finish();
}

sub createExampleTable() {
  my $self = shift;
  my @sqls = ("CREATE TABLE example (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  name VARCHAR(255) NOT NULL,
  sex VARCHAR(15) DEFAULT NULL,
  created_at TEXT NOT NULL DEFAULT (DATETIME('now', 'localtime')),
  updated_at TEXT NOT NULL DEFAULT (DATETIME('now', 'localtime'))
)",
"CREATE INDEX created_at_idx ON example(created_at)",
"CREATE INDEX name_idx ON example(name)",
"CREATE INDEX updated_at_idx ON example(updated_at)");
  foreach my $sql (@sqls){
    $self->executeSQL($sql);
  }
}


sub dbclose(){
  my $self = shift;
  $self->{'rdbh'}->disconnect();
}

sub dbconnect(){
  my $self = shift;
  unlink($self->{'database'}) if -f $self->{'database'};
  $self->{'rdbh'} = DBI->connect("DBI:SQLite:database=".$self->{'database'}) or die "Failed to connect to db: ".$self->{'database'};
}

sub commit(){
  my $self = shift;
  $self->{'rdbh'}->commit();
}

sub executeSQL(){
  my $self = shift;
  my $sql = shift;
  $self->{'rdbh'}->do($sql) or die("Failed to execute ".$sql);
}

sub selectFromExampleTable(){
  my $self = shift;
  my $sql = "SELECT id, name, sex, created_at, updated_at FROM example";
  my $sth = $self->{'rdbh'}->prepare($sql) or die("Failed to prepare ".$sql);
  $sth->execute() or die("Failed to execute ".$sql);
  while(my $row_ref = $sth->fetchrow_hashref) {
    print(join("\t", $row_ref->{'id'}, $row_ref->{'name'}, $row_ref->{'sex'}, $row_ref->{'created_at'}, $row_ref->{'updated_at'})."\n");
  }
  $sth->finish();
}

if ($0 eq __FILE__) {
  my $pro = new SqliteManipulator();
  $pro->init();
  $pro->dbconnect();
  $pro->beginTransaction();
  $pro->createExampleTable();
  $pro->createExampleData();
  $pro->selectFromExampleTable();
  $pro->commit();
  $pro->dbclose();
}
else{
  1;
}

PHP

<?php
class SQLiteManipulator {
  private $rdbh;
  public function __construct($opt=[]) {
    $this->dbname = '/tmp/example.sqlite';
  }

  function beginTransaction() {
    $this->rdbh->beginTransaction();
  }

  function createExampleData(){
    $datas = [
      ['name1', 'male'],
      ['name2', 'female']
    ];
    $sql = "INSERT INTO example (name, sex) VALUES (?, ?)";
    $sth = $this->rdbh->prepare($sql);
    foreach($datas as $data) {
      $sth->execute([$data[0], $data[1]]);
    }
  }

  function createExampleTable(){
    $sqls = ["
    CREATE TABLE example (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      name VARCHAR(255) NOT NULL,
      sex VARCHAR(15) DEFAULT NULL,
      created_at TEXT NOT NULL DEFAULT (DATETIME('now', 'localtime')),
      updated_at TEXT NOT NULL DEFAULT (DATETIME('now', 'localtime'))
    )",
    "CREATE INDEX created_at_idx ON example(created_at)",
    "CREATE INDEX name_idx ON example(name)",
    "CREATE INDEX updated_at_idx ON example(updated_at)"
    ];
    foreach($sqls as $sql){
      $this->executeSQL($sql);
    }
  }

  function dbclose() {
    $this->rdbh = null;
  }
  
  function dbconnect(){
    if(file_exists($this->dbname)){
      unlink($this->dbname);
    }

    try {
      $this->rdbh = new PDO('sqlite:'.$this->dbname);
      $this->rdbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    }
    catch (PDOException $e) {
      die("Failed to connect: ".$e->getMessage());
    }
  }


  function endTransaction(){
    $this->rdbh->commit();
  }

  function executeSQL($sql) {
    $sqls = explode(';', $sql);
    foreach ($sqls as $sql) {
      if($sql) {
        $sth = $this->rdbh->prepare($sql);
        $sth->execute();
      }
    }
  }
  
  function rollback(){
    $this->rdbh->rollback();
  }

  function selectFromExampleTable(){
    $sql = "SELECT id, name, sex, created_at, updated_at FROM example";
    $sth = $this->rdbh->prepare($sql);
    $sth->execute();
    while($row = $sth->fetch(PDO::FETCH_ASSOC)){
      print(implode("\t", [$row['id'], $row['name'], $row['sex'], $row['created_at'], $row['updated_at']])."\n");
    }
  }
}

if ( !isset(debug_backtrace()[0]) ) {
  $rdb = new SQLiteManipulator();
  try{
    $rdb->dbconnect();
  } catch(PDOException $e){
    echo $e->getMessage();
    exit(1);
  }

  try{
    $rdb->beginTransaction();
  } catch(PDOException $e){
    echo $e->getMessage();
    $rdb->dbclose();
    exit(1);
  }

  try {
    $rdb->createExampleTable();
    $rdb->createExampleData();
    $rdb->selectFromExampleTable();
    $rdb->endTransaction();
    $rdb->dbclose();
  } catch(PDOException $e){
    $rdb->rollback();
    $rdb->dbclose();
    throw $e;
  }
}

Python

import sqlite3
import os
from pprint import pprint

class SQLiteManipulator():
  def __init__(self, opt=[]):
    self.dbfile = '/tmp/example.sqlite'
    pass

  def beginTransaction(self):
    sql = "BEGIN"
    self.executeSQL(sql)

  def createExampleTable(self):
    sqls = ['''\
CREATE TABLE example (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  name VARCHAR(255) NOT NULL,
  sex VARCHAR(15) DEFAULT NULL,
  created_at TEXT NOT NULL DEFAULT (DATETIME('now', 'localtime')),
  updated_at TEXT NOT NULL DEFAULT (DATETIME('now', 'localtime'))
)
''',
      'CREATE INDEX created_at_idx ON example(created_at)',
      'CREATE INDEX name_idx ON example(name)',
      'CREATE INDEX updated_at_idx ON example(updated_at)'
    ]
    for sql in sqls:
      self.executeSQL(sql)

  def createExampleData(self):
    datas = [
      ['name1', 'male'],
      ['name2', 'female']
    ]
    cur = self.rdbh.cursor()
    for data in datas:
      try:
        cur.execute("INSERT INTO example (name, sex) VALUES (?, ?)", [data[0], data[1]])          
      except Exception as e:
        pro.rollback()
        print(e)

  def dbclose(self):
    self.rdbh.commit()
    self.cur.close()
    self.rdbh.close()

  def dbconnect(self):
    if os.path.exists(self.dbfile):
      os.remove(self.dbfile)
    self.rdbh = sqlite3.connect(self.dbfile)
    self.cur = self.rdbh.cursor()

  def commit(self):
    self.rdbh.commit()

  def executeSQL(self, sql):
    cur = self.rdbh.cursor()
    try:
      cur.execute(sql)
    except Exception as e:
      pro.rollback()
      print(e)

  def rollback(self):
    self.rdbh.rollback()

  def selectFromExampleTable(self):
    sql = "SELECT id, name, sex, created_at, updated_at FROM example"
    self.cur.execute(sql)
    for [id, name, sex, created_at, updated_at] in self.cur:
      print("\t".join([str(id), name, sex, str(created_at), str(updated_at)]))

if __name__ == "__main__":
  pro = SQLiteManipulator()
  pro.dbconnect()
  pro.beginTransaction()
  try:
    pro.createExampleTable()
    pro.createExampleData()
    pro.selectFromExampleTable()
    pro.commit()
    pro.dbclose()
  except Exception as e:
    pro.rollback()
    pro.dbclose()
    print(e)

Ruby

require 'dotenv'
require 'pg'

class PsqlManipulator
  def initialize(opt=[])
    Dotenv.load(
      File.join(File.dirname(File.expand_path(__FILE__)), '.env')
    )
  end

  def dbconnect
    dbname = "example"
    timezone = "Asia/Tokyo"
    @rdbh = PG::connect(:host => ENV["DBHOST"], :user => ENV["DBUSER"], :password => ENV["DBPASSWORD"], :dbname => dbname)
    sql = "SET client_min_messages = 'error'"
    @rdbh.query(sql)
    sql = "SET TIME ZONE '"+ timezone + "'"
    @rdbh.query(sql)
  end

  def beginTransaction
    sql = "BEGIN"
    executeSQL(sql)
  end

  def createExampleType
    sql = "CREATE TYPE example_sex AS ENUM ('male', 'female')"
    executeSQL(sql)
  end

  def createExampleTable
    sql = '''
CREATE TABLE example (
  id SERIAL NOT NULL,
  name VARCHAR(255) NOT NULL,
  sex example_sex DEFAULT NULL,
  created_at timestamp with time zone DEFAULT NOW(),
  updated_at timestamp with time zone DEFAULT NOW(),
  PRIMARY KEY (id)
);
CREATE INDEX created_at_idx ON example(created_at);
CREATE INDEX name_idx ON example(name);
CREATE INDEX updated_at_idx ON example(updated_at);
'''
    executeSQL(sql)
  end

  def createExampleData
    datas = [
      ['name1', 'male'],
      ['name2', 'female']
    ]
    datas.each do |data|
      @rdbh.exec_params("INSERT INTO example (name, sex) VALUES ($1, $2)", [data[0], data[1]])
    end
  end

  def dbclose
    @rdbh.close()
  end

  def dropExampleTable
    sql = "DROP TABLE IF EXISTS example"
    executeSQL(sql)
  end

  def dropExampleType
    sql = "DROP TYPE IF EXISTS example_sex"
    executeSQL(sql)
  end

  def endTransaction
    sql = "END"
    @rdbh.query(sql)
  end

  def executeSQL(sql)
    @rdbh.query(sql)
  end

  def selectFromExampleTable
    sql = "SELECT id, name, sex, to_char(created_at, 'YYYY/MM/DD HH24:MI:SS') AS created_at, to_char(updated_at, 'YYYY/MM/DD HH24:MI:SS') AS updated_at FROM example"
    results = @rdbh.query(sql)
    results.each do |row|
      print([row["id"], row['name'], row['sex'], row['created_at'], row['updated_at']].join("\t") + "\n")
    end
  end
end

if $0 == __FILE__
  rdb = PsqlManipulator.new()
  begin
    rdb.dbconnect()
  rescue => err
    print(err)
    exit(1)
  end

  begin
    rdb.beginTransaction()
    begin
      rdb.dropExampleTable()
      rdb.dropExampleType()
      rdb.createExampleType()
      rdb.createExampleTable()
      rdb.createExampleData()
      rdb.selectFromExampleTable()
      rdb.dropExampleTable()
      rdb.dropExampleType()
      rdb.endTransaction()
    rescue => err
      puts("Automatically roll backed.")
      print(err)
      rdb.dbclose()
      exit(1)
    end
  rescue => err
    rdb.dbclose()
    print(err)
    exit(1)
  end
end