SQLite Forum

Hello, this is strange one.

Using SQlite3 3.35 on my pc, I create a database, with three tables and two views. Then, I copy it over to my phone (Android 11) and open it with aSQLiteManager to test: everything works great including both views.

I copy the exact same database file to an Android 10 or below phone. When I try to open it with aSQLiteManager, it says "Race.db is not a database" and then overwrites it with a blank database. I tried other SQLite database utilities with the same result. They all work OK with my database on Android 11 but not on phones running previous versions.

It really looks like the view "r1_Trois" is the cause of the problem. If I re-create the database without that view, the new database can be opened without any issues on all Android versions and the other view is working just fine.

Thanks in advance for your help!

Here's the SQl code:

CREATE TABLE [Station] (  
    [Station] INTEGER,
    [DescStation] NVARCHAR(25),
    [NoApp] INTEGER);

CREATE TABLE [Destinataire] (  
    [Courriel] NVARCHAR(60));

CREATE TABLE [Coureurs] (  
    [Dossard] INTEGER PRIMARY KEY,
    [Prenom] NVARCHAR(25),
    [Nom] NVARCHAR(25),
    [Epreuve] NVARCHAR(10),
    [Sexe] NVARCHAR(1),
    [TempsPassage] DATETIME,
    [Statut] NVARCHAR(9));

CREATE VIEW r1_Trois AS SELECT Epreuve, rn AS "#", Sexe as "S", Dossard AS "3_Premiers", STRFTIME('%H:%M %d',TempsPassage) AS "Temps/Jour" FROM (SELECT *, ROW_NUMBER() OVER (PARTITION BY Epreuve, Sexe ORDER BY TempsPassage) rn FROM Coureurs WHERE TempsPassage <> '' AND Statut IS "En_Course") WHERE rn <= 3 ORDER BY SUBSTR(Epreuve, 5) + 0 DESC, Sexe DESC, rn ASC;

CREATE VIEW r2_DNFs AS SELECT Epreuve, Dossard as "Liste_DNFs", STRFTIME('%H:%M %d',TempsPassage) AS "Temps/jour" FROM Coureurs WHERE TempsPassage <> '' AND Statut is "DNF" ORDER BY SUBSTR(Epreuve, 5) + 0 DESC, Dossard;

.quit