Re: [sqlite] sqlite3_bind_int64() on Solaris 10?

[Pam Li]

 Our debug build works with CC compiler, while release build does not.  
Eventually we found out that it was "-fast" (specifically, 
"-xalias_level=basic" in "-fast") CC compiler option, that causes problems.   
We are wondering what GCC 4.3.3 compiler options were used for testing on 
Solaris 5.10 SPARC architecture. Thanks a lot,pam
 > From: d...@sqlite.org
> Date: Fri, 29 Jun 2012 16:26:10 -0400
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] sqlite3_bind_int64() on Solaris 10?
> 
> On Fri, Jun 29, 2012 at 4:22 PM, Pam Li  wrote:
> 
> >
> > Thanks for the qucik answer.  Looks like our compilers are different.  We
> > use CC.  Which version of GCC do you use? Thanks again,pam
> >
> 
> Tested with GCC 4.3.3.
> 
> 
> 
> >  > From: d...@sqlite.org
> > > Date: Fri, 29 Jun 2012 15:51:35 -0400
> > > To: sqlite-users@sqlite.org
> > > Subject: Re: [sqlite] sqlite3_bind_int64() on Solaris 10?
> > >
> > > FWIW, every recent release of SQLite has been tested on SunOS 5.10
> > (Sparc)
> > > using GCC.  That doesn't really answer your question (I don't know the
> > real
> > > answer) but it does at least demonstrate that SQLite has been made to
> > work
> > > correctly on Sparc and on SunOS.
> > >
> > > On Fri, Jun 29, 2012 at 3:17 PM, Pam Li  wrote:
> > >
> > > >
> > > >
> > > >
> > > >
> > > >
> > > >
> > > >
> > > > Hello,  We are using SQLITE 3.7.10 C/C++ interface API in our product.
> > > >  This is the very first time we use it.   Our product runs on Windows,
> > > > Linux and Solaris. We ran into a problem that a query on a 64 bit
> > integer
> > > > key is successful on both Windows and Linux, but not on Solaris 10.
> > The
> > > > code, database, and input data for the query are the same fro all
> > > > platforms. Query: select * from  where MAC_ADDR in (?1, ?2.
> > ?3); On
> > > > Solaris, if the MAC_ADDRESS value is 4 byte or less, a query is
> > successful.
> > > >  If its value is 5-8 bytes, the query fails. API sequence we use for
> > the
> > > > query: ===sqlite3_prepare_v2()
> > sqlite3_bind_int64()
> > > > sqlite3_step() ... ===  Has anyone else had
> > this
> > > > issue?  Any clue is appreciated.  Best regards,pam
> > > >
> > > > ___
> > > > 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
> >
> 
> 
> 
> -- 
> 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] max size of a TEXT field

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 02/07/12 13:20, Jay A. Kreibich wrote:
> By default, 10^9 bytes (~1GB).  Can be altered, but there is a hard 
> limit of (2^31 - 1), or 2GB.

Even more subtleties than that.  SQLite's internals are fundamentally 32
bits even on 64 bit platforms.  For example the APIs that pass data around
use 'int' which is 32 bit even on 64 bit platforms (ie the limits don't
change just by using 64 bits).

You can ask for data in UTF16 which uses a minimum of 2 bytes per
codepoint while UTF8 (the default) uses a minimum of one byte per
codepoint.  Consequently if you had a 1.1 billion codepoints in the
database itself (stored in UTF8), then you couldn't retrieve is as UTF16
because it would exceed signed 32 bit ints being used.  Lest you think
everyone is UTF8, the "normal" size for Java and Windows is UTF16.

Also the worst case for UTF8 is 6 bytes per codepoint, although they are rare.

The net consequence is that the largest size is really 1 billion
codepoints, otherwise some platforms using UTF16 will have problems.  And
if you want to be absolutely certain then the maximum is one sixth of that.

