Transcript Introduction to SQL Light

Introduction to SQLite
ISYS 475
Download SQLite
• http://sqlite.org/download.html
– Precompiled Binaries for Windows
• Create a folder “sqlite” under c:\xampp and
copy the downloaded program to the folder.
Introduction
• SQLite is a database system that is:
– self-contained: It requires very minimal support from
external libraries;
– Serverless: reads and writes directly from the database
files on disk and requires no intermediary server
process. SQLite itself is the server.
– zero-configuration: It does not need to be "installed"
before it is used. There is no "setup" procedure;
– transactional: all changes and queries appear to be
Atomic, Consistent, Isolated, and Durable (ACID) that
can survives system failure.
– It is small. A typical installation occupies only about
256K on your drive.
Where are good places to use it?
• In desktop applications. SQLite makes a great
application file format. You can use SQL, don't
have to design your own file format.
• For smaller websites. SQLite should be able to
manage a site that gets under 100,000 hits a day
easily. In other words, most websites would be
fine with SQLite as the database.
• •For embedded devices. The uses in the iPhone
and Symbian are good examples of this.
Two Ways of Working with SQLite
• SQLite Command Line
• PHP/SQLite
Starting SQLite Command Line
• To start the sqlite3 program from the Windows command prompt,
just type "sqlite3" followed by the name the file that holds the SQLite
database. If the file does not exist, a new one is created
automatically.
• Note: The standard extension for SQLite database is “.sqlite”
Helpful links
• Command line commands:
– http://www.sqlite.org/sqlite.html
– http://www.pantz.org/software/sqlite/sqlite_com
mands_and_general_usage.html
For a listing of the available dot commands, you
can enter ".help" at any time
.read FILENAME
• Execute SQL in FILENAME
sqlite> .read c:\\teaching\\475\\MySQLOrder.txt
sqlite> .tables
customers odetails orders
products salesreps tbl1
Note: Need the escape character, “\\”
.databases
• List names and files of attached databases
sqlite> .database
seq name
file
--- --------------- -----------------------------------0 main
sqlite>
c:\xampp\Sqlite\salesdb.sqlite
.tables
• List names of tables
sqlite> .tables
customers odetails orders
products salesreps
.schema
• Show the CREATE statements
sqlite> .schema
CREATE TABLE customers (cid char(3), cname varchar(20), rating char(1),
city varchar(20));
CREATE TABLE odetails(oid char(3), pid char(3),qty decimal(5,1));
CREATE TABLE orders(oid char(3), cid char(3),sid char(3),odate date);
CREATE TABLE products(pid char(7), pname varchar(15), price decimal(6,2),
onhand decimal(5,1));
CREATE TABLE salesreps(sid char(3),sname varchar(20),phone char(4));
CREATE TABLE tbl1(one varchar(10), two smallint);
.backup
• sqlite> .backup c:\\xampp\\sqlite\\backup.txt
.mode MODE
• Set output mode where MODE is one of:
•
csv Comma-separated values
•
column Left-aligned columns.
•
html HTML <table> code
•
insert SQL insert statements for TABLE
•
line One value per line
•
list Values delimited by .separator string
•
tabs Tab-separated values
•
tcl TCL list elements
sqlite> .mode list
sqlite> select * from tbl1;
Set output to html
sqlite> .mode html
sqlite> select * from customers;
<TR><TD>C1</TD>
<TD>MYERS</TD>
<TD>A</TD>
<TD>CHICAGO</TD>
</TR>
<TR><TD>C2</TD>
<TD>GOODMAN</TD>
<TD>A</TD>
<TD>SAN FRANCISCO</TD>
</TR>
<TR><TD>C3</TD>
<TD>LEE</TD>
<TD>B</TD>
<TD>CHICAGO</TD>
</TR>
<TR><TD>C4</TD>
<TD>GRAUER</TD>
<TD>C</TD>
<TD>LOS ANGELES</TD>
</TR>
Other Commands
• .output FILENAME
Send output to
FILENAME
• .output stdout
Send output to the screen
• .quit
PHP and SQLite
http://www.php.net/manual/en/boo
k.sqlite.php
PHP Links to SQLite
• This is an extension for the SQLite
Embeddable SQL Database Engine. SQLite is a
C library that implements an embeddable SQL
database engine. Programs that link with the
SQLite library can have SQL database access
without running a separate RDBMS process.
• SQLite is the server. The SQLite library reads
and writes directly to and from the database
files on disk.
Run phpinfo();
http://localhost/xampp/
Two Ways of Working with SQLite
• Using PDO commands
• Using PHP/SQLIte commands
Working with an existing Sqlite
database with PDO
• DSN: sqlite:absolute path to database
• Example:
sqlite:c:\\xampp\\Sqlite\\salesdb.sqlite
Define a database handler
1. Connecting to a database in the current project
folder:
$db = new PDO("sqlite:SalesDB.sqlite");
2. Connecting to a database at a specified path:
$db = new PDO("sqlite:c:\\xampp\\Sqlite\\salesdb.sqlite");
Run SQL SELECT command and fetch
records
<?php
<?php
$db = new PDO("sqlite:c:\\xampp\\sqlite\\salesdb.sqlite");
$result=$db->query("SELECT * FROM customers");
$result->setFetchMode(PDO::FETCH_ASSOC);
while($row = $result->fetch()) {
echo $row['cid'] . " " . $row['cname'] . " " . $row['city'] . " " . $row['rating'] . "<br>";
}
?>
Using foreach
<?php
$db = new PDO("sqlite:c:\\xampp\\sqlite\\salesdb.sqlite");
$result=$db->query("SELECT * FROM customers");
$result->setFetchMode(PDO::FETCH_ASSOC);
foreach ($result as $row)
{
echo $row['cid'] . " " . $row['cname'] . " " . $row['city'] . " " . $row['rating'] .
"<br>";
}
?>
Use PDO exec to run Insert
<?php
$db = new PDO("sqlite:c:\\xampp\\sqlite\\salesdb.sqlite");
$db->exec("INSERT INTO customers VALUES ('c6','chen','sf', 'a')");
?>
Use PDO exec to run Delete
<?php
$db = new PDO("sqlite:c:\\xampp\\sqlite\\salesdb.sqlite");
$count = $db->exec("DELETE FROM customers WHERE cid = 'c6'");
print("Deleted $count rows.\n");
?>
Use PDO to create a new sqlite
database
• DSN: sqlite:absolute path to database
• Example:
sqlite:customer.sqlite
sqlite:c:\\xampp\\sqlite\\customer.sqlite
Note: If the database does not exist, SQLite will create it.
But we need to execute SQL CREATE TABLE command to
create tables.
Define a database handler
1. Creating a new database in the current project
folder:
$db = new PDO("sqlite:customer.sqlite");
2. Creating a new database at a specified path:
$db = new PDO("sqlite:c:\\xampp\\sqlite\\customer.sqlite");
Creating a New SQLite Table
• Use exec() command to run the CREATE TABLE
command:
$db->exec('CREATE TABLE customer (cid CHAR(5) PRIMARY KEY,
cname CHAR(25), city CHAR(25), rating CHAR(1))');
Run SQL Insert commands to add
records
<?php
$db = new PDO("sqlite:c:\\xampp\\sqlite\\customer.sqlite");
$db->exec('CREATE TABLE customer (cid CHAR(5) PRIMARY KEY,
cname CHAR(25), city CHAR(25), rating CHAR(1))');
$db->exec("INSERT INTO customer VALUES ('c1','peter','sf', 'a')");
$db->exec("INSERT INTO customer VALUES ('c2','paul','la', 'b')");
$db->exec("INSERT INTO customer VALUES ('c3','mary','sf', 'a')");
?>
Run SQL SELECT command and fetch
records
<?php
$db = new PDO("sqlite:c:\\xampp\\sqlite\\customer.sqlite");
$result=$db->query("SELECT * FROM customer");
$result->setFetchMode(PDO::FETCH_ASSOC);
foreach ($result as $row)
{
echo $row['cid'] . " " . $row['cname'] . " " . $row['city'] . " " .
$row['rating'] . "<br>";
}
?>
Using PHP/SQLIte3 class
http://www.php.net/manual/en/sqlite3.close.php
• SQLite3::__construct — Instantiates an SQLite3 object
and opens an SQLite 3 database
• SQLite3::query — Executes an SQL query and returns a
SQLite3Result class
• •SQLite3Result::fetchArray — Fetches a result row as an
associative or numerically indexed array or both
• SQLite3::exec — Executes a result-less query against a
given database
• SQLite3::changes — Returns the number of database
rows that were changed (or inserted or deleted)
• SQLite3::close — Closes the database connection
Working with SQLite3 class
<?php
$db = new SQLite3("c:\\xampp\\sqlite\\customer.sqlite");
$result=$db->query("SELECT * FROM customer");
while($row = $result->fetchArray())
{
echo $row['cid'] . " " . $row['cname'] . " " . $row['city'] . " " . $row['rating'] .
"<br>";
}
?>
Adding a record
<?php
$db = new SQLite3("c:\\xampp\\sqlite\\customer.sqlite");
$db->exec("INSERT INTO customer VALUES ('c6','chen','sf', 'a')");
echo $db->changes() . 'record added';
?>
Deleting a record
<?php
$db = new SQLite3("c:\\xampp\\sqlite\\customer.sqlite");
$db->exec("DELETE FROM customer WHERE cid = 'c6'");
echo $db->changes() . 'record deleted';
?>
In-memory database
• An in-memory database (IMDB; also main memory
database system or MMDB) is a database management
system that primarily relies on main memory for
computer data storage. It is contrasted with database
management systems which employ a disk storage
mechanism. Main memory databases are faster than
disk-optimized databases since the internal
optimization algorithms are simpler and execute fewer
CPU instructions. Accessing data in memory eliminates
seek time when querying the data, which provides
faster and more predictable performance than disk
Creating SQLite Database in Memory
• DSN: sqlite::memory:
– Example:
$db = new PDO("sqlite::memory:");
Note: there is a “:” after the word memory.
• The database ceases to exist as soon as the database
connection is closed.
• opening two database connections each with the
filename ":memory:" will create two independent inmemory databases.
In Memory Database Example
<?php
$db = new PDO("sqlite::memory:");
$db->exec('CREATE TABLE customer (cid CHAR(5) PRIMARY KEY,
cname CHAR(25), city CHAR(25), rating CHAR(1))');
$db->exec("INSERT INTO customer VALUES ('c1','peter','sf', 'a')");
$db->exec("INSERT INTO customer VALUES ('c2','paul','la', 'b')");
$db->exec("INSERT INTO customer VALUES ('c3','mary','sf', 'a')");
$result=$db->query("SELECT * FROM customer");
$result->setFetchMode(PDO::FETCH_ASSOC);
foreach ($result as $row)
{
echo $row['cid'] . " " . $row['cname'] . " " . $row['city'] . " " . $row['rating'] .
"<br>";
}
?>
ATTACH DATABASE
• The ATTACH DATABASE statement adds
another database file to the current database
connection.
• Example:
– ATTACH DATABASE "FromDisk.sqlite" AS diskdb;
• Tables in an attached database can be referred
to using the syntax database-name.tablename.
Copy Disk Database to Memory
<?php
$dbMemo = new PDO('sqlite::memory:');
$dbMemo->exec('ATTACH "c:\\xampp\\sqlite\\salesdb.sqlite" AS diskdb');
$dbMemo->exec('create table customer as select * from diskdb.customers');
$dbMemo->exec('DETACH diskdb');
$result=$dbMemo->query("SELECT * FROM customer");
$result->setFetchMode(PDO::FETCH_ASSOC);
foreach ($result as $row)
{
echo $row['cid'] . " " . $row['cname'] . " " . $row['city'] . " " . $row['rating'] .
"<br>";
}
?>
Copy Memory Database to disk
<?php
$db = new PDO("sqlite::memory:");
$db->exec('CREATE TABLE customer (cid CHAR(5) PRIMARY KEY,
cname CHAR(25), city CHAR(25), rating CHAR(1))');
$db->exec("INSERT INTO customer VALUES ('c12','peter','sf', 'a')");
$db->exec("INSERT INTO customer VALUES ('c22','paul','la', 'b')");
$db->exec("INSERT INTO customer VALUES ('c32','mary','sf', 'a')");
$result=$db->query("SELECT * FROM customer");
$result->setFetchMode(PDO::FETCH_ASSOC);
foreach ($result as $row)
{
echo $row['cid'] . " " . $row['cname'] . " " . $row['city'] . " " . $row['rating'] . "<br>";
}
$db->exec('ATTACH "c:\\xampp\\sqlite\\customer.sqlite" AS diskdb');
$db->exec('insert into diskdb.customer select * from customer');
$db->exec('DETACH diskdb');
$dbSave = new PDO('sqlite:c:\\xampp\\sqlite\\customer.sqlite');
$result=$dbSave->query("SELECT * FROM customer");
$result->setFetchMode(PDO::FETCH_ASSOC);
foreach ($result as $row)
{
echo $row['cid'] . " " . $row['cname'] . " " . $row['city'] . " " . $row['rating'] . "<br>";
}
?>