Re: [sqlite] Stitching together Text Files into a New Database
1. Define a separate file in SQLite for each campaign, with the three columns you need: CAMPAIGN1 Time number (assuming the values are always numbers; use TEXT if they contain a mix of letters/numbers) TagA number TagB number CAMPAIGN2 Time TagA TagB Etc. 2. Import each of your text files into the appropriate SQLite table. N.B. How are columns demarcated in your text files? Tabs? Spaces? Commas? You may need to clean the text files up before importing so that a single character is the column delimiter. 3. Create an amalgamation table where all of the data from the separate campaign tables can later be merged: CAMPAIGNS id INTEGER PRIMARY KEY AUTOINCREMENT, Source text Time number TagA number TagB number 4. After you have imported the individual campaign text files into their respective CAMPAIGN# tables, you can copy the data from each of those tables into your amalgamated CAMPAIGNS table by executing this query: insert into CAMPAIGNS (source, time, TagA, TagB) select 'C1' as source, time, tagA, TagB from CAMPAIGN1 5. Repeat step #4 for each CAMPAIGN# table, changing the [source] column value in your select-clause : 'C1', 'C2','C3', etc. At the end of the process, your CAMPAIGNS table will have five columns and look like this (hypothetical data): id source time taga tagb 1 C11231000 199 . . . 123478 C7 188 4567885 You can afterwards create indexes on the columns to speed up queries. E.g. you might want an index on source if you frequently need to ask a question about the rows from the a particular campaign. There are a variety of GUI tools available for SQLite. The one I use most often is a plug-in for Firefox and is found here: http://code.google.com/p/sqlite-manager/ Regards Tim Romano Swarthmore PA On Wed, Aug 25, 2010 at 8:42 AM, Lorenzo Isella wrote: > Dear All, > I am quite new to databases in general and sqlite in particular. > I have a number of data files which are nothing else than text files with a > pretty simple simple structure: there are only 3 columns of integer numbers, > something along these lines > > 123 1000 199 > 123 1100 188 > 125 800 805 > > and so on. > The first column contains only non-decreasing times. > Each of these text files corresponds to a different data collection > campaign (let us call them A,B,C etc...). > I would like (with a minimal effort) to merge them into an sqlite database > where each column now has a name (time, ID tag A, ID tag B) and each record > is also marked according to its original dataset (i.e. looking at any entry, > I must be able to tell the the original data file it belongs to). > Any suggestion is really appreciated > > Lorenzo > > P.S.: of course in the future I may get some new datafiles to merge, hence > it is important that new data can be added effortlessly. > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] partial index?
OK. Thanks for the clarification, Igor and Filip. I was misunderstanding the partial index to work, in effect, like a standard index on a virtual column based on a function that returns null for the "irrelevant" values, with the index defined to ignore nulls. I see now from the "inclusion" test described in the paper that the partial index will not be used if the query itself does not contain the same set of conditions that were used to define the index. That makes the partial index safe, not the trouble I was envisioning. Regards Tim Romano On Fri, Aug 20, 2010 at 8:01 AM, Igor Tandetnik wrote: > Tim Romano wrote: > > Igor, > > Here's the example where a partial index can "hide" rows. > > > > From the wikipedia article cited by the OP: > > > > > > It is not necessary that the condition be the same as the index > criterion; > > Stonebraker's paper below presents a number of examples with indexes > similar > > to the following: > > > > create index partial_salary on employee(age) where salary > 2100; > > > > > > > > What would happen if you issued these queries? > > > > select max(age) from employee > > select avg(age) from employee > > > > Would the ages of employees earning <= 2100 be included? > > Of course. The presence or absence of an index never affect the meaning of > a query - just its performance. > > > Is the > > partial-index used under those circumstances? > > No, I don't see how it could be beneficial for these queries. > -- > Igor Tandetnik > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] partial index?
Igor, Here's the example where a partial index can "hide" rows. >From the wikipedia article cited by the OP: It is not necessary that the condition be the same as the index criterion; Stonebraker's paper below presents a number of examples with indexes similar to the following: create index partial_salary on employee(age) where salary > 2100; What would happen if you issued these queries? select max(age) from employee select avg(age) from employee Would the ages of employees earning <= 2100 be included? Is the partial-index used under those circumstances? -- Tim Romano On Thu, Aug 19, 2010 at 9:16 PM, Igor Tandetnik wrote: > Tim Romano wrote: > > How would you find a row whose column X contained value Y if the > "partial" > > index on column X specified that rows containing value Y in column X > should > > never be returned? > > No one suggests partial index should be capable of hiding anything. The > idea is that, when the query can be proven to only involve rows covered by > the partial index, the index can be used to speed up the query. Otherwise, > it simply won't be used. > -- > Igor Tandetnik > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] partial index?
Typo: "... more performant than partial query" should read "more performant than a partial index". Tim Romano > >> > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] partial index?
Eric, How would you find a row whose column X contained value Y if the "partial" index on column X specified that rows containing value Y in column X should never be returned? If the index hides the row, how do you cause the row to become visible to a query? You have to drop the index. However, I would be willing to accept an index on a *virtual* column whose set of discrete possible values was a subset of the values in the actual underlying table, or some translated form of those values, for example a column that was the result of a function that converted a date to 'Q1', 'Q2', 'Q3', or 'Q4'. Compare: http://www.oracle-base.com/articles/11g/VirtualColumns_11gR1.php If your goal is performance, moving rows out of the table when they cease to meet your business rule's definition of relevance will be more performant than partial query: not only will the index contain just as few nodes, but the table itself will contain fewer rows than the table when using a partial index. And programming would not be more difficult: you'd simply substitute a trigger for the partial index declaration. Moreover, this technique would be highly portable. Partial indexes, not. Regards Tim Romano Swarthmore PA > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] partial index?
Ah, an opportunity for another purist tirade presents itself. I don't have a hack for SQLite but something I consider to be a much better practice that accomplishes the same goal. If your business rules would declare that rows with value X in column Y no longer belong to the set, the most straightforward way to implement such a rule is to move those rows to another table where they do belong. Use an after update/insert trigger to do this Splitting the rows into separate tables In that manner, you could move an inactive|invisible row back into active|visible status if the need should ever arise, simply by changing the column value and moving the row back into the active table. Under the partial index method, how would you ever find a row again once it has become invisible, unless you were perhaps to change or suspend the partial index rule, and cause the missing rows to reappear? The partial index is one very messy thing, fraught with ambiguities, something to avoid. I can imagine other business rules being really bollixed up by the sudden reappearance of zombie rows. Regards Tim Romano Swarthmore PA on the Gender column. On Thu, Aug 19, 2010 at 4:30 PM, Eric Smith wrote: > Afaict sqlite doesn't support indices on subsets of rows in a table, Ю > la http://en.wikipedia.org/wiki/Partial_index -- right? > > Any plans to implement that? > > Are there any known hacks to implement something similar? > > -- > Eric A. Smith > > Keeping Young #3: > Keep the juices flowing by janglin round gently as you move. >-- Satchel Paige > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] trigger or application code
David, Your approach contravenes "best practice" by violating the core referential integrity paradigm: your CHANGES table refers to an item not yet in the ITEMS table and actually governs whether an ITEM item can be created. The child is giving birth to the parent. This is unnecessarily convoluted. In your example, you have the ITEMS table track the most recent amount. That's all it's doing. Now, if that's all you want this table to do ( you don't want to have a full ITEMS master table with item-description, UPC codes, etc etc, for example), you can eliminate the ITEMS table. You could always get the most recent amount with a simple query. select amount from changes where code = ? and changedate = ( select max(changedate) from changes where code = ? ) or in the alternative select amount from changes where code = ? order by changedate desc limit 1 The problem with this approach is that any [code] value under the sun is acceptable; there's no ITEMS table to prevent invalid codes via a foreign key declaration. Regards Tim Romano On Tue, Aug 10, 2010 at 1:20 PM, Igor Tandetnik wrote: > David Bicking wrote: > > I am building an application with these two tables: > > > > CREATE TABLE changes(ChangeDate, Code, AdjAmount, Descr, Creatable); > > CREATE TABLE items(Code, Amount) > > > > Now, what I would like to happen, I insert in to changes, and it updates > the Amount in items. > > > > I can get that with > > > > CREATE TRIGGER changes_after_insert on changes > > BEGIN > > Update items set Amount = Amount + new.AdjAmount where code = new.code; > > END; > > > > And for 90% of the time, that will do what I want. > > But for 8% of the time, the items entry won't be there, so I would like > to insert a new items entry but only if the Creatable > > flag is not 'N'. > > The remaining 2% of the time, the items entry doesn't exist and the > Createable flag is 'N', and I need to ABORT the insert and > > report the error back to the application. > > > > My question is can all this be done in a trigger? > > Yes, but it's pretty awkward: > > BEGIN > select raise(ABORT, 'Item does not exist') > where new.Creatable = 'N' and new.Code not in (select Code from items); > > insert into items(Code, Amount) > select new.Code, 0 > where new.Code not in (select Code from items); > > update items set Amount = Amount + new.AdjAmount > where code = new.code; > END; > > > Or is this type of logic better handled at the application level? > > Quite possibly. > -- > Igor Tandetnik > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Substring (LIKE "%key%") searches, would FTS3 with suffix-tree tokenizer be the fast way?
First, permit me a little rant. As a user, I dislike this kind of incremental search feature if there's no easy way to toggle it or to configure it and the list of items will be large enough to cause a typing lag. The feature can become an intrusive nuisance, the opposite of what is intended. Browsers put this feature on the URL address bar and Google has it on its search-input. Keystrokes entered often get swallowed up. It's worse than typing on a 300 baud dumb terminal, for at least on those ancient machines your characters would eventually be displayed on the green screen, whereas with today's browsers the characters often just get eaten; I find myself having to retype the first few characters of a URL or search term far too often. I agree with Radzi's suggestion. Once you have the initial set of of hits (rowid, name) in an array, do the rest in procedurally rather than going back against the database with a new SQL query and a longer search string. That will be much faster that issuing a new SQL query after every keystroke. I would wait until the user had typed at least two characters before kicking off the initial search because finding every value that contains a common letter is not helpful when the list of matches is a very long one. Regards Tim Romano Swarthmore PA On Fri, Aug 6, 2010 at 9:54 PM, Scott Hess wrote: > On Fri, Aug 6, 2010 at 6:08 PM, Sam Roberts wrote: > > On Fri, Aug 6, 2010 at 11:32 AM, Scott Hess wrote: > >> On Thu, Aug 5, 2010 at 12:42 PM, Sam Roberts > wrote: > >>> FTS3 only searches full terms/words by default, but I think if I built > a custom > >>> tokenizer that returned all the suffix trees for a name: > >> > >> FTS3 can do prefix searches, MATCH 'a*'. Also, it aimed to support > > > > Prefix searches don't allow matching in the middle of words. For > > example, I want "bert" > > to match my name, "roberts". > > Darn. Sorry, was only thinking with half my brain, and that half > connected your problem up with some past idea. You're right, you'd > need the tidbits to get at the interior substrings. > > That said, you should be able to pretty easily copy the current > tokenizer and modify it to return multiple tokens at a single > location. > > -scott > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to write the trigger?
Michael, In this particular instance my questions were posed rhetorically to the OP after I had given his trigger a little more thought. But I can be lazy, for sure, so if you don't mind I'll keep your rebuke ready to hand for the next time it's deserved, which should be soon as it's hot and humid here. Regards Tim Romano Swarthmore PA On Tue, Aug 3, 2010 at 9:10 AM, Black, Michael (IS) wrote: > You could've tested this in the time it took for you to get answer: > > > > From: sqlite-users-boun...@sqlite.org on behalf of Tim Romano > Sent: Tue 8/3/2010 6:53 AM > To: General Discussion of SQLite Database > Subject: EXTERNAL:Re: [sqlite] How to write the trigger? > > > > Is a to-be-inserted row supposed to be included among the counted rows in a > BEFORE INSERT trigger? Could you end up with 5 rows? What happens if you > make this an AFTER trigger? > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to write the trigger?
Is a to-be-inserted row supposed to be included among the counted rows in a BEFORE INSERT trigger? Could you end up with 5 rows? What happens if you make this an AFTER trigger? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to write the trigger?
Could your trigger be running into the syntax restriction described below? I'm not sure if the restriction actually applies to your circumstance -- not sure whether LIMIT and ORDER BY are invalid *anywhere* in a statement in a trigger that deletes a row, i.e. invalid also in a subquery, as you have done. "The ORDER BY and LIMIT clauses on UPDATE<http://www.sqlite.org/lang_update.html> and DELETE <http://www.sqlite.org/lang_delete.html> statements are not supported. ORDER BY and LIMIT are not normally supported for UPDATE<http://www.sqlite.org/lang_update.html> or DELETE <http://www.sqlite.org/lang_delete.html> in any context but can be enabled for top-level statements using the SQLITE_ENABLE_UPDATE_DELETE_LIMIT<http://www.sqlite.org/compile.html#enable_update_delete_limit> compile-time option. However, that compile-time option only applies to top-level UPDATE<http://www.sqlite.org/lang_update.html> andDELETE <http://www.sqlite.org/lang_delete.html> statements, not UPDATE<http://www.sqlite.org/lang_update.html> and DELETE <http://www.sqlite.org/lang_delete.html> statements within triggers." http://www.sqlite.org/lang_createtrigger.html Regards Tim Romano Swarthmore PA On Mon, Aug 2, 2010 at 10:45 PM, liubin liu <7101...@sina.com> wrote: > > I created a table like this： > CREATE TABLE record_trip (trip_id CHAR(1), gp_no CHAR(1), rec_date INTEGER, > trun CHAR(1), ctrl_id CHAR(1), moment_value INTEGER, minutes_value INTEGER, > set_value INTEGER ); > CREATE UNIQUE INDEX i_record_trip ON record_trip (trip_id, gp_no, > rec_date); > > > And the trigger like： > CREATE TRIGGER trig1 before insert on record_trip > when (select count(*) from record_trip where trip_id=new.trip_id AND > gp_no=new.gp_no)>4 > begin > delete from record_trip where trip_id=new.trip_id AND gp_no=new.gp_no AND > rec_date=(select rec_date from record_trip where trip_id=new.trip_id and > gp_no = new.gp_no order by rec_date limit 1); > end; > > > I felt the when clause is wrong. It couldn't realize the intention of > executing the trigger after the num of trip_id and gp_no is larger than 4. > -- > View this message in context: > http://old.nabble.com/How-to-write-the-trigger--tp29331491p29331491.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 > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] crypt() as SQL core function
Whoa, Nellie. :-) I use the abs() function on full-text proximity queries for situations when the order of the words is not relevant. Don't be taking that function out. Regards Tim Romano Swarthmore PA On Mon, Aug 2, 2010 at 2:34 AM, Alexey Pechnikov wrote: > 2010/8/2 Simon Slavin : > > But frankly I'm not even sure that abs(), round() and soundex() should be > in SQLite. I wonder how they ever made it in. > > Soundex function is build-in :-) Compile with -DSQLITE_SOUNDEX. > > -- > Best regards, Alexey Pechnikov. > http://pechnikov.tel/ > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] PRAGMA database_list: insert into table?
A reversed-string index, which would be used transparently on ends-with queries, would not have to store the data redundantly in the base table. That is what I think you are suggesting?Though I would welcome the "smart" approach you suggest, it is far more ambitious than a raw-reverse function. I was trying to keep my request in the shallow end of the swimming pool. The main drawback of the raw-reverse version of the index-optimized ends-with query is the need to store the reversed form in the base table. Disk is very cheap nowadays, so that's not where the real problem lies; the real problem is that the unreversed and the reversed form of the text data might fall out of synch with each other. A smart unicode-savvy "reverse index" for use with ends-with queries could avoid that particular pitfall. But it would require a change to the query-parsing logic. SQLite's unoptimized ends-with queries are fast on desktop PCs if the table fits in available RAM : 5.5 seconds on the first query when the table is still disk-resident and 120ms after the table is ram-resident, this against a table with 265K rows. The common scenario where an index would significantly improve ends-with performance is a (typically handheld) computing device with gigabytes of disk but a relatively underpowered CPU and limited RAM . Shared-resource web hosting environments are another. The shared-hosting environment might be used to justify a request for the smart unicode-savvy approach but not a request for the raw-reverse function, because one would have ready access to the naive solution via the UDF mechanism. Regards Tim Romano Swarthmore PA P.S. A raw-reverse function should probably be called FLIP() so as not to get bollixed up with any reverse() function in UDF libraries. FLIP connotes something inelegant as well :-) On Fri, Jul 30, 2010 at 1:30 PM, Roger Binns wrote: > -BEGIN PGP SIGNED MESSAGE- > Hash: SHA1 > > On 07/30/2010 08:00 AM, Tim Romano wrote: > > Several things might be responsible for there being no "outpouring of > > support" for a raw reverse function. > > Also note that you are proposing it as a very specific workaround for a > specific scenario which is why you want the codepoints reversed rather than > being done "correctly" when dealing with combining unicode codepoints. ie > your required reverse semantics are not correct for 100% of people who > would > want a reverse function :) > > - From your further description it is apparent that you don't actually want > a > reverse function, but rather a string endswith function that is not O(n). > > Another example usage is processing web logs, where for example you want to > find all accesses to names ending in 'google.com'. > > > That the SQL dev team has not responded I take as an indication that this > > would be very low priority for them, > > I don't think the issue was articulated well enough (I think my second > paragraph above is better :-) and the solution you required is not a SQL > standard nor fits in with how SQLite development works in general, and you > rejected the existing means by which SQLite allows people to customise it. > > You can of course demand their attention through the mechanisms listed at: > > http://www.hwaci.com/sw/sqlite/prosupport.html > > > Anyway, the lack of such a function is only a problem for me vis-a-vis an > > amalgamation distribution of SQLite. > > It is only a problem if you are using an environment where SQLite is hidden > inside a black box and that environment prevents you from using any of the > SQLite extension mechanisms. Given SQLite's very liberal license that > really is a self inflicted problem. > > What I suggest you do is a survey of how other databases do this, and > provide some timings when using SQLite as it currently stands and with a > workaround such as using a custom reverse function. It becomes a far more > compelling case when you can show query times going from unusable to quick, > as well as some example usage scenarios. (Yes this is a fair bit of work, > but that is exactly what you are requesting of the SQLite team - why should > they do lots of work if you are not prepared to.) > > There are also many other possible solutions than a visible reverse > function. For example an index behind the scenes could be created that > allows searching from either end, or something similar with a collation. > > > I may decide to forget about it as a > > target platform and rewrite the app as a web-service, where I would > indeed > > have access to the UDF mechanism. But there was merit in having the > > application work in offline mode too. > > Are the data sets the same size? I'd expect o
Re: [sqlite] PRAGMA database_list: insert into table?
Roger, I'm not thinking of you as a gatekeeper but as a member of the "vocal opposition". You did oppose my request, after all, you didn't simply ignore it. But your opposition was not unreasonable -- it was simply based on certain assumptions that I saw an opportunity to address. Several things might be responsible for there being no "outpouring of support" for a raw reverse function. Probably the two most important are 1) it can be readily accomplished as a UDF, if you have access to the UDF mechanism; and 2) how many people are writing applications where "string ends-with" queries are just as important as "string begins-with" queries? Such queries are the bread-and-butter of some lexicographical or grammatical natural-language oriented applications. I would use the function to flip the raw codepoint order of text, storing the reversed version redundantly in a second column, so that ends-with queries would have access to an index (with GLOB): these queries would be restated as starts-with queries against the reversed-format column. The search-term would be flipped by the function too. ... where reversedColumn GLOB reverse( ? ) Or maybe even there will be "virtual columns" in SQLite so that there would be no need to store the reversed form redundantly in the base table. The second column would be defined as virtual, pointing at an actual column, but given a reversed collation, then indexed. That the SQL dev team has not responded I take as an indication that this would be very low priority for them, below the horizon of their attention, though I think the waters of the discussion may have been clouded by the issue of an intelligent reverse function which would keep multi-codepoint combining forms intact during the reversal process. I see no reason why the two functions could not simply coexist under different names. Oracle and SQLServer both have raw reverse functions now. Anyway, the lack of such a function is only a problem for me vis-a-vis an amalgamation distribution of SQLite. I may decide to forget about it as a target platform and rewrite the app as a web-service, where I would indeed have access to the UDF mechanism. But there was merit in having the application work in offline mode too. Regards Tim Romano Swarthmore PA On Tue, Jul 27, 2010 at 12:10 PM, Roger Binns wrote: > -BEGIN PGP SIGNED MESSAGE- > Hash: SHA1 > > On 07/27/2010 07:07 AM, Tim Romano wrote: > > I will consider it progress if we could get beyond the two stock replies: > > "you should do that in your application" and "you could do that in an > > extension". > > You seem to think I am sort of gatekeeper of what goes into SQLite. I am > not. However I do try help - ie try to discern your goals and show how > they > can best be met given the architecture of SQLite and how the development > team is most likely to behave. Please feel free to ignore me. > > Also note: > > 1: There was no great outpouring of consensus that SQLite should be changed > > 2: The SQLite dev team have not responded at all > > Roger > -BEGIN PGP SIGNATURE- > Version: GnuPG v1.4.10 (GNU/Linux) > Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ > > iEYEARECAAYFAkxPBQAACgkQmOOfHg372QTUSACeM5kPZRxHWONCXpP22EbXp7b0 > TDMAoNaEY+9HIohMvf2XIUwC5EucxYdt > =1BbC > -END PGP SIGNATURE- > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] PRAGMA database_list: insert into table?
Roger, I do not expect my request to be a priority. I have suggested only that such black-box amalgamations be kept in mind as a mitigating factor when the architects are assigning a priority to a feature request. In those cases where it is impossible for the end-user to avail himself of the load_extension capabilities of SQLite, the core may be the only place to implement a desired feature. The function I asked for (raw/naive codepoint-by-codepoint string reverse) does exist in other mainstream databases, including Oracle and SQLServer. I will consider it progress if we could get beyond the two stock replies: "you should do that in your application" and "you could do that in an extension". Regards Tim Romano Swarthmore PA On Mon, Jul 26, 2010 at 6:37 PM, Roger Binns wrote: > -BEGIN PGP SIGNED MESSAGE- > Hash: SHA1 > > On 07/26/2010 12:17 PM, Tim Romano wrote: > > But Roger, the "layer sitting in front of SQLite" is a programming > > environment which provides its own (black-box) connectivity to SQLite, > and > > it isn't going to be calling any DLL into which one will have injected a > UDF > > library in the manner you have laid out, and it's not going to let the > > developer load an extension either. That's what I've been trying to > make > > clear. > > You keep missing what I am saying :-) > > This is only an issue if the layer includes SQLite as an amalgamation > statically in which case it is a black box as you described.(*) > > If that layer uses SQLite as a DLL then you load the same SQLite DLL > first, tell it call the callback of your choice on connections being > opened, and then when the layer uses SQLite (which will be the same DLL) > and opens a connection, your callback is called. > > (*) If your problem is now that you have chosen to use some sort of > access layer, and that layer has chosen to hide SQLite internally, and > they haven't provided access to some SQLite functionality, and they > won't if you ask them to, then expecting this to be a priority to the > SQLite team is rather strange. > > Roger > -BEGIN PGP SIGNATURE- > Version: GnuPG v1.4.10 (GNU/Linux) > Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ > > iEYEARECAAYFAkxODhwACgkQmOOfHg372QStjwCfdBiKhnNrcpMHCqcWPJI3DzSu > ejUAoL2PmX3pJ8/1c/RH8zYXRfq1pZyA > =T6Bb > -END PGP SIGNATURE- > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] PRAGMA database_list: insert into table?
But Roger, the "layer sitting in front of SQLite" is a programming
environment which provides its own (black-box) connectivity to SQLite, and
it isn't going to be calling any DLL into which one will have injected a UDF
library in the manner you have laid out, and it's not going to let the
developer load an extension either. That's what I've been trying to make
clear.
Regards
Tim Romano
Swarthmore PA
On Mon, Jul 26, 2010 at 12:07 PM, Roger Binns wrote:
> -BEGIN PGP SIGNED MESSAGE-
> Hash: SHA1
>
> On 07/26/2010 06:03 AM, Tim Romano wrote:
> > The goal is to expose the UDF to the "layer sitting in front". Are you
> > saying sqlite3_auto_extension allows me to run a program, once, that will
> > register a piece of code that is loaded whenever ANY OTHER PROGRAM
> creates a
> > connection to my database file? And that this happens *transparently* --
>
> Nope. You have to do something each program session. The steps:
>
> - - LoadLibrary("sqlite.dll") then call sqlite3_auto_extension registering
> callback that adds UDF etc
> - - Now use your database access layer
>
> The operating system will use the already loaded sqlite dll rather than
> loading an additional copy when the database access layer requests SQLite
> and then the callback is called when each database is opened (aka
> "connection").
>
> You can also do the first step using a technique known as dll injection
> where you have a shared library that loads SQLite, calls auto_extension,
> implements whatever UDF functions you want etc. Your library is then
> automatically loaded and run in any process:
>
> http://en.wikipedia.org/wiki/DLL_injection
>
> Roger
> -BEGIN PGP SIGNATURE-
> Version: GnuPG v1.4.10 (GNU/Linux)
> Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/
>
> iEYEARECAAYFAkxNstQACgkQmOOfHg372QTzyACg3R2pBsKSJT/a5g6ApMc1Ul1D
> +i0AoIAHw4m+X8sIXA0EXPmbd3blmcSp
> =E8dR
> -END PGP SIGNATURE-
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] PRAGMA database_list: insert into table?
Since this discussion might help the OP too, I hope it's not regarded as a thread hijack. I will create a new thread otherwise. Mr. v3meo, your thoughts? A question about this advice: "If the layer sitting in front of SQLite doesn't expose create function then you can still do so. Load the SQLite DLL separately and use sqlite3_auto_extension to register a piece of code that is called back for each new connection that registers the UDF." The goal is to expose the UDF to the "layer sitting in front". Are you saying sqlite3_auto_extension allows me to run a program, once, that will register a piece of code that is loaded whenever ANY OTHER PROGRAM creates a connection to my database file? And that this happens *transparently* -- the other program simply creates a connection as usual, and does nothing else, and the extension is ready to be used in sql statements? The other program (the "layer sitting in front of SQLite") does not first need to invoke sqlite3_enable_load_extension? Is the entry point to the extension being written to the database by sqlite3_auto_extension? Regards Tim Romano Swarthmore PA On Sun, Jul 25, 2010 at 1:24 PM, Roger Binns wrote: > -BEGIN PGP SIGNED MESSAGE- > Hash: SHA1 > > On 07/25/2010 07:08 AM, Tim Romano wrote: > > Something that can be knocked out in a half-hour > > Nothing gets banged out in half an hour with SQLite :-) Using a string > reverse example: > > - - The code has to be hooked in various places > - - The function has to deal with the various ways strings could be passed > and > returned, especially UTF8 vs UTF16 encoding > - - The function has to make sense with things like combining diacritics > and > surrogate pairs > - - The function has to deal with bad parameters (eg passed an integer, > float > or blob to reverse) > - - The function has to deal with running out of memory > - - Documentation has to be added in several different places including > inside > the code, "core functions" in syntax pages etc > - - Various specifications have to be added as well as evidence marks in > the > code (for example look at Reading Data in > http://www.sqlite.org/fileio.html > and note the [H35010] and similar annotations and at the timelime for EV > style annotations) > - - The TCL test suite has to be updated, not just for correct behaviour > but > also verifying behaviour with all the bad inputs, running out of memory, > invalid UTF8/16 etc > - - The TH3 test suite has to be updated (C code generation based) and > verify > that it maintains 100% MC/DC test coverage > - - If this function is present in other database engines then the SQL > Logic > Test suite has to be updated and outputs compared > - - The changelog has to be updated > > The code then has to be maintained and tested for all future SQLite 3 > releases and internal changes (eg if UTF32 was added, new/changing memory > allocators). > > See http://ometer.com/features.html and especially the Linus Torvalds > posts > it links to. > > > Some application > > runtime environments are "sandboxed" and do not give the developer the > > freedom to call an external library, > > But eventually the SQLite library is used so there is some mechanism to > call > external libraries, although there may be restrictions. > > > or to add UDFs to their implementation of SQLite. > > This is a good example. If the layer sitting in front of SQLite doesn't > expose create function then you can still do so. Load the SQLite DLL > separately and use sqlite3_auto_extension to register a piece of code that > is called back for each new connection that registers the UDF. > > > Of course I understand it is not the fault of the SQLite architects or > its > > author when a runtime environment restricts what can be done with SQLite. > > And it is really the authors of those environments that should be contacted > about making their wrapping more complete and facilitating full usage of > SQLite. > > And failing that you can still implement what you need in your host > programming environment (eg a reverse function). Sure it may be a lot less > efficient or more hassle, but you choose that environment :-) > > > I'm only asking that such environments be kept in mind as one of the > > criteria when assigning a priority to a requested feature. > > You are probably not aware that the SQLite C API is designed specifically > to > be easily used by other development environments. It is an absolute joy to > wrap. (Yes there a few minor quirks, but compared to some other APIs they > are insignificant.) > > Roger > -BEGIN PGP SIGNATURE- > Version: GnuPG v1.
Re: [sqlite] PRAGMA database_list: insert into table?
Roger, Clearly not every feature that has found its way into SQLite is useful to "the majority" of the user base, but I will accept the core philosophical position here as not unreasonable--a requested feature that benefits very few use-cases might well be placed lower on the to-do list than one everybody wants. And yet there are some very simple and lightweight functions that are easy to add to the core without fear of bloat. Something that can be knocked out in a half-hour might get a higher a priority than something everyone desires but is far more involved, and could take many man-weeks to get right. You write: "There is no reason why you can't talk to the library simultaneously via ADO.net as well as via the SQLite API directly." It is not always possible to do what you have suggested. Some application runtime environments are "sandboxed" and do not give the developer the freedom to call an external library, or to add UDFs to their implementation of SQLite. If the functionality is not present in the core database, the developer could be out of luck when programming in such environments. Of course I understand it is not the fault of the SQLite architects or its author when a runtime environment restricts what can be done with SQLite. I'm only asking that such environments be kept in mind as one of the criteria when assigning a priority to a requested feature. @Sam: I understand the open-source and TCL origins of SQLite, but the essence of something does not always come out of its origins; destiny and destination can shape things too. Who could have known, way back when, that SQLite would influence the direction of the web? Regards Tim Romano Swarthmore PA On Sat, Jul 24, 2010 at 10:57 AM, Roger Binns wrote: > -BEGIN PGP SIGNED MESSAGE- > Hash: SHA1 > > On 07/24/2010 04:42 AM, Tim Romano wrote: > > Quite a few users of SQLite these days are not wrapping > > the SQLite libraries in their own client app but are communicating with > the > > database via a bridge as if it were a remote server engine. > > Yes, but the SQLite library is still local within the process in that case. > There is no reason why you can't talk to the library simultaneously via > ADO.net as well as via the SQLite API directly. (If you are using pragmas > then you are already having SQLite specific code.) > > > Your opposition to my request several months ago for a raw reverse > > function was colored in this way. You did not acknowledge at the time > that a > > raw-reversed (and hence possibly malformed) sequence of unicode > codepoints > > could give middleware the hiccups, and insisted that it this reversal be > > done "in the application". > > SQLite doesn't have a reverse function as shipped, and so is not the one > creating invalid data. I'll happily acknowledge that malformed Unicode is > a > bad thing under all circumstances. > > The license of SQLite allows you to do anything you want with it. (The > trademark prevents you calling the result 'SQLite'.) You can add, change, > delete etc anything. You can redistribute the changes or keep them secret. > You can charge for them. > > What many of these requests amount to is wanting someone else to make a > change (typically the SQLite developers) and for the change to be > distributed as part of SQLite. The bar for that is *considerably* higher > and you would need to demonstrate the value to the majority of the user > base > and why the extensive existing mechanisms (extensions, the SQLite API etc) > are not sufficient. > > The "opposition" is pointing out that bar, and suggesting alternate > approaches. (Note I am not a core developer nor do I speak for them but > have been around long enough to observe what they usually do.) > > Roger > -BEGIN PGP SIGNATURE- > Version: GnuPG v1.4.10 (GNU/Linux) > Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ > > iEYEARECAAYFAkxK/2kACgkQmOOfHg372QSyrgCfaMDkggv6PObyADTR+Cfdz68E > b3YAnj/ihpG0DVet4Y/5Z/RlSDs9QuWR > =K1/M > -END PGP SIGNATURE- > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] PRAGMA database_list: insert into table?
Roger, It would be more accurate to say that SQL is a library which *can* live inside an application. Whether it actually does live inside it will depend on how one is using SQLite. When it is used as a "back-end" reached via middleware, such as the ADO.NET driver, SQLite does not live inside the client application. Quite a few users of SQLite these days are not wrapping the SQLite libraries in their own client app but are communicating with the database via a bridge as if it were a remote server engine. The reason I bring this up is that your replies are sometimes colored by the assumption that one is using SQLite in library-mode, which may not be the case. Your opposition to my request several months ago for a raw reverse function was colored in this way. You did not acknowledge at the time that a raw-reversed (and hence possibly malformed) sequence of unicode codepoints could give middleware the hiccups, and insisted that it this reversal be done "in the application". Regards Tim Romano Swarthmore PA On Fri, Jul 23, 2010 at 11:19 PM, Roger Binns wrote: > -BEGIN PGP SIGNED MESSAGE- > > Remember that SQLite is a library - it lives inside your application - and > is not some remote unchangeable component. > > > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] error in sum function
Indeed, Igor, it is documented behavior, but my point was to reply to the OP, who had written ruefully about SQLite's laissez-faire approach to datatypes. For the OP's benefit, I wanted to demonstrate the behavior, show what can be done with CAST, and then finally to show what can be done with CAST in a CHECK constraint. Using a CAST in the CHECK constraint can prevent the insertion of REALS into a column one has defined as INTEGER. It's possible to turn loose-loafer-wearing SQLite into a veritable buttoned-down wing-tipped data martinet. Regards Tim Romano Swarthmore PA On Thu, Jul 15, 2010 at 8:07 AM, Igor Tandetnik wrote: > Tim Romano wrote: > > So it would appear that if the numeric value to be inserted can be > coerced > > to INTEGER without loss, it will in fact become an INTEGER, otherwise it > > stay what it was, REAL. > > ..., the behavior that is amply documented at > http://sqlite.org/datatype3.html > -- > Igor Tandetnik > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] error in sum function
Ignore the typo: should be 2 | 2.2 As someone who tends to make typogarphical errors, I do like forums with post-editing capabilities much better than mailing lists. Regards Tim Romano Swarthmore PA ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] error in sum function
I tried this in SQLite Manager for Firefox.
CREATE TABLE "PRODUCTS" ("id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL
UNIQUE , "product" TEXT, "rank" INTEGER)
insert into products (product, rank) values ('gizmo', 1.0)
insert into products (product, rank) values ('widget', 2.0)
insert into products (product, rank) values ('foo', 2.2)
insert into products (product, rank) values ('foo2', 2.00)
select typeof(rank) from products
integer
integer
real
So it would appear that if the numeric value to be inserted can be coerced
to INTEGER without loss, it will in fact become an INTEGER, otherwise it
stay what it was, REAL.
TEST:
select cast(rank as integer), rank from products where cast(rank as
integer) <> rank
1 |2.2
So, if the OP executes the TEST query above, it should discover any value
with something other than zero(s) to the right of the decimal point.
And you can convert that test into a check constraint to prevent non-integer
values from being inserted into the table:
CREATE TABLE "main"."PROD" ("id" INTEGER PRIMARY KEY AUTOINCREMENT NOT
NULL UNIQUE , "product" TEXT,
"rank" INTEGER check (cast(rank as INTEGER) =rank) )
Regards
Tim Romano
Swarthmore PA
On Wed, Jul 14, 2010 at 10:19 AM, Bogdan Ureche wrote:
> >
> >
> > SQLite Expert (which I use extensively in the Pro version) enforces
> > displaying types as declared. So if you declare your column as INTEGER
> > (or INT, ...) then it will display integers regardless of the actual
> > individual data type using common conversions. This is a side effect
> > of the Delphi grid component that Bogdan uses.
> >
> >
> This is the default behavior in SQLite Expert but can be overridden. If you
> store floating-point values in columns declared as INTEGER, you might want
> to change the default type mappings to INTEGER -> Float (or WideString) and
> you will see the floating-point values correctly in the grid.
>
> Bogdan Ureche
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Query critique
If you had the following tables
Table PEOPLERANKINGS(personid INTEGER PRIMARY KEY, personname TEXT, ranking)
Table FRIENDS(personid1, personid2)
and an index on
PEOPLERANKINGS.ranking
and FRIENDS.personid1,FRIENDS.personid2 is a composite unique primary key
You could get the top 10 ranked people
select * from PEOPLERANKINGS order by ranking desc limit 10
and get your own ranking and the ranking of your friends:
select peoplerankings.* from PEOPLERANKINGS
where personid IN
(select personid2 from FRIENDS where personid1 = ?yourId? )
NOTE: befriend yourself by default in the FRIENDS table.
Regards
Tim Romano
Swarthmore PA
On Fri, Jul 9, 2010 at 5:52 AM, Benoit Mortgat wrote:
> On Fri, Jul 9, 2010 at 11:08, Ian Hardingham wrote:
> > Hey guys.
> >
> > I have a query which is very slow, and was wondering if there was any
> > advice you guys had on it.
> >
> > Here are two table definitions:
> >
> > CREATE TABLE IF NOT EXISTS globalRankingTable (id INTEGER PRIMARY KEY
> > AUTOINCREMENT, name TEXT NOT NULL UNIQUE, ranking TEXT, score REAL,
> > record TEXT);
> >
> > CREATE TABLE IF NOT EXISTS friendTable (id INTEGER PRIMARY KEY
> > AUTOINCREMENT, simpleId TEXT NOT NULL UNIQUE, player TEXT, friend TEXT);
> >
> > And here is my query (written in a script language):
> >
> > db.query("SELECT * FROM (SELECT DISTINCT * FROM globalRankingTable WHERE
> > upper(name) = upper('?') OR id < ? union all SELECT a.* FROM
> > globalRankingTable a, friendTable b WHERE upper(b.player) = upper('?')
> > AND upper(b.friend) = upper(a.name)) ORDER BY score DESC", 0,
> > %client.username, %globId, %client.username);
> >
>
> Create an index either on player column or friend column in your second
> table.
> CREATE INDEX i_friendTable ON friendTable (player ASC);
>
>
> --
> Benoit Mortgat
> 20, avenue Marcel Paul
> 69200 Vénissieux, France
> +33 6 17 15 41 58
> +33 4 27 11 61 23
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to select an entry that appears <=n times and only show n times if it appears more than n times?
I'm not quite sure what you meant by "only show n times if it appears more
than n times'. Is the pseudo-code below analogous to what you are trying
to do?
for each surname in
(select surname from phone-book order by surname)
{
print surname no more than n times
}
Regards
Tim Romano
Swarthmore PA
> > On 2 Jul 2010, at 5:15pm, Peng Yu wrote:
> > I want to select an entry that appears
> > <=n times and only show n times if it appears more than n times. I
> > think that "group by" might help.
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] create table {table-name} as select.... table definition is imperfectly cloned
Here is what I wrote to Jay earlier this morning but meant to send to the entire list. I am still in the habit of hitting Reply in Google Mail when I should be hitting a different button that also says Reply. Jay, I agree with 99.44% of what you say. If in the future ALTER TABLE would allow a constraint to be added to a table, then I would agree somewhat less because the CREATE TABLE x as SELECT ... syntax does indeed create a persistent database object. To call that object a "resultset" and not a "table" is true enough, but perhaps only for the time being. @Pavel: I have documented the problem where Adobe treats INT PK as INTEGER PK and returns the wrong rows in joins. I have encountered that problem in the real world and have been bitten in the ass by it. But the wound is healed (I recreated my tables to use INTEGER PK) and my buttock shows now only the faint marks where the cur's fangs sank in. I agree that CREATE TABLE x as SELECT ... does not create a primary key. I was speaking so sloppily there. But it creates a persistent table without a primary key, changing the prototype's INTEGER to INT in the resulting "tabular object". Inasmuch as ALTER TABLE cannot (today) add a PK constraint to that tabular object, it will remain a table without a primary key. However, I do not know how that table-without-key would fare if the SQLite database file were queried by Adobe AIR. My guess is that one would need a bandage. Regards Tim Romano Swarthmore PA On Thu, Jul 1, 2010 at 9:21 AM, Pavel Ivanov wrote: > > No, I am not asking SQLite to "emulate an error" in Adobe's code. Rather > I > > am suggesting this: if SQLite is going to distinguish in any way between > INT > > and INTEGER on primary key definitions, the CREATE TABLE X as SELECT... > > syntax ought not to produce a table with an INT primary key if the > prototype > > had INTEGER. > > Tim, why do you keep insisting on your claim that came out of thin > air? I first thought that it has some ground under it but as Jay said > and as below log shows you are totally wrong. > > SQLite version 3.6.23.1 > Enter ".help" for instructions > Enter SQL statements terminated with a ";" > sqlite> create table t (id integer primary key, t text); > sqlite> .s > CREATE TABLE t (id integer primary key, t text); > sqlite> insert into t values (1, '1'); > sqlite> insert into t values (999, '999'); > sqlite> create table t_copy as select * from t; > sqlite> .s > CREATE TABLE t (id integer primary key, t text); > CREATE TABLE t_copy(id INT,t TEXT); > sqlite> > > > You see? There's no INT PRIMARY KEY and there will never be. So Adobe > will never be confused with your copied table because it as well as > SQLite will understand that new table doesn't have primary key at all. > And in this case it doesn't matter whether you have INT or INTEGER. So > what are you fighting for after all? > > > Pavel > > On Thu, Jul 1, 2010 at 7:41 AM, Tim Romano > wrote: > > If cross-implementation portability is a stated design goal, Adobe's > > departure from the "authoritative" behavior is indeed a bug. If > > cross-implementation portability is not "officially" supported but is > simply > > something that users might expect and attempt, then Adobe's departure > from > > the authoritative behavior is just a pothole on the lonely highway. > > > > I don't know the particulars of how it happened. I think Adobe may not > have > > envisioned SQLite database files created outside of AIR being used in AIR > > apps. They were probably thinking in terms of "local storage" databases > > instantiated and defined through the AIR API. > > > > No, I am not asking SQLite to "emulate an error" in Adobe's code. Rather > I > > am suggesting this: if SQLite is going to distinguish in any way between > INT > > and INTEGER on primary key definitions, the CREATE TABLE X as SELECT... > > syntax ought not to produce a table with an INT primary key if the > prototype > > had INTEGER. > > > > > > Regards > > Tim Romano > > Swarthmore PA > > > > > > > > On Wed, Jun 30, 2010 at 9:24 AM, David Bicking > wrote: > > > >> > >> > >> All things considered, I would say this is clearly and 100% a bug in > >> Adobe's code. The given SQL nowhere mentioned rowid, yet they are > >> joining on rowid... that has got to be an error. > >> > >> And how is Adobe Air doing this? Did they write there own sql parser? > >> Ther
Re: [sqlite] create table {table-name} as select.... table definition is imperfectly cloned
If cross-implementation portability is a stated design goal, Adobe's departure from the "authoritative" behavior is indeed a bug. If cross-implementation portability is not "officially" supported but is simply something that users might expect and attempt, then Adobe's departure from the authoritative behavior is just a pothole on the lonely highway. I don't know the particulars of how it happened. I think Adobe may not have envisioned SQLite database files created outside of AIR being used in AIR apps. They were probably thinking in terms of "local storage" databases instantiated and defined through the AIR API. No, I am not asking SQLite to "emulate an error" in Adobe's code. Rather I am suggesting this: if SQLite is going to distinguish in any way between INT and INTEGER on primary key definitions, the CREATE TABLE X as SELECT... syntax ought not to produce a table with an INT primary key if the prototype had INTEGER. Regards Tim Romano Swarthmore PA On Wed, Jun 30, 2010 at 9:24 AM, David Bicking wrote: > > > All things considered, I would say this is clearly and 100% a bug in > Adobe's code. The given SQL nowhere mentioned rowid, yet they are > joining on rowid... that has got to be an error. > > And how is Adobe Air doing this? Did they write there own sql parser? > There own VDBE? (Hopefully I got those initials correct.) Are they > calling Sqlite or did they write their own library claiming it is > compatible with the Sqlite file format? > > While I don't necessarily disagree with what you are saying, in this > example you appear to be asking the Sqlite developers to emulate an > error in someone else's code. > > David > > PS. Probably should remove my opinions from this, because the only > reason I am sending this is because I am curious what the hell Adobe is > doing that is causing this confusion. > > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] create table {table-name} as select.... table definition is imperfectly cloned
Jay, Let me try to "scratch on the wall" one more time and perhaps my point will make its way through. Notwithstanding your insistence that INT and INTEGER are the same in SQLite, *with respect to use in the PRIMARY KEY definition* there are subtle differences. In the authoritative version of SQLite, an INTEGER PRIMARY KEY is an alias for the rowid but an INT PRIMARY KEY (or smallint or any other flavor of int) is not an alias for the rowid but an "ordinary" column. From the docs: "The special behavior of INTEGER PRIMARY KEY is only available if the type name is exactly "INTEGER" in any mixture of upper and lower case. Other integer type names like "INT" or "BIGINT" or "SHORT INTEGER" or "UNSIGNED INTEGER" causes the primary key column to behave as an ordinary table column with integer affinity <http://www.sqlite.org/datatype3.html#affinity> and a unique index, not as an alias for the rowid." http://www.sqlite.org/lang_createtable.html I pointed out above that this subtle behavioral difference in the authoritative version was *not* honored by Adobe in its implementation of SQLite. The CAVEAT: Adobe's departure from the authoritative version in this regard may prove to be a pitfall for anyone who may ever need to share a SQLite database file they have created with someone who is using software based on the Adobe implementation. The RECOMMENDATION: My advice for anyone who may find themselves in that position would be to eschew INT PRIMARY KEY and to use INTEGER PRIMARY KEY. Corollary advice would be to eschew all use of the CREATE TABLE FOO AS SELECT syntax because it produces a table with INT PRIMARY KEY definition even if the prototype table had INTEGER PRIMARY KEY. (@Puneet: you see, I am hardly trying to promote the use of CREATE TABLE FOO AS SELECT.. syntax) The SIDEBAR: I am not saying that the CREATE TABLE FOO AS SELECT syntax must do something other than what it is doing now, thought I certainly recommend the architects consider such a change in light of the potential pitfalls of the current behavior in the wider context of "portability" of SQLite database files. I mean cross-implementation portability not cross-platform portability. The EXAMPLE: If you create a database in the authoritative version of SQLite using INT PRIMARY KEY (rather than INTEGER PRIMARY KEY), when you share the database with your Adobe-using affiliate, all hell will break loose. I will repeat the example I gave above: Let's say you had created this table in authoritative SQLite using INT rather than INTEGER PRIMARY KEY: CREATE TABLE FOO (id INT PRIMARY KEY, name TEXT) insert into FOO(1,'Groucho') insert into FOO(2,'Chico') Insert into FOO(999, 'Harpo') And then you have another table MOVIES where FOOID is a foreign key pointing back to FOO.id: MOVIES fooid|moviename 1|Duck Soup 2|Duck Soup 3|Duck Soup ** Note that Harpo's id is 999 in FOO and that table MOVIES contains no such foreign key. Now your Adobe affiliate executes this query: select MOVIES.moviename, FOO.name from MOVIES INNER JOIN FOO ON FOO.id = MOVIES.fooid Unless Adobe has since changed the behavior they told me they were not going to change, the query above will produce this result in Adobe: Duck Soup|Groucho Duck Soup|Chico Duck Soup|Harpo But Harpo should *not* appear in the resultset. Adobe looks for the row in FOO whose rowid =3 and finds Harpo. If Adobe were following authoritative SQLite, it would look for the row in FOO whose "ordinary column" id = 3 and find no such row. As I said, all hell can break loose because the queries don't break -- they simply return the wrong results which on their face may seem plausible and could therefore go undetected as erroneous until well after the damage (whatever it may be) has been done. CONCLUSION: wherever the SQLite architects find opportunities to tighten up behaviors in this nexus, the tightening up effort would be well-spent, IMO. Which brings me back full circle to the subject line of this posting. Regards Tim Romano Swarthmore PA On Tue, Jun 29, 2010 at 12:30 PM, Jay A. Kreibich wrote: > On Tue, Jun 29, 2010 at 11:46:34AM -0400, Tim Romano scratched on the wall: > > > The core concern, at least as I see it, is the undesirable effects of > > sharing data between implementations that do not handle INT and INTEGER > > primary keys compatibly. > > The only known program that can read SQLite database files is SQLite. > In the SQLite world, INT and INTEGER are the same. The only way to > get the SQL generated by CREATE TABLE ... SELECT back out of an > SQLite database is to dump the data into a SQL text file. If you read > that SQL back into SQLite, it will know exactly what to do with it. > > If you read that SQL into any other database, all best are off, and > thi
Re: [sqlite] create table {table-name} as select.... table definition is imperfectly cloned
Puneet,
I am simply pointing out a potential pitfall. Putting up a highway sign
that says "Soft Shoulder" is one way to go about things. Widening the
shoulder and perhaps paving it is another.
Regards
Tim Romano
Swarthmore PA
On Tue, Jun 29, 2010 at 11:56 AM, P Kishor wrote:
> On Tue, Jun 29, 2010 at 10:46 AM, Tim Romano
> wrote:
> > Puneet,
> > I am not trying to give Adobe any sort of primacy; but I wouldn't call
> them
> > unimportant either.
> >
> > The core concern, at least as I see it, is the undesirable effects of
> > sharing data between implementations that do not handle INT and INTEGER
> > primary keys compatibly.
> >
>
> There is no incompatibility. CREATE TABLE t AS SELECT ... is not meant
> to clone a table. Period. Done.
>
> Don't depend on it, don't expect it, don't promote it.
>
>
>
>
>
> > I don't use and won't use the "create table as select ... " syntax, but
> > others might, hence the advisory.
> >
> > Here's a little story: years ago, back in the days of dumb terminals,
> > oncology patients were dying in abnormally high numbers not long after
> > receiving their radiation treatment. Turned out that the software that
> > controlled the radiation dosage was written to be used with a
> dumb-terminal
> > that did not have cursor-positioning keys. It might have been a VT100. I
> > don't recall. You had to hit RETURN to move from field to field. But the
> > hospital had installed a "compatible" terminal that did have these
> > cursor-arrow keys. The hospital technician, ignorant of the
> consequences,
> > were in the habit of using the arrow keys to move the cursor around the
> > radiation dosage and timing screen, and then would key in their values
> and
> > execute the program. The numbers they keyed in were actually hitting the
> > wrong input fields. The users' eyes told them they were on the dosage
> field
> > but they were actually keying in the value for the time, or vice-versa,
> and
> > the patients were being given lethal doses of radiation.
> >
> > I saw this on 60 minutes or 20/20 or some show like that -- I didn't
> write
> > that program or install the compatible terminal. But since then, whenever
> I
> > see the opportunity for things going FUBAR, I will say something.
> >
> > Regards
> > Tim Romano
> > Swarthmore PA
> >
> >
> >
> >
> >
> >
> >
> >
> > On Tue, Jun 29, 2010 at 11:10 AM, P Kishor wrote:
> >
> >> On Tue, Jun 29, 2010 at 9:58 AM, Tim Romano
> >> wrote:
> >> > But there may be an argument for making the cloning more precise.
> >>
> >>
> >> The issue is that CREATE TABLE t AS SELECT... is not meant to clone a
> >> table. Not too long ago I encountered the same issue (search the mail
> >> archives). Igor, as usual, explained it succinctly and effectively --
> >>
> >> sqlite> CREATE TABLE t (a INTEGER PRIMARY KEY, b TEXT);
> >> sqlite> INSERT INTO t (b) VALUES ('foo');
> >> sqlite> INSERT INTO t (b) VALUES ('bar');
> >> sqlite> INSERT INTO t (b) VALUES ('baz');
> >> sqlite> SELECT * FROM t;
> >> a b
> >> -- --
> >> 1 foo
> >> 2 bar
> >> 3 baz
> >> sqlite> CREATE TABLE u AS SELECT a + 0.5 AS a, b FROM t;
> >> sqlite> SELECT * FROM u;
> >> a b
> >> -- --
> >> 1.5 foo
> >> 2.5 bar
> >> 3.5 baz
> >> sqlite> .s
> >> CREATE TABLE t (a INTEGER PRIMARY KEY, b TEXT);
> >> CREATE TABLE u(a,b TEXT);
> >> sqlite> SELECT Typeof(a) FROM u;
> >> Typeof(a)
> >> --
> >> real
> >> real
> >> real
> >> sqlite> CREATE TABLE v (a INTEGER PRIMARY KEY, b TEXT);
> >> sqlite> INSERT INTO v SELECT * FROM t;
> >> sqlite> SELECT * FROM v;
> >> a b
> >> -- --
> >> 1 foo
> >> 2 bar
> >> 3 baz
> >> sqlite> DELETE FROM v;
> >> sqlite> INSERT INTO v SELECT a + 0.5, b FROM t;
> >> Error: datatype mismatch
> >> sqlite>
> >>
> >>
> >> In other words, don't use CREATE TABLE .. AS SELECT .. to clone.
> >> Instead, create the new table the way you want to, and then use INSERT
> >> to pop
Re: [sqlite] create table {table-name} as select.... table definition is imperfectly cloned
Pavel,
As I said, I am biased in favor of the benefits to there being
core-compatibility among the various implementations of SQLite but I am not
dogmatic about it, and I have no say in the matter in any case ;-)
But I think you may have misunderstood me: I am not arguing that SQlite
should be compatible with PICK. They are nothing alike. PICK is quasi-OO,
sort of like XML, with nesting depth limited to 3. PICK is simply an
example of a database that came in many slightly different versions all of
which adhered to a core definition, and this compatibility was a good
thing.
Regards
Tim Romano
Swarthmore PA
On Tue, Jun 29, 2010 at 11:18 AM, Pavel Ivanov wrote:
> > I think
> > SQLite implementations should probably adhere to a core spec but I
> recognize
> > this as my bias, not dogma.
>
> Probably this is my personal opinion but why should SQLite comply with
> specification of Pick Multi-dimensional databases if it never claimed
> to be multi-dimensional? SQLite is a relational database, it complies
> with standard for relational databases and relational language - SQL.
> And SQL standard doesn't define which type should be assigned to
> columns in the table created by CREATE ... AS SELECT. But even if it
> defined that special treatment of INTEGER PRIMARY KEY column as
> equivalent to rowid is definitely not in that standard - it's SQLite's
> special feature. So if you rely on that feature then your application
> doesn't conform SQL standard, so you should change your application...
> But I digress and don't mean any offence. I just want to say that
> SQLite complies with SQL standard and all other features should be
> either taken by you as is or shouldn't be used at all.
>
>
> Pavel
>
> On Tue, Jun 29, 2010 at 10:58 AM, Tim Romano
> wrote:
> > But there may be an argument for making the cloning more precise. It's a
> > bit of a mess, or at least it seems so to me because my first ten years
> of
> > database work was done with PICK, a database that was developed by PICK
> > Systems but licensed to many companies and marketed under different
> brands
> > with subtle functionality differences, yet applications that adhered to
> the
> > core PICK spec were completely portable across all implementations. I
> think
> > SQLite implementations should probably adhere to a core spec but I
> recognize
> > this as my bias, not dogma.
> >
> > Adobe (and possibly Google and some others who are involved in
> coordinating
> > their SQLite implementations --I'm not fully "up" on the details of who
> all
> > are involved in that cooperative effort, or the extent to which they have
> > reconciled their implementations) treats INT primary keys as aliases for
> > the RowId, whereas SQLite does so only with INTEGER primary keys.
> >
> > Should one ever share a SQLite database that happens to contain tables
> > defined via the "CREATE TABLE X as SELECT..." cloning approach with
> someone
> > who is using an Adobe-based tool, the query results returned by the
> > Adobe-based tool will not jibe with the results returned by the SQlite
> > command-line utility. On the table with INT primary key, Adobe will be
> > fetching the row by row-id.
> >
> > If a table has foreign-key value of 10, and is joined to a table with an
> > INT primary key, Adobe will bring over the 10th row in the table even
> though
> > the value in the primary key column of that table may or may not contain
> the
> > number 10.
> >
> > In Adobe:
> >
> > CREATE TABLE FOO (id INT PRIMARY KEY, name TEXT)
> > insert into FOO(1,'Groucho')
> > insert into FOO(2,'Chico')
> > Insert into FOO(999, 'Harpo')
> >
> > the row id of Harpo's row is 3 (it's PK value is 999) and Harpo will be
> > returned by a join when the foreign key = 3 not when the foreign key =
> 999.
> >
> >
> > Regards
> > Tim Romano
> > --
> > Most people assume one is wearing underwear. Not so for intelligence.
> >
> >
> > On Tue, Jun 29, 2010 at 9:46 AM, Jay A. Kreibich wrote:
> >
> >> On Tue, Jun 29, 2010 at 06:59:18AM -0400, Tim Romano scratched on the
> wall:
> >> > CREATE TABLE "main"."proto" ("id" INTEGER PRIMARY KEY AUTOINCREMENT
> >> NOT
> >> > NULL , "name" TEXT)
> >> > CREATE TABLE "main"."clone" as select * from PROTO
> >> >
> >> > The primary key of table CLONE is defined as "INT" not "INTEGER".
> >>
Re: [sqlite] create table {table-name} as select.... table definition is imperfectly cloned
Puneet,
I am not trying to give Adobe any sort of primacy; but I wouldn't call them
unimportant either.
The core concern, at least as I see it, is the undesirable effects of
sharing data between implementations that do not handle INT and INTEGER
primary keys compatibly.
I don't use and won't use the "create table as select ... " syntax, but
others might, hence the advisory.
Here's a little story: years ago, back in the days of dumb terminals,
oncology patients were dying in abnormally high numbers not long after
receiving their radiation treatment. Turned out that the software that
controlled the radiation dosage was written to be used with a dumb-terminal
that did not have cursor-positioning keys. It might have been a VT100. I
don't recall. You had to hit RETURN to move from field to field. But the
hospital had installed a "compatible" terminal that did have these
cursor-arrow keys. The hospital technician, ignorant of the consequences,
were in the habit of using the arrow keys to move the cursor around the
radiation dosage and timing screen, and then would key in their values and
execute the program. The numbers they keyed in were actually hitting the
wrong input fields. The users' eyes told them they were on the dosage field
but they were actually keying in the value for the time, or vice-versa, and
the patients were being given lethal doses of radiation.
I saw this on 60 minutes or 20/20 or some show like that -- I didn't write
that program or install the compatible terminal. But since then, whenever I
see the opportunity for things going FUBAR, I will say something.
Regards
Tim Romano
Swarthmore PA
On Tue, Jun 29, 2010 at 11:10 AM, P Kishor wrote:
> On Tue, Jun 29, 2010 at 9:58 AM, Tim Romano
> wrote:
> > But there may be an argument for making the cloning more precise.
>
>
> The issue is that CREATE TABLE t AS SELECT... is not meant to clone a
> table. Not too long ago I encountered the same issue (search the mail
> archives). Igor, as usual, explained it succinctly and effectively --
>
> sqlite> CREATE TABLE t (a INTEGER PRIMARY KEY, b TEXT);
> sqlite> INSERT INTO t (b) VALUES ('foo');
> sqlite> INSERT INTO t (b) VALUES ('bar');
> sqlite> INSERT INTO t (b) VALUES ('baz');
> sqlite> SELECT * FROM t;
> a b
> -- --
> 1 foo
> 2 bar
> 3 baz
> sqlite> CREATE TABLE u AS SELECT a + 0.5 AS a, b FROM t;
> sqlite> SELECT * FROM u;
> a b
> -- --
> 1.5 foo
> 2.5 bar
> 3.5 baz
> sqlite> .s
> CREATE TABLE t (a INTEGER PRIMARY KEY, b TEXT);
> CREATE TABLE u(a,b TEXT);
> sqlite> SELECT Typeof(a) FROM u;
> Typeof(a)
> --
> real
> real
> real
> sqlite> CREATE TABLE v (a INTEGER PRIMARY KEY, b TEXT);
> sqlite> INSERT INTO v SELECT * FROM t;
> sqlite> SELECT * FROM v;
> a b
> -- --
> 1 foo
> 2 bar
> 3 baz
> sqlite> DELETE FROM v;
> sqlite> INSERT INTO v SELECT a + 0.5, b FROM t;
> Error: datatype mismatch
> sqlite>
>
>
> In other words, don't use CREATE TABLE .. AS SELECT .. to clone.
> Instead, create the new table the way you want to, and then use INSERT
> to populate it with data from the old table.
>
>
> > It's a
> > bit of a mess, or at least it seems so to me because my first ten years
> of
> > database work was done with PICK, a database that was developed by PICK
> > Systems but licensed to many companies and marketed under different
> brands
> > with subtle functionality differences, yet applications that adhered to
> the
> > core PICK spec were completely portable across all implementations. I
> think
> > SQLite implementations should probably adhere to a core spec but I
> recognize
> > this as my bias, not dogma.
> >
> > Adobe (and possibly Google and some others who are involved in
> coordinating
> > their SQLite implementations --I'm not fully "up" on the details of who
> all
> > are involved in that cooperative effort, or the extent to which they have
> > reconciled their implementations) treats INT primary keys as aliases for
> > the RowId, whereas SQLite does so only with INTEGER primary keys.
> >
> > Should one ever share a SQLite database that happens to contain tables
> > defined via the "CREATE TABLE X as SELECT..." cloning approach with
> someone
> > who is using an Adobe-based tool, the query results returned by the
> > Adobe-based tool will not jibe with the results returned by the SQlite
> > command-line utility. On the table with I
Re: [sqlite] create table {table-name} as select.... table definition is imperfectly cloned
But there may be an argument for making the cloning more precise. It's a
bit of a mess, or at least it seems so to me because my first ten years of
database work was done with PICK, a database that was developed by PICK
Systems but licensed to many companies and marketed under different brands
with subtle functionality differences, yet applications that adhered to the
core PICK spec were completely portable across all implementations. I think
SQLite implementations should probably adhere to a core spec but I recognize
this as my bias, not dogma.
Adobe (and possibly Google and some others who are involved in coordinating
their SQLite implementations --I'm not fully "up" on the details of who all
are involved in that cooperative effort, or the extent to which they have
reconciled their implementations) treats INT primary keys as aliases for
the RowId, whereas SQLite does so only with INTEGER primary keys.
Should one ever share a SQLite database that happens to contain tables
defined via the "CREATE TABLE X as SELECT..." cloning approach with someone
who is using an Adobe-based tool, the query results returned by the
Adobe-based tool will not jibe with the results returned by the SQlite
command-line utility. On the table with INT primary key, Adobe will be
fetching the row by row-id.
If a table has foreign-key value of 10, and is joined to a table with an
INT primary key, Adobe will bring over the 10th row in the table even though
the value in the primary key column of that table may or may not contain the
number 10.
In Adobe:
CREATE TABLE FOO (id INT PRIMARY KEY, name TEXT)
insert into FOO(1,'Groucho')
insert into FOO(2,'Chico')
Insert into FOO(999, 'Harpo')
the row id of Harpo's row is 3 (it's PK value is 999) and Harpo will be
returned by a join when the foreign key = 3 not when the foreign key = 999.
Regards
Tim Romano
--
Most people assume one is wearing underwear. Not so for intelligence.
On Tue, Jun 29, 2010 at 9:46 AM, Jay A. Kreibich wrote:
> On Tue, Jun 29, 2010 at 06:59:18AM -0400, Tim Romano scratched on the wall:
> > CREATE TABLE "main"."proto" ("id" INTEGER PRIMARY KEY AUTOINCREMENT
> NOT
> > NULL , "name" TEXT)
> > CREATE TABLE "main"."clone" as select * from PROTO
> >
> > The primary key of table CLONE is defined as "INT" not "INTEGER".
>
> The only information preserved by CREATE TABLE ... AS SELECT is the
> column affinity (not "type"). These are equivalent, and both
> translate to an INTEGER affinity.
>
>
>
> ...which actually surprises me, since I was under the impression
> CREATE TABLE ... AS SELECT always produced NONE affinities. Is this
> a semi-recent (last year) change?
>
> -j
>
>
> --
> Jay A. Kreibich < J A Y @ K R E I B I.C H >
>
> "Intelligence is like underwear: it is important that you have it,
> but showing it to the wrong people has the tendency to make them
> feel uncomfortable." -- Angela Johnson
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] create table {table-name} as select.... table definition is imperfectly cloned
CREATE TABLE "main"."proto" ("id" INTEGER PRIMARY KEY AUTOINCREMENT NOT
NULL , "name" TEXT)
CREATE TABLE "main"."clone" as select * from PROTO
The primary key of table CLONE is defined as "INT" not "INTEGER".
Regards
Tim Romano
Swarthmore PA
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] (python) how to define unchangeable global ID in a table?
My remarks were made in the context of AUTOINCREMENTING primary keys. With auto-incremented keys, the database is free to implement the incrementation in the manner it sees fit. It may skip numbers. It may re-generate keys on import/restore and cascade the changes out to child tables. Given these possibilities, it is not best practice to exchange autoincremented keys between systems but to use instead an alternate unique key that is guaranteed to remain constant. Regards Tim Romano Swarthmore PA On Mon, Jun 28, 2010 at 2:31 PM, Pavel Ivanov wrote: > > Such freedom is not suitable for data interchange between two systems. > Not > > that SQLite or any other database would change the PK during > import-export, > > but they are free to do so as long as the *intramural* integrity is > > preserved. > > Can you point out some documentation supporting this claim? > I've no time now to search internet on this matter but I believe DBMS > changing data that *I stored* in it is fundamentally broken. I > wouldn't advise anybody to use it. DBMS is allowed to change only > internal details which do not appear in CREATE TABLE and INSERT > statements (like ROWID for example). And AFAIK semantically primary > key is no difference with unique constraint (except ability to be > referenced by foreign key of course). And even more: I believe in a > completely normalized database there couldn't be any unique > constraints other than primary key. And to advise to developers either > to not use primary key or to declare all columns referencing to it as > foreign key is too much of a restrain. > > > Pavel > > On Mon, Jun 28, 2010 at 2:15 PM, Tim Romano > wrote: > > Pavel, > > Although you are right that SQLite persists the rowid for INTEGER PRIMARY > > KEYS across VACUUMs and suchlike, I too am right. > > > > I was focusing on the OP's use of the words "guaranteed" and "globally" > and > > on this requirement: > > > > The OP wrote: > > "BTW, in my story it is necessary to store the unique IDs as an integer > > type not something like "uuid" or "hash" because the unique ID also > > standard for a position in a string in exchanging protocol between 2 > > system." > > > > Since no SQL standard requires the primary key to do anything other than > be > > unique within the relation and with respect to its foreign references. > As > > long as the database maintains meets those requirements, it is free to > > change the PK value as an "implementation detail" -- provided RI is not > > broken in the process. The purist in me says the PK is for nothing but > > uniqueness. It should have no other meaning whatsoever. > > > > Such freedom is not suitable for data interchange between two systems. > Not > > that SQLite or any other database would change the PK during > import-export, > > but they are free to do so as long as the *intramural* integrity is > > preserved. Once you move into the extra-mural realm (data exchange, or > > replication) I would advise against relying upon the PK value. > > > > The safest "guaranteed" way to achieve what the OP wants is to add > another > > column to the table and to make it an alternate unique key. This value > > carries for him the specific meaning "position in a string in exchange > > protocol between 2 systems". > > > > Regards > > Tim Romano > > Swarthmore PA > > > > > > On Mon, Jun 28, 2010 at 10:23 AM, Pavel Ivanov > wrote: > > > >> > the primary key column [id] is defined as INTEGER PRMARY KEY; so > defined, > >> > SQLite will treat this column as an alias for the ROWID. There is no > >> > guarantee that ROWID will remain constant over time: its job is very > >> simple: > >> > to be unique. There is no "be constant" clause in its contract, so to > >> > >> Tim, you are not right here. You are right that as is ROWID is not > >> guaranteed to be constant over time. But if you define some column as > >> an alias to ROWID (i.e. if you have column INTEGER PRIMARY KEY) then > >> SQLite guarantees that the value of this column will persist through > >> any internal operations (like VACUUM or dumping and loading database > >> again). Of course nobody will block UPDATEs on this column (as long as > >> uniqueness remains valid), but that's a different story. > >> > >> > >> Pavel > >> > >> On Mon, Jun 28, 2010 at 8:39 AM, Tim Romano > >> wrote: > >> >
Re: [sqlite] (python) how to define unchangeable global ID in a table?
Pavel,
Although you are right that SQLite persists the rowid for INTEGER PRIMARY
KEYS across VACUUMs and suchlike, I too am right.
I was focusing on the OP's use of the words "guaranteed" and "globally" and
on this requirement:
The OP wrote:
"BTW, in my story it is necessary to store the unique IDs as an integer
type not something like "uuid" or "hash" because the unique ID also
standard for a position in a string in exchanging protocol between 2
system."
Since no SQL standard requires the primary key to do anything other than be
unique within the relation and with respect to its foreign references. As
long as the database maintains meets those requirements, it is free to
change the PK value as an "implementation detail" -- provided RI is not
broken in the process. The purist in me says the PK is for nothing but
uniqueness. It should have no other meaning whatsoever.
Such freedom is not suitable for data interchange between two systems. Not
that SQLite or any other database would change the PK during import-export,
but they are free to do so as long as the *intramural* integrity is
preserved. Once you move into the extra-mural realm (data exchange, or
replication) I would advise against relying upon the PK value.
The safest "guaranteed" way to achieve what the OP wants is to add another
column to the table and to make it an alternate unique key. This value
carries for him the specific meaning "position in a string in exchange
protocol between 2 systems".
Regards
Tim Romano
Swarthmore PA
On Mon, Jun 28, 2010 at 10:23 AM, Pavel Ivanov wrote:
> > the primary key column [id] is defined as INTEGER PRMARY KEY; so defined,
> > SQLite will treat this column as an alias for the ROWID. There is no
> > guarantee that ROWID will remain constant over time: its job is very
> simple:
> > to be unique. There is no "be constant" clause in its contract, so to
>
> Tim, you are not right here. You are right that as is ROWID is not
> guaranteed to be constant over time. But if you define some column as
> an alias to ROWID (i.e. if you have column INTEGER PRIMARY KEY) then
> SQLite guarantees that the value of this column will persist through
> any internal operations (like VACUUM or dumping and loading database
> again). Of course nobody will block UPDATEs on this column (as long as
> uniqueness remains valid), but that's a different story.
>
>
> Pavel
>
> On Mon, Jun 28, 2010 at 8:39 AM, Tim Romano
> wrote:
> > In this example:
> >
> > CREATE TABLE tableA {
> > id INTEGER PRIMARY KEY AUTOINCREMENT,
> > name TEXT NOT NULL UNIQUE,
> > myspecialvalue TEXT NOT NULL UNIQUE
> > }
> >
> >
> > the primary key column [id] is defined as INTEGER PRMARY KEY; so defined,
> > SQLite will treat this column as an alias for the ROWID. There is no
> > guarantee that ROWID will remain constant over time: its job is very
> simple:
> > to be unique. There is no "be constant" clause in its contract, so to
> > speak. Therefore, you should add another column [myspecialvalue] and make
> it
> > unique if you want to associate a value with a tuple and also want to
> > guarantee that the associated value is both unique and remains
> unchanging.
> > Of course you have to prevent edits to the associated value to enforce
> its
> > immutability.
> >
> > Regards
> > Tim Romano
> > Swarthmore PA
> >
> >
> >
> >
> >
> >
> >
> >
> > On Sat, Jun 26, 2010 at 11:34 AM, kee wrote:
> >
> >> Dear all
> >>
> >> I have 2 string lists, listA and listB as raw data which need to be
> >> store in the SQLITE database, both of them may have duplicated records
> >>
> >> listA listB
> >> ===
> >> orangejapan
> >> pearchina
> >> orangechina
> >> apple american
> >> cherry india
> >> lemon china
> >> lemon japan
> >> strawberry korea
> >> banana thailand
> >> australia
> >>
> >> I want all items in listA and listB have a runtime defined global ID and
> >> fix it, which means no matter how the lists changed later after the
> >> first time running, all the item always
Re: [sqlite] (python) how to define unchangeable global ID in a table?
You could also define your primary key as INT PRIMARY KEY (rather than
INTEGER PRIMARY KEY) and in that case SQLite will treat it as a normal
column and it will remain immutable over time (unless you change it).
However, I would advise against using INT PRIMARY KEY inasmuch as this
subtle (yet documented) difference between INT and INTEGER in primary keys
in SQLite has already been a cause of significant confusion and is an
"accident waiting to happen" IMO since not all implementations of SQLite may
be hep to the nuance. What you want is a simple ersatz value for the entity
(i.e. an "alternate unique key"); a second column which *explicitly* acts
in this manner will be clearer.
Regards
Tim Romano
Swarthmore PA
On Mon, Jun 28, 2010 at 8:43 AM, Tim Romano wrote:
> And myspecialvalue can be INTEGER|TEXT.
>
>
> On Mon, Jun 28, 2010 at 8:39 AM, Tim Romano wrote:
>
>> In this example:
>>
>> CREATE TABLE tableA {
>>
>> id INTEGER PRIMARY KEY AUTOINCREMENT,
>> name TEXT NOT NULL UNIQUE,
>> myspecialvalue TEXT NOT NULL UNIQUE
>> }
>>
>>
>>
>>
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] (python) how to define unchangeable global ID in a table?
And myspecialvalue can be INTEGER|TEXT.
On Mon, Jun 28, 2010 at 8:39 AM, Tim Romano wrote:
> In this example:
>
> CREATE TABLE tableA {
>
> id INTEGER PRIMARY KEY AUTOINCREMENT,
> name TEXT NOT NULL UNIQUE,
> myspecialvalue TEXT NOT NULL UNIQUE
> }
>
>
>
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] (python) how to define unchangeable global ID in a table?
In this example:
CREATE TABLE tableA {
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL UNIQUE,
myspecialvalue TEXT NOT NULL UNIQUE
}
the primary key column [id] is defined as INTEGER PRMARY KEY; so defined,
SQLite will treat this column as an alias for the ROWID. There is no
guarantee that ROWID will remain constant over time: its job is very simple:
to be unique. There is no "be constant" clause in its contract, so to
speak. Therefore, you should add another column [myspecialvalue] and make it
unique if you want to associate a value with a tuple and also want to
guarantee that the associated value is both unique and remains unchanging.
Of course you have to prevent edits to the associated value to enforce its
immutability.
Regards
Tim Romano
Swarthmore PA
On Sat, Jun 26, 2010 at 11:34 AM, kee wrote:
> Dear all
>
> I have 2 string lists, listA and listB as raw data which need to be
> store in the SQLITE database, both of them may have duplicated records
>
> listA listB
> ===
> orangejapan
> pearchina
> orangechina
> apple american
> cherry india
> lemon china
> lemon japan
> strawberry korea
> banana thailand
> australia
>
> I want all items in listA and listB have a runtime defined global ID and
> fix it, which means no matter how the lists changed later after the
> first time running, all the item always have an unique int type ID bind
> with, looks like:
> A B
>
> 1orange 1 japan
> 2pear 2 china
> 3apple 3 american
> 4cherry 4 india
> 5lemon5 taiwan
> 6strawberry6 korea
> 7banana 7 thailand
>8 australia
>
>
> So I defined table with such structure:
> CREATE TABLE tableA {
>uinque_id INTEGER PRIMARY KEY AUTOINCREMENT,
>name TEXT NOT NULL UNIQUE,
> }
> CREATE TABLE tableB {
>uinque_id INTEGER PRIMARY KEY AUTOINCREMENT,
>name TEXT NOT NULL UNIQUE,
> }
>
> and my plan is to use "INSERT OR FAIL" to insert data into those tables.
>
> Here comes my QUESTION 1, is it possible no matter what the list
> changed, all items always get an unique ID, should any other limitation
> should be added into the defination, and if I use "CREATE TABLE
> table_dst AS SELECT * FROM table_src" to duplicate tables later, can
> those definition be copied either?
>
>
> Then, it may need to make a matrix for 2 tables: I want list all
> possible combination of 2 lists, for example:
>
> listC = listA * listB
>
> japan orange
> china orange
> american orange
> india orange
> ...
> thailand banana
> australiabanana
>
> I also want to use same table structure to store the combination result
> and assigned unique ID for those combined items same as before:
> CREATE TABLE tableC {
>uinque_id INTEGER PRIMARY KEY AUTOINCREMENT,
>name_combination TEXT NOT NULL UNIQUE,
> }
> Here comes my QUESTION 2, is it also reasonable using such a structure
> store the combination or should there be a better way to do it?
> I means will such a structure be a problem if the listA and listB be
> changed, should I store uniqueIDA and uniqueIDB replace the
> name_combination field will be a better solution?
>
> BTW, I using the python as the interface insert the lists into those
> tables, also the uinque_id in database is not need to be reused if some
> items in listA and listB been deleted, just remain as is because it will
> never get to sqlite limitation.
>
> BTW, in my story it is necessary to store the unique IDs as an integer
> type not something like "uuid" or "hash" because the unique ID also
> standard for a position in a string in exchanging protocol between 2
> system.
>
>
> And : a more general question:
> Anyone has better solution to solve my problem in sqlite - the items in
> a list need to be bind with an unchangeable integer type unique ID no
> matter what the list will be modified?
>
> Any comments and suggestions wi
Re: [sqlite] alternative to UNIQUE CONSTRAINT
Could there be an issue with the character-encoding of the text column? Regards Tim Romano Swarthmore PA On Fri, Jun 25, 2010 at 12:35 PM, Oliver Peters wrote: > Igor Tandetnik writes: > > [...] > > > Isn't that exactly what you were asking for - a different syntax to > achieve > the same end result? > > Not really because the assumed ODBC-Driver problem has nothing to do with > the > syntax but the underlying mechanism (sounds logically for me ;-) ) because > I get > an error about "invalid descriptor index" and a few errors in the > SQL.LOG-file I > produced under Win XP (http://paste.ubuntuusers.de/398565/). > > > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Question About SQLITE and AIR efficiency
Does you query involve LIKE? That has been overriden in AIR so it won't make use of an index. If your queries permit, use GLOB instead. I have used SQLite with Adobe AIR without any performance problems/slowness other than the one above relating to LIKE. N.B. If your database was designed outside of AIR, be aware that Adobe and SQLite differ with respect to primary keys. In SQLite, a PK defined as INT (as distinct from INTEGER) is a normal column whereas one defined as INTEGER (verbatim, i.e. upper-case I, uppercase N, uppercase T, uppercase E, etc) is treated as an alias for the ROWID; whereas in AIR, INT and INTEGER are treated the *same* -- as aliases for the ROWID. This can wreak havoc if you have any tables with INT primary keys as the wrong rows may be incorporated into the result-set by joins. I discovered this and brought this to Adobe's attention months ago and their decision was to document it rather than to change their implementation. Regards Tim Romano Swarthmore PA On Sun, Jun 20, 2010 at 7:44 PM, Richard Hipp wrote: > On Sun, Jun 20, 2010 at 6:11 PM, Felipe Aramburu > wrote: > > > I have a query that I can execute in about 150ms in a sqlite tool like > > sqlite expert professional that takes 1200ms when I execute the query > from > > AIR. > > > > > This might be because AIR is using an older version of SQLite that lacks > some optimization that makes your query run faster. The following > information would be useful in diagnosing the problem: > > (1) "SELECT sqlite_version()" run on both AIR and "SQLite Expert > Professional" > (2) Your schema > (3) The query you are running. > > > > > > I am using prepared statements, synchronous connection. Does anyone have > > any > > idea why a query takes 8 times longer in air? > > > > Felipe > > ___ > > sqlite-users mailing list > > sqlite-users@sqlite.org > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > > > > -- > - > D. Richard Hipp > d...@sqlite.org > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] database development - correct way?
Oliver, Your first solution CREATE TABLE customer( idINTEGER PRIMARY KEY AUTOINCREMENT, customernumberINTEGER, customeroriginINTEGER, name TEXT, UNIQUE(customernumber,customerorigin) ); is the better of the two because it simplifies foreign keys: the OrderHeader table would contain a single-column reference to CUSTOMER rather than two columns. Either approach is legitimate as far as RDBMS design is concerned; however some client-side application frameworks and middleware libraries do not support multi-column primary keys. Regards Tim Romano Swarthmore PA On Wed, Jun 9, 2010 at 1:37 PM, Oliver Peters wrote: > Rich Shepard writes: > > > > > On Wed, 9 Jun 2010, Oliver Peters wrote: > > > > > So I assume that it is not(!) a mistake not(!) to use a composite PK in > my > > > table "customer" (customernumber,customerorigin) and to refer to it > from > > > the table "order" where I had to use these fields as a composite FK? > > > > Oliver, > > > >Too many negatives there for me to really follow what you're asking. > > > sorry, I try my very best: > > adverted to the table customer I've 2 possible solutions and I ask myself > if the > first one is incorrect - the reason why I ask lies in the behaviour of my > frontend (OpenOffice Base) that has problems to handle UNIQUE-Constraints > under > special circumstances (main-subform-connections) > > solution 1 > -- > CREATE TABLE customer( > idINTEGER PRIMARY KEY AUTOINCREMENT, > customernumberINTEGER, > customeroriginINTEGER, > name TEXT, > UNIQUE(customernumber,customerorigin) > ); > > > solution 2 > -- > CREATE TABLE customer( >customernumberINTEGER, > customeroriginINTEGER, > name TEXT, >PRIMARY KEY(customernumber,customerorigin) > ); > > thx for your patience > Oliver > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] database development - correct way?
Placing a unique composite index on (customer, article) in the Orders table prevents recurring purchases of the same article by the same customer. Acme might buy a widget in June and then place another order for a widget in September, but the order would be rejected as a duplicate. Typically, Orders are divided into OrderHeader and OrderDetail tables: OrderHeader id integer primary key orderdate customerid OrderDetail id orderid references OrderHeader(id) articleid references article(id) quantity int And you could then place a unique composite index on (orderid, articleid) in OrderDetail if you wanted to prevent the same article from appearing on more than one line-item of the order. Regards Tim Romano Swarthmore PA Regards Tim Romano . On Wed, Jun 9, 2010 at 9:09 AM, Oliver Peters wrote: > Hello, > > despite it's just a question about construction I hope somebody is willing > to > push me into the right direction if necessary. > > my simplified case > -- > I've the 3 tables customer, article and order > > my thoughts about the table customer: > the customernumber can be from 3 different sources with possible > overlappings > (i.e. I can get 3 from source A and 3 from source B) so I adopt the > field customerorigin to make a difference > For simplicity I created a field id that is taking the part of the Primary > Key > and just declared "UNIQUE(customernumber,customerorigin)" > > > the SQL-Code > > CREATE TABLE customer( > idINTEGER PRIMARY KEY AUTOINCREMENT, > customernumberINTEGER, > customeroriginINTEGER, > name TEXT, > UNIQUE(customernumber,customerorigin) > ); > > CREATE TABLE article( > idINTEGER PRIMARY KEY AUTOINCREMENT, > name TEXT > ); > > CREATE TABLE order( > idINTEGER PRIMARY KEY AUTOINCREMENT, > id_customer INTEGER, > id_articleINTEGER, > UNIQUE(id_customer,id_article), > FOREIGN KEY(id_customer) REFERENCES customer(id), > FOREIGN KEY(id_article) REFERENCES article(id) > ); > > > simple question > --- > Is this a correct way or do I make a mistake? > > greetings > Oliver > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Oracle connection
http://tinyurl.com/29sk9pr On Thu, Jun 3, 2010 at 12:09 PM, Simon Hax wrote: > I don't know what JET is. > I testet ADO. That works. > > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Oracle connection
MSFT JET supports heterogeneous data sources. Assuming you have a JET MDB with a data-connection to Oracle and another data-connection to SQLite, and have attached the relevant tables from each, you could move data from one back-end data source to the other. This approach brings the data from the first back-end out to the client, which then pushes the data out to the other back-end. http://my.safaribooksonline.com/0596004397/adonetckbk-CHP-3-SECT-6#X2ludGVybmFsX0ZsYXNoUmVhZGVyP3htbGlkPTAtNTk2LTAwNDM5LTcvMTM2 Regards Tim Romano On Thu, Jun 3, 2010 at 11:25 AM, Simon Hax wrote: > Is something possible like > > insert into sqlite_table.ColumnA select x from ta...@oracledb > > ? > (Without copying into local memory (firstly do a select from Oracle ) > and then insert into a Sqlite database (via e.g. ADO ). ) > > S. > ___ > GRATIS für alle WEB.DE Nutzer: Die maxdome Movie-FLAT! > Jetzt freischalten unter http://movieflat.web.de > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Aggregate and join query very slow
How many distinct media-types are there? How many distinct facilities do you have? How many rows are typically returned by your FacilityScore subquery? SELECT facilitynumber,SUM(score_rev) AS score FROM release_cl WHERE media<3 AND year=2006 GROUP BY facilitynumber Regards Tim Romano On Fri, May 28, 2010 at 10:57 AM, Michael Ash wrote: > Thank you very much. Both replies pointed to indexes. So I changed > the indexes and markedly improved performance from 12 seconds to about > 1.5 seconds for the faster variant (using nested SELECTS) and about > 2.2 second for the slower variant. > > Per suggestions, I indexed year and media on the big table. So I now > have separate indexes for the key variable (releasenumber) and for > year and for media.Would it make more sense to have a single > index for all three, thus: > > CREATE UNIQUE INDEX r ON release_cl (year,media,releasenumber); > > I remain concerned that I am not using indexes as well as possible. > The query still takes many times longer than does the same query on > MySQL. > > Thank you very much again for the helpful responses. > > > Best, > Michael Ash > > > > > > 3. Re: Aggregate and join query very slow (Max Vlasov) > > Message: 3 > > Date: Thu, 27 May 2010 17:26:10 +0400 > > From: Max Vlasov > > Subject: Re: [sqlite] Aggregate and join query very slow > > To: General Discussion of SQLite Database > > Message-ID: > > > > Content-Type: text/plain; charset=ISO-8859-1 > > > > On Thu, May 27, 2010 at 3:07 PM, Michael Ash > wrote: > > > >> ...These are large tables (52,355 records in facility and 4,085,137 in > >> release_cl). > >> > >> ... > >> sqlite> explain query plan > >> ...> SELECT name,score > >> ...> FROM facility f, (SELECT facilitynumber,SUM(score_rev) AS score > >> ...> FROM release_cl > >> ...> WHERE media<3 > >> ...> AND year=2006 > >> ...> GROUP BY facilitynumber) r > >> > > > > > > Michael, from what I see, if your release_cl table is not properly > indexed > > to be quickly aggregated (media and year field), this will lead to full > > table reading (so all the data of your 4M records). If it's properly > > indexed, and the result number of records of this select is big, consider > > adding non-indexed fields to this (or brand-new) index since otherwise > > sqlite quickly finds records with this index, but have to look up main > data > > tree to retrieve other fields. > > > > Max, > > maxerist.net > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] select intersecting intervals
Right, Jean-Christophe, about "moving too much data around". You'd want each
of the inner selects to produce a vector of integer ids. "select *" is
clearly not going to be very efficient. 200K rows is a very small table for
this sort of test, in my view. Were the faster intereections any faster
than:
select * from T
where lo ... {low condition}
and hi ..{ hi condition}
and name = 'x'
where only one index is chosen? The answer would depend on the cardinality.
If [name] is highly differentiated then name='x' would return very few
rows and the query is fast using standard approach. But if [name] were,
say, FirstName, and there were hundreds of thousands of records containing
"Joe" in the OP's table, then the name index won't help us very much.
That's why I am asking about Minimal Perfect Hash and intersection of
vectors.
The Minimal-Perfect-Hash-INTERSECTION-OF-VECTORS approach might benefit
queries against tables having several million rows. What I'm wondering (and
lack the C skills to find out for myself) is whether SQLite's underlying
algorithms for INTERSECT could be optimized with a minimal perfect hash
approach. The algorithms would decide which vector of ids is the better
candidate to be used for the MPH and which is the better candidate to be
iterated, and then iterate over the one vector of ids, testing for the
existence of each id in the MPH using the optimized Exists() function
supplied by the mph library for the particular type of mph being used.
The question, in scenarios where the vectors contain many items, is whether
the overhead of creating the MPH and testing for existence is significantly
less than the overhead of doing whatever INTERSECT is doing now when it
intersects vectors of ids. You have a ready-made acronym to advertise the
speed if it turns out to be faster: MPH. ;-)
Regards
Tim Romano
Swarthmore PA
On Wed, May 12, 2010 at 8:52 PM, Jean-Christophe Deschamps
wrote:
>
> >
> >I would first create an INTEGER primary key and then place an index on
> >name,
> >another on i_from, and another on i_to, and then see if the approach below
> >has any benefit.
> >
> >When I tried this with a geo-queryit was actually slower than the standard
> >select, and I'm curious if that's always going to be the case. It will
> >come
> >down to how efficient the INTERSECT of the vectors of integers is. Each
> >vector will have been the result of an index-scan. If INTERSECT were
> >optimized (perhaps with a minimal perfect hash function
> >http://cmph.sourceforge.net/index.html) this approach might be useful.
>
>
> All three following queries use only simple indexes (PK, name, lo, hi).
>
> Query#1:
> select * from tst where lo < 345678
> intersect
> select * from tst where hi > 123456
> intersect
> select * from tst where name = 'aaelj';
>
> Query#2
> select * from tst
> join (
> select rowid from tst where lo < 345678
> intersect
> select rowid from tst where hi > 123456
> ) as interval
> on tst.rowid = interval.rowid and name = 'aaelj';
>
> Query#3
> select * from tst
> join (
> select rowid from tst where lo < 345678
> intersect
> select rowid from tst where hi > 123456
> intersect
> select rowid from tst where name = 'aaelj'
> ) as interval
> on tst.rowid = interval.rowid;
>
> On a 200K-row test table with random data, queries #2 and #3 were
> essentially identical while #1 was twice slower (moving too much data
> around, uselessly).
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] select intersecting intervals
I would first create an INTEGER primary key and then place an index on name, another on i_from, and another on i_to, and then see if the approach below has any benefit. When I tried this with a geo-queryit was actually slower than the standard select, and I'm curious if that's always going to be the case. It will come down to how efficient the INTERSECT of the vectors of integers is. Each vector will have been the result of an index-scan. If INTERSECT were optimized (perhaps with a minimal perfect hash function http://cmph.sourceforge.net/index.html) this approach might be useful. select * from T JOIN ( select pk_col from T where i_from > ? intersect select pk_col from T where i_to < ? ) as DESIREDINTERVAL ON T.pk_col = DESIREDINTERVAL.pk_col and T.name = ? Regards Tim Romano Swarthmore PA On Wed, May 12, 2010 at 12:00 PM, Jan Asselman wrote: > Hi, > > Given the following table with large row count 'row_count': > > CREATE TABLE table > ( >i_name TEXT, >i_from INTEGER, >i_toINTEGER, >i_data BLOB > ) > > I am wondering what would be the fastest way to get all rows with a > given name 'myname' that intersect with a given interval [a, b]? > > > CREATE INDEX idx_from ON table (i_name, i_from); > CREATE INDEX idx_to ON table (i_name, i_to); > > SELECT data FROM table WHERE name = 'myname' AND i_from < b AND i_to > a > >-> index idx_from will be used >-> in worst case (a is larger than all i_to) all 'myname' rows > will be traversed before concluding result set is empty > > SELECT data FROM table WHERE name = 'myname' AND i_to > a AND i_from < b > >-> index idx_to will be used >-> in worst case (b is smaller than all i_from) all 'myname' > rows > will be traversed before concluding result set is empty > > > > I know this is exactly what a one dimensional R-tree index is used for, > but my project requires 64 bit integer minimum- and maximum-value > pairs... > > All suggestions or corrections are appreciated. > > > Thanks in advance, > > Jan Asselman > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] join performance query
Let's try that again : expose the [number] column to the outer selection (**
are for emphasis**):
( select id_song, **number** from
(
select id_song, **number**
from PLAYLIST_SONG
where id_playlist=2
{and|or } number > 258
) as MYPLAYLISTSONGS
Regards
Tim Romano
On Tue, May 11, 2010 at 12:46 PM, Tim Romano wrote:
> And you would put move your title-condition to the outer query:
>
> .
> .
> .
> ) as SONGIDLIST
> on SONG.id_song = SONGIDLIST.id_song
>
> where
> your title-condition and|or your title-number condition
>
>
> Regards
> Tim Romano
>
>
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] join performance query
And you would put move your title-condition to the outer query: . . . ) as SONGIDLIST on SONG.id_song = SONGIDLIST.id_song where your title-condition and|or your title-number condition Regards Tim Romano ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] join performance query
Arrrgh, Google Chrome ate the top half of my reply.
You must also expose the number column in the inner query against
PLAYLIST_SONG; include your number-condition there and also specify the
number column in the select-list:
( select id_song, number from
(
select id_song from PLAYLIST_SONG
where id_playlist=2
{AND|OR } number > 258
) as MYPLAYLISTSONGS
Regards
Tim Romano
On Tue, May 11, 2010 at 12:39 PM, Tim Romano wrote:
> You could remove the title condition from the inner SONGS select, limiting
> your conditions to artist and genre; an index on column [artist] would make
> this subquery run quickly:
>
>
> (
> select id_song from
> SONG
> where genre_id = 0 AND artist = 'Las ketchup'
> // AND title >= 'Asereje(karaoke version)'// --> moved to outer select
> > ) as MYSONGS
>
>
>
> The goal is to produce small inner subsets using indexes, and then to join
> these with each other, and to let the inner subsets expose the necessary
> columns to the outer query.
>
> Regards
> Tim Romano
>
>
>
> On Tue, May 11, 2010 at 11:13 AM, Andrea Galeazzi wrote:
>
>> Sorry but in your solution, how can I solve the condition
>> AND title >= 'Asereje(karaoke version)' AND (title > 'Asereje(karaoke
>> >> version)' OR number > 258)
>> ?
>> title is on song and number is song_number on Playlist_Song AS PS.
>> Furthermore I also need title and number in place of your select * from
>> SONG
>> Could you write it again please?
>> Thanks
>>
>> Citando Tim Romano :
>>
>> > 1. Try discrete single-column indexes rather than multi-column composite
>> > indexes.
>> > 2. Try breaking the query down into subsets expressed as parenthetical
>> > queries; you can treat these parenthetical queries as if they were
>> tables by
>> > assigning them an alias, and then you can join against the aliases. I
>> have
>> > sped queries up in SQLite using this approach and, with a little
>> tinkering,
>> > the time can drop from over a minute to sub-second. Performance will
>> > depend on the indexes and criteria used, of course. But this approach
>> lets
>> > you see how SQLite is optimizing the creation of the component sets from
>> > which you can build up your ultimate query.
>> > .
>> > select * from SONG
>> > JOIN
>> >
>> > ( select id_song from
>> >
>> > (
>> > select id_song from PLAYLIST_SONG
>> > where id_playlist=2
>> > ) as MYPLAYLISTSONGS
>> >
>> > JOIN
>> >
>> > (
>> > select id_song from
>> > SONG
>> > where genre_id = 0 AND artist = 'Las ketchup'
>> > AND title >= 'Asereje(karaoke version)'
>> > ) as MYSONGS
>> >
>> > on MYSONGS.id_song = MYPLAYLISTSONGS.id_song
>> >
>> >
>> > ) as SONGIDLIST
>> >
>> > on SONG.id_song = SONGIDLIST.id_song
>> >
>> >
>> > Regards
>> > Tim Romano
>> >
>> >
>> >
>> >
>> >
>> >
>> > On Tue, May 11, 2010 at 6:07 AM, Andrea Galeazzi
>> wrote:
>> >
>> >> Hi guys,
>> >> I'm in a bind for a huge time consuming query!
>> >> I made the following database schema:
>> >>
>> >> CREATE TABLE Song (
>> >>idINTEGER NOT NULL UNIQUE,
>> >>titleVARCHAR(40) NOT NULL DEFAULT '' COLLATE NOCASE,
>> >>artistVARCHAR(40) NOT NULL DEFAULT '' COLLATE
>> NOCASE,
>> >>genre_idINT NOT NULL DEFAULT 0,
>> >> PRIMARY KEY (id),
>> >>
>> >> CONSTRAINT fk_Genre FOREIGN KEY (genre_id)
>> >>REFERENCES Genre (id)
>> >>ON DELETE SET DEFAULT
>> >>ON UPDATE CASCADE);
>> >>
>> >> CREATE INDEX Song_Browse_View_idx ON Song(genre_id,artist,title);
>> >>
>> >> CREATE TABLE PlayList (
>> >>id INTEGER NOT NULL UNIQUE,
>> >>name VARCHAR(15) NOT NULL COLLATE NOCASE, --KORGCOLLATE,
>> >>length INT NOT NULL DEFAULT 0,
>> >>created_date TEXT,
>> >> PRIMARY KEY (id));
>> >>
>> >> CREATE TABLE PlayList_Song (
>> >>id_song INT NOT NULL,
>> >>id_playlist INT NOT NULL,
>> >>song_number INTEGER NOT NULL,
>>
Re: [sqlite] join performance query
You could remove the title condition from the inner SONGS select, limiting your conditions to artist and genre; an index on column [artist] would make this subquery run quickly: ( select id_song from SONG where genre_id = 0 AND artist = 'Las ketchup' // AND title >= 'Asereje(karaoke version)'// --> moved to outer select > ) as MYSONGS The goal is to produce small inner subsets using indexes, and then to join these with each other, and to let the inner subsets expose the necessary columns to the outer query. Regards Tim Romano On Tue, May 11, 2010 at 11:13 AM, Andrea Galeazzi wrote: > Sorry but in your solution, how can I solve the condition > AND title >= 'Asereje(karaoke version)' AND (title > 'Asereje(karaoke > >> version)' OR number > 258) > ? > title is on song and number is song_number on Playlist_Song AS PS. > Furthermore I also need title and number in place of your select * from > SONG > Could you write it again please? > Thanks > > Citando Tim Romano : > > > 1. Try discrete single-column indexes rather than multi-column composite > > indexes. > > 2. Try breaking the query down into subsets expressed as parenthetical > > queries; you can treat these parenthetical queries as if they were tables > by > > assigning them an alias, and then you can join against the aliases. I > have > > sped queries up in SQLite using this approach and, with a little > tinkering, > > the time can drop from over a minute to sub-second. Performance will > > depend on the indexes and criteria used, of course. But this approach > lets > > you see how SQLite is optimizing the creation of the component sets from > > which you can build up your ultimate query. > > . > > select * from SONG > > JOIN > > > > ( select id_song from > > > > ( > > select id_song from PLAYLIST_SONG > > where id_playlist=2 > > ) as MYPLAYLISTSONGS > > > > JOIN > > > > ( > > select id_song from > > SONG > > where genre_id = 0 AND artist = 'Las ketchup' > > AND title >= 'Asereje(karaoke version)' > > ) as MYSONGS > > > > on MYSONGS.id_song = MYPLAYLISTSONGS.id_song > > > > > > ) as SONGIDLIST > > > > on SONG.id_song = SONGIDLIST.id_song > > > > > > Regards > > Tim Romano > > > > > > > > > > > > > > On Tue, May 11, 2010 at 6:07 AM, Andrea Galeazzi > wrote: > > > >> Hi guys, > >> I'm in a bind for a huge time consuming query! > >> I made the following database schema: > >> > >> CREATE TABLE Song ( > >>idINTEGER NOT NULL UNIQUE, > >>titleVARCHAR(40) NOT NULL DEFAULT '' COLLATE NOCASE, > >>artistVARCHAR(40) NOT NULL DEFAULT '' COLLATE NOCASE, > >>genre_idINT NOT NULL DEFAULT 0, > >> PRIMARY KEY (id), > >> > >> CONSTRAINT fk_Genre FOREIGN KEY (genre_id) > >>REFERENCES Genre (id) > >>ON DELETE SET DEFAULT > >>ON UPDATE CASCADE); > >> > >> CREATE INDEX Song_Browse_View_idx ON Song(genre_id,artist,title); > >> > >> CREATE TABLE PlayList ( > >>id INTEGER NOT NULL UNIQUE, > >>name VARCHAR(15) NOT NULL COLLATE NOCASE, --KORGCOLLATE, > >>length INT NOT NULL DEFAULT 0, > >>created_date TEXT, > >> PRIMARY KEY (id)); > >> > >> CREATE TABLE PlayList_Song ( > >>id_song INT NOT NULL, > >>id_playlist INT NOT NULL, > >>song_number INTEGER NOT NULL, > >> PRIMARY KEY (id_playlist, song_number), > >> CONSTRAINT fk_PlayList_Song1 FOREIGN KEY (id_song) > >>REFERENCES Song (id) > >>ON DELETE CASCADE > >>ON UPDATE CASCADE, > >> CONSTRAINT fk_PlayList_Song2 FOREIGN KEY (id_playlist) > >>REFERENCES PlayList (id) > >>ON DELETE CASCADE > >>ON UPDATE CASCADE); > >> > >> CREATE INDEX PlayList_Song_song_number_idx ON > PlayList_Song(song_number); > >> > >> Now I need to scroll title filtered by genre_id and artist both in Song > >> table and Playlist. > >> The query for the first case is very fast: > >> SELECT id AS number,title FROM Song WHERE genre_id = 0 AND artist = > >> 'Las ketchup' > >> AND title >= 'Asereje(karaoke version)' AND (title > 'Asereje
Re: [sqlite] join performance query
1. Try discrete single-column indexes rather than multi-column composite indexes. 2. Try breaking the query down into subsets expressed as parenthetical queries; you can treat these parenthetical queries as if they were tables by assigning them an alias, and then you can join against the aliases. I have sped queries up in SQLite using this approach and, with a little tinkering, the time can drop from over a minute to sub-second. Performance will depend on the indexes and criteria used, of course. But this approach lets you see how SQLite is optimizing the creation of the component sets from which you can build up your ultimate query. . select * from SONG JOIN ( select id_song from ( select id_song from PLAYLIST_SONG where id_playlist=2 ) as MYPLAYLISTSONGS JOIN ( select id_song from SONG where genre_id = 0 AND artist = 'Las ketchup' AND title >= 'Asereje(karaoke version)' ) as MYSONGS on MYSONGS.id_song = MYPLAYLISTSONGS.id_song ) as SONGIDLIST on SONG.id_song = SONGIDLIST.id_song Regards Tim Romano On Tue, May 11, 2010 at 6:07 AM, Andrea Galeazzi wrote: > Hi guys, > I'm in a bind for a huge time consuming query! > I made the following database schema: > > CREATE TABLE Song ( >idINTEGER NOT NULL UNIQUE, >titleVARCHAR(40) NOT NULL DEFAULT '' COLLATE NOCASE, >artistVARCHAR(40) NOT NULL DEFAULT '' COLLATE NOCASE, >genre_idINT NOT NULL DEFAULT 0, > PRIMARY KEY (id), > > CONSTRAINT fk_Genre FOREIGN KEY (genre_id) >REFERENCES Genre (id) >ON DELETE SET DEFAULT >ON UPDATE CASCADE); > > CREATE INDEX Song_Browse_View_idx ON Song(genre_id,artist,title); > > CREATE TABLE PlayList ( >id INTEGER NOT NULL UNIQUE, >name VARCHAR(15) NOT NULL COLLATE NOCASE, --KORGCOLLATE, >length INT NOT NULL DEFAULT 0, >created_date TEXT, > PRIMARY KEY (id)); > > CREATE TABLE PlayList_Song ( >id_song INT NOT NULL, >id_playlist INT NOT NULL, >song_number INTEGER NOT NULL, > PRIMARY KEY (id_playlist, song_number), > CONSTRAINT fk_PlayList_Song1 FOREIGN KEY (id_song) >REFERENCES Song (id) >ON DELETE CASCADE >ON UPDATE CASCADE, > CONSTRAINT fk_PlayList_Song2 FOREIGN KEY (id_playlist) >REFERENCES PlayList (id) >ON DELETE CASCADE >ON UPDATE CASCADE); > > CREATE INDEX PlayList_Song_song_number_idx ON PlayList_Song(song_number); > > Now I need to scroll title filtered by genre_id and artist both in Song > table and Playlist. > The query for the first case is very fast: > SELECT id AS number,title FROM Song WHERE genre_id = 0 AND artist = > 'Las ketchup' > AND title >= 'Asereje(karaoke version)' AND (title > 'Asereje(karaoke > version)' OR number > 258) > ORDER BY title ASC , number ASC LIMIT 4; > > The second case is about 35 times slower... so the scrolling is quite > impossible (or useless)! > SELECT song_number AS number,title FROM Song AS S, Playlist_Song AS PS > WHERE S.id = PS.id_song AND > PS.id_playlist = 2 AND genre_id = 0 AND artist = 'Las ketchup' > AND title >= 'Asereje(karaoke version)' AND (title > 'Asereje(karaoke > version)' OR number > 959) > ORDER BY title ASC , number ASC LIMIT 4; > > I also execute the EXPLAIN QUERY PLAN: > 1st query: 0 0 TABLE Song WITH INDEX Song_Browse_View_idx ORDER BY > > 2nd query: 0 0 TABLE Song WITH INDEX Song_Browse_View_idx ORDER BY > 1 1 TABLE Playlist_Song AS PS > So it seems that the second plan (1,1) requires very long time! > How can I optimized a such kind of query? > Cheers > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Select via Wi-fi very slow
N.B. Queries with LIKE will not use an index if the particular implementation of SQLite overrides LIKE. The .NET implementation I'm familiar with has done so; the OP's may have done so too. However, GLOB was left intact and does make use of an index on "starts with" and "equals" substring searches. GLOB is case-sensitive. select * from products where description GLOB 'shirt*' Note the asterisk wildcard instead of the percent-symbol. Regards Tim Romano Swarthmore PA Regards Tim Romano On Tue, May 11, 2010 at 5:50 AM, Pavel Ivanov wrote: > > Sometimes search found 200 records. When I do a query via wi-fi takes 1 > > minute. > > How can I decrease this time? > > Time taken to search for the records does not depend on how many > records found. It depends on how many records were searched through. > Most probably for your query no indexes are used, so the whole table > is scanned through. And that means that the whole database is copied > to your device via WiFi, which apparently is slow. > > To decrease the amount of data transfered to the device you can use > indexes. For this particular query you can create index like this: > > CREATE INDEX Product_Ind on Product > (description COLLATE NOCASE); > > > Pavel > > On Mon, May 10, 2010 at 6:31 PM, Ernany wrote: > > Hello, > > > > I'll try to explain my problem: > > > > I have a Symbol MC3090 Data Collector with VB.Net 2005. I have a database > > with 80,000 records on the computer. > > > > For example: I search all words that begin with "shirt" and show in the > Grid > > Collector. > > Sometimes search found 200 records. When I do a query via wi-fi takes 1 > > minute. > > How can I decrease this time? > > > > On the computer the same search takes a few seconds ... > > > > > > Public ConnStringDados As String = "Data Source=" & Address & "\" & > NameDB > > & ";Version=3;Compress=True;Synchronous=Off;Cache Size=8000;" > > > > > > My select: > > > > "SELECT codigo, description FROM Product WHERE description Like '" & Word > > _Search & "%'" > > > > > > Thanks, > > > > Ernany > > ___ > > sqlite-users mailing list > > sqlite-users@sqlite.org > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Should this work?
The select/group by part of your statement will group table SERIESDATA by text-column series_id (aliased to id) and return the min and max data_index for each grouping, assuming those columns are populated with data for each row. The set will have three columns and some number of rows, one per id. id | min(data_index) | max(data_index) However, it is not clear to me where you want to put that aggregated set. Do you have another *table* called SERIESID with those three columns in it? Regards Tim Romano Swarthmore PA On Mon, May 10, 2010 at 2:43 AM, Matt Young wrote: > # series data looks like: > create table seriesdata ( >data_index INTEGER PRIMARY KEY autoincrement, >series_id text, >year numeric, >value numeric); > # and is filled > insert into seriesid >select >s.series_id as id, min(data_index),max(data_index) >from >seriesdata as s >group by >id; > > # I intend seriesid to pick up the minand max values of data_index for > each unique series_id > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Should this work?
elaboration: " ... you could this (to find the set to be inserted): " TR ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Should this work?
I don't understand why you are simulating distinct. Either something is going completely over my head (quite possible) or you are making things harder than they need to be. If you want to insert into T1 a distinct set of rows from T2 that don't already exist in T1, you could do this: select distinct col1, col2 from T1 where not exists ( select col1, col2 from T2 where T1.col1 = T2.col1 and T1.col2 = T2.col2 ) Regards Tim Romano Swarthmore PA On Thu, May 6, 2010 at 9:14 AM, Matt Young wrote: > Got it, thinks Jay. > > > On 5/6/10, Jay A. Kreibich wrote: > > On Thu, May 06, 2010 at 05:10:31AM -0700, Matt Young scratched on the > wall: > >> OK, I got it. > >> > >> insert into seriesid > >> select series_id,min(ROWID) from > >> seriesdata group by series_id; > >> > >> This gets me a table with a pointer to the firs instance of series_id > >> in the bigger table having multiple copies, it assumes that the ids > >> are contiguous, allowing me to use offset, limit to extract just the > >> date series I want. > > > > It would make a lot more sense to just query the seriesdata table with > > a "WHERE series_id = :id" condition. You're writing SQL, but you're > > still thinking in C. > > > > > > > > Also, you don't want to be using ROWID as the target of your foreign > key. > > In cases like this, setup an explicit INTEGER PRIMARY KEY, which will > > act as a ROWID alias. > > > > The difference is that ROWIDs are not stable through a VACUUM, while > > INTEGER PRIMARY KEYs are properly preserved. > > > > -j > > > > -- > > Jay A. Kreibich < J A Y @ K R E I B I.C H > > > > > "Our opponent is an alien starship packed with atomic bombs. We have > > a protractor." "I'll go home and see if I can scrounge up a ruler > > and a piece of string." --from Anathem by Neal Stephenson > > ___ > > sqlite-users mailing list > > sqlite-users@sqlite.org > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Can I throw a query out to the group?
Matt, You cannot select a column from a relation if the relation does not include the column. The query inside the ( ) returns a single column, table_id. select a_format from ( select table_id from table_id_list where prefix_code = 'MyPrefix_code' ); However, if table_id corresponds to a_format, you can supply an alias for that column in the inner select, like this: select a_format from ( select table_id as a_format from table_id_list where prefix_code = 'MyPrefix_code' ); Regards Tim Romano Swarthmore PA On Sun, May 2, 2010 at 10:22 AM, Matt Young wrote: > I am a bit of a novice. I am dealing with meta data, descriptions of > the Bureau of Labor Statistics database. BLS data is identified by a > series code (16 chars long) but the series format is different for > each data group) > > I need to extract from an SQL table the names of other SQL tables and > reference them in a second query to build the proper series_id code: > > I haven't tried this, I assume it can't be done: > > select a_format from (select table_id from table_id_list where > prefix_code = 'MyPrefix_code'); > > Is this beyond SQL? Is there a better solution? > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Optimising usage of LIKE
By "version" I meant "implementation". On Mon, May 3, 2010 at 7:25 AM, Tim Romano wrote: > Which version of SQLite are you using? If LIKE has been overridden in the > implementation you're using, it won't have the advantage of an index > whatever the collation, in which case you might consider GLOB though it is > case-sensitive. > Regards > Tim Romano > > > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Optimising usage of LIKE
Which version of SQLite are you using? If LIKE has been overridden in the implementation you're using, it won't have the advantage of an index whatever the collation, in which case you might consider GLOB though it is case-sensitive. Regards Tim Romano ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] scripting language interpreter
Since Javascript has been mentioned: http://code.google.com/apis/v8/intro.html Tim Romano ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] scripting language interpreter
@DRH : Thank you for the info on SQLite with TCL. Regards Tim Romano > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] scripting language interpreter
Very cool, Jay. Regards Tim Romano On Sat, May 1, 2010 at 11:23 AM, Jay A. Kreibich wrote: > > > > I'd love to see a Lua extension. I know that's been proposed. Lua is > MIT licensed, small, fast, and reasonably mature. It is also designed > to be easily embeddable, making it a good fit. I realize many of you > may not know about Lua, but it is heavily used in a number of specific > areas, including the game industry, where it is *the* behavioral > and AI language, mostly because it is easy to learn, easy to embed, > and will compile on just about anything. http://www.lua.org/ > > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] scripting language interpreter
Simon, It's not clear to me how this is a result of scripting language support: "Another problem with it is that sooner or later you need your inner language (your SQL engine) to have access to your outer environment, for example, to find out if you have lots of filespace free." Could you please elaborate? The ability to declare FOREIGN KEYS and TRANSACTIONS are not what I had in mind when I used the phrase "scripting language". I am thinking of full-blown procedural logic integrated with the RDBMS. The foremost example that comes to mind is VBA in MS-Access, though many other examples are available, e.g. Revelation, Borland Paradox, or even java in Oracle. Now, comparing SQLite with those other databases is like comparing a giraffe with an alligator and then complaining that the alligator cannot reach the tall acacia fruits or that the giraffe cannot take down a water buffalo because he cannot fully submerge and his teeth are too dull. I am very aware of SQLite's many strengths and how it is different from those other databases. But I'd still like SQLite to have an integrated interpreted scripting language if that were possible, because I know from experience that if the interpreted scripting language integration is well handled you can end up with the best of both worlds: SQL set logic and procedural logic with arrays/collections. That is of course one of the main reasons for the loadable extensions module in SQLite. An important difference is that the scripting language can be "sandboxed" and prevented from referencing the file system. If support for an interprested scripting language were available, Google's and Adobe's and other similar implementations of SQLite could have access to the sort of power that loadable extensions offer. Regards Tim Romano On Sat, May 1, 2010 at 10:04 AM, Simon Slavin wrote: > > On 1 May 2010, at 2:25pm, Tim Romano wrote: > > > In part, this is a very broad question but I hope it's not unacceptable. > > From a birdseye perspective, what is involved in integrating an > interpreted > > scripting language with a database engine? I am aware that SQLite > supports > > loadable extensions, but would the SQLite architecture also permit the > > integration of an interpreted scripting language? An integrated > scripting > > language makes an already powerful database engine orders of magnitude > more > > useful especially when one is solving ad hoc data problems requiring very > > rapid turnaround. SQlite with, say, an ActionScript interpreter (ala > > MS-Access->VBA) would be an amazingly powerful desktop tool. Do you know > of > > any project pursuing such an integration? > > SQLite already contains three scripting elements. The first is TRIGGERs, > where you can supply a sequence of operations to be completed when certain > things happen. The second is FOREIGN KEY support, which is more restricted > in terms of what can be done, but easier to understand. And the third is > the COMMIT/ROLLBACK system which can also be considered a method of > pre-programming certain events. > > One problem with implementing scripting within a database language is that > it turns into just another layer of complication. If you migrate some of > your application logic into the scripting language it gives you another > layer of stuff to debug. I already don't know how a value arrived in one of > my fields: is it a default value for that column ? Or did it arrive there > via a TRIGGER ? Or was it explicitly put in there by my application ? If > you add another layer to that you're complicating an already complicated set > of possibilities. > > Another problem with it is that sooner or later you need your inner > language (your SQL engine) to have access to your outer environment, for > example, to find out if you have lots of filespace free. At that point you > have to start specifying things about your OS (for example, that it actually > does have a file system and you're not just using memory). Which means > you're going to restrict yourself to only some implementations of SQLite. > Which then means SQLite either has to have forks or waste code in > environments it's not suitable for. That way madness lies. > > So the conventional way to handle it is to put your SQL engine near the > bottom of the heap, just above the file system. If you want it to act as if > it has a scripting language, implement your own, and call SQLite only by > your own calls rather than directly. > > Simon. > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] scripting language interpreter
In part, this is a very broad question but I hope it's not unacceptable. >From a birdseye perspective, what is involved in integrating an interpreted scripting language with a database engine? I am aware that SQLite supports loadable extensions, but would the SQLite architecture also permit the integration of an interpreted scripting language? An integrated scripting language makes an already powerful database engine orders of magnitude more useful especially when one is solving ad hoc data problems requiring very rapid turnaround. SQlite with, say, an ActionScript interpreter (ala MS-Access->VBA) would be an amazingly powerful desktop tool. Do you know of any project pursuing such an integration? Regards Tim Romano ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Searching with like for a specific start letter
A practical situation? Lexicographical applications and full-text applications against text corpora require indexed substring searches, including ends-with searches. (The FTS extension is not always a good fit.) I am glad that only the LIKE operator has been overridden in Adobe's version and in the version that ships with the System.Data.SQLite (.NET) adapter; I'd be up the creek if both LIKE and GLOB had been overridden. I like your renaming suggestion but unfortunately that's not an option if the implementors want to make their implementation widely available and support standard syntax. Hence, Adobe and Google et al don't have a LIKEU(). Tim Romano On Wed, Apr 28, 2010 at 10:09 AM, Jean-Christophe Deschamps wrote: > Tim, > > > I agree it is possible to overload LIKE and GLOB independantly but I > don't see a practical situation where overloading only one of them > would be desirable. > > For instance, if some extension overloads LIKE to support ICU, it would > be logical and consistent to overload GLOB with the same > function. Given that the two entries differ only by a parameter, > enjoying Unicode support in LIKE and not in GLOB (or vice-versa) would > be a bit strange. > > Should one have a need to keep the native functions untouched, there is > the easy possibility to call the new versions with new names (e.g. > LIKEU, GLOBU) even if that makes the SQL less standard. > > In short: possible yes, likely not much. > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Searching with like for a specific start letter
Jean-Christophe,
But did I say that GLOB uses an index if it has been overloaded? No. I
wrote that if LIKE has been overloaded, queries that contain LIKE won't use
the index. Typically, GLOB won't have been overridden too just because LIKE
has been overridden: the rationale for overriding the LIKE operator does not
apply equally to GLOB, and it would make little sense to override GLOB in a
manner that vitiates its raison d'être. You are conflating these two
functions ("... if LIKE/GLOB has been overridden... overloads LIKE/GLOB")
but in important respects they are dissimilar.
Regards
Tim Romano
On Mon, Apr 26, 2010 at 8:27 PM, Jean-Christophe Deschamps
wrote:
> Tim,
>
> >Queries using GLOB do use the index on the column in question (i.e.
> >optimization is attempted)
> >Queries using LIKE do not use that index if the LIKE operator has been
> >overridden.
>
> Sorry but GLOB doesn't use an index either if LIKE/GLOB has been
> overloaded. This is consistent with the docs and the output of Explain
> query plan for both variants when an extension is active and overloads
> LIKE/GLOB.
>
> Things can be different with a custom built of SQLite, where native
> LIKE/GLOB itself has been modified. With custom code, all bets are off.
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Searching with like for a specific start letter
Edit: I meant to type "Firefox" not Firebird. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Searching with like for a specific start letter
I am not quite sure what it is, and why it is, that you are doubting, Jean-Christophe. Queries using GLOB do use the index on the column in question (i.e. optimization is attempted) Queries using LIKE do not use that index if the LIKE operator has been overridden. You could confirm this claim from the docs and/or by using EXPLAIN QUERY PLAN, assuming you had access to a version of SQLite where the LIKE operator has been overridden (as it has been in Adobe AIR, Firebird, and in the version that ships with the System.Data.SQLite .NET provider as well, IIRC. Regards Tim Romano On Mon, Apr 26, 2010 at 9:24 AM, Jean-Christophe Deschamps wrote: > At 14:31 26/04/2010, you wrote: > > >If the implementation of SQLite you are using overrides the LIKE operator > >(as more than a few do), then SQLite will not make use of an index on the > >column in question. Use the GLOB operator instead. > > I doubt it. GLOB is absolutely nothing more or less than an invokation > of the same code for LIKE but with slightly different > parameters. Except if people have made a completely differing version, > departing from the architecture of the standard SQLite code (and there > is little reason to, if any) AND have made LIKE and GLOB two completely > distinct functions, there shouldn't be any significant difference in > running time (for equivalent queries, of course). > > Also if ever LIKE is overloaded, then GLOB gets excluded from standard > optimization, except large changes in SQLite code. > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Searching with like for a specific start letter
Yes. If the OP's [normword] column contains proper nouns, he must normalize to lower case in order to get accurate results from GLOB. Or, if his lexicon contains proper nouns in upper case and normal nouns in lower case, then he could always leave the case intact and use GLOB to get a count of proper nouns versus normal nouns ;-) ... GLOB 'A*' ... GLOB 'a*' Regards Tim Romano On Mon, Apr 26, 2010 at 8:47 AM, Igor Tandetnik wrote: > Tim Romano wrote: > > If the implementation of SQLite you are using overrides the LIKE operator > > (as more than a few do), then SQLite will not make use of an index on the > > column in question. Use the GLOB operator instead. > > > > For example, I have a lexicon containing 263,000 words: > > > > select count(*) from lexicon where spelling like 'a%' // 552 ms on > first > > run and then 355ms on second and subsequent runs > > select count(*) from lexicon where spelling glob 'a*' // 110 ms on first > > run and then ~10ms on second and subsequent runs > > Note that, by default, LIKE is case-insensitive while GLOB is > case-sensitive. Thus, even if not overridden with a custom function, LIKE > cannot be optimized unless the column is declared with NOCASE collation. > Again, this article provides all the details: > http://sqlite.org/optoverview.html#like_opt > -- > Igor Tandetnik > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Searching with like for a specific start letter
If the implementation of SQLite you are using overrides the LIKE operator (as more than a few do), then SQLite will not make use of an index on the column in question. Use the GLOB operator instead. For example, I have a lexicon containing 263,000 words: select count(*) from lexicon where spelling like 'a%' // 552 ms on first run and then 355ms on second and subsequent runs select count(*) from lexicon where spelling glob 'a*' // 110 ms on first run and then ~10ms on second and subsequent runs Alternatively: select substr(spelling,1,1), count (*) from lexicon group by substr(spelling,1,1) order by substr(spelling,1,1) // ~3500 ms on first run and then ~2400 ms on second and subsequent runs Of course, if your lexicon is static, you could create an ancillary table of first letters and their corresponding counts. Regards Tim Romano 2010/4/25 Alberto Simões > Hello > > I am running on the fly a query to count the number of words starting > with one of the 26 letters. > > I am doing the usual SELECT COUNT(term) from dictionary WHERE normword > LIKE "a%" (for the 26 letters) > > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Data optimization with GLOB, virtual deletes
Just guessing, but column 'path' probably has greater cardinality than
column 'extension'. What happens if you reverse the order of these columns
in the index?
i.e. (basename, path, extension, deleted)
Also, I don't recall your saying whether a single composite index was faster
than separate indexes? Is it?
Regards
Tim Romano
On Fri, Apr 16, 2010 at 8:17 AM, Mike Goins wrote:
> Sorry, this may look a bit familiar.
>
> Table structure:
> CREATE TABLE tb_file (tb_file_key INTEGER NOT NULL PRIMARY KEY
> AUTOINCREMENT , basename TEXT, extension TEXT, path TEXT, deleted
> INTEGER default 0 );
>
> Index:
> CREATE INDEX fullpath_idx on tb_file (basename, extension, path, deleted);
>
> Example insert:
> INSERT INTO tb_file (basename, extension, path) VALUES ('aa', 'bb', 'cc');
>
> Query:
> SELECT tb_file_key, basename, extension, path FROM tb_file WHERE
> basename GLOB 'a*' AND extension GLOB 'b*' AND path GLOB 'c*' AND
> deleted = 0 ORDER BY tb_file_key DESC;
>
>
> It's basically something to track existing files on a file system for
> an embedded device.
> As new files are added, new entries are made, and when files are
> removed the deleted column is set to 1 (Only one writer process, 5
> readers). There is not any type of VACUUM since there is not any
> shortage of space and the readers need access nearly all the time.
>
> My query gets slower as the table grows larger. The count of the
> deleted = 0 remains relatively constant while the virtually deleted
> (=1) grows. At 3000 deleted and 75 not, the query runs 4-5 times
> slower then when just the 75 not. The data lookup does not need to
> necessarily fast, while I prefer to minimize the growth in query .
>
> Some solutions I am looking at to minimize
> 1. Delete aged entries.
> 2. Delete aged entries and enable a vacuum mode that does not starve
> readers.
> 3. Create index on deleted, use that to create a TEMP table on which
> the query is run.
> 4. Optimize the query, part we have discussed already to remove the GLOB.
> 5. Fix? the index?
>
> Can I dismiss any of these right of the bat? I'm a little baffled
> with 4 and 5 and may need a couple suggestions.
>
> Thanks again.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Index and GLOB
Right, Igor. We can eliminate the middle-column issue : ... where basename GLOB 'a'// no index ... where basename GLOB 'a*' // index used Regards Tim Romano On Sun, Apr 11, 2010 at 8:43 AM, Igor Tandetnik wrote: > Tim Romano wrote: > > I believe the behavior is expected and believe (subject to correction) > that > > the single composite index is placed on a concatenation of the three > column > > values (basename + extension + path). I don't know how the internals > of > > SQLite work never having explored the code, but in situations like yours, > > where you want to do substring queries on each of the three columns that > > comprise your "full path", I would normally create three separate indexes > > rather than a single composite index. I am guessing that when you look > for > > rows where column 'extension' starts with 'b' (column 'extension' is the > > middle column of the three-column composite index) you are running into a > > scenario analogous to where someColumn LIKE '%x%'. > > Try this: > > explain query plan SELECT tb_file_key, basename, extension, > path FROM tb_file WHERE basename = 'a' AND extension GLOB 'b*' AND > path = 'c'; > > This query does use the index, and is equivalent to the OP's second query. > How does your theory explain this case? > -- > Igor Tandetnik > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Index and GLOB
I believe the behavior is expected and believe (subject to correction) that
the single composite index is placed on a concatenation of the three column
values (basename + extension + path). I don't know how the internals of
SQLite work never having explored the code, but in situations like yours,
where you want to do substring queries on each of the three columns that
comprise your "full path", I would normally create three separate indexes
rather than a single composite index. I am guessing that when you look for
rows where column 'extension' starts with 'b' (column 'extension' is the
middle column of the three-column composite index) you are running into a
scenario analogous to .... where someColumn LIKE '%x%'.
Regards
Tim Romano
On Fri, Apr 9, 2010 at 5:03 PM, Mike Goins wrote:
> First thanks to all that have helped on previous occasions.
>
> I'm a little confused about some results using an index and GLOB. I'm
> trying to optimize some queries to ensure they use an index after
> reviewing the LIKE Optimization section at the sqlite website.
>
> Using the latest binary, sqlite3-3.6.23.1.bin.gz
>
> sqlite> CREATE TABLE tb_file (tb_file_key INTEGER NOT NULL PRIMARY KEY
> AUTOINCREMENT , basename TEXT, extension TEXT, path TEXT, deleted
> INTEGER default 0 );
> sqlite> CREATE INDEX fullpath_idx on tb_file (basename, extension, path);
> sqlite> INSERT INTO tb_file (basename, extension, path) VALUES ('a', 'b',
> 'c');
> sqlite> select * from tb_file;
> tb_f basename exte path dele
> -
> 1 a b c 0
>
> sqlite> explain query plan SELECT tb_file_key, basename, extension,
> path FROM tb_file WHERE basename GLOB 'a*' AND extension GLOB 'b' AND
> path GLOB 'c';
> orde from deta
> -
> 0 0 TABLE tb_file WITH INDEX fullpath_idx
> sqlite> explain query plan SELECT tb_file_key, basename, extension,
> path FROM tb_file WHERE basename GLOB 'a' AND extension GLOB 'b*' AND
> path GLOB 'c';
> orde from deta
> -
> 0 0 TABLE tb_file USING PRIMARY KEY ORDER BY
>
>
> The first select uses the index since the the glob character is picked
> up, while the second does not. Is this expected? Does it matter?
>
> Thanks
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Feature request: hash index
IN( {inlist} ) syntax is not optimized, Alexey.
http://www.mail-archive.com/sqlite-users@sqlite.org/msg49985.html
Regards
Tim Romano
On Mon, Apr 5, 2010 at 3:22 PM, Alexey Pechnikov wrote:
> Hello!
>
> On Monday 05 April 2010 22:22:40 Roger Binns wrote:
> > Virtual tables already let you implement indices in any way you want -
> see
> > the xBestIndex and xFilter methods. (The former is especially hard to
> > understand until you have worked with it a bit.)
>
> Now virtual tables performance is bad in some useful situations. As example
> this query produce full-scan of the FTS3 virtual table 'data':
> sqlite> select count(*) from data where rowid in (1);
> ^CError: interrupted
>
> After 30 minuts I cancel query... The test database is not too big - only
> about 60G size and less than 400M records ;-)
>
> And so select like to below are very slow:
> select ... from my_virtual_table where rowid in (...);
>
> Best regards, Alexey Pechnikov.
> http://pechnikov.tel/
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Feature request: hash index
Yes. I think a couple of hash choices would be a good addition to SQLite. I posted recently asking about INTERSECT and whether a "minimal perfect hash" might be worth consideration for that function. http://www.mail-archive.com/sqlite-users@sqlite.org/msg51046.html And a standard sparse hash would be more suitable than a btree for some lexicographical database applications. Regards Tim Romano On Mon, Apr 5, 2010 at 8:45 AM, Alexey Pechnikov wrote: > Hello! > > The b-tree index is not good choice for high-selective data, but there is > no > hash index in SQLite. The hash index will be useful for many new projects > and will optimize a lot of existing applications. > > Is anyone interested in this feature? > > Best regards, Alexey Pechnikov. > http://pechnikov.tel/ > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Problem with sqlite providing different programs different data
I should add that when you create a SQLite database outside of Adobe and use INT PK, in Adobe's implementation joins will return perfectly plausible yet often completely inaccurate results, grabbing rows from the joined table with the RowID not the actual PK. http://forums.adobe.com/message/2365982#2365982 Tim Romano ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Problem with sqlite providing different programs different data
I don't know what your specific problem may be but check to make sure that you are using INTEGER [exactly I-N-T-E-G-E-R] primary keys (not INT or any other variant form). I documented on Adobe's bug database and in their AIR discussion forum a problem with the Adobe implementation of SQLite --Adobe is treating INT and INTEGER primary keys as if they were the same, taking both forms as an alias for the RowId, which in SQLite they are not. Regards Tim Romano On Thu, Apr 1, 2010 at 1:10 PM, Felipe Aramburu wrote: > I have some code that is using flex sdk 3.2 and I am updating and > inserting into a database with the following queries: > > stmt.text = 'INSERT INTO Question( hotlist, name, label, datatypeid, > advanced, multivalue, measurementtypeid) VALUES (@hotlist, > @name,@label,@datatypeid,@advanced,@multivalue,@measurementtypeid );'; > > > stmt.text = 'UPDATE Question SET hotlist = @hotlist, name = @name, > label = @label, measurementtypeid = @measurementtypeid, datatypeid = > @datatypeid, advanced = @advanced, multivalue = @multivalue WHERE id = > @id;'; > > I don't think those are very interesting to the problem but its better > to provide more detail than less. > The program executes these queries without a problem, the view > displays the information as expected and a quick query using the > sqlite command line tool will confirm that such queries executed > successfully and without a problem.. > > The problem arises when I check the database in two different tools > > Lita > Sqlite Expert Professional (SEP) > > Both of these tools show a version of the database that is different > from what i see in the command line and they are equivalent in their > discrepancies (they are different from the command line but the same > as each other). > > So heres the basica scenario: > > 1. i update my database with the code i wrote > 2. those changes are reflected in the command line > 3. those changes are not reflected in Lita/SEP > > 1. I update information in Lita/SEP on that same database file > 2. those changes are reflected in Lita/SEP > 3. those changes are not reflected in the command line > > > This seems so bizarre to me. How can two different programs make > changes to one database file and the information i see is different in > these two programs, but correct according to the changes i made using > that program. > > > I appreciate any help. > Felipe > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Select * from tablename results in an exception
http://social.msdn.microsoft.com/Forums/en-US/Vsexpressvb/thread/27aec612-5ca4-41ba-80d6-0204893fdcd1 Maybe related. Regards Tim Romano ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Preserving column size
Sorry I misunderstood your requirement, Kevin. I was focusing on the singular "a table" and "the table" in your original post, versus the plural "many types of structs". I thought you were looking for serialization approach. Regards Tim Romano On Tue, Mar 30, 2010 at 8:48 AM, Kevin M. wrote: > Actually Tim that is not what I was asking at all. This is converting an > application from using MySQL to SQLite. All the tables are normalized > already. But instead of rewriting a large chunk of the application, I'd > rather use the implementation as is substituting only the code needed to use > SQLite instead of MySQL. > > The various structs are not intermingled and in fact many do not share data > at all. My concern was being able to use existing code to iterate through > the columns of each row and assign the values into a chunk of memory > (representing a particular struct) while faithfully retaining the size of > each variable in the struct. As such, the suggestion to use > sqlite3_column_decltype seems most appropriate to accomplish the task. > Because then I can check for SQLITE_INTEGER as the type and get the > decltype to find out what size. Thus, the function called remains generic > to any given struct, but still assigns values that are the correct size & > type to the chunk of memory. > > > > > > From: Tim Romano > To: General Discussion of SQLite Database > Sent: Tue, March 30, 2010 7:28:16 AM > Subject: Re: [sqlite] Preserving column size > > On Tue, Mar 30, 2010 at 4:30 AM, Jean-Denis Muys >wrote: > > > > > > > See http://www.boost.org and more specifically: > > http://www.boost.org/doc/libs/1_42_0/libs/serialization/doc/index.html > > > > > > The OP's question is another form of this question: What's the best way to > violate 1NF in SQLite, so that instead of defining multiple tables, a > single > table will contain multiple actual or virtual column sets where these > column > sets have different numbers of columns and different combinations of > datatypes? And I must be able to query the store. > > We have three "conceptually similar" serialization approaches (BLOB, JSON, > BOOST). But the OP could also store in the following manner and not violate > 1NF: > > Table: STRUCT > id INTEGER PRIMARY KEY > structname TEXT > [...any other attributes that need to be tracked] > > > Table: STRUCTMEMBERS > id INTEGER PRIMARY KEY > structid INTEGER (FK references STRUCT) > membername TEXT > memberdatatype TEXT > membervalue TEXT > > casting 'membervalue' as appropriate during the reconstitution phase. > > > Regards > Tim Romano > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Preserving column size
On Tue, Mar 30, 2010 at 4:30 AM, Jean-Denis Muys wrote: > > > See http://www.boost.org and more specifically: > http://www.boost.org/doc/libs/1_42_0/libs/serialization/doc/index.html > > The OP's question is another form of this question: What's the best way to violate 1NF in SQLite, so that instead of defining multiple tables, a single table will contain multiple actual or virtual column sets where these column sets have different numbers of columns and different combinations of datatypes? And I must be able to query the store. We have three "conceptually similar" serialization approaches (BLOB, JSON, BOOST). But the OP could also store in the following manner and not violate 1NF: Table: STRUCT id INTEGER PRIMARY KEY structname TEXT [...any other attributes that need to be tracked] Table: STRUCTMEMBERS id INTEGER PRIMARY KEY structid INTEGER (FK references STRUCT) membername TEXT memberdatatype TEXT membervalue TEXT casting 'membervalue' as appropriate during the reconstitution phase. Regards Tim Romano ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Preserving column size
On 3/29/2010 4:19 PM, Kevin M. wrote: > I have a C/C++ application in which I want to store data from a struct into a > table (using SQLite 3.6.23) and later retrieve data from the table and store > it back in the struct. But, I need a general interface for this as there are > many types of structs used. > Couldn't you convert the structs to JSON format and store them in a TEXT field? There are a number of C++ JSON libraries listed here: http://www.json.org/ Regards Tim Romano ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Preserving column size
On Mon, Mar 29, 2010 at 4:19 PM, Kevin M. wrote: > I have a C/C++ application in which I want to store data from a struct into > a table (using SQLite 3.6.23) and later retrieve data from the table and > store it back in the struct. But, I need a general interface for this as > there are many types of structs used. You could convert the structs to JSON format and store them in a TEXT field. There are a number of C++ JSON libraries listed here: http://www.json.org/ Regards Tim Romano P.S. Apologies if this reply comes through twice -- I resubscribed under a new email address but replied under the old address. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] INTERSECT versus INNER JOIN with latitude, longitude queries
Another addendum: apologies -- I hope my
discussion was clear enough despite the disconnect between my head and
my fingers; I just noticed that I had typed "INNER JOIN" (yikes) rather
than "INNER LOOP", by which I mean fetching the rowids using an index
(on LATITUDE say) and then having to loop through those rowids in order
to fetch rows from the base table to compare the LONGITUDE.
If INTERSECT could be optimized would this form of query:
select rowid from T where {latitude criteria}
INTERSECT
select rowid from T where {longitude criteria)
ever be faster than a query that uses a single index followed by a
looping read of the base table:
select rowid from T where
lat >= ? and lon <= ? and lat <= ? and lon >= ?
Regards
Tim Romano
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] INTERSECT versus INNER JOIN with latitude, longitude queries
On 3/22/2010 7:32 AM, Tim Romano wrote: > On 3/22/2010 2:15 AM, Max Vlasov wrote: > >>> Assuming a table where Latitude column and Longitude column each have >>> their own index: >>> >>> perform select #1 which returns the rowids of rows whose latitude meets >>> criteria >>> INTERSECT >>> perform select #2 which returns the rowids of rows whose longitude meets >>> criteria >>> >>> >>> >>> >> Ok, just test. >> >> Created a base with a table >> >> CREATE TABLE [TestTable] ([Id] INTEGER PRIMARY KEY AUTOINCREMENT, >> [X] INTEGER,[Y] INTEGER) >> >> Filled with 1,000,000 records: >> >> INSERT INTO TestTable >> (X, Y) >> VALUES >> ((random() % 5) + 4, (random() % 5) + 4) >> >> Final size: 44M >> >> Simple query >> SELECT rowid FROM TestTable WHERE (X BETWEEN 3 AND 4) >> Time: 330 ms, 110,000 rows >> >> Intersect query >> SELECT rowid FROM TestTable WHERE (X BETWEEN 3 AND 4) INTERSECT >> SELECT rowid FROM TestTable WHERE (Y BETWEEN 3 AND 4) >> Time:1800 ms, 10,000 rows >> >> and from my vfs stat the latter reads about 3M of data from this 44M base >> (so no full table scan) >> >> You say that your INNER JOIN QUERY faster? You probably have a different >> scheme, maybe that's the reason, but please let us know in this case >> >> Max >> >> > Max, > The three main differences between my table schema and yours above: > -- x and y are declared as floats > -- and the x and y values were not random values > > What is your performance with a query that uses only a single index > without the INTERSECT function? > > select rowid from TT where x>= ? and y<= ? and and x<= ? and y>= ? > > Regards > Tim Romano > ___ > I forgot to add the third difference: the indexes :-) TR ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] INTERSECT versus INNER JOIN with latitude, longitude queries
On 3/22/2010 2:15 AM, Max Vlasov wrote: >> Assuming a table where Latitude column and Longitude column each have >> their own index: >> >> perform select #1 which returns the rowids of rows whose latitude meets >> criteria >> INTERSECT >> perform select #2 which returns the rowids of rows whose longitude meets >> criteria >> >> >> > Ok, just test. > > Created a base with a table > > CREATE TABLE [TestTable] ([Id] INTEGER PRIMARY KEY AUTOINCREMENT, > [X] INTEGER,[Y] INTEGER) > > Filled with 1,000,000 records: > > INSERT INTO TestTable > (X, Y) > VALUES > ((random() % 5) + 4, (random() % 5) + 4) > > Final size: 44M > > Simple query > SELECT rowid FROM TestTable WHERE (X BETWEEN 3 AND 4) > Time: 330 ms, 110,000 rows > > Intersect query > SELECT rowid FROM TestTable WHERE (X BETWEEN 3 AND 4) INTERSECT > SELECT rowid FROM TestTable WHERE (Y BETWEEN 3 AND 4) > Time:1800 ms, 10,000 rows > > and from my vfs stat the latter reads about 3M of data from this 44M base > (so no full table scan) > > You say that your INNER JOIN QUERY faster? You probably have a different > scheme, maybe that's the reason, but please let us know in this case > > Max > Max, The three main differences between my table schema and yours above: -- x and y are declared as floats -- and the x and y values were not random values What is your performance with a query that uses only a single index without the INTERSECT function? select rowid from TT where x >= ? and y <= ? and and x <= ? and y >= ? Regards Tim Romano ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] INTERSECT versus INNER JOIN with latitude, longitude queries
On 3/21/2010 5:22 PM, Max Vlasov wrote: > On Sun, Mar 21, 2010 at 3:50 PM, Tim Romano wrote: > > >> For someone who doesn't read C, could someone who knows please describe >> the SQLite INTERSECT algorithm? What optimizations are available to it? >> Does INTERSECT have to assume that neither vector is pre-sorted? Here's >> the background of my question: >> >> >> > Tim, > maybe drh answer on my question regarding INTERSECT could help? > > http://www.mail-archive.com/sqlite-users@sqlite.org/msg49646.html > > Max > ___ > Thanks, Max. What I was wondering, in particular, is whether an intersection using some form of "minimal perfect hashing" [for those who, like me, are novices at this, here's some discussion: http://en.wikipedia.org/wiki/Perfect_hash_function] of two vectors of rowids would be less expensive than a single index read (against index on Latitude, say) followed by a base table read to compare the longitude values of each of the candidate rows. Assuming a table where Latitude column and Longitude column each have their own index: perform select #1 which returns the rowids of rows whose latitude meets criteria INTERSECT perform select #2 which returns the rowids of rows whose longitude meets criteria You could build a perfect hash from one of the vectors, then iterate the other vector and ask the hash whether it contains the current iterator value; if yes, add value to the intersection set. I do not have any idea of the costliness (RAM, CPU, DISK I/O) of such an approach. Regards Tim Romano ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] INTERSECT versus INNER JOIN with latitude, longitude queries
On 3/21/2010 10:26 AM, Igor Tandetnik wrote: > Tim Romano wrote: > >> For latitude/longitude queries >> > Without diving into the details of your situation, I wonder if you are aware > of R-Tree extension: > > http://www.sqlite.org/rtree.html > Thank you Igor. I had read about R-TREE after seeing that extension mentioned recently in this forum. But I am also curious about INTERSECT and what sort of optimizations it uses now and what additional intelligence, if any, it could be retrofitted to employ. Regards Tim Romano ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] INTERSECT versus INNER JOIN with latitude, longitude queries
For someone who doesn't read C, could someone who knows please describe the SQLite INTERSECT algorithm? What optimizations are available to it? Does INTERSECT have to assume that neither vector is pre-sorted? Here's the background of my question: For latitude/longitude queries, I was speculating that after a table had reached some unknown number of rows, INTERSECTing two vectors of rowids might be faster than an INNER JOIN in which only one index had been chosen by SQLite, the one on LAT or LON, which would require reading the base table in order to fetch the other geo-values. The count of matching rowids extracted via the index is the number of rows that would have to be read from the base table in order to examine the other geo-column's value. I supposed that with an INTERSECT, one could select the rowids of those tuples that met the latitude criteria, and INTERSECT these with the rowids of those tuples that met the longitude criteria, so that each select could use an index. However, the INNER JOIN with a single index was always faster in my tests, though I never tested with more than a million rows. So it seems that intersecting two vectors of rowids is more expensive than using one index and reading the base table to get the other geo-value, either that, or the point at which INTERSECT becomes faster than INNER JOIN is well beyond the size of my test database. Regards Tim Romano ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [sqlite-dev] Poor SQLite indexing performance
I have not done this, but if you have enough RAM available, you might try putting your primary keys in a table in an in-memory database, and test for existence there. That would allow you to enforce uniqueness while postponing creation of the PK index on the disk table until after the initial population has completed. The only other way to enforce uniqueness without an index is a hashed table (a feature not available in SQLite). On a table with very many rows, finding the key using a hash can be much quicker than scanning a b-tree for it, and inserts moreover do not slow down as no unique index is being created/reorganized during batch population of the table. Regards Tim Romano On 3/15/2010 10:31 AM, Pavel Ivanov wrote: > > >> Is there any way to have a UNIQUE >> field but disable indexing till the end? >> > How do you expect your uniqueness to be enforced? SQLite does that by > looking into index - if value is there then it is repeated, if value > is not there then it's unique and should be inserted into index for > further check. > > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] On conflicting Primary key how to insert record and increment the Key
If you want to "combine two tables" by copying rows from TableB into
TableA, and TableA has an autoincrementing primary key, there is no need
to reference the PK column in the SQL statement:
insert into TableA(address, weight)
select address, weight from TableB
The approach above does indeed "bypass the primary key conflict" because
it leaves it up to TableA to assign the PK value upon insert. Now, I
don't know what inflexible "program" you are using that doesn't give you
control over which columns you want to select. Maybe your "program" will
let you create a view on TableB?
create view MyView as select address, weight from TableB
and then you could
insert into TableA(address, weight)
select address, weight from MyView
Regards
Tim Romano
On 3/15/2010 9:32 AM, dravid11 wrote:
> Well the situation is that i am merging data of one table in data of another
> table using a program
> so it is going to add all values by it self .I am not actually using insert
> query in that case to select values to add.
>
>
> There is another scenario , what if inserting a data and i want to bypass
> the primary key conflict and just update rest of the values.
>
> again i am combing two tables together so it should take all columns .
>
>
>
> Tim Romano wrote:
>
>> If all you want to do is to insert a new row, do not mention the primary
>> key column name in the insert statement:
>>
>> INSERT INTO temp (address, weight)
>> values( "blah blah", 100)
>>
>> The autoincrementing primary key will be autoincremented.
>>
>> Regards
>> Tim Romano
>>
>>
>> On 3/15/2010 9:15 AM, dravid11 wrote:
>>
>>> Hello !
>>> I have been trying to search for this solutions for days,yet did not find
>>> the solution.
>>>
>>>
>>> I want to write an insert query on a table. When there is a conflicting
>>> primary key
>>> then it should increment the primary key and insert the row .
>>>
>>> For example i want to run this query
>>> INSERT INTO temp VALUES("1","112","112");
>>>
>>> Here first column is auto increment primary Key.
>>> This query will add the record(20,112,112) in the table
>>> Now when i run the query as
>>>
>>> INSERT INTO temp VALUES("1","100","100");
>>>
>>> I want the result as (2,100,100)
>>>
>>> But it give unique constraint error.
>>> How can i do that ,i.e, duplicate primary key comes in a table insert the
>>> new record and change the primary key of the new record.
>>>
>>> I have used this query but did not work
>>>
>>> insert or replace INTO temp (tempID,Address,Weight)
>>> VALUES(new."tempID",new."Address",new."Weight") ;
>>> SELECT RAISE(IGNORE);
>>> END
>>>
>>>
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
>>
>>
>
>
>
>
> No virus found in this incoming message.
> Checked by AVG - www.avg.com
> Version: 9.0.733 / Virus Database: 271.1.1/2748 - Release Date: 03/15/10
> 03:33:00
>
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] On conflicting Primary key how to insert record and increment the Key
If all you want to do is to insert a new row, do not mention the primary
key column name in the insert statement:
INSERT INTO temp (address, weight)
values( "blah blah", 100)
The autoincrementing primary key will be autoincremented.
Regards
Tim Romano
On 3/15/2010 9:15 AM, dravid11 wrote:
> Hello !
> I have been trying to search for this solutions for days,yet did not find
> the solution.
>
>
> I want to write an insert query on a table. When there is a conflicting
> primary key
> then it should increment the primary key and insert the row .
>
> For example i want to run this query
> INSERT INTO temp VALUES("1","112","112");
>
> Here first column is auto increment primary Key.
> This query will add the record(20,112,112) in the table
> Now when i run the query as
>
> INSERT INTO temp VALUES("1","100","100");
>
> I want the result as (2,100,100)
>
> But it give unique constraint error.
> How can i do that ,i.e, duplicate primary key comes in a table insert the
> new record and change the primary key of the new record.
>
> I have used this query but did not work
>
> insert or replace INTO temp (tempID,Address,Weight)
> VALUES(new."tempID",new."Address",new."Weight") ;
> SELECT RAISE(IGNORE);
> END
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Min() ; was: Re: if exist
On 3/9/2010 2:13 PM, P Kishor wrote: <> about the following example I provided: >> select min(c) from T where 1=2 >> group by foo >> >> returns no rows, presumably because the null value was removed from the >> aggregated set. >> Foo was simply my shorthand for "another column, not column 'c' ", Sorry. The slanty lines are just drawing attention to the group-by clause, which was the subject of my post. Tim Romano ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Min() ; was: Re: if exist
On 3/9/2010 10:56 AM, Scott Hess wrote: > On Tue, Mar 9, 2010 at 7:15 AM, Tim Romano wrote: > >> Of these three: >> >> select c from T where 1=2 // returns 0 rows >> select min(c) from T where 1=2 // returns 1 row >> select min(88,99) from T where 1=2 // returns 0 rows >> >> the only case that "threw" me is the second one, where a row is returned >> despite a WHERE condition that should yield an empty set (or so I thought). >> > The first and last cases will run for each row in the WHERE clause. > The second case is aggregating over all c, and will always return one > row, even if the WHERE clause selects many rows, so it is consistent > for it to return one row if the WHERE clause selects for no rows. > It's as if you coded it like this: > > SELECT min(SELECT c FROM t WHERE ...) > > meaning the minimum of that set of inputs, and if that set is empty, > there is no minimum, so you get a result of NULL, but not no result, > if you see what I mean. > Thanks for the replies, Scott and Igor and Pavel and Puneet. What I see is that an aggregate function needs to partner with the GROUP BY clause in order for nulls to be removed from the aggregated set. select min(c) from T where 1=2 returns 1 row that contains despite the presence of the aggregate function and so select min(c) is null from T where 1 =2 returns true (1). But select min(c) from T where 1=2 /group by/ foo returns no rows, presumably because the null value was removed from the aggregated set. Regards Tim Romano ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Min() ; was: Re: if exist
Wrapping a column in the min() function causes a query that returns no rows to return a row? select c from T where 1=2 // returns 0 rows select min(c) from T where 1=2 // returns 1 row select min(88,99) from T where 1=2 // returns 0 rows Tim Romano On 3/9/2010 4:15 AM, Martin.Engelschalk wrote: > Hi, > > try this: > > select coalesce(min(length), 0) from t where id = ? > > Martin > > Andrea Galeazzi schrieb: > >> Hi All, >> I've got a table T made up of only two fields: INT id (PRIMARY KEY) and >> INT length. >> I need a statement in order to yield 0 when the key doesn't exist. At >> this moment the query is too simple: >> SELECT length FROM T WHERE id = ? >> Any idea about it? >> ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Min() ; was: Re: if exist
On 3/9/2010 8:04 AM, P Kishor wrote: > >> select min(88,99) from T where 1=2 // returns 0 rows >> > The above is correct SQL and the answer is correct. Per the docs, > "Note that min() is a simple function when it has 2 or more arguments > but operates as an aggregate function if given only a single > argument." > > Finally, note that when returning both aggregate and non-aggregate > columns, you should use the GROUP BY clause. I believe that SQLite > will return rows even without GROUP BY, but the answer may be > undependable. > Of these three: select c from T where 1=2 // returns 0 rows select min(c) from T where 1=2 // returns 1 row select min(88,99) from T where 1=2 // returns 0 rows the only case that "threw" me is the second one, where a row is returned despite a WHERE condition that should yield an empty set (or so I thought). Regarding your point about the GROUP BY clause -- I'm not sure what you mean by "non-aggregate columns". Are you referring to a query where one wants to find the minimum value in a given column for the /entire/ table? select min(askingprice) from cars4sale group by rowid //<= a group by is required here? Regards Tim Romano ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Two columns in one index, or one column for each index?
You might also see how well INTERSECT performs on your Latitude/Longitude query. Put an index on (float) LAT and another on (float) LON and use an INTEGER primary key in MYTABLE. select * from MYTABLE JOIN ( select id from MYTABLE where (lat >= 30 and lat <= 33) INTERSECT select id from MYTABLE where (lon >=-80 and lon <= -55) ) as IDLIST on IDLIST.id = MYTABLE.id Regards Tim Romano ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Hash keys
Even though SQLite is small enough to be embedded in a phone, there are quite a few questions here about large databases and performance optimization. My question fits in with the latter. Have the SQLite architects considered adding hash key as an option to the b-tree? One of the main tables in my current (lexicographic) database in SQLite would be a prime candidate for hash keys. It contains many rows, is never sorted, subsets are almost never extracted from it (i.e.groups of rows are not extracted using some common attribute) and almost every query fetches thousands of records from this table by their PK. In such a scenario, hash-keys can be faster than b-trees. I don't mean to suggest that SQLite is slow, just the contrary. I'm very pleased with its performance and would call it "blistering". But I am looking forward to a much larger database. The first database I ever worked with was optimized for OLTP and used sparse tables with hashed primary keys. The algorithm was right-weighted and sequential numbers worked well as PKs. Hash keys delivered excellent performance when inserting or retrieving records even if the table contained millions of rows. Contention was minimized. No need for table locks, row locks sufficed. New records were interspersed not appended to a caboose, so in a busy data-entry scenario processes weren't wanting to claim the same block. And there was no b-tree structure to keep balanced. The tradeoff was that sorting and selecting *groups* of records based on a column value was relatively slow because the tables were sparse and the physical order of records was totally driven by the hash value of the key: you had to do a full table scan to select a subset. B-trees were later added to address those issues. Regards Tim Romano ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] what are the limitations for IN() lists?
Dan, Thanks for that detail about the b-tree for IN-list queries. When I examine a query plan for a query like the one below: explain query plan select * from title where id IN(10,20,30,40) the plan indicates that an index is used (there's a unique index on title.id) : TABLE title WITH INDEX TITLE_ID_UIX Does SQLite iterate every item in the unique index and look for it in the transient b-tree structure? And if so, does SQLite do this regardless of the relative number of items in each structure, index versus b-tree? We could have 1,000,000 titles and 200 items in the IN-list, but each of the million items would be looked for in the b-tree? Regards Tim Romano On 1/28/2010 12:26 PM, Dan Kennedy wrote: > On Jan 28, 2010, at 10:26 PM, Tim Romano wrote: > > >> Thanks for this clarification. >> >> Wouldn't SQLite simply rewrite my IN-list query, transparently, as an >> equijoin against a transient table that has been populated with the >> values in the IN-list? I don't understand why the IN-list should have >> to be avoided. >> > It creates a temporary b-tree structure and inserts all the values in > the IN(...) clause into it. Then for each row evaluating "? IN (...)" > can be done with a single lookup in the b-tree. > > Dan. > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] what are the limitations for IN() lists?
The front-end could be any client that can issue a RESTful request and POST parameters to the server. Mine happens to be written in Adobe FlashBuilder ( née FlexBuilder). I wrote the webservice in ASP.NET using Robert Simpson's System.Data.SQLite ADO.NET provider against SQLite3. The main challenge relating to the IN-list question I had (i.e. what is max # of items in an IN-list) is that the user on the front-end is presented with a grid analogous to this: Zip| Type (urban, suburban, rural) | City| State | Population |AverageIncome | AverageIQ | NumberOfFishingPonds My application has nothing to do with fishing, it is lexicographical in nature, but the structure of the problem is analogous: the user may see up to a thousand zip codes in a grid, which can be sorted in any number of ways and which offers the user the ability to check which zip codes for which they want to see greater detail. They might choose everything in OKLAHOMA. Or all rural ZIPS. There is also a SelectAll button which selects the whole kit and kaboodle. This list of selected zip codes gets POSTED to the webserver in one fell swoop. Rather than make a dozen or a hundred round-trips to the webserver, passing one or a few zipcodes at a time, because that would have significant latency through the cloud, I am passing the entire list of desired zip-codes, and getting a single freight train of data in response (about 200K of data which isn't too bad over broadband). The zip-list is simply plugged into an IN-list : select columns from foo where zip in ( 10024, 89445, etc ) I could inject those zip values into a TEMP table and rewrite my query as an equijoin, but I don't see why SQLite wouldn't do that transparently "behind the scenes", in any case. What would prevent such an internal optimization of the query? I understand Jay's point about avoiding the construction of SQL statement strings, but I don't consider that a hard-and-fast rule; it's simply a desideratum. It's main value, IMO, is for inserts where you don't want to recompile the same insert statement again and again and again and would use a parameter to avoid that problem. Regards Tim Romano On 1/27/2010 11:30 AM, Simon Slavin wrote: > > mm. A couple of things worth considering: first that JavaScript under HTML5 > has its own access to SQL commands. If this system is for use only inside an > organisation, and you can say everyone must use a modern browser, then you > can use the HTML5 tools which automatically ensure keep the databases local > (in fact, on the client's hard disk, not the server !). By the way, all the > browsers I've seen that support this actually use sqlite3 internally. > > However, you might be planning to do this on the server using PHP. And PHP > has more than one SQLite library and you should be sure you're using one that > uses sqlite3, not the original sqlite library. > > So part of your design decision is whether some of the presentation work can > be done in JavaScript on the client. > > >> 2c) issues query to disk-database to fetch random hex value to >> ensure temp table is named uniquely >> > You don't need this. If you're using a TEMP table, or keeping the table in > :memory:, then you can call it whatever you want: only the single connection > you're using right now can see it, and it will vanish as soon as Apache (or > whatever) has finished serving that particular web page. > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] what are the limitations for IN() lists?
Thanks for this clarification. Wouldn't SQLite simply rewrite my IN-list query, transparently, as an equijoin against a transient table that has been populated with the values in the IN-list? I don't understand why the IN-list should have to be avoided. Thanks Tim Romano On 1/27/2010 12:28 AM, Jay A. Kreibich wrote: >[] temp database are always cleaned up when the database >connection is closed. And since temp tables and indexes go into the >temp database, and not your main active database, there is no >long-term maintenance. > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] what are the limitations for IN() lists?
Thanks for the suggestion of a memory-database, Jean-Christophe. It is not something I've used so far with SQLite but I have some preliminary questions in the abstract. The typical scenario with a webservice goes like this (database connections are ephemeral, not persistent): 1. User visits URL, passing parameters to the webservice in query-string and/or in the form-fields. 2. Webservice: 1) receives the request 2) instantiates a database connection 3) creates a command with SQL statement (in my case, using IN-list) 4) executes the command 5) grabs the results 6) closes the database connection 7) sends the results to the browser-agent The question in my mind is whether the following is any more performance-efficient than the approach above (note 2a-2e and 5a-5b): 2. Webservice: 1) receives the request 2) instantiates a database connection 2a) creates an in-memory database 2b) attaches in-memory database 2c) issues query to disk-database to fetch random hex value to ensure temp table is named uniquely 2d) creates temporary table in the in-memory database 2e) populates temporary table with values that would otherwise be placed in the IN-list 3) creates a command with SQL statement (now joining disk-tables to in-memory table) 4) executes the command 5) grabs the results 5a) drops the temporary table in the IN-memory database 5b) detaches the memory-database 6) closes the database connection 7) sends the results to the browser-agent At what point does step #3) in the top IN-list approach become more expensive than steps 2a-2e and 5a-5b in the bottom in-memory approach? Regards Tim Romano ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
