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/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 @@ -36,9 +36,7 @@ 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; @@ -86,24 +84,44 @@ 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()); + } + /* 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 +130,7 @@ //result.add(entry.getName()); } stream.close(); + */ } catch (IOException e) { e.printStackTrace(); } 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) @@ -70,8 +76,8 @@ fun updateShapes(shapes: List) : Int @Transaction - fun updateStops(stops: List){ + fun updateAllStops(stops: List){ deleteAllStops() insertStops(stops) } } \ No newline at end of file