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

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:

Re: Editing/Appending SQLite files
 

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

Re: Editing/Appending SQLite files
 

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
 

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


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!

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!

Re: Editing/Appending SQLite files
 

Please don't give anyone ideas like that...
Can of worms I say!

Re: Editing/Appending SQLite files
 

You don't think a new station at the end of Zoo Street should be called Zoo Street Station? It could happen :)