git.iko.soy/Idris-dev
on a427f21 help sign in
Idris-dev/contribs/lib/SQLite3/sqlite3.tex
157 lines 7.5 kB history raw
1 \documentclass[11pt]{article}
2 \topmargin -1.5cm
3 \oddsidemargin -0.04cm
4 \evensidemargin -0.04cm
5 \textwidth 16.59cm
6 \textheight 21.94cm
7 %\pagestyle{empty}
8 \parskip 7.2pt
9 \parindent 0pt
11 \begin{document}
12 \section{Introduction}
13\label{Introduction}
14This document provides overview on how the Idris library for SQLite3 works. The code base is written on top of the
15SQLite3 C/C++ Interface.
16This library supports some features of the low-level library, implemented in a higher level basis.
17The library allows making use of queries as data types. The aim of this library is to avoid
18SQL injections which are the most common security issues.
19To avoid this we have defined a datatype which describes queries.
20The queries are written using these data types and parsed as quoted string
21while replacing values with \emph{indexed question mark} to be binded with the prepared statement.
22This prevents the user from preparing the queries inappropriately by not allowing to pass
23any types of values directly to the prepare function. However this is a design choice, the library
24does not prevent the user from passing a String directly to the prepare function.
26\section{Using the library}
27\label{Using the library}
28Simple examples have been provided in the \emph{testunit.idr} file.
29To open a database, use the open\_db function which takes the filename as a string.
30This function implements the sqlite3\_open function.
31This function returns a pointer to the database which can be used by other function
32to access the database object.
33Whether or not an error occurs when it is opened,
34resources should be released by passing it to close\_db when it is no longer required.
35A query can then be builts using the supported data types.
36The library supports limited number of queries at the moment. The supported sql commands are :
37\newline
39\textbf{SELECT,}
40\textbf{INSERT,}
41\textbf{UPDATE,}
42\textbf{CREATE}
44\subsection{Creating Queries}
45\label{Creating Queries}
47To create a query, use the evalSQL function which takes an empty list and the SQL query.
48The routine evaluates the expression recrusively by dividing it into smaller parts,
49filling up the list with the received values. For instance the following is a valid query:
51let sql = evalSQL [ ] ((SELECT ALL)(TBL ["tbl1"]) (MkCond (MkNULL (VCol "data"))))
52\newline
53\newline{This will be parsed as :}
54\b{“SELECT * FROM tbl1 WHERE data IS NULL “}
56The evaluator returns a string and a list of indexed values.
57The string part of the query could be parsed to prepare.
58\emph{evalSQL} parses the query as a string replacing values with \emph{?} and an index starting from one,
59storing them in the list given to it as an argument.
60For example:
63evalSQL [ ] (SELECT (Cols ["data"]) (SELECT (Cols["num","data"]) (TBL ["tbl1"])
64\newline (MkCond(Equals(VCol "num")(VInt 2))))Empty))
66is passed to prepare as :
68SELECT * FROM tbl1 WHERE data = ?1 AND num = ?2 OR num = ?3
70and returns the list as follows:
71\newline{(Just (1, VStr "data0")) ,(Just (2, VInt 1)) , (Just (3, VInt 2))}
73The values in the list are binded to the statement using bindMulti function.
74This function takes the statement pointer returned by prepare and the list returned by evalSQL
75and binds the values by calling the appropriate functions.
77The function exec\_db\_v2 executes the statement.
78Note that the other version of exec\_db implements the sqlite3\_exec() interface which is a wrapper
79around sqlite3\_prepare\_v2(), sqlite3\_step(), and sqlite3\_finalize(),
80without having to use a lot of code, though the use of this function is not recommended in this case.
82The functions finalize\_db and close\_db must be called to clean up the open resources.
83To print the result, the function toList\_v1 can be used which takes a DBPointer and returns
84the resulting table.
86\subsection{More Examples}
87\label{More Examples}
88Nested expressions are also supported. You could write queries such as :
90evalSQL [ ] (SELECT (Cols ["data"]) (SELECT (Cols["num","data"]) (TBL ["tbl1"]) (MkCond (Equals (VCol "num")(VInt 2)))) Empty)
92Parsed as :
94SELECT data FROM (SELECT num data FROM tbl1 WHERE num = ?1)
96Note that in this example, the inner query must include the same column as the outer one otherwise this query will not validate.
98\subsection{SQL Data Type}
99\label{SQL Data Type}
100The SQL Data Types describe the queries that are supported by the library.
101The implementation of these datatypes is similar to defining a grammar.
102Data type Value consists of Int, String and Float.
103The data type can be extended to further support queries.
104The evaulator function makes use of smaller function to evaulate parts of the query.
105For instance given a SELECT qeury, it calls \emph{evalSQL} to obtain the list of tables,
106and clauseString to evaluate the rest of the expression which could include a WHERE clause or could be Empty.
109\section{Evaluating Queries}
110\label{Evaluating Queries}
111The method used here is a divide and conquer manner which divides the query into smaller
112parts and deals with each part and puts the results together to return a String representation
113of the query and a list of values.
114However this could result to a slow type checking process since it needs to go through all
115the possibilities to type check. Maybe this can be improved?
117\subsection{evalSQL Function}
118\label{evalSQL Function}
119This is a recursive evaluator which makes a call to itself.
120An SQL could consist of a SELECT ALL or Columns followed by another SQL
121which could be a nested query or just the name of the table(s).
122It then call the appropriate functions to evaluate the query.
123It returns the well-quoted String expression and a list of indexed values which are passed to the bind function.
124\section{Pointers passed to Idris functions}
125\label{Pointers passed to Idris functions}
127The library uses FFI. In order to make the pointers passed to each function more explicit,
128we have defined them as data types. For instance open\_db takes a String and returns a DBPointer
129(Database Pointer) and prepare\_db
130takes a DBPointer and returns a StmtPtr(Statemenet Pointer).
131Error handling is done using the \emph {DB a} data type which
132is either \emph {IO String} indicating an error or \emph{ IO a}.
133To test the functions in main, you need to use runDB :
134\begin{center}
135\centering runDB : DB a $\rightarrow$ IO a
136\end{center}
137More examples on how to use the functions can be found in \emph{testunit.idr}
139\section{Using the C Functions}
140\label{Using the C Functions}
141The library includes some wrapper functions such as get\_table
142and exec\_db. Using these functions mixed with prepare, step or finalize
143inappropriately could cause segmentation faults.
144Perhaps these functions could be removed from the library if no longer required in the future
145since the main purpose of this library is to make use of data types.
146However these functionalities could be implemented internally without calling
147the C Interface wrapper functions.
149\section{Further improving the library}
150\label{Further improving the library}
151There are many features that could be added to this library. These include adding more useful SQL Commands,
152extending the SQL datatype to support more complex queries and possibly improving \emph{evalSQL}
153for a faster performance.
154Another useful thing to implement is the dot operator(.) which is the member access operator to yield
155the value of a field in a table. This enables comparing values in different tables and constructing
156more complex queries.
157\end{document}