Show Menu
Cheatography
  1. Home >
  2. Cheat Sheets >

sqlite and java Cheat Sheet (DRAFT) by

This is a draft cheat sheet. It is a work in progress and is not finished yet.

what's sqlite?

SQLite is an in-process library that implements a self-c­ont­ained, server­less, zero-c­onf­igu­ration, transa­ctional SQL database engine.

SQLite engine is not a standalone process like other databases, you can link it statically or dynami­cally as per your requir­ement with your applic­ation. SQLite accesses its storage files directly.

A complete SQLite database is stored in a single cross-­pla­tform disk file.

SQLite is very small and light weight, less than 400KiB fully configured or less than 250KiB with optional features omitted.

SQLite transa­ctions are fully ACID-c­omp­liant, allowing safe access from multiple processes or threads.

SQLite history?

2000 - D. Richard Hipp designed SQLite for the purpose of no admini­str­ation required for operating a program.

2000 - In August, SQLite 1.0 released with GNU Database Manager.

2011 - Hipp announced to add UNQl interface to SQLite DB and to develop UNQLite (Document oriented database).

Limitation

Only Left out join
no right/full outer join
alter table [add column]
no drop column, alter column,add constraint
trigger for each row
not for each statement
view read only
no insert­/up­dat­e/d­elete on a view
grant and revoke on OS file level

instal­ltion

3 Create a folder C:\>sqlite and unzip above two zipped files in this folder, which will give you sqlite­3.def, sqlite­3.dll and sqlite­3.exe files.
4 Add C:\>sqlite in your PATH enviro­nment variable and finally go to the command prompt and issue sqlite3 command
Linux: sqlite3
$tar xvfz sqlite­-au­toc­onf­-30­715­02.t­ar.gz
$cd sqlite­-au­toc­onf­-30­71502
$./con­figure --pref­ix=­/us­r/local
$make
$make install

SQLite command

DDL: create alter drop
DML insert update delete
Query: select
dot cmd (no ;)
.help
.table ?pattern?
.schema
.databases
.schema sqlite­_master
.header on
.width n1,n2,n3

cmd list

.archive ... Manage SQL archives
.auth ON|OFF Show authorizer callbacks
.backup ?DB? FILE Backup DB (default "­mai­n") to FILE
.bail on|off Stop after hitting an error. Default OFF
.binary on|off Turn binary output on or off. Default OFF
.cd DIRECTORY Change the working directory to DIRECTORY
.changes on|off Show number of rows changed by SQL
.check GLOB Fail if output since .testcase does not match
.clone NEWDB Clone data into NEWDB from the existing database
.databases List names and files of attached databases
.dbconfig ?op? ?val? List or change sqlite­3_d­b_c­onfig() options
.dbinfo ?DB? Show status inform­ation about the database
.dump ?TABLE? ... Render all database content as SQL
.echo on|off Turn command echo on or off
.eqp on|off­|fu­ll|... Enable or disable automatic EXPLAIN QUERY PLAN
.excel Display the output of next command in a spread­sheet
.exit ?CODE? Exit this program with return­-code CODE
.expert EXPERI­MENTAL. Suggest indexes for specified queries
.fulls­chema ?--indent? Show schema and the content of sqlite­_stat tables
.headers on|off Turn display of headers on or off
.help ?-all? ?PATTERN? Show help text for PATTERN
.import FILE TABLE Import data from FILE into TABLE
.imposter INDEX TABLE Create imposter table TABLE on index INDEX
.indexes ?TABLE? Show names of indexes
.limit ?LIMIT? ?VAL? Display or change the value of an SQLITE­_LIMIT
.lint OPTIONS Report potential schema issues.
.load FILE ?ENTRY? Load an extension library
.log FILE|off Turn logging on or off. FILE can be stderr­/stdout
.mode MODE ?TABLE? Set output mode
.nullvalue STRING Use STRING in place of NULL values
.once (-e|-x­|FILE) Output for the next SQL command only to FILE
.open ?OPTIONS? ?FILE? Close existing database and reopen FILE
.output ?FILE? Send output to FILE or stdout if FILE is omitted
.parameter CMD ... Manage SQL parameter bindings
.print STRING... Print literal STRING
.progress N Invoke progress handler after every N opcodes
.prompt MAIN CONTINUE Replace the standard prompts
.quit Exit this program
.read FILE Read input from FILE
.restore ?DB? FILE Restore content of DB (default "­mai­n") from FILE
.save FILE Write in-memory database into FILE
.scanstats on|off Turn sqlite­3_s­tmt­_sc­ans­tatus() metrics on or off
.schema ?PATTERN? Show the CREATE statements matching PATTERN
.selftest ?OPTIONS? Run tests defined in the SELFTEST table
.separator COL ?ROW? Change the column and row separators
.sha3sum ... Compute a SHA3 hash of database content
.shell CMD ARGS... Run CMD ARGS... in a system shell
.show Show the current values for various settings
.stats ?on|off? Show stats or turn stats on or off
.system CMD ARGS... Run CMD ARGS... in a system shell
.tables ?TABLE? List names of tables matching LIKE pattern TABLE
.testcase NAME Begin redire­cting output to 'testc­ase­-ou­t.txt'
.timeout MS Try opening locked tables for MS millis­econds
.timer on|off Turn SQL timer on or off
.trace ?OPTIONS? Output each SQL statement as it is run
.vfsinfo ?AUX? Inform­ation about the top-level VFS
.vfslist List all available VFSes
.vfsname ?AUX? Print the name of the VFS stack
.width NUM1 NUM2 ... Set column widths for "­col­umn­" mode

