[sqlite] Limit option
Hi Everyone, I don't know this was problem or not. I'm using version 2.8.8. I have a table with 350 record, With below Query I got 5 records, (which suppose to be result i want) SELECT DISTINCT LangNo, MovieSTK FROM VoIS WHERE BranchSMS='GKL1' AND DateID='2' AND LangNo BETWEEN 1 AND 5 ORDER BY LangNo; But when use below Query, I have only 1 record, SELECT DISTINCT LangNo, MovieSTK FROM VoIS WHERE BranchSMS='GKL1' AND DateID='2' AND LangNo BETWEEN 1 AND 5 ORDER BY LangNo Limit 7; Can someone tell me what was the problem. Beside, I'm not clear was does the statement mean - (got from the web-site) The LIMIT clause places an upper bound on the number of rows returned in the result. A negative LIMIT indicates no upper bound. The optional OFFSET following LIMIT specifies how many rows to skip at the beginning of the result set. In a compound query, the LIMIT clause may only appear on the final SELECT statement. The limit is applied to the entire query not to the individual SELECT statement to which it is attached. Can someone help me on this. Thx in advance. Regards KL Chin - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
RE: [sqlite] Subject: Re: [sqlite] Let us make SQLite more powerful
> -Original Message- > From: Mrs. Brisby [mailto:[EMAIL PROTECTED] > Sent: 29 December 2003 23:41 > To: Yogesh Vachhani > Cc: [EMAIL PROTECTED] > Subject: Re: [sqlite] Subject: Re: [sqlite] Let us make SQLite more > powerful > > > On Sat, 2003-12-27 at 07:16, Yogesh Vachhani wrote: > > > Why exactly do you think the JOIN operator is the wrong tool for > > > this? > > > > I feel JOIN operations are taxing on the Process as well as on RAM > > and HDD (in form of temporary files) > > All right then. SQLite doesn't produce temporary files for JOIN, and > your query _can_ be expressed relationally as a JOIN, so it should never > be any faster than a JOIN operation- except due to parsing. Can you > verify that parsing is taking "too long" here? > My understanding is that logically, by their very nature, joins are always likely to be slower than single table queries - I thought that this was a given downside to normalisation or am I incorrect? Maybe the downside is not pronounced in SQLite but I'm sure that you see it in other RDBMS (if you crank up the results set volume enough). > > > > If you think this is easier to read, then consider creating VIEWs > > > to > > > store intermediate queries. > > > > I have never considered this as I am not a power user of SQLite. > > Sorry...! > > VIEWs are quite normal in other SQL providers. That said, you may want > to become more familiar with SQL before you start suggesting extensions > to it- at the very least, be aware that syntactical additions may very > well be fine, but you will face criticism if your reasoning is flawed. > In SQLite, VIEWs are simply an expedient for expressing your query - there is no performance upside to using them opposed to a fully specified SQL command. > > > > > So one can see that one would not lose anything by using SQLite's > > > > extended storage model (if implemented). Even though the > > > LineItems > > > > information is embedded into the Invoices table, one can still > > > write > > > > a simple query to access it as if it were in an independent > > > table. > > > > > > Backwards compatibility gets lost. > > > API changes so programs need to be altered. > > I did say at the start that we should extend the existing > > functionality so that who are comfortable with the existing on will > > keep on using this one and other can try out the other one! > > > > This not lead to lost in BACKWARD compatibility?! > > If you have a table which has a value that can return multiple values > you MUST alter the API of the callback function or of the fetch > function. Otherwise how is older code supposed to deal with a table > having multiple values in an attribute? > Why? Most systems will return you the whole column value with delimiter character(s) separating the sub-fields. I think the alterations to the API will not be in the callback at all, but WILL be in the query processor and index engine. We are talking about changing the search engine to delineate certain field values when querying. It's easier for the indexer, which simply creates multiple postings for the same record but with different terms (each sub-field). > > > > That's an idea > > > anyway, it can already be done with SQLite by supplying your own > > > function. Additionally, you could add lists like this yourself > > > (pick a terminator, and make a function that indexes) > > > > By the way does anyone know whether SQLite supports User Defined > > Functions? If such a thing is possible then all such functionality > > can be implementd out side SQLite in a separate DLL. What do u all > > think? > > Yes, SQLite supports "user-defined" functions. The Wiki has a great deal > of documentation about this... > > > > Generally speaking, I think that trying to store an unknown number > > > of distinct values in one record column is a bad idea, and it > > > violates level 1 normalization. You really should use a separate > > > table for the phone numbers and/or addresses, one record per > > > instance. > > > > It does not violate some of the normalization rules but then how many > > follow this pracitcally. In fact I have noticed that in practicaly > > implementations many times a developer has to avoid normalization > > rules to implement some pecular requirements of their customers > > I do not think you know what you are talking about. "level 1 > normalization" comes from graph theory and describes a manner in which > every graph can be translated into another graph as utilizing no nested > functions - which are called "level 1 normal forms". > > I don't think the responder quite spoke correctly either. The structure > you desire _can_ be folded down (normalized) into the view normal people > have of SQL: You aren't suggesting that anything be made available that > isn't presently available- but you want to write these systems without > learning SQL, or because you believe giving syntactical hints will make > queries run faster. > > I do think that this reas
RE: [sqlite] Subject: Re: [sqlite] Let us make SQLite more powerful
On Tue, 2003-12-30 at 06:46, Steve O'Hara wrote:
> My understanding is that logically, by their very nature, joins are always
> likely to be slower than single table queries - I thought that this was a
> given downside to normalisation or am I incorrect? Maybe the downside is
> not pronounced in SQLite but I'm sure that you see it in other RDBMS (if you
> crank up the results set volume enough).
Not necessarily; JOIN is an algebraic operation. Most RDBMS can handle
JOINs efficiently - especially with the aid of indexes. Some RDBMS even
translate "multi-occurrence columns" into JOIN operations transparently.
Some database engines cannot optimize all forms of JOIN, nor can they
optimize all syntax for a join, but these are deficiencies in the
database engine, not in SQL itself.
> > > > If you think this is easier to read, then consider creating VIEWs
> > > > to
> > > > store intermediate queries.
> > >
> > > I have never considered this as I am not a power user of SQLite.
> > > Sorry...!
> >
> > VIEWs are quite normal in other SQL providers. That said, you may want
> > to become more familiar with SQL before you start suggesting extensions
> > to it- at the very least, be aware that syntactical additions may very
> > well be fine, but you will face criticism if your reasoning is flawed.
> >
>
> In SQLite, VIEWs are simply an expedient for expressing your query - there
> is no performance upside to using them opposed to a fully specified SQL
> command.
Agreed. They were covered because another reason for the "new syntax"
was because they're "easier to read" (something I happen to disagree
with).
> > If you have a table which has a value that can return multiple values
> > you MUST alter the API of the callback function or of the fetch
> > function. Otherwise how is older code supposed to deal with a table
> > having multiple values in an attribute?
> >
>
> Why?
> Most systems will return you the whole column value with delimiter
> character(s) separating the sub-fields.
> I think the alterations to the API will not be in the callback at all, but
> WILL be in the query processor and index engine.
> We are talking about changing the search engine to delineate certain field
> values when querying. It's easier for the indexer, which simply creates
> multiple postings for the same record but with different terms (each
> sub-field).
No, we weren't talking about that. That's one solution that I suggested
(using a user-defined function to split out various portions of the
value). We were talking about adding new syntax to SQL(ite) that
performs implicit joins making it possible to return and store multiple
_values_ within a single column/row.
> I fully understand your point about normalisation and fully understanding
> issues before pronouncing on them.
> However, I think that your not seeing the full picture of the usefulness of
> multi-occurance columns with the simple example mentioned previously.
> Take a trivial names and address scenario - each person can have multiple
> addresses, phone numbers, emails, fax numbers.
> In a normalised world where we have a table for each of these, you soon run
> into a complicated query if you want to find a person who has a particular
> address, a particular phone number, a particular email etc. Don't we have
> to do unions of a number of joins?
> Whereas, in a multi-occurrence schema it's simply
> select * from names where address='vghg' and phone='776' and email='jhjh'
> etc.
and how is the callback called for each _phone_ where:
select * from names where address='yghg';
How many times is callback called? for which rows? How do we tell which
inner-structure we are in? Is the callback going to get a folded string?
an incomplete one? or just some dummy blob to be passed to another
sqlite function?
These are very important questions that _must_ be resolved. If they
aren't- then compatibility is broken.
Thus I suggested moving it entirely into functions, that is:
select * from names where any_equals('address','yghg');
where any_equals() is a function that splits/checks values as
appropriate.
besides not making any changes to the callback API, this method is
future proof, although as you might have noticed, it cannot presently
take advantage of indexes (sqlite doesn't presently index functions,
IIRC).
if the question is simple enough- and all your operations are like
any_equals() you can optimize this to use the LIKE operator which
doesn't require any new functions AND can utilize indexes (my other
suggestion).
-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
[sqlite] Compiling a shared library (.so) WITH threadsafe option enabled
I managed to compile SQLite (2.8.8) under Linux with the ThreadSafe option set to 1. It works, but the file size is twice as big as the original .so from the SQLite site (without the ThreadSafe portion). What should I do, or, what I shouldn't do???
Re: [sqlite] Compiling a shared library (.so) WITH threadsafe option enabled
Roger Reghin wrote: > I managed to compile SQLite (2.8.8) under Linux with the ThreadSafe option > set to 1. > > It works, but the file size is twice as big as the original .so from the > SQLite site (without the ThreadSafe portion). > > What should I do, or, what I shouldn't do??? 1. Run "strip" to remove symbolic information from the library. 2. Compile with -DNDEBUG=1 2. Compile with -O2 -- D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565 - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sqlite] How to find the primary key ?
>> Hi Kurt. I could use code to parse out the other stuff too. >> If its written in C/C++, would you be willing to share it? >> >> cheers >> -brett It's a tcl function that returns a list containing a string, 5 lists of strings, and 3 lists of lists of strings. You'd have to translate it. It's too long to post to the group; if you want it, let me know and I'll mail it to you directly. Regards - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sqlite] Compiling a shared library (.so) WITH threadsafe option enabled
Richard, worked =)) Thank you so much!! What I did: - Downloaded sqlite-2.8.8.tar.gz - Followed: $ tar -xzvf sqlite-2.8.8.tar.gz $ mkdir bld $ cd bld $ ../sqlite/configure So far, this is what the site tells you to do... I added these things myself to the Makefile... TCC = gcc -g -O2 -DTHREADSAFE=1 -DNDEBUG=1 LIBREADLINE = ... -lpthread Then: $ make $ cd .libs $ strip libsqlite.so.0.8.6 That's all. Now the libsqlite.so file is only 260K (less than half the last size)... May I ask?? - Is the threadsafe really enabled by changing only those two lines in the Makefile? - Isn't the libsqlite.so too small? Haven't I stripped too much from it?? Thank you so much again!! Roger Reghin. - Original Message - From: "D. Richard Hipp" <[EMAIL PROTECTED]> To: "Roger Reghin" <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: Tuesday, December 30, 2003 11:48 AM Subject: Re: [sqlite] Compiling a shared library (.so) WITH threadsafe option enabled > Roger Reghin wrote: > > I managed to compile SQLite (2.8.8) under Linux with the ThreadSafe option > > set to 1. > > > > It works, but the file size is twice as big as the original .so from the > > SQLite site (without the ThreadSafe portion). > > > > What should I do, or, what I shouldn't do??? > > >1. Run "strip" to remove symbolic information from the library. >2. Compile with -DNDEBUG=1 >2. Compile with -O2 > > -- > D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565 > - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sqlite] Compiling a shared library (.so) WITH threadsafe option enabled
Hi! > I added these things myself to the Makefile... > > TCC = gcc -g -O2 -DTHREADSAFE=1 -DNDEBUG=1 > LIBREADLINE = ... -lpthread > > Then: > > $ make > $ cd .libs > $ strip libsqlite.so.0.8.6 > > That's all. Now the libsqlite.so file is only 260K (less than half the last > size)... > > May I ask?? > > - Is the threadsafe really enabled by changing only those two lines in > the Makefile? Yes, -DTHREADSAFE=1 enables thread safety > - Isn't the libsqlite.so too small? Haven't I stripped too much from > it?? Probably you compiled with debug enabled the first time. So the compiler put a lot of debugging symbols into the output files. With -DNDEBUG=1 you disabled the debugging mode - now the compiler doesn't generate the debugging symbols. Thus the resulting files are much smaller. You can verify this: - Compile without -DNDEBUG=1. The resulting library should have the old length - Now remove the debugging symbols by using the strip command (strip libsqlite.so) - Now the library should have about 260K again... The strip command removes debugging symbols from object files, executables and libraries while -DNDEBUG=1 causes the compiler to not generate them (and so it's useless to strip the library after creation)... - Danny -- Danny Reinhold Reinhold Software & Services http://www.rsas.de - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sqlite] Compiling a shared library (.so) WITH threadsafe option enabled
Danny, thank you for your answer!! =) Roger. - Original Message - From: "Danny Reinhold" <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: Tuesday, December 30, 2003 1:15 PM Subject: Re: [sqlite] Compiling a shared library (.so) WITH threadsafe option enabled > Hi! > > > I added these things myself to the Makefile... > > > > TCC = gcc -g -O2 -DTHREADSAFE=1 -DNDEBUG=1 > > LIBREADLINE = ... -lpthread > > > > Then: > > > > $ make > > $ cd .libs > > $ strip libsqlite.so.0.8.6 > > > > That's all. Now the libsqlite.so file is only 260K (less than half the > last > > size)... > > > > May I ask?? > > > > - Is the threadsafe really enabled by changing only those two lines in > > the Makefile? > Yes, -DTHREADSAFE=1 enables thread safety > > > - Isn't the libsqlite.so too small? Haven't I stripped too much from > > it?? > Probably you compiled with debug enabled the first time. So the compiler > put a lot of debugging symbols into the output files. > With -DNDEBUG=1 you disabled the debugging mode - now the compiler > doesn't generate the debugging symbols. Thus the resulting files are much > smaller. > > You can verify this: > - Compile without -DNDEBUG=1. The resulting library should have the > old length > - Now remove the debugging symbols by using the strip command > (strip libsqlite.so) > - Now the library should have about 260K again... > > The strip command removes debugging symbols from object files, > executables and libraries while -DNDEBUG=1 causes the compiler > to not generate them (and so it's useless to strip the library after > creation)... > > - Danny > > -- > Danny Reinhold > Reinhold Software & Services > http://www.rsas.de > > > > - > To unsubscribe, e-mail: [EMAIL PROTECTED] > For additional commands, e-mail: [EMAIL PROTECTED] > > - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
[sqlite] ODBC Driver
Hi, I try to find a ODBC driver to use on linux with OOo. Can you help me ? P.S. Sorry for my english. - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sqlite] Compiling a shared library (.so) WITH threadsafe option enabled
Hi! > Danny, > > thank you for your answer!! =) No problem - but it wasn't correct... ;-) (I should sleep a little ;-)) > > > TCC = gcc -g -O2 -DTHREADSAFE=1 -DNDEBUG=1 The important thing is not only the option -DNDEBUG=1 but mainly -g ofcourse... -DNDEBUG=1 is a SQLite specific directive while -g is a compiler option for the gcc... - Danny -- Danny Reinhold Reinhold Software & Services - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sqlite] Compiling a shared library (.so) WITH threadsafe option enabled
Danny Reinhold wrote: TCC = gcc -g -O2 -DTHREADSAFE=1 -DNDEBUG=1 The important thing is not only the option -DNDEBUG=1 but mainly -g ofcourse... -DNDEBUG=1 is a SQLite specific directive while -g is a compiler option for the gcc... Actually, the -DNDEBUG=1 is not SQLite specific. This is how you disable assert()s. The SQLite library is full of assert()s for sanity checking. But it is smaller and runs twice as fast if you leave them out. -- D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565 - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sqlite] Compiling a shared library (.so) WITH threadsafe option enabled
Hi again, > Actually, the -DNDEBUG=1 is not SQLite specific. This is how > you disable assert()s. The SQLite library is full of assert()s > for sanity checking. But it is smaller and runs twice as fast > if you leave them out. Oh yes, you are right. (I forgot it because I don't use assert() very often...) I really think that I need to sleep... ;-)) BTW: I didn't find a way to control the creation of debug (with correct setting of NDEBUG) or thread safe code in the configure.ac file. Shall I add those options? - Danny - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
[sqlite] UNIQUE vs PRIMARY KEY
In SQLite, are UNIQUE columns basically the same as PRIMARY KEY columns and if they are should the table_info for a table with UNIQUE columns have '1's for UNIQUE columns? They don't right now and I'm wondering if I should think about filing a feature request that they be handled the same way. Thanks. - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sqlite] UNIQUE vs PRIMARY KEY
>> ... are UNIQUE columns basically the same as PRIMARY KEY ...? No, they're not the same thing. There are dozens of elementary articles on database theory on the www. Try a Google search on 'database primary key' or something like that. - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sqlite] UNIQUE vs PRIMARY KEY
On Dec 30, 2003, at 3:12 PM, Kurt Welgehausen wrote: ... are UNIQUE columns basically the same as PRIMARY KEY ...? No, they're not the same thing. I understand that they are not the same thing in SQL. What I want to know is whether or not applying UNIQUE and PRIMARY KEY amount to the same thing *in SQLite*. Because if they are functionally the same *in SQLite* (and I think, right now, that they are) then it might be interesting to request that UNIQUE columns be marked as PRIMARY KEYs in the table_info(). Of course, the fact that they aren't really the same thing in SQL may be one good reason not to have such a feature. But then perhaps we should have an extra column in table_info that indicates whether or not a given table column is UNIQUE. Right now, short of parsing the SQL that creates a table, I can't identify UNIQUE columns in a table. - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sqlite] UNIQUE vs PRIMARY KEY
In general, the columns that constitute the PK are not unique. It's the whole key that's unique, not the individual columns that it's composed of. - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
