Editing/Appending SQLite files
 

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,

Re: Editing/Appending SQLite files
 

read docs about sqlite commands and try, this is very easy to do

Re: Editing/Appending SQLite files
 

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

Re: Editing/Appending SQLite files
 

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.

Re: Editing/Appending SQLite files
 

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.

Re: Editing/Appending SQLite files
 

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

Re: Editing/Appending SQLite files
 

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?

Re: Editing/Appending SQLite files
 

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!

Re: Editing/Appending SQLite files
 

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?

Re: Editing/Appending SQLite files
 

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