date:20140217

On 17 Feb 2014, at 6:33pm, Peter Haworth  wrote:

> Is there a list of available loadable extensions for functions, virtual
> tables, etc?

I've seen some extensions lists on the web but none of them struck me as worth 
bookmarking.

The SQLite site includes a page of contributions, which contains some 
extensions:

Look especially at the last entry.  Some entries on that page are obsolete, 
attempts at making up for things which have since been built into SQLite or 
things that don't work with up-to-date compilers.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Text column: never used vs. set to empty string

On 17 Feb 2014, at 7:08pm, Tim Streater  wrote:

> Yes. I'm concluding that there's something fishy with the way my 
> data-gathering page is operating. I've recently added "use strict"; to my 
> javascript and that may be exposing something.

My web apps involve a hand-off between JavaScript (running on the user's 
browser) and PHP (running on the server, accessing the SQLite database).  Over 
the years the two have communicated various ways as standards have evolved, 
HTTPS has become available, and both languages have improved.  And each time I 
change how it works I run into some unexpected weird encoding problem.  Every 
single time.

The new debugging tools built into Chrome, FireFox and Safari are wonderful.  I 
mean completely unbelievable even a decade ago.  And these three lines for PHP 
transform it out of sight:

error_reporting(E_ALL);
ini_set('display_errors', '1');
ini_set('log_errors', '1');

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Once again about random values appearance

2014-02-17 Thread Max Vlasov

On Mon, Feb 17, 2014 at 7:00 PM, RSmith  wrote:
>
>
> On 2014/02/17 09:59, Max Vlasov wrote:
>>
>> .
>> So
>>
>>Select nanosec() - nanosec() from ...
>>
>> returns non-zero values for most of the times, so there's no guarantee the
>> user functions or any other functions will be called once for the step.//... 
>> etc.
>
>
> Did you mark your nanosec function as SQLITE_DETERMINISTIC 
> ?
> http://www.sqlite.org/c3ref/create_function.html
>
> Which, if not, it can and will very much return non-zero values.
>

Very interesting option, it did affected the results, they're now zero
for both cases. Also I see this quite a young option listed as a new
feature of 3.8.3.

"Added SQLITE_DETERMINISTIC as an optional bit in the 4th argument to
the sqlite3_create_function() and related interfaces, providing
applications with the ability to create new functions that can be
factored out of inner loops when they have constant arguments"

So the query with this option

  Select nanosec() as v from TestTable where v<>v

always returns empty dataset.

But it seems this option still has something to explain since

  Select nanosec() - nanosec() from TestTable

returns always zeros while

  Select nanosec(), nanosec() from TestTable

returns different values for fields

Either nanosec() - nanosec() is much faster than the granularity of
performance counter on average windows hardware or they are different
cases for some reasons.

Max
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Text column: never used vs. set to empty string

On 17 Feb 2014 at 18:38, Simon Slavin  wrote:

> On 17 Feb 2014, at 4:57pm, Tim Streater  wrote:
>
>> Thanks. I should perhaps have made it clearer that I'm looking at an issue a
>> user has. The application gathers some data from the user via a set of fields
>> they complete in a browser window, which data is then gathered up and sent
>> using ajax to be processed by a PHP script, which writes it to an sqlite db.
>> The user complains that some of this data doesn't make it, so I want to pin
>> down where in the chain this might be failing. Hence my Q.
>
> Ah.  In that case it's more likely to be writing zero-length strings.  In
> other words "a
> column into which no data has ever been entered" does not happen.
>
> I'm with Mister Beal and possibly yourself: your problem is outside of SQLite.

Yes. I'm concluding that there's something fishy with the way my data-gathering 
page is operating. I've recently added "use strict"; to my javascript and that 
may be exposing something.



--
Cheers  --  Tim
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Text column: never used vs. set to empty string

On 17 Feb 2014, at 4:57pm, Tim Streater  wrote:

> Thanks. I should perhaps have made it clearer that I'm looking at an issue a 
> user has. The application gathers some data from the user via a set of fields 
> they complete in a browser window, which data is then gathered up and sent 
> using ajax to be processed by a PHP script, which writes it to an sqlite db. 
> The user complains that some of this data doesn't make it, so I want to pin 
> down where in the chain this might be failing. Hence my Q.

