Re: [sqlite] Help!!! sqlite 3.5.8 crash: access violation
The sqlite3_analyzer-3_5_4 also crash when analyzing my DB: AppName: sqlite3_analyzer.exe AppVer: 0.0.0.0 ModName: sqlite3_analyzer.exe ModVer: 0.0.0.0 Offset: 00028c72 sqlite3 (3.5.9) crash when excute "PRAGMA integrity_check": AppName: sqlite3.exe AppVer: 0.0.0.0 ModName: sqlite3.exe ModVer: 0.0.0.0 Offset: d17e My DB file is too big, how can I provide it to your guys? Even the DB is damaged, sqlite should return an fault result instead of crashing, shouldn't it? - Original Message - From: "Teg" <[EMAIL PROTECTED]> To: "General Discussion of SQLite Database" Sent: Wednesday, May 14, 2008 10:17 PM Subject: Re: [sqlite] Help!!! sqlite 3.5.8 crash: access violation > Hello qinligeng, > > Wednesday, May 14, 2008, 12:58:32 AM, you wrote: > > q1c> When I execute sql statement "delete from Carimages where OpTime > q1c> <'2008-05-01 00:00:00'" in my database, sqlite3 crashed. > q1c> The Exception Information reported by XP is: > q1c> Code: 0xc005 Flags:0x > q1c> Record: 0x Address: 0x00406652 > > q1c> The sqlite3.exe is downloaded from > q1c> http://www.sqlite.org/sqlite-3_5_8.zip > q1c> The database file is to big ( about 600M, after compressed by > q1c> WinRAR, the size is 18M), so I can't upload here. > q1c> ___ > q1c> sqlite-users mailing list > q1c> sqlite-users@sqlite.org > q1c> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > I have a smallish DB that will do that here. It's interesting, the > commandline tool Sqlite3.exe simply reports it's damaged. The > statically linked version I use in my program will crash my program. I > traced it down to "sqlite_step" but, no further. > > I'd try an integrity check on your DB. Mine's definitely damaged. > > > > -- > Best regards, > Tegmailto:[EMAIL PROTECTED] > > ___ > 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] Help!!! sqlite 3.5.8 crash: access violation
I am sorry that I can not provide any sample records of the table because every time I acces the table sqlite will crash. exceute sql: select * from carimages limit 0,1; OK! exceute sql: select * from carimages limit 1,1; OK! exceute sql: select * from carimages limit 2,1; CRASH!! Maybe the database has been damaged. The shcema of table "CarImages" is : CREATE TABLE CarImages( CarID char(32) NOT NULL, CarNumber char(20) NULL, OpTime datetime NOT NULL , TSCode char(6) NOT NULL, LaneNum int NOT NULL, PicBigPlate Long null, PicSmallPlate Long null, PicBlackWhitePlate Long null, PicLane Long null, Constraint CarImages_Key Primary Key (CarID) ); - Original Message - From: "Dennis Cote" <[EMAIL PROTECTED]> To: "General Discussion of SQLite Database" Sent: Wednesday, May 14, 2008 9:50 PM Subject: Re: [sqlite] Help!!! sqlite 3.5.8 crash: access violation > [EMAIL PROTECTED] wrote: >> When I execute sql statement "delete from Carimages where OpTime >> <'2008-05-01 00:00:00'" in my database, sqlite3 crashed. >> The Exception Information reported by XP is: >> Code: 0xc005 Flags:0x >> Record: 0x Address: 0x00406652 >> >> The sqlite3.exe is downloaded from http://www.sqlite.org/sqlite-3_5_8.zip >> The database file is to big ( about 600M, after compressed by WinRAR, the >> size is 18M), so I can't upload here. > > Please don't hijack other threads. > http://en.wikipedia.org/wiki/Thread_hijacking > > What is the schema of Carimages table? Can you provide a couple of > sample records instead of the entire database? > > Dennis Cote > ___ > 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] SQLite3 C API question
On Wed, May 14, 2008 at 09:26:53PM -0700, Jon Dixon scratched on the wall: > I am a Perl programmer, and I am trying to update the DBD::SQLite > package to use version 3 rather than version 2 of SQLite (somehow it > doesn't seem that anyone else has done this yet). DBD::SQLite-1.14 (http://search.cpan.org/~msergeant/DBD-SQLite-1.14/) uses SQLite 3.4.2. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "'People who live in bamboo houses should not throw pandas.' Jesus said that." - "The Ninja", www.AskANinja.com, "Special Delivery 10: Pop!Tech 2006" ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SQLite3 C API question
I am a Perl programmer, and I am trying to update the DBD::SQLite package to use version 3 rather than version 2 of SQLite (somehow it doesn't seem that anyone else has done this yet). Unfortunately, I am really rusty on my C skills, which are needed to handle the glue between Perl and the SQLite routines. Using the API description with the old version of the routines, I believe I have most of them changed over, but the part that steps through and grabs a row at a time is a little beyond me. The old routine used the following command: imp_sth->retval = sqlite_step(imp_sth->vm, &(imp_sth->ncols), (const char ***)&(imp_sth->results), (const char ***)&(imp_sth->coldata)); to load the proper values in the proper places. Unfortunately for the purposes of this exercise, the sqlite_step interface is now significantly different. So can someone with more C background help me out with a snippet of code using the new API that will load imp_sth->ncols, imp_sth->results, and imp_sth->coldata in the same manner that the old API would have? Thanks, Jon ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SQLite remote management tools?
Are there any GUI-based tools that provide management of a remote SQLite database? I'm looking for a workstation-based tool that will allow me to manage a database on an embedded device. Thanks in advance, - Richard Klein ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Confirming locking and transaction characteristics
David Barrett <[EMAIL PROTECTED]> wrote: > Igor Tandetnik wrote: >> SQLite detects deadlock situation. The call trying to execute the >> update statement fails immediately with SQLITE_BUSY error. Neither >> transaction releases its locks: to make progress, one of them has to >> explicitly roll back. > > Aha, got it. One more question: take the same scenario as before, but > now have two distinct tables (barA and barB) inside the same database, Makes no difference: SQLite locks at file level, not table level. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Confirming locking and transaction characteristics
Igor Tandetnik wrote: > David Barrett <[EMAIL PROTECTED]> wrote: >> There is a single table (bar) with a single column (foo) with a single >> row containing the integer value "1". Two processes (A and B) have >> opened the database in READWRITE mode. They both try to atomically >> increment the value at the same time. What happens? >> >> 1) [Process A] BEGIN TRANSACTION >> 2) [Process B] BEGIN TRANSACTION >> 3) [A] SELECT foo FROM bar >> (A acquires a SHARED lock) >> 4) [B] SELECT foo FROM bar >> (B acquires a SHARED lock) >> 5) [A] UPDATE bar SET foo=2 >> (A acquires the RESERVED lock) >> 6) [B] UPDATE bar SET foo=2 >> (? query fails, B's transaction aborted, SHARED lock released?) > > SQLite detects deadlock situation. The call trying to execute the update > statement fails immediately with SQLITE_BUSY error. Neither transaction > releases its locks: to make progress, one of them has to explicitly roll > back. Aha, got it. One more question: take the same scenario as before, but now have two distinct tables (barA and barB) inside the same database, and have each process only deal with its own table: 1) [Process A] BEGIN TRANSACTION 2) [Process B] BEGIN TRANSACTION 3) [A] SELECT foo FROM barA (A acquires a SHARED lock) 4) [B] SELECT foo FROM barB (B acquires a SHARED lock) 5) [A] UPDATE barA SET foo=2 (A acquires the RESERVED lock) 6) [B] UPDATE barB SET foo=2 (returns SQLITE_BUSY?) Would the UPDATE in (6) still return SQLITE_BUSY, and would one of the processes still need to rollback for the other to continue? I guess this is really a question of whether SQLite supports table locking or only database locking (and I'm guessing it's the latter, meaning the two disjoint tables should be put in different databases so they are independently locked). > Why not simply execute "UPDATE bar SET foo=foo+1;"? Heh, just looking for a simple example of a multi-query transaction. Thanks for your help! -david ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Confirming locking and transaction characteristics
David Barrett <[EMAIL PROTECTED]> wrote: > There is a single table (bar) with a single column (foo) with a single > row containing the integer value "1". Two processes (A and B) have > opened the database in READWRITE mode. They both try to atomically > increment the value at the same time. What happens? > > 1) [Process A] BEGIN TRANSACTION > 2) [Process B] BEGIN TRANSACTION > 3) [A] SELECT foo FROM bar > (A acquires a SHARED lock) > 4) [B] SELECT foo FROM bar > (B acquires a SHARED lock) > 5) [A] UPDATE bar SET foo=2 > (A acquires the RESERVED lock) > 6) [B] UPDATE bar SET foo=2 > (? query fails, B's transaction aborted, SHARED lock released?) SQLite detects deadlock situation. The call trying to execute the update statement fails immediately with SQLITE_BUSY error. Neither transaction releases its locks: to make progress, one of them has to explicitly roll back. > 7) [A] COMMIT > (A gets the EXCLUSIVE lock, writes, clears the lock) Unless B rolled back after getting an error on step 6, A will get the same error here. Why not simply execute "UPDATE bar SET foo=foo+1;"? Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Confirming locking and transaction characteristics
Can you please double-check the following to make sure it's accurate? I've read the "File Locking and Concurrency" page and I'm trying to verify how SQLite works in the following scenario: There is a single table (bar) with a single column (foo) with a single row containing the integer value "1". Two processes (A and B) have opened the database in READWRITE mode. They both try to atomically increment the value at the same time. What happens? 1) [Process A] BEGIN TRANSACTION 2) [Process B] BEGIN TRANSACTION 3) [A] SELECT foo FROM bar (A acquires a SHARED lock) 4) [B] SELECT foo FROM bar (B acquires a SHARED lock) 5) [A] UPDATE bar SET foo=2 (A acquires the RESERVED lock) 6) [B] UPDATE bar SET foo=2 (? query fails, B's transaction aborted, SHARED lock released?) 7) [A] COMMIT (A gets the EXCLUSIVE lock, writes, clears the lock) Is this right? In particular, in (6) does B's query fail because there is already a RESERVED lock held by A? Or is there some other system that notices B's conflicting transaction and aborts it? Or do I completely misunderstand this, and do both transactions succeed (leaving the value as "2" rather than "3")? Just trying to sort this all out in my head. Thanks! -david ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite version 3.5.9
On Wed, May 14, 2008 at 3:40 PM, Jay A. Kreibich <[EMAIL PROTECTED]> wrote: > > > WORKAROUNDS: > > Set SQLites page size to be much larger (PRAGMA page_size). >Makes SQLite deal with bigger chunks of data, reducing the overhead >percentage. You'll very likely need to turn this up pretty high >to see significant changes. > > Set SQLites page cache to be much larger (PRAGMA cache_size) >Reduces the number of I/O operations. Great for lookups and sorts. >Not that useful for writes. Depends a lot on how you use the DB. > > Live dangerously and turn down/off disk syncing (PRAGMA synchronous). >Reduces the delay for writes. Dangerous. > > Or, brute force: Copy the file locally, do your stuff, copy it back. > Thank you all for your suggestions and explanations. I now understand better the complexity underlying networked volumes. I tried: PRAGMA page_size = SQLITE_MAX_PAGE_SIZE PRAGMA cache_size = 100 PRAGMA synchronous = OFF (all executed before creating any tables) There was no improvement in first read/write performance at all. Looks like the brute force solution is the only answer here. Cheers, Peter. -- - Peter K. Stys, MD Dept. of Clinical Neurosciences Hotchkiss Brain Institute University of Calgary tel (403) 210-8646 - ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Proposed SQLite C/C++ interface behavior change.
On Tue, May 13, 2008 at 7:51 PM, D. Richard Hipp <[EMAIL PROTECTED]> wrote: > for version 3.6.0 we are considering a behavior change in which a call > to sqlite3_close() will silently and automatically call > sqlite3_finalize() on all outstanding prepared statements. > > D. Richard Hipp > [EMAIL PROTECTED] > > We would be very much in favor of this change. While calling sqlite3_close() without first finalizing all statements is a programmer error, if this occurs returning SQLITE_BUSY doesn't help much, it seems to compound the problem. We actually run into this problem a lot recently after some new features have exponentially increased the concurrent access in our application. the sqlite3_close() error occurs for us as a secondary error. Usually we get db locked first, and then fail to finalize the statement (our bad), but then it's compounded when sqlite3_close() fails. Unfortunately due to how connection pooling works in SQLite.NET this sqlite3_close() failure ends up happening on a garbage collector thread which in MS's infinite wisdom leads to a full application crash (at least it did initially--we've fixed that since this started). Interestingly, we're getting these errors in SQLite.NET which Robert said earlier in this thread already handles finalizing dangling statements. We're not using the official SQLite.NET distribution, we have some small modifications, but the code he mentioned is in place in the version we're using and is untouched. Thanks, Sam -- - We're Hiring! Seeking passionate Flex, C#, or C++ (RTSP, H264) developer. Position is in the Washington D.C. metro area. Contact [EMAIL PROTECTED] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite version 3.5.9
On Wed, May 14, 2008 at 02:40:28PM -0600, Peter K. Stys scratched on the wall: > On Wed, May 14, 2008 at 2:09 PM, D. Richard Hipp <[EMAIL PROTECTED]> wrote: > > Performance on a remote volume is about 20x slower because the raw I/O > > capability of a network disk drive is about about 1/20th of a local > > disk drive. There isn't anything much SQLite can do about that. > I would disagree with this, unless I misunderstand. File copies (from the > Finder under OS X) to/from our Xserve run at about 50 MBytes/s or about 50% > of theoretical max on our Gbit LAN, whereas reading the records from the > same file via SQLite is 20-25x slower (?2MB/sec at best, terrible > performance). So there is plenty of raw I/O bandwidth across the LAN and > network drive, but for some reason SQLite access to its remote files is > extremely slow (to be clear: these are single users accessing single files). Bandwidth isn't the issue... it is mostly latency. Copy operations can buffer large amounts of data and spit it across in one big linear stream. The file system driver can easily pipeline file requests, keeping the pipe good and full (or nearly so). SQLite, on the other hand, is going to pick and choose different blocks for reading and writing from all over the data file. That's why you see the 40% drop (vs copies) even on local disks. The killer is that the overhead for a file request is MUCH larger for network based systems since it requires a network round-trip and that's aways going to be a dozen milliseconds or so, no matter what the bandwidth of the network. Copies can hide a lot of this because they're moving larger chunks of data, so the overhead percentage is reduced. If you know what you need next (such as a copy), you can also stagger requests. SQLite (or any application that accesses a file in a non-linear way) can't do that. SQLite tends to digest fairly small chunks of data (related to the page size), keeping the overhead high for network systems. Additionally, SQLite is normally extremely paranoid about I/O operations and blocks fully on all file operations, including writes. Operating systems also tend to be a lot more cautious about caching file pages in RAM from network volumes vs. local disks, which will further cut into your SQLite performance (but make little difference to a copy). I can go on and on, but it basically boils down to A) Copies are about the worst comparison you can make because they're a near-ideal situation. B) Network transaction overhead is significant and SQLite's I/O behavior tends to make the worst of that. This is pretty inherent in what SQLite does. It isn't poor coding, it's just the way things work for any system that needs quasi-random access to a file in small chunks. > So I don't understand why the huge performance hit (compared to other > network file access like Finder copies, not comparing remote vs. local) when > accessing remote SQLite files? I could understand some slowdown because of > extra overhead with network operations, but 20-fold? Yes. Easily. I'm sure if you do a bit of testing you'll also find the bandwidth of the network doesn't matter much. If you see 20-fold for GigE, I'd expect 25-fold (or less) for 100Mb. The issue is the huge number of round-trips, not the speed in which the smallish pages are passed back. WORKAROUNDS: Set SQLites page size to be much larger (PRAGMA page_size). Makes SQLite deal with bigger chunks of data, reducing the overhead percentage. You'll very likely need to turn this up pretty high to see significant changes. Set SQLites page cache to be much larger (PRAGMA cache_size) Reduces the number of I/O operations. Great for lookups and sorts. Not that useful for writes. Depends a lot on how you use the DB. Live dangerously and turn down/off disk syncing (PRAGMA synchronous). Reduces the delay for writes. Dangerous. Or, brute force: Copy the file locally, do your stuff, copy it back. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "'People who live in bamboo houses should not throw pandas.' Jesus said that." - "The Ninja", www.AskANinja.com, "Special Delivery 10: Pop!Tech 2006" ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite version 3.5.9
> You may see some performance increase by setting pragma page_size to a > larger value so that SQLite transfers fewer, but larger, blocks across > the network. I would try benchmark tests with page sizes of 8K and 32K > to see if there is a substantial difference. Good point Dennis, though you should probably mention that he would need to be using at least 3.5.8 and Vacuum after setting the page size pragma for it to actually take effect on an existing database. -Brad ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite version 3.5.9
> I would disagree with this, unless I misunderstand. File copies (from the > Finder under OS X) to/from our Xserve run at about 50 MBytes/s or about 50% > of theoretical max on our Gbit LAN, whereas reading the records from the > same file via SQLite is 20-25x slower (—2MB/sec at best, terrible > performance). So there is plenty of raw I/O bandwidth across the LAN and > network drive, but for some reason SQLite access to its remote files is > extremely slow (to be clear: these are single users accessing single files). Peter, there is a lot more latency over a network than just hitting a local disk as well, so you've got potentially hundreds of requests from disk to perform a single select on the database (traversing the Btree, etc). Your OS may perform some read-aheads and caching which would reduce the latency to nearly nothing for the disk access (on a local machine), but you're having to deal with network latency and protocol overhead on _each_ of those hundreds of requests when you're working over a network. Raw sequential throughput you mentioned really has no relevance here at all. Like Richard said, use the right tool for the job. You need a database that resides on the server and communicates using its own network protocol. If you'd like to continue using SQLite you might check out some of the server/client wrappers out there: http://www.sqlite.org/cvstrac/wiki?p=SqliteNetwork You've got to realize that no other (non-server based) database would be able to perform better in this situation. -Brad ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite version 3.5.9
Peter K. Stys wrote: > > I would disagree with this, unless I misunderstand. File copies (from the > Finder under OS X) to/from our Xserve run at about 50 MBytes/s or about 50% > of theoretical max on our Gbit LAN, whereas reading the records from the > same file via SQLite is 20-25x slower (≈2MB/sec at best, terrible > performance). So there is plenty of raw I/O bandwidth across the LAN and > network drive, but for some reason SQLite access to its remote files is > extremely slow (to be clear: these are single users accessing single files). > > In contrast SQLite R/W to local files runs at about 60% of raw binary file > access on a local volume, very acceptable (≈35MB/s vs. 60-70MB/sec). > > So I don't understand why the huge performance hit (compared to other > network file access like Finder copies, not comparing remote vs. local) when > accessing remote SQLite files? I could understand some slowdown because of > extra overhead with network operations, but 20-fold? > Random access I/O to a file using 1K pages is very different than sequential reading or writing used to do a file copy. There is *much* more overhead involved. What SQLite is doing is more like copying a directory with thousands of small files, rather than copying a single large file. You may see some performance increase by setting pragma page_size to a larger value so that SQLite transfers fewer, but larger, blocks across the network. I would try benchmark tests with page sizes of 8K and 32K to see if there is a substantial difference. HTH Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite version 3.5.9
On Wed, May 14, 2008 at 2:09 PM, D. Richard Hipp <[EMAIL PROTECTED]> wrote: > > On May 14, 2008, at 3:58 PM, Peter K. Stys wrote: > > > I'm curious, when you say performance enhancements, does that include > > improved R/W performance to/from remote volumes on OS X, which > > presently > > suffer serious (20-fold) speed issues. I had a recent post on this. > > If not, will 3.6.0 address this? > > > > Performance on a remote volume is about 20x slower because the raw I/O > capability of a network disk drive is about about 1/20th of a local > disk drive. There isn't anything much SQLite can do about that. > Dear Richard, I would disagree with this, unless I misunderstand. File copies (from the Finder under OS X) to/from our Xserve run at about 50 MBytes/s or about 50% of theoretical max on our Gbit LAN, whereas reading the records from the same file via SQLite is 20-25x slower (≈2MB/sec at best, terrible performance). So there is plenty of raw I/O bandwidth across the LAN and network drive, but for some reason SQLite access to its remote files is extremely slow (to be clear: these are single users accessing single files). In contrast SQLite R/W to local files runs at about 60% of raw binary file access on a local volume, very acceptable (≈35MB/s vs. 60-70MB/sec). So I don't understand why the huge performance hit (compared to other network file access like Finder copies, not comparing remote vs. local) when accessing remote SQLite files? I could understand some slowdown because of extra overhead with network operations, but 20-fold? Peter. -- - Peter K. Stys, MD Dept. of Clinical Neurosciences Hotchkiss Brain Institute University of Calgary tel (403) 210-8646 - ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite version 3.5.9
On May 14, 2008, at 3:58 PM, Peter K. Stys wrote: > I'm curious, when you say performance enhancements, does that include > improved R/W performance to/from remote volumes on OS X, which > presently > suffer serious (20-fold) speed issues. I had a recent post on this. > If not, will 3.6.0 address this? > Performance on a remote volume is about 20x slower because the raw I/O capability of a network disk drive is about about 1/20th of a local disk drive. There isn't anything much SQLite can do about that. If you need to access a database that physically resides on a remote machine, you should probably use a client/server database engine with the server located on the same machine where the data lives. D. Richard Hipp [EMAIL PROTECTED] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite version 3.5.9
I'm curious, when you say performance enhancements, does that include improved R/W performance to/from remote volumes on OS X, which presently suffer serious (20-fold) speed issues. I had a recent post on this. If not, will 3.6.0 address this? Thanks, Peter. On Wed, May 14, 2008 at 11:17 AM, D. Richard Hipp <[EMAIL PROTECTED]> wrote: > SQLite version 3.5.9 is now available on the SQLite website > > http://www.sqlite.org/ > http://www.sqlite.org/download.html > > This release features some minor bug fixes and performance > enhancements. There is also a new *experimental* PRAGMA called > "journal_mode" which can provide performance improvements under some > circumstances. Additional information about these and other changes > is available on the website. > > We anticipate that version 3.5.9 will be the last version in the 3.5 > series. Our plan is for the next release to be version 3.6.0 which > incorporates changes in the VFS layer used to tie SQLite into the > underlying operating system. > > D. Richard Hipp > [EMAIL PROTECTED] > > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- - Peter K. Stys, MD Dept. of Clinical Neurosciences Hotchkiss Brain Institute University of Calgary tel (403) 210-8646 - ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite version 3.5.9
On May 14, 2008, at 2:14 PM, Petite Abeille wrote: > > On May 14, 2008, at 8:10 PM, D. Richard Hipp wrote: > >> Works for me. Did you compile the shell yourself or use the prebuilt >> binary? > > I did compile it myself. Any additional configuration(s) one should > take care of to enable this pragma? > No. It should just work. As I said, I can't get it to fail. What is the result of the pragma statement - what does it return. It should return the new journal mode: $ sqlite3 test.db sqlite3> PRAGMA journal_mode=OFF; off If you did not see the "off" return, then perhaps you mistyped the pragma name. Unrecognized pragmas are silently ignored (a feature, not a bug). D. Richard Hipp [EMAIL PROTECTED] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite version 3.5.9
On May 14, 2008, at 8:10 PM, D. Richard Hipp wrote: > > Works for me. Did you compile the shell yourself or use the prebuilt > binary? Ooops... never mind... the shell works fine... I was using sqlite3_prepare and my application was linked against a different version of the lib... Everything works as advertise :) Thanks for the release! Cheers, PA. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite version 3.5.9
On May 14, 2008, at 8:10 PM, D. Richard Hipp wrote: > Works for me. Did you compile the shell yourself or use the prebuilt > binary? I did compile it myself. Any additional configuration(s) one should take care of to enable this pragma? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite version 3.5.9
On May 14, 2008, at 1:59 PM, Petite Abeille wrote: > Hello, > > On May 14, 2008, at 7:17 PM, D. Richard Hipp wrote: > >> There is also a new *experimental* PRAGMA called "journal_mode" >> which can provide performance improvements under some circumstances. > > I'm trying the new journal_mode pragma: > > % uname -v > Darwin Kernel Version 9.2.2; root:xnu-1228.4.31~1/RELEASE_I386 > > % sqlite3 -version > 3.5.9 > > pragma journal_mode = off > > But this doesn't seem to have the expected effect as a '-journal' file > is still created. > Works for me. Did you compile the shell yourself or use the prebuilt binary? D. Richard Hipp [EMAIL PROTECTED] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite version 3.5.9
Hello, On May 14, 2008, at 7:17 PM, D. Richard Hipp wrote: > There is also a new *experimental* PRAGMA called "journal_mode" > which can provide performance improvements under some circumstances. I'm trying the new journal_mode pragma: % uname -v Darwin Kernel Version 9.2.2; root:xnu-1228.4.31~1/RELEASE_I386 % sqlite3 -version 3.5.9 pragma journal_mode = off But this doesn't seem to have the expected effect as a '-journal' file is still created. Did I misunderstood what 'journal_mode = off' is meant to do, e.g. not creating a journal file in the first place? Thanks in advance. Kind regards, -- PA. http://alt.textdrive.com/nanoki/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SQLite version 3.5.9
SQLite version 3.5.9 is now available on the SQLite website http://www.sqlite.org/ http://www.sqlite.org/download.html This release features some minor bug fixes and performance enhancements. There is also a new *experimental* PRAGMA called "journal_mode" which can provide performance improvements under some circumstances. Additional information about these and other changes is available on the website. We anticipate that version 3.5.9 will be the last version in the 3.5 series. Our plan is for the next release to be version 3.6.0 which incorporates changes in the VFS layer used to tie SQLite into the underlying operating system. D. Richard Hipp [EMAIL PROTECTED] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Proposed SQLite C/C++ interface behavior change.
On Tue, May 13, 2008 at 08:22:07PM -0400, D. Richard Hipp wrote: > Perhaps a better approach would be to modify sqlite3_close() to return > SQLITE_MISUSE if called with unfinalized prepared statements and also > fail and assert() in that case. That way, applications would crash > during development so that the bugs would be found more quickly, but > after delivery (when assert() is usually turned off) would merely leak > memory and file descriptors but would otherwise continue functioning. > > By similar reasoning, perhaps SQLite should always assert(0) in places > where it returns SQLITE_MISUSE. This is an abuse of assert() since > assert() is only suppose to fail due to internal inconsistencies, not > misuse by the caller. But it would get the point across. I like your assert() idea. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Is this Sorting order right?
Mahalakshmi.m <[EMAIL PROTECTED]> wrote: > unsigned short ArtistName; > sqlite3_bind_text16(insert,1,ArtistName,-1,SQLITE_STATIC); Doesn't compile. The third parameter of sqlite3_bind_text16 is a void*, and you are passing unsigned short there. You might have meant &ArtistName, except that the length is wrong then: you do not have a NUL-terminated buffer. Also, I don't see you actually initialize ArtistName anywhere. Did you actually mean to put some random garbage value into the database? Also, you are only inserting a single row. How do you plan to demonstrate incorrect sorting order with just one row? > unsigned char * Name = sqlite3_column_text16(select, 0 ); sqlite3_column_text16 gives you a pointer to a Unicode (wide) string, not a char* (narrow) string. > For this I got wrong output.. Not at all surprising. -- With best wishes, Igor Tandetnik With sufficient thrust, pigs fly just fine. However, this is not necessarily a good idea. It is hard to be sure where they are going to land, and it could be dangerous sitting under them as they fly overhead. -- RFC 1925 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Proposed SQLite C/C++ interface behavior change.
Posted too soon ... change sqlite3_closeAndFreeMutex() to just
sqlite3_close();
My version of sqlite3_finalize() contains this small addition:
if (p->magic == VDBE_MAGIC_DEAD && p->db == NULL)
{
sqlite3_free(p);
return SQLITE_OK;
}
-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Robert Simpson
Sent: Wednesday, May 14, 2008 7:58 AM
To: 'General Discussion of SQLite Database'
Subject: Re: [sqlite] Proposed SQLite C/C++ interface behavior change.
I wrote this behavior into the SQLite.NET provider as well. However, I make
a temp copy of the open statements in the db->Vdbe and finalize the copies
so that subsequent calls to sqlite3_finalize() on the original pointer will
not fail, but be a no op.
int ret = sqlite3_close (db);
if (ret == SQLITE_BUSY && db->pVdbe)
{
while (db->pVdbe)
{
// Make a copy of the first prepared statement
Vdbe *p = (Vdbe *)sqlite3_malloc(sizeof(Vdbe));
Vdbe *po = db->pVdbe;
if (!p) return SQLITE_NOMEM;
CopyMemory(p, po, sizeof(Vdbe));
// Put it on the chain so we can free it
db->pVdbe = p;
ret = sqlite3_finalize((sqlite3_stmt *)p); // This will also free the
copy's memory
if (ret)
{
// finalize failed -- so we must put back anything we munged
CopyMemory(po, p, sizeof(Vdbe));
db->pVdbe = po;
break;
}
else
{
ZeroMemory(po, sizeof(Vdbe));
po->magic = VDBE_MAGIC_DEAD;
}
}
ret = sqlite3_closeAndFreeMutex(db);
}
return ret;
-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Roger Binns
Sent: Tuesday, May 13, 2008 7:56 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Proposed SQLite C/C++ interface behavior change.
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
Virgilio Alexandre Fornazin wrote:
> A good and new safe could be a sqlite3_close_v2() call prototyped like
>
> int sqlite3_close_v2(sqlite3 * db, int closePendingStatements);
Funnily enough that is exactly what I provide in my Python wrapper for
SQLite! Reference counting ensures that the sqlite3_db can't be freed
before all the statements are closed but the above method is a quick way
for the developer to proactively close everything.
Having a close_v2 is most likely the best solution since there won't be
semantics changes over what people have already developed. It can also
ensure the right thing happens when threads are being abused.
Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.6 (GNU/Linux)
iD8DBQFIKlSymOOfHg372QQRAiEbAJ9mbFpensXkTXmJtI90vPfTqMNpswCgmh2/
2HwZvkW8FdDUzWId2mtE5fs=
=oawA
-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
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Proposed SQLite C/C++ interface behavior change.
I wrote this behavior into the SQLite.NET provider as well. However, I make
a temp copy of the open statements in the db->Vdbe and finalize the copies
so that subsequent calls to sqlite3_finalize() on the original pointer will
not fail, but be a no op.
int ret = sqlite3_close (db);
if (ret == SQLITE_BUSY && db->pVdbe)
{
while (db->pVdbe)
{
// Make a copy of the first prepared statement
Vdbe *p = (Vdbe *)sqlite3_malloc(sizeof(Vdbe));
Vdbe *po = db->pVdbe;
if (!p) return SQLITE_NOMEM;
CopyMemory(p, po, sizeof(Vdbe));
// Put it on the chain so we can free it
db->pVdbe = p;
ret = sqlite3_finalize((sqlite3_stmt *)p); // This will also free the
copy's memory
if (ret)
{
// finalize failed -- so we must put back anything we munged
CopyMemory(po, p, sizeof(Vdbe));
db->pVdbe = po;
break;
}
else
{
ZeroMemory(po, sizeof(Vdbe));
po->magic = VDBE_MAGIC_DEAD;
}
}
ret = sqlite3_closeAndFreeMutex(db);
}
return ret;
-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Roger Binns
Sent: Tuesday, May 13, 2008 7:56 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Proposed SQLite C/C++ interface behavior change.
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
Virgilio Alexandre Fornazin wrote:
> A good and new safe could be a sqlite3_close_v2() call prototyped like
>
> int sqlite3_close_v2(sqlite3 * db, int closePendingStatements);
Funnily enough that is exactly what I provide in my Python wrapper for
SQLite! Reference counting ensures that the sqlite3_db can't be freed
before all the statements are closed but the above method is a quick way
for the developer to proactively close everything.
Having a close_v2 is most likely the best solution since there won't be
semantics changes over what people have already developed. It can also
ensure the right thing happens when threads are being abused.
Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.6 (GNU/Linux)
iD8DBQFIKlSymOOfHg372QQRAiEbAJ9mbFpensXkTXmJtI90vPfTqMNpswCgmh2/
2HwZvkW8FdDUzWId2mtE5fs=
=oawA
-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
[sqlite] Is this Sorting order right?
Igor Tandetnik wrote:
>I'm not sure I understand. What exactly are you doing differently in these
>two cases? Can you quote the exact code that fails, in full?
#include
#include
int main()
{
sqlite3* db;
sqlite3_stmt* insert;
unsigned short ArtistName;
sqlite3_open(" mysqlite.db ", &db);
sqlite3_exec(db, "pragma encoding = UTF16", 0, 0, 0);
sqlite3_exec(db, "create table ARTIST(id integer primary key not null ,
ArtistName test not null collate nocase )", 0, 0, 0);
sqlite3_prepare(db,"INSERT INTO ARTIST (ArtistName)
VALUES(?);",-1,&insert,0);
sqlite3_bind_text16(insert,1,ArtistName,-1,SQLITE_STATIC);
sqlite3_step(insert);
sqlite3_finalize(insert);
sqlite3_stmt* select;
sqlite3_prepare(db, " select ArtistName from ARTIST order by
ArtistName ", -1, &select, 0);
while (sqlite3_step(select) == SQLITE_ROW)
{
unsigned char * Name = sqlite3_column_text16(select, 0 );
printf("text= %s \n", Name );
}
sqlite3_finalize(select);
sqlite3_close(db);
return 0;
}
For this I got wrong output..
Thanks & Regards,
Mahalakshmi
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Proposed SQLite C/C++ interface behavior change.
I concur with bob's idea to get a list of open statements. But also like the idea the sqlite3_close_v2 interface that give programmers the option. Ken Bob Ebert <[EMAIL PROTECTED]> wrote: It seems like a programmer error if a statement remains unfinalized when the DB is closed. It would be great if something about the API would help programmers detect and fix errors like this. Having sqlite3_close return an error helped a little, because it made you aware there was a problem, but it didn't help much in finding the problem. I suspect that if you make sqlite3_close automatically finalize all statements it'll cause more problems for programmers that might still be hanging on to those unfinalized statements. Could you do something in between, where sqlite3_close does close the file and puts all the statements in some kind of "error" state, but which doesn't free all the memory for the unfinalized statements? Then you could safely and reliably report MISUSE later if the statement was accessed. It would also be a nice touch if sqlite3_close could report on the unfinalized statements, even just getting the SQL text used to create the statement would be a huge help in tracking down these leaks. Or add an additional API to report on all unfinalized statements that could be called when this sort of error occurs? --Bob -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of D. Richard Hipp Sent: Tuesday, May 13, 2008 5:22 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] Proposed SQLite C/C++ interface behavior change. On May 13, 2008, at 8:02 PM, Scott Hess wrote: > On Tue, May 13, 2008 at 4:51 PM, D. Richard Hipp > wrote: >> The currently documented behavior of sqlite3_close() is that when it >> called on a database connection that has unfinalized prepared >> statements is to return SQLITE_BUSY and fail to close the connection. >> The rational is that we did not want a call to sqlite3_close() to >> destroy sqlite3_stmt* pointers out from under other subsystems. But >> for version 3.6.0 we are considering a behavior change in which a >> call to sqlite3_close() will silently and automatically call >> sqlite3_finalize() on all outstanding prepared statements. >> >> This is, technically, an incompatible change and we strive to avoid >> incompatible changes. But we think it unlikely that this change will >> cause any problems, and in fact we suspect it will likely fix more >> bugs than it will induce. But before we move forward, it seems good >> to submit the idea to the community of SQLite users and programmers. >> >> Does anybody have any thoughts on this proposed behavior changes for >> the sqlite3_close() interface? > > What will happen if you call things like this: > > db = sqlite3_open(...); > sqlite3_prepare_v2(db, zSql, -1, &stmt, NULL); // ... > sqlite3_close(db); > sqlite3_finalize(stmt); > > ??? If sqlite3_finalize() will succeed, then this seems pretty > reasonable to me. If sqlite3_finalize() will fail with a segfault or > somesuch, that may be dangerous. The current behavior in the above is that sqlite3_close() will return SQLITE_BUSY and then the application will leak memory and file descriptors because the database connection is never closed. The proposed new behavior is that the stmt object would be destroyed (and its memory passed to free()) by the sqlite3_close() call. We could arrange for sqlite3_finalize() to attempt to return SQLITE_MISUSE, but if memory were reused in between the close and the finalize, there is a small but non-zero chance that the misuse could go undetected and segfault could result. Or if this were on android and memory used by the stmt object gets unmapped, you might segfault with high probability. > > My rational is that if someone is building a system which is > abstracting SQLite, then it may be that the database handles and the > statement handles will not have clear clean-up ordering (for instance > in a garbage- collected system). One _could_ go ahead and create such > an ordering by using refcounting or whatever is appropriate in your > system, but it would also seem reasonable for SQLite to manage this. > > This may also come up with multi-threaded systems, where it could be > challenging to convince another thread to finalize statements before > calling close. > > I don't suggest that you should be able to do anything interesting > with the orphaned statement handle at this point, other than > finalizing it. I suppose it would be nice if all statement-handle > functions would start throwing SQLITE_MISUSE or other appropriate > error code. > Perhaps a better approach would be to modify sqlite3_close() to return SQLITE_MISUSE if called with unfinalized prepared statements and also fail and assert() in that case. That way, applications would crash during development so that the bugs would be found more quickly, but after delivery (when assert() is usually turned off) w
Re: [sqlite] Help!!! sqlite 3.5.8 crash: access violation
Hello qinligeng, Wednesday, May 14, 2008, 12:58:32 AM, you wrote: q1c> When I execute sql statement "delete from Carimages where OpTime q1c> <'2008-05-01 00:00:00'" in my database, sqlite3 crashed. q1c> The Exception Information reported by XP is: q1c> Code: 0xc005 Flags:0x q1c> Record: 0x Address: 0x00406652 q1c> The sqlite3.exe is downloaded from q1c> http://www.sqlite.org/sqlite-3_5_8.zip q1c> The database file is to big ( about 600M, after compressed by q1c> WinRAR, the size is 18M), so I can't upload here. q1c> ___ q1c> sqlite-users mailing list q1c> sqlite-users@sqlite.org q1c> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users I have a smallish DB that will do that here. It's interesting, the commandline tool Sqlite3.exe simply reports it's damaged. The statically linked version I use in my program will crash my program. I traced it down to "sqlite_step" but, no further. I'd try an integrity check on your DB. Mine's definitely damaged. -- Best regards, Tegmailto:[EMAIL PROTECTED] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Help!!! sqlite 3.5.8 crash: access violation
[EMAIL PROTECTED] wrote: > When I execute sql statement "delete from Carimages where OpTime <'2008-05-01 > 00:00:00'" in my database, sqlite3 crashed. > The Exception Information reported by XP is: > Code: 0xc005 Flags:0x > Record: 0x Address: 0x00406652 > > The sqlite3.exe is downloaded from http://www.sqlite.org/sqlite-3_5_8.zip > The database file is to big ( about 600M, after compressed by WinRAR, the > size is 18M), so I can't upload here. Please don't hijack other threads. http://en.wikipedia.org/wiki/Thread_hijacking What is the schema of Carimages table? Can you provide a couple of sample records instead of the entire database? Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] nVARCHAR as unique index
"Farzana" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > The query we used is "SELECT * FROM Brand ORDER BY BrandDescription". > The Brand table contains more than 30,000 records. The execution time > is reasonable when we execute in the system.But it is slower when we > execute the same in the device. And do you have reasons to expect the query to run faster on the device? Does the device have a faster CPU, or faster I/O, than your development system? Because otherwise I don't quite see why you would be surprised by this outcome. Double-check that you have actually created the index in the copy of the database that's sitting on the device. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] nVARCHAR as unique index
The query we used is "SELECT * FROM Brand ORDER BY BrandDescription". The Brand table contains more than 30,000 records. The execution time is reasonable when we execute in the system.But it is slower when we execute the same in the device. Regards, Farzana. Igor Tandetnik wrote: > > "Farzana" <[EMAIL PROTECTED]> > wrote in message news:[EMAIL PROTECTED] >> We have already created such index but we have to use ORDER BY in the >> query explicitly to sort the data by BrandDescription. > > Of course. Why do you believe this is a problem? How else do you expect > SQLite to know you want results sorted by this column? SQLite will see > there's a suitable index, and will use it to speed up ORDER BY clause. > > If the query is still slower than you hoped, post the statement here, > and also post the output of this statement: > > explain query plan select ; > > Igor Tandetnik > > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > -- View this message in context: http://www.nabble.com/nVARCHAR-as-unique-index-tp17206197p17229808.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] nVARCHAR as unique index
"Farzana" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > We have already created such index but we have to use ORDER BY in the > query explicitly to sort the data by BrandDescription. Of course. Why do you believe this is a problem? How else do you expect SQLite to know you want results sorted by this column? SQLite will see there's a suitable index, and will use it to speed up ORDER BY clause. If the query is still slower than you hoped, post the statement here, and also post the output of this statement: explain query plan select ; Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] nVARCHAR as unique index
Thanks Filip. We have already created such index but we have to use ORDER BY in the query explicitly to sort the data by BrandDescription. Regards, Farzana. Filip Navara-2 wrote: > > CREATE INDEX BrandKey2 ON Brand(BrandDescription); > > On Wed, May 14, 2008 at 7:32 AM, Farzana <[EMAIL PROTECTED]> > wrote: >> >> Thanks for your reply Igor. >> >> When we checked with the provided query we found that the duplicate >> values >> are present in the BrandDescription. We are working with the device >> where >> the memory is limited. We are suppose to sort the data by >> BrandDescription.When we tried with "ORDER BY BrandDescription" in the >> query >> to be executed, its taking more time than we expected. >> >> Is there any work around to sort the data by BrandDescription >> physically? >> Thanks in advance. >> >> Regards, >> Farzana. >> >> >> >> >> Igor Tandetnik wrote: >> > >> > "Farzana" <[EMAIL PROTECTED]> >> > wrote in message news:[EMAIL PROTECTED] >> >> We are using SQLite and we have the table structure as CREATE TABLE >> >> Brand(PcNo numeric(4) Not Null,SubPcNo numeric(4) Not Null,BrandNo >> >> numeric(9) Not Null,BrandDescription nVARCHAR(254)Not Null,ST >> >> numeric(1),TS numeric(14)) where the index is CREATE UNIQUE index >> >> BrandKey1 on Brand(PcNo,SubPcNo,BrandNo). When we use this table in >> >> the application we are suppose to sort the datas by >> >> BrandDescription.So we tried to create a unique index as CREATE >> >> UNIQUE INDEX BrandKey2 on Brand(BrandDescription).But we couldn't >> >> able to create the unique index. >> > >> > Define "couldn't able". What exactly seems to be the problem? Did you >> > get an error message? What did the message say? >> > >> > Is it possible that the values in BrandDescription column are not in >> > fact unique? If they are not, then naturally you would get an error >> > trying to create a unique index. Try this statement: >> > >> > select * from Brand >> > group by BrandDescription >> > having count(*) > 1; >> > >> > If this returns any rows, you have duplicates. >> > >> > Igor Tandetnik >> > >> > >> > >> > ___ >> > sqlite-users mailing list >> > sqlite-users@sqlite.org >> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> > >> > >> >> -- >> View this message in context: >> http://www.nabble.com/nVARCHAR-as-unique-index-tp17206197p17223665.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 > > -- View this message in context: http://www.nabble.com/nVARCHAR-as-unique-index-tp17206197p17227307.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] nVARCHAR as unique index
CREATE INDEX BrandKey2 ON Brand(BrandDescription); On Wed, May 14, 2008 at 7:32 AM, Farzana <[EMAIL PROTECTED]> wrote: > > Thanks for your reply Igor. > > When we checked with the provided query we found that the duplicate values > are present in the BrandDescription. We are working with the device where > the memory is limited. We are suppose to sort the data by > BrandDescription.When we tried with "ORDER BY BrandDescription" in the query > to be executed, its taking more time than we expected. > > Is there any work around to sort the data by BrandDescription physically? > Thanks in advance. > > Regards, > Farzana. > > > > > Igor Tandetnik wrote: > > > > "Farzana" <[EMAIL PROTECTED]> > > wrote in message news:[EMAIL PROTECTED] > >> We are using SQLite and we have the table structure as CREATE TABLE > >> Brand(PcNo numeric(4) Not Null,SubPcNo numeric(4) Not Null,BrandNo > >> numeric(9) Not Null,BrandDescription nVARCHAR(254)Not Null,ST > >> numeric(1),TS numeric(14)) where the index is CREATE UNIQUE index > >> BrandKey1 on Brand(PcNo,SubPcNo,BrandNo). When we use this table in > >> the application we are suppose to sort the datas by > >> BrandDescription.So we tried to create a unique index as CREATE > >> UNIQUE INDEX BrandKey2 on Brand(BrandDescription).But we couldn't > >> able to create the unique index. > > > > Define "couldn't able". What exactly seems to be the problem? Did you > > get an error message? What did the message say? > > > > Is it possible that the values in BrandDescription column are not in > > fact unique? If they are not, then naturally you would get an error > > trying to create a unique index. Try this statement: > > > > select * from Brand > > group by BrandDescription > > having count(*) > 1; > > > > If this returns any rows, you have duplicates. > > > > Igor Tandetnik > > > > > > > > ___ > > sqlite-users mailing list > > sqlite-users@sqlite.org > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > > > > -- > View this message in context: > http://www.nabble.com/nVARCHAR-as-unique-index-tp17206197p17223665.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
