SQLITE

Links

• SQLite Tutorial

SQLite

Command line

$ sqlite3 base.db -csv "select * from table" | tee output.csv
$ sqlite3 base.db -headers "select * from table" | column -t -s \|

Create BASE

CREATE TABLE IF NOT EXISTS arquitetura(
    ID INTEGER PRIMARY KEY AUTOINCREMENT,
    HOSTNAME text,
    PUBLIC_IP text,
    STATE text,
    LOCAL_ADDRESS text,
    LOCAL_PORT integer,
    REMOTE_ADDRESS text,
    REMOTE_PORT integer,
    PROCESS_ID integer,
    PROCESS_NAME text,
    OS_VERSION text,
    PROCESSOR_COUNT integer,
    RAM_MEMORY_KB real)

Select

Example

SELECT DISTINCT
      HOSTNAME,
      REMOTE_ADDRESS,
      REMOTE_PORT,
      PROCESS_NAME
    FROM
      arquitetura
    WHERE
      STATE == 'ESTABLISHED'
    AND
      LOCAL_PORT NOT IN (
        SELECT DISTINCT
          LOCAL_PORT
        FROM
          arquitetura
        WHERE
          STATE == 'LISTENING'
        AND
          LOCAL_ADDRESS <> '127.0.0.1'
          )
    AND
      LOCAL_ADDRESS <> '127.0.0.1'
    ORDER BY
      'REMOTE_PORT'

Delete

Example

delete from bloqueios where timestamp <= '$(expr $(date +"%s") - 10368000)'

List Tables

.tables

SELECT 
    name
FROM 
    sqlite_schema
WHERE 
    type ='table' AND 
    name NOT LIKE 'sqlite_%';

Get Table Info

PRAGMA table_info(table_name);