Re: [sqlite] Size query

[Stephen Chrzanowski]

But wouldn't you be aware of what the data is you want to transmit anyways?
Sure, it thinks as 1 as a length of 5, but, you'll know that you'll need
to send 4 or 8 bytes.

On Wed, Nov 3, 2010 at 6:50 PM, Scott A Mintz wrote:

> If x is numeric length(x) returns the length of x expressed as a string.
> So the value of length(1 ) is 5, not 2.
>
> -Scott
>
> sqlite-users-boun...@sqlite.org wrote on 11/03/2010 06:35:52 PM:
>
> > Couldn't you do something like:
> >
> > select length(FieldName) from TableName where Condition=True
> >
> > ?
> >
> > The result would be the size.  Otherwise, the only thing I can think of
> is
> > just doing a select to get the results you want, then just keep a
> running
> > tally on what would need to be transmitted, then do your packet math.
> >
> > On Wed, Nov 3, 2010 at 6:24 PM, Scott A Mintz
> wrote:
> >
> > > Is it possible to construct a query that will tell me the total size
> in
> > > bytes of the result set?  Specifically, in a messaging protocol that
> > > returns data we need to fragment the reply and it would be nice to
> know
> > > how much data or how many packets will be required to send the
> response.
> > >
> > > Thanks,
> > > Scott
> > > ___
> > > sqlite-users mailing list
> > > sqlite-users@sqlite.org
> > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> > >
> > ___
> > sqlite-users mailing list
> > sqlite-users@sqlite.org
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Size query

[Samuel Adam]

On Wed, 03 Nov 2010 19:17:48 -0400, Samuel Adam   
wrote:

> SQLite uses its own variable-length integer representation
> internally, occupying between 1 and 64 bits per value; if this is for a

Sorry to reply to my own post; I wish to be precise.  By “internally”, I  
meant (and should have said) “in the database file format”.  Which raises  
another question:  If you want the byte-length of an SQLite integer, do  
you mean as stored in the database, or as processed in-memory, typically  
via sqlite3_int64 and sqlite3_uint64?  Looking at the typedefs, these  
latter *could* compile to something bigger than 8 bytes on exotic  
platforms (although it would be difficult for them to be smaller).

Samuel Adam 
763 Montgomery Road
Hillsborough, NJ  08844-1304
United States
http://certifound.com/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Size query

[Samuel Adam]

On Wed, 03 Nov 2010 18:24:57 -0400, Scott A Mintz  
 wrote:

> Is it possible to construct a query that will tell me the total size in
> bytes of the result set?  Specifically, in a messaging protocol that
> returns data we need to fragment the reply and it would be nice to know
> how much data or how many packets will be required to send the response.

Per my other post, for TEXT values, you can CAST to BLOB and then use  
length().  Assuming the C API, I am guessing that it’s much more efficient  
play with sqlite3_column_bytes(), sqlite3_column_bytes16(),  
sqlite3_value_bytes(), and/or sqlite3_value_bytes16().  These will return  
byte-counts (with no zero terminator) for both TEXT and BLOB values.

As far as I can tell, you will need to count the bytes in numeric values  
yourself.  (I’ve looked into this before, and just glanced into it  
again.)  SQLite uses its own variable-length integer representation  
internally, occupying between 1 and 64 bits per value; if this is for a  
network protocol, you probably want to count the bytes in protocol  
representation rather than SQLite’s representation.

Counting the bytes (or lack thereof) in NULL values should probably also  
be done in your protocol’s representation.

Samuel Adam 
763 Montgomery Road
Hillsborough, NJ  08844-1304
United States
http://certifound.com/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Size query

[Samuel Adam]

On Wed, 03 Nov 2010 18:35:52 -0400, Stephen Chrzanowski  
 wrote:

> Couldn't you do something like:
>
> select length(FieldName) from TableName where Condition=True
>
> ?
>
> The result would be the size.  Otherwise, the only thing I can think of

