12B4wRUp

1package com.bluej.movingbuddy;
2
3import java.util.ArrayList;
4import java.util.List;
5import android.content.ContentValues;
6import android.content.Context;
7import android.database.Cursor;
8import android.database.sqlite.SQLiteDatabase;
9import android.database.sqlite.SQLiteOpenHelper;
10import android.database.SQLException;
11import android.util.Log;
12
13public class MBDatabaseManager extends SQLiteOpenHelper {
14
15//Database Version
16private final static int DATABASE_VERSION = 1;
17
18//Database Name
19private final static String DATABASE_NAME = "dbMovingBuddy";
20
21//items and weights table name
22private final static String tblInW = "ItemsAndWeights";
23//items and weights table columns
24private final static String InWID = "ID";
25private final static String InWItem = "Item";
26private final static String InWDesc = "Description";
27private final static String InWWeightOne = "Weight1";
28private final static String InWWeightTwo = "Weight2";
29private final static String InWWeightThree = "Weight3";
30private final static String InWWeightAvg = "WeightAvg";
31
32//allowances table name
33private final static String tblAllowances = "Allowances";
34//allowances table columns
35private final static String AllowancesID = "ID";
36private final static String AllowancesRank = "Rank";
37private final static String AllowancesWithDep = "WithDep";
38private final static String AllowancesNoDep = "NoDep";
39
40//estimator table name
41private final static String tblEstimator = "Estimator";
42//estimator table columns
43private final static String EstimatorID = "ID";
44private final static String EstimatorRoom = "Room";
45private final static String EstimatorItem = "Item";
46private final static String EstimatorWeight = "Weight";
47
48//inventory table name
49private final static String tblInventory = "Inventory";
50//inventory table column
51private final static String InventoryID = "ID";
52private final static String InventoryItem = "Item";
53private final static String InventoryWeight = "Weight";
54private final static String InventoryValue = "Value";
55private final static String InventoryImage1 = "Image1";
56private final static String InventoryCondition = "Condition";
57private final static String InventoryImage2 = "Image2";
58private final static String InventoryNotes = "Notes";
59private final static String InventoryMovingInstructions = "MovingInstructions";
60
61//stunt table name
62private final static String TABLE_NAME = "database_table";
63//stunt table column names
64private final static String TABLE_ROW_ID = "id";
65private final static String TABLE_ROW_ONE = "table_row_one";
66private final static String TABLE_ROW_TWO = "table_row_two";
67
68public MBDatabaseManager(Context context) {
69    // TODO Auto-generated constructor stub
70    super(context, DATABASE_NAME, null, DATABASE_VERSION);
71}
72
73public void onCreate(SQLiteDatabase db) {
74    // TODO Auto-generated method stub
75    //This string is used to create the database. It should be changed to suit your needs.
76
77    //create items and weights table
78    String dbCreateItemsAndWeights = "create table " +
79    tblInW +
80    " (" +
81    InWID + " integer primary key autoincrement not null," +
82    InWItem + " text," +
83    InWDesc + " text," +
84    InWWeightOne + " integer," +
85    InWWeightTwo + " integer," +
86    InWWeightThree + " integer," +
87    InWWeightAvg + " integer" +
88    ");";
89
90    db.execSQL(dbCreateItemsAndWeights);
91
92    //allowances table
93    String dbCreateAllowances = "create table " +
94    tblAllowances +
95    " (" +
96    AllowancesID + " integer primary key autoincrement not null," +
97    AllowancesRank + " text," +
98    AllowancesWithDep + " integer," +
99    AllowancesNoDep + " integer" +
100    ");";
101
102    db.execSQL(dbCreateAllowances);
103
104    //estimator table
105    String dbCreateEstimator = "create table " +
106    tblEstimator + 
107    " (" +
108    EstimatorID + " integer primary key autoincrement not null," +
109    EstimatorRoom + " text," +
110    EstimatorItem + " integer" +
111    EstimatorWeight + " integer" +
112    ");";
113
114    db.execSQL(dbCreateEstimator);
115
116    //inventory table
117    String dbCreateInventory = "create table " +
118    tblInventory + 
119    " (" +
120    InventoryID + " integer primary key autoincrement not null," +
121    InventoryItem + " text," + 
122    InventoryWeight + " integer," +
123    InventoryValue + " integer," +
124    InventoryImage1 + " blob," +
125    InventoryCondition + " text," + 
126    InventoryImage2 + " blob," + 
127    InventoryNotes + " text," +
128    InventoryMovingInstructions + " text" +
129    ");";
130
131    db.execSQL(dbCreateInventory);      
132
133    //stunt table
134    String newTableQueryString = "create table " +
135    TABLE_NAME +
136    " (" +
137    TABLE_ROW_ID + " integer primary key autoincrement not null," +
138    TABLE_ROW_ONE + " text," +
139    TABLE_ROW_TWO + " text" +
140    ");";
141
142    db.execSQL(newTableQueryString);        
143
144    //inject data into the tables here? this is probably the best place???
145
146
147}
148
149public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
150    // TODO Auto-generated method stub
151
152    db.execSQL("DROP TABLE IF EXISTS " + tblInW);
153    db.execSQL("DROP TABLE IF EXISTS " + tblAllowances);
154    db.execSQL("DROP TABLE IF EXISTS " + tblEstimator);
155    db.execSQL("DROP TABLE IF EXISTS " + tblInventory);
156    db.execSQL("DROP TABLE IF EXISTS " + TABLE_NAME);
157
158    onCreate(db);
159}
160
161// Adding a row to the database table
162
163public void addInWItem(String string){
164
165
166}
167public void addRow(String rowStringOne, String rowStringTwo){
168
169    SQLiteDatabase db = this.getWritableDatabase();
170
171    //this is a key value pair holder used by android's SQLite functions
172    ContentValues values = new ContentValues();
173    values.put(TABLE_ROW_ONE, rowStringOne);
174    values.put(TABLE_ROW_TWO, rowStringTwo);
175
176    //ask the database object to insert the new data
177    try {
178        db.insert(TABLE_NAME,  null, values);
179    } catch (Exception e) {
180        // TODO Auto-generated catch block
181        Log.e("DB ERROR", e.toString());
182        e.printStackTrace();
183    }
184}
185
186
187//DELETING A ROW FROM THE DATABASE TABLE
188//
189// This is an example of how to delete a row from a database table
190// using this class. In most cases, this method probably does not need to be rewritten.
191//
192// @param rowID the SQLite database identifier for the row to delete.
193//
194public void deleteRow(long rowID){
195    SQLiteDatabase db = this.getWritableDatabase();
196    // ask the database object to delete the row of given rowID
197    try {
198        db.delete(TABLE_NAME, TABLE_ROW_ID + "=" + rowID, null);
199    } catch (Exception e) {
200        // TODO Auto-generated catch block
201        Log.e("DB ERROR", e.toString());
202        e.printStackTrace();
203    }
204
205}
206
207//UPDATING A ROW IN THE DATABASE TABLE
208//
209// This is an example of how to update a row in the database table
210// using this class. You should edit this method to suit your needs.
211//
212// @param rowID the SQLite database identifier for the row to update.
213// @param rowStringOne the new value for the row's first column
214// @param rowStringTwo the new value for the row's second column
215
216public void updateRow(long rowID, String rowStringOne, String rowStringTwo){
217
218    SQLiteDatabase db = this.getWritableDatabase();
219
220    //this is a key value pair holder used by android's SQLite functions
221    ContentValues values = new ContentValues();
222    values.put(TABLE_ROW_ONE,  rowStringOne);
223    values.put(TABLE_ROW_TWO,  rowStringTwo);
224
225    //ask the database object to update the database row of given rowID
226    try {
227        db.update(TABLE_NAME,  values, TABLE_ROW_ID + "=" + rowID, null);
228    } catch (Exception e) {
229        // TODO Auto-generated catch block
230        Log.e("DB Error", e.toString());
231        e.printStackTrace();
232    }
233}
234
235//RETRIEVING A ROW IN THE DATABASE TABLE
236//'
237// This is an example of how to retrieve a row from a database table using this class. You should edit this method to suit your needs.
238//
239// @param rowID the id of the row to retrieve
240// @return an array containing the data from the row
241
242public ArrayList<Object> getRowAsArray(long rowID){
243
244    SQLiteDatabase db = this.getReadableDatabase();
245    //create an array list to store data from the database row.
246    //I would recommend creating a JavaBean compliant object 
247    //to store this data instead. That way you can ensure data types are correct.
248    ArrayList<Object> rowArray = new ArrayList<Object>();
249    Cursor cursor;
250
251    try {
252        // this is a database call that creates a "cursor" object.
253        // the cursor object stores the information collected from the 
254        // database and is used to iterate through the data.
255        cursor = db.query(
256                TABLE_NAME,
257                new String[] { TABLE_ROW_ID, TABLE_ROW_ONE, TABLE_ROW_TWO },
258                TABLE_ROW_ID + "=" + rowID,
259                null, null, null, null, null);
260
261        //move the pointer to position zero in the cursor.
262        cursor.moveToFirst();
263
264        // if there is data available after the cursor's pointer, add
265        // it to the ArrayList that will be returned by the method.
266
267        if (!cursor.isAfterLast()){
268            do{
269
270                rowArray.add(cursor.getLong(0));
271                rowArray.add(cursor.getString(1));
272                rowArray.add(cursor.getString(2));
273
274              } while (cursor.moveToNext());
275
276            }
277
278        //let java know that you are through with the cursor.
279        cursor.close();
280
281    } catch (SQLException e) {
282        // TODO Auto-generated catch block
283        Log.e("DB ERROR", e.toString());
284        e.printStackTrace();
285    }
286
287    //return the ArrayList containing the given row from the database.
288    return rowArray;
289
290
291    }
292
293//RETRIEVING ALL ROWS FROM THE DATABASE TABLE
294//
295//This is an example of how to retrieve all data from a database table using this class.
296//You should edit this method to suit your needs.
297//
298// the key is automatically assigned by the database
299
300public ArrayList<ArrayList<Object>> getAllRowsAsArrays(){
301
302    SQLiteDatabase db = this.getReadableDatabase();
303
304    //create an ArrayList that will hold all of the data collected from the database
305    ArrayList<ArrayList<Object>> dataArrays = new ArrayList<ArrayList<Object>>();
306
307    //this is a database call that creates a "cursor" object.
308    //the cursor object stores the information collected from the database and is used to iterate through the data.
309    Cursor cursor;
310
311    try{
312        //ask the database object to create the cursor.
313        cursor = db.query(
314                TABLE_NAME,
315                new String[]{TABLE_ROW_ID, TABLE_ROW_ONE, TABLE_ROW_TWO},
316                null, null, null, null, null
317                );
318
319        //move the cursor's pointer to position zero.
320        cursor.moveToFirst();
321
322        //if there is data after the current cursor position add it to the ArrayList.
323        if (!cursor.isAfterLast()){
324
325            do
326            {
327                ArrayList<Object> dataList = new ArrayList<Object>();
328
329                dataList.add(cursor.getLong(0));
330                dataList.add(cursor.getString(1));
331                dataList.add(cursor.getString(2));
332
333                dataArrays.add(dataList);
334            } 
335
336            //move the cursor's pointer up one position.
337            while (cursor.moveToNext());
338
339        }
340    }
341    catch (SQLException e){
342
343        Log.e("DB ERROR", e.toString());
344        e.printStackTrace();
345    }
346
347    //return the ArrayList that holds the data collected from the database.
348    return dataArrays;
349}
350
351 }