Ah.  In that case it's more likely to be writing zero-length strings.  In other 
words "a
column into which no data has ever been entered" does not happen.

I'm with Mister Beal and possibly yourself: your problem is outside of SQLite.  
My only answer is to convert to Unicode as fast as possible then handle 
everything with Unicode-safe APIs.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Loadable extensions

2014-02-17 Thread Peter Haworth

Is there a list of available loadable extensions for functions, virtual
tables, etc?
Pete
lcSQL Software 
Home of lcStackBrowser  and
SQLiteAdmin 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Text column: never used vs. set to empty string

Forgot to add: My headache was essentially UTF-8 encoding, but the same would happen with others, though invalid chars do not really 
exist in UTF7 or ANSI, but in the higher level encodings they are plentiful.



On 2014/02/17 19:35, RSmith wrote:


Yeah, I too have had real problems with this - It isn't limited to Latin1 either - but in my case I found one invalid character 
that doesn't conform to the specified encoding would drop the entire string in any encoding.  This was hard to trace because 99 
conversions would succeed with all kinds of weird and wonderful encoded characters, and then one suddenly returns an empty string.
I eventually traced this down to the OS's C API for encoding conversions which seems to simply drop the entire value and not even 
cause an error - just returns empty string. Peculiar behaviour if you ask me (though that is probably meant to prompt an error 
from the high-level code's design, but nobody told me...).  This seems the case for both Windows and OSX, I can't speak for Linux, 
Android, etc. maybe someone else knows?


Anyway, adding a conversion check is the key, but the point is moot if you rely on PHP or such to do the conversions for you. 
Scripts don't have GUIs... they cant really do much about it. Maybe an error log somewhere contains some info?


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Question regarding guarantees of SQLITE_DETERMINISTIC

2014-02-17 Thread Stephan Beal

On Mon, Feb 17, 2014 at 6:23 PM, RSmith  wrote:

> The flag is telling SQLite that your function will behave
> determinsitcally, i.e. it won't change the output for the same inputs
> within a single query.
>

i figured so, just wanted to be sure.

> SQLite then uses this information to "maybe" cache the output and re-use
> it, but there is no guarantee the optimisation is possible within every
> query situation, so it is very much possible SQLite can call your function
> again within the same query, you have to make your function behave
> deterministically if you tell SQLite that it is so - else query results can
> be undefined.
>

But it would be really handy if sqlite3 could somehow guaranty that the
optimization would apply :). (No, i'm not asking for that as a feature - it
doesn't sound feasible to me for all query constructs.)

> The OP seems to have "tested" SQLite's determinism with adding a very
> indeterministic function, so what I was trying to point out is either his
> function isn't behaving deterministically and/or he did not specify the
> flag to let SQLite know -

It was your response to that which lead me to that flag - i wasn't aware of
it before and wanted to double-check before i go applying it to my local
now() impl (which i "now" won't do, of course). But... i can set it on 5 or
6 others which do behave deterministically, so thank you for that :).

but to your question specifically, no the flag does not force determinism
> (AFAICT),

That coincides with Richard's answer (which i consider to be definitive ;).

> it only allows the optimisation

But it "would be cool if..." ;)

-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
"Freedom is sloppy. But since tyranny's the only guaranteed byproduct of
those who insist on a perfect world, freedom will have to do." -- Bigby Wolf
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Text column: never used vs. set to empty string

On 2014/02/17 19:01, Stephan Beal wrote:

On Mon, Feb 17, 2014 at 5:57 PM, Tim Streater wrote:

complete in a browser window, which data is then gathered up and sent
using ajax to be processed by a PHP script, which writes it to an sqlite
db. The user complains that some of this data doesn't make it, so I want to
pin down where in the chain this might be failing. Hence my Q.

FWIW, i have seen a similar problem in a legacy app which uses latin1
encoding in the DB. Latin1 doesn't always survive round-trip through PHP's
JSON APIs. My case was similar to yours, and we eventually determined that
the fields which got "lost" (set to null or empty values) were those which
came out of the latin1-encoded MySQL db containing invalid UTF8 characters
- the whole values were getting dropped upon transforming to JSON.

