Re: [sqlite] Order Statistic Trees
On Sat, May 17, 2014 at 9:41 PM, Scott Robison sc...@casaderobison.comwrote: On May 17, 2014 8:40 PM, Simon Slavin slav...@bigfraud.org wrote: Just want to check: this is what it sounds like, right ? Your user has a window open where they are scrolling through the table, ordered by SomeColumn. You need to know whether the window needs to change to reflect the new row. Is that right ? In a nutshell. Further it needs to work in an environment where I control the data model but not the view or controller, so when a new record comes in, I need to know which row it is per current sort order so the model can advertise the new row to the view. SELECT COUNT(*) FROM SomeTable WHERE SomeColumn = value ORDER BY SomeColumn The second SELECT doesn't need the ORDER BY. It will have no effect. Sorry, typo. I meant =, fingers and brain out of sync. How many columns does a table have ? Roughly. If it's not a lot all you need to is create one index for each column that might be chosen as 'SomeColumn'. Or have you already done this and the speed your complaining about is the speed with the index Yes, speed for large collections is inadequate. One technique I used to use in the old days (long before SQL) was to keep a second table which contained just the rows that were shown on the display. So you'd have your full database on backing store and then another table with just 20 rows. To see whether your new row will effect the display, you just compare it with the first and last entries in the display table, which is very small so it's fast to do things with. That would by far make the most sense. Maybe I can invert control between the view model. Thanks for the thought. Just in case anyone is interested: I got pulled off *this* project for a higher priority project for a few days. Those days gave me time to think. Instead of using the in memory vector for my main data (6 strings, a 64 bit int, and a few bits for flags) which *could* get very large for some known worst case scenario, I now use a temp table as a disk based vector. The only index for that table is an integer primary key so I can quickly pull things into ram as needed. The indexes are in memory because I do need to know the relative insertion point of each row for each supported sort order which I can't easily get from SQLite. Those are small enough that I can keep them in ram even for our current worst case scenario (which is about 250,000 messages in a single folder; some people don't believe in organizing their mailboxes). The string based sort columns just have enough info to retrieve the string in question from the database plus the first X bytes of the utf8 string rep. Only if those first bytes are identical between two strings do I need to go to disk, which makes the implementation fast enough (which it wasn't when I had to keep fetching strings for every comparison during an insert). I still like the idea of the order statistic tree, but getting away from the problem for a few days helped me come back to it refreshed as it were and come up with a good enough solution that fit better into the current codebase. Thanks for the ideas! -- Scott Robison ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Small bug with .import shell command
Hello, When the first value of the first line is empty, .import fails: $ echo '|test' ko.csv $ echo '|test' ok.csv $ sqlite3 SQLite version 3.8.4.3 2014-04-03 16:53:12 sqlite .import ko.csv test ko.csv: empty file sqlite .import ok.csv test sqlite An error happens also when the table already exists: sqlite create table test (id text, data test); sqlite .import ko.csv test ko.csv:1: expected 2 columns but found 1 - extras ignored sqlite Maybe CSVReader.z should be pre-allocated ? Regards. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Simple Select from IN - from a newbie.
I'm making good headway with the conversion from the use of strings into a normalized database. With the kind help of everyone on the list, I can retrieve the data from the new structure. I am now at the stage of saving the data to the new structure. Each time I do an update, I need to update multiple rows in the TeamPersonTable with a new orderId and TeamId. I looked up the SQLite documentation and found that Insert works on multiple rows. Can I update multiple rows with one statement ? Cheers. On 5/24/14, fantasia dosa fantasia.d...@gmail.com wrote: I had the misconception that a View contains stored data - come to think of it, it would not be practical if the result set was really large. Thank you everyone for giving me a better understanding of what a view really is about. Cheers. On 5/24/14, Simon Slavin-3 [via SQLite] ml-node+s1065341n75815...@n5.nabble.com wrote: On 23 May 2014, at 7:53pm, Humblebee fantasia.d...@gmail.com wrote: I have a question: so every time you run a query against a view, that view's query is run/updated if not cached . Does this mean that if the View is Temporary, then it's not cached? and for normal views, it's cached? The data associated with a VIEW are never stored at all. Creating a view is more like a way of saving a SELECT statement in your database. When you use a VIEW in a later statement SQL executes the SELECT at that point. So the command needed to reproduce the view can be stored in your database, or it can be TEMP, but the data it would produce is not stored. This is oversimplification since the SELECT itself is never executed, SQLite just figures out how the results would be used in your later statement, but you understand what I mean. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ If you reply to this email, your message will be added to the discussion below: http://sqlite.1065341.n5.nabble.com/Simple-Select-from-IN-from-a-newbie-tp75751p75815.html To unsubscribe from Simple Select from IN - from a newbie., visit http://sqlite.1065341.n5.nabble.com/template/NamlServlet.jtp?macro=unsubscribe_by_codenode=75751code=ZmFudGFzaWEuZG9zYUBnbWFpbC5jb218NzU3NTF8MTk4Njk4NTgwNw== -- View this message in context: http://sqlite.1065341.n5.nabble.com/Simple-Select-from-IN-from-a-newbie-tp75751p75820.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Simple Select from IN - from a newbie.
On Sat, 24 May 2014 03:08:32 -0700 (PDT), Humblebee fantasia.d...@gmail.com wrote: I'm making good headway with the conversion from the use of strings into a normalized database. With the kind help of everyone on the list, I can retrieve the data from the new structure. I am now at the stage of saving the data to the new structure. Each time I do an update, I need to update multiple rows in the TeamPersonTable with a new orderId and TeamId. I looked up the SQLite documentation and found that Insert works on multiple rows. Can I update multiple rows with one statement ? Not in the same way as : INSERT (columnlist) VALUES (valuelist1) , (valuelist2) ... , (valuelistN); You can UPDATE multiple rows, but all of them will get the same column values as specified in SET col1=val1,col2=val2,...,colN=valN . The WHERE cluase determines which rows are updated. Cheers. -- Groet, Cordialement, Pozdrawiam, Regards, Kees Nuyt ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Simple Select from IN - from a newbie.
On 24 May 2014, at 11:08am, Humblebee fantasia.d...@gmail.com wrote: I looked up the SQLite documentation and found that Insert works on multiple rows. Can I update multiple rows with one statement ? No. But if you do not already understand BEGIN and END you should read about them. Doing BEGIN ... UPDATE ... UPDATE ... UPDATE ... END if much faster than doing UPDATE ... UPDATE ... UPDATE . Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Simple Select from IN - from a newbie.
On 2014/05/24 12:08, Humblebee wrote: I'm making good headway with the conversion from the use of strings into a normalized database. With the kind help of everyone on the list, I can retrieve the data from the new structure. I am now at the stage of saving the data to the new structure. Each time I do an update, I need to update multiple rows in the TeamPersonTable with a new orderId and TeamId. I looked up the SQLite documentation and found that Insert works on multiple rows. Can I update multiple rows with one statement ? Not in the way that Insert works (as others have already mentioned) and yes, updating many things when done inside a transaction is much faster as Simon suggested. It's not impossible though - and if you have a real reason to do multiple updates a lot and would find it much better to update in list-like fashion as per the Insert method, there is a way, it simply involves making either a primary key or Unique index and then using inserts. So let's say your TeamPersonTable looks like this: tpId, teamId, personId, orderId 1 : 1 : 4 :1 2 : 1 : 5 :0 3 : 2 : 4 :0 4 : 2 : 5 :1 where tpId is the Primary Key (or other form of Unique Index) you can construct the following Query to update the team and order values (The new teams are say 3 and 7 and the new orders are reversed): INSERT OR UPDATE TeamPersonTable (tpId,teamId,personId,orderId) VALUES (1,3,4,0),(2,3,5,1),(3,7,4,1),(4,7,5,0); Basically this will try to insert the new value, but because the primary key already exists in the table, it will fail the Insert, and fall back to (or handle the error by) using the Update method (as per our SQL) and will simply update (overwrite) the existing row where the primary key is the same. The downside to this method (and why Updates are better) is that you have to supply the ENTIRE row's values every time - where, with UPDATE, you only need to supply (or know) the field(s) you are actually updating. Moreover, when you later change the table's layout (and you likely will), you will have to change all the INSERT queries but not necessarily the UPDATE queries since they only target the fields specified and not the entire row. I know in this table it's probably of little concern, but bigger tables will be really cumbersome using the method above. Achieving what we did up there with the inserts, but using updates might look like this - a bit more SQL, but easier to follow and adjust later: BEGIN TRANSACTION; -- or just BEGIN; UPDATE TeamPersonTable SET teamId=3 WHERE teamId=1; UPDATE TeamPersonTable SET teamId=7 WHERE teamId=2; UPDATE TeamPersonTable SET orderId=0 WHERE tpId IN (1,4); UPDATE TeamPersonTable SET orderId=1 WHERE tpId IN (2,3); COMMIT; -- or END TRANSACTION; or just END; obviously your own criteria for deciding what the Ids must be will make the actual SQL look different, but you get the idea. A note on transactions: The INSERT OR UPDATE thing above is just one statement and as such the transaction is implicit - i.e. SQLite treats it as a transaction, as if we did a start and end before and after it anyway and if one part of it fails, everything gets undone. The later UPDATE functions are multiple, and so enclosed in an explicit transaction - i.e. where we tell it when to start and end. This explicit transaction (in the way I have done it) mainly increases speed, it won't actually undo anything - since there is no ROLLBACK specified (unless the computer crashes mid-transaction in which case it will be rolled back automatically next time you open a connection to it). You may (if needed) add this kind of functionality for the processing of transactions with specifying how to handle errors and the like, which you can read more about in the transaction pages on the SQLite site. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Simple Select from IN - from a newbie.
Thank you everyone for your help and especially for the very detailed and clear explanation. There are so many intricacies of SQL that it's hard to know what to do as a beginner. Having such clear and understandable explanations makes learning the ropes so much easier. I am supplementing my learning of SQL by reading as much as I can using various sources on the internet, but the explanations and help on this forum has surely been the Best. Cheers. On 5/25/14, RSmith [via SQLite] ml-node+s1065341n75823...@n5.nabble.com wrote: On 2014/05/24 12:08, Humblebee wrote: I'm making good headway with the conversion from the use of strings into a normalized database. With the kind help of everyone on the list, I can retrieve the data from the new structure. I am now at the stage of saving the data to the new structure. Each time I do an update, I need to update multiple rows in the TeamPersonTable with a new orderId and TeamId. I looked up the SQLite documentation and found that Insert works on multiple rows. Can I update multiple rows with one statement ? Not in the way that Insert works (as others have already mentioned) and yes, updating many things when done inside a transaction is much faster as Simon suggested. It's not impossible though - and if you have a real reason to do multiple updates a lot and would find it much better to update in list-like fashion as per the Insert method, there is a way, it simply involves making either a primary key or Unique index and then using inserts. So let's say your TeamPersonTable looks like this: tpId, teamId, personId, orderId 1 : 1 : 4 :1 2 : 1 : 5 :0 3 : 2 : 4 :0 4 : 2 : 5 :1 where tpId is the Primary Key (or other form of Unique Index) you can construct the following Query to update the team and order values (The new teams are say 3 and 7 and the new orders are reversed): INSERT OR UPDATE TeamPersonTable (tpId,teamId,personId,orderId) VALUES (1,3,4,0),(2,3,5,1),(3,7,4,1),(4,7,5,0); Basically this will try to insert the new value, but because the primary key already exists in the table, it will fail the Insert, and fall back to (or handle the error by) using the Update method (as per our SQL) and will simply update (overwrite) the existing row where the primary key is the same. The downside to this method (and why Updates are better) is that you have to supply the ENTIRE row's values every time - where, with UPDATE, you only need to supply (or know) the field(s) you are actually updating. Moreover, when you later change the table's layout (and you likely will), you will have to change all the INSERT queries but not necessarily the UPDATE queries since they only target the fields specified and not the entire row. I know in this table it's probably of little concern, but bigger tables will be really cumbersome using the method above. Achieving what we did up there with the inserts, but using updates might look like this - a bit more SQL, but easier to follow and adjust later: BEGIN TRANSACTION; -- or just BEGIN; UPDATE TeamPersonTable SET teamId=3 WHERE teamId=1; UPDATE TeamPersonTable SET teamId=7 WHERE teamId=2; UPDATE TeamPersonTable SET orderId=0 WHERE tpId IN (1,4); UPDATE TeamPersonTable SET orderId=1 WHERE tpId IN (2,3); COMMIT; -- or END TRANSACTION; or just END; obviously your own criteria for deciding what the Ids must be will make the actual SQL look different, but you get the idea. A note on transactions: The INSERT OR UPDATE thing above is just one statement and as such the transaction is implicit - i.e. SQLite treats it as a transaction, as if we did a start and end before and after it anyway and if one part of it fails, everything gets undone. The later UPDATE functions are multiple, and so enclosed in an explicit transaction - i.e. where we tell it when to start and end. This explicit transaction (in the way I have done it) mainly increases speed, it won't actually undo anything - since there is no ROLLBACK specified (unless the computer crashes mid-transaction in which case it will be rolled back automatically next time you open a connection to it). You may (if needed) add this kind of functionality for the processing of transactions with specifying how to handle errors and the like, which you can read more about in the transaction pages on the SQLite site. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ If you reply to this email, your message will be added to the discussion below: http://sqlite.1065341.n5.nabble.com/Simple-Select-from-IN-from-a-newbie-tp75751p75823.html To unsubscribe from Simple Select from IN - from a newbie., visit
