[sqlite] Insert / Update images using MS VBScript

[MoDementia]

I have spent most of the day searching for examples in VBscript to add /
update an image into a database without luck.

If anyone has a snippet of code they could share I would be most grateful.

I have either an image as an object in the script and or a physical file
location i.e. "C:\image.jpg"

None of the examples I looked at even came close to helping me understand
what I need to do :(

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

Re: [sqlite] Saving an in-memory database to file

[Jay A. Kreibich]

On Fri, May 30, 2008 at 09:24:29PM -0700, Bruce Robertson scratched on the wall:
> Well, an interesting illustration of basic sqlite; but no relation to the
> question being asked.

  Actually, it is a good answer to the question that was asked.

  Running SQLite without a database file creates an in-memory database.
  Using the .dump command will dump that in-memory DB to a SQL file that
  can then be re-read into a file-backed database (or back into a memory
  database), just as Mark asked about.

  Of course, I assume Mark wants to do this via code.  That will
  require poking around the shell code to see how the ".dump" command
  is implemented within the command shell.

   -j


> > On 5/30/08, Mark Stewart <[EMAIL PROTECTED]> wrote:
> >> 
> >>  Is there a recommended way to save an in-memory database to a file?  Is
> >> there
> >>  a way to access the underlying in-memory data directly to save out to disk
> >>  (if that would even work)?
> >> 
> >>  My other thought was to create an empty file based db and attach it,
> >>  creating tables and transferring all the data through sql.
> >> 
> >>  Maybe there is some other option?
> >> 
> > 
> > 
> > [12:04 AM] ~/foo$ ls
> > [12:04 AM] ~/foo$ sqlite3
> > SQLite version 3.5.6
> > Enter ".help" for instructions
> > sqlite> CREATE TABLE t (a, b);
> > sqlite> INSERT INTO t VALUES (1, 'one');
> > sqlite> INSERT INTO t VALUES (2, 'two');
> > sqlite> SELECT * FROM t;
> > 1|one
> > 2|two
> > sqlite> .q
> > [12:04 AM] ~/foo$ ls
> > [12:04 AM] ~/foo$ sqlite3
> > SQLite version 3.5.6
> > Enter ".help" for instructions
> > sqlite> CREATE TABLE t (a, b);
> > sqlite> INSERT INTO t VALUES (1, 'one');
> > sqlite> INSERT INTO t VALUES (2, 'two');
> > sqlite> SELECT * FROM t;
> > 1|one
> > 2|two
> > sqlite> .o foo.sql
> > sqlite> .dump
> > sqlite> .q
> > [12:05 AM] ~/foo$ ls
> > foo.sql
> > 12:05 AM] ~/foo$ cat foo.sql
> > BEGIN TRANSACTION;
> > CREATE TABLE t (a, b);
> > INSERT INTO "t" VALUES(1,'one');
> > INSERT INTO "t" VALUES(2,'two');
> > COMMIT;
> > [12:06 AM] ~/foo$
> > ___
> > 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

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

"'People who live in bamboo houses should not throw pandas.' Jesus said that."
   - "The Ninja", www.AskANinja.com, "Special Delivery 10: Pop!Tech 2006"
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Saving an in-memory database to file

[Bruce Robertson]

Well, an interesting illustration of basic sqlite; but no relation to the
question being asked.


