· 9 years ago · Jan 23, 2017, 11:50 PM
1/**
2 * Sample React Native App with SQLite
3 * Demo the react-native-sqlite-storage
4 *
5 *
6 */
7'use strict';
8
9var React = require('react-native');
10var SQLite = require('react-native-sqlite-storage');
11SQLite.DEBUG(true);
12SQLite.enablePromise(true);
13SQLite.enablePromise(false);
14
15var {
16 AppRegistry,
17 StyleSheet,
18 Text,
19 View,
20 ListView
21} = React;
22
23var database_name = "Test.db";
24var database_version = "1.0";
25var database_displayname = "SQLite Test Database";
26var database_size = 200000;
27var db;
28
29var SQLiteDemo = React.createClass({
30 getInitialState(){
31 return {
32 progress: [],
33 dataSource: new ListView.DataSource({
34 rowHasChanged: (row1, row2) => row1 !== row2,
35 })
36 };
37 },
38
39 componentWillUnmount(){
40 this.closeDatabase();
41 },
42
43 errorCB(err) {
44 console.log("error: ",err);
45 this.state.progress.push("Error: "+ (err.message || err));
46 this.setState(this.state);
47 return false;
48 },
49
50 successCB() {
51 console.log("SQL executed ...");
52 },
53
54 openCB() {
55 this.state.progress.push("Database OPEN");
56 this.setState(this.state);
57 },
58
59 closeCB() {
60 this.state.progress.push("Database CLOSED");
61 this.setState(this.state);
62 },
63
64 deleteCB() {
65 console.log("Database DELETED");
66 this.state.progress.push("Database DELETED");
67 this.setState(this.state);
68 },
69
70 populateDatabase(db){
71 var that = this;
72 that.state.progress.push("Database integrity check");
73 that.setState(that.state);
74 db.executeSql('SELECT 1 FROM Version LIMIT 1', [],
75 function () {
76 that.state.progress.push("Database is ready ... executing query ...");
77 that.setState(that.state);
78 db.transaction(that.queryEmployees,that.errorCB,function() {
79 that.state.progress.push("Processing completed");
80 that.setState(that.state);
81 });
82 },
83 function (error) {
84 console.log("received version error:", error);
85 that.state.progress.push("Database not yet ready ... populating data");
86 that.setState(that.state);
87 db.transaction(that.populateDB, that.errorCB, function () {
88 that.state.progress.push("Database populated ... executing query ...");
89 that.setState(that.state);
90 db.transaction(that.queryEmployees,that.errorCB, function () {
91 console.log("Transaction is now finished");
92 that.state.progress.push("Processing completed");
93 that.setState(that.state);
94 that.closeDatabase();
95 });
96 });
97 });
98 },
99
100 populateDB(tx) {
101 this.state.progress.push("Executing DROP stmts");
102 this.setState(this.state);
103
104 tx.executeSql('DROP TABLE IF EXISTS Employees;');
105 tx.executeSql('DROP TABLE IF EXISTS Offices;');
106 tx.executeSql('DROP TABLE IF EXISTS Departments;');
107
108 this.state.progress.push("Executing CREATE stmts");
109 this.setState(this.state);
110
111 tx.executeSql('CREATE TABLE IF NOT EXISTS Version( '
112 + 'version_id INTEGER PRIMARY KEY NOT NULL); ', [], this.successCB, this.errorCB);
113
114 tx.executeSql('CREATE TABLE IF NOT EXISTS Departments( '
115 + 'department_id INTEGER PRIMARY KEY NOT NULL, '
116 + 'name VARCHAR(30) ); ', [], this.successCB, this.errorCB);
117
118 tx.executeSql('CREATE TABLE IF NOT EXISTS Offices( '
119 + 'office_id INTEGER PRIMARY KEY NOT NULL, '
120 + 'name VARCHAR(20), '
121 + 'longtitude FLOAT, '
122 + 'latitude FLOAT ) ; ', [], this.successCB, this.errorCB);
123
124 tx.executeSql('CREATE TABLE IF NOT EXISTS Employees( '
125 + 'employe_id INTEGER PRIMARY KEY NOT NULL, '
126 + 'name VARCHAR(55), '
127 + 'office INTEGER, '
128 + 'department INTEGER, '
129 + 'FOREIGN KEY ( office ) REFERENCES Offices ( office_id ) '
130 + 'FOREIGN KEY ( department ) REFERENCES Departments ( department_id ));', []);
131
132 this.state.progress.push("Executing INSERT stmts");
133 this.setState(this.state);
134
135 tx.executeSql('INSERT INTO Departments (name) VALUES ("Client Services");', []);
136 tx.executeSql('INSERT INTO Departments (name) VALUES ("Investor Services");', []);
137 tx.executeSql('INSERT INTO Departments (name) VALUES ("Shipping");', []);
138 tx.executeSql('INSERT INTO Departments (name) VALUES ("Direct Sales");', []);
139
140 tx.executeSql('INSERT INTO Offices (name, longtitude, latitude) VALUES ("Denver", 59.8, 34.);', []);
141 tx.executeSql('INSERT INTO Offices (name, longtitude, latitude) VALUES ("Warsaw", 15.7, 54.);', []);
142 tx.executeSql('INSERT INTO Offices (name, longtitude, latitude) VALUES ("Berlin", 35.3, 12.);', []);
143 tx.executeSql('INSERT INTO Offices (name, longtitude, latitude) VALUES ("Paris", 10.7, 14.);', []);
144
145 tx.executeSql('INSERT INTO Employees (name, office, department) VALUES ("Sylvester Stallone", 2, 4);', []);
146 tx.executeSql('INSERT INTO Employees (name, office, department) VALUES ("Elvis Presley", 2, 4);', []);
147 tx.executeSql('INSERT INTO Employees (name, office, department) VALUES ("Leslie Nelson", 3, 4);', []);
148 tx.executeSql('INSERT INTO Employees (name, office, department) VALUES ("Fidel Castro", 3, 3);', []);
149 tx.executeSql('INSERT INTO Employees (name, office, department) VALUES ("Bill Clinton", 1, 3);', []);
150 tx.executeSql('INSERT INTO Employees (name, office, department) VALUES ("Margaret Thatcher", 1, 3);', []);
151 tx.executeSql('INSERT INTO Employees (name, office, department) VALUES ("Donald Trump", 1, 3);', []);
152 tx.executeSql('INSERT INTO Employees (name, office, department) VALUES ("Dr DRE", 2, 2);', []);
153 tx.executeSql('INSERT INTO Employees (name, office, department) VALUES ("Samantha Fox", 2, 1);', []);
154 console.log("all config SQL done");
155 },
156
157 queryEmployees(tx) {
158 console.log("Executing sql...");
159 tx.executeSql('SELECT a.name, b.name as deptName FROM Employees a, Departments b WHERE a.department = b.department_id and a.department=?', [3],
160 this.queryEmployeesSuccess,this.errorCB);
161 //tx.executeSql('SELECT a.name, from TEST', [],() => {},this.errorCB);
162 },
163
164 queryEmployeesSuccess(tx,results) {
165 this.state.progress.push("Query completed");
166 this.setState(this.state);
167 var len = results.rows.length;
168 for (let i = 0; i < len; i++) {
169 let row = results.rows.item(i);
170 this.state.progress.push(`Empl Name: ${row.name}, Dept Name: ${row.deptName}`);
171 }
172 this.setState(this.state);
173 },
174
175 loadAndQueryDB(){
176 this.state.progress.push("Opening database ...");
177 this.setState(this.state);
178 db = SQLite.openDatabase(database_name, database_version, database_displayname, database_size, this.openCB, this.errorCB);
179 this.populateDatabase(db);
180 },
181
182 deleteDatabase(){
183 this.state.progress = ["Deleting database"];
184 this.setState(this.state);
185 SQLite.deleteDatabase(database_name, this.deleteCB, this.errorCB);
186 },
187
188 closeDatabase(){
189 var that = this;
190 if (db) {
191 console.log("Closing database ...");
192 that.state.progress.push("Closing database");
193 that.setState(that.state);
194 db.close(that.closeCB,that.errorCB);
195 } else {
196 that.state.progress.push("Database was not OPENED");
197 that.setState(that.state);
198 }
199 },
200
201 runDemo(){
202 this.state.progress = ["Starting SQLite Demo"];
203 this.setState(this.state);
204 this.loadAndQueryDB();
205 },
206
207 renderProgressEntry(entry){
208 return (<View style={listStyles.li}>
209 <View>
210 <Text style={listStyles.title}>{entry}</Text>
211 </View>
212 </View>)
213 },
214
215 render(){
216 var ds = new ListView.DataSource({rowHasChanged: (r1, r2) => r1 !== r2});
217 return (<View style={styles.mainContainer}>
218 <View style={styles.toolbar}>
219 <Text style={styles.toolbarButton} onPress={this.runDemo}>
220 Run Demo
221 </Text>
222 <Text style={styles.toolbarButton} onPress={this.closeDatabase}>
223 Close DB
224 </Text>
225 <Text style={styles.toolbarButton} onPress={this.deleteDatabase}>
226 Delete DB
227 </Text>
228 </View>
229 <ListView
230 dataSource={ds.cloneWithRows(this.state.progress)}
231 renderRow={this.renderProgressEntry}
232 style={listStyles.liContainer}/>
233 </View>);
234 }
235});
236
237var listStyles = StyleSheet.create({
238 li: {
239 borderBottomColor: '#c8c7cc',
240 borderBottomWidth: 0.5,
241 paddingTop: 15,
242 paddingRight: 15,
243 paddingBottom: 15,
244 },
245 liContainer: {
246 backgroundColor: '#fff',
247 flex: 1,
248 paddingLeft: 15,
249 },
250 liIndent: {
251 flex: 1,
252 },
253 liText: {
254 color: '#333',
255 fontSize: 17,
256 fontWeight: '400',
257 marginBottom: -3.5,
258 marginTop: -3.5,
259 },
260});
261
262var styles = StyleSheet.create({
263 container: {
264 flex: 1,
265 justifyContent: 'center',
266 alignItems: 'center',
267 backgroundColor: '#F5FCFF',
268 },
269 welcome: {
270 fontSize: 20,
271 textAlign: 'center',
272 margin: 10,
273 },
274 instructions: {
275 textAlign: 'center',
276 color: '#333333',
277 marginBottom: 5,
278 },
279 toolbar: {
280 backgroundColor: '#51c04d',
281 paddingTop: 30,
282 paddingBottom: 10,
283 flexDirection: 'row'
284 },
285 toolbarButton: {
286 color: 'blue',
287 textAlign: 'center',
288 flex: 1
289 },
290 mainContainer: {
291 flex: 1
292 }
293});
294
295AppRegistry.registerComponent('SQLiteDemo', () => SQLiteDemo);