Last active
June 12, 2017 12:45
-
-
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.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
<?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); | |
} | |
} |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
<?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