Re: [sqlite] WAL journal size grow unlimited

[Alexey Pechnikov]

And additional problem fired when increased range j<=2000:
...
558
database disk image is malformed
while executing
"db eval {insert into role (uuid) values (hex(randomblob(16)))}"
...

$ ls -lh|grep .db
-rw-r--r-- 1 veter veter 4,4G Июл  7 09:10 grow.db
-rw-r--r-- 1 veter veter 288K Июл  7 09:10 grow.db-shm
-rw-r--r-- 1 veter veter 261M Июл  7 09:10 grow.db-wal

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

Re: [sqlite] Numbers as CHARs.

[Jay A. Kreibich]

On Tue, Jul 06, 2010 at 09:45:50PM -0400, Pavel Ivanov scratched on the wall:

> Leading zeros in the number can never be significant,

  While that's true for SQL values, it isn't true in the general case.
  C programmers and old-school UNIX folks tend to get very nervous
  about leading zeros.  Consider:
  
int main( ) { printf( "%d\n", 0123 ); }

  (hint: it prints "83").  Unless you know the application that is
  processing your input values very well, it pays not to make too many
  assumptions.

  For example, do you know the value of "i" and "d" in these statements?
  Without looking at the docs?  Do you think most programmers do?
  Do you think they usually get it "right" ?

  int i, d;
  sscanf( "0123", "%i", &i );
  sscanf( "0123", "%d", &d );

   -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] column types (was Re: Numbers as CHARs)

[Jay A. Kreibich]

On Tue, Jul 06, 2010 at 05:15:46PM -0700, Darren Duncan scratched on the wall:
> Simon Slavin wrote:
> > SQLite has types.  It just doesn't require every value in the same
> > column to be of the same type.
> 
> Or to put it another way, every SQLite column *does* have a type,

  Not really.  SQLite columns have an affinity, which is sorta-kinda a
  "preferred type."  It is not the same thing as an actual type.

  The only exception is INTEGER PRIMARY KEY columns, which must, in
  fact, contain nothing but integer values.

  Actual values must, of course, be one of integer, float, text,
  BLOB, or NULL, but with the exception of I PK columns, any column can
  have any value type.



  The confusing thing is that SQL has "column types" but SQLite doesn't
  really use them as type definitions.  Rather, SQLite uses SQL column
  types as text notations to determine a column affinity, but the
  mapping is somewhat indirect as defined in section 2.1 of
  .

   -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] Getting declared datatype of a column in C

[Jay A. Kreibich]

On Tue, Jul 06, 2010 at 11:27:09PM +0100, Andrew Wood scratched on the wall:
> Ive been looking at the function
> 
> sqlite3_column_decltype
> 
> and am a little confused as to its use.
> 
> How can I find out the declared type of a column.

  If you just want to know the declared type of a column you already
  know about, it use "PRAGMA table_info".

  http://www.sqlite.org/pragma.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] Import all tables from one db file to another

[Jay A. Kreibich]

On Tue, Jul 06, 2010 at 04:42:05PM -0500, Peng Yu scratched on the wall:
> I could create tables in database B to select all the data from
> database A and insert them in B. I don't find any simpler way to copy
> all tables from A to B. But I may miss something.
> 
> I'm wondering if there is a very simple single command that can just
> copy all the tables from database A to database B. Could you please
> let me know?

  No.  Your best bet is to select the sql column from sqlite_master,
  re-execute the command to create the exact same tables, and then
  attempt to copy the data with an INSERT...SELECT command.  This
  usually only works with simple tables, however, and gets messy with
  indexes, foreign keys, and other constraints.

  If you really want to see how to do it, look at the code for VACUUM.

   -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] How to determine when to VACUUM?

[Jay A. Kreibich]

On Tue, Jul 06, 2010 at 11:45:18PM +0200, Kristoffer Danielsson scratched on 
the wall:
> 
> I've been reading the documentation. I've been googling and thinking.
>  
> Still, I can't figure out the best way to determine when to run 
> the VACUUM-command. Note that I do NOT want to enable "auto vacuum".
> 
> I do remember reading something about calculating empty space,
> used pages etc etc. Still, no perfect answer.
>  
> Q: How do I programmatically (through sqlite-APIs?) determine if it's
> time to VACUUM a database? In general, what is the best method here?

  It depends on your needs and goals.

  VACUUM does two things.  First it recovers space.  This usually is
  not significant unless you've just deleted a huge number of rows you
  are unlikely to replace with other data.  Over time, most databases
  say roughly the same size, or they continually grow.  It is also easy
  to get into trouble if space is so tight you need to VACUUM, because
  the VACUUM process can easily require free space that is 2x the
  database size.  In other words, you can't VACUUM a 10GB DB file
  with only 10GB of free space, and you'll usually need something
  much closer to 20GB  (in essence, you need free space to hold a copy
  of the old and a copy of the new database in addition to the original
  database file).

  The other reason to VACUUM is to defragment the database.  This
  happens at the database page level and the file level.  VACUUM will
  "repack" database pages to get better record storage, and it will
  also rearrange the file so that all the pages that belong to a
  database object (table, index, etc.) are in the same place.  This can
  greatly improve scan speeds, as the I/O is better.  This is more
  important for somewhat large databases that have a high flux.

  Overall, I tend to manually VACUUM files when I delete something very
  large, or when roughly 40% of the contents have changed.  I've never
  done it in code.  There are many applications that use utility
  databases (prefs, configs, and even document files) that just
  never VACUUM.

   -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] WAL journal size grow unlimited

[Alexey Pechnikov]

Oh, yes, check null size for new database is incorrect in WAL mode. I
replace this script by more simple:
==
http://mobigroup.ru/files/test.tcl

#!/usr/bin/tclsh8.5
package require sqlite3

sqlite3 db grow.db
db eval {pragma cache_size=32000}
db eval {pragma journal_mode=WAL}
db eval {PRAGMA synchronous=normal}

db eval {CREATE VIRTUAL TABLE role USING fts3(id INTEGER PRIMARY KEY, uuid
TEXT);}

for {set j 1} {$j<=200} {incr j} {
  puts $j
  db transaction {
for {set i 0} {$i<10} {incr i} {
  db eval {insert into role (uuid) values (hex(randomblob(16)))}
}
  }
}

db close
==

After step between 40 and 45 WAL journal starts grow:

$ ls -lh|grep grow
-rw-r--r-- 1 veter veter 358M Июл  7 07:09 grow.db
-rw-r--r-- 1 veter veter  32K Июл  7 07:08 grow.db-shm
-rw-r--r-- 1 veter veter  23M Июл  7 07:09 grow.db-wal
$ ls -lh|grep grow
-rw-r--r-- 1 veter veter 358M Июл  7 07:09 grow.db
-rw-r--r-- 1 veter veter  32K Июл  7 07:08 grow.db-shm
-rw-r--r-- 1 veter veter 105M Июл  7 07:09 grow.db-wal
$ ls -lh|grep grow
-rw-r--r-- 1 veter veter 374M Июл  7 07:09 grow.db
-rw-r--r-- 1 veter veter 256K Июл  7 07:09 grow.db-shm
-rw-r--r-- 1 veter veter 228M Июл  7 07:09 grow.db-wal

There is only single process:

$ ls -lh|grep grow
-rw-r--r-- 1 veter veter 862M Июл  7 07:11 grow.db
-rw-r--r-- 1 veter veter 256K Июл  7 07:10 grow.db-shm
-rw-r--r-- 1 veter veter 254M Июл  7 07:11 grow.db-wal
$ fuser grow.db
grow.db: 16190
$ ps aux|grep 16190|grep -v grep
veter16190 84.8  3.2 304372 273136 pts/1   Dl+  07:07   3:48
/usr/bin/tclsh8.5 ./test.tcl
$ ls -lh|grep grow
-rw-r--r-- 1 veter veter 862M Июл  7 07:11 grow.db
-rw-r--r-- 1 veter veter 256K Июл  7 07:10 grow.db-shm
-rw-r--r-- 1 veter veter 254M Июл  7 07:11 grow.db-wal

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

Re: [sqlite] Numbers as CHARs.

[Pavel Ivanov]

> So, because type VLADIVOSTOK is not recognized, sqlite tries to
> convert any value entered, even if it is delimited with single quotes,
> to something recognizable. I guess it starts with INT, and since it is
> able to convert '043000205563' to integer, that is what it does. In
> the case of 'foobar', it can't convert it to integer, so it converts
> it to a text string.

Read that document again. :) This case is bullet 5 in section 2.1,
i.e. completely unrecognized type has NUMBER affinity which by
definition tries to convert everything first to integer, then if
conversion to integer fails it tries to convert to double, then if
that fails too it inserts what is given.

> Well, in defense of Simon, he is partially correct, in that, setting
> the right type would help.

You should give the definition of "right" then. My definition is as
follows: "right" type is the one that allows to achieve intended
functionality. With that in mind CHAR and TEXT are both "right", as
well as VARCHAR or VARCHAR(256). Everyone can use what he is used to.


Pavel

On Tue, Jul 6, 2010 at 10:09 PM, P Kishor  wrote:
> On Tue, Jul 6, 2010 at 8:45 PM, Pavel Ivanov  wrote:
 The receiving field is defined as CHAR; [snip]
