diff --git a/src/it/reyboz/bustorino/middleware/DatabaseUpdateService.java b/src/it/reyboz/bustorino/middleware/DatabaseUpdateService.java index 83a89ce..fe80056 100644 --- a/src/it/reyboz/bustorino/middleware/DatabaseUpdateService.java +++ b/src/it/reyboz/bustorino/middleware/DatabaseUpdateService.java @@ -1,259 +1,270 @@ /* BusTO (middleware) Copyright (C) 2018 Fabio Mazza This program 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. This program 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 this program. If not, see . */ package it.reyboz.bustorino.middleware; import android.app.IntentService; import android.content.*; import android.database.sqlite.SQLiteDatabase; +import android.support.annotation.Nullable; import android.util.Log; import it.reyboz.bustorino.ActivityMain; import it.reyboz.bustorino.R; import it.reyboz.bustorino.backend.Fetcher; import it.reyboz.bustorino.backend.FiveTAPIFetcher; import it.reyboz.bustorino.backend.Route; import it.reyboz.bustorino.backend.Stop; import org.json.JSONException; import org.json.JSONObject; import java.lang.ref.WeakReference; import java.util.ArrayList; import java.util.concurrent.atomic.AtomicReference; import static it.reyboz.bustorino.middleware.NextGenDB.Contract.*; /** * An {@link IntentService} subclass for handling asynchronous task requests in * a service on a separate handler thread. */ public class DatabaseUpdateService extends IntentService { // IntentService can perform, e.g. ACTION_FETCH_NEW_ITEMS private static final String ACTION_UPDATE = "it.reyboz.bustorino.middleware.action.UPDATE_DB"; private static final String DB_VERSION = "NextGenDB.GTTVersion"; private static final String DEBUG_TAG = "DatabaseService_BusTO"; // TODO: Rename parameters private static final String TRIAL = "it.reyboz.bustorino.middleware.extra.TRIAL"; + private static final String COMPULSORY = "compulsory_update"; private static final int MAX_TRIALS = 5; + private static final int VERSION_UNAIVALABLE = -2; public DatabaseUpdateService() { super("DatabaseUpdateService"); } private boolean isRunning; private int updateTrial; /** * Starts this service to perform action Foo with the given parameters. If * the service is already performing a task this action will be queued. * * @see IntentService */ - public static void startDBUpdate(Context con, int trial){ + public static void startDBUpdate(Context con, int trial, @Nullable Boolean mustUpdate){ Intent intent = new Intent(con, DatabaseUpdateService.class); intent.setAction(ACTION_UPDATE); intent.putExtra(TRIAL,trial); + if(mustUpdate!=null){ + intent.putExtra(COMPULSORY,mustUpdate); + } con.startService(intent); } public static void startDBUpdate(Context con) { - startDBUpdate(con,0); + startDBUpdate(con, 0, false); } - @Override + @Override protected void onHandleIntent(Intent intent) { if (intent != null) { final String action = intent.getAction(); if (ACTION_UPDATE.equals(action)) { Log.d(DEBUG_TAG,"Started action update"); SharedPreferences shPr = getSharedPreferences(getString(R.string.mainSharedPreferences),MODE_PRIVATE); int versionDB = shPr.getInt(DB_VERSION,-1); final int trial = intent.getIntExtra(TRIAL,-1); updateTrial = trial; - - int newVersion = getNewVersion(trial); + UpdateRequestParams params = new UpdateRequestParams(intent); + int newVersion = getNewVersion(params); + if(newVersion==VERSION_UNAIVALABLE){ + //NOTHING LEFT TO DO + return; + } Log.d(DEBUG_TAG,"newDBVersion: "+newVersion+" oldVersion: "+versionDB); - if(versionDB==-1 || newVersion>versionDB){ + if(params.mustUpdate || versionDB==-1 || newVersion>versionDB){ final SharedPreferences.Editor editor = shPr.edit(); editor.putBoolean(getString(R.string.databaseUpdatingPref),true); editor.commit(); Log.d(DEBUG_TAG,"Downloading the bus stops info"); final AtomicReference gres = new AtomicReference<>(); - if(!performDBUpdate(gres)) restartDBUpdateifPossible(trial,gres); - /*switch (gres.get()){ - case SERVER_ERROR: - restartDBUpdateifPossible(trial); - break; - case PARSER_ERROR: - - break; - case EMPTY_RESULT_SET: - break; - case QUERY_TOO_SHORT: - break; - case SERVER_ERROR_404: - break; - }*/ + if(!performDBUpdate(gres)) + restartDBUpdateifPossible(params,gres); else { editor.putInt(DB_VERSION,newVersion); // BY COMMENTING THIS, THE APP WILL CONTINUOUSLY UPDATE THE DATABASE editor.apply(); } } else { Log.d(DEBUG_TAG,"No update needed"); } Log.d(DEBUG_TAG,"Finished update"); final SharedPreferences.Editor editor = shPr.edit(); editor.putBoolean(getString(R.string.databaseUpdatingPref),false); editor.commit(); } } } private boolean performDBUpdate(AtomicReference gres){ final FiveTAPIFetcher f = new FiveTAPIFetcher(); final ArrayList stops = f.getAllStopsFromGTT(gres); //final ArrayList cpOp = new ArrayList<>(); if(gres.get()!= Fetcher.result.OK){ Log.w(DEBUG_TAG,"Something went wrong downloading"); return false; } final NextGenDB dbHelp = new NextGenDB(getApplicationContext()); final SQLiteDatabase db = dbHelp.getWritableDatabase(); //Empty the needed tables db.beginTransaction(); //db.execSQL("DELETE FROM "+StopsTable.TABLE_NAME); //db.delete(LinesTable.TABLE_NAME,null,null); //put new data long startTime = System.currentTimeMillis(); Log.d(DEBUG_TAG,"Inserting "+stops.size()+" stops"); for (final Stop s : stops) { final ContentValues cv = new ContentValues(); cv.put(StopsTable.COL_ID, s.ID); cv.put(StopsTable.COL_NAME, s.getStopDefaultName()); if (s.location != null) cv.put(StopsTable.COL_LOCATION, s.location); cv.put(StopsTable.COL_LAT, s.getLatitude()); cv.put(StopsTable.COL_LONG, s.getLongitude()); if (s.getAbsurdGTTPlaceName() != null) cv.put(StopsTable.COL_PLACE, s.getAbsurdGTTPlaceName()); cv.put(StopsTable.COL_LINES_STOPPING, s.routesThatStopHereToString()); if (s.type != null) cv.put(StopsTable.COL_TYPE, s.type.getCode()); //Log.d(DEBUG_TAG,cv.toString()); //cpOp.add(ContentProviderOperation.newInsert(uritobeused).withValues(cv).build()); //valuesArr[i] = cv; db.replace(StopsTable.TABLE_NAME,null,cv); } db.setTransactionSuccessful(); db.endTransaction(); long endTime = System.currentTimeMillis(); Log.d(DEBUG_TAG,"Inserting stops took: "+((double) (endTime-startTime)/1000)+" s"); final ArrayList routes = f.getAllLinesFromGTT(gres); if(routes==null){ Log.w(DEBUG_TAG,"Something went wrong downloading the lines"); dbHelp.close(); return false; } db.beginTransaction(); startTime = System.currentTimeMillis(); for (Route r: routes){ final ContentValues cv = new ContentValues(); cv.put(LinesTable.COLUMN_NAME,r.name); switch (r.type){ case BUS: cv.put(LinesTable.COLUMN_TYPE,"URBANO"); break; case RAILWAY: cv.put(LinesTable.COLUMN_TYPE,"FERROVIA"); break; case LONG_DISTANCE_BUS: cv.put(LinesTable.COLUMN_TYPE,"EXTRA"); break; } cv.put(LinesTable.COLUMN_DESCRIPTION,r.description); //db.insert(LinesTable.TABLE_NAME,null,cv); int rows = db.update(LinesTable.TABLE_NAME,cv,LinesTable.COLUMN_NAME+" = ?",new String[]{r.name}); if(rows<1){ //we haven't changed anything db.insert(LinesTable.TABLE_NAME,null,cv); } } db.setTransactionSuccessful(); db.endTransaction(); endTime = System.currentTimeMillis(); Log.d(DEBUG_TAG,"Inserting lines took: "+((double) (endTime-startTime)/1000)+" s"); dbHelp.close(); return true; } - private int getNewVersion(int trial){ + private int getNewVersion(UpdateRequestParams params){ AtomicReference gres = new AtomicReference<>(); String networkRequest = FiveTAPIFetcher.performAPIRequest(FiveTAPIFetcher.QueryType.STOPS_VERSION,null,gres); if(networkRequest == null){ - restartDBUpdateifPossible(trial,gres); - return -2; + restartDBUpdateifPossible(params,gres); + return VERSION_UNAIVALABLE; } boolean needed; try { JSONObject resp = new JSONObject(networkRequest); return resp.getInt("id"); } catch (JSONException e) { e.printStackTrace(); Log.e(DEBUG_TAG,"Error: wrong JSON response\nResponse:\t"+networkRequest); - return -2; + return -4; + } + } + private void restartDBUpdateifPossible(UpdateRequestParams pars, AtomicReference res){ + if (pars.trial res){ - if (currentTrial. */ package it.reyboz.bustorino.middleware; import android.content.ContentValues; import android.content.Context; +import android.content.SharedPreferences; import android.database.sqlite.SQLiteConstraintException; import android.database.sqlite.SQLiteDatabase; import android.database.sqlite.SQLiteException; import android.database.sqlite.SQLiteOpenHelper; -import android.graphics.LinearGradient; import android.provider.BaseColumns; import android.util.Log; +import it.reyboz.bustorino.R; import static it.reyboz.bustorino.middleware.NextGenDB.Contract.*; public class NextGenDB extends SQLiteOpenHelper{ public static final String DATABASE_NAME = "bustodatabase.db"; - public static final int DATABASE_VERSION = 1; + public static final int DATABASE_VERSION = 2; //Some generating Strings private static final String SQL_CREATE_LINES_TABLE="CREATE TABLE "+Contract.LinesTable.TABLE_NAME+" ("+ Contract.LinesTable._ID +" INTEGER PRIMARY KEY AUTOINCREMENT, "+ Contract.LinesTable.COLUMN_NAME +" TEXT, "+ Contract.LinesTable.COLUMN_DESCRIPTION +" TEXT, "+Contract.LinesTable.COLUMN_TYPE +" TEXT, "+ "UNIQUE ("+LinesTable.COLUMN_NAME+","+LinesTable.COLUMN_DESCRIPTION+","+LinesTable.COLUMN_TYPE+" ) "+" )"; private static final String SQL_CREATE_BRANCH_TABLE="CREATE TABLE "+Contract.BranchesTable.TABLE_NAME+" ("+ Contract.BranchesTable._ID +" INTEGER, "+ Contract.BranchesTable.COL_BRANCHID +" INTEGER PRIMARY KEY, "+ Contract.BranchesTable.COL_LINE +" INTEGER, "+ Contract.BranchesTable.COL_DESCRIPTION +" TEXT, "+ Contract.BranchesTable.COL_DIRECTION+" TEXT, "+ Contract.BranchesTable.COL_TYPE +" INTEGER, "+ //SERVICE DAYS: 0 => FERIALE,1=>FESTIVO,-1=>UNKNOWN,add others if necessary Contract.BranchesTable.COL_FESTIVO +" INTEGER, "+ //DAYS COLUMNS. IT'S SO TEDIOUS I TRIED TO KILL MYSELF BranchesTable.COL_LUN+" INTEGER, "+BranchesTable.COL_MAR+" INTEGER, "+BranchesTable.COL_MER+" INTEGER, "+BranchesTable.COL_GIO+" INTEGER, "+ BranchesTable.COL_VEN+" INTEGER, "+ BranchesTable.COL_SAB+" INTEGER, "+BranchesTable.COL_DOM+" INTEGER, "+ "FOREIGN KEY("+ Contract.BranchesTable.COL_LINE +") references "+ Contract.LinesTable.TABLE_NAME+"("+ Contract.LinesTable._ID+") " +")"; private static final String SQL_CREATE_CONNECTIONS_TABLE="CREATE TABLE "+Contract.ConnectionsTable.TABLE_NAME+" ("+ Contract.ConnectionsTable.COLUMN_BRANCH+" INTEGER, "+ Contract.ConnectionsTable.COLUMN_STOP_ID+" TEXT, "+ Contract.ConnectionsTable.COLUMN_ORDER+" INTEGER, "+ "PRIMARY KEY ("+ Contract.ConnectionsTable.COLUMN_BRANCH+","+ Contract.ConnectionsTable.COLUMN_STOP_ID + "), "+ "FOREIGN KEY("+ Contract.ConnectionsTable.COLUMN_BRANCH+") references "+ Contract.BranchesTable.TABLE_NAME+"("+ Contract.BranchesTable.COL_BRANCHID +"), "+ "FOREIGN KEY("+ Contract.ConnectionsTable.COLUMN_STOP_ID+") references "+ Contract.StopsTable.TABLE_NAME+"("+ Contract.StopsTable.COL_ID +") " +")"; private static final String SQL_CREATE_STOPS_TABLE="CREATE TABLE "+Contract.StopsTable.TABLE_NAME+" ("+ Contract.StopsTable.COL_ID+" TEXT PRIMARY KEY, "+ Contract.StopsTable.COL_TYPE+" INTEGER, "+Contract.StopsTable.COL_LAT+" REAL NOT NULL, "+ Contract.StopsTable.COL_LONG+" REAL NOT NULL, "+ Contract.StopsTable.COL_NAME+" TEXT NOT NULL, "+ Contract.StopsTable.COL_LOCATION+" TEXT, "+Contract.StopsTable.COL_PLACE+" TEXT, "+ Contract.StopsTable.COL_LINES_STOPPING +" TEXT )"; + private Context appContext; public NextGenDB(Context context) { super(context, DATABASE_NAME, null, DATABASE_VERSION); + appContext = context.getApplicationContext(); } @Override public void onCreate(SQLiteDatabase db) { Log.d("BusTO-AppDB","Lines creating database:\n"+SQL_CREATE_LINES_TABLE+"\n"+ SQL_CREATE_STOPS_TABLE+"\n"+SQL_CREATE_BRANCH_TABLE+"\n"+SQL_CREATE_CONNECTIONS_TABLE); db.execSQL(SQL_CREATE_LINES_TABLE); db.execSQL(SQL_CREATE_STOPS_TABLE); //tables with constraints db.execSQL(SQL_CREATE_BRANCH_TABLE); db.execSQL(SQL_CREATE_CONNECTIONS_TABLE); } @Override public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { + if(oldVersion<2 && newVersion == 2){ + //DROP ALL TABLES + db.execSQL("DROP TABLE "+ConnectionsTable.TABLE_NAME); + db.execSQL("DROP TABLE "+BranchesTable.TABLE_NAME); + db.execSQL("DROP TABLE "+LinesTable.TABLE_NAME); + db.execSQL("DROP TABLE "+ StopsTable.TABLE_NAME); + //RECREATE THE TABLES WITH THE NEW SCHEMA + db.execSQL(SQL_CREATE_LINES_TABLE); + db.execSQL(SQL_CREATE_STOPS_TABLE); + //tables with constraints + db.execSQL(SQL_CREATE_BRANCH_TABLE); + db.execSQL(SQL_CREATE_CONNECTIONS_TABLE); + + DatabaseUpdateService.startDBUpdate(appContext,0,true); + } } @Override public void onConfigure(SQLiteDatabase db) { super.onConfigure(db); db.execSQL("PRAGMA foreign_keys=ON"); } /** * Insert batch content, already prepared as * @param content ContentValues array * @return number of lines inserted */ public int insertBatchContent(ContentValues[] content,String tableName) throws SQLiteException { final SQLiteDatabase db = this.getWritableDatabase(); int success = 0; db.beginTransaction(); for (final ContentValues cv : content) { try { db.replaceOrThrow(tableName, null, cv); success++; } catch (SQLiteConstraintException d){ Log.w("NextGenDB_Insert","Failed insert with FOREIGN KEY... \n"+d.getMessage()); } catch (Exception e) { Log.w("NextGenDB_Insert", e); } } db.setTransactionSuccessful(); db.endTransaction(); return success; } public static final class Contract{ //Ok, I get it, it really is a pain in the ass.. // But it's the only way to have maintainable code + public interface DataTables { + String getTableName(); + String[] getFields(); + } - public static final class LinesTable implements BaseColumns{ + public static final class LinesTable implements BaseColumns, DataTables { //The fields public static final String TABLE_NAME = "lines"; - public static final String COLUMN_NAME = "name"; + public static final String COLUMN_NAME = "line_name"; public static final String COLUMN_DESCRIPTION = "line_description"; - public static final String COLUMN_TYPE = "bacino"; + public static final String COLUMN_TYPE = "line_bacino"; + + @Override + public String getTableName() { + return TABLE_NAME; + } + + @Override + public String[] getFields() { + return new String[]{COLUMN_NAME,COLUMN_DESCRIPTION,COLUMN_TYPE}; + } } - public static final class BranchesTable implements BaseColumns{ + public static final class BranchesTable implements BaseColumns, DataTables { public static final String TABLE_NAME = "branches"; public static final String COL_BRANCHID = "branchid"; public static final String COL_LINE = "lineid"; public static final String COL_DESCRIPTION = "branch_description"; - public static final String COL_DIRECTION = "direzione"; - public static final String COL_FESTIVO = "festivo"; - public static final String COL_TYPE = "type"; - public static final String COL_LUN="lun"; - public static final String COL_MAR="mar"; - public static final String COL_MER="mer"; - public static final String COL_GIO="gio"; - public static final String COL_VEN="ven"; - public static final String COL_SAB="sab"; - public static final String COL_DOM="dom"; + public static final String COL_DIRECTION = "branch_direzione"; + public static final String COL_FESTIVO = "branch_festivo"; + public static final String COL_TYPE = "branch_type"; + public static final String COL_LUN="runs_lun"; + public static final String COL_MAR="runs_mar"; + public static final String COL_MER="runs_mer"; + public static final String COL_GIO="runs_gio"; + public static final String COL_VEN="runs_ven"; + public static final String COL_SAB="runs_sab"; + public static final String COL_DOM="runs_dom"; + + @Override + public String getTableName() { + return TABLE_NAME; + } + @Override + public String[] getFields() { + return new String[]{COL_BRANCHID,COL_LINE,COL_DESCRIPTION, + COL_DIRECTION,COL_FESTIVO,COL_TYPE, + COL_LUN,COL_MAR,COL_MER,COL_GIO,COL_VEN,COL_SAB,COL_DOM + }; + } } - public static final class ConnectionsTable { + public static final class ConnectionsTable implements DataTables { public static final String TABLE_NAME = "connections"; - public static final String COLUMN_BRANCH = "branch"; + public static final String COLUMN_BRANCH = "branchid"; public static final String COLUMN_STOP_ID = "stopid"; - static final String COLUMN_ORDER = "ordine"; + public static final String COLUMN_ORDER = "ordine"; + + @Override + public String getTableName() { + return TABLE_NAME; + } + @Override + public String[] getFields() { + return new String[]{COLUMN_STOP_ID,COLUMN_BRANCH,COLUMN_ORDER}; + } } - public static final class StopsTable { + public static final class StopsTable implements DataTables { public static final String TABLE_NAME = "stops"; - public static final String COL_ID = "id"; //integer - public static final String COL_TYPE = "type"; - public static final String COL_NAME = "name"; - public static final String COL_LAT = "lat"; - public static final String COL_LONG = "longitude"; - public static final String COL_LOCATION = "location"; - public static final String COL_PLACE = "placeName"; - public static final String COL_LINES_STOPPING = "lines"; - + public static final String COL_ID = "stopid"; //integer + public static final String COL_TYPE = "stop_type"; + public static final String COL_NAME = "stop_name"; + public static final String COL_LAT = "stop_latitude"; + public static final String COL_LONG = "stop_longitude"; + public static final String COL_LOCATION = "stop_location"; + public static final String COL_PLACE = "stop_placeName"; + public static final String COL_LINES_STOPPING = "stop_lines"; + + @Override + public String getTableName() { + return TABLE_NAME; + } + @Override + public String[] getFields() { + return new String[]{COL_ID,COL_TYPE,COL_NAME,COL_LAT,COL_LONG,COL_LOCATION,COL_PLACE,COL_LINES_STOPPING}; + } } } public static final class DBUpdatingException extends Exception{ public DBUpdatingException(String message) { super(message); } } + }