Re: [sqlite] Support EXTRACT SQL standard function

2012-02-12 Thread Yuriy Kaminskiy 
Roger Andersson wrote:
> On 02/12/12 20:34, Yuriy Kaminskiy wrote:
>> I wonder, how it will be handled if you issue such request at
>> month/year/...
>> change (23:59.59.999 GMT ->  00:00:00.000 GMT)?
>> Is timestamp for current_date/current_time generated once and cached
>> at start of
>> SELECT evaluation?
>>
>> It is certainly *not* cached for different rows:
>> SELECT *, current_date, current_time, current_time FROM t
>> while($row = $sth ->  fetch) { print ++$i," row: @$row"; sleep 5; }
>> 1 row: 0 2012-02-12 19:20:40 19:20:40
>> 2 row: 1 2012-02-12 19:20:40 19:20:40
>> 3 row: 2 2012-02-12 19:20:45 19:20:45
>> 4 row: 3 2012-02-12 19:20:50 19:20:50
>> 5 row: 4 2012-02-12 19:20:55 19:20:55
>> (two first are same due to sqlite [or perl DBI binding?] seems
>> executes one row
>> ahead).
>>
>> But do they use same cached value *within one row*?
>>
>> If not, results may be randomly inconsistent and broken (race condition).
> No idea!

Okey, I looked at sources, and have not found any caching.

If you want trivially triggered case, repetitive execution

SELECT * FROM t WHERE strftime('%f')<>strftime('%f')

needed only about 17 to 100 iteration to hit error and return
non-deterministical row for me.

But same applies to *all other* use of date/time-functions, you'll just need
more (un)luck, and in one happy day

SELECT substr(current_date,...),
   substr(current_date,...),
   substr(current-date,...)

will result in 31,02,2012 instead of 31,01,2012 (or 01,02,2012).
Don't you think 31'th February is lucky day?

So more than one invocation of date/time-related functions per statement is
certainly buggy :-|

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

Re: [sqlite] Support EXTRACT SQL standard function

2012-02-12 Thread Roger Andersson 

On 02/12/12 20:34, Yuriy Kaminskiy wrote:

I wonder, how it will be handled if you issue such request at month/year/...
change (23:59.59.999 GMT ->  00:00:00.000 GMT)?
Is timestamp for current_date/current_time generated once and cached at start of
SELECT evaluation?

It is certainly *not* cached for different rows:
SELECT *, current_date, current_time, current_time FROM t
while($row = $sth ->  fetch) { print ++$i," row: @$row"; sleep 5; }
1 row: 0 2012-02-12 19:20:40 19:20:40
2 row: 1 2012-02-12 19:20:40 19:20:40
3 row: 2 2012-02-12 19:20:45 19:20:45
4 row: 3 2012-02-12 19:20:50 19:20:50
5 row: 4 2012-02-12 19:20:55 19:20:55
(two first are same due to sqlite [or perl DBI binding?] seems executes one row
ahead).

But do they use same cached value *within one row*?

If not, results may be randomly inconsistent and broken (race condition).

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

Re: [sqlite] Commit method not available

2012-02-12 Thread Kyle Cashion 

Thanks!

- Original Message - 
From: "Joe Mistachkin" 

To: "'General Discussion of SQLite Database'" 
Sent: Sunday, February 12, 2012 1:40 PM
Subject: Re: [sqlite] Commit method not available




Kyle Cashion wrote:


What am I missing?



You'll want to capture the return value of the
SQLiteConnection.BeginTransaction
method (which is the actual transaction) and then use the Commit and/or
Rollback
methods of that [SQLiteTransaction] object.

--
Joe Mistachkin

___
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] freelist_count - can it shrink back?

2012-02-12 Thread Simon Slavin 

On 11 Feb 2012, at 10:06pm, Orit Alul wrote:

> When deleting from sqlite, the freed space is added to the free_list count.
> My question is, when I insert new data, is the needed space allocated out of 
> the free_list or do the db file grows larger and the free space stays till 
> vacuum operation is performed?
> Basically, my question is: on intensive inserts and deletes will the freed 
> space be re-used ?

SQLite uses available free space first.  Only if there is no free space will it 
make the database file bigger.  (This isn't quite accurate but it's mostly 
correct.)

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

Re: [sqlite] Commit method not available

2012-02-12 Thread Joe Mistachkin 

Kyle Cashion wrote:
>
> What am I missing?
>

You'll want to capture the return value of the
SQLiteConnection.BeginTransaction
method (which is the actual transaction) and then use the Commit and/or
Rollback
methods of that [SQLiteTransaction] object.

