[sqlite] what are the limitations for IN() lists?

What is the maximum number of literal values that can be put inside the IN ( ) list ? select * from T where aColumn in (1,2,3,4,...) I didn't see the answer here: http://www.sqlite.org/limits.html My queries could have more than 255 values from time to time, which exceeds the limit

Re: [sqlite] How to find Rank in SQLite3?

. The assumption is that there are far fewer distinct salaries than distinct employees. Tim Romano CREATE TABLE "EES" ("id" int PRIMARY KEY NOT NULL , "dept" int NOT NULL , "salary" int NOT NULL , "empname" text NOT NULL ) select EES.dept, EES.empname,

Re: [sqlite] How to find Rank in SQLite3?

Jigar, If these two employees have the same rank: dept, employee, salary 10, Joe, 75000 10, Mary, 75000 then you do not need to include employee columns in the inline view. The salary gets the rank, not the employee, and the employee record is joined to the ranked salary on salary. Tim

Re: [sqlite] How to find Rank in SQLite3?

In my last post, I wrote "salary bands" but it would be clearer to say "salary tiers". Dept, salary, rank 1, 75000, 1 1, 5, 2 1, 49000, 3 2, 7, 1 2, 68000, 2 3, 52000, 1 Tim ___ sqlite-users mailing list sqlite-users@sqlite.org

Re: [sqlite] How to find Rank in SQLite3?

-band rankings within department, you will be working with far fewer than 200,000 rows, and the salary-bank rankings-by-department inline view will probably be held in a transient table and used as the inner loop. I would put an index on dept and salary in the EEs table. Regards Tim Romano

Re: [sqlite] How to find Rank in SQLite3?

Would all employees with the same salary have the same rank within a department? On 1/13/2010 5:06 AM, Jigar Shah wrote: > I have to migrate from MySQL to SQLite3. > > Following query in mysql gives the Rank for each employee within his dept > on the basis of salary. > >

Re: [sqlite] SQlite query performs 10 times slower than MS Access query

of the SQLite gurus in this respect? Regards Tim Romano P.S. I've noticed a striking performance boost in SQLite when joined inline views (as shown below) are used instead of standard table joins. I haven't delved into it but it could be that because the transient table instantiated by the inline select

Re: [sqlite] ICU collation

Sylvain, Here is a link I provided earlier, showing how normalization can be done in the application layer: http://php.net/manual/de/normalizer.normalize.php Regards Tim Romano ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org

Re: [sqlite] ICU collation

tion to be added to the SQLite core (because I don't have access to its UDF mechanism in Adobe Flex/FlashBuilder) and do agree that there are often good reasons for wanting something to be done in the database layer, provided it does not slow the database down for everyone else. Re

Re: [sqlite] ICU collation

) the application typically does a normalization of the Unicode character into an ASCII representation: ä => ae. In Unicode umlauted 'a' (ä) is canonically equivalent to 'a' + combining diaresis but not 'a' + 'e'. Regards Tim Romano On 12/19/2009 4:07 PM, Sylvain Pointeau wrote: > Hi, >

Re: [sqlite] Using Parameters with views

on artist.artistid = ITEM_ARTIST.artistid group by artistname having count(ITEM_ARTIST.artistid) = @desireditemcount Regards Tim Romano Cariotoglou Mike wrote: > I thought of that, and the answer is NO. > actually, since my post, I did a little investigation : > it is not the view that is th

Re: [sqlite] rowid of the current insert

by using a trigger on MOVIES or by using a trigger on PEOPLE. Regards Tim Romano Yuzem wrote: > Tim Romano wrote: > >> You should keep your id and the imdbid in separate columns, because you >> can then insert a title even if IMDB does not have it yet. >> >> >

Re: [sqlite] SQL question

T.CreateTS = MostRecent.LatestTimeStamp order by T.oper, T.product, T.category, T.name NOTE, that this approach assumes the data in column CreateTS is a string and always follows the format: 2009-11-24 09:49:20.25 -MM-DD HR:MN:SECONDS.HUNDREDTHS Regards Tim Romano Florian Schricker wrote: > Hi everyo

Re: [sqlite] rowid of the current insert

You should keep your id and the imdbid in separate columns, because you can then insert a title even if IMDB does not have it yet. id INTEGER PRIMARY KEY imdbid integer// you should allow this column to be null title not null, et cetera Regards Tim Romano Yuzem wrote: > CREATE TA

Re: [sqlite] SQL selecting in two related tables?

