Re: [sqlite] importing data from file with 3 colums to table with 4 columns

[ca44]

Thank you very much Simon. 

That worked very slick. 



Say, is there a way to put all of the SQLite3 commands  I used into a script 
and have SQLite3 execute them in the script sequentially? 



-Chris 


- Original Message - 
From: "Simon Davies"  
To: "General Discussion of SQLite Database"  
Sent: Thursday, July 8, 2010 4:48:20 PM 
Subject: Re: [sqlite] importing data from file with 3 colums to table with 
4columns 

On 9 July 2010 00:07,   wrote: 
> 
> 
> Hello, 
> 
> I have a db tbl with the following schema: 
> 
> 
> 
> _ID integer primary key autoincrement 
> 
> name varchar(40) 
> 
> category varchar(40) 
> 
> recommendation varchar(40) 
> 
> 
> 
> I have a data file I want to import which contains 3 columns worth of data. 
> 
> It looks like this: 
> 
> 
> 
> Barracuda|seafood|No 
> Catfish|seafood|No 
> Caviar|seafood|No 
> Conch|seafood|No 
> Herring(pickled)|seafood|No 
> Lox(smoked salmon)|seafood|No 
> Octopus|seafood|No 
> 
> 
> 
> When I try and import it I get the following error: 
> 
> "line 1: expected 4 columns of data but found 3" 
> 
> 
> 
> Since I obviously don't want to explicitly load data into the _ID column, how 
> do I tell it to put the data from the import file into the 3 remaining 
> columns? 
> 

Create a table (tmp) with three cols to receive the data from your 
file, then use 
INSERT INTO tbl( name, category, recommendation ) SELECT * from tmp; 

> 
> 
> Thank you in advance. 
> 
> -Chris 
> 

Regards, 
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] importing data from file with 3 colums to table with 4 columns

[Simon Davies]

On 9 July 2010 00:07,   wrote:
>
>
> Hello,
>
> I have a db tbl with the following schema:
>
>
>
> _ID integer primary key autoincrement
>
> name varchar(40)
>
> category varchar(40)
>
> recommendation varchar(40)
>
>
>
> I have a data file I want to import which contains 3 columns worth of data.
>
> It looks like this:
>
>
>
> Barracuda|seafood|No
> Catfish|seafood|No
> Caviar|seafood|No
> Conch|seafood|No
> Herring(pickled)|seafood|No
> Lox(smoked salmon)|seafood|No
> Octopus|seafood|No
>
>
>
> When I try and import it I get the following error:
>
> "line 1: expected 4 columns of data but found 3"
>
>
>
> Since I obviously don't want to explicitly load data into the _ID column, how 
> do I tell it to put the data from the import file into the 3 remaining 
> columns?
>

Create a table (tmp) with three cols to receive the data from your
file, then use
INSERT INTO tbl( name, category, recommendation ) SELECT * from tmp;

>
>
> Thank you in advance.
>
> -Chris
>

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

[sqlite] importing data from file with 3 colums to table with 4 columns

[ca44]

Hello, 

I have a db tbl with the following schema: 



_ID integer primary key autoincrement 

name varchar(40) 

category varchar(40) 

recommendation varchar(40) 



I have a data file I want to import which contains 3 columns worth of data. 

It looks like this: 



Barracuda|seafood|No 
Catfish|seafood|No 
Caviar|seafood|No 
Conch|seafood|No 
Herring(pickled)|seafood|No 
Lox(smoked salmon)|seafood|No 
Octopus|seafood|No 



When I try and import it I get the following error: 

"line 1: expected 4 columns of data but found 3" 



Since I obviously don't want to explicitly load data into the _ID column, how 
do I tell it to put the data from the import file into the 3 remaining columns? 



Thank you in advance. 



-Chris 


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

Re: [sqlite] WAL questions

[Richard Hipp]

On Thu, Jul 8, 2010 at 12:45 PM, Richard Hipp  wrote:

>
>
> PRAGMA name.journal_mode=MODE; -- set the mode of database "name" to MODE.
> PRAGMA name.journal_mode;  -- report the mode of database "name".
> PRAGMA journal_mode; -- an alias for "PRAGMA main.journal_mode"
> PRAGMA journal_mode=MODE;  -- set the mode of all currently ATTACHed
> databases.
>

The latest version 3.7.0 pre-release snapshot does journal_mode as described
above.
http://www.sqlite.org/draft/download.html to get a copy for testing.  The
documentation at
http://www.sqlite.org/draft/pragma.html#pragma_journal_modehas been
updated as well.  Please report any problems to this list.  Thanks!



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

[Richard Hipp]

On Thu, Jul 8, 2010 at 12:23 PM, Jay A. Kreibich  wrote:

> > First Proposed Change:
> >
> > (1) and (2) are the same.
> >
> > (3) PRAGMA journal_mode=MODE;  -- set the mode to MODE for all attached
> > databases.  Databases created by subsequent ATTACH use MODE.  Existing
> > databases added by ATTACH use MODE if and only if doing so does not
> require
> > them to change in or out of WAL mode.
>
> > (4) PRAGMA journal_mode;  -- report the most recent setting by (3).  Or
> if
> > (3) has never been used, work the same as "PRAGMA main.journal_mode;"
>
> >
>  Contexts are generally bad.  This is even more true if there might
>  ever be a way to change the compile time default journal mode to
>  anything other than "delete."  Then there is no way to get the default.
>

Agreed.

Since sending the prior email, internal discussion has moved us in the
direction of eliminating the "default journal mode" all together.  This
makes both the code and documentation smaller, which implies that it will
also make things easier to understand.

So probably we'll end up with something like this:

PRAGMA name.journal_mode=MODE; -- set the mode of database "name" to MODE.
PRAGMA name.journal_mode;  -- report the mode of database "name".
PRAGMA journal_mode; -- an alias for "PRAGMA main.journal_mode"
PRAGMA journal_mode=MODE;  -- set the mode of all currently ATTACHed
databases.

When new databases are ATTACH-ed, they come up in either DELETE or WAL mode
(depending on how they were last closed) and must be changed to whatever the
application desires using a new PRAGMA journal_mode call.  No more trying to
guess what the application wants and apply that as a default.

This is ever-so-slightly incompatible with prior SQLite versions, but on the
other hand, we never have promised to maintain PRAGMA compatibility, and if
we are going to change something, it seems like 3.6->3.7 is a good time to
change it.


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

[Jay A. Kreibich]

On Thu, Jul 08, 2010 at 08:06:23PM +0400, Alexey Pechnikov scratched on the 
wall:
> 2010/7/8 Jay A. Kreibich 
> 
> > > It's not helpful for backward compability. How about version downgrade of
> > > the Android or some other mobile OS and as result impossibility to open
> > > any SQLite database?..
> >
> >   That's not backwards compatibility (newer versions working with items
> >  from older environments), that's forwards compatibility (older versions
> >  working new items from a newer environment).
> >
> 
> I did speak about "Backwards Compatibility" chapter from
> http://www.sqlite.org/draft/wal.html

  Which pretty much says "if you turn this on, the file will not work
  with older versions until you turn it off."  What's the issue?

> > It is no different than a new(er) application that uses newer APIs
> > not working on an older version of the OS.
> 
> And how many such changes in POSIX file API?.. I think SQLite API as
> applications file format is similar to POSIX file API.

  Which is why you're using bleeding-edge features in an
  unreleased development version?

  If it is just a file API, use the system libs in their default
  configuration and be done with it.

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

[Jay A. Kreibich]

On Thu, Jul 08, 2010 at 08:23:55AM -0400, Richard Hipp scratched on the wall:
> On Thu, Jul 8, 2010 at 7:10 AM, Richard Hipp  wrote:
> 
> > Though, I will admit, this is confusing, and I was thinking last night
> > about ways we could possibly change it
> 
> Current behavior:
> 
> (1) PRAGMA name.journal_mode=MODE; -- set the mode to MODE for database
> "name".
> 
> (2) PRAGMA name.journal_mode; -- return current journal mode for database
> "name".
> 
> (3) PRAGMA journal_mode=MODE;  -- set the mode to MODE for all attached
> databases and also apply MODE to all databases ATTACH-ed in the future.
> 
> (4) PRAGMA journal_mode; -- report the most recent setting by (3).  Report
> "delete" if there have been no prior occurences of (3).
> 
> First Proposed Change:
> 
> (1) and (2) are the same.
> 
> (3) PRAGMA journal_mode=MODE;  -- set the mode to MODE for all attached
> databases.  Databases created by subsequent ATTACH use MODE.  Existing
> databases added by ATTACH use MODE if and only if doing so does not require
> them to change in or out of WAL mode.