--
Joe Mistachkin

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

Re: [sqlite] Support EXTRACT SQL standard function

2012-02-12 Thread Yuriy Kaminskiy 
Roger Andersson wrote:
> On 02/11/12 15:22, Kit wrote:
>> 2012/2/10 Willian Gustavo
>> Veiga:
>>> SQLite is a great database to unit test (TDD) applications. You can
>>> run it
>>> in memory with your tests ...
>>>
>>> I've found a problem when I was unit testing my application. MySQL
>>> (production database) supports EXTRACT SQL standard function. SQLite
>>> don't
>>> support it. It would be great to have support in this standard.
>>> Unfortunately, strftime isn't a solution. It's not a standard.
>> Function strftime is your solution. Write two models. One for MySQL,
>> one for SQLite. These databases are quite different and require
>> different SQL queries.
> Maybe views could be used to handle differences, at least some of them ;-)
> sqlite> create view dateCurrent as
>...> select
>...> substr(date(),0,5) as year,
>...> substr(date(),6,2) as month,
>...> substr(date(),9,2) as day;
> sqlite> .header on
> sqlite> select * from dateCurrent;
> year|month|day
> 2012|02|12
> 
> mysql> create view dateCurrent as
> -> select
> -> substr(current_date,1,4) as year,
> -> substr(current_date,6,2) as month,
> -> substr(current_date,9,2) as day;
> mysql> select * from dateCurrent;
> +--+---+-+
> | year | month | day |
> +--+---+-+
> | 2012 | 02| 12  |
> +--+---+-+

I wonder, how it will be handled if you issue such request at month/year/...
change (23:59.59.999 GMT -> 00:00:00.000 GMT)?
Is timestamp for current_date/current_time generated once and cached at start of
SELECT evaluation?

It is certainly *not* cached for different rows:
SELECT *, current_date, current_time, current_time FROM t
while($row = $sth -> fetch) { print ++$i," row: @$row"; sleep 5; }
1 row: 0 2012-02-12 19:20:40 19:20:40
2 row: 1 2012-02-12 19:20:40 19:20:40
3 row: 2 2012-02-12 19:20:45 19:20:45
4 row: 3 2012-02-12 19:20:50 19:20:50
5 row: 4 2012-02-12 19:20:55 19:20:55
(two first are same due to sqlite [or perl DBI binding?] seems executes one row
ahead).

But do they use same cached value *within one row*?

If not, results may be randomly inconsistent and broken (race condition).

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

Re: [sqlite] SQLite extensions repository

2012-02-12 Thread Alexey Pechnikov 
Developers support a private repositories usually. As example:

http://sqlite.mobigroup.ru/wiki?name=extensions

http://sqlite.mobigroup.ru/wiki?name=utils

2012/2/12 Marco Bambini :
> Hello,
> I am wondering if there is there a place that collect all available sqlite 
> extensions.
>
> Please let me know.
> Thanks.
> --
> Marco Bambini
> http://www.sqlabs.com
> http://twitter.com/sqlabs
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users



-- 
Best regards, Alexey Pechnikov.
http://pechnikov.tel/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Commit method not available

2012-02-12 Thread Kyle Cashion 
I'm using the drivers from sqlite-netFx40-setup-bundle-x86-2010-1.0.79.0.exe in 
VS2010, .NET 4.  Connection.BeginTransaction is available as a method, but 
Connection.Commit is not, nor is Connection.Rollback.

What am I missing?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] opening places.sqlite from Firefox (10.0.1) profile qAdmin Cannot perform on closed dataset

2012-02-12 Thread Christoph P.U. Kukulies 

Am 11.02.2012 13:47, schrieb Richard Hipp:

On Sat, Feb 11, 2012 at 4:56 AM, Christoph Kukulieswrote:


I'm trying to open some Firefox files that are located in the profiles
directory using Sqlite admin
(http://sqliteadmin.orbmu2k.**de/).

I'm getting an error message:

"Cannot perform this operation on a closed dataset."


That is not an error message that SQLite generates.  What program are you
trying to use to "open" the Firefox database files?


I wrote it: sqliteadmin (of the above site.)

I will try to open it by other means, but actually I never had a problem 
with said tool otherwise.


--
Christoph

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

Re: [sqlite] SQLite extensions repository

2012-02-12 Thread Luuk 
On 12-02-2012 19:48, Marco Bambini wrote:
> Hello,
> I am wondering if there is there a place that collect all available sqlite 
> extensions.
> 
> Please let me know.
> Thanks.

http://www.sqlite.org/contrib
looks like a good place for it ;)

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

