Repository navigation
Why does the DB get sorted when saved from of version 3.9.0 #830
Description
Details for the issue
Since version 3.9.0 it came to my attention that tables are sorted during save of the database. In version 3.9.0 it was only sorted when you did a refresh of the table, but since version 3.9.1 it always sort the table prior it is saved.
This is very annoying, because I have a config table in the database, which contains configuration parameters for my application which should be saved in the order it was written in the table. Now the table is sorted when saved, I can not read the table anymore, because al configuration items are out of order and my application behaves bad.
Please tell me that this sort option can be switched off somewhere, because I use tables which should not be sorted, but stay in the order the were written.
Useful extra information
I'm opening this issue because:
- DB4S is crashing
- DB4S has a bug
- DB4S needs a feature
- DB4S has another problem
I'm using DB4S on:
- Windows: ( version: 10 )
- Linux: ( distro: ___ )
- Mac OS: ( version: ___ )
- Other: ___
I'm using DB4S version:
- 3.9.1
- 3.9.0
- Other: ___
I have also:
- Tried out the nightly version: https://github.com/sqlitebrowser/sqlitebrowser#windows
- Searched for an existing similar issue: https://github.com/sqlitebrowser/sqlitebrowser/issues?utf8=%E2%9C%93&q=is%3Aissue%20
Activity
chrisjlocke commentedon Oct 16, 2016
Are you sure the records are getting sorted, and not just the view of them? I think in the newer version, the rowid is sorted descending, so newer records are at the top of the list.
Records are not 'sorted' per se, but have a rowid column. Unless this is an auto increment field, rowids will get reused.
justinclift commentedon Oct 16, 2016
@rvalk Ahhh... it sounds like there's a small misunderstanding with how databases work.
It should be pretty easy to make this work for you though. 😄
With SQL databases, you control the order of rows using an "ORDER BY" statement in your SQL query:
http://www.w3schools.com/sql/sql_orderby.asp
With your application, what is the SQL query you are using at the moment? We can probably suggest how to adjust it, so the order is how you want. 😄
chrisjlocke commentedon Oct 16, 2016
There was a question recently on the sqlite mailing list that mentioned sorting, and someone wanted to specifically insert a record between two rowids, so 'shuffle down' the other rowids. Thats not what the rowid column is for. So this was a good explanation:
vtronko commentedon Oct 16, 2016
@justinclift i think he means db sctructure tab.
justinclift commentedon Oct 16, 2016
Hmmm, that's possible too. 😉
rvalk commentedon Oct 16, 2016
Hello,
What I use is:
Select * from adapter;
If rs <> nil then
Do until rs.eof
cgf=cfg+1
Config(cfg)=rs.field("ConfData").stringvalue
rs.MoveNext
Loop
End if
The config array will be used to configure the application.
These entries in the db should stay unchanged as I wrote them in the db, but the last 2 versions of sqlitebrowser sort them ascending before writing the changes.
That is my problem.
With kind regards,
Rob
justinclift commentedon Oct 16, 2016
@rvalk Cool. 😄
You will probably need to update the SQL query to be:
The something_goes_here is a new column, with a row number in it, so that the order is always given in the row number. Kind of like this:
Does that make sense?
By the way, pick a better column name than "something_goes_here". 😄
rvalk commentedon Oct 16, 2016
chrisjlocke, I am sure it is not only the view. If I restart the application after I saved the db changes, the application goes nuts. When I pause th eapplication and check its config, all the values are in the wrong places. This was not the case with the version prior 3.9.0. With 3.9.0 I really also thought that it was only the view, because I saw that refreshing the records sorted the records in the table, but as far as I know it did not save that sorted order. Now with 3.9.1 I am faced with a changed table. Before upgrading to 3.9.1 I never saw my application going nuts.
I dont want an order in this table, I know how to order the table ascending or descending on a certain field, but I want this table unsorted in the order that I wrote the records into the table. Unchanged.
That is the way I do read the table when I start the application, in the order I did write th erecords in the table.
rvalk commentedon Oct 16, 2016
Justin, I dont want my table sorted. I only want it in the way I did write the records to the table. These are only 14 records and it is read in the way it was saved when setup was used of my application.
justinclift commentedon Oct 16, 2016
@rvalk I understand. It's a pain that this has changed from your point of view. 😦
The problem is that SQL databases never guarantee an order for their results without being told to (eg using ORDER BY). SQL databases aren't really a spreadsheet, even though they can look like it at first.
With SQL, you really, really, really need to know how to grab the exact bit of data you want. Using a row order like a spreadsheet is kind of um... playing with fire?
justinclift commentedon Oct 16, 2016
@rvalk As an alternative approach, instead of using an order like the above example... do each of your configuration lines have a unique name or something?
rvalk commentedon Oct 16, 2016
hmmm...ok, then I will use an index from 1 to 14 to keep the table in the order I want it to be.
No, just one field. I use it this way to have just 1 database containing all the files I need and not all kind of seperate files on disk. With only one field I can make changes in the setup of my application without a need to adjust the database fields. If I change something one day and have a config item more, I delete the contents of the table and write 15 records to that table. When something else goes out, I delete the contents of the table again and write 14 records in the table. You see my point, I do not need to add or delete fields in the table. This is by the way the only table I use this way, all other tables are used as you would expect a database is used and do not give any problem. :-)
justinclift commentedon Oct 16, 2016
Ahhh. Yeah. It sounds like adding an index will probably be the best idea then. 😄
rvalk commentedon Oct 16, 2016
Ok, that is what I thought already.
Fyi, Justin, sqlitebrowser is a great application, I use it during development of my application to check if the info is stored in the tables as I want it and also ti flip bits to see how my application responds on the change.
Great work!
justinclift commentedon Oct 16, 2016
Thanks @rvalk. 😄