Re: [sqlite] wrong expression

[Richard Hipp]

On Thu, Jul 5, 2012 at 3:06 AM,  wrote:

>
>
>
> In file os_win.c in line 2052 must be:
> if( locktype==PENDING_LOCK && res ){
> instead of:
> if( locktype==EXCLUSIVE_LOCK && res ){
>

No.  Code is correct as written.  The SQLite core never actually requests a
PENDING lock.  PENDING is only an intermediate state on the way toward
EXCLUSIVE.  So locktype will never equal PENDING_LOCK.  The code in
question is simply advancing the lock state through the required
intermediate PENDING state.



> ___
> 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] SQLite4 nit/question

[Richard Hipp]

On Thu, Jul 5, 2012 at 7:23 PM, Nico Williams  wrote:

> In the key encoding page you talk about encoding E as a varint, and in
> some cases -E as a varint, but the page on varint says they are
> unsigned.  I assume that "-E" == ~E + 1 (i.e., two's complement of E),
> and that ~E == one's complement of E.
>

Varints encode only non-negative numbers.  Those places where you see -E
correspond to places where E is negative - numbers between -1.0 and 1.0.
Hence -E is non-negative and can be varint encoded.


>
> Nico
> --
> ___
> 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] Bug regarding HAVING aggregation and typecast comparisons

[Pavel Ivanov]

> sqlite> SELECT test_base.id, SUM(test_join.value) FROM test_base LEFT
> JOIN test_join ON test_join.base_id = test_base.id GROUP BY
> test_base.id HAVING SUM(test_join.value)='0';
> sqlite>
>
> The last statement generates no results.  There is no mention of a
> special case for HAVING so I would assume that the engine should also
> typecast the string into a corresponding numerical value.

No, your assumption is incorrect. SUM() is not a database column -
it's a function. So it doesn't have any affinity. Value '0' doesn't
have affinity too. Thus SQLite doesn't convert those values and
compares them as is. Numeric value won't be ever equal to string.


Pavel


On Thu, Jul 5, 2012 at 11:17 PM, Benjamin Feng  wrote:
> sqlite> CREATE TABLE test_base(id INTEGER NOT NULL PRIMARY KEY);
> sqlite> CREATE TABLE test_join(id INTEGER NOT NULL PRIMARY KEY,
> base_id INTEGER NOT NULL REFERENCES test_base(id), value NUMERIC NOT
> NULL);
> sqlite> INSERT INTO test_base VALUES(1);
> sqlite> INSERT INTO test_join VALUES(1, 1, 0);
>
> sqlite> SELECT test_base.id, test_join.value FROM test_base LEFT JOIN
> test_join ON test_join.base_id = test_base.id WHERE test_join.value=0;
> 1|0
> sqlite> SELECT test_base.id, test_join.value FROM test_base LEFT JOIN
> test_join ON test_join.base_id = test_base.id WHERE
> test_join.value='0';
> 1|0
> sqlite> SELECT test_base.id, SUM(test_join.value) FROM test_base LEFT
> JOIN test_join ON test_join.base_id = test_base.id GROUP BY
> test_base.id HAVING SUM(test_join.value)=0;
> 1|0
> sqlite> SELECT test_base.id, SUM(test_join.value) FROM test_base LEFT
> JOIN test_join ON test_join.base_id = test_base.id GROUP BY
> test_base.id HAVING SUM(test_join.value)='0';
> sqlite>
>
> The last statement generates no results.  There is no mention of a
> special case for HAVING so I would assume that the engine should also
> typecast the string into a corresponding numerical value.  This is
> failing on all numerical types of `value` (including INTEGER).
> ___
> 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] Database sharing across processes

[Pavel Ivanov]

On Thu, Jul 5, 2012 at 10:54 AM, Jonathan Haws
 wrote:
> I am fairly new to database development and I am working on an embedded 
> system where we are utilizing SQLite to manage some files and other 
> information that is being shared between processes.  What I am doing is I 
> have the SQLite amalgamation source code that I am compiling into each binary 
> executable and each executable is opening the same database file on disk.
>
> My question is this: is this the appropriate way to go about this?

Yes.

> Is there a better way to accomplish this task?

This is good enough, considering the amount of information you gave.

> If I continue down this path, are there are particular settings that I need 
> to set?

No, you don't need any settings.

> I have read through a lot of the documentation and it seems like I may want 
> to put the database in shared-cache mode, however that also seems to only 
> apply to threads within a single process.  Is that correct?

Correct. No need to use shared-cache mode if you use single-threaded processes.

> I am also thinking that I may want to make use of the sqlite_unlock_notify() 
> call to ensure that if I try to write to the database and it fails to get a 
> lock, it will pend until it is available.  However, I thought that a query 
> would pend until it gets a lock anyway.  Is that not the case?