Caution:  This will return the size in bytes of a BLOB field, but the size  
in *characters* of a TEXT field.  Except for lobotomized 7-bit-only text,  
the two will almost never be the same in a UTF-8 database.  If the  
database is encoded in UTF-16, there are exactly two bytes for every  
plane-0 character and exactly four bytes for every character in plane 1  
and up.

Casting to a BLOB will result in the text being simply reinterpreted as a  
BLOB, which is probably what the original poster wants.

If applied to a numeric (INTEGER or FLOAT) value, length() will first cast  
input to TEXT, then return the size in characters; since the cast will  
only return values <127, the result will be in bytes for UTF-8 and half  
the byte-size for UTF-16.

Casting to BLOB will not fix this, as the value is still first cast to  
TEXT.

Samuel Adam 
763 Montgomery Road
Hillsborough, NJ  08844-1304
United States
http://certifound.com/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Size query

[Scott A Mintz]

If x is numeric length(x) returns the length of x expressed as a string. 
So the value of length(1 ) is 5, not 2.

-Scott

sqlite-users-boun...@sqlite.org wrote on 11/03/2010 06:35:52 PM:

> Couldn't you do something like:
> 
> select length(FieldName) from TableName where Condition=True
> 
> ?
> 
> The result would be the size.  Otherwise, the only thing I can think of 
is
> just doing a select to get the results you want, then just keep a 
running
> tally on what would need to be transmitted, then do your packet math.
> 
> On Wed, Nov 3, 2010 at 6:24 PM, Scott A Mintz 
wrote:
> 
> > Is it possible to construct a query that will tell me the total size 
in
> > bytes of the result set?  Specifically, in a messaging protocol that
> > returns data we need to fragment the reply and it would be nice to 
know
> > how much data or how many packets will be required to send the 
response.
> >
> > Thanks,
> > Scott
> > ___
> > sqlite-users mailing list
> > sqlite-users@sqlite.org
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

Re: [sqlite] Size query

[Stephen Chrzanowski]

Couldn't you do something like:

select length(FieldName) from TableName where Condition=True

?

The result would be the size.  Otherwise, the only thing I can think of is
just doing a select to get the results you want, then just keep a running
tally on what would need to be transmitted, then do your packet math.

On Wed, Nov 3, 2010 at 6:24 PM, Scott A Mintz wrote:

> Is it possible to construct a query that will tell me the total size in
> bytes of the result set?  Specifically, in a messaging protocol that
> returns data we need to fragment the reply and it would be nice to know
> how much data or how many packets will be required to send the response.
>
> Thanks,
> Scott
> ___
> 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] Size query

[Scott A Mintz]

Is it possible to construct a query that will tell me the total size in 
bytes of the result set?  Specifically, in a messaging protocol that 
returns data we need to fragment the reply and it would be nice to know 
how much data or how many packets will be required to send the response.

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

Re: [sqlite] sqlite from fossil

[Benjamin Peterson]

Richard Hipp  writes:
> Try setting:
> 
>  fossil setting autosync off
> 
> before you do the
> 
>  fossil update

I actually get this from "fossil clone http://sqlite.org/src;, too.




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

Re: [sqlite] sqlite from fossil

[Richard Hipp]

On Wed, Nov 3, 2010 at 5:30 PM, Benjamin Peterson wrote:

> PF  writes:
>
> > You need to set:
> > fossil setting manifest on
>
> Thanks. Now "fossil update" says.
>
> fossil: server says: login failed
>
>
Try setting:

 fossil setting autosync off

before you do the

 fossil update




>
>
>
> ___
> 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] sqlite from fossil

[Benjamin Peterson]

PF  writes:

> You need to set:
> fossil setting manifest on

Thanks. Now "fossil update" says.

fossil: server says: login failed




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

Re: [sqlite] A question about transactions

[Igor Tandetnik]

Pavel Ivanov  wrote:
>> Yes. That's precisely the intended use case. Remember though that the 
>> transaction is not really committed until COMMIT statement
>> runs: if your application crashes or machine loses power, all changes to the 
>> beginning of the transaction are rolled back, not
>> just those since last "committed" checkpoint.  
> 
> Does my memory fool me or there are some error conditions in
> SQLite when it automatically (without explicit user request) rollbacks
> the whole transaction disregarding any savepoints?