; > But I neglected to add the issues column to the outer select; it should have read: select M.title, SubscriptionVariants.issues from Magazine as M join ( select distinct issn, issues from subscription ) as SubscriptionVariants on SubscriptionVariants.issn = M.issn order by M.title, Subscrip

Re: [sqlite] SQL selecting in two related tables?

by M.title, SubscriptionVariants.issues Regards Tim Romano cmar...@unc.edu wrote: > On Fri, 6 Nov 2009, Jean-Denis Muys wrote: > >> I have two related tables, for example: >> >> Magazine >> === >> ISSN Title >> -- >> x123 Dr Dobb's >> e753 Ti

Re: [sqlite] The next release of SQLite....

Adobe has security "sandbox" requirements, so I would not want to disparage them for their decisions, though I do wish they would be more forthcoming with information. Regards Tim Romano Jean-Christophe Deschamps wrote: >>> Does Adobe actually filter out statements similar to

Re: [sqlite] The next release of SQLite....

Thanks very much for pointing this out, Jean-Christophe. Yes, glob is exactly what I wanted. I will look further into this approach: select sqlite3_load_extension('mylibrary', 'entrypoint'); to see if Adobe's security permits it. However, the Adobe FlashBuilder database application

Re: [sqlite] The next release of SQLite....

in the future in terms of how they decide to handle normalisation of Unicode composed characters (.e.g is a + combining diaresis LIKE a-umlaut?). One can always rely upon the raw string function; not so with functions that incorporate higher-order Unicode awareness. Tim Romano Jay A. Kreibich

Re: [sqlite] The next release of SQLite....

out combining characters and base characters and higher order Unicode intelligence could be saved for a UREVERSE() function, one which preserves Unicode composed characters. Thanks for considering. Regards Tim Romano D. Richard Hipp wrote: > Last minute comments on the pending release of

Re: [sqlite] Pragma not allowed in SQL error

Thanks, Simon. I've tried "PRAGMA encoding" too but Adobe complains whenever it sees PRAGMA. Tim Romano Simon Slavin wrote: > On 3 Dec 2009, at 2:51pm, Tim Romano wrote: > > >> The statement I'm issuing to the database >> via Adobe's libraries is >

Re: [sqlite] Pragma not allowed in SQL error

are preventing my PRAGMA directive from being executed? Tim Tim Romano wrote: > I was trying to do > > PRAGMA set case_sensitive_like =1 > > in Adobe Flash Builder Beta 4 and got the following error: > > Error #2044: Unhandled SQLErrorEvent:. errorID=3115, operation=execute , >

[sqlite] Pragma not allowed in SQL error

I was trying to do PRAGMA set case_sensitive_like =1 in Adobe Flash Builder Beta 4 and got the following error: Error #2044: Unhandled SQLErrorEvent:. errorID=3115, operation=execute , message=Error #3115: SQL Error. , details=PRAGMA is not allowed in SQL. Is this an Adobe error? Or are they

Re: [sqlite] WHERE CLAUSE in UNICODE

column to use NOCASE collation and have the LIKE operator be case-sensitive when used with the BINARY column and case-insensitive when used with the NOCASE column-- at least not without a PRAGMA change. Thanks. Tim Romano Igor Tandetnik wrote: > ... LIKE is case insensitive by default, while BIN

Re: [sqlite] WHERE CLAUSE in UNICODE

column. I /believe/ case_sensitive_like is OFF. I've done nothing to change turn it on. How can I be sure? Thanks Tim Romano ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] WHERE CLAUSE in UNICODE

Why does the optimizer not attempt to use an index when the WHERE clause uses the LIKE operator with a text column, as in: ... where myTextColumn like 'M%' My question ultimately concerns Unicode and indexing, and since these subjects are being discussed *passim* in this thread, I hope you

Re: [sqlite] another Feature Request: char from codepoint? (with some additional thoughts on the demerits of Unicode intelligence in the database)

; all operations that make use of the raw string could be carried out by the database engine. Regards Tim Romano ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] another Feature Request: char from codepoint?