>>> SQLite has no such type.  Define the fields as TEXT instead:
>>
>> Simon, please don't confuse poor users. SQLite will work perfectly and
>> indistinguishably well with both CHAR and TEXT. Please read the link
>> you gave more carefully (hint: bullet number 2 in the section 2.1).
>>
>>> Then try 'UPDATE UPCs SET UPC=043000205563;' and see what you get.
>>
>> And your propose is nothing better than original one. The problem is
>> you both are trying to insert into text field a number. Leading zeros
>> in the number can never be significant, so they are trimmed before
>> this number is converted to text. The solution is to put single quotes
>> around anything that supposed to be treated as text.
>
> Well, in defense of Simon, he is partially correct, in that, setting
> the right type would help. However, he errs in that he does not advise
> the OP to use single quotes to delimit the string. Consider the
> following --
>
> sqlite> CREATE TABLE UPCs (UPC VLADIVOSTOK);
> sqlite> INSERT INTO UPCs VALUES ('043000205563');
> sqlite> SELECT * FROM UPCs;
> UPC
> ---
> 43000205563
> sqlite> SELECT Typeof(UPC) FROM UPCs;
> Typeof(UPC)
> ---
> integer
> sqlite> .s
> CREATE TABLE UPCs (UPC VLADIVOSTOK);
> sqlite> INSERT INTO UPCs VALUES ('foobar');
> sqlite> SELECT Typeof(UPC) FROM UPCs;
> Typeof(UPC)
> ---
> integer
> text
> sqlite>
>
>
> So, because type VLADIVOSTOK is not recognized, sqlite tries to
> convert any value entered, even if it is delimited with single quotes,
> to something recognizable. I guess it starts with INT, and since it is
> able to convert '043000205563' to integer, that is what it does. In
> the case of 'foobar', it can't convert it to integer, so it converts
> it to a text string.
>
> At least, that is how I understand it.
>
> This whole type and affinity thing seems to be the source of much
> confusion for many folks. I am sure I would also be confused by it if
> I spent more than a moment on it. Since I do all my checks for data in
> and out in my application, I really don't ever bother with this stuff,
> but I wonder if there were some way to reduce this confusion in the
> minds of others.
>
>
>>
>>
>> Pavel
>>
>> On Tue, Jul 6, 2010 at 6:17 PM, Simon Slavin  wrote:
>>>
>>> On 6 Jul 2010, at 11:10pm, Ted Rolle, Jr. wrote:
>>>
 The receiving field is defined as CHAR; [snip]
>>>
>>> SQLite has no such type.  Define the fields as TEXT instead:
>>>
>>> 
>>>
>>> Then try 'UPDATE UPCs SET UPC=043000205563;' and see what you get.
>>>
 Last question: is this an example of SQLite's "typelessness"?
>>>
>>> SQLite has types.  It just doesn't require every value in the same column 
>>> to be of the same type.
>>>
>>> Simon.
>>> ___
>>> 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
>>
>
>
>
> --
> Puneet Kishor http://www.punkish.org
> Carbon Model http://carbonmodel.org
> Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
> Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
> Nelson Institute, UW-Madison http://www.nelson.wisc.edu
> ---
> Assertions are politics; backing up assertions with evidence is science
> ===
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-us

Re: [sqlite] Numbers as CHARs.

[P Kishor]

On Tue, Jul 6, 2010 at 8:45 PM, Pavel Ivanov  wrote:
>>> The receiving field is defined as CHAR; [snip]
>> SQLite has no such type.  Define the fields as TEXT instead:
>
> Simon, please don't confuse poor users. SQLite will work perfectly and
> indistinguishably well with both CHAR and TEXT. Please read the link
> you gave more carefully (hint: bullet number 2 in the section 2.1).
>
>> Then try 'UPDATE UPCs SET UPC=043000205563;' and see what you get.
>
> And your propose is nothing better than original one. The problem is
> you both are trying to insert into text field a number. Leading zeros
> in the number can never be significant, so they are trimmed before
> this number is converted to text. The solution is to put single quotes
> around anything that supposed to be treated as text.

Well, in defense of Simon, he is partially correct, in that, setting
the right type would help. However, he errs in that he does not advise
the OP to use single quotes to delimit the string. Consider the
following --

sqlite> CREATE TABLE UPCs (UPC VLADIVOSTOK);
sqlite> INSERT INTO UPCs VALUES ('043000205563');
sqlite> SELECT * FROM UPCs;
UPC
---
43000205563
sqlite> SELECT Typeof(UPC) FROM UPCs;
Typeof(UPC)
---
integer
sqlite> .s
CREATE TABLE UPCs (UPC VLADIVOSTOK);
sqlite> INSERT INTO UPCs VALUES ('foobar');
sqlite> SELECT Typeof(UPC) FROM UPCs;
Typeof(UPC)
---
integer
text
sqlite>


So, because type VLADIVOSTOK is not recognized, sqlite tries to
convert any value entered, even if it is delimited with single quotes,
to something recognizable. I guess it starts with INT, and since it is
able to convert '043000205563' to integer, that is what it does. In
the case of 'foobar', it can't convert it to integer, so it converts
it to a text string.

At least, that is how I understand it.

This whole type and affinity thing seems to be the source of much
confusion for many folks. I am sure I would also be confused by it if
I spent more than a moment on it. Since I do all my checks for data in
and out in my application, I really don't ever bother with this stuff,
but I wonder if there were some way to reduce this confusion in the
minds of others.


>
>
> Pavel
>
> On Tue, Jul 6, 2010 at 6:17 PM, Simon Slavin  wrote:
>>
>> On 6 Jul 2010, at 11:10pm, Ted Rolle, Jr. wrote:
>>
>>> The receiving field is defined as CHAR; [snip]
>>
>> SQLite has no such type.  Define the fields as TEXT instead:
>>
>> 
>>
>> Then try 'UPDATE UPCs SET UPC=043000205563;' and see what you get.
>>
>>> Last question: is this an example of SQLite's "typelessness"?
>>
>> SQLite has types.  It just doesn't require every value in the same column to 
>> be of the same type.
>>
>> Simon.
>> ___
>> 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
>



-- 
Puneet Kishor http://www.punkish.org
Carbon Model http://carbonmodel.org
Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
Nelson Institute, UW-Madison http://www.nelson.wisc.edu
---
Assertions are politics; backing up assertions with evidence is science
===
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] EXTERNAL: setup sqlite in vc++

[Pavel Ivanov]

> I found out that including the header file alone is not enough. I need to
> link the sqlite lib to my project. But how can can I get the lib?

Just include sqlite3.c file into your project as a source and that's
it, VC++ will take care of compiling it and linking it into your
binary.


Pavel

On Tue, Jul 6, 2010 at 6:09 PM, smengl90
 wrote:
>
> I found out that including the header file alone is not enough. I need to
> link the sqlite lib to my project. But how can can I get the lib?
>
>
>
> smengl90 wrote:
>>
>> Thanks for prompt replies. I did as you advised, and included the .h file
>> as a source file and copied your code. But when I compiled it, I got these
>> linking errors:
>>
>> 1>Linking...
>> 1>TestSqlite.obj : error LNK2019: unresolved external symbol
>> _sqlite3_close referenced in function _main
>> 1>TestSqlite.obj : error LNK2019: unresolved external symbol _sqlite3_free
>> referenced in function _main
>> 1>TestSqlite.obj : error LNK2019: unresolved external symbol _sqlite3_exec
>> referenced in function _main
>> 1>TestSqlite.obj : error LNK2019: unresolved external symbol _sqlite3_open
>> referenced in function _main
>> 1>C:\Documents and Settings\My Documents\Visual Studio
>> 2008\Projects\TestSqlite\Debug\TestSqlite.exe : fatal error LNK1120: 4
>> unresolved externals
>>
>> Any ideas what I did wrong?
>>
>> Thanks
>>
>>
>> Black, Michael (IS) wrote:
>>>
>>> You don't need C++ wrappers...just code in C.  There's lots more C
>>> examples out there than C++.
>>>
>>> Easiest way for me to integrate is to download the amalgamation and
>>> include it as  a source file in your project.
>>>
>>> The amalgamzation is at the top of the download page:
>>>
>>> http://www.sqlite.org/download.html
>>>
>>> Simple program for you to compile as C++ which I wrote from a question
>>> somebody had on the list here.
>>>
>>> #include 
>>> #include 
>>> #include "sqlite3.h"
>>> int main()
>>> {
>>>         sqlite3 *db;
>>>         char *errmsg=NULL;
>>>         int rc;
>>>         int i;
>>>         sqlite3_open("test.db",&db);
>>>         rc=sqlite3_exec(db, "CREATE TABLE log (id_client varchar, utc
>>> number)",NULL,NULL,&errmsg);
>>>         if (rc != SQLITE_OK) {
>>>                 puts(errmsg);
>>>                 sqlite3_free(errmsg);
>>>         }
>>>         sqlite3_exec(db,"BEGIN",NULL,NULL,&errmsg);
>>>         for(i=0;i<10;i++) {
>>>          int j=i*10;
>>>          char sql[4096];
>>>          sprintf(sql,"INSERT INTO log VALUES ('%d',%d)",i,j);
>>>          rc=sqlite3_exec(db, sql,NULL,NULL,&errmsg);
>>>          if (rc != SQLITE_OK) {
>>>                  std::cout << errmsg << std::endl;
>>>                  sqlite3_free(errmsg);
>>>                  exit(-1);
>>>          }
>>>         }
>>>         sqlite3_exec(db,"COMMIT",NULL,NULL,&errmsg);
>>>         sqlite3_close(db);
>>>         return 0;
>>> }
>>>
>>>
>>> Michael D. Black
>>> Senior Scientist
>>> Northrop Grumman Mission Systems
>>>
>>>
>>> 
>>>
>>> From: sqlite-users-boun...@sqlite.org on behalf of smengl90
>>> Sent: Tue 7/6/2010 2:33 PM
>>> To: sqlite-users@sqlite.org
>>> Subject: EXTERNAL:[sqlite] setup sqlite in vc++
>>>
>>>
>>>
>>>
>>> Hi guys,
>>>
>>> I am trying to setup sqlite to be used with VC++ 2008. Can someone show
>>> me
>>> where I can find instructions on how to set it up? and do I need a c++
>>> wrapper to code in C++? If yes, can someone also show how to setup the
>>> wrapper?
>>>
>>> Thanks
>>> --
>>> View this message in context:
>>> http://old.nabble.com/setup-sqlite-in-vc%2B%2B-tp29086729p29086729.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
>>>
>>>
>>>
>>> ___
>>> sqlite-users mailing list
>>> sqlite-users@sqlite.org
>>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>>
>>>
>>
>>
>
> --
> View this message in context: 
> http://old.nabble.com/setup-sqlite-in-vc%2B%2B-tp29086729p29090562.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
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Numbers as CHARs.