I believe ON CONFLICT ROLLBACK (and its equivalents, like RAISE(ROLLBACK) ) 
would roll back the whole transaction.
-- 
Igor Tandetnik


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

Re: [sqlite] A question about transactions

[Pavel Ivanov]

> Yes. That's precisely the intended use case. Remember though that the 
> transaction is not really committed until COMMIT statement runs: if your 
> application crashes or machine loses power, all changes to the beginning of 
> the transaction are rolled back, not just those since last "committed" 
> checkpoint.

Does my memory fool me or there are some error conditions in
SQLite when it automatically (without explicit user request) rollbacks
the whole transaction disregarding any savepoints? If it's indeed the
case then OP should handle these situations in his class (if they are
possible in his SQLite usage pattern).


Pavel

On Wed, Nov 3, 2010 at 2:02 PM, Igor Tandetnik  wrote:
> jeff archer  wrote:
>> I am using SQLite from C++ and I have implemented a class to manager nested
>> transactions using savepoints. I have currently implemented as a stack of
>> transactions such that the first Begin uses BEGIN IMMEDIATE, while subsequent
>> levels use SAVEPOINT T where  is a sequentially increasing number
>> starting at 0001. Commit does RELEASE on the latest SAVEPOINT or a real 
>> COMMIT
>> once savepoint stack is cleared. Rollback does ROLLBACK TO or a real ROLLBACK
>> once savepoint stack is cleared.
>>
>> Is this OK to mix savepoints with transactions like this?
>
> Yes. That's precisely the intended use case. Remember though that the 
> transaction is not really committed until COMMIT statement runs: if your 
> application crashes or machine loses power, all changes to the beginning of 
> the transaction are rolled back, not just those since last "committed" 
> checkpoint.
> --
> Igor Tandetnik
>
>
> ___
> 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] A question about transactions

[Igor Tandetnik]

jeff archer  wrote:
> I am using SQLite from C++ and I have implemented a class to manager nested
> transactions using savepoints. I have currently implemented as a stack of
> transactions such that the first Begin uses BEGIN IMMEDIATE, while subsequent
> levels use SAVEPOINT T where  is a sequentially increasing number
> starting at 0001. Commit does RELEASE on the latest SAVEPOINT or a real COMMIT
> once savepoint stack is cleared. Rollback does ROLLBACK TO or a real ROLLBACK
> once savepoint stack is cleared.
> 
> Is this OK to mix savepoints with transactions like this?

Yes. That's precisely the intended use case. Remember though that the 
transaction is not really committed until COMMIT statement runs: if your 
application crashes or machine loses power, all changes to the beginning of the 
transaction are rolled back, not just those since last "committed" checkpoint.
-- 
Igor Tandetnik


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

[sqlite] Concurrency problem

[Yan Burman]

Hi.


I use sqlite in my code for a while now, and at first I parsed all queries each 
time, but now I'm migrating my code to using prepared statements (using 
sqlite3_prepare_v2
and binding parameters).
At some point I started receiving 'library routine called out of sequence' 
error every now and then while the application is running.
I run the code in multithreaded environment + once in a while the command line 
utility accesses the same database.
I use a single connection to the database and use a mutex to protect access to 
db.
I was using version 2.6.23.1 when the error occurred. Once I upgraded to 3.7.3 
the problem seem to have disappeared (without changing any of my code).
I went over my code and I could not find any problem (for example calling 
something without my mutex held).
Is it possible that I stumbled upon a bug in 2.6.23.1 that was fixed in 3.7.3, 
or do I still have a problem that is harder to reproduce?
I don't like problems that go away on their own.

Btw, I'm accessing the database in embedded linux environment from jffs2 
filesystem if that matters.
Also, I have a potential case where I may compile the same statement twice, but 
in theory this should not be a problem, since this is at most a one time 
resource leak.

I would really appreciate some help.

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

Re: [sqlite] Using sqlite's WAL with a hash table store

