Java tutorial
/* * Copyright (C) 2010 Christian Schneider * * This file is part of NS2 news. * * NS2 news 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. * * NS2 news 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 NS2 news. If not, see <http://www.gnu.org/licenses/>. * */ package tritop.android.naturalselectionnews; import java.util.ArrayList; import java.util.Iterator; import org.json.JSONException; import org.json.JSONObject; import android.content.ContentValues; import android.content.Context; import android.database.Cursor; import android.database.sqlite.SQLiteConstraintException; import android.database.sqlite.SQLiteDatabase; import android.database.sqlite.SQLiteDatabase.CursorFactory; import android.database.sqlite.SQLiteOpenHelper; import android.database.sqlite.SQLiteStatement; import android.util.Log; public class DBHelper extends SQLiteOpenHelper implements IGLOBALS { public static final String DATABASE_NAME = "tsa_stats.db"; private static final int DATABASE_VERSION = 1; public static final String WAR_STATS_DATA_DATE = "date"; public static final String WAR_STATS_DATA_MAP = "map"; public static final String WAR_STATS_DATA_LENGTH = "length"; public static final String WAR_STATS_DATA_WINNER = "winner"; public static final String WAR_STATS_DATA_VERSION = "version"; public static final String KILL_STATS_DATA_MAP = "map"; public static final String KILL_STATS_DATA_ATTACKER_TEAM = "attacker_team"; public static final String KILL_STATS_DATA_ATTACKER_Z = "attackerz"; public static final String KILL_STATS_DATA_TARGET_WEAPON = "target_weapon"; public static final String KILL_STATS_DATA_TARGET_TYPE = "target_type"; public static final String KILL_STATS_DATA_TARGET_ATTACKER_TYPE = "attacker_type"; public static final String KILL_STATS_DATA_TARGET_VERSION = "version"; public static final String KILL_STATS_DATA_TARGET_Z = "targetz"; public static final String KILL_STATS_DATA_TARGET_X = "targetx"; public static final String KILL_STATS_DATA_TARGET_Y = "targety"; public static final String KILL_STATS_DATA_DATE = "date"; public static final String KILL_STATS_DATA_TARGET_LIFETIME = "target_lifetime"; public static final String KILL_STATS_DATA_TARGET_TEAM = "target_team"; public static final String KILL_STATS_DATA_ATTACKER_Y = "attackery"; public static final String KILL_STATS_DATA_ATTACKER_X = "attackerx"; public static final String KILL_STATS_DATA_ATTACKER_WEAPON = "attacker_weapon"; public static final String NEWS_FEED_DATA_TITLE = "title"; public static final String NEWS_FEED_DATA_LINK = "link"; public static final String NEWS_FEED_DATA_DATE = "updated"; public static final String TWITTER_FEED_DATA_TITLE = "title"; public static final String TWITTER_FEED_DATA_GUID = "guid"; public static final String TWITTER_FEED_DATA_DATE = "pubdate"; private static final String WAR_STATS_TABLE_NAME = "warstats"; private static final String CREATE_WAR_STATS_TABLE_STATEMENT = "CREATE TABLE IF NOT EXISTS " + WAR_STATS_TABLE_NAME + "(id integer PRIMARY KEY AUTOINCREMENT," + WAR_STATS_DATA_DATE + " integer," + WAR_STATS_DATA_MAP + " varchar(100)," + WAR_STATS_DATA_LENGTH + " float," + WAR_STATS_DATA_WINNER + " integer," + WAR_STATS_DATA_VERSION + " float," + "CONSTRAINT uc_warstats UNIQUE(" + WAR_STATS_DATA_DATE + "," + WAR_STATS_DATA_LENGTH + "))"; private static final String KILL_STATS_TABLE_NAME = "killstats"; private static final String CREATE_KILL_STATS_TABLE_STATEMENT = "CREATE TABLE IF NOT EXISTS " + KILL_STATS_TABLE_NAME + "(id integer PRIMARY KEY AUTOINCREMENT," + KILL_STATS_DATA_MAP + " VARCHAR(100)," + KILL_STATS_DATA_ATTACKER_TEAM + " INTEGER," + KILL_STATS_DATA_ATTACKER_Z + " FLOAT," + KILL_STATS_DATA_TARGET_WEAPON + " VARCHAR(100)," + KILL_STATS_DATA_TARGET_TYPE + " VARCHAR(100)," + KILL_STATS_DATA_TARGET_ATTACKER_TYPE + " VARCHAR(100)," + KILL_STATS_DATA_TARGET_VERSION + " FLOAT," + KILL_STATS_DATA_TARGET_Z + " FLOAT," + KILL_STATS_DATA_TARGET_X + " FLOAT," + KILL_STATS_DATA_TARGET_Y + " FLOAT," + KILL_STATS_DATA_DATE + " INTEGER," + KILL_STATS_DATA_TARGET_LIFETIME + " FLOAT," + KILL_STATS_DATA_TARGET_TEAM + " INTEGER," + KILL_STATS_DATA_ATTACKER_Y + " FLOAT," + KILL_STATS_DATA_ATTACKER_X + " FLOAT," + KILL_STATS_DATA_ATTACKER_WEAPON + " VARCHAR(100)," + "CONSTRAINT uc_killstats UNIQUE(" + KILL_STATS_DATA_DATE + "," + KILL_STATS_DATA_TARGET_LIFETIME + "))"; private static final String NEWS_FEED_TABLE_NAME = "newsfeed"; private static final String CREATE_NEWS_FEED_TABLE_STATEMENT = "CREATE TABLE IF NOT EXISTS " + NEWS_FEED_TABLE_NAME + "(id integer PRIMARY KEY AUTOINCREMENT," + NEWS_FEED_DATA_TITLE + " varchar(1000)," + NEWS_FEED_DATA_LINK + " varchar(500)," + NEWS_FEED_DATA_DATE + " integer UNIQUE" + ")"; private static final String TWITTER_FEED_TABLE_NAME = "twitterfeed"; private static final String CREATE_TWITTER_FEED_TABLE_STATEMENT = "CREATE TABLE IF NOT EXISTS " + TWITTER_FEED_TABLE_NAME + "(id integer PRIMARY KEY AUTOINCREMENT," + TWITTER_FEED_DATA_TITLE + " varchar(200)," + TWITTER_FEED_DATA_GUID + " integer," + TWITTER_FEED_DATA_DATE + " integer UNIQUE" + ")"; private static final String LOGTAG = "DBHelper"; private SQLiteDatabase mDB; private SQLiteStatement mInsertKillStatement; private SQLiteStatement mInsertWarStatement; private SQLiteStatement mInsertNewsStatement; private SQLiteStatement mInsertTwitterStatement; private SQLiteStatement mTweetMaxIdStm; private SQLiteStatement mWarWhoIsWinningStm; private static ArrayList<String> killColumns = new ArrayList<String>(); static { killColumns.add(KILL_STATS_DATA_MAP); killColumns.add(KILL_STATS_DATA_ATTACKER_TEAM); killColumns.add(KILL_STATS_DATA_ATTACKER_Z); killColumns.add(KILL_STATS_DATA_TARGET_WEAPON); killColumns.add(KILL_STATS_DATA_TARGET_TYPE); killColumns.add(KILL_STATS_DATA_TARGET_ATTACKER_TYPE); killColumns.add(KILL_STATS_DATA_TARGET_VERSION); killColumns.add(KILL_STATS_DATA_TARGET_Z); killColumns.add(KILL_STATS_DATA_TARGET_X); killColumns.add(KILL_STATS_DATA_TARGET_Y); killColumns.add(KILL_STATS_DATA_DATE); killColumns.add(KILL_STATS_DATA_TARGET_LIFETIME); killColumns.add(KILL_STATS_DATA_TARGET_TEAM); killColumns.add(KILL_STATS_DATA_ATTACKER_Y); killColumns.add(KILL_STATS_DATA_ATTACKER_X); killColumns.add(KILL_STATS_DATA_ATTACKER_WEAPON); } public DBHelper(Context context) { super(context, DATABASE_NAME, null, DATABASE_VERSION); if (DEBUG_ON) { Log.e(LOGTAG, "On Constructor "); } mDB = getWritableDatabase(); mInsertWarStatement = mDB.compileStatement("INSERT INTO " + WAR_STATS_TABLE_NAME + " (" + WAR_STATS_DATA_DATE + "," + WAR_STATS_DATA_MAP + "," + WAR_STATS_DATA_LENGTH + "," + WAR_STATS_DATA_WINNER + "," + WAR_STATS_DATA_VERSION + ") VALUES (?,?,?,?,?)"); mInsertNewsStatement = mDB .compileStatement("INSERT INTO " + NEWS_FEED_TABLE_NAME + " (" + NEWS_FEED_DATA_TITLE + "," + NEWS_FEED_DATA_LINK + "," + NEWS_FEED_DATA_DATE + ") VALUES (?,?,?)"); mInsertTwitterStatement = mDB .compileStatement("INSERT INTO " + TWITTER_FEED_TABLE_NAME + " (" + TWITTER_FEED_DATA_TITLE + "," + TWITTER_FEED_DATA_GUID + "," + TWITTER_FEED_DATA_DATE + ") VALUES (?,?,?)"); mTweetMaxIdStm = mDB .compileStatement("SELECT MAX(" + TWITTER_FEED_DATA_GUID + ") FROM " + TWITTER_FEED_TABLE_NAME); mWarWhoIsWinningStm = mDB.compileStatement("SELECT COUNT(*) FROM " + WAR_STATS_TABLE_NAME + " WHERE " + WAR_STATS_DATA_WINNER + "=? AND " + WAR_STATS_DATA_VERSION + "=?"); buildKillStatement(); } //********************************************************* // Helper builds INSERT statement for the killstats //********************************************************* private void buildKillStatement() { StringBuilder sbFields = new StringBuilder(); StringBuilder sbQM = new StringBuilder(); sbFields.append(killColumns.get(0)); sbQM.append("?"); for (int i = 1; i < killColumns.size(); i++) { sbFields.append(","); sbQM.append(","); sbFields.append(killColumns.get(i)); sbQM.append("?"); } //Log.e(LOGTAG,sbFields.toString()); //Log.e(LOGTAG,sbQM.toString()); mInsertKillStatement = mDB.compileStatement("INSERT INTO " + KILL_STATS_TABLE_NAME + " (" + sbFields.toString() + ") VALUES (" + sbQM.toString() + ")"); } @Override public void onCreate(SQLiteDatabase db) { if (DEBUG_ON) { Log.e(LOGTAG, "On Create "); } db.execSQL(CREATE_WAR_STATS_TABLE_STATEMENT); db.execSQL(CREATE_KILL_STATS_TABLE_STATEMENT); db.execSQL(CREATE_NEWS_FEED_TABLE_STATEMENT); db.execSQL(CREATE_TWITTER_FEED_TABLE_STATEMENT); } //******************************************************************** // Upgrade from an older DB version to a newer (delete old create new) //******************************************************************** @Override public void onUpgrade(SQLiteDatabase db, int arg1, int arg2) { if (DEBUG_ON) { Log.e(LOGTAG, "On Upgrade "); } db.execSQL("DROP TABLE IF EXISTS " + WAR_STATS_TABLE_NAME); db.execSQL("DROP TABLE IF EXISTS " + KILL_STATS_TABLE_NAME); db.execSQL("DROP TABLE IF EXISTS " + NEWS_FEED_TABLE_NAME); db.execSQL("DROP TABLE IF EXISTS " + TWITTER_FEED_TABLE_NAME); onCreate(db); } //***************************************************************************** // Insert values in warstats table this happens inside a beginTransaction block //***************************************************************************** public synchronized void insertBulkWarStatsValues(long date, String map, double length, int winner, double version) { try { if (DEBUG_ON) { Log.e(LOGTAG, "BULK inserting " + map + " winner" + winner + " date" + date + " length" + length + " version" + version); } mInsertWarStatement.bindLong(1, date); mInsertWarStatement.bindString(2, map); mInsertWarStatement.bindDouble(3, length); mInsertWarStatement.bindLong(4, winner); mInsertWarStatement.bindDouble(5, version); mInsertWarStatement.executeInsert(); } catch (SQLiteConstraintException e) { if (DEBUG_ON) { Log.e(LOGTAG, "BULK war inserting exception "); } } } //***************************************************************************** // Insert values in news table this happens inside a beginTransaction block //***************************************************************************** public synchronized void insertBulkNewsValues(String title, String link, long date) { try { if (DEBUG_ON) { Log.e(LOGTAG, "BULK inserting " + title + " date" + date + " link" + link); } mInsertNewsStatement.bindString(1, title); mInsertNewsStatement.bindString(2, link); mInsertNewsStatement.bindLong(3, date); mInsertNewsStatement.executeInsert(); } catch (SQLiteConstraintException e) { if (DEBUG_ON) { Log.e(LOGTAG, "BULK news inserting exception "); } } } //***************************************************************************** // Insert values in tweet table this happens inside a beginTransaction block //***************************************************************************** public synchronized void insertBulkTwitterValues(String title, long guid, long date) { try { if (DEBUG_ON) { Log.e(LOGTAG, "BULK inserting " + title + " date" + date + " link" + guid); } mInsertTwitterStatement.bindString(1, title); mInsertTwitterStatement.bindLong(2, guid); mInsertTwitterStatement.bindLong(3, date); mInsertTwitterStatement.executeInsert(); } catch (SQLiteConstraintException e) { if (DEBUG_ON) { Log.e(LOGTAG, "BULK news inserting exception "); } } } //***************************************************************************** // Insert values in killstats table this happens inside a beginTransaction block //***************************************************************************** public synchronized void insertBulkKillStatsValues(JSONObject killDataObject) { try { if (DEBUG_ON) { Log.e(LOGTAG, "bulk inserting kill"); } mInsertKillStatement.clearBindings(); Iterator<String> keys = killDataObject.keys(); while (keys.hasNext()) { String key = keys.next(); if (key != null && key.length() > 0) { String value; try { value = killDataObject.getString(key); mInsertKillStatement.bindString(killColumns.indexOf(key) + 1, value); } catch (JSONException e) { e.printStackTrace(); } } } mInsertKillStatement.executeInsert(); } catch (SQLiteConstraintException e) { if (DEBUG_ON) { Log.e(LOGTAG, "BULK kill Constraint exception "); } } } public void beginBulkWork() { if (DEBUG_ON) { Log.e(LOGTAG, "Start BULK inserting "); } mDB.beginTransaction(); } public void endBulkWork() { if (DEBUG_ON) { Log.e(LOGTAG, "End BULK inserting "); } mDB.setTransactionSuccessful(); mDB.endTransaction(); } public synchronized void insertWarStatsValues(long date, String map, double length, int winner, double version) { if (DEBUG_ON) { Log.e(LOGTAG, "inserting " + map + " winner" + winner); } ContentValues val = new ContentValues(); val.put(WAR_STATS_DATA_DATE, date); val.put(WAR_STATS_DATA_MAP, map); val.put(WAR_STATS_DATA_LENGTH, length); val.put(WAR_STATS_DATA_WINNER, winner); val.put(WAR_STATS_DATA_VERSION, version); insertWarStatsValues(val); } public synchronized void insertWarStatsValues(ContentValues val) { mDB.insert(WAR_STATS_TABLE_NAME, null, val); } public synchronized void insertKillStatsValues(JSONObject killDataObject) { if (DEBUG_ON) { Log.e(LOGTAG, "inserting kill"); } ContentValues val = new ContentValues(); Iterator<String> keys = killDataObject.keys(); while (keys.hasNext()) { String key = keys.next(); if (key != null && key.length() > 0) { String value; try { value = killDataObject.getString(key); val.put(key, value); } catch (JSONException e) { e.printStackTrace(); } } } insertKillStatsValues(val); } public synchronized void insertKillStatsValues(ContentValues val) { mDB.insert(KILL_STATS_TABLE_NAME, null, val); } public synchronized void insertNewsFeedValues(ContentValues val) { mDB.insert(NEWS_FEED_TABLE_NAME, null, val); } //***************************************************************************** // returns the guid of the latest tweet we saved //***************************************************************************** public synchronized long getMaxTwitterGuid() { long guid = 1; guid = mTweetMaxIdStm.simpleQueryForLong(); if (DEBUG_ON) { Log.e(LOGTAG, "getMAxTwitterGuid: " + guid); } guid = (guid < 1) ? 1 : guid; return guid; } //***************************************************************************** // return how often has team x won in build y //***************************************************************************** public synchronized long getTeamWins(int team, int build) { long wins = 0; mWarWhoIsWinningStm.bindLong(1, team); mWarWhoIsWinningStm.bindLong(2, build); wins = mWarWhoIsWinningStm.simpleQueryForLong(); if (DEBUG_ON) { Log.e(LOGTAG, "Team :" + team + "Wins: " + wins); } wins = (wins < 0) ? 0 : wins; return wins; } //***************************************************************************** // get the last 5 Tweets //***************************************************************************** public synchronized String[] getTweets() { String[] tweets = null; if (!mDB.isOpen()) { mDB = getWritableDatabase(); } Cursor cur = mDB.query(TWITTER_FEED_TABLE_NAME, new String[] { TWITTER_FEED_DATA_TITLE }, null, null, null, null, " " + TWITTER_FEED_DATA_GUID + " DESC ", " 5 "); if (cur != null) { cur.moveToFirst(); ArrayList list = new ArrayList<String>(); while (cur.isAfterLast() == false) { list.add(cur.getString(0)); cur.moveToNext(); } cur.close(); tweets = (String[]) list.toArray(new String[list.size()]); } return tweets; } @Override public synchronized void close() { if (mDB != null) { mDB.close(); } super.close(); } //***************************************************************************** // Delete Data older than x days //***************************************************************************** public synchronized void logrotate(int deleteDays) { long timeNow = System.currentTimeMillis(); long deleteBefore = deleteDays * MILLIS_PER_DAY; deleteOldWarstats((timeNow - deleteBefore)); deleteOldKillstats((timeNow - deleteBefore)); } public synchronized void deleteOldWarstats(long timestamp) { mDB.delete(WAR_STATS_TABLE_NAME, WAR_STATS_DATA_DATE + "<" + timestamp, null); } public synchronized void deleteOldKillstats(long timestamp) { mDB.delete(KILL_STATS_TABLE_NAME, KILL_STATS_DATA_DATE + "<" + timestamp, null); } }