> On 5/30/08, Mark Stewart <[EMAIL PROTECTED]> wrote:
>> 
>>  Is there a recommended way to save an in-memory database to a file?  Is
>> there
>>  a way to access the underlying in-memory data directly to save out to disk
>>  (if that would even work)?
>> 
>>  My other thought was to create an empty file based db and attach it,
>>  creating tables and transferring all the data through sql.
>> 
>>  Maybe there is some other option?
>> 
> 
> 
> [12:04 AM] ~/foo$ ls
> [12:04 AM] ~/foo$ sqlite3
> SQLite version 3.5.6
> Enter ".help" for instructions
> sqlite> CREATE TABLE t (a, b);
> sqlite> INSERT INTO t VALUES (1, 'one');
> sqlite> INSERT INTO t VALUES (2, 'two');
> sqlite> SELECT * FROM t;
> 1|one
> 2|two
> sqlite> .q
> [12:04 AM] ~/foo$ ls
> [12:04 AM] ~/foo$ sqlite3
> SQLite version 3.5.6
> Enter ".help" for instructions
> sqlite> CREATE TABLE t (a, b);
> sqlite> INSERT INTO t VALUES (1, 'one');
> sqlite> INSERT INTO t VALUES (2, 'two');
> sqlite> SELECT * FROM t;
> 1|one
> 2|two
> sqlite> .o foo.sql
> sqlite> .dump
> sqlite> .q
> [12:05 AM] ~/foo$ ls
> foo.sql
> 12:05 AM] ~/foo$ cat foo.sql
> BEGIN TRANSACTION;
> CREATE TABLE t (a, b);
> INSERT INTO "t" VALUES(1,'one');
> INSERT INTO "t" VALUES(2,'two');
> COMMIT;
> [12:06 AM] ~/foo$
> ___
> 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] Saving an in-memory database to file

[P Kishor]

On 5/30/08, Mark Stewart <[EMAIL PROTECTED]> wrote:
>
>  Is there a recommended way to save an in-memory database to a file?  Is there
>  a way to access the underlying in-memory data directly to save out to disk
>  (if that would even work)?
>
>  My other thought was to create an empty file based db and attach it,
>  creating tables and transferring all the data through sql.
>
>  Maybe there is some other option?
>


[12:04 AM] ~/foo$ ls
[12:04 AM] ~/foo$ sqlite3
SQLite version 3.5.6
Enter ".help" for instructions
sqlite> CREATE TABLE t (a, b);
sqlite> INSERT INTO t VALUES (1, 'one');
sqlite> INSERT INTO t VALUES (2, 'two');
sqlite> SELECT * FROM t;
1|one
2|two
sqlite> .q
[12:04 AM] ~/foo$ ls
[12:04 AM] ~/foo$ sqlite3
SQLite version 3.5.6
Enter ".help" for instructions
sqlite> CREATE TABLE t (a, b);
sqlite> INSERT INTO t VALUES (1, 'one');
sqlite> INSERT INTO t VALUES (2, 'two');
sqlite> SELECT * FROM t;
1|one
2|two
sqlite> .o foo.sql
sqlite> .dump
sqlite> .q
[12:05 AM] ~/foo$ ls
foo.sql
12:05 AM] ~/foo$ cat foo.sql
BEGIN TRANSACTION;
CREATE TABLE t (a, b);
INSERT INTO "t" VALUES(1,'one');
INSERT INTO "t" VALUES(2,'two');
COMMIT;
[12:06 AM] ~/foo$
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Saving an in-memory database to file

[Mark Stewart]

Is there a recommended way to save an in-memory database to a file?  Is there
a way to access the underlying in-memory data directly to save out to disk
(if that would even work)?  

My other thought was to create an empty file based db and attach it,
creating tables and transferring all the data through sql.

Maybe there is some other option?
-- 
View this message in context: 
http://www.nabble.com/Saving-an-in-memory-database-to-file-tp17571347p17571347.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

Re: [sqlite] Connections

[Jim Dodgen]

sqlite is serverless and does not have connections in the traditional 
sense.  The database is just a file and the number of programs that can 
have the file open is dependent upon the operating system.

Hildemaro Carrasquel wrote:
> Hello.-
>
> How many connections can i have in sqlite (simultaneously)?
>
>   

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

[sqlite] Connections

[Hildemaro Carrasquel]

Hello.-

How many connections can i have in sqlite (simultaneously)?

-- 
Ing. Hildemaro Carrasquel
Ingeniero de Proyectos
Cel.: 04164388917/04121832139
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] get the actual database size.

[Kees Nuyt]

On Fri, 30 May 2008 09:18:39 -0700 (PDT), you wrote:

>Hi ,
>I have two questions:
>    1) Calucate the actual size of the database(NOT RUN VACUUM) - "Actual 
>database size" which won't include the space of deleted rows.
>        To be more specific you calculate the database size by the following 
>below(WITHOUT VACUUM)
>        Database file size - (pragma freelist_count *  pragma page_size )
>        this calculation will return the actuall database size(the size 
>exclude the space of deleted rows)
>    2) No VACUUM the database which has a lot of deletion.
>    Is that true that the space of the database won't reclaim if we don't 
>run the "VACUUM" on the database.
>  Thanks,
>JP

Would sqlite3_analyzer work for you?
It produces both a human readable report as well as a table
definition and insert statements to feed to sqlite3 command
line tool.

CREATE TABLE space_used(
   name clob,-- Name of a table or index in the
database file
   tblname clob, -- Name of associated table
   is_index boolean, -- TRUE if it is an index, false for a
table
   nentry int,   -- Number of entries in the BTree
   leaf_entries int, -- Number of leaf entries
   payload int,  -- Total amount of data stored in this
table or index
   ovfl_payload int, -- Total amount of data stored on
overflow pages
   ovfl_cnt int, -- Number of entries that use overflow
   mx_payload int,   -- Maximum payload size
   int_pages int,-- Number of interior pages used
   leaf_pages int,   -- Number of leaf pages used
   ovfl_pages int,   -- Number of overflow pages used
   int_unused int,   -- Number of unused bytes on interior
pages
   leaf_unused int,  -- Number of unused bytes on primary
pages
   ovfl_unused int,  -- Number of unused bytes on overflow
pages
   gap_cnt int   -- Number of gaps in the page layout
);

A wealth of information, really.
sqlite3_analyzer is available on the download page
http://www.sqlite.org/download.html .
-- 
  (  Kees Nuyt
  )
c[_]
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Max data in row

[Igor Tandetnik]

Jay A. Kreibich <[EMAIL PROTECTED]> wrote:
> On Fri, May 30, 2008 at 07:40:13AM -0700, Ken scratched on the wall:
>> Hildemaro Carrasquel
>> <[EMAIL PROTECTED]> wrote:
>>>
>>> How many rows can i insert in one table?
>
>> I'm not 100% sure but I think the answer is:
>>
>> 1,844,674,407,370,9551,615
>>
>> which is an unsigned int (64bit).
>
>  RowIDs are signed, and every row needs a RowID.

However, I believe they can be negative. Now, SQLite never automatically 
generates a negative RowID, but you can insert one explicitly.

Igor Tandetnik 



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

Re: [sqlite] get the actual database size.

[Joanne Pham]

Hi ,
I have two questions:
    1) Calucate the actual size of the database(NOT RUN VACUUM) - "Actual 
database size" which won't include the space of deleted rows.
        To be more specific you calculate the database size by the following 
below(WITHOUT VACUUM)
        Database file size - (pragma freelist_count *  pragma page_size )
        this calculation will return the actuall database size(the size exclude 
the space of deleted rows)
    2) No VACUUM the database which has a lot of deletion.
    Is that true that the space of the database won't reclaim if we don't 
run the "VACUUM" on the database.
  Thanks,
JP



- Original Message 
From: Ronny Dierckx <[EMAIL PROTECTED]>
To: [EMAIL PROTECTED]; General Discussion of SQLite Database 

Sent: Friday, May 30, 2008 12:12:14 AM
Subject: Re: [sqlite] get the actual database size.

Hi,

I think a possible solution is to calculate the difference between
the database file size and the number of free pages multiplied by
the page size. This is of course an approximation, but it works for me.

Number of free pages: pragma freelist_count
Page size: pragma page_size 

Ronny

http://www.syntegro.be


-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of P Kishor
Sent: vrijdag 30 mei 2008 2:40
To: General Discussion of SQLite Database
Subject: Re: [sqlite] get the actual database size.

On 5/29/08, Joanne Pham <[EMAIL PROTECTED]> wrote:
> Hi All,
>  I have the database which has a lot of insertion and deletion.
>  Is there anyway that I can get the actual database size without running
VACUUM.

Your question implies that VACUUM lets you "get the database size."
No, it doesn't. VACUUM recovers the space left behind by deleting data
from the db.

To find out the size of the database, just read the size of the file
in the operating system.

Or, maybe you are asking something completely different that I don't get.


