Re: [sqlite] Index on expressions
Use a trigger to populate your index column. http://www.sqlite.org/lang_createtrigger.html On Wed, Sep 3, 2014 at 9:01 AM, Dominique Devienne ddevie...@gmail.com wrote: On Wed, Sep 3, 2014 at 2:52 PM, Richard Hipp d...@sqlite.org wrote: SQLite does not (yet) support indexes on expressions. This begs the question: Are there plans, possibly ongoing, to add this support? Any timeframe? Asked differently, if adding this support, could this be done by adding virtual / computed columns to tables, and indexing those columns? You can emulate virtual columns with views of course, but then you have a table and view, necessarily named differently, and of course you cannot index views, while some DBMS allow indexing virtual / computed columns. --DD Fantasy SQL: create table t (id number primary key, c1, c2, ... ); alter table t add column c99 as (c1 + c2); create index idx_t_c99 on t.c99; ___ 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] Improving Bulk Insert Speed (C/C++)
Neither of these are your problem, but I noticed the following in your posted code: PRAGMA auto_vacuum=NONE; has no affect after your tables are created. You should move this setting earlier in your code. http://www.sqlite.org/pragma.html#pragma_auto_vacuum PRAGMA count_changes=OFF is deprecated and shouldn't be used. http://www.sqlite.org/pragma.html#pragma_count_changes On Thu, Apr 3, 2014 at 5:41 AM, Simon Slavin slav...@bigfraud.org wrote: On 3 Apr 2014, at 3:29am, Kevin Xu accol...@gmail.com wrote: I have not discovered how to find internal memory throughput usage in OSX, and I agree that something is not allowing the system from maxing out the CPU or I/O. A single application can max a single core of a single CPU if it tries hard. So if your bottleneck is CPU then Activity Monitor will show a section running at 98% or above. If that's not happening, then something else is your bottleneck -- probably either main memory or main storage. 'nice' will probably not help unless you are knowingly running other applications which hog CPU time. On modern Macs the OS tends to use one core and leave the other cores free for apps. If you can't find memory throughput out using the OS X 10.9 version of Activity Monitor, it's probably only possible to find it out using profiling tools and a debugger. I don't see a way to find out memory throughput using Activity Monitor. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Improving Bulk Insert Speed (C/C++)
What is your page size? Make sure it matches the sector size of your device. SSDs can be picky about write sizes. As an experiment, you might try using larger page sizes that are multiples of the sector size. Try to reduce the size of the records you are writing. Ie. can you map any string data to an integer enumeration?Perhaps you can normalize some of the columns to de-duplicate the amount of data stored. Can you compress your blob data? In general, the less bytes you have to write the better. If possible, make sure your records fit nicely into your page size as this should make writes more efficient. The sqlite-analyzer might give you some clues about the shape of your database. http://www.sqlite.org/download.html Do you have a non-integer column defined as a PRIMARY KEY? Perhaps the WITHOUT ROWID optimization will help. http://www.sqlite.org/withoutrowid.html You mentioned that this work might get moved to a computing cluster. As a caution, your tuning for your mac laptop/ssd combination might make little difference (or even make it worse) on a machine with different cpu/memory/io characteristics. Ie. you might be severely CPU bound there. Are you really going to be doing bulk loads like this all the time? Usually data is loaded once, then read many times. If not, perhaps you should be focused on improving your query performance. HTH. -Shane ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Improving Bulk Insert Speed (C/C++)
I had a few suggestions and questions earlier in this thread that I don't think have been responded to. And yes, without seeing the source it will be difficult to make more suggestions. On Apr 2, 2014 4:57 PM, Kevin Xu accol...@gmail.com wrote: The app seems to use between 60-80% CPU while it is running (from Activity Monitor) while disk use (using sudo iotop -P on OSX) seem to suggest about 20%-30% I/O use. (spikes to over 30million% every 10s or so, might be when sqlite decides to page out) Clearly my computations on the data are not complex enough to max out the processor (the bulk inserts, at least, is single threaded) while iotop results suggests I/O isn't maxed out either, which is why I suspect performance improvements are still possible. On the other hand, it is difficult for anyone to suggest more ideas without seeing the actual source code (professor wants it private for now), so I will try coding up the virtual table, and pure binary file dump (as a baseline) to see if I discover any more issues. Kevin Xu On Apr 2, 2014, at 1:28 PM, Keith Medcalf kmedc...@dessus.com wrote: On Tue, 1 Apr 2014 20:58:14 -0700 Kevin Xu accol...@gmail.com wrote: I am trying to see if it is possible to achieve even higher throughput - my professor was adamant that he managed to insert 3 million rows in 7 seconds (which works out to over 420K inserts per second) though he could not find his code that did it or records of his insertions. When I profiled the application (using Instruments), and after inverting the call tree, the time spent within the program is broken down as follows: 2170ms 15.1% - sqlite3VdbeExec - sqlite3_step - insert function 2142ms 14.9% - pwrite - unixWrite - pager_write/pager_write_pagelist 1925ms 14.9% - std::string::insert - boost::spirit 539ms3.7% - pack (my compression function) A question that nobody seems to have bothered to ask, and that will entirely direct your solution search: Is the limit I/O or CPU? That is, which has hit 100% usage, CPU or I/O? If is is I/O then you might want to look for consumers of I/O. The best way to make I/O go faster is not to do it. If the limit is CPU, then you need to devise a way to consume less CPU (or get a faster CPU) or to get more processors and parallelize your processing. Secondly, what is the headroom you have available? For example if you are using 100% CPU and 97% I/O, then you are damn close to balanced and the additional couple of K per second you can gain in I/O is unlikely to make any significant difference, and will likely end up being the bottleneck even if you spend a couple of million dollars on CPU and MEMORY (to drive CPU usage down to 0.0001% but peg I/O to 100%). If the problem is CPU, then the first place to attack is boost:spirit (or use multiprocessing) -- after checking to ensure that you I/O system is properly offloaded from the main CPU and not consuming spin cycles while doing I/O, of course. If neither I/O nor CPU is pegged at 100% then you have a crappy scheduler or simply insufficient overlap between I/O and compute, and you need to fix this first. ___ 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
[sqlite] sqlite3_close() documentation inconsistency
Documentation for sqlite3_close() on http://www.sqlite.org/c3ref/close.html has two conflicting statements: If the database connection is associated with unfinalized prepared statements or unfinished sqlite3_backup objects then sqlite3_close() will leave the database connection open and return SQLITE_BUSY. and If sqlite3_close() is called on a database connection that still has outstanding prepared statements, BLOB handles, and/or sqlite3_backup objects then it returns SQLITE_OK but... ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite3.dll for Win 64
The TK_* identifiers are all defined in parse.h which is generated by lemon.exe from parse.y. parse.h is included in the amalgamation file, sqlite3.c. Try deleting your parse.c and parse.h and sqlite3.c and re-running your make. Check that parse.h was generated correctly and subsequently concatenated into sqlite3.c correctly. HTH. -Shane On Mon, Jul 25, 2011 at 3:49 PM, Everton Vieira tonvie...@gmail.com wrote: So, now i'm stock on this errors: sqlite3.c sqlite3.c(40972) : warning C4244: '=' : conversion from '__int64' to 'int', poss ible loss of data sqlite3.c(57259) : error C2065: 'TK_REGISTER' : undeclared identifier sqlite3.c(57266) : error C2065: 'TK_UMINUS' : undeclared identifier sqlite3.c(57266) : error C2065: 'TK_INTEGER' : undeclared identifier sqlite3.c(57266) : error C2065: 'TK_FLOAT' : undeclared identifier sqlite3.c(57273) : error C2065: 'TK_STRING' : undeclared identifier sqlite3.c(57273) : error C2065: 'TK_FLOAT' : undeclared identifier sqlite3.c(57273) : error C2065: 'TK_INTEGER' : undeclared identifier sqlite3.c(57282) : error C2065: 'TK_FLOAT' : undeclared identifier sqlite3.c(57284) : error C2065: 'TK_INTEGER' : undeclared identifier sqlite3.c(57284) : error C2065: 'TK_FLOAT' : undeclared identifier sqlite3.c(57293) : error C2065: 'TK_UMINUS' : undeclared identifier sqlite3.c(57307) : error C2065: 'TK_NULL' : undeclared identifier sqlite3.c(57312) : error C2065: 'TK_BLOB' : undeclared identifier sqlite3.c(61966) : error C2065: 'TK_VARIABLE' : undeclared identifier sqlite3.c(63498) : error C2196: case value '0' already used sqlite3.c(63752) : error C2196: case value '0' already used sqlite3.c(63822) : error C2196: case value '0' already used sqlite3.c(63823) : error C2196: case value '0' already used sqlite3.c(63824) : error C2196: case value '0' already used sqlite3.c(63825) : error C2196: case value '0' already used sqlite3.c(63826) : error C2196: case value '0' already used sqlite3.c(63847) : error C2196: case value '0' already used sqlite3.c(63848) : error C2196: case value '0' already used sqlite3.c(63849) : error C2196: case value '0' already used sqlite3.c(63870) : error C2196: case value '0' already used sqlite3.c(63871) : error C2196: case value '0' already used sqlite3.c(63872) : error C2196: case value '0' already used sqlite3.c(64070) : error C2196: case value '0' already used sqlite3.c(64071) : error C2196: case value '0' already used sqlite3.c(64072) : error C2196: case value '0' already used sqlite3.c(64073) : error C2196: case value '0' already used sqlite3.c(64191) : error C2196: case value '0' already used sqlite3.c(64214) : error C2196: case value '0' already used sqlite3.c(64238) : error C2196: case value '0' already used sqlite3.c(64254) : error C2196: case value '0' already used sqlite3.c(64272) : error C2196: case value '0' already used sqlite3.c(64354) : error C2196: case value '0' already used sqlite3.c(64355) : error C2196: case value '0' already used sqlite3.c(64356) : error C2196: case value '0' already used sqlite3.c(64357) : error C2196: case value '0' already used sqlite3.c(64358) : error C2196: case value '0' already used sqlite3.c(64359) : error C2196: case value '0' already used sqlite3.c(64410) : error C2196: case value '0' already used sqlite3.c(64411) : error C2196: case value '0' already used sqlite3.c(64412) : error C2196: case value '0' already used sqlite3.c(64413) : error C2196: case value '0' already used sqlite3.c(64546) : error C2196: case value '0' already used sqlite3.c(64547) : error C2196: case value '0' already used sqlite3.c(64588) : error C2196: case value '0' already used sqlite3.c(64605) : error C2196: case value '0' already used sqlite3.c(64654) : error C2196: case value '0' already used sqlite3.c(64666) : error C2196: case value '0' already used sqlite3.c(69909) : error C2065: 'TK_COLUMN' : undeclared identifier sqlite3.c(69911) : error C2065: 'TK_AS' : undeclared identifier sqlite3.c(70079) : error C2065: 'TK_DELETE' : undeclared identifier sqlite3.c(70082) : error C2065: 'TK_INSERT' : undeclared identifier sqlite3.c(70186) : error C2065: 'TK_STRING' : undeclared identifier sqlite3.c(70231) : error C2065: 'TK_TRIGGER' : undeclared identifier sqlite3.c(70231) : error C2065: 'TK_COLUMN' : undeclared identifier sqlite3.c(70255) : error C2065: 'TK_COLUMN' : undeclared identifier sqlite3.c(70327) : error C2065: 'TK_ID' : undeclared identifier sqlite3.c(70327) : error C2051: case expression not constant sqlite3.c(70334) : error C2065: 'TK_DOT' : undeclared identifier sqlite3.c(70334) : error C2051: case expression not constant sqlite3.c(70342) : error C2065: 'TK_ID' : undeclared identifier sqlite3.c(70357) : error C2065: 'TK_CONST_FUNC' : undeclared identifier sqlite3.c(70357) : error C2051: case expression not constant sqlite3.c(70358) : error C2065: 'TK_FUNCTION' : undeclared identifier sqlite3.c(70358) : error C2051: case expression not constant
Re: [sqlite] SQLite3.dll for Win 64
The makefile builds lemon.exe from lemon.c as part of the build process, so make sure it was generated correctly as well. lemon -x should print a version number. lemon -? should print an error and help message. Again, HTH. -Shane On Mon, Jul 25, 2011 at 5:51 PM, Shane Harrelson shane.harrel...@gmail.comwrote: The TK_* identifiers are all defined in parse.h which is generated by lemon.exe from parse.y. parse.h is included in the amalgamation file, sqlite3.c. Try deleting your parse.c and parse.h and sqlite3.c and re-running your make. Check that parse.h was generated correctly and subsequently concatenated into sqlite3.c correctly. HTH. -Shane On Mon, Jul 25, 2011 at 3:49 PM, Everton Vieira tonvie...@gmail.comwrote: So, now i'm stock on this errors: sqlite3.c sqlite3.c(40972) : warning C4244: '=' : conversion from '__int64' to 'int', poss ible loss of data sqlite3.c(57259) : error C2065: 'TK_REGISTER' : undeclared identifier sqlite3.c(57266) : error C2065: 'TK_UMINUS' : undeclared identifier sqlite3.c(57266) : error C2065: 'TK_INTEGER' : undeclared identifier sqlite3.c(57266) : error C2065: 'TK_FLOAT' : undeclared identifier sqlite3.c(57273) : error C2065: 'TK_STRING' : undeclared identifier sqlite3.c(57273) : error C2065: 'TK_FLOAT' : undeclared identifier sqlite3.c(57273) : error C2065: 'TK_INTEGER' : undeclared identifier sqlite3.c(57282) : error C2065: 'TK_FLOAT' : undeclared identifier sqlite3.c(57284) : error C2065: 'TK_INTEGER' : undeclared identifier sqlite3.c(57284) : error C2065: 'TK_FLOAT' : undeclared identifier sqlite3.c(57293) : error C2065: 'TK_UMINUS' : undeclared identifier sqlite3.c(57307) : error C2065: 'TK_NULL' : undeclared identifier sqlite3.c(57312) : error C2065: 'TK_BLOB' : undeclared identifier sqlite3.c(61966) : error C2065: 'TK_VARIABLE' : undeclared identifier sqlite3.c(63498) : error C2196: case value '0' already used sqlite3.c(63752) : error C2196: case value '0' already used sqlite3.c(63822) : error C2196: case value '0' already used sqlite3.c(63823) : error C2196: case value '0' already used sqlite3.c(63824) : error C2196: case value '0' already used sqlite3.c(63825) : error C2196: case value '0' already used sqlite3.c(63826) : error C2196: case value '0' already used sqlite3.c(63847) : error C2196: case value '0' already used sqlite3.c(63848) : error C2196: case value '0' already used sqlite3.c(63849) : error C2196: case value '0' already used sqlite3.c(63870) : error C2196: case value '0' already used sqlite3.c(63871) : error C2196: case value '0' already used sqlite3.c(63872) : error C2196: case value '0' already used sqlite3.c(64070) : error C2196: case value '0' already used sqlite3.c(64071) : error C2196: case value '0' already used sqlite3.c(64072) : error C2196: case value '0' already used sqlite3.c(64073) : error C2196: case value '0' already used sqlite3.c(64191) : error C2196: case value '0' already used sqlite3.c(64214) : error C2196: case value '0' already used sqlite3.c(64238) : error C2196: case value '0' already used sqlite3.c(64254) : error C2196: case value '0' already used sqlite3.c(64272) : error C2196: case value '0' already used sqlite3.c(64354) : error C2196: case value '0' already used sqlite3.c(64355) : error C2196: case value '0' already used sqlite3.c(64356) : error C2196: case value '0' already used sqlite3.c(64357) : error C2196: case value '0' already used sqlite3.c(64358) : error C2196: case value '0' already used sqlite3.c(64359) : error C2196: case value '0' already used sqlite3.c(64410) : error C2196: case value '0' already used sqlite3.c(64411) : error C2196: case value '0' already used sqlite3.c(64412) : error C2196: case value '0' already used sqlite3.c(64413) : error C2196: case value '0' already used sqlite3.c(64546) : error C2196: case value '0' already used sqlite3.c(64547) : error C2196: case value '0' already used sqlite3.c(64588) : error C2196: case value '0' already used sqlite3.c(64605) : error C2196: case value '0' already used sqlite3.c(64654) : error C2196: case value '0' already used sqlite3.c(64666) : error C2196: case value '0' already used sqlite3.c(69909) : error C2065: 'TK_COLUMN' : undeclared identifier sqlite3.c(69911) : error C2065: 'TK_AS' : undeclared identifier sqlite3.c(70079) : error C2065: 'TK_DELETE' : undeclared identifier sqlite3.c(70082) : error C2065: 'TK_INSERT' : undeclared identifier sqlite3.c(70186) : error C2065: 'TK_STRING' : undeclared identifier sqlite3.c(70231) : error C2065: 'TK_TRIGGER' : undeclared identifier sqlite3.c(70231) : error C2065: 'TK_COLUMN' : undeclared identifier sqlite3.c(70255) : error C2065: 'TK_COLUMN' : undeclared identifier sqlite3.c(70327) : error C2065: 'TK_ID' : undeclared identifier sqlite3.c(70327) : error C2051: case expression not constant sqlite3.c(70334) : error C2065: 'TK_DOT' : undeclared identifier sqlite3.c(70334) : error C2051: case expression not constant sqlite3.c
Re: [sqlite] Support for .Net CE 3.5
You'll have to go back to one of the legacy versions from http://sqlite.phxsoftware.com . Compact framework support was one of the features that had to be temporarily dropped when maintenance was moved to http://www.sqlite.org . Restoring compact framework is currently one of our top priorities. On Wed, Jun 1, 2011 at 1:08 PM, Jeff Hoffman j...@starlitsoftware.comwrote: I am programming for Windows Mobile 6.5.3 and would like to use SQLite. I see this message on the Features page: Supports the Full and Compact .NET Framework, and native C/C++ development. 100% binary compatible with the original sqlite3.dll. Compact framework not currently not included. We hope to have this feature included again soon. Can anyone tell me the latest version of System.Data.SQLite that supports .Net CE 3.5? Any other advice would be welcome too! Thanks, Jeff ___ 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] .NET4 is available?
The latest versions are available at http://system.data.sqlite.org On Thu, May 26, 2011 at 11:54 AM, Massimo Savazzi massimo.sava...@gmail.com wrote: I'm using the old: 1.0.66.1 .NET4 http://sqlite.phxsoftware.com/tags/.NET+4/default.aspx Do we have an updated version? = STRICTLY PERSONAL AND CONFIDENTIAL This message may contain confidential and proprietary material for the sole use of the intended recipient. Any review or distribution by others is strictly prohibited. If you are not the intended recipient please contact the sender and delete all copies. Le informazioni contenute in questa comunicazione sono riservate e destinate esclusivamente alla/e persona/e o all'ente/i sopra indicati. E' vietato ai soggetti diversi dai destinatari qualsiasi uso, copia, diffusione di quanto in essa contenutaai sensi della Legge 196/03. Se questa comunicazione Vi e' pervenuta per errore, Vi preghiamo di rispondere a questa mail per segnalare l'errore e successivamente di cancellarla dal Vostro sistema = ___ 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] Discussing and contributing to new System.Data.SQLite project
Hi- The SDS project is currently being maintained by the same people who maintain SQLite. We're currently also using the same mailing list (this one, sqlite-users) for discussions of SDS. Contributions are always welcome, but please note we will need a contributor agreement on file: http://system.data.sqlite.org/index.html/doc/trunk/www/contribute.wiki Current priorities going forward include restoring compact framework and design time support. Thanks for your interest in helping! -Shane On Fri, May 27, 2011 at 9:50 AM, Samuel Neff srneff.li...@gmail.com wrote: Who is maintaining the new System.Data.SQLite project at http://system.data.sqlite.org? Is there a separate mailing list for that project or should questions on that project be sent to the sqlite-users this mailing list? My company is interested in knowing what the plans are going forward and most specifically how can we contribute. We have a patch that exposes the native backup API via the .NET wrapper which we'd like to contribute. We also are planning on writing a new connection pool implementation that is not dependent on the garbage collector and is compatible with WAL mode. Thanks, Sam ___ 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] Unable to load dll SQLite.Interop.dll
The setup packages available from here: http://system.data.sqlite.org/index.html/doc/trunk/www/downloads.wiki are suppose to check if the MSVC runtimes are installed or not and install them. It's the main reason the packages are as large as they are. How did you install the SDS DLLs? Thanks. -Shane On Thu, May 19, 2011 at 11:24 PM, Anderson Laécio G. Trindade laec...@yahoo.com.br wrote: Ops... I was dealing with [Unable to load dll SQLite.Interop.dll] error when I was running my software on other computers. Finally I figured out that the dependency msvcr100.dll was missing on those computers. This can be helpful for other users. Regards, Anderson Laécio Galindo Trindade ___ 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] Interop DLL question
Switching to a single format greatly simplified things from an administrative point of view - giving us a consistent single packaging format across all platforms (Windows, Mono, Compact, ARM, etc.) - a support customer even specifically pointed out this made things much easier for them as well. However, cases like yours, and cases where inexperienced users didn't catch some of the subtleties of the packaging change, are causing us to rethink this. We're currently discussing whether to revert back to offering a mixed-mode DLL in addition to the current packages. Thanks for you input. -Shane On Fri, May 20, 2011 at 12:10 AM, Michael Yeaney michael.yea...@gmail.comwrote: For the 1.0.72.0 (3.7.6+) package (x64), is there a stand-alone (mixed mode) DLL offered anymore that does not require the interop library (much like the 1.0.66.0 version? I'm using SQLite successfully in a few Microsoft Azure deployments, but with the new version, the servers are not able to locate the SQLite.Interop.dll library, as it is not in any search paths on the cloud machines. The goal here is simple xcopy deployment (in other words _not_ requiring GAC registration). Ideas? I'm assuming I'm missing something...but I just can't find it. Many thanks! ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] System.Data.SQLite Status
On Fri, May 13, 2011 at 3:24 AM, Clay Fowler paul.clay.fow...@gmail.comwrote: The downloads at http://system.data.sqlite.org/index.html/doc/trunk/www/downloads.wikiappear broken in various ways: The precompiled binaries fail to work on desktop from Mono on OS X or on Microsoft's CLR from Windows (even with the included test.exe) because they I've tested the setup on multiple versions of Windows. If you're having problems please try this: Run the appropriate setup for your Version of Windows from the downloads page and install to a temp directory like C:\temp\SDS - this is so it is writable without having to have administration privileges. Choose all the default options for installation. Run the test utility - C:\temp\SDS\bin\test.exe. Does it work? What errors are reported? attempt to load SQLite.Interop.dll. According to the bundled readme.html, this should not happen (it says SQLite.Interop.dll is only needed when deploying on the compact framework and that normally only System.Data.SQLite.dll itself should be deployed with desktop apps). But even with the SQLite.Interop.dll present, it still fails to load. The bundled readme, as well as the features page, have an update in red, describing the decision we made to ship the SDS DLL and the Interop DLL separately for all platforms: Currently all versions provided as System.Data.SQLite.dll and SQLite.Interop.dll. This provides consistency across all packages, including Compact and Mono. This was a very difficult decision to make, but from a support perspective, what we felt was the best choice. Meanwhile, the source download is broken because core projects within the solution are completely empty (no source files), such as the main System.Data.SQLite assembly. This is true for both the 2008 and 2010 solutions in the .zip. The solution files appear empty because they both (2008 and 2010) reference common target files that specify shared build information. See System.Data.SQLite.Files.targets for example. You should have been able to open the SQLite.NET.2010.sln solution file and hit rebuild solution. (Depending on your VS setup, you may need update the references for the designer to build, but it's not needed to build and use the interop or SDS DLLs or the test executable.) So it doesn't appear possible to get anything that works either from binary or source at the moment, or I am overlooking something. ___ 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] Building managed only System.Data.SQLite
Remember that System.Data.SQLite.dll needs to be able to find the SQLite.Interop.dll. There's a post-build step that attempts to copy the Interop DLL to the appropriate debug directory. You may still need to copy into your debug directory for your test purposes. On Wed, Apr 20, 2011 at 3:04 PM, Rich Rattanni ratta...@gmail.com wrote: Shane: I downloaded http://system.data.sqlite.org/sqlite-dotnetsrc-1006900.zip and changed SQLite.NET.Settings.targets as per your recommendation. It did not appear to solve my problem since my application threw an exception stating that Sqlite.Interop was missing. To be complete I tried 3 permutations of the two options UseInteropDll and UseSqliteStandard ( I did not try false, false). All resulting System.Data.SQLite.DLLs threw exceptions due to a missing InterOp DLL. I pulled down a copy of System.Data.SQLite 1.0.66.0 and built the Managed Only version, and it appeared to work. I will mess around some more with the project to see if I can build the managed only version. I was hoping to get my hands on the latest so I was not fighting issues that may have been fixed by a release. Thank you again for trying to help, rest assured it was appreciated. -- Rich On Tue, Apr 19, 2011 at 5:36 PM, Rich Rattanni ratta...@gmail.com wrote: Daniel: I have not tried Csharp-sqlite, it looks interesting but I do not know if that is right for me at this moment. Shane: I will try what you recommend tomorrow, thank you. -- Rich On Tue, Apr 19, 2011 at 4:47 PM, Shane Harrelson shane.harrel...@gmail.com wrote: The target build settings can be controlled from SQLite.NET.Settings.targets - in particular, you should probably look at UseInteropDll and UseSqliteStandard. To override the USE_INTEROP_DLL setting, try copying SQLite.NET.Settings.targets to SQLite.NET.Settings.targets.user and make the settings changes there. This should work with VS2008 and VS2010. HTH. -Shane On Tue, Apr 19, 2011 at 3:09 PM, Rich Rattanni ratta...@gmail.com wrote: I was wondering if anyone has had any luck building the Managed-Only System.Data.SQLite .NET adapter for SQLite from the source provided at system.data.sqlite.org? I downloaded the pre-built binaries but they appear to rely on the InterOp assembly. My current project is running under Linux 2.6 on an ARM processor, and uses managed-only copy of the System.Data.SQLite adapter + SQLite 3.5.0 + mono to run my .NET app. I am looking to do some bug tracing / upgrading so I would like to build my own copy from source. -- Rich ___ 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 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Building managed only System.Data.SQLite
The target build settings can be controlled from SQLite.NET.Settings.targets - in particular, you should probably look at UseInteropDll and UseSqliteStandard. To override the USE_INTEROP_DLL setting, try copying SQLite.NET.Settings.targets to SQLite.NET.Settings.targets.user and make the settings changes there. This should work with VS2008 and VS2010. HTH. -Shane On Tue, Apr 19, 2011 at 3:09 PM, Rich Rattanni ratta...@gmail.com wrote: I was wondering if anyone has had any luck building the Managed-Only System.Data.SQLite .NET adapter for SQLite from the source provided at system.data.sqlite.org? I downloaded the pre-built binaries but they appear to rely on the InterOp assembly. My current project is running under Linux 2.6 on an ARM processor, and uses managed-only copy of the System.Data.SQLite adapter + SQLite 3.5.0 + mono to run my .NET app. I am looking to do some bug tracing / upgrading so I would like to build my own copy from source. -- Rich ___ 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] BUG: mutex_w32.c; diagnostic message needs to be clearer
I updated the debug statement here: http://www.sqlite.org/src/info/def98fd23e Thanks for the report. -Shane On Mon, Mar 14, 2011 at 7:54 PM, Noah Hart n...@lipmantpa.com wrote: In the routine winMutexTry at line 284 - printf(enter mutex %p (%d) with nRef=%d\n, p, p-trace, p-nRef); + printf(try mutex %p (%d) with nRef=%d\n, p, p-trace, p-nRef); -- View this message in context: http://old.nabble.com/BUG%3A-mutex_w32.c--diagnostic-message-needs-to-be-clearer-tp31149931p31149931.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Source code for system.data.sqlite
Thanks for the feedback. We're currently trying to work out a Contributor License Agreement for System.Data.SQLite - probably something along the lines of http://www.fossil-scm.org/fossil/doc/trunk/www/copyright-release.html used for Fossil. You need to login to the Fossil site, even if only as Anonymous, to be able to add a ticket. -Shane On Tue, Mar 1, 2011 at 8:00 PM, Paul Shaffer sqli...@cyberplasm.com wrote: Since sqlite has taken on system.data.sqlite for .net you will be seeing issues on this until a forum is set up for it. Please try to make sure the entire source tips are available for download. The last time I checked 1.0.68 (or ?) did not compile due to missing linq related files. Hopefully there would be some way to contribute source changes to the project in future, there is no way now. There is also no way to add on to issues in the tracker, and no way to distinguish contributors to issues. I can't get into the dev forum or I would post this there. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Problem with VACUUM feature
Hi-
On Windows, SQLite uses the FILE_FLAG_DELETE_ON_CLOSE flag to have
temporary files automatically deleted after they are closed. WINCE
doesn't support this flag, so you will see special logic in os_win.c,
wrapped in #ifdef SQLITE_OS_WINCE, for handling the deletion of these
files. You mentioned in an earlier post that you had ported to your
platform based on this code. Could you check that your ported code
includes this logic?
-Shane
On Wed, Feb 23, 2011 at 9:00 AM, Sudha Venkatareddy sudha@gmail.com wrote:
Hi,
I referred the link http://www.sqlite.org/cvstrac/tktview?tn=2829
it is slightly related to it but the temporary files are created while
running VACUUM command.
---
Ticket 2829:
This patch seems to fix it (added: SQLITE_OPEN_DELETEONCLOSE):
if( flags (SQLITE_OPEN_TEMP_DB | SQLITE_OPEN_TEMP_JOURNAL
- | SQLITE_OPEN_SUBJOURNAL) ){
+ | SQLITE_OPEN_SUBJOURNAL | SQLITE_OPEN_DELETEONCLOSE) ){
--
The temp files were created in the below call sequence:
-
62 otherOsOpen() sqlite3.c:123900 0x3afe25bd
61 sqlite3OsOpen() sqlite3.c:15280 0x3af550d0
60 pagerOpentemp() sqlite3.c:39431 0x3af62e70
59 pager_write_pagelist() sqlite3.c:40030 0x3af63a68
58 sqlite3PagerCommitPhaseOne() sqlite3.c:41884 0x3af669ff
57 sqlite3BtreeCommitPhaseOne() sqlite3.c:48993 0x3af72665
56 sqlite3BtreeCommit() sqlite3.c:49083 0x3af728e6
55 sqlite3RunVacuum() sqlite3.c:94735 0x3afcce29
54 sqlite3VdbeExec() sqlite3.c:66384 0x3af961e4
53 sqlite3Step() sqlite3.c:59380 0x3af87b34
52 sqlite3_step() sqlite3.c:59444 0x3af87d6e
51 sqlite3_exec() sqlite3.c:84701 0x3afb86b9
--
Basically there 2 problems associated when i run VACUUM command.
Problem 1. Running VACUUM leaves 3 temporary files in the temp directory
which are not deleted when main DB is closed.
Problem 2. Upon applying VACUUM command on say main DB file MyDb.db , and
closing the main DB connection, the size of the main DB file MyDb.db does
not change where as one of the temp file(etilqs_*) will actually contain the
reduced size of the same data as of main DB file.
I am not sure if this is the expected behaviour or there is some bug in the
flow.
Please let me know if there is a solution to resolve this issue.
Thanks,
Sudha
On Wed, Feb 23, 2011 at 5:52 PM, Simon Slavin slav...@bigfraud.org wrote:
On 23 Feb 2011, at 6:11am, Sudha Venkatareddy wrote:
*Actual output: MyDb.db remains size 23KB(size not changes from original)
and creates temporary file etilqs_Hm4RUi6JPXcMZ17 whose data is same as
MyDb.db but the size is reduced to 13KB*
Your problem is probably related to
http://www.sqlite.org/cvstrac/tktview?tn=2829
. It's quite legitimate for your symptoms to occur while the database
handle is still open but you should not be seeing those files after you have
closed the connection to the database. Either you are not closing the
database connection properly, or some part of the API you're using is not
closing the database connection properly.
I'm not familiar with how this problem manifests because I don't use
Windows, so I'll leave it up to an expert to tell you if it needs fixing
somehow.
Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] UPDATE/INSERTing 1-2k rows slower than expected
On Mon, Feb 14, 2011 at 6:04 PM, Simon Slavin slav...@bigfraud.org wrote: If the data is backed up when SQLite has the files closed, things are fine. The really hard part of this comes when you're working with systems that must be live at all times. Which is why you'll never see a bank use SQLite to maintain its live transaction system. The big DBMSs have systems inside themselves that allow the backing-up of an active ever-changing database. Simon. SQLite is used as the DB engine on some major 24/7 data stores (I can't be more specific due to NDAs). You can get an idea of some of the high demand areas SQLite is used in by reviewing the consortium membership list (http://www.sqlite.org) and well-known users list (http://www.sqlite.org/famous.html), though this should not be seen as an endorsement by any of the listed companies. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] BUG: test script exclusive2.test needs do_not_use_codec
Thanks. I think I got all the changes. On Mon, Feb 14, 2011 at 7:47 PM, Noah Hart n...@lipmantpa.com wrote: exclusive2.test reads directly from the database using binary read It needs the following changes: -source $testdir/tester.tcl +source $testdir/tester.tcl + +# Do not use a codec for tests in this file, as the database file is +# manipulated directly using tcl scripts (using binary read) +# +do_not_use_codec ~Noah Hart -- View this message in context: http://old.nabble.com/BUG%3A-test-script-exclusive2.test-needs-do_not_use_codec-tp30927245p30927245.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] BUG: capi3e.test when compiled with SQLITE_OMIT_UTF16
Thanks.Hopefully corrected here: http://www.sqlite.org/src/info/b04304b967 -Shane On Fri, Feb 11, 2011 at 4:59 PM, Noah Hart n...@lipmantpa.com wrote: capi3e.test needs ifcapable utf16 logic before capi3e-2.1.$i to properly pass tests when compiled with SQLITE_OMIT_UTF16 ~ Noah Hart -- View this message in context: http://old.nabble.com/BUG%3A-capi3e.test-when-compiled-with-SQLITE_OMIT_UTF16-tp30905474p30905474.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Outer query returning results not found in subquery
On Thu, Feb 10, 2011 at 2:29 PM, Igor Tandetnik itandet...@mvps.org wrote:
On 2/10/2011 2:17 PM, Dan Kubb wrote:
Database setup:
CREATE TABLE test (letter VARCHAR(1) PRIMARY KEY, number INTEGER
NOT NULL);
INSERT INTO test (letter, number) VALUES('b', 1);
INSERT INTO test (letter, number) VALUES('a', 2);
INSERT INTO test (letter, number) VALUES('c', 2);
Initial query:
SELECT letter, number FROM test ORDER BY letter, number LIMIT
1;
This returns a|2, the second row from the results as you would
expect given that we're sorting on the letter then the number.
However, here's what I did not expect:
Initial query as a subquery:
SELECT DISTINCT number FROM (SELECT letter, number FROM test
ORDER BY letter, number LIMIT 1) AS test;
This returns 1
Yes, looks like a bug. If you drop DISTINCT, it returns a single row
with the value 2.
My guess is, DISTINCT is internally implemented as ORDER BY, and that
overrules ORDER BY found in the subquery.
--
Igor Tandetnik
Thanks for the report and recreate. A ticket was opened here:
http://www.sqlite.org/src/tktview/752e1646fcc7b649184e49783577a7feb5f7fc9c
I was able to recreate the problem. Additionally, if query
optimizations are disabled, then the correct result is returned, so
this is most likely and issue with the query flattener.
-Shane
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Windows performance problems associated with malloc()
I believe the Windows default is to use the LFH on Vista and newer
versions of Windows.
The suggestion by Marcus Grimm to use _set_sbh_threshold() to enable use
of the SBH (small block heap) may help under some usage scenarios on
those platforms.
-Shane
On Fri, Dec 17, 2010 at 6:29 PM, Doug pa...@poweradmin.com wrote:
I wonder if HeapSetInformation (which can enable a low-fragmentation heap)
would be helpful too. You can set it on the process
and the CRT heaps. Note that it's not available in Win2K and earlier.
Doug
-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Marcus Grimm
Sent: Friday, December 17, 2010 9:21 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Windows performance problems associated with malloc()
An SQLite user has brought to our attention a performance issue in
SQLite that seems to be associated with malloc(). If you have
insights or corroborating experience with this issue please let me know.
We recently had a malloc/free slowdown issue after changing to VS2008 in
combination with XP.
Not sure if it applies in your case but for us this helps:
--
if( _get_sbh_threshold() 512 )
{
_set_sbh_threshold(512);
}
---
I'm unable to run your sqlite3.exe: MSVCR100.dll no found.
Anyway, maybe the above helps.
KInd regards
Marcus
SQLite supports a zero-malloc option (see
http://www.sqlite.org/malloc.html#memsys5 for details) which uses its
own internal memory allocator rather than system malloc(). Earlier
today, we patched the command-line shell to allow the zero-malloc
option to be turned on. If you do:
sqlite3 DATABASE
then the regular system memory allocator is used, but if you say:
sqlite3 -heap 100M DATABASE
then the MEMSYS5 memory allocator will be used with a pool of 100MB of
memory to work with. (You can adjust the size of your memory pool for
whatever you need.)
There are win32 and win64 builds of this updated command-line shell
compiled using vs2010 here:
http://www.sqlite.org/draft/download.html
For certain full-text search queries against a large database, we are
seeing speeds which are 3x faster when using -heap 300M (the memsys5
memory
allocator) versus omitting the -heap option and thus using system
malloc().
This is on windows7. Similar results are seen with both gcc and
vs2010 builds.
If you have any large queries that you can run on windows using the
command-line shell, I would appreciate you timing those queries using
the new shells from the download page, both with -heap 300M and
without it, and letting me know about any performance differences you see.
I also observe that compiling for 64-bit using vs2010 (not an option
with my ancient version 2.95.3 gcc cross-compiler) that the queries
are an additional 2x faster. I was surprised at the dramatic
performance increase in going from 32-bit to 64-bit. Is such a
speed-up typical?
The use of -heap 300M seems to not make any performance difference
on Linux.
Any insights into why this is, what we are doing wrong, or what we can
do to improve the performance of malloc() on windows will be
appreciated.
--
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
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] WAL locking problem on Windows CE
On Fri, Dec 10, 2010 at 9:06 AM, Richard Hipp d...@sqlite.org wrote: On Fri, Dec 10, 2010 at 4:02 AM, Nicklas Larsson nirre.lars...@gmail.comwrote: Hi, we have been running SQLite (3.6.21) successfully on Windows CE for a while. When upgrading to version 3.7.3 our intent was to enable WAL. However, we ran into problems related to the locking mechanism. In method winShmSystemLock calls are made to LockFileEx with parameters that will make 'winceLockFileEx' always return FALSE. Does anyone know if WAL is expected to work on Windows CE? I can't see how if that is the case. I don't think shared memory works on winCE. Shane can answer that definitively when he gets online today. But with SQLite 3.7.4, there is a work-around. Set PRAGMA locking_mode=EXCLUSIVE prior to engaging PRAGMA journal_mode=WAL and shared memory won't be used. See http://www.sqlite.org/wal.html#noshm for details. Best Regards, Nicklas! ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users The locking primitives used for WAL are unfortunately not supported on Windows CE. -Shane ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite in WinCE Emulator - Disk I/O Error and change of pragma don´t work
According to the MSDN support sites, there are a couple of known issues with the Windows Mobile Emulator, when using emulated storage cards. With an emulated storage card, SetEndOfFile() (which we use for truncating a file) and FlushFileBuffers() (which we use for syncing) will fail. They do not fail for a real storage card or for other emulated storage. These errors are handled by SQLite and reported to the calling application. Possible work arounds for you when using emulated storage cards include: -Adding the define SQLITE_NO_SYNC and using the PRAGMA journal_mode = PERSIST journaling modes -Use system storage instead of an emulated storage card These would only be needed when testing on the emulator with an emulated storage card. Regards. -Shane On Tue, Dec 7, 2010 at 2:53 PM, Horacio Rabelo Pereira horac...@bysat.com.br wrote: Hi, all I'm using SQLite version 3.7.2 ported to Windows CE, in a application developed using Lazarus I got the compiled SQLITE3.DLL for Windows CE in the site www.parmaja.com For now, I am using one emulator to run the native Windows CE application in a desktop computer running Windows 2000 SP4. That emulator is the Microsoft Device Emulator V3. Well, considering that is the first time I work in the environment, I made a little application in Lazarus that load text files to the SQLite database. The process was done well, until the moment that will close execute the command “commit”. I this moment the application crashes showing the message “Disk I/O Error”. During the execution, the journal file for database is created, and after the crash it is removed and the database file remains without any modifications. Searching in the Web, I found a note describing a workaround, that consist in change the pragma “jornal_mode” to value “truncate”. For my surprise, all my tries to change the value of pragma “journal_mode” don't work. I try change via SQL DDL script, via SQL Expert and via application. In all the cases, when I access the database after the changes, the values of pragma “journal_mode” it ever “delete”, not the value that I change. I try “truncate”, “memory”, etc, without any success. In resume, I have two problems 1)The “Disk I/O Error” when I try to commit the transaction (running in a Windows CE Emulator) 2)The change of value of pragma that don't work. Thanks in advance Horacio Pereira Belo Horizonte – MG – Brasil hora...@bysat.com.br ___ 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] Some floats of 15 digits or less do not round-trip
In Visual C, a long double and a double are the same, and only offer 53 bits of precision. On GCC, a long double has 80 bits of precision. Unfortunately, I don't think there's a way to have Visual C use more precision. Because of this, round off error will always differ between the two compilers when using the long double type. -Shane On Mon, Nov 29, 2010 at 10:06 PM, Rick Regan exploringbin...@gmail.com wrote: On Mon, Nov 29, 2010 at 3:04 PM, Rick Regan exploringbin...@gmail.comwrote: So the question remains -- why does the Windows build get it wrong? I think I figured it out. It is due to the use of extended precision. In sqlite3.c, if you change #define LONGDOUBLE_TYPE long double to #define LONGDOUBLE_TYPE double and set the FPU precision to 53 bits, you get the same wrong result as on Windows. Obviously, we don't want the same wrong result as on Windows -- so how do we fix Windows? What's the procedure for opening a bug report? ___ 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.7.3_for_Pocket_PC_2003_can't_compile
You need to compile with SQLITE_OMIT_WAL for now. On Thu, Oct 21, 2010 at 10:09 PM, 祝久文 adlind...@hotmail.com wrote: Hello, When compiling for Pocket PC 2003(VS2005),it gives out two errors as follows: .\os_win.c(1350) : error C2065: 'LOCKFILE_EXCLUSIVE_LOCK' : undeclared identifier .\os_win.c(1357) : warning C4013: 'UnlockFileEx' undefined; assuming extern returning int Would you give me some solutions,please? CN, Adlindary ___ 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] Porting SQLite 3.7.2 to vxWorks 6.7
See:
http://www.sqlite.org/src/info/d1ed743b6e
for changes for SQLITE_OMIT_WAL.
3.7.3 should be available from the downloads page:
http://www.sqlite.org/download.html
as of October 8, 2010. You might need to refresh your browser cache.
Direct link to the amalgamation is:
http://www.sqlite.org/sqlite-amalgamation-3_7_3.zip
HTH.
-Shane
On Mon, Oct 11, 2010 at 12:11 PM, Scott A Mintz sami...@ra.rockwell.com wrote:
I think there is a bug in 3.7.2 when you define SQLITE_OMIT_WAL. There
are references to pagerPagecount() that cannot be resolved because that
function is defined inside of a #ifndef SQLITE_OMIT_WAL.
I also keep seeing references to 3.7.3 in this list. But the SQLite
download page only has links for 3.7.2. Where can I download 3.7.3?
-Scott
sqlite-users-boun...@sqlite.org wrote on 10/09/2010 07:38:24 AM:
Hello Scott,
I did some searching with these symbols, they should be in
'libos.a'.
rtpLib.o - rtpVerifyAndLock
pgMgrLib.o - pgMgrPageFree, pgMgrPageAllocAt
you should modify kernel components with your VIP project.
Best Regards,
Huang ZhiHua
2010/10/9 Scott A Mintz sami...@ra.rockwell.com
Thank you. Those changes (modified slightly for 3.7.2) allowed me to
create a DKM project that compiles sqlite3.c to libSQLite3.a.
However, when I link my main VIP project, I get the following
unresolved
errors:
dld: warning: Undefined symbol 'rtpVerifyAndLock' in file
'partialImage.o'
dld: warning: Undefined symbol 'pgMgrPageFree' in file
'partialImage.o'
dld: warning: Undefined symbol 'pgMgrPageAllocAt' in file
'partialImage.o'
Those are not directly used by SQLite. But I have a feeling that one
or
more of the file I/O, semaphore, locking, or memory library system
calls
are...
-Scott
sqlite-users-boun...@sqlite.org wrote on 10/08/2010 07:38:45 AM:
Hello Scott,
Below is my patch on the latest SQLite 3.7.3. Please notice that
I
only
verify it with GCC 4.1.2 compiler in VxWorks 6.6/6.7/6.8(I have not
verify
it with my real target machine yet).
*** sqlite3.c.orig 2010-10-08 10:42:22.0 +0800
--- sqlite3.c 2010-10-08 19:24:18.390625000 +0800
***
*** 17,22
--- 17,26
** language. The code for the sqlite3 command-line shell is also
in
a
** separate file. This file contains only code for the core SQLite
library.
*/
+ #if defined(OS_VXWORKS)
+ #include vxWorks.h
+ #endif /* OS_VXWORKS */
+
#define SQLITE_CORE 1
#define SQLITE_AMALGAMATION 1
#ifndef SQLITE_PRIVATE
***
*** 22795,22801
--- 22799,22811
#include sys/stat.h
#include fcntl.h
#include unistd.h
+
+ #if defined(OS_VXWORKS) defined(_WRS_KERNEL)
+ #include sys/times.h
+ #else
#include sys/time.h
+ #endif /* OS_VXWORKS */
+
#include errno.h
#include sys/mman.h
***
*** 24945,24951
--- 24955,24965
/*
** Close a file.
*/
+ #if (OS_VXWORKS 600)
static int semClose(sqlite3_file *id) {
+ #else
+ static int semClose_native(sqlite3_file *id) {
+ #endif
if( id ){
unixFile *pFile = (unixFile*)id;
semUnlock(id, NO_LOCK);
***
*** 25581,25587
--- 25595,25607
}
return -1;
}
+
+ #if defined(OS_VXWORKS) defined(_WRS_KERNEL)
+ got = write(id-h, (char *)pBuf, cnt);
+ #else
got = write(id-h, pBuf, cnt);
+ #endif /* OS_VXWORKS */
+
#endif
TIMER_END;
if( got0 ){
***
*** 26762,26768
--- 26782,26792
semIoFinder, /* Finder function name */
semIoMethods, /* sqlite3_io_methods object name */
1, /* shared memory is disabled */
+ #if (OS_VXWORKS 600)
semClose, /* xClose method */
+ #else
+ semClose_native, /* xClose method */
+ #endif
semLock, /* xLock method */
semUnlock, /* xUnlock method */
semCheckReservedLock /* xCheckReservedLock method */
***
*** 27517,27523
noLock = eType!=SQLITE_OPEN_MAIN_DB;
! #if defined(__APPLE__) || SQLITE_ENABLE_LOCKING_STYLE
struct statfs fsInfo;
if( fstatfs(fd, fsInfo) == -1 ){
((unixFile*)pFile)-lastErrno = errno;
--- 27541,27547
noLock = eType!=SQLITE_OPEN_MAIN_DB;
! #if (defined(__APPLE__) || SQLITE_ENABLE_LOCKING_STYLE)
!defined(OS_VXWORKS)
struct statfs fsInfo;
if( fstatfs(fd, fsInfo) == -1 ){
((unixFile*)pFile)-lastErrno = errno;
***
*** 27530,27536
}
#endif
! #if SQLITE_ENABLE_LOCKING_STYLE
#if SQLITE_PREFER_PROXY_LOCKING
isAutoProxy = 1;
#endif
--- 27554,27560
}
#endif
! #if
Re: [sqlite] Compiling dll 3.7.3 issue was [BUG] JOIN subquery in FROM with FTS3 table
You'll get this if your .DEF file includes any APIs that are not compiled into your build. A few sources are: SQLITE_OMIT_LOAD_EXTENSION=1 SQLITE_ENABLE_COLUMN_METADATA=1 SQLITE_ENABLE_STAT2=1 SQLITE_ENABLE_FTS3=1 SQLITE_ENABLE_RTREE=1 Depending on how you're compiling, you have a couple of options. 1) enable RTREE extensions in your build 2) remove the RTREE APIs from your .DEF file 3) don't use the .DEF (see below) All of the external SQLite APIs are prefixed with the define SQLITE_API. This is usually not defined to any value. You could try changing this to use the export declspec directive. Something like the following should work: #define SQLITE_API __declspec(dllexport) HTH. -SHane On Fri, Oct 8, 2010 at 10:03 AM, Shopsland gmail shopsl...@gmail.com wrote: Hi, Thanks Dan, Richard and Max for the (ultra) fast answer and Dan and Richard for the fix, because, as Max says, the bug is fixed now in 3.7.3. But I have a problem compiling sqlite with VC++. I was able to compile 3.7.2 without issues. With 3.7.3 The linker says: 'error LNK2001: unresolved external symbol sqlite3_rtree_geometry_callback' Keep up the good work! :-) Jochi Martinez www.bfreenews.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Breakpoints uncorrelated on MSVC amalgamation build
This limitation has been around for a while in the MS Visual debuggers... I can't find the MSDN article that discusses it, but once you exceed 64k lines, all bets are off. Work arounds include using the canonical source to build and debug, or stripping comment lines, white space etc. from the amalgamation to get below 64k. HTH. -Shane On Thu, Sep 23, 2010 at 10:54 AM, Ben Harper b...@imqs.co.za wrote: I can't confirm this behaviour on anything other than 2010. But I seem to recall the same business a few months ago, when I must have been on 2008. -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Virgilio Fornazin Sent: 23 September 2010 03:24 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] Breakpoints uncorrelated on MSVC amalgamation build Which MSVC compiler has this bug? There's a link to information on that ? On Thu, Sep 23, 2010 at 10:17, Ben Harper b...@imqs.co.za wrote: I just discovered the MSVC compiler generates bad debug info for source files larger than 64k lines, which is the case with the Sqlite amalgamation. Does anyone know of a workaround? Thanks, Ben ___ 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 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] memory wasted shm mapped file (3.7.2)
I tried to reproduce this, and could not. There are some questions inline below.Additionally, I want to verify that you've tried this with a version of SQLite containing the previously linked fix. -Shane On Fri, Sep 10, 2010 at 12:54 AM, Max Vlasov max.vla...@gmail.com wrote: But as a side effect I got not expected result in other area, when I tried to append 1,500,000 records to this 1.7G file having 5G of free space on the disk, I got the error a user reported recently about win7 64bit, Disk I/O error. (http://www.mail-archive.com/sqlite-users@sqlite.org/msg54935.html, but this seems was not related to WAL) Now I the problem is fully reproducible. A modified versions of the steps: Windows 7 64bit Home Premium, sqlite 3.7.2 in dll 1. Create db with the table CREATE TABLE [TestTable] ( [Id] INTEGER PRIMARY KEY AUTOINCREMENT, [Text] VARCHAR(200) ) Which version of SQLite are you using? What compilation options? Do you have syncs disabled? 2. Open the db that should currently be in journal_mode=delete Does the DB contain anything at this point? Or just the empty table? What page size are you using? 3. Change journal_mode=WAL; What are you using for the wal_autocheckpoint setting? 4. BEGIN TRANSACTION 4. Make 1,300,000 repeated queries INSERT INTO TestTable (Text) VALUES (12345678912345 (the exact length of the string = 1152) 5. While the queries are executed, when the shm file grows to 11M (0xAC), the failure occurs with Disk I/O error (both result and extended are 10 (SQLITE_IOERR)). There's a change that there's something wrong with my program, can someone do a similar test on another Windows 64bit system? Thanks Max ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] memory wasted shm mapped file (3.7.2)
Michele- I've looked at trying to reproduce your issue on an 32-bit Windows XP system using the latest code, and could not. Even assuming the worst case of a 512 byte page size, starting with a 1.2gb DB file, deleting all the records would result in a WAL file of roughly the same size containing 2mil+ pages. Each of the WAL pages has an 8 byte entry in the SHM file. The SHM file uses 32k regions which can each hold 4k entries. For 2mil+ entries, we would need around 500 regions. The SHM file is what is memory mapped, with each 32k region being mapped into memory. The Windows implementation uses an average 48k for each region, so at most we would use around 24mb of memory. I attempted to reproduce this by creating a 1.5gb DB, containing 17mil records, each 65 bytes long, using a page size of 512 bytes. Starting with this DB in WAL mode, I deleted all records. Maximum shared memory usage did not exceed 24mb. If you're sure you added the fix Dan indicated correctly into your build, then we're going to need more info on exactly what you're doing. What Windows version are you using specifically? What page size are you using? What SQL queries are you executing? Can you provide a short series of statements with the CLI to reproduce this? HTH. -Shane On Thu, Sep 9, 2010 at 11:36 AM, Michele Pradella michele.prade...@selea.com wrote: Hi Max, I got the problem in both situations: 1. I have a journal_mode=DELETE database and I convert it to WAL. 2. I create a new database with WAL mode. I never check the handles in the task manager, but I always see the mapped files in vmmap growing up. I think it's the same. Anyway I have the memory wasted especially when I have a quite big DB(about 1.2GB with about 17milions of records) and I try to Delete a lot of records: in this situation I see mapped files growing up and waste a lot of memory (I reached the maximum 32bit windows memory limit so my application crash). ps.With this DB the SELECT count(ID) FROM table_name it's very slow...it take minutes(with the sqlite shell)! Il 09/09/2010 17.04, Max Vlasov ha scritto: On Thu, Sep 9, 2010 at 11:37 AM, Dan Kennedydanielk1...@gmail.com wrote: On Sep 9, 2010, at 1:12 PM, Michele Pradella wrote: Hi, do you have some news about the wasted memory? have you found the reason for the windows backend? Fixed here: http://www.sqlite.org/src/ci/f213e133f6 Dan, don't know whether it is related, but I detected memory leak in 3.7.2 related to handle count increasing. Steps to reproduce (Windows XP SP3, sqlite3.7.2.dll compiled with bcc) 1. Create or use previous db with the table CREATE TABLE [TestTable] ( [Id] INTEGER PRIMARY KEY AUTOINCREMENT, [Text] VARCHAR(200) ) 2. Open the db that currently in journal_mode=delete 3. Change journal_mode=WAL; 4. BEGIN TRANSACTION 4. Make 50,000 repeated queries INSERT INTO TestTable (Text) VALUES (12345678912345 (in my case the lengh of this string was about 1100 bytes) 5. See while the queries are processing how handles in Task manager increasing (total about 14). The followiing commit does not help in decreasing the number to the start value. I tried to look with Process explorer, it seems there are many handles titled section. Also I could not reproduce this when the db is already in WAL mode when opened. Michele, can you tell us what is the mode when you initially open db? Max ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Selea s.r.l. Michele Pradella RD SELEA s.r.l. Via Aldo Moro 69 Italy - 46019 Cicognara (MN) Tel +39 0375 889091 Fax +39 0375 889080 *michele.prade...@selea.com* mailto:michele.prade...@selea.com *http://www.selea.com* ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite fails to build for WinCE 4.2 (WM 2003)
On Windows CE, you need to compile with SQLITE_OMIT_WAL. SQLite
currently doesn't support the WAL journaling mode on CE.
On Tue, Aug 24, 2010 at 6:32 AM, Aleksandr Jr. aleksand...@mail.ru wrote:
Hi.
I got a problem when compiling SQLte 3.7.2 for Windows CE 4.2 (Windows
Mobile 2003) - it cannot be done.
In the function winShmSystemLock(...) is called API-function
UnlockFileEx(...), but it is implemented only in Windows CE 5.0 and later
(and there in winShmSystemLock(...) is used constant LOCKFILE_EXCLUSIVE_LOCK
- it is not exists in WM 2003 SDK).
I suggest solution (2 patches in winShmSystemLock(...)):
1.
old code:
if( lockType == _SHM_WRLCK ) dwFlags |= LOCKFILE_EXCLUSIVE_LOCK;
new code:
#if SQLITE_OS_WINCE==1 _WIN32_WCE!=0x420 _WIN32_WCE!=420
if( lockType == _SHM_WRLCK ) dwFlags |= LOCKFILE_EXCLUSIVE_LOCK;
#endif // WinCE 4.20?
2.
old code:
if( lockType==_SHM_UNLCK ){
rc = UnlockFileEx(pFile-hFile.h, 0, nByte, 0, ovlp);
}else{
new code:
if( lockType==_SHM_UNLCK ){
#if SQLITE_OS_WINCE==1 _WIN32_WCE!=0x420 _WIN32_WCE!=420
rc = UnlockFileEx(pFile-hFile.h, 0, nByte, 0, ovlp);
#else // WinCE 4.20?
rc = UnlockFile(pFile-hFile.h, ovlp.Offset, ovlp.OffsetHigh, nByte, 0);
#endif // WinCE 4.20?
}else{
I'm not sure whether I pass right parameters to the UnlockFile(...), it
should be checked
Why 2 conditions (0x420 and 420)? In eVC 4.0 variable $(CEVER)
(_WIN32_WCE get value from it) has decimal form, but in VS2005 and later
this variable has hex. form.
---
WBR,
Aleksandr Jr.
___
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] poor insert performance with 3.7/Windows
I've not seen the performance degradation you've reported here in my testing. If you could provide any more details that would help in reproducing this, it would be appreciated. And yes, you can safely modify the SQLITE_FCNTL_SIZE_HINT in os_win.c to be a no-op in the same way as os_unix.c. -Shane On Fri, Jul 23, 2010 at 6:26 PM, GB gbi...@web.de wrote: Hi all, I had to recognise that our data-transforming (read: heavily inserting) procedures had a massive drop in performance with 3.7. Some investigation showed that this is related to pager_write_pagelist where a hint to an estimated target filesize is given to the filesystem, which on Windows translates to a winTruncate. This in turn causes a low-level-flush of file metadata and performance drops to nil. Is the setting of the file size really needed here or can we safely drop it? Dropping it seems to work for ourselves, Regards, Gerd ___ 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] Regression with sqlite-snapshot-201007091257
I tried to reproduce the issue with the latest version, as well as with the referenced snapshot, and could not. I ran two instances of the sqlite CLI as you indicated without issue. C:\work\sqlite\win32\Debugsqlite_snapshot test.db SQLite version 3.7.0 Enter .help for instructions Enter SQL statements terminated with a ; sqlite pragma encoding=UTF-8; sqlite pragma auto_vacuum=incremental; sqlite pragma journal_mode=truncate; truncate sqlite select sqlite_version(); 3.7.0 sqlite select sqlite_source_id(); 2010-07-09 12:57:54 0c32c4bbdd74297767dcf4ec4295f9cc72875af0 sqlite PRAGMA compile_options; DEBUG OMIT_LOAD_EXTENSION TEMP_STORE=1 TEST THREADSAFE=1 sqlite I included the output of version, source_id, and compile_options for reference. What options are you compiling with? Is there perhaps a journal file somewhere that you're unaware of? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [csv extension] Error while reading long lines
Thanks for the report. The extension is still very a much a
work-in-progress and any feedback is greatly appreciated.
-Shane
On Sun, Apr 18, 2010 at 12:51 PM, gwenn gwenn.k...@gmail.com wrote:
Hello,
There is a little bug/typo in the csv extension when lines exceed 100
characters:
*** glibc detected *** sqlite3: realloc(): invalid pointer:
0x00ad1a78 ***
=== Backtrace: =
/lib/libc.so.6[0x7f6dab009d16]
/lib/libc.so.6(realloc+0x321)[0x7f6dab00fda1]
./libSqliteCsv.so[0x7f6da9ef9dbf]
A possible patch is:
--- /tmp/SQLite-d474195a997b9d94/ext/csv/csv.c 2009-11-05 05:14:30.0
+0100
+++ csv.c 2010-04-18 18:48:04.0 +0200
@@ -160,7 +160,7 @@
}
}
if( bShrink ){
- pCSV-zRow = realloc( pCSV-zRow, n+1 );
+ pCSV-zRow = sqlite3_realloc( pCSV-zRow, n+1 );
pCSV-maxRow = n+1;
}
return bEol ? pCSV-zRow : 0;
Regards.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Index and GLOB
On Thu, Apr 15, 2010 at 9:51 AM, Mike Goins mike.go...@adtecservices.netwrote: sqlite explain query plan SELECT tb_file_key, basename, extension, path FROM tb_file WHERE basename GLOB 'a' AND extension GLOB 'b' AND path GLOB '*'; 0|0|TABLE tb_file WITH INDEX fullpath_idx SQLite will not try to optimize a GLOB (to use an index) if it doesn't contain wild cards -- although it probably should. This optimization has been added to our list for a possible future enhancement. As a workaround, as has been noted, you could simply use the foo = 'b' instead of foo GLOB 'b'. Is the explain query plan lying when it says it will use the index? Can I just slap a trailing * on the end of each parameter for it really use the index? Yep, I know I can modify the query, but that requires more prepared statements, and each I'm adding extends the connect/prepare time for my embedded system. How I wish the SSE was still around. I'm not sure what you mean by lying. When I try EXPLAIN QUERY PLAN on a GLOB 'b' example, it reports that it will not use an index. Output of the three cases below: C:\work\sqlite\misc\glob_indextype test.sql DROP TABLE IF EXISTS t1; CREATE TABLE t1 (word TEXT); CREATE INDEX t1_word ON t1(word); EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE word GLOB 'b'; EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE word GLOB 'b*'; EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE word = 'b'; C:\work\sqlite\misc\glob_indexsqlite3 test.sql 0|0|TABLE t1 0|0|TABLE t1 WITH INDEX t1_word 0|0|TABLE t1 WITH INDEX t1_word You could add a * to the end of each parameter if you like, but note that GLOB 'b' and GLOB 'b*' are different conditions, and will only be the same if your data set doesn't contain more than one entry beginning with 'b'. I suppose you could also try something like: SELECT * FROM t1 WHERE word GLOB 'b*' AND word GLOB 'b' Where the first GLOB uses the parameter with '*' appended, and the second GLOB uses the actual parameter. HTH. -Shane ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SIGSEGV on INSERT DEFAULT VALUES with triggers
On Tue, Apr 13, 2010 at 12:30 PM, Tomasz Ł. Nowak
tomasz.no...@man.poznan.pl wrote:
Shane, you wrote:
I could not reproduce this on the current or 3.6.23 build:
./sqlite3 -version
3.6.23
cat script.sql
CREATE TABLE current(x,y,z);
INSERT INTO current DEFAULT VALUES ('a', 'b', 30);
./sqlite3 -init script.sql
-- Loading resources from script.sql
Error: near line 2: near (: syntax error
try exactly this code (it is even simpler than code I sent previously):
CREATE TABLE current (src varchar, price decimal(10,2) NOT NULL, timestamp
TEXT DEFAULT (DATETIME('NOW')));
CREATE TRIGGER entry_in_tab_s before insert on current begin insert or
ignore into tab_s values (new.src, new.dst, 9); end;
INSERT INTO current DEFAULT VALUES ('a', 30);
I used different compilations (e.g. amalgamation, Debian packages,
static binary downloadable from sqlite web page). One of them is Debian
source package, version 3.6.23:
libtool: compile: gcc -g -O2 -DSQLITE_OS_UNIX=1 -I. -I./src
-D_HAVE_SQLITE_CONFIG_H -DNDEBUG -I/usr/include/tcl8.5 -DSQLITE_THREADSAFE=1
-DSQLITE_THREAD_OVERRIDE_LOCK=-1 -DSQLITE_OMIT_LOAD_EXTENSION=1
-DSQLITE_TEMP_STORE=1 -c sqlite3.c -o sqlite3.o
(enabling load extension doesn't change the described behaviour)
I missed the trigger part in my original reproduction. Regardless, it has
been fixed previously here:
http://www.sqlite.org/src/info/f3162063fd
HTH
-Shane
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Index and GLOB
On Mon, Apr 12, 2010 at 9:14 AM, Igor Tandetnik itandet...@mvps.org wrote: Mike Goins wrote: sqlite explain query plan SELECT tb_file_key, basename, extension, path FROM tb_file WHERE basename GLOB 'a' AND extension GLOB 'b' AND path GLOB '*'; 0|0|TABLE tb_file WITH INDEX fullpath_idx The last one with the leading wildcard doesn't look like it should use the index. SQLite can use the index to satisfy conditions on basename and extension, then do a full scan of what's left. -- Igor Tandetnik SQLite will not try to optimize a GLOB (to use an index) if it doesn't contain wild cards -- although it probably should. This optimization has been added to our list for a possible future enhancement. As a workaround, as has been noted, you could simply use the foo = 'b' instead of foo GLOB 'b'. -Shane ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Android database corruption
On Fri, Mar 26, 2010 at 11:38 AM, cliff 2 bailey.cliff...@gmail.com wrote: Hi, Hopefully someone here can help where the android guys haven't been able to. We are having an issue with our sqlite database running on the android platform. We are accessing the db from the sdcard on an android phone running android 2.1. It works perfectly 99% of the time, but every now and again, we get database corruption appear randomly. We have not changed any pragmas, so synchronous is set to full and journal_mode delete. It does not correspond to any power failures, or application crashes, and will succeed in pulling back data from the database for hundreds of calls, and then fail on a query that has worked many times before. It can occur in a number of native calls. We have performed integrity checks on the db in question and they return ok (prior to the corruption - androids response to a corrupt db is to delete it, so we can't see its state after corruption). The database in question is downloaded as a complete binary and then accessed read-only using android's rawQuery call. A couple of typical stack traces for when the corruption occurs: 03-26 14:09:50.572 E/DatabaseHelper( 1253): android.database.sqlite.SQLiteDatabaseCorruptException: database disk image is malformed: , while compiling: SELECT o.FieldId,o.Reference, o.Category, o.OIndex,o.Description, c.Choice, c.Derivative FROM Option o, Choice c WHERE o.FieldId = c.FieldId and o.Reference = ? ORDER BY o.Option, o.OIndex 03-26 14:09:50.572 E/DatabaseHelper( 1253): at android.database.sqlite.SQLiteProgram.native_compile(Native Method) 03-26 14:09:50.572 E/DatabaseHelper( 1253): at android.database.sqlite.SQLiteProgram.compile(SQLiteProgram.java:110) 03-26 14:09:50.572 E/DatabaseHelper( 1253): at android.database.sqlite.SQLiteProgram.init(SQLiteProgram.java:59) 03-26 14:09:50.572 E/DatabaseHelper( 1253): at android.database.sqlite.SQLiteQuery.init(SQLiteQuery.java:49) 03-26 14:09:50.572 E/DatabaseHelper( 1253): at android.database.sqlite.SQLiteDirectCursorDriver.query(SQLiteDirectCursorDriver.java:49) 03-26 14:09:50.572 E/DatabaseHelper( 1253): at android.database.sqlite.SQLiteDatabase.rawQueryWithFactory(SQLiteDatabase.java:1221) 03-26 14:09:50.572 E/DatabaseHelper( 1253): at android.database.sqlite.SQLiteDatabase.rawQuery(SQLiteDatabase.java:1194) We can run the exact same query hundreds of times and it is fine. Then all of a sudden it fails. Another typical stack trace: 03-24 14:54:04.678 E/AsyncTask( 6828): RuntimeException while executing background thread 1045 03-24 14:54:04.678 E/AsyncTask( 6828): android.database.sqlite.SQLiteDatabaseCorruptException: database disk image is malformed 1046 03-24 14:54:04.678 E/AsyncTask( 6828): at android.database.sqlite.SQLiteQuery.native_fill_window(Native Method) 1047 03-24 14:54:04.678 E/AsyncTask( 6828): at android.database.sqlite.SQLiteQuery.fillWindow(SQLiteQuery.java:75) 1048 03-24 14:54:04.678 E/AsyncTask( 6828): at android.database.sqlite.SQLiteCursor.fillWindow(SQLiteCursor.java:288) 1049 03-24 14:54:04.678 E/AsyncTask( 6828): at android.database.sqlite.SQLiteCursor.getCount(SQLiteCursor.java:269) 1050 03-24 14:54:04.678 E/AsyncTask( 6828): at android.database.AbstractCursor.moveToPosition(AbstractCursor.java:171) 1051 03-24 14:54:04.678 E/AsyncTask( 6828): at android.database.AbstractCursor.moveToFirst(AbstractCursor.java:248) The one above is while trying to move to the first row in a cursor. (android method native_fill_window source available here: http://www.netmite.com/android/mydroid/frameworks/base/core/jni/android_database_SQLiteQuery.cpp and native_compile http://www.netmite.com/android/mydroid/frameworks/base/core/jni/android_database_SQLiteProgram.cpp ) We have been looking for some way we could be corrupting the database through incorrect use, however we are now out of options. We have triple checked we are opening and closing all cursors and connections correctly and checked there aren't multiple threads accessing the db at the same time and nothing seems out of the ordinary when corruption occurs. As all we are doing is reading the database, I really can't see how we can be corrupting it. I was wondering if anyone on this forum had experienced anything similar in the past, or could suggest things we could do to track down what is causing the corruption. From what I've read on the SQLite site this type of corruption should be all but impossible, however it is definitely happening. If you need any more information to help track down the problem, please let me know. Thanks in advance for any tips, or advice to help solve this. Cliff -- View this message in context: http://old.nabble.com/Android-database-corruption-tp28044218p28044218.html Sent from the SQLite mailing list archive at Nabble.com. ___
Re: [sqlite] Available alternatives to syntax diagrams in documentation
I added a psuedo-BNF renderer for the bubble syntax graph data at http://www.sqlite.org/docsrc/artifact/873cf35adf to go along with the text based bubble graph at http://www.sqlite.org/docsrc/artifact/645054606c These are not meant to replace the official syntax specification at http://www.sqlite.org/syntaxdiagrams.html -Shane ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Available alternatives to syntax diagrams in documentation
On Thu, Mar 25, 2010 at 7:06 PM, P Kishor punk.k...@gmail.com wrote: On Thu, Mar 25, 2010 at 5:59 PM, Shane Harrelson sh...@sqlite.org wrote: I added a psuedo-BNF renderer for the bubble syntax graph data at http://www.sqlite.org/docsrc/artifact/873cf35adf to go along with the text based bubble graph at http://www.sqlite.org/docsrc/artifact/645054606c These are not meant to replace the official syntax specification at http://www.sqlite.org/syntaxdiagrams.html This is gorgeous, but is it not possible to just have plain text docs? Much like most other database manuals do? http://www.postgresql.org/docs/8.4/static/ddl-basics.html http://dev.mysql.com/doc/refman/5.5/en/create-table.html or am I missing something? -- Puneet Kishor Is this http://www.sqlite.org/lang.html or http://www.sqlite.org/lang_createtable.html what you're looking for? Otherwise, I'm not sure what you're asking for. -Shane ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Available alternatives to syntax diagrams in documentation
On Thu, Mar 25, 2010 at 7:22 PM, P Kishor punk.k...@gmail.com wrote: On Thu, Mar 25, 2010 at 6:19 PM, Shane Harrelson sh...@sqlite.org wrote: On Thu, Mar 25, 2010 at 7:06 PM, P Kishor punk.k...@gmail.com wrote: On Thu, Mar 25, 2010 at 5:59 PM, Shane Harrelson sh...@sqlite.org wrote: I added a psuedo-BNF renderer for the bubble syntax graph data at http://www.sqlite.org/docsrc/artifact/873cf35adf to go along with the text based bubble graph at http://www.sqlite.org/docsrc/artifact/645054606c These are not meant to replace the official syntax specification at http://www.sqlite.org/syntaxdiagrams.html This is gorgeous, but is it not possible to just have plain text docs? Much like most other database manuals do? http://www.postgresql.org/docs/8.4/static/ddl-basics.html http://dev.mysql.com/doc/refman/5.5/en/create-table.html or am I missing something? -- Puneet Kishor Is this http://www.sqlite.org/lang.html or http://www.sqlite.org/lang_createtable.html what you're looking for? Otherwise, I'm not sure what you're asking for. The links you provided goes back to diagrams. I believe the OP was asking if there was plain text documentation available. Whether or not he was asking that, I am wondering... is there plain text documentation available at all? I mean, where can I see syntax spelled out like so DELETE FROM qualified_table-name WHERE expr It used to be available until the docs were migrated to the diagrams. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users Is this http://www.sqlite.org/lang.html or http://www.sqlite.org/lang_createtable.html what you're looking for? Otherwise, I'm not sure what you're asking for. -Shane ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Available alternatives to syntax diagrams in documentation
On Thu, Mar 25, 2010 at 7:24 PM, P Kishor punk.k...@gmail.com wrote: On Thu, Mar 25, 2010 at 6:22 PM, P Kishor punk.k...@gmail.com wrote: On Thu, Mar 25, 2010 at 6:19 PM, Shane Harrelson sh...@sqlite.org wrote: On Thu, Mar 25, 2010 at 7:06 PM, P Kishor punk.k...@gmail.com wrote: On Thu, Mar 25, 2010 at 5:59 PM, Shane Harrelson sh...@sqlite.org wrote: I added a psuedo-BNF renderer for the bubble syntax graph data at http://www.sqlite.org/docsrc/artifact/873cf35adf to go along with the text based bubble graph at http://www.sqlite.org/docsrc/artifact/645054606c These are not meant to replace the official syntax specification at http://www.sqlite.org/syntaxdiagrams.html This is gorgeous, but is it not possible to just have plain text docs? Much like most other database manuals do? http://www.postgresql.org/docs/8.4/static/ddl-basics.html http://dev.mysql.com/doc/refman/5.5/en/create-table.html or am I missing something? -- Puneet Kishor Is this http://www.sqlite.org/lang.html or http://www.sqlite.org/lang_createtable.html what you're looking for? Otherwise, I'm not sure what you're asking for. The links you provided goes back to diagrams. I believe the OP was asking if there was plain text documentation available. Whether or not he was asking that, I am wondering... is there plain text documentation available at all? I mean, where can I see syntax spelled out like so DELETE FROM qualified_table-name WHERE expr It used to be available until the docs were migrated to the diagrams. Here is another way of conveying the problem here. Go to your browser's settings and turn off images. Then try to read the documentation at sqlite.org. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users If you scroll down on on any of the lang_*.html pages, like http://www.sqlite.org/lang_createtable.html you'll see text based descriptions of the individual command. Even with graphics turned off. All of the individual pages are referenced from: http://www.sqlite.org/lang.html Is that what you mean? -Shane ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] out of memory in ANALYZE with SQLITE_ENABLE_STAT2
On Thu, Mar 25, 2010 at 10:22 AM, Martin Wilck
martin.wi...@ts.fujitsu.comwrote:
Hello,
with sqlite 3.6.23 with SQLITE_ENABLE_STAT2, I get
an out of memory error running the following simple SQL code:
CREATE TABLE dist_T (
d_row INTEGER PRIMARY KEY,
dist TEXT UNIQUE NOT NULL
);
INSERT INTO dist_T VALUES (NULL, '');
INSERT INTO dist_T VALUES (NULL, 'a');
INSERT INTO dist_T VALUES (NULL, 'b');
INSERT INTO dist_T VALUES (NULL, 'c');
INSERT INTO dist_T VALUES (NULL, 'd');
INSERT INTO dist_T VALUES (NULL, 'e');
INSERT INTO dist_T VALUES (NULL, 'f');
INSERT INTO dist_T VALUES (NULL, 'g');
INSERT INTO dist_T VALUES (NULL, 'h');
INSERT INTO dist_T VALUES (NULL, 'i');
ANALYZE;
This is always reproducable if dist_T contains 10 or more rows. With 0-9
rows, I don't see the problem. Also not if SQLITE_ENABLE_STAT2 is not set.
The problem happens when interpreting the results of SELECT
idx,sampleno,sample FROM 'main'.sqlite_stat2.on line 66154 in sqlite3.c
(sqlite3AnalysisLoad()), apparently because
sqlite3_column_bytes() returned a size of 0 bytes for column 2 in line
66145:
66145 int n = sqlite3_column_bytes(pStmt, 2);
66146 if( n24 ){
66147 n = 24;
66148 }
66149 pSample-nByte = (u8)n;
66150 pSample-u.z = sqlite3DbMallocRaw(dbMem, n);
66151 if( pSample-u.z ){
66152 memcpy(pSample-u.z, z, n);
66153 }else{
66154 db-mallocFailed = 1;
66155 break;
66156 }
I am including some gdb output. Note that pResultSet[2].n = 0.
(gdb) bt
#0 sqlite3AnalysisLoad (db=0x55af68, iDb=0) at sqlite3.c:66154
#1 0x2af0c5884bd8 in sqlite3VdbeExec (p=0x569e38) at sqlite3.c:57585
#2 0x2af0c587c02d in sqlite3Step (p=0x569e38) at sqlite3.c:51342
#3 0x2af0c587c249 in sqlite3_step (pStmt=0x569e38) at sqlite3.c:51402
#4 0x00403eea in shell_exec (db=0x55af68, zSql=0x55aee0
ANALYZE;,
xCallback=0x402ba1 shell_callback, pArg=0x7fffe5377850,
pzErrMsg=0x7fffe5375aa8) at shell.c:1012
#5 0x00408019 in process_input (p=0x7fffe5377850, in=0x55ab50)
at shell.c:2236
#6 0x00406d1a in do_meta_command (zLine=0x5537d0 .read,
p=0x7fffe5377850) at shell.c:1860
#7 0x00407deb in process_input (p=0x7fffe5377850, in=0x0) at
shell.c:2195
#8 0x00409183 in main (argc=1, argv=0x7fffe5378eb8) at
shell.c:2616
(gdb) p *pIdx
$4 = {zName = 0x56c23d sqlite_autoindex_dist_T_1, nColumn = 1,
aiColumn = 0x56c230, aiRowEst = 0x56c234,
pTable = 0x56b328, tnum = 3, onError = 99 'c', autoIndex = 1 '\001',
zColAff = 0x56bce8 ab, pNext = 0x0,
pSchema = 0x55bb88, aSortOrder = 0x56c23c , azColl = 0x56c228,
aSample = 0x570058}
(gdb) p *((Vdbe*) pStmt)
$5 = {db = 0x55af68, pPrev = 0x0, pNext = 0x569e38, nOp = 15, nOpAlloc =
42, aOp = 0x570d88, nLabel = 4,
nLabelAlloc = 26, aLabel = 0x0, apArg = 0x570fd0, aColName =
0x571188, pResultSet = 0x570ef0, nResColumn = 3,
nCursor = 1, apCsr = 0x570fd0, errorAction = 2 '\002', okVar = 0
'\0', nVar = 0, aVar = 0x570fd0,
azVar = 0x570fd0, magic = 3186757027, nMem = 4, aMem = 0x570eb8,
cacheCtr = 3, pc = 8, rc = 0, zErrMsg = 0x0,
explain = 0 '\0', changeCntOn = 0 '\0', expired = 0 '\0', runOnlyOnce
= 0 '\0', minWriteFileFormat = 255 '377',
inVtabMethod = 0 '\0', usesStmtJournal = 0 '\0', readOnly = 1 '\001',
isPrepareV2 = 0 '\0', nChange = 0,
btreeMask = 1, startTime = 0, aMutex = {nMutex = 0, aBtree = {0x0
repeats 11 times}}, aCounter = {0, 0},
zSql = 0x5685b0 SELECT idx,sampleno,sample FROM
'main'.sqlite_stat2, pFree = 0x0, nFkConstraint = 0,
nStmtDefCons = 0, iStatement = 0, pFrame = 0x0, nFrame = 0, expmask = 0}
(gdb) p ((Vdbe*) pStmt)-pResultSet[0]
$8 = {u = {i = 0, nZero = 0, pDef = 0x0, pRowSet = 0x0, pFrame = 0x0}, r
= 0, db = 0x55af68,
z = 0x568fd8 sqlite_autoindex_dist_T_1, n = 25, flags = 514, type =
3 '\003', enc = 1 '\001', xDel = 0,
zMalloc = 0x568fd8 sqlite_autoindex_dist_T_1}
(gdb) p ((Vdbe*) pStmt)-pResultSet[1]
$9 = {u = {i = 0, nZero = 0, pDef = 0x0, pRowSet = 0x0, pFrame = 0x0}, r
= 0, db = 0x55af68, z = 0x568208 0,
n = 1, flags = 514, type = 3 '\003', enc = 1 '\001', xDel = 0,
zMalloc = 0x568208 0}
(gdb) p ((Vdbe*) pStmt)-pResultSet[2]
$10 = {u = {i = 0, nZero = 0, pDef = 0x0, pRowSet = 0x0, pFrame = 0x0},
r = 0, db = 0x55af68, z = 0x568bc8 ,
n = 0, flags = 514, type = 3 '\003', enc = 1 '\001', xDel = 0,
zMalloc = 0x568bc8 }
Regards
Martin
--
Dr. Martin Wilck
PRIMERGY System Software Engineer
x86 Server Engineering
Fujitsu Technology Solutions GmbH
Heinz-Nixdorf-Ring 1
33106 Paderborn, Germany
Phone: ++49 5251 525 2796
Fax:++49 5251 525 2820
Email: martin.wi...@ts.fujitsu.com
Internet: http://ts.fujitsu.com
Re: [sqlite] corruption problem with attached macintosh database
On Wed, Mar 24, 2010 at 7:20 PM, Dave Dyer ddyer-sql...@real-me.net wrote: Could you help us by adding any of the following details ? What OS is the Mac running ? OSX 10.4.11 for me, but also snow leopard. What OS is the PC running ? Windows 2003 server for me, but also XP (note the file systems are all mac file systems) What protocol is being used to access the Mac file share ? Presumably windows standard file sharing protocol over tcp Is the file-sharing host accessing the database as a shared file, or as a file on its hard disk ? The mac acting as file host is accessing the file as a local file. Does this happen without any data-changing instructions ? In other words can I get this fault using only _open, ATTACH and lots of SELECT commands until something falls over ? Or even just repeated _open, ATTACH and _close until something falls over ? No data on this question. The purpose of this querty setup is to copy some data into an auxialiary database. Note that the main database, which is updated periodically, doesn't have a corruption problem, Are both 'main' and 'auxiliary' on in the same folder, being accessed the same way ? Yes. And significantly, there is no corruption problem with simultaneous updates to the main database. and that this is only a problem with databases resident on a mac. The same scenario, with databases resident on the PC disk, works fine. When the databases are on the PC disk, what protocol is the Mac using to access them ? Presumably the same. Before recent versions of OSX this kind of access used samba server. What journaling mode are you using? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SIGSEGV on INSERT DEFAULT VALUES with triggers
Can you provide more details?
What options are you using?
What version of the source? Amalgamation? Preprocessed? Complete package?
I could not reproduce this on the current or 3.6.23 build:
./sqlite3 -version
3.6.23
cat script.sql
CREATE TABLE current(x,y,z);
INSERT INTO current DEFAULT VALUES ('a', 'b', 30);
./sqlite3 -init script.sql
-- Loading resources from script.sql
Error: near line 2: near (: syntax error
If you are using the amalgamation or other pre-processed sources, you should
view the warnings at
http://www.sqlite.org/compile.html#omitfeatures
Trying to OMIT or ENABLE features with compile options without using the
canonical sources can lead to issues like you are seeing.
HTH.
-Shane
2010/3/24 Tomasz Ł. Nowak tomasz.no...@man.poznan.pl
Hi!
A bug resulting in a crash (segmentation fault) of sqlite3 has been
detected. Please find attached the script causing problems and a debug
session log.
Tested to be vulnerable:
linux 3.6.23
linux 3.6.22
linux 3.6.4
solaris 3.5.1
Tested to be invulnerable:
linux 2.8.17
Best wishes
Tomasz Nowak
___
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] Available alternatives to syntax diagrams in documentation
I knocked out a quick TCl script that uses *exactly* the same syntax graph specification as bubble-generator.tcl but attempts to render the graphs without GIFs. You can see the script here: http://www.sqlite.org/docsrc/finfo?name=art/syntax/bubble-generator-text.tcl You can view the output here: http://www.sqlite.org/docsrc/artifact/149ec0f41f The script still needs a little work (especially in browsers other than FF), but the graphs are reasonable similar to the GIF based versions and usable. I have no idea what kinds of things a screen reader would need to make this version useful, but if you let me know, I will try to add them. HTH. -Shane On Mon, Mar 22, 2010 at 2:29 PM, Jay A. Kreibich j...@kreibi.ch wrote: On Mon, Mar 22, 2010 at 11:13:25PM +0530, Roger Binns scratched on the wall: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Jay A. Kreibich wrote: On Mon, Mar 22, 2010 at 05:57:18PM +0530, Roger Binns scratched on the wall: If you are the developer type yourself then it is a simple matter of programming :-) As someone that's been spending a lot of time with that script, excuse me while I run around the room laughing maniacally. Perhaps you may want to review the definition SMOP Yes, I'm well aware. I was confirming, not trying to contradict. And in defense of the script, the biggest issue is that I don't know TCL and it's somewhat casual syntax for literals can be very frustrating for someone that has not used this class of language. -j -- Jay A. Kreibich J A Y @ K R E I B I.C H Our opponent is an alien starship packed with atomic bombs. We have a protractor. I'll go home and see if I can scrounge up a ruler and a piece of string. --from Anathem by Neal Stephenson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Available alternatives to syntax diagrams in documentation
More updates for Chrome support. http://www.sqlite.org/docsrc/artifact/981f61a5e4 On Tue, Mar 23, 2010 at 11:50 PM, Shane Harrelson sh...@sqlite.org wrote: I knocked out a quick TCl script that uses *exactly* the same syntax graph specification as bubble-generator.tcl but attempts to render the graphs without GIFs. You can see the script here: http://www.sqlite.org/docsrc/finfo?name=art/syntax/bubble-generator-text.tcl You can view the output here: http://www.sqlite.org/docsrc/artifact/149ec0f41f The script still needs a little work (especially in browsers other than FF), but the graphs are reasonable similar to the GIF based versions and usable. I have no idea what kinds of things a screen reader would need to make this version useful, but if you let me know, I will try to add them. HTH. -Shane On Mon, Mar 22, 2010 at 2:29 PM, Jay A. Kreibich j...@kreibi.ch wrote: On Mon, Mar 22, 2010 at 11:13:25PM +0530, Roger Binns scratched on the wall: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Jay A. Kreibich wrote: On Mon, Mar 22, 2010 at 05:57:18PM +0530, Roger Binns scratched on the wall: If you are the developer type yourself then it is a simple matter of programming :-) As someone that's been spending a lot of time with that script, excuse me while I run around the room laughing maniacally. Perhaps you may want to review the definition SMOP Yes, I'm well aware. I was confirming, not trying to contradict. And in defense of the script, the biggest issue is that I don't know TCL and it's somewhat casual syntax for literals can be very frustrating for someone that has not used this class of language. -j -- Jay A. Kreibich J A Y @ K R E I B I.C H Our opponent is an alien starship packed with atomic bombs. We have a protractor. I'll go home and see if I can scrounge up a ruler and a piece of string. --from Anathem by Neal Stephenson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Bug Report: DateTime incorrect for Windows
On Fri, Feb 26, 2010 at 1:49 AM, 4eanlss 4ean...@engineer.com wrote: Shane, I apologize for my quick snap response. I have debugged the code and have identified what is happening. The calculation in my environment results in 0 (zero) for this: static const sqlite3_int64 max32BitValue = (sqlite3_int64)20 + (sqlite3_int64)20 + (sqlite3_int64)294967296; Which breaks all of the following datetime calculations. When compiled with the type sqlite3_uint64 then it works correctly. I am working on a much simpler winCurrentTime solution but I'm stilling creating all test cases to verify the algorithm before posting. 4eanlss I'd be worried that other 64 bit calculations are going wrong as well. Can you verify what type sqlite3_int64 is being set to in your environment? Also can you try the following (note the L suffix on the constants): static const sqlite3_int64 max32BitValue = (sqlite3_int64)20L + (sqlite3_int64)20L + (sqlite3_int64)294967296L; -Shane ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Bug in porter stemmer
Additionally, your algorithm reference for step1c is from the Snowball
English (Porter2) algorithm.
The implementation used in SQLite is for the original Porter algorithm
discussed here:
http://tartarus.org/~martin/PorterStemmer/
HTH.
-SHane
On Wed, Feb 24, 2010 at 10:05 AM, D. Richard Hipp d...@hwaci.com wrote:
We got the Porter stemmer code directly from Martin Porter.
I'm sorry it does not work like you want it to. Unfortunately, we
cannot change it now without introducing a serious incompatibility
with the millions and millions of applications already in the field
that are using the existing implementation.
FTS3 has a pluggable stemmer module. You can write your own stemmer
that works correctly if you like, and link it in for use in your
applications. We will also investigate making your recommended
changes for FTS4. However, in order to maintain backwards
compatibility of FTS3, we cannot change the stemmer algorithm, even to
fix a bug.
On Feb 24, 2010, at 9:59 AM, James Berry wrote:
Can somebody please clarify the bug reporting process for sqlite? My
understanding is that it's not possible to file bug reports
directly, and that the advise is to write to the user list first.
I've done that (below) but have no response so far and am concerned
that this means the bug report will just be forgotten others, as
well as by me.
How does this bug move from a message on a list to a ticket (and
ultimately a patch, we hope) in the system?
James
On Feb 22, 2010, at 2:51 PM, James Berry wrote:
I'm writing to report a bug in the porter-stemmer algorithm
supplied as part of the FTS3 implementation.
The stemmer has an inverted logic error that prevents it from
properly stemming words of the following form:
dry - dri
cry - cri
This means, for instance, that the following words don't stem the
same:
dried - dri -doesn't match- dry
cried - cry -doesn't match- cry
The bug seems to have been introduced as a simple logic error by
whoever wrote the stemmer code. The original description of step 1c
is here: http://snowball.tartarus.org/algorithms/english/stemmer.html
Step 1c:
replace suffix y or Y by i if preceded by a non-vowel which
is
not the first letter of the word (so cry - cri, by - by, say -
say)
But the code in sqlite reads like this:
/* Step 1c */
if( z[0]=='y' hasVowel(z+1) ){
z[0] = 'i';
}
In other words, sqlite turns the y into an i only if it is preceded
by a vowel (say - sai), while the algorithm intends this to be
done if it is _not_ preceded by a vowel.
But there are two other problems in that same line of code:
(1) hasVowel checks whether a vowel exists anywhere in the string,
not just in the next character, which is incorrect, and goes
against the step 1c directions above. (amplify would not be
properly stemmed to amplifi, for instance)
(2) The check for the first letter is not performed (for words
like by, etc)
I've fixed both of those errors in the patch below:
/* Step 1c */
- if( z[0]=='y' hasVowel(z+1) ){
+ if( z[0]=='y' isConsonant(z+1) z[2] ){
z[0] = 'i';
}
___
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
D. Richard Hipp
d...@hwaci.com
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Bug Report: DateTime incorrect for Windows
On Tue, Feb 23, 2010 at 1:56 AM, 4eanlss 4ean...@engineer.com wrote: Shane Harrelson shane.harrel...@... writes: I was unable to duplicate your error with the CLI and 3.6.22 (compiled with cygwin\gcc or msvc): Ok, so Borland not supported and windows API not followed. Just as I expected from open source software. I never said Borland was not supported. I said I wasn't able to reproduce the problem with the two compilers currently available to me. It would be very difficult to maintain build environments for every compiler and compiler version used by SQLite users. If you check the full source, you'll see where we've made specific modifications for many compilers not used by us, like the Borland compilers, all done and tested with the help of users. I'm not certain what you mean by windows API not followed. Could you provide more information? In non CE builds, the only Windows API called in the winCurrentTime() function is GetSystemTimeAsFileTime(). This returns a FILETIME structure containing two DWORDS (u32s) representing the the number of 100-nanosecond intervals since January 1, 1601. The MSDN docs on the Windows API that use and manipulate system time values state that it will be zero or positive, and less than 0x8000 (except for functions such as SetFileTime() which use 0x to indicate that the previous file time should be preserved.) Regardless, an int64 is large enough to represent all the 100-nanosecond intervals from January 1, 1601 through sometime in the year 30,828. We use casting and 64-bit math to convert and manipulate these two DWORDS as 64-bit *signed *integers. We avoid using uint64s because it's difficult enough working through all the various compiler quirks out there for manipulating 64-bit types with just int64s without introducing another type. Case in point, the odd initialization of some of the consts we use in the winCurrentTime() function and the use of math instead of shifting. I suspect that one of these quirks in your build environment is being exposed by the code in winCurrentTime().Perhaps you could debug through the code and find where the calculations are going wrong? In this way we could provide more help. HTH. -Shane ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] round problem?
http://www.sqlite.org/lang_corefunc.html#round Under the covers, when the second term to ROUND(X,y) is omitted, SQLite adds 0.5 and then truncates.Because of floating point precision, some numbers can not be represented exactly... causing the odd rounding you saw in your examples. I'll see what I can do to update the documentation and/or make the rounding more consistent. HTH. -Shane On Mon, Feb 15, 2010 at 12:03 PM, Phil Hibbs sna...@gmail.com wrote: Igor: http://en.wikipedia.org/wiki/Rounding#Round_half_to_even So, are you saying round-half-to-even is the SQLite behaviour? I would have expected it to have used the normal mathematical convention of round-half-away-from-zero. The reason this is normal mathematical behaviour is that any decimal result is likely to be a truncation of the real result, e.g. pi = 3.14195. The SQL standard leaves it up to the implementation: sec 4.4.1: An approximation obtained by rounding of a numerical value N for an exact numeric type T is a value V representable in T such that the absolute value of the difference between N and the nu- merical value of V is not greater than half the absolute value of the difference between two successive numerical values repre- sentable in T. If there are more than one such values V, then it is implementation-defined which one is taken. So I guess round half to even is a reasonable interpretation, but I'd like to see this, and all implementation-defined behaviour, documented. Phil Hibbs. -- Don't you just hate self-referential sigs? ___ 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] round problem?
On Mon, Feb 15, 2010 at 12:52 PM, Phil Hibbs sna...@gmail.com wrote: Shane Harelson: Under the covers, when the second term to ROUND(X,y) is omitted, SQLite adds 0.5 and then truncates.Because of floating point precision, some numbers can not be represented exactly... causing the odd rounding you saw in your examples. I've just had a look under the covers, and indeed it seems that SQLite has its own printf implementation. Makes sense, as I think the C standard itself is also implementation-defined in this case, and this might be the kind of thing that SQLite coders want control over. It was hasty to conclude that round-to-even is the rule, as one more example shows: sqlite select round(40226.5); 40227.0 I think the 40223.5 case is just an example of binary/decimal floating point incompatibility. As Kernighan and Plauger put it: Floating-point numbers are a lot like sandpiles: Every time you move one you lose a little sand and pick up a little dirt. I'm a little surprised that it's going wrong with a number ending in 0.5 though, I'd have thought that that would be expressible perfectly in binary without loss. I don't know enough about it though. Phil Hibbs. As you and Igor point out, the numbers being tested can be accurately represented. However, as part of the SQLite's internal printf() implementation, the floating point value is shifted to (loosely) scientific form and manipulated for formatting. So 40224.5 is converted to something like 4.02245e4 before printing. The errors get introduced as part of this manipulation. I'm looking at how this can be improved. HTH. -Shane ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] round problem?
On Mon, Feb 15, 2010 at 12:52 PM, Phil Hibbs sna...@gmail.com wrote: Shane Harelson: Under the covers, when the second term to ROUND(X,y) is omitted, SQLite adds 0.5 and then truncates.Because of floating point precision, some numbers can not be represented exactly... causing the odd rounding you saw in your examples. I've just had a look under the covers, and indeed it seems that SQLite has its own printf implementation. Makes sense, as I think the C standard itself is also implementation-defined in this case, and this might be the kind of thing that SQLite coders want control over. It was hasty to conclude that round-to-even is the rule, as one more example shows: sqlite select round(40226.5); 40227.0 I think the 40223.5 case is just an example of binary/decimal floating point incompatibility. As Kernighan and Plauger put it: Floating-point numbers are a lot like sandpiles: Every time you move one you lose a little sand and pick up a little dirt. I'm a little surprised that it's going wrong with a number ending in 0.5 though, I'd have thought that that would be expressible perfectly in binary without loss. I don't know enough about it though. Phil Hibbs. As you and Igor point out, the numbers being tested can be accurately represented. However, as part of the SQLite's internal printf() implementation, the floating point value is shifted to (loosely) scientific form and manipulated for formatting. So 40224.5 is converted to something like 4.02245e4 before printing. The errors get introduced as part of this manipulation. I'm looking at how this can be improved. HTH. -Shane PS. Sorry if this got posted multiple times. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Writing and reading a csv using sqlite3
On Mon, Feb 15, 2010 at 4:31 PM, Simon Slavin slav...@bigfraud.org wrote: On 15 Feb 2010, at 9:28pm, Roger Binns wrote: Simon Slavin wrote: It uses one when it outputs, but it won't accept the same format when it inputs. So the program is itself inconsistent: however you define 'csv format', either its output or input function is broken. There is a ticket covering this: http://www.sqlite.org/src/tktview?name=c25aab7e7e Ahha. And that points to http://www.sqlite.org/cvstrac/tktview?tn=3276 which explains and fixes the problem in one way. Okay. Simon. In the past, the shell tool (CLI) was never officially supported. There were no testscripts to verify correct operation, or guarantees of included features or compatibility from one version to the next, unlike with the core SQLite library. We've taken steps to more formally support this, as is indicated by the addition of some test scripts for it here: http://www.sqlite.org/src/dir?name=tool However, there are still many features of the CLI that we've yet to write tests for, including tests for CSV support. Work has begun to revamp the CSV support entirely, by implementing it with a virtual table interface. You can see the beginnings of it here in this branch: http://www.sqlite.org/src/timeline?t=csv_ext There are a couple more third party CSV extensions for SQLite that are much more advanced, and it's unlikely that this implementation will ever reach that level of sophistication or support much more than the current import/export needs of the CLI. HTH. -Shane ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] system.data.sqlite encryption
I've taken a cursory glance at the encryption support in the system.data.sqlite C# wrapper from http://sqlite.phxsoftware.com It does appear, that you could, with a little effort, modify the wrapper to work with the SEE extension. I don't see anything in the wrapper that would make it impossible. The only tricky bit would be utilizing the support for passing the encryption key on the open commands (although you should be able to sidestep this and use the encryption pragmas of the SEE extension directly.) HTH. -Shane On Sat, Feb 6, 2010 at 7:21 PM, Sylvain Pointeau sylvain.point...@gmail.com wrote: I already recompiled it, but integrating the encryption extension is again another step. how to be sure of that before to buy the extension ($2'000) ? Sylvain On Sat, Feb 6, 2010 at 1:44 AM, Shane Harrelson sh...@sqlite.org wrote: Yes. If you're willing to compile (and possibly modify) the system.data.sqlite libraries for yourself, it should work. -Shane On Fri, Feb 5, 2010 at 5:05 AM, Sylvain Pointeau sylvain.point...@gmail.com wrote: I posted on this forum http://sqlite.phxsoftware.com/forums/p/2170/8904.aspx The answer was: I'm afraid not. This is an open-source project, and the encryption Dr. Hipp sells is not open source. but as the license of system.data.sqlite is Released to the public domain, use at your own risk! I think I can integrate the encrypted sqlite lib, that I will buy, into system.data.sqlite. I would just like to have a bit of guidance... Best regards, Sylvain On Mon, Feb 1, 2010 at 4:41 PM, Shane Harrelson sh...@sqlite.org wrote: I think you should probably ask this question on the System.data.sqlite support forums at http://sqlite.phxsoftware.com/forums I'm not familiar with enough with their ADO .NET implementation to answer your question. -Shane On Sun, Jan 31, 2010 at 11:54 AM, Sylvain Pointeau sylvain.point...@gmail.com wrote: Hi all, I am using System.data.sqlite from my .NET project, but I am planning to access my db from C/C++ and C# / C++/CI The point is that I want an encryted database, and I plan to use the extension from http://www.hwaci.com/sw/sqlite/prosupport.html#crypto how to use / implement this exension in system.data.sqlite? or are they compatible both? thank you in advance for your help. Best regards, Sylvain ___ 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 ___ 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] system.data.sqlite encryption
I've taken a cursory glance at the encryption support in the system.data.sqlite C# wrapper from http://sqlite.phxsoftware.com It does appear, that you could, with a little effort, modify the wrapper to work with the SEE extension. I don't see anything in the wrapper that would make it impossible. The only tricky bit would be utilizing the support for passing the encryption key on the open commands (although you should be able to sidestep this and use the encryption pragmas of the SEE extension directly.) HTH. -Shane On Sat, Feb 6, 2010 at 7:21 PM, Sylvain Pointeau sylvain.point...@gmail.com wrote: I already recompiled it, but integrating the encryption extension is again another step. how to be sure of that before to buy the extension ($2'000) ? Sylvain On Sat, Feb 6, 2010 at 1:44 AM, Shane Harrelson sh...@sqlite.org wrote: Yes. If you're willing to compile (and possibly modify) the system.data.sqlite libraries for yourself, it should work. -Shane On Fri, Feb 5, 2010 at 5:05 AM, Sylvain Pointeau sylvain.point...@gmail.com wrote: I posted on this forum http://sqlite.phxsoftware.com/forums/p/2170/8904.aspx The answer was: I'm afraid not. This is an open-source project, and the encryption Dr. Hipp sells is not open source. but as the license of system.data.sqlite is Released to the public domain, use at your own risk! I think I can integrate the encrypted sqlite lib, that I will buy, into system.data.sqlite. I would just like to have a bit of guidance... Best regards, Sylvain On Mon, Feb 1, 2010 at 4:41 PM, Shane Harrelson sh...@sqlite.org wrote: I think you should probably ask this question on the System.data.sqlite support forums at http://sqlite.phxsoftware.com/forums I'm not familiar with enough with their ADO .NET implementation to answer your question. -Shane On Sun, Jan 31, 2010 at 11:54 AM, Sylvain Pointeau sylvain.point...@gmail.com wrote: Hi all, I am using System.data.sqlite from my .NET project, but I am planning to access my db from C/C++ and C# / C++/CI The point is that I want an encryted database, and I plan to use the extension from http://www.hwaci.com/sw/sqlite/prosupport.html#crypto how to use / implement this exension in system.data.sqlite? or are they compatible both? thank you in advance for your help. Best regards, Sylvain ___ 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 ___ 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] any command to find last rowid in a table
Assuming you have a table with the following schema:
CREATE TABLE t1 (
id INTEGER PRIMARY KEY AUTOINCREMENT,
col2 REAL,
col3 TEXT
);
your C code *could* look something like the following:
sqlite3_exec(db, INSERT INTO t1 (col2,col3) VALUES (1.0,'row one');,
0, 0, 0);
rowid = sqlite3_last_insert_rowid(db);
HTH.
-Shane
On Mon, Feb 8, 2010 at 2:59 PM, Vasanta vtan...@gmail.com wrote:
I understand SELECT last_insert_rowid() gives rowird, but I have to call
these SQL statements in C language code, for that I have to sqlite3_prepare
and sqlite3_step() calls, I am looking for sample of sqlite3_step, how that
return the rowid, can I call like this:
int rowid;
sqlite3_stmt *pStmt;
sqlite3(pDB, SELECT last_insert_rowid(), -1, pStmt, 0);
rowid = sqlite3_step(pStmt);
On Mon, Feb 8, 2010 at 2:36 PM, Kees Nuyt k.n...@zonnet.nl wrote:
On Mon, 8 Feb 2010 11:44:39 -0500, Vasanta
vtan...@gmail.com wrote:
I tried to use this function call
sqlite3_last_insert_rowid()
http://www.sqlite.org/c3ref/last_insert_rowid.html
calling from C language function, but it always returns zero, any
idea?.
I have valid DB handle.
The function only returns the rowid of the last successful
INSERT statement on the same connection / DB handle. It
tells you which row has been inserted.
Perhaps you expected it to predict which row would be
inserted on the next INSERT statement?
If you need a new ID for every row you insert, don't try to
find out which value to use, but let SQLite do the work.
Sample code:
CREATE TABLE t1 (
id INTEGER PRIMARY KEY AUTOINCREMENT,
col2 REAL,
col3 TEXT
);
BEGIN;
INSERT INTO t1 (col2,col3)
VALUES (julianday('now'),'row one');
SELECT last_insert_rowid();
INSERT INTO t1 (col2,col3)
VALUES (julianday('now'),'row two');
SELECT last_insert_rowid();
INSERT INTO t1 (col2,col3)
VALUES (julianday('now'),'row three');
SELECT last_insert_rowid();
COMMIT;
Read http://www.sqlite.org/c3ref/last_insert_rowid.html
again for more details.
--
( Kees Nuyt
)
c[_]
___
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] Bug Report: DateTime incorrect for Windows
I was unable to duplicate your error with the CLI and 3.6.22 (compiled with
cygwin\gcc or msvc):
SQLite version 3.6.22
Enter .help for instructions
Enter SQL statements terminated with a ;
sqlite SELECT datetime('now') as NOW;
2010-02-05 16:33:50
sqlite SELECT datetime('now','utc') as UTC;
2010-02-05 21:33:50
sqlite SELECT datetime('now','localtime') as MST;
2010-02-05 11:33:50
The date/time functions are described at
http://www.sqlite.org/lang_datefunc.html
It states that the 'utc assumes that the string to its left is in the
local timezone and adjusts that string to be in UTC' so I think at least the
results for datetime('now','utc') are consistent.
-Shane
On Fri, Feb 5, 2010 at 1:04 AM, 4ean...@engineer.com wrote:
Hello,
I'm compiling the 3.6.21 amalgamation with Borland command-line compiler
version 5.2 for Windows XP.
My compilation command is bcc32.exe -5 -H- -O2 -RT- -a8 -x- maintest.c
sqlite3.c
where maintest.c is similar to the
http://www.sqlite.org/quickstart.htmlsample C code.
The 3 SQL statements executed in maintest.c are:
SELECT datetime('now') as NOW;
SELECT datetime('now','utc') as UTC;
SELECT datetime('now','localtime') as MST;
Current local-time is 22:50 MST.
The results are:
NOW = 1601-01-01 00:04:49
UTC = 1601-01-01 07:04:49
MST = 1601-01-01 17:04:49
These are not as expected.
If I adjust function winCurrentTime() to use sqlite3_uint64 instead of
sqlite3_int64 then the math for system time works out correctly.
I did this by adding option -DSQLITE_FIX_WINTIME=1 to my compiler command
line and code modifications as show below.
Here are the results with unsigned int64:
NOW = 2010-02-05 05:51:05
UTC = 2010-02-05 12:51:05
MST = 2010-02-04 22:51:05
My compiler's defines are:
_INTEGRAL_MAX_BITS
__TLS__
__FLAT__
_Windows
__BORLANDC__
__CDECL__
__CONSOLE__
_MT
__CGVER__
__MT__
__BCOPT__
_CPPUNWIND
__TURBO__
_WIN32
_M_IX86
__WIN32__
Here is the code inserted immediately before winCurrentTime() function:
#if defined(SQLITE_FIX_WINTIME) SQLITE_FIX_WINTIME
#define sqlite3_int64 sqlite3_uint64
#endif
Then the code inserted immediately after winCurrentTime() function:
#if defined(SQLITE_FIX_WINTIME) SQLITE_FIX_WINTIME
#undef sqlite3_int64
#endif
___
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] any command to find last rowid in a table
As stated before, in general, you should not specify the ROWID on inserts, but instead, let the database engine choose it for you. This is true of most/all database engines. The syntax you're trying below is not supported. Indeed, even it were, max(ROWID) is the maximum ROWID *in use*. Trying to insert another row with the same ROWID will result in a collision. At the very least you would need to do something like SELECT max(ROWID)+1 from EVENTLOG_TBL;, use sqlite_step() to run this query, and bind the ROWID returned here into your insert statement and run that. You can read more on SQLite's ROWID usage at http://www.sqlite.org/search?q=rowid HTH. -Shane On Fri, Feb 5, 2010 at 3:22 PM, Vasanta vtan...@gmail.com wrote: This is my actual string, still not working: const char *replaceSql = INSERT INTO EVENTLOG_TBL \ (_ROWID_, component, facilityId, logLevel,\ textMessage, binMessage) VALUES(?,?,?,?,?,?); SELECT max(ROWID) from EVENTLOG_TBL; On Fri, Feb 5, 2010 at 3:05 PM, Vasanta vtan...@gmail.com wrote: Thanks jay. Can I combine like this: INSERT INTO trends(UnitID,HeureTrends,DateTrends) VALUES(?,?,?);SELECT max(ROWID) FROM table-name; or INSERT INTO trends(UnitID,HeureTrends,DateTrends) VALUES(?,?,?);SELECT last_insert_rowid() AS [ID]; On Fri, Feb 5, 2010 at 2:49 PM, Jay A. Kreibich j...@kreibi.ch wrote: On Fri, Feb 05, 2010 at 02:28:33PM -0500, Vasanta scratched on the wall: command SELECT rowid from table-name; gives all rows from 1 to 100 for total 100 rows, any command to get last rowid?. I need insert from last rowid onwards (if table already 100 records, need to insert from 101 onwards) SELECT max(ROWID) FROM table-name; You shouldn't be setting ROWIDs manually, however. Just insert the row and let SQLite pick the ROWID. If you have a ROWID alias in the form of an INTEGER PRIMARY KEY, in most cases you should still just let SQLite pick the value, possibly with AUTOINCREMENT. -j -- Jay A. Kreibich J A Y @ K R E I B I.C H Our opponent is an alien starship packed with atomic bombs. We have a protractor. I'll go home and see if I can scrounge up a ruler and a piece of string. --from Anathem by Neal Stephenson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] system.data.sqlite encryption
Yes. If you're willing to compile (and possibly modify) the system.data.sqlite libraries for yourself, it should work. -Shane On Fri, Feb 5, 2010 at 5:05 AM, Sylvain Pointeau sylvain.point...@gmail.com wrote: I posted on this forum http://sqlite.phxsoftware.com/forums/p/2170/8904.aspx The answer was: I'm afraid not. This is an open-source project, and the encryption Dr. Hipp sells is not open source. but as the license of system.data.sqlite is Released to the public domain, use at your own risk! I think I can integrate the encrypted sqlite lib, that I will buy, into system.data.sqlite. I would just like to have a bit of guidance... Best regards, Sylvain On Mon, Feb 1, 2010 at 4:41 PM, Shane Harrelson sh...@sqlite.org wrote: I think you should probably ask this question on the System.data.sqlite support forums at http://sqlite.phxsoftware.com/forums I'm not familiar with enough with their ADO .NET implementation to answer your question. -Shane On Sun, Jan 31, 2010 at 11:54 AM, Sylvain Pointeau sylvain.point...@gmail.com wrote: Hi all, I am using System.data.sqlite from my .NET project, but I am planning to access my db from C/C++ and C# / C++/CI The point is that I want an encryted database, and I plan to use the extension from http://www.hwaci.com/sw/sqlite/prosupport.html#crypto how to use / implement this exension in system.data.sqlite? or are they compatible both? thank you in advance for your help. Best regards, Sylvain ___ 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 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] journal files
Actually, I believe the entire 28 byte header is zeroed, not just the first 4 bytes. See the zeroJournalHdr() function in pager.c for details. -Shane On Tue, Feb 2, 2010 at 7:09 AM, Pavel Ivanov paiva...@gmail.com wrote: What do you want to see in journal files? You can execute 'PRAGMA journal_mode = persist' and all information in journal file except first 4 bytes will be left on disk for you. Is it enough? Pavel On Tue, Feb 2, 2010 at 7:00 AM, rishabh rishabh.bits...@gmail.com wrote: hey, I am coding for an application wherein i need to check the journal files as in i dont want it to get deleted after the commit. how to go about it? where in the Sqlite3.c code can i edit it. also, is it possible to customize the sqlite code for the journal file a bit as per my needs? thanx -- View this message in context: http://old.nabble.com/journal-files-tp27419280p27419280.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 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] system.data.sqlite encryption
I think you should probably ask this question on the System.data.sqlite support forums at http://sqlite.phxsoftware.com/forums I'm not familiar with enough with their ADO .NET implementation to answer your question. -Shane On Sun, Jan 31, 2010 at 11:54 AM, Sylvain Pointeau sylvain.point...@gmail.com wrote: Hi all, I am using System.data.sqlite from my .NET project, but I am planning to access my db from C/C++ and C# / C++/CI The point is that I want an encryted database, and I plan to use the extension from http://www.hwaci.com/sw/sqlite/prosupport.html#crypto how to use / implement this exension in system.data.sqlite? or are they compatible both? thank you in advance for your help. Best regards, Sylvain ___ 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] SQL Crash with sqlite 3.6.22 commandline
Can you verify that changes for ticket http://www.sqlite.org/src/info/1258875e07 checked-in yesterday resolve your issue? Thanks. -Shane On Fri, Jan 22, 2010 at 9:51 AM, ve3meo holden_fam...@sympatico.ca wrote: I just discovered that attachments can be sent through this newsgroup so I have attached a small database with which you should be able to reproduce the problem. The one table in it has a field collated RMNOCASE. The following query produces these results in three different versions of sqlite: 3.5.4 works perfectly 3.6.17 gracefully reports an error - missing RMNOCASE collation 3.6.21 crash SELECT Name COLLATE NOCASE FROM AddressTable WHERE Name LIKE '%_'; 3.5.4 carried the COLLATE NOCASE override at the beginning of the SELECT through to the comparison in the WHEN. The later ones do not, and they 'progress' from reporting an error to a crash. This regression renders queries, on databases having collations unavailable to the sqlite in use, that were developed on older versions of sqlite problematic when run from newer versions. Regards, Tom D. Richard Hipp d...@hwaci.com wrote in message news:41371dfd-279f-429d-9186-476efb63e...@hwaci.com... I am unable to reproduce this problem. Using the script below, with RMNOCASE changed to just NOCASE, everything works fine on the SQLite command-line shell on the website on Linux. I also tried various other versions of SQLite with the same result. On Jan 21, 2010, at 8:00 AM, Hub Dog wrote: I hava a table. The table schema is CREATE TABLE AddressTable ( AddressID INTEGER PRIMARY KEY , AddressType INTEGER , Name TEXT COLLATE RMNOCASE , Street1 TEXT , Street2 TEXT , City TEXT , State TEXT , Zip TEXT , Country TEXT , Phone1 TEXT , Phone2 TEXT , Fax TEXT , Email TEXT , URL TEXT , Latitude INTEGER , Longitude INTEGER , Note BLOB ) ;. if I execute following sql to query data , the sqlite 3.6.22 command line downloaded from www.sqlite.org will crash. SELECT Adr.Name COLLATE NOCASE AS AddressName FROM AddressTable AS Adr WHERE Adr.Name LIKE '%_'. if I change the Adr.Name to AddressName , the sql execute result is ok. SELECT Adr.Name COLLATE NOCASE AS AddressName FROM AddressTable AS Adr WHERE AddressName LIKE '%_' ; it seems the crash was related with the collate RMNOCASE of AddressTable table's field Name. in default sqlite command line, there is no rmnocase collation. so I mapped it to the default nocase collation. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users D. Richard Hipp d...@hwaci.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 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] grabbing row contents?
Instead of using sqlite3_exec() and a callback, use sqlite3_prepare_v2() on your statement and then run it with sqlite3_step() in a loop (there will be one iteration be row of the result set). sqlite3_step() will return something other than SQLITE_ROW when it's done. When it returns SQLITE_ROW, you can then use the sqlite3_column_*() apis to look at the result row. HTH. -Shane On Fri, Jan 22, 2010 at 10:57 AM, gary clark burslem2...@yahoo.com wrote: Hiya, Got a quick question. I perform the following on a table: select * from table How do I know when the select has completed all its transactions? sqlite3_exec(db,query.c_str(),callback, this, db_err); I'm using the above statement,the callback is getting called as expected. Thanks, garyc ___ 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] BUG: .echo ON in command line tool doesn't echo all statements
On Fri, Jan 8, 2010 at 5:17 AM, Kees Nuyt k.n...@zonnet.nl wrote: (Repeated with the proper BUG: subject) On Mon, 04 Jan 2010 19:55:13 +0100, Kees Nuyt k.n...@zonnet.nl wrote: Between versions 3.6.19 and 3.6.20 something has changed which causes the command line tool to ignore the .echo ON command for some statements. CREATE and INSERT statements aren't echoed anymore, but SELECT statements are echoed correctly. Needless to say that this makes it a bit harder to debug SQL scripts or prepare demo output to help other people. Platform tested: MS Windows (Vista 32-bit). Additional info: sqlite3 command line tool executable for Windows straight from http://www.sqlite.org/download.html Version 3.6.22 has the same bug. Can anyone confirm this behaviour? -- ( Kees Nuyt ) c[_] Confirmed. Fixed here: http://www.sqlite.org/src/vinfo/7080ae3bc3 Thanks for the report. -Shane ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] BUG Report -- schema.test does not check for authorization in build
Thanks for the reports on the TCL test cases. I think
http://www.sqlite.org/src/vinfo/97f8a886b6 should correct everything you
reported.
On Tue, Dec 29, 2009 at 1:02 PM, Noah Hart n...@lipmantpa.com wrote:
Test schema-13.1 fails with
Error: {authorization not available in this build}
Test needs to be bracket with
ifcapable auth {
do_test schema-13.1 {
set S [sqlite3_prepare_v2 db SELECT * FROM sqlite_master -1 dummy]
db function hello hello
db function hello {}
db auth auth
db auth fails because tclsqlite.c has
#ifdef SQLITE_OMIT_AUTHORIZATION
Tcl_AppendResult(interp, authorization not available in this
build, 0);
return TCL_ERROR;
#else
Regards,
Noah Hart
CONFIDENTIALITY NOTICE:
This message may contain confidential and/or privileged information. If you
are not the addressee or authorized to receive this for the addressee, you
must not use, copy, disclose, or take any action based on this message or
any information herein. If you have received this message in error, please
advise the sender immediately by reply e-mail and delete this message. Thank
you for your cooperation.
___
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_complete always returning 1?
Thanks for the report. You're right that it was indeed inconsistent with the stated requirements. I've checked in a fix here: http://www.sqlite.org/src/vinfo/76eca7295cf7df4bef013af6c8c37251300cd383 We're still evaluating whether to keep the code change or fall back to editing the requirement, as the issue has been around for a long time and this change affects the existing operation of the interface. -Shane On Thu, Dec 17, 2009 at 10:39 AM, Nicolas Rivera nicolas.riv...@ac4s.comwrote: If I am doing something wrong, does anyone have an idea of what it could be? It doesn't sound like it. That's all I was looking for. Thanks! Nick ___ 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] Putting images into SQLite.
As others have said, there are lots of ways to store the image data directly in the DB with BLOBs, encoding, etc. Alternatively, you could store the pics separate from the DB and just store the path to the pic file in the DB. -Shane On Sun, Dec 6, 2009 at 8:35 PM, Ted Rolle, Jr. ster...@gmail.com wrote: From what I read, it is necessary to have a programmatic interface to put images into a database. True? --- 3.14159265358979323846264338327950 Let the spirit of pi spread 2884197169399375105820974944592307 all around the world! 8164062862089986280348253421170679 http://pi314.at PI VOBISCUM! ___ 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] substr in sqlite3 3.6.20
On Fri, Nov 20, 2009 at 2:30 PM, priimak prii...@stanford.edu wrote: Hi. What happened to substr function. I upgraded from 3.6.7 to 3.6.20 and found following difference. in 3.6.7 $ sqlite3 a.db sqlite create table X ( v text not null ); sqlite insert into X ( v ) values ( 123456789 ); sqlite select substr( v, 0, 5 ) from X; 12345 in 3.6.20 sqlite create table X ( v text not null ); sqlite insert into X ( v ) values ( 123456789 ); sqlite select substr( v, 0, 5 ) from X; 1234 You can see that they behave differently with 3.6.7 doing the right thing. I did not find any mentioning of that here http://www.sqlite.org/changes.html If that is a bug, it *is* a major one! It's not a bug. substr() is suppose to be 1 based. Older versions behaved incorrectly. It was fixed by this ticket: http://www.sqlite.org/cvstrac/tktview?tn=3628 -Shane -- Dmitri Priimak ___ 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] bad data in a database file or bug?
in one case you do: select count(*) from feeds where _id = '0'; in the other you do: select count(*) from feeds where _id = 0; --- note the missing quotes this shouldn't make a difference (since SQLite is typeless), but I wonder if it is in this case. what is the type of _id? can you run the query that DRH suggested? SELECT _id, typeof(_id) FROM feeds; -Shane On Tue, Nov 17, 2009 at 8:57 PM, Vasu Nori vn...@google.com wrote: if I vacuum it, database file sems to have correct data. sqlite vacuum ... ; sqlite select count(*) from feeds where _id = '0'; count(*) -- 1 I can't tell if this is a bug in sqlite3 or if the database file is corrupt but sqlite3 can't recognize it when I do pragma integrity_check. On Tue, Nov 17, 2009 at 5:09 PM, Vasu Nori vn...@google.com wrote: hi all attached is a database file with a strange behavior. it has a table feeds. has the following data (just selected 2 columns for discussion purpose) sqlite select _id, feed from feeds; _id feed -- - 1 http://www.google.com/calendar/feeds/1acpbk5jj328tc9up029ml6nog%40group.calendar.google.com/private/full 2 http://www.google.com/calendar/feeds/1hpeekrpepcq41if0r6cnrf3nc%40group.calendar.google.com/private/full 3 http://www.google.com/calendar/feeds/43p69tkmn5k339b10e9f1rrqgo%40group.calendar.google.com/private/full 4 http://www.google.com/calendar/feeds/9rdtjbibdsve143hvcbrf2ru1c%40group.calendar.google.com/private/full 5 http://www.google.com/calendar/feeds/fpbubjo06bb9t86o2bm9c3456c%40group.calendar.google.com/private/full 6 http://www.google.com/calendar/feeds/m76b3e2ssuos4qm6qijb7o2pak%40group.calendar.google.com/private/full 7 http://www.google.com/calendar/feeds/tijtufmt5st0m4rnvjhug7f8r8%40group.calendar.google.com/private/full 8 http://www.google.com/calendar/feeds/tomastaylor%40gmail.com/private/full 9 http://www.google.com/calendar/feeds/ttevp5oljm14v1m176spijqiag%40group.calendar.google.com/private/full 10 http://www.google.com/calendar/feeds/usa%40holiday.calendar.google.com/private/full 11 http://www.google.com/calendar/feeds/uu7524qmpvorujaq6hnfnvfrtg%40group.calendar.google.com/private/full 0 https://mail.google.com/mail/g/?client=1256578631218 13 http://www.google.com/m8/feeds/groups/tomastay...@gmail.com/base2_property-android?v=3.0 database file doesn't 'seem' to be corrupt sqlite pragma integrity_check; integrity_check --- ok but can't select the row _id = 0 sqlite select count(*) from feeds where _id = 0; count(*) -- 0 what gives? any insights into this interesting behavior? thanks ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Why does LIKE operator affect order of query plan?
On Sun, Nov 15, 2009 at 11:41 AM, P Kishor punk.k...@gmail.com wrote: On Sun, Nov 15, 2009 at 10:39 AM, Tim Romano tim.rom...@yahoo.com wrote: I have a query with joined inline views that runs in about 100ms against a 4 million row table joined to a 275,000 row table. Not bad, SQLite :-) But when I use the LIKE operator instead of the = operator, the order of the query plan changes, though the same indexes are involved, and the query takes 40 seconds. I'm trying to figure out what, if anything, I can do to guide SQLite here. In broad terms, what is it about the use of the LIKE operator that causes SQLite to re-order the plan, and is there any way to guide? LIKE doesn't use indexes, although there are tricks that these SQL gurus will probably tell that could help you with workarounds. LIKE does a full scan. There are certain conditions in which a LIKE (or GLOB) term will be transformed by the query planner into an equivalent expression to allow the use of indices. Please see http://www.sqlite.org/optoverview.html for a more in-depth discussion. And what does the from column in the explain plan results refer to? Are the values the tables/relations in the query statement? If so, how are they mapped? In order of appearance in the statement, so that 0 is the first table mentioned in the statement? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Fwd: SQLite 3.6.20 problem
Fixed in check-in http://www.sqlite.org/src/info/8097c64acf On Mon, Nov 9, 2009 at 10:52 AM, D. Richard Hipp d...@hwaci.com wrote: Begin forwarded message: From: Ken Zalewski ken...@nycap.rr.com Date: November 9, 2009 9:45:22 AM EST To: d...@hwaci.com Subject: SQLite 3.6.20 problem Rich, It seems that the -batch option in 3.6.20 has stopped working. If I run sqlite3 -batch blah.db create_tables.sql the blah.db database is never created. In versions up to and including 3.6.19, the blah.db file would properly be created. In fact, the -batch option, even when used with an already-existing database, seems to have no effect. If I run sqlite3 -batch existing.db create_tables.sql, no tables are actually created. This seems to be a major change. Does -batch serve any purpose any longer? D. Richard Hipp d...@hwaci.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Conflict with SQLite ODBC Driver and MS Office 2007 ?
I know of no conflicts. I regularly test against SQL Server 2005, MySQL, OracleXE, and SQLite all via ODBC interfaces (specifically Christian Werner's ODBC interface for SQLite). The various ODBC interfaces all play happily. HTH -Shane On Wed, Oct 14, 2009 at 4:15 PM, Jack Ort jack@gmail.com wrote: Hello! Not sure where to ask this question, so I apologize if this is not appropriate. I want to use SQLite as the basis for a new project where I work. I'm new to SQLite - this would be my first use of it beyond some simple test applications. Plan to use REBOL to develop a GUI frontend. For reporting, I thought I might use MS Access called by REBOL code to provide canned reports against the SQLite database. I believe I need to use the SQLite ODBC driver (http://www.ch-werner.de/sqliteodbc/) for Access to link to the SQLite db. Now my question: my IT Manager is witholding approval of using SQLite because he thinks he's heard of a conflict between the SQLite ODBC driver and the SQL Server 2005 stuff that gets installed as part of a MS Office 2007 installation. (Some users have Office 2007 - I have Office 2003 so I cannot test.) I suspect someone meant to refer to a conflict with SQL Server Express instead of SQLite. I cannot find anything in Google searches. Does anyone here know of any conflicts I should be concerned about? This would be in a XP SP2 environment. Thanks in advance! -Jack ___ 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] Sqlite testing
I'd like to help, but you've not provided any relevant information. Obviously, in order to test, you're going to need to resolve your build failure. SQLite version, compiler, compiler options, compiler output, etc. would all be useful towards this end. -Shane On Fri, Sep 25, 2009 at 12:17 AM, ydlu yudian...@gmail.com wrote: I create the Windows CE 6.0 OS image, so the Console command windows was included. I try to build SQLite3.exe but failed! So how to test SQLite3 on WIndows CE environment on every build? Thanks On Wed, Sep 23, 2009 at 8:03 AM, Shane Harrelson sh...@sqlite.org wrote: On Wed, Sep 23, 2009 at 12:22 AM, Roger Binns rog...@rogerbinns.com wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 ydlu wrote: I am Windows CE software developer, so I am really, really want to learn how you built and test sqlite3.exe in Windows CE platform. so I can run a console command line in Windows CE enivroment. http://www.sqlite.org/testing.html Most likely TH3 is used. Roger As Roger guessed, we do indeed use TH3 (http://www.sqlite.org/th3.html). Most Windows CE platforms don't include a console, so the standard TCL based test fixture would be tricky to port (PocketConsole would be would a tool you could use, but it looks to be no longer available). Additionally, providing access to the 500+ test files on the test device would be cumbersome, as well as having sufficient resources (memory, etc.) on the device to execute all the tests. HTH. -Shane ___ 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] Sqlite testing
I'd like to help, but you've not provided any relevant information. Obviously, in order to test, you're going to need to resolve your build failure. SQLite version, compiler, compiler options, compiler output, etc. would all be useful towards this end. -Shane On Fri, Sep 25, 2009 at 12:17 AM, ydlu yudian...@gmail.com wrote: I create the Windows CE 6.0 OS image, so the Console command windows was included. I try to build SQLite3.exe but failed! So how to test SQLite3 on WIndows CE environment on every build? Thanks On Wed, Sep 23, 2009 at 8:03 AM, Shane Harrelson sh...@sqlite.org wrote: On Wed, Sep 23, 2009 at 12:22 AM, Roger Binns rog...@rogerbinns.com wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 ydlu wrote: I am Windows CE software developer, so I am really, really want to learn how you built and test sqlite3.exe in Windows CE platform. so I can run a console command line in Windows CE enivroment. http://www.sqlite.org/testing.html Most likely TH3 is used. Roger As Roger guessed, we do indeed use TH3 (http://www.sqlite.org/th3.html). Most Windows CE platforms don't include a console, so the standard TCL based test fixture would be tricky to port (PocketConsole would be would a tool you could use, but it looks to be no longer available). Additionally, providing access to the 500+ test files on the test device would be cumbersome, as well as having sufficient resources (memory, etc.) on the device to execute all the tests. HTH. -Shane ___ 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] Sqlite testing
On Wed, Sep 23, 2009 at 12:22 AM, Roger Binns rog...@rogerbinns.com wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 ydlu wrote: I am Windows CE software developer, so I am really, really want to learn how you built and test sqlite3.exe in Windows CE platform. so I can run a console command line in Windows CE enivroment. http://www.sqlite.org/testing.html Most likely TH3 is used. Roger As Roger guessed, we do indeed use TH3 (http://www.sqlite.org/th3.html). Most Windows CE platforms don't include a console, so the standard TCL based test fixture would be tricky to port (PocketConsole would be would a tool you could use, but it looks to be no longer available). Additionally, providing access to the 500+ test files on the test device would be cumbersome, as well as having sufficient resources (memory, etc.) on the device to execute all the tests. HTH. -Shane ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite3_exec fails on arm
I wasn't able to duplicate this with my ARM system. Can you provide more details? or was the issue DRH pointed out above your problem? On Wed, Sep 9, 2009 at 7:02 AM, gprand g20...@aon.at wrote: Hi, I can confirm this behavoir. Now, after 2 days debugging and unsuccessful searching around I have found your posting. It drives me crazy, but I can not locate the problem. Exactly the same code works, compiled for X86, and fails, compiled for ARM. All machine depending configuration is omitted. My poor debugging results are: * 100 Bytes be read from the file. * SQLite attempts to parse the sql text * SQLite creates sqlite_master table * Now it attempts to parse the sql statement again * Now SQLite crashes in ARM mode. In X86 mode 1024 bytes be read and all works fine. Why? Gottfried Sorry for poor english. Hi, I am using sqlite3.6.17 on arm_v7 . sqlite3_Exec fails giving the following error sqlite3_exec malformed database schema error : unrecognized token '. I am able to read the database manually using select commands. Executing the same sqlite3_exec statements on x86 machine on the same db file does not give any error . Any help on this issue will be welcome, -- View this message in context: http://www.nabble.com/sqlite3_exec-fails-on-arm-tp25293839p25362643.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite Windows Mobile 6 Edition
What compiler and compile time options are you using? On Fri, Sep 4, 2009 at 2:00 PM, Dominique Jannmicrotechno...@yandex.com wrote: Good night. I have a question about SQLite, very interesting project, how compiling SQLite sources for Windows Mobile 6. I couldn't compile project for this platform because have a many errors in compiling process. If you can hel me with this, i will be very thankful. ___ 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] AIX test failures for 3.6.17
On Tue, Aug 18, 2009 at 3:28 PM, Kenkennethinbox-sql...@yahoo.com wrote: Running make test resulted in the following failures on aix 5.3 Note that the CFLAGS=-DSQLITE_DISABLE_DIRSYNC was set for the make. 14 errors out of 40926 tests Failures on these tests: backup2-10 io-4.1 io-4.2.3 nan-1.1.2 nan-1.1.3 nan-1.1.4 nan-1.1.5 nan-1.1.5 nan-4.7 nan-4.8 nan-4.14 nan-4.15 nan-4.16 nan-4.17 Any suggestions or reason why the io test would fail? io-3.3... Ok io-4.1... Expected: [3] Got: [2] io-4.2.1... Ok io-4.2.2... Ok io-4.2.3... Expected: [3] Got: [2] io-4.3.1... Ok io-4.3.2... Ok Thanks, Ken Most Unix's support fsync()'ing directories, and the expected sync numbers in io.test assume that if you're on a Unix platform, you're going to be calling fsync() on the directory. On AIX, which typically doesn't support fsync() on directories (hence, your compiler option -DSQLITE_DISABLE_DIRSYNC), the sync counts are different then what the tests were expecting. I've checked in an update to io.test which I think should correct the test for you. HTH. -Shane ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Multiple indexes in SQLite, and selecting which to use
On Mon, Aug 17, 2009 at 3:15 AM, Dan Kennedy danielk1...@gmail.com wrote: The INDEXED BY feature was introduced to address concerns that SQLite might suddenly start using a different plan for a query in the field than it did in the office during testing. Either because somebody ran ANALYZE, or because the SQLite version was upgraded. In this situation, some users consider it better to throw an exception than to run the query with a different, possibly slower, plan. Confusion reigns supreme. Your second last paragraph says (about your last scenario) that it uses index i1 instead of the apparently better index i2 -- no exception throwing. Your last paragraph indicates that in this case an exception would be thrown. I guess I got that wrong then. Said users considered it better to throw an error if the index that the author of the SQL query expected it to use had been removed or radically altered. Dan. Sorry for muddying the waters. I was just trying to make clear that INDEXED BY isn't intended to be used as a tuning mechanism for index selection. My statement should have been clearer. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Does PRAGMA synchronous=OFF ensure that no synching is done for the entire session?
On Mon, Aug 17, 2009 at 11:53 AM, D. Richard Hipp d...@hwaci.com wrote: On Aug 17, 2009, at 11:41 AM, Matt Sergeant wrote: Kernels will fflush when a file handle is closed Not according to Ted Ts'o (creator of the Ext2/3/4 filesystems). See, for example, the extensive discussions of this at http://thunk.org/tytso/blog/2009/03/12/delayed-allocation-and-the-zero-length-file-problem/ http://thunk.org/tytso/blog/2009/03/15/dont-fear-the-fsync/ Ted says that it is widely believed among programmers that close() will sync a file, but in fact nothing in POSIX requires this and in fact Linux does not do it. Some hacks were added to ext4 in the 2.6.30 kernel release to mitigate the damage following a power loss when programs fail to fsync() prior to close(). But everybody agrees those changes are an ugly hack. In POSIX, the bottom line is this: The *only* way to force data to oxide is to call sync() or fsync(). Some kernels and/or some filesystems might sync at other times, but it is not something that you can rely on. D. Richard Hipp d...@hwaci.com To the original question though, with PRAGMA synchronous=OFF, SQLite will NOT do explicit fsync()'s. A exception to this occurs with attached DB's and a transaction; when the transaction is committed and the master journal is deleted, SQLite fsyncs the directory that contained the journal to ensure the delete is flushed. The only way around this fsync() is to compile with SQLITE_DISABLE_DIRSYNC. This might be changed in the future. -Shane ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Multiple indexes in SQLite, and selecting which to use
No. It only collects/updates stats when you explicitly call the ANALYZE. On Sat, Aug 15, 2009 at 2:48 AM, Jim Showalter j...@jimandlisa.com wrote: It doesn't collect those statistics automatically, as part of query plan optimization? - Original Message - From: Dan Kennedy danielk1...@gmail.com To: General Discussion of SQLite Database sqlite-users@sqlite.org Sent: Friday, August 14, 2009 11:37 PM Subject: Re: [sqlite] Multiple indexes in SQLite, and selecting which to use On Aug 15, 2009, at 1:34 PM, Jim Showalter wrote: How will that help him fix this problem, if the problem is that SQLite's query optimizer is selecting a suboptimal index to use, and there is no way to specify which index to use? The statistics collected by the ANALYZE command will be used by SQLite to (hopefully) select the optimal index. ___ 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] Multiple indexes in SQLite, and selecting which to use
INDEXED BY doesn't allow you to specify which index to use. It just causes the query to fail if SQLite thinks it should use an index different then the one specified by the INDEXED BY clause. On Sun, Aug 16, 2009 at 7:59 PM, His Nerdship shol...@hotmail.com wrote: Hi Pavel, Does INDEXED BY clause work for you? http://www.sqlite.org/lang_select.html The page suggests that INDEXED BY can only be used in single-table queries. The report queries also join other smaller tables, some of them from an attached database, so I don't think this will work. However, you have given me an idea - maybe I could break it down to two queries. Use the INDEX BY on the big table to get a subset table, and do another SELECT on the subset. Thanks. Sholto (His Nerdship) Is there any way the code can 'suggest' SQLite use a certain index? Pavel -- View this message in context: http://www.nabble.com/Multiple-indexes-in-SQLite%2C-and-selecting-which-to-use-tp24981846p24999340.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] DELETE ststement not working as expected
I believe sqlite3_bind_int64() takes a signed int for the value to bind. You state you are passing it a large unsigned int. If the unsigned value is large enough, it will appear to be a negative signed int. HTH. -Shane ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Topics 1 - Re: sqllogictest tool - please help :-)
What have you been able to do so far? Have you been able to build sqllogictest (SLT)? Were you able to retrieve some of the test files for SLT? Have you gotten MySQL installed? Are you able to use MySQL via an ODBC interface? (SLT uses an ODBC interface to non-SQLite databases.) On Wed, Jun 10, 2009 at 3:27 AM, tsachi ofir tsac...@gmail.com wrote: Hi, we are working with SQLite in our company. we need a tool that check if SQL returns the correct answer and to compare with MySQL. I got a new job and its my first assignment ( to start working on sqlogictest) , and I don't understand much about sqllogictest. Please help. Tsachi -- Message: 1 Date: Tue, 9 Jun 2009 16:12:30 -0400 From: D. Richard Hipp d...@hwaci.com Subject: Re: [sqlite] sqllogictest tool - please help :-) To: General Discussion of SQLite Database sqlite-users@sqlite.org Message-ID: 511286c3-5b52-4f01-8f74-3448d23d8...@hwaci.com Content-Type: text/plain; charset=US-ASCII; format=flowed; delsp=yes On Jun 9, 2009, at 10:38 AM, tsachi ofir wrote: Hi , I wont to use the sqllogictest program. Let's start with the basics. Why do you think you want to use this program? What do you think it will accomplish for you? I'm using SQLite. I have read the About Sqllogictest and didn't fully understand how to work with it. Can you please help on how I can use it? I will be very grateful for your help. - Where are the files of the program? is it those three files? 1. sqllogictest.mk 2. sqllogictest.h 3. sqllogictest.c - How can I install it? - Just to migrate the files to which folder? Thanks for your help. Tsachi Ofir ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users D. Richard Hipp d...@hwaci.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite Version 2.1
The 2.8 version should be able to read the 2.1... although it will try to convert the format first, so make a backup copy of the original. On Mon, Jun 8, 2009 at 12:35 PM, Marcus Haßmannmar...@hassmann.eu wrote: The empty database in an example. I have to do an investigation for a non empty database... Marcus Haßmann Shane Harrelson schrieb: http://www.sqlite.org/sqlite-2.8.17.tar.gz I think is the oldest version still available from the SQLite website. BTW, I'm not certain what kind of forensic evidence you can get from your db if it's empty as you say. HTH. -Shane On Sun, Jun 7, 2009 at 5:12 PM, Marcus Haßmannmar...@hassmann.eu wrote: I tested BearShare Lite Version 5.2.5.1 The database is located in C:\Program Files\BearShare\db and it is named library.db. The header of the empty database is ** This file contains an SQLite 2.1 database ** Marcus Haßmann Simon Slavin schrieb: On 7 Jun 2009, at 8:22pm, Marcus Haßmann wrote: I tested all versions of sqlite browser already - without success. Are you certain that this really is a sqlite database, and not just a file with the right extension ? Does it start with the right header ? Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-user ___ 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 -- Mit freundlichen Grüßen Marcus Haßmann -- Hassmann-Software In der Pottaschdell 31 66333 Völklingen ICQ: 192 982 963 Tel.: 06898 / 49 32 30 Mobil: 0176 / 400 53 54 5 Email: i...@hassmann-software.de Web: http://www.hassmann-software.de XING: http://www.xing.com/profile/Marcus_Hassmann ___ 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] SQLite Version 2.1
http://www.sqlite.org/sqlite-2.8.17.tar.gz I think is the oldest version still available from the SQLite website. BTW, I'm not certain what kind of forensic evidence you can get from your db if it's empty as you say. HTH. -Shane On Sun, Jun 7, 2009 at 5:12 PM, Marcus Haßmannmar...@hassmann.eu wrote: I tested BearShare Lite Version 5.2.5.1 The database is located in C:\Program Files\BearShare\db and it is named library.db. The header of the empty database is ** This file contains an SQLite 2.1 database ** Marcus Haßmann Simon Slavin schrieb: On 7 Jun 2009, at 8:22pm, Marcus Haßmann wrote: I tested all versions of sqlite browser already - without success. Are you certain that this really is a sqlite database, and not just a file with the right extension ? Does it start with the right header ? Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-user ___ 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] most efficient way to get 1st row
Additionally, it's important to note that the LIMIT/OFFSET clause is not standard SQL, and although it is supported by SQLite, and many other SQL engines, there are some that do NOT support it, most notably Microsoft SQL Server. HTH. -Shane On Tue, May 19, 2009 at 2:23 PM, Sam Carleton scarle...@gmail.com wrote: Kees Nuyt wrote: Imagine a SELECT with an ORDER BY which makes SQLite sort the resultset before it can return the first row in the resultset. Need I say more? http://www.sqlite.org/cvstrac/wiki?p=ScrollingCursor Thank you for the link, it is a VERY useful read, VERY useful! Sam ___ 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] most efficient way to get 1st row
We're not talking about transaction control - we're talking about limiting the size of the result set. And LIMIT/OFFSET clauses are not supported by the SQL-92 standard. LIMIT is listed as a reserved word, but that's it. http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt On Wed, May 20, 2009 at 2:47 PM, John Stanton jo...@viacognis.com wrote: What makes you think that transaction control is not part of SQL-92? If a database engine does not support transaction control it is not a full implementation. Transaction control is at the very heart of effective data management. Shane Harrelson wrote: Additionally, it's important to note that the LIMIT/OFFSET clause is not standard SQL, and although it is supported by SQLite, and many other SQL engines, there are some that do NOT support it, most notably Microsoft SQL Server. HTH. -Shane On Tue, May 19, 2009 at 2:23 PM, Sam Carleton scarle...@gmail.com wrote: Kees Nuyt wrote: Imagine a SELECT with an ORDER BY which makes SQLite sort the resultset before it can return the first row in the resultset. Need I say more? http://www.sqlite.org/cvstrac/wiki?p=ScrollingCursor Thank you for the link, it is a VERY useful read, VERY useful! Sam ___ 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 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite crashing on iPhone (or so says Apple)
Version 3.6.13 fixed some potential alignment issues that could occur on SPARC (and potentially other) architectures. I don't know how you or Apple are testing your app, but if you (or they) are using a device emulator for the testing, the emulator might not be testing alignment conditions the same. HTH. -Shane On Tue, Apr 21, 2009 at 11:27 AM, D. Richard Hipp d...@hwaci.com wrote: On Apr 21, 2009, at 11:10 AM, Mark Spiegel wrote: I'm a bit confused by the following: The assign 100K or so to each database connection's lookaside memory allocator using sqlite3_db_config(SQLITE_DBCONFIG_LOOKASIDE, ...) immediately after it is opened. If memory is at a premium, why would you reserve a large amount of it for SQLite's look aside allocator? (It's really a zone allocator.) This SQLite mechanism ostensibly attempts to trade memory for speed. If memory is at a premium, in this case a fixed upper bound, that trade off doesn't seem to make sense. I would think in a case where memory is tight, zero bytes should be reserved. This is a reasonable observation. On the other hand, the lookaside memory allocator (which is just a zone allocator, as you observe) makes a big performance difference. And if you only have a single database connection, it doesn't really matter if the memory goes into lookaside or is in the global heap. If you have multiple database connections, you might get increased memory efficiency by sharing between those two connections - which cannot happen with lookaside. The page cache is going to be the biggest user of memory. The page cache memory will probably be measured in megabytes. Memory used by lookaside is measured in kilobytes. A few dozen KB of additional memory assigned to lookaside won't make that much difference in your overall memory usage, but it will make a difference in performance. So it seems to me to be worth the tradeoff, even if memory is tight. The reason I suggested using sqltie3_db_config() to assign a static buffer for lookaside is so that the lookaside subsystem will not go to the heap to get its (default) 50K allocation. The MEMSYS5 memory allocator is a first-fit power-of-two memory allocator specifically designed to avoid memory fragmentation and hence allow applications to be designed that are guaranteed to never fail a memory allocation. But doing large heap allocations (more than 2K or 4K) tends to defeat the anti-fragmentation properties of MEMSYS5. Hence, we desire to avoid the 50K heap allocation for the initial lookaside buffer. One could, as you observe, achieve the same result by turning lookaside off all together, but then you take a performance hit. D. Richard Hipp d...@hwaci.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] select the first 2 rows
Remember, the order of rows returned from a SELECT, even one with a LIMIT clause, is undefined, so the first 2 rows may not be consistently the same. If you are dependent upon the rows being returned in a particular order (say by rowid), you should include an ORDER BY clause on your SELECT to ensure the rows are returned in the order you require. -Shane On Wed, Apr 1, 2009 at 3:02 PM, Eric Minbiole eminbi...@mavroimaging.comwrote: Hi all, I have a big table and I want only select the first 2 rows. I have tried this : select top 2 from table; but it doesn't work! Any help please. JP Use a LIMIT clause instead of TOP: SELECT * FROM table LIMIT 2; http://www.sqlite.org/lang_select.html ___ 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] SQLite Transaction Rate and speed...
Already there: http://www.sqlite.org/faq.html#q19 On Sun, Mar 8, 2009 at 8:15 AM, Alexey Pechnikov pechni...@mobigroup.ru wrote: Hello! On Saturday 07 March 2009 01:59:13 Roger Binns wrote: A transaction requires two syncs (ie requesting the drive write the data to the metal and not return until it does). On average each sync will take a disk rotation so a 7200rpm drive maxes out at 60 transactions a second. If you require each data update to be physically on the disk then this is your limit no matter what library or storage mechanism you use. That's great description! Can you add this to documentation? I did know the limit experimentally but I didn't can to calculate this. You can also look at alternate storage mechanisms. For example rotating drives can be paired with a battery backed controller, or solid state could be used if the wear leveling and write-erase performance meet your needs. Do you have experimental results of SQLite performance on SSD? Best regards. ___ 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] Visual Studio 2005 Linker Problems - SQLite v3.6.3
The workaround I gave you was overly simplistic. You should take a look at the sample makefiles provided for examples of what compilation options to use. In particular, you may need to define SQLITE_CORE for your files. This flag is used to control the definition of sqlite3_api in the sqlite2ext.h header file. Typically, you do not need to worry about this define, but depending on your build environment and options, you may need to adjust its use. On Tue, Feb 17, 2009 at 9:27 AM, Ian Thomas tho...@ccdc.cam.ac.uk wrote: Hello again, Although the previous suggestion does fix the linker problem for VS2005 (Release and Debug), and for the VS2003 Debug build, I still cannot link the Release build in VS2003. With the 'rtree.c' source file excluded I now get this similar error message: Linking... fts3_tokenizer.obj : error LNK2005: _sqlite3_api already defined in fts3.obj fts3_tokenizer.obj : warning LNK4006: _sqlite3_api already defined in fts3.obj; second definition ignored I am using FTS3 so I cannot disable this optional module. Any ideas please? Thanks again, Ian Ian Thomas wrote: Hi Shane, Thanks very much for the suggestion. I excluded the 'rtree.c' source file from my build configuration and it now links fine (release and debug). I guess there's still an underlying issue but as I don't need the rtree module it doesn't affect me! Thanks again, Ian Shane Harrelson wrote: rtree support is an extension to SQLite and is not needed to use the core functionality. Try leaving the rtree source files out of your builds to see if this corrects your problem. You might also consider using the SQLITE_OMIT_LOAD_EXTENSION option as well. http://www.sqlite.org/compile.html On Mon, Feb 16, 2009 at 9:29 AM, Ian Thomas tho...@ccdc.cam.ac.uk wrote: Hello, I'm trying to compile my own debug build of SQLite v3.6.3 in Visual Studio 2005 so that I can step through a problem I'm having with my application. I have followed the instructions found here: http://www.sqlite.org/cvstrac/wiki?p=HowToCompileWithVsNet Using the standard v3.6.3 source tree (not the amalgamation version) everything compiles fine but fails to link with this error: 1rtree.obj : error LNK2005: _sqlite3_api already defined in fts3_tokenizer.obj 1 Creating library D:\x_mirror\buildman\tools\sqlite-3.6.3\SQLiteVS2005\Debug\SQLiteVS2005.lib and object D:\x_mirror\buildman\tools\sqlite-3.6.3\SQLiteVS2005\Debug\SQLiteVS2005.exp 1D:\x_mirror\buildman\tools\sqlite-3.6.3\SQLiteVS2005\Debug\SQLiteVS2005.dll : fatal error LNK1169: one or more multiply defined symbols found I have only two SQLite preprocessor defines set in my project: SQLITE_ENABLE_COLUMN_METADATA SQLITE_ENABLE_FTS3 I get this error both with and without the SQLITE_ENABLE_FTS3 define. I get the same linker error when trying to build SQLite v3.6.10 and when trying to use Visual Studio 2003 instead of 2005. I'm sure there must be an easy fix for this problem - any advice would be really appreciated. I imagine I could use the /FORCE:MULTIPLE linker option to get past this but I'm reluctant to do so without checking first. Thanks in advance, Ian Thomas LEGAL NOTICE Unless expressly stated otherwise, information contained in this message is confidential. If this message is not intended for you, please inform postmas...@ccdc.cam.ac.uk and delete the message. The Cambridge Crystallographic Data Centre is a company Limited by Guarantee and a Registered Charity. Registered in England No. 2155347 Registered Charity No. 800579 Registered office 12 Union Road, Cambridge CB2 1EZ. ___ 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 -- Ian Thomas Scientific Software Engineer CCDC 12 Union Road Cambridge CB2 1EZ UK Tel - +44 1223 763884 LEGAL NOTICE Unless expressly stated otherwise, information contained in this message is confidential. If this message is not intended for you, please inform postmas...@ccdc.cam.ac.uk and delete the message. The Cambridge Crystallographic Data Centre is a company Limited by Guarantee and a Registered Charity. Registered in England No. 2155347 Registered Charity No. 800579 Registered office 12 Union Road, Cambridge CB2 1EZ. ___ 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] Visual Studio 2005 Linker Problems - SQLite v3.6.3
rtree support is an extension to SQLite and is not needed to use the core functionality. Try leaving the rtree source files out of your builds to see if this corrects your problem. You might also consider using the SQLITE_OMIT_LOAD_EXTENSION option as well. http://www.sqlite.org/compile.html On Mon, Feb 16, 2009 at 9:29 AM, Ian Thomas tho...@ccdc.cam.ac.uk wrote: Hello, I'm trying to compile my own debug build of SQLite v3.6.3 in Visual Studio 2005 so that I can step through a problem I'm having with my application. I have followed the instructions found here: http://www.sqlite.org/cvstrac/wiki?p=HowToCompileWithVsNet Using the standard v3.6.3 source tree (not the amalgamation version) everything compiles fine but fails to link with this error: 1rtree.obj : error LNK2005: _sqlite3_api already defined in fts3_tokenizer.obj 1 Creating library D:\x_mirror\buildman\tools\sqlite-3.6.3\SQLiteVS2005\Debug\SQLiteVS2005.lib and object D:\x_mirror\buildman\tools\sqlite-3.6.3\SQLiteVS2005\Debug\SQLiteVS2005.exp 1D:\x_mirror\buildman\tools\sqlite-3.6.3\SQLiteVS2005\Debug\SQLiteVS2005.dll : fatal error LNK1169: one or more multiply defined symbols found I have only two SQLite preprocessor defines set in my project: SQLITE_ENABLE_COLUMN_METADATA SQLITE_ENABLE_FTS3 I get this error both with and without the SQLITE_ENABLE_FTS3 define. I get the same linker error when trying to build SQLite v3.6.10 and when trying to use Visual Studio 2003 instead of 2005. I'm sure there must be an easy fix for this problem - any advice would be really appreciated. I imagine I could use the /FORCE:MULTIPLE linker option to get past this but I'm reluctant to do so without checking first. Thanks in advance, Ian Thomas LEGAL NOTICE Unless expressly stated otherwise, information contained in this message is confidential. If this message is not intended for you, please inform postmas...@ccdc.cam.ac.uk and delete the message. The Cambridge Crystallographic Data Centre is a company Limited by Guarantee and a Registered Charity. Registered in England No. 2155347 Registered Charity No. 800579 Registered office 12 Union Road, Cambridge CB2 1EZ. ___ 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] INVALID COMMAND NAME sqlite3_soft_heap_limit
If you use the supplied makefile, make fulltest should build the testfixture and run the all.test set. The testfixture is similar to the sqlite3 CLI except that it includes a TCL interpreter as well as an extensive set of test harnesses for virtually every part of the SQLite core. On Mon, Dec 22, 2008 at 9:05 AM, mkrajachan...@gmail.com wrote: Hello Dan, Thank U for ur reply.. Could you please let me know more about testfixture. Also, please forward releated links Thanks Chandru On Mon, Dec 22, 2008 at 3:06 PM, Dan danielk1...@gmail.com wrote: On Dec 22, 2008, at 3:55 PM, mkrajachan...@gmail.com wrote: Hello all, While i am trying to run the all.test file through the tcl shell I am getting the following error # tclsh all.test invalid command name sqlite3_soft_heap_limit while executing sqlite3_soft_heap_limit $soft_limit (file ./tester.tcl line 50) invoked from within source $testdir/tester.tcl (file all.test line 16) # how can i getrid of this problem Thanks in advance You need to build and use 'testfixture', not the regular sqlite3 tcl shell, to run the test suite. Are you doing so? ___ 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] Which TCL distrabution for Windows XP for running SQLite tests?
Sorry for the late reply. I use both the ActiveTCL and the Cygwin distributions for testing on Windows. It should be noted that both versions have limitations that will cause false positives in the test suite. Be on the look out especially for problems with 64-integers and floating point values. I believe the ActiveTCL distribution uses a 3 digit mantissa to display floating point values which throws off the checking in the test suite. The Cygwin distribution is a little out dated and doesn't have full 64-bit support yet which also causes problems in the test suite. HTH. -Shane On Sat, Nov 15, 2008 at 5:40 PM, Brown, Daniel [EMAIL PROTECTED] wrote: Good afternoon list, If I would like to use the TCL based tests on my version of SQLite on Windows XP what would be the recommend TCL distribution to use? Would ActiveTCL (http://tinyurl.com/5wl6uv) be compatible with the SQLite TCL scripts on Windows XP? Cheers, Daniel Brown | Software Engineer The best laid schemes o' mice an' men, gang aft agley ___ 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] Which TCL distrabution for Windows XP for running SQLite tests?
On Mon, Nov 24, 2008 at 9:30 AM, Michael Schlenker [EMAIL PROTECTED] wrote: Shane Harrelson schrieb: Sorry for the late reply. I use both the ActiveTCL and the Cygwin distributions for testing on Windows. It should be noted that both versions have limitations that will cause false positives in the test suite. Be on the look out especially for problems with 64-integers and floating point values. I believe the ActiveTCL distribution uses a 3 digit mantissa to display floating point values which throws off the checking in the test suite. That sounds strange. Might be more an issue with Tcl 8.4 vs. Tcl 8.5. Tcl 8.5 has a totally revamped floating point handling, so that there should be no loss of precision when converting a floating point through a string rep anymore (and Tcl got big integer support based on libtommath as a side effect). It wasn't a loss of precision, it was mearly a display issue. The Windows ActiveTCL 8.5 distribution might display the floating point value as 5.4e-018 while the unix TCL 8.5 version would display 5.4e-18 (or something similar). There's probably an option to control this in TCL, but I couldn't find it with a quick check. Cygwin is not really a supported platform for Tcl anymore and the port is old and strange. So, guess you should use the ActiveTcl 8.5 offering, unless DRH recommends something older than Tcl 8.5 (which is the current stable branch while 8.4 is getting its last update soon). I test with both as to get assurance of greater compatibility with more versions on Windows. Michael -- Michael Schlenker Software Engineer CONTACT Software GmbH Tel.: +49 (421) 20153-80 Wiener Straße 1-3 Fax:+49 (421) 20153-41 28359 Bremen http://www.contact.de/ E-Mail: [EMAIL PROTECTED] Sitz der Gesellschaft: Bremen Geschäftsführer: Karl Heinz Zachries, Ralf Holtgrefe Eingetragen im Handelsregister des Amtsgerichts Bremen unter HRB 13215 ___ 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] sqlite crash with WHERE x in () query
Thanks for the report. The current test suite has many cases testing queries of the form you mention. I added one specifically for the query you suggested to in4.test. Everything passes for the current version. If you could test with the latest version of SQLite, it might help show where the problem lies (in SQSLite, the Python bindings, or possibly in your application code). Most of the historical versions of SQLite are also available from the website. If this was a problem in an older version, but not with the latest, it might be beneficial for you to try incremental versions to find where it was fixed. -Shane On Fri, Nov 7, 2008 at 6:48 PM, Matt Craighead [EMAIL PROTECTED] wrote: Hi all, I've hit what I believe may be a crash bug in SQLite. I'm using the version bundled with Python 2.5.2 on Windows, which is SQLite 3.3.4, it would appear. I know, this isn't the latest version, but this happens to be the version bundled with the version of Python my product relies on. (I haven't switched to Python 2.6 yet; for one, there are no publicly available mod_python Win32 binaries for Apache.) Originally my query looked something like: SELECT x FROM table WHERE id IN (SELECT id FROM other_table WHERE y=1) I assumed this would do the subquery only once, but I found (both from slow performance and by using EXPLAIN QUERY PLAN) that in fact it was doing a full table scan of table, and for each element, looking at its id to see whether that id could be found in other_table. On the other hand, if I did two separate queries, and wrote the second query as follows: SELECT x FROM table WHERE id IN (1,3,5,7) ...it would use table's index, which was a lot faster. Unfortunately if the list of id's was empty, I'd end up constructing a query with a WHERE x IN () clause -- and this would cause a crash deep in the guts of sqlite3.dll. Without symbols it wasn't clear where the crash was coming from. From the grammar diagram at http://www.sqlite.org/lang_expr.html, it would appear that using () for an empty list is not legal SQL syntax as recognized by SQLite. Is that correct? Even if so, it still shouldn't crash, right? I would think that it should consistently either produce an error, or consistently treat () as an empty set. I don't get an error, and while sometimes it appears to be treated as an empty set, sometimes I get a crash. Oddly this crash doesn't happen all the time. It only seems to happen for the following specific query: SELECT x FROM table WHERE id IN () AND x IN (SELECT x FROM table2 WHERE id=value) Any ideas? Has this been fixed in a newer version already? I've already worked around this and I no longer issue SQLite any IN () queries, but I figured I'd still report the problem. -- Matt Craighead Founder/CEO, Conifer Systems LLC http://www.conifersystems.com 512-772-1834 ___ 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
