Skip to content

Instantly share code, notes, and snippets.

@KEINOS
Last active June 12, 2017 12:45
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save KEINOS/873269390bc6c9c0cb724dd52170ea9f to your computer and use it in GitHub Desktop.
Save KEINOS/873269390bc6c9c0cb724dd52170ea9f to your computer and use it in GitHub Desktop.
配列のように、SQLite3クラスを使用するためのシンプルなクラスです。Simple class for use of the SQLite3 class, as easy like an array.
<?php
include_once('File1');
// Include sample usage file. Or uncomment above and check the url below to
// see how it works on line.
// https://paiza.io/projects/FnRl1GA_JWvITavxABEeLQ?locale=en-us
/* =========================================================================
KEINOS_DB class
A simple class to use the SQLite3 class, as easy as an array.
https://gist.github.com/KEINOS/873269390bc6c9c0cb724dd52170ea9f
-------------------------------------------------------------------------
ex:
$db = new KEINOS_DB( 'myDB' );
$db->save( 'key1', 'data1' );
$value = $db->load( 'key1' );
echo $value; // gets 'data1'
echo $db->dump(); // gets all the data saved.
========================================================================= */
class KEINOS_DB
{
// Properties
var $version = '20170612-1925';
var $nameTable = 'list_hash';
function __construct($name_db, $is_inmemory = false)
{
$DSN = ($is_inmemory) ? 'sqlite::memory:' : "sqlite:${name_db}";
$this->db = new PDO( $DSN );
$this->db->exec(
"CREATE TABLE IF NOT EXISTS {$this->nameTable}( id INTEGER PRIMARY KEY, key TEXT, value TEXT )"
);
}
function save($key, $value)
{
try {
$key = trim((string)$key);
$value = serialize($value);
if ($this->key_exists( $key )){
$id = $this->find_id( $key )[0];
//$sql = 'update テーブル名 set name =:name where id = :value';
$sQuery = "UPDATE {$this->nameTable} set value=? where id=?";
$sth = $this->db->prepare($sQuery);
$sth->bindValue(1, $value, PDO::PARAM_STR);
$sth->bindValue(2, $id, PDO::PARAM_STR);
} else {
$sQuery = "INSERT INTO {$this->nameTable}(key,value) VALUES(?,?)";
$sth = $this->db->prepare($sQuery);
$sth->bindValue(1, $key, PDO::PARAM_STR);
$sth->bindValue(2, $value, PDO::PARAM_STR);
}
$result = $sth->execute();
} catch (PDOException $e) {
echo "ERROR:" . PHP_EOL;
echo $e->getMessage();
}
return $result;
}
function load($key)
{
$result = $this->find_value($key)[0];
return unserialize($result);
}
function key_exists($key)
{
try {
$sQuery = "SELECT id FROM {$this->nameTable} WHERE key = ?";
$sth = $this->db->prepare($sQuery);
$sth->bindValue(1, (string)$key);
$sth->execute();
$tmp = ( $sth->fetchColumn() ) ? true : false;
return( $tmp );
} catch (PDOException $e) {
echo "ERROR:" . PHP_EOL;
echo $e->getMessage();
die();
}
}
function find_id($key){
return $this->find_value( $key, true );
}
function find_value($key, $return_by_id = false)
{
try {
$result = array();
$column = ($return_by_id) ? 'id' : 'value';
$sQuery = "SELECT ${column} FROM {$this->nameTable} WHERE key = ?";
$sth = $this->db->prepare($sQuery);
$sth->bindValue(1, (string)$key);
$sth->execute();
$fetched = $sth->fetchAll(PDO::FETCH_ASSOC);
} catch (PDOException $e) {
echo "ERROR:" . PHP_EOL;
echo $e->getMessage();
die();
}
foreach ($fetched as $item) {
$result[] = $item[$column];
}
return $result;
}
function dump()
{
$sQuery = "SELECT * FROM {$this->nameTable}";
return $this->db->query($sQuery)->fetchAll(PDO::FETCH_ASSOC);
}
}
<?php
/* =========================================================================
Sample usage
of
KEINOS_DB class
https://gist.github.com/KEINOS/873269390bc6c9c0cb724dd52170ea9f
========================================================================= */
// Flag for debugging. Nothing important for the sample. See `debug_echo`
// user function below.
define('MODE_DEBUG', true);
// Includes KEINOS_DB class file.
require_once('class.KEINOS_DB.php');
/* ---------------------
Create instance of KEINOS_DB using In-memory SQLite3.
Set nothing or `false` to create DB file into local instead of in-
memory. For example, this line below will create 'myDB.db' file at the
same level of the script.
$a = new KEINOS_DB( 'myDB' );
[Caution] : Setting `true` the 2nd parameter, the DB file will NOT be
created, but in the memory. So after the script finishes run, the DB
file in the memory will be deleted.
--------------------- */
$a = new KEINOS_DB('myDB', true);
/* ---------------------
Set sample datas
--------------------- */
//sample1
// Similar usage of `$a['sample1'] = 'This is a sample1-1.';`
$a->save('sample1', 'This is a sample data1-1.');
$a->save('sample1', 'This is a sample data1-2.'); //overwrites
$a->save('sample1', 'This is a sample data1-3.'); //overwrites
//sample2
$data2 = 'This is a sample data2.';
$a->save('sample2', $data2);
//sample3
$data3 = array(
'sample3-1', 'This is a sample data3-1.',
'sample3-2', 'This is a sample data3-2.',
);
$a->save('sample3', $data3);
/* ---------------------
Get saved datas
--------------------- */
$key = 'sample1';
$result = $a->load($key);
// Show result
debug_echo($key, $result);
/* ---------------------
Dump all the saved datas
--------------------- */
debug_echo('dump', $a->dump());
/* ===========================================================
User function for debugging
=========================================================== */
function debug_echo($title, $m)
{
if (MODE_DEBUG) {
if (is_string($m)) {
echo "${title}: ${m}" . PHP_EOL;
} else {
echo "${title}: " . print_r($m, true) . PHP_EOL;
}
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment