Android Open Source - frc-notebook Database Handler






From Project

Back to project page frc-notebook.

License

The source code is released under:

GNU General Public License

If you think the Android project frc-notebook listed in this page is inappropriate, such as containing malicious code/tools or violating the copyright, please email info at java2s dot com, thanks.

Java Source Code

package com.plnyyanks.frcnotebook.database;
//ww  w .  ja v  a2 s .  c  o m
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

import com.google.gson.JsonArray;
import com.google.gson.JsonElement;
import com.google.gson.JsonNull;
import com.google.gson.JsonObject;
import com.plnyyanks.frcnotebook.Constants;
import com.plnyyanks.frcnotebook.datatypes.Event;
import com.plnyyanks.frcnotebook.datatypes.Match;
import com.plnyyanks.frcnotebook.datatypes.Note;
import com.plnyyanks.frcnotebook.datatypes.Team;
import com.plnyyanks.frcnotebook.json.JSONManager;

import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;

/**
 * File created by phil on 3/1/14.
 * Copyright 2014, Phil Lopreiato
 * This file is part of FRC Notebook.
 * FRC Notebook is free software: you can redistribute it and/or modify it under the terms of the GNU General Public License as published by the Free Software Foundation, either version 3 of the License, or (at your option) any later version.
 * FRC Notebook is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License for more details.
 * You should have received a copy of the GNU General Public License along with FRC Notebook. If not, see http://www.gnu.org/licenses/.
 */
public class DatabaseHandler extends SQLiteOpenHelper {
    private static final int DATABASE_VERSION = 17;
    public static final String DATABASE_NAME = "VOL_NOTES",

    TABLE_EVENTS            = "events",
            KEY_EVENTKEY    = "eventKey",
            KEY_EVENTNAME   = "eventName",
            KEY_EVENTSHORT  = "eventShortName",
            KEY_EVENTYEAR   = "eventYear",
            KEY_EVENTLOC    = "eventLocation",
            KEY_EVENTSTART  = "startDate",
            KEY_EVENTEND    = "endDate",
            KEY_EVENTOFFICIAL= "official",

    TABLE_MATCHES           = "matches",
            KEY_MATCHTIME   = "time",
            KEY_MATCHKEY    = "matchKey",
            KEY_MATCHTYPE   = "type",
            KEY_MATCHNO     = "matchNumber",
            KEY_MATCHSET    = "matchSet",
            KEY_REDALLIANCE = "redAlliance",
            KEY_BLUEALLIANCE= "blueAlliance",
            KEY_BLUESCORE   = "blueScore",
            KEY_REDSCORE    = "redScore",

    TABLE_TEAMS             = "teams",
            KEY_TEAMKEY     = "teamKey",
            KEY_TEAMNUMBER  = "teamNumber",
            KEY_TEAMNAME    = "teamName",
            KEY_TEAMSITE    = "teamWebsite",
            KEY_TEAMEVENTS  = "events",

    TABLE_NOTES             = "notes",
            KEY_NOTEID      = "id",
            //KEY_EVENTKEY  = "eventKey",
            //KEY_MATCHKEY  = "matchKey",
            //KEY_TEAMKEY   = "teamKey",
            KEY_NOTE        = "note",
            KEY_NOTETIME    = "timestamp",
            KEY_NOTEPARENT  = "parentId",
            KEY_NOTEPICS    = "pictures",

    TABLE_PREDEF_NOTES      = "prefedined_note",
            KEY_DEF_NOTEID  = "id",
            KEY_DEF_NOTE    = "note";


    private SQLiteDatabase db;

    public DatabaseHandler(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
        db = getWritableDatabase();
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        String CREATE_EVENTS_TABLE = "CREATE TABLE " + TABLE_EVENTS + "("
                + KEY_EVENTKEY + " TEXT PRIMARY KEY,"
                + KEY_EVENTNAME + " TEXT,"
                + KEY_EVENTSHORT + " TEXT,"
                + KEY_EVENTYEAR + " INTEGER,"
                + KEY_EVENTLOC + " TEXT,"
                + KEY_EVENTSTART + " TEXT,"
                + KEY_EVENTEND + " TEXT,"
                + KEY_EVENTOFFICIAL+ " INTEGER DEFAULT 1"
                + ")";
        db.execSQL(CREATE_EVENTS_TABLE);

        String CREATE_MATCHES_TABLE = "CREATE TABLE " + TABLE_MATCHES + "("
                + KEY_MATCHKEY + " TEXT PRIMARY KEY,"
                + KEY_MATCHTYPE + " TEXT,"
                + KEY_MATCHNO + " INTEGER,"
                + KEY_MATCHSET + " INTEGER,"
                + KEY_BLUEALLIANCE + " TEXT,"
                + KEY_REDALLIANCE + " TEXT,"
                + KEY_BLUESCORE + " INTEGER,"
                + KEY_REDSCORE + " INTEGER,"
                + KEY_MATCHTIME + " TEXT"
                + ")";
        db.execSQL(CREATE_MATCHES_TABLE);

        String CREATE_TEAMS_TABLE = "CREATE TABLE " + TABLE_TEAMS + "("
                + KEY_TEAMKEY + " TEXT PRIMARY KEY,"
                + KEY_TEAMNUMBER + " INTEGER,"
                + KEY_TEAMNAME + " TEXT,"
                + KEY_TEAMSITE + " TEXT,"
                + KEY_TEAMEVENTS + " TEXT"
                + ")";
        db.execSQL(CREATE_TEAMS_TABLE);

        String CREATE_NOTES_TABLE = "CREATE TABLE " + TABLE_NOTES + "("
                + KEY_NOTEID + " INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,"
                + KEY_EVENTKEY + " TEXT NOT NULL,"
                + KEY_MATCHKEY + " TEXT NOT NULL,"
                + KEY_TEAMKEY + " TEXT NOT NULL,"
                + KEY_NOTE + " TEXT NOT NULL,"
                + KEY_NOTETIME + " TEXT,"
                + KEY_NOTEPARENT + " INTEGER,"
                + KEY_NOTEPICS + " TEXT"
                + ")";
        db.execSQL(CREATE_NOTES_TABLE);

        String CREATE_DEF_NOTE_TABLE = "CREATE TABLE "+TABLE_PREDEF_NOTES + "("
                + KEY_DEF_NOTEID + " INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,"
                + KEY_DEF_NOTE + " TEXT"
                + ")";
        db.execSQL(CREATE_DEF_NOTE_TABLE);
    }