>  Thanks,
>  JP
>
>
___
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

Re: [sqlite] get the actual database size.

[P Kishor]

On 5/30/08, Joanne Pham <[EMAIL PROTECTED]> wrote:
>
>
>
> Thanks for response.
>
> I know the VACUUM is recovers the space left behind by deleting data from db
> but this
>
> is very expensive operator and it holds the locks for this database which
> will be the big impact for other operations like inserting/updating  the
> database.
>
> I can read tye size of file in the operating system but without VACUUM the
> database first the size is not accurate.

Well, it depends on how one defines the "size of the database." That
is such a misleading concept. In my view, the file size *is* the size
of the database with or without VACUUM-ing. VACUUM simply "changes"
the database ("changes" is in quotes) by recovering the deleted space.

There are so many other ways one could envision the "size" of the database --

1. The number of rows in tables

2. #1 above + all the space held by INDEXes (which can be substantial,
if not more than the tables themselves)

3. Any additional space held by the database's internal administrative
overhead -- TRIGGERs, VIEWs, schema tables, etc. all which are mystery
to mortals.

Then, while exactly the same database will be exactly the same bytes,
on the disk it will occupy different number of bytes depending on the
operating system and disk size.

To be absolutely clear, you have to ask for not the size of the
database, but the size of the file in which the database is held. Its
correct size is read from the filesystem (easy to do in SQLite, more
complicated in other db that scatter crap around on all kinds of
locations). The size at any given time depends on whether or not you
have run VACUUM.

So, there you have it. If you don't want to VACUUM it, you still get
the file size, but with deleted space included. If you VACUUM it, you
get the file size with no air pockets.


>
> So it seems like it is the must to ran the VACUUM before read the size of
> the file.
>
> Thanks,
>
> JP
>
>
> - Original Message 
> From: P Kishor <[EMAIL PROTECTED]>
> To: General Discussion of SQLite Database 
> Sent: Thursday, May 29, 2008 5:40:23 PM
> Subject: Re: [sqlite] get the actual database size.
>
> On 5/29/08, Joanne Pham <[EMAIL PROTECTED]> wrote:
> > Hi All,
> >  I have the database which has a lot of insertion and deletion.
> >  Is there anyway that I can get the actual database size without running
> VACUUM.
>
> Your question implies that VACUUM lets you "get the database size."
> No, it doesn't. VACUUM recovers the space left behind by deleting data
> from the db.
>
> To find out the size of the database, just read the size of the file
> in the operating system.
>
> Or, maybe you are asking something completely different that I don't get.
>
>
> >  Thanks,
> >  JP
> >
> >
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>


-- 
Puneet Kishor http://punkish.eidesis.org/
Nelson Institute for Environmental Studies http://www.nelson.wisc.edu/
Open Source Geospatial Foundation (OSGeo) http://www.osgeo.org/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] get the actual database size.

[Joanne Pham]

Thanks for response.
I know the VACUUM is recovers the space left behind by deleting data from db 
but this 
is very expensive operator and it holds the locks for this database which will 
be the big impact for other operations like inserting/updating  the database.
I can read tye size of file in the operating system but without VACUUM the 
database first the size is not accurate.
So it seems like it is the must to ran the VACUUM before read the size of the 
file.
Thanks,
JP



- Original Message 
From: P Kishor <[EMAIL PROTECTED]>
To: General Discussion of SQLite Database 
Sent: Thursday, May 29, 2008 5:40:23 PM
Subject: Re: [sqlite] get the actual database size.

On 5/29/08, Joanne Pham <[EMAIL PROTECTED]> wrote:
> Hi All,
>  I have the database which has a lot of insertion and deletion.
>  Is there anyway that I can get the actual database size without running 
>VACUUM.

Your question implies that VACUUM lets you "get the database size."
No, it doesn't. VACUUM recovers the space left behind by deleting data
from the db.

To find out the size of the database, just read the size of the file
in the operating system.

Or, maybe you are asking something completely different that I don't get.


>  Thanks,
>  JP
>
>
___
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] Max data in row

[Jay A. Kreibich]

