1 |
package com.grundfos.android.people;
|
2 |
|
3 |
import java.io.BufferedReader;
|
4 |
import java.io.InputStream;
|
5 |
import java.io.InputStreamReader;
|
6 |
import java.net.URL;
|
7 |
import java.net.URLConnection;
|
8 |
import java.util.StringTokenizer;
|
9 |
|
10 |
import android.content.Context;
|
11 |
import android.database.Cursor;
|
12 |
import android.database.SQLException;
|
13 |
import android.database.sqlite.SQLiteDatabase;
|
14 |
import android.database.sqlite.SQLiteOpenHelper;
|
15 |
import android.database.sqlite.SQLiteStatement;
|
16 |
import android.os.Handler;
|
17 |
import android.os.Message;
|
18 |
import android.util.Log;
|
19 |
|
20 |
public class PeopleDatabase {
|
21 |
|
22 |
class PeopleDatabaseHelper extends SQLiteOpenHelper
|
23 |
{
|
24 |
/** The name of the database file on the file system */
|
25 |
private static final String DATABASE_NAME = "People";
|
26 |
/** The version of the database that this class understands. */
|
27 |
private static final int DATABASE_VERSION = 6;
|
28 |
|
29 |
public PeopleDatabaseHelper(Context context)
|
30 |
{
|
31 |
super(context, DATABASE_NAME, null, DATABASE_VERSION);
|
32 |
}
|
33 |
|
34 |
@Override
|
35 |
public void onCreate(SQLiteDatabase db) {
|
36 |
db.beginTransaction();
|
37 |
try {
|
38 |
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();
|
40 |
} catch (SQLException e) {
|
41 |
Log.e("Error creating tables and debug data", e.toString());
|
42 |
} finally {
|
43 |
db.endTransaction();
|
44 |
}
|
45 |
}
|
46 |
|
47 |
@Override
|
48 |
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
|
49 |
db.beginTransaction();
|
50 |
try {
|
51 |
db.execSQL("DROP TABLE people");
|
52 |
db.setTransactionSuccessful();
|
53 |
} catch (SQLException e) {
|
54 |
Log.e("Error creating tables and debug data", e.toString());
|
55 |
} finally {
|
56 |
db.endTransaction();
|
57 |
}
|
58 |
onCreate(db);
|
59 |
}
|
60 |
}
|
61 |
|
62 |
PeopleDatabaseHelper dbHelper;
|
63 |
|
64 |
public PeopleDatabase(Context context) {
|
65 |
dbHelper = new PeopleDatabaseHelper(context);
|
66 |
}
|
67 |
|
68 |
|
69 |
public Cursor getPeopleList(String search) {
|
70 |
search = search.replace(' ', '%').toLowerCase();
|
71 |
search = "%" + search + "%";
|
72 |
|
73 |
String sql = "SELECT id AS _id, name, inits, company FROM people WHERE lowername LIKE ? ORDER BY name LIMIT 200";
|
74 |
SQLiteDatabase d = dbHelper.getReadableDatabase();
|
75 |
|
76 |
try {
|
77 |
String[] args = {search};
|
78 |
Cursor c = d.rawQuery(sql, args);
|
79 |
return c;
|
80 |
} catch (Exception e) {
|
81 |
Log.e("people", "-", e);
|
82 |
}
|
83 |
|
84 |
return null;
|
85 |
}
|
86 |
|
87 |
public PeopleBean getPeople(long id) {
|
88 |
String sql = "SELECT id, name, inits, title, dept, company,phone,cell,email FROM people WHERE id = " + id + "";
|
89 |
SQLiteDatabase d = dbHelper.getReadableDatabase();
|
90 |
|
91 |
try {
|
92 |
Cursor c = d.rawQuery(sql, null);
|
93 |
|
94 |
c.moveToNext();
|
95 |
PeopleBean people = new PeopleBean();
|
96 |
people.id = c.getInt(0);
|
97 |
people.name = c.getString(1);
|
98 |
people.inits = c.getString(2);
|
99 |
people.title = c.getString(3);
|
100 |
people.dept = c.getString(4);
|
101 |
people.company = c.getString(5);
|
102 |
people.phone = c.getString(6);
|
103 |
people.cell = c.getString(7);
|
104 |
people.email = c.getString(8);
|
105 |
|
106 |
c.close();
|
107 |
d.close();
|
108 |
return people;
|
109 |
} catch (Exception e) {
|
110 |
Log.e("people", "-", e);
|
111 |
}
|
112 |
|
113 |
return null;
|
114 |
}
|
115 |
|
116 |
/** Returns the number of Jobs */
|
117 |
public int getPeopleCount(){
|
118 |
|
119 |
Cursor c = null;
|
120 |
try {
|
121 |
SQLiteDatabase d = dbHelper.getReadableDatabase();
|
122 |
c = d.rawQuery("SELECT count(*) FROM people", null);
|
123 |
if (0 >= c.getCount()) { return 0; }
|
124 |
c.moveToFirst();
|
125 |
int count = c.getInt(0);
|
126 |
|
127 |
c.close();
|
128 |
d.close();
|
129 |
|
130 |
return count;
|
131 |
}
|
132 |
finally {
|
133 |
if (null != c) {
|
134 |
try { c.close(); }
|
135 |
catch (SQLException e) { }
|
136 |
}
|
137 |
}
|
138 |
}
|
139 |
|
140 |
|
141 |
class DataLoader
|
142 |
{
|
143 |
SQLiteDatabase db;
|
144 |
Handler handler;
|
145 |
int count = 0;
|
146 |
int maxCount = 30000;
|
147 |
SQLiteStatement stmt;
|
148 |
|
149 |
DataLoader(Handler hndl) {
|
150 |
handler = hndl;
|
151 |
}
|
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
|
171 |
{
|
172 |
StringTokenizer fields = new StringTokenizer(line, ";");
|
173 |
String name = fields.nextToken().trim();
|
174 |
stmt.bindString(1, name.toLowerCase() );
|
175 |
stmt.bindString(2, name );
|
176 |
stmt.bindString(3, fields.nextToken().trim() );
|
177 |
stmt.bindString(4, fields.nextToken().trim() );
|
178 |
stmt.bindString(5, fields.nextToken().trim() );
|
179 |
stmt.bindString(6, fields.nextToken().trim() );
|
180 |
stmt.bindString(7, fields.nextToken().trim() );
|
181 |
stmt.bindString(8, fields.nextToken().trim() );
|
182 |
stmt.bindString(9, fields.nextToken().trim() );
|
183 |
stmt.execute();
|
184 |
} catch (Exception e) {
|
185 |
Log.e("PeopleDataBase", "Token error: " + line, e);
|
186 |
}
|
187 |
}
|
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 |
} finally {
|
228 |
stmt.close();
|
229 |
db.endTransaction();
|
230 |
db.close();
|
231 |
}
|
232 |
|
233 |
}
|
234 |
}
|
235 |
|
236 |
|
237 |
|
238 |
public void loadData(Handler hndl) throws Exception
|
239 |
{
|
240 |
DataLoader dl = new DataLoader(hndl);
|
241 |
dl.loadData();
|
242 |
}
|
243 |
|
244 |
public void close()
|
245 |
{
|
246 |
dbHelper.close();
|
247 |
}
|
248 |
|
249 |
|
250 |
}
|