Re: [sqlite] Trigger SQL and database schema
On Oct 16, 2013, at 7:20 AM, Darren Duncanwrote: > 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#i1576452 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Trigger SQL and database schema
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 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] System.Data.SQLite Deployment Problem
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 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] System.Data.SQLite Deployment Problem
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: 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 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Pattern for using sqlite3_busy_timeout()
Hi, As I've learnt more about how to use SQLite (mainly from looking at this mailing list), I've realised I've coded somethings in a less than an optimal manner . One of the things I've realised is that SQLite has a timing system built into accessing the database, so that if things are busy it will back off for a while and then retry for a certain amount of milliseconds (microseconds?) and then fail with SQLITE_IOERR_BLOCKED or SQLITE_BUSY. Of course I realised this after I'd written my own system to do exactly the same thing. I'm not a big fan of reinventing the wheel and, to be honest, suspect (know?) that the writers of SQLite understand their system far, far better than I can, so want to do-the-right-thing and use sqlite3_busy_timeout as its supposed to be used rather than me mimicking it badly. I've read through the on-line documentation and can see more or less how it works, but I want to check the pattern usage of this to see if it follows best practise, or failing that, somebody to comment on if this looks appropriate. Just to be clear I'm not asking anybody to write my code for me, and I have looked through the manual and as many examples as I can find of this function to see how its used. Here's how I used to do things using the code I used to close a database. This is a simple code example, the code I have for executing SQL is far longer but I don't feel it offers anything more to the discussion. int DB_CloseDatabase(DB_p db) { int rc; int do_loop = 1; int timer_interval = SQL_TIMER_RETRY_INTERVAL; int no_attempts = SQL_MAX_NO_ATTEMPTS; while (do_loop && no_attempts > 0) { rc = sqlite3_close(db); switch (rc) { case SQLITE_OK: do_loop = 0; break; case SQLITE_BUSY: if (debug) printf("1. Sleeping for %d\n" , timer_interval); USleep(timer_interval); timer_interval += timer_interval; no_attempts--; break; default: { char error_string[EXCEPTION_MAX_STRING]; Exception_t exception; SetException(exception , rc , DB_ConstructErrorString(error_string , EXCEPTION_MAX_STRING , "Close Database%s:'" , (char*) sqlite3_errmsg(db) , "Close Database1)); Throw(exception); break; } } } if (no_attempts <= 0) { char error_string[EXCEPTION_MAX_STRING]; Exception_t exception; SetException(exception , rc , DB_ConstructErrorString(error_string , EXCEPTION_MAX_STRING , "Close Database%s:'" , (char*) sqlite3_errmsg(db) , "Close Database2")); Throw(exception); } return 0; } My intention with this is to try and close the database, if it fails the first time due to SQLITE_BUSY being returned, then I would sleep for a certain amount of time determined by timer_interval (in uSecs), I would then double the time_interval so that the next time it would back off for even longer, try again for SQL_MAX_NO_ATTEMPTS and if that all failed, I would eventually drop into an Exception handling routine. This seems to work OK and I have managed to simulate a locked database, I can see the time_interval being incremented. The logic in this is based on Ethernet packet handling (or how I remember it used to) when packets collided (without the randomisation of the back off). Here's my rewrite of the simple function above: int DB_CloseDatabase(DB_p db) { int rc; int do_loop = 1; int no_attempts = SQL_MAX_NO_ATTEMPTS; sqlite3_busy_timeout(db , SQL_TIMER_RETRY_INTERVAL); while (do_loop && no_attempts > 0) { rc = sqlite3_close(db); switch (rc) { case SQLITE_OK: do_loop = 0; break; case SQLITE_IOERR_BLOCKED: case SQLITE_BUSY: no_attempts--; break; default: { char error_string[EXCEPTION_MAX_STRING]; Exception_t exception; // Reset the database timeout to 0 sqlite3_busy_timeout(db , 0); SetException(exception , rc , DB_ConstructErrorString(error_string , EXCEPTION_MAX_STRING , "Close Database%s:'" , (char*) sqlite3_errmsg(db) , "Close Database")); Throw(exception); break; } } } // Reset the database timeout to 0 sqlite3_busy_timeout(db , 0); if (no_attempts <= 0) { char error_string[EXCEPTION_MAX_STRING]; Exception_t exception; SetException(exception , rc , DB_ConstructErrorString(error_string , EXCEPTION_MAX_STRING , "Close Database%s:'" , (char*)
Re: [sqlite] Segmentation Fault With Trigger
techi eth wrote: You should compile your code & sqlite3.c with -g -O0 when sending a stack trace. Most likely, the bug is in your program. Since you're on Linux, try running with valgrind memcheck tool to find bugs (access to free memory, uninitialized memory, etc.) If you compile with clang-3.3 or newer, or gcc-4.8.* or newer, you can also compile & link with -fsanitize=address to find bugs (much quicker than using valgrind if your program is slow). But the best is to try both (valgrind & gcc/clang address sanitizer, not at the same time) since they can find different bugs: * only valgrind will find uninitialized memory access * only the address sanitizer will find global and stack overflows * both will find other kind of errors (double free, use of freed memory...) Dominique > Please provide me hint to solve the issue. > > > Thanks.. > > > On Fri, Oct 11, 2013 at 7:58 PM, techi ethwrote: > >> It is giving same trace what i have sent last time. >> >> It is possible by some one to send test code snapshot of scenario of >> selecting user defined function while trigger execution. >> >> Thanks a lot... >> >> >> On Fri, Oct 11, 2013 at 7:41 PM, Richard Hipp wrote: >> >>> On Fri, Oct 11, 2013 at 10:06 AM, techi eth wrote: >>> >>> > Please provide some hint. >>> > >>> >>> Recompile with -O0 and -g and then post the stack trace after the >>> segfault. >>> >>> -- >>> D. Richard Hipp >>> d...@sqlite.org >>> ___ >>> sqlite-users mailing list >>> sqlite-users@sqlite.org >>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >>> >> >> > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] FTS4 + spellfix1 with multiple languages
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? Kind regards, Raf Geens ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Segmentation Fault With Trigger
I'm not an sqlite3 dev, but I don't think you'll get much help until you provide enough information for somebody to see what is going wrong. You were already asked to provide a backtrace from a debug-compiled sqlite3 library (the backtrace you sent is all but useless). A self-contained .c file that demonstrates the problem would be even better. For example, without knowing what your user-defined function does, we have to assume it is full of memory corruption bugs that cause the problems you experience. Ryan On 15/10/2013 12:12 AM, techi eth wrote: Please provide me hint to solve the issue. Thanks.. On Fri, Oct 11, 2013 at 7:58 PM, techi ethwrote: It is giving same trace what i have sent last time. It is possible by some one to send test code snapshot of scenario of selecting user defined function while trigger execution. Thanks a lot... On Fri, Oct 11, 2013 at 7:41 PM, Richard Hipp wrote: On Fri, Oct 11, 2013 at 10:06 AM, techi eth wrote: Please provide some hint. Recompile with -O0 and -g and then post the stack trace after the segfault. -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Getting context information (ie db name) from within sqlite error callback
Hi, I have sqlite running with multiple connections to databases (and perhaps even multithreaded). I am using a simple error logging callback function to display additional information while I am hunting down a problem with some databases. What I really want to be able to do is have the error callback function tell me the particular database that was being accessed when the error occurred, i.e. more than just a char * error string with details of the error. At the moment I can't see how to get any more information than what is in the char* and the error code that get passed to the callback. I am setting up the callback according to http://www.sqlite.org/errlog.html, and it works absolutely fine, I just need more specific info! Does anyone have any thoughts on this? Thanks! Corey ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Trigger SQL and database schema
Sqlite Dog wrote: > seems like SQLite is not checking trigger SQL for invalid column names > until execution? No. > Is there a way to force this check? Not without compiling the respective INSERT/UPDATE/DELETE statement. > The problem: trying to find out which indices, triggers and views depend on > particular column. We can recreate table without this column and recompile > indices, triggers and views. SQLite does not have a "compiled" state that is exposed to the application. What you could do is to try to run EXPLAIN QUERY PLAN on some triggering statement. Regards, Clemens ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Trigger SQL and database schema
Hi, seems like SQLite is not checking trigger SQL for invalid column names until execution? Is there a way to force this check? The problem: trying to find out which indices, triggers and views depend on particular column. We can recreate table without this column and recompile indices, triggers and views. The ones that don't recompile, perhaps, depend on this column. This works for indices and views but not for triggers that ignores schema until executed. -- Regards, SqliteDog support team ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users