[Jay A. Kreibich]

On Wed, Nov 03, 2010 at 05:10:22PM +0300, Alexey Pechnikov scratched on the 
wall:
> 2010/11/3 Jay A. Kreibich 
> >
> >  Why not just use an off-the-self hash cache, like memcached, or an
> >  off-the-self hash database, like Redis?  Redis even supports an
> >  Append-Only write mode (e.g. WAL-ish journal mode).
> 
> How about power fail or OS crash? As I know, Redis does not garantees the
> data safety.

  This is getting a bit off topic, but quickly...

  No, by default Redis does not provide the level of protection a
  default file-backed SQLite database provides.  Redis's append-only
  mode does a pretty good job, however.  Redis will update journal
  with each command, and a sync is performed every second.  In theory,
  in the case of a power or OS crash, maximum data loss is right around
  one second worth of transactions.  Application crash will not result
  in data loss, since the OS still has valid file buffers that will
  eventually be flushed.  You can also configure things so that the
  journal syncs after each command, providing similar protection to
  SQLite.  That is, as you might expect, somewhat slow, however.

> And I think SQLite in-memory database is faster.

  Without testing, I'd guess Redis is faster for basic read/write
  operations.  Redis is also a proper server and allows multiple client
  connections, even for a fully memory based data set.

  By default Redis will hold all data in RAM, and is highly optimized
  for one-- and only one-- basic operation, while SQLite is supporting
  a much richer and more expressive data processing environment. 
  
  Each tool has its place, and they're not really trying to solve
  the same problem.

  The whole reason I'm looking to merge the two has to do with SQLite's
  expressive environment.  In designing a very high-performance app,
  the highly-optimized common-case queries can talk to Redis directly.
  This is fast, but requires jumping through a lot of hoops in the
  application code.  Conversely, the less frequent queries (including
  many of the management tasks) can talk to SQLite, which can then talk
  to Redis.  Use of the SQL language makes development MUCH faster for
  those operations that are not as performance-critical.

   -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

[sqlite] Problem with

[uncle.f]

Hello,

We are running SQLite 3.7.3 on an embedded device and using C API to
interact with the DB. One of our goals is to ensure that the database
on disk never grows past certain size. We open DB connection once and
it stays open for the whole duration of C application. The following
PRAGMAs are used to open the database:

page_size=1024
max_page_count=5120
count_changes=OFF
journal_mode=OFF
temp_store=MEMORY

When we hit the limit with the INSERT statement we get back
SQLITE_FULL, which is fine and is expected at some point. However, all
subsequent SELECTs or, in fact, any other DB interactions return
SQLITE_CORRUPT. That is until we close and re-open the same database
again, we can then SELECT,DELETE and UPDATE without a problem.

Is this intended behaviour?
Are we doing something wrong?

Thanks in advance,

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

Re: [sqlite] 'no such column' error returned in a CASE statement

[Ioannis Epaminonda]

Pavel Ivanov-2 wrote:
> 
> 
> Yes, it's expected. Column aliases are visible only in GROUP BY/ORDER
> BY/HAVING clauses and outer selects. All other places should use exact
> column expression instead.
> 
> Pavel
> 
> 

Ah, thanks Pavel for the clarification, now it makes sense.
This is a bit inconvenient but i guess i can use a VIEW for the second
select.

I noticed that the same applies for aggregate functions in the select
statement.

I was searching for some documentation in the sqlite site but didn't find
anything so i guess this is defined in the SQL standard.


-- 
View this message in context: 
http://old.nabble.com/%27no-such-column%27-error-returned-in-a-CASE-statement-tp30113686p30123189.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] Using sqlite's WAL with a hash table store

[Alexey Pechnikov]

2010/11/3 Jay A. Kreibich 
>
>  Why not just use an off-the-self hash cache, like memcached, or an
>  off-the-self hash database, like Redis?  Redis even supports an
>  Append-Only write mode (e.g. WAL-ish journal mode).
>