> (4) PRAGMA journal_mode;  -- report the most recent setting by (3).  Or if
> (3) has never been used, work the same as "PRAGMA main.journal_mode;"

  Contexts are generally bad.  This is even more true if there might
  ever be a way to change the compile time default journal mode to
  anything other than "delete."  Then there is no way to get the default.


  There are a few pragmas that use this general approach... where
  getting/setting an unqualified setting changes the default, not
  "main".  It might be better if all unqualified statements refer
  to main, and pragmas like this have a default.journal_mode (get/set)
  or all.journal_mode (set) syntax.


> Second Proposed Change:
> 
> Remove the WAL mode from "PRAGMA journal_mode".  The journal_mode pragma
> only specifies the various rollback journal modes.  Enable the WAL using a
> separate pragma such as  "PRAGMA wal=ON; PRAGMA wal=OFF;"
>
> Thoughts?  Comments? 

  I like this.  I suspect in the future we will have a "PRAGMA
  wal_mode" as well, so this makes a bit more sense.

  But as an extension, does it make even more sense to have "PRAGMA
  wal=[on|off]" or "PRAGMA transaction=[wal|journal]" ? 




> Other suggestions?

  This has nothing to do with WAL, but it might be nice to expose
  the logic that does SQL-type => SQLite-affinity mappings
  (i.e. sqlite3AffinityType()):

int sqlite3_get_affinity( const char *type );

  Takes a string that contains an SQL type.  Returns one of:

  SQLITE_AFF_TEXT, SQLITE_AFF_NONE, SQLITE_AFF_NUMERIC,
  SQLITE_AFF_INTEGER, SQLITE_AFF_REAL.


   -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] LIKE with BLOB

[Igor Tandetnik]

Matthew Jones  wrote:
> From a web search (and abbreviated):
> 
>>> I have BLOBs in my schema and the data will often start with bytes of
>>> 0 value.
>>> I'm having a tough time coming up with the proper SQL syntax to
>>> select all the columns that start with 2 0's (or any zeros).
> 
>> SELECT * FROM mytable WHERE myblob LIKE X'0025';
>> SELECT * FROM mytable WHERE quote(myblob) LIKE 'X''00%';

Another approach:

SELECT * FROM mytable WHERE substr(myblob, 1, 1) = X'00';

> Now I have a column of blob data (always 20 bytes) and I would like to
> do a LIKE select on this column where I have the first 10 bytes but they
> can be any value including, of course, the % character. Is this possible
> or does the arbitrary nature of the data make this infeasible?

SELECT * FROM mytable WHERE substr(myblob, 1, 10) = ?;

-- 
Igor Tandetnik


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

Re: [sqlite] LIKE with BLOB

[Pavel Ivanov]

> If it is possible, how would I define a prepared statement so that I can
> just bind the (10 byte) value into it?

Is it possible to pre-process your 10 bytes and insert e.g. symbol '\'
before any '\', '_' and '%' symbol? After that you can query
SELECT * FROM mytable WHERE myblob LIKE ? ESCAPE '\'


Pavel

On Thu, Jul 8, 2010 at 10:46 AM, Matthew Jones  wrote:
>  From a web search (and abbreviated):
>
>  >>I have BLOBs in my schema and the data will often start with bytes of
>  >>0 value.
>  >>I'm having a tough time coming up with the proper SQL syntax to
>  >> select all the columns that start with 2 0's (or any zeros).
>
>  > SELECT * FROM mytable WHERE myblob LIKE X'0025';
>  > SELECT * FROM mytable WHERE quote(myblob) LIKE 'X''00%';
>
> Now I have a column of blob data (always 20 bytes) and I would like to
> do a LIKE select on this column where I have the first 10 bytes but they
> can be any value including, of course, the % character. Is this possible
> or does the arbitrary nature of the data make this infeasible?
>
> If it is possible, how would I define a prepared statement so that I can
> just bind the (10 byte) value into it?
>
> Thanks
>
> --
> Matthew Jones
> Hewlett-Packard Ltd
> Long Down Avenue
> Stoke Gifford
> Bristol.  BS34 8QZ
> Tel:   +44 (0) 117 312 7490
> Email:    matthew.jo...@hp.com
>
> Hewlett-Packard Limited registered Office: Cain Road, Bracknell, Berks,
> RG12 1HN. Registered No: 690597 England
>
> The contents of this message and any attachments to it are confidential
> and may be legally privileged. If you have received this message in
> error, you should delete it from your system immediately and advise the
> sender.
>
> To any recipient of this message within HP, unless otherwise stated you
> should consider this message and attachments as "HP CONFIDENTIAL".
> ___
> 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] WAL questions

[Alexey Pechnikov]

2010/7/8 Jay A. Kreibich 

> > It's not helpful for backward compability. How about version downgrade of
> > the Android or some other mobile OS and as result impossibility to open
> any
> > SQLite database?..
>
>   That's not backwards compatibility (newer versions working with items
>  from older environments), that's forwards compatibility (older versions
>  working new items from a newer environment).
>

I did speak about "Backwards Compatibility" chapter from
http://www.sqlite.org/draft/wal.html

 It is no different than a new(er) application that uses newer APIs
>  not working on an older version of the OS.


And how many such changes in POSIX file API?.. I think SQLite API as
applications file format is similar to POSIX file API.

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

[sqlite] LIKE with BLOB

[Matthew Jones]

 From a web search (and abbreviated):

 >>I have BLOBs in my schema and the data will often start with bytes of 
 >>0 value.
 >>I'm having a tough time coming up with the proper SQL syntax to
 >> select all the columns that start with 2 0's (or any zeros).

 > SELECT * FROM mytable WHERE myblob LIKE X'0025';
 > SELECT * FROM mytable WHERE quote(myblob) LIKE 'X''00%';

Now I have a column of blob data (always 20 bytes) and I would like to 
do a LIKE select on this column where I have the first 10 bytes but they 
can be any value including, of course, the % character. Is this possible 
or does the arbitrary nature of the data make this infeasible?

If it is possible, how would I define a prepared statement so that I can 
just bind the (10 byte) value into it?

Thanks

-- 
Matthew Jones
Hewlett-Packard Ltd
Long Down Avenue
Stoke Gifford
Bristol.  BS34 8QZ
Tel:   +44 (0) 117 312 7490
Email:matthew.jo...@hp.com

Hewlett-Packard Limited registered Office: Cain Road, Bracknell, Berks, 
RG12 1HN. Registered No: 690597 England

The contents of this message and any attachments to it are confidential 
and may be legally privileged. If you have received this message in 
error, you should delete it from your system immediately and advise the 
sender.

To any recipient of this message within HP, unless otherwise stated you 
should consider this message and attachments as "HP CONFIDENTIAL".
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite_temp_master schema

[Simon Slavin]

On 8 Jul 2010, at 4:09pm, Jon Polfer wrote:

> My apologies - I took a closer look and realized that I was actually
> putting a semicolon on the end of the statement:
> 
> .schema sqlite_temp_master;  -- doesn't work.

Which is correct.  Semicolons are for SQL commands.  '.schema' is not a SQL 
command.

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

Re: [sqlite] Temp views and sqlite_temp_master

[Jay A. Kreibich]

On Wed, Jul 07, 2010 at 08:20:13PM -0500, Jon Polfer scratched on the wall:
> I'm currently running SQLite 3.5.9.
> 
> I've been experimenting around with temporary views, and discovered
> that:
> 
> a)  They don't appear in sqlite_master after you create them; they do
> however appear in a (I believe undocumented) table called
> sqlite_temp_master that I found by running an EXPLAIN on the CREATE
> TEMPORARY VIEW.

