Active Topics
-
The N900 is now 15 years old! (7)
to Nokia N900 by Danct12 - 22 hrs, 30 mins ago -
Sailfish OS for the Motorola Moto G7 Power (XT1955-5) - (ocean) (4)
to SailfishOS by Maemish - 1 day, 17 hrs ago -
F(x)tec Pro1x (QWERTY) for sale (0)
to Buy & Sell by aln00000 - 4 days, 2 hrs ago - more...
| The Following User Says Thank You to Markkyboy For This Useful Post: | ||
 |
|
2017-10-13
, 19:48
|
|
Posts: 433 |
Thanked: 727 times |
Joined on Oct 2012
@ Costa Blanca, Espaņa
|
#12
|
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!).......
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!).......
__________________
..oO(Don't just sit there standing around, pick up a shovel and sweep up!)Oo..
..oO(Don't just sit there standing around, pick up a shovel and sweep up!)Oo..
| The Following User Says Thank You to Markkyboy For This Useful Post: | ||
|
|
2017-10-13
, 20:04
|
|
Posts: 177 |
Thanked: 427 times |
Joined on Sep 2017
|
#13
|
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...
| The Following User Says Thank You to suicidal_orange For This Useful Post: | ||
|
|
2017-10-13
, 20:25
|
|
Posts: 1,048 |
Thanked: 979 times |
Joined on Mar 2008
@ SF Bay Area
|
#14
|
Markyyboy, how exactly are you trying to retrieve data?
Have you tried the following SQL statement:
Have you tried the following SQL statement:
select * from stations order by "station";
__________________
qgvdial: Google Voice client. All downloads
qgvtp: Phone integration for the n900 that dials out and sends texts using qgvdial.
mosquitto: message broker that implements the MQ Telemetry Transport protocol version 3.
qgvnotify: Google voice and contacts notifier for diablo and maemo.
If you want to thank me, click the Thanks button.
If you'd like to thank my applications, vote to move them to extras.
qgvdial: Google Voice client. All downloads
qgvtp: Phone integration for the n900 that dials out and sends texts using qgvdial.
mosquitto: message broker that implements the MQ Telemetry Transport protocol version 3.
qgvnotify: Google voice and contacts notifier for diablo and maemo.
If you want to thank me, click the Thanks button.
If you'd like to thank my applications, vote to move them to extras.
| The Following User Says Thank You to uvatbc For This Useful Post: | ||
|
|
2017-10-13
, 20:26
|
|
Posts: 177 |
Thanked: 427 times |
Joined on Sep 2017
|
#15
|
To document this properly I did:
Find current field names
Create a tempory table with the same fields
Put sorted data into the temporary table (assumes new stations are added at the bottom)
Delete original table
Rename new table to original name
Find current field names
Code:
PRAGMA table_info(stations);
Code:
CREATE TABLE temp_table (id INTEGER PRIMARY KEY, station TEXT, favourite INTEGER) ;
Code:
INSERT INTO temp_table (station, favourite) SELECT station, favourite FROM stations ORDER BY STATION ASC ;
Code:
DROP TABLE stations ;
Code:
ALTER TABLE temp_table RENAME TO stations ;
|
|
2017-10-13
, 20:35
|
|
Posts: 433 |
Thanked: 727 times |
Joined on Oct 2012
@ Costa Blanca, Espaņa
|
#16
|
Originally Posted by uvatbc
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).
Markyyboy, how exactly are you trying to retrieve data?
Have you tried the following SQL statement:
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.
__________________
..oO(Don't just sit there standing around, pick up a shovel and sweep up!)Oo..
..oO(Don't just sit there standing around, pick up a shovel and sweep up!)Oo..
| The Following User Says Thank You to Markkyboy For This Useful Post: | ||
|
|
2017-10-13
, 21:00
|
|
Posts: 433 |
Thanked: 727 times |
Joined on Oct 2012
@ Costa Blanca, Espaņa
|
#18
|
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
So, as I append new stations, will I have to do the same maneuvers?
thanks,
Mark
__________________
..oO(Don't just sit there standing around, pick up a shovel and sweep up!)Oo..
..oO(Don't just sit there standing around, pick up a shovel and sweep up!)Oo..
| The Following User Says Thank You to Markkyboy For This Useful Post: | ||
|
|
2017-10-13
, 21:16
|
|
Posts: 1,048 |
Thanked: 979 times |
Joined on Mar 2008
@ SF Bay Area
|
#19
|
Originally Posted by Markkyboy
It shouldn't matter what the on disk sqlite3 format is...
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.
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.
__________________
qgvdial: Google Voice client. All downloads
qgvtp: Phone integration for the n900 that dials out and sends texts using qgvdial.
mosquitto: message broker that implements the MQ Telemetry Transport protocol version 3.
qgvnotify: Google voice and contacts notifier for diablo and maemo.
If you want to thank me, click the Thanks button.
If you'd like to thank my applications, vote to move them to extras.
qgvdial: Google Voice client. All downloads
qgvtp: Phone integration for the n900 that dials out and sends texts using qgvdial.
mosquitto: message broker that implements the MQ Telemetry Transport protocol version 3.
qgvnotify: Google voice and contacts notifier for diablo and maemo.
If you want to thank me, click the Thanks button.
If you'd like to thank my applications, vote to move them to extras.
|
|
2017-10-13
, 22:04
|
|
Posts: 177 |
Thanked: 427 times |
Joined on Sep 2017
|
#20
|
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...
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!
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...
Originally Posted by Markkyboy
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!
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
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!
https://www.dropbox.com/s/9e0298pzki...RIALS.rar?dl=0
..oO(Don't just sit there standing around, pick up a shovel and sweep up!)Oo..