How about power fail or OS crash? As I know, Redis does not garantees the
data safety. And I think SQLite in-memory database is faster. I use
in-memory SQLite DB and dump (full or incrementally) periodically the DB on
disk and restore from disk on startup.

-- 
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] A question about transactions

[jeff archer]

I am using SQLite from C++ and I have implemented a class to manager nested 
transactions using savepoints.  I have currently implemented as a stack of 
transactions such that the first Begin uses BEGIN IMMEDIATE, while subsequent 
levels use SAVEPOINT T where  is a sequentially increasing number 
starting at 0001.  Commit does RELEASE on the latest SAVEPOINT or a real COMMIT 
once savepoint stack is cleared.  Rollback does ROLLBACK TO or a real ROLLBACK 
once savepoint stack is cleared.

Is this OK to mix savepoints with transactions like this?

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

Re: [sqlite] Using sqlite's WAL with a hash table store

[Ben Harper]

Interesting.. thanks.

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Jay A. Kreibich
Sent: 03 November 2010 03:44 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Using sqlite's WAL with a hash table store

On Wed, Nov 03, 2010 at 12:31:35PM +0200, Ben Harper scratched on the wall:
> I guess I could actually dump the hash table into a blob.

> I'm also doing something like a bloom filter, and I guess that can just
> as well go into a blob too.. Basically the system is a big cache,
> and it must quickly answer the question "Do you have this item in
> your cache?". The cache is going to receive a lot of queries for
> which the answer is "NO", and I need the determination of that
> answer to be fast.

  Why not just use an off-the-self hash cache, like memcached, or an
  off-the-self hash database, like Redis?  Redis even supports an
  Append-Only write mode (e.g. WAL-ish journal mode).

  If you really want to access it from inside SQLite, use a virtual
  table to wrap a Redis database.  I've been toying with this idea for
  a personal project.

   -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


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

[sqlite] [FTS4] Suggestions...

[Shopsland gmail]

Hi,

I saw in the timeline that Sqlite developers are working on FTS4.
Great news! :-)

I would like to suggest a couple of fixes.

The following syntax involving two or more words and double quotes
returns an error:

  ...MATCH 'Electric car -"general motors"'

  ...MATCH 'Electric car title:"general motors"'

In both cases we get the following database error:

  "Database error: SQL logic error or missing database"

Maybe those are not too complex fix and they would add a lot more
power to FTS syntax.

Thank you and keep up the good work! :-)

Jochi Martínez
www.bfreenews.com
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Using sqlite's WAL with a hash table store

[Jay A. Kreibich]

On Wed, Nov 03, 2010 at 12:31:35PM +0200, Ben Harper scratched on the wall:
> I guess I could actually dump the hash table into a blob. 

> I'm also doing something like a bloom filter, and I guess that can just
> as well go into a blob too.. Basically the system is a big cache,
> and it must quickly answer the question "Do you have this item in
> your cache?". The cache is going to receive a lot of queries for
> which the answer is "NO", and I need the determination of that
> answer to be fast.

  Why not just use an off-the-self hash cache, like memcached, or an
  off-the-self hash database, like Redis?  Redis even supports an
  Append-Only write mode (e.g. WAL-ish journal mode).

  If you really want to access it from inside SQLite, use a virtual
  table to wrap a Redis database.  I've been toying with this idea for
  a personal project.

   -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] filling an in-memory database from a byte stream?

[Richard Hipp]

On Tue, Nov 2, 2010 at 12:04 PM, Niemann, Hartmut <
hartmut.niem...@siemens.com> wrote:

> Hello *!
>
> I am investigating using sqlite (with Java) as a readonly database in a
> viewer application.
> One problem is that the database can reside not only on the local hard
> disc, but also on a
> possibly slowly connected remote system that can be contacted only via ftp
> or some
> proprietary interface.
>
> I found two solutions.
> I could copy remote databases to a temporary place on the local disk and
> open them there.
> Or I create and fill an in-memory database, but the online backup api uses
> two database
> objects, i.e. it can not read from something that is not
> a database.
>
> Would it also be possible to create an in-memory database and fill it from
> a byte stream or a file?
>

