Re: [sqlite] Trigger SQL and database schema

[Petite Abeille]

On Oct 16, 2013, at 7:20 AM, Darren Duncan  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#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

[Darren Duncan]

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

[Joe Mistachkin]

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

[Paul Bainter]

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()

[Rob Willett]

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

[Dominique Pellé]

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 eth  wrote:
>
>> 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

[Raf Geens]

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

[Ryan Johnson]

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 eth  wrote:


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

[Corey Putkunz]

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

[Clemens Ladisch]

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

[Sqlite Dog]

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