    @Override
    public void onUpgrade(SQLiteDatabase sqLiteDatabase, int oldVersion, int newVersion) {

        if(oldVersion<12&&newVersion>=12){
            String upgradeQuery1 = "ALTER TABLE "+TABLE_NOTES+" ADD COLUMN "+KEY_NOTEPARENT+" INTEGER";
            String upgradeQuery2 = "ALTER TABLE "+TABLE_NOTES+" ADD COLUMN "+KEY_NOTEPICS+  " TEXT";
            sqLiteDatabase.execSQL(upgradeQuery1);
            sqLiteDatabase.execSQL(upgradeQuery2);

            String CREATE_DEF_NOTE_TABLE = "CREATE TABLE "+TABLE_PREDEF_NOTES + "("
                    + KEY_DEF_NOTEID + " INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,"
                    + KEY_DEF_NOTE + " TEXT"
                    + ")";
            sqLiteDatabase.execSQL(CREATE_DEF_NOTE_TABLE);

            return;
        }

        if(oldVersion<14 && newVersion>=14){
            String updateQuery = "ALTER TABLE "+TABLE_MATCHES+" ADD COLUMN "+KEY_MATCHTIME+" TEXT";
            sqLiteDatabase.execSQL(updateQuery);

            return;
        }

        if(oldVersion<16 && newVersion>=16 ){
            if(!columnExists(sqLiteDatabase,TABLE_MATCHES,KEY_MATCHTIME)) {
                Log.d(Constants.LOG_TAG,"Adding match time column");
                String updateQuery = "ALTER TABLE " + TABLE_MATCHES + " ADD COLUMN " + KEY_MATCHTIME + " TEXT";
                sqLiteDatabase.execSQL(updateQuery);
            }
            return;
        }

        if(oldVersion<17 && newVersion>=17){
            if(!columnExists(sqLiteDatabase,TABLE_EVENTS,KEY_EVENTOFFICIAL)) {
                Log.d(Constants.LOG_TAG,"Adding event official column");
                String updateQuery = "ALTER TABLE " + TABLE_EVENTS + " ADD COLUMN " + KEY_EVENTOFFICIAL + " INTEGER DEFAULT 1";
                sqLiteDatabase.execSQL(updateQuery);
            }
            return;
        }

        // on upgrade drop older tables
        sqLiteDatabase.execSQL("DROP TABLE IF EXISTS " + TABLE_EVENTS);
        sqLiteDatabase.execSQL("DROP TABLE IF EXISTS " + TABLE_MATCHES);
        sqLiteDatabase.execSQL("DROP TABLE IF EXISTS " + TABLE_TEAMS);
        sqLiteDatabase.execSQL("DROP TABLE IF EXISTS " + TABLE_NOTES);
        sqLiteDatabase.execSQL("DROP TABLE IF EXISTS " + TABLE_PREDEF_NOTES);

        // create new tables
        onCreate(sqLiteDatabase);

    }