You cannot populate an in-memory database from a data stream.

But you can write a really simple VFS (http://www.sqlite.org/c3ref/vfs.html)
that implements a "filesystem" consisting of a single read-only file
contained in memory, then register this VFS using sqlite3_vfs_register()
then load your remote database into memory that the VFS uses as its one
file.

-- 
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] filling an in-memory database from a byte stream?

[Niemann, Hartmut]

Hello *!

I am investigating using sqlite (with Java) as a readonly database in a viewer 
application.
One problem is that the database can reside not only on the local hard disc, 
but also on a
possibly slowly connected remote system that can be contacted only via ftp or 
some
proprietary interface.

I found two solutions.
I could copy remote databases to a temporary place on the local disk and open 
them there.
Or I create and fill an in-memory database, but the online backup api uses two 
database
objects, i.e. it can not read from something that is not
a database.

Would it also be possible to create an in-memory database and fill it from a 
byte stream or a file?

(There is no need for the opposite direction. The in-memory database is used 
read-only and
goes to the bit bucket at program end.)


Mit freundlichen Grüßen
Dr. Hartmut Niemann

Siemens AG
Industry Sector
Mobility Division
Rolling Stock
I MO RS LC EN LE 8
Werner-von-Siemens-Str. 67
91052 Erlangen, Deutschland
Tel.: +49 (9131) 7-34264
Fax: +49 (9131) 7-26254
mailto:hartmut.niem...@siemens.com

Siemens Aktiengesellschaft: Vorsitzender des Aufsichtsrats: Gerhard Cromme; 
Vorstand: Peter Löscher, Vorsitzender; Wolfgang Dehen, Brigitte Ederer, Joe 
Kaeser, Barbara Kux, Hermann Requardt, Siegfried Russwurm, Peter Y. Solmssen; 
Sitz der Gesellschaft: Berlin und München, Deutschland; Registergericht: Berlin 
Charlottenburg, HRB 12300, München, HRB 6684; WEEE-Reg.-Nr. DE 23691322

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

Re: [sqlite] Using sqlite's WAL with a hash table store

[Ben Harper]

Thanks - I didn't think of using that. Maybe it's a good fit.

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Alexey Pechnikov
Sent: 03 November 2010 11:51 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Using sqlite's WAL with a hash table store

FTS3 extension is very fast and scalable hash engine. I did test FTS3 up to
400+ millions of record and it's nice.

2010/11/3 Ben Harper 

> Hi,
> I know the answer to this question is really "Just try it and see", but I
> want to gauge whether the idea is sane or not before I spend/waste time on
> the effort:
>
> I want to build a custom hash table DB, and to solve the
> concurrency+durability I need something akin to a WAL, and SQLite's WAL
> seems like a perfect fit. I've looked into the wal.c/wal.h a bit and from my
> brief perusal it looks like I could quite easily strap the SQLite WAL onto
> my custom hash table DB.
>
> Does this sound like a reasonable thing to do?
> Am I going to have to do a lot of work to spoof the WAL logic, or is it
> made to run pretty much ignorant of the file that it is WAL'ing against?
>
> Thanks,
> Ben
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



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


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

Re: [sqlite] Using sqlite's WAL with a hash table store

[Ben Harper]

I guess I could actually dump the hash table into a blob. I'm also doing 
something like a bloom filter, and I guess that can just as well go into a blob 
too.. Basically the system is a big cache, and it must quickly answer the 
question "Do you have this item in your cache?". The cache is going to receive 
a lot of queries for which the answer is "NO", and I need the determination of 
that answer to be fast. That's why I've got a bloom filter-ish thing going 
which performs that task. Even for a large cache (500k entries), one can keep 
the entire filter in about 2MB of memory and have less than 1% false positive 
rate. Unfortunately I can't think of an equivalent data structure that would 
use B+Tree linear indices to achieve that.

I've done a naive implementation of what I want using straight sqlite, but the 
performance is not really adequate. It's hard to quantify exactly where the 
bottlenecks lie though, which is what motivates me to try something else and 
see what kind of performance delta I get.

