android/people/PeopleDatabase.java

package com.grundfos.android.people;

import java.io.BufferedReader;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.net.URL;
import java.net.URLConnection;
import java.util.StringTokenizer;

import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.database.sqlite.SQLiteStatement;
import android.os.Handler;
import android.os.Message;
import android.util.Log;

public class PeopleDatabase  {

        class PeopleDatabaseHelper extends SQLiteOpenHelper
        {
                /** The name of the database file on the file system */
                private static final String DATABASE_NAME = "People";
                /** The version of the database that this class understands. */
                private static final int DATABASE_VERSION = 6;

                public PeopleDatabaseHelper(Context context)
                {
                        super(context, DATABASE_NAME, null, DATABASE_VERSION);  
                }

                @Override
                public void onCreate(SQLiteDatabase db) {
                        db.beginTransaction();
                        try {
                                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)");
                                db.setTransactionSuccessful();
                        } catch (SQLException e) {
                                Log.e("Error creating tables and debug data", e.toString());
                        } finally {
                                db.endTransaction();
                        }
                }

                @Override
                public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
                        db.beginTransaction();
                        try {
                                db.execSQL("DROP TABLE people");
                                db.setTransactionSuccessful();
                        } catch (SQLException e) {
                                Log.e("Error creating tables and debug data", e.toString());
                        } finally {
                                db.endTransaction();
                        }
                        onCreate(db);
                }
        }

        PeopleDatabaseHelper dbHelper;

        public PeopleDatabase(Context context) {
                dbHelper = new PeopleDatabaseHelper(context);
        }


        public Cursor getPeopleList(String search) {
                search = search.replace(' ', '%').toLowerCase();                
                search = "%" + search + "%";

                String sql = "SELECT id AS _id, name, inits, company FROM people WHERE lowername LIKE ? ORDER BY name LIMIT 200";
                SQLiteDatabase d = dbHelper.getReadableDatabase();

                try {
                        String[] args = {search};
                        Cursor c = d.rawQuery(sql, args);
                        return c;
                } catch (Exception e) {
                        Log.e("people", "-", e);
                }

                return null;
        }

        public PeopleBean getPeople(long  id) {
                String sql = "SELECT id, name, inits, title, dept, company,phone,cell,email FROM people WHERE id = " + id + "";
                SQLiteDatabase d = dbHelper.getReadableDatabase();

                try {
                        Cursor c = d.rawQuery(sql, null);

                        c.moveToNext();
                        PeopleBean people = new PeopleBean();
                        people.id = c.getInt(0);
                        people.name = c.getString(1);
                        people.inits = c.getString(2);
                        people.title = c.getString(3);
                        people.dept = c.getString(4);
                        people.company = c.getString(5);
                        people.phone = c.getString(6);
                        people.cell = c.getString(7);
                        people.email = c.getString(8);

                        c.close();
                        d.close();
                        return people;
                } catch (Exception e) {
                        Log.e("people", "-", e);
                }

                return null;
        }

        /** Returns the number of Jobs */
        public int getPeopleCount(){

                Cursor c = null;
                try {
                        SQLiteDatabase d = dbHelper.getReadableDatabase(); 
                        c = d.rawQuery("SELECT count(*) FROM people", null);
                        if (0 >= c.getCount()) { return 0; }
                        c.moveToFirst();
                        int count = c.getInt(0);

                        c.close();
                        d.close();

                        return count;
                }
                finally {
                        if (null != c) {
                                try { c.close(); }
                                catch (SQLException e) { }
                        }
                }
        }


        class DataLoader
        {
                SQLiteDatabase db; 
                Handler handler;
                int count = 0;
                int maxCount = 30000;
                SQLiteStatement stmt;

                DataLoader(Handler hndl) {
                        handler = hndl;
                }

                private void sendCount() {
                        Message msg = Message.obtain();
                        msg.what = 1;
                        msg.arg1 = count;
                        handler.sendMessage(msg);
                }               
                
                private void sendMaxCount()
                {
                        Message msg = Message.obtain();
                        msg.what = 0;
                        msg.arg1 = maxCount;
                        handler.sendMessage(msg);                       
                }

                private void insertLine(String line)
                {
                        try
                        {
                                StringTokenizer fields = new StringTokenizer(line, ";");
                                String name = fields.nextToken().trim();
                                stmt.bindString(1, name.toLowerCase() );
                                stmt.bindString(2, name );
                                stmt.bindString(3, fields.nextToken().trim() );
                                stmt.bindString(4, fields.nextToken().trim() );
                                stmt.bindString(5, fields.nextToken().trim() );
                                stmt.bindString(6, fields.nextToken().trim() );
                                stmt.bindString(7, fields.nextToken().trim() );                         
                                stmt.bindString(8, fields.nextToken().trim() );
                                stmt.bindString(8, fields.nextToken().trim() );
                                stmt.execute();
                        } catch (Exception e) {
                                Log.e("PeopleDataBase", "Token error: " + line, e);
                        }
                }

                public void loadData() throws Exception
                {
                        try
                        {
                                db = dbHelper.getWritableDatabase();
                                db.beginTransaction();
                                db.execSQL("DELETE FROM people");                       
                                stmt = db.compileStatement("INSERT INTO people (lowername,name,inits,title,dept,company,phone,cell,email) VALUES (?,?,?,?,?,?,?,?,?);");

                                URL url = new URL("http://t-hoerup.dk/peopledata.txt");

                                URLConnection conn = url.openConnection();
                                conn.setConnectTimeout(1000);                   
                                InputStream is = conn.getInputStream();

                                BufferedReader input = new BufferedReader( new InputStreamReader(is), 32768);
                                String line;


                                sendMaxCount();
                                while ( (line = input.readLine()) != null) {
                                        if (line.charAt(0) == '#') { //Lines starting with # are comments and should not be parsed by loaddata worker func.
                                                if (line.contains("count:")) {
                                                        maxCount = Integer.parseInt(line.substring(line.indexOf(':')+1).trim());
                                                        sendMaxCount();
                                                }
                                                Log.i("PeopleDB", "Count:"+maxCount);

                                        } else {
                                                count ++;
                                                if ((count % 100) == 0)
                                                        sendCount();
                                                insertLine(line);
                                        }
                                }                       
                                is.close();
                                db.setTransactionSuccessful();

                        } finally {
                                stmt.close();
                                db.endTransaction();
                                db.close();
                        }

                }                       
        }


        public void loadData(Handler hndl) throws Exception
        {
                DataLoader dl = new DataLoader(hndl);
                dl.loadData();
        }

        public void close()
        {
                dbHelper.close();
        }


}
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(8, 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	}