http://www.sqlite.org/faq.html#q7
http://www.sqlite.org/sqlite.html

  Not obvious, but there.

> b) You can create another view with the same name that is non-temporary
> (CREATE VIEW test_view AS SELECT 2 + 2 AS four).

  All temp items go into a different database named "temp".  Object
  names are only unique within a database.

> c) It appears that, if you have a temporary view and a non-temporary
> view of the same name, the temporary view is used, regardless of order
> they were created in.

  Yes.  The search pattern for an unqualified identifier is always:

  1) temp database
  2) main database (opened with sqlite3_open())
  3) any others, in "slot" order (opened with ATTACH).  These will fill
 up in order (assuming no DETACH commands are run).

  If you want to access a specific object, just qualify it.

> Letter a) surprises me; can I count on sqlite_temp_master being around
> for a while?

  Yes, it is just as standard as sqlite_master.

> Letter b) feels like a bug.  Has this been fixed?  

  Not if you understand how it works.  Not fixing what isn't broken.

> Letter c) makes me wonder: is there is a way to reference both views?

  As others have answered, just qualify the identifier with a database
  name.

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

[Jay A. Kreibich]

On Thu, Jul 08, 2010 at 06:50:52PM +0400, Alexey Pechnikov scratched on the 
wall:
> 2010/7/8 Richard Hipp 
> 
> > In the current implementation, if you call "PRAGMA wal_checkpoint" just
> > prior to closing the database, the WAL file will be deleted automatically.
> > But it keeps the database in WAL mode, so the WAL is recreated the next
> > time you open and write to the database.
> 
> It's not helpful for backward compability. How about version downgrade of
> the Android or some other mobile OS and as result impossibility to open any
> SQLite database?..

  That's not backwards compatibility (newer versions working with items
  from older environments), that's forwards compatibility (older versions
  working new items from a newer environment).
  
  It is no different than a new(er) application that uses newer APIs
  not working on an older version of the OS.  If you want the ability
  to do this, don't use new features (or turn it off every time you
  close the database).

   -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] work-arounds for (possibly buggy) nfs?

[Eric Smith]

Richard Hipp wrote: 

> sqlite3 db ./foo -vfs unix-dotfile 
> 
> That uses an alternative VFS that uses dot-file locking instead of 
> posix advisory locks.  The dot-file locks are someone slower and have less 
> concurrency, so the use of "PRAGMA locking_mode=EXCLUSIVE" might also be a 
> good idea in such a configuration.  

Looking at your code, I see that unix-dotfile locks are in reality 
always exlusive.  My guess is that the existence of a separate file 
indicates that a process owns the (unique) lock, and non-existence 
indicates no one owns the lock.  

I see you defer to open(2) with O_EXCL to get atomicity here.  

The docs leave something unclear: 

If I ATTACH a database with pragma locking_mode=EXCLUSIVE under the 
unix-dotfile VFS, get a lock on the attached db, execute a write 
transaction on it, and then DETACH the database, then I'm assuming the 
lock on the attached db is released.  Is that right?  

Eric 

-- 
Eric A. Smith

I still maintain the point that designing a monolithic kernel in 
1991 is a fundamental error.  Be thankful you are not my student.  
You would not get a high grade for such a design.
-- Andrew Tanenbaum, to Linus Torvalds
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite_temp_master schema

[Jon Polfer]

My apologies - I took a closer look and realized that I was actually
putting a semicolon on the end of the statement:

.schema sqlite_temp_master;  -- doesn't work.

-Jon


__ 
Jon Polfer
Project Engineer - High Level Software

Engineering Office Phone: 262-832-0049  (Ext. 5 for Jon Polfer)
Fax: 
E-mail: jpol...@forceamerica.com

FORCE America Inc. 
W229 N1433 Westwood Drive, Suite 200 
Waukesha, WI 53186
www.forceamerica.com

The Leading Innovator in Mobile Hydraulic Solutions


-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Richard Hipp
Sent: Thursday, July 08, 2010 9:48 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] sqlite_temp_master schema

On Thu, Jul 8, 2010 at 8:49 AM, Jon Polfer 
wrote:

> I'm running SQLite 3.5.9.
>
> In the interactive client sqlite3, when I type:
>
> .schema sqlite_temp_master
>
> No schema description is returned, even when data exists in the table.
>
> Is this to be expected?
>

Cannot replicate.  Here is what I get:

d...@elly:~> sqlite/history/bld/sqlite3-3.5.9
SQLite version 3.5.9
Enter ".help" for instructions
sqlite> .schema sqlite_temp_master
CREATE TEMP TABLE sqlite_temp_master (
  type text,
  name text,
  tbl_name text,
  rootpage integer,
  sql text
);
sqlite> .quit
d...@elly:~>




>
> -Jon
>
>
> __
> Jon Polfer
> Project Engineer - High Level Software
>
> Engineering Office Phone: 262-832-0049  (Ext. 5 for Jon Polfer)
> Fax:
> E-mail: jpol...@forceamerica.com
>
> FORCE America Inc.
> W229 N1433 Westwood Drive, Suite 200
> Waukesha, WI 53186
> www.forceamerica.com
>
> The Leading Innovator in Mobile Hydraulic Solutions
>
>
>
>
> The information contained in this message and any attachment may be
> proprietary, confidential, and privileged or subject to the work
> product doctrine and thus protected from disclosure.  If the reader
> of this message is not the intended recipient, or an employee or
> agent responsible for delivering this message to the intended
> recipient, you are hereby notified that any dissemination,
> distribution or copying of this communication is strictly prohibited.
> If you have received this communication in error, please notify me
> immediately by replying to this message and deleting it and all
> copies and backups thereof.  Thank you.
>
>
> Disclaimer added by CodeTwo Exchange Rules
> http://www.codetwo.com
> ___
> 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-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 Thu, Jul 08, 2010 at 02:27:02PM +0100, Andrew Wood scratched on the wall:

> Even if I fix the way a blob is inserted so that it causes it to be 
> correctly detected  as a blob by sqlite3_column_type() theres still a 
> problem -  because if a field comes back as SQLITE_NULL my application 
> still needs to know what type the field was supposed to be.

  Most applications know what they asked for, but I realize there are
  some that do not (like GUI database managers).  Still, if you have a
  NULL, what are you going to do about it?

  If you know a column should have BLOBs in it, you can also just keep
  asking for BLOBs.  SQLite will covert them using the rules defined
  here:  http://sqlite.org/c3ref/column_blob.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] Which SQLite API return SQLITE_BUSY?

[Jay A. Kreibich]

On Thu, Jul 08, 2010 at 12:42:36PM +0530, Lloyd scratched on the wall:
> Hi,
> 
> Which are all the type of SQLite APIs can return SQLITE_BUSY?
> 
> sqlite3_open_v2
> sqlite3_prepare_v2

  All styles of these calls, not just the _v2 versions.

> sqlite3_step

  That's the big one.

> sqlite3_finalize

  sqlite3_reset() can as well, but in the case of both _finalize() and
  _reset() the call itself (e.g. the finalize operation or reset
  operation) will always work.  The error code is returned as the "left
  over" from sqlite3_step().  See the history between _prepare() and
  _prepare_v2().

> sqlite3_bind_blob
> sqlite3_column_int

  The sqlite3_column_xxx() and sqlite3_bind_xxx() calls should not.



  Additionally, sqlite3_blob_open() can, however, as can some of the
  sqlite3_backup_xxx() calls (they use _prepare/_step internally).
  sqlite3_exec() and sqlite3_get_table() as well.


  sqlite3_close() can also return SQLITE_BUSY, but it isn't a locking
  issue-- it just means you forgot to finalize all the statements.
  IIRC, there are one or two other functions that do something similar,
  but they're usually pretty obvious.

  I think that's the bulk of it, however.

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

[Richard Hipp]

On Thu, Jul 8, 2010 at 10:50 AM, Alexey Pechnikov wrote:

> 2010/7/8 Richard Hipp 
>
> > In the current implementation, if you call "PRAGMA wal_checkpoint" just
> > prior to closing the database, the WAL file will be deleted
> automatically.
> > But it keeps the database in WAL mode, so the WAL is recreated the next
> > time
> > you open and write to the database.
>
>
> It's not helpful for backward compability. How about version downgrade of
> the Android or some other mobile OS and as result impossibility to open any
> SQLite database?..
>