On Fri, May 30, 2008 at 07:40:13AM -0700, Ken scratched on the wall:
> Hildemaro Carrasquel <[EMAIL PROTECTED]> wrote: 
> >
> > How many rows can i insert in one table?
 
> I'm not 100% sure but I think the answer is:
> 
> 1,844,674,407,370,9551,615
> 
> which is an unsigned int (64bit).

  RowIDs are signed, and every row needs a RowID.

  From :

...the largest ROWID is equal to the largest
possible integer (9223372036854775807 in SQLite
version 3.0.0 and later)...


   -j

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

"'People who live in bamboo houses should not throw pandas.' Jesus said that."
   - "The Ninja", www.AskANinja.com, "Special Delivery 10: Pop!Tech 2006"
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Max data in row

[Ken]

I'm not 100% sure but I think the answer is:

1,844,674,407,370,9551,615

which is an unsigned int (64bit).


Hildemaro Carrasquel <[EMAIL PROTECTED]> wrote: How many rows can i insert in 
one table?
-- 
Ing. Hildemaro Carrasquel
Ingeniero de Proyectos
Cel.: 04164388917/04121832139
___
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] Max data in row

[Hildemaro Carrasquel]

How many rows can i insert in one table?
-- 
Ing. Hildemaro Carrasquel
Ingeniero de Proyectos
Cel.: 04164388917/04121832139
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Setting Precision for Floating Point data

[Jay A. Kreibich]

On Fri, May 30, 2008 at 08:16:51AM -0500, John Stanton scratched on the wall:
> Use integers if you want to assign a specific scale and precision.

> Because floating point numbers are an approximation you can enforce a 
> certain precision by calculating differences.  

> You cannot use equality with FP

  You can, but you might not get the results you expect.

> but you can decide that equality is when (A - B) < |N| where N 
> is the precision and || absolute value.

  That would be:   |(A-B)| < N

   -j

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

"'People who live in bamboo houses should not throw pandas.' Jesus said that."
   - "The Ninja", www.AskANinja.com, "Special Delivery 10: Pop!Tech 2006"
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Setting Precision for Floating Point data

[John Stanton]

Use integers if you want to assign a specific scale and precision.

Because floating point numbers are an approximation you can enforce a 
certain precision by calculating differences.  You cannot use equality 
with FP but you can decide that equality is when (A - B) < |N| where N 
is the precision and || absolute value.

MoDementia wrote:
> After experiencing some difficulty with comparing dates stored internally as
> a floating point I was informed about the following.
> 
> "... due to the way floats are stored in computers, '=' isn't really a good
> choice of an operator for them. 
> Instead of
> 
> Date = 38953.5890509
> 
> you should use
> 
> Date > 38953.5890508 AND Date < 38953.5890510"
> 
> Is there any way to set a precision value? 10 or 12 even 8 decimal places
> would be fine in this instance for SQLite "REAL" data so that what is "seen"
> can be compared without surprises?
> 
> ___
> 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] Setting Precision for Floating Point data

[MoDementia]

After experiencing some difficulty with comparing dates stored internally as
a floating point I was informed about the following.

"... due to the way floats are stored in computers, '=' isn't really a good
choice of an operator for them. 
Instead of

Date = 38953.5890509

you should use

Date > 38953.5890508 AND Date < 38953.5890510"

Is there any way to set a precision value? 10 or 12 even 8 decimal places
would be fine in this instance for SQLite "REAL" data so that what is "seen"
can be compared without surprises?

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

Re: [sqlite] Follow-up: changed default ordering of SELECT queries in 3.5.9?

[D. Richard Hipp]

On May 30, 2008, at 7:52 AM, Ralf Junker wrote:

>  This undefined sort order has changed between previous versions and  
> might do so again in the future.


I would change "might" in the previous sentence to "probably".  ;-)


D. Richard Hipp
[EMAIL PROTECTED]



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

Re: [sqlite] changed default ordering of SELECT queries in 3.5.9?

[D. Richard Hipp]

On May 30, 2008, at 6:45 AM, Jens Miltner wrote:

> I'm suspecting that there is nothing that guarantees the order in
> which results are returned unless I explicitely specify an "ORDER BY"
> term,

