Page Menu
Home
GitPull.it
Search
Configure Global Search
Log In
Files
F2444084
D69.1729695790.diff
No One
Temporary
Actions
View File
Edit File
Delete File
View Transforms
Subscribe
Award Token
Flag For Later
Size
21 KB
Referenced Files
None
Subscribers
None
D69.1729695790.diff
View Options
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">
<Button
- android:text="Download GTFS data"
- android:layout_width="wrap_content"
- android:layout_height="wrap_content" android:id="@+id/button"
- app:layout_constraintTop_toTopOf="parent"
- app:layout_constraintBottom_toBottomOf="parent" app:layout_constraintEnd_toEndOf="parent"
- app:layout_constraintStart_toStartOf="parent"
- android:onClick="runExp"
- />
+ 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" />
<Button
android:text="Delete temporary GTFS file"
@@ -24,4 +27,14 @@
app:layout_constraintBottom_toBottomOf="parent" app:layout_constraintEnd_toEndOf="parent"
app:layout_constraintStart_toStartOf="parent"
app:layout_constraintTop_toBottomOf="@+id/button"/>
+
+ <Button
+ android:id="@+id/deleteDbButton"
+ android:layout_width="wrap_content"
+ android:layout_height="wrap_content"
+ android:text="Clean GTFS data"
+ app:layout_constraintBottom_toTopOf="@+id/deleteButton"
+ app:layout_constraintEnd_toEndOf="parent"
+ app:layout_constraintStart_toStartOf="parent"
+ app:layout_constraintTop_toBottomOf="@+id/button" />
</androidx.constraintlayout.widget.ConstraintLayout>
\ 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<Fetcher.Result> res = new AtomicReference<>();
//List<String> 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<? extends ZipEntry> entries = zipFile.entries();
ZipEntry entry;
String line;
- final BufferedReader reader = new BufferedReader(new InputStreamReader(stream));
+ //final BufferedReader reader = new BufferedReader(new InputStreamReader(stream));
+ HashSet<ZipEntry> 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<Integer,String> columnMap = new HashMap<>();
+ //lineElements = readCsvLine(header);
+ NamedCsvReader csvReader = NamedCsvReader.builder().build(reader);
+ CloseableIterator<NamedCsvRow> iterator = csvReader.iterator();
final CsvTableInserter inserter = new CsvTableInserter(tableName,con);
+ /*final HashMap<Integer,String> 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<String,String> 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<String,String> getColumnsAsString(@NonNull String[] lineElements, Map<Integer,String> 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<Fetcher.Result> 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<Fetcher.Result> 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<Int>? = null
+ private var tripsIDsPresent: HashSet<String>? = 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<String,String>) {
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<GtfsRoute>())
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<GtfsStop>())
- "routes" -> dao.clearAndInsertRoutes(elementsList.filterIsInstance<GtfsRoute>())
+ "stops" -> {
+ dao.insertStops(elementsList.filterIsInstance<GtfsStop>())
+ }
+ "routes" -> dao.insertRoutes(elementsList.filterIsInstance<GtfsRoute>())
"calendar" -> dao.insertServices(elementsList.filterIsInstance<GtfsService>())
"calendar_dates" -> dao.insertDates(elementsList.filterIsInstance<GtfsServiceDate>())
"trips" -> dao.insertTrips(elementsList.filterIsInstance<GtfsTrip>())
@@ -72,11 +100,16 @@
"shapes" -> dao.insertShapes(elementsList.filterIsInstance<GtfsShape>())
}
+ ///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<List<GtfsRoute>>
+ @Query("SELECT "+GtfsTrip.COL_TRIP_ID+" FROM "+GtfsTrip.DB_TABLE)
+ fun getAllTripsIDs() : List<String>
+
+ @Query("SELECT "+GtfsStop.COL_STOP_ID+" FROM "+GtfsStop.DB_TABLE)
+ fun getAllStopsIDs() : List<Int>
+
@Query("SELECT * FROM "+GtfsStop.DB_TABLE+" WHERE "+GtfsStop.COL_STOP_CODE+" LIKE :queryID")
fun getStopByStopID(queryID: String): LiveData<List<GtfsStop>>
@@ -42,7 +48,7 @@
@Insert(onConflict = OnConflictStrategy.REPLACE)
fun insertRoutes(users: List<GtfsRoute>)
- @Insert
+ @Insert(onConflict = OnConflictStrategy.REPLACE)
fun insertStops(stops: List<GtfsStop>)
@Insert(onConflict = OnConflictStrategy.REPLACE)
fun insertCalendarServices(services: List<GtfsService>)
@@ -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<GtfsShape>) : Int
@Transaction
- fun updateStops(stops: List<GtfsStop>){
+ fun updateAllStops(stops: List<GtfsStop>){
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
File Metadata
Details
Attached
Mime Type
text/plain
Expires
Wed, Oct 23, 17:03 (7 h, 38 m)
Storage Engine
blob
Storage Format
Raw Data
Storage Handle
639204
Default Alt Text
D69.1729695790.diff (21 KB)
Attached To
Mode
D69: Fix insertion of GTFS data in database
Attached
Detach File
Event Timeline
Log In to Comment