Thanks.


-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Simon Slavin
Sent: 03 November 2010 11:17 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Using sqlite's WAL with a hash table store


On 3 Nov 2010, at 8:30am, Ben Harper wrote:

> I know the answer to this question is really "Just try it and see", but I 
> want to gauge whether the idea is sane or not before I spend/waste time on 
> the effort:
>
> I want to build a custom hash table DB, and to solve the 
> concurrency+durability I need something akin to a WAL, and SQLite's WAL seems 
> like a perfect fit. I've looked into the wal.c/wal.h a bit and from my brief 
> perusal it looks like I could quite easily strap the SQLite WAL onto my 
> custom hash table DB.

Modifying SQL, and taking SQL source code and putting it into your own project, 
are difficult and time-consuming.  As a prototype why not /use/ SQL, storing 
your hash codes in a column ?  Use that as a prototype and see if it's fast 
enough.  If it is, stop there.

If you find calculating your hashes externally proves too clunky, you could 
write a custom function to calculate your hash codes

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

, or you could remove the extra column but implement your hash codes as a 
collating sequence:

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

Any of the three above ways to do it gets you all the advantages of the WAL 
code /and/ a SQL engine.

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] Using sqlite's WAL with a hash table store

[Alexey Pechnikov]

FTS3 extension is very fast and scalable hash engine. I did test FTS3 up to
400+ millions of record and it's nice.

2010/11/3 Ben Harper 

> Hi,
> I know the answer to this question is really "Just try it and see", but I
> want to gauge whether the idea is sane or not before I spend/waste time on
> the effort:
>
> I want to build a custom hash table DB, and to solve the
> concurrency+durability I need something akin to a WAL, and SQLite's WAL
> seems like a perfect fit. I've looked into the wal.c/wal.h a bit and from my
> brief perusal it looks like I could quite easily strap the SQLite WAL onto
> my custom hash table DB.
>
> Does this sound like a reasonable thing to do?
> Am I going to have to do a lot of work to spoof the WAL logic, or is it
> made to run pretty much ignorant of the file that it is WAL'ing against?
>
> Thanks,
> Ben
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



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

Re: [sqlite] Using sqlite's WAL with a hash table store

[Simon Slavin]

On 3 Nov 2010, at 8:30am, Ben Harper wrote:

> I know the answer to this question is really "Just try it and see", but I 
> want to gauge whether the idea is sane or not before I spend/waste time on 
> the effort:
> 
> I want to build a custom hash table DB, and to solve the 
> concurrency+durability I need something akin to a WAL, and SQLite's WAL seems 
> like a perfect fit. I've looked into the wal.c/wal.h a bit and from my brief 
> perusal it looks like I could quite easily strap the SQLite WAL onto my 
> custom hash table DB.

Modifying SQL, and taking SQL source code and putting it into your own project, 
are difficult and time-consuming.  As a prototype why not /use/ SQL, storing 
your hash codes in a column ?  Use that as a prototype and see if it's fast 
enough.  If it is, stop there.

If you find calculating your hashes externally proves too clunky, you could 
write a custom function to calculate your hash codes

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

, or you could remove the extra column but implement your hash codes as a 
collating sequence:

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

Any of the three above ways to do it gets you all the advantages of the WAL 
code /and/ a SQL engine.

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

[sqlite] Using sqlite's WAL with a hash table store

[Ben Harper]

Hi,
I know the answer to this question is really "Just try it and see", but I want 
to gauge whether the idea is sane or not before I spend/waste time on the 
effort:

I want to build a custom hash table DB, and to solve the concurrency+durability 
I need something akin to a WAL, and SQLite's WAL seems like a perfect fit. I've 
looked into the wal.c/wal.h a bit and from my brief perusal it looks like I 
could quite easily strap the SQLite WAL onto my custom hash table DB.

Does this sound like a reasonable thing to do?
Am I going to have to do a lot of work to spoof the WAL logic, or is it made to 
run pretty much ignorant of the file that it is WAL'ing against?

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