sqlite-cheat-sheet.md

SQLite cheat sheet

• tutorial
• sqlite browser

Alternatives

h2

sudo apt install h2database

hsqldb

sudo apt install hsqldb-utils

sqlite install

sudo apt-get install sqlite3 sqlite3-doc sqlite3-tool

tools

• sqldiff
• sqlite3_analyzer
• mysql db to sqlite

mysql to sqlite

init db

sqlite3 -init db.sqlite

init db and import

# [pip3 install termsql](https://github.com/tobimensch/termsql)
termsql -i mycsvfile.CSV -d ',' -c 'field_name,field_index' -t 'index_de' -o mynewdatabase.db

open db

sqlite3 mynewdatabase.db
.tables

or

sqlite3
.open mydatabase.db

or

sqlite3
attach "mydatabase.db" as mydb;

redirect output to file redirect output to stdout

.output result-output.txt
select * from index_de;
.output

execute inline query

sqlite3 $DB_FILE "select count(*) from r_d_dxc_developer;"

create table and import

CREATE TABLE index_de(field_name TEXT NOT NULL, field_type TEXT NOT NULL );
CREATE TABLE index_cn(field_name TEXT NOT NULL, field_type TEXT NOT NULL );
-- show tables 
.tables
.schema index_cn

-- import csv to table 
.mode csv
.separator ","
.import autolabel-staging-merge.elk-index.cn.fields.csv index_cn
.import autolabel-merge.elk-index.de-prod.fields.csv index_de

sqlite export to file

.output autolabel-merge.fields
.shell ls -la autolabel-merge.fields
.shell cat autolabel-merge.fields

select index_cn.field_name, index_de.field_type "type_de", index_cn.field_type "type_cn"
from index_cn index_cn
inner join index_de on index_de.field_name=index_cn.field_name 
where index_cn.field_type != index_de.field_type
;
.output stdout
.exit

sqlite import sql write to db

cat src/scripts.sql | sqlite3 src/db.sqlite

constraint example

CREATE TABLE cv_generated (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  tags TEXT,
  board INTEGER,
  position_note TEXT,
  creation_date DATETIME DEFAULT CURRENT_TIMESTAMP,

  FOREIGN KEY (board) REFERENCES board (id)
);