\documentclass[10pt,a4paper]{article}

% Packages
\usepackage{fancyhdr}           % For header and footer
\usepackage{multicol}           % Allows multicols in tables
\usepackage{tabularx}           % Intelligent column widths
\usepackage{tabulary}           % Used in header and footer
\usepackage{hhline}             % Border under tables
\usepackage{graphicx}           % For images
\usepackage{xcolor}             % For hex colours
%\usepackage[utf8x]{inputenc}    % For unicode character support
\usepackage[T1]{fontenc}        % Without this we get weird character replacements
\usepackage{colortbl}           % For coloured tables
\usepackage{setspace}           % For line height
\usepackage{lastpage}           % Needed for total page number
\usepackage{seqsplit}           % Splits long words.
%\usepackage{opensans}          % Can't make this work so far. Shame. Would be lovely.
\usepackage[normalem]{ulem}     % For underlining links
% Most of the following are not required for the majority
% of cheat sheets but are needed for some symbol support.
\usepackage{amsmath}            % Symbols
\usepackage{MnSymbol}           % Symbols
\usepackage{wasysym}            % Symbols
%\usepackage[english,german,french,spanish,italian]{babel}              % Languages

% Document Info
\author{Jianmin Feng (taotao)}
\pdfinfo{
  /Title (sqlite-and-java.pdf)
  /Creator (Cheatography)
  /Author (Jianmin Feng (taotao))
  /Subject (sqlite and java Cheat Sheet)
}

% Lengths and widths
\addtolength{\textwidth}{6cm}
\addtolength{\textheight}{-1cm}
\addtolength{\hoffset}{-3cm}
\addtolength{\voffset}{-2cm}
\setlength{\tabcolsep}{0.2cm} % Space between columns
\setlength{\headsep}{-12pt} % Reduce space between header and content
\setlength{\headheight}{85pt} % If less, LaTeX automatically increases it
\renewcommand{\footrulewidth}{0pt} % Remove footer line
\renewcommand{\headrulewidth}{0pt} % Remove header line
\renewcommand{\seqinsert}{\ifmmode\allowbreak\else\-\fi} % Hyphens in seqsplit
% This two commands together give roughly
% the right line height in the tables
\renewcommand{\arraystretch}{1.3}
\onehalfspacing

