[sqlite] (no subject)
Here is the problem that I am having with SQLite with encryption extension enabled, and I need help. I have an application that creates and saves some data in a SQLite database. I am using SQLite v 3.6.14, accessing it through the native dll form a C# WPF application. For some reason that I cant figure out am getting a corrupted file with some strange characteristics in some rare instances. First of all there some significant rows of a table are missing and another table whose structure is intact has all the rows empty. Second I am able to open the file with SQLite browser application (specially compiled for my license of SEE of course), but there are some Sql statements that do not work. A simple select on the partially filled table works, for example, but any select statement that involves aggregate functions (count, average, max min, distinct etc...) do not work. Third, I open the file with a text editor and I see that a significant chunk of the file towards the end is filled with the NUL character which is unlike all the other files that are not corrupted. This is the way my C# application works with the files: 1. Start transaction Write to table 1 Write to table 2 Write to table 3 Write around 80,000 rows in table 4 Write data to table 5 based on aggregate data from table 4 Commit transaction Table 4 has thousands of rows missing and table 5 is empty all together. Before someone states the obvious, I can see that there is no surprise that table 5 is empty since aggregate functions are not really working on table 4. But the question is what can lead to this kind of corruption where data write can stop after some thousands of rows and aggregate functions not working. Does anyone have any idea why this is happening? My problem is further complicated by the fact that I am not able to reproduce the problem for the life of me; it only happened to one of our clients, and they are not able to tell me anything that is useful to determine the sequence of events that lead to this file corruption. I do try to handle all exceptions in the application, but when this problem happens no exceptions are thrown by the application or the underlying SQLite library.Thanks for the help. Dawit ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] catastrophic installation on Ubuntu 11.10
Hello Richard I didn't heard from you since I send you the file. I have solved my problem by installing Win7 on virtualbox. I think your discussions are quiet interesting, but for my level, it is too much. Can you please remove me from the list? If you still have questions/solutions, you can write to me directly. Best Regards Jean-Christophe Le 26-04-2012 13:04, Richard Hipp a écrit : On Thu, Apr 26, 2012 at 2:37 AM, Jean-Christophechristo...@ls-s.dk wrote: Hello, I have to tell that I didn't knew SQLite at all when I started this! On an Ubuntu 11.10, I have installed: command line interface for SQLite Navigateur de bases de données SQLite Sqliteman Afterwards, I have downloaded the last version from your website and install it with make install. When I try to make a new database in command line, I have: |SQLite header and source version mismatch 2011-06-23 19:49:22 4374b7e83ea0a3fbc3691f9c0c9362**72862f32f2 2012-03-20 11:35:50 00bb9c9ce4f465e6ac321ced2a9d00**62dc364669 jean-christophe@PC01:~/**Téléchargements/sqlite-**autoconf-3071100$| This happened because when you compiled the new version of SQLite, the Makefile somehow picked up the preexisting sqlite3.h header file that was already installed on Ubuntu rather than the latest header file from the source tree. What procedure did you use to build? Can you send us the output of your build? I look at google, make some research and I find a post, where they use ldconfig. When I try ldconfig on my machine, I have this: |jean-christophe@PC01:~$ sudo ldconfig -p | grep sqlite libsqlite3.so.0 (libc6) = /usr/lib/i386-linux-gnu/**libsqlite3.so.0 libsqlite.so.0 (libc6) = /usr/lib/libsqlite.so.0| So far, so good. I want to uninstall everything, and start from scratch. I use the program manager from ubuntu and uninstall: command line interface for SQLite, Navigateur de bases de données SQLite and Sqliteman I make uninstall the downloaded installed package. All the programs are removed, but if I make the samme command as previously, I have now this: jean-christophe@PC01:~$ sudo ldconfig -p | grep sqlite [sudo] password for jean-christophe: libsqlite3.so.0 (libc6) = /usr/lib/i386-linux-gnu/**libsqlite3.so.0 libsqlite3.so.0 (libc6) = /usr/local/lib/libsqlite3.so.0 libsqlite3.so (libc6) = /usr/local/lib/libsqlite3.so libsqlite.so.0 (libc6) = /usr/lib/libsqlite.so.0 Can you please tell me, what I have to do for removing all occurencies of SQLite without damaging some programs using it? When it's down, could you maybe suggest me som tutorials. I have plan to use it with Lazarus. Thank you Best regards Jean-Christophe __**_ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-**bin/mailman/listinfo/sqlite-**usershttp://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] is SQLite the right tool to analyze a 44GB file
Am 01.05.2012 22:06, schrieb peter korinis: I'm new to SQLite . not a programmer . not a DBA . just an end-user with no dev support for a pilot project (single user, no updates, just queries). I want to analyze the data contained in a 44GB csv file with 44M rows x 600 columns (fields all15 char). Seems like a DBMS will allow me to query it in a variety of ways to analyze the data. I have the data files and SQLite on my laptop: a 64-bit Win7 Intel dual-proc with 4GB RAM + 200GB free disk space. End-user tools like Excel Access failed due to lack of memory. I downloaded SQLite ver.3.6.23.1. I tried to use Firefox Data Manager add-on but it would not load the csv files - 'csv worker failed'. So I tried Database Master from Nucleon but it failed after loading (it took 100 minutes) ~57,000 rows with error message = 'database or disk is full. I tried to create another table in the same db but could not with same error message. The DB size shows as 10,000KB (that looks suspiciously like a size setting?). From what I've read SQLite can handle this size DB. So it seems that either I do not have enough RAM or there are memory/storage (default) limits or maybe time-out issues that prevent loading this large file . or the 2 GUI tools I tried have size limits. I do have a fast server (16GB, 12 procs, 64-bit intel, Win server) and an iMAC available. 1. Is SQLite the wrong tool for this project? (I don't want the overkill and admin overhead of a large MySQL or SQL Server, etc.) 2. If SQLite will work, are there configuration settings in SQLite or Win7 that will permit the load . or is there a better tool for this project? Thanks much for helping a newbie! peterK ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users Hello - reading your post I'd say you're on the same path I started a few years ago :-) You need the following knowledge to work with databases (including Access): - databasedesign (catchword normalization) - SQL (I mean writing your own code to really understand what you're doing) - a scripting language to preprocess your data (i.e. your data might be in a fixed length format,...). I recommend gawk because it's powerful and simple. - it might be necessary to inspect your csv file if you don't know the structure (is there a header?, what separator is used?,...) so under windows I'd try to print the first 10 lines into a new file and use an editor to inspect it or use a pager program like less that is capable to open your 44 GB file in a second (don't use notepad for this task ;-)) In a few words: a database is not just a more powerful spreadsheet program greetings Oliver ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] (no subject)
On 2 May 2012, at 8:55am, Octet Computech dawitteg...@hotmail.com wrote: I have an application that creates and saves some data in a SQLite database. I am using SQLite v 3.6.14, accessing it through the native dll form a C# WPF application. For some reason that I cant figure out am getting a corrupted file with some strange characteristics in some rare instances. Are you calling the SQLite C library functions as described on this page: http://www.sqlite.org/c3ref/funclist.html or are you using some other library to execute SQLite commands ? If you're using another library, which library are you using ? Second I am able to open the file with SQLite browser application (specially compiled for my license of SEE of course), but there are some Sql statements that do not work. Please download the shell tool for your OS from http://www.sqlite.org/download.html and try accessing the database using that shell tool instead of whatever browser application you're using. Does the shell tool still suggest the same corruption as your browser app ? If there is no shell tool for your OS, you can transfer a copy of the database to a standard computer. The file format is identical on all platforms. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] transactions and locking
Hello, I'm sorry I have to bother you readers but I haven't understood http://sqlite.org/lockingv3.html chapter 7. 1. In autocommit mode, all changes to the database are committed as soon as all operations associated with the current database connection complete. How does SQLite know when ALL operations … complete? In PERL DBI context, I may prepare and execute as many statements as I like. When does SQLite commit? 2. The SQL command COMMIT does not actually commit the changes to disk. It just turns autocommit back on. Is AutoCommit enabled after every commit? Do I have to disable AutoCommit again after a commit to have it be disabled? 3. If the SQL COMMIT command turns autocommit on and the autocommit logic then tries to commit change but fails because some other process is holding a SHARED lock, then autocommit is turned back off automatically. This allows the user to retry the COMMIT at a later time after the SHARED lock has had an opportunity to clear. If I had AutoCommit disabled and the commit fails, I have to retry to commit myself. Is it possible (AutoCommit enabled or disabled) to have commits get queued and executed when possible by SQLite so that I don't have to write code myself but can rely on the order of commits (per connection)? 4. By default, SQLite version 3 operates in autocommit mode. Is SQLite transactional by default? Do I have to use transactions? I'd like to. Anything besides begin and commit I have to consider? 5. Does a SELECT … require a transaction begin and commit to transfer data? Thank you! Lars Hansen ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] transactions and locking
On Wed, May 02, 2012 at 01:16:41PM +0200, Lars Hansen scratched on the wall: Hello, I'm sorry I have to bother you readers but I haven't understood http://sqlite.org/lockingv3.html chapter 7. 1. In autocommit mode, all changes to the database are committed as soon as all operations associated with the current database connection complete. How does SQLite know when ALL operations … complete? In PERL DBI context, I may prepare and execute as many statements as I like. When does SQLite commit? When the last active statement finishes execution. Prepared statements don't count, only active ones. Many programs only have one active statement at a time, but it can get more complex. Basically, SQLite keeps a reference count of how many statements are actually running. Any time that reference count hits zero, the database state goes through a commit. 2. The SQL command COMMIT does not actually commit the changes to disk. It just turns autocommit back on. Is AutoCommit enabled after every commit? Yes. Any time there is not an explicit transaction open, autocommit is enabled. Do I have to disable AutoCommit again after a commit to have it be disabled? BEGIN. Start a new explicit transaction and autocommit will be disabled. 3. If the SQL COMMIT command turns autocommit on and the autocommit logic then tries to commit change but fails because some other process is holding a SHARED lock, then autocommit is turned back off automatically. This allows the user to retry the COMMIT at a later time after the SHARED lock has had an opportunity to clear. If I had AutoCommit disabled and the commit fails, I have to retry to commit myself. Having and open transaction (e.g. a BEGIN) and having autocommit disabled are the same thing. You can't have one without the other. And yes, if COMMIT fails, the transaction is (usually) still open and if you want to proceed you have to manually retry. Is it possible (AutoCommit enabled or disabled) to have commits get queued and executed when possible by SQLite so that I don't have to write code myself but can rely on the order of commits (per connection)? Again, if you're inside an explicit transaction (BEGIN/COMMIT) then autocommit is always disabled. SQLite can, to a degree, retry COMMIT statements. But since there is no server to coordinate or queue requests, it isn't fully automatic, nor does it always work. See http://sqlite.org/c3ref/busy_handler.html 4. By default, SQLite version 3 operates in autocommit mode. Is SQLite transactional by default? Yes. Every command is run in the context of a transaction-- either an explicit one using the BEGIN/COMMIT commands, or an implicit, automatic autocommit transaction. Do I have to use transactions? Explicit BEGIN/END transactions? No. If you don't, SQLite will wrap your commands in an automatic transaction. I'd like to. Anything besides begin and commit I have to consider? Locking. Read: http://sqlite.org/lockingv3.html 5. Does a SELECT … require a transaction begin and commit to transfer data? SELECT requires a transaction (for locking) but not an explicit BEGIN/COMMIT transaction. If you do not wrap SELECT in a BEGIN/COMMIT block, SQLite will-- as with any command-- wrap it in an automatic transaction. -j -- Jay A. Kreibich J A Y @ K R E I B I.C H Intelligence is like underwear: it is important that you have it, but showing it to the wrong people has the tendency to make them feel uncomfortable. -- Angela Johnson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] transactions and locking
On 2 May 2012, at 12:16pm, Lars Hansen la...@me.com wrote: I'm sorry I have to bother you readers but I haven't understood http://sqlite.org/lockingv3.html chapter 7. I've answered your questions in order, but they're all interrelated, so please read them all before worrying about the first answer. For instance, the clearest and most useful answer is to your question 4. 1. In autocommit mode, all changes to the database are committed as soon as all operations associated with the current database connection complete. How does SQLite know when ALL operations … complete? In PERL DBI context, I may prepare and execute as many statements as I like. When does SQLite commit? I feel that that text is poorly worded. It seems to mean that the changes are committed when the connection to the database is closed, i.e. when _close() is used. This is not what happens. What it actually means is that the changes are committed when SQLite returns to autocommit mode. 2. The SQL command COMMIT does not actually commit the changes to disk. It just turns autocommit back on. Is AutoCommit enabled after every commit? Do I have to disable AutoCommit again after a commit to have it be disabled? Autocommit is usually on. You turn it off by explicitly opening a transaction yourself. Commmitting the transaction doesn't actually do any of the hard work, it just turns autocommit back on. autocommit immediately notices that there are pending operations to commit, and does the associated work. 3. If the SQL COMMIT command turns autocommit on and the autocommit logic then tries to commit change but fails because some other process is holding a SHARED lock, then autocommit is turned back off automatically. This allows the user to retry the COMMIT at a later time after the SHARED lock has had an opportunity to clear. If I had AutoCommit disabled and the commit fails, I have to retry to commit myself. Is it possible (AutoCommit enabled or disabled) to have commits get queued and executed when possible by SQLite so that I don't have to write code myself but can rely on the order of commits (per connection)? No. Because if your commit is going to fail, which it might do after a long delay, your application will need to know that it has failed before it is able to proceed with other operations. In other words, if commits got queued up and executed later, you might have to undo any number of subsequent transactions. And that would require your application to keep track of them all, which would be difficult and annoying to program. However this normally does all come out in the wash. If you think about which database changes you want in the same transaction, you will find that your natural programming style leads to you wanting to know, when you issue your COMMIT to close the transaction, whether the commit succeeded or failed. 4. By default, SQLite version 3 operates in autocommit mode. Is SQLite transactional by default? Do I have to use transactions? I'd like to. Anything besides begin and commit I have to consider? You should generally be using transactions with any dependent set of changes. However, if you have not declared your own transaction, and do an UPDATE or INSERT or DELETE, SQLite automatically wraps your operation in a 'BEGIN … op … COMMIT' for you. If the COMMIT it inserts after your operation results in an error, it returns that error for the operation rather than looking for a COMMIT you never issued. 5. Does a SELECT … require a transaction begin and commit to transfer data? Although SELECT makes no changes to the database, in respect of database locking SELECT operates as described in my answer to question 4. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] [ANN] ODB C++ ORM 2.0.0 released, adds support for C++11, polymorphism
I am pleased to announce the release of ODB 2.0.0. ODB is an open source object-relational mapping (ORM) system for C++. It allows you to persist C++ objects to a relational database without having to deal with tables, columns, or SQL and without manually writing any of the mapping code. Major new features in this release: * Support for C++11 which adds integration with the new C++11 standard library components, including smart pointers and containers. Now you can use std::unique_ptr and std::shared_ptr as object pointers (their lazy versions are also provided). For containers, support was added for std::array, std::forward_list, and the unordered containers. * Support for polymorphism which allows you to persist, load, update, erase, and query objects of derived classes using their base class interfaces. Persistent class hierarchies are mapped to the relational database model using the table-per-difference mapping. * Support for composite object ids which are translated to composite primary keys in the relational database. * Support for the NULL semantics for composite values. This release has also been tested with GCC 4.7 and Clang 3.0 with the ODB compiler now supporting the GCC 4.7 series plugin interface. With this release we are also introducing a free proprietary license for small object models. A more detailed discussion of these features can be found in the following blog post: http://www.codesynthesis.com/~boris/blog/2012/05/02/odb-2-0-0-released/ For the complete list of new features in this version see the official release announcement: http://www.codesynthesis.com/pipermail/odb-announcements/2012/13.html ODB is written in portable C++ and you should be able to use it with any modern C++ compiler. In particular, we have tested this release on GNU/Linux (x86/x86-64), Windows (x86/x86-64), Mac OS X, and Solaris (x86/x86-64/SPARC) with GNU g++ 4.2.x-4.7.x, MS Visual C++ 2008 and 2010, Sun Studio 12, and Clang 3.0. The currently supported database systems are SQLite, MySQL, PostgreSQL, Oracle, and SQL Server. ODB also provides profiles for Boost and Qt, which allow you to seamlessly use value types, containers, and smart pointers from these libraries in your persistent classes. More information, documentation, source code, and pre-compiled binaries are available from: http://www.codesynthesis.com/products/odb/ Enjoy, Boris ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Missing SQL_CONSTRAINTS resource in System.Data.SQLite.Linq since v1.0.79.0
Hi, I've just noticed that a resource is missing in System.Data.SQLite.Linq.dll since version 1.0.79.0. The missing resource causes an exception when calling the SQLiteProviderServices' ISQLiteSchemaExtensions.BuildTempSchema implementation. Hope this will help Mathieu TAUZIN ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] (no subject)
Hi Simon, thanks for the reply. I am using the C libraries as described in the link that you mentioned. I am not able to use a shell(or any other) [recompiled tool as the version of SQLite that I am using to create has the SEE (encryption) enabled. Whatever tool that I use I have to compile it with the licence and encryption key that is specific to our licence. At any rate, I have compiled a sql browser tool that uses the sql library with the encryption enabled, and I am having problem executing the SQL statemsnts (not all, but aggretate functions). Thanks for your help. From: slav...@bigfraud.org Date: Wed, 2 May 2012 11:57:40 +0100 To: sqlite-users@sqlite.org Subject: Re: [sqlite] (no subject) On 2 May 2012, at 8:55am, Octet Computech dawitteg...@hotmail.com wrote: I have an application that creates and saves some data in a SQLite database. I am using SQLite v 3.6.14, accessing it through the native dll form a C# WPF application. For some reason that I cant figure out am getting a corrupted file with some strange characteristics in some rare instances. Are you calling the SQLite C library functions as described on this page: http://www.sqlite.org/c3ref/funclist.html or are you using some other library to execute SQLite commands ? If you're using another library, which library are you using ? Second I am able to open the file with SQLite browser application (specially compiled for my license of SEE of course), but there are some Sql statements that do not work. Please download the shell tool for your OS from http://www.sqlite.org/download.html and try accessing the database using that shell tool instead of whatever browser application you're using. Does the shell tool still suggest the same corruption as your browser app ? If there is no shell tool for your OS, you can transfer a copy of the database to a standard computer. The file format is identical on all platforms. 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] is SQLite the right tool to analyze a 44GB file
Thank you all. Look like I'm stuck with the CLI though I have contacted Nucleon software support ... tried CLI yesterday but need more practice. Is there a good reference book you would recommend for SQLite? peter -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Black, Michael (IS) Sent: Tuesday, May 01, 2012 4:22 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] is SQLite the right tool to analyze a 44GB file You need to try and do an import from the shell. GUIs seem to have way too many limits. http://sqlite.org/download.html Don't do any indexes up frontdo them afterwords if they'll help your queries. Indexes will slow down your import notably. I don't think you're anywhere near the limits of sqlite since it talks about terabytes. http://sqlite.org/limits.html Somebody else can answer for sure but wrapping your .import inside a transaction may be a good thing. I don't know if that's done by default. Your queries are liable to be pretty slow depending on what you have to do. Michael D. Black Senior Scientist Advanced Analytics Directorate Advanced GEOINT Solutions Operating Unit Northrop Grumman Information Systems From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of peter korinis [kori...@earthlink.net] Sent: Tuesday, May 01, 2012 3:06 PM To: sqlite-users@sqlite.org Subject: EXT :[sqlite] is SQLite the right tool to analyze a 44GB file I'm new to SQLite . not a programmer . not a DBA . just an end-user with no dev support for a pilot project (single user, no updates, just queries). I want to analyze the data contained in a 44GB csv file with 44M rows x 600 columns (fields all 15 char). Seems like a DBMS will allow me to query it in a variety of ways to analyze the data. I have the data files and SQLite on my laptop: a 64-bit Win7 Intel dual-proc with 4GB RAM + 200GB free disk space. End-user tools like Excel Access failed due to lack of memory. I downloaded SQLite ver.3.6.23.1. I tried to use Firefox Data Manager add-on but it would not load the csv files - 'csv worker failed'. So I tried Database Master from Nucleon but it failed after loading (it took 100 minutes) ~57,000 rows with error message = 'database or disk is full. I tried to create another table in the same db but could not with same error message. The DB size shows as 10,000KB (that looks suspiciously like a size setting?). From what I've read SQLite can handle this size DB. So it seems that either I do not have enough RAM or there are memory/storage (default) limits or maybe time-out issues that prevent loading this large file . or the 2 GUI tools I tried have size limits. I do have a fast server (16GB, 12 procs, 64-bit intel, Win server) and an iMAC available. 1. Is SQLite the wrong tool for this project? (I don't want the overkill and admin overhead of a large MySQL or SQL Server, etc.) 2. If SQLite will work, are there configuration settings in SQLite or Win7 that will permit the load . or is there a better tool for this project? Thanks much for helping a newbie! peterK ___ 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] Extended error code values
The list of extended error codes on http://www.sqlite.org/c3ref/c_abort_rollback.html are in a format that is fairly inconvenient when you're trying to lookup an error value. Below are what they resolve to, and I'm wondering if perhaps someone could update the page to show those values for future use? Maybe the source could be updated as well? #define SQLITE_IOERR_READ (SQLITE_IOERR | (18)) //256 #define SQLITE_IOERR_SHORT_READ(SQLITE_IOERR | (28)) //522 #define SQLITE_IOERR_WRITE (SQLITE_IOERR | (38)) //778 #define SQLITE_IOERR_FSYNC (SQLITE_IOERR | (48)) //1034 #define SQLITE_IOERR_DIR_FSYNC (SQLITE_IOERR | (58)) //1290 #define SQLITE_IOERR_TRUNCATE (SQLITE_IOERR | (68)) //1546 #define SQLITE_IOERR_FSTAT (SQLITE_IOERR | (78)) //1802 #define SQLITE_IOERR_UNLOCK(SQLITE_IOERR | (88)) //2058 #define SQLITE_IOERR_RDLOCK(SQLITE_IOERR | (98)) //2314 #define SQLITE_IOERR_DELETE(SQLITE_IOERR | (108)) //2570 #define SQLITE_IOERR_BLOCKED (SQLITE_IOERR | (118)) //2826 #define SQLITE_IOERR_NOMEM (SQLITE_IOERR | (128)) //3082 #define SQLITE_IOERR_ACCESS(SQLITE_IOERR | (138)) //3338 #define SQLITE_IOERR_CHECKRESERVEDLOCK (SQLITE_IOERR | (148)) //3594 #define SQLITE_IOERR_LOCK (SQLITE_IOERR | (158)) //3850 #define SQLITE_IOERR_CLOSE (SQLITE_IOERR | (168)) //4106 #define SQLITE_IOERR_DIR_CLOSE (SQLITE_IOERR | (178)) //4362 #define SQLITE_IOERR_SHMOPEN (SQLITE_IOERR | (188)) //4618 #define SQLITE_IOERR_SHMSIZE (SQLITE_IOERR | (198)) //4874 #define SQLITE_IOERR_SHMLOCK (SQLITE_IOERR | (208)) //5130 #define SQLITE_IOERR_SHMMAP(SQLITE_IOERR | (218)) //5386 #define SQLITE_IOERR_SEEK (SQLITE_IOERR | (228)) //5642 #define SQLITE_LOCKED_SHAREDCACHE (SQLITE_LOCKED | (18)) //262 #define SQLITE_BUSY_RECOVERY (SQLITE_BUSY | (18)) //261 #define SQLITE_CANTOPEN_NOTEMPDIR (SQLITE_CANTOPEN | (18)) //270 #define SQLITE_CORRUPT_VTAB(SQLITE_CORRUPT | (18)) //267 #define SQLITE_READONLY_RECOVERY (SQLITE_READONLY | (18)) //264 #define SQLITE_READONLY_CANTLOCK (SQLITE_READONLY | (28)) //520 #define SQLITE_ABORT_ROLLBACK (SQLITE_ABORT | (28)) //516 Doug ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] is SQLite the right tool to analyze a 44GB file
Does that mean using the CLI worked for you? If so, you may be able to access the database with the other programs AFTER you create it. Seems that creating a db from csv is challenging to some and not thoroughly tested for large data sources. Michael D. Black Senior Scientist Advanced Analytics Directorate Advanced GEOINT Solutions Operating Unit Northrop Grumman Information Systems From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of peter korinis [kori...@earthlink.net] Sent: Wednesday, May 02, 2012 9:06 AM To: 'General Discussion of SQLite Database' Subject: EXT :Re: [sqlite] is SQLite the right tool to analyze a 44GB file Thank you all. Look like I'm stuck with the CLI though I have contacted Nucleon software support ... tried CLI yesterday but need more practice. Is there a good reference book you would recommend for SQLite? peter -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Black, Michael (IS) Sent: Tuesday, May 01, 2012 4:22 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] is SQLite the right tool to analyze a 44GB file You need to try and do an import from the shell. GUIs seem to have way too many limits. http://sqlite.org/download.html Don't do any indexes up frontdo them afterwords if they'll help your queries. Indexes will slow down your import notably. I don't think you're anywhere near the limits of sqlite since it talks about terabytes. http://sqlite.org/limits.html Somebody else can answer for sure but wrapping your .import inside a transaction may be a good thing. I don't know if that's done by default. Your queries are liable to be pretty slow depending on what you have to do. Michael D. Black Senior Scientist Advanced Analytics Directorate Advanced GEOINT Solutions Operating Unit Northrop Grumman Information Systems From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of peter korinis [kori...@earthlink.net] Sent: Tuesday, May 01, 2012 3:06 PM To: sqlite-users@sqlite.org Subject: EXT :[sqlite] is SQLite the right tool to analyze a 44GB file I'm new to SQLite . not a programmer . not a DBA . just an end-user with no dev support for a pilot project (single user, no updates, just queries). I want to analyze the data contained in a 44GB csv file with 44M rows x 600 columns (fields all 15 char). Seems like a DBMS will allow me to query it in a variety of ways to analyze the data. I have the data files and SQLite on my laptop: a 64-bit Win7 Intel dual-proc with 4GB RAM + 200GB free disk space. End-user tools like Excel Access failed due to lack of memory. I downloaded SQLite ver.3.6.23.1. I tried to use Firefox Data Manager add-on but it would not load the csv files - 'csv worker failed'. So I tried Database Master from Nucleon but it failed after loading (it took 100 minutes) ~57,000 rows with error message = 'database or disk is full. I tried to create another table in the same db but could not with same error message. The DB size shows as 10,000KB (that looks suspiciously like a size setting?). From what I've read SQLite can handle this size DB. So it seems that either I do not have enough RAM or there are memory/storage (default) limits or maybe time-out issues that prevent loading this large file . or the 2 GUI tools I tried have size limits. I do have a fast server (16GB, 12 procs, 64-bit intel, Win server) and an iMAC available. 1. Is SQLite the wrong tool for this project? (I don't want the overkill and admin overhead of a large MySQL or SQL Server, etc.) 2. If SQLite will work, are there configuration settings in SQLite or Win7 that will permit the load . or is there a better tool for this project? Thanks much for helping a newbie! peterK ___ 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] Missing SQL_CONSTRAINTS resource in System.Data.SQLite.Linq since v1.0.79.0
TAUZIN Mathieu wrote: I've just noticed that a resource is missing in System.Data.SQLite.Linq.dll since version 1.0.79.0. I'm not able to reproduce this problem. When viewed with ILDASM, the resource named System.Data.SQLite.Linq.Properties.Resources.resources is present. Also, the test cases for these resources passes with version 1.0.79.0 (as well as 1.0.80.0 and trunk). I've added an additional test case for the ISQLiteSchemaExtensions.BuildTempSchema method and that passes too: https://system.data.sqlite.org/index.html/ci/0a25b20f9c?sbs=0 Any additional information you could provide on this would be useful? For example, which build of System.Data.SQLite are you using? Is the assembly in the GAC? -- Joe Mistachkin ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Bug: Memory leak using PRAGMA temp_store_directory
Josh Gibbs wrote: I reported this a while ago and forgot about this until today while I was doing some debugging and once again got the report of leaked memory. I'm using the c amalgamation code from 3.7.10 with VStudio 2010, and always start up my databases setting a temp directory to be used in the form: PRAGMA temp_store_directory = 'my_app_dir_dbtemp' This is passed into the 'sqlite3_exec' function. On exit, the program is reporting that line 15215 of sqlite.c is where the unfreed allocation occurred:void *p = SQLITE_MALLOC( nByte ); 1) This pragma is deprecated and should not be used. If you look at documentation, it should be quite clear. If you look at sources, it should be even more clear (global variable, that affects all database connections, unprotected and not thread-safe, etc; you totally don't want to touch it, even with pole stick). 2) This is not real leak - previous value is freed. The content of the memory contains my temp path. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Missing SQL_CONSTRAINTS resource in System.Data.SQLite.Linq since v1.0.79.0
Hi, I'm using the assembly in the GAC, the resource named System.Data.SQLite.Linq.Properties.Resources.resources is present but I could'nt see the SQL_CONSTRAINTS string resource in it browsing the assemblies 1.0.79.0 and 1.0.80.0 with ILSpy. I checked the resource string is not missing in v 1.0.74.0 (from gac also). Mathieu -Message d'origine- De : sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] De la part de Joe Mistachkin Envoyé : mercredi 2 mai 2012 16:28 À : 'General Discussion of SQLite Database' Objet : Re: [sqlite] Missing SQL_CONSTRAINTS resource in System.Data.SQLite.Linq since v1.0.79.0 TAUZIN Mathieu wrote: I've just noticed that a resource is missing in System.Data.SQLite.Linq.dll since version 1.0.79.0. I'm not able to reproduce this problem. When viewed with ILDASM, the resource named System.Data.SQLite.Linq.Properties.Resources.resources is present. Also, the test cases for these resources passes with version 1.0.79.0 (as well as 1.0.80.0 and trunk). I've added an additional test case for the ISQLiteSchemaExtensions.BuildTempSchema method and that passes too: https://system.data.sqlite.org/index.html/ci/0a25b20f9c?sbs=0 Any additional information you could provide on this would be useful? For example, which build of System.Data.SQLite are you using? Is the assembly in the GAC? -- Joe Mistachkin ___ 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] Missing SQL_CONSTRAINTS resource in System.Data.SQLite.Linq since v1.0.79.0
Using Reflector, I can see the resources: https://system.data.sqlite.org/temporary/linq_resources.png What is the exception you are seeing when the BuildTempSchema method is called? -- Joe Mistachkin ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] transactions and locking
Thank you both, I now have a better understanding. Lars ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] is SQLite the right tool to analyze a 44GB file
not sure yet ... but i'm working on it (between interruptions). thanks -Original Message- From: Black, Michael (IS) michael.bla...@ngc.com Sent: May 2, 2012 10:15 AM To: General Discussion of SQLite Database sqlite-users@sqlite.org Subject: Re: [sqlite] is SQLite the right tool to analyze a 44GB file Does that mean using the CLI worked for you? If so, you may be able to access the database with the other programs AFTER you create it. Seems that creating a db from csv is challenging to some and not thoroughly tested for large data sources. Michael D. Black Senior Scientist Advanced Analytics Directorate Advanced GEOINT Solutions Operating Unit Northrop Grumman Information Systems From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of peter korinis [kori...@earthlink.net] Sent: Wednesday, May 02, 2012 9:06 AM To: 'General Discussion of SQLite Database' Subject: EXT :Re: [sqlite] is SQLite the right tool to analyze a 44GB file Thank you all. Look like I'm stuck with the CLI though I have contacted Nucleon software support ... tried CLI yesterday but need more practice. Is there a good reference book you would recommend for SQLite? peter -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Black, Michael (IS) Sent: Tuesday, May 01, 2012 4:22 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] is SQLite the right tool to analyze a 44GB file You need to try and do an import from the shell. GUIs seem to have way too many limits. http://sqlite.org/download.html Don't do any indexes up frontdo them afterwords if they'll help your queries. Indexes will slow down your import notably. I don't think you're anywhere near the limits of sqlite since it talks about terabytes. http://sqlite.org/limits.html Somebody else can answer for sure but wrapping your .import inside a transaction may be a good thing. I don't know if that's done by default. Your queries are liable to be pretty slow depending on what you have to do. Michael D. Black Senior Scientist Advanced Analytics Directorate Advanced GEOINT Solutions Operating Unit Northrop Grumman Information Systems From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of peter korinis [kori...@earthlink.net] Sent: Tuesday, May 01, 2012 3:06 PM To: sqlite-users@sqlite.org Subject: EXT :[sqlite] is SQLite the right tool to analyze a 44GB file I'm new to SQLite . not a programmer . not a DBA . just an end-user with no dev support for a pilot project (single user, no updates, just queries). I want to analyze the data contained in a 44GB csv file with 44M rows x 600 columns (fields all 15 char). Seems like a DBMS will allow me to query it in a variety of ways to analyze the data. I have the data files and SQLite on my laptop: a 64-bit Win7 Intel dual-proc with 4GB RAM + 200GB free disk space. End-user tools like Excel Access failed due to lack of memory. I downloaded SQLite ver.3.6.23.1. I tried to use Firefox Data Manager add-on but it would not load the csv files - 'csv worker failed'. So I tried Database Master from Nucleon but it failed after loading (it took 100 minutes) ~57,000 rows with error message = 'database or disk is full. I tried to create another table in the same db but could not with same error message. The DB size shows as 10,000KB (that looks suspiciously like a size setting?). From what I've read SQLite can handle this size DB. So it seems that either I do not have enough RAM or there are memory/storage (default) limits or maybe time-out issues that prevent loading this large file . or the 2 GUI tools I tried have size limits. I do have a fast server (16GB, 12 procs, 64-bit intel, Win server) and an iMAC available. 1. Is SQLite the wrong tool for this project? (I don't want the overkill and admin overhead of a large MySQL or SQL Server, etc.) 2. If SQLite will work, are there configuration settings in SQLite or Win7 that will permit the load . or is there a better tool for this project? Thanks much for helping a newbie! peterK ___ 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 pk ___ sqlite-users mailing list sqlite-users@sqlite.org
[sqlite] Data Import Techniques
Hi I am writing an application which requires approximately 50,000 items to be imported from a text file into a table. Each item is a single string of 8 characters, and the target table has an auto-incrementing PK and one other field, to hold the 8 character string. Using the Import Table Data function in SQLiteman, the data loads very quickly. However in my application, using either an SQL insert command or a resultset, the import is very much slower. Is there another technique I can use to speed things up? Thanks Nige ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Data Import Techniques
If you are not wrapping the inserts in an explicit transaction, try that. On 5/2/2012 9:04 AM, Nigel Verity wrote: Hi I am writing an application which requires approximately 50,000 items to be imported from a text file into a table. Each item is a single string of 8 characters, and the target table has an auto-incrementing PK and one other field, to hold the 8 character string. Using the Import Table Data function in SQLiteman, the data loads very quickly. However in my application, using either an SQL insert command or a resultset, the import is very much slower. Is there another technique I can use to speed things up? Thanks Nige ___ 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] Data Import Techniques
Did you try wrapping all your INSERT statements into a single transaction? BEGIN TRANSACTION INSERT... INSERT... ... COMMIT -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- boun...@sqlite.org] On Behalf Of Nigel Verity Sent: Wednesday, May 02, 2012 12:05 PM To: sqlite-users@sqlite.org Subject: [sqlite] Data Import Techniques Hi I am writing an application which requires approximately 50,000 items to be imported from a text file into a table. Each item is a single string of 8 characters, and the target table has an auto-incrementing PK and one other field, to hold the 8 character string. Using the Import Table Data function in SQLiteman, the data loads very quickly. However in my application, using either an SQL insert command or a resultset, the import is very much slower. Is there another technique I can use to speed things up? Thanks Nige ___ 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 and pooling problem
The fixes for this issue have now been merged to trunk along with an appropriate test case that can reproduce the issue. https://system.data.sqlite.org/index.html/ci/ae1f4354e4?sbs=0 -- Joe Mistachkin ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Data Import Techniques
Using the Import Table Data function in SQLiteman, the data loads very quickly. However in my application, using either an SQL insert command or a resultset, the import is very much slower. Is there another technique I can use to speed things up? This FAQ entry might interest you... (19) INSERT is really slow - I can only do few dozen INSERTs per second - http://sqlite.org/faq.html#q19 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SQlite exclusive table lock
Hi, Should I use Shared-Cache in sqlite to have the exclusive table lock, so that readers have to wait for the write operation to finish. I don't want the complete database file locked while there is write operation. I can't use WAL because it doesn't stop readers from reading. Thanks Harnek ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQlite exclusive table lock
I can't use WAL because it doesn't stop readers from reading. For this sort of synchronization you should use OS synchronization primitives, especially if you want to do that among different processes. Pavel On Wed, May 2, 2012 at 5:11 PM, Harnek Manj hm...@gemcomsoftware.com wrote: Hi, Should I use Shared-Cache in sqlite to have the exclusive table lock, so that readers have to wait for the write operation to finish. I don't want the complete database file locked while there is write operation. I can't use WAL because it doesn't stop readers from reading. Thanks Harnek ___ 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 exclusive table lock
Pavel, So does it mean that in SQlite there is no way to stop the read operation while a write operation is running, like table level exclusive lock. Thanks Harnek -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Pavel Ivanov Sent: May-02-12 4:26 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] SQlite exclusive table lock I can't use WAL because it doesn't stop readers from reading. For this sort of synchronization you should use OS synchronization primitives, especially if you want to do that among different processes. Pavel On Wed, May 2, 2012 at 5:11 PM, Harnek Manj hm...@gemcomsoftware.com wrote: Hi, Should I use Shared-Cache in sqlite to have the exclusive table lock, so that readers have to wait for the write operation to finish. I don't want the complete database file locked while there is write operation. I can't use WAL because it doesn't stop readers from reading. Thanks Harnek ___ 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 exclusive table lock
On 3 May 2012, at 1:03am, Harnek Manj hm...@gemcomsoftware.com wrote: So does it mean that in SQlite there is no way to stop the read operation while a write operation is running, like table level exclusive lock. You seem to have jumped straight over the basic features of SQL and looked at some of the most advanced and complicated features. When you want to block everything else use BEGIN EXCLUSIVE TRANSACTION See http://sqlite.org/lang_transaction.html However, I don't understand why you would want to block a read operation. The difference can matter only when you have multiple threads or processes running at once. And if you're doing that then there's no harm in getting the data as it was before the write happened. If the first operation had to finish before the second started, who wouldn't you be doing both operations in the same thread of the same process ? Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
