File: sqlite.html

package info (click to toggle)
sqlrelay 1%3A0.37.1-3.1
  • links: PTS
  • area: main
  • in suites: etch, etch-m68k
  • size: 13,084 kB
  • ctags: 6,691
  • sloc: cpp: 48,136; python: 10,118; ansic: 9,673; java: 9,195; php: 8,839; perl: 8,827; sh: 8,554; ruby: 8,516; tcl: 5,039; makefile: 3,665
file content (188 lines) | stat: -rw-r--r-- 9,709 bytes parent folder | download | duplicates (2) 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
 
<html>

<head>
<title>SQL Relay - Getting Started With SQLite</title>
<link rel="stylesheet" href="../css/styles.css">
</head>

<body>

<span class="heading1">Getting Started With SQLite</span><br>

<ul>
<li><a href="#installation">Installation</a></li>
<li><a href="#creating">Creating a Database</a></li>
<li><a href="#accessing">Accessing a Database</a></li>
<li><a href="#sqlrelay">Accessing a Database With SQL Relay</a></li>
</ul>

<a name="installation"></a>
<span class="heading1">Installation</span><br>

<p>I've successfully installed SQLite on Linux, FreeBSD, NetBSD, OpenBSD, SCO
OpenServer and Solaris.  On most platforms, I've had to compile it from 
source.  SQLite is available from the 
<a href="http://www.sqlite.org">SQLite home page</a>.  I usually give 
the <i>configure</i> script the <i>--prefix=/usr/local/sqlite</i> parameter so 
that SQLite will be installed entirely under /usr/local/sqlite and add 
/usr/local/sqlite/bin to my PATH environment variable and /usr/local/sqlite/lib
to my LD_LIBRARY_PATH environment variable.</p>

<p>On OpenBSD and NetBSD sqlite will build cleanly but will crash at run time.
To get it working, you have to edit the Makefile that the configure script 
generates, search for a line like:</p>

<blockquote>
<b>LIBREADLINE = -lreadline</b>
</blockquote>

<p>And add -lcurses to the end of it as such:</p>

<blockquote>
<b>LIBREADLINE = -lreadline -lcurses</b>
</blockquote>

<p>Compiling sqlite with this modification will make it work.</p>

<span class="heading2">RPM Based Linux</span><br>

<p>To install SQLite on an RPM-based Linux distribution like RedHat, 
Mandrake or TurboLinux, acquire the sqlite RPM from the
<a href="http://www.sqlite.org">SQLite home page</a> and install it
using <i>rpm -i</i>.</p>

<span class="heading2">Slackware Linux</span><br>

<p>The sqlite package is (or at least was once) available from 
<a href="http://www.infa.abo.fi/~patrik/slackpacks/">http://www.infa.abo.fi/~patrik/slackpacks/</a>.  You can install it using <i>installpkg</i>.</p>

<a name="creating"></a>
<span class="heading1">Creating a Database</span><br>

<p>In version 1.0.x of SQLite, a database is just a directory with table files 
in it.  In versions 2.x an 3.x, the database is a single file.  Unlike other
relation database systems, there is no daemon managing the database, the SQLite
API interprets queries and runs them against the file(s) directly. I usually
create a directory /usr/local/sqlite/var and create database directories or
files there as follows.  SQLite database users are equivalent to unix users.  
Database directory and file permissions dictate what permissions a user has on 
the database.</p>

<blockquote>
<b>mkdir -p /usr/local/sqlite/var</b><br>
<b>chown testuser /usr/local/sqlite/var</b><br>
<b>chmod 755 /usr/local/sqlite/var</b><br>
</blockquote>

<p>For SQLite versions 2.x and 3.x, it is only necessary to create the
directory, the database file will get created the first time a user tries to
access it.</p>

<p>For SQLite version 1.0.x, the following commands are necessary to create a 
database called <i>testdb</i> owned by the user <i>testuser</i>.</p>

<blockquote>
<b>mkdir -p /usr/local/sqlite/var/testdb</b><br>
<b>chown testuser /usr/local/sqlite/var/testdb</b><br>
<b>chmod 755 /usr/local/sqlite/var/testdb</b><br>
</blockquote>

<a name="accessing"></a>
<span class="heading1">Accessing a Database</span><br>