TL;DR: double-check all encodings.

Yeah, I too have had real problems with this - It isn't limited to Latin1 either - but in my case I found one invalid character that
doesn't conform to the specified encoding would drop the entire string in any encoding. This was hard to trace because 99
conversions would succeed with all kinds of weird and wonderful encoded characters, and then one suddenly returns an empty string.
I eventually traced this down to the OS's C API for encoding conversions which seems to simply drop the entire value and not even
cause an error - just returns empty string. Peculiar behaviour if you ask me (though that is probably meant to prompt an error from
the high-level code's design, but nobody told me...). This seems the case for both Windows and OSX, I can't speak for Linux,
Android, etc. maybe someone else knows?

Anyway, adding a conversion check is the key, but the point is moot if you rely on PHP or such to do the conversions for you.
Scripts don't have GUIs... they cant really do much about it. Maybe an error log somewhere contains some info?

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Text column: never used vs. set to empty string

On 17 Feb 2014 at 17:01, Stephan Beal  wrote:

> FWIW, i have seen a similar problem in a legacy app which uses latin1
> encoding in the DB. Latin1 doesn't always survive round-trip through PHP's
> JSON APIs. My case was similar to yours, and we eventually determined that
> the fields which got "lost" (set to null or empty values) were those which
> came out of the latin1-encoded MySQL db containing invalid UTF8 characters
> - the whole values were getting dropped upon transforming to JSON.

I'm not using JSON, and I've been careful to have everything in UTF8 
throughout. I'm asking the user to send me the db, so I can look at it. But you 
make a good point. I said I've been careful, but the possibility I overlooked 
something related to that is not excluded.

--
Cheers  --  Tim
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Question regarding guarantees of SQLITE_DETERMINISTIC



On 2014/02/17 18:47, Stephan Beal wrote:

Hi, all,

Regarding SQLITE_DETERMINISTIC:

http://www.sqlite.org/c3ref/create_function.html

does specifying that flag guaranty that sqlite3 will only call my
"deterministic" function one time during any given SQL statement, or must
my function actually guaranty that deterministic behaviour itself?


The flag is telling SQLite that your function will behave determinsitcally, i.e. it won't change the output for the same inputs 
within a single query. SQLite then uses this information to "maybe" cache the output and re-use it, but there is no guarantee the 
optimisation is possible within every query situation, so it is very much possible SQLite can call your function again within the 
same query, you have to make your function behave deterministically if you tell SQLite that it is so - else query results can be 
undefined.


The OP seems to have "tested" SQLite's determinism with adding a very indeterministic function, so what I was trying to point out is 
either his function isn't behaving deterministically and/or he did not specify the flag to let SQLite know - but to your question 
specifically, no the flag does not force determinism (AFAICT), it only allows the optimisation, or more specifically, NOT specifying 
the flag forces SQLIte to call the function every time and not expect determinsitic results (even if your return values are constant).



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Text column: never used vs. set to empty string

2014-02-17 Thread Stephan Beal

On Mon, Feb 17, 2014 at 5:57 PM, Tim Streater  wrote:

> complete in a browser window, which data is then gathered up and sent
> using ajax to be processed by a PHP script, which writes it to an sqlite
> db. The user complains that some of this data doesn't make it, so I want to
> pin down where in the chain this might be failing. Hence my Q.
>

FWIW, i have seen a similar problem in a legacy app which uses latin1
encoding in the DB. Latin1 doesn't always survive round-trip through PHP's
JSON APIs. My case was similar to yours, and we eventually determined that
the fields which got "lost" (set to null or empty values) were those which
came out of the latin1-encoded MySQL db containing invalid UTF8 characters
- the whole values were getting dropped upon transforming to JSON.

TL;DR: double-check all encodings.

-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
"Freedom is sloppy. But since tyranny's the only guaranteed byproduct of
those who insist on a perfect world, freedom will have to do." -- Bigby Wolf
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Text column: never used vs. set to empty string

On 17 Feb 2014 at 14:10, Simon Slavin  wrote: 

> On 17 Feb 2014, at 11:37am, Tim Streater  wrote:
>
>> If I have a text column defined as it might be as MYCOL TEXT (that is with no
>> default value), is there a way to distinguish in some row or other between a
>> column into which no data has ever been entered, and a column that might have
>> been set to a string, but later set to the empty string? (or even only ever
>> set to the empty string, perhaps).
>
> In SQL, "no data" means "NUL" whereas an empty string is a string with zero
> characters in.
>
> So just do
>
> SELECT typeof(t) FROM myTable
>
> and see whether you get 'text' or not.

Thanks. I should perhaps have made it clearer that I'm looking at an issue a 
user has. The application gathers some data from the user via a set of fields 
they complete in a browser window, which data is then gathered up and sent 
using ajax to be processed by a PHP script, which writes it to an sqlite db. 
The user complains that some of this data doesn't make it, so I want to pin 
down where in the chain this might be failing. Hence my Q.

--
Cheers  --  Tim
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Question regarding guarantees of SQLITE_DETERMINISTIC

2014-02-17 Thread Richard Hipp

On Mon, Feb 17, 2014 at 11:47 AM, Stephan Beal wrote:

> Hi, all,
>
> Regarding SQLITE_DETERMINISTIC:
>
> http://www.sqlite.org/c3ref/create_function.html
>
> does specifying that flag guaranty that sqlite3 will only call my
> "deterministic" function one time during any given SQL statement, or must
> my function actually guaranty that deterministic behaviour itself?
>
> The canonical example is a now() function which returns time(NULL) (there
> was a long thread on this topic a few months back).
>
> My concrete questions:
>
> - Does such function need to make the guaranty itself or is marking it as
> SQLITE_DETERMINISTIC enough to keep sqlite from calling it twice in one
> statement?
>

The is a constraint on the function implementation, that allows SQLite to
perform certain optimizations in the generated VDBE code that would
otherwise not be valid.  So the function must guarantee that it will always
return the same values given the same inputs.


>
> - Can "within a single SQL statement" be interpreted as "within the
> lifetime of a given preparation of a given sqlite3_stmt handle" without
> distorting the truth too much, or is there a more complex definition
> involving subselects and such?
>
>
In call to the function within any subquery or trigger must return the same
value if it has the same inputs.

But the return value can change after each sqlite3_reset().

-- 
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] Question regarding guarantees of SQLITE_DETERMINISTIC

