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(9, 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	torben	228	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	torben	230	import android.os.Handler;
17			import android.os.Message;
18	torben	228	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	torben	234	private static final int DATABASE_VERSION = 6;
28	torben	228
29			public PeopleDatabaseHelper(Context context)
30			{
31			super(context, DATABASE_NAME, null, DATABASE_VERSION);
32			}
33	torben	230
34	torben	228	@Override
35			public void onCreate(SQLiteDatabase db) {
36			db.beginTransaction();
37			try {
38	torben	234	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	torben	228	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	torben	230
64	torben	228	public PeopleDatabase(Context context) {
65			dbHelper = new PeopleDatabaseHelper(context);
66			}
67
68
69			public Cursor getPeopleList(String search) {
70	torben	234	search = search.replace(' ', '%').toLowerCase();
71	torben	228	search = "%" + search + "%";
72
73	torben	234	String sql = "SELECT id AS _id, name, inits, company FROM people WHERE lowername LIKE ? ORDER BY name LIMIT 200";
74	torben	228	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	torben	230
141			class DataLoader
142	torben	228	{
143	torben	230	SQLiteDatabase db;
144			Handler handler;
145			int count = 0;
146			int maxCount = 30000;
147			SQLiteStatement stmt;
148	torben	228
149	torben	230	DataLoader(Handler hndl) {
150			handler = hndl;
151			}
152	torben	228
153	torben	230	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	torben	228	try
171			{
172			StringTokenizer fields = new StringTokenizer(line, ";");
173	torben	234	String name = fields.nextToken().trim();
174			stmt.bindString(1, name.toLowerCase() );
175			stmt.bindString(2, name );
176	torben	228	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	torben	235	stmt.bindString(9, fields.nextToken().trim() );
183	torben	228	stmt.execute();
184			} catch (Exception e) {
185			Log.e("PeopleDataBase", "Token error: " + line, e);
186			}
187			}
188
189	torben	230	public void loadData() throws Exception
190			{
191			try
192			{
193			db = dbHelper.getWritableDatabase();
194			db.beginTransaction();
195			db.execSQL("DELETE FROM people");
196	torben	234	stmt = db.compileStatement("INSERT INTO people (lowername,name,inits,title,dept,company,phone,cell,email) VALUES (?,?,?,?,?,?,?,?,?);");
197	torben	228
198	torben	230	URL url = new URL("http://t-hoerup.dk/peopledata.txt");
199	torben	228
200	torben	230	URLConnection conn = url.openConnection();
201			conn.setConnectTimeout(1000);
202			InputStream is = conn.getInputStream();
203	torben	228
204	torben	230	BufferedReader input = new BufferedReader( new InputStreamReader(is), 32768);
205			String line;
206	torben	228
207
208	torben	230	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	torben	234
227	torben	230	} finally {
228			stmt.close();
229			db.endTransaction();
230			db.close();
231			}
232
233			}
234	torben	228	}
235	torben	230
236
237
238			public void loadData(Handler hndl) throws Exception
239			{
240			DataLoader dl = new DataLoader(hndl);
241			dl.loadData();
242			}
243
244	torben	228	public void close()
245			{
246			dbHelper.close();
247			}
248
249	torben	230
250	torben	228	}