[Pavel Ivanov]

>> The receiving field is defined as CHAR; [snip]
> SQLite has no such type.  Define the fields as TEXT instead:

Simon, please don't confuse poor users. SQLite will work perfectly and
indistinguishably well with both CHAR and TEXT. Please read the link
you gave more carefully (hint: bullet number 2 in the section 2.1).

> Then try 'UPDATE UPCs SET UPC=043000205563;' and see what you get.

And your propose is nothing better than original one. The problem is
you both are trying to insert into text field a number. Leading zeros
in the number can never be significant, so they are trimmed before
this number is converted to text. The solution is to put single quotes
around anything that supposed to be treated as text.


Pavel

On Tue, Jul 6, 2010 at 6:17 PM, Simon Slavin  wrote:
>
> On 6 Jul 2010, at 11:10pm, Ted Rolle, Jr. wrote:
>
>> The receiving field is defined as CHAR; [snip]
>
> SQLite has no such type.  Define the fields as TEXT instead:
>
> 
>
> Then try 'UPDATE UPCs SET UPC=043000205563;' and see what you get.
>
>> Last question: is this an example of SQLite's "typelessness"?
>
> SQLite has types.  It just doesn't require every value in the same column to 
> be of the same type.
>
> Simon.
> ___
> 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] Numbers as CHARs.

[Ted Rolle, Jr.]

On Tue, 6 Jul 2010 17:13:44 -0500
P Kishor  wrote:

> sqlite> CREATE TABLE UPCs (UPC TEXT);
> sqlite> INSERT INTO UPCs VALUES ('043000205563');
> sqlite> SELECT * FROM UPCs;
> UPC
> 
> 043000205563
> sqlite>

I did as you said with sqlite and it worked perfectly.  Thank you.

This is probably out of the group's purview, but can it be a problem
with SQLite Manager?

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

Re: [sqlite] EXTERNAL: setup sqlite in vc++

[Mohd Radzi Ibrahim]

Hi, 

You could also use another open source layer for SQLite3, wxSQLite3 project on 
sourceforge. It's uses wxWidgets cross-platform C++ toolkit.

best regards,
Radzi. 
On 7-Jul-2010, at 6:09 AM, smengl90 wrote:

> 
> I found out that including the header file alone is not enough. I need to
> link the sqlite lib to my project. But how can can I get the lib? 
> 
> 
> 
> smengl90 wrote:
>> 
>> Thanks for prompt replies. I did as you advised, and included the .h file
>> as a source file and copied your code. But when I compiled it, I got these
>> linking errors:
>> 
>> 1>Linking...
>> 1>TestSqlite.obj : error LNK2019: unresolved external symbol
>> _sqlite3_close referenced in function _main
>> 1>TestSqlite.obj : error LNK2019: unresolved external symbol _sqlite3_free
>> referenced in function _main
>> 1>TestSqlite.obj : error LNK2019: unresolved external symbol _sqlite3_exec
>> referenced in function _main
>> 1>TestSqlite.obj : error LNK2019: unresolved external symbol _sqlite3_open
>> referenced in function _main
>> 1>C:\Documents and Settings\My Documents\Visual Studio
>> 2008\Projects\TestSqlite\Debug\TestSqlite.exe : fatal error LNK1120: 4
>> unresolved externals
>> 
>> Any ideas what I did wrong?
>> 
>> Thanks
>> 
>> 
>> Black, Michael (IS) wrote:
>>> 
>>> You don't need C++ wrappers...just code in C.  There's lots more C
>>> examples out there than C++.
>>> 
>>> Easiest way for me to integrate is to download the amalgamation and
>>> include it as  a source file in your project.
>>> 
>>> The amalgamzation is at the top of the download page:
>>> 
>>> http://www.sqlite.org/download.html
>>> 
>>> Simple program for you to compile as C++ which I wrote from a question
>>> somebody had on the list here.
>>> 
>>> #include 
>>> #include 
>>> #include "sqlite3.h"
>>> int main()
>>> {
>>>sqlite3 *db;
>>>char *errmsg=NULL;
>>>int rc;
>>>int i;
>>>sqlite3_open("test.db",&db);
>>>rc=sqlite3_exec(db, "CREATE TABLE log (id_client varchar, utc
>>> number)",NULL,NULL,&errmsg);
>>>if (rc != SQLITE_OK) {
>>>puts(errmsg);
>>>sqlite3_free(errmsg);
>>>}
>>>sqlite3_exec(db,"BEGIN",NULL,NULL,&errmsg);
>>>for(i=0;i<10;i++) {
>>> int j=i*10;
>>> char sql[4096];
>>> sprintf(sql,"INSERT INTO log VALUES ('%d',%d)",i,j);
>>> rc=sqlite3_exec(db, sql,NULL,NULL,&errmsg);
>>> if (rc != SQLITE_OK) {
>>> std::cout << errmsg << std::endl;
>>> sqlite3_free(errmsg);
>>> exit(-1);
>>> }
>>>}
>>>sqlite3_exec(db,"COMMIT",NULL,NULL,&errmsg);
>>>sqlite3_close(db);
>>>return 0;
>>> }
>>> 
>>> 
>>> Michael D. Black
>>> Senior Scientist
>>> Northrop Grumman Mission Systems
>>> 
>>> 
>>> 
>>> 
>>> From: sqlite-users-boun...@sqlite.org on behalf of smengl90
>>> Sent: Tue 7/6/2010 2:33 PM
>>> To: sqlite-users@sqlite.org
>>> Subject: EXTERNAL:[sqlite] setup sqlite in vc++
>>> 
>>> 
>>> 
>>> 
>>> Hi guys,
>>> 
>>> I am trying to setup sqlite to be used with VC++ 2008. Can someone show
>>> me
>>> where I can find instructions on how to set it up? and do I need a c++
>>> wrapper to code in C++? If yes, can someone also show how to setup the
>>> wrapper?
>>> 
>>> Thanks
>>> --
>>> View this message in context:
>>> http://old.nabble.com/setup-sqlite-in-vc%2B%2B-tp29086729p29086729.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
>>> 
>>> 
>>> 
>>> ___
>>> sqlite-users mailing list
>>> sqlite-users@sqlite.org
>>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>> 
>>> 
>> 
>> 
> 
> -- 
> View this message in context: 
> http://old.nabble.com/setup-sqlite-in-vc%2B%2B-tp29086729p29090562.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

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

[sqlite] column types (was Re: Numbers as CHARs)

[Darren Duncan]

Simon Slavin wrote:
> SQLite has types.  It just doesn't require every value in the same column to 
> be of the same type.

Or to put it another way, every SQLite column *does* have a type, but in the 
general case that type is a generic or union type whose domain encompasses the 
domains of the numeric, text, blob, etc types.  This is how I see it, and put 
in 
those terms, SQLite is still strongly typed, but it is just more flexible than 
some other DBMSs, those that don't support generic or union types.

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

Re: [sqlite] Getting declared datatype of a column in C

[Igor Tandetnik]

Andrew Wood  wrote:
> Ive been looking at the function
> 
> sqlite3_column_decltype
> 
> and am a little confused as to its use.
> 
> How can I find out the declared type of a column.
> 
> As I understand this function, I would have to do a single col select first

Not necessarily a single-column one. The second parameter of the function is 
the column number. Also, you don't need to actually run the query - it's enough 
to just prepare it.

> (select col1 from table) then pass that prepared
> statement to ?  sqlite3_column_decltype 
> 
> Then for the second col change the query to "select col2 from table" and 
> repeat.

You can do "select col1, col2, col3 from mytable;" and query all columns at 
once.
-- 
Igor Tandetnik

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

Re: [sqlite] WAL journal size grow unlimited

[Richard Hipp]

2010/7/6 Alexey Pechnikov 

>
> My test script
> http://mobigroup.ru/files/test.tcl
>


When I run this script it immediately crashes with  "no such table:
view_role"


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



-- 
-
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] Getting declared datatype of a column in C

[Andrew Wood]

Ive been looking at the function

sqlite3_column_decltype

and am a little confused as to its use.

How can I find out the declared type of a column.

As I understand this function, I would have to do a single col select first, 
(select col1 from table) then pass that prepared statement to ?  
sqlite3_column_decltype

Then for the second col change the query to "select col2 from table" and repeat.

Surely theres a quicker way?



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

Re: [sqlite] How to determine when to VACUUM?

