1/*
2 * Copyright (C) 2014 Google Inc. All Rights Reserved.
3 *
4 * Licensed under the Apache License, Version 2.0 (the "License");
5 * you may not use this file except in compliance with the License.
6 * You may obtain a copy of the License at
7 *
8 *      http://www.apache.org/licenses/LICENSE-2.0
9 *
10 * Unless required by applicable law or agreed to in writing, software
11 * distributed under the License is distributed on an "AS IS" BASIS,
12 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
13 * See the License for the specific language governing permissions and
14 * limitations under the License.
15 */
16
17package com.example.android.wearable.speedtracker.db;
18
19import android.content.ContentValues;
20import android.content.Context;
21import android.database.Cursor;
22import android.database.sqlite.SQLiteDatabase;
23import android.database.sqlite.SQLiteOpenHelper;
24import android.provider.BaseColumns;
25import android.util.Log;
26
27import com.example.android.wearable.speedtracker.common.LocationEntry;
28import com.example.android.wearable.speedtracker.common.Utils;
29
30import java.util.ArrayList;
31import java.util.Calendar;
32import java.util.List;
33
34/**
35 * A helper class to set up the database that holds the GPS location information
36 */
37public class LocationDbHelper extends SQLiteOpenHelper {
38
39    private static final String TAG = "LocationDbHelper";
40
41    public static final String TABLE_NAME = "location";
42    public static final String COLUMN_NAME_DAY = "day";
43    public static final String COLUMN_NAME_LATITUDE = "lat";
44    public static final String COLUMN_NAME_LONGITUDE = "lon";
45    public static final String COLUMN_NAME_TIME = "time";
46
47    private static final String TEXT_TYPE = " TEXT";
48    private static final String INTEGER_TYPE = " INTEGER";
49    private static final String REAL_TYPE = " REAL";
50    private static final String COMMA_SEP = ",";
51    private static final String SQL_CREATE_ENTRIES =
52            "CREATE TABLE " + TABLE_NAME + " ("
53                    + BaseColumns._ID + " INTEGER PRIMARY KEY,"
54                    + COLUMN_NAME_DAY + TEXT_TYPE + COMMA_SEP
55                    + COLUMN_NAME_LATITUDE + REAL_TYPE + COMMA_SEP
56                    + COLUMN_NAME_LONGITUDE + REAL_TYPE + COMMA_SEP
57                    + COLUMN_NAME_TIME + INTEGER_TYPE
58                    + " )";
59    private static final String SQL_DELETE_ENTRIES = "DROP TABLE IF EXISTS " + TABLE_NAME;
60
61    public static final int DATABASE_VERSION = 1;
62    public static final String DATABASE_NAME = "Location.db";
63
64    public LocationDbHelper(Context context) {
65        super(context, DATABASE_NAME, null, DATABASE_VERSION);
66    }
67
68    @Override
69    public void onCreate(SQLiteDatabase db) {
70        db.execSQL(SQL_CREATE_ENTRIES);
71    }
72
73    @Override
74    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
75        db.execSQL(SQL_DELETE_ENTRIES);
76        onCreate(db);
77    }
78
79    /**
80     * Inserts a {@link com.example.android.wearable.speedtracker.common.LocationEntry} item to the
81     * database.
82     */
83    public final long insert(LocationEntry entry) {
84        if (Log.isLoggable(TAG, Log.DEBUG)) {
85            Log.d(TAG, "Inserting a LocationEntry");
86        }
87        // Gets the data repository in write mode
88        SQLiteDatabase db = getWritableDatabase();
89
90        // Create a new map of values, where column names are the keys
91        ContentValues values = new ContentValues();
92        values.put(COLUMN_NAME_DAY, entry.day);
93        values.put(COLUMN_NAME_LONGITUDE, entry.longitude);
94        values.put(COLUMN_NAME_LATITUDE, entry.latitude);
95        values.put(COLUMN_NAME_TIME, entry.calendar.getTimeInMillis());
96
97        // Insert the new row, returning the primary key value of the new row
98        return db.insert(TABLE_NAME, "null", values);
99    }
100
101    /**
102     * Returns a list of {@link com.example.android.wearable.speedtracker.common.LocationEntry}
103     * objects from the database for a given day. The list can be empty (but not {@code null}) if
104     * there are no such items. This method looks at the day that the calendar argument points at.
105     */
106    public final List<LocationEntry> read(Calendar calendar) {
107        SQLiteDatabase db = getReadableDatabase();
108        String[] projection = {
109                COLUMN_NAME_LONGITUDE,
110                COLUMN_NAME_LATITUDE,
111                COLUMN_NAME_TIME
112        };
113        String day = Utils.getHashedDay(calendar);
114
115        // sort ASC based on the time of the entry
116        String sortOrder = COLUMN_NAME_TIME + " ASC";
117        String selection = COLUMN_NAME_DAY + " LIKE ?";
118
119        Cursor cursor = db.query(
120                TABLE_NAME,                 // The table to query
121                projection,                 // The columns to return
122                selection,                  // The columns for the WHERE clause
123                new String[]{day},          // The values for the WHERE clause
124                null,                       // don't group the rows
125                null,                       // don't filter by row groups
126                sortOrder                   // The sort order
127        );
128
129        List<LocationEntry> result = new ArrayList<LocationEntry>();
130        int count = cursor.getCount();
131        if (count > 0) {
132            cursor.moveToFirst();
133            while (!cursor.isAfterLast()) {
134                Calendar cal = Calendar.getInstance();
135                cal.setTimeInMillis(cursor.getLong(2));
136                LocationEntry entry = new LocationEntry(cal, cursor.getDouble(1),
137                        cursor.getDouble(0));
138                result.add(entry);
139                cursor.moveToNext();
140            }
141        }
142        cursor.close();
143        return result;
144    }
145
146    /**
147     * Deletes all the entries in the database for the given day. The argument {@code day} should
148     * match the format provided by {@link getHashedDay()}
149     */
150    public final int delete(String day) {
151        SQLiteDatabase db = getWritableDatabase();
152        // Define 'where' part of the query.
153        String selection = COLUMN_NAME_DAY + " LIKE ?";
154        String[] selectionArgs = {day};
155        return db.delete(TABLE_NAME, selection, selectionArgs);
156    }
157
158    /**
159     * Deletes all the entries in the database for the day that the {@link java.util.Calendar}
160     * argument points at.
161     */
162    public final int delete(Calendar calendar) {
163        return delete(Utils.getHashedDay(calendar));
164    }
165}
166