· 9 years ago · Dec 01, 2016, 08:28 AM
1<!DOCTYPE HTML>
2<html>
3<head>
4 <title>Safari SQLite Hello World Example</title>
5 <script type="text/javascript" src="jquery-1.4.2.min.js"></script>
6 <script type="text/javascript">
7
8 // The first thing we want to do is create the local
9 // database (if it doesn't exist) or open the connection
10 // if it does exist. Let's define some options for our
11 // test database.
12 var databaseOptions = {
13 fileName: "sqlite_helloWorld",
14 version: "1.0",
15 displayName: "SQLite Hello World",
16 maxSize: 1024
17 };
18
19 // Now that we have our database properties defined, let's
20 // creaete or open our database, getting a reference to the
21 // generated connection.
22 //
23 // NOTE: This might throw errors, but we're not going to
24 // worry about that for this Hello World example.
25 var database = openDatabase(
26 databaseOptions.fileName,
27 databaseOptions.version,
28 databaseOptions.displayName,
29 databaseOptions.maxSize
30 );
31
32
33 // -------------------------------------------------- //
34 // -------------------------------------------------- //
35
36
37 // Now that we have the databse connection, let's create our
38 // first table if it doesn't exist. According to Safari, all
39 // queries must be part of a transaction. To execute a
40 // transaction, we have to call the transaction() function
41 // and pass it a callback that is, itself, passed a reference
42 // to the transaction object.
43 database.transaction(
44 function( transaction ){
45
46 // Create our girls table if it doesn't exist.
47 transaction.executeSql(
48 "CREATE TABLE IF NOT EXISTS girls (" +
49 "id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT," +
50 "name TEXT NOT NULL" +
51 ");"
52 );
53
54 }
55 );
56
57
58 // -------------------------------------------------- //
59 // -------------------------------------------------- //
60
61
62 // Now that we have our database table created, let's
63 // create some "service" functions that we can use to
64 // touch the girls (no, not in that way - perv).
65
66 // NOTE: Since SQLite database interactions are performed
67 // asynchronously by default (it seems), we have to provide
68 // callbacks to execute when the results are available.
69
70
71 // I save a girl.
72 var saveGirl = function( name, callback ){
73 // Insert a new girl.
74 database.transaction(
75 function( transaction ){
76
77 // Insert a new girl with the given values.
78 transaction.executeSql(
79 (
80 "INSERT INTO girls (" +
81 "name " +
82 " ) VALUES ( " +
83 "? " +
84 ");"
85 ),
86 [
87 name
88 ],
89 function( transaction, results ){
90 // Execute the success callback,
91 // passing back the newly created ID.
92 callback( results.insertId );
93 }
94 );
95
96 }
97 );
98 };
99
100
101 // I get all the girls.
102 var getGirls = function( callback ){
103 // Get all the girls.
104 database.transaction(
105 function( transaction ){
106
107 // Get all the girls in the table.
108 transaction.executeSql(
109 (
110 "SELECT " +
111 "* " +
112 "FROM " +
113 "girls " +
114 "ORDER BY " +
115 "name ASC"
116 ),
117 [],
118 function( transaction, results ){
119 // Return the girls results set.
120 callback( results );
121 }
122 );
123
124 }
125 );
126 };
127
128
129 // I delete all the girls.
130 var deleteGirls = function( callback ){
131 // Get all the girls.
132 database.transaction(
133 function( transaction ){
134
135 // Delete all the girls.
136 transaction.executeSql(
137 (
138 "DELETE FROM " +
139 "girls "
140 ),
141 [],
142 function(){
143 // Execute the success callback.
144 callback();
145 }
146 );
147
148 }
149 );
150 };
151
152
153 // -------------------------------------------------- //
154 // -------------------------------------------------- //
155
156
157 // When the DOM is ready, init the scripts.
158 $(function(){
159 // Get the form.
160 var form = $( "form" );
161
162 // Get the girl list.
163 var list = $( "#girls" );
164
165 // Get the Clear Girls link.
166 var clearGirls = $( "#clearGirls" );
167
168
169 // I refresh the girls list.
170 var refreshGirls = function( results ){
171 // Clear out the list of girls.
172 list.empty();
173
174 // Check to see if we have any results.
175 if (!results){
176 return;
177 }
178
179 // Loop over the current list of girls and add them
180 // to the visual list.
181 $.each(
182 results.rows,
183 function( rowIndex ){
184 var row = results.rows.item( rowIndex );
185
186 // Append the list item.
187 list.append( "<li>" + row.name + "</li>" );
188 }
189 );
190 };
191
192
193 // Bind the form to save the girl.
194 form.submit(
195 function( event ){
196 // Prevent the default submit.
197 event.preventDefault();
198
199 // Save the girl.
200 saveGirl(
201 form.find( "input.name" ).val(),
202 function(){
203 // Reset the form and focus the input.
204 form.find( "input.name" )
205 .val( "" )
206 .focus()
207 ;
208
209 // Refresh the girl list.
210 getGirls( refreshGirls );
211 }
212 );
213 }
214 );
215
216
217 // Bind to the clear girls link.
218 clearGirls.click(
219 function( event ){
220 // Prevent default click.
221 event.preventDefault();
222
223 // Clear the girls
224 deleteGirls( refreshGirls );
225 }
226 );
227
228
229 // Refresh the girls list - this will pull the persisted
230 // girl data out of the SQLite database and put it into
231 // the UL element.
232 getGirls( refreshGirls );
233 });
234
235 </script>
236</head>
237<body>
238
239 <h1>
240 Safari SQLite Hello World Example
241 </h1>
242
243 <form>
244 Name:
245 <input type="text" name="name" class="name" />
246 <input type="submit" value="Add Girl" />
247 </form>
248
249 <h2>
250 Girls
251 </h2>
252
253 <ul id="girls">
254 <!-- To be populated dynamically from SQLite. -->
255 </ul>
256
257 <p>
258 <a id="clearGirls" href="#">Clear Girls</a>!
259 </p>
260
261</body>
262</html>