Syntax

case insens­itive in genera;\l
GLOB and glob not same
space and comments: - / /
statement ;
analyze [datab­ase­_na­me.[­ta­ble­_name]
alter table [add column­|rename to ...]
attach database 'dbname' as 'alias name'
DETACH DATABASE 'Alias­-Name';
begin [exclusive transa­ction;
commit;
CREATE TRIGGER databa­se_name BEFORE INSERT ON table_name FOR EACH ROW begin ....en­d.t­rig­ger­_name
CREATE VIRTUAL TABLE databa­se_­nam­e.t­abl­e_name USING weblog( access.log );
EXPLAIN INSERT statem­ent...;
EXPLAIN QUERY PLAN SELECT statem­ent...;
WHERE column­_name GLOB { PATTERN };
PRAGMA table_­inf­o(t­abl­e_n­ame);
PRAGMA cache_size = 1024;
RELEASE savepo­int­_name;
ROLLBACK TO SAVEPOINT savepo­int­_name;
VACUUM;

data type

null
integer
real
text
UTF-8, UTF-16BE or UTF-16LE
blob
a blob of data, stored exactly as it was input
Type affinity
text ( null, texxt, blob)
numeric ( all 5)
integer ( all 5, casting exception)
real( all 5, force integer to float)
NONE no coerce
boolea­n(0,1)
date: text, real ( days since noon, 11/24/­471­4BC­),i­nteger ( second from 1970-0-01 UTC)

first scope

sqlite3 test.db
sqlite>
.databases
main: C:\Use­rs­\jmf­eng­\te­st.db
sqlite3 test.db .dump >te­std­b.sql
sqlite3 testdb.db­<te­std­b.sql
sqlite3 attach database 'test.db' as 'test';
database main and temp reserved for primary dba nd temp database (temp objects)
create table ... ; .tables .schema table_name
insert into t (....) values­(....)
insert into .... select ....
select ... from .... where .....
.header on | .mode column­|.width #,#,#....
operator: (+-*/%) (== = != <> > < >= <= !< !>) ( and between exists in not in like GLOB not or is null is is not || unque) ( & I ~ << >>)
expression (values + operators + functions)
select curren­t_t­ime­stamp; select 3+2;
LIKE % _
GLOBE ? [1] * [0,n]
GLOBE '200';GLOBE '2??3'
LIMIT [no of row] offset [row num]

advanced sqlite - pragma

pragma pragma­_na­me=­value; set enviro­nmental var and state flag
pragma databa­se.a­ut­o_v­acuum=0 [1|2]
None, full or increm­ental shrink file
cache_­siz­e=p­ages; default 2000, minimal 10 pages
PRAGMA case_s­ens­iti­ve_like = [true|­false]; for LIKE operat­or,­default false
PRAGMA count_­changes = [true|­false]; DML return, default false
PRAGMA databa­se_­list;
PRAGMA encoding = format; control string encoding UTF-8, UTF-16le, or UTF-16be
PRAGMA [datab­ase.]f­ree­lis­t_c­ount;
PRAGMA databa­se.j­ou­rna­l_mode =delet­e|t­run­cat­e|p­ers­ist­|me­mor­y|off;
PRAGMA [datab­ase.]m­ax_­pag­e_count = max_pa­ge;The default value is 1,073,­741,823 which is one giga-page, default 1K/peage, total 1TB size
PRAGMA [datab­ase.]p­age­_count;
PRAGMA [datab­ase.]p­age­_size = bytes; 0.5k~32k, set the page size and then vacuum it immedi­ately to change the database size
PRAGMA parser­_trace = [true|­false]; default false
PRAGMA [datab­ase.]s­che­ma_­version = number; keep DDL count
PRAGMA databa­se.s­ec­ure­_delete = [true|­false]; default false
PRAGMA sql_trace = [true|­false];
PRAGMA [datab­ase.]s­ync­hronous =
0-OFF 1 noreml 2-full
PRAGMA temp_store = mode;
0 defaul­t|file, 1 file, 2 memory
PRAGMA temp_s­tor­e_d­ire­ctory = 'direc­tor­y_p­ath';
PRAGMA [datab­ase.]u­ser­_ve­rsion = number;
PRAGMA writab­le_­schema = [true|­false]; system table sqlite_ will be able modified
 

advanced sqlite

not null, default, unique­,pr­ima­ry,­CHE­CK(­SALARY > 0)
Crosse join|inner join |left outer join
Union |union all (dupli­cates)
CREATE TRIGGER trigge­r_name [BEFOR­E|A­FTER] UPDATE OF column­_name ON table_name BEGIN ... END;
DML; FOR EACH ROW; old. new.;w­hen­;before after; on table_­name; raise()
SELECT name FROM sqlite­_master WHERE type = 'trigger';
DROP TRIGGER trigge­r_name;
CREATE INDEX index_name ON table_­name;
CREATE UNIQUE INDEX index_name on table_name (colum­n_n­ame­_list);
implicit index on primary key and unique key
DROP INDEX salary­_index;
avoid indexes ( small table,DML freq,a lot null| freq manipu­late]
SELECT­|DE­LET­E|U­PDATE column1, column2... INDEXED BY (index­_name) table_name WHERE (CONDI­TION);
ACID: Atomicity Consis­tency Isolation Durability
BEGIN TRANSA­CTION
COMMIT
ROLLBACK
integer autoin­cement

advanced sqlite - injection

$name = "Qadir'; DELETE FROM users;";
@$db->query("SELECT * FROM users WHERE username = '{$name}'");

if (preg_match("/^\w{8,20}$/", $_GET['username'], $matches)){
   $db = new SQLiteDatabase('filename');
   $result = @$db->query("SELECT * FROM users WHERE username = $matches[0]");
} else {
   echo "username not accepted";
}

if (get_magic_quotes_gpc()) {
   $name = sqlite_escape_string($name);
}
$result = @$db->query("SELECT * FROM users WHERE username = '{$name}'");

explain, vacuum

EXPLAIN  QUERY PLAN [SQLite Query]


VACUUM command rebuilds the database file from scratch,cleans the main database by copying its contents to a temporary database file and reloading the original database file from the copy. used for  cleaning free pages, align data to be contiguous, clean data structure. rowid changed, unless integer promarykey explicitly. main db only, not on attached. fail if active tx, no-op for in memory db

$sqlite3 database_name "VACUUM;"
sqlite> VACUUM;
sqlite> VACUUM table_name;

sqlite> PRAGMA auto_vacuum = NONE; -- 0 means disable auto vacuum
sqlite> PRAGMA auto_vacuum = FULL; -- 1 means enable full auto vacuum
sqlite> PRAGMA auto_vacuum = INCREMENTAL; -- 2 means enable incremental vacuum

$sqlite3 database_name "PRAGMA auto_vacuum;"

date times

date(t­ime­string, modifi­ers...)
YYYY-MM-DD
time(t­ime­string, modifi­ers...)
HH:MM:SS
dateti­me(­tim­est­ring, modifi­ers...)
julian­day­(ti­mes­tring, modifi­ers...)
strfti­me(­tim­est­ring, modifi­ers...)
YYYY-MM-DD HH:MM:­SS.SSS now
%d %f %H %j %J %m %M %s %S %w %W %Y %%
SELECT date('­now');
2013-05-07
SELECT date('­now­','­start of month','+1 month','-1 day');
2013-05-31
SELECT dateti­me(­109­294­1466, 'unixe­poch');
2004-08-19 18:51:06
SELECT dateti­me(­109­294­1466, 'unixe­poch', 'local­time');
2004-08-19 13:51:06
SELECT strfti­me(­'%s­','­now');
1393348134
SELECT julian­day­('now') - julian­day­('1­776­-07­-04');
86798.7­09­4695023
SELECT strfti­me(­'%s­','­now') - strfti­me(­'%s­','­200­4-01-01 02:34:­56');
295001572
SELECT date('­now­','­start of year','+9 months­','­weekday 2');
first duesday in oct 2013-10-01
SELECT time('­12:00', 'local­time');
05:00:00
SELECT time('­12:00', 'utc');
19:00:00

useful functions

count, max, min avg,sum, abs
random
-92233­720­368­547­75808 and +92233­720­368­547­75807.
upper, lower,­len­gth­,su­bst­r,i­nstr, replace
sqlite­_ve­rsion()

java and sqlite

https://www.tutorialspoint.com/sqlite/sqlite_java.htm

1 download jar
2 add to build path

import java.sql.*;

public class SQLiteJDBC {
  public static void main( String args[] ) {
      Connection c = null;
      
      try {
         Class.forName("org.sqlite.JDBC");
         c = DriverManager.getConnection("jdbc:sqlite:test.db");
      } catch ( Exception e ) {
         System.err.println( e.getClass().getName() + ": " + e.getMessage() );
         System.exit(0);
      }
      System.out.println("Opened database successfully");
   }
}