[Gerry Snyder]

On 7/6/2010 2:58 PM, Kristoffer Danielsson wrote:
> What you are saying makes sense. Thanks for your advice!
>
>
>
> However, I do believe there are times when vacuuming would be beneficial. For 
> instance, if a database for software X is detected to have 90% unused space 
> for a couple of weeks, then why bloat the harddrive?
>

If there is lots of free space on the drive, why work at giving it a 
little bit more?

In any event, knowing the database and hard disk usage patterns will 
lead to much, much, much better criteria for vacuuming than any general 
rule of thumb.

Optimizing plentiful resources is non-optimal.


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

Re: [sqlite] Import all tables from one db file to another

[Peng Yu]

On Tue, Jul 6, 2010 at 4:45 PM, Simon Slavin  wrote:
>
> On 6 Jul 2010, at 10:42pm, Peng Yu wrote:
>
>> I could create tables in database B to select all the data from
>> database A and insert them in B. I don't find any simpler way to copy
>> all tables from A to B. But I may miss something.
>>
>> I'm wondering if there is a very simple single command that can just
>> copy all the tables from database A to database B. Could you please
>> let me know?
>
> If you are not worried about other users who may be using the database at the 
> same time, then you can simply use file commands to copy the database file as 
> a normal file on disk.

I've thought about copying db files. But the problem is that the
destination db might already have some tables in it and I don't want
to override them.

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

Re: [sqlite] Numbers as CHARs.

[Simon Slavin]

On 6 Jul 2010, at 11:10pm, Ted Rolle, Jr. wrote:

> The receiving field is defined as CHAR; [snip]

SQLite has no such type.  Define the fields as TEXT instead:



Then try 'UPDATE UPCs SET UPC=043000205563;' and see what you get.

> Last question: is this an example of SQLite's "typelessness"?

SQLite has types.  It just doesn't require every value in the same column to be 
of the same type.

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

Re: [sqlite] Numbers as CHARs.

[P Kishor]

On Tue, Jul 6, 2010 at 5:10 PM, Ted Rolle, Jr.  wrote:
> I, (or more to the point, SQLite) can't seem to retain leading zeros
> on numbers.
>
> The receiving field is defined as CHAR;
> I'm using the SQLite Manager in Firefox.
> I've also tried sqlite3 from the command line.
> Here's a typical (and minimal) statement:
> UPDATE UPCs SET UPC=043000205563;
> UPDATE UPCs SET UPC=CAST(043000205562 AS CHAR) WHERE rowid=93; didn't
> work either.
>
> SELECT TYPEOF(UPC) FROM UPCs WHERE rowid=93; returns 'text'.
>
> I'm reluctant to put quotes (single or double) around the values as
> they are retained in the field.
>

I have no idea why you would say that. It works just fine.

sqlite> CREATE TABLE UPCs (UPC TEXT);
sqlite> INSERT INTO UPCs VALUES ('043000205563');
sqlite> SELECT * FROM UPCs;
UPC

043000205563
sqlite>



> On other text/numeric fields I've added a textual value; that seems to
> "fix" the problem.  But not really.
>
> Last question: is this an example of SQLite's "typelessness"?
>
> Ted
> --
> __
> 3.14159265358979323846264338327950      Let the spirit of pi
> 2884197169399375105820974944592307   spread all over the world!
> 8164062862089986280348253421170679 http://pi314.at  PI VOBISCUM!
> ==
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Puneet Kishor http://www.punkish.org
Carbon Model http://carbonmodel.org
Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
Nelson Institute, UW-Madison http://www.nelson.wisc.edu
---
Assertions are politics; backing up assertions with evidence is science
===
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Reg: In Memory Database Using SQLite

[Jean-Christophe Deschamps]

>I'm aware that SQLite offers the functionality of in-memory databases, 
>but I
>don't know how to read the data and send it over the wire to the server or
>how to push the data into the in-memory database of the server.

The backup API included in SQLite offers the facility to backup/restore 
(in fact = copy) disk-based or memory databases to/from disk or 
memory.  So you can launch a backup operation all in one shunk to 
create a memory DB, work on it and then backup it back to the 
server.  I use this everyday and it works like a charm.



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

[sqlite] Numbers as CHARs.

[Ted Rolle, Jr.]

I, (or more to the point, SQLite) can't seem to retain leading zeros
on numbers.

The receiving field is defined as CHAR;
I'm using the SQLite Manager in Firefox.
I've also tried sqlite3 from the command line.
Here's a typical (and minimal) statement:
UPDATE UPCs SET UPC=043000205563;
UPDATE UPCs SET UPC=CAST(043000205562 AS CHAR) WHERE rowid=93; didn't
work either.

SELECT TYPEOF(UPC) FROM UPCs WHERE rowid=93; returns 'text'.

I'm reluctant to put quotes (single or double) around the values as
they are retained in the field.

On other text/numeric fields I've added a textual value; that seems to
"fix" the problem.  But not really.

Last question: is this an example of SQLite's "typelessness"?

Ted
-- 
__
3.14159265358979323846264338327950  Let the spirit of pi
2884197169399375105820974944592307   spread all over the world!
8164062862089986280348253421170679 http://pi314.at  PI VOBISCUM!
==
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to determine when to VACUUM?

[Matthew L. Creech]

On Tue, Jul 6, 2010 at 5:58 PM, Kristoffer Danielsson
 wrote:
>
> However, I do believe there are times when vacuuming would be beneficial. For 
> instance, if a database for software X is detected to have 90% unused space 
> for a couple of weeks, then why bloat the harddrive? (I don't know how to do 
> that though :P)
>
> In my opinion, the user should always have the option to vacuum the database. 
> My goal is to let software X have some logic to give the user a hint when 
> this action would be appropriate.
>

It can also be beneficial for performance reasons, although you'd
obviously have to do testing to see whether the very-CPU-intensive
VACUUM operation is worth the gain in subsequent performance.  There
are some cases where it clearly is.

As for your original question, I would think that you'd want to use
some ratio of 'PRAGMA freelist_count;' to 'PRAGMA page_count;' to make
your determination:

http://sqlite.org/pragma.html

Once the number of unused pages gets large enough compared to the
total database size, it might be an appropriate time to VACUUM.

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

Re: [sqlite] EXTERNAL: setup sqlite in vc++

[smengl90]

I found out that including the header file alone is not enough. I need to
link the sqlite lib to my project. But how can can I get the lib? 



smengl90 wrote:
> 
> Thanks for prompt replies. I did as you advised, and included the .h file
> as a source file and copied your code. But when I compiled it, I got these
> linking errors:
> 
> 1>Linking...
> 1>TestSqlite.obj : error LNK2019: unresolved external symbol
> _sqlite3_close referenced in function _main
> 1>TestSqlite.obj : error LNK2019: unresolved external symbol _sqlite3_free
> referenced in function _main
> 1>TestSqlite.obj : error LNK2019: unresolved external symbol _sqlite3_exec
> referenced in function _main
> 1>TestSqlite.obj : error LNK2019: unresolved external symbol _sqlite3_open
> referenced in function _main
> 1>C:\Documents and Settings\My Documents\Visual Studio
> 2008\Projects\TestSqlite\Debug\TestSqlite.exe : fatal error LNK1120: 4
> unresolved externals
> 
> Any ideas what I did wrong?
> 
> Thanks
> 
> 
> Black, Michael (IS) wrote:
>> 
>> You don't need C++ wrappers...just code in C.  There's lots more C
>> examples out there than C++.
>>  
>> Easiest way for me to integrate is to download the amalgamation and
>> include it as  a source file in your project.
>>  
>> The amalgamzation is at the top of the download page:
>>  
>> http://www.sqlite.org/download.html
>>  
>> Simple program for you to compile as C++ which I wrote from a question
>> somebody had on the list here.
>>  
>> #include 
>> #include 
>> #include "sqlite3.h"
>> int main()
>> {
>> sqlite3 *db;
>> char *errmsg=NULL;
>> int rc;
>> int i;
>> sqlite3_open("test.db",&db);
>> rc=sqlite3_exec(db, "CREATE TABLE log (id_client varchar, utc
>> number)",NULL,NULL,&errmsg);
>> if (rc != SQLITE_OK) {
>> puts(errmsg);
>> sqlite3_free(errmsg);
>> }
>> sqlite3_exec(db,"BEGIN",NULL,NULL,&errmsg);
>> for(i=0;i<10;i++) {
>>  int j=i*10;
>>  char sql[4096];
>>  sprintf(sql,"INSERT INTO log VALUES ('%d',%d)",i,j);
>>  rc=sqlite3_exec(db, sql,NULL,NULL,&errmsg);
>>  if (rc != SQLITE_OK) {
>>  std::cout << errmsg << std::endl;
>>  sqlite3_free(errmsg);
>>  exit(-1);
>>  }
>> }
>> sqlite3_exec(db,"COMMIT",NULL,NULL,&errmsg);
>> sqlite3_close(db);
>> return 0;
>> }
>> 
>>  
>> Michael D. Black
>> Senior Scientist
>> Northrop Grumman Mission Systems
>>  
>> 
>> 
>> 
>> From: sqlite-users-boun...@sqlite.org on behalf of smengl90
>> Sent: Tue 7/6/2010 2:33 PM
>> To: sqlite-users@sqlite.org
>> Subject: EXTERNAL:[sqlite] setup sqlite in vc++
>> 
>> 
>> 
>> 
>> Hi guys,
>> 
>> I am trying to setup sqlite to be used with VC++ 2008. Can someone show
>> me
>> where I can find instructions on how to set it up? and do I need a c++
>> wrapper to code in C++? If yes, can someone also show how to setup the
>> wrapper?
>> 
>> Thanks
>> --
>> View this message in context:
>> http://old.nabble.com/setup-sqlite-in-vc%2B%2B-tp29086729p29086729.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
>> 
>> 
>> 
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>> 
>> 
> 
> 

