Re: [sqlite] more test suite problems on Windows
Dennis Cote <[EMAIL PROTECTED]> wrote: > > Have you tried to run the latest laststmtchanges.test on Windows? I am > still getting a failure that shows the same double counting that was > originally reported on the mailing list. > > laststmtchanges-1.2.1... > Expected: [5] > Got: [10] > I think that was fixed by check-in [3868]. -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Recursive triggers
I've taken a further look. The main problem is that the cursor number is hardcoded, as can be seen in the following example (A delete trigger causing further deletes.) 29|OpenRead|3|6| 30|SetNumColumns|3|1| 31|Rewind|3|38| 32|Column|3|0| 33|Rowid|0|0| 34|Ne|355|37|collseq(BINARY) 35|Rowid|3|0| 36|FifoWrite|0|0| 37|Next|3|32| 38|Close|3|0| The OpenRead, SetNumColumns, Rewind, Column, Rowid, Next and Close commands in the example depend on fixed cursor identifiers. To be able to call a trigger recursively I'd need to modify the opcodes to support passing the appropriate cursor identifiers around on the stack like parameters in a C routine. My thinking was to allow negative cursor identifiers to indicate the location of the real cursor as an offset into the VDBE stack. This of course would mean, that the cursors oldIdx, newIdx need to be pushed onto the stack by the caller before the gosub and popped afterwards. In addition an opcode may be required to allocate a new cursor identifier dynamically and push it onto the stack, if the trigger requires new cursors. Do you see any possibility of supporting this without breaking other things? Mike -Ursprüngliche Nachricht- Von: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Gesendet: Donnerstag, 26. April 2007 21:05 An: sqlite-users@sqlite.org Betreff: Re: [sqlite] Recursive triggers "Michael Ruck" <[EMAIL PROTECTED]> wrote: > Hello, > > I've been looking into the way triggers are implemented and was > thinking about adding support for recursive triggers, as they would > simplify my current project dramatically. What was/is the reason to leave them out? > > My thoughts were adding recursive triggers by calling them like > subroutines (via the VDBE Gosub and Return) on demand. I haven't > thought this through, but wanted to ask if there are limitations or > blocking points in doing recursive triggers this way? I know that the > VDBE stack is limited and queries with very deep triggers may abort, if the stack is overflowed. > > Is the function sqlite3CodeRowTrigger, the only place I'd need to > adjust to support recursive triggers? > Recursive triggers are hard to implement correctly. But if you think you can do so by tweaking sqlite3CodeRowTrigger, then by all means give it a whirl. -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] more test suite problems on Windows
[EMAIL PROTECTED] wrote: Perhaps not. But is was the cause of problems I was having with misc7 yesterday when I checked in my patches for winXP. Richard, You were correct. I'm not sure how it happened but I must have had a log from the previous version of the test and the source for the new test. Anyway, misc7 works now. Have you tried to run the latest laststmtchanges.test on Windows? I am still getting a failure that shows the same double counting that was originally reported on the mailing list. laststmtchanges-1.2.1... Expected: [5] Got: [10] Dennis Cote - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Re: An explanation?
B V, Phanisekhar <[EMAIL PROTECTED]> wrote: Is it that when a Integer column of a table is defined as primary key, the it will be part of every index table (rather than rowid) It's not either-or. Such a column simply becomes an alias for rowid. You can refer to the same column by the name you've given it, or by the name ROWID (and also _ROWID_ and OID). How does it work when we define a non integer as primary key. Then no special aliasing occurs. You have ROWID, and you have that column with a separate index on it. Except for an INTEGER column, PRIMARY KEY is functionally equivalent to UNIQUE NOT NULL. Assuming in the example given below if we make the Title column as primary key and create index on Id, how does it affect the performance of the two queries? Performance remains the same, but the size of the data becomes larger. First, every row in the table now has three columns - an implicit ROWID, Id and TitleName. Second, since Id is no longer an alias for ROWID, a separate index on Id needs to be created and maintained. Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] An explanation?
Thanks Dennis, Is it that when a Integer column of a table is defined as primary key, the it will be part of every index table (rather than rowid) defined on that table? How does it work when we define a non integer as primary key. Assuming in the example given below if we make the Title column as primary key and create index on Id, how does it affect the performance of the two queries? Regards, Phanisekahr From: Dennis Cote [mailto:[EMAIL PROTECTED] Sent: Thu 4/26/2007 7:52 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] An explanation? B V, Phanisekhar wrote: > Thanks for that Info. > > I have another question: > > Assume I have a table given below > "CREATE TABLE IF NOT EXISTS Title(Id INTEGER PRIMARY KEY, TitleName > String)" > "CREATE UNIQUE INDEX IF NOT EXISTS TitleIdx ON TitleName" > > Now since Id is an integer and a primary key, this will work as rowid > internally. > > I have two queries that needs to be optimized: > > 1 Select TitleName from Title where Id = ? > 2 Select Id from Title where TitleName = ? > > In order to make the previous two queries optimized, how should I > declare my Table and Index? > > Should it be: > > 1 > "CREATE TABLE IF NOT EXISTS Title(Id INTEGER PRIMARY KEY, TitleName > String)" > "CREATE UNIQUE INDEX IF NOT EXISTS TitleIdx ON (TitleName, Id)" > > 2 > The one which I assumed > > Which one of these will give the better performance for the two queries? > Or is there any other alternative that will give even better > performance? > > Regards, > Phanisekhar > Phanisekhar, Your original index definition is all that is needed. The index already contains the rowid for the table record, which happens to be the column id because of the integer primary key optimization. There is nothing to be gained by adding it to the index again. Your first query will be satisfied by a binary search in the title table looking for the id. It won't use the index. Your second query will be satisfied by a binary search in the TitleIdx index looking for a matching title. It won't use the Title table. HTH Dennis Cote - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Recursive triggers
"Michael Ruck" <[EMAIL PROTECTED]> wrote: > Hello, > > I've been looking into the way triggers are implemented and was thinking > about adding support for recursive triggers, as they would simplify my > current project dramatically. What was/is the reason to leave them out? > > My thoughts were adding recursive triggers by calling them like subroutines > (via the VDBE Gosub and Return) on demand. I haven't thought this through, > but wanted to ask if there are limitations or blocking points in doing > recursive triggers this way? I know that the VDBE stack is limited and > queries with very deep triggers may abort, if the stack is overflowed. > > Is the function sqlite3CodeRowTrigger, the only place I'd need to adjust to > support recursive triggers? > Recursive triggers are hard to implement correctly. But if you think you can do so by tweaking sqlite3CodeRowTrigger, then by all means give it a whirl. -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] License Queries
"P Kishor" <[EMAIL PROTECTED]> wrote: > alright. Let's do it again (DRH can chime in to educate us if I get > it wrong) -- > You got it right as far as I can see. The problem with "public domain" (and this is something I did not know when I put SQLite in the public domain in 2001) is that some jurisdictions (ex: Germany) do not recognize the right of an author to dedicate a work to the public domain. I can say "this is in the public domain" all I want, but my words will not be legally binding in a German court, I'm told. The only way for me to get rid of the copyright on SQLite is to die, something I'm not planning to do in the near term. I'm told that a similar situation in other EU countries. Small companies do not seem to care about this. They happily use SQLite in their products and don't sweat the IP issues. Larger companies with large legal staffs, however, do tend to get concerned. They want a piece of paper containing my signature and legal language saying they can use SQLite. This is how they manage risk. For a small fee (small in comparison to the fee they are paying their own lawyer to negotigate the deal, that is) I am more than happy to provide them with this piece of paper. The license you buy includes indemnity, warranty of title, and a one-year warranty of function. So there is some substance to it. If some patent troll decides to come after SQLite (despite me using only 17+ year old algorithms) it is my financial neck on the line. Thus, if you are a company that has a lot of resources (and are therefore a lawsuit target) the license can be a pretty good deal. Four companies (whose names you would likely recognize but which I will not reveal) have purchased licenses so far (3 in the EU, 1 in the US). So far, everybody on all sides has been happy. But the licenses are entirely optional. Anybody can download a copy of the SQLite sources from the website and do anything they want with them. No license required. Just remember, if it breaks, you get to keep both pieces. ;-) -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Recursive triggers
Hello, I've been looking into the way triggers are implemented and was thinking about adding support for recursive triggers, as they would simplify my current project dramatically. What was/is the reason to leave them out? My thoughts were adding recursive triggers by calling them like subroutines (via the VDBE Gosub and Return) on demand. I haven't thought this through, but wanted to ask if there are limitations or blocking points in doing recursive triggers this way? I know that the VDBE stack is limited and queries with very deep triggers may abort, if the stack is overflowed. Is the function sqlite3CodeRowTrigger, the only place I'd need to adjust to support recursive triggers? Thanks, Mike - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] more test suite problems on Windows
Dennis Cote <[EMAIL PROTECTED]> wrote: > [EMAIL PROTECTED] wrote: > > > > > > Your previous changes left the ABC table in a different > > state than what these latter tests expect to see. > > > > > Richard, > > I don't think that is the case Perhaps not. But is was the cause of problems I was having with misc7 yesterday when I checked in my patches for winXP. -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Import quoted and NULL values with .import
Liam Healy wrote: With the quotes removed, null values come out as nothing beteween the delimiters, e.g. 2007-06-08,70,70,5,70,70,5 ends with eight null values. Sqlite interprets each as the empty string "", which is not the same thing. I presume if it said NULL it would be right, but I can't figure out how to make the script output NULL instead of nothing (and Oracle's NVL doesn't do the right thing for me). So is there a way to have SQLite interpet a missing value as NULL? Liam, It won't do you any good to get Oracle to output NULL, SQLite will import that as a string 'NULL' not as a null value. There is no way to get SQLite to import null values. All you can do is run a few update statements after you do the import that change the empty strings into real nulls. update table t set coln = null where coln = ''; HTH Dennis Cote - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Import quoted and NULL values with .import
I am trying to import (with .import) into SQLite3 some tables that are currently in an Oracle instance. To do this I am using an Oracle script (a nice one I found at http://www.tek-tips.com/viewthread.cfm?qid=1250849=8) . This script produces another script which is then executed. The result is almost what I want, but there are a couple of minor issues. The first is that every value is surrounded by double quotes. This is OK except that SQLite keeps the quotes. So for instance in the file I might have "5.4", which sqlite evidently keeps as a string instead of the number 5.4 (the column is declared numeric). I can fix that problem by removing the quotes from the produced script, but I wondered if there was a way for sqlite to interpret a value depending on the type of the column. The second issue is null values. With the quotes removed, null values come out as nothing beteween the delimiters, e.g. 2007-06-08,70,70,5,70,70,5 ends with eight null values. Sqlite interprets each as the empty string "", which is not the same thing. I presume if it said NULL it would be right, but I can't figure out how to make the script output NULL instead of nothing (and Oracle's NVL doesn't do the right thing for me). So is there a way to have SQLite interpet a missing value as NULL? Thanks, Liam
Re: [sqlite] Odd performance issue under Windows
Nuno Lucas wrote: On 4/26/07, John Elrick <[EMAIL PROTECTED]> wrote: 2. Settings which cause Media Center to return control more optimistically than Pro or Home. In this case, there would be a hypothetically higher risk of data loss on the Media Center machine. However, the point of a synchronous=FULL, as I understand it, it to thoroughly verify that the data is "safe". It would be good to know if there is a way to defeat this safeguard and how to determine if it has been defeated. The thing is cheap IDE disks already lie to the OS saying they had flushed the buffers when they don't. Maybe Windows knows it can't depend on the drive and falls back to the lazy write scheme, (the default on Windows 2000). Very possible, however, for the record the drives in both machines are SCSI and are fairly high end. John - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Odd performance issue under Windows
Nuno Lucas wrote: And you seem to not have noticed this link: http://www.microsoft.com/technet/prodtechnol/windows2000serv/maintain/optimize/wperfch7.mspx I actually did and thank you. I received your message within a minute after I had sent my last one. The wonders of email delays John - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Odd performance issue under Windows
On 4/26/07, John Elrick <[EMAIL PROTECTED]> wrote: Using two machines as an example, XP Home and XP Media Center. XP Home and Media Center with the pragma synchronous=OFF executes the test operation in under 2.0 seconds. Set to FULL the times are, respectively 118 seconds and 8 seconds. The test involves writing 1,000 rows to a table with no start/end transaction. The resulting file is 59kb in size. As each row has a "hard flush", we can see that each one takes about 118 mili-secs (118/1000s). That is a value well within the range of current normal hard disks (it depends alot on the disk rotation speed). 8 mili-seconds is a too low value, It is a value within the average read access time of current hard-disks (well, maybe not so current, but in the same order of magnitude). Too fast for a single write, so it's impossible that the flush was done. Write caching is enabled on both machines, therefore it is likely that we can eliminate Windows caching as a factor. At this point we have several distinct possibilities: 1. Hard drive caching. Both machines are relatively new, the Home machine is a two year old HP Pavilion desktop replacement. The Media Center machine is a year old HP Pavilion desktop replacement. I would tend to think that the caching capabilities of the two machines are close enough that 59kb of data shouldn't cause an order of magnitude difference. I could be wrong. I believe you are right. 2. Settings which cause Media Center to return control more optimistically than Pro or Home. In this case, there would be a hypothetically higher risk of data loss on the Media Center machine. However, the point of a synchronous=FULL, as I understand it, it to thoroughly verify that the data is "safe". It would be good to know if there is a way to defeat this safeguard and how to determine if it has been defeated. The thing is cheap IDE disks already lie to the OS saying they had flushed the buffers when they don't. Maybe Windows knows it can't depend on the drive and falls back to the lazy write scheme, (the default on Windows 2000). Maybe Windows XP is more conscious about this and tries to make sure it does it's best to flush the buffers (or maybe it's disk drivers are better). The only way for you to make sure is to install 2000/Media Center and XP on the same machine and test. SQLite always does the right thing. If the OS lies and says it completed the operation (probably because the hardware also lied to it), there's nothing SQLite can do. There is an interesting parallel around Win95 and Win2000 FLUSH performance: http://support.microsoft.com/kb/281281 And you seem to not have noticed this link: http://www.microsoft.com/technet/prodtechnol/windows2000serv/maintain/optimize/wperfch7.mspx Regards, ~Nuno Lucas 3. Settings which cause Media Center to be more aggressive about flushing its cache than Pro or Home. If this hypothesis is correct, Pro or Home would be putting a higher priority on services other than disk I/O. One final factor in my weighing is that performance reports I have read on the Wiki and other postings indicate that inserts outside of a transaction should still be closer to the single digit mark than triple digits. All of this having been said, all I am certain about at this time is that XP/Vista/Pro/Home appear to be an order of magnitude slower in returning control than Media Center and W2K. I don't know why. And that bothers me. John - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Odd performance issue under Windows
Griggs, Donald wrote: I may be confused a bit. Regarding: 1) "the described slowdown occurs consistently on Windows XP Home and Pro and on Windows Vista." On its face, I would think this means that Xp Home and Vista do *NOT* have a problem, and that "fast" behaviour represents an integrity-risk problem. With the caveat that at this point I am speculating on the available data... Using two machines as an example, XP Home and XP Media Center. XP Home and Media Center with the pragma synchronous=OFF executes the test operation in under 2.0 seconds. Set to FULL the times are, respectively 118 seconds and 8 seconds. The test involves writing 1,000 rows to a table with no start/end transaction. The resulting file is 59kb in size. Write caching is enabled on both machines, therefore it is likely that we can eliminate Windows caching as a factor. At this point we have several distinct possibilities: 1. Hard drive caching. Both machines are relatively new, the Home machine is a two year old HP Pavilion desktop replacement. The Media Center machine is a year old HP Pavilion desktop replacement. I would tend to think that the caching capabilities of the two machines are close enough that 59kb of data shouldn't cause an order of magnitude difference. I could be wrong. 2. Settings which cause Media Center to return control more optimistically than Pro or Home. In this case, there would be a hypothetically higher risk of data loss on the Media Center machine. However, the point of a synchronous=FULL, as I understand it, it to thoroughly verify that the data is "safe". It would be good to know if there is a way to defeat this safeguard and how to determine if it has been defeated. 3. Settings which cause Media Center to be more aggressive about flushing its cache than Pro or Home. If this hypothesis is correct, Pro or Home would be putting a higher priority on services other than disk I/O. One final factor in my weighing is that performance reports I have read on the Wiki and other postings indicate that inserts outside of a transaction should still be closer to the single digit mark than triple digits. All of this having been said, all I am certain about at this time is that XP/Vista/Pro/Home appear to be an order of magnitude slower in returning control than Media Center and W2K. I don't know why. And that bothers me. John - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Odd performance issue under Windows
I may be confused a bit. Regarding: 1) "the described slowdown occurs consistently on Windows XP Home and Pro and on Windows Vista." On its face, I would think this means that Xp Home and Vista do *NOT* have a problem, and that "fast" behaviour represents an integrity-risk problem. Regarding: 2) " ...hypothesis is that Media Center and Windows 2000 may be far more aggressive about committing data to disk than XP Home and Pro and Vista." Did your wording get reversed by accident? Doesn't '1)' convey the opposite of '2)' ? -Original Message- From: John Elrick [mailto:[EMAIL PROTECTED] Sent: Thursday, April 26, 2007 11:10 AM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Odd performance issue under Windows Some interesting new information, if anyone can make use of it. After adding one more machine to the test, we have established that the described slowdown occurs consistently on Windows XP Home and Pro and on Windows Vista. The problem appears to be absent on Windows Media Center (two different machines), and may be absent on Windows 2000. Executing the example Ruby script with pragma synchronous=OFF shows nearly identical execution times on all machines. Write caching is enabled on all machines. My preliminary hypothesis is that Media Center and Windows 2000 may be far more aggressive about committing data to disk than XP Home and Pro and Vista. I am still uncertain what factors or configuration settings could be affecting the performance. Thanks, John Elrick - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Odd performance issue under Windows
Some interesting new information, if anyone can make use of it. After adding one more machine to the test, we have established that the described slowdown occurs consistently on Windows XP Home and Pro and on Windows Vista. The problem appears to be absent on Windows Media Center (two different machines), and may be absent on Windows 2000. Executing the example Ruby script with pragma synchronous=OFF shows nearly identical execution times on all machines. Write caching is enabled on all machines. My preliminary hypothesis is that Media Center and Windows 2000 may be far more aggressive about committing data to disk than XP Home and Pro and Vista. I am still uncertain what factors or configuration settings could be affecting the performance. Thanks, John Elrick - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Vista problem on its aggressive "previous version"
Hi Klemens, Thank you, I'm trying to follow this: http://technet2.microsoft.com/WindowsVista/en/library/4ac505e6-dd8b-4ae7-80fa-b9d77cd8104d1033.mspx?mfr=true Cheers, Mike Klemens Friedl 提到: Try to deactivate the shadow copy for the directory where the sqlite db file(s) are stored (directory extended properties). - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] License Queries
Pavan wrote: Thanks for the quick reply. I did not quite understand to which part of the question this reply belongs. Are you suggesting that I should not take explicit license ? You certainly don' t need to. You can if it makes you or your lawyers feel more comfortable. BTW, i dont want to do any changes to sqlite, but i am more concerned about the licensing issues of the modules which are accesing sqlite. Sqlite is public domain. You can apply any license you want to any program that access sqlite. HTH Dennis Cote - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Vista problem on its aggressive "previous version"
Try to deactivate the shadow copy for the directory where the sqlite db file(s) are stored (directory extended properties). - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] License Queries
alright. Let's do it again (DRH can chime in to educate us if I get it wrong) -- On 4/26/07, Pavan <[EMAIL PROTECTED]> wrote: Hi Kishor, >you don't have to do anything. Thanks for the quick reply. I did not quite understand to which part of the question this reply belongs. .. .. > > On 4/26/07, Pavan <[EMAIL PROTECTED]> wrote: > > Hi, > > > > I have gone through this link > > http://www.sqlite.org/copyright.html and here are few more practical > queries > > related to commercial usage:- > > > > 1. If i develop a wrapper module around sqlite(without taking explicit > > license) to store some private data in the db, should the wrapper module > be > > made public ? no. > > 2. If i develop a wrapper module around sqlite(taking explicit license) > to > > store some private data in the db, should the wrapper module be made > public > > ? no. > > 3. If i take explicit license from hawci, will the license apply for > > subsequent upgrades of the sqlite ? Only DRH can answer that, but my guess would be "yes." > > 4. Anyother information which someone wants to share related to license. You can do what you wish with SQLite. There is no restriction. You don't even need to take an explicit license, but may want to do so if -- - You are using SQLite in a jurisdiction that does not recognize the public domain. - You are using SQLite in a jurisdiction that does not recognize the right of an author to dedicate their work to the public domain. - You want to hold a tangible legal document as evidence that you have the legal right to use and distribute SQLite. - Your legal department tells you that you have to purchase a license. - God knows why. Keep in mind, DRH does sell a version of SQLite that can encrypt data. Since it is encrypted, I know nothing about it. -- Puneet Kishor http://punkish.eidesis.org/ Nelson Inst. for Env. Studies, UW-Madison http://www.nelson.wisc.edu/ Open Source Geospatial Foundation http://www.osgeo.org/education/ - collaborate, communicate, compete = - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] License Queries
Hi Kishor, you don't have to do anything. Thanks for the quick reply. I did not quite understand to which part of the question this reply belongs. Are you suggesting that I should not take explicit license ? BTW, i dont want to do any changes to sqlite, but i am more concerned about the licensing issues of the modules which are accesing sqlite. Thanks, Pavan. On 4/26/07, P Kishor <[EMAIL PROTECTED]> wrote: you don't have to do anything. SQLite is in public domain. You can cook with it, make castles with it, become a billionaire (although, if you do, remember to give some money to your favorite open source project), or roll pancakes with it. If you make and modifications to SQLite AND if you want those modifications to be included in future versions of SQLite, then you have to -- 1. convince Richard that your mods are all that, and 2. put your mods in the same license as SQLite. Other than that, do what you wish with it. On 4/26/07, Pavan <[EMAIL PROTECTED]> wrote: > Hi, > > I have gone through this link > http://www.sqlite.org/copyright.html and here are few more practical queries > related to commercial usage:- > > 1. If i develop a wrapper module around sqlite(without taking explicit > license) to store some private data in the db, should the wrapper module be > made public ? > 2. If i develop a wrapper module around sqlite(taking explicit license) to > store some private data in the db, should the wrapper module be made public > ? > 3. If i take explicit license from hawci, will the license apply for > subsequent upgrades of the sqlite ? > 4. Anyother information which someone wants to share related to license. > > Thanks in advance, > Pavan. > > -- > ' > Always finish stronger than you start > * > -- Puneet Kishor http://punkish.eidesis.org/ Nelson Inst. for Env. Studies, UW-Madison http://www.nelson.wisc.edu/ Open Source Geospatial Foundation http://www.osgeo.org/education/ - collaborate, communicate, compete = - To unsubscribe, send email to [EMAIL PROTECTED] - -- ' Always finish stronger than you start *
Re: [sqlite] An explanation?
B V, Phanisekhar wrote: Thanks for that Info. I have another question: Assume I have a table given below "CREATE TABLE IF NOT EXISTS Title(Id INTEGER PRIMARY KEY, TitleName String)" "CREATE UNIQUE INDEX IF NOT EXISTS TitleIdx ON TitleName" Now since Id is an integer and a primary key, this will work as rowid internally. I have two queries that needs to be optimized: 1 Select TitleName from Title where Id = ? 2 Select Id from Title where TitleName = ? In order to make the previous two queries optimized, how should I declare my Table and Index? Should it be: 1 "CREATE TABLE IF NOT EXISTS Title(Id INTEGER PRIMARY KEY, TitleName String)" "CREATE UNIQUE INDEX IF NOT EXISTS TitleIdx ON (TitleName, Id)" 2 The one which I assumed Which one of these will give the better performance for the two queries? Or is there any other alternative that will give even better performance? Regards, Phanisekhar Phanisekhar, Your original index definition is all that is needed. The index already contains the rowid for the table record, which happens to be the column id because of the integer primary key optimization. There is nothing to be gained by adding it to the index again. Your first query will be satisfied by a binary search in the title table looking for the id. It won't use the index. Your second query will be satisfied by a binary search in the TitleIdx index looking for a matching title. It won't use the Title table. HTH Dennis Cote - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] more test suite problems on Windows
[EMAIL PROTECTED] wrote:
Your previous changes left the ABC table in a different
state than what these latter tests expect to see.
Richard,
I don't think that is the case. I'm using the misc7.test file from CVS
head. A recent change to that file that appears prior to these failures
is the omission of test misc7-6.1.X on windows. However that is followed
by this block of code
sqlite3 db test.db
execsql {
DELETE FROM abc;
INSERT INTO abc VALUES(1, 2, 3);
INSERT INTO abc VALUES(2, 3, 4);
INSERT INTO abc SELECT a+2, b, c FROM abc;
}
which is putting only know values into the table abc. The table abc
isn't used again until the tests that fail, misc7-10 and misc7-11.
I haven't used the virtual table functions which these tests are testing
on Windows, so perhaps there is a fundamental problem with virtual
tables on windows, but I'd have thought someone would have let you know
about that long ago.
Dennis Cote
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
RE: [sqlite] An explanation?
Thanks for that Info. I have another question: Assume I have a table given below "CREATE TABLE IF NOT EXISTS Title(Id INTEGER PRIMARY KEY, TitleName String)" "CREATE UNIQUE INDEX IF NOT EXISTS TitleIdx ON TitleName" Now since Id is an integer and a primary key, this will work as rowid internally. I have two queries that needs to be optimized: 1 Select TitleName from Title where Id = ? 2 Select Id from Title where TitleName = ? In order to make the previous two queries optimized, how should I declare my Table and Index? Should it be: 1 "CREATE TABLE IF NOT EXISTS Title(Id INTEGER PRIMARY KEY, TitleName String)" "CREATE UNIQUE INDEX IF NOT EXISTS TitleIdx ON (TitleName, Id)" 2 The one which I assumed Which one of these will give the better performance for the two queries? Or is there any other alternative that will give even better performance? Regards, Phanisekhar -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Thursday, April 26, 2007 3:42 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] An explanation? "B V, Phanisekhar" <[EMAIL PROTECTED]> wrote: > How does the index table looks? > > Assume the main table to be: > CREATE TABLE table1 (a INTEGER, b INTEGER) > Assume there is an index on column a: > CREATE INDEX index1 ON table1 (a); > > Now let's suppose the entries in table1 be: > 10, 91 >9, 56 > 89, 78 > 34, 12 > 99, 26 > 19, 77 > 44, 62 > 59, 55 Each table entry also has a hidden ROWID. Let's assume that the rowids are sequential. Then your data is really this: 1, 10, 91 2, 9, 56 3, 89, 78 4, 34, 12 5, 99, 26 6, 19, 77 7, 44, 62 8, 59, 55 Here the rowids are sequential. That do not have to be. But they do have to be unique and in increasing order. Because the rowids are ordered, we can do a binary search to quickly find an entry with a particular rowid. > > Corresponding to this table1 how will index table be? > The index on table1(a) consists of all table1.a values followed by their corresponding rowid, in increasing order: 9, 2 10, 1 19, 6 34, 4 44, 7 59, 8 89, 3 99, 5 > If each data value was unique, then one index lookup would find the > matching record. Can you explain how this is? Doesn't it will do binary > search on index table? > When you do: SELECT b FROM table1 WHERE a=34; SQLite first does a binary search on the index to find the entry where a==34. From this entry it discovers the rowid. rowid=4. Then it does a binary search on the table using rowid=4 to find the corresponding entry in the table. From that entry it sees that b=12. So in this case, SQLite has to do two separate binary searches, one on the index and another on the table. If, however, you declare your index like this: CREATE INDEX index1 ON table1(a, b); Then the index will look like this: 9, 56, 2 10, 91, 1 19, 77, 6 34, 12, 4 44, 62, 7 59, 55, 8 89, 78, 3 99, 26, 5 With this two-column index, if you repeat the same query SELECT b FROM table1 WHERE a=34 Then SQLite begins as it did before by doing a binary search on the index to find the row of the index where a==34. But having found that index row, it can read out the value of b=12 directly, without having to do a second binary search on the table. The original table is never consulted and the query runs twice as fast. -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] License Queries
you don't have to do anything. SQLite is in public domain. You can cook with it, make castles with it, become a billionaire (although, if you do, remember to give some money to your favorite open source project), or roll pancakes with it. If you make and modifications to SQLite AND if you want those modifications to be included in future versions of SQLite, then you have to -- 1. convince Richard that your mods are all that, and 2. put your mods in the same license as SQLite. Other than that, do what you wish with it. On 4/26/07, Pavan <[EMAIL PROTECTED]> wrote: Hi, I have gone through this link http://www.sqlite.org/copyright.html and here are few more practical queries related to commercial usage:- 1. If i develop a wrapper module around sqlite(without taking explicit license) to store some private data in the db, should the wrapper module be made public ? 2. If i develop a wrapper module around sqlite(taking explicit license) to store some private data in the db, should the wrapper module be made public ? 3. If i take explicit license from hawci, will the license apply for subsequent upgrades of the sqlite ? 4. Anyother information which someone wants to share related to license. Thanks in advance, Pavan. -- ' Always finish stronger than you start * -- Puneet Kishor http://punkish.eidesis.org/ Nelson Inst. for Env. Studies, UW-Madison http://www.nelson.wisc.edu/ Open Source Geospatial Foundation http://www.osgeo.org/education/ - collaborate, communicate, compete = - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] License Queries
Hi, I have gone through this link http://www.sqlite.org/copyright.html and here are few more practical queries related to commercial usage:- 1. If i develop a wrapper module around sqlite(without taking explicit license) to store some private data in the db, should the wrapper module be made public ? 2. If i develop a wrapper module around sqlite(taking explicit license) to store some private data in the db, should the wrapper module be made public ? 3. If i take explicit license from hawci, will the license apply for subsequent upgrades of the sqlite ? 4. Anyother information which someone wants to share related to license. Thanks in advance, Pavan. -- ' Always finish stronger than you start *
Re: [sqlite] An explanation?
"B V, Phanisekhar" <[EMAIL PROTECTED]> wrote: > How does the index table looks? > > Assume the main table to be: > CREATE TABLE table1 (a INTEGER, b INTEGER) > Assume there is an index on column a: > CREATE INDEX index1 ON table1 (a); > > Now let's suppose the entries in table1 be: > 10, 91 >9, 56 > 89, 78 > 34, 12 > 99, 26 > 19, 77 > 44, 62 > 59, 55 Each table entry also has a hidden ROWID. Let's assume that the rowids are sequential. Then your data is really this: 1, 10, 91 2, 9, 56 3, 89, 78 4, 34, 12 5, 99, 26 6, 19, 77 7, 44, 62 8, 59, 55 Here the rowids are sequential. That do not have to be. But they do have to be unique and in increasing order. Because the rowids are ordered, we can do a binary search to quickly find an entry with a particular rowid. > > Corresponding to this table1 how will index table be? > The index on table1(a) consists of all table1.a values followed by their corresponding rowid, in increasing order: 9, 2 10, 1 19, 6 34, 4 44, 7 59, 8 89, 3 99, 5 > If each data value was unique, then one index lookup would find the > matching record. Can you explain how this is? Doesn't it will do binary > search on index table? > When you do: SELECT b FROM table1 WHERE a=34; SQLite first does a binary search on the index to find the entry where a==34. From this entry it discovers the rowid. rowid=4. Then it does a binary search on the table using rowid=4 to find the corresponding entry in the table. From that entry it sees that b=12. So in this case, SQLite has to do two separate binary searches, one on the index and another on the table. If, however, you declare your index like this: CREATE INDEX index1 ON table1(a, b); Then the index will look like this: 9, 56, 2 10, 91, 1 19, 77, 6 34, 12, 4 44, 62, 7 59, 55, 8 89, 78, 3 99, 26, 5 With this two-column index, if you repeat the same query SELECT b FROM table1 WHERE a=34 Then SQLite begins as it did before by doing a binary search on the index to find the row of the index where a==34. But having found that index row, it can read out the value of b=12 directly, without having to do a second binary search on the table. The original table is never consulted and the query runs twice as fast. -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] ALTER TABLE
Is there a reason why ALTER TABLE ADD can add only one column? I'v changed the parser to allow any number of columns - I'm calling sqlite3AlterFinishAddColumn() for every column. It seems to work. Am I missing some problem, or nobody wanted more columns before? Wiktor Adamski -- Wkrec znajomych :)>>> http://link.interia.pl/f1a5c - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] lemon compile parse.y error for windows
[EMAIL PROTECTED] wrote: > > then I use the command > > lemon parse.y > > I successfully get the parse.h file > > but the file is not right. > I only get 137 ids > > the follwing 15 ids do not exist in the parse.h > > TK_TO_TEXT > > TK_CONST_FUNC > You need to run the awk script "addopcodes.awk" in order to add these additional values. -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Japanese-Korean characters
Hi Pavan, try http://reddog.s35.xrea.com/wiki/TkSQLite.html It's excellent. Ulrich On Thursday 26 April 2007 10:26, Pavan wrote: > Hi Ralf, > > Thanks for the link. My OS is Linux. Is there something available on > these lines for linux ? > > Best Regards, > Pavan. > > On 4/26/07, Ralf Junker <[EMAIL PROTECTED]> wrote: > > Hello Pavan, > > > > >Can we store/retrieve Japanese/korean characters in sqlite db ? > > > > Yes, you can well do so, as others have already pointed out. > > > > If you are also looking for a Unicode GUI SQLite database manager to > > display and edit Japanese / Korean character databases, you might want to > > have a look at SQLiteSpy, freeware from > > http://www.yunqa.de/delphi/sqlitespy/ . > > > > SQLiteSpy is designed to support any language when run on a Windows NT 4 > > and later operating system (Win2K, WinXp, Vista, etc.). I have received > > positive reports that it works well with German, French, Eastern > > European, Greek and Cyrillic characters. Given that your system fonts > > supports Japanese and Koean characters, they should work just as well. If > > you are experiencing problems, please contact me via e-mail and I will > > see what I can do. > > > > Ralf > > > > > > > > - > > To unsubscribe, send email to [EMAIL PROTECTED] > > > > - > > - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] An explanation?
Dennis, How does the index table looks? Assume the main table to be: CREATE TABLE table1 (a INTEGER, b INTEGER) Assume there is an index on column a: CREATE INDEX index1 ON table1 (a); Now let's suppose the entries in table1 be: 10, 91 9, 56 89, 78 34, 12 99, 26 19, 77 44, 62 59, 55 Corresponding to this table1 how will index table be? If each data value was unique, then one index lookup would find the matching record. Can you explain how this is? Doesn't it will do binary search on index table? Regards, Phani -Original Message- From: Dennis Cote [mailto:[EMAIL PROTECTED] Sent: Tuesday, April 24, 2007 4:06 AM To: sqlite-users@sqlite.org Subject: Re: [sqlite] An explanation? Marco Bambini wrote: > > Database is uniformly distributed, I created it ad hoc just for my > test (sqlite 3.3.12): Marco, Another way to think of this is that if your database contained random numbers in the range 1-100 for both a and b, then an index on either of those values would allow sqlite to ignore all but the requested value, or 99% of the entries. It would only have to examine 1% of the records and would run in perhaps 2% of the time of a full table scan. If your data had even more distinct values, things would be even faster. Ultimately, if each data value was unique, then one index lookup would find the matching record, and the lookup time would only be about 2/300,000 or 0.0007% of the time for a full table scan. Indexes are not a magical cure all, they only speed up lookups if you enough different values to let them to reduce the search space to a small enough portion of the entire database to pay for their overhead. Dennis Cote - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Japanese-Korean characters
Hello Ralf Junker, >>Thanks for the link. My OS is Linux. Is there something available on these >>lines for linux ? > >SQLiteSpy apparently runs on Linux with the help of wine, but I have not >tested this myself: http://www.winehq.com/ Here is a small report on this: http://appdb.winehq.org/appview.php?iAppId=2672 Ralf - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Japanese-Korean characters
Hello Pavan, >Thanks for the link. My OS is Linux. Is there something available on these >lines for linux ? SQLiteSpy apparently runs on Linux with the help of wine, but I have not tested this myself: http://www.winehq.com/ Ralf - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Japanese-Korean characters
Hi Ralf, Thanks for the link. My OS is Linux. Is there something available on these lines for linux ? Best Regards, Pavan. On 4/26/07, Ralf Junker <[EMAIL PROTECTED]> wrote: Hello Pavan, >Can we store/retrieve Japanese/korean characters in sqlite db ? Yes, you can well do so, as others have already pointed out. If you are also looking for a Unicode GUI SQLite database manager to display and edit Japanese / Korean character databases, you might want to have a look at SQLiteSpy, freeware from http://www.yunqa.de/delphi/sqlitespy/ . SQLiteSpy is designed to support any language when run on a Windows NT 4 and later operating system (Win2K, WinXp, Vista, etc.). I have received positive reports that it works well with German, French, Eastern European, Greek and Cyrillic characters. Given that your system fonts supports Japanese and Koean characters, they should work just as well. If you are experiencing problems, please contact me via e-mail and I will see what I can do. Ralf - To unsubscribe, send email to [EMAIL PROTECTED] - -- ' Always finish stronger than you start *
Re: [sqlite] Japanese-Korean characters
Hello Pavan, >Can we store/retrieve Japanese/korean characters in sqlite db ? Yes, you can well do so, as others have already pointed out. If you are also looking for a Unicode GUI SQLite database manager to display and edit Japanese / Korean character databases, you might want to have a look at SQLiteSpy, freeware from http://www.yunqa.de/delphi/sqlitespy/ . SQLiteSpy is designed to support any language when run on a Windows NT 4 and later operating system (Win2K, WinXp, Vista, etc.). I have received positive reports that it works well with German, French, Eastern European, Greek and Cyrillic characters. Given that your system fonts supports Japanese and Koean characters, they should work just as well. If you are experiencing problems, please contact me via e-mail and I will see what I can do. Ralf - To unsubscribe, send email to [EMAIL PROTECTED] -
