maemo.org - Talk - Editing/Appending SQLite files

maemo.org - Talk (https://talk.maemo.org/index.php)

- SailfishOS (https://talk.maemo.org/forumdisplay.php?f=52)

- - Editing/Appending SQLite files (https://talk.maemo.org/showthread.php?t=99956)

Re: Editing/Appending SQLite files

Sqlite and text file in winzip archive;

https://www.dropbox.com/s/9e0298pzki...RIALS.rar?dl=0

Re: Editing/Appending SQLite files

Man!, where are my posts going?........what the fk is going on!, lol......I posted here 20 minutes ago, now it's gone.

I got a result of sorts. I managed to work out the command to sort my sqlite contents either by ID or station....great!, but that only sorts the output on screen, it doesn't sort the contents physically and so, when viewed in the app on my phone, the list still has a clump of new stations at the bottom - I guess there is something wrong with the file itself.

The command, as stated by rinigus, was as follows;

SELECT * FROM stations ORDER BY STATION ASC; (I then read that ASC is set by default, so I can omit that in future).

(let's see if this post stays put!).......

Re: Editing/Appending SQLite files

Don't worry, I see plenty of posts! I've had a play with a list of pokemon (first file I found) and got just as far as you in sorting an output, now just need to direct it into a different table...

Re: Editing/Appending SQLite files

Markyyboy, how exactly are you trying to retrieve data?
Have you tried the following SQL statement:

Quote:

select * from stations order by "station";

Re: Editing/Appending SQLite files

To document this properly I did:

Find current field names

Code:

PRAGMA table_info(stations);

Create a tempory table with the same fields

Code:

CREATE TABLE temp_table (id INTEGER PRIMARY KEY, station TEXT, favourite INTEGER) ;

Put sorted data into the temporary table (assumes new stations are added at the bottom)

Code:

INSERT INTO temp_table (station, favourite) SELECT station, favourite FROM stations ORDER BY STATION ASC ;

Delete original table

Code:

DROP TABLE stations ;

Rename new table to original name

Code:

ALTER TABLE temp_table RENAME TO stations ;

Re: Editing/Appending SQLite files

Quote:

Originally Posted by uvatbc (Post 1536724)

Markyyboy, how exactly are you trying to retrieve data?
Have you tried the following SQL statement:

Due to my browser throwing a wobbly/multiple posts/etc, there is an overlap, your command suggestion is exactly what I came up with, while it sorts it on screen, the list is still not sorted in the device - I am doing this by SSH, straight into the DB (this also requires that the temp DB created by the app found in '.config/harbour-nationalrail/' must be removed beforehand).

Presumably you have a Sailfish device, head to Jolla Store and download NationalRail app. By doing this, you will have the 'stationsDB.sqlite' file found in '/usr/share/harbour-nationalrail/data' folder

The sqlite list is out of date, there are more than 18 new stations to add to the list.....or I am now thinking the file should be made from scratch and incorporate a way to make updating easier in future releases.

Re: Editing/Appending SQLite files

Can you see my post above yours? The problem is solved :)

Re: Editing/Appending SQLite files

wow!, hats off to you suicidal_orange (is that a colour or a depressed fruit?), thank you!......so let me get this straight, you never used sqlite before and yet within a couple of hours, you got it sorted!, bloody genius!.

So, as I append new stations, will I have to do the same maneuvers?

thanks,
Mark

Re: Editing/Appending SQLite files

Quote:

Originally Posted by Markkyboy (Post 1536729)

It shouldn't matter what the on disk sqlite3 format is...
Any program that reads the data from the sqlite database is always going to use some select statement.
If that select statement has an "order by" clause, then the data will be returned to the app ordered even though it isnt ordered on disk.

In other words, do not waste your time resetting the on disk format again and again. Use the correct select from order by statement and let SQL do that heavy lifting.
This sort of thing is exactly why you're supposed to use SQL and not a flat text file.

Re: Editing/Appending SQLite files

Very true uvatbc, this should be fixed in the program doing the query but at least new train stations aren't an everyday occurrence.

That said it's a bit strange the original programmer didn't use a flat file so the list was easy to edit being as there are only two fields in a single table. Then again if they were future proofing they would have sorted in the query in the first place...

Quote:

Originally Posted by Markkyboy (Post 1536737)

wow!, hats off to you suicidal_orange (is that a colour or a depressed fruit?), thank you!

It's an old gamer tag. I always chose the guy in desert camouflage in original counter strike even in jungle/urban maps - not good for surviving!

Quote:

.....so let me get this straight, you never used sqlite before and yet within a couple of hours, you got it sorted!, bloody genius!.

So, as I append new stations, will I have to do the same maneuvers?

thanks,
Mark

Indeed, but to be fair it's just the principals of databases. SELECT shows stuff, but it doesn't save it, I hadn't done an INSERT in over a decade and that was through a website but the theory is still the same. The built in command line is much more convenient than going through a website too...

As above the program is designed badly so yes, you'll need to do this whenever you add a station. Unless it starts with Z!