2014-02-17 Thread Stephan Beal

Hi, all,

Regarding SQLITE_DETERMINISTIC:

http://www.sqlite.org/c3ref/create_function.html

does specifying that flag guaranty that sqlite3 will only call my
"deterministic" function one time during any given SQL statement, or must
my function actually guaranty that deterministic behaviour itself?

The canonical example is a now() function which returns time(NULL) (there
was a long thread on this topic a few months back).

My concrete questions:

- Does such function need to make the guaranty itself or is marking it as
SQLITE_DETERMINISTIC enough to keep sqlite from calling it twice in one
statement?

- Can "within a single SQL statement" be interpreted as "within the
lifetime of a given preparation of a given sqlite3_stmt handle" without
distorting the truth too much, or is there a more complex definition
involving subselects and such?


-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
"Freedom is sloppy. But since tyranny's the only guaranteed byproduct of
those who insist on a perfect world, freedom will have to do." -- Bigby Wolf
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Once again about random values appearance

On 2014/02/17 09:59, Max Vlasov wrote:

Ok, I hope I found the topic, the title was
   "racing with date('now') (was: Select with dates):
one of the links to the archive
   https://www.mail-archive.com/sqlite-users@sqlite.org/msg79456.html

CMIIW, but as I see it, the final modification was commented by Richard

  > As a compromise, the current SQLite trunk causes 'now' to be
exactly  the
  > same for all date and time functions within a single sqlite3_step()
call.