<p>To access an SQLite database, su to the appropriate user and run the
sqlite3 command line utility (for versions 1.0.x and 2.x the command line
utility is just called sqlite).</p>

<blockquote>
<b>su testuser</b><br>
<b>sqlite3 /usr/local/sqlite/var/testdb</b>
</blockquote>

<p>Once you're connected to the database, the sqlite client prompts you to
enter a query.  Queries may be split across multiple lines.  To run a query, 
end it with a semicolon.  To exit, type .exit</p>

<p>A sample sqlite session follows.</p>

<blockquote>
<PRE>
[testuser@localhost testuser]$ <b>sqlite3 /usr/local/sqlite/var/testdb</b>
SQLite version <FONT color=#ff00ff>3.0.2</FONT>
Enter <FONT color=#ff00ff>&quot;.help&quot;</FONT> for instructions
sqlite&gt; <B><FONT color=#a62828>create</FONT></B> <FONT color=#6959cf>table</FONT> testtable (
   ...&gt; col1 <B><FONT color=#288a51>char</FONT></B>(<FONT color=#ff00ff>40</FONT>),
   ...&gt; col2 <B><FONT color=#288a51>integer</FONT></B>
   ...&gt; );
sqlite&gt; .tables
testtable
sqlite&gt; <B><FONT color=#a62828>insert</FONT></B> <FONT color=#6959cf>into</FONT> testtable <FONT color=#6959cf>values</FONT> (<FONT color=#ff00ff>'hello'</FONT>,<FONT color=#ff00ff>50</FONT>);
sqlite&gt; <B><FONT color=#a62828>insert</FONT></B> <FONT color=#6959cf>into</FONT> testtable <FONT color=#6959cf>values</FONT> (<FONT color=#ff00ff>'hi'</FONT>,<FONT color=#ff00ff>60</FONT>);
sqlite&gt; <B><FONT color=#a62828>insert</FONT></B> <FONT color=#6959cf>into</FONT> testtable <FONT color=#6959cf>values</FONT> (<FONT color=#ff00ff>'bye'</FONT>,<FONT color=#ff00ff>70</FONT>);
sqlite&gt; <B><FONT color=#a62828>select</FONT></B> * <FONT color=#6959cf>from</FONT> testtable;
hi|<FONT color=#ff00ff>60</FONT>
hello|<FONT color=#ff00ff>50</FONT>
bye|<FONT color=#ff00ff>70</FONT>
sqlite&gt; <B><FONT color=#a62828>update</FONT></B> testtable <B><FONT color=#a62828>set</FONT></B> col2=<FONT color=#ff00ff>0</FONT> <FONT color=#6959cf>where</FONT> col1=<FONT color=#ff00ff>'hi'</FONT>;
sqlite&gt; <B><FONT color=#a62828>select</FONT></B> * <FONT color=#6959cf>from</FONT> testtable;
hi|<FONT color=#ff00ff>0</FONT>
hello|<FONT color=#ff00ff>50</FONT>
bye|<FONT color=#ff00ff>70</FONT>
sqlite&gt; <B><FONT color=#a62828>delete</FONT></B> <FONT color=#6959cf>from</FONT> testtable <FONT color=#6959cf>where</FONT> col2=<FONT color=#ff00ff>50</FONT>;
sqlite&gt; <B><FONT color=#a62828>select</FONT></B> * <FONT color=#6959cf>from</FONT> testtable;
hi|<FONT color=#ff00ff>0</FONT>
bye|<FONT color=#ff00ff>70</FONT>
sqlite&gt; <B><FONT color=#a62828>drop</FONT></B> <FONT color=#6959cf>table</FONT> testtable;
sqlite&gt; .exit
</PRE>
</blockquote>

<a name="sqlrelay"></a>
<span class="heading1">Accessing a Database With SQL Relay</span><br>

<p>Accessing SQLite from SQL Relay requires an instance entry in your 
<i>sqlrelay.conf</i> file for the database that you want 
to access.  Here is an example <i>sqlrelay.conf</i> which defines an SQL Relay 
instance called sqlitetest.  This instance connects to the 
<i>/usr/local/sqlite/var/testdb</i> database.  Note how the runasuser and 
runasgroup attributes of the instance tag are set to the owner of the 
database.</p>