self (or I can borrow one from a generous benefactor). Regards Tim Romano Roger Binns wrote: > -BEGIN PGP SIGNED MESSAGE- > Hash: SHA1 > > Tim Romano wrote: > >> I would hope that it could be included by default. >> > > [Note I do not speak

Re: [sqlite] Suggested user-defined-function example

Thank you, Jay and Pavel. So, there is much work going on "behind the scenes" in these two lines because of the tightly-knit connection between TCL and SQlite: proc sql_addnum { a b } { return [expr { $a + $b }] } db function addnum sql_addnum Regards

Re: [sqlite] Suggested user-defined-function example

stored inside the database? Do the functions one registers in SQLite with the "function" method have at their disposal only simple scalar operators, or can there be loop control structures? Thanks Tim Romano Walter Dnes wrote: > Whilst trying to get a TCL script to create a funct

Re: [sqlite] another Feature Request: char from codepoint?

sibly malformed") representation for insertion into a Text field. I want to sidestep that issue by having the flip() done by the database. Regards Tim Romano Jean-Denis Muys wrote: > On 11/19/09 14:55 , "Tim Romano" <tim.rom...@yahoo.com> wrote: > > > >

Re: [sqlite] another Feature Request: char from codepoint?

execute the query in SQLite3.exe command-line. Regards Tim Romano ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] another Feature Request: char from codepoint?

something else to exclude to bring the weight down! :-) Regards Tim Romano ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] another Feature Request: char from codepoint?

preserves combined Unicode forms during the reversal could be weighty but the raw-flip is a tiny bit of code. Regards Tim Romano Igor Tandetnik wrote: > Tim Romano <tim.rom...@yahoo.com> wrote: > >> I respectfully disagree, Roger, about the simplicity. Creating an >> exte

Re: [sqlite] another Feature Request: char from codepoint?

Apologies. An accident -- I was trying to grab the To: address from a previous email and didn't realize there was a big thing attached. I don't see anything attached to this one. I hope there isn't. Tim Romano Jean-Christophe Deschamps wrote: > > Please don't set the ReplyTo field to th

Re: [sqlite] another Feature Request: char from codepoint?

, the Microsoft SQL Server 2000 reverse() function does it codepoint by codepoint, and their substring() function also splits combining characters from base characters; the len() function counts codepoints too, and does not merge combining forms and base forms into one unit. Regards Tim Romano

[sqlite] another Feature Request: char from codepoint?

I wanted to get a clearer sense of how SQLite treats decomposed Unicode characters (http://unicode.org/faq/char_combmark.html#2) in its various string functions and with the concatenation operator yet there doesn't seem to be any way to get them into the database using the SQlite3.EXE

Re: [sqlite] Unicode support

point from Swahili next to a codepoint from Hungarian? Shouldn't I be able to say to a database: this column contains codepoints (characters) and collation is not relevant, sort the column using the numeric value of the codepoints? Tim Romano Nicolas Williams wrote: > On Tue, Nov 17, 2009

Re: [sqlite] Unicode support

For those who are insisting on Unicode graphemic codepoint-combination intelligence: why can't we have a function that simply reverses the order of the codepoints, and is blissfully ignorant about what those individual codepoints or codepoint-combinations might signify as graphemes in a

Re: [sqlite] feature request: built-in FLIP(string) function

thout much to-do. Tim Romano Simon Slavin wrote: > On 17 Nov 2009, at 5:52pm, Igor Tandetnik wrote: > > >> But for your goals, it has to be sortable, right? In a proper Unicode >> collation, U+0041 U+0301 would behave quite differently from U+0301 U+0041. >

Re: [sqlite] feature request: built-in FLIP(string) function

he indexes are not confused by the sort order. Regards Igor Tandetnik wrote: > Tim Romano <tim.rom...@yahoo.com> wrote: > >> Understood that an index cannot be placed on a function; I wasn't >> thinking of a "virtual field" as one can have in Oracle or MS-Access, >>

Re: [sqlite] feature request: built-in FLIP(string) function

-codepoint and no attempt is made to "be intelligent" about the combining form, everything will be honky-dory. Regards Tim Romano Igor Tandetnik wrote: > Tim Romano wrote: > >> You can accomplish this on the front-end, of course, but it would be >> much more co

[sqlite] feature request: built-in FLIP(string) function

STARTS-WITH and ENDS-WITH searches are the bread-and-butter of text-centric/word-centric applications (e.g. in linguistics and philology) where you have to work with suffixes, prefixes, and enclitic|proclitic particles quite often. You must routinely examine the ends of strings in a wide range

Re: [sqlite] Why does LIKE operator affect order of query plan?

sus 40 seconds. Regards Tim Romano D. Richard Hipp wrote: > On Nov 16, 2009, at 7:02 PM, Tim Romano wrote: > > >> Thanks for the reply. Sorry, I didn't make my question clear >> enough. I >> was trying to find out if the following statement would be true: >

Re: [sqlite] Why does LIKE operator affect order of query plan?

() . Regards Tim D. Richard Hipp wrote: > On Nov 16, 2009, at 5:14 PM, Tim Romano wrote: > >> Do I understand the docs correctly, that if the query statement >> contains >> a literal string (as distinct from a bound parameter) it doesn't >> matter >&g

Re: [sqlite] Why does LIKE operator affect order of query plan?

pare.html> or sqlite3_prepare16() <http://www.sqlite.org/c3ref/prepare.html>. Thanks Tim Igor Tandetnik wrote: > Tim Romano wrote: > >> Thanks for the correction, Pavel, about the mixed comparison mode >> (ASCII-range: case-insensitive, above-ASCII: case-sensitive). &

Re: [sqlite] Why does LIKE operator affect order of query plan?

a requirement at all. It's just the fact that LIKE will > compare ASCII characters case-insensitive and all other characters > case-sensitive when case-sensitive comparison is off. > > Pavel > > On Mon, Nov 16, 2009 at 7:47 AM, Tim Romano <tim.rom...@yahoo.com>

Re: [sqlite] Why does LIKE operator affect order of query plan?

After reading http://www.sqlite.org/optoverview.html, I think my query meets the requirements for index use with the LIKE operator: The column is varchar(75) and so TEXT affinity. The column uses Latin-1 characters exclusively. The wildcard appears at the far right end of the string literal,

Re: [sqlite] Why does LIKE operator affect order of query plan?

Thanks for the reply. A follow question: I can understand why ... myColumn LIKE "%foo%" ... would have to do a full scan but shouldn't ...myColumn LIKE "foo%" ... be able to use an index? P Kishor wrote: > On Sun, Nov 15, 2009 at 10:39 AM, Tim Romano <t

[sqlite] Why does LIKE operator affect order of query plan?

I have a query with joined inline views that runs in about 100ms against a 4 million row table joined to a 275,000 row table. Not bad, SQLite :-) But when I use the LIKE operator instead of the = operator, the order of the query plan changes, though the same indexes are involved, and the