But this is just for now and date-related functions. I wanted to be sure so
created a user function NanoSec() that returns  nanoseconds as it is
calculated with QueryPerformanceCounter and QueryPerformanceFrequency on
Windows and clock_gettime(CLOCK_REALTIME... on Linux. Seems like it's not
always real nanoseconds but value that is changed very frequently to be
different for close VDBE instructions of sqlite engine.

So

   Select nanosec() - nanosec() from ...

returns non-zero values for most of the times, so there's no guarantee the
user functions or any other functions will be called once for the step.//... 
etc.

Did you mark your nanosec function as SQLITE_DETERMINISTIC 
?
http://www.sqlite.org/c3ref/create_function.html

Which, if not, it can and will very much return non-zero values.

And if you did, either your function or your version of SQLite is broken.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Windows service with SQLITE

2014-02-17 Thread Teg

Hello tejas,

You're using windows so, you should be using "Procmon" to watch file
IO. You need to add and look at logging. This is sort of
trouble-shooting 101. If you can't get the debugger on it, you'll have
to debug with logging.

I'd  generate  a  log  file  and log everything to do with opening and
accessing the DB. 

C

Sunday, February 16, 2014, 11:45:27 PM, you wrote:

tp> Hi Teg,

tp> I tried putting delay of 3 seconds and 30 seconds but nothing happens.
tp> Still not able to access database and store anything.

tp> When i create a file to store data it stores. Strange..

tp> Need more views please.

tp> Thanks,

tp> Tejas



tp> On Sun, Feb 16, 2014 at 12:35 AM, Teg  wrote:

>> Hello tejas,
>>
>> I'm thinking it sounds like the processing collides and you don't have
>> retry's  built into the process.  Basically a race condition. The fact
>> you  can  run  one  process  and it works, and then later run a second
>> process  and  it works suggests the problem is in the startup when you
>> have  two  starting at the same time.
>>
>> As an experiment, I might put a long delay in the thread of the second
>> process so, the first process gets good and started before the second
>> one tries.
>>
>> C
>>
>> Saturday, February 15, 2014, 7:29:14 AM, you wrote:
>>
>> tp> Hi All,
>>
>> tp> We have a small issue with sqlite database.
>>
>> tp> Program structure:
>> tp> one windows service calls two windows processes process-1 and
>> process-2.
>> tp> both processes use common database db1.s3db file to store data.
>>
>> tp> When windows services starts and calls two processes p-1 and p-2 none
>> of
>> tp> the process is able to store data in the database.
>> tp> But if i call only one p-1 with service then everything works well
>> tp> meanwhile if i run p-2 manually with double click then also two
>> processes
>> tp> are able to send data in to database.
>>
>> tp> Please help i am clueless.
>>
>> tp> Thanks,
>>
>> tp> Tejas
>> tp> ___
>> tp> sqlite-users mailing list
>> tp> sqlite-users@sqlite.org
>> tp> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
>>
>>
>> --
>> Best regards,
>>  Tegmailto:t...@djii.com
>>
>>



-- 
Best regards,
 Tegmailto:t...@djii.com

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Text column: never used vs. set to empty string

2014-02-17 Thread Clemens Ladisch

Tim Streater wrote:
> If I have a text column defined as it might be as MYCOL TEXT (that is
> with no default value)

All columns have a default value.  With no _explicitly_ specified
default value, the column's default value is NULL.

> is there a way to distinguish ... between ... no data ... and ... the
> empty string?

When you have never entered data, the value is NULL, which is distinct
from '', the empty string.

As long as your program takes care to never confuse these two values, it
is possible to assign the meaning "never entered data" to the NULL value
and have it work as you want.

(Please note that in SQL, the NULL value does _not_ have any predefined
meaning such as "unknown" or "missing" or "invalid".  SQL defines only
how the NULL value behaves; it is your job as the programmer or database
designer to determine if you can assign a meaning to the NULL value that
matches this behaviour with the intended usage.)

Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Text column: never used vs. set to empty string

On 17 Feb 2014, at 11:37am, Tim Streater  wrote:

> If I have a text column defined as it might be as MYCOL TEXT (that is with no 
> default value), is there a way to distinguish in some row or other between a 
> column into which no data has ever been entered, and a column that might have 
> been set to a string, but later set to the empty string? (or even only ever 
> set to the empty string, perhaps).

In SQL, "no data" means "NUL" whereas an empty string is a string with zero 
characters in.

So just do

SELECT typeof(t) FROM myTable

and see whether you get 'text' or not.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Once again about random values appearance

2014-02-17 Thread Max Vlasov

On Mon, Feb 17, 2014 at 2:27 PM, Simon Slavin  wrote:

>
> On 17 Feb 2014, at 7:59am, Max Vlasov  wrote:
>
> > So the nanosec example modified
> >
> > Select v-v from
> > (
> >   Select nanosec() as v from TestTable
> > )
> >
> > ...shows non-zero values for the current (3.8.3) and for older (3.6.10)
> > version.
>
> Erm ... that worries me a little.
>
> I don't know how SQLite handles sub-selects internally.  But the
> conventional way of doing them is a JOIN to a VIEW.  Which means that v-v
> should always equal zero.
>


Explain lit a litle light
...
"4""Function""0""NanoSec(0)"
"5""Function""0""NanoSec(0)"
"6""Subtract""2"
"7""ResultRow""1"
"8""Next""4"
.
So no intermediate storage probably for performance reasons. Also the
listing looks very self-explainable while possible stateful one will add
more complexity to the VDBE code.


>
> By the way, when figuring out how to optimize this still it's worth noting
> that a parameterless function is rare in SQL.  It's rarely worth optimizing
> unnamed expressions because you rarely get the same unnamed expression
> (including parameters) used twice.
>
>
Simon, I see your point, next time my test function will depend on at least
a dozen of very important parameters and will have a very, very, very long
name :)