sqlite3_unlock_notify() works only with shared-cache mode within one
process. It doesn't work in inter-process locking. And by default
query won't be pending until locking is possible. If you use function
sqlite3_busy_timeout() you can obtain behavior close to what you want.


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

[sqlite] Bug regarding HAVING aggregation and typecast comparisons

[Benjamin Feng]

sqlite> CREATE TABLE test_base(id INTEGER NOT NULL PRIMARY KEY);
sqlite> CREATE TABLE test_join(id INTEGER NOT NULL PRIMARY KEY,
base_id INTEGER NOT NULL REFERENCES test_base(id), value NUMERIC NOT
NULL);
sqlite> INSERT INTO test_base VALUES(1);
sqlite> INSERT INTO test_join VALUES(1, 1, 0);

sqlite> SELECT test_base.id, test_join.value FROM test_base LEFT JOIN
test_join ON test_join.base_id = test_base.id WHERE test_join.value=0;
1|0
sqlite> SELECT test_base.id, test_join.value FROM test_base LEFT JOIN
test_join ON test_join.base_id = test_base.id WHERE
test_join.value='0';
1|0
sqlite> SELECT test_base.id, SUM(test_join.value) FROM test_base LEFT
JOIN test_join ON test_join.base_id = test_base.id GROUP BY
test_base.id HAVING SUM(test_join.value)=0;
1|0
sqlite> SELECT test_base.id, SUM(test_join.value) FROM test_base LEFT
JOIN test_join ON test_join.base_id = test_base.id GROUP BY
test_base.id HAVING SUM(test_join.value)='0';
sqlite>

The last statement generates no results.  There is no mention of a
special case for HAVING so I would assume that the engine should also
typecast the string into a corresponding numerical value.  This is
failing on all numerical types of `value` (including INTEGER).
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Database sharing across processes

[Jonathan Haws]

I am fairly new to database development and I am working on an embedded system 
where we are utilizing SQLite to manage some files and other information that 
is being shared between processes.  What I am doing is I have the SQLite 
amalgamation source code that I am compiling into each binary executable and 
each executable is opening the same database file on disk.

My question is this: is this the appropriate way to go about this?  Is there a 
better way to accomplish this task?  If I continue down this path, are there 
are particular settings that I need to set?

I have read through a lot of the documentation and it seems like I may want to 
put the database in shared-cache mode, however that also seems to only apply to 
threads within a single process.  Is that correct?

I am also thinking that I may want to make use of the sqlite_unlock_notify() 
call to ensure that if I try to write to the database and it fails to get a 
lock, it will pend until it is available.  However, I thought that a query 
would pend until it gets a lock anyway.  Is that not the case?

Thanks for the help and sorry for being such a noob.

Jonathan


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

[sqlite] wrong expression

