sqlite3 db_name sqlite3 .svn/wc.db "delete from work_queue"
esempio di connesione PDO e operazioni di base
class SqlLiteCache {
function run() {
// esempio uso di sqlite come cache
$db = new PDO("sqlite:".APP_PATH."/var/cache_db.sqlite");
$stm = $db->prepare('select 1 from cache');
// test table exists
if (false === $stm ) {
// set up the table
$sql_create = 'CREATE TABLE cache (key TEXT,
type TEXT,
content BLOB,
age INTEGER,
UNIQUE(key))';
$db->exec($sql_create);
}
// get
$then = 0;
$sql = "SELECT content, type, age FROM cache WHERE key = ? AND age > ?";
$stm = $db->prepare($sql);
$stm->execute(array($key, $then));
$entry = $stm->fetch(PDO::FETCH_ASSOC);
// delete
$stm = $db->prepare('DELETE FROM cache WHERE key = ? OR age < ?');
$stm->execute(array($key, $then));
// insert
$entry = array();
$entry['type'] = '';
$entry['content'] = '';
$sql = "INSERT INTO cache ( key, type, content, age )
VALUES (?, ?, ?, strftime('%s', 'now'))";
$stm = $db->prepare($sql);
$stm->execute(array($key, $entry['type'], $entry['content']));
}
}
sqlite3:
$db = new SQLite3('test.db',SQLITE3_OPEN_CREATE);
if(!$db){
echo $db->lastErrorMsg();
} else {
echo "Opened database successfully\n";
}
$sql =<<<EOF
CREATE TABLE TEST_TBL
(ID INT PRIMARY KEY NOT NULL,
NAME TEXT NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR(50),
NUM REAL);
INSERT INTO TEST_TBL (ID,NAME,AGE,ADDRESS,NUM)
VALUES (1, 'Paul', 32, 'California', 20000.00 );
INSERT INTO TEST_TBL (ID,NAME,AGE,ADDRESS,NUM)
VALUES (2, 'Allen', 25, 'Texas', 15000.00 );
INSERT INTO TEST_TBL (ID,NAME,AGE,ADDRESS,NUM)
VALUES (3, 'Teddy', 23, 'Norway', 20000.00 );
INSERT INTO TEST_TBL (ID,NAME,AGE,ADDRESS,NUM)
VALUES (4, 'Mark', 25, 'Rich-Mond ', 65000.00 );
EOF;
//---------
$ret = $db->exec($sql);
if(!$ret){
echo $db->lastErrorMsg();
} else {
echo "Table created successfully\n";
}
//---------
// or DELETE from TEST_TBL where ID=2;
$ret = $db->exec($sql="UPDATE TEST_TBL set NUM = 25000 where ID=1;");
if(!$ret){
echo $db->lastErrorMsg();
} else {
echo $db->changes(), " Record updated successfully\n";
}
//---------
$ret = $db->query($sql="SELECT * from TEST_TBL;");
while($row = $ret->fetchArray(SQLITE3_ASSOC) ){
echo "ID = ". $row['ID'] . "\n";
echo "NAME = ". $row['NAME'] ."\n";
echo "ADDRESS = ". $row['ADDRESS'] ."\n";
echo "NUM = ".$row['NUM'] ."\n\n";
}
$db->close();
memory DB:
$DB = new SQLite3($path=':memory:');
$DB->exec($sql_create_db);
/*SQLite3Result*/ $rs= $DB->query($sql);
while($row = $ret->fetchArray(SQLITE3_ASSOC) ){
}
$DB->close();
escaping:
$name = sqlite_escape_string($name);
$result = @$db->query("SELECT * FROM users WHERE username='{$name}'");
limit:
LIMIT [no OF ROWS] OFFSET [ROW num]
autoincrement:
sqlite> CREATE TABLE XXX ( ID INTEGER PRIMARY KEY AUTOINCREMENT, NAME TEXT NOT NULL, );
index:
CREATE INDEX index_name ON TABLE_NAME;
VACUUM: cleans the main database by copying its contents to a temporary database file and rebuilding the original database file from the copy, eliminates free pages, aligns table data to be contiguous, and otherwise cleans up the database file structure.
sqlite3 database_name "VACUUM;"