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

<?php
  try {
    /* open the database */
    $db = 'servers.sqlite3';
    $dbh = new PDO("sqlite:$db");

    /* fetch the data */
    $servers = $dbh->prepare( "SELECT * FROM Servers" );
    $servers->execute();
  } catch (Exception $e) { die ($e); }
?>
<!DOCTYPE html>
<html>
<head>
  <title>Servers Status</title>
</head>
<body>
  <table>
    <tr>
      <th>Hostname</th>
      <th>IP Address</th>
      <th>Status</th>
      <th>Last Check</th>
    </tr>
<?php 
  /* present database data as a table */
  while ($server = $servers->fetchObject()): ?>
    <tr>
      <td><?php echo $server->Hostname; ?></td>
      <td><?php echo $server->IPAddress; ?></td>
      <td><?php echo $server->Status; ?></td>
      <td><?php echo date('r', $server->Timestamp); ?></td>
    <tr>
<?php
  endwhile;
  
  /* close the database */
  try { $dbh = NULL; } catch(Exception $e) { die ($e); }
?>
  </table>
</body>
</html>

dokuwiki

Table of Contents

cheat sheet

web

sample