Presumably the version downgrade comes with a script.  (Otherwise, other
changes such as schema changes would go uncorrected.)  That script merely
has to run

 PRAGMA journal_mode=DELETE;

on every database.  Problem solved.


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

Re: [sqlite] WAL questions

[Alexey Pechnikov]

2010/7/8 Richard Hipp 

> In the current implementation, if you call "PRAGMA wal_checkpoint" just
> prior to closing the database, the WAL file will be deleted automatically.
> But it keeps the database in WAL mode, so the WAL is recreated the next
> time
> you open and write to the database.


It's not helpful for backward compability. How about version downgrade of
the Android or some other mobile OS and as result impossibility to open any
SQLite database?..

-- 
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] Reg: In Memory Database Using SQLite

[Eric Smith]

Subhadeep Ghosh wrote: 

> I am aware of the functionality which you mentioned.  But the reason why 
> I ended up coding the feature was because of a very specific requirement.  
> My requirement was such that some of my client applications were running 
> on disk-less systems where I was not using any kind of network storage.  
> But my applications used SQLite.  So I coded the below mentioned feature 
> such that the database was being stored on the server and when the 
> disk-less clients wanted to use them, they fetched the database from the 
> server over the network and directly create a database out of the fetched 
> data (basically serializing and de-serializing the database).  
> 
> I am guessing that SQLite does not support such functionality.  The 
> closest thing would have been to fetch the database over the network, 
> store it in a file (which in this case is not possible) and then open the 
> database.  
> 
> I maybe wrong, but if such a feature already exists then I would be more 
> than happy to adopt it in my code.  

I see.  

Firstly, you may be better off using a client/server db, since that 
seems to map more naturally into your use-case.  But let's forget about 
that for the moment.  

Had I written your application, I first would have considered dumping 
the original database to raw SQL text and reading it into the in-mem 
db on the other end.  That would be pretty trivial to do, and if it 
suits your needs perf-wise, you're golden.  This also gives the extra 
advantage of having a textual communication format between the master 
and the slave, which can be a useful debugging tool.  

I would then have considered using a RAM-backed filesystem on the 
slave.  You can copy the sqlite db as-is to the slave and open it 
using normal sqlite api calls.  SQLite and your app think the db is 
disk-backed because the OS is faking the existence of a disk.  Whether 
this option works well depends on how easy it is to get a ram-backed fs 
up and running on your slave.  (In linux this is very easy, not sure 
about your deployment oS.)  

Eric 

-- 
Eric A. Smith

The people can always be brought to the bidding of the leaders. That 
is easy. All you have to do is tell them they are being attacked and 
denounce the pacifists for lack of patriotism and exposing the 
country to danger. It works the same way in any country. 
-- Herman Goering, at the Nuremberg trials
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite_temp_master schema

[Richard Hipp]

On Thu, Jul 8, 2010 at 8:49 AM, Jon Polfer  wrote:

> I'm running SQLite 3.5.9.
>
> In the interactive client sqlite3, when I type:
>
> .schema sqlite_temp_master
>
> No schema description is returned, even when data exists in the table.
>
> Is this to be expected?
>

Cannot replicate.  Here is what I get:

d...@elly:~> sqlite/history/bld/sqlite3-3.5.9
SQLite version 3.5.9
Enter ".help" for instructions
sqlite> .schema sqlite_temp_master
CREATE TEMP TABLE sqlite_temp_master (
  type text,
  name text,
  tbl_name text,
  rootpage integer,
  sql text
);
sqlite> .quit
d...@elly:~>




>
> -Jon
>
>
> __
> Jon Polfer
> Project Engineer - High Level Software
>
> Engineering Office Phone: 262-832-0049  (Ext. 5 for Jon Polfer)
> Fax:
> E-mail: jpol...@forceamerica.com
>
> FORCE America Inc.
> W229 N1433 Westwood Drive, Suite 200
> Waukesha, WI 53186
> www.forceamerica.com
>
> The Leading Innovator in Mobile Hydraulic Solutions
>
>
>
>
> The information contained in this message and any attachment may be
> proprietary, confidential, and privileged or subject to the work
> product doctrine and thus protected from disclosure.  If the reader
> of this message is not the intended recipient, or an employee or
> agent responsible for delivering this message to the intended
> recipient, you are hereby notified that any dissemination,
> distribution or copying of this communication is strictly prohibited.
> If you have received this communication in error, please notify me
> immediately by replying to this message and deleting it and all
> copies and backups thereof.  Thank you.
>
>
> Disclaimer added by CodeTwo Exchange Rules
> http://www.codetwo.com
> ___
> 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] work-arounds for (possibly buggy) nfs?

[Eric Smith]

Richard Hipp wrote:

> sqlite3 db ./foo -vfs unix-dotfile

Works like a charm!

> That uses an alternative VFS that uses dot-file locking instead of posix
> advisory locks.  The dot-file locks are someone slower and have less
> concurrency, so the use of "PRAGMA locking_mode=EXCLUSIVE" might also be a
> good idea in such a configuration.

My use-case requires no concurrency whatsoever.  I'll circle back if I
notice any perf changes.

Many thanks!

Eric

--
Eric A. Smith

fenderberg, n.:
The large glacial deposits that form on the insides
of car fenders during snowstorms.
-- "Sniglets", Rich Hall & Friends
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] docs bug: tclsqlite.html

[Richard Hipp]

On Thu, Jul 8, 2010 at 10:19 AM, Eric Smith  wrote:

> tclsqlite.html lists an "unlock_notify" method with no other
> documentation.  Trying to use it gives me this:
>
> -bash-2.05b$ tcl
> % package require sqlite
> 3.6.23
> % sqlite3 db /tmp/foo
> % db unlock_notify
> unlock_notify not available in this build
> %
>

Recompile with -DSQLITE_ENABLE_UNLOCK_NOTIFY


>
> --
> Eric A. Smith
>
> The concept is interesting and well-formed, but in order to earn
> better than a 'C,' the idea must be feasible.
>-- A Yale University management professor in response to Fred Smith's
> paper
>   proposing reliable overnight delivery service.
>   (Smith went on to found Federal Express Corp.)
> ___
> 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] work-arounds for (possibly buggy) nfs?

[Richard Hipp]

On Thu, Jul 8, 2010 at 10:31 AM, Eric Smith  wrote:

> I'm forced into a situation where I have to use an nfs server that I
> think is buggy.
>

Try using:

sqlite3 db ./foo -vfs unix-dotfile

That uses an alternative VFS that uses dot-file locking instead of posix
advisory locks.  The dot-file locks are someone slower and have less
concurrency, so the use of "PRAGMA locking_mode=EXCLUSIVE" might also be a
good idea in such a configuration.



>
> I can read/write files normally using fopen() on the exported
> filesystem, but can't do anything useful with sqlite 3.6.23.1:
>
> -bash-2.05b$ tclsh
> % package require sqlite
> 3.6.23
> % sqlite3 db ./foo
> % db eval {pragma synchronous=off}
> disk I/O error
> % puts $::errorInfo
> disk I/O error
>while executing
> "db eval {pragma synchronous=off}"
> % db eval {create table t(a)}
> disk I/O error
> % ^C
> -bash-2.05b$ lh
> total 0
> -rw-r--r--  1 esmith  1005 0B Jul  8 10:19 foo
> -bash-2.05b$ echo foobar > foo
> -bash-2.05b$ lh
> total 2
> -rw-r--r--  1 esmith  1005 7B Jul  8 10:23 foo
> -bash-2.05b$ tclsh
> % package require sqlite
> 3.6.23
> %
> % file delete -force foo
> % sqlite3 db ./foo
> % db eval {pragma locking_mode=exclusive}
> exclusive
> % db eval {pragma synchronous=off}
> disk I/O error
>
> Everything works fine when I use a database on the local disk:
>
> % db close
> % sqlite3 db /tmp/foo
> % db eval {pragma synchronous=off}
> % db eval {create table t(a)}
> % db eval {insert into t values('blah')}
> % db eval {select count(*) from t}
> 1
> %
>
> -bash-2.05b$ uname -a
> FreeBSD  5.40. FreeBSD 5.40. #0: Fri Sep 19 03:14:59 EDT 2008
>  i386
>
> Can anyone offer any hints?
>
> Thanks!
> Eric
>
> --
> Eric A. Smith
>
> Furbling, v.:
>Having to wander through a maze of ropes at an airport or
>bank even when you are the only person in line.
>-- Rich Hall, "Sniglets"
> ___
> 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 questions