    public void clearDatabase() {
        // on upgrade drop older tables
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_EVENTS);
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_MATCHES);
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_TEAMS);
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_NOTES);
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_PREDEF_NOTES);

        // create new tables
        onCreate(db);
    }

    //managing events in SQL
    public long addEvent(Event in) {
        //first, check if that event exists already and only insert if it doesn't
        if (!eventExists(in.getEventKey())) {

            ContentValues values = new ContentValues();
            values.put(KEY_EVENTKEY, in.getEventKey());
            values.put(KEY_EVENTNAME, in.getEventName());
            values.put(KEY_EVENTSHORT, in.getShortName());
            values.put(KEY_EVENTYEAR, in.getEventYear());
            values.put(KEY_EVENTLOC, in.getEventLocation());
            values.put(KEY_EVENTSTART, in.getEventStart());
            values.put(KEY_EVENTEND, in.getEventEnd());
            values.put(KEY_EVENTOFFICIAL,in.isOfficial());

            //insert the row
            return db.insert(TABLE_EVENTS, null, values);
        } else {
            return updateEvent(in);
        }
    }
    public Event getEvent(String key) {

        Cursor cursor = db.query(TABLE_EVENTS, new String[]{KEY_EVENTKEY, KEY_EVENTNAME, KEY_EVENTSHORT, KEY_EVENTYEAR, KEY_EVENTLOC, KEY_EVENTSTART, KEY_EVENTEND,KEY_EVENTOFFICIAL},
                KEY_EVENTKEY + "=?", new String[]{key}, null, null, null, null);
        if (cursor != null && cursor.moveToFirst()) {
            Event event = new Event(cursor.getString(0), cursor.getString(1), cursor.getString(2), cursor.getString(4), cursor.getString(5), cursor.getString(6), cursor.getInt(3),cursor.getInt(7)==1);
            cursor.close();
            return event;
        } else {
            return null;
        }

    }
    public ArrayList<Event> getAllEvents() {
        ArrayList<Event> eventList = new ArrayList<Event>();

        String selectQuery = "SELECT * FROM " + TABLE_EVENTS;

        Cursor cursor = db.rawQuery(selectQuery, null);

        //loop through rows
        if (cursor.moveToFirst()) {
            do {
                Event event = new Event();
                event.setEventKey(cursor.getString(0));
                event.setEventName(cursor.getString(1));
                event.setShortName(cursor.getString(2));
                event.setEventYear(cursor.getInt(3));
                event.setEventLocation(cursor.getString(4));
                event.setEventStart(cursor.getString(5));
                event.setEventEnd(cursor.getString(6));
                event.setOfficial(cursor.getInt(7)==1);

                eventList.add(event);
            } while (cursor.moveToNext());
        }

        cursor.close();

        return eventList;
    }
    public ArrayList<Event> getCurrentEvents(){
        ArrayList<Event> eventList = new ArrayList<Event>();

        String selectQuery = "SELECT * FROM " + TABLE_EVENTS;

        Cursor cursor = db.rawQuery(selectQuery, null);
        Date currentDate = new Date();
        //loop through rows
        if (cursor.moveToFirst()) {
            do {
                Event event = new Event();
                event.setEventKey(cursor.getString(0));
                event.setEventName(cursor.getString(1));
                event.setShortName(cursor.getString(2));
                event.setEventYear(cursor.getInt(3));
                event.setEventLocation(cursor.getString(4));
                event.setEventStart(cursor.getString(5));
                event.setEventEnd(cursor.getString(6));
                event.setOfficial(cursor.getInt(7)==1);
                if(currentDate.compareTo(event.getStartDate())>=0 && currentDate.compareTo(event.getEndDate())<=0)
                    eventList.add(event);
            } while (cursor.moveToNext());
        }

        cursor.close();

        return eventList;
    }
    public ArrayList<String> getAllEventKeys(){
        ArrayList<String> eventList = new ArrayList<String>();
        String selectQuery = "SELECT * FROM " + TABLE_EVENTS;
        Cursor cursor = db.rawQuery(selectQuery, null);

        //loop through rows
        if (cursor.moveToFirst()) {
            do {
                eventList.add(cursor.getString(0));
            } while (cursor.moveToNext());
        }

        cursor.close();

        return eventList;
    }
    public boolean eventExists(String key) {
        Cursor cursor = db.query(TABLE_EVENTS, new String[]{KEY_EVENTKEY}, KEY_EVENTKEY + "=?", new String[]{key}, null, null, null, null);
        return cursor.moveToFirst();
    }
    public int updateEvent(Event in) {

        ContentValues values = new ContentValues();
        values.put(KEY_EVENTKEY, in.getEventKey());
        values.put(KEY_EVENTNAME, in.getEventName());
        values.put(KEY_EVENTSHORT, in.getShortName());
        values.put(KEY_EVENTYEAR, in.getEventYear());
        values.put(KEY_EVENTLOC, in.getEventLocation());
        values.put(KEY_EVENTSTART, in.getEventStart());
        values.put(KEY_EVENTEND, in.getEventEnd());
        values.put(KEY_EVENTOFFICIAL,in.isOfficial());

        return db.update(TABLE_EVENTS, values, KEY_EVENTKEY + " =?", new String[]{in.getEventKey()});
    }
    public void deleteEvent(String eventKey) {
        db.delete(TABLE_EVENTS, KEY_EVENTKEY + "=?", new String[]{eventKey});
        deleteMatchesAtEvent(eventKey);
        deleteEventFromTeams(eventKey);
        deleteNotesFromEvent(eventKey);
    }
    public JsonArray exportEvents() {
        JsonArray output = new JsonArray();
        String selectQuery = "SELECT * FROM " + TABLE_EVENTS;
        Cursor cursor = db.rawQuery(selectQuery, null);
        //loop through rows
        if (cursor.moveToFirst()) {
            do {
                JsonObject event = new JsonObject();
                event.addProperty(KEY_EVENTKEY, cursor.getString(0));
                event.addProperty(KEY_EVENTNAME, cursor.getString(1));
                event.addProperty(KEY_EVENTSHORT, cursor.getString(2));
                event.addProperty(KEY_EVENTYEAR, cursor.getInt(3));
                event.addProperty(KEY_EVENTLOC, cursor.getString(4));
                event.addProperty(KEY_EVENTSTART, cursor.getString(5));
                event.addProperty(KEY_EVENTEND, cursor.getString(6));
                event.addProperty(KEY_EVENTOFFICIAL,cursor.getInt(7)==1);

                output.add(event);
            } while (cursor.moveToNext());
        }

        cursor.close();
        return output;
    }
    public void importEvents(JsonArray events) {
        Iterator<JsonElement> iterator = events.iterator();
        JsonObject e;
        Event event;

        while (iterator.hasNext()) {
            e = iterator.next().getAsJsonObject();
            event = new Event();
            event.setEventKey(e.get(KEY_EVENTKEY).getAsString());
            event.setEventName(e.get(KEY_EVENTNAME).getAsString());
            event.setShortName(e.get(KEY_EVENTSHORT).getAsString());
            event.setEventYear(e.get(KEY_EVENTYEAR).getAsInt());
            if(!(e.get(KEY_EVENTLOC) instanceof JsonNull))
                event.setEventLocation(e.get(KEY_EVENTLOC).getAsString());
            event.setEventStart(e.get(KEY_EVENTSTART).getAsString());
            event.setEventEnd(e.get(KEY_EVENTEND).getAsString());
            event.setOfficial(e.get(KEY_EVENTOFFICIAL).getAsBoolean());

            addEvent(event);
        }
    }

    //managing Matches in SQL
    public long addMatch(Match in) {
        //first, check if that event exists already and only insert if it doesn't
        if (!matchExists(in.getMatchKey())) {

            ContentValues values = new ContentValues();
            values.put(KEY_MATCHKEY, in.getMatchKey());
            values.put(KEY_MATCHTYPE, Match.SHORT_TYPES.get(in.getMatchType()));
            values.put(KEY_MATCHNO, in.getMatchNumber());
            values.put(KEY_MATCHSET, in.getSetNumber());
            values.put(KEY_BLUEALLIANCE, in.getBlueAlliance());
            values.put(KEY_REDALLIANCE, in.getRedAlliance());
            values.put(KEY_BLUESCORE, in.getBlueScore());
            values.put(KEY_REDSCORE, in.getRedScore());
            values.put(KEY_MATCHTIME,in.getMatchTime());

            //insert the row
            return db.insert(TABLE_MATCHES, null, values);
        } else {
            return updateMatch(in);
        }
    }
    public Match getMatch(String key) {

        Cursor cursor = db.query(TABLE_MATCHES, new String[]{KEY_MATCHKEY, KEY_MATCHTYPE, KEY_MATCHNO, KEY_MATCHSET, KEY_BLUEALLIANCE, KEY_REDALLIANCE, KEY_BLUESCORE, KEY_REDSCORE,KEY_MATCHTIME},
                KEY_MATCHKEY + "=?", new String[]{key}, null, null, null, null);

        if (cursor != null && cursor.moveToFirst()) {
            //(String matchKey, String matchType, int matchNumber, String blueAlliance, String redAlliance, int blueScore, int redScore)
            Match match = new Match();
            match.setMatchKey(cursor.getString(0));
            match.setMatchType(cursor.getString(1));
            match.setMatchNumber(cursor.getInt(2));
            match.setSetNumber(cursor.getInt(3));
            match.setBlueAlliance(cursor.getString(4));
            match.setRedAlliance(cursor.getString(5));
            match.setBlueScore(cursor.getInt(6));
            match.setRedScore(cursor.getInt(7));
            match.setMatchTime(cursor.getString(8));

            cursor.close();
            return match;
        } else {
            return null;
        }

    }
    public ArrayList<Match> getAllMatchesForTeam(String teamKey){
        ArrayList<Match> matchList = new ArrayList<Match>();
        String selectQuery = "SELECT * FROM " + TABLE_MATCHES + (!teamKey.equals("all")?(" WHERE " + KEY_REDALLIANCE + " LIKE '%" + teamKey + "%' OR "+KEY_BLUEALLIANCE + " LIKE '%" + teamKey + "%'"):"");
        Cursor cursor = db.rawQuery(selectQuery, null);
        //loop through rows
        ////(String matchKey, String matchType, int matchNumber, int[] blueAlliance, int[] redAlliance, int blueScore, int redScore)
        if (cursor.moveToFirst()) {
            do {
                Match match = new Match();
                match.setMatchKey(cursor.getString(0));
                match.setMatchType(cursor.getString(1));
                match.setMatchNumber(cursor.getInt(2));
                match.setSetNumber(cursor.getInt(3));
                match.setBlueAlliance(cursor.getString(4));
                match.setRedAlliance(cursor.getString(5));
                match.setBlueScore(cursor.getInt(6));
                match.setRedScore(cursor.getInt(7));
                match.setMatchTime(cursor.getString(8));
                matchList.add(match);
            } while (cursor.moveToNext());
        }

        cursor.close();

        return matchList;
    }
    public ArrayList<Match> getAllMatches(String eventKey) {
        ArrayList<Match> matchList = new ArrayList<Match>();

        String selectQuery = "SELECT * FROM " + TABLE_MATCHES + (!eventKey.equals("all")?(" WHERE " + KEY_MATCHKEY + " LIKE '%" + eventKey + "%'"):"");

        Cursor cursor = db.rawQuery(selectQuery, null);

        //loop through rows
        ////(String matchKey, String matchType, int matchNumber, int[] blueAlliance, int[] redAlliance, int blueScore, int redScore)
        if (cursor.moveToFirst()) {
            do {
                Match match = new Match();
                match.setMatchKey(cursor.getString(0));
                match.setMatchType(cursor.getString(1));
                match.setMatchNumber(cursor.getInt(2));
                match.setSetNumber(cursor.getInt(3));
                match.setBlueAlliance(cursor.getString(4));
                match.setRedAlliance(cursor.getString(5));
                match.setBlueScore(cursor.getInt(6));
                match.setRedScore(cursor.getInt(7));
                match.setMatchTime(cursor.getString(8));
                matchList.add(match);
            } while (cursor.moveToNext());
        }

        cursor.close();

        return matchList;
    }
    public ArrayList<Match> getAllMatches(String eventKey, String teamKey) {
        ArrayList<Match> matchList = new ArrayList<Match>();

        String selectQuery = "SELECT * FROM " + TABLE_MATCHES + " WHERE " + KEY_MATCHKEY + " LIKE '%" + eventKey + "%'"+(!teamKey.equals("all")?(" AND (" + KEY_REDALLIANCE + " LIKE '%" + teamKey + "%' OR "+KEY_BLUEALLIANCE + " LIKE '%" + teamKey + "%')"):"");

        Cursor cursor = db.rawQuery(selectQuery, null);

        //loop through rows
        ////(String matchKey, String matchType, int matchNumber, int[] blueAlliance, int[] redAlliance, int blueScore, int redScore)
        if (cursor.moveToFirst()) {
            do {
                Match match = new Match();
                match.setMatchKey(cursor.getString(0));
                match.setMatchType(cursor.getString(1));
                match.setMatchNumber(cursor.getInt(2));
                match.setSetNumber(cursor.getInt(3));
                match.setBlueAlliance(cursor.getString(4));
                match.setRedAlliance(cursor.getString(5));
                match.setBlueScore(cursor.getInt(6));
                match.setRedScore(cursor.getInt(7));
                match.setMatchTime(cursor.getString(8));
                matchList.add(match);
            } while (cursor.moveToNext());
        }

        cursor.close();

        return matchList;
    }
    public boolean matchExists(String key) {
        Log.d(Constants.LOG_TAG,"Testing key: "+key);
        if(key==null) return false;
        Cursor cursor = db.query(TABLE_MATCHES, new String[]{KEY_MATCHKEY}, KEY_MATCHKEY + "=?", new String[]{key}, null, null, null, null);
        return cursor.moveToFirst();

    }
    public int updateMatch(Match in) {

        ContentValues values = new ContentValues();
        values.put(KEY_MATCHKEY, in.getMatchKey());
        values.put(KEY_MATCHTYPE, Match.SHORT_TYPES.get(in.getMatchType()));
        values.put(KEY_MATCHNO, in.getMatchNumber());
        values.put(KEY_MATCHSET, in.getSetNumber());
        values.put(KEY_BLUEALLIANCE, in.getBlueAlliance());
        values.put(KEY_REDALLIANCE, in.getRedAlliance());
        values.put(KEY_BLUESCORE, in.getBlueScore());
        values.put(KEY_REDSCORE, in.getRedScore());
        values.put(KEY_MATCHTIME,in.getMatchTime());

        return db.update(TABLE_MATCHES, values, KEY_MATCHKEY + " =?", new String[]{in.getMatchKey()});
    }
    public void deleteMatchesAtEvent(String eventKey) {
        db.delete(TABLE_MATCHES, KEY_MATCHKEY + " LIKE '%" + eventKey + "%'", null);
    }
    public JsonArray exportMatches() {
        JsonArray output = new JsonArray();
        String selectQuery = "SELECT * FROM " + TABLE_MATCHES;
        Cursor cursor = db.rawQuery(selectQuery, null);

        //loop through rows
        ////(String matchKey, String matchType, int matchNumber, int[] blueAlliance, int[] redAlliance, int blueScore, int redScore)
        if (cursor.moveToFirst()) {
            do {
                JsonObject match = new JsonObject();
                match.addProperty(KEY_MATCHKEY, cursor.getString(0));
                match.addProperty(KEY_MATCHTYPE, cursor.getString(1));
                match.addProperty(KEY_MATCHNO, cursor.getInt(2));
                match.addProperty(KEY_MATCHSET, cursor.getInt(3));
                match.add(KEY_REDALLIANCE, JSONManager.getasJsonArray(cursor.getString(5)));
                match.add(KEY_BLUEALLIANCE, JSONManager.getasJsonArray(cursor.getString(4)));
                match.addProperty(KEY_BLUESCORE, cursor.getInt(6));
                match.addProperty(KEY_REDSCORE, cursor.getInt(7));
                match.addProperty(KEY_MATCHTIME,cursor.getString(8));

                output.add(match);
            } while (cursor.moveToNext());
        }

        cursor.close();

        return output;
    }
    public void importMatches(JsonArray matches) {
        Iterator<JsonElement> iterator = matches.iterator();
        JsonObject m;
        Match match;

        while (iterator.hasNext()) {
            m = iterator.next().getAsJsonObject();
            match = new Match();
            match.setMatchKey(m.get(KEY_MATCHKEY).getAsString());
            match.setMatchType(m.get(KEY_MATCHTYPE).getAsString());
            match.setMatchNumber(m.get(KEY_MATCHNO).getAsInt());
            match.setSetNumber(m.get(KEY_MATCHSET).getAsInt());
            match.setRedAlliance(m.get(KEY_REDALLIANCE).toString());
            match.setBlueAlliance(m.get(KEY_BLUEALLIANCE).toString());
            match.setRedScore(m.get(KEY_REDSCORE).getAsInt());
            match.setBlueScore(m.get(KEY_BLUESCORE).getAsInt());
            try {
                match.setMatchTime(m.get(KEY_MATCHTIME).getAsString());
            }catch(Exception e){
                match.setMatchTime("");
            }

            addMatch(match);
        }
    }

    //managing teams in SQL
    public long addTeam(Team in) {
        //first, check if that event exists already and only insert if it doesn't
        if (!teamExists(in.getTeamKey())) {

            ContentValues values = new ContentValues();
            values.put(KEY_TEAMKEY, in.getTeamKey());
            values.put(KEY_TEAMNUMBER, in.getTeamNumber());
            values.put(KEY_TEAMNAME, in.getTeamName());
            values.put(KEY_TEAMSITE, in.getTeamWebsite());
            values.put(KEY_TEAMEVENTS, JSONManager.flattenToJsonArray(in.getTeamEvents()));

            //insert the row
            return db.insert(TABLE_TEAMS, null, values);
        } else {
            return updateTeam(in);
        }
    }
    public Team getTeam(String key) {
        Cursor cursor = db.query(TABLE_TEAMS, new String[]{KEY_TEAMKEY, KEY_TEAMNUMBER, KEY_TEAMNAME, KEY_TEAMSITE, KEY_TEAMEVENTS},
                KEY_TEAMKEY + "=?", new String[]{key}, null, null, null, null);
        if (cursor != null && cursor.moveToFirst()) {
            Team team = new Team(cursor.getString(0), cursor.getInt(1), cursor.getString(2), cursor.getString(3), JSONManager.getAsStringArrayList(cursor.getString(4)));
            cursor.close();
            return team;
        } else {
            return null;
        }

    }
    public ArrayList<Team> getAllTeams() {
        ArrayList<Team> teamList = new ArrayList<Team>();

        String selectQuery = "SELECT * FROM " + TABLE_TEAMS;

        Cursor cursor = db.rawQuery(selectQuery, null);

        //loop through rows
        if (cursor.moveToFirst()) {
            do {
                Team team = new Team();
                team.setTeamKey(cursor.getString(0));
                team.setTeamNumber(cursor.getInt(1));
                team.setTeamName(cursor.getString(2));
                team.setTeamName(cursor.getString(3));
                team.setTeamEvents(JSONManager.getAsStringArrayList(cursor.getString(4)));

                teamList.add(team);
            } while (cursor.moveToNext());
        }

        cursor.close();

        return teamList;
    }
    public ArrayList<Team> getAllTeamAtEvent(String eventKey) {
        if(eventKey.equals("all")) return getAllTeams();

        ArrayList<Team> teamList = new ArrayList<Team>();

        String selectQuery = "SELECT * FROM " + TABLE_TEAMS + " WHERE " + KEY_TEAMEVENTS + " LIKE '%" + eventKey + "%'";

        Cursor cursor = db.rawQuery(selectQuery, null);

        //loop through rows
        if (cursor.moveToFirst()) {
            do {
                Team team = new Team();
                team.setTeamKey(cursor.getString(0));
                team.setTeamNumber(cursor.getInt(1));
                team.setTeamName(cursor.getString(2));
                team.setTeamName(cursor.getString(3));
                team.setTeamEvents(JSONManager.getAsStringArrayList(cursor.getString(4)));

                teamList.add(team);
            } while (cursor.moveToNext());
        }

        cursor.close();

        return teamList;
    }
    public boolean teamExists(String key) {
        Cursor cursor = db.query(TABLE_TEAMS, new String[]{KEY_TEAMKEY}, KEY_TEAMKEY + "=?", new String[]{key}, null, null, null, null);
        return cursor.moveToFirst();
    }
    public int updateTeam(Team in) {
        return updateTeam(in, true);
    }
    public int updateTeam(Team in, boolean mergeEvents) {

        Team currentVals = getTeam(in.getTeamKey());
        if (mergeEvents)
            in.mergeEvents(currentVals.getTeamEvents());

        ContentValues values = new ContentValues();
        values.put(KEY_TEAMKEY, in.getTeamKey());
        values.put(KEY_TEAMNUMBER, in.getTeamNumber());
        values.put(KEY_TEAMNAME, in.getTeamName());
        values.put(KEY_TEAMSITE, in.getTeamWebsite());
        values.put(KEY_TEAMEVENTS, in.getTeamEvents().toString());

        return db.update(TABLE_TEAMS, values, KEY_TEAMKEY + " =?", new String[]{in.getTeamKey()});
    }
    public void deleteTeam(Team in) {
        db.delete(TABLE_TEAMS, KEY_TEAMKEY + "=?", new String[]{in.getTeamKey()});
    }
    public void deleteEventFromTeams(String eventKey) {
        String selectQuery = "SELECT * FROM " + TABLE_TEAMS + " WHERE " + KEY_TEAMEVENTS + " LIKE '%" + eventKey + "%'";
        Cursor cursor = db.rawQuery(selectQuery, null);

        //loop through rows
        if (cursor.moveToFirst()) {
            do {
                Team team = new Team();
                team.setTeamKey(cursor.getString(0));
                team.setTeamNumber(cursor.getInt(1));
                team.setTeamName(cursor.getString(2));
                team.setTeamName(cursor.getString(3));
                team.setTeamEvents(JSONManager.getAsStringArrayList(cursor.getString(4)));

                team.removeEvent(eventKey);
                updateTeam(team, false);
            } while (cursor.moveToNext());
        }

        cursor.close();
    }
    public void addEventToTeam(String teamKey,String eventKey){
        Team team = getTeam(teamKey);
        if(team == null){
            Log.w(Constants.LOG_TAG,"Team key "+teamKey+" not found");
            return;
        }
        team.addEvent(eventKey);

        updateTeam(team,false);
    }
    public void addEventToTeams(String[] teamKeys,String eventKey){
        for(String team:teamKeys)
            addEventToTeam(team,eventKey);
    }
    public JsonArray exportTeams() {
        JsonArray output = new JsonArray();
        String selectQuery = "SELECT * FROM " + TABLE_TEAMS;
        Cursor cursor = db.rawQuery(selectQuery, null);

        //loop through rows
        if (cursor.moveToFirst()) {
            do {
                JsonObject team = new JsonObject();
                team.addProperty(KEY_TEAMKEY, cursor.getString(0));
                team.addProperty(KEY_TEAMNUMBER, cursor.getInt(1));
                team.addProperty(KEY_TEAMNAME, cursor.getString(2));
                team.addProperty(KEY_TEAMNAME, cursor.getString(3));
                team.add(KEY_TEAMEVENTS, JSONManager.getasJsonArray(cursor.getString(4)));

                output.add(team);
            } while (cursor.moveToNext());
        }

        cursor.close();

        return output;
    }
    public void importTeams(JsonArray teams) {
        Iterator<JsonElement> iterator = teams.iterator();
        JsonObject t;
        Team team;

        while (iterator.hasNext()) {
            t = iterator.next().getAsJsonObject();
            team = new Team();

            team.setTeamKey(t.get(KEY_TEAMKEY).getAsString());
            try {
                team.setTeamName(t.get(KEY_TEAMNAME).getAsString());
            } catch (Exception e) {
                team.setTeamName("");
            }
            team.setTeamNumber(t.get(KEY_TEAMNUMBER).getAsInt());
            try {
                team.setTeamWebsite(t.get(KEY_TEAMSITE).getAsString());
            } catch (Exception e) {
                team.setTeamWebsite("");
            }

            team.setTeamEvents(JSONManager.getAsStringArrayList(t.get(KEY_TEAMEVENTS).getAsJsonArray().toString()));

            addTeam(team);
        }
    }

    //managing notes in SQL
    public short addNote(Note in) {
        short existCheck = noteExists(in);
        if (existCheck != -1) {
            Log.d(Constants.LOG_TAG,"Note already exists");
            return existCheck;
        }
        Log.d(Constants.LOG_TAG, "ADDING NOTE FOR: " + in.getTeamKey() + " " + in.getEventKey() + " " + in.getMatchKey());
        ContentValues values = new ContentValues();
        values.put(KEY_EVENTKEY, in.getEventKey());
        values.put(KEY_MATCHKEY, in.getMatchKey());
        values.put(KEY_TEAMKEY, in.getTeamKey());
        values.put(KEY_NOTE, in.getNote());
        values.put(KEY_NOTETIME, in.getTimestamp());
        values.put(KEY_NOTEPARENT, in.getParent());
        values.put(KEY_NOTEPICS, in.getPictures());

        //insert the row
        if (db.insert(TABLE_NOTES, null, values) == -1) {
            //error, return -1
            return -1;
        } else {
            //else, return the note's ID
            Cursor cursor = db.rawQuery("SELECT MAX(" + KEY_NOTEID + ") FROM " + TABLE_NOTES, null);
            if (cursor.moveToFirst()) {
                Log.d(Constants.LOG_TAG, "LARGEST ID FETCHED: " + cursor.getShort(0));
                return cursor.getShort(0);
            } else {
                Log.d(Constants.LOG_TAG, "NO RECORD FOUND");
                return -1;
            }
        }
    }
    public Note getNote(short id) {
        Cursor cursor = db.query(TABLE_NOTES, new String[]{KEY_NOTEID, KEY_EVENTKEY, KEY_MATCHKEY, KEY_TEAMKEY, KEY_NOTE, KEY_NOTETIME,KEY_NOTEPARENT,KEY_NOTEPICS},
                KEY_NOTEID + "=? ", new String[]{Short.toString(id)}, null, null, null, null);

        //loop through rows
        Note note = new Note();
        if (cursor.moveToFirst()) {

            note.setId(cursor.getShort(0));
            note.setEventKey(cursor.getString(1));
            note.setMatchKey(cursor.getString(2));
            note.setTeamKey(cursor.getString(3));
            note.setNote(cursor.getString(4));
            note.setTimestamp(cursor.getLong(5));
            note.setParent(cursor.getShort(6));
            note.setPictures(cursor.getString(7));

        }
        cursor.close();
        return note;
    }
    public ArrayList<Note> getAllNotes() {
        ArrayList<Note> noteList = new ArrayList<Note>();

        String selectQuery = "SELECT * FROM " + TABLE_NOTES;

        Cursor cursor = db.rawQuery(selectQuery, null);

        //loop through rows
        if (cursor.moveToFirst()) {
            do {
                Note note = new Note();
                note.setId(cursor.getShort(0));
                note.setEventKey(cursor.getString(1));
                note.setMatchKey(cursor.getString(2));
                note.setTeamKey(cursor.getString(3));
                note.setNote(cursor.getString(4));
                note.setTimestamp(cursor.getLong(5));
                note.setParent(cursor.getShort(6));
                note.setPictures(cursor.getString(7));

                noteList.add(note);
            } while (cursor.moveToNext());
        }

        cursor.close();

        return noteList;
    }
    public ArrayList<Note> getAllNotes(String whereClause,String[] vars){
        ArrayList<Note> noteList = new ArrayList<Note>();
        Cursor cursor = db.query(TABLE_NOTES, new String[]{KEY_NOTEID, KEY_EVENTKEY, KEY_MATCHKEY, KEY_TEAMKEY, KEY_NOTE, KEY_NOTETIME,KEY_NOTEPARENT,KEY_NOTEPICS},
                whereClause,vars, null, null, null, null);

        //loop through rows
        if (cursor.moveToFirst()) {
            do {
                Note note = new Note();
                note.setId(cursor.getShort(0));
                note.setEventKey(cursor.getString(1));
                note.setMatchKey(cursor.getString(2));
                note.setTeamKey(cursor.getString(3));
                note.setNote(cursor.getString(4));
                note.setTimestamp(cursor.getLong(5));
                note.setParent(cursor.getShort(6));
                note.setPictures(cursor.getString(7));

                noteList.add(note);
            } while (cursor.moveToNext());
        }

        cursor.close();
        //Log.d(Constants.LOG_TAG, " FOUND " + noteList.size() + " NOTES");
        return noteList;
    }
    public ArrayList<Note> getAllNotes(String teamKey) {
        ArrayList<Note> noteList = new ArrayList<Note>();

        Cursor cursor = db.query(TABLE_NOTES, new String[]{KEY_NOTEID, KEY_EVENTKEY, KEY_MATCHKEY, KEY_TEAMKEY, KEY_NOTE, KEY_NOTETIME,KEY_NOTEPARENT,KEY_NOTEPICS},
                KEY_TEAMKEY + "=?", new String[]{teamKey}, null, null, null, null);

        //loop through rows
        if (cursor.moveToFirst()) {
            do {
                Note note = new Note();
                note.setId(cursor.getShort(0));
                note.setEventKey(cursor.getString(1));
                note.setMatchKey(cursor.getString(2));
                note.setTeamKey(cursor.getString(3));
                note.setNote(cursor.getString(4));
                note.setTimestamp(cursor.getLong(5));
                note.setParent(cursor.getShort(6));
                note.setPictures(cursor.getString(7));

                noteList.add(note);
            } while (cursor.moveToNext());
        }

        cursor.close();

        return noteList;
    }
    public ArrayList<Note> getAllNotes(String teamKey, String eventKey) {
        ArrayList<Note> noteList = new ArrayList<Note>();

        Cursor cursor = db.query(TABLE_NOTES, new String[]{KEY_NOTEID, KEY_EVENTKEY, KEY_MATCHKEY, KEY_TEAMKEY, KEY_NOTE, KEY_NOTETIME,KEY_NOTEPARENT,KEY_NOTEPICS},
                KEY_TEAMKEY + "=? AND " + KEY_EVENTKEY + "=?", new String[]{teamKey, eventKey}, null, null, null, null);

        //loop through rows
        if (cursor.moveToFirst()) {
            do {
                Note note = new Note();
                note.setId(cursor.getShort(0));
                note.setEventKey(cursor.getString(1));
                note.setMatchKey(cursor.getString(2));
                note.setTeamKey(cursor.getString(3));
                note.setNote(cursor.getString(4));
                note.setTimestamp(cursor.getLong(5));
                note.setParent(cursor.getShort(6));
                note.setPictures(cursor.getString(7));

                noteList.add(note);
            } while (cursor.moveToNext());
        }

        cursor.close();
        //Log.d(Constants.LOG_TAG, " FOUND " + noteList.size() + " NOTES");
        return noteList;
    }
    public ArrayList<Note> getAllNotes(String teamKey, String eventKey, String matchKey) {
        ArrayList<Note> noteList = new ArrayList<Note>();

        Cursor cursor;
        if (!eventKey.equals("all") && !teamKey.equals("")) {
            if(teamKey.equals("all")){
                //looking for notes for ALL THE TEAMS
                cursor = db.query(TABLE_NOTES, new String[]{KEY_NOTEID, KEY_EVENTKEY, KEY_MATCHKEY, KEY_TEAMKEY, KEY_NOTE, KEY_NOTETIME,KEY_NOTEPARENT,KEY_NOTEPICS},
                        KEY_EVENTKEY + "=? AND " + KEY_MATCHKEY + "=?", new String[]{eventKey, matchKey}, null, null, null, null);
            }else{
                //regular event. Proceed normally
                cursor = db.query(TABLE_NOTES, new String[]{KEY_NOTEID, KEY_EVENTKEY, KEY_MATCHKEY, KEY_TEAMKEY, KEY_NOTE, KEY_NOTETIME,KEY_NOTEPARENT,KEY_NOTEPICS},
                        KEY_TEAMKEY + "=? AND " + KEY_EVENTKEY + "=? AND " + KEY_MATCHKEY + "=?", new String[]{teamKey, eventKey, matchKey}, null, null, null, null);
            }
        } else if (eventKey.equals("all")) {
            //looking for all events worth of notes
            if(teamKey.equals("all")){
                cursor = db.query(TABLE_NOTES, new String[]{KEY_NOTEID, KEY_EVENTKEY, KEY_MATCHKEY, KEY_TEAMKEY, KEY_NOTE, KEY_NOTETIME,KEY_NOTEPARENT,KEY_NOTEPICS},
                        KEY_EVENTKEY+"=? AND "+ KEY_MATCHKEY + "=?", new String[]{eventKey,matchKey}, null, null, null, null);
            }else{
                cursor = db.query(TABLE_NOTES, new String[]{KEY_NOTEID, KEY_EVENTKEY, KEY_MATCHKEY, KEY_TEAMKEY, KEY_NOTE, KEY_NOTETIME,KEY_NOTEPARENT,KEY_NOTEPICS},
                        KEY_TEAMKEY + "=? AND " +KEY_EVENTKEY+"=? AND "+ KEY_MATCHKEY + "=?", new String[]{teamKey,eventKey,matchKey}, null, null, null, null);
            }
        } else if (teamKey.equals("")) {
            //looking for all notes on a particular match
            cursor = db.query(TABLE_NOTES, new String[]{KEY_NOTEID, KEY_EVENTKEY, KEY_MATCHKEY, KEY_TEAMKEY, KEY_NOTE, KEY_NOTETIME,KEY_NOTEPARENT,KEY_NOTEPICS},
                    KEY_EVENTKEY + "=? AND " + KEY_MATCHKEY + "=?", new String[]{eventKey, matchKey}, null, null, null, null);
        } else {
            if(teamKey.equals("all")){
                cursor = db.query(TABLE_NOTES, new String[]{KEY_NOTEID, KEY_EVENTKEY, KEY_MATCHKEY, KEY_TEAMKEY, KEY_NOTE, KEY_NOTETIME,KEY_NOTEPARENT,KEY_NOTEPICS},
                        KEY_EVENTKEY + "=? AND " + KEY_MATCHKEY + "=?", new String[]{eventKey, matchKey}, null, null, null, null);
            }else{
                cursor = db.query(TABLE_NOTES, new String[]{KEY_NOTEID, KEY_EVENTKEY, KEY_MATCHKEY, KEY_TEAMKEY, KEY_NOTE, KEY_NOTETIME,KEY_NOTEPARENT,KEY_NOTEPICS},
                        KEY_TEAMKEY + "=? AND " + KEY_EVENTKEY + "=? AND " + KEY_MATCHKEY + "=?", new String[]{teamKey, eventKey, matchKey}, null, null, null, null);
            }
        }
        //loop through rows
        if (cursor.moveToFirst()) {
            do {
                Note note = new Note();
                note.setId(cursor.getShort(0));
                note.setEventKey(cursor.getString(1));
                note.setMatchKey(cursor.getString(2));
                note.setTeamKey(cursor.getString(3));
                note.setNote(cursor.getString(4));
                note.setTimestamp(cursor.getLong(5));
                note.setParent(cursor.getShort(6));
                note.setPictures(cursor.getString(7));

                noteList.add(note);
            } while (cursor.moveToNext());
        }

        cursor.close();
        //Log.d(Constants.LOG_TAG, " FOUND " + noteList.size() + " NOTES");
        return noteList;
    }
    public ArrayList<Note> getAllMatchNotes(String teamKey, String eventKey) {
        ArrayList<Note> noteList = new ArrayList<Note>();

        Cursor cursor;
        if (!eventKey.equals("all")) {
            if(teamKey.equals("all")){
                cursor = db.query(TABLE_NOTES, new String[]{KEY_NOTEID, KEY_EVENTKEY, KEY_MATCHKEY, KEY_TEAMKEY, KEY_NOTE, KEY_NOTETIME,KEY_NOTEPARENT,KEY_NOTEPICS},
                       KEY_EVENTKEY + "=? AND " + KEY_MATCHKEY + "!=?", new String[]{eventKey, "all"}, null, null, null, null);
            }else{
                cursor = db.query(TABLE_NOTES, new String[]{KEY_NOTEID, KEY_EVENTKEY, KEY_MATCHKEY, KEY_TEAMKEY, KEY_NOTE, KEY_NOTETIME,KEY_NOTEPARENT,KEY_NOTEPICS},
                        KEY_TEAMKEY + "=? AND " + KEY_EVENTKEY + "=? AND " + KEY_MATCHKEY + "!=?", new String[]{teamKey, eventKey, "all"}, null, null, null, null);
            }
        } else {
            if(teamKey.equals("all")){
                cursor = db.query(TABLE_NOTES, new String[]{KEY_NOTEID, KEY_EVENTKEY, KEY_MATCHKEY, KEY_TEAMKEY, KEY_NOTE, KEY_NOTETIME,KEY_NOTEPARENT,KEY_NOTEPICS},
                        KEY_MATCHKEY + "!=?", new String[]{"all"}, null, null, null, null);
            }else{
                cursor = db.query(TABLE_NOTES, new String[]{KEY_NOTEID, KEY_EVENTKEY, KEY_MATCHKEY, KEY_TEAMKEY, KEY_NOTE, KEY_NOTETIME,KEY_NOTEPARENT,KEY_NOTEPICS},
                        KEY_TEAMKEY + "=? AND " + KEY_MATCHKEY + "!=?", new String[]{teamKey, "all"}, null, null, null, null);
            }
        }
        //loop through rows
        if (cursor.moveToFirst()) {
            do {
                Note note = new Note();
                note.setId(cursor.getShort(0));
                note.setEventKey(cursor.getString(1));
                note.setMatchKey(cursor.getString(2));
                note.setTeamKey(cursor.getString(3));
                note.setNote(cursor.getString(4));
                note.setTimestamp(cursor.getLong(5));

                noteList.add(note);
            } while (cursor.moveToNext());
        }

        cursor.close();
        //Log.d(Constants.LOG_TAG, " FOUND " + noteList.size() + " NOTES");
        return noteList;
    }
    public short noteExists(Note note) {
        Cursor cursor = db.query(TABLE_NOTES, new String[]{KEY_NOTE}, KEY_MATCHKEY + "=? AND " + KEY_EVENTKEY + "=? AND " + KEY_TEAMKEY + "=? AND " + KEY_NOTE + "=? AND "+KEY_NOTEPARENT+"=?",
                new String[]{note.getMatchKey(), note.getEventKey(), note.getTeamKey(), note.getNote(),Short.toString(note.getParent())}, null, null, null, null);
        if (cursor.moveToFirst())
            return cursor.getShort(0);
        else
            return -1;
    }
    public boolean noteExists(short id) {
        Cursor cursor = db.query(TABLE_NOTES, new String[]{KEY_NOTEID}, KEY_NOTEID + "=?", new String[]{Short.toString(id)}, null, null, null, null);
        return cursor.moveToFirst();
    }
    public int updateNote(Note in) {
        ContentValues values = new ContentValues();
        values.put(KEY_NOTEID, in.getId());
        values.put(KEY_EVENTKEY, in.getEventKey());
        values.put(KEY_MATCHKEY, in.getMatchKey());
        values.put(KEY_TEAMKEY, in.getTeamKey());
        values.put(KEY_NOTE, in.getNote());
        values.put(KEY_NOTETIME, in.getTimestamp());
        values.put(KEY_NOTEPARENT,in.getParent());
        values.put(KEY_NOTEPICS,in.getPictures());

        return db.update(TABLE_NOTES, values, KEY_NOTEID + " =?", new String[]{Short.toString(in.getId())});
    }
    public void deleteNote(Note in) {
        deleteNote(Short.toString(in.getId()));
    }
    public void deleteNote(String id) {
        db.delete(TABLE_NOTES, KEY_NOTEID + "=?", new String[]{id});
    }
    public void deleteNotesFromEvent(String eventKey) {
        db.delete(TABLE_NOTES, KEY_EVENTKEY + "=?", new String[]{eventKey});
    }
    public void deleteNotesFromParent(short parentId){
        db.delete(TABLE_NOTES, KEY_NOTEPARENT + "=?", new String[]{Short.toString(parentId)});
    }
    public JsonArray exportNotes() {
        JsonArray output = new JsonArray();
        String selectQuery = "SELECT * FROM " + TABLE_NOTES;
        Cursor cursor = db.rawQuery(selectQuery, null);

        //loop through rows
        if (cursor.moveToFirst()) {
            do {
                JsonObject note = new JsonObject();
                note.addProperty(KEY_NOTEID, cursor.getShort(0));
                note.addProperty(KEY_EVENTKEY, cursor.getString(1));
                note.addProperty(KEY_MATCHKEY, cursor.getString(2));
                note.addProperty(KEY_TEAMKEY, cursor.getString(3));
                note.addProperty(KEY_NOTE, cursor.getString(4));
                note.addProperty(KEY_NOTETIME, cursor.getLong(5));
                try {
                    note.addProperty(KEY_NOTEPARENT, cursor.getShort(6));
                    note.addProperty(KEY_NOTEPICS, cursor.getString(7));
                }catch(Exception e){
                    note.addProperty(KEY_NOTEPARENT,-1);
                    note.addProperty(KEY_NOTEPICS,"");
                }

                output.add(note);
            } while (cursor.moveToNext());
        }

        cursor.close();

        return output;
    }
    public void importNotes(JsonArray notes) {
        Iterator<JsonElement> iterator = notes.iterator();
        JsonObject n;
        Note note;

        while (iterator.hasNext()) {
            n = iterator.next().getAsJsonObject();
            note = new Note();

            note.setId(n.get(KEY_NOTEID).getAsShort());
            note.setEventKey(n.get(KEY_EVENTKEY).getAsString());
            note.setMatchKey(n.get(KEY_MATCHKEY).getAsString());
            note.setTeamKey(n.get(KEY_TEAMKEY).getAsString());
            note.setNote(n.get(KEY_NOTE).getAsString());
            note.setTimestamp(n.get(KEY_NOTETIME).getAsLong());
            try {
                note.setParent(n.get(KEY_NOTEPARENT).getAsShort());
                note.setPictures(n.get(KEY_NOTEPICS).getAsString());
            }catch(Exception e){
                note.setParent((short)-1);
                note.setPictures("");
            }

            addNote(note);
        }
    }

    public short addDefNote(String n){
        short existCheck = defNoteExists(n);
        if (existCheck != -1) {
            return existCheck;
        }
        ContentValues values = new ContentValues();
        values.put(KEY_DEF_NOTE, n);

        //insert the row
        if (db.insert(TABLE_PREDEF_NOTES, null, values) == -1) {
            //error, return -1
            return -1;
        } else {
            //else, return the note's ID
            Cursor cursor = db.rawQuery("SELECT MAX(" + KEY_DEF_NOTEID + ") FROM " + TABLE_PREDEF_NOTES, null);
            if (cursor.moveToFirst()) {
                return cursor.getShort(0);
            } else {
                return -1;
            }
        }
    }
    public String getDefNote(short id){
        Cursor cursor = db.query(TABLE_PREDEF_NOTES, new String[]{KEY_DEF_NOTE},
                KEY_DEF_NOTEID + "=? ", new String[]{Short.toString(id)}, null, null, null, null);

        if (cursor.moveToFirst()) {
            String out = cursor.getString(0);
            cursor.close();
            return out;
        }
        cursor.close();
        return "";
    }
    public HashMap<Short,String> getAllDefNotes(){
        HashMap<Short,String> noteList = new HashMap<Short, String>();
        String selectQuery = "SELECT * FROM " + TABLE_PREDEF_NOTES;
        Cursor cursor = db.rawQuery(selectQuery, null);

        //loop through rows
        if (cursor.moveToFirst()) {
            do {
               noteList.put(cursor.getShort(0), cursor.getString(1));
            } while (cursor.moveToNext());
        }

        cursor.close();

        return noteList;
    }
    public short defNoteExists(String n){
        Cursor cursor = db.query(TABLE_PREDEF_NOTES, new String[]{KEY_DEF_NOTEID}, KEY_DEF_NOTE + "=?",
                new String[]{n}, null, null, null, null);
        if (cursor.moveToFirst())
            return cursor.getShort(0);
        else
            return -1;
    }
    public int updateDefNote(short id,String n){
        ContentValues values = new ContentValues();
        values.put(KEY_DEF_NOTE, n);

        return db.update(TABLE_PREDEF_NOTES, values, KEY_DEF_NOTEID+ " =?", new String[]{Short.toString(id)});
    }
    public void deleteDefNote(short id){
        db.delete(TABLE_PREDEF_NOTES, KEY_DEF_NOTEID + "=?", new String[]{Short.toString(id)});
        deleteNotesFromParent(id);
    }
    public JsonArray exportDefNotes(){
        JsonArray output = new JsonArray();
        String selectQuery = "SELECT * FROM " + TABLE_PREDEF_NOTES;
        Cursor cursor = db.rawQuery(selectQuery, null);

        //loop through rows
        if (cursor.moveToFirst()) {
            do {
                JsonObject note = new JsonObject();
                note.addProperty(KEY_DEF_NOTEID, cursor.getShort(0));
                note.addProperty(KEY_DEF_NOTE,cursor.getString(1));

                output.add(note);
            } while (cursor.moveToNext());
        }

        cursor.close();

        return output;
    }
    public void importDefNotes(JsonArray notes){
        Iterator<JsonElement> iterator = notes.iterator();
        JsonObject n;

        while (iterator.hasNext()) {
            n = iterator.next().getAsJsonObject();

            addDefNote(n.get(KEY_DEF_NOTE).getAsString());
        }
    }

    public JsonObject exportDatabase() {
        return exportDatabase(true, true, true, true);
    }
    public JsonObject exportDatabase(boolean events, boolean matches, boolean teams, boolean notes) {
        JsonObject output = new JsonObject();
        if (events) {
            output.add(TABLE_EVENTS, exportEvents());
        }
        if (matches) {
            output.add(TABLE_MATCHES, exportMatches());
        }
        if (teams) {
            output.add(TABLE_TEAMS, exportTeams());
        }
        if (notes) {
            output.add(TABLE_NOTES, exportNotes());
            if(tableExists(TABLE_PREDEF_NOTES)){
                output.add(TABLE_PREDEF_NOTES,exportDefNotes());
            }
        }
        return output;
    }
    public void importDatabase(JsonObject data) {
        if(data == null || data.equals(new JsonObject())) return;
        JsonElement e;

        e = data.get(TABLE_EVENTS);
        if(e != null)
            importEvents(e.getAsJsonArray());

        e = data.get(TABLE_MATCHES);
        if(e != null)
            importMatches(e.getAsJsonArray());

        e = data.get(TABLE_TEAMS);
        if(e != null)
            importTeams(e.getAsJsonArray());

        e = data.get(TABLE_NOTES);
        if(e != null)
            importNotes(e.getAsJsonArray());

        if(tableExists(TABLE_PREDEF_NOTES)){
            try {
                e = data.get(TABLE_PREDEF_NOTES);
                if(e != null)
                    importDefNotes(e.getAsJsonArray());
            }catch (Exception ex){

            }
        }
    }

    public boolean tableExists(String table){
        Cursor cursor = db.rawQuery("SELECT DISTINCT tbl_name from sqlite_master where tbl_name = '"+table+"'", null);
        if(cursor!=null && cursor.getCount()>0) {
            cursor.close();
            return true;
        }
        return false;
    }
    private boolean columnExists(SQLiteDatabase inDatabase, String inTable, String columnToCheck) {
        try{
            //query 1 row
            Cursor mCursor  = inDatabase.rawQuery( "SELECT * FROM " + inTable + " LIMIT 0", null );

            //getColumnIndex gives us the index (0 to ...) of the column - otherwise we get a -1
            if(mCursor.getColumnIndex(columnToCheck) != -1)
                return true;
            else
                return false;

        }catch (Exception Exp){
            //something went wrong. Missing the database? The table?
            Log.d("... - existsColumnInTable","When checking whether a column exists in the table, an error occurred: " + Exp.getMessage());
            return false;
        }
    }
}