For the folks who don't know what codepoints are, or what the Unicode fuss
is then read this article titled "The Absolute Minimum Every Software
Developer Absolutely, Positively Must Know About Unicode and Character Sets"

  http://www.joelonsoftware.com/articles/Unicode.html

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

iEYEARECAAYFAk/yDNUACgkQmOOfHg372QR9twCeOP3rtAc1nbQ0gHFYL8Y97y+H
U+0AoKYpYW13yfnyKEExq2t+tgUQ+Ppb
=1cpR
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Consequences of lexicographic sorting of keys in SQLite4?

[Steven E. Harris]

Richard Hipp  writes:

> The keys are encoded (see
> http://www.sqlite.org/src4/doc/trunk/www/key_encoding.wiki) in a way
> that causes a lexicographical ordering of the keys to correspond to
> what the user wants out of ORDER BY.

I don't understand why the example entries for values 99.0, 99.01, and
99.0001 have first bytes of 0xb4 and 0xb5, respectively.

If we take 99 and use it as "X" in the "2*X+0" formula, shouldn't we
wind up with 198, or 0xC6? Likewise, for the second two examples, I'd
expect 2*99+1 to yield 199, or 0xC7. (The later entry for  has bytes
0xC7 and 0xC6, which match my expectation.)

One more nit: In the paragraph beginning with "If the numeric value is
exactly zero," there's an extra "then" which should have been "the"
instead:

  s/then then/then the/

Thank you for documenting all of this. It's ugly for a good reason,
which makes it beautiful at the same time.

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

Re: [sqlite] max size of a TEXT field

[Jay A. Kreibich]

On Mon, Jul 02, 2012 at 10:02:29PM +0200, deltagam...@gmx.net scratched on the 
wall:
> Hello,
> 
> I couldnt find in the documentation what the max size of TEXT
> field/column is.

  First item:  http://sqlite.org/limits.html#max_length

  By default, 10^9 bytes (~1GB).  Can be altered, but there is a hard
  limit of (2^31 - 1), or 2GB.

   -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] max size of a TEXT field

[Pavel Ivanov]

On Mon, Jul 2, 2012 at 4:02 PM, deltagam...@gmx.net  wrote:
> Hello,
>
> I couldnt find in the documentation what the max size of TEXT field/column
> is.

http://www.sqlite.org/limits.html#max_length

By default it's 10^9 bytes and cannot be more than 2^31 - 1.


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

Re: [sqlite] max size of a TEXT field

[Igor Tandetnik]

On 7/2/2012 4:02 PM, deltagam...@gmx.net wrote:

I couldnt find in the documentation what the max size of TEXT
field/column is.


There's no set limit. As much as can fit into memory, basically.
--
Igor Tandetnik

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

[sqlite] max size of a TEXT field

[deltagam...@gmx.net]

Hello,

I couldnt find in the documentation what the max size of TEXT 
field/column is.


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

Re: [sqlite] IP address support (was: Consequences of lexicographic sorting of keys in SQLite4?)

[Nico Williams]

On Mon, Jul 2, 2012 at 1:14 PM, Simon Slavin  wrote:
> Your argument is for SQLite to allow users to implement their own affinities 
> (datatypes ?) must like SQLite3 allows users to implement collation 
> algorithms and functions.  But I don't think you're going to get something as 
> rarely used in SQLite4 when it's specially designed to be tiny and light.  
> SQLite stays that way by refusing to add rarely-used facilities.

No simple user-defined type is likely to allow for user-defined
literals for user-defined types.

What does matter, I think, is how commonly used some type might be.
IP addresses are very commonly used in relational DBs.

> But I still question whether you're engaged in premature optimization.  Would 
> things really be that much slower if you stored IPv4/6 as Higits ?  IPv4 
> stored as hex is readable, sortable, can be transferred from place to place 
> as an 8 character string and works perfectly with the standard distribution 
> of SQLite3 with no changes.  Implement conversion in- and out- functions 
> either as SQLite functions or in your programming language, as suits you.  
> You're concerned about the performance you lose by storing 10 octet strings 
> instead of having a custom type ?  Possibly if you're running a major ISP or 
> doing packet analysis and logging for one.  But you wouldn't be using SQLite 
> to do that anyway, you'd be running something with built-in caching and 
> redundancy.