[Richard Hipp]

On Thu, Jul 8, 2010 at 10:15 AM, Alexey Pechnikov wrote:

> >
> > Remove the WAL mode from "PRAGMA journal_mode".  The journal_mode pragma
> > only specifies the various rollback journal modes.  Enable the WAL using
> a
> > separate pragma such as  "PRAGMA wal=ON; PRAGMA wal=OFF;"
> >
>
> It's more clean I think. With wal=on and journal_mode=delete SQLite may
> delete WAL journal before the last connection is closed and so provide
> backward compability (of cource, only new SQLite versions can restore the
> crashed databases). With wal=on and journal_mode=persist SQLite may use
> persistent WAL journal without backward compability reasons.
>

In the current implementation, if you call "PRAGMA wal_checkpoint" just
prior to closing the database, the WAL file will be deleted automatically.
But it keeps the database in WAL mode, so the WAL is recreated the next time
you open and write to the database.

-- 
-
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] work-arounds for (possibly buggy) nfs?

[Eric Smith]

I'm forced into a situation where I have to use an nfs server that I 
think is buggy.  

I can read/write files normally using fopen() on the exported 
filesystem, but can't do anything useful with sqlite 3.6.23.1:

-bash-2.05b$ tclsh
% package require sqlite
3.6.23
% sqlite3 db ./foo
% db eval {pragma synchronous=off}
disk I/O error
% puts $::errorInfo
disk I/O error
while executing
"db eval {pragma synchronous=off}"
% db eval {create table t(a)}
disk I/O error
% ^C
-bash-2.05b$ lh
total 0
-rw-r--r--  1 esmith  1005 0B Jul  8 10:19 foo
-bash-2.05b$ echo foobar > foo
-bash-2.05b$ lh
total 2
-rw-r--r--  1 esmith  1005 7B Jul  8 10:23 foo
-bash-2.05b$ tclsh
% package require sqlite
3.6.23
% 
% file delete -force foo
% sqlite3 db ./foo
% db eval {pragma locking_mode=exclusive}
exclusive
% db eval {pragma synchronous=off}
disk I/O error

Everything works fine when I use a database on the local disk:

% db close
% sqlite3 db /tmp/foo
% db eval {pragma synchronous=off}
% db eval {create table t(a)}
% db eval {insert into t values('blah')}
% db eval {select count(*) from t}
1
% 

-bash-2.05b$ uname -a
FreeBSD  5.40. FreeBSD 5.40. #0: Fri Sep 19 03:14:59 EDT 2008  i386

Can anyone offer any hints?

Thanks!
Eric

-- 
Eric A. Smith

Furbling, v.:
Having to wander through a maze of ropes at an airport or 
bank even when you are the only person in line.
-- Rich Hall, "Sniglets"
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] docs bug: tclsqlite.html

[Eric Smith]

tclsqlite.html lists an "unlock_notify" method with no other
documentation.  Trying to use it gives me this:

-bash-2.05b$ tcl
% package require sqlite
3.6.23
% sqlite3 db /tmp/foo
% db unlock_notify
unlock_notify not available in this build
% 

--
Eric A. Smith

The concept is interesting and well-formed, but in order to earn 
better than a 'C,' the idea must be feasible.
-- A Yale University management professor in response to Fred Smith's paper
   proposing reliable overnight delivery service.
   (Smith went on to found Federal Express Corp.)
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] WAL questions

[Alexey Pechnikov]

>
> Remove the WAL mode from "PRAGMA journal_mode".  The journal_mode pragma
> only specifies the various rollback journal modes.  Enable the WAL using a
> separate pragma such as  "PRAGMA wal=ON; PRAGMA wal=OFF;"
>

It's more clean I think. With wal=on and journal_mode=delete SQLite may
delete WAL journal before the last connection is closed and so provide
backward compability (of cource, only new SQLite versions can restore the
crashed databases). With wal=on and journal_mode=persist SQLite may use
persistent WAL journal without backward compability reasons.
-- 
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] SQLite for .NET Framework 4.0

[Roosevelt Anderson]

You should ask your question here http://sqlite.phxsoftware.com/forums/

On Thu, Jul 8, 2010 at 9:24 AM, Daniel  wrote:
> I forgot to say I have Visual Studio 2010 with .NET Framework 4.0 for
> programming in C#.
>
> In VS2008 it worked fine and after changing to Version 2010 it don't.
> ___
> 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] "adb shell" command gives very inconsistent results

[ca44]

My apology. 

It was late when I posted this and wasn't paying enough attention to where I 
was posting it to. 



-Chris 


- Original Message - 
From: "Simon Slavin"  
To: "General Discussion of SQLite Database"  
Sent: Wednesday, July 7, 2010 10:57:26 PM 
Subject: Re: [sqlite] "adb shell" command gives very inconsistent results 


On 8 Jul 2010, at 6:18am, c...@comcast.net wrote: 

>  I do not own an Android phone and am working exclusively in the 
> Eclipse/emulator environment on my laptop. 

You're posting to the sqlite discussion list.  You need to ask about this on a 
list about Eclipse or something. 

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

[Max Vlasov]

>>

> >
> > Simon, you gave an interesting explanation, but does this rule work in
> > general? I mean there are many models, many sizes and so on.
>
> Don't know.  You could test it.  Write a program that creates a file half
> the size of the drive, then writes to random parts of it timing each
> command.  If all the write commands take about the same amount of time then
> it doesn't work the way I described.  I just repeated the description I had
> read of the way SSD drives work.
>

Thanks, that what I thought )
SSD is an interesting thing to research especially in sqlite perspective,
for example, for reading it's access times that makes SSD winner, so
probably reading large sqlite database randomly can have some benefits being
used on SSD, but I don't know of any real world measurements. Do you know
any?

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

Re: [sqlite] Books which cover C API

[Andrew Wood]

Id seen the book page but wasnt sure which one covered the C API the 
best. Ill try the APress one for now, thanks for the tip.

On 07/07/10 19:55, Jay A. Kreibich wrote:
> On Wed, Jul 07, 2010 at 07:45:02PM +0100, Andrew Wood scratched on the wall:
>
>> Which of the books on the market is the best for covering the C API?
>>  
>http://sqlite.org/books.html
>
>
>
>
>If you want to go out and buy something today, the most popular book
>is "The Definitive Guide to SQLite" by Mike Owens.  It is a bit
>older, but the core APIs haven't really changed:
>
> http://www.amazon.com/Definitive-Guide-SQLite-Mike-Owens/dp/1590596730/
>
>
>
>This one is coming out next month.  I like it.
>
> http://www.amazon.com/Using-SQLite-Jay-Kreibich/dp/0596521189/
>
>
> -j
>
>

___
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

[Andrew Wood]

Just thought of something else.

Even if I fix the way a blob is inserted so that it causes it to be 
correctly detected  as a blob by sqlite3_column_type() theres still a 
problem -  because if a field comes back as SQLITE_NULL my application 
still needs to know what type the field was supposed to be.




On 07/07/10 15:31, Jay A. Kreibich wrote:
> On Wed, Jul 07, 2010 at 02:35:58PM +0100, Andrew Wood scratched on the wall:
>
>> Ok, what if I come at the problem from the opposite side:
>>
>> The problem is, Ive got a BLOB field which contains  a char array as an
>> escpaped string but when I call sqlite3_column_type(preparedstatement,
>> x); it comes back as SQLITE_TEXT not SQLITE_BLOB
>>  
>It sounds like it is being inserted as a text value (or you're
>calling sqlite3_column_bytes16() or something silly).  Try
>selecting typeof(col) and see what that returns.
>
>If you're inserting literal values, BLOBs must be in hex.  For
>example, these will both insert the same three bytes
>(ASCII 'A' = 0x41):
>
>
>  X'414243'is a BLOB value.
>   'ABC'   is a text value.
>
>
>  -j
>
>

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