-- 
View this message in context: 
http://old.nabble.com/setup-sqlite-in-vc%2B%2B-tp29086729p29090562.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] How to determine when to VACUUM?

[Kristoffer Danielsson]

What you are saying makes sense. Thanks for your advice!

 

However, I do believe there are times when vacuuming would be beneficial. For 
instance, if a database for software X is detected to have 90% unused space for 
a couple of weeks, then why bloat the harddrive? (I don't know how to do that 
though :P)

 

In my opinion, the user should always have the option to vacuum the database. 
My goal is to let software X have some logic to give the user a hint when this 
action would be appropriate.


 
> From: slav...@bigfraud.org
> Date: Tue, 6 Jul 2010 22:52:11 +0100
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] How to determine when to VACUUM?
> 
> 
> On 6 Jul 2010, at 10:45pm, Kristoffer Danielsson wrote:
> 
> > Q: How do I programmatically (through sqlite-APIs?) determine if it's time 
> > to VACUUM a database?
> 
> It's never time to VACUUM a database. The VACUUM command is useful only if 
> you have want to recover unused space from the database file. So if your 
> database file once took up 5 Meg, and you deleted a lot of data from it and 
> it now takes up only 2 Meg, you could recover 3 Megabytes of disk space. But 
> how useful is that 3 Megabytes of space to you ? Are you going to use it for 
> something really valuable ? And how long will it be before you get 3 
> Megabytes more data which will fill it up again ?
> 
> If you're trying to get the database in shape to make copies, e.g. to burn it 
> on a DVD or send it to customers, or put it on a device with limited space, 
> then there might be some reason to use VACUUM. If not, then it's just a waste 
> of resources.
> 
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
  
_
Messenger i mobilen på 5 sekunder!
http://new.windowslivemobile.msn.com/se-SE/Default.aspx
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] WAL journal size grow unlimited

[Alexey Pechnikov]

There is only single process:

$ fuser test.db
test.db:  3326
$ ps aux|grep 3326|grep -v grep
veter 3326 55.9  3.3 309892 278832 pts/1   Dl+  01:19  17:11
/usr/bin/tclsh8.5 ./test.tcl

And I see now:
$ ls -lh
итого 3,9G
-rw-r--r-- 1 veter veter 3,1G Июл  7 01:50 test.db
-rw-r--r-- 1 veter veter 896K Июл  7 01:51 test.db-shm
-rw-r--r-- 1 veter veter 880M Июл  7 01:51 test.db-wal
-rwxr-xr-x 1 veter veter 3,0K Июл  7 01:19 test.tcl


My test script
http://mobigroup.ru/files/test.tcl

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

Re: [sqlite] How to determine when to VACUUM?

[Simon Slavin]

On 6 Jul 2010, at 10:45pm, Kristoffer Danielsson wrote:

> Q: How do I programmatically (through sqlite-APIs?) determine if it's time to 
> VACUUM a database?

It's never time to VACUUM a database.  The VACUUM command is useful only if you 
have want to recover unused space from the database file.  So if your database 
file once took up 5 Meg, and you deleted a lot of data from it and it now takes 
up only 2 Meg, you could recover 3 Megabytes of disk space.  But how useful is 
that 3 Megabytes of space to you ?  Are you going to use it for something 
really valuable ?  And how long will it be before you get 3 Megabytes more data 
which will fill it up again ?

If you're trying to get the database in shape to make copies, e.g. to burn it 
on a DVD or send it to customers, or put it on a device with limited space, 
then there might be some reason to use VACUUM.  If not, then it's just a waste 
of resources.

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

Re: [sqlite] Import all tables from one db file to another

[Simon Slavin]

On 6 Jul 2010, at 10:42pm, Peng Yu wrote:

> I could create tables in database B to select all the data from
> database A and insert them in B. I don't find any simpler way to copy
> all tables from A to B. But I may miss something.
> 
> I'm wondering if there is a very simple single command that can just
> copy all the tables from database A to database B. Could you please
> let me know?

If you are not worried about other users who may be using the database at the 
same time, then you can simply use file commands to copy the database file as a 
normal file on disk.

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

[sqlite] How to determine when to VACUUM?

[Kristoffer Danielsson]

I've been reading the documentation. I've been googling and thinking.

 

Still, I can't figure out the best way to determine when to run the 
VACUUM-command. Note that I do NOT want to enable "auto vacuum".

I do remember reading something about calculating empty space, used pages etc 
etc. Still, no perfect answer.

 

Q: How do I programmatically (through sqlite-APIs?) determine if it's time to 
VACUUM a database? In general, what is the best method here?

 

Thanks!

/Chris
  
_
Håll skräpposten borta med nya Hotmail. Klicka här!
http://explore.live.com/windows-live-hotmail
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Import all tables from one db file to another

[Peng Yu]

I could create tables in database B to select all the data from
database A and insert them in B. I don't find any simpler way to copy
all tables from A to B. But I may miss something.

I'm wondering if there is a very simple single command that can just
copy all the tables from database A to database B. Could you please
let me know?

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

Re: [sqlite] WAL journal size grow unlimited

[Richard Hipp]

A busy read transaction holds the WAL open and prevent is from being reset.
If you find that your WAL is growing without bound, this suggests that you
have an unfinalized query hanging around someplace, perhaps in a separate
hung process.

2010/7/6 Alexey Pechnikov 

> $ ls -lh
> -rw-r--r-- 1 veter veter 700M Июл  7 00:55 test.db
> -rw-r--r-- 1 veter veter 192K Июл  7 00:55 test.db-shm
> -rw-r--r-- 1 veter veter 186M Июл  7 00:55 test.db-wal
> -rwxr-xr-x 1 veter veter 3,0K Июл  7 00:47 test.tcl
> $ sqlite3 test.db 'pragma page_size'
> 8192
> $ sqlite3 test.db 'PRAGMA wal_autocheckpoint'
> 1000
>
>
> --
> 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
>



-- 
-
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] How to show all the commands that are executed?

[Peng Yu]

Hi,

It seems that .echo on shows certain commands but not all. For
example, 'create table' command is not shown. Is there a command to
show all the commands that are executed?


$ cat ./main.sql
#!/usr/bin/env bash

rm -f main.db
sqlite3 main.db 

Re: [sqlite] EXTERNAL: setup sqlite in vc++

[smengl90]

Thanks for prompt replies. I did as you advised, and included the .h file as
a source file and copied your code. But when I compiled it, I got these
linking errors:

1>Linking...
1>TestSqlite.obj : error LNK2019: unresolved external symbol _sqlite3_close
referenced in function _main
1>TestSqlite.obj : error LNK2019: unresolved external symbol _sqlite3_free
referenced in function _main
1>TestSqlite.obj : error LNK2019: unresolved external symbol _sqlite3_exec
referenced in function _main
1>TestSqlite.obj : error LNK2019: unresolved external symbol _sqlite3_open
referenced in function _main
1>C:\Documents and Settings\My Documents\Visual Studio
2008\Projects\TestSqlite\Debug\TestSqlite.exe : fatal error LNK1120: 4
unresolved externals

Any ideas what I did wrong?

Thanks


Black, Michael (IS) wrote:
> 
> You don't need C++ wrappers...just code in C.  There's lots more C
> examples out there than C++.
>  
> Easiest way for me to integrate is to download the amalgamation and
> include it as  a source file in your project.
>  
> The amalgamzation is at the top of the download page:
>  
> http://www.sqlite.org/download.html
>  
> Simple program for you to compile as C++ which I wrote from a question
> somebody had on the list here.
>  
> #include 
> #include 
> #include "sqlite3.h"
> int main()
> {
> sqlite3 *db;
> char *errmsg=NULL;
> int rc;
> int i;
> sqlite3_open("test.db",&db);
> rc=sqlite3_exec(db, "CREATE TABLE log (id_client varchar, utc
> number)",NULL,NULL,&errmsg);
> if (rc != SQLITE_OK) {
> puts(errmsg);
> sqlite3_free(errmsg);
> }
> sqlite3_exec(db,"BEGIN",NULL,NULL,&errmsg);
> for(i=0;i<10;i++) {
>  int j=i*10;
>  char sql[4096];
>  sprintf(sql,"INSERT INTO log VALUES ('%d',%d)",i,j);
>  rc=sqlite3_exec(db, sql,NULL,NULL,&errmsg);
>  if (rc != SQLITE_OK) {
>  std::cout << errmsg << std::endl;
>  sqlite3_free(errmsg);
>  exit(-1);
>  }
> }
> sqlite3_exec(db,"COMMIT",NULL,NULL,&errmsg);
> sqlite3_close(db);
> return 0;
> }
> 
>  
> Michael D. Black
> Senior Scientist
> Northrop Grumman Mission Systems
>  
> 
> 
> 
> From: sqlite-users-boun...@sqlite.org on behalf of smengl90
> Sent: Tue 7/6/2010 2:33 PM
> To: sqlite-users@sqlite.org
> Subject: EXTERNAL:[sqlite] setup sqlite in vc++
> 
> 
> 
> 
> Hi guys,
> 
> I am trying to setup sqlite to be used with VC++ 2008. Can someone show me
> where I can find instructions on how to set it up? and do I need a c++
> wrapper to code in C++? If yes, can someone also show how to setup the
> wrapper?
> 
> Thanks
> --
> View this message in context:
> http://old.nabble.com/setup-sqlite-in-vc%2B%2B-tp29086729p29086729.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
> 
> 
> 
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 
> 

