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