Re: [sqlite] SQLite for .NET Framework 4.0

[Daniel]

I forgot to say I have Visual Studio 2010 with .NET Framework 4.0 for 
programming in C#.

In VS2008 it worked fine and after changing to Version 2010 it don't.
___
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

[Subhadeep Ghosh]

Hello Eric,

I am aware of the functionality which you mentioned. But the reason why I
ended up coding the feature was because of a very specific requirement. My
requirement was such that some of my client applications were running on
disk-less systems where I was not using any kind of network storage. But my
applications used SQLite. So I coded the below mentioned feature such that
the database was being stored on the server and when the disk-less clients
wanted to use them, they fetched the database from the server over the
network and directly create a database out of the fetched data (basically
serializing and de-serializing the database).

I am guessing that SQLite does not support such functionality. The closest
thing would have been to fetch the database over the network, store it in a
file (which in this case is not possible) and then open the database.

I maybe wrong, but if such a feature already exists then I would be more
than happy to adopt it in my code.

Thank you and regards,
Subhadeep Ghosh.

On Thu, Jul 8, 2010 at 6:40 PM, Eric Smith  wrote:

> Subhadeep Ghosh wrote:
>
> > I finally managed to create a wrapper around the SQLite core to support
> > the creation of in-memory databases.  The wrapper comprises of three
> > functions - one to serialize the database, one to de-serialize a database
> > and the third one to do the cleanup job.
> >
> > The function which serializes a database, copies the entire contents of
> > the database to a block of memory.  The function which de-serializes
> takes
> > a block of memory as input and creates a database object out of it.
> >
> > I am including the source code for the interested souls and I've not put
> > comments as I hate doing it.
> >
> > I hope you guys find the code useful or at-least interesting.
>
> I hope you didn't spend too much time on that.  SQLite supports
> in-memory databases natively: if you open the file called ":memory:" you
> get an in-memory database that dies when the connection is closed:
>
> http://sqlite.org/c3ref/open.html
>
> --
> Eric A. Smith
>
> More people would come here if it weren't so crowded.
>-- Yogi Berra
> ___
> 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] Reg: In Memory Database Using SQLite

[Eric Smith]

Subhadeep Ghosh wrote: 

> I finally managed to create a wrapper around the SQLite core to support 
> the creation of in-memory databases.  The wrapper comprises of three 
> functions - one to serialize the database, one to de-serialize a database 
> and the third one to do the cleanup job.  
> 
> The function which serializes a database, copies the entire contents of 
> the database to a block of memory.  The function which de-serializes takes 
> a block of memory as input and creates a database object out of it.  
> 
> I am including the source code for the interested souls and I've not put 
> comments as I hate doing it.  
> 
> I hope you guys find the code useful or at-least interesting.  

I hope you didn't spend too much time on that.  SQLite supports 
in-memory databases natively: if you open the file called ":memory:" you
get an in-memory database that dies when the connection is closed:

http://sqlite.org/c3ref/open.html

-- 
Eric A. Smith

More people would come here if it weren't so crowded.
-- Yogi Berra
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite for .NET Framework 4.0

[Michael Knigge]

Daniel schrieb:
> does anyone know if there is a support for SQLite in .NET Framework 4.0.

As always: http://sqlite.phxsoftware.com/


-- 
Yours sincerely

Michael Knigge
Development


S.E.T. Software GmbH
Lister Straße 15
30163 Hannover
GERMANY

Tel.  +49 511/3 97 80-23
Fax   +49 511/3 97 80-65
michael.kni...@set-software.de

Commercial Registry: HRB52778 Local Court Hannover
Chief Executive Officer: Till Dammermann, Dr. Bernd Huber
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] WAL questions

[Max Vlasov]

Second Proposed Change:

>
> Remove the WAL mode from "PRAGMA journal_mode".  The journal_mode pragma
> only specifies the various rollback journal modes.  Enable the WAL using a
> separate pragma such as  "PRAGMA wal=ON; PRAGMA wal=OFF;"
>
> Thoughts?  Comments?  Other suggestions?
>
>
Maybe it's not the right time, but can it be implemented with separated
pragma as your second proposed change but with more options like OFF,
PERSISTENT, TEMPORAL. Still thinking about the fact that WAL being a new
software feature and actually becomes a new file format change, the latter
option can solve this (if it's technically possible and not hard to append
of course). The logic can be like this, if WAL=TEMPORAL, the first
reader/writer that accesses the base makes the changes to 18,19 bytes (=2)
allowing it and following reader/writers work in WAL mode and the last
reader/writer that closes the base, reverts it back (=1) allowing the file
format stays the same. Sure there are possible cases when the format still
changed (for example unexpected program end), but the next successful
open/close will fix this.

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

[sqlite] sqlite_temp_master schema

[Jon Polfer]

I'm running SQLite 3.5.9.

In the interactive client sqlite3, when I type:

.schema sqlite_temp_master

No schema description is returned, even when data exists in the table.

Is this to be expected?

-Jon


__ 
Jon Polfer
Project Engineer - High Level Software

Engineering Office Phone: 262-832-0049  (Ext. 5 for Jon Polfer)
Fax: 
E-mail: jpol...@forceamerica.com

FORCE America Inc. 
W229 N1433 Westwood Drive, Suite 200 
Waukesha, WI 53186
www.forceamerica.com

The Leading Innovator in Mobile Hydraulic Solutions




The information contained in this message and any attachment may be
proprietary, confidential, and privileged or subject to the work
product doctrine and thus protected from disclosure.  If the reader
of this message is not the intended recipient, or an employee or
agent responsible for delivering this message to the intended
recipient, you are hereby notified that any dissemination,
distribution or copying of this communication is strictly prohibited.
If you have received this communication in error, please notify me
immediately by replying to this message and deleting it and all
copies and backups thereof.  Thank you.


Disclaimer added by CodeTwo Exchange Rules
http://www.codetwo.com
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] WAL questions

[Richard Hipp]

On Thu, Jul 8, 2010 at 7:10 AM, Richard Hipp  wrote:

> Though, I will admit, this is confusing, and I was thinking last night
> about ways we could possibly change it
>


Current behavior:

(1) PRAGMA name.journal_mode=MODE; -- set the mode to MODE for database
"name".

(2) PRAGMA name.journal_mode; -- return current journal mode for database
"name".

(3) PRAGMA journal_mode=MODE;  -- set the mode to MODE for all attached
databases and also apply MODE to all databases ATTACH-ed in the future.

(4) PRAGMA journal_mode; -- report the most recent setting by (3).  Report
"delete" if there have been no prior occurences of (3).

First Proposed Change:

(1) and (2) are the same.

(3) PRAGMA journal_mode=MODE;  -- set the mode to MODE for all attached
databases.  Databases created by subsequent ATTACH use MODE.  Existing
databases added by ATTACH use MODE if and only if doing so does not require
them to change in or out of WAL mode.

(4) PRAGMA journal_mode;  -- report the most recent setting by (3).  Or if
(3) has never been used, work the same as "PRAGMA main.journal_mode;"

Second Proposed Change:

Remove the WAL mode from "PRAGMA journal_mode".  The journal_mode pragma
only specifies the various rollback journal modes.  Enable the WAL using a
separate pragma such as  "PRAGMA wal=ON; PRAGMA wal=OFF;"

Thoughts?  Comments?  Other suggestions?

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

[Richard Hipp]

On Thu, Jul 8, 2010 at 7:51 AM, Max Vlasov  wrote:

> So all his current code base works once it started using this version of
> sqlite, but consequently small (or maybe large part) of his bases becomes
> WAL-enabled (number 2 in the file format). The latter may appear because of
> his own WAL-On without WAL-Off or WAL-On with unexpected program
> interruption. Everything is ok, until these bases have to be
> distributed/used in other enviroments, that probably use lower than 3.7
> versions of sqlite3, so it stops working claming about unknown file format.
>