-- 
View this message in context: 
http://old.nabble.com/setup-sqlite-in-vc%2B%2B-tp29086729p29090109.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

[sqlite] WAL journal size grow unlimited

[Alexey Pechnikov]

$ ls -lh
-rw-r--r-- 1 veter veter 700M Июл  7 00:55 test.db
-rw-r--r-- 1 veter veter 192K Июл  7 00:55 test.db-shm
-rw-r--r-- 1 veter veter 186M Июл  7 00:55 test.db-wal
-rwxr-xr-x 1 veter veter 3,0K Июл  7 00:47 test.tcl
$ sqlite3 test.db 'pragma page_size'
8192
$ sqlite3 test.db 'PRAGMA wal_autocheckpoint'
1000


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

Re: [sqlite] setup sqlite in vc++

[Sam Carleton]

On Tue, Jul 6, 2010 at 3:33 PM, smengl90 <
fixed-term.seak.meng...@us.bosch.com> wrote:

>
> Hi guys,
>
> I am trying to setup sqlite to be used with VC++ 2008. Can someone show me
> where I can find instructions on how to set it up? and do I need a c++
> wrapper to code in C++? If yes, can someone also show how to setup the
> wrapper?
>

What exactly do you mean "setup sqlite"? It is simply a library that you
link into your C++ program, make the C calls and you are in business. If you
want to wrap it, there are lots and lots of different C++ wrappers, but
being one that has used both, there is a lot more power in using the
straight C interface, and it is pretty easy too.  This coming from a dude
that hated databases before working with sqlite and now cannot life without
them!

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

Re: [sqlite] EXTERNAL: setup sqlite in vc++

[Black, Michael (IS)]

You don't need C++ wrappers...just code in C.  There's lots more C examples out 
there than C++.
 
Easiest way for me to integrate is to download the amalgamation and include it 
as  a source file in your project.
 
The amalgamzation is at the top of the download page:
 
http://www.sqlite.org/download.html
 
Simple program for you to compile as C++ which I wrote from a question somebody 
had on the list here.
 
#include 
#include 
#include "sqlite3.h"
int main()
{
sqlite3 *db;
char *errmsg=NULL;
int rc;
int i;
sqlite3_open("test.db",&db);
rc=sqlite3_exec(db, "CREATE TABLE log (id_client varchar, utc 
number)",NULL,NULL,&errmsg);
if (rc != SQLITE_OK) {
puts(errmsg);
sqlite3_free(errmsg);
}
sqlite3_exec(db,"BEGIN",NULL,NULL,&errmsg);
for(i=0;i<10;i++) {
 int j=i*10;
 char sql[4096];
 sprintf(sql,"INSERT INTO log VALUES ('%d',%d)",i,j);
 rc=sqlite3_exec(db, sql,NULL,NULL,&errmsg);
 if (rc != SQLITE_OK) {
 std::cout << errmsg << std::endl;
 sqlite3_free(errmsg);
 exit(-1);
 }
}
sqlite3_exec(db,"COMMIT",NULL,NULL,&errmsg);
sqlite3_close(db);
return 0;
}

 
Michael D. Black
Senior Scientist
Northrop Grumman Mission Systems
 



From: sqlite-users-boun...@sqlite.org on behalf of smengl90
Sent: Tue 7/6/2010 2:33 PM
To: sqlite-users@sqlite.org
Subject: EXTERNAL:[sqlite] setup sqlite in vc++




Hi guys,

I am trying to setup sqlite to be used with VC++ 2008. Can someone show me
where I can find instructions on how to set it up? and do I need a c++
wrapper to code in C++? If yes, can someone also show how to setup the
wrapper?

Thanks
--
View this message in context: 
http://old.nabble.com/setup-sqlite-in-vc%2B%2B-tp29086729p29086729.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


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

[sqlite] setup sqlite in vc++

[smengl90]

Hi guys,

I am trying to setup sqlite to be used with VC++ 2008. Can someone show me
where I can find instructions on how to set it up? and do I need a c++
wrapper to code in C++? If yes, can someone also show how to setup the
wrapper?

Thanks
-- 
View this message in context: 
http://old.nabble.com/setup-sqlite-in-vc%2B%2B-tp29086729p29086729.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] WAL - Performance/fsync

[Martin Knafve]

>> You're right - what I meant was "explicit transaction". I know that you
>> can speed up things by placing everything in a large transaction, but in
>> my software these kinds of bulk INSERT operations are very rare. (In my
>> case, they would only be made during a BACKUP/RESTORE process).
>
> I think you're going to have to give us some figures.  What kind of speed
> are you thinking of as 'low', and are you using a standard Windows
> platform ?
>

A few minutes ago I re-run my tests using todays 3.7-snapshot and WAL
seems to work fine for me now. When I tested it on another snapshot
yesterday I couldn't get it to work. Maybe I made a mistake, or I happened
to use a version which it wasn't working in.

But to answer your question. To get an idea of the performance, I'm
executing "insert into mytable values (10)" 3000 times. Of course, this
doesn't represent what my application is normally doing, I'm merely trying
to understand how fast SQLite is. My application is heavy on INSERT's
though.

- No explicit transactions used: Running the statements takes ~11 seconds.
- Running in 1000 explicit transactions, 3 inserts in each. Takes ~4 seconds
- Running it with WAL enabled takes ~0.2 seconds

The 11 seconds is too slow for my needs, 0.2 seconds should be fast
enough. I'm currently using SQL Compact Edition and I'm guessing the
WAL-performance makes SQLite faster than SQL Compact (haven't measured
that in a long time thuogh). The SQL Compact team is releasing a new beta
during this week so I guess I got some more testing to do before the
weekend.

Kind regards,
Martin Knafve



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

Re: [sqlite] EXTERNAL: Reg: In Memory Database Using SQLite

[Black, Michael (IS)]

When you say "send it" do you mean you can mount the server file system?
 
If so, just attach a database table on the server and copy your database over 
to it.  The server can then copy it into memory.  You just need some sort of 
notification method (like a table with a boolean) to show who has control.
 
http://sqlite.phxsoftware.com/forums/p/285/285.aspx
 
 
 
Michael D. Black
Senior Scientist
Northrop Grumman Mission Systems
 



From: sqlite-users-boun...@sqlite.org on behalf of Subhadeep Ghosh
Sent: Tue 7/6/2010 10:27 AM
To: sqlite-users@sqlite.org
Subject: EXTERNAL:[sqlite] Reg: In Memory Database Using SQLite



Hello People,

I was creating a server and a corresponding client application, in which the
I'm using SQLite for both the client and the server. The tricky part in this
setup is that the client is not required to store the database file with
itself, but it fetches it from the server, does whatever it needs to and
sends it back to the server (and the server stores it).

I'm aware that SQLite offers the functionality of in-memory databases, but I
don't know how to read the data and send it over the wire to the server or
how to push the data into the in-memory database of the server.

It would be great if someone could help me on this one.

Thank you and regards,
Subhadeep Ghosh.
___
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] Reg: In Memory Database Using SQLite

[Subhadeep Ghosh]

Hello People,

I was creating a server and a corresponding client application, in which the
I'm using SQLite for both the client and the server. The tricky part in this
setup is that the client is not required to store the database file with
itself, but it fetches it from the server, does whatever it needs to and
sends it back to the server (and the server stores it).

I'm aware that SQLite offers the functionality of in-memory databases, but I
don't know how to read the data and send it over the wire to the server or
how to push the data into the in-memory database of the server.

It would be great if someone could help me on this one.

Thank you and regards,
Subhadeep Ghosh.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] WAL - Performance/fsync

[Simon Slavin]

On 6 Jul 2010, at 6:46pm, Martin Knafve wrote:

> You're right - what I meant was "explicit transaction". I know that you
> can speed up things by placing everything in a large transaction, but in
> my software these kinds of bulk INSERT operations are very rare. (In my
> case, they would only be made during a BACKUP/RESTORE process).

I think you're going to have to give us some figures.  What kind of speed are 
you thinking of as 'low', and are you using a standard Windows platform ?

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

Re: [sqlite] WAL - Performance/fsync

[Martin Knafve]

>
> On 6 Jul 2010, at 6:05pm, Martin Knafve wrote:
>
>> On my PC, INSERT's with fsync disabled
>> is ~50 times faster than when fsync is used, so I would expect enabling
>> WAL to give me at least a 500% speedup. Of course, my assumptions are
>> very
>> rough and I understand that I'm likely far off.
>>
>> Am I missing something here? What kind of performance improvments should
>> I
>> expect on INSERT statements not taking part in a transaction when using
>> the WAL?
>
> All INSERT statements take part in a transaction -- SQLite cannot make
> changes which aren't part of a transaction.  If you haven't explicitly
> declared a transaction before the INSERT statement, SQLite creates a
> transaction especially for that one INSERT statement.  So to dramatically
> speed up a bunch of INSERT statements, put BEGIN TRANSACTION ... COMMIT
> around the whole bunch.
>

You're right - what I meant was "explicit transaction". I know that you
can speed up things by placing everything in a large transaction, but in
my software these kinds of bulk INSERT operations are very rare. (In my
case, they would only be made during a BACKUP/RESTORE process).