Hex is not good enough for CIDR blocks (i.e., bit strings of length <=
sizeof(address-type)), unless you encode the bit string length mod 8
into the last byte, with appropriate zero-bit padding.  Then it's good
enough, but hardly user-friendly.

IP addresses are useful in databases that store configuration
information.  NICs, for example, must have such databases, and any
sufficiently large network is going to need one too.  I've seen a
number of home-grown Moira-like databases, and I maintained one called
UName*It (which used UniSQL/X underneath).  Any organization with
home-grown DNS management is going to need this, and any open source
and/or commercial DNS management tools will need this under the hood.
IP addresses are really quite common in databases.  This is why
Postgres has native IP address support.

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

Re: [sqlite] IP address support (was: Consequences of lexicographic sorting of keys in SQLite4?)

[Simon Slavin]

On 2 Jul 2012, at 6:27pm, Nico Williams  wrote:

> A pair of built-in functions could take care of the user-friendliness
> aspect to some degree, but built-in literals for bit string and IPv4/6
> CIDR notation would so much more user-friendly...  IMO it's worth
> doing.  It's really quite common to store IP addresses in relational
> databases...

Your argument is for SQLite to allow users to implement their own affinities 
(datatypes ?) must like SQLite3 allows users to implement collation algorithms 
and functions.  But I don't think you're going to get something as rarely used 
in SQLite4 when it's specially designed to be tiny and light.  SQLite stays 
that way by refusing to add rarely-used facilities.

But I still question whether you're engaged in premature optimization.  Would 
things really be that much slower if you stored IPv4/6 as Higits ?  IPv4 stored 
as hex is readable, sortable, can be transferred from place to place as an 8 
character string and works perfectly with the standard distribution of SQLite3 
with no changes.  Implement conversion in- and out- functions either as SQLite 
functions or in your programming language, as suits you.  You're concerned 
about the performance you lose by storing 10 octet strings instead of having a 
custom type ?  Possibly if you're running a major ISP or doing packet analysis 
and logging for one.  But you wouldn't be using SQLite to do that anyway, you'd 
be running something with built-in caching and redundancy.

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

Re: [sqlite] Consequences of lexicographic sorting of keys in SQLite4?

[Cory Nelson]

On Fri, Jun 29, 2012 at 5:58 PM, Richard Hipp  wrote:
>
> On Fri, Jun 29, 2012 at 6:40 PM, Nico Williams wrote:
>
> > On Fri, Jun 29, 2012 at 5:24 PM, Richard Hipp  wrote:
> > > varint+value does not sort BLOBs in lexicographical order.
> > >
> > > Not having a distinct terminator for the BLOB means that two BLOBs where
> > > one is a prefix of the other might not compare correctly.
> >
> > Would 31-bit encoding help?
> >
>
> Maybe.
>
> But you know:  How often do people use BLOBs as keys?  What other SQL
> engines other than SQLite even allow BLOBs as keys?  Are we trying to
> optimize something that is never actually used?
>

128-bit GUIDs as a primary key are very common in cases where records
are to be synced between two (often disconnected) databases.
Admittedly 3 bytes of overhead in this case is probably not going to
be a huge deal, but if a 19% overhead can be avoided early in the
design, why not?

Okay. I'm done bike shedding ;)

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

Re: [sqlite] Consequences of lexicographic sorting of keys in SQLite4?

