Re: [sqlite] ftruncate implementation
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 23/01/12 22:43, David Henry wrote: I am working without an operating system so there are no other agents trying to steal data. Bearing that in mind, is it still necessary to actually write zero data to the sectors allocated? Is SQLite expecting it? You should ensure that your operating system still reports free space correctly to all other file system users (ie the space is actually allocated). For example on traditional Unix file systems you can open a file, seek to 1TB and write one byte. The file system will not report 1TB less free space. If you read in the space you'll get zeroes back. As you write it will come out of free space. But if the disk only had 10MB free you'll suddenly find writes failing even though you allocated the space. Trying to recover will get hairy depending on what writes will succeed or fail as the mess is cleaned up. You could (in theory, maybe not in practise) get the database into a state where SQLite can't put it back into a clean state by applying journals. Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.11 (GNU/Linux) iEYEARECAAYFAk8eZhEACgkQmOOfHg372QQBoQCfS7bsoSk+M8Irqv6ZTAElm3BC u0wAnjL8w6OblWe/dTI67PhIfsgxqD1n =DoRG -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] replying to thread
If you have an email client that can handle mailto:-prefixed link in the web page, the one just to the right of the respondee's name under the page title, it will get the right reference to link your response to the preceding post/email. Mozilla's Thunderbird is one such client, but many other can do this. Cheers, -- Larry Brasfield ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] storing and comparing dates in sqlite
It's your choice, really. You could store it as TEXT, in some standardised format (e.g. XML dateTime format: -mm-ddThh:mm:ss. plus a timezone offset if you wanted) or your own format, and query it back as text, or you could store it in UNIX integer time (seconds since unix epoch (01/01/1970)) - so in an INTEGER field, etc. Depends how you're going to use it. Either way I've suggested has pros/cons. Nick. -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of dotolee Sent: 23 January 2012 18:01 To: sqlite-users@sqlite.org Subject: Re: [sqlite] storing and comparing dates in sqlite can you point me in the right direction? aka. what data type am i using to store the date in my sqlite database? is TEXT correct? how do I do a select on it? thanks. Stephan Beal-3 wrote: On Mon, Jan 23, 2012 at 6:51 PM, dotolee woo_ju...@yahoo.com wrote: i'm new to sqlite... and relatively new to php. just wondering what the best way is to store and compare dates. For any given 10 developers you'll likely hear 11 opinions on this topic. IMO Unix timestamps are the most portable form out there. Portable meaning, in this context, the ability to work with them (more or less easily) in a wide variety of contexts. -- - stephan beal http://wanderinghorse.net/home/stephan/ http://gplus.to/sgbeal ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- View this message in context: http://old.nabble.com/storing-and-comparing-dates-in-sqlite-tp33189769p33189862.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] ftruncate implementation
On 24 Jan 2012, at 6:43am, David Henry wrote: I am working without an operating system so there are no other agents trying to steal data. You're writing an operating system ? That can be lots of fun. Good luck. Bearing that in mind, is it still necessary to actually write zero data to the sectors allocated? Is SQLite expecting it? I haven't looked though the code with that in mind, but as far as I know, SQLite does not make any assumption about what will be in newly-assigned sectors. If it wants zeros there it'll write them itself. It is ftruncate itself which chooses to write zeros to any newly-assigned pages of disk space. This is part of the specification of ftruncate, and documented here: http://pubs.opengroup.org/onlinepubs/007908799/xsh/ftruncate.html If your version of ftruncate doesn't write the zeros, you haven't implemented ftruncate properly. But if you're not trying to reproduce UNIX, I guess it doesn't matter. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] C# amalgamation hand holding requested
Ok. So I am doing things correctly. I am already doing things correctly. I am using the provided .NET dll. I copy it to my local project. And when that project moves to production, it will be in the project's .bin folder. I should not have to worry about the dll hell associated with HP and Quicken and what not. Thanks. I was getting confused. dvn On Mon, Jan 23, 2012 at 2:03 PM, Simon Slavin slav...@bigfraud.org wrote: On 23 Jan 2012, at 7:39pm, Roosevelt Anderson wrote: The C# compiler does not compile C. C# and VB.NET get compiled down to bytecode. On Mon, Jan 23, 2012 at 12:56 PM, Simon Slavin slav...@bigfraud.org wrote: On 23 Jan 2012, at 4:39pm, Roosevelt Anderson wrote: You can't use the amalgamation directly in C# as the SQLite amalgamation is in C. Can you not simply tell your compiler that '.c' files are C and not C# ? That's what you do with Objective-C. Or do C# compilers not compile C ? Ah. So C# is not a superset of C. That explains things. Thanks. 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] replying to thread
Testing with Microsoft Outlook ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] C# amalgamation hand holding requested
Yes -- you do need to worry. Plusyou apparently should rename the DLL so it doesn't collide with any others from what I can discern from this: http://msdn.microsoft.com/en-us/library/windows/desktop/ms682586(v=vs.85).aspx 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 Don V Nielsen [donvniel...@gmail.com] Sent: Tuesday, January 24, 2012 9:39 AM To: General Discussion of SQLite Database Subject: EXT :Re: [sqlite] C# amalgamation hand holding requested Ok. So I am doing things correctly. I am already doing things correctly. I am using the provided .NET dll. I copy it to my local project. And when that project moves to production, it will be in the project's .bin folder. I should not have to worry about the dll hell associated with HP and Quicken and what not. Thanks. I was getting confused. dvn On Mon, Jan 23, 2012 at 2:03 PM, Simon Slavin slav...@bigfraud.org wrote: On 23 Jan 2012, at 7:39pm, Roosevelt Anderson wrote: The C# compiler does not compile C. C# and VB.NET get compiled down to bytecode. On Mon, Jan 23, 2012 at 12:56 PM, Simon Slavin slav...@bigfraud.org wrote: On 23 Jan 2012, at 4:39pm, Roosevelt Anderson wrote: You can't use the amalgamation directly in C# as the SQLite amalgamation is in C. Can you not simply tell your compiler that '.c' files are C and not C# ? That's what you do with Objective-C. Or do C# compilers not compile C ? Ah. So C# is not a superset of C. That explains things. Thanks. 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] C# amalgamation hand holding requested
This is .NET development not regular Windows development. The .NET application most of the time will use the version of System.Data.SQLite.dll that is in the same directory as the executing application. The only time it will not use this version is if you load System.Data.SQLite.dll into the Global Assembly Cache which I do not recommend. On Tue, Jan 24, 2012 at 10:49 AM, Black, Michael (IS) michael.bla...@ngc.com wrote: Yes -- you do need to worry. Plusyou apparently should rename the DLL so it doesn't collide with any others from what I can discern from this: http://msdn.microsoft.com/en-us/library/windows/desktop/ms682586(v=vs.85).aspx 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 Don V Nielsen [donvniel...@gmail.com] Sent: Tuesday, January 24, 2012 9:39 AM To: General Discussion of SQLite Database Subject: EXT :Re: [sqlite] C# amalgamation hand holding requested Ok. So I am doing things correctly. I am already doing things correctly. I am using the provided .NET dll. I copy it to my local project. And when that project moves to production, it will be in the project's .bin folder. I should not have to worry about the dll hell associated with HP and Quicken and what not. Thanks. I was getting confused. dvn On Mon, Jan 23, 2012 at 2:03 PM, Simon Slavin slav...@bigfraud.org wrote: On 23 Jan 2012, at 7:39pm, Roosevelt Anderson wrote: The C# compiler does not compile C. C# and VB.NET get compiled down to bytecode. On Mon, Jan 23, 2012 at 12:56 PM, Simon Slavin slav...@bigfraud.org wrote: On 23 Jan 2012, at 4:39pm, Roosevelt Anderson wrote: You can't use the amalgamation directly in C# as the SQLite amalgamation is in C. Can you not simply tell your compiler that '.c' files are C and not C# ? That's what you do with Objective-C. Or do C# compilers not compile C ? Ah. So C# is not a superset of C. That explains things. Thanks. 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 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Found it
Richard, Your last request for some detailed information about what queries were being processed did the trick. Here is a description of the problem (and yes, it appears to be in SQLite). In 3.7.5, the number one malloc consumer is INSERT INTO SEQUENCE_ELEMENTS ( SEQUENCE_ELEMENT_OID, SEQUENCE_ELEMENT_NAME, definition_parent, instance_parent ) values ( :SEQUENCE_ELEMENT_OID, :SEQUENCE_ELEMENT_NAME, :definition_parent, :instance_parent ) Times called: 1 Cumulative Allocated Memory: 12,856 Count of _malloc Calls:169 Cumulative Reallocated Memory: 13,544 Count of _realloc Calls: 24 Cumulative Reallocated Memory where nil: 0 Count of _realloc Calls where nil: 0 Count of _free Calls: 111 Cumulative _mallocs by size = 1kb:9,168 bytes (166 count; 55 avg) 1kb to 4kb:3,688 bytes (3 count; 1,229 avg) 4bk to 8kb:0 bytes (0 count; NAN avg) 8bk to 16kb: 0 bytes (0 count; NAN avg) 16bk to 32kb: 0 bytes (0 count; NAN avg) 32bk to 64kb: 0 bytes (0 count; NAN avg) 64bk to 128kb: 0 bytes (0 count; NAN avg) 128kb to 256kb:0 bytes (0 count; NAN avg) 256kb to 512kb:0 bytes (0 count; NAN avg) 512kb to 1024kb: 0 bytes (0 count; NAN avg) 1mb: 0 bytes (0 count; NAN avg) Cumulative _reallocs by size = 1kb:1,272 bytes (20 count; 64 avg) 1kb to 4kb:12,272 bytes (4 count; 3,068 avg) 4bk to 8kb:0 bytes (0 count; NAN avg) 8bk to 16kb: 0 bytes (0 count; NAN avg) 16bk to 32kb: 0 bytes (0 count; NAN avg) 32bk to 64kb: 0 bytes (0 count; NAN avg) 64bk to 128kb: 0 bytes (0 count; NAN avg) 128kb to 256kb:0 bytes (0 count; NAN avg) 256kb to 512kb:0 bytes (0 count; NAN avg) 512kb to 1024kb: 0 bytes (0 count; NAN avg) 1mb: 0 bytes (0 count; NAN avg) No reallocs of nil pointers --- However, in 3.7.6, the following is the number one consumer of mallocs: CREATE TRIGGER sequence_elements_after_insert after insert on sequence_elements begin update sequence_elements set sort_key = sequence_element_oid where sequence_element_oid = new.sequence_element_oid and sort_key is null; insert into responses (definition_parent, instance_parent, response_name, prelisted_value) select de.data_element_oid, new.sequence_element_oid, ag.initial_value, '' from data_elements de, attribute_groups ag where de.definition_parent = new.definition_parent and de.attribute_group = ag.attribute_group_name; insert or ignore into altered_sequences values(new.definition_parent); end Times called: 30502 Cumulative Allocated Memory: 255,240,736 Count of _malloc Calls:3,080,702 Cumulative Reallocated Memory: 3,904,256 Count of _realloc Calls: 30,502 Cumulative Reallocated Memory where nil: 0 Count of _realloc Calls where nil: 0 Count of _free Calls: 1,799,618 Cumulative _mallocs by size = 1kb:174,227,424 bytes (3,019,698 count; 58 avg) 1kb to 4kb:81,013,312 bytes (61,004 count; 1,328 avg) 4bk to 8kb:0 bytes (0 count; NAN avg) 8bk to 16kb: 0 bytes (0 count; NAN avg) 16bk to 32kb: 0 bytes (0 count; NAN avg) 32bk to 64kb: 0 bytes (0 count; NAN avg) 64bk to 128kb: 0 bytes (0 count; NAN avg) 128kb to 256kb:0 bytes (0 count; NAN avg) 256kb to 512kb:0 bytes (0 count; NAN avg) 512kb to 1024kb: 0 bytes (0 count; NAN avg) 1mb: 0 bytes (0 count; NAN avg) Cumulative _reallocs by size = 1kb:3,904,256 bytes (30,502 count; 128 avg) 1kb to 4kb:0 bytes (0 count; NAN avg) 4bk to 8kb:0 bytes (0 count; NAN avg) 8bk to 16kb: 0 bytes (0 count; NAN avg) 16bk to 32kb: 0 bytes (0 count; NAN avg) 32bk to 64kb: 0 bytes (0 count; NAN avg) 64bk to 128kb: 0 bytes (0 count; NAN avg) 128kb to 256kb:0 bytes (0 count; NAN avg) 256kb to
Re: [sqlite] Incompatible versions of SQLite on same system
Joe, Good to know! I had been looking just for sqlite3.dll. I don't have access to the machine right now, but I do have a clone of its system drive, and a search for sqlite turned up: System.Data.SQLite.Linq.DLL (more than one copy) System.Data.SQLite.DLL (more than one copy) System.Data.SQLite64.DLL (probably because it's 64-bit W7) At the time the clone was made, HP Connection Manager wasn't on it, but HP Power Assistant was, and the latter's directory in Program Files contains both System.Data.SQLite.DLL and System.Data.SQLite64.DLL, so there's a good chance that HP Connection Manager will have those, too (I am not willing to uninstall Intuit's TurboTax at this time, so there's no way to get HP Connection Manager installed to determine for sure what SQLite-related DLLs it installs). Regards, Joe Original Message Subject: Re: [sqlite] Incompatible versions of SQLite on same system From: Joe Mistachkin sql...@mistachkin.com To: 'General Discussion of SQLite Database' sqlite-users@sqlite.org Date: Thu Jan 19 2012 10:06:09 GMT-0600 (Central Standard Time) Another thing to keep in mind is that the System.Data.SQLite project compiles the native code for SQLite into files named SQLite.Interop.dll and System.Data.SQLite.dll (mixed-mode assembly), depending on the build configuration. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Found it
Addendum. After looking further it appears that the reparsing is happening independently of triggering. In this case, the reparsing occurs 30,502 times for every view and trigger I have examined. Only one trigger appears in the 3.7.5 listing and it is parsed exactly 1 time. 3.7.5 CREATE TRIGGER error_warning_after_delete after delete on local_errors_warnings begin insert into local_error_warning_deletions values( old.error_warning_oid); update alerter_links set is_dirty = 1where sequence_instance_oid = old.sequence_instance_oid and response_definition_oid in( select response_definition_oidfrom behavior_independents where behavior_oid = old.behavior_oid); end Times called: 1 Cumulative Allocated Memory: 5,960 Count of _malloc Calls:56 Cumulative Reallocated Memory: 0 Count of _realloc Calls: 0 Cumulative Reallocated Memory where nil: 0 Count of _realloc Calls where nil: 0 Count of _free Calls: 32 Cumulative _mallocs by size = 1kb:3,304 bytes (54 count; 61 avg) 1kb to 4kb:2,656 bytes (2 count; 1,328 avg) 4bk to 8kb:0 bytes (0 count; NAN avg) 8bk to 16kb: 0 bytes (0 count; NAN avg) 16bk to 32kb: 0 bytes (0 count; NAN avg) 32bk to 64kb: 0 bytes (0 count; NAN avg) 64bk to 128kb: 0 bytes (0 count; NAN avg) 128kb to 256kb:0 bytes (0 count; NAN avg) 256kb to 512kb:0 bytes (0 count; NAN avg) 512kb to 1024kb: 0 bytes (0 count; NAN avg) 1mb: 0 bytes (0 count; NAN avg) No reallocs No reallocs of nil pointers --- 3.7.6 CREATE TRIGGER error_warning_after_delete after delete on local_errors_warnings begin insert into local_error_warning_deletions values( old.error_warning_oid); update alerter_links set is_dirty = 1where sequence_instance_oid = old.sequence_instance_oid and response_definition_oid in( select response_definition_oidfrom behavior_independents where behavior_oid = old.behavior_oid); end Times called: 30502 Cumulative Allocated Memory: 180,327,824 Count of _malloc Calls:1,708,112 Cumulative Reallocated Memory: 0 Count of _realloc Calls: 0 Cumulative Reallocated Memory where nil: 0 Count of _realloc Calls where nil: 0 Count of _free Calls: 976,064 Cumulative _mallocs by size = 1kb:99,314,512 bytes (1,647,108 count; 60 avg) 1kb to 4kb:81,013,312 bytes (61,004 count; 1,328 avg) 4bk to 8kb:0 bytes (0 count; NAN avg) 8bk to 16kb: 0 bytes (0 count; NAN avg) 16bk to 32kb: 0 bytes (0 count; NAN avg) 32bk to 64kb: 0 bytes (0 count; NAN avg) 64bk to 128kb: 0 bytes (0 count; NAN avg) 128kb to 256kb:0 bytes (0 count; NAN avg) 256kb to 512kb:0 bytes (0 count; NAN avg) 512kb to 1024kb: 0 bytes (0 count; NAN avg) 1mb: 0 bytes (0 count; NAN avg) No reallocs No reallocs of nil pointers --- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] C# amalgamation hand holding requested
And what's your reference for that? It can't load your DLL if an already-same-named DLL is loaded by some other app. Or is there another reference page from Microsoft which contradicts the one I sent which explains that? If the sqlite guys would put the version# in the DLL name that would help a LOT. 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 Roosevelt Anderson [roosevelt.ander...@gmail.com] Sent: Tuesday, January 24, 2012 10:22 AM To: General Discussion of SQLite Database Subject: EXT :Re: [sqlite] C# amalgamation hand holding requested This is .NET development not regular Windows development. The .NET application most of the time will use the version of System.Data.SQLite.dll that is in the same directory as the executing application. The only time it will not use this version is if you load System.Data.SQLite.dll into the Global Assembly Cache which I do not recommend. On Tue, Jan 24, 2012 at 10:49 AM, Black, Michael (IS) michael.bla...@ngc.com wrote: Yes -- you do need to worry. Plusyou apparently should rename the DLL so it doesn't collide with any others from what I can discern from this: http://msdn.microsoft.com/en-us/library/windows/desktop/ms682586(v=vs.85).aspx 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 Don V Nielsen [donvniel...@gmail.com] Sent: Tuesday, January 24, 2012 9:39 AM To: General Discussion of SQLite Database Subject: EXT :Re: [sqlite] C# amalgamation hand holding requested Ok. So I am doing things correctly. I am already doing things correctly. I am using the provided .NET dll. I copy it to my local project. And when that project moves to production, it will be in the project's .bin folder. I should not have to worry about the dll hell associated with HP and Quicken and what not. Thanks. I was getting confused. dvn On Mon, Jan 23, 2012 at 2:03 PM, Simon Slavin slav...@bigfraud.org wrote: On 23 Jan 2012, at 7:39pm, Roosevelt Anderson wrote: The C# compiler does not compile C. C# and VB.NET get compiled down to bytecode. On Mon, Jan 23, 2012 at 12:56 PM, Simon Slavin slav...@bigfraud.org wrote: On 23 Jan 2012, at 4:39pm, Roosevelt Anderson wrote: You can't use the amalgamation directly in C# as the SQLite amalgamation is in C. Can you not simply tell your compiler that '.c' files are C and not C# ? That's what you do with Objective-C. Or do C# compilers not compile C ? Ah. So C# is not a superset of C. That explains things. Thanks. 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 ___ 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] Found it
While reviewing our other thread, I noticed a piece of information concerning sqlite3ResetInternalSchema. Based on the number of times that I reported it had been called, I believe that something was changed between 3.7.5 and 3.7.6 which causes sqlite3ResetInternalSchema to be called when it wasn't in the earlier version. Is there some kind of enumeration of what events would cause sqlite3ResetInternalSchema to be called in version 3.7.5 vs 3.7.6? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Found it
On Tue, Jan 24, 2012 at 11:59 AM, John Elrick john.elr...@fenestra.comwrote: While reviewing our other thread, I noticed a piece of information concerning sqlite3ResetInternalSchema. Based on the number of times that I reported it had been called, I believe that something was changed between 3.7.5 and 3.7.6 which causes sqlite3ResetInternalSchema to be called when it wasn't in the earlier version. Is there some kind of enumeration of what events would cause sqlite3ResetInternalSchema to be called in version 3.7.5 vs 3.7.6? I agree that the fact that the triggers are being reparsed is a big hint. But we still don't know why they are being reparsed. And we still cannot reproduce the problem in a command-line shell - in only seems to occur within your Delphi app. Can you put a breakpoint on sqlite3ResetInternalSchema() and figure out from whence it is being called so often? ___ 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
Re: [sqlite] Found it
On Tue, Jan 24, 2012 at 12:06 PM, Richard Hipp d...@sqlite.org wrote: On Tue, Jan 24, 2012 at 11:59 AM, John Elrick john.elr...@fenestra.com wrote: While reviewing our other thread, I noticed a piece of information concerning sqlite3ResetInternalSchema. Based on the number of times that I reported it had been called, I believe that something was changed between 3.7.5 and 3.7.6 which causes sqlite3ResetInternalSchema to be called when it wasn't in the earlier version. Is there some kind of enumeration of what events would cause sqlite3ResetInternalSchema to be called in version 3.7.5 vs 3.7.6? I agree that the fact that the triggers are being reparsed is a big hint. But we still don't know why they are being reparsed. And we still cannot reproduce the problem in a command-line shell - in only seems to occur within your Delphi app. Can you put a breakpoint on sqlite3ResetInternalSchema() and figure out from whence it is being called so often? Just did. Most of the calls are bubbling up from _sqlite3_step which I mentioned before. There are no new changes to the schema immediately prior to these calls. Here is one example where step triggers a call to sqlite3ResetInternalSchema() (apologies for using Delphi and our wrappers, but I hope it's clear): itsSaveTabsheet := itsDatabase.prepareExec('INSERT INTO TABSHEETS VALUES (null, :workbookType, ' + ':manifestOid, :surveyOid, :formDefinitionId, :prefKey, ' + ':name, :originalName, :sequence, :columnList)'); ... function TDataServices.saveTabsheet(const tabsheetId, aWorkbookType, manifestOid, surveyOid, formDefinitionOid, aPrefKey, aName, aSeq, aColumnList: string): string; begin itsSaveTabsheet := itsSaveTabsheet; itsSaveTabsheet.bind(':workbookType', aWorkbookType); itsSaveTabsheet.bind(':manifestOid', manifestOid); itsSaveTabsheet.bind(':surveyOid', surveyOid); itsSaveTabsheet.bind(':formDefinitionId', StrToIntDef(formDefinitionOid, 0)); itsSaveTabsheet.bind(':prefKey', aPrefKey); itsSaveTabsheet.bind(':name', aName); itsSaveTabsheet.bind(':originalName', aName); itsSaveTabsheet.bind(':sequence', aSeq); itsSaveTabsheet.bind(':columnList', aColumnList); itsSaveTabsheet.execute; result := IntToStr(itsDatabase.lastInsertRowId); end; ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] C# amalgamation hand holding requested
.NET code is compiled to byte code that runs on the Common Language Runtime (CLR) which is a virtual machine. Each .NET application creates an instance of the CLR. This is getting outside of the scope of this list. If you need more information I would research the architecture of NET. On Tue, Jan 24, 2012 at 11:41 AM, Black, Michael (IS) michael.bla...@ngc.com wrote: And what's your reference for that? It can't load your DLL if an already-same-named DLL is loaded by some other app. Or is there another reference page from Microsoft which contradicts the one I sent which explains that? If the sqlite guys would put the version# in the DLL name that would help a LOT. 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 Roosevelt Anderson [roosevelt.ander...@gmail.com] Sent: Tuesday, January 24, 2012 10:22 AM To: General Discussion of SQLite Database Subject: EXT :Re: [sqlite] C# amalgamation hand holding requested This is .NET development not regular Windows development. The .NET application most of the time will use the version of System.Data.SQLite.dll that is in the same directory as the executing application. The only time it will not use this version is if you load System.Data.SQLite.dll into the Global Assembly Cache which I do not recommend. On Tue, Jan 24, 2012 at 10:49 AM, Black, Michael (IS) michael.bla...@ngc.com wrote: Yes -- you do need to worry. Plusyou apparently should rename the DLL so it doesn't collide with any others from what I can discern from this: http://msdn.microsoft.com/en-us/library/windows/desktop/ms682586(v=vs.85).aspx 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 Don V Nielsen [donvniel...@gmail.com] Sent: Tuesday, January 24, 2012 9:39 AM To: General Discussion of SQLite Database Subject: EXT :Re: [sqlite] C# amalgamation hand holding requested Ok. So I am doing things correctly. I am already doing things correctly. I am using the provided .NET dll. I copy it to my local project. And when that project moves to production, it will be in the project's .bin folder. I should not have to worry about the dll hell associated with HP and Quicken and what not. Thanks. I was getting confused. dvn On Mon, Jan 23, 2012 at 2:03 PM, Simon Slavin slav...@bigfraud.org wrote: On 23 Jan 2012, at 7:39pm, Roosevelt Anderson wrote: The C# compiler does not compile C. C# and VB.NET get compiled down to bytecode. On Mon, Jan 23, 2012 at 12:56 PM, Simon Slavin slav...@bigfraud.org wrote: On 23 Jan 2012, at 4:39pm, Roosevelt Anderson wrote: You can't use the amalgamation directly in C# as the SQLite amalgamation is in C. Can you not simply tell your compiler that '.c' files are C and not C# ? That's what you do with Objective-C. Or do C# compilers not compile C ? Ah. So C# is not a superset of C. That explains things. Thanks. 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 ___ 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] Found it
On Tue, Jan 24, 2012 at 12:15 PM, John Elrick john.elr...@fenestra.comwrote: Can you put a breakpoint on sqlite3ResetInternalSchema() and figure out from whence it is being called so often? Just did. Most of the calls are bubbling up from _sqlite3_step which I mentioned before. There are no new changes to the schema immediately prior to these calls. Here is one example where step triggers a call to sqlite3ResetInternalSchema() (apologies for using Delphi and our wrappers, but I hope it's clear): That information isn't really useful. I still have no idea what is wrong, nor idea how to recreate the observed behavior. Let's bisect to find the specific check-in that is causing your problem. Here are two amalgamations that are in between 3.7.5 and 3.7.6. Please let me know how these work, and based on that will continue to narrow down the changes. http://www.sqlite.org/fenstra/sqlite-201103081639.zip http://www.sqlite.org/fenstra/sqlite-201103241737.zip -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Found it
On 01/25/2012 12:15 AM, John Elrick wrote: On Tue, Jan 24, 2012 at 12:06 PM, Richard Hippd...@sqlite.org wrote: On Tue, Jan 24, 2012 at 11:59 AM, John Elrickjohn.elr...@fenestra.com wrote: While reviewing our other thread, I noticed a piece of information concerning sqlite3ResetInternalSchema. Based on the number of times that I reported it had been called, I believe that something was changed between 3.7.5 and 3.7.6 which causes sqlite3ResetInternalSchema to be called when it wasn't in the earlier version. Is there some kind of enumeration of what events would cause sqlite3ResetInternalSchema to be called in version 3.7.5 vs 3.7.6? I agree that the fact that the triggers are being reparsed is a big hint. But we still don't know why they are being reparsed. And we still cannot reproduce the problem in a command-line shell - in only seems to occur within your Delphi app. Can you put a breakpoint on sqlite3ResetInternalSchema() and figure out from whence it is being called so often? Just did. Most of the calls are bubbling up from _sqlite3_step which I mentioned before. Can you see the whole call stack? There are a couple of different ways sqlite3ResetInternalSchema() may be called from within sqlite3_step(). Knowing which will be a clue as to why it is being called. Dan. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Found it
On Tue, Jan 24, 2012 at 1:18 PM, Dan Kennedy danielk1...@gmail.com wrote: On 01/25/2012 12:15 AM, John Elrick wrote: On Tue, Jan 24, 2012 at 12:06 PM, Richard Hippd...@sqlite.org wrote: On Tue, Jan 24, 2012 at 11:59 AM, John Elrickjohn.elrick@fenestra.**comjohn.elr...@fenestra.com wrote: While reviewing our other thread, I noticed a piece of information concerning sqlite3ResetInternalSchema. Based on the number of times that I reported it had been called, I believe that something was changed between 3.7.5 and 3.7.6 which causes sqlite3ResetInternalSchema to be called when it wasn't in the earlier version. Is there some kind of enumeration of what events would cause sqlite3ResetInternalSchema to be called in version 3.7.5 vs 3.7.6? I agree that the fact that the triggers are being reparsed is a big hint. But we still don't know why they are being reparsed. And we still cannot reproduce the problem in a command-line shell - in only seems to occur within your Delphi app. Can you put a breakpoint on sqlite3ResetInternalSchema() and figure out from whence it is being called so often? Just did. Most of the calls are bubbling up from _sqlite3_step which I mentioned before. Can you see the whole call stack? There are a couple of different ways sqlite3ResetInternalSchema() may be called from within sqlite3_step(). Knowing which will be a clue as to why it is being called. Unfortunately, no I cannot. Delphi 2007 doesn't give me accurate information from a linked OBJ. It's roughly equivalent to trying to get accurate call stacks from inside a DLL. I've had to hand add pseudo call stack information. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Found it
On Tue, Jan 24, 2012 at 12:53 PM, Richard Hipp d...@sqlite.org wrote: On Tue, Jan 24, 2012 at 12:15 PM, John Elrick john.elr...@fenestra.com wrote: SNIP http://www.sqlite.org/fenstra/sqlite-201103081639.zip This one gives the high number of mallocs just as does 3.7.6. http://www.sqlite.org/fenstra/sqlite-201103241737.zip This one is failing to compile. It needs _msize. That sounds like the 3.7.10 issue reported recently. John ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Found it
You can compile sqlite3.dll with debug information, and then you can in VS attach to your running .exe - you should be able to see sqlite stacks/breakpoints/step-in/out/etc. should also work. Best regards, Krystian Bigaj On 24 January 2012 19:25, John Elrick john.elr...@fenestra.com wrote: On Tue, Jan 24, 2012 at 1:18 PM, Dan Kennedy danielk1...@gmail.com wrote: On 01/25/2012 12:15 AM, John Elrick wrote: Unfortunately, no I cannot. Delphi 2007 doesn't give me accurate information from a linked OBJ. It's roughly equivalent to trying to get accurate call stacks from inside a DLL. I've had to hand add pseudo call stack information. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Found it
On Tue, Jan 24, 2012 at 1:29 PM, Krystian Bigaj krystian.bi...@gmail.comwrote: You can compile sqlite3.dll with debug information, and then you can in VS attach to your running .exe - you should be able to see sqlite stacks/breakpoints/step-in/out/etc. should also work. Thanks very much for the suggestion. We do not have, nor have any intention of using, Visual Studio. It's also not a DLL, the resulting output is a Borland OBJ, which cannot be generated correctly from any other compiler. John ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Found it
On Tue, Jan 24, 2012 at 1:28 PM, John Elrick john.elr...@fenestra.comwrote: SNIP http://www.sqlite.org/fenstra/sqlite-201103241737.zip This one is failing to compile. It needs _msize. That sounds like the 3.7.10 issue reported recently. I opened the sqlite3.c file and it is version 3.7.10. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] ftruncate implementation
From the ftruncate page: If the file size is increased, the extended area shall appear as if it were zero-filled. It doesn't have to write zeros, just act like it did. --David Garfield On Tue, Jan 24, 2012 at 08:19, Simon Slavin slav...@bigfraud.org wrote: On 24 Jan 2012, at 6:43am, David Henry wrote: I am working without an operating system so there are no other agents trying to steal data. You're writing an operating system ? That can be lots of fun. Good luck. Bearing that in mind, is it still necessary to actually write zero data to the sectors allocated? Is SQLite expecting it? I haven't looked though the code with that in mind, but as far as I know, SQLite does not make any assumption about what will be in newly-assigned sectors. If it wants zeros there it'll write them itself. It is ftruncate itself which chooses to write zeros to any newly-assigned pages of disk space. This is part of the specification of ftruncate, and documented here: http://pubs.opengroup.org/onlinepubs/007908799/xsh/ftruncate.html If your version of ftruncate doesn't write the zeros, you haven't implemented ftruncate properly. But if you're not trying to reproduce UNIX, I guess it doesn't matter. 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] Found it
On 24 January 2012 19:32, John Elrick john.elr...@fenestra.com wrote: On Tue, Jan 24, 2012 at 1:29 PM, Krystian Bigaj krystian.bi...@gmail.com wrote: You can compile sqlite3.dll with debug information, and then you can in VS attach to your running .exe - you should be able to see sqlite stacks/breakpoints/step-in/out/etc. should also work. Thanks very much for the suggestion. We do not have, nor have any intention of using, Visual Studio. It's also not a DLL, the resulting output is a Borland OBJ, which cannot be generated correctly from any other compiler. So maybe you could try build OBJ with debug/remote options/symbols (tdebug?). And then try to attach to running process from C++ Builder IDE (if it's not plain Borland C++ compiler). I've never used it, but it might work. PS. I forgot also that VS doesn't support debugging code if there is more than 64K lines in source file. Best regards, Krystian Bigaj ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] makefile for c
On Sun, Jan 15, 2012 at 15:17:37 -0600, Bill McCormick wrote: Tim Streater wrote, On 1/15/2012 3:00 PM: On 15 Jan 2012 at 20:44, Bill McCormickwpmccorm...@gmail.com wrote: What is the problem with the shared lib stuff? Black, Michael (IS) wrote, On 1/15/2012 2:27 PM: A simple one -- and please compile sqlite3.c into your program and make everybody happy. Forget the shared library stuff as we have just been talking about. The problem is that the computer vendor installs a shared lib with a version of the library. Some 3rd party app installer then replaced that shared lib with another version, and existing apps don't like it. SQLite is small enough that it can be compiled and linked in in its entirety. I am quite certain Debian requires packages to be linked dynamically to libraries that are packaged, the strongest reason being security support. Of course since Debian package declares dependency and versions, 3rd party installer won't just replace the shared library with incompatible version. OK. I don't see this as a problem for my use case. It would be better for me to stick with the shared libs. What is the difference between the two libs: libsqlite.so.0 and libsqlite3.so.0? I assume that I'll be linking to libsqlite3. When you are compiling, you link against the .so file, which is symlink to the actual .so.0 file. That file is provided by 'libsqlite3-dev' package and is called '/usr/lib/libsqlite3.so'. The corresponding linker option is '-lsqlite3'. Since the header is installed as '/usr/include/sqlite3.h', you don't need any special options for compilation step. To see where the files are, use 'dpkg -L libsqlite3-dev'. -- Jan 'Bulb' Hudec b...@ucw.cz ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] ftruncate implementation
On 24 Jan 2012, at 6:51pm, David Garfield wrote: From the ftruncate page: If the file size is increased, the extended area shall appear as if it were zero-filled. It doesn't have to write zeros, just act like it did. That's a bit of handwaving. What happens is that the file system doesn't itself write zeros to the disk surface. It puts zeros into the cache of the sector in memory and marks that sector as dirty (i.e. needing to eventually be written back to disk). So if the program doesn't write to that sector and just releases it, those zeros do eventually get written to disk. But if the program does write to that sector the disk surface only gets touched once, not twice, which is faster. So it doesn't write zeros to disk, but it does write zeros to the bit of memory that represents that bit of disk, and arrange that (unless the program overwrites them) they'll be written to disk eventually. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Found it
On Tue, Jan 24, 2012 at 12:53 PM, Richard Hipp d...@sqlite.org wrote: On Tue, Jan 24, 2012 at 12:15 PM, John Elrick john.elr...@fenestra.com wrote: Can you put a breakpoint on sqlite3ResetInternalSchema() and figure out from whence it is being called so often? Just did. Most of the calls are bubbling up from _sqlite3_step which I mentioned before. Richard, I added some tracking which keeps a log of every query and writes the last 50 things done when the call to sqlite3ResetInternalSchema() takes place. Here is a fairly representative entry. The last SQL is the one which triggered the call to sqlite3ResetInternalSchema(). Nothing here which touches the schema of the database that I can see. last 50 sqls = 'step: select distinct RESPONSES.RESPONSE_OID from RESPONSES, DATA_ELEMENTS, SEQUENCE_ELEMENTS where SEQUENCE_ELEMENTS.SEQUENCE_ELEMENT_OID=:SEQUENCE_ELEMENTS_SEQUENCE_ELEMENT_OID and DATA_ELEMENTS.DATA_ELEMENT_NAME=:DATA_ELEMENTS_DATA_ELEMENT_NAME and RESPONSES.instance_parent=SEQUENCE_ELEMENTS.SEQUENCE_ELEMENT_OID and RESPONSES.definition_parent=DATA_ELEMENTS.DATA_ELEMENT_OID order by RESPONSES.rowid step: SELECT * FROM RESPONSES a where a.soft_deleted_char = 'F' and a.RESPONSE_OID = :oid step: UPDATE RESPONSES SET RESPONSE_NAME = :RESPONSE_NAME, prelisted_value = :prelisted_value WHERE RESPONSE_OID = :RESPONSE_OID step: select distinct DATA_ELEMENTS.DATA_ELEMENT_OID from RESPONSES, DATA_ELEMENTS, SEQUENCE_ELEMENTS where SEQUENCE_ELEMENTS.SEQUENCE_ELEMENT_OID=:SEQUENCE_ELEMENTS_SEQUENCE_ELEMENT_OID and DATA_ELEMENTS.DATA_ELEMENT_NAME=:DATA_ELEMENTS_DATA_ELEMENT_NAME and RESPONSES.instance_parent=SEQUENCE_ELEMENTS.SEQUENCE_ELEMENT_OID and RESPONSES.definition_parent=DATA_ELEMENTS.DATA_ELEMENT_OID order by DATA_ELEMENTS.rowid step: select distinct DATA_ELEMENTS.DATA_ELEMENT_OID from RESPONSES, DATA_ELEMENTS, SEQUENCE_ELEMENTS where SEQUENCE_ELEMENTS.SEQUENCE_ELEMENT_OID=:SEQUENCE_ELEMENTS_SEQUENCE_ELEMENT_OID and DATA_ELEMENTS.DATA_ELEMENT_NAME=:DATA_ELEMENTS_DATA_ELEMENT_NAME and RESPONSES.instance_parent=SEQUENCE_ELEMENTS.SEQUENCE_ELEMENT_OID and RESPONSES.definition_parent=DATA_ELEMENTS.DATA_ELEMENT_OID order by DATA_ELEMENTS.rowid step: select distinct RESPONSES.RESPONSE_OID from RESPONSES, DATA_ELEMENTS, SEQUENCE_ELEMENTS where SEQUENCE_ELEMENTS.SEQUENCE_ELEMENT_OID=:SEQUENCE_ELEMENTS_SEQUENCE_ELEMENT_OID and DATA_ELEMENTS.DATA_ELEMENT_NAME=:DATA_ELEMENTS_DATA_ELEMENT_NAME and RESPONSES.instance_parent=SEQUENCE_ELEMENTS.SEQUENCE_ELEMENT_OID and RESPONSES.definition_parent=DATA_ELEMENTS.DATA_ELEMENT_OID order by RESPONSES.rowid step: select distinct RESPONSES.RESPONSE_OID from RESPONSES, DATA_ELEMENTS, SEQUENCE_ELEMENTS where SEQUENCE_ELEMENTS.SEQUENCE_ELEMENT_OID=:SEQUENCE_ELEMENTS_SEQUENCE_ELEMENT_OID and DATA_ELEMENTS.DATA_ELEMENT_NAME=:DATA_ELEMENTS_DATA_ELEMENT_NAME and RESPONSES.instance_parent=SEQUENCE_ELEMENTS.SEQUENCE_ELEMENT_OID and RESPONSES.definition_parent=DATA_ELEMENTS.DATA_ELEMENT_OID order by RESPONSES.rowid step: SELECT * FROM RESPONSES a where a.soft_deleted_char = 'F' and a.RESPONSE_OID = :oid step: UPDATE RESPONSES SET RESPONSE_NAME = :RESPONSE_NAME, prelisted_value = :prelisted_value WHERE RESPONSE_OID = :RESPONSE_OID step: select distinct DATA_ELEMENTS.DATA_ELEMENT_OID from RESPONSES, DATA_ELEMENTS, SEQUENCE_ELEMENTS where SEQUENCE_ELEMENTS.SEQUENCE_ELEMENT_OID=:SEQUENCE_ELEMENTS_SEQUENCE_ELEMENT_OID and DATA_ELEMENTS.DATA_ELEMENT_NAME=:DATA_ELEMENTS_DATA_ELEMENT_NAME and RESPONSES.instance_parent=SEQUENCE_ELEMENTS.SEQUENCE_ELEMENT_OID and RESPONSES.definition_parent=DATA_ELEMENTS.DATA_ELEMENT_OID order by DATA_ELEMENTS.rowid step: select distinct DATA_ELEMENTS.DATA_ELEMENT_OID from RESPONSES, DATA_ELEMENTS, SEQUENCE_ELEMENTS where SEQUENCE_ELEMENTS.SEQUENCE_ELEMENT_OID=:SEQUENCE_ELEMENTS_SEQUENCE_ELEMENT_OID and DATA_ELEMENTS.DATA_ELEMENT_NAME=:DATA_ELEMENTS_DATA_ELEMENT_NAME and RESPONSES.instance_parent=SEQUENCE_ELEMENTS.SEQUENCE_ELEMENT_OID and RESPONSES.definition_parent=DATA_ELEMENTS.DATA_ELEMENT_OID order by DATA_ELEMENTS.rowid step: select distinct RESPONSES.RESPONSE_OID from RESPONSES, DATA_ELEMENTS, SEQUENCE_ELEMENTS where SEQUENCE_ELEMENTS.SEQUENCE_ELEMENT_OID=:SEQUENCE_ELEMENTS_SEQUENCE_ELEMENT_OID and DATA_ELEMENTS.DATA_ELEMENT_NAME=:DATA_ELEMENTS_DATA_ELEMENT_NAME and RESPONSES.instance_parent=SEQUENCE_ELEMENTS.SEQUENCE_ELEMENT_OID and RESPONSES.definition_parent=DATA_ELEMENTS.DATA_ELEMENT_OID order by RESPONSES.rowid step: select distinct RESPONSES.RESPONSE_OID from RESPONSES, DATA_ELEMENTS, SEQUENCE_ELEMENTS where SEQUENCE_ELEMENTS.SEQUENCE_ELEMENT_OID=:SEQUENCE_ELEMENTS_SEQUENCE_ELEMENT_OID and DATA_ELEMENTS.DATA_ELEMENT_NAME=:DATA_ELEMENTS_DATA_ELEMENT_NAME and RESPONSES.instance_parent=SEQUENCE_ELEMENTS.SEQUENCE_ELEMENT_OID and RESPONSES.definition_parent=DATA_ELEMENTS.DATA_ELEMENT_OID order by RESPONSES.rowid step: SELECT * FROM RESPONSES a where a.soft_deleted_char = 'F' and a.RESPONSE_OID = :oid step: UPDATE RESPONSES SET
Re: [sqlite] ftruncate implementation
no, it is more handwaving. The dirty marking you describe will be done for a partial page at the old end of the file. Any additional pages will normally be left unallocated, and the operating system will provide zeros at read time when you try to read them. This makes them appear zero-filled, without actually zero filling anything. Having said that, I would expect certain operating systems (like DOS) to actually do the zero-filling and complete it before returning from the call. --David Garfield On Tue, Jan 24, 2012 at 14:00, Simon Slavin slav...@bigfraud.org wrote: On 24 Jan 2012, at 6:51pm, David Garfield wrote: From the ftruncate page: If the file size is increased, the extended area shall appear as if it were zero-filled. It doesn't have to write zeros, just act like it did. That's a bit of handwaving. What happens is that the file system doesn't itself write zeros to the disk surface. It puts zeros into the cache of the sector in memory and marks that sector as dirty (i.e. needing to eventually be written back to disk). So if the program doesn't write to that sector and just releases it, those zeros do eventually get written to disk. But if the program does write to that sector the disk surface only gets touched once, not twice, which is faster. So it doesn't write zeros to disk, but it does write zeros to the bit of memory that represents that bit of disk, and arrange that (unless the program overwrites them) they'll be written to disk eventually. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Incompatible versions of SQLite on same system
On Tue, Jan 24, 2012 at 11:32 AM, Joe Winograd j...@winograd.us wrote: Joe, Good to know! I had been looking just for sqlite3.dll. I don't have access to the machine right now, but I do have a clone of its system drive, and a search for sqlite turned up: System.Data.SQLite.Linq.DLL (more than one copy) System.Data.SQLite.DLL (more than one copy) System.Data.SQLite64.DLL (probably because it's 64-bit W7) At the time the clone was made, HP Connection Manager wasn't on it, but HP Power Assistant was, and the latter's directory in Program Files contains both System.Data.SQLite.DLL and System.Data.SQLite64.DLL, so there's a good chance that HP Connection Manager will have those, too (I am not willing to uninstall Intuit's TurboTax at this time, so there's no way to get HP Connection Manager installed to determine for sure what SQLite-related DLLs it installs). Regards, Joe It's a bit of a kludge, but ...you can go ahead and reinstall HP's Connection Manager. When you want to run TurboTax ...just *temporarily* rename the conflicting parent directory (C:\Program Files\HP Connection Manager to C:\Program Files\HP Connection Manager_ ) and reboot your system. It will be unable to find the directory to load the conflicting files (or run HP Connection Manager). Then, run TurboTax to your heart's content and when finished ...rename the parent directory *back* (remove the underscore at the end of the directory name) and reboot to return HP Connection Manager to a working state. Cheers -- -- -- --ô¿ô-- K e V i N Original Message Subject: Re: [sqlite] Incompatible versions of SQLite on same system From: Joe Mistachkin sql...@mistachkin.com To: 'General Discussion of SQLite Database' sqlite-users@sqlite.org Date: Thu Jan 19 2012 10:06:09 GMT-0600 (Central Standard Time) Another thing to keep in mind is that the System.Data.SQLite project compiles the native code for SQLite into files named SQLite.Interop.dll and System.Data.SQLite.dll (mixed-mode assembly), depending on the build configuration. __**_ 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] Found it
Maybe a view is getting materialized, or a some other temp table's creation under the hood is triggering this? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] ftruncate implementation
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 24/01/12 11:00, Simon Slavin wrote: That's a bit of handwaving. What happens is that the file system doesn't itself write zeros to the disk surface. Not handwaving and Unix has operated this way since the early days. They are called sparse files: http://en.wikipedia.org/wiki/Sparse_file Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.11 (GNU/Linux) iEYEARECAAYFAk8fCUoACgkQmOOfHg372QTznQCg5lezhupQx8fVtExLjZthottc KtcAnjjgzG2MfdhLf6RGf7vgRi9wjaXL =Atgu -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] ftruncate implementation
On 24 Jan 2012, at 7:09pm, David Garfield wrote: no, it is more handwaving. The dirty marking you describe will be done for a partial page at the old end of the file. Any additional pages will normally be left unallocated, and the operating system will provide zeros at read time when you try to read them. Yes, but SQLite allocates pages in order, one by one. It doesn't take advantage of the ability to allocate files sparsely. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Incompatible versions of SQLite on same system
you can go ahead and reinstall HP's Connection Manager Kevin, Thanks for the idea, but it will not install. The way this group operates with excessive trimming/snipping, that has long since been removed from this thread (this M.O. makes it very difficult for a member to join a discussion in the middle), but here's what I wrote at the get-go: I received the following dialog box with the title dbUpdate.exe: Cannot load assembly: System.Data.SQLite, Version=1.0.61.0, Culture=neutral, PublicKeyToken=db937bc2d44ff139 The application will now exit. The supposed answer from HP is that you may use use either TurboTax or Connection Manager, but not both! I was hoping the experts in this forum would have a solution to this problem, or at least a better work-around than uninstalling one and reinstalling the other each time I need one of the programs. One of the really interesting things that came out in this thread (also long since trimmed) is that there is no SQLite DLL of any kind in the TurboTax directory structure and there doesn't seem to be one in any common/shared area that TT would be using (there *is* one in \Common Files\Apple, but TT is surely not using that). Also trimmed from earlier in the thread: I downloaded the latest SQLite3.dll from the link that Richard provided and replaced the copy in c:\Program Files (x86)\Hewlett-Packard\HP Connection Manager. Running CM fails in the same way as previously reported and running DBUpdate.exe (which is in the same directory as the CM executable and the new SQLite3.dll) also fails in the same way as previously reported. Regards, Joe Original Message Subject: Re: [sqlite] Incompatible versions of SQLite on same system From: Kevin Benson kevin.m.ben...@gmail.com To: General Discussion of SQLite Database sqlite-users@sqlite.org Date: Tue Jan 24 2012 13:38:24 GMT-0600 (Central Standard Time) On Tue, Jan 24, 2012 at 11:32 AM, Joe Winogradj...@winograd.us wrote: Joe, Good to know! I had been looking just forsqlite3.dll. I don't have access to the machine right now, but I do have a clone of its system drive, and a search forsqlite turned up: System.Data.SQLite.Linq.DLL (more than one copy) System.Data.SQLite.DLL (more than one copy) System.Data.SQLite64.DLL (probably because it's 64-bit W7) At the time the clone was made, HP Connection Manager wasn't on it, but HP Power Assistant was, and the latter's directory in Program Files contains bothSystem.Data.SQLite.DLL andSystem.Data.SQLite64.DLL, so there's a good chance that HP Connection Manager will have those, too (I am not willing to uninstall Intuit's TurboTax at this time, so there's no way to get HP Connection Manager installed to determine for sure what SQLite-related DLLs it installs). Regards, Joe It's a bit of a kludge, but ...you can go ahead and reinstall HP's Connection Manager. When you want to run TurboTax ...just *temporarily* rename the conflicting parent directory (C:\Program Files\HP Connection Manager to C:\Program Files\HP Connection Manager_ ) and reboot your system. It will be unable to find the directory to load the conflicting files (or run HP Connection Manager). Then, run TurboTax to your heart's content and when finished ...rename the parent directory *back* (remove the underscore at the end of the directory name) and reboot to return HP Connection Manager to a working state. Cheers -- -- -- --ô¿ô-- K e V i N Original Message Subject: Re: [sqlite] Incompatible versions of SQLite on same system From: Joe Mistachkinsql...@mistachkin.com To: 'General Discussion of SQLite Database'sqlite-users@sqlite.org Date: Thu Jan 19 2012 10:06:09 GMT-0600 (Central Standard Time) Another thing to keep in mind is that the System.Data.SQLite project compiles the native code for SQLite into files named SQLite.Interop.dll and System.Data.SQLite.dll (mixed-mode assembly), depending on the build configuration. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Found it
On Tue, Jan 24, 2012 at 1:28 PM, John Elrick john.elr...@fenestra.comwrote: http://www.sqlite.org/fenstra/sqlite-201103081639.zip This one gives the high number of mallocs just as does 3.7.6. OK. Please try these: http://www.sqlite.org/fenstra/sqlite-201102040051.zip http://www.sqlite.org/fenstra/sqlite-201102112254.zip -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] ftruncate implementation
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 24/01/12 11:53, Simon Slavin wrote: Yes, but SQLite allocates pages in order, one by one. Not quite true. See also SQLITE_FCNTL_CHUNK_SIZE. It doesn't take advantage of the ability to allocate files sparsely. Ever tried zeroblob? Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.11 (GNU/Linux) iEYEARECAAYFAk8fGkAACgkQmOOfHg372QTGkACfbwQfSKwHq19HfDXWYNhD3LEa 818AnjD1c4av9YQyxxAoV5jYIUVVk3zz =4lCD -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Found it
Nope and nope. They are happening apparently out of the blue. On Jan 24, 2012 2:40 PM, Nico Williams n...@cryptonector.com wrote: Maybe a view is getting materialized, or a some other temp table's creation under the hood is triggering this? ___ 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] Incompatible versions of SQLite on same system
On 24 Jan 2012 at 20:02, Joe Winograd j...@winograd.us wrote: Thanks for the idea, but it will not install. The way this group operates with excessive trimming/snipping ... No it doesn't. It doesn't do *enough* trimming and snipping, and as a result our inboxes grow exponentially. If I want to read a thread I can sort by subject and then read it through. But this is made harder by the excessive repetition due to inadequate trimming (particularly of .sigs). -- Cheers -- Tim ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Incompatible versions of SQLite on same system
On 2012-01-24 16:09, Tim Streater wrote: On 24 Jan 2012 at 20:02, Joe Winogradj...@winograd.us wrote: Thanks for the idea, but it will not install. The way this group operates with excessive trimming/snipping ... No it doesn't. It doesn't do *enough* trimming and snipping, and as a result our inboxes grow exponentially. If I want to read a thread I can sort by subject and then read it through. But this is made harder by the excessive repetition due to inadequate trimming (particularly of .sigs). -- Cheers -- Tim Did you guys ever consider Google Groups or something of the like? Email lists are soo clumsy. Normand ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Incompatible versions of SQLite on same system
1. Could we please stay on topic 2. Could we please not go down this road again? If you guys really want to argue about proper posting etiquette and / or using something besides an email list, they've both been done to death. Just search the archives or start a new thread that's appropriately titled. Just leave it out of this one. Eric On 2012-01-24 16:09, Tim Streater wrote: On 24 Jan 2012 at 20:02, Joe Winogradj...@winograd.us wrote: Thanks for the idea, but it will not install. The way this group operates with excessive trimming/snipping ... No it doesn't. It doesn't do *enough* trimming and snipping, and as a result our inboxes grow exponentially. If I want to read a thread I can sort by subject and then read it through. But this is made harder by the excessive repetition due to inadequate trimming (particularly of .sigs). -- Cheers -- Tim Did you guys ever consider Google Groups or something of the like? Email lists are soo clumsy. Normand ___ 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] Found it
On Tue, Jan 24, 2012 at 3:05 PM, Richard Hipp d...@sqlite.org wrote: On Tue, Jan 24, 2012 at 1:28 PM, John Elrick john.elr...@fenestra.com wrote: http://www.sqlite.org/fenstra/sqlite-201103081639.zip This one gives the high number of mallocs just as does 3.7.6. OK. Please try these: http://www.sqlite.org/fenstra/sqlite-201102040051.zip http://www.sqlite.org/fenstra/sqlite-201102112254.zip -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users Both of these are low malloc count. I happened to think on my long drive from Frederick, are their canonical procedures used to flag the system, to tell it that the schema needs rebuilt? Maybe I should attempt a call stack trace on those (if they exist). ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Found it
On Tue, Jan 24, 2012 at 5:21 PM, John Elrick john.elr...@fenestra.comwrote: On Tue, Jan 24, 2012 at 3:05 PM, Richard Hipp d...@sqlite.org wrote: OK. Please try these: http://www.sqlite.org/fenstra/sqlite-201102040051.zip http://www.sqlite.org/fenstra/sqlite-201102112254.zip Both of these are low malloc count. Progress! Consider the timeline at http://www.sqlite.org/src/timeline?p=36d79e6f54cdc412n=37nd You have reported that the top-most check-in exhibits the problem but the bottom-most check-in does not. So the problem must be somewhere in between these two. Here are two other check-ins in the middle for you to try so that we can narrow it down further: http://www.sqlite.org/fenstra/sqlite-201102211146.zip http://www.sqlite.org/fenstra/sqlite-201103040056.zip -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Found it
On Tue, Jan 24, 2012 at 5:36 PM, Richard Hipp d...@sqlite.org wrote: On Tue, Jan 24, 2012 at 5:21 PM, John Elrick john.elr...@fenestra.com wrote: On Tue, Jan 24, 2012 at 3:05 PM, Richard Hipp d...@sqlite.org wrote: OK. Please try these: http://www.sqlite.org/fenstra/sqlite-201102040051.zip Low malloc count http://www.sqlite.org/fenstra/sqlite-201102112254.zip High malloc count The problem is between the two. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Found it
Let me rephrase that... I ran this test with a small case. See results below. On Tue, Jan 24, 2012 at 5:55 PM, John Elrick john.elr...@fenestra.comwrote: SNIP http://www.sqlite.org/fenstra/sqlite-201102040051.zip Low malloc count 8,191 calls to malloc (consistent with 3.7.5 for this dataset) http://www.sqlite.org/fenstra/sqlite-201102112254.zip High malloc count 14,726 calls to malloc 3.7.6 148,606 calls to malloc So there was a jump, but not to the full level. The problem is between the two. -- John Elrick Fenestra Technologies 540-868-1377 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Found it
On Tue, Jan 24, 2012 at 5:58 PM, John Elrick john.elr...@fenestra.comwrote: Let me rephrase that... http://www.sqlite.org/fenstra/sqlite-201102040051.zip 8,191 calls to malloc (consistent with 3.7.5 for this dataset) http://www.sqlite.org/fenstra/sqlite-201102112254.zip 14,726 calls to malloc 3.7.6 148,606 calls to malloc I still believe that the problem is along this graph: http://www.sqlite.org/src/timeline?from=36d79e6f54to=f7e2ea33d5nomerge Do you disagree? Do you think the problem occurs before or after the set of changes shown above. Only you are able to recreate the problem (so far), so you have to be judge as to when it works and when it does not. Let me know where we ought to be looking. -- John Elrick Fenestra Technologies 540-868-1377 ___ 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
Re: [sqlite] Found it
I really found it this time. While I was looking over the code changes, I noticed a section of code dealing with case OP_VerifyCookie: Inside that section is the following in 3.7.6 if( u.av.iMeta!=pOp-p2 || u.av.iGen!=pOp-p3 ){ sqlite3DbFree(db, p-zErrMsg); p-zErrMsg = sqlite3DbStrDup(db, database schema has changed); /* If the schema-cookie from the database file matches the cookie ** stored with the in-memory representation of the schema, do ** not reload the schema from the database file. ** ** If virtual-tables are in use, this is not just an optimization. ** Often, v-tables store their data in other SQLite tables, which ** are queried from within xNext() and other v-table methods using ** prepared queries. If such a query is out-of-date, we do not want to ** discard the database schema, as the user code implementing the ** v-table would have to be ready for the sqlite3_vtab structure itself ** to be invalidated whenever sqlite3_step() is called from within ** a v-table method. */ if( db-aDb[pOp-p1].pSchema-schema_cookie!=u.av.iMeta ){ sqlite3ResetInternalSchema(db, pOp-p1); } On a hunch, I replaced this line: if( u.av.iMeta!=pOp-p2 || u.av.iGen!=pOp-p3 ){ with the if statement from 3.7.5 if( u.av.iMeta!=pOp-p2 ){ When I do, the number of mallocs drops back to 14,000 - still a little higher than in 3.7.5, but nowhere near 140,000. Does that help? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Found it
On Tue, Jan 24, 2012 at 7:18 PM, John Elrick john.elr...@fenestra.comwrote: I really found it this time. While I was looking over the code changes, I noticed a section of code dealing with case OP_VerifyCookie: On a hunch, I replaced this line: if( u.av.iMeta!=pOp-p2 || u.av.iGen!=pOp-p3 ){ with the if statement from 3.7.5 if( u.av.iMeta!=pOp-p2 ){ When I do, the number of mallocs drops back to 14,000 - still a little higher than in 3.7.5, but nowhere near 140,000. So the problem was introduced by http://www.sqlite.org/src/info/36c04dd1695f08 Which is the fix for this bug: http://www.sqlite.org/src/info/f7b4edece25c99 That helps. But we still cannot recreate the problem. Are you using shared cache mode and do you have multiple database connections open on the same database file? -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Found it
On Tue, Jan 24, 2012 at 7:26 PM, Richard Hipp d...@sqlite.org wrote: On Tue, Jan 24, 2012 at 7:18 PM, John Elrick john.elr...@fenestra.com wrote: SNIP So the problem was introduced by http://www.sqlite.org/src/info/36c04dd1695f08 Which is the fix for this bug: http://www.sqlite.org/src/info/f7b4edece25c99 That helps. But we still cannot recreate the problem. Are you using shared cache mode and do you have multiple database connections open on the same database file? Nope and nope. It appears that the value is set here: u.av.pBt = db-aDb[pOp-p1].pBt; if( u.av.pBt ){ sqlite3BtreeGetMeta(u.av.pBt, BTREE_SCHEMA_VERSION, (u32 *)u.av.iMeta); u.av.iGen = db-aDb[pOp-p1].pSchema-iGeneration; }else{ u.av.iGen = u.av.iMeta = 0; } I added a log trace to the else condition and it never fired off, so something is causing db-aDb[pOp-p1].pSchema-iGeneration to change relative to pOp-p3. Give me some guidance as to where to dig and I'll be glad to do so (tomorrow, my 9 year old daughter just asked for ice cream). ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Found it
On Tue, Jan 24, 2012 at 7:37 PM, John Elrick john.elr...@fenestra.comwrote: It appears that the value is set here: u.av.pBt = db-aDb[pOp-p1].pBt; if( u.av.pBt ){ sqlite3BtreeGetMeta(u.av.pBt, BTREE_SCHEMA_VERSION, (u32 *)u.av.iMeta); u.av.iGen = db-aDb[pOp-p1].pSchema-iGeneration; }else{ u.av.iGen = u.av.iMeta = 0; } I added a log trace to the else condition and it never fired off, so something is causing db-aDb[pOp-p1].pSchema-iGeneration to change relative to pOp-p3. Give me some guidance as to where to dig and I'll be glad to do so iGeneration changes in only one place in the code: http://www.sqlite.org/src/artifact/5069f2248?ln=431 May I recommend that you put a printf() before this line http://www.sqlite.org/src/artifact/fc1b97fa6816?ln=3022 And have that printf display the following values: db (rendered using %p) pOp-p1 (using %d) db-aDb[pOp-p1].pSchema (using %p) db-aDb[pOp-p1].pSchema-schema_cookie (using %d) db-aDb[pOp-p1].pSchema-iGeneration (using %d) iMeta (using %d) pOp-p2 (using %d) pOp-p3 (using %d) p-zSql (using %s) And then also add a printf on the line above where iGeneration changes and display its new value every time it changes. -- 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] SQL query causes various hangs/crashes/exits
Hi, I hope I'm in the right place - this is my first post to sqlite-users. I'm looking for help on a query I am composing which seems to be blowing-up the query engine/API. Or maybe I'm just missing something very obvious... I have a query which works fine on a small database (15KB) which I manipulated to have just one matching result, but on my full-size database (40MB) this query causes my Windows Phone app to completely die with no exception and similarly on my desktop PC while trying to understand the issue SQLite Database Browser 2.0 b1.exe ends up Not Responding (never comes back to life). Unfortunately I can't execute this query via sqlite3.exe because it contains Unicode characters in the query string. I'm happy to provide any more background info as required but just on first sight, is there anything immediately wrong with this query? SELECT DISTINCT d.rowid AS id, d.*, i.relevance FROM dictionary d JOIN hp_index i ON i.dictionary_id = d.rowid JOIN hanzi h ON h.rowid = i.hanzi_id WHERE h.traditional = '我' OR h.simplified = '我' ORDER BY i.relevance desc I can get it to work by dropping the OR h.simplified part of the WHERE clause. However, if I use a single where clause but use the h.simplified column instead then it breaks again. Maybe it's the index? CREATE INDEX i3 ON hanzi(traditional, simplified); Any help much appreciated for my first post. Cheers, Larry P.S. here's the query plan fr the first broken (on large dataset) query showing the indexes working as intended: 0|0|2|SEARCH TABLE hanzi AS h USING COVERING INDEX i3 (traditional=?) (~10 rows) 0|0|2|SEARCH TABLE hanzi AS h USING AUTOMATIC COVERING INDEX (simplified=?) (~7 rows) 0|1|1|SEARCH TABLE hp_index AS i USING INDEX i5 (hanzi_id=?) (~10 rows) 0|2|0|SEARCH TABLE dictionary AS d USING INTEGER PRIMARY KEY (rowid=?) (~1 rows) 0|0|0|USE TEMP B-TREE FOR DISTINCT 0|0|0|USE TEMP B-TREE FOR ORDER BY ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users