Kind regards
Martin


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

Re: [sqlite] WAL - Performance/fsync

[Martin Knafve]

> On Tue, Jul 6, 2010 at 1:05 PM, Martin Knafve
> wrote:
>
>> Hi,
>>
>> I'm considering using SQLite as backend for my software. What's stopping
>> me is the low insert-performance. If I disable fsync/FlushFileBuffers,
>> performance is good but I can't do with the risk of database corruption.
>>
>> I don't know the details of the WAL-implementation in 3.7, but if
>> fsync/FlushFileBuffers are only required when data is moved from the
>> write-ahead log to the database file, I would expect performance to be a
>> lot faster. Reading the documentation about WAL, I get the impression
>> that
>> I can accomplish this using the following pragmas:
>>
>> sqlite3_exec(db, "PRAGMA journal_mode=WAL", NULL, 0, &db_err);
>> sqlite3_exec(db, "PRAGMA synchronous=NORMAL", NULL, 0, &db_err);
>>
>
> If you do the above, no fsyncs happen except when doing a checkpoint, and
> a
> checkpoint should only happen after about each megabyte of inserting.
>
> We don't have any git support here, so I don't know where you got your
> snapshot, but it wasn't an official SQLite.  Prerelease snapshots for
> SQLite
> 3.7.0 are available here:
>
>  http://www.sqlite.org/draft/download.html
>

Thanks for the info, I'll have the official snapshot a go.

Kind regards,
Martin


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

Re: [sqlite] WAL - Performance/fsync

[Richard Hipp]

On Tue, Jul 6, 2010 at 1:05 PM, Martin Knafve wrote:

> Hi,
>
> I'm considering using SQLite as backend for my software. What's stopping
> me is the low insert-performance. If I disable fsync/FlushFileBuffers,
> performance is good but I can't do with the risk of database corruption.
>
> I don't know the details of the WAL-implementation in 3.7, but if
> fsync/FlushFileBuffers are only required when data is moved from the
> write-ahead log to the database file, I would expect performance to be a
> lot faster. Reading the documentation about WAL, I get the impression that
> I can accomplish this using the following pragmas:
>
> sqlite3_exec(db, "PRAGMA journal_mode=WAL", NULL, 0, &db_err);
> sqlite3_exec(db, "PRAGMA synchronous=NORMAL", NULL, 0, &db_err);
>

If you do the above, no fsyncs happen except when doing a checkpoint, and a
checkpoint should only happen after about each megabyte of inserting.

We don't have any git support here, so I don't know where you got your
snapshot, but it wasn't an official SQLite.  Prerelease snapshots for SQLite
3.7.0 are available here:

 http://www.sqlite.org/draft/download.html

Additional information on WAL:

http://www.sqlite.org/draft/wal.html


>
> I picked a snapshot from git and tried it. I'm unable to see any real
> performance improvement by doing this though. Haven't looked very
> carefully and only run the tests a few times but I don't notice any
> difference larger than 10% or so. On my PC, INSERT's with fsync disabled
> is ~50 times faster than when fsync is used, so I would expect enabling
> WAL to give me at least a 500% speedup. Of course, my assumptions are very
> rough and I understand that I'm likely far off.
>
> Am I missing something here? What kind of performance improvments should I
> expect on INSERT statements not taking part in a transaction when using
> the WAL?
>
> Kind regards,
>
> Martin Knafve
>
>
>
>
>
> ___
> 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] WAL - Performance/fsync

[Simon Slavin]

On 6 Jul 2010, at 6:05pm, Martin Knafve wrote:

> On my PC, INSERT's with fsync disabled
> is ~50 times faster than when fsync is used, so I would expect enabling
> WAL to give me at least a 500% speedup. Of course, my assumptions are very
> rough and I understand that I'm likely far off.
> 
> Am I missing something here? What kind of performance improvments should I
> expect on INSERT statements not taking part in a transaction when using
> the WAL?

All INSERT statements take part in a transaction -- SQLite cannot make changes 
which aren't part of a transaction.  If you haven't explicitly declared a 
transaction before the INSERT statement, SQLite creates a transaction 
especially for that one INSERT statement.  So to dramatically speed up a bunch 
of INSERT statements, put BEGIN TRANSACTION ... COMMIT around the whole bunch.

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

Re: [sqlite] EXTERNAL: WAL - Performance/fsync

[Black, Michael (IS)]

You'll find a  LARGE performance improvement if you wrap your inserts inside a 
BEGIN/COMMIT and only commit every 1000 or so inserts or such.
 
BEGIN
for all records
   INSERT
   count++
   if ((count % 1000)==0) COMMIT;BEGIN  // commit and begin again
end
COMMIT
 
 
Michael D. Black
Senior Scientist
Northrop Grumman Mission Systems
 



From: sqlite-users-boun...@sqlite.org on behalf of Martin Knafve
Sent: Tue 7/6/2010 12:05 PM
To: sqlite-users@sqlite.org
Subject: EXTERNAL:[sqlite] WAL - Performance/fsync



Hi,

I'm considering using SQLite as backend for my software. What's stopping
me is the low insert-performance. If I disable fsync/FlushFileBuffers,
performance is good but I can't do with the risk of database corruption.

I don't know the details of the WAL-implementation in 3.7, but if
fsync/FlushFileBuffers are only required when data is moved from the
write-ahead log to the database file, I would expect performance to be a
lot faster. Reading the documentation about WAL, I get the impression that
I can accomplish this using the following pragmas:

sqlite3_exec(db, "PRAGMA journal_mode=WAL", NULL, 0, &db_err);
sqlite3_exec(db, "PRAGMA synchronous=NORMAL", NULL, 0, &db_err);

I picked a snapshot from git and tried it. I'm unable to see any real
performance improvement by doing this though. Haven't looked very
carefully and only run the tests a few times but I don't notice any
difference larger than 10% or so. On my PC, INSERT's with fsync disabled
is ~50 times faster than when fsync is used, so I would expect enabling
WAL to give me at least a 500% speedup. Of course, my assumptions are very
rough and I understand that I'm likely far off.

Am I missing something here? What kind of performance improvments should I
expect on INSERT statements not taking part in a transaction when using
the WAL?

Kind regards,

Martin Knafve





___
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] WAL - Performance/fsync

[Martin Knafve]

Hi,

I'm considering using SQLite as backend for my software. What's stopping
me is the low insert-performance. If I disable fsync/FlushFileBuffers,
performance is good but I can't do with the risk of database corruption.

I don't know the details of the WAL-implementation in 3.7, but if
fsync/FlushFileBuffers are only required when data is moved from the
write-ahead log to the database file, I would expect performance to be a
lot faster. Reading the documentation about WAL, I get the impression that
I can accomplish this using the following pragmas:

sqlite3_exec(db, "PRAGMA journal_mode=WAL", NULL, 0, &db_err);
sqlite3_exec(db, "PRAGMA synchronous=NORMAL", NULL, 0, &db_err);

I picked a snapshot from git and tried it. I'm unable to see any real
performance improvement by doing this though. Haven't looked very
carefully and only run the tests a few times but I don't notice any
difference larger than 10% or so. On my PC, INSERT's with fsync disabled
is ~50 times faster than when fsync is used, so I would expect enabling
WAL to give me at least a 500% speedup. Of course, my assumptions are very
rough and I understand that I'm likely far off.

Am I missing something here? What kind of performance improvments should I
expect on INSERT statements not taking part in a transaction when using
the WAL?

Kind regards,

Martin Knafve





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

[sqlite] Slow FTS3 query for time strings (like to "19:10:59")

[Alexey Pechnikov]

sqlite> .schema role
CREATE VIRTUAL TABLE role USING fts3(uuid TEXT, uid INTEGER, dt TEXT, ts
INTEGER, fields, tasks, TOKENIZE icu ru_RU);

Fast queries:

sqlite> select count(*) from role where role match '1278429059';
count(*)
471
CPU Time: user 0.00 sys 0.00
sqlite> select count(*) from role where role match '19 10 59';
count(*)
476
CPU Time: user 0.584037 sys 0.00

Slow queries:

sqlite> select count(*) from role where role match '"19:10:59"';
count(*)
471
CPU Time: user 3.500219 sys 0.080005
sqlite> select count(*) from role where role match '19:10:59';
count(*)
476
CPU Time: user 1.888118 sys 0.036002

Why these are too slow and how last queries are interpretated by FTS3? Why
results are different?

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

Re: [sqlite] "use database" in sqlite3?

[Pavel Ivanov]

> Is there a command
> similar to 'use database' (mysql) in sqlite3 so that I can make a
> particular database as default? (I don't find such command, but please
> let me know in case if I miss anything.)

Yes. It is sqlite3_open() in C API. And if you use command line
utility then you should pass the name of database file that you want
to make default in the command line.

You cannot switch default databases while connection is open. In fact
"connection" in SQLite and "connection" in MySQL are completely
different things, so there's nothing inappropriate in such SQLite's
behavior.


Pavel

On Tue, Jul 6, 2010 at 12:00 PM, Peng Yu  wrote:
> Hi,
>
> I can attach a database to the current session. But I have to
> explicitly specify the table name to refer to any tables in it (such
> 'create_index' in 'create_index.sqlite_master'). Is there a command
> similar to 'use database' (mysql) in sqlite3 so that I can make a
> particular database as default? (I don't find such command, but please
> let me know in case if I miss anything.)
>
> sqlite> attach database 'create_index.db' as create_index;
> sqlite> select * from create_index.sqlite_master;
>
> --
> Regards,
> Peng
> ___
> 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] Use of sqlite3_step()