[Niall O'Reilly]

On 2 Jul 2012, at 18:20, Jay A. Kreibich wrote:

>  The idea of using a plugin system to expand database functionality
>  seems to fit well with the SQLite way of getting things done.
>  Functions, collations, and virtual tables are already done in a
>  similar way.  Extending that to types seems like a natural thing.

Indeed.

>  You can, of course, use a user-defined function that just converts a
>  string to a BLOB of some type.  As long as you use the encoder function
>  for inputs and the decoder for all outputs, you should be good.

Functionally, although involving more overhead, a collation
is enough.  The combination of encoder and decoder obviates
repeated references to the collation function for ORDER BY,
BETWEEN, and so on.

>  That
>  starts to get deep into your SQL, however.  The ability to define
>  native types is similar in complexity to adding user-defined
>  functions.
> 
>  Just a thought.  Any opinions?

/Niall

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

Re: [sqlite] IP address support (was: Consequences of lexicographic sorting of keys in SQLite4?)

[Nico Williams]

On Mon, Jul 2, 2012 at 12:11 PM, Niall O'Reilly  wrote:
> On 2 Jul 2012, at 17:52, Nico Williams wrote:
>> So an IPv4 CIDR block like 10.2.93.128/25 would encode as x'0A025D81'
>> and 10.2.93.128/26 as x'0A025D82', and so on, with 10.2.93.128/32
>> encoded as x'0A025D8000' (that's 5 bytes).  That is, IPv4 addresses
>> would require one more byte than usual.
>
> You're missing some cases which I would find indispensible.
> I have a trip tomorrow.  I may be able to use the plane time
> to think about your examples above and to put together some
> complementary ones of my own.

Well, encoding bit strings (e.g., IP CIDR notation) as BLOBs is not at
all user-friendly.  But it does work for sorting.

A pair of built-in functions could take care of the user-friendliness
aspect to some degree, but built-in literals for bit string and IPv4/6
CIDR notation would so much more user-friendly...  IMO it's worth
doing.  It's really quite common to store IP addresses in relational
databases...

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

Re: [sqlite] Consequences of lexicographic sorting of keys in SQLite4?

[Jay A. Kreibich]

On Mon, Jul 02, 2012 at 10:13:13AM -0500, Nico Williams scratched on the wall:

> That reminds me: it'd be nice to have a bit string type, since the
> correct way to sort IPv4 CIDR blocks is as bit strings.  This is also
> a proper way to sort IPv6 blocks.  Alternatively, it'd be nice to have
> native IP address types in SQLite4, as otherwise one has to jump
> through hoops to handle IP addresses properly.


  I'd suggest something bigger, as long as we're putting a lot of
  options on the table.

  Postgres supports user-defined types on the server side.  This is now
  most of their slightly esoteric types (such as CIDR addresses) are
  supported internally.  To define a type, the server developer writes a
  server-side plugin that provides a few functions to the server.
  Required functions convert the in-memory representation of the type
  to/from strings (for SQL input/output) and also convert the in-memory
  representation to/from a bit stream for storing on disk.  I think you
  can also provide a sort function.

http://www.postgresql.org/docs/9.0/static/xtypes.html

  The idea of using a plugin system to expand database functionality
  seems to fit well with the SQLite way of getting things done.
  Functions, collations, and virtual tables are already done in a
  similar way.  Extending that to types seems like a natural thing.

  You can, of course, use a user-defined function that just converts a
  string to a BLOB of some type.  As long as you use the encoder function
  for inputs and the decoder for all outputs, you should be good.  That
  starts to get deep into your SQL, however.  The ability to define
  native types is similar in complexity to adding user-defined
  functions.

  Just a thought.  Any opinions?

   -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] IP address support (was: Consequences of lexicographic sorting of keys in SQLite4?)

[Niall O'Reilly]

On 2 Jul 2012, at 17:52, Nico Williams wrote:

> So an IPv4 CIDR block like 10.2.93.128/25 would encode as x'0A025D81'
> and 10.2.93.128/26 as x'0A025D82', and so on, with 10.2.93.128/32
> encoded as x'0A025D8000' (that's 5 bytes).  That is, IPv4 addresses
> would require one more byte than usual.

You're missing some cases which I would find indispensible.
I have a trip tomorrow.  I may be able to use the plane time
to think about your examples above and to put together some
complementary ones of my own.

/Niall

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

Re: [sqlite] IP address support (was: Consequences of lexicographic sorting of keys in SQLite4?)

[Nico Williams]

So an IPv4 CIDR block like 10.2.93.128/25 would encode as x'0A025D81'
and 10.2.93.128/26 as x'0A025D82', and so on, with 10.2.93.128/32
encoded as x'0A025D8000' (that's 5 bytes).  That is, IPv4 addresses
would require one more byte than usual.

I'm not sure that we can justify the extra complexity for a native bit
string type, but fwiw the literal syntax for it should probably be the
same as blob, with a / at the end.  Still, native bit
string support would be handy.

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

Re: [sqlite] IP address support (was: Consequences of lexicographic sorting of keys in SQLite4?)

[Nico Williams]

Ah, if you encode any bit string as a BLOB such that it ends in 3 bits
that encode the length of the string mod 8, and with 7 - length of
string mod 8 preceding zero-valued bits then you get a result that
should sort [lexicographically] correctly, no?

So bit string would be a trivial extension of BLOB, and possibly no
special support should be required in SQLite (3 or 4).

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

Re: [sqlite] IP address support (was: Consequences of lexicographic sorting of keys in SQLite4?)

[Nico Williams]

The key is to come up with a bit string encoding in bytes that is
suitable for use in table keys -- they have to sort correctly when
sorted lexicographically.  The encoding should be reasonably
efficient; one byte per-bit, for example, would be too inefficient
(though in a pinch much better than no bit string support).  I'm
thinking of a 7 bits / byte encoding, with the remaining indicating
the last byte of the encoding, which byte would contain up to 4 bits
of the bit string plus 3 bits to encode the length of the bit string
mod 8.

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

[sqlite] IP address support (was: Consequences of lexicographic sorting of keys in SQLite4?)

[Niall O'Reilly]

On 2 Jul 2012, at 16:13, Nico Williams wrote:

> That reminds me: it'd be nice to have a bit string type, since the
> correct way to sort IPv4 CIDR blocks is as bit strings.

Nice, definitely!

> This is also
> a proper way to sort IPv6 blocks.  Alternatively, it'd be nice to have
> native IP address types in SQLite4, as otherwise one has to jump
> through hoops to handle IP addresses properly.

Bit strings would be more general.
Native IP would remove a sometimes-asserted motivation for preferring
PostgreSQL.

As I see it, ranges, as well as single addresses and CIDR prefixes, 
need to be supported, perhaps like the Perl Net::IP module does.

With some care over the encoding, a natural ordering arises which
places nested prefixes, ranges, and individual addresses in the
"right" order.  This would eliminate as much as possible of the
hoop-jumping.

I'll try to put together some examples of as illustrations.

/Niall

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

Re: [sqlite] Consequences of lexicographic sorting of keys in SQLite4?

[Nico Williams]

On Mon, Jul 2, 2012 at 4:29 AM, Niall O'Reilly  wrote:
>
> On 29 Jun 2012, at 23:58, Richard Hipp wrote:
>
>> But you know:  How often do people use BLOBs as keys?  What other SQL
>> engines other than SQLite even allow BLOBs as keys?  Are we trying to
>> optimize something that is never actually used?
>
> For an IPAM application I have on my back burner, BLOB seems
> a natural way to express IPv[46] addresses, ranges, and prefixes.
> A bulkier alternative would be hexadecimal encoding as text.

That reminds me: it'd be nice to have a bit string type, since the
correct way to sort IPv4 CIDR blocks is as bit strings.  This is also
a proper way to sort IPv6 blocks.  Alternatively, it'd be nice to have
native IP address types in SQLite4, as otherwise one has to jump
through hoops to handle IP addresses properly.

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

Re: [sqlite] database busy error

[Kevin Martin]

> If I have a pool of external (c++) progs, all writing into the same sqlit3 db,
> how do i handle this correctly ?


I think it depends on your applications.

We use sqlite for IPC and data sharing within processes on the same machine. 
I'm not suggesting it for you, but the following works for us:

o We do all our modifications within exclusive transactions (by this I mean we 
do any selects, calculations, updates, inserts which are required for the 
modification within a single exclusive transaction) 

o We work under the assumption that the db will become unblocked eventually.

o We have a few attempts to begin exclusive (5 I think) with random pauses 
between them. If we still haven't locked it after that we tell nagios of this 
fact which alerts us something is wrong then we enter an infinite loop 
attempting to get the lock.

The only time we've ever had nagios alerts was due to filesystem/SAN issues. 
Under normal working we've never had a problem. There are probably around 5 
small writes/second to the database.

Thanks,
Kev

On 2 Jul 2012, at 14:13, deltagam...@gmx.net wrote:

> Now, sometimes I get db busy error (5)
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 

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

Re: [sqlite] database busy error

[Stephan Beal]

On Mon, Jul 2, 2012 at 3:13 PM, deltagam...@gmx.net wrote:

> If I have a pool of external (c++) progs, all writing into the same sqlit3
> db,
> how do i handle this correctly ?
> Now, sometimes I get db busy error (5)
>

See: http://sqlite.org/lockingv3.html

Search for SQLITE_BUSY to see what can cause that. Other docs (which i
can't seem to find at the moment) explain how to deal with BUSY in your app.

-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] database busy error

[deltagam...@gmx.net]

If I have a pool of external (c++) progs, all writing into the same 
sqlit3 db,

how do i handle this correctly ?
Now, sometimes I get db busy error (5)
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Blobs and ordering [was: Consequences of lexicographic sorting of keys in SQLite4?]

[Niall O'Reilly]

Simon,

Thanks for your considered comments.

On 2 Jul 2012, at 12:20, Simon Slavin wrote:

> Worth remembering that BLOBs don't have a well-ordering function.  You can 
> compare two BLOBs and tell whether they're the same (usually, but lossless 
> encoding defeats this), but if they're not the same you can't put one 
> 'before' the other.

OK, in the general case.

> This is because BLOBs are essentially black boxes.  You have no idea what the 
> data represents.

If I'm responsible for the data, I can take care that applying memcmp()
to two BLOBs is meaningful.

>  If you know what it represented, you'd probably be storing it as text or a 
> number.

I'm not sure I can depend on having 128-bit unsigned integers available.

Notational options make normalization necessary for text.  With BLOB, I 
can
use the result from inet_pton(); with TEXT, I have to apply inet_ntop() 
to
the result of inet_pton().  Old-school parsimony makes me disinclined to
do this.  Perhaps I need to lighten up?

>  Think of storing images as BLOBs.  How do you compare two images ?

I don't think the analogy applies.  
Images belong to a different specialization of the same base class.

Thanks again,

Niall O'Reilly

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

Re: [sqlite] Consequences of lexicographic sorting of keys in SQLite4?

[Simon Slavin]

On 2 Jul 2012, at 10:29am, Niall O'Reilly  wrote:

> On 29 Jun 2012, at 23:58, Richard Hipp wrote:
> 
>> But you know:  How often do people use BLOBs as keys?  What other SQL
>> engines other than SQLite even allow BLOBs as keys?  Are we trying to
>> optimize something that is never actually used?
> 
>   For an IPAM application I have on my back burner, BLOB seems
>   a natural way to express IPv[46] addresses, ranges, and prefixes.
>   A bulkier alternative would be hexadecimal encoding as text.

Strikes me as premature optimisation.  Storing them as strings of decimal or 
hex (with, of course, leading zeros) would allow you to sort them meaningfully, 
take substrings meaningfully, and to understand the contents of your file when 
displayed using debugging tools.  If you do that, and the results turn out to 
be too slow for your user(s), /then/ revisit ideas of making things faster or 
more compact.

Worth remembering that BLOBs don't have a well-ordering function.  You can 
compare two BLOBs and tell whether they're the same (usually, but lossless 
encoding defeats this), but if they're not the same you can't put one 'before' 
the other.

This is because BLOBs are essentially black boxes.  You have no idea what the 
data represents.  If you know what it represented, you'd probably be storing it 
as text or a number.  Think of storing images as BLOBs.  How do you compare two 
images ?  Is one before another because it is smaller ?  Or because it contains 
darker pixels (lower brightness) ?  Or because the EXIF information says it was 
taken on an earlier date ?  Or because it's an earlier frame in the animation 
you're making ?

So if a function like building an index requires an ordering function, you 
can't use it on a BLOB.  Now, as it happens, BLOBs are stored as octets, and if 
the functionality is presented there's no harm in sorting them as if they're 
octet-streams.  But it doesn't really mean anything.

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

Re: [sqlite] Consequences of lexicographic sorting of keys in SQLite4?

[Niall O'Reilly]

On 2 Jul 2012, at 10:51, Dan Kennedy wrote:

> That would be a reasonable use. But the blob in this case will be what,
> eight bytes (or 10 in its encoded form)?

10, 18, 34, or 66, depending on which of six classes [*] of object
is involved, using the encoding I have in mind at the moment.
Still small.

* 2x address families, 3x kinds of object (address, prefix, range).

/Niall

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

Re: [sqlite] Consequences of lexicographic sorting of keys in SQLite4?

[Dan Kennedy]

On 07/02/2012 04:29 PM, Niall O'Reilly wrote:


On 29 Jun 2012, at 23:58, Richard Hipp wrote:


But you know:  How often do people use BLOBs as keys?  What other SQL
engines other than SQLite even allow BLOBs as keys?  Are we trying to
optimize something that is never actually used?


For an IPAM application I have on my back burner, BLOB seems
a natural way to express IPv[46] addresses, ranges, and prefixes.
A bulkier alternative would be hexadecimal encoding as text.


That would be a reasonable use. But the blob in this case will be what,
eight bytes (or 10 in its encoded form)? So the encoding and decoding
(it's actually not clear we will ever want to decode, but anyhow) isn't
going to cost much in the way of CPU. And making the keys memcmp()
compatible allows some other optimizations - prefix compression and so
on. Plus I think memcmp() will be generally faster than any other
type of comparison.

Creating and using an index on larger blobs might be different of
course.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Consequences of lexicographic sorting of keys in SQLite4?

[Niall O'Reilly]

On 29 Jun 2012, at 23:58, Richard Hipp wrote:

> But you know:  How often do people use BLOBs as keys?  What other SQL
> engines other than SQLite even allow BLOBs as keys?  Are we trying to
> optimize something that is never actually used?

For an IPAM application I have on my back burner, BLOB seems
a natural way to express IPv[46] addresses, ranges, and prefixes.
A bulkier alternative would be hexadecimal encoding as text.

/Niall

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

Re: [sqlite] sqlite4: type decl/def discrepancy (w/ fix)

[Niall O'Reilly]

On 29 Jun 2012, at 17:17, Stephan Beal wrote:

> If i can be of any assistance, i'm free to help this weekend. i feel kinda
> bad about spamming the user list so much, though :/.

No need to feel bad.
It helps us to see "over the horizon".

> Should we try to
> convince the admin ;) to set to a v4-specific list

Unless v4 isn't intended to be ready for a really long time, I would
hope that the admin won't be minded to build its own reservation
for it.  8-)

> (or i can alternately move to the dev list (subscribing now))?

May make sense.  Please don't forget to let us mere users have a
trickle of news about v4.

Best regards,
Niall O'Reilly

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