Re: [sqlite] Usage of sqlite3_db_release_memory

2012-02-12 Thread Richard Hipp 
On Sat, Feb 11, 2012 at 11:50 AM, Rittick Gupta  wrote:

> What memory does the sqlite3_db_release_memory call release from the heap.
>

Cache memory, holding disk content that might possibly be reused in the
future but that is not currently in use.


> Is it required that this call be made frequently to free up space which is
> no longer used ?


No.  SQLite will automatically clean up all the memory it uses.


> Is it applicable in a shared cache environment ?
>

Yes.


>
> Will appreciate a response.
> ___
> 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

[sqlite] SQLite extensions repository

2012-02-12 Thread Marco Bambini 
Hello,
I am wondering if there is there a place that collect all available sqlite 
extensions.

Please let me know.
Thanks.
--
Marco Bambini
http://www.sqlabs.com
http://twitter.com/sqlabs

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

[sqlite] freelist_count - can it shrink back?

2012-02-12 Thread Orit Alul 
Hi,

Continuing the following thread, I'd like to explain my question better:
http://www.mail-archive.com/sqlite-users@sqlite.org/msg67668.html

When deleting from sqlite, the freed space is added to the free_list count.
My question is, when I insert new data, is the needed space allocated out of 
the free_list or do the db file grows larger and the free space stays till 
vacuum operation is performed?
Basically, my question is: on intensive inserts and deletes will the freed 
space be re-used ?

Please advice,
Thanks,
Orit



[Creative Zone by MediaMind]
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Usage of sqlite3_db_release_memory

2012-02-12 Thread Rittick Gupta 
What memory does the sqlite3_db_release_memory call release from the heap.
Is it required that this call be made frequently to free up space which is no 
longer used ? Is it applicable in a shared cache environment ?

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

Re: [sqlite] SQLite libraries

2012-02-12 Thread Luuk 
On 12-02-2012 19:15, Steinar Midtskogen wrote:
> Hello
> 
> Has anyone collected a library of extensions to SQLite, such as useful
> aggregate functions, modules, etc?
> 
> There is http://www.sqlite.org/contrib and extension-functions.c, but
> is there more out there?
> 

You can Google for: sqlite extension functions

And you will find within the first 10 results a link to:
http://brayden.org/twiki/bin/view/Software/SqliteExtensions

But, you might get better results if you add the function you are
looking for

another link:
http://reddog.s35.xrea.com/wiki/SQLite%20user%20function.html



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

[sqlite] SQLite libraries

2012-02-12 Thread Steinar Midtskogen 
Hello

Has anyone collected a library of extensions to SQLite, such as useful
aggregate functions, modules, etc?

There is http://www.sqlite.org/contrib and extension-functions.c, but
is there more out there?

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

[sqlite] Tiny correction

2012-02-12 Thread gwenn 
Hello,
It seems that the icuFunctionError can be simplified:

  char zBuf[128];
  sqlite3_snprintf(128, zBuf, "ICU error: %s(): %s", zName, u_errorName(e));
  zBuf[127] = '\0'; // <- useless

In the documentation:
"As long as the buffer size is greater than zero, sqlite3_snprintf()
guarantees that the buffer is always zero-terminated."

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

Re: [sqlite] Support EXTRACT SQL standard function

2012-02-12 Thread Roger Andersson 

On 02/11/12 15:22, Kit wrote:

2012/2/10 Willian Gustavo Veiga:

SQLite is a great database to unit test (TDD) applications. You can run it
in memory with your tests ...

I've found a problem when I was unit testing my application. MySQL
(production database) supports EXTRACT SQL standard function. SQLite don't
support it. It would be great to have support in this standard.
Unfortunately, strftime isn't a solution. It's not a standard.

Function strftime is your solution. Write two models. One for MySQL,
one for SQLite. These databases are quite different and require
different SQL queries.

Maybe views could be used to handle differences, at least some of them ;-)
sqlite> create view dateCurrent as
   ...> select
   ...> substr(date(),0,5) as year,
   ...> substr(date(),6,2) as month,
   ...> substr(date(),9,2) as day;
sqlite> .header on
sqlite> select * from dateCurrent;
year|month|day
2012|02|12

mysql> create view dateCurrent as
-> select
-> substr(current_date,1,4) as year,
-> substr(current_date,6,2) as month,
-> substr(current_date,9,2) as day;
mysql> select * from dateCurrent;
+--+---+-+
| year | month | day |
+--+---+-+
| 2012 | 02| 12  |
+--+---+-+

--
Roger


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