Max
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Windows service with SQLITE

2014-02-17 Thread Richard Hipp

On Sat, Feb 15, 2014 at 7:29 AM, tejas parekh  wrote:

> Hi All,
>
> We have a small issue with sqlite database.
>
> Program structure:
> one windows service calls two windows processes process-1 and process-2.
> both processes use common database db1.s3db file to store data.
>
> When windows services starts and calls two processes p-1 and p-2 none of
> the process is able to store data in the database.
> But if i call only one p-1 with service then everything works well
> meanwhile if i run p-2 manually with double click then also two processes
> are able to send data in to database.
>

Have you set up an Error and Warning log (http://www.sqlite.org/errlog.html)
to see if it gives you any additional information?

-- 
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] Windows service with SQLITE

2014-02-17 Thread tejas parekh

Hi Teg,

I tried putting delay of 3 seconds and 30 seconds but nothing happens.
Still not able to access database and store anything.

When i create a file to store data it stores. Strange..

Need more views please.

Thanks,

Tejas



On Sun, Feb 16, 2014 at 12:35 AM, Teg  wrote:

> Hello tejas,
>
> I'm thinking it sounds like the processing collides and you don't have
> retry's  built into the process.  Basically a race condition. The fact
> you  can  run  one  process  and it works, and then later run a second
> process  and  it works suggests the problem is in the startup when you
> have  two  starting at the same time.
>
> As an experiment, I might put a long delay in the thread of the second
> process so, the first process gets good and started before the second
> one tries.
>
> C
>
> Saturday, February 15, 2014, 7:29:14 AM, you wrote:
>
> tp> Hi All,
>
> tp> We have a small issue with sqlite database.
>
> tp> Program structure:
> tp> one windows service calls two windows processes process-1 and
> process-2.
> tp> both processes use common database db1.s3db file to store data.
>
> tp> When windows services starts and calls two processes p-1 and p-2 none
> of
> tp> the process is able to store data in the database.
> tp> But if i call only one p-1 with service then everything works well
> tp> meanwhile if i run p-2 manually with double click then also two
> processes
> tp> are able to send data in to database.
>
> tp> Please help i am clueless.
>
> tp> Thanks,
>
> tp> Tejas
> tp> ___
> tp> sqlite-users mailing list
> tp> sqlite-users@sqlite.org
> tp> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>
>
> --
> Best regards,
>  Tegmailto:t...@djii.com
>
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Text column: never used vs. set to empty string

2014-02-17 Thread Donald Griggs

On Mon, Feb 17, 2014 at 6:37 AM, Tim Streater  wrote:

> If I have a text column defined as it might be as MYCOL TEXT (that is with
> no default value), is there a way to distinguish in some row or other
> between a column into which no data has ever been entered, and a column
> that might have been set to a string, but later set to the empty string?
> (or even only ever set to the empty string, perhaps).
>
> 

