sqlite sqlite3
====== cheat sheet ======
sqlite> .schema
CREATE TABLE temps (timestamp DATETIME, temp NUMERIC);
sqlite>
.tables
select * from temps;
select temp from temps;
select temp, timestamp from temps;
select timestamp, temp from temps;
====== web ======
https://bodo-schoenfeld.de/python-und-sqlite3/
====== sample ======
https://www.networkworld.com/article/2224091/an-introduction-to-sqlite-for-developers-and-sysadmins.html
$ sqlite3 servers.sqlite3
SQLite version 3.7.14.1 2012-10-04 19:37:12
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite>
* create the table
sqlite> CREATE TABLE Servers( Hostname TEXT PRIMARY KEY, IPAddress TEXT, Role TEXT, Status TEXT, Timestamp INT );
* bash for adding ips to sql table.
#!/bin/sh
# WARNING: this script is prone to SQL injections.
# you should always validate user input in real-world
# scripts and applications.
# our database
_DB="servers.sqlite3"
# read hostname from stdin
printf "Hostname: "
read _HOSTNAME
# read ip address from stdin
printf "${_HOSTNAME}'s IP Address: "
read _IP
# read server's role from stdin
printf "${_HOSTNAME}'s role: "
read _ROLE
# INSERT data in database
sqlite3 $_DB "INSERT INTO Servers(Hostname, IPAddress, Role)
VALUES('${_HOSTNAME}', '${_IP}', '${_ROLE}')"
* read the database
$ sqlite3 servers.sqlite3 "SELECT * FROM Servers"
mx1|1.2.3.4|Mail||
www1|2.3.4.5|Web||
* php front end
prepare( "SELECT * FROM Servers" );
$servers->execute();
} catch (Exception $e) { die ($e); }
?>
Servers Status
| Hostname |
IP Address |
Status |
Last Check |
fetchObject()): ?>
| Hostname; ?> |
IPAddress; ?> |
Status; ?> |
Timestamp); ?> |