Correct.  Older versions of SQLite cannot read or write a database file that
has a non-empty WAL, since older versions of SQLite do not know what to do
with the WAL.  There is nothing really we can do about this, other than not
provide WAL as an option.



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

[Max Vlasov]

> You want "PRAGMA main.journal_mode"
>
> A "PRAGMA journal_mode;" (without the "main.") shows you the default
> journal
> mode used by newly created databases, which is always "DELETE" unless you
> have changed it with a prior "PRAGMA journal_mode=MODE" command.
>
> Though, I will admit, this is confusing, and I was thinking last night
> about
> ways we could possibly change it
>

Thanks for pointing out. I have two observations
- According to docs this was present for ages so it's a shame on me not to
knowing it in the first place. So probably it should stay as it is now.

- But this is the first time when this important pragma really affects the
sqlite file format and what it more important, not only for current session.
You probably had reasons for implementing WAL the way it is now, but
imagine, this setting once changed don't remind of itself for the developer.
So all his current code base works once it started using this version of
sqlite, but consequently small (or maybe large part) of his bases becomes
WAL-enabled (number 2 in the file format). The latter may appear because of
his own WAL-On without WAL-Off or WAL-On with unexpected program
interruption. Everything is ok, until these bases have to be
distributed/used in other enviroments, that probably use lower than 3.7
versions of sqlite3, so it stops working claming about unknown file format.
I think it's a potential way of new wave of mass bug/missing reporting or
simply confusion

I may exaggerate, I suggest other participants of the list share their
thoughts

Thanks,

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

Re: [sqlite] Problem to switch between WAL and delete journal modes

[Igor Tandetnik]

Alexey Pechnikov  wrote:
> Some times database can return journal_mode=delete when exists WAL journal:

Why shouldn't it? journal_mode reports the kind of journal this connection is 
going to write for upcoming transactions - not the kind of journal that the 
previous crashed application left behind (and which has been rolled back and 
cleared up before pragma statement was even executed).
-- 
Igor Tandetnik

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

Re: [sqlite] Temp views and sqlite_temp_master

[Simon Davies]

On 8 July 2010 02:20, Jon Polfer  wrote:
> I'm currently running SQLite 3.5.9.
>
> I've been experimenting around with temporary views, and discovered
> that:
>
> a)  They don't appear in sqlite_master after you create them; they do
> however appear in a (I believe undocumented) table called
> sqlite_temp_master that I found by running an EXPLAIN on the CREATE
> TEMPORARY VIEW.
>
> CREATE TEMPORARY VIEW test_view AS SELECT 1 + 1 AS two;
> SELECT count(*) from sqlite_master where type = 'view' and name =
> 'test_view';   -- returns 0
> SELECT count(*) from sqlite_temp_master where type = 'view' and name =
> 'test_view';  -- returns 1
>
>
> b) You can create another view with the same name that is non-temporary
> (CREATE VIEW test_view AS SELECT 2 + 2 AS four).
>
> CREATE TEMPORARY VIEW test_view AS SELECT 1 + 1 AS two;
> CREATE VIEW test_view AS SELECT 2 + 2 AS four;
>
> NB:  One can then drop the test_view twice.
>
> c) It appears that, if you have a temporary view and a non-temporary
> view of the same name, the temporary view is used, regardless of order
> they were created in.
>
> CREATE VIEW test_view AS SELECT 2 + 2 AS four;
> CREATE TEMPORARY VIEW test_view AS SELECT 1 + 1 as two;
> SELECT * FROM test_view;  -- returns 2
> DROP VIEW test_view;
> DROP VIEW test_view;
> CREATE TEMPORARY VIEW test_view AS SELECT 1 + 1 as two;
> CREATE VIEW test_view AS SELECT 2 + 2 AS four;
> SELECT * FROM test_view;  -- returns 2
>
>
> Letter a) surprises me; can I count on sqlite_temp_master being around
> for a while?

At least while you have temp data (tables, views etc)

>
> Letter b) feels like a bug.  Has this been fixed?

I don't think it's a bug

>
> Letter c) makes me wonder: is there is a way to reference both views?

qualify with the db name - main or temp:

SQLite version 3.6.11
Enter ".help" for instructions
sqlite>
sqlite> CREATE TEMPORARY VIEW test_view AS SELECT 1 + 1 as two;
sqlite> CREATE VIEW test_view AS SELECT 2 + 2 AS four;
sqlite> SELECT * FROM main.test_view;
4
sqlite> SELECT * FROM temp.test_view;
2
sqlite>

>
> -Jon
>

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

Re: [sqlite] WAL questions

[Richard Hipp]

On Thu, Jul 8, 2010 at 12:45 AM, Max Vlasov  wrote:

> Alexey,
>
> I read this sentence, but it didn't help.
> So I suppose there's a bug in PRAGMA journal_mode logic
> Steps to reproduce.
>
> 1. Create an empty base with some table.
> Look at the 18,19 offsets, they both = 1, it's ok, the base is compatible
> with full range of sqlite3 versions.
>
> 2. Do PRAGMA journal_mode=WAL; , it returns wal, so WAL is set.
> Look at the 18,19 offsets, they both = 2, the base no longer compatible
> with
> older versions, checking... yes, they say "encrypted" or something.
> Query PRAGMA journal_mode; alone (just to check not set) , it still says
> "wal", ok
>
> 3. Close the database. offsets 18,19 still = 2, so the WAL setting are kept
> between sessions.
>
> 4. Open the db again, offsets 18,19 still = 2,
> query PRAGMA journal_mode; it says  "delete", but definetely should return
> "wal".
>

You want "PRAGMA main.journal_mode"

A "PRAGMA journal_mode;" (without the "main.") shows you the default journal
mode used by newly created databases, which is always "DELETE" unless you
have changed it with a prior "PRAGMA journal_mode=MODE" command.

Though, I will admit, this is confusing, and I was thinking last night about
ways we could possibly change it



>
> Max
>
>
> On Thu, Jul 8, 2010 at 12:09 AM, Alexey Pechnikov  >wrote:
>
> > See http://sqlite.org/draft/wal.html :
> >
> > "An SQLite database _connection_
> > defaults
> > to journal_mode=DELETE. To convert to WAL mode, use the following
> > pragma: PRAGMA journal_mode=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
> >
> ___
> 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] Temp views and sqlite_temp_master

[Jon Polfer]

I'm currently running SQLite 3.5.9.

I've been experimenting around with temporary views, and discovered
that:

a)  They don't appear in sqlite_master after you create them; they do
however appear in a (I believe undocumented) table called
sqlite_temp_master that I found by running an EXPLAIN on the CREATE
TEMPORARY VIEW.

CREATE TEMPORARY VIEW test_view AS SELECT 1 + 1 AS two;
SELECT count(*) from sqlite_master where type = 'view' and name =
'test_view';   -- returns 0
SELECT count(*) from sqlite_temp_master where type = 'view' and name =
'test_view';  -- returns 1


b) You can create another view with the same name that is non-temporary
(CREATE VIEW test_view AS SELECT 2 + 2 AS four).

CREATE TEMPORARY VIEW test_view AS SELECT 1 + 1 AS two;
CREATE VIEW test_view AS SELECT 2 + 2 AS four;

NB:  One can then drop the test_view twice.

c) It appears that, if you have a temporary view and a non-temporary
view of the same name, the temporary view is used, regardless of order
they were created in.

CREATE VIEW test_view AS SELECT 2 + 2 AS four;
CREATE TEMPORARY VIEW test_view AS SELECT 1 + 1 as two;
SELECT * FROM test_view;  -- returns 2
DROP VIEW test_view;
DROP VIEW test_view;
CREATE TEMPORARY VIEW test_view AS SELECT 1 + 1 as two;
CREATE VIEW test_view AS SELECT 2 + 2 AS four;
SELECT * FROM test_view;  -- returns 2


Letter a) surprises me; can I count on sqlite_temp_master being around
for a while?

Letter b) feels like a bug.  Has this been fixed?  

Letter c) makes me wonder: is there is a way to reference both views?

-Jon

__ 
Jon Polfer
Project Engineer - High Level Software

Engineering Office Phone: 262-832-0049  (Ext. 5 for Jon Polfer)
Fax: 
E-mail: jpol...@forceamerica.com

