Changes between Initial Version and Version 1 of Docs/Prog/Manual/ApplicationLibraries/Database

Timestamp:

11/30/09 14:20:44 (15 years ago)

Author:

Don Wilson

Comment:

--

Docs/Prog/Manual/ApplicationLibraries/Database

@@ +1 @@
+[[TOC(heading=Table of Contents, Docs/Prog/*)]] [[PageOutline]]
+
+= Database =
+== About SQLite ==
+The Cardinal 825 comes fully equipped with an open source relational database known as SQLite. SQLite is a software library that implements a [http://sqlite.org/selfcontained.html self-contained], [http://sqlite.org/serverless.html serverless], [http://sqlite.org/zeroconf.html zero-configuration], [http://sqlite.org/transactional.html transactional] SQL database engine. SQLite is the [http://sqlite.org/mostdeployed.html most widely deployed] SQL database engine in the world. The source code for SQLite is in the [http://sqlite.org/copyright.html public domain]. SQLite is used by companies such as Adobe, Bloomberg, mozilla, symbian, Skype, and Google (Android OS).
+
+== SQLite Library (libsql) ==
+Cardinal supplies a library that allows usage of the SQLite database as well as a wrapper class to make generating queries and using the database as easy as possible. You can get the latest library from [wiki:/Libs/libsql this wiki] or by [wiki:/Docs/Prog/Manual/Subversion checking out the latest source] from the repository.
+
+== Using libsql ==
+Once you have the latest source you will need to include it in your project by modifying your project settings. Follow the steps listed below to get up and running.
+
+1. Right-click on your project and choose '''properties''' from the drop-down menu.
+
+  [[Image(screenshot.1.jpeg)]]
+
+2. Choose '''C/C++ Build''' and then '''Settings''' from the next screen. Click on '''Directories''' under '''Sourcery G++ C++ Compiler'''.
+
+  [[Image(screenshot.2.jpeg)]]
+
+3. Click on the add button and choose '''Workspace''' and the libsql project from the popup.
+
+  [[Image(screenshot.3.jpeg)]]
+
+4. Click '''OK'', '''OK''' and then observed the Include paths. It should have '''${workspace_loc/libsql}'''.'''''
+
+  [[Image(screenshot.4.jpeg)]]
+
+5. Next, click on '''Libraries''' under '''Sourcery G++ C++ Linker'''.
+
+  [[Image(screenshot.5.jpeg)]]
+
+6. In the '''Libraries (-l)''' pane choose the add button and enter '''pthread'''. Click the add button again and enter '''sql'''. If you're using the lib825 (and you probably are) that pane should look like the screenshot below.
+
+  [[Image(screenshot.6.jpeg)]]
+
+7. In the '''Library search path (-L)''' pane click the green add and choose '''Workspace'''. Pick libsql/Debug (or Release for release build) from the dropdown. If you do not have Debug you can either build the debug release of the libsql or manually enter /Debug after the libsql path. The final result should be identical to the image shown below.
+
+  [[Image(screenshot.7.jpeg)]]
+
+8. The '''Library search path (-L)''' should be identical to the image below (Release may say Debug if you're building a Debug build).
+
+  [[Image(screenshot.8.jpeg)]]
+
+9. Click '''OK''' to save your settings.
+
+== SqlBuilder Class ==
+A query builder class has been included with the SQLite library to facilitate generation of queries. Using the class is recommended to eliminate query bugs (and debugging time). Below you can find examples of using the class.
+
+{{{
+#!Lineno
+#!c
+
+// LibSQL Headers
+#include "SqlBuilder.h"
+
+int main(int ac, char **av) {
+    SqlBuilder Sql;
+        
+    // Example of a simple query
+    Sql.Select("name");
+    Sql.From("customers");
+    Sql.Print();
+    Sql.Reset();
+
+    // Example of using sorting
+    Sql.Select("id");
+    Sql.From("trucks");
+    Sql.Sort(SQL_ORDER_ASC,"name");
+    Sql.Print();
+    Sql.Reset();
+        
+    // Example of using multiple arguments with a where clause
+    Sql.Select("name","value");
+    Sql.From("configuration");
+    Sql.Where("name LIKE 'Thomas'", "id > 0");
+    Sql.Print();
+    Sql.Reset();
+        
+    // Most complicated example, multiple arguments, multiple sorts, and multiple tables with aliases
+    // This also does a simple inner join and then sorts by customer name, truck name, in descending order.
+    Sql.Select("c.id,t.id,c.name,t.name");
+    Sql.From("trucks", "t"); // trucks table aliased as t, so the id field would be t.id
+    Sql.From("customers", "c");
+    Sql.Where("c.id > 0", "t.id > 0", "t.name LIKE 'temp'", "c.name LIKE 'kyle'");
+    Sql.Sort(SQL_ORDER_DESC,"c.name","t.name");
+    Sql.Print();
+    Sql.Reset();
+
+    // Using values to bind to the where clause.
+    int nTmp = 5;
+    Sql.Select("test");
+    Sql.From("trucks");
+    Sql.Where("id =", nTmp);
+    Sql.Print();
+        
+    return 0;
+}
+}}}
+
+Output:
+
+{{{
+# ./sqltest
+SqlBuilder :: Query Generated = [SELECT name FROM customers]
+SqlBuilder :: Query Generated = [SELECT id FROM trucks ORDER BY name ASC]
+SqlBuilder :: Query Generated = [SELECT name, value FROM configuration WHERE name LIKE 'Thomas' id > 0]
+SqlBuilder :: Query Generated = [SELECT c.id,t.id,c.name,t.name FROM trucks AS t, customers AS c WHERE c.id > 0 AND t.id > 0 AND t.name LIKE 'temp' AND c.name LIKE 'kyle' ORDER BY c.name, t.name DESC]
+SqlBuilder :: Query Generated = [SELECT test FROM trucks WHERE id = 5]
+}}}
+
+== SQLite++ Class ==
+A C++ wrapper has been provided that sits on top of the SQLite C functions and aims to make implementation easier. See below for examples.
+
+{{{
+#!Lineno
+#!c
+database db;
+
+// Headers
+#include "sq3.hpp"
+
+int main(int ac,char **av) {
+    database db;
+        
+    // Executing a simple query
+    db.execute("SELECT * FROM trucks");
+        
+    // Iterating through rows of a result set
+    statement st(db, "SELECT * FROM trucks");
+    cursor cur(st.get_cursor());
+    
+    while(cur.step() == SQLITE_ROW) {
+        string name;
+        float accum;
+        
+        // Do stuff with data
+        cur.get(0,name);
+        cur.get(1,accum);
+    }
+    
+    // Doing multiple inserts using bind and transactions for speed
+    bool bError = false;
+    
+    transaction tr(db);
+    st.prepare("INSERT INTO trucks (name,accum) VALUES (?,?);");
+    
+    for(int i = 0; i < 10; i++) {
+        st.bind(1,"truck!");
+        st.bind(2,i*1000.0);
+        
+        if (st.execute() == SQLITE_ERROR) {
+                bError = true;
+        }
+    }
+    
+    if (bError) {
+        tr.rollback();
+    } else {
+        tr.commit();
+    }
+}
+}}}
+
+== Using SQLite++ with SqlBuilder ==
+You can easily combine SQLite++ and SqlBuilder to make database access easy.
+
+{{{
+#!Lineno
+#!c
+
+// LibSQL Headers
+#include "sq3.hpp"
+#include "SqlBuilder.h"
+
+int main(int ac, char **av) {
+    database db;
+    SqlBuilder sql;
+
+    // Identical to the select above
+    sql.Select("*");
+    sql.From("trucks");
+    db.execute(sql.GetQuery());
+
+    // Grab a specific row instead of all results
+    // Gets the name of truck id 10
+    sql.Select("name");
+    sql.From("trucks");
+    sql.Where("id =",10);
+    db.execute(sql.GetQuery());
+}
+}}}