Re: [sqlite] optimization question

Thank you, Igor. Processing time: 5 seconds. :-) Igor Tandetnik wrote: > Tim Romano <tim.rom...@yahoo.com> wrote: > >> I've read http://www.sqlite.org/optoverview.html but don't find my >> answer there. >> >> In the following query, WOIDS has 4 million

[sqlite] etiquette question: PNG attachments (28K, 11K) of EXPLAIN results permitted?

My update query has been running for 45 minutes, and I'm not sure how to stop it. I think it's selecting a row from a table with 25,000 rows 4 million times rather than selecting a row from a table with 4 million rows 25,000 times. To prevent this in the future, I need to learn more about how

[sqlite] optimization question

I've read http://www.sqlite.org/optoverview.html but don't find my answer there. In the following query, WOIDS has 4 million rows and CORNFIX has 25,000 rows. UPDATEWOIDS SET corn = 1 WHERE EXISTS ( SELECT * FROM CORNFIX WHERE (cornfix.col_1 =

[sqlite] test harness?

I am encountering significant differences in the way the data libraries for SQLite produced by a "major player" are handling joins, unions, and inline views, in combination, compared to several other GUI front ends for SQLite which have been well-received and which jibe with each other. Is

Re: [sqlite] what sort of programming errors would cause these strange results

I haven't gotten my daily digest yet from the SQLITE-USERS group, and don't know if there have been responses to my request for help sent yesterday, but I wanted to report that I have found the cause of the errors in the library I'm using. The library is treating SQLite PK columns of INT

[sqlite] What sort of programming errors would cause these strange results?

I have a SQLite database working quite nicely. The three main tables have 4 million rows and 275,000 rows respectively, and query response times are excellent: I have used several GUI front ends to query the data, and they jibe with each other, both with regard to the (accurate) results they

[sqlite] Two feature requests

Requesting these here, since I'm not quite sure how to go about it via the WIKI (do you simply edit the request list there and prepend|append your request to the list?) 1. An IFEMPTY(a,b) operator would be a convenience, analogous to IFNULL(a,b). It would return the first non-null,

[sqlite] composite PK Constraint: effects on query performance?

Does SQLite 3.1.x tacitly create an index to implement composite PK constraints, and is that index used, when possible, to enhance the performance of joins and order by clauses? Thanks ___ sqlite-users mailing list sqlite-users@sqlite.org

<    1   2