FORCE America Inc. 
W229 N1433 Westwood Drive, Suite 200 
Waukesha, WI 53186
www.forceamerica.com

The Leading Innovator in Mobile Hydraulic Solutions



The information contained in this message and any attachment may be
proprietary, confidential, and privileged or subject to the work
product doctrine and thus protected from disclosure.  If the reader
of this message is not the intended recipient, or an employee or
agent responsible for delivering this message to the intended
recipient, you are hereby notified that any dissemination,
distribution or copying of this communication is strictly prohibited.
If you have received this communication in error, please notify me
immediately by replying to this message and deleting it and all
copies and backups thereof.  Thank you.


Disclaimer added by CodeTwo Exchange Rules
http://www.codetwo.com
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[Black, Michael (IS)]

Try this project -- it should be completely self contained.
 
 
Michael D. Black
Senior Scientist
Northrop Grumman Mission Systems
 



From: sqlite-users-boun...@sqlite.org on behalf of smengl90
Sent: Wed 7/7/2010 1:06 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] EXTERNAL:Re: EXTERNAL: setup sqlite in vc++




Here is my email address fixed-term.seak.meng...@us.bosch.com

Thanks


Black, Michael (IS) wrote:
>
> I've got Visual Express 2008 C++ -- I made a Win32 console application and
> compiled this just fine.
> 
> If you want to send your email address I'll email you the project and you
> can try it on the non-Express version.
> 
> 
> Michael D. Black
> Senior Scientist
> Northrop Grumman Mission Systems
> 
>
> 
>
> From: sqlite-users-boun...@sqlite.org on behalf of smengl90
> Sent: Wed 7/7/2010 12:19 PM
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] EXTERNAL:Re: EXTERNAL: setup sqlite in vc++
>
>
>
>
> Ok, I did just that, but now I get errors when compiling, all related to
> the
> malloc operations.
>
> 1>Compiling...
> 1>TestSqlite.cpp
> 1>c:\documents and settings\las1pal\my documents\visual studio
> 2008\projects\sqlite3\sqlite3.c(12281) : error C2440: '=' : cannot convert
> from 'void *' to 'char *'
> 1>Conversion from 'void*' to pointer to non-'void' requires an
> explicit cast
> 1>c:\documents and settings\las1pal\my documents\visual studio
> 2008\projects\sqlite3\sqlite3.c(12975) : error C2440: '=' : cannot convert
> from 'void *' to 'sqlite3_int64 *'
> 1>Conversion from 'void*' to pointer to non-'void' requires an
> explicit cast
> 1>c:\documents and settings\las1pal\my documents\visual studio
> 2008\projects\sqlite3\sqlite3.c(13028) : error C2440: '=' : cannot convert
> from 'void *' to 'sqlite3_int64 *'
> 1>Conversion from 'void*' to pointer to non-'void' requires an
> explicit cast
> 1>c:\documents and settings\las1pal\my documents\visual studio
> 2008\projects\sqlite3\sqlite3.c(15959) : error C2440: '=' : cannot convert
> from 'void *' to 'sqlite3_mutex *'
> 1>Conversion from 'void*' to pointer to non-'void' requires an
> explicit cast
> ...
>
> Is it because I include a .c source file in a c++ project?
>
> Thanks
>
>
>
>
>
> ___
> 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-tp29086729p29099389.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] Problem to switch between WAL and delete journal modes

[Simon Slavin]

On 8 Jul 2010, at 11:50am, Simon Slavin wrote:

> That 'sqlite3' command does not refer to the grow.db database

Whoops.   Sorry, I should have read your post more carefully.

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

Re: [sqlite] Problem to switch between WAL and delete journal modes

[Simon Slavin]

On 8 Jul 2010, at 7:46am, Alexey Pechnikov wrote:

> $ ls|grep grow
> grow.db
> grow.db-shm
> grow.db-wal
> 
> $ sqlite3
> SQLite version 3.7.0
> Enter ".help" for instructions
> Enter SQL statements terminated with a ";"
> sqlite> pragma journal_mode;
> delete
> sqlite> .q

That 'sqlite3' command does not refer to the grow.db database so there's no 
reason for SQLite to guess it should be in WAL mode.  Try 'sqlite3 grow.db' 
instead.

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

Re: [sqlite] SQLite for .NET Framework 4.0

[Simon Slavin]

On 8 Jul 2010, at 11:24am, Daniel wrote:

> does anyone know if there is a support for SQLite in .NET Framework 4.0.

.NET is a set of library calls from a superset of C.  SQLite is a set of 
library calls from C.  The two are compatible: you can do both from the same 
piece of code.

Simon.
___
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 8 Jul 2010, at 9:30am, Max Vlasov wrote:

>> Actually the SSD possibility makes it worse, not better.
>> 
> 
> Simon, you gave an interesting explanation, but does this rule work in
> general? I mean there are many models, many sizes and so on.

Don't know.  You could test it.  Write a program that creates a file half the 
size of the drive, then writes to random parts of it timing each command.  If 
all the write commands take about the same amount of time then it doesn't work 
the way I described.  I just repeated the description I had read of the way SSD 
drives work.

>  For example
> SanDisk SSD used in my Asus T91MT claims it has some internal writing cache,
> so this controller can have its own logic working independently of the
> software installed.

That 'internal writing cache' is the logic I was describing.

> Also, allowing several chips writing at the same time
> might have conflict  with any OS'  own caching mechanism.

The OS would not know about this problem at all.  The OS doesn't know any thing 
about the internal organisation of the drive: its problem ends when the drive 
says "I have accepted this write command from you.".  After that it's the 
drive's responsibility to make sure that the data gets written.

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

[sqlite] SQLite for .NET Framework 4.0

[Daniel]

Hi All,

does anyone know if there is a support for SQLite in .NET Framework 4.0.

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

Re: [sqlite] WAL questions

[Alexey Pechnikov]

I see this too:

$ sqlite3 grow.db 'pragma journal_mode'
delete

$ hexdump -s 17 -n 2 grow.db   | head -n1
011 0200

$ sqlite3 grow.db 'pragma journal_mode=delete'
delete

$ hexdump -s 17 -n 2 grow.db  | head -n1
011 0100

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

[Max Vlasov]

> >> (I guess it well might not on an SSD disk, but on a conventional
> >> rotational disk, pager could read several pages ahead with one seek -
> >> but does it?)
> >
> >  No, the pager does not.  Among other things, my feeling is that the
> >  locality of pages is not very strong, unless the database was just
> >  VACUUMed.
>
> Actually the SSD possibility makes it worse, not better.
>

Simon, you gave an interesting explanation, but does this rule work in
general? I mean there are many models, many sizes and so on. For example
SanDisk SSD used in my Asus T91MT claims it has some internal writing cache,
so this controller can have its own logic working independently of the
software installed. Also, allowing several chips writing at the same time
might have conflict  with any OS'  own caching mechanism. Besides I'm not
sure the caching in any OS is smart enough to take this into account. For
example (I'm not sure Windows is the best) but giving the fact that XP
didn't have proper partition alignment for SSD and it took some time for
enthusiasts to let MS know about this makes me think the cache system in
Windows is still not smart enough even for much easier SSD-related tasks :)

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

[sqlite] Which SQLite API return SQLITE_BUSY?

[Lloyd]

Hi,

Which are all the type of SQLite APIs can return SQLITE_BUSY?

sqlite3_open_v2
sqlite3_prepare_v2
sqlite3_step
sqlite3_finalize
sqlite3_bind_blob
sqlite3_column_int

Thanks,
  Lloyd




__
Scanned and protected by Email scanner
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Problem to switch between WAL and delete journal modes

[Alexey Pechnikov]

Some times database can return journal_mode=delete when exists WAL journal:

$ ls|grep grow
grow.db
grow.db-shm
grow.db-wal

$ sqlite3
SQLite version 3.7.0
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> pragma journal_mode;
delete
sqlite> .q

$ ls|grep grow
grow.db
grow.db-shm
grow.db-wal

$ sqlite3 grow.db 'pragma journal_mode'
delete

$ ls|grep grow
grow.db


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