Java Source Code List

.BasicTest.java
.TestStartActivity.java
com.plnyyanks.frcnotebook.Constants.java
com.plnyyanks.frcnotebook.activities.AddEvent.java
com.plnyyanks.frcnotebook.activities.EventDownloadActivity.java
com.plnyyanks.frcnotebook.activities.FieldMonitorActivity.java
com.plnyyanks.frcnotebook.activities.PredefinedNoteManager.java
com.plnyyanks.frcnotebook.activities.SettingsActivity.java
com.plnyyanks.frcnotebook.activities.StartActivity.java
com.plnyyanks.frcnotebook.activities.ViewEvent.java
com.plnyyanks.frcnotebook.activities.ViewMatch.java
com.plnyyanks.frcnotebook.activities.ViewTeam.java
com.plnyyanks.frcnotebook.adapters.ActionBarCallback.java
com.plnyyanks.frcnotebook.adapters.AdapterInterface.java
com.plnyyanks.frcnotebook.adapters.AllianceExpandableListAdapter.java
com.plnyyanks.frcnotebook.adapters.CustomExapandableListAdapter.java
com.plnyyanks.frcnotebook.adapters.ListViewArrayAdapter.java
com.plnyyanks.frcnotebook.adapters.MatchListExpandableListAdapter.java
com.plnyyanks.frcnotebook.adapters.NotesExpandableListAdapter.java
com.plnyyanks.frcnotebook.background.AddMatchesFromURL.java
com.plnyyanks.frcnotebook.background.DeleteEvent.java
com.plnyyanks.frcnotebook.background.GetEventMatches.java
com.plnyyanks.frcnotebook.background.GetNotesForMatch.java
com.plnyyanks.frcnotebook.background.GetNotesForTeam.java
com.plnyyanks.frcnotebook.background.GetTeamsAttending.java
com.plnyyanks.frcnotebook.background.ShowLocalEvents.java
com.plnyyanks.frcnotebook.background.ValidateNewEventData.java
com.plnyyanks.frcnotebook.database.BackupDatabase.java
com.plnyyanks.frcnotebook.database.DatabaseHandler.java
com.plnyyanks.frcnotebook.database.ImportDatabase.java
com.plnyyanks.frcnotebook.database.PreferenceHandler.java
com.plnyyanks.frcnotebook.datafeed.EventDetailFetcher.java
com.plnyyanks.frcnotebook.datafeed.EventListFetcher.java
com.plnyyanks.frcnotebook.datafeed.GET_Request.java
com.plnyyanks.frcnotebook.datafeed.MATCH_PROPS.java
com.plnyyanks.frcnotebook.datafeed.MatchDetailFetcher.java
com.plnyyanks.frcnotebook.datafeed.TBADatafeed.java
com.plnyyanks.frcnotebook.datafeed.USFIRSTParser.java
com.plnyyanks.frcnotebook.datatypes.Event.java
com.plnyyanks.frcnotebook.datatypes.ListElement.java
com.plnyyanks.frcnotebook.datatypes.ListGroup.java
com.plnyyanks.frcnotebook.datatypes.ListHeader.java
com.plnyyanks.frcnotebook.datatypes.ListItem.java
com.plnyyanks.frcnotebook.datatypes.Match.java
com.plnyyanks.frcnotebook.datatypes.Note.java
com.plnyyanks.frcnotebook.datatypes.Team.java
com.plnyyanks.frcnotebook.dialogs.AddNoteDialog.java
com.plnyyanks.frcnotebook.dialogs.AddPredefNoteDialog.java
com.plnyyanks.frcnotebook.dialogs.DatePickerFragment.java
com.plnyyanks.frcnotebook.dialogs.DeleteDialog.java
com.plnyyanks.frcnotebook.dialogs.EditNoteDialog.java
com.plnyyanks.frcnotebook.dialogs.EditPredefNoteDialog.java
com.plnyyanks.frcnotebook.dialogs.InputURLForMatchesDialog.java
com.plnyyanks.frcnotebook.dialogs.ProgressDialog.java
com.plnyyanks.frcnotebook.json.JSONManager.java