% Commands
\newcommand{\SetRowColor}[1]{\noalign{\gdef\RowColorName{#1}}\rowcolor{\RowColorName}} % Shortcut for row colour
\newcommand{\mymulticolumn}[3]{\multicolumn{#1}{>{\columncolor{\RowColorName}}#2}{#3}} % For coloured multi-cols
\newcolumntype{x}[1]{>{\raggedright}p{#1}} % New column types for ragged-right paragraph columns
\newcommand{\tn}{\tabularnewline} % Required as custom column type in use

% Font and Colours
\definecolor{HeadBackground}{HTML}{333333}
\definecolor{FootBackground}{HTML}{666666}
\definecolor{TextColor}{HTML}{333333}
\definecolor{DarkBackground}{HTML}{0B5408}
\definecolor{LightBackground}{HTML}{F7F9F7}
\renewcommand{\familydefault}{\sfdefault}
\color{TextColor}

% Header and Footer
\pagestyle{fancy}
\fancyhead{} % Set header to blank
\fancyfoot{} % Set footer to blank
\fancyhead[L]{
\noindent
\begin{multicols}{3}
\begin{tabulary}{5.8cm}{C}
    \SetRowColor{DarkBackground}
    \vspace{-7pt}
    {\parbox{\dimexpr\textwidth-2\fboxsep\relax}{\noindent
        \hspace*{-6pt}\includegraphics[width=5.8cm]{/web/www.cheatography.com/public/images/cheatography_logo.pdf}}
    }
\end{tabulary}
\columnbreak
\begin{tabulary}{11cm}{L}
    \vspace{-2pt}\large{\bf{\textcolor{DarkBackground}{\textrm{sqlite and java Cheat Sheet}}}} \\
    \normalsize{by \textcolor{DarkBackground}{Jianmin Feng (taotao)} via \textcolor{DarkBackground}{\uline{cheatography.com/79308/cs/19774/}}}
\end{tabulary}
\end{multicols}}

\fancyfoot[L]{ \footnotesize
\noindent
\begin{multicols}{3}
\begin{tabulary}{5.8cm}{LL}
  \SetRowColor{FootBackground}
  \mymulticolumn{2}{p{5.377cm}}{\bf\textcolor{white}{Cheatographer}}  \\
  \vspace{-2pt}Jianmin Feng (taotao) \\
  \uline{cheatography.com/taotao} \\
  \end{tabulary}
\vfill
\columnbreak
\begin{tabulary}{5.8cm}{L}
  \SetRowColor{FootBackground}
  \mymulticolumn{1}{p{5.377cm}}{\bf\textcolor{white}{Cheat Sheet}}  \\
   \vspace{-2pt}Not Yet Published.\\
   Updated 6th June, 2019.\\
   Page {\thepage} of \pageref{LastPage}.
\end{tabulary}
\vfill
\columnbreak
\begin{tabulary}{5.8cm}{L}
  \SetRowColor{FootBackground}
  \mymulticolumn{1}{p{5.377cm}}{\bf\textcolor{white}{Sponsor}}  \\
  \SetRowColor{white}
  \vspace{-5pt}
  %\includegraphics[width=48px,height=48px]{dave.jpeg}
  Measure your website readability!\\
  www.readability-score.com
\end{tabulary}
\end{multicols}}




\begin{document}
\raggedright
\raggedcolumns

% Set font size to small. Switch to any value
% from this page to resize cheat sheet text:
% www.emerson.emory.edu/services/latex/latex_169.html
\footnotesize % Small font.

\begin{multicols*}{2}

\begin{tabularx}{8.4cm}{X}
\SetRowColor{DarkBackground}
\mymulticolumn{1}{x{8.4cm}}{\bf\textcolor{white}{what's sqlite?}}  \tn
\SetRowColor{white}
\mymulticolumn{1}{x{8.4cm}}{SQLite is an in-process library that implements a self-contained, serverless, zero-configuration, transactional SQL database engine.   \newline % Row Count 3 (+ 3)
SQLite engine is not a standalone process like other databases, you can link it statically or dynamically as per your requirement with your application. SQLite accesses its storage files directly. \newline % Row Count 7 (+ 4)
A complete SQLite database is stored in a single cross-platform disk file. \newline % Row Count 9 (+ 2)
SQLite is very small and light weight, less than 400KiB fully configured or less than 250KiB with optional features omitted. \newline % Row Count 12 (+ 3)
SQLite transactions are fully ACID-compliant, allowing safe access from multiple processes or threads.% Row Count 15 (+ 3)
} \tn 
\hhline{>{\arrayrulecolor{DarkBackground}}-}
\end{tabularx}
\par\addvspace{1.3em}

\begin{tabularx}{8.4cm}{X}
\SetRowColor{DarkBackground}
\mymulticolumn{1}{x{8.4cm}}{\bf\textcolor{white}{SQLite history?}}  \tn
\SetRowColor{white}
\mymulticolumn{1}{x{8.4cm}}{2000 - D. Richard Hipp designed SQLite for the purpose of no administration required for operating a program. \newline % Row Count 3 (+ 3)
2000 - In August, SQLite 1.0 released with GNU Database Manager. \newline % Row Count 5 (+ 2)
2011 - Hipp announced to add UNQl interface to SQLite DB and to develop UNQLite (Document oriented database).% Row Count 8 (+ 3)
} \tn 
\hhline{>{\arrayrulecolor{DarkBackground}}-}
\end{tabularx}
\par\addvspace{1.3em}

\begin{tabularx}{8.4cm}{x{3.04 cm} x{4.96 cm} }
\SetRowColor{DarkBackground}
\mymulticolumn{2}{x{8.4cm}}{\bf\textcolor{white}{Limitation}}  \tn
% Row 0
\SetRowColor{LightBackground}
Only Left out join & no right/full outer join \tn 
% Row Count 2 (+ 2)
% Row 1
\SetRowColor{white}
alter table {[}add column{]} & no drop column, alter column,add constraint \tn 
% Row Count 4 (+ 2)
% Row 2
\SetRowColor{LightBackground}
trigger for each row & not for each statement \tn 
% Row Count 6 (+ 2)
% Row 3
\SetRowColor{white}
view read only & no insert/update/delete on a view \tn 
% Row Count 8 (+ 2)
% Row 4
\SetRowColor{LightBackground}
\mymulticolumn{2}{x{8.4cm}}{grant and revoke on OS file level} \tn 
% Row Count 9 (+ 1)
\hhline{>{\arrayrulecolor{DarkBackground}}--}
\end{tabularx}
\par\addvspace{1.3em}

\begin{tabularx}{8.4cm}{p{0.8 cm} p{0.8 cm} }
\SetRowColor{DarkBackground}
\mymulticolumn{2}{x{8.4cm}}{\bf\textcolor{white}{installtion}}  \tn
% Row 0
\SetRowColor{LightBackground}
\mymulticolumn{2}{x{8.4cm}}{1 download binary: \seqsplit{https://www.sqlite.org/download.html}} \tn 
% Row Count 2 (+ 2)
% Row 1
\SetRowColor{white}
\mymulticolumn{2}{x{8.4cm}}{3  Create a folder C:\textbackslash{}\textgreater{}sqlite and unzip above two zipped files in this folder, which will give you sqlite3.def, sqlite3.dll and sqlite3.exe files.} \tn 
% Row Count 5 (+ 3)
% Row 2
\SetRowColor{LightBackground}
\mymulticolumn{2}{x{8.4cm}}{4 Add C:\textbackslash{}\textgreater{}sqlite in your PATH environment variable and finally go to the command prompt and issue sqlite3 command} \tn 
% Row Count 8 (+ 3)
\hhline{>{\arrayrulecolor{DarkBackground}}--}
\SetRowColor{LightBackground}
\mymulticolumn{2}{x{8.4cm}}{Linux: sqlite3 \newline \$tar xvfz \seqsplit{sqlite-autoconf-3071502.tar.gz} \newline \$cd sqlite-autoconf-3071502 \newline \$./configure -{}-prefix=/usr/local \newline \$make \newline \$make install}  \tn 
\hhline{>{\arrayrulecolor{DarkBackground}}--}
\end{tabularx}
\par\addvspace{1.3em}

\begin{tabularx}{8.4cm}{x{2.4 cm} x{5.6 cm} }
\SetRowColor{DarkBackground}
\mymulticolumn{2}{x{8.4cm}}{\bf\textcolor{white}{SQLite command}}  \tn
% Row 0
\SetRowColor{LightBackground}
\mymulticolumn{2}{x{8.4cm}}{DDL: create alter drop} \tn 
% Row Count 1 (+ 1)
% Row 1
\SetRowColor{white}
\mymulticolumn{2}{x{8.4cm}}{DML insert update delete} \tn 
% Row Count 2 (+ 1)
% Row 2
\SetRowColor{LightBackground}
\mymulticolumn{2}{x{8.4cm}}{Query: select} \tn 
% Row Count 3 (+ 1)
% Row 3
\SetRowColor{white}
\mymulticolumn{2}{x{8.4cm}}{dot cmd (no ;)} \tn 
% Row Count 4 (+ 1)
% Row 4
\SetRowColor{LightBackground}
.help & .table ?pattern? \tn 
% Row Count 5 (+ 1)
% Row 5
\SetRowColor{white}
.schema & .databases \tn 
% Row Count 6 (+ 1)
% Row 6
\SetRowColor{LightBackground}
\mymulticolumn{2}{x{8.4cm}}{.schema sqlite\_master} \tn 
% Row Count 7 (+ 1)
% Row 7
\SetRowColor{white}
\mymulticolumn{2}{x{8.4cm}}{.header on} \tn 
% Row Count 8 (+ 1)
% Row 8
\SetRowColor{LightBackground}
\mymulticolumn{2}{x{8.4cm}}{.width n1,n2,n3} \tn 
% Row Count 9 (+ 1)
\hhline{>{\arrayrulecolor{DarkBackground}}--}
\end{tabularx}
\par\addvspace{1.3em}

\begin{tabularx}{8.4cm}{X}
\SetRowColor{DarkBackground}
\mymulticolumn{1}{x{8.4cm}}{\bf\textcolor{white}{cmd list}}  \tn
\SetRowColor{white}
\mymulticolumn{1}{x{8.4cm}}{.archive ...                   Manage SQL archives \newline % Row Count 2 (+ 2)
.auth ON|OFF             Show authorizer callbacks \newline % Row Count 4 (+ 2)
.backup ?DB? FILE     Backup DB (default "main") to FILE \newline % Row Count 6 (+ 2)
.bail on|off                   Stop after hitting an error.  Default OFF \newline % Row Count 8 (+ 2)
.binary on|off               Turn binary output on or off.  Default OFF \newline % Row Count 10 (+ 2)
.cd DIRECTORY            Change the working directory to DIRECTORY \newline % Row Count 12 (+ 2)
.changes on|off          Show number of rows changed by SQL \newline % Row Count 14 (+ 2)
.check GLOB              Fail if output since .testcase does not match \newline % Row Count 16 (+ 2)
.clone NEWDB             Clone data into NEWDB from the existing database \newline % Row Count 18 (+ 2)
.databases               List names and files of attached databases \newline % Row Count 20 (+ 2)
.dbconfig ?op? ?val?     List or change sqlite3\_db\_config() options \newline % Row Count 22 (+ 2)
.dbinfo ?DB?             Show status information about the database \newline % Row Count 24 (+ 2)
.dump ?TABLE? ...        Render all database content as SQL \newline % Row Count 26 (+ 2)
.echo on|off             Turn command echo on or off \newline % Row Count 28 (+ 2)
.eqp on|off|full|...     Enable or disable automatic EXPLAIN QUERY PLAN \newline % Row Count 30 (+ 2)
} \tn 
\end{tabularx}
\par\addvspace{1.3em}

\vfill
\columnbreak
\begin{tabularx}{8.4cm}{X}
\SetRowColor{DarkBackground}
\mymulticolumn{1}{x{8.4cm}}{\bf\textcolor{white}{cmd list (cont)}}  \tn
\SetRowColor{white}
\mymulticolumn{1}{x{8.4cm}}{.excel                   Display the output of next command in a spreadsheet \newline % Row Count 2 (+ 2)
.exit ?CODE?             Exit this program with return-code CODE \newline % Row Count 4 (+ 2)
.expert                  EXPERIMENTAL. Suggest indexes for specified queries \newline % Row Count 6 (+ 2)
.fullschema ?-{}-indent?   Show schema and the content of sqlite\_stat tables \newline % Row Count 8 (+ 2)
.headers on|off          Turn display of headers on or off \newline % Row Count 10 (+ 2)
.help ?-all? ?PATTERN?   Show help text for PATTERN \newline % Row Count 12 (+ 2)
.import FILE TABLE       Import data from FILE into TABLE \newline % Row Count 14 (+ 2)
.imposter INDEX TABLE    Create imposter table TABLE on index INDEX \newline % Row Count 16 (+ 2)
.indexes ?TABLE?         Show names of indexes \newline % Row Count 17 (+ 1)
.limit ?LIMIT? ?VAL?     Display or change the value of an SQLITE\_LIMIT \newline % Row Count 19 (+ 2)
.lint OPTIONS            Report potential schema issues. \newline % Row Count 21 (+ 2)
.load FILE ?ENTRY?       Load an extension library \newline % Row Count 23 (+ 2)
.log FILE|off            Turn logging on or off.  FILE can be stderr/stdout \newline % Row Count 25 (+ 2)
.mode MODE ?TABLE?       Set output mode \newline % Row Count 26 (+ 1)
.nullvalue STRING        Use STRING in place of NULL values \newline % Row Count 28 (+ 2)
.once (-e|-x|FILE)       Output for the next SQL command only to FILE \newline % Row Count 30 (+ 2)
} \tn 
\end{tabularx}
\par\addvspace{1.3em}

\vfill
\columnbreak
\begin{tabularx}{8.4cm}{X}
\SetRowColor{DarkBackground}
\mymulticolumn{1}{x{8.4cm}}{\bf\textcolor{white}{cmd list (cont)}}  \tn
\SetRowColor{white}
\mymulticolumn{1}{x{8.4cm}}{.open ?OPTIONS? ?FILE?   Close existing database and reopen FILE \newline % Row Count 2 (+ 2)
.output ?FILE?           Send output to FILE or stdout if FILE is omitted \newline % Row Count 4 (+ 2)
.parameter CMD ...       Manage SQL parameter bindings \newline % Row Count 6 (+ 2)
.print STRING...         Print literal STRING \newline % Row Count 7 (+ 1)
.progress N              Invoke progress handler after every N opcodes \newline % Row Count 9 (+ 2)
.prompt MAIN CONTINUE    Replace the standard prompts \newline % Row Count 11 (+ 2)
.quit                    Exit this program \newline % Row Count 12 (+ 1)
.read FILE               Read input from FILE \newline % Row Count 13 (+ 1)
.restore ?DB? FILE       Restore content of DB (default "main") from FILE \newline % Row Count 15 (+ 2)
.save FILE               Write in-memory database into FILE \newline % Row Count 17 (+ 2)
.scanstats on|off        Turn \seqsplit{sqlite3\_stmt\_scanstatus()} metrics on or off \newline % Row Count 19 (+ 2)
.schema ?PATTERN?        Show the CREATE statements matching PATTERN \newline % Row Count 21 (+ 2)
.selftest ?OPTIONS?      Run tests defined in the SELFTEST table \newline % Row Count 23 (+ 2)
.separator COL ?ROW?     Change the column and row separators \newline % Row Count 25 (+ 2)
.sha3sum ...             Compute a SHA3 hash of database content \newline % Row Count 27 (+ 2)
.shell CMD ARGS...       Run CMD ARGS... in a system shell \newline % Row Count 29 (+ 2)
.show                    Show the current values for various settings \newline % Row Count 31 (+ 2)
} \tn 
\end{tabularx}
\par\addvspace{1.3em}

\vfill
\columnbreak
\begin{tabularx}{8.4cm}{X}
\SetRowColor{DarkBackground}
\mymulticolumn{1}{x{8.4cm}}{\bf\textcolor{white}{cmd list (cont)}}  \tn
\SetRowColor{white}
\mymulticolumn{1}{x{8.4cm}}{.stats ?on|off?          Show stats or turn stats on or off \newline % Row Count 2 (+ 2)
.system CMD ARGS...      Run CMD ARGS... in a system shell \newline % Row Count 4 (+ 2)
.tables ?TABLE?          List names of tables matching LIKE pattern TABLE \newline % Row Count 6 (+ 2)
.testcase NAME           Begin redirecting output to 'testcase-out.txt' \newline % Row Count 8 (+ 2)
.timeout MS              Try opening locked tables for MS milliseconds \newline % Row Count 10 (+ 2)
.timer on|off            Turn SQL timer on or off \newline % Row Count 11 (+ 1)
.trace ?OPTIONS?         Output each SQL statement as it is run \newline % Row Count 13 (+ 2)
.vfsinfo ?AUX?           Information about the top-level VFS \newline % Row Count 15 (+ 2)
.vfslist                 List all available VFSes \newline % Row Count 16 (+ 1)
.vfsname ?AUX?           Print the name of the VFS stack \newline % Row Count 18 (+ 2)
.width NUM1 NUM2 ...     Set column widths for "column" mode% Row Count 20 (+ 2)
} \tn 
\hhline{>{\arrayrulecolor{DarkBackground}}-}
\end{tabularx}
\par\addvspace{1.3em}

\begin{tabularx}{8.4cm}{x{4.64 cm} x{3.36 cm} }
\SetRowColor{DarkBackground}
\mymulticolumn{2}{x{8.4cm}}{\bf\textcolor{white}{Syntax}}  \tn
% Row 0
\SetRowColor{LightBackground}
case insensitive in genera;\textbackslash{}l & GLOB and glob not same \tn 
% Row Count 2 (+ 2)
% Row 1
\SetRowColor{white}
\mymulticolumn{2}{x{8.4cm}}{space and comments: - /{\emph{ }}/} \tn 
% Row Count 3 (+ 1)
% Row 2
\SetRowColor{LightBackground}
\mymulticolumn{2}{x{8.4cm}}{statement ;} \tn 
% Row Count 4 (+ 1)
% Row 3
\SetRowColor{white}
\mymulticolumn{2}{x{8.4cm}}{analyze {[}database\_name.{[}table\_name{]}} \tn 
% Row Count 5 (+ 1)
% Row 4
\SetRowColor{LightBackground}
\mymulticolumn{2}{x{8.4cm}}{alter table {[}add column|rename to ...{]}} \tn 
% Row Count 6 (+ 1)
% Row 5
\SetRowColor{white}
attach database 'dbname' as 'alias name' & DETACH DATABASE 'Alias-Name'; \tn 
% Row Count 8 (+ 2)
% Row 6
\SetRowColor{LightBackground}
\mymulticolumn{2}{x{8.4cm}}{begin {[}exclusive transaction;} \tn 
% Row Count 9 (+ 1)
% Row 7
\SetRowColor{white}
\mymulticolumn{2}{x{8.4cm}}{commit;} \tn 
% Row Count 10 (+ 1)
% Row 8
\SetRowColor{LightBackground}
\mymulticolumn{2}{x{8.4cm}}{CREATE TRIGGER database\_name BEFORE INSERT ON table\_name FOR EACH ROW begin ....end.trigger\_name} \tn 
% Row Count 12 (+ 2)
% Row 9
\SetRowColor{white}
\mymulticolumn{2}{x{8.4cm}}{CREATE VIRTUAL TABLE \seqsplit{database\_name.table\_name} USING weblog( access.log );} \tn 
% Row Count 14 (+ 2)
% Row 10
\SetRowColor{LightBackground}
\mymulticolumn{2}{x{8.4cm}}{EXPLAIN INSERT statement...;} \tn 
% Row Count 15 (+ 1)
% Row 11
\SetRowColor{white}
\mymulticolumn{2}{x{8.4cm}}{EXPLAIN QUERY PLAN SELECT statement...;} \tn 
% Row Count 16 (+ 1)
% Row 12
\SetRowColor{LightBackground}
\mymulticolumn{2}{x{8.4cm}}{WHERE column\_name GLOB \{ PATTERN \};} \tn 
% Row Count 17 (+ 1)
% Row 13
\SetRowColor{white}
\mymulticolumn{2}{x{8.4cm}}{PRAGMA \seqsplit{table\_info(table\_name);}} \tn 
% Row Count 18 (+ 1)
% Row 14
\SetRowColor{LightBackground}
\mymulticolumn{2}{x{8.4cm}}{PRAGMA cache\_size = 1024;} \tn 
% Row Count 19 (+ 1)
% Row 15
\SetRowColor{white}
\mymulticolumn{2}{x{8.4cm}}{RELEASE savepoint\_name;} \tn 
% Row Count 20 (+ 1)
% Row 16
\SetRowColor{LightBackground}
\mymulticolumn{2}{x{8.4cm}}{ROLLBACK TO SAVEPOINT savepoint\_name;} \tn 
% Row Count 21 (+ 1)
% Row 17
\SetRowColor{white}
\mymulticolumn{2}{x{8.4cm}}{VACUUM;} \tn 
% Row Count 22 (+ 1)
\hhline{>{\arrayrulecolor{DarkBackground}}--}
\end{tabularx}
\par\addvspace{1.3em}

\begin{tabularx}{8.4cm}{p{0.8 cm} x{7.2 cm} }
\SetRowColor{DarkBackground}
\mymulticolumn{2}{x{8.4cm}}{\bf\textcolor{white}{data type}}  \tn
% Row 0
\SetRowColor{LightBackground}
\mymulticolumn{2}{x{8.4cm}}{null} \tn 
% Row Count 1 (+ 1)
% Row 1
\SetRowColor{white}
\mymulticolumn{2}{x{8.4cm}}{integer} \tn 
% Row Count 2 (+ 1)
% Row 2
\SetRowColor{LightBackground}
\mymulticolumn{2}{x{8.4cm}}{real} \tn 
% Row Count 3 (+ 1)
% Row 3
\SetRowColor{white}
text & UTF-8, UTF-16BE or UTF-16LE \tn 
% Row Count 4 (+ 1)
% Row 4
\SetRowColor{LightBackground}
blob & a blob of data, stored exactly as it was input \tn 
% Row Count 6 (+ 2)
% Row 5
\SetRowColor{white}
\mymulticolumn{2}{x{8.4cm}}{Type affinity} \tn 
% Row Count 7 (+ 1)
% Row 6
\SetRowColor{LightBackground}
\mymulticolumn{2}{x{8.4cm}}{text ( null, texxt, blob)} \tn 
% Row Count 8 (+ 1)
% Row 7
\SetRowColor{white}
\mymulticolumn{2}{x{8.4cm}}{numeric ( all 5)} \tn 
% Row Count 9 (+ 1)
% Row 8
\SetRowColor{LightBackground}
\mymulticolumn{2}{x{8.4cm}}{integer ( all 5, casting exception)} \tn 
% Row Count 10 (+ 1)
% Row 9
\SetRowColor{white}
\mymulticolumn{2}{x{8.4cm}}{real( all 5, force integer to float)} \tn 
% Row Count 11 (+ 1)
% Row 10
\SetRowColor{LightBackground}
\mymulticolumn{2}{x{8.4cm}}{NONE no coerce} \tn 
% Row Count 12 (+ 1)
% Row 11
\SetRowColor{white}
\mymulticolumn{2}{x{8.4cm}}{boolean(0,1)} \tn 
% Row Count 13 (+ 1)
% Row 12
\SetRowColor{LightBackground}
\mymulticolumn{2}{x{8.4cm}}{date: text, real ( days since noon, 11/24/4714BC),integer ( second from 1970-0-01 UTC)} \tn 
% Row Count 15 (+ 2)
\hhline{>{\arrayrulecolor{DarkBackground}}--}
\end{tabularx}
\par\addvspace{1.3em}

\begin{tabularx}{8.4cm}{x{3.52 cm} x{4.48 cm} }
\SetRowColor{DarkBackground}
\mymulticolumn{2}{x{8.4cm}}{\bf\textcolor{white}{first scope}}  \tn
% Row 0
\SetRowColor{LightBackground}
sqlite3 test.db & sqlite\textgreater{} \tn 
% Row Count 1 (+ 1)
% Row 1
\SetRowColor{white}
.databases & main: C:\textbackslash{}Users\textbackslash{}jmfeng\textbackslash{}test.db \tn 
% Row Count 3 (+ 2)
% Row 2
\SetRowColor{LightBackground}
\mymulticolumn{2}{x{8.4cm}}{sqlite3 test.db .dump \textgreater{}testdb.sql} \tn 
% Row Count 4 (+ 1)
% Row 3
\SetRowColor{white}
\mymulticolumn{2}{x{8.4cm}}{sqlite3 testdb.db\textless{}testdb.sql} \tn 
% Row Count 5 (+ 1)
% Row 4
\SetRowColor{LightBackground}
\mymulticolumn{2}{x{8.4cm}}{sqlite3 attach database 'test.db' as 'test';} \tn 
% Row Count 6 (+ 1)
% Row 5
\SetRowColor{white}
\mymulticolumn{2}{x{8.4cm}}{database main and temp reserved for primary dba nd temp database (temp objects)} \tn 
% Row Count 8 (+ 2)
% Row 6
\SetRowColor{LightBackground}
\mymulticolumn{2}{x{8.4cm}}{create table ... ; .tables  .schema table\_name} \tn 
% Row Count 9 (+ 1)
% Row 7
\SetRowColor{white}
\mymulticolumn{2}{x{8.4cm}}{insert into t (....) values(....)} \tn 
% Row Count 10 (+ 1)
% Row 8
\SetRowColor{LightBackground}
\mymulticolumn{2}{x{8.4cm}}{insert into .... select ....} \tn 
% Row Count 11 (+ 1)
% Row 9
\SetRowColor{white}
select ... from .... where ..... & .header on | .mode column|.width \#,\#,\#.... \tn 
% Row Count 13 (+ 2)
% Row 10
\SetRowColor{LightBackground}
\mymulticolumn{2}{x{8.4cm}}{operator: (+-*/\%) (== = != \textless{}\textgreater{} \textgreater{} \textless{} \textgreater{}= \textless{}= !\textless{} !\textgreater{}) ( and between exists in not in like GLOB  not or is null is is not || unque) ( \& I \textasciitilde{} \textless{}\textless{} \textgreater{}\textgreater{})} \tn 
% Row Count 16 (+ 3)
% Row 11
\SetRowColor{white}
\mymulticolumn{2}{x{8.4cm}}{expression (values + operators + functions)} \tn 
% Row Count 17 (+ 1)
% Row 12
\SetRowColor{LightBackground}
\mymulticolumn{2}{x{8.4cm}}{select current\_timestamp; select 3+2;} \tn 
% Row Count 18 (+ 1)
% Row 13
\SetRowColor{white}
\mymulticolumn{2}{x{8.4cm}}{LIKE \% \_} \tn 
% Row Count 19 (+ 1)
% Row 14
\SetRowColor{LightBackground}
GLOBE ? {[}1{]} * {[}0,n{]} & GLOBE '{\emph{200}}';GLOBE '2??3' \tn 
% Row Count 21 (+ 2)
% Row 15
\SetRowColor{white}
\mymulticolumn{2}{x{8.4cm}}{LIMIT {[}no of row{]} offset {[}row num{]}} \tn 
% Row Count 22 (+ 1)
\hhline{>{\arrayrulecolor{DarkBackground}}--}
\end{tabularx}
\par\addvspace{1.3em}

\begin{tabularx}{8.4cm}{x{3.84 cm} x{4.16 cm} }
\SetRowColor{DarkBackground}
\mymulticolumn{2}{x{8.4cm}}{\bf\textcolor{white}{advanced sqlite - pragma}}  \tn
% Row 0
\SetRowColor{LightBackground}
\mymulticolumn{2}{x{8.4cm}}{pragma pragma\_name=value; set environmental var and state flag} \tn 
% Row Count 2 (+ 2)
% Row 1
\SetRowColor{white}
pragma \seqsplit{database.auto\_vacuum=0} {[}1|2{]} & None, full or incremental  shrink file \tn 
% Row Count 4 (+ 2)
% Row 2
\SetRowColor{LightBackground}
\mymulticolumn{2}{x{8.4cm}}{cache\_size=pages; default 2000, minimal 10 pages} \tn 
% Row Count 5 (+ 1)
% Row 3
\SetRowColor{white}
\mymulticolumn{2}{x{8.4cm}}{PRAGMA case\_sensitive\_like = {[}true|false{]}; for LIKE operator,default false} \tn 
% Row Count 7 (+ 2)
% Row 4
\SetRowColor{LightBackground}
\mymulticolumn{2}{x{8.4cm}}{PRAGMA count\_changes = {[}true|false{]}; DML return, default false} \tn 
% Row Count 9 (+ 2)
% Row 5
\SetRowColor{white}
\mymulticolumn{2}{x{8.4cm}}{PRAGMA database\_list;} \tn 
% Row Count 10 (+ 1)
% Row 6
\SetRowColor{LightBackground}
\mymulticolumn{2}{x{8.4cm}}{PRAGMA encoding = format; control string encoding UTF-8, UTF-16le, or UTF-16be} \tn 
% Row Count 12 (+ 2)
% Row 7
\SetRowColor{white}
\mymulticolumn{2}{x{8.4cm}}{PRAGMA {[}database.{]}freelist\_count;} \tn 
% Row Count 13 (+ 1)
% Row 8
\SetRowColor{LightBackground}
\mymulticolumn{2}{x{8.4cm}}{PRAGMA database.journal\_mode \seqsplit{=delete|truncate|persist|memory|off;}} \tn 
% Row Count 15 (+ 2)
% Row 9
\SetRowColor{white}
\mymulticolumn{2}{x{8.4cm}}{PRAGMA {[}database.{]}max\_page\_count = max\_page;The default value is 1,073,741,823 which is one giga-page, default 1K/peage, total 1TB size} \tn 
% Row Count 18 (+ 3)
% Row 10
\SetRowColor{LightBackground}
\mymulticolumn{2}{x{8.4cm}}{PRAGMA {[}database.{]}page\_count;} \tn 
% Row Count 19 (+ 1)
% Row 11
\SetRowColor{white}
\mymulticolumn{2}{x{8.4cm}}{PRAGMA {[}database.{]}page\_size = bytes; 0.5k\textasciitilde{}32k, set the page size and then vacuum it immediately to change the database size} \tn 
% Row Count 22 (+ 3)
% Row 12
\SetRowColor{LightBackground}
\mymulticolumn{2}{x{8.4cm}}{PRAGMA parser\_trace = {[}true|false{]}; default false} \tn 
% Row Count 23 (+ 1)
% Row 13
\SetRowColor{white}
\mymulticolumn{2}{x{8.4cm}}{PRAGMA {[}database.{]}schema\_version = number; keep DDL count} \tn 
% Row Count 25 (+ 2)
% Row 14
\SetRowColor{LightBackground}
\mymulticolumn{2}{x{8.4cm}}{PRAGMA database.secure\_delete = {[}true|false{]}; default false} \tn 
% Row Count 27 (+ 2)
% Row 15
\SetRowColor{white}
\mymulticolumn{2}{x{8.4cm}}{PRAGMA sql\_trace = {[}true|false{]};} \tn 
% Row Count 28 (+ 1)
% Row 16
\SetRowColor{LightBackground}
PRAGMA {[}database.{]}synchronous = & 0-OFF 1 noreml 2-full \tn 
% Row Count 30 (+ 2)
\end{tabularx}
\par\addvspace{1.3em}

\vfill
\columnbreak
\begin{tabularx}{8.4cm}{x{3.84 cm} x{4.16 cm} }
\SetRowColor{DarkBackground}
\mymulticolumn{2}{x{8.4cm}}{\bf\textcolor{white}{advanced sqlite - pragma (cont)}}  \tn
% Row 17
\SetRowColor{LightBackground}
PRAGMA temp\_store = mode; & 0 default|file, 1 file, 2 memory \tn 
% Row Count 2 (+ 2)
% Row 18
\SetRowColor{white}
\mymulticolumn{2}{x{8.4cm}}{PRAGMA temp\_store\_directory = 'directory\_path';} \tn 
% Row Count 3 (+ 1)
% Row 19
\SetRowColor{LightBackground}
\mymulticolumn{2}{x{8.4cm}}{PRAGMA {[}database.{]}user\_version = number;} \tn 
% Row Count 4 (+ 1)
% Row 20
\SetRowColor{white}
\mymulticolumn{2}{x{8.4cm}}{PRAGMA writable\_schema = {[}true|false{]}; system table sqlite\_ will be able modified} \tn 
% Row Count 6 (+ 2)
\hhline{>{\arrayrulecolor{DarkBackground}}--}
\end{tabularx}
\par\addvspace{1.3em}

\begin{tabularx}{8.4cm}{p{0.8 cm} p{0.8 cm} }
\SetRowColor{DarkBackground}
\mymulticolumn{2}{x{8.4cm}}{\bf\textcolor{white}{advanced sqlite}}  \tn
% Row 0
\SetRowColor{LightBackground}
\mymulticolumn{2}{x{8.4cm}}{not null, default, unique,primary,CHECK(SALARY \textgreater{} 0)} \tn 
% Row Count 2 (+ 2)
% Row 1
\SetRowColor{white}
\mymulticolumn{2}{x{8.4cm}}{Crosse join|inner join |left outer join} \tn 
% Row Count 3 (+ 1)
% Row 2
\SetRowColor{LightBackground}
\mymulticolumn{2}{x{8.4cm}}{Union |union all (duplicates)} \tn 
% Row Count 4 (+ 1)
% Row 3
\SetRowColor{white}
\mymulticolumn{2}{x{8.4cm}}{CREATE TRIGGER trigger\_name {[}BEFORE|AFTER{]} UPDATE OF column\_name  ON table\_name BEGIN ... END;} \tn 
% Row Count 6 (+ 2)
% Row 4
\SetRowColor{LightBackground}
\mymulticolumn{2}{x{8.4cm}}{DML; FOR EACH ROW; old. new.;when;before after; on table\_name; raise()} \tn 
% Row Count 8 (+ 2)
% Row 5
\SetRowColor{white}
\mymulticolumn{2}{x{8.4cm}}{SELECT name FROM sqlite\_master WHERE type = 'trigger';} \tn 
% Row Count 10 (+ 2)
% Row 6
\SetRowColor{LightBackground}
\mymulticolumn{2}{x{8.4cm}}{DROP TRIGGER trigger\_name;} \tn 
% Row Count 11 (+ 1)
% Row 7
\SetRowColor{white}
\mymulticolumn{2}{x{8.4cm}}{CREATE INDEX index\_name ON table\_name;} \tn 
% Row Count 12 (+ 1)
% Row 8
\SetRowColor{LightBackground}
\mymulticolumn{2}{x{8.4cm}}{CREATE UNIQUE INDEX index\_name on table\_name (column\_name\_list);} \tn 
% Row Count 14 (+ 2)
% Row 9
\SetRowColor{white}
\mymulticolumn{2}{x{8.4cm}}{implicit index on primary key and unique key} \tn 
% Row Count 15 (+ 1)
% Row 10
\SetRowColor{LightBackground}
\mymulticolumn{2}{x{8.4cm}}{DROP INDEX salary\_index;} \tn 
% Row Count 16 (+ 1)
% Row 11
\SetRowColor{white}
\mymulticolumn{2}{x{8.4cm}}{avoid indexes ( small table,DML freq,a lot null| freq manipulate{]}} \tn 
% Row Count 18 (+ 2)
% Row 12
\SetRowColor{LightBackground}
\mymulticolumn{2}{x{8.4cm}}{SELECT|DELETE|UPDATE column1, column2... INDEXED BY (index\_name) table\_name WHERE (CONDITION);} \tn 
% Row Count 20 (+ 2)
% Row 13
\SetRowColor{white}
\mymulticolumn{2}{x{8.4cm}}{ACID: Atomicity Consistency Isolation Durability} \tn 
% Row Count 21 (+ 1)
% Row 14
\SetRowColor{LightBackground}
\mymulticolumn{2}{x{8.4cm}}{BEGIN TRANSACTION} \tn 
% Row Count 22 (+ 1)
% Row 15
\SetRowColor{white}
\mymulticolumn{2}{x{8.4cm}}{COMMIT} \tn 
% Row Count 23 (+ 1)
% Row 16
\SetRowColor{LightBackground}
\mymulticolumn{2}{x{8.4cm}}{ROLLBACK} \tn 
% Row Count 24 (+ 1)
% Row 17
\SetRowColor{white}
\mymulticolumn{2}{x{8.4cm}}{integer autoincement} \tn 
% Row Count 25 (+ 1)
\hhline{>{\arrayrulecolor{DarkBackground}}--}
\end{tabularx}
\par\addvspace{1.3em}

\begin{tabularx}{8.4cm}{X}
\SetRowColor{DarkBackground}
\mymulticolumn{1}{x{8.4cm}}{\bf\textcolor{white}{advanced sqlite - injection}}  \tn
\SetRowColor{LightBackground}
\mymulticolumn{1}{x{8.4cm}}{\$name = "Qadir'; DELETE FROM users;"; \newline @\$db-\textgreater{}query("SELECT {\emph{ FROM users WHERE username = '\{\$name\}'"); \newline  \newline if (preg\_match("/\textasciicircum{}\textbackslash{}w\{8,20\}\$/", \$\_GET{[}'username'{]}, \$matches))\{ \newline    \$db = new \seqsplit{SQLiteDatabase('filename');} \newline    \$result = @\$db-\textgreater{}query("SELECT }} FROM users WHERE username = \$matches{[}0{]}"); \newline \} else \{ \newline    echo "username not accepted"; \newline \} \newline  \newline if \seqsplit{(get\_magic\_quotes\_gpc())} \{ \newline    \$name = \seqsplit{sqlite\_escape\_string(\$name);} \newline \} \newline \$result = @\$db-\textgreater{}query("SELECT * FROM users WHERE username = '\{\$name\}'");} \tn 
\hhline{>{\arrayrulecolor{DarkBackground}}-}
\end{tabularx}
\par\addvspace{1.3em}

\begin{tabularx}{8.4cm}{X}
\SetRowColor{DarkBackground}
\mymulticolumn{1}{x{8.4cm}}{\bf\textcolor{white}{explain, vacuum}}  \tn
\SetRowColor{LightBackground}
\mymulticolumn{1}{x{8.4cm}}{EXPLAIN  QUERY PLAN {[}SQLite Query{]} \newline  \newline  \newline 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 \newline  \newline \$sqlite3 database\_name "VACUUM;" \newline sqlite\textgreater{} VACUUM; \newline sqlite\textgreater{} VACUUM table\_name; \newline  \newline sqlite\textgreater{} PRAGMA auto\_vacuum = NONE; -{}- 0 means disable auto vacuum \newline sqlite\textgreater{} PRAGMA auto\_vacuum = FULL; -{}- 1 means enable full auto vacuum \newline sqlite\textgreater{} PRAGMA auto\_vacuum = INCREMENTAL; -{}- 2 means enable incremental vacuum \newline  \newline \$sqlite3 database\_name "PRAGMA auto\_vacuum;"} \tn 
\hhline{>{\arrayrulecolor{DarkBackground}}-}
\end{tabularx}
\par\addvspace{1.3em}

\begin{tabularx}{8.4cm}{x{4.48 cm} x{3.52 cm} }
\SetRowColor{DarkBackground}
\mymulticolumn{2}{x{8.4cm}}{\bf\textcolor{white}{date times}}  \tn
% Row 0
\SetRowColor{LightBackground}
date(timestring, modifiers...) & YYYY-MM-DD \tn 
% Row Count 2 (+ 2)
% Row 1
\SetRowColor{white}
time(timestring, modifiers...) & HH:MM:SS \tn 
% Row Count 4 (+ 2)
% Row 2
\SetRowColor{LightBackground}
\mymulticolumn{2}{x{8.4cm}}{datetime(timestring, modifiers...)} \tn 
% Row Count 5 (+ 1)
% Row 3
\SetRowColor{white}
\mymulticolumn{2}{x{8.4cm}}{julianday(timestring, modifiers...)} \tn 
% Row Count 6 (+ 1)
% Row 4
\SetRowColor{LightBackground}
\mymulticolumn{2}{x{8.4cm}}{strftime(timestring, modifiers...)} \tn 
% Row Count 7 (+ 1)
% Row 5
\SetRowColor{white}
\mymulticolumn{2}{x{8.4cm}}{YYYY-MM-DD HH:MM:SS.SSS  now} \tn 
% Row Count 8 (+ 1)
% Row 6
\SetRowColor{LightBackground}
\mymulticolumn{2}{x{8.4cm}}{\%d  \%f  \%H \%j \%J \%m \%M \%s \%S \%w \%W \%Y \%\%} \tn 
% Row Count 9 (+ 1)
% Row 7
\SetRowColor{white}
SELECT date('now'); & 2013-05-07 \tn 
% Row Count 10 (+ 1)
% Row 8
\SetRowColor{LightBackground}
SELECT date('now','start of month','+1 month','-1 day'); & 2013-05-31 \tn 
% Row Count 13 (+ 3)
% Row 9
\SetRowColor{white}
SELECT datetime(1092941466, 'unixepoch'); & 2004-08-19 18:51:06 \tn 
% Row Count 15 (+ 2)
% Row 10
\SetRowColor{LightBackground}
SELECT datetime(1092941466, 'unixepoch', 'localtime'); & 2004-08-19 13:51:06 \tn 
% Row Count 18 (+ 3)
% Row 11
\SetRowColor{white}
SELECT strftime('\%s','now'); & 1393348134 \tn 
% Row Count 20 (+ 2)
% Row 12
\SetRowColor{LightBackground}
SELECT julianday('now') - \seqsplit{julianday('1776-07-04');} & 86798.7094695023 \tn 
% Row Count 23 (+ 3)
% Row 13
\SetRowColor{white}
SELECT strftime('\%s','now') - strftime('\%s','2004-01-01 02:34:56'); & 295001572 \tn 
% Row Count 27 (+ 4)
% Row 14
\SetRowColor{LightBackground}
SELECT date('now','start of year','+9 months','weekday 2'); & first duesday in oct  2013-10-01 \tn 
% Row Count 30 (+ 3)
\end{tabularx}
\par\addvspace{1.3em}

\vfill
\columnbreak
\begin{tabularx}{8.4cm}{x{4.48 cm} x{3.52 cm} }
\SetRowColor{DarkBackground}
\mymulticolumn{2}{x{8.4cm}}{\bf\textcolor{white}{date times (cont)}}  \tn
% Row 15
\SetRowColor{LightBackground}
SELECT time('12:00', 'localtime'); & 05:00:00 \tn 
% Row Count 2 (+ 2)
% Row 16
\SetRowColor{white}
SELECT time('12:00', 'utc'); & 19:00:00 \tn 
% Row Count 4 (+ 2)
\hhline{>{\arrayrulecolor{DarkBackground}}--}
\end{tabularx}
\par\addvspace{1.3em}

\begin{tabularx}{8.4cm}{p{1.04 cm} x{6.96 cm} }
\SetRowColor{DarkBackground}
\mymulticolumn{2}{x{8.4cm}}{\bf\textcolor{white}{useful functions}}  \tn
% Row 0
\SetRowColor{LightBackground}
\mymulticolumn{2}{x{8.4cm}}{count, max, min avg,sum, abs} \tn 
% Row Count 1 (+ 1)
% Row 1
\SetRowColor{white}
\seqsplit{random} & -9223372036854775808 and +9223372036854775807. \tn 
% Row Count 3 (+ 2)
% Row 2
\SetRowColor{LightBackground}
\mymulticolumn{2}{x{8.4cm}}{upper, lower,length,substr,instr, replace} \tn 
% Row Count 4 (+ 1)
% Row 3
\SetRowColor{white}
\mymulticolumn{2}{x{8.4cm}}{sqlite\_version()} \tn 
% Row Count 5 (+ 1)
\hhline{>{\arrayrulecolor{DarkBackground}}--}
\end{tabularx}
\par\addvspace{1.3em}

\begin{tabularx}{8.4cm}{X}
\SetRowColor{DarkBackground}
\mymulticolumn{1}{x{8.4cm}}{\bf\textcolor{white}{java and sqlite}}  \tn
\SetRowColor{LightBackground}
\mymulticolumn{1}{x{8.4cm}}{\seqsplit{https://www.tutorialspoint.com/sqlite/sqlite\_java.htm} \newline  \newline 1 download jar \newline 2 add to build path \newline  \newline import java.sql.*; \newline  \newline public class SQLiteJDBC \{ \newline   public static void main( String args{[}{]} ) \{ \newline       Connection c = null; \newline        \newline       try \{ \newline          \seqsplit{Class.forName("org.sqlite.JDBC");} \newline          c = \seqsplit{DriverManager.getConnection("jdbc:sqlite:test.db");} \newline       \} catch ( Exception e ) \{ \newline          System.err.println( e.getClass().getName() + ": " + e.getMessage() ); \newline          System.exit(0); \newline       \} \newline       \seqsplit{System.out.println("Opened} database successfully"); \newline    \} \newline \}} \tn 
\hhline{>{\arrayrulecolor{DarkBackground}}-}
\end{tabularx}
\par\addvspace{1.3em}


% That's all folks
\end{multicols*}

\end{document}