Unuseable On Android 10, sql error -> un

[gmanic]

Describe the bug
A clear and concise description of what the bug is.

To Reproduce
Steps to reproduce the behavior:

1. Have Android 10 installed with latest (Nov) patchlevel
2. Start app
3. See error - multiple popups with the following error message

`Invalid SQL - use default instead
SQL-error 'Invalid column max(width, height) as col_width'

SELECT _id._data AS disp_text, max(width, height) AS col_width, longitude, datetaken, _data, orientation
FROM content://media/external/file
WHERE (( media_type=1)) `

The app has to be killed with the app switcher. There is no log written.

Expected behavior
Start of App showing map with photos.

Smartphone (please complete the following information):

• Android version [e.g. Android-7.1] : 10
• A Photo Manager Version (i.e. 0.4.6.160304) : latest Version from fdroid

Additional context
Add any other context about the problem here.

Crash Report
If you report an app crash: Can you add the crash logfile to this ticket?

When APhotoManager crashes it tries to write a crash log file in the Error Log Folder.

• [ExternalStorageDirectory]/copy/log/androFotofinder.logcat-2017....txt
  • i.e. /storage/sdcard0/copy/log/androFotofinder.logcat-20170728-135704.txt
    • crash data from 2017-07-28 13:57

For more details on Error Loging see diagnostic settings.

---

[Update 2020-04-13]

Technical Problem

Android-10 (api29) prevents apps from using sql-functions on media-content-provider colums where APhotoManager heavily depends on.

To achive android-10 compatibility i have seperated all database access logic into a seperate layer where there are two implementations for:

• A Contentprovider-implementation for android-9 and below
  ** is used since APhotoManager-0.8.1
• A database implementation that works with a data clone of media-contentprovider
  ** will be used in android-10 and up, when finished
  ** done: code to one-time copy all data from media-contentprovider to database
  ** done: code to query database
  ** done: datachanges from within APhotoManager are written to media-contentprovider and to database
  ** open todo: when changes are done to media-contentprovider outside APhotoManager (i.e. when camera shoots a photo) the changes must be transfered to database, too

Currently work on this ticket is stopped, because i work on sd-card-support #169 to make APhotoManager run on my new android-9 device.

[Update k3b 20210122]

