Reply
Thread Tools
Markkyboy's Avatar
Posts: 433 | Thanked: 727 times | Joined on Oct 2012 @ Costa Blanca, Espaņa
#1
I realise this is not Sailfish specific, but I cannot find the answers I need to this little debacle....

My question;

I have an SQLite file containing 2,500+ lines of code, each line is a train station on the UK rail network. The SQLIte file was compiled 2 years ago, since then, 18 NEW stations have been added.

I have tried many different methods to update my SQLite file and it always ends one of 3 undesirable outcomes;

1). No changes at all;

2). new stations are appended to the list but dumped at the bottom; not in alphabetical order;

3). the file is empty!!......

With the 18 new stations dumped at the bottom of the file, I coudln't find a way to simply rearrange the order and when I did find a way, it doesn't translate when the file is used; i.e the new stations are still physically clumped together at the bottom of the user list!......grrrrr.

The methods used were; a standalone Windows program called DB Browser for SQLite and then I tried SQLite commands in terminal (i.e. - 'sqlite> .open stationsDB.sqlite' ), but I cannot get the deisred result - all listed alphabetically as are the other 2500+ existing stations.

I think it is fair to say, I don't know what to do next, any help appreciated

Regards,
__________________
..oO(Don't just sit there standing around, pick up a shovel and sweep up!)Oo..
 

The Following 3 Users Say Thank You to Markkyboy For This Useful Post:
coderus's Avatar
Posts: 6,436 | Thanked: 12,699 times | Joined on Nov 2011 @ Ängelholm, Sweden
#2
read docs about sqlite commands and try, this is very easy to do
__________________
Telegram | Openrepos | GitHub | Revolut donations
 

The Following 2 Users Say Thank You to coderus For This Useful Post:
Community Council | Posts: 4,920 | Thanked: 12,867 times | Joined on May 2012 @ Southerrn Finland
#3
Why do you want to have the physical file arranged in a particular order?
The whole point of using databases instead of flat files is the query interface, it does not matter at all how the DB is organized; you specify in your query how you want the results to appear...
 

The Following 5 Users Say Thank You to juiceme For This Useful Post:
Markkyboy's Avatar
Posts: 433 | Thanked: 727 times | Joined on Oct 2012 @ Costa Blanca, Espaņa
#4
Simple answer - I don't - but when I add the new data it is all clumped at the bottom of the list in the Stations search window.

As per comment from coderus, I have read the documentation for sqlite3, there isn't much to read, the commands seem quite simple, but I've ended up with the same result as last time - the new stations are NOT listed alphabetically and are at the end of the list and so, when the app is used, the user is presented initially with all stations listed in alphabetical order, that is until the user scrolls to the bottom and finds a handful of stations in no particular order at all.
__________________
..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:
Posts: 1,414 | Thanked: 7,547 times | Joined on Aug 2016 @ Estonia
#5
Originally Posted by Markkyboy View Post
Simple answer - I don't - but when I add the new data it is all clumped at the bottom of the list in the Stations search window.

As per comment from coderus, I have read the documentation for sqlite3, there isn't much to read, the commands seem quite simple, but I've ended up with the same result as last time - the new stations are NOT listed alphabetically and are at the end of the list and so, when the app is used, the user is presented initially with all stations listed in alphabetical order, that is until the user scrolls to the bottom and finds a handful of stations in no particular order at all.
I would suggest to start with some SQL tutorial, for example https://en.wikibooks.org/wiki/Structured_Query_Language.

In short, you insert your data into the database and, when you fetch it, you need to get it in sorted form. So, your SELECT statement should have ORDER BY specified. To make it fast, you need to create an INDEX that would depend on the field used for sorting.

SQL is rather simple and applicable to SQLite nicely. QtSql provides the interface that you could also use for it.
 

The Following 7 Users Say Thank You to rinigus For This Useful Post:
Markkyboy's Avatar
Posts: 433 | Thanked: 727 times | Joined on Oct 2012 @ Costa Blanca, Espaņa
#6
dayum!, back to square ONE......same outcomes, lost everything, nothing happened or....yep, stations clumped at bottom of list.......I have learned how to delete individual lines though!, great!

It's looking like I need to write the database from scratch - Jeeeez!, 2,600 entries >gulp!<
__________________
..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:
Posts: 177 | Thanked: 427 times | Joined on Sep 2017
#7
Did you make the program that reads the database? If you did tweak it's 'search' function to sort by name not ID.

If you didn't I suspect there's a command line way to read the database sorted and save the output as a copy - have you tried that?
 

The Following User Says Thank You to suicidal_orange For This Useful Post:
Markkyboy's Avatar
Posts: 433 | Thanked: 727 times | Joined on Oct 2012 @ Costa Blanca, Espaņa
#8
No, the database was made by someone else, the entire app was by someone else, I've forked it and realised that the DB needs updating, so here I am.

I've lost count of what I've tried and I haven't tried your suggestion, I'm lacking a lot of knowledge here which doesn't help..........I've been at this for 2 days now, patience is wearing thin......I just want to append a handful of stations and move onto the rest of the app.

What do you suggest then command line wise?

I can get my data into the sqlite file - not a problem, so once they are entered to the file, I then need to make the list 'sorted by order'.....I thought this would be quite easy, lol.....famous last words!
__________________
..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:
Posts: 177 | Thanked: 427 times | Joined on Sep 2017
#9
I've never used sqlite but may as well learn it - can probably have a look this evening if not tomorrow. Do you have a link to the database?
 

The Following User Says Thank You to suicidal_orange For This Useful Post:
Markkyboy's Avatar
Posts: 433 | Thanked: 727 times | Joined on Oct 2012 @ Costa Blanca, Espaņa
#10
Thanks, I'll post back here shortly. I need to append the new stations to the file again. Most likely will use my dropbox to share the file.


Sorry if forums users are seeing double posts, my pc/connection/router/something is having a senior moment; meanwhile, I'll try posting content here....

Sqlite file and text file in winzip archive;

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..

Last edited by Markkyboy; 2017-10-13 at 18:31.
 

The Following User Says Thank You to Markkyboy For This Useful Post:
Reply

Thread Tools

 
Forum Jump


All times are GMT. The time now is 14:59.