[Andrew Wood]

Whoops thats what comes of writing code when you're feeling under weather :)

Thanks

On 06/07/10 16:43, Mike Shal wrote:
>
> I haven't tried it, but I suspect this is your problem. It looks like
> you're declaring a new 'queryreturn' int, but the while loop is
> checking against the one from the top of the program. You can probably
> just remove the 'int' here.
>
> -Mike
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
> __
> This email has been scanned by the MessageLabs Email Security System.
> For more information please visit http://www.messagelabs.com/email
> __
>

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

Re: [sqlite] "use database" in sqlite3?

[P Kishor]

On Tue, Jul 6, 2010 at 11:00 AM, Peng Yu  wrote:
> Hi,
>
> I can attach a database to the current session. But I have to
> explicitly specify the table name to refer to any tables in it (such
> 'create_index' in 'create_index.sqlite_master'). Is there a command
> similar to 'use database' (mysql) in sqlite3 so that I can make a
> particular database as default? (I don't find such command, but please
> let me know in case if I miss anything.)
>
> sqlite> attach database 'create_index.db' as create_index;
> sqlite> select * from create_index.sqlite_master;
>

As far as I know, there is no such command as 'use '. You
can shorten your db prefix to a single letter, and save typing, if
that bothers you

ATTACH DATABASE 'very_long_name_of_my_external_db_file.sqlite' AS v;
SELECT * FROM v.table;

In my personal view, always fully qualifying your source table and
columns is very good practice as it eliminates any ambiguity.


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



-- 
Puneet Kishor http://www.punkish.org
Carbon Model http://carbonmodel.org
Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
Nelson Institute, UW-Madison http://www.nelson.wisc.edu
---
Assertions are politics; backing up assertions with evidence is science
===
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] "use database" in sqlite3?

[Peng Yu]

Hi,

I can attach a database to the current session. But I have to
explicitly specify the table name to refer to any tables in it (such
'create_index' in 'create_index.sqlite_master'). Is there a command
similar to 'use database' (mysql) in sqlite3 so that I can make a
particular database as default? (I don't find such command, but please
let me know in case if I miss anything.)

sqlite> attach database 'create_index.db' as create_index;
sqlite> select * from create_index.sqlite_master;

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

Re: [sqlite] Use of sqlite3_step()

[Sam Carleton]

I use a bit simpler approach, don't know if it is correct or not, but
it seems to work:

int rc = sqlite3_step(stmt);

while(rc == SQLITE_ROW)
{
/* read the row info */
   rc = sqlite3_step(stmt);
}

if( rc != SQLITE_DONE)
{
  /* handle error */
}
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Use of sqlite3_step()

[Mike Shal]

On 7/6/10, Andrew Wood  wrote:
> Whats the procedure for using sqlite3_step?
>
>  I thought it would be along the lines of the following, but the while
>  loop executes infinately. I would have expected the while loop to run
>  once for each row found?
>
>  Code is as follows:
>
>  int queryreturn = sqlite3_step(preparedstatement);
>
>  if (queryreturn == SQLITE_DONE)
>  {
>  //no rows found
>  return;
>
>  }
>  else if (queryreturn == SQLITE_ROW)
>  {
>  //row found, will process in a sec
>  }
>  else
>  {
>  //error, throw exception
>
>  }
>  //if we get here we have 1 or more rows to process
>
>  while (queryreturn==SQLITE_ROW)
>  {//process row
>  printf("row found\n");
>
>  //advance to next row
>  int queryreturn = sqlite3_step(preparedstatement);
^^^

I haven't tried it, but I suspect this is your problem. It looks like
you're declaring a new 'queryreturn' int, but the while loop is
checking against the one from the top of the program. You can probably
just remove the 'int' here.

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

[sqlite] Use of sqlite3_step()

[Andrew Wood]

Whats the procedure for using sqlite3_step?

I thought it would be along the lines of the following, but the while 
loop executes infinately. I would have expected the while loop to run 
once for each row found?

Code is as follows:

int queryreturn = sqlite3_step(preparedstatement);

if (queryreturn == SQLITE_DONE)
 {
 //no rows found
 return;

 }
 else if (queryreturn == SQLITE_ROW)
 {
 //row found, will process in a sec
 }
 else
 {
 //error, throw exception

 }
 //if we get here we have 1 or more rows to process

 while (queryreturn==SQLITE_ROW)
 {//process row
 printf("row found\n");

 //advance to next row
 int queryreturn = sqlite3_step(preparedstatement);
 }
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Bug: #if SQLITE_HAS_CODEC warning

[Dair Grant]

Hi,

3.6.23.1's amalgamated sqlite.h include a prototype for sqlite3_key() if
SQLITE_HAS_CODEC is defined.

This test is currently done with "#if SQLITE_HAS_CODEC", which triggers a
warning from gcc -Wundef if this macro isn't declared via a precompiled
header/command line flag/etc.

Most of the other macro tests are done with #ifdef, so could this macro be
tested with #ifdef or use 'defined(SQLITE_HAS_CODEC) && SQLITE_HAS_CODEC'
for this test? Thanks.


-dair
___
d...@refnum.com  http://www.refnum.com/


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

[sqlite] Please beta-test SQLite 3.7.0

[D. Richard Hipp]

We have scheduled the release of SQLite version 3.7.0 for Thursday,  
2010-07-15.  That date could yet change, but 2010-07-15 is our target.

SQLite version 3.7.0 will feature the addition of a write-ahead log  
(WAL) capability for transaction control.  See 
http://www.sqlite.org/draft/wal.html 
  for additional information.  WAL should increase performance and  
concurrency in many situations.  Version 3.7.0 also has other changes,  
including a new feature that automatically creates transient indices  
to improve performance for some complex joins, and other performance  
enhancements.

Version 3.7.0 has been long in the making.  We've worked on it for  
months.  We have checked in nearly 600 separate change-sets since  
3.6.23.1.  About 7.5% of the source code in version 3.7.0 is new.

Version 3.7.0 is currently passing all of our extensive internal  
tests.  We have verified it using our 41-point pre-release checklist  
(which takes several days to work through).  And version 3.7.0 is  
currently in use running the www.sqlite.org website (in WAL mode).  We  
have tested the new release about as much as we can.  Additional help  
from you is needed:

Please help us by beta-testing SQLite 3.7.0 in your application!   
Report any problems found on this mailing list.

You can download daily snapshots from http://www.sqlite.org/draft/download.html 
  and view updated documentation at http://www.sqlite.org/draft/index.html

Thanks.

D. Richard Hipp
d...@hwaci.com



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

Re: [sqlite] problem with auto boot

[Simon Slavin]

On 6 Jul 2010, at 1:06pm, Daniel wrote:

> Am 06.07.2010 13:28, schrieb Simon Slavin:
>> 
>> 
>> It is probably trying to access a file in a folder it has no rights to.  
>> Perhaps the root folder of the boot drive.
> 
> That was also my intention, so I copied the whole folder to my data 
> partition.

The default folder for an application is not the folder that application is 
stored in.  When you are a normal user the default folder is probably the 
user's home folder.  But while the computer is booting there is no user, and 
the default folder is probably an important folder with high protection.

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

Re: [sqlite] problem with auto boot

[Daniel]

Am 06.07.2010 13:28, schrieb Simon Slavin:
> On 6 Jul 2010, at 10:36am, Daniel wrote:
>
>
>> so now I used the full path to my database and it works fine. Thanks
>> everyone for your help.
>> To run on auto boot the program needed to have the full reference to the
>> database otherwise it will return an exception.
>>  
> It is probably trying to access a file in a folder it has no rights to.  
> Perhaps the root folder of the boot drive.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>
That was also my intention, so I copied the whole folder to my data 
partition.

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

Re: [sqlite] problem with auto boot

[Simon Slavin]

On 6 Jul 2010, at 10:36am, Daniel wrote:

> so now I used the full path to my database and it works fine. Thanks 
> everyone for your help.
> To run on auto boot the program needed to have the full reference to the 
> database otherwise it will return an exception.

It is probably trying to access a file in a folder it has no rights to.  
Perhaps the root folder of the boot drive.

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

Re: [sqlite] problem with auto boot

[Daniel]

Hi,

so now I used the full path to my database and it works fine. Thanks 
everyone for your help.
To run on auto boot the program needed to have the full reference to the 
database otherwise it will return an exception.

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

Re: [sqlite] problem with auto boot

[Daniel]

Hi All,

I tried a lot but no success. The Error message said following:

/System.TypeInitializationException: Der Typeninitialisierer für 
"Sunbird_info.Event" hat eine Ausnahme verursacht. ---> 
System.Data.SQLite.SQLiteException: Unable to open the database file
bei System.Data.SQLite.SQLite3.Open(String strFilename, 
SQLiteOpenFlagsEnum flags, Int32 maxPoolSize, Boolean usePool)
bei System.Data.SQLite.SQLiteConnection.Open()/

Everytime on auto boot I get the exception. As I start the program by 
myself it runs without any errors. I tried just to open the database and 
get back the 'open state' of my database by using an messagebox. By 
starting normaly it turns back 'state = open' ... on auto boot there is 
again just that exception.

What do you mean with

"what folder
have you specified in Start in:?"


I saved the database file in the same folder like the .exe of my program 
is in. So I just using "Data Source=local.sqlite" without referencing on 
the full path.
I will reference now on the full path an give you an update whats happend.

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