Correct.

SQLite (and all other SQL database engines) are free to return results  
in whatever order it thinks it can get them most efficiently as long  
as there is no ORDER BY clause.  We frequently revise algorithms in  
order to increase performance.  You should never depend on the output  
order unless you have an explicit ORDER BY clause.

Note that adding an ORDER BY clause does not necessarily force SQLite  
to sort.  Often, an ORDER BY clause will merely constrain the choice  
of algorithms to one that naturally returns the results in the order  
you request.  In most cases, if a query is already returning results  
in the order you want, adding an ORDER BY will not slow it down.  The  
ORDER BY will simply prevent some future version of SQLite from using  
some newer algorithm that returns results in a different order.

D. Richard Hipp
[EMAIL PROTECTED]



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

Re: [sqlite] Follow-up: changed default ordering of SELECT queries in 3.5.9?

[Ralf Junker]

Hello Jens Miltner,

>However, I'd still like to know whether the new behavior of returning  
>the DISTINCT results in 'arbitrary' order is expected.

Unless ORDER BY is specified, the result of any SELECT is by definition 
undefined. This undefined sort order has changed between previous versions and 
might do so again in the future.

Ralf 

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

Re: [sqlite] Follow-up: changed default ordering of SELECT queries in 3.5.9?

[Jens Miltner]

Am 30.05.2008 um 12:55 schrieb Jens Miltner:

>
> Am 30.05.2008 um 12:45 schrieb Jens Miltner:
>
>> Hi,
>>
>> I've got a question and I don't know exactly whether the behavior  
>> is standard or expected or not.
>>
>> Assuming the following schema and data:
>> CREATE TABLE t1 (id, name);
>> INSERT INTO t1 values (1, 'John');
>> INSERT INTO t1 values (2, 'Arnold');
>> INSERT INTO t1 values (3, 'Zork');
>>
>> We have some queries that do roughly this:
>>
>> CREATE VIEW v1 AS SELECT id FROM t1 ORDER BY name;
>> CREATE TABLE t2 AS SELECT id FROM v1;
>> SELECT id from t2;
>>
>> This produces the ids in the expected order:
>>
>> 2
>> 1
>> 3
>>
>>
>> However, when fetching DISTINCT values, the values are returned in  
>> sorted order, not in the 'native' order in the table:
>>
>> SELECT DISTINCT id from t2;
>>
>> 1
>> 2
>> 3
>>
>>
>>
>> Up until version 3.5.1 (well actually that was the last version  
>> before 3.5.9 that we used), sqlite would return the distinct values  
>> in the same order as in the query without the DISTINCT keyword.
>>
>> I'm suspecting that there is nothing that guarantees the order in  
>> which results are returned unless I explicitely specify an "ORDER  
>> BY" term, so this may be according to the standards, but it's  
>> nonetheless a change that hurts us.
>> What we did so far was to create a temporary table that contained  
>> record IDs in a specific order, e.g.
>>
>> CREATE TEMP TABLE foo AS SELECT record_id FROM bar WHERE > condition> ORDER BY lastname;
>>
>> then, after some further processing, we'd do something like
>>
>> SELECT DISTINCT record_id FROM foo;
>>
>> to get the record IDs in the defined order and eventually something  
>> like
>>
>> SELECT lastname, firstname, contact_info
>> FROM foo
>> LEFT JOIN bar ON foo.record_id=bar.record_id
>> LEFT JOIN contacts ON contacts.bar_id=bar.record_id
>> WHERE foo.record_id=5;
>>
>> to get the information for a single record.
>>
>> The reasoning behind putting this sorted list of record IDs into a  
>> temporary table is not really obvious from this example, but our  
>> real code does a lot more processing and reuses some classes that  
>> use temporary or real tables to store record selections. These are  
>> mainly used as sets of records to display in lists for certain  
>> [dynamically constructed] filter conditions. The display code will  
>> then pick the record id and run a simple query to fetch the  
>> displayed properties for that record. Since we can't cache the  
>> information for all records in the displayed set (might be a really  
>> large set initially), we came up with this approach to reduce the  
>> size of the "cache" to the table containing just the record IDs.  
>> However, in order to get a fast access, we have to rely on the  
>> order of the record IDs in the table to match the order induced by  
>> the "ORDER BY" expression...
>>
>> So, to cut a long story short: is this change in the returned order  
>> for DISTINCT queries between version 3.5.1 and 3.5.9 a side effect  
>> of switching the query engine? If so, is it an undesired side  
>> effect or is it just something we have to live with?
>
> As a follow-up:
> One idea that occurred to me to work around this problem was to add  
> an "ORDER BY rowid" to my "SELECT DISTINCT record_id ..." statement  
> to enforce the "old" order, but apparently, one cannot order by  
> rowid (although it is a pseudo-column that can be SELECTed)?

