diff --git a/build.gradle b/build.gradle
--- a/build.gradle
+++ b/build.gradle
@@ -36,6 +36,7 @@
jcenter()
maven { url 'https://maven.google.com' }
google()
+ mavenCentral()
}
}
@@ -129,6 +130,9 @@
androidTestImplementation "androidx.room:room-testing:$room_version"
//multidex - we need this to build the app
implementation "androidx.multidex:multidex:$multidex_version"
+
+ implementation 'de.siegmar:fastcsv:2.0.0'
+
}
}
diff --git a/res/layout/activity_experiments.xml b/res/layout/activity_experiments.xml
--- a/res/layout/activity_experiments.xml
+++ b/res/layout/activity_experiments.xml
@@ -8,14 +8,17 @@
tools:context=".ActivityExperiments">
+ android:id="@+id/button"
+ android:layout_width="wrap_content"
+ android:layout_height="wrap_content"
+ android:onClick="runExp"
+ android:text="Download GTFS data"
+ app:layout_constraintBottom_toBottomOf="parent"
+ app:layout_constraintEnd_toEndOf="parent"
+ app:layout_constraintHorizontal_bias="0.497"
+ app:layout_constraintStart_toStartOf="parent"
+ app:layout_constraintTop_toTopOf="parent"
+ app:layout_constraintVertical_bias="0.292" />
+
+
\ No newline at end of file
diff --git a/src/it/reyboz/bustorino/ActivityExperiments.java b/src/it/reyboz/bustorino/ActivityExperiments.java
--- a/src/it/reyboz/bustorino/ActivityExperiments.java
+++ b/src/it/reyboz/bustorino/ActivityExperiments.java
@@ -31,14 +31,14 @@
import it.reyboz.bustorino.backend.gtfs.GtfsDataParser;
import it.reyboz.bustorino.backend.networkTools;
import it.reyboz.bustorino.backend.utils;
+import it.reyboz.bustorino.data.gtfs.GtfsDatabase;
+import it.reyboz.bustorino.data.gtfs.StaticGtfsDao;
import it.reyboz.bustorino.middleware.GeneralActivity;
import java.io.*;
import java.net.MalformedURLException;
import java.net.URL;
-import java.util.Date;
-import java.util.List;
-import java.util.Locale;
+import java.util.*;
import java.util.concurrent.ExecutorService;
import java.util.concurrent.Executors;
import java.util.concurrent.atomic.AtomicReference;
@@ -48,6 +48,9 @@
public class ActivityExperiments extends GeneralActivity {
+ ExecutorService executorService;
+ final static String DEBUG_TAG = "ExperimentsGTFS";
+
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
@@ -65,6 +68,12 @@
} else
Toast.makeText(this, "Gtfs data zip not present", Toast.LENGTH_SHORT).show();
});
+
+ Button cleanDBButton = findViewById(R.id.deleteDbButton);
+ if(cleanDBButton!=null)
+ cleanDBButton.setOnClickListener(this::deleteDatabase);
+
+ executorService = Executors.newFixedThreadPool(2);
}
public void runExp(View v){
@@ -74,7 +83,7 @@
Runnable run = new Runnable() {
@Override
public void run() {
- final String DEBUG_TAG = "ExperimentsGTFS";
+
AtomicReference res = new AtomicReference<>();
//List files = GtfsDataParser.readFilesList(res);
Date updateDate = GtfsDataParser.getLastGTFSUpdateDate(res);
@@ -82,28 +91,54 @@
"ExperimentGTFS", "Last update date is " + updateDate//utils.joinList(files, "\n")
);
//Toast.makeText(v.getContext(), "Gtfs data already downloaded", Toast.LENGTH_SHORT).show();
+ StaticGtfsDao dao = GtfsDatabase.Companion.getGtfsDatabase(appContext).gtfsDao();
+ Log.d(DEBUG_TAG, String.valueOf(dao));
+ dao.deleteAllStopTimes();
+
File saveFile = new File(getFilesDir(), "gtfs_data.zip");
if (!saveFile.isDirectory() && saveFile.exists()) {
Log.w(DEBUG_TAG, "Zip exists: " + saveFile);
- try (FileInputStream fileStream = new FileInputStream(saveFile)) {
- ZipInputStream stream = new ZipInputStream(fileStream);
+
+
+ try (ZipFile zipFile = new ZipFile(saveFile)) {
+ //ZipInputStream stream = new ZipInputStream(fileStream);
// now iterate through each item in the stream. The get next
// entry call will return a ZipEntry for each file in the
// stream
+ Enumeration entries = zipFile.entries();
ZipEntry entry;
String line;
- final BufferedReader reader = new BufferedReader(new InputStreamReader(stream));
+ //final BufferedReader reader = new BufferedReader(new InputStreamReader(stream));
+ HashSet readLater = new HashSet<>();
+ while(entries.hasMoreElements()){
+ entry = entries.nextElement();
+ //String tableName = entry.getName().split("\\.")[0].trim();
+ if(entry.getName().trim().equals("stop_times.txt")) {
+ readLater.add(entry);
+ continue;
+ }
+ GtfsDataParser.readGtfsZipEntry(entry, zipFile, v.getContext().getApplicationContext());
+ }
+ for(ZipEntry laterEntry: readLater){
+ GtfsDataParser.readGtfsZipEntry(laterEntry, zipFile, v.getContext().getApplicationContext());
+ }
+ //Toast.makeText(appContext, "D", Toast.LENGTH_SHORT).show();
+ /*
while ((entry = stream.getNextEntry()) != null) {
String s = String.format(Locale.ENGLISH, "Entry: %s len %d added",
entry.getName(),
entry.getSize()
);
+ if(entry.getName().contains("stop_times.")){
+ //skip and do later
+
+ }
//Toast.makeText(v.getContext(), "File: " + entry.getName(), Toast.LENGTH_SHORT).show();
Log.d(DEBUG_TAG, s);
//read data in table
final String tableName = entry.getName().split("\\.")[0].trim();
- GtfsDataParser.readCSVWithColumns(reader, tableName, v.getContext().getApplicationContext());
+
// Once we get the entry from the stream, the stream is
@@ -112,6 +147,7 @@
//result.add(entry.getName());
}
stream.close();
+ */
} catch (IOException e) {
e.printStackTrace();
}
@@ -128,7 +164,8 @@
}
};
- ExecutorService executorService = Executors.newFixedThreadPool(2);
+
+ Toast.makeText(this, "Launching, no result will show", Toast.LENGTH_SHORT).show();
//Looper looper = new Looper(true);
//Handler handler = new Handler();
//handler.post(run);
@@ -137,5 +174,23 @@
}
+ public void deleteDatabase(View v){
+ final Context con = getApplicationContext().getApplicationContext();
+ Toast.makeText(this, "Deleting GTFS DB contents, wait a few seconds", Toast.LENGTH_SHORT).show();
+ Runnable deleteDB = new Runnable() {
+ @Override
+ public void run() {
+ StaticGtfsDao dao = GtfsDatabase.Companion.getGtfsDatabase(con).gtfsDao();
+ Log.d(DEBUG_TAG, String.valueOf(dao));
+ dao.deleteAllStopTimes();
+ dao.deleteAllTrips();
+ dao.deleteAllRoutes();
+ dao.deleteAllStops();
+ dao.deleteAllServices();
+ Log.d(DEBUG_TAG, "Deleted stuff");
+ }
+ };
+ executorService.execute(deleteDB);
+ }
}
\ No newline at end of file
diff --git a/src/it/reyboz/bustorino/backend/gtfs/GtfsDataParser.java b/src/it/reyboz/bustorino/backend/gtfs/GtfsDataParser.java
--- a/src/it/reyboz/bustorino/backend/gtfs/GtfsDataParser.java
+++ b/src/it/reyboz/bustorino/backend/gtfs/GtfsDataParser.java
@@ -3,6 +3,10 @@
import android.content.Context;
import android.util.Log;
import androidx.annotation.NonNull;
+
+import de.siegmar.fastcsv.reader.CloseableIterator;
+import de.siegmar.fastcsv.reader.NamedCsvReader;
+import de.siegmar.fastcsv.reader.NamedCsvRow;
import it.reyboz.bustorino.backend.Fetcher;
import it.reyboz.bustorino.backend.networkTools;
import it.reyboz.bustorino.data.gtfs.CsvTableInserter;
@@ -15,6 +19,7 @@
import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStream;
+import java.io.InputStreamReader;
import java.net.HttpURLConnection;
import java.net.URL;
import java.text.ParseException;
@@ -24,6 +29,7 @@
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import java.util.zip.ZipEntry;
+import java.util.zip.ZipFile;
import java.util.zip.ZipInputStream;
abstract public class GtfsDataParser {
@@ -143,6 +149,18 @@
return finalDate;
}
+ public static void readGtfsZipEntry(ZipEntry entry, ZipFile zipFile, Context con) throws IOException{
+ String tableName = entry.getName().split("\\.")[0].trim();
+ InputStream stream = zipFile.getInputStream(entry);
+ String s = String.format(Locale.ENGLISH, "Entry: %s len %d added",
+ entry.getName(),
+ entry.getSize()
+ );
+ Log.d(DEBUG_TAG, s);
+ final BufferedReader reader = new BufferedReader(new InputStreamReader(stream));
+ GtfsDataParser.readCSVWithColumns(reader, tableName, con);
+ stream.close();
+ }
public static void readCSVWithColumns(BufferedReader reader, String tableName, Context con) throws IOException {
@@ -151,21 +169,22 @@
String line;
- final String header = reader.readLine();
+ /*final String header = reader.readLine();
if (header == null){
throw new IOException();
- }
+ }*/
//elements = header.split("\n")[0].split(",");
//System.out.println(Arrays.toString(elements));
- lineElements = readCsvLine(header);
-
-
- final HashMap columnMap = new HashMap<>();
+ //lineElements = readCsvLine(header);
+ NamedCsvReader csvReader = NamedCsvReader.builder().build(reader);
+ CloseableIterator iterator = csvReader.iterator();
final CsvTableInserter inserter = new CsvTableInserter(tableName,con);
+ /*final HashMap columnMap = new HashMap<>();
+
for (int i=0; i< lineElements.size(); i++){
//columnMap.put(i, fixStringIfItHasQuotes(elements[i].trim()) );
columnMap.put(i, lineElements.get(i).trim() );
@@ -185,10 +204,20 @@
first=false;
}
inserter.addElement(rowsMap);
+ }*/
+ int c = 0;
+ while (iterator.hasNext()){
+
+ final Map rowsMap = iterator.next().getFields();
+ if (c < 1){
+ Log.d(DEBUG_TAG, " in map:"+rowsMap);
+ c++;
+ }
+ inserter.addElement(rowsMap);
}
//commit data
- inserter.insertDataInDatabase();
+ inserter.finishInsert();
}
@NonNull
private static Map getColumnsAsString(@NonNull String[] lineElements, Map colsIndices)
diff --git a/src/it/reyboz/bustorino/backend/networkTools.java b/src/it/reyboz/bustorino/backend/networkTools.java
--- a/src/it/reyboz/bustorino/backend/networkTools.java
+++ b/src/it/reyboz/bustorino/backend/networkTools.java
@@ -26,6 +26,7 @@
import java.net.HttpURLConnection;
import java.net.URL;
import java.util.ArrayList;
+import java.util.Date;
import java.util.Map;
import java.util.Scanner;
import java.util.concurrent.atomic.AtomicReference;
@@ -79,6 +80,7 @@
}
urlConnection.setConnectTimeout(4000);
urlConnection.setReadTimeout(50 * 1000);
+ System.out.println("Last modified: "+new Date(urlConnection.getLastModified()));
Log.d("BusTO net Tools", "Download file "+url);
try (InputStream inputStream = urlConnection.getInputStream()) {
@@ -114,6 +116,37 @@
urlConnection.disconnect();
return Fetcher.Result.OK;
}
+
+ @Nullable
+ public static Date checkLastModificationDate(URL url, AtomicReference res) {
+ HttpURLConnection urlConnection;
+ try {
+ urlConnection = (HttpURLConnection) url.openConnection();
+ } catch (IOException e) {
+ //e.printStackTrace();
+ res.set(Fetcher.Result.CONNECTION_ERROR);
+ return null;
+ }
+ urlConnection.setConnectTimeout(4000);
+ urlConnection.setReadTimeout(4 * 1000);
+ System.out.println("Last modified: "+new Date(urlConnection.getLastModified()));
+
+ Log.d("BusTO net Tools", "Download file "+url);
+ final Date theDate = new Date(urlConnection.getLastModified());
+
+ try {
+ if(urlConnection.getResponseCode()==404)
+ res.set(Fetcher.Result.SERVER_ERROR_404);
+ else if(urlConnection.getResponseCode()!=200)
+ res.set(Fetcher.Result.SERVER_ERROR);
+ } catch (IOException e) {
+ e.printStackTrace();
+ res.set(Fetcher.Result.PARSER_ERROR);
+ }
+ urlConnection.disconnect();
+ //theDate.getTime()
+ return theDate;
+ }
@Nullable
static String queryURL(URL url, AtomicReference res){
return queryURL(url,res,null);
diff --git a/src/it/reyboz/bustorino/data/gtfs/CsvTableInserter.kt b/src/it/reyboz/bustorino/data/gtfs/CsvTableInserter.kt
--- a/src/it/reyboz/bustorino/data/gtfs/CsvTableInserter.kt
+++ b/src/it/reyboz/bustorino/data/gtfs/CsvTableInserter.kt
@@ -29,6 +29,21 @@
private val elementsList: MutableList< in GtfsTable> = mutableListOf()
+ private var stopsIDsPresent: HashSet? = null
+ private var tripsIDsPresent: HashSet? = null
+
+ private var countInsert = 0
+ init {
+ if(tableName == "stop_times") {
+ stopsIDsPresent = dao.getAllStopsIDs().toHashSet()
+ tripsIDsPresent = dao.getAllTripsIDs().toHashSet()
+ Log.d(DEBUG_TAG, "num stop IDs present: "+ stopsIDsPresent!!.size)
+ Log.d(DEBUG_TAG, "num trips IDs present: "+ tripsIDsPresent!!.size)
+ } else if(tableName == "routes"){
+ dao.deleteAllRoutes()
+ }
+ }
+
fun addElement(csvLineElements: Map) {
when(tableName){
@@ -44,14 +59,23 @@
elementsList.add(GtfsTrip(csvLineElements))
"shapes" ->
elementsList.add(GtfsShape(csvLineElements))
- "stop_times" ->
- elementsList.add(GtfsStopTime(csvLineElements))
+ "stop_times" -> {
+ //filter stop
+ val stopTime = GtfsStopTime(csvLineElements)
+ /*
+ val stopOk = //tripsIDsPresent?.contains(stopTime.tripID) == true
+ (stopsIDsPresent?.contains(stopTime.stopID) == true)// &&
+ // tripsIDsPresent?.contains(stopTime.tripID) == true)
+ if (stopOk)
+ */
+ elementsList.add(stopTime)
+ }
}
if(elementsList.size >= MAX_ELEMENTS){
//have to insert
- Log.d(DEBUG_TAG, "Inserting first batch of elements now, list size: "+elementsList.size)
+
if (tableName == "routes")
dao.insertRoutes(elementsList.filterIsInstance())
else
@@ -61,10 +85,14 @@
}
}
- fun insertDataInDatabase(){
+ private fun insertDataInDatabase(){
+ //Log.d(DEBUG_TAG, "Inserting batch of elements now, list size: "+elementsList.size)
+ countInsert += elementsList.size
when(tableName){
- "stops" -> dao.updateStops(elementsList.filterIsInstance())
- "routes" -> dao.clearAndInsertRoutes(elementsList.filterIsInstance())
+ "stops" -> {
+ dao.insertStops(elementsList.filterIsInstance())
+ }
+ "routes" -> dao.insertRoutes(elementsList.filterIsInstance())
"calendar" -> dao.insertServices(elementsList.filterIsInstance())
"calendar_dates" -> dao.insertDates(elementsList.filterIsInstance())
"trips" -> dao.insertTrips(elementsList.filterIsInstance())
@@ -72,11 +100,16 @@
"shapes" -> dao.insertShapes(elementsList.filterIsInstance())
}
+ ///if(elementsList.size < MAX_ELEMENTS)
+ }
+ fun finishInsert(){
+ insertDataInDatabase()
+ Log.d(DEBUG_TAG, "Inserted "+countInsert+" elements from "+tableName);
}
companion object{
- val MAX_ELEMENTS = 5000
+ const val MAX_ELEMENTS = 5000
- val DEBUG_TAG="BusTO - TableInserter"
+ const val DEBUG_TAG="BusTO - TableInserter"
}
}
\ No newline at end of file
diff --git a/src/it/reyboz/bustorino/data/gtfs/GtfsDatabase.kt b/src/it/reyboz/bustorino/data/gtfs/GtfsDatabase.kt
--- a/src/it/reyboz/bustorino/data/gtfs/GtfsDatabase.kt
+++ b/src/it/reyboz/bustorino/data/gtfs/GtfsDatabase.kt
@@ -52,6 +52,6 @@
}
const val VERSION = 1
- const val FOREIGNKEY_ONDELETE = ForeignKey.NO_ACTION
+ const val FOREIGNKEY_ONDELETE = ForeignKey.CASCADE
}
}
\ No newline at end of file
diff --git a/src/it/reyboz/bustorino/data/gtfs/GtfsTrip.kt b/src/it/reyboz/bustorino/data/gtfs/GtfsTrip.kt
--- a/src/it/reyboz/bustorino/data/gtfs/GtfsTrip.kt
+++ b/src/it/reyboz/bustorino/data/gtfs/GtfsTrip.kt
@@ -24,7 +24,7 @@
ForeignKey(entity = GtfsRoute::class,
parentColumns = [GtfsRoute.COL_ROUTE_ID],
childColumns = [GtfsTrip.COL_ROUTE_ID],
- onDelete = GtfsDatabase.FOREIGNKEY_ONDELETE),
+ onDelete = ForeignKey.CASCADE),
// The service_id: ID referencing calendar.service_id or calendar_dates.service_id
/*
ForeignKey(entity = GtfsService::class,
diff --git a/src/it/reyboz/bustorino/data/gtfs/StaticGtfsDao.kt b/src/it/reyboz/bustorino/data/gtfs/StaticGtfsDao.kt
--- a/src/it/reyboz/bustorino/data/gtfs/StaticGtfsDao.kt
+++ b/src/it/reyboz/bustorino/data/gtfs/StaticGtfsDao.kt
@@ -25,6 +25,12 @@
@Query("SELECT * FROM "+GtfsRoute.DB_TABLE+" ORDER BY "+GtfsRoute.COL_SORT_ORDER)
fun getAllRoutes() : LiveData>
+ @Query("SELECT "+GtfsTrip.COL_TRIP_ID+" FROM "+GtfsTrip.DB_TABLE)
+ fun getAllTripsIDs() : List
+
+ @Query("SELECT "+GtfsStop.COL_STOP_ID+" FROM "+GtfsStop.DB_TABLE)
+ fun getAllStopsIDs() : List
+
@Query("SELECT * FROM "+GtfsStop.DB_TABLE+" WHERE "+GtfsStop.COL_STOP_CODE+" LIKE :queryID")
fun getStopByStopID(queryID: String): LiveData>
@@ -42,7 +48,7 @@
@Insert(onConflict = OnConflictStrategy.REPLACE)
fun insertRoutes(users: List)
- @Insert
+ @Insert(onConflict = OnConflictStrategy.REPLACE)
fun insertStops(stops: List)
@Insert(onConflict = OnConflictStrategy.REPLACE)
fun insertCalendarServices(services: List)
@@ -66,12 +72,19 @@
fun deleteAllRoutes()
@Query("DELETE FROM "+GtfsStop.DB_TABLE)
fun deleteAllStops()
+ @Query("DELETE FROM "+GtfsTrip.DB_TABLE)
+ fun deleteAllTrips()
@Update(onConflict = OnConflictStrategy.REPLACE)
fun updateShapes(shapes: List) : Int
@Transaction
- fun updateStops(stops: List){
+ fun updateAllStops(stops: List){
deleteAllStops()
insertStops(stops)
}
+ @Query("DELETE FROM "+GtfsStopTime.DB_TABLE)
+ fun deleteAllStopTimes()
+ @Query("DELETE FROM "+GtfsService.DB_TABLE)
+ fun deleteAllServices()
+
}
\ No newline at end of file