since android-10 it is not allowed to query the media database for latitude/longitude any more ( https://developer.android.com/reference/android/provider/MediaStore.Images.ImageColumns#LATITUDE ) :-(

As consequence APhotoManager needs

• its own local copy of the media database
• a full media rescan (or the import of the gps data through a csv file).

[k3b]

thanks for the report.

Unfortunatley i have no android 10 device to verify whether this is something special to your android-device or whether this is an antdoir-10 incompatibility issue.

your device does not allow to

  select max(width, height) AS col_width FROM content://media/external/file

may be this an android-10 security feature.

[gmanic]

Thanks for your reply. It's an Essential Phone with a clean Android.

I would like to assist to debug this issue, if this is possible. I have, though, no root access to the device and I will not change that. Anything I can do, just let me know.

[k3b]

thanks for your assist offer but i donot think that we can remotely solve this problem.

A Photo manger uses the media-content provider "content://media/external/file" to save and search photo data. it uses some database columns that are not available at content://media/external/photo to store non standard infos (tags, rating and private-photos)

i am afraid that android-10 does not allow accessing all existing columns via "content://media/external/file" any more :-(

you can use

https://github.com/k3b/ContentProviderHelper/ (available on f-droid)

to explore the contentproviders

i have to borrow some android-10 device to find out more

According to https://developer.android.com/reference/android/provider/MediaStore.MediaColumns.html the api should still work

[gmanic]

Seems not easy. I tried ContentProviderHelper and it worked; the query for one specific _id within content://media/external/files showed width, height with correct values. Sharing the result unfortunately doesn't seem to work.
I fully removed APhotoManager and tried to reinstall all three available versions on f-droid, to no avail.

[gmanic]

What region in Germany you're from? Maybe we should email details (how?)

[k3b]

On Redit i asked Other android-10 users all having the same problem as you :-(

I need to reimplement some basic functionality :-( :-( :-( :-(

[k3b]

@gmanic: under https://github.com/k3b/APhotoManager/releases/tag/t0.8.0.191121-A10 there is a special apk for download that replaces the sqLite-sql function "max(...)". Can you check if the gallery is working again?

[gmanic]

Installed the apk and no error on startup anymore. Top.

Major lag noticeable on scrolling through images. Switching to "date filter" gives a short error "error while loading folder date" (Fehler beim Laden des Ordners Datum). Selecting "Map filter" gives crash of the app.

Let me know if I should test anything.

Here's the link to the logfile with all logs enabled in settings.

[k3b]

thanks for the logs.

it seems that android media-conten- provider does not accept sqLite-functions any more as colums

i found these in the logs

[ 11-21 20:16:43.260 28522:28660 W/k3bFoto  ]
GalleryA- from openPicker(dirQueryID=Datum)-DirectoryLoaderTask
	 SELECT max(_id) AS _id, strftime('/%Y/%m/%d/', datetaken / 1000, 'unixepoch', 'localtime') AS disp_txt, count(*) AS count, max(longitude) AS longitude, max(_data) AS _data 
FROM content://media/external/file 
GROUP BY strftime('/%Y/%m/%d/', datetaken / 1000, 'unixepoch', 'localtime') 
ORDER BY strftime('/%Y/%m/%d/', datetaken / 1000, 'unixepoch', 'localtime')
	Invalid column strftime('/%Y/%m/%d/', datetaken / 1000, 'unixepoch', 'localtime') AS disp_txt


[ 11-21 20:17:04.792 28522:28522 D/k3bFoto  ]
LocationMapFragment#1 reloadFotoMarker(onScroll) zoom 3.0, query  SELECT max(_id) AS _id, ((round((latitude * 0.2) - 0.5) /0.2) + 2.5) AS latitude,
 ((round((longitude * 0.2) - 0.5) /0.2) + 2.5) AS longitude, 
count(*) AS count  FROM content://media/external/file  

WHERE (latitude >= ?) AND (latitude < ?) 
AND (longitude >= ?) AND (longitude < ?)  	

PARAMETERS -89.781164, -62.554498, -80.312500, 100.000000  GROUP BY ((round((latitude * 0.2) - 0.5) /0.2) + 2.5), ((round((longitude * 0.2) - 0.5) /0.2) + 2.5)  ORDER BY ((round((latitude * 0.2) - 0.5) /0.2) + 2.5), ((round((longitude * 0.2) - 0.5) /0.2) + 2.5)

LocationMapFragment#1 -FotoMarkerLoaderTask#1-doInBackground : 
	exception : Invalid column ((round((latitude * 0.2) - 0.5) /0.2) + 2.5) AS latitude
java.lang.IllegalArgumentException: Invalid column ((round((latitude * 0.2) - 0.5) /0.2) + 2.5) AS latitude
	at android.database.DatabaseUtils.readExceptionFromParcel(DatabaseUtils.java:170)
	at android.database.DatabaseUtils.readExceptionFromParcel(DatabaseUtils.java:140)
	at android.content.ContentProviderProxy.query(ContentProviderNative.java:423)
...
	at de.k3b.android.androFotoFinder.locationmap.MarkerLoaderTask.doInBackground(MarkerLoaderTask.java:99)
	at de.k3b.android.androFotoFinder.locationmap.MarkerLoaderTaskWithRecycling.doInBackground(MarkerLoaderTaskWithRecycling.java:74)
	at de.k3b.android.androFotoFinder.locationmap.MarkerLoaderTaskWithRecycling.doInBackground(MarkerLoaderTaskWithRecycling.java:37)

[k3b]

@gmanic I have started to refactor out all old-incompatible api calls into a seperate module with an alternative android-10-compatible implementation. much work ahead :-(

[gmanic]

Thanks. Anything I could do? No idea of Java and android programming, though.

[k3b]

@gmanic current status under android 10:

• generate a local copy of the android-system-media-database
  • copying the data android-system-media-database to local database 10 0000 jpg-s per minute)
• the media scanner now is working again.
  • it updates both local db and android-system-media-database
  • The Android-6ff version is terrible slow (30 jpg-s per minute)
  • The Android-4 and Android-5 Version is faster (200 jpg-s per minute)
  • If you have many photos you can import the meta data from a csv generated on a pc (600 jpg-s per minute)
• With Android-6ff you have full support for fotos on sd-card.

Next milesone will be:

• find out if a photo in android-system-media-database was added/modified/deleted and sync the changes to apm-s local database.