Ahem - sorry for the wasted bandwidth, but I actually tried this on a  
joined statement and forgot to specify which table to fetch the rowid  
from.
Of course, ordering by rowid works (once I got the syntax right) and  
it produces the expected (i.e. 3.5.1-compatible) result list.
Mea culpa...

However, I'd still like to know whether the new behavior of returning  
the DISTINCT results in 'arbitrary' order is expected.

Thanks



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

[sqlite] Follow-up: changed default ordering of SELECT queries in 3.5.9?

[Jens Miltner]

Am 30.05.2008 um 12:45 schrieb Jens Miltner:

> Hi,
>
> I've got a question and I don't know exactly whether the behavior is  
> standard or expected or not.
>
> Assuming the following schema and data:
>  CREATE TABLE t1 (id, name);
>  INSERT INTO t1 values (1, 'John');
>  INSERT INTO t1 values (2, 'Arnold');
>  INSERT INTO t1 values (3, 'Zork');
>
> We have some queries that do roughly this:
>
>  CREATE VIEW v1 AS SELECT id FROM t1 ORDER BY name;
>  CREATE TABLE t2 AS SELECT id FROM v1;
>  SELECT id from t2;
>
> This produces the ids in the expected order:
>
> 2
> 1
> 3
>
>
> However, when fetching DISTINCT values, the values are returned in  
> sorted order, not in the 'native' order in the table:
>
>  SELECT DISTINCT id from t2;
>
> 1
> 2
> 3
>
>
>
> Up until version 3.5.1 (well actually that was the last version  
> before 3.5.9 that we used), sqlite would return the distinct values  
> in the same order as in the query without the DISTINCT keyword.
>
> I'm suspecting that there is nothing that guarantees the order in  
> which results are returned unless I explicitely specify an "ORDER  
> BY" term, so this may be according to the standards, but it's  
> nonetheless a change that hurts us.
> What we did so far was to create a temporary table that contained  
> record IDs in a specific order, e.g.
>
>  CREATE TEMP TABLE foo AS SELECT record_id FROM bar WHERE  condition> ORDER BY lastname;
>
> then, after some further processing, we'd do something like
>
>  SELECT DISTINCT record_id FROM foo;
>
> to get the record IDs in the defined order and eventually something  
> like
>
>  SELECT lastname, firstname, contact_info
>  FROM foo
>  LEFT JOIN bar ON foo.record_id=bar.record_id
>  LEFT JOIN contacts ON contacts.bar_id=bar.record_id
>  WHERE foo.record_id=5;
>
> to get the information for a single record.
>
> The reasoning behind putting this sorted list of record IDs into a  
> temporary table is not really obvious from this example, but our  
> real code does a lot more processing and reuses some classes that  
> use temporary or real tables to store record selections. These are  
> mainly used as sets of records to display in lists for certain  
> [dynamically constructed] filter conditions. The display code will  
> then pick the record id and run a simple query to fetch the  
> displayed properties for that record. Since we can't cache the  
> information for all records in the displayed set (might be a really  
> large set initially), we came up with this approach to reduce the  
> size of the "cache" to the table containing just the record IDs.  
> However, in order to get a fast access, we have to rely on the order  
> of the record IDs in the table to match the order induced by the  
> "ORDER BY" expression...
>
> So, to cut a long story short: is this change in the returned order  
> for DISTINCT queries between version 3.5.1 and 3.5.9 a side effect  
> of switching the query engine? If so, is it an undesired side effect  
> or is it just something we have to live with?

