Create/open a database:
sqlite3 test.db
Create Table:
create table tbl1(one varchar(10) primary key, two smallint, f2 text, f3 real);
Insert row:
insert into tbl1 (one,two) values('hello!',10);
insert into tbl1 values('goodbye', 20);
select rows: select * from tbl1;
hello!|10
goodbye|20
Show list of the tables:
.tables
Show the structure of a table:
.schema table
Add column in table:
ALTER TABLE table_name
ADD new_column_name column_definition;
Modify column in table:
You can not use the ALTER TABLE statement to modify a column in SQLite.
Instead you will need to rename the table, create a new table, and copy the data into the new table.
PRAGMA foreign_keys=off;
BEGIN TRANSACTION;
ALTER TABLE table1 RENAME TO _table1_old;
CREATE TABLE table1 (
( column1 datatype [ NULL | NOT NULL ],
column2 datatype [ NULL | NOT NULL ],
...
);
INSERT INTO table1 (column1, column2, ... column_n)
SELECT column1, column2, ... column_n
FROM _table1_old;
COMMIT;
PRAGMA foreign_keys=on;
Rename table , delete Table:
ALTER TABLE table_name
RENAME TO new_table_name;
drop Table tbl1;
Insert DateTime:
insert into tbl1(id, enterDate) values (1,DateTime()); // Now
insert into tbl1(id, enterDate) values (2,'2007-01-01 10:00:00');// the right format of DateTime
File I/O:
sqlite> CREATE TABLE images(name TEXT, type TEXT, img BLOB);
sqlite> INSERT INTO images(name,type,img)
...> VALUES('icon','jpeg',readfile('icon.jpg'));
sqlite> SELECT writefile('icon.jpg',img) FROM images WHERE name='icon';
Querying the database schema:
SELECT name FROM sqlite_master
WHERE type IN ('table','view') AND name NOT LIKE 'sqlite_%'
ORDER BY 1
CSV Export /Import:
sqlite> SELECT * FROM tab1;
sqlite> .system c:/work/dataout.csv
sqlite> .mode csv
sqlite> .import C:/work/somedata.csv tab1
Meta queries
List of All tables:
SELECT name FROM sqlite_master WHERE type = 'table'
List of all objects
SELECT * FROM sqlite_master
List of all columns and their spec:
WITH all_tables AS (SELECT name FROM sqlite_master WHERE type = 'table')
SELECT at.name table_name, pti.*
FROM all_tables at INNER JOIN pragma_table_info(at.name) pti
ORDER BY table_name
Sqlite PHP
Connecting and Performing sql operations:
< ?php
try {
$db = new PDO('sqlite:mydatabase.db');
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
// Create a table if not exists
$db->exec('CREATE TABLE IF NOT EXISTS users (id INTEGER PRIMARY KEY, name TEXT)');
// Insert data
$db->exec('INSERT INTO users (name) VALUES ("John Doe")');
echo 'Table created and data inserted successfully.';
} catch (PDOException $e) {
echo 'Error: ' . $e->getMessage();
}
? >
Transactions
The following method inserts a new document into the documents table and returns the document id.
/**
* Insert blob data into the documents table
* @param type $pathToFile
* @return document id
*/
public function insertDoc($mimeType, $pathToFile) {
$sql = "INSERT INTO documents(mime_type,doc) "
. "VALUES(:mime_type,:doc)";
// read data from the file
$fh = fopen($pathToFile, 'rb');
$stmt = $this->pdo->prepare($sql);
// pass values
$stmt->bindParam(':mime_type', $mimeType);
$stmt->bindParam(':doc', $fh, \PDO::PARAM_LOB);
// execute the INSERT statement
$stmt->execute();
fclose($fh);
// return the document id
return $this->pdo->lastInsertId();
}
// The following method assigns a document to a task.
/**
* Assign a document to a task
* @param int $taskId
* @param int $documentId
*/
private function assignDocToTask($taskId, $documentId) {
$sql = "INSERT INTO task_documents(task_id,document_id) "
. "VALUES(:task_id,:document_id)";
$stmt = $this->pdo->prepare($sql);
$stmt->bindParam(':task_id', $taskId);
$stmt->bindParam(':document_id', $documentId);
$stmt->execute();
}
//The following method inserts a document and assigns it to a task within a single transaction.
/**
* Add a task and associate a document to it
* @param int $taskId
* @param string $mimeType
* @param string $pathToFile
*/
public function attachDocToTask($taskId, $mimeType, $pathToFile) {
try {
// to make sure the foreign key constraint is ON
$this->pdo->exec('PRAGMA foreign_keys = ON');
// begin the transaction
$this->pdo->beginTransaction();
// insert a document first
$documentId = $this->insertDoc($mimeType, $pathToFile);
// associate document with the task
$this->assignDocToTask($taskId, $documentId);
// commit update
$this->pdo->commit();
} catch (\PDOException $e) {
// rollback update
$this->pdo->rollback();
//
throw $e;
}
}