Hi, Tim,

I would think of two ways:

1) You might default the field to a value you know from your data to be
impossible.  E.g.
  MYCOL2 TEXT DEFAULT '#UNUSED#'
Your initial building of the table would then OMIT the field name
MYCOL2 in order to invoke the default, e.g.
INSERT INTO MyTable (MyCol1, Mycol3) VALUES ('a', 'b');

2) You might set and/or default the field to NULL.   (Importantly, but as
you may already know, NULL is not like an ordinary value.  It means
something like 'Value is unknown.'   As a result, testing for a value equal
to NULL will ALWAYS return FALSE, for example.   If this would be your
first exposure to NULL you'd want to read up on it first.)

Now if instead what you want is to:
   Initialize a value to the empty string
   set it to something else
   set it back to the empty string
   Then have sqlite "invoke a past memory of that value" then, no, sql
won't do that directly.

If that's what you need then perhaps you could
   define a new boolean column to keep track of whether MyCol had ever been
used.
   Update that new column directly or via a trigger.

Others on this list may have better advice.

Donald
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Text column: never used vs. set to empty string

If I have a text column defined as it might be as MYCOL TEXT (that is with no 
default value), is there a way to distinguish in some row or other between a 
column into which no data has ever been entered, and a column that might have 
been set to a string, but later set to the empty string? (or even only ever set 
to the empty string, perhaps).

Thanks,

--
Cheers  --  Tim
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Explain Query Plan with virtual tables involved

2014-02-17 Thread Dominique Devienne

Would it be possible to have plans for virtual tables more in line
with plans for regular tables?

With virtual tables, the plan is always a SCAN TABLE, while for
regular tables it's either a SCAN TABLE for full scans, and a SEARCH
TABLE for an indexed plan.

Because I don't yet use idxStr but only idxNum in sqlite3_index_info,
my plans look like:

SCAN TABLE folder_member VIRTUAL TABLE INDEX 16:
SCAN TABLE folder_member VIRTUAL TABLE INDEX -1:

(16 is the by-folder non unique index, -1 is the full-scan index).

Even if I used idxStr, it will still be a SCAN TABLE, there's no way
to tell SQLite that a specific index is the "full scan" index of the
vtable, to have the plan use SCAN TABLE only for this one, and SEARCH
TABLE for the other cursors/indexes, and this despite the fact that I
use an astronomical estimatedCost (std::numeric_limits::max()).

Could we add either a convention on idxNum to indicate this is a full
scan, or a new field in sqlite3_index_info to explicitly indicate it?

Also, is SQLite itself building idxStr such that it looks like "nkey
(guid=? AND folder=?)", i.e. I need to build such a string myself, to
have my vtable-using plans resemble the "native" SQLite table plans?

Finally, all my indexes are basically COVERING indexes, i.e. there's
no separate lookup of the row, given the rowid, so how could I
similarly communicate this fact to SQLite, such that it prints it like
for its native plans?

My hope is that in the future, all plans look the same, except for an
additional VIRTUAL keyword between USING and INDEX, and possibly the
idxNum perhaps.

Any chance this might happen?

If not, and it's not a question of dev time, why would the above goal
not be considered?

Thanks, --DD

C:\Users\DDevienne>sqlite3
SQLite version 3.8.3.1 2014-02-11 14:52:19
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> create table folder_member (guid blob, folder blob, folder_type text);
sqlite> create unique index nkey on folder_member(guid, folder);
sqlite> create index by_guid on folder_member(guid);
sqlite> create index by_folder_type on folder_member(folder_type);
sqlite> create index by_folder on folder_member(folder);
sqlite> explain query plan select * from folder_member where guid='ab'
and folder='bc';
0|0|0|SEARCH TABLE folder_member USING INDEX nkey (guid=? AND folder=?)
sqlite> explain query plan select * from folder_member where folder_type='foo';
0|0|0|SEARCH TABLE folder_member USING INDEX by_folder_type (folder_type=?)
sqlite> explain query plan select * from folder_member;
0|0|0|SCAN TABLE folder_member
sqlite>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Once again about random values appearance