<PRE>
<FONT color=#0000ff>&lt;?</FONT><B><FONT color=#288a51>xml version=</FONT></B><FONT color=#ff00ff>&quot;1.0&quot;</FONT><FONT color=#0000ff>?&gt;</FONT>
<FONT color=#008a8e>&lt;!</FONT><B><FONT color=#a62828>DOCTYPE</FONT></B> instances <B><FONT color=#a62828>SYSTEM</FONT></B> <FONT color=#ff00ff>&quot;sqlrelay.dtd&quot;</FONT><FONT color=#008a8e>&gt;</FONT>
<FONT color=#008a8e>&lt;instances&gt;</FONT>

        <FONT color=#008a8e>&lt;instance id=</FONT><FONT color=#ff00ff>&quot;sqlitetest&quot;</FONT><FONT color=#008a8e> port=</FONT><FONT color=#ff00ff>&quot;9000&quot;</FONT><FONT color=#008a8e> socket=</FONT><FONT color=#ff00ff>&quot;/tmp/sqlitetest.socket&quot;</FONT><FONT color=#008a8e> dbase=</FONT><FONT color=#ff00ff>&quot;sqlite&quot;</FONT><FONT color=#008a8e> connections=</FONT><FONT color=#ff00ff>&quot;3&quot;</FONT><FONT color=#008a8e> maxconnections=</FONT><FONT color=#ff00ff>&quot;5&quot;</FONT><FONT color=#008a8e> maxqueuelength=</FONT><FONT color=#ff00ff>&quot;0&quot;</FONT><FONT color=#008a8e> growby=</FONT><FONT color=#ff00ff>&quot;1&quot;</FONT><FONT color=#008a8e> ttl=</FONT><FONT color=#ff00ff>&quot;60&quot;</FONT><FONT color=#008a8e> endofsession=</FONT><FONT color=#ff00ff>&quot;commit&quot;</FONT><FONT color=#008a8e> sessiontimeout=</FONT><FONT color=#ff00ff>&quot;600&quot;</FONT><FONT color=#008a8e> runasuser=</FONT><FONT color=#ff00ff>&quot;testuser&quot;</FONT><FONT color=#008a8e> runasgroup=</FONT><FONT color=#ff00ff>&quot;testuser&quot;</FONT><FONT color=#008a8e> cursors=</FONT><FONT color=#ff00ff>&quot;5&quot;</FONT><FONT color=#008a8e> authtier=</FONT><FONT color=#ff00ff>&quot;listener&quot;</FONT><FONT color=#008a8e> handoff=</FONT><FONT color=#ff00ff>&quot;pass&quot;</FONT><FONT color=#008a8e>&gt;</FONT>
                <FONT color=#008a8e>&lt;users&gt;</FONT>
                        <FONT color=#008a8e>&lt;user user=</FONT><FONT color=#ff00ff>&quot;sqlitetest&quot;</FONT><FONT color=#008a8e> password=</FONT><FONT color=#ff00ff>&quot;sqlitetest&quot;</FONT><FONT color=#008a8e>/&gt;</FONT>
                <FONT color=#008a8e>&lt;/users&gt;</FONT>
                <FONT color=#008a8e>&lt;connections&gt;</FONT>
                        <FONT color=#008a8e>&lt;connection connectionid=</FONT><FONT color=#ff00ff>&quot;sqlitetest&quot;</FONT><FONT color=#008a8e> string=</FONT><FONT color=#ff00ff>&quot;db=/usr/local/sqlite/var/testdb&quot;</FONT><FONT color=#008a8e> metric=</FONT><FONT color=#ff00ff>&quot;1&quot;</FONT><FONT color=#008a8e>/&gt;</FONT>
                <FONT color=#008a8e>&lt;/connections&gt;</FONT>
        <FONT color=#008a8e>&lt;/instance&gt;</FONT>

<FONT color=#008a8e>&lt;/instances&gt;</FONT>
</PRE>

<p>Now you can start up this instance with the following command.</p>

<blockquote>
<b>sqlr-start -id sqlitetest</b>
</blockquote>

<p>To connect to the instance and run queries, use the following command.</p>

<blockquote>
<b>sqlrsh -id sqlitetest</b>
</blockquote>

<p>The following command shuts down the SQL Relay instance.</p>

<blockquote>
<b>sqlr-stop sqlitetest</b>
</blockquote>

</body>

</html>