Joe, Thank You, Thank You, that is exactly what I needed. I couldn't think of how to debug this, so with your suggestion, I put a try block in my code and wrote the stack trace out to a text file and that gave me exactly what I needed. I was missing a dll, but it ended up having nothing to do with SQLite. You are awesome.
Sincerely, Paul Bainter -----Original Message----- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of sqlite-users-requ...@sqlite.org Sent: Wednesday, October 16, 2013 10:00 AM To: sqlite-users@sqlite.org Subject: sqlite-users Digest, Vol 70, Issue 16 Send sqlite-users mailing list submissions to sqlite-users@sqlite.org To subscribe or unsubscribe via the World Wide Web, visit http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users or, via email, send a message with subject or body 'help' to sqlite-users-requ...@sqlite.org You can reach the person managing the list at sqlite-users-ow...@sqlite.org When replying, please edit your Subject line so it is more specific than "Re: Contents of sqlite-users digest..." Today's Topics: 1. System.Data.SQLite Deployment Problem (Paul Bainter) 2. Re: System.Data.SQLite Deployment Problem (Joe Mistachkin) 3. Re: Trigger SQL and database schema (Darren Duncan) 4. Re: Trigger SQL and database schema (Petite Abeille) 5. Re: Trigger SQL and database schema (Darren Duncan) 6. Re: FTS4 + spellfix1 with multiple languages (Dan Kennedy) 7. JDBC Driver Issue (mitzanu) 8. ALTER COLUMN in sqlite (mitzanu) 9. Feature Request: Binding Arrays (Dominique Devienne) 10. Re: Feature Request: Binding Arrays (Clemens Ladisch) 11. Re: Feature Request: Binding Arrays (Dominique Devienne) 12. Re: Feature Request: Binding Arrays (Richard Hipp) 13. Re: FTS4 + spellfix1 with multiple languages (Raf Geens) 14. Re: Feature Request: Binding Arrays (Paul van Helden) 15. Trying to figure out how to circumvent sqlite3_win32_mbcs_to_utf8 (Mike Clagett) 16. Re: Feature Request: Binding Arrays (Richard Hipp) 17. Re: Trying to figure out how to circumvent sqlite3_win32_mbcs_to_utf8 (Richard Hipp) 18. Re: ALTER COLUMN in sqlite (John McKown) 19. Re: Feature Request: Binding Arrays (Paul van Helden) 20. Re: Feature Request: Binding Arrays (techi eth) 21. Analyze optimizing views? (Daniel Polski) 22. Re: Analyze optimizing views? (Richard Hipp) 23. Re: ALTER COLUMN in sqlite (a.furi...@lqt.it) ---------------------------------------------------------------------- Message: 1 Date: Tue, 15 Oct 2013 20:56:10 -0600 From: "Paul Bainter" <pbain...@gmail.com> To: <sqlite-users@sqlite.org> Subject: [sqlite] System.Data.SQLite Deployment Problem Message-ID: <004901ceca1b$45edc060$d1c94120$@gmail.com> Content-Type: text/plain; charset="us-ascii" I'm having a terrible time trying to deploy my SQLite application. I downloaded from System.Data.SQLite.org the file: sqlite-netFx45-setup-bundle-x86-2012-1.0.88.0.exe and installed that on my development machine (Windows 7 Ultimate x64). I actually have 2 development machines, a laptop and a desktop both are Windows7 x64 and both have Visual Studio 2012. I used this particular download file because I enjoy working with EntityFramework and this saved me a lot of valuable time in my development. I'm using Visual Studio 2012 and I got my application running and everything is great. I configured all the libraries and my main application to build to x86, so that it matched the sqlite installation file. I then copied all the files from the bin\debug directory and placed them on a target machine and made sure the sqlite database file was accessible in the correct location per the exe.config file. both System.Data.SQLite.dll and System.Data.SQLite.Linq.dll were included. Also, per the installation notes, I placed the following code in the Configuration file: <system.data> <DbProviderFactories> <remove invariant="System.Data.SQLite" /> <add name="SQLite Data Provider" invariant="System.Data.SQLite" description=".Net Framework Data Provider for SQLite" type="System.Data.SQLite.SQLiteFactory, System.Data.SQLite, Version=1.0.88.0, Culture=neutral, PublicKeyToken=db937bc2d44ff139" /> </DbProviderFactories> </system.data> When I run the program on the target machine, the main window comes up fine because it doesn't access the database, but once I bring up a window that does access the database, I get a message stating that the application has stopped working. No error message specific to the problem and then another window from the OS stating that it will try to discover what the problem is, but of course it can't. I've used several machines as the target machine such as Windows 7 Ultimate x64, Windows 7 Ultimate x86, a virtual Windows 7 Ultimate x64, etc. I even tried to install sqlite-netFx45-setup-bundle-x86-2012-1.0.88.0.exe on some of the target machines to see if that would help and it didn't. Each of the target machines has installed the .NET 4.5 update, so that is also not the problem. I'm completely stumped on this issue. I really want to be able to use EntityFramework and love the SQLite database, but this has got me pulling my hair out, (not that I have much anyway. J) Any help would be tremendously appreciated. Sincerely, Paul Bainter ------------------------------ Message: 2 Date: Tue, 15 Oct 2013 20:24:28 -0700 From: "Joe Mistachkin" <sql...@mistachkin.com> To: "'General Discussion of SQLite Database'" <sqlite-users@sqlite.org> Subject: Re: [sqlite] System.Data.SQLite Deployment Problem Message-ID: <A4474A490C9F46D89209C36AC69A0174@LACHRYMOSE> Content-Type: text/plain; charset="us-ascii" Paul Bainter wrote: > > When I run the program on the target machine, the main window comes up fine > because it doesn't access the database, but once I bring up a window > that does access the database, I get a message stating that the > application has stopped working. No error message specific to the > problem and then another window from the OS stating that it will try > to discover what the problem is, > but of course it can't. > Do you have a stack trace showing where the exception is happening? Can you enable just-in-time debugging on one of the deployment machines? Do you know what type of exception is being thrown? I suspect the exception being thrown is due to the native "SQLite.Interop.dll" not being found, being the wrong architecture (x86 versus x64), or missing its runtime libraries. Without the stack trace and/or the type of exception being thrown, it's very hard to be sure which issue you are encountering. -- Joe Mistachkin ------------------------------ Message: 3 Date: Tue, 15 Oct 2013 22:20:21 -0700 From: Darren Duncan <dar...@darrenduncan.net> To: General Discussion of SQLite Database <sqlite-users@sqlite.org> Subject: Re: [sqlite] Trigger SQL and database schema Message-ID: <525e2215.70...@darrenduncan.net> Content-Type: text/plain; charset=UTF-8; format=flowed On 2013.10.14 11:58 PM, Sqlite Dog wrote: > seems like SQLite is not checking trigger SQL for invalid column names > until execution? What you describe sounds like the behavior of every SQL DBMS which has triggers whose trigger behavior I know. Seems better to me to retain this behavior than to reverse it, at least for default semantics. -- Darren Duncan ------------------------------ Message: 4 Date: Wed, 16 Oct 2013 07:34:20 +0200 From: Petite Abeille <petite.abei...@gmail.com> To: General Discussion of SQLite Database <sqlite-users@sqlite.org> Subject: Re: [sqlite] Trigger SQL and database schema Message-ID: <d44378a0-3699-4e5e-b7a9-e50d00505...@gmail.com> Content-Type: text/plain; charset=windows-1252 On Oct 16, 2013, at 7:20 AM, Darren Duncan <dar...@darrenduncan.net> wrote: > On 2013.10.14 11:58 PM, Sqlite Dog wrote: >> seems like SQLite is not checking trigger SQL for invalid column >> names until execution? > > What you describe sounds like the behavior of every SQL DBMS which has triggers whose trigger behavior I know. Hmmm? FWIW? Oracle, for one, will invalidate triggers, views, packages, etc if their underlying tables change. There is even a very handy ALL_DEPENDENCIES views to track all the explicit interdependencies between objects: http://docs.oracle.com/cd/B28359_01/server.111/b28320/statviews_1066.htm#i15 76452 ------------------------------ Message: 5 Date: Tue, 15 Oct 2013 23:25:19 -0700 From: Darren Duncan <dar...@darrenduncan.net> To: sqlite-users@sqlite.org Subject: Re: [sqlite] Trigger SQL and database schema Message-ID: <525e314f.8080...@darrenduncan.net> Content-Type: text/plain; charset=UTF-8; format=flowed On 2013.10.15 10:34 PM, Petite Abeille wrote: > On Oct 16, 2013, at 7:20 AM, Darren Duncan <dar...@darrenduncan.net> wrote: > >> On 2013.10.14 11:58 PM, Sqlite Dog wrote: >>> seems like SQLite is not checking trigger SQL for invalid column >>> names until execution? >> >> What you describe sounds like the behavior of every SQL DBMS which has triggers whose trigger behavior I know. > > Hmmm? FWIW? Oracle, for one, will invalidate triggers, views, packages, etc if their underlying tables change. > > There is even a very handy ALL_DEPENDENCIES views to track all the explicit interdependencies between objects: > > http://docs.oracle.com/cd/B28359_01/server.111/b28320/statviews_1066.h > tm#i1576452 But the key thing here, and my point, is that even Oracle wouldn't block the underlying tables change due to the invalidation of other schema objects that would result. Oracle would allow the invalid trigger/view/package definitions to exist, rather than requiring the user to temporarily delete those first or update their definitions simultaneously with the underlying tables thereby enforcing compatibility. This is what I'm talking about, that invalid trigger/etc definitions are allowed to exist, by every SQL DBMS whose behavior I know about, and SQLite matching that behavior would best be maintained. Not checking trigger/etc validity until execution makes it possible to separately change the tables and other objects depending on them, or for that matter, altering underlying tables again to bring them back into compatibility with other objects' expectations of them, at which point the triggers/etc would become valid again without having ever changed. -- Darren Duncan ------------------------------ Message: 6 Date: Wed, 16 Oct 2013 13:48:30 +0700 From: Dan Kennedy <danielk1...@gmail.com> To: sqlite-users@sqlite.org Subject: Re: [sqlite] FTS4 + spellfix1 with multiple languages Message-ID: <525e36be.6040...@gmail.com> Content-Type: text/plain; charset=UTF-8; format=flowed On 10/15/2013 08:13 PM, Raf Geens wrote: > Hi, > > > I have a FTS4 table that contains entries in multiple languages (using the languageid option). I also have a spellfix1 table that I use to search with misspelled words on the FTS4 table. In the spellfix1 documentation a fts4aux table is used to fill a spellfix1 table based on a FTS4 one. This works in a single-language scenario. However, I've found that the fts4aux table is empty if the languageid option is used on the FTS4 table. > > > My workaround for this has been to create temporary copies of the FTS4 table, one for each language, with the languageid column dropped. I can then use fts4aux and fill the spellfix1 table language by language. This feels like a big hack though. Have I missed a better way to do this? The upcoming 3.8.1 release adds a hidden languageid column to the fts4aux table: http://sqlite.org/draft/fts3.html#f4alid So you by adding "languageid=N" to the WHERE clause used to query the fts4aux table you can read the vocabulary belonging to languages with non-zero language-ids. Will this work for you? Dan. ------------------------------ Message: 7 Date: Wed, 16 Oct 2013 00:46:33 -0700 (PDT) From: mitzanu <mitz...@gmail.com> To: sqlite-users@sqlite.org Subject: [sqlite] JDBC Driver Issue Message-ID: <1381909593826-71705.p...@n5.nabble.com> Content-Type: text/plain; charset=us-ascii Sqlite JDBC : I have a bug in Jdbc driver. The method DatabaseMetaData.getColumns(String catalog, String schemaPattern, String tableNamePattern, String columnNamePattern) throws SQLException; does not correctly return the value in resultSet.getInt(11 ) - NULLABLE OR NOT. Please make sure that also the Primary Key columns have the correct value here. I consider a column mandatory ( not null ) if : DatabaseMetaData.columnNoNulls == rs.getInt(11) One more request for Sqlite : Would be great if you would support adding foreign keys after the table has been created. This is highly required by database design tools. All users are creating the foreign keys after the table has been created. This would be a great advantage for thouse who intend to a real database design. So please add commands for ALTER TABLE... ADD CONSTRAINT ... FOREIGN KEY ( col1, col2 ) REFERENCES ... ( col1, col2 ). -- View this message in context: http://sqlite.1065341.n5.nabble.com/JDBC-Driver-Issue-tp71705.html Sent from the SQLite mailing list archive at Nabble.com. ------------------------------ Message: 8 Date: Wed, 16 Oct 2013 00:48:58 -0700 (PDT) From: mitzanu <mitz...@gmail.com> To: sqlite-users@sqlite.org Subject: [sqlite] ALTER COLUMN in sqlite Message-ID: <1381909738857-71706.p...@n5.nabble.com> Content-Type: text/plain; charset=UTF-8 There's no ALTER COLUMN in sqlite. I believe the option is to: ?Rename the table to a temporary name ?Create a new table without the NOT NULL constraint ?Copy the content of the old table to the new one ?Remove the old table Can you guys implement ALTER COLUMN in sqlite? it would be a great feature. -- View this message in context: http://sqlite.1065341.n5.nabble.com/ALTER-COLUMN-in-sqlite-tp71706.html Sent from the SQLite mailing list archive at Nabble.com. ------------------------------ Message: 9 Date: Wed, 16 Oct 2013 10:40:31 +0200 From: Dominique Devienne <ddevie...@gmail.com> To: General Discussion of SQLite Database <sqlite-users@sqlite.org> Subject: [sqlite] Feature Request: Binding Arrays Message-ID: <cafcrh-81kpwgu6t-ww+7gjbtqdmjddsesxtbyvwiaobbxea...@mail.gmail.com> Content-Type: text/plain; charset=ISO-8859-1 We have an SQLite virtual-table heavy application with a lot of the GUI driven by SQL queries, and often times we have queries of the form select * from some_table where some_column in (...) where ... is coming from prior selections in the GUI, or filtering, etc... 1) In some places, we create temporary tables and join with those, instead of using the in (list) where clause. 2) In other places we synthesize the query text by splicing list.join(", ") in the in (list) where clause. (whether you splice the text of the values, or a series of %i and do proper binding makes little difference IMHO, in both cases you need to reparse). Both solutions are unsatisfactory, because with 1) you have to create dummy transient tables, for which you need to invent table names, insert, join with, and then delete/cleanup, and 2) constantly reparse and prepare queries, which can get super long if the array to "bind" is big. Any chance SQLite would add true array binding? For example, given create table t (name text, type text, primary key (name, type)); select * from t where type in (%1); and binding would look something like this: sqlite3_bind_array_begin(stmt, 1 [, types.size()]); // size param? for (const auto& type: types) { sqlite3_bind_text(stmt, 1, type.c_str(), type.size(), SQLITE_TRANSIENT); } sqlite3_bind_array_end(stmt, 1); Whether the API allows only homogeneous elements in the array (element type specified in the sqlite3_bind_array_begin) or it's the usual SQLite duck typing matters little me. Obviously I would welcome such a change. I have no clue how difficult to implement that is of course, but if somehow it could be added, and doesn't make SQLite that much bigger, then such an addition would be very much welcome. If I somehow missed a better work-around to this lack of array-binding, I'm also interested of course, but obviously I'd prefer real array binding. Thanks for any insight on this, --DD ------------------------------ Message: 10 Date: Wed, 16 Oct 2013 12:03:37 +0200 From: Clemens Ladisch <clem...@ladisch.de> To: sqlite-users@sqlite.org Subject: Re: [sqlite] Feature Request: Binding Arrays Message-ID: <525e6479.70...@ladisch.de> Content-Type: text/plain; charset=us-ascii Dominique Devienne wrote: > select * from some_table where some_column in (...) > > 2) In other places we synthesize the query text by splicing > list.join(", ") in the in (list) where clause. > > Both solutions are unsatisfactory, because ... 2) constantly reparse > and prepare queries, which can get super long if the array to "bind" is big. > > Any chance SQLite would add true array binding? The compiled statement depends on the number of elements, so SQLite would have to reprepare anyway: > .explain on > explain select 1 in (111,222,333); addr opcode p1 p2 p3 p4 p5 comment ---- ------------- ---- ---- ---- ------------- -- ------------- ... 8 Integer 111 3 0 00 9 MakeRecord 3 1 4 b 00 10 IdxInsert 1 4 0 00 11 Integer 222 3 0 00 12 MakeRecord 3 1 4 b 00 13 IdxInsert 1 4 0 00 14 Integer 333 3 0 00 15 MakeRecord 3 1 4 b 00 16 IdxInsert 1 4 0 00 ... Regards, Clemens ------------------------------ Message: 11 Date: Wed, 16 Oct 2013 13:25:41 +0200 From: Dominique Devienne <ddevie...@gmail.com> To: General Discussion of SQLite Database <sqlite-users@sqlite.org> Subject: Re: [sqlite] Feature Request: Binding Arrays Message-ID: <CAFCRh-_ZjrdrJXgAuo0NsPdi2MZFvnxK7YAdx9SpMOjA=9g...@mail.gmail.com> Content-Type: text/plain; charset=ISO-8859-1 On Wed, Oct 16, 2013 at 12:03 PM, Clemens Ladisch <clem...@ladisch.de>wrote: > Dominique Devienne wrote: > > select * from some_table where some_column in (...) > > > > 2) In other places we synthesize the query text by splicing > > list.join(", > ") > > in the in (list) where clause. > > > > Both solutions are unsatisfactory, because ... 2) constantly reparse > > and prepare queries, which can get super long if the array to "bind" is big. > > > > Any chance SQLite would add true array binding? > > The compiled statement depends on the number of elements, so SQLite > would have to reprepare anyway: > But isn't that a consequence of the fact that a in (list) where clause is necessary bounded and known at parse time? The same way I can manually transform the in (list) into a join to a temp table, so can the query optimizer. It already uses hidden intermediary result-sets for query processing, and the array would basically be one such internal (anonymous) hidden "result-set". The difference with the manual transform-into-join code I'm forced to do now is that SQLite wouldn't have to name the table and column to create the temp table, fill it, using, drop it, etc... These activities trigger authorizer hooks, trace hooks, change the (temp) schema, etc... (i.e. a bunch of DDL and DML statements) while SQLite itself, would it support array binding, would generate none of that monitored statement activity. Notice that I'm inquiring about array-binding for in (list) only, not for putting into table cells, not selecting them, not joining on them, etc... I'd love to be able to do that, but that's a different can of worms entirely. Thanks, --DD ------------------------------ Message: 12 Date: Wed, 16 Oct 2013 07:28:04 -0400 From: Richard Hipp <d...@sqlite.org> To: General Discussion of SQLite Database <sqlite-users@sqlite.org> Subject: Re: [sqlite] Feature Request: Binding Arrays Message-ID: <CALwJ=MxcKohR3Q3+2EaX+5VLb8yQhJwOo_FZ4dR5_+Jo5_Ac=q...@mail.gmail.com> Content-Type: text/plain; charset=ISO-8859-1 Please see http://www.sqlite.org/src/artifact/2ece66438?ln=13-75 -- D. Richard Hipp d...@sqlite.org ------------------------------ Message: 13 Date: Wed, 16 Oct 2013 13:39:50 +0200 From: Raf Geens <raf.ge...@mapscape.eu> To: General Discussion of SQLite Database <sqlite-users@sqlite.org> Subject: Re: [sqlite] FTS4 + spellfix1 with multiple languages Message-ID: <525e7b06.3090...@mapscape.eu> Content-Type: text/plain; charset=ISO-8859-1; format=flowed On 10/16/13 08:48, Dan Kennedy wrote: > On 10/15/2013 08:13 PM, Raf Geens wrote: >> Hi, >> >> I have a FTS4 table that contains entries in multiple languages >> (using the languageid option). I also have a spellfix1 table that I >> use to search with misspelled words on the FTS4 table. In the >> spellfix1 documentation a fts4aux table is used to fill a spellfix1 >> table based on a FTS4 one. This works in a single-language scenario. >> However, I've found that the fts4aux table is empty if the languageid >> option is used on the FTS4 table. >> >> My workaround for this has been to create temporary copies of the >> FTS4 table, one for each language, with the languageid column >> dropped. I can then use fts4aux and fill the spellfix1 table language >> by language. This feels like a big hack though. Have I missed a >> better way to do this? > > The upcoming 3.8.1 release adds a hidden languageid column > to the fts4aux table: > > http://sqlite.org/draft/fts3.html#f4alid > > So you by adding "languageid=N" to the WHERE clause used > to query the fts4aux table you can read the vocabulary > belonging to languages with non-zero language-ids. > > Will this work for you? > > Dan. Yes, that sounds ideal, thanks! ------------------------------ Message: 14 Date: Wed, 16 Oct 2013 13:40:48 +0200 From: Paul van Helden <p...@planetgis.co.za> To: General Discussion of SQLite Database <sqlite-users@sqlite.org> Subject: Re: [sqlite] Feature Request: Binding Arrays Message-ID: <capjgfzcko0xy6r8a0dzsyp_mphnv9pchknhhpy6n2bkpqo_...@mail.gmail.com> Content-Type: text/plain; charset=ISO-8859-1 Fantastic! I've been wanting this for a long time. Since which version do we have sqlite3_intarray_xxxxx? On Wed, Oct 16, 2013 at 1:28 PM, Richard Hipp <d...@sqlite.org> wrote: > Please see http://www.sqlite.org/src/artifact/2ece66438?ln=13-75 > > > -- > D. Richard Hipp > d...@sqlite.org > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ------------------------------ Message: 15 Date: Wed, 16 Oct 2013 11:51:30 +0000 From: Mike Clagett <mike.clag...@mathworks.com> To: "sqlite-users@sqlite.org" <sqlite-users@sqlite.org> Subject: [sqlite] Trying to figure out how to circumvent sqlite3_win32_mbcs_to_utf8 Message-ID: <1906f14e9cd63e419dabd460b290750907f6c...@exmb-00-ah.ad.mathworks.com> Content-Type: text/plain; charset="us-ascii" Hi - We have a C++ (VisualC++) app that is reading from and writing to a sqlite database. Profiling reveals that it is spending 883.437 of its 2160.988 seconds in the sqlite3_win32_mbcs_to_utf8 function. We are using std::basic_string<wchar_t,...> as our string type and I can only assume that these are being seen by sqlite as mbcs strings. I would like to know a better way of doing this that will eliminate all these unnecessary conversions. I believe it may end up being a combination of picking the correct string type (although using anything but the type we are using may be difficult if it contravenes a product-wide standard) and setting the defaults properly in sqlite. I have attempted the latter by issuing a m_db.executeStatement("PRAGMA encoding = \"UTF-16\"", error); just after I create a database. I clearly am not doing this effectively as it seems to have no effect on the use of the function in question. Any guidance from older hands would be greatly appreciated. Thanks. Michael Clagett Principal Software Engineer Mathworks, Inc. mike.clag...@mathworks.com<mailto:mike.clag...@mathworks.com> (508)-647-4307 ------------------------------ Message: 16 Date: Wed, 16 Oct 2013 07:51:48 -0400 From: Richard Hipp <d...@sqlite.org> To: General Discussion of SQLite Database <sqlite-users@sqlite.org> Subject: Re: [sqlite] Feature Request: Binding Arrays Message-ID: <CALwJ=mxockup6pdifkbze1lc2w2_ggkvqknpgkh4q4dhqnm...@mail.gmail.com> Content-Type: text/plain; charset=ISO-8859-1 On Wed, Oct 16, 2013 at 7:40 AM, Paul van Helden <p...@planetgis.co.za>wrote: > Fantastic! I've been wanting this for a long time. > > Since which version do we have sqlite3_intarray_xxxxx? > > Since version 3.6.21, circa 2009-12-07. Note however that this capability is not built in. It is an extension that you need to compile and link separately. -- D. Richard Hipp d...@sqlite.org ------------------------------ Message: 17 Date: Wed, 16 Oct 2013 08:07:07 -0400 From: Richard Hipp <d...@sqlite.org> To: General Discussion of SQLite Database <sqlite-users@sqlite.org> Subject: Re: [sqlite] Trying to figure out how to circumvent sqlite3_win32_mbcs_to_utf8 Message-ID: <CALwJ=MxDAV3Bw3quLcPaEwR0c=yafytmppfgom3wejdjn-1...@mail.gmail.com> Content-Type: text/plain; charset=ISO-8859-1 On Wed, Oct 16, 2013 at 7:51 AM, Mike Clagett <mike.clag...@mathworks.com>wrote: > Hi - > > We have a C++ (VisualC++) app that is reading from and writing to a sqlite > database. Profiling reveals that it is spending 883.437 of its 2160.988 > seconds in the sqlite3_win32_mbcs_to_utf8 function. Wow. That routine should only be called when (1) reporting a low-level I/O error and (2) creating a temporary filename. And both of those should be rare occurrences. 1. What does your profiler say is the most frequent caller to sqlite3_win32_mbcs_to_utf8()? 2. Have you enabled error logging? (http://www.sqlite.org/errlog.html) 3. Have you tried running with PRAGMA temp_store=MEMORY to see if that helps? 4. Please tell us which MathWorks products us SQLite, so that we can add them to http://www.sqlite.org/famous.html > We are using std::basic_string<wchar_t,...> as our string type and I can > only assume that these are being seen by sqlite as mbcs strings. No. The problem is that SQLite uses UTF8 for filenames and Windows uses MBCS for filenames and so we have to convert between the two when making Windows system calls such as opening new files. > I would like to know a better way of doing this that will eliminate all > these unnecessary conversions. I believe it may end up being a > combination of picking the correct string type (although using anything but > the type we are using may be difficult if it contravenes a product-wide > standard) and setting the defaults properly in sqlite. I have attempted > the latter by issuing a m_db.executeStatement("PRAGMA encoding = > \"UTF-16\"", error); just after I create a database. I clearly am not > doing this effectively as it seems to have no effect on the use of the > function in question. > > Any guidance from older hands would be greatly appreciated. > > Thanks. > > Michael Clagett > Principal Software Engineer > Mathworks, Inc. > mike.clag...@mathworks.com<mailto:mike.clag...@mathworks.com> > (508)-647-4307 > > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- D. Richard Hipp d...@sqlite.org ------------------------------ Message: 18 Date: Wed, 16 Oct 2013 07:32:08 -0500 From: John McKown <john.archie.mck...@gmail.com> To: General Discussion of SQLite Database <sqlite-users@sqlite.org> Subject: Re: [sqlite] ALTER COLUMN in sqlite Message-ID: <caajsdjgtet95f112zphyqczatd+wzxx0qeyyqgdzofztevm...@mail.gmail.com> Content-Type: text/plain; charset=windows-1252 I'm not truly against such a thing. But we need to remember the main use for SQLite is to be small, fast, and "embedded". At least as best as I can tell, it is not meant to compete with MariaDB (nee MySQL) or PostgreSQL. It doesn't appear to be _committed_ to being 100% SQL compliant (as in exactly matching the ANSI/ISO requirements). I can't say for sure, but would somebody want to store 20 million rows in a SQLite data base? Why? I really am curious. Perhaps I'm "out of touch" (won't be the first time). My use for SQLite is for storing smallish amount of data which is dedicated to a single application. My "large" data base needs are relegated to PostgreSQL data bases. IMO, the "proper" way to do this is just what you outlined. It is a "one shot" and should not take long to run in most cases. Unless those million row SQLite data bases are more prevalent than that I had ever thought On Wed, Oct 16, 2013 at 2:48 AM, mitzanu <mitz...@gmail.com> wrote: > There's no ALTER COLUMN in sqlite. > > I believe the option is to: > > ?Rename the table to a temporary name > ?Create a new table without the NOT NULL constraint > ?Copy the content of the old table to the new one > ?Remove the old table > > Can you guys implement ALTER COLUMN in sqlite? it would be a great feature. > > > > -- > View this message in context: > http://sqlite.1065341.n5.nabble.com/ALTER-COLUMN-in-sqlite-tp71706.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 > -- This is clearly another case of too many mad scientists, and not enough hunchbacks. Maranatha! <>< John McKown ------------------------------ Message: 19 Date: Wed, 16 Oct 2013 14:45:57 +0200 From: Paul van Helden <p...@planetgis.co.za> To: General Discussion of SQLite Database <sqlite-users@sqlite.org> Subject: Re: [sqlite] Feature Request: Binding Arrays Message-ID: <CAPjGFZcrM5vKyQok8jOBg1VTym24kp8=NShdqj-Eww5=h3s...@mail.gmail.com> Content-Type: text/plain; charset=ISO-8859-1 > Since version 3.6.21, circa 2009-12-07. Note however that this capability > is not built in. It is an extension that you need to compile and link > separately. > > OK... Herewith my vote to make it standard then, like SQLITE_ENABLE_COLUMN_METADATA was enabled for the precompiled binary at some point. I prefer to stick to the precompiled binaries. Besides, it would make the sqlite3_intarray functions more visible in the documentation, etc. I'm sure I'm not the only one that didn't know about this very useful functionality. It's about time the binary got slightly bigger ;-) ------------------------------ Message: 20 Date: Wed, 16 Oct 2013 18:40:15 +0530 From: techi eth <techi...@gmail.com> To: General Discussion of SQLite Database <sqlite-users@sqlite.org> Subject: Re: [sqlite] Feature Request: Binding Arrays Message-ID: <CAJw2sSDAM4fpYDa5Kn8+YTBzGcCMej=tlqjkmej5knpfpkt...@mail.gmail.com> Content-Type: text/plain; charset=ISO-8859-1 It is really useful feature. I have a use case where I need to log the data in continuous interval & store in database. If array type is supported by sqlite then in single row I can store data in array of time stamp & array of value. Is it specific to int type or any other data type can be supported? On Wed, Oct 16, 2013 at 6:15 PM, Paul van Helden <p...@planetgis.co.za>wrote: > > Since version 3.6.21, circa 2009-12-07. Note however that this > capability > > is not built in. It is an extension that you need to compile and link > > separately. > > > > OK... Herewith my vote to make it standard then, like > SQLITE_ENABLE_COLUMN_METADATA was enabled for the precompiled binary at > some point. > > I prefer to stick to the precompiled binaries. Besides, it would make the > sqlite3_intarray functions more visible in the documentation, etc. I'm sure > I'm not the only one that didn't know about this very useful functionality. > > It's about time the binary got slightly bigger ;-) > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ------------------------------ Message: 21 Date: Wed, 16 Oct 2013 16:29:57 +0200 From: Daniel Polski <dan...@agelektronik.se> To: General Discussion of SQLite Database <sqlite-users@sqlite.org> Subject: [sqlite] Analyze optimizing views? Message-ID: <525ea2e5.7030...@agelektronik.se> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Hello, Does the ANALYZE command gather statistics and optimize for views I've created or only "real tables"? Best regards, Daniel ------------------------------ Message: 22 Date: Wed, 16 Oct 2013 10:43:09 -0400 From: Richard Hipp <d...@sqlite.org> To: General Discussion of SQLite Database <sqlite-users@sqlite.org> Subject: Re: [sqlite] Analyze optimizing views? Message-ID: <CALwJ=My9k+j7_1HcJpcG8E7gCav4sDVnLJ1mfy_MVe=4au-...@mail.gmail.com> Content-Type: text/plain; charset=ISO-8859-1 On Wed, Oct 16, 2013 at 10:29 AM, Daniel Polski <dan...@agelektronik.se>wrote: > Hello, > Does the ANALYZE command gather statistics and optimize for views I've > created or only "real tables"? > Only real tables. Views are just macros that are applied to queries when the queries are run. If you have: CREATE TABLE t1(a,b,c); CREATE VIEW v1 AS SELECT a+b, c+a FROM t1; Then you do: SELECT * FROM v1; That's exactly the same as doing: SELECT * FROM (SELECT a+b, c+a FROM t1); -- D. Richard Hipp d...@sqlite.org ------------------------------ Message: 23 Date: Wed, 16 Oct 2013 17:02:57 +0200 From: a.furi...@lqt.it To: <sqlite-users@sqlite.org> Subject: Re: [sqlite] ALTER COLUMN in sqlite Message-ID: <17a7c45b930f3f4360860b6703637...@lqt.it> Content-Type: text/plain; charset=UTF-8; format=flowed On Wed, 16 Oct 2013 07:32:08 -0500, John McKown wrote: > I can't say for sure, but would somebody want to store 20 > million rows in a SQLite data base? Why? I really am curious > Hi John, you could eventually find interesting in some way my own first hand experiences in the Geographic/GeoSpatial field. in this very specific environment (certainly not the most common one, I agree) it's not at all exceptional deploying SQLite/SpatiaLite DB-files as big as 20/40 GB (and even more) containing several tenths/hundredths million rows disseminated in many hundredths different tables (aka layers in GIS jargon) strictly related the one to the other not only in the "classic" relational way based on Primary and Foreign Keys, but even in the "awkward spatial way" based on geometric and topological relationships. there are several good technical reasons suggesting to use exactly SQLite/SpatiaLite for processing, validating and distributing huge geographic datasets: - SQLite/SpatiaLite offers exactly the same overall level of standard Spatial SQL processing capabilities supported by the much more sophisticated (and complex) PostgreSQL/PostGIS; but it's by way simpler to be installed and configured, and it's usually faster under many common working conditions (mainly thanks to its really outstanding first class R*Tree implementation). - not to mention the SQLite's "forbidden weapon"; if your HW supports an adequate amount of RAM you can directly load a whole DB in memory at once; and under such a configuration you can easily reach dramatically impressive supersonic speeds. - you can directly copy / send a whole DB (even a really huge one) from a locations to another in a single shot and in the most painless way, because after all it simply is an ordinary file. - if something goes completely wrong during any complex Spatial data processing operation (it happens ... nobody's perfect), you simply have to remove a single file and then patiently restart yet again from scratch after correcting your buggish Spatial SQL scripts. under the same conditions fully recovering some client/server Spatial DBMS left in an inconsistent state could eventually be a not so pleasant and easy affair. - you can freely ATTACH and DETACH together many DB-files depending on your very variable specific requirements, thus achieving an outstanding and unconstrained flexibility. Quite often this is a really usefull feature, e.g. when you have to collect, integrate and merge together many different datasets presenting a very loose standardization because they were produced during a long period of time by many different subjects for different initial purposes. all this considered, my answer is: "oh yes, it makes perfectly sense storing 20 million rows in a SQLite DB; and it works absolutely well" :-D by Sandro ------------------------------ _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users End of sqlite-users Digest, Vol 70, Issue 16 ******************************************** _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