As a follow-up:
One idea that occurred to me to work around this problem was to add an  
"ORDER BY rowid" to my "SELECT DISTINCT record_id ..." statement to  
enforce the "old" order, but apparently, one cannot order by rowid  
(although it is a pseudo-column that can be SELECTed)?




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

[sqlite] changed default ordering of SELECT queries in 3.5.9?

[Jens Miltner]

Hi,

I've got a question and I don't know exactly whether the behavior is  
standard or expected or not.

Assuming the following schema and data:
   CREATE TABLE t1 (id, name);
   INSERT INTO t1 values (1, 'John');
   INSERT INTO t1 values (2, 'Arnold');
   INSERT INTO t1 values (3, 'Zork');

We have some queries that do roughly this:

   CREATE VIEW v1 AS SELECT id FROM t1 ORDER BY name;
   CREATE TABLE t2 AS SELECT id FROM v1;
   SELECT id from t2;

This produces the ids in the expected order:

2
1
3


However, when fetching DISTINCT values, the values are returned in  
sorted order, not in the 'native' order in the table:

   SELECT DISTINCT id from t2;

1
2
3



Up until version 3.5.1 (well actually that was the last version before  
3.5.9 that we used), sqlite would return the distinct values in the  
same order as in the query without the DISTINCT keyword.

I'm suspecting that there is nothing that guarantees the order in  
which results are returned unless I explicitely specify an "ORDER BY"  
term, so this may be according to the standards, but it's nonetheless  
a change that hurts us.
What we did so far was to create a temporary table that contained  
record IDs in a specific order, e.g.

   CREATE TEMP TABLE foo AS SELECT record_id FROM bar WHERE  ORDER BY lastname;

then, after some further processing, we'd do something like

   SELECT DISTINCT record_id FROM foo;

to get the record IDs in the defined order and eventually something like

   SELECT lastname, firstname, contact_info
   FROM foo
   LEFT JOIN bar ON foo.record_id=bar.record_id
   LEFT JOIN contacts ON contacts.bar_id=bar.record_id
   WHERE foo.record_id=5;

to get the information for a single record.

The reasoning behind putting this sorted list of record IDs into a  
temporary table is not really obvious from this example, but our real  
code does a lot more processing and reuses some classes that use  
temporary or real tables to store record selections. These are mainly  
used as sets of records to display in lists for certain [dynamically  
constructed] filter conditions. The display code will then pick the  
record id and run a simple query to fetch the displayed properties for  
that record. Since we can't cache the information for all records in  
the displayed set (might be a really large set initially), we came up  
with this approach to reduce the size of the "cache" to the table  
containing just the record IDs. However, in order to get a fast  
access, we have to rely on the order of the record IDs in the table to  
match the order induced by the "ORDER BY" expression...

So, to cut a long story short: is this change in the returned order  
for DISTINCT queries between version 3.5.1 and 3.5.9 a side effect of  
switching the query engine? If so, is it an undesired side effect or  
is it just something we have to live with?


Thanks,


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

Re: [sqlite] get the actual database size.

[Ronny Dierckx]

Hi,

I think a possible solution is to calculate the difference between
the database file size and the number of free pages multiplied by
the page size. This is of course an approximation, but it works for me.

Number of free pages: pragma freelist_count
Page size: pragma page_size 

Ronny

http://www.syntegro.be


-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of P Kishor
Sent: vrijdag 30 mei 2008 2:40
To: General Discussion of SQLite Database
Subject: Re: [sqlite] get the actual database size.

On 5/29/08, Joanne Pham <[EMAIL PROTECTED]> wrote:
> Hi All,
>  I have the database which has a lot of insertion and deletion.
>  Is there anyway that I can get the actual database size without running
VACUUM.

Your question implies that VACUUM lets you "get the database size."
No, it doesn't. VACUUM recovers the space left behind by deleting data
from the db.

To find out the size of the database, just read the size of the file
in the operating system.

Or, maybe you are asking something completely different that I don't get.


>  Thanks,
>  JP
>
>
___
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