In file os_win.c in line 2052 must be:
if( locktype==PENDING_LOCK && res ){
instead of:
if( locktype==EXCLUSIVE_LOCK && res ){
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] seeking working PDO-Sqlite with php form example

[D A]

Can anybody explain to me how to set up my sqlite database file with my
Linux server?

To my limited knowledge, i have uploaded the following files in a directory
on my server:

1. sqlite.db
2. form.php

Do i have to also upload the command shell??  I am confident in the php and
sqlite code and file paths, but I just cannot get anything to show up on
screen.  Just a blank page, no action.  I've dealt with permissions as well.

What am I doing wrong???  Does anybody have working code tailored to a
simple form that I can build off??  Please help.

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

Re: [sqlite] Inserting from another table...

[Pavel Ivanov]

The insert statement below should insert one row into table
PP_VIEWER_SETTINGS. Does it do that? Is it what you called "does not
work"? To insert several rows you need to write a huge join of
dblookup to itself, so your insert statement should look like this:

insert into PP_VIEWER_SETTINGS (...)
select a.ItemValue, b.ItemValue, c.ItemValue, ...
from dblookup a, dblookup b, dblookup c, ...
where a.Category = "KvsSettings"
and a.ItemName = "Company"
and b.Category = "KvsSettings"
and b.ItemName = "DspNextPrevious"
and c.Category = "KvsSettings"
and c.ItemName = "EnableCarts"
...
and a.? = b.?
and a.? = c.?
...
;

Question marks here is the field which value should identify what row
particular ItemName should go to.


Pavel


On Thu, Jul 5, 2012 at 11:03 PM, Sam Carleton
 wrote:
> I am working on converting my system table from one form to another.  The
> old form was one row per value with a category/key/value (DBLookup) , the
> new form is a separate column for each value (PP_VIEWER_SETTINGS).  I am
> trying to create an insert statement to run when the new table is created,
> but when I run it, it does not work nor do I get any errors in SQLite
> manager:
>
> /* The OLD one */
> CREATE TABLE DBLookup (
> Category VARCHAR(32) NOT NULL,
> ItemName VARCHAR(128) NOT NULL,
> ItemValue VARCHAR(3000) NOT NULL,
> PRIMARY KEY(Category, ItemName))
>
> /* The NEW one */
> CREATE TABLE PP_VIEWER_SETTINGS
> (
>   VIEWER_SETTINGS_ID  INTEGER PRIMARY KEY
> AUTOINCREMENT,
>   COMPANY_NAMEVARCHAR(   260) NOT NULL,
>   DSPNEXTPREVIOUSSMALLINT NOT NULL,
>   ENABLE_CARTS   SMALLINT NOT NULL,
>   ENABLE_DEBUGINFO   SMALLINT NOT NULL,
>   ENABLE_FAVORITES   SMALLINT NOT NULL,
>   ENABLE_RIGHTCLICK  SMALLINT NOT NULL,
>   ENABLE_SLIDESHOW   SMALLINT NOT NULL,
>   ENABLE_TIMEOUT SMALLINT NOT NULL,
>   EXIT_KVS   SMALLINT NOT NULL,
>   EXIT_PASSWORD   VARCHAR(20) NOT NULL,
>   IS_CART_FAVORITES  SMALLINT NOT NULL,
>   IS_LOGIN_REQUIRED  SMALLINT NOT NULL,
>   IMAGE_SIZE  INTEGER NOT NULL,
>   PHONE_NUM_FORMATVARCHAR(20) NOT NULL,
>   THEME_IDINTEGER NOT NULL,
>   THUMBNAIL_SIZE SMALLINT NOT NULL,
>   TICKER_MSG  VARCHAR(   260) NOT NULL,
>   TO_AFTER   SMALLINT NOT NULL,
>   TO_STARTS  SMALLINT NOT NULL,
>   TO_TRANSITION_SECS SMALLINT NOT NULL,
>   SS_COUNT   SMALLINT NOT NULL,
>   SS_DEFAULT_IS_IN_SLIDESHOW SMALLINT NOT NULL,
>   SS_DISPLAY_SECONDS DOUBLE PRECISION NOT NULL,
>   SS_ZOOM_FACTOR DOUBLE PRECISION NOT NULL,
>   USERLAN VARCHAR(   260) NOT NULL
> );
>
> /* The insert script */
>
> insert into PP_VIEWER_SETTINGS
> ( COMPANY_NAME, DSPNEXTPREVIOUS, ENABLE_CARTS, ENABLE_DEBUGINFO,
> ENABLE_FAVORITES, ENABLE_RIGHTCLICK, ENABLE_SLIDESHOW,
>   ENABLE_TIMEOUT, EXIT_KVS, EXIT_PASSWORD, IS_CART_FAVORITES,
> IS_LOGIN_REQUIRED, IMAGE_SIZE, PHONE_NUM_FORMAT, THEME_ID,
>   THUMBNAIL_SIZE, TICKER_MSG, TO_AFTER, TO_STARTS, TO_TRANSITION_SECS,
> SS_COUNT, SS_DEFAULT_IS_IN_SLIDESHOW,
>   SS_DISPLAY_SECONDS, SS_ZOOM_FACTOR, USERLAN)
>   values (
> (select ItemValue from dblookup where Category = "KvsSettings" and
> ItemName = "Company"),
> (select ItemValue from dblookup where Category = "KvsSettings" and
> ItemName = "DspNextPrevious"),
> (select ItemValue from dblookup where Category = "KvsSettings" and
> ItemName = "EnableCarts"),
> (select ItemValue from dblookup where Category = "KvsSettings" and
> ItemName = "EnableDebugInfo"),
> (select ItemValue from dblookup where Category = "KvsSettings" and
> ItemName = "EnableFavorites"),
> (select ItemValue from dblookup where Category = "KvsSettings" and
> ItemName = "EnableRightClick"),
> (select ItemValue from dblookup where Category = "KvsSettings" and
> ItemName = "EnableSlideShow"),
> 1,
> (select ItemValue from dblookup where Category = "KvsSettings" and
> ItemName = "ExitKvs"),
> (select ItemValue from dblookup where Category = "KvsSettings" and
> ItemName = "ExitPassword"),
> (select ItemValue from dblookup where Category = "KvsSettings" and
> ItemName = "IsCartFavorites"),
> (select ItemValue from dblookup where Category = "KvsSettings" and
> ItemName = 

[sqlite] Inserting from another table...

[Sam Carleton]

I am working on converting my system table from one form to another.  The
old form was one row per value with a category/key/value (DBLookup) , the
new form is a separate column for each value (PP_VIEWER_SETTINGS).  I am
trying to create an insert statement to run when the new table is created,
but when I run it, it does not work nor do I get any errors in SQLite
manager:

/* The OLD one */
CREATE TABLE DBLookup (
Category VARCHAR(32) NOT NULL,
ItemName VARCHAR(128) NOT NULL,
ItemValue VARCHAR(3000) NOT NULL,
PRIMARY KEY(Category, ItemName))

/* The NEW one */
CREATE TABLE PP_VIEWER_SETTINGS
(
  VIEWER_SETTINGS_ID  INTEGER PRIMARY KEY
AUTOINCREMENT,
  COMPANY_NAMEVARCHAR(   260) NOT NULL,
  DSPNEXTPREVIOUSSMALLINT NOT NULL,
  ENABLE_CARTS   SMALLINT NOT NULL,
  ENABLE_DEBUGINFO   SMALLINT NOT NULL,
  ENABLE_FAVORITES   SMALLINT NOT NULL,
  ENABLE_RIGHTCLICK  SMALLINT NOT NULL,
  ENABLE_SLIDESHOW   SMALLINT NOT NULL,
  ENABLE_TIMEOUT SMALLINT NOT NULL,
  EXIT_KVS   SMALLINT NOT NULL,
  EXIT_PASSWORD   VARCHAR(20) NOT NULL,
  IS_CART_FAVORITES  SMALLINT NOT NULL,
  IS_LOGIN_REQUIRED  SMALLINT NOT NULL,
  IMAGE_SIZE  INTEGER NOT NULL,
  PHONE_NUM_FORMATVARCHAR(20) NOT NULL,
  THEME_IDINTEGER NOT NULL,
  THUMBNAIL_SIZE SMALLINT NOT NULL,
  TICKER_MSG  VARCHAR(   260) NOT NULL,
  TO_AFTER   SMALLINT NOT NULL,
  TO_STARTS  SMALLINT NOT NULL,
  TO_TRANSITION_SECS SMALLINT NOT NULL,
  SS_COUNT   SMALLINT NOT NULL,
  SS_DEFAULT_IS_IN_SLIDESHOW SMALLINT NOT NULL,
  SS_DISPLAY_SECONDS DOUBLE PRECISION NOT NULL,
  SS_ZOOM_FACTOR DOUBLE PRECISION NOT NULL,
  USERLAN VARCHAR(   260) NOT NULL
);

/* The insert script */

insert into PP_VIEWER_SETTINGS
( COMPANY_NAME, DSPNEXTPREVIOUS, ENABLE_CARTS, ENABLE_DEBUGINFO,
ENABLE_FAVORITES, ENABLE_RIGHTCLICK, ENABLE_SLIDESHOW,
  ENABLE_TIMEOUT, EXIT_KVS, EXIT_PASSWORD, IS_CART_FAVORITES,
IS_LOGIN_REQUIRED, IMAGE_SIZE, PHONE_NUM_FORMAT, THEME_ID,
  THUMBNAIL_SIZE, TICKER_MSG, TO_AFTER, TO_STARTS, TO_TRANSITION_SECS,
SS_COUNT, SS_DEFAULT_IS_IN_SLIDESHOW,
  SS_DISPLAY_SECONDS, SS_ZOOM_FACTOR, USERLAN)
  values (
(select ItemValue from dblookup where Category = "KvsSettings" and
ItemName = "Company"),
(select ItemValue from dblookup where Category = "KvsSettings" and
ItemName = "DspNextPrevious"),
(select ItemValue from dblookup where Category = "KvsSettings" and
ItemName = "EnableCarts"),
(select ItemValue from dblookup where Category = "KvsSettings" and
ItemName = "EnableDebugInfo"),
(select ItemValue from dblookup where Category = "KvsSettings" and
ItemName = "EnableFavorites"),
(select ItemValue from dblookup where Category = "KvsSettings" and
ItemName = "EnableRightClick"),
(select ItemValue from dblookup where Category = "KvsSettings" and
ItemName = "EnableSlideShow"),
1,
(select ItemValue from dblookup where Category = "KvsSettings" and
ItemName = "ExitKvs"),
(select ItemValue from dblookup where Category = "KvsSettings" and
ItemName = "ExitPassword"),
(select ItemValue from dblookup where Category = "KvsSettings" and
ItemName = "IsCartFavorites"),
(select ItemValue from dblookup where Category = "KvsSettings" and
ItemName = "IsLoginRequired"),
900,
(select ItemValue from dblookup where Category = "KvsSettings" and
ItemName = "PhoneNumberFormat"),
0,
(select ItemValue from dblookup where Category = "KvsSettings" and
ItemName = "ThumbnailSize"),
(select ItemValue from dblookup where Category = "KvsSettings" and
ItemName = "TickerMsg"),
(select ItemValue from dblookup where Category = "KvsSettings" and
ItemName = "TimeoutAfter"),
(select ItemValue from dblookup where Category = "KvsSettings" and
ItemName = "TimeoutStarts"),
(select ItemValue from dblookup where Category = "KvsSettings" and
ItemName = "TransitionSeconds"),
0,
(select ItemValue from dblookup where Category =
"SlideShowSettings" and ItemName = "DefaultIsInSlideShow"),
(select ItemValue from dblookup where Category =
"SlideShowSettings" and ItemName = "DisplaySeconds"),
(select ItemValue from dblookup where Category =

[sqlite] SQLite4 nit/question

[Nico Williams]

In the key encoding page you talk about encoding E as a varint, and in
some cases -E as a varint, but the page on varint says they are
unsigned.  I assume that "-E" == ~E + 1 (i.e., two's complement of E),
and that ~E == one's complement of E.

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

Re: [sqlite] Standalone Lua regex that can be used in sqlite

[Petite Abeille]

On Jul 4, 2012, at 11:00 AM, Domingo Alvarez Duarte wrote:

> I did a modification to the LUA regex code to allow using it without
> LUA dependency so it can be used with sqlite as regex function.

Well done. Lua [1] and SQLite are made for each other :)

[1] http://www.lua.org/about.html#name
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Multiple connections to in-memory database

[Jay A. Kreibich]

On Thu, Jul 05, 2012 at 09:03:54AM -0400, Pavel Ivanov scratched on the wall:
> So this feature shouldn't work for you. From my first message:
> 
> > But this possibility was
> > introduced in SQLite 3.7.13. So your asp.net provider should be
> > compiled with the latest version of SQLite, otherwise it won't work.


  Also, not to state the obvious, but you can only share a :memory:
  database across connections that originate from the same process.

   -j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it,
 but showing it to the wrong people has the tendency to make them
 feel uncomfortable." -- Angela Johnson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] When to call VACUUM - revisited

[Jay A. Kreibich]

On Thu, Jul 05, 2012 at 01:29:18PM +0100, Simon Slavin scratched on the wall:
> 
> On 5 Jul 2012, at 9:34am, _ph_  wrote:
> 
> > I already read your previous replies, but to revisit my scenaro:
> > 
> > - My OS is "sensitive to fragmentation"
> > - We are running with auto-vacuum enabled, so the freelist_count is usually
> > small (not a good indicator) 
> 
> Ah.  If you're always running auto-vacuum, then I don't think
> explicitly issuing VACUUM is going to be useful at all.  Don't bother.

  Yes, bother.  Auto-vacuum only deals with free pages.  It does not do
  all the other things a normal vacuum does.  Even if you run
  auto-vacuum, it is still a good idea to vacuum a very dynamic
  database from time to time.  Auto vacuum tends to *increase* the amount
  of fragmentation within the file, since it moves pages around to keep
  the free list short.  This means that pages for a given object (table,
  index, etc.) may be spread out across the SQLite file, which can cause 
  extra seeks during table/index scans.  OS level (filesystem)
  defragmentation won't help with this.

  http://www.sqlite.org/pragma.html#pragma_auto_vacuum
 
 Note, however, that auto-vacuum only truncates the freelist pages
 from the file. Auto-vacuum does not defragment the database nor
 repack individual database pages the way that the VACUUM command
 does. In fact, because it moves pages around within the file,
 auto-vacuum can actually make fragmentation worse.

  On the other hand, if the internal structure of the SQLite file is
  badly fragmented, having the file be fragmented in the filesystem
  isn't such a big deal.  You only take the hit once.

> >   but fragmentation supposedly gets worse
> 
> Fragmentation of the database file on disk is something that SQLite
> can't control, so you are down to the various defragmentation
> facilities (including the one built into Windows) to solve that.

  You can also get fragmentation inside the SQLite file, in the way
  that the pages are used.  VACUUM is the only way to fix this, since
  the defrag process has to do with moving SQLite pages around inside
  the SQLite file.

> > -We use sqlite as application data format, a typical user has dozens
> > of files. 
> >   This makes "During a support call" is not an option

  Yes and no.  If there is some hidden menu feature to force a VACUUM,
  that might come in handy if you have a customer with a particularly 
  large (or slow) file.  It is easy to put in "just in case", and 
  doesn't change the customer experience if it isn't in their face.

  If you're using databases as application files, I'm assuming they're
  not all that huge, however.  If the files are moderately small (a few
  dozen megs or less) you might just vacuum the file every time you
  open it (if file updates tend to be very dynamic) or every 20th time
  or something.  A file that's only a few megs only takes a few seconds
  to VACUUM.  You can put up a dialog that says "Optimizing file
  structure...".

  On the other hand, a file that's only a few megs is not likely to
  see much of a performance boost from a VACUUM.  I'd be more concerned
  about filesystem fragmentation than I would be about SQLite
  fragmentation.

> You could use the shell tool to turn the database file into SQL commands,
> and then back into a new database file on disk.  This will both 
> defragment the file, and make sure it's not using unneeded space.

  For all intents and purposes, this is what VACUUM does.

   -j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it,
 but showing it to the wrong people has the tendency to make them
 feel uncomfortable." -- Angela Johnson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Standalone LUA regex that can be used in sqlite

[Jay A. Kreibich]

On Wed, Jul 04, 2012 at 11:00:55AM +0200, Domingo Alvarez Duarte scratched on 
the wall:
> Hello !
> 
> I did a modification to the LUA regex code to allow using it without
> LUA dependency so it can be used with sqlite as regex function.

  Very handy!

> It's very light and small thus a good candidate to be included on sqlite.

  It's a very useful thing to have around, but understand it is extremely
  unlikely that the code will ever be part of the SQLite code base.  The
  difference in licenses makes this almost impossible.

> If you are interested on it you can download it here
> http://code.google.com/p/lua-regex-standalone/ , it's released under
> the same licence of LUA (MIT license).

  I would suggest making the sqlite3-lua-regex.c file a full-blown
  extension.  If you're careful about how you build the file, the
  code can be compiled as a static library, or as a dynamic extension.

  For examples on how to do this, have a look at the chapter 9
  examples from "Using SQLite."  The code can be downloaded here:
  http://examples.oreilly.com/9780596521196/



  There are also several extensions here, of varying quality:
  http://www.sqlite.org/contrib/



  Also see:

  sqlite3_auto_extension()
  http://www.sqlite.org/c3ref/auto_extension.html

  sqlite3_load_extension()
  http://www.sqlite.org/c3ref/load_extension.html



-j
  
-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it,
 but showing it to the wrong people has the tendency to make them
 feel uncomfortable." -- Angela Johnson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] When to call VACUUM - revisited

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 05/07/12 05:29, Simon Slavin wrote:
> Fragmentation of the database file on disk is something that SQLite
> can't control,

However you can ask SQLite to do things that will mitigate fragmentation
by extending the file in larger blocks.  See the file controls, especially
 SQLITE_FCNTL_CHUNK_SIZE:

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

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.11 (GNU/Linux)

iEYEARECAAYFAk/1oosACgkQmOOfHg372QQSRACgglDh1oe9cL7Wm7ShbUYjDWdZ
ONMAoJApn6aFivn9CtZiD66QIDJUn4md
=t518
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Strange issue with sqlite 3.7.9

[Alan Chandler]

On 05/07/12 01:05, Richard Hipp wrote:

On Wed, Jul 4, 2012 at 3:05 PM, Alan Chandlerwrote:


The commit referenced by that page:

http://www.sqlite.org/src/info/b23ae131874bc5c621f0

went into 3.7.9. So the problem was probably introduced in
3.7.9, not 3.7.10.



Indeed - I just tried the test case in that ticket and in fact
demonstrated that the bug is in 3.7.9


Does that mean that the problem is fixed by
http://www.sqlite.org/src/info/0dc4cb9355 and does not exist in recent
releases of SQLite?  Or are you saying that this is a new problem that
needs to be addressed.  If the latter, I'm going to need you to send me a
database again so that I can reproduce the problem, because I did keep the
one you sent last time.
No, its the same problem and fixed in later releases.  The only issue is 
that the main web site lists the bug as starting in 3.7.10, when in fact 
it starts in 3.7.9.  This is unfortunate because the latest Ubuntu LTS 
release (12.04) uses 3.7.9, and so my application broke again.  (I have 
reported this to Ubuntu and they have at least acknowledged the bug).


The test case in this ticket http://www.sqlite.org/src/info/b7c8682cc1 
demonstrates the problem in 3.7.9


--
Alan Chandler
http://www.chandlerfamily.org.uk

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

Re: [sqlite] Multiple connections to in-memory database

[T Ü]

Thank you so much Pavel. I will try with the new version.




 From: Pavel Ivanov 
To: T Ü  
Cc: General Discussion of SQLite Database  
Sent: Thursday, July 5, 2012 4:03 PM
Subject: Re: [sqlite] Multiple connections to in-memory database
 
So this feature shouldn't work for you. From my first message:

> But this possibility was
> introduced in SQLite 3.7.13. So your asp.net provider should be
> compiled with the latest version of SQLite, otherwise it won't work.


Pavel


On Thu, Jul 5, 2012 at 8:56 AM, T Ü  wrote:
> It returns 3.6.23.1
>
> 
> From: Pavel Ivanov 
> To: T Ü 
> Cc: General Discussion of SQLite Database 
> Sent: Thursday, July 5, 2012 3:40 PM
>
> Subject: Re: [sqlite] Multiple connections to in-memory database
>
> On Thu, Jul 5, 2012 at 8:37 AM, T Ü  wrote:
>> By trying I found out that SQLiteConnection("Data
>> Source=:memory:;cache=shared"); worked.
>> In a single aspx.page at cs code, first I open an in-memory database
>> connection
>>        SQLiteConnection conn = new SQLiteConnection ( "Data
>> Source=:memory:;cache=shared" );
>>        conn.Open();
>> than create table and insert some data
>> then without closing that connection open another connection in the
>> sameway,
>> but when I try to select the rows of the table that I created in the
>> previous table, I get no such table error.
>> What am I doing wrong???
>
> Please execute "SELECT sqlite_version()" in your cs code and tell us
> the result of it.
>
> Pavel
>
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Multiple connections to in-memory database

[Pavel Ivanov]

So this feature shouldn't work for you. From my first message:

> But this possibility was
> introduced in SQLite 3.7.13. So your asp.net provider should be
> compiled with the latest version of SQLite, otherwise it won't work.


Pavel


On Thu, Jul 5, 2012 at 8:56 AM, T Ü  wrote:
> It returns 3.6.23.1
>
> 
> From: Pavel Ivanov 
> To: T Ü 
> Cc: General Discussion of SQLite Database 
> Sent: Thursday, July 5, 2012 3:40 PM
>
> Subject: Re: [sqlite] Multiple connections to in-memory database
>
> On Thu, Jul 5, 2012 at 8:37 AM, T Ü  wrote:
>> By trying I found out that SQLiteConnection("Data
>> Source=:memory:;cache=shared"); worked.
>> In a single aspx.page at cs code, first I open an in-memory database
>> connection
>>SQLiteConnection conn = new SQLiteConnection ( "Data
>> Source=:memory:;cache=shared" );
>>conn.Open();
>> than create table and insert some data
>> then without closing that connection open another connection in the
>> sameway,
>> but when I try to select the rows of the table that I created in the
>> previous table, I get no such table error.
>> What am I doing wrong???
>
> Please execute "SELECT sqlite_version()" in your cs code and tell us
> the result of it.
>
> Pavel
>
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Multiple connections to in-memory database

[T Ü]

It returns 3.6.23.1




 From: Pavel Ivanov 
To: T Ü  
Cc: General Discussion of SQLite Database  
Sent: Thursday, July 5, 2012 3:40 PM
Subject: Re: [sqlite] Multiple connections to in-memory database
 
On Thu, Jul 5, 2012 at 8:37 AM, T Ü  wrote:
> By trying I found out that SQLiteConnection("Data
> Source=:memory:;cache=shared"); worked.
> In a single aspx.page at cs code, first I open an in-memory database
> connection
>         SQLiteConnection conn = new SQLiteConnection ( "Data
> Source=:memory:;cache=shared" );
>         conn.Open();
> than create table and insert some data
> then without closing that connection open another connection in the sameway,
> but when I try to select the rows of the table that I created in the
> previous table, I get no such table error.
> What am I doing wrong???

Please execute "SELECT sqlite_version()" in your cs code and tell us
the result of it.

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

Re: [sqlite] Multiple connections to in-memory database

[Pavel Ivanov]

On Thu, Jul 5, 2012 at 8:37 AM, T Ü  wrote:
> By trying I found out that SQLiteConnection("Data
> Source=:memory:;cache=shared"); worked.
> In a single aspx.page at cs code, first I open an in-memory database
> connection
> SQLiteConnection conn = new SQLiteConnection ( "Data
> Source=:memory:;cache=shared" );
> conn.Open();
> than create table and insert some data
> then without closing that connection open another connection in the sameway,
> but when I try to select the rows of the table that I created in the
> previous table, I get no such table error.
> What am I doing wrong???

Please execute "SELECT sqlite_version()" in your cs code and tell us
the result of it.

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

Re: [sqlite] Multiple connections to in-memory database

[T Ü]

By trying I found out that SQLiteConnection("Data 
Source=:memory:;cache=shared"); worked.
In a single aspx.page at cs code, first I open an in-memory database connection 

        SQLiteConnection conn = new SQLiteConnection ( "Data 
Source=:memory:;cache=shared" );
    conn.Open();

than create table and insert some data
then without closing that connection open another connection in the sameway, 
but when I try to select the rows of the table that I created in the previous 
table, I get no such table error.
What am I doing wrong???

 

 From: Pavel Ivanov 
To: T Ü ; General Discussion of SQLite Database 
 
Sent: Thursday, July 5, 2012 3:21 PM
Subject: Re: [sqlite] Multiple connections to in-memory database
 
On Thu, Jul 5, 2012 at 7:46 AM, T Ü  wrote:
> I have an asp.net application.
> I open a sqlite in-memory connection with SQLiteConnection conn = new 
> SQLiteConnection ( "Data Source=:memory:" ); command.
> I read that by using cache=shared parameter, I can make that in-memory 
> database reachable from other connections.
>
> 1.What is the way of applying cache=shared parameter in this type of 
> connection? SQLiteConnection conn = new SQLiteConnection ( "Data 
> Source=:memory:?cache=shared" ); is not working?

I think you should write SQLiteConnection("Data
Source=file::memory:?cache=shared"). But this possibility was
introduced in SQLite 3.7.13. So your asp.net provider should be
compiled with the latest version of SQLite, otherwise it won't work.

> 2.What is the way of creating a new connection for accessing the previously 
> opened in-memory database?

You should create new connection the same way as previously opened
one, i.e. SQLiteConnection("Data Source=file::memory:?cache=shared").


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

Re: [sqlite] When to call VACUUM - revisited

[Simon Slavin]

On 5 Jul 2012, at 9:34am, _ph_  wrote:

> I already read your previous replies, but to revisit my scenaro:
> 
> - My OS is "sensitive to fragmentation"
> - We are running with auto-vacuum enabled, so the freelist_count is usually
> small (not a good indicator) 

Ah.  If you're always running auto-vacuum, then I don't think explicitly 
issuing VACUUM is going to be useful at all.  Don't bother.

>   but fragmentation supposedly gets worse

Fragmentation of the database file on disk is something that SQLite can't 
control, so you are down to the various defragmentation facilities (including 
the one built into Windows) to solve that.

> -We use sqlite as application data format, a typical user has dozens of
> files. 
>   This makes "During a support call" is not an option

Okay.

> So for me from reading documentation and this list, there's an "omnious
> cloud of defragmentation looming" - wthout much data how bad it is or can
> be. 

You could use the shell tool to turn the database file into SQL commands, and 
then back into a new database file on disk.  This will both defragment the 
file, and make sure it's not using unneeded space.  Of course, it's a very slow 
process, and most people will use it only when they think their database file 
is corrupt.

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

Re: [sqlite] Multiple connections to in-memory database

[Pavel Ivanov]

On Thu, Jul 5, 2012 at 7:46 AM, T Ü  wrote:
> I have an asp.net application.
> I open a sqlite in-memory connection with SQLiteConnection conn = new 
> SQLiteConnection ( "Data Source=:memory:" ); command.
> I read that by using cache=shared parameter, I can make that in-memory 
> database reachable from other connections.
>
> 1.What is the way of applying cache=shared parameter in this type of 
> connection? SQLiteConnection conn = new SQLiteConnection ( "Data 
> Source=:memory:?cache=shared" ); is not working?

I think you should write SQLiteConnection("Data
Source=file::memory:?cache=shared"). But this possibility was
introduced in SQLite 3.7.13. So your asp.net provider should be
compiled with the latest version of SQLite, otherwise it won't work.

> 2.What is the way of creating a new connection for accessing the previously 
> opened in-memory database?

You should create new connection the same way as previously opened
one, i.e. SQLiteConnection("Data Source=file::memory:?cache=shared").


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

[sqlite] Multiple connections to in-memory database

[T Ü]

I have an asp.net application.
I open a sqlite in-memory connection with SQLiteConnection conn = new 
SQLiteConnection ( "Data Source=:memory:" ); command.
I read that by using cache=shared parameter, I can make that in-memory database 
reachable from other connections.

1.What is the way of applying cache=shared parameter in this type of 
connection? SQLiteConnection conn = new SQLiteConnection ( "Data 
Source=:memory:?cache=shared" ); is not working?

2.What is the way of creating a new connection for accessing the previously 
opened in-memory database?

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

Re: [sqlite] When to call VACUUM - revisited

[_ph_]

Hi Simon,

I already read your previous replies, but to revisit my scenaro:

 - My OS is "sensitive to fragmentation"
 - We are running with auto-vacuum enabled, so the freelist_count is usually
small (not a good indicator) 
   but fragmentation supposedly gets worse
 -We use sqlite as application data format, a typical user has dozens of
files. 
   This makes "During a support call" is not an option

So for me from reading documentation and this list, there's an "omnious
cloud of defragmentation looming" - wthout much data how bad it is or can
be. 

--
View this message in context: 
http://sqlite.1065341.n5.nabble.com/When-to-call-VACUUM-revisited-tp63114p63128.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