SQL

Do you like SQLite ?

just started using it a bit more often … https://www.sqlitetutorial.net/

Query

sqlite3 /path/to/db “select date,time,ip from table where ip=‘1.2.3.4’ limit 100;

.schema

show the database schema and indexes

sqlite> .schema
CREATE TABLE attack(
  "date" TEXT,
  "time" TEXT,
  "ip" TEXT,
  "asnr" TEXT,
  "ascountry" TEXT,
  "asdesc" TEXT,
  "port" TEXT,
  "proto" TEXT,
  "server" TEXT,
  "type" TEXT,
  "method" TEXT,
  "pass" TEXT,
  "user" TEXT,
  "ver" TEXT
);
CREATE UNIQUE INDEX id on attack(date,time,ip,user,pass);

Insert

INSERT INTO table (column1,column2 ,..)
VALUES( value1, value2 ,...);

Update

 update attack set asnr='$asnr', ascountry='$ascountry', asdesc='$asdesc' where ip='$ip';

Delete

delete from attack where ip='$ip';

Transfer one Table from one host to another

ssh remotehost "sqlite3 /var/db/egal.sqlite '.dump table'" |sqlite3 mydb.sqlite

HamsterDB Cleanup

sqlite3 /var/db/runden.db
delete from runden where datum like '%2020-%';
vacuum;

Any Comments ?

sha256: f9dddde1c1140ca0a8fecf1baf3a673ecfadc7d29b2c7a9ee6e33f3c52c6fd1b

Cheatsheet

Sqlite