13 |
import android.database.sqlite.SQLiteDatabase; |
import android.database.sqlite.SQLiteDatabase; |
14 |
import android.database.sqlite.SQLiteOpenHelper; |
import android.database.sqlite.SQLiteOpenHelper; |
15 |
import android.database.sqlite.SQLiteStatement; |
import android.database.sqlite.SQLiteStatement; |
16 |
|
import android.os.Handler; |
17 |
|
import android.os.Message; |
18 |
import android.util.Log; |
import android.util.Log; |
19 |
|
|
20 |
public class PeopleDatabase { |
public class PeopleDatabase { |
24 |
/** The name of the database file on the file system */ |
/** The name of the database file on the file system */ |
25 |
private static final String DATABASE_NAME = "People"; |
private static final String DATABASE_NAME = "People"; |
26 |
/** The version of the database that this class understands. */ |
/** The version of the database that this class understands. */ |
27 |
private static final int DATABASE_VERSION = 5; |
private static final int DATABASE_VERSION = 6; |
28 |
|
|
29 |
public PeopleDatabaseHelper(Context context) |
public PeopleDatabaseHelper(Context context) |
30 |
{ |
{ |
31 |
super(context, DATABASE_NAME, null, DATABASE_VERSION); |
super(context, DATABASE_NAME, null, DATABASE_VERSION); |
32 |
} |
} |
33 |
|
|
34 |
@Override |
@Override |
35 |
public void onCreate(SQLiteDatabase db) { |
public void onCreate(SQLiteDatabase db) { |
36 |
db.beginTransaction(); |
db.beginTransaction(); |
37 |
try { |
try { |
38 |
db.execSQL("CREATE TABLE people (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, inits TEXT, title TEXT, dept TEXT, company TEXT, phone TEXT, cell TEXT, email TEXT)"); |
db.execSQL("CREATE TABLE people (id INTEGER PRIMARY KEY AUTOINCREMENT, lowername TEXT, name TEXT, inits TEXT, title TEXT, dept TEXT, company TEXT, phone TEXT, cell TEXT, email TEXT)"); |
39 |
db.setTransactionSuccessful(); |
db.setTransactionSuccessful(); |
40 |
} catch (SQLException e) { |
} catch (SQLException e) { |
41 |
Log.e("Error creating tables and debug data", e.toString()); |
Log.e("Error creating tables and debug data", e.toString()); |
60 |
} |
} |
61 |
|
|
62 |
PeopleDatabaseHelper dbHelper; |
PeopleDatabaseHelper dbHelper; |
63 |
|
|
64 |
public PeopleDatabase(Context context) { |
public PeopleDatabase(Context context) { |
65 |
dbHelper = new PeopleDatabaseHelper(context); |
dbHelper = new PeopleDatabaseHelper(context); |
66 |
} |
} |
67 |
|
|
68 |
|
|
69 |
public Cursor getPeopleList(String search) { |
public Cursor getPeopleList(String search) { |
70 |
search = search.replace(' ', '%'); |
search = search.replace(' ', '%').toLowerCase(); |
71 |
search = "%" + search + "%"; |
search = "%" + search + "%"; |
72 |
|
|
73 |
String sql = "SELECT id AS _id, name, inits, company FROM people WHERE name LIKE ? ORDER BY name LIMIT 200"; |
String sql = "SELECT id AS _id, name, inits, company FROM people WHERE lowername LIKE ? ORDER BY name LIMIT 200"; |
74 |
SQLiteDatabase d = dbHelper.getReadableDatabase(); |
SQLiteDatabase d = dbHelper.getReadableDatabase(); |
75 |
|
|
76 |
try { |
try { |
137 |
} |
} |
138 |
} |
} |
139 |
|
|
140 |
private void loadData(String data) |
|
141 |
|
class DataLoader |
142 |
{ |
{ |
143 |
SQLiteDatabase db = dbHelper.getWritableDatabase(); |
SQLiteDatabase db; |
144 |
db.beginTransaction(); |
Handler handler; |
145 |
db.execSQL("DELETE FROM people"); |
int count = 0; |
146 |
StringTokenizer lines = new StringTokenizer( data, "|" ); |
int maxCount = 30000; |
147 |
|
SQLiteStatement stmt; |
148 |
SQLiteStatement stmt = db.compileStatement("INSERT INTO people (name,inits,title,dept,company,phone,cell,email) VALUES (?,?,?,?,?,?,?,?);"); |
|
149 |
while (lines.hasMoreTokens()) |
DataLoader(Handler hndl) { |
150 |
{ |
handler = hndl; |
151 |
String line = lines.nextToken(); |
} |
152 |
|
|
153 |
|
private void sendCount() { |
154 |
|
Message msg = Message.obtain(); |
155 |
|
msg.what = 1; |
156 |
|
msg.arg1 = count; |
157 |
|
handler.sendMessage(msg); |
158 |
|
} |
159 |
|
|
160 |
|
private void sendMaxCount() |
161 |
|
{ |
162 |
|
Message msg = Message.obtain(); |
163 |
|
msg.what = 0; |
164 |
|
msg.arg1 = maxCount; |
165 |
|
handler.sendMessage(msg); |
166 |
|
} |
167 |
|
|
168 |
|
private void insertLine(String line) |
169 |
|
{ |
170 |
try |
try |
171 |
{ |
{ |
172 |
StringTokenizer fields = new StringTokenizer(line, ";"); |
StringTokenizer fields = new StringTokenizer(line, ";"); |
173 |
stmt.bindString(1, fields.nextToken().trim() ); |
String name = fields.nextToken().trim(); |
174 |
stmt.bindString(2, fields.nextToken().trim() ); |
stmt.bindString(1, name.toLowerCase() ); |
175 |
|
stmt.bindString(2, name ); |
176 |
stmt.bindString(3, fields.nextToken().trim() ); |
stmt.bindString(3, fields.nextToken().trim() ); |
177 |
stmt.bindString(4, fields.nextToken().trim() ); |
stmt.bindString(4, fields.nextToken().trim() ); |
178 |
stmt.bindString(5, fields.nextToken().trim() ); |
stmt.bindString(5, fields.nextToken().trim() ); |
179 |
stmt.bindString(6, fields.nextToken().trim() ); |
stmt.bindString(6, fields.nextToken().trim() ); |
180 |
stmt.bindString(7, fields.nextToken().trim() ); |
stmt.bindString(7, fields.nextToken().trim() ); |
181 |
stmt.bindString(8, fields.nextToken().trim() ); |
stmt.bindString(8, fields.nextToken().trim() ); |
182 |
|
stmt.bindString(9, fields.nextToken().trim() ); |
183 |
stmt.execute(); |
stmt.execute(); |
184 |
} catch (Exception e) { |
} catch (Exception e) { |
185 |
Log.e("PeopleDataBase", "Token error: " + line, e); |
Log.e("PeopleDataBase", "Token error: " + line, e); |
186 |
} |
} |
|
|
|
187 |
} |
} |
|
stmt.close(); |
|
|
db.setTransactionSuccessful(); |
|
|
db.endTransaction(); |
|
|
db.close(); |
|
188 |
|
|
189 |
} |
public void loadData() throws Exception |
190 |
|
{ |
191 |
|
try |
192 |
|
{ |
193 |
|
db = dbHelper.getWritableDatabase(); |
194 |
|
db.beginTransaction(); |
195 |
|
db.execSQL("DELETE FROM people"); |
196 |
|
stmt = db.compileStatement("INSERT INTO people (lowername,name,inits,title,dept,company,phone,cell,email) VALUES (?,?,?,?,?,?,?,?,?);"); |
197 |
|
|
198 |
|
URL url = new URL("http://t-hoerup.dk/peopledata.txt"); |
199 |
|
|
200 |
|
URLConnection conn = url.openConnection(); |
201 |
|
conn.setConnectTimeout(1000); |
202 |
|
InputStream is = conn.getInputStream(); |
203 |
|
|
204 |
|
BufferedReader input = new BufferedReader( new InputStreamReader(is), 32768); |
205 |
|
String line; |
206 |
|
|
207 |
|
|
208 |
|
sendMaxCount(); |
209 |
|
while ( (line = input.readLine()) != null) { |
210 |
|
if (line.charAt(0) == '#') { //Lines starting with # are comments and should not be parsed by loaddata worker func. |
211 |
|
if (line.contains("count:")) { |
212 |
|
maxCount = Integer.parseInt(line.substring(line.indexOf(':')+1).trim()); |
213 |
|
sendMaxCount(); |
214 |
|
} |
215 |
|
Log.i("PeopleDB", "Count:"+maxCount); |
216 |
|
|
217 |
|
} else { |
218 |
|
count ++; |
219 |
|
if ((count % 100) == 0) |
220 |
|
sendCount(); |
221 |
|
insertLine(line); |
222 |
|
} |
223 |
|
} |
224 |
|
is.close(); |
225 |
|
db.setTransactionSuccessful(); |
226 |
|
|
227 |
public void loadData() throws Exception |
} finally { |
228 |
{ |
stmt.close(); |
229 |
URL url = new URL("http://t-hoerup.dk/peopledata.txt"); |
db.endTransaction(); |
230 |
|
db.close(); |
231 |
|
} |
232 |
|
|
233 |
|
} |
234 |
|
} |
235 |
|
|
|
URLConnection conn = url.openConnection(); |
|
|
conn.setConnectTimeout(1000); |
|
|
InputStream is = conn.getInputStream(); |
|
|
|
|
|
BufferedReader input = new BufferedReader( new InputStreamReader(is), 32768); |
|
|
StringBuilder sb = new StringBuilder(1024000); |
|
|
String line; |
|
|
|
|
|
while ( (line = input.readLine()) != null) { |
|
|
sb.append(line); |
|
|
sb.append("\n"); |
|
|
} |
|
|
|
|
|
is.close(); |
|
|
Log.i("PeopleDatabase", "Parsing data"); |
|
236 |
|
|
|
loadData(sb.toString()); |
|
237 |
|
|
238 |
Log.i("PeopleDatabase", "Parsing finished"); |
public void loadData(Handler hndl) throws Exception |
239 |
|
{ |
240 |
|
DataLoader dl = new DataLoader(hndl); |
241 |
|
dl.loadData(); |
242 |
} |
} |
243 |
|
|
244 |
public void close() |
public void close() |
245 |
{ |
{ |
246 |
dbHelper.close(); |
dbHelper.close(); |
247 |
} |
} |
248 |
|
|
249 |
|
|
250 |
} |
} |