Re: [sqlite] Bi-directional unique

Black, Michael (IS) wrote: > I have a need to create a unique bi-directional relationship. > > You can think of it as pairings of people who eat dinner together. > > You can't guarantee that one column is less than the other so there's no win > there. Why can't you have a well-known ordering

Re: [sqlite] determine SQLITE_MAX_LENGTH or sql_limit/sqlite3_limit

On 02/10/2011 01:56 AM, Vannus wrote: > Zeoslib is reading sqlite field lengths incorrectly, as it checks for > brackets after the field typename ie. CHAR(123) > presumably this is only affecting me because I haven't defined field lengths > in my sqlite3 db. > > I don't want to hard-code

Re: [sqlite] Where to find amalgamation source for the 3.6.23.1

On Wed, Feb 9, 2011 at 8:51 PM, Sam Carleton wrote: > 3.6.23.1 amalgamation http://www.*sqlite*.*org*/sqlite-*amalgamation*-*3.6.23.1*.tar.gz -- -- -- ô¿ô¬ K e V i N /¯\

[sqlite] SIGSEGV error when using sqlite3_exec()

I am using an in-memory dbase in a multi-threaded application and have the following two questions: I create dbase connections by each thread using the API: { Sqlite3 *db =3D NULL; Sqlite3_open(":memory:", ); When I try to use sqlite3_exec( ) I get segmentation

[sqlite] Where to find amalgamation source for the 3.6.23.1

I hacked the 3.6.23.1 amalgamation code a while back. I need to move those hacks to 3.7.500, but I don't seem to have a copy of the 3.6.23.1 amalgamation code. Where can I find a copy? Sam ___ sqlite-users mailing list sqlite-users@sqlite.org

Re: [sqlite] Bi-directional unique

On 2/9/2011 7:29 PM, Samuel Adam wrote: > In pseudo-C: > > int32_t x, y; > int64_t xk; /* k lives in the low bits of xk */ > > if (x< 0 || y< 0) ; /* Return an error. */ > > xk = (int64_t)x<<32 | x^y; > /* Weird integer concatenation;

Re: [sqlite] Bi-directional unique

On Wed, 09 Feb 2011 17:54:37 -0500, Igor Tandetnik wrote: > On 2/9/2011 5:49 PM, Jim Wilcoxson wrote: >> I assumed you could generate k in a procedural language outside of SQL, >> but >> if you want to do this purely in SQL, I think you can just say: >> >> create table

Re: [sqlite] Bi-directional unique

On Wed, 09 Feb 2011 18:51:45 -0500, Samuel Adam wrote: > On Wed, 09 Feb 2011 18:39:14 -0500, David Bicking > wrote: > >> I don't think this will work. xor(6,1) = 7 and xor(4,3) = 7, so you >> would fail to insert proper pairs. Or am I missing something?

Re: [sqlite] Bi-directional unique

On Wed, 09 Feb 2011 18:39:14 -0500, David Bicking wrote: > I don't think this will work. xor(6,1) = 7 and xor(4,3) = 7, so you > would fail to insert proper pairs. Or am I missing something? (At least > I assume that the integers are not limited to just 1 2 or 3 as in the >

Re: [sqlite] Multiple clients accessing one DB

On Wed, Feb 9, 2011 at 6:35 PM, Sam Carleton wrote: > Ok, the question now is if I should stop the slow walk towards a > client/server DB and start running... > I'm guessing that if you enable WAL you will get all the concurrency you need and then some. -- D.

Re: [sqlite] Bi-directional unique

I don't think this will work. xor(6,1) = 7 and xor(4,3) = 7, so you would fail to insert proper pairs. Or am I missing something? (At least I assume that the integers are not limited to just 1 2 or 3 as in the examples. David On 02/09/2011 05:58 PM, Samuel Adam wrote: > On Wed, 09 Feb 2011

Re: [sqlite] Multiple clients accessing one DB

On Wed, Feb 9, 2011 at 1:46 PM, Igor Tandetnik wrote: > > On 2/9/2011 1:42 PM, Sam Carleton wrote: > > > It is my understanding that SQLite is designed to allow multiple clients > > from the same computer to access the DB file at one time. > > Yes. But if one of those clients

Re: [sqlite] Bi-directional unique

On Wed, 09 Feb 2011 13:12:32 -0500, Black, Michael (IS) wrote: > I have a need to create a unique bi-directional relationship. > > You can think of it as pairings of people who eat dinner together. Two questions come to mind: (a) Do you trust app-level code to

Re: [sqlite] Bi-directional unique

On 2/9/2011 5:49 PM, Jim Wilcoxson wrote: > I assumed you could generate k in a procedural language outside of SQL, but > if you want to do this purely in SQL, I think you can just say: > > create table t(k int primary key) > insert into t values (min(?,?)<<32 | max(?,?)) > > and bind i,j,i,j to

Re: [sqlite] Bi-directional unique

On Wed, Feb 9, 2011 at 5:25 PM, Simon Slavin wrote: > > On 9 Feb 2011, at 7:32pm, Black, Michael (IS) wrote: > > > Didn't we just determine a couple of days ago that triggers were > performance killers? > > > > That's one reason I want to avoid those. > > Okay, then since

Re: [sqlite] Bi-directional unique

On 9 Feb 2011, at 7:32pm, Black, Michael (IS) wrote: > Didn't we just determine a couple of days ago that triggers were performance > killers? > > That's one reason I want to avoid those. Okay, then since your program knows the logic of how that table will be used, it can do it for itself.

Re: [sqlite] Bi-directional unique

On 2/9/2011 4:36 PM, Black, Michael (IS) wrote: > This then begs the question of what happens when an update needs a bigger int? Same thing that happens when an update to a text field inserts longer text. -- Igor Tandetnik ___ sqlite-users mailing

Re: [sqlite] Bi-directional unique

My apologies if this is stupid, or it's already been discussed. There's a way of choosing an ordering on anything, even strings. Have two tables - one where members of column A are "larger", one where members of column B are "larger". Only insert into the correct table (O(1) operation).

Re: [sqlite] Bi-directional unique

I like this one...but I tested it a bit and the bad part is that doing a select on t requires a complete table scan. So finding these values is too slow. I did have to modify a bit to get it to work correctly...now I'll try one of the other suggestions. sqlite> create table t (nm integer

Re: [sqlite] Bi-directional unique

This then begs the question of what happens when an update needs a bigger int? Doesn't that cause a fair bit more overhead than just keeping it 4 bytes? Fragging the database? I suppose for embedded use that might be important for most applications needing 2 bytes or less usually. Michael D.

Re: [sqlite] EXT :Re: Bi-directional unique

On 2/9/2011 4:13 PM, Black, Michael (IS) wrote: > And what's the advantage of packing like this? SQLite uses variable-length encoding for integers - smaller values (nearer to zero, to be precise) get packed into fewer bytes. So if you have two 1 values to store, combining them in the way Max

Re: [sqlite] EXT :Re: Bi-directional unique

And what's the advantage of packing like this? It would seem all you do is add a lot more shifts to insert/retrieve the value. I don't see the benefit. So what if you're storing 0x0001 and 0x0001. Same space, only one shift and an or/and to insert/retrieve necessary. Michael D. Black

Re: [sqlite] EXT :Re: Bi-directional unique

Michael, another small improvement... to take advantage of effective integer packing you can encode two numbers interleaving them dedicating odd bits to one and even to another. This will require extra steps in the program so this is your turn to decide worth it or not. Max On Wed, Feb 9, 2011

Re: [sqlite] Bi-directional unique

On Wed, Feb 9, 2011 at 2:00 PM, Nico Williams wrote: > This way an insert into t requires one lookup and update in t_idx and > one lookup and update in the table's implied rowid index.  I.e., twice > the work of an insert without this additional index and trigger. Also,

Re: [sqlite] Bi-directional unique

That works too..except you need create UNIQUE index t_idx on t(m,n); Otherwise duplicate inserts can exist. I'll give this one a try too. Michael D. Black Senior Scientist NG Information Systems Advanced Analytics Directorate From:

Re: [sqlite] Bi-directional unique

sqlite> create table t(n integer not null check (typeof(n) = 'integer'), m integer not null check (typeof(m) = 'integer')); sqlite> create index t_idx on t (n, m); sqlite> explain query plan select count(*) != 0 from t t2 where t2.n = ? and t2.m = ?; 0|0|TABLE t AS t2 WITH INDEX t_idx sqlite>

Re: [sqlite] EXT :Re: Bi-directional unique

I think I can get away with 31 bits (I'll have to add some error checking just in case). I like this idea as it collapses into a single field that is easily indexed. I'll try doing this the typical way with a query first and then compare with this approach (which I expect should be notably

Re: [sqlite] Bi-directional unique

Didn't we just determine a couple of days ago that triggers were performance killers? That's one reason I want to avoid those. Michael D. Black Senior Scientist NG Information Systems Advanced Analytics Directorate From:

Re: [sqlite] sqlite async

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 02/08/2011 09:50 PM, Mike Blumenkrantz wrote: > I have checked out the documentation and found information about > using sqlite in async mode with threads, Do you mean this? http://www.sqlite.org/asyncvfs.html > but I am wondering if there is

Re: [sqlite] Bi-directional unique

If you don't care about the order and the integers are smallish, like 31 bits or less, I'd do this: create table t(k int primary key); i = whatever j = whatever if i < j: k = i<<32 | j else: k = j<<32 | i insert k into table To see if a pair is in the table, do the same steps and lookup

[sqlite] determine SQLITE_MAX_LENGTH or sql_limit/sqlite3_limit

Zeoslib is reading sqlite field lengths incorrectly, as it checks for brackets after the field typename ie. CHAR(123) presumably this is only affecting me because I haven't defined field lengths in my sqlite3 db. I don't want to hard-code 1,000,000,000 or 2147483647 in as the field length - but

Re: [sqlite] Multiple clients accessing one DB

On 2/9/2011 1:42 PM, Sam Carleton wrote: > Currently I have two and sometimes three clients access the SQLite db, all > on the same machine. > > * A C# program that doesn't ever stay connection all that long. > * An Apache application that stays connected all the time. > * A Qt application that

[sqlite] Multiple clients accessing one DB

Currently I have two and sometimes three clients access the SQLite db, all on the same machine. * A C# program that doesn't ever stay connection all that long. * An Apache application that stays connected all the time. * A Qt application that stays connected when it is running. I am getting

Re: [sqlite] Bi-directional unique

If you don't care about the order then use and instead of trigger to force the ordering then you will get the collisions you expect. On 2/9/2011 10:12 AM, Black, Michael (IS) wrote: > I have a need to create a unique bi-directional relationship. > > You can think of it as pairings of people who

Re: [sqlite] Bi-directional unique

On Wed, Feb 09, 2011 at 06:12:32PM +, Black, Michael (IS) scratched on the wall: > I have a need to create a unique bi-directional relationship. > You can't guarantee that one column is less than the other so there's > no win there. Not sure what you mean by that. If the values are not

Re: [sqlite] Bi-directional unique

On 9 Feb 2011, at 6:12pm, Black, Michael (IS) wrote: > I have a need to create a unique bi-directional relationship. > > You can think of it as pairings of people who eat dinner together. > > > > create table t(i int, j int); > > insert into t(1,2); > > insert into t(2,1); << should give

Re: [sqlite] database disk image is malformed 3.7.x

> I didn't find a way yet to reproduce the issue with a "clean" database. > Only way I can reproduce it is with some of the database, like the > test.db3. So I'm running out of ideas. This is the theory. test.db3 is an auto-vacuum database. http://www.sqlite.org/src/info/89b8c9ac54 Dan.

[sqlite] Bi-directional unique

I have a need to create a unique bi-directional relationship. You can think of it as pairings of people who eat dinner together. create table t(i int, j int); insert into t(1,2); insert into t(2,1); << should give an error because the pairing of 1-2 already exists. insert into t(3,2); <<

Re: [sqlite] database disk image is malformed 3.7.x

On 02/10/2011 12:10 AM, Dan Kennedy wrote: > On 02/09/2011 08:17 PM, Dennis Geldhof wrote: >> I checked some things for the attached database. It is created with >> sqlite version 3.6.3 or 3.6.23.1 with the help of the system.data.sqlite >> wrapper. The header displays (with the ./showdb) version

Re: [sqlite] Feature suggestion for the Tcl interface : ability to use a list variable in combination with IN

Fredrik Karlsson wrote: > package require sqlite3 > sqlite3 db :memory: > db eval {create table a (id INTEGER);} > db eval {insert into a values (1);} > db eval {insert into a values (2);} > db eval {select * from a where id in (1,3);} vals {parray vals} > vals(*) = id > vals(id) = 1 > set alist

Re: [sqlite] database disk image is malformed 3.7.x

On 02/09/2011 08:17 PM, Dennis Geldhof wrote: > I checked some things for the attached database. It is created with > sqlite version 3.6.3 or 3.6.23.1 with the help of the system.data.sqlite > wrapper. The header displays (with the ./showdb) version 3.7.4, so it is > opened and changed with a

[sqlite] Feature suggestion for the Tcl interface : ability to use a list variable in combination with IN

Dear list, I find the IN operator quite useful for selecting a set number of things. However, I often have a Tcl list with the things I want to match already when I get to the stage there I should issue a SELECT on the database. I then paste all the elements of the list together with ',' or just

Re: [sqlite] sqlite async

> but I am wondering if there is any > (somewhat) easy way to get direct fd access and maintain asyncronicity without > threads. How do you see it's possible? Even if we assume that all files were opened in async mode and only aio functions were used what would that mean? You are calling

Re: [sqlite] database disk image is malformed 3.7.x

I checked some things for the attached database. It is created with sqlite version 3.6.3 or 3.6.23.1 with the help of the system.data.sqlite wrapper. The header displays (with the ./showdb) version 3.7.4, so it is opened and changed with a database viewer tool. Before executing the "query" the

Re: [sqlite] SQLite and Memory Databases

Bastian Clarenbach wrote: > My environment does not have direct file access, instead I can only request > files and get a memblock returned that contains the entire file. You should be able to write a virtual file system that reads and writes to a block of

Re: [sqlite] SQLite and Memory Databases

Yes, I expect the database to be small enough. It is the loading a :memory: database from and storing to memory blocks that still eludes me. I will take a look at the backup link. Thanks! On 9 February 2011 13:48, Simon Slavin wrote: > > On 9 Feb 2011, at 10:14am,

Re: [sqlite] SQLite and Memory Databases

On 9 Feb 2011, at 10:14am, Bastian Clarenbach wrote: > My environment does not have direct file access, instead I can only request > files and get a memblock returned that contains the entire file. I am trying > to figure out how to do one, preferably both, of the following scenarios. > > 1. I

[sqlite] sqlite async

Hi, I am writing an open source database library, and I would like to add an sqlite backend to it. I have checked out the documentation and found information about using sqlite in async mode with threads, but I am wondering if there is any (somewhat) easy way to get direct fd access and maintain

[sqlite] SQLite and Memory Databases

Hi, My environment does not have direct file access, instead I can only request files and get a memblock returned that contains the entire file. I am trying to figure out how to do one, preferably both, of the following scenarios. 1. I want to create a database 'offline' and then load and use

Re: [sqlite] database disk image is malformed 3.7.x

> -Original Message- > From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- > boun...@sqlite.org] On Behalf Of Dan Kennedy > Sent: woensdag 9 februari 2011 5:57 > To: sqlite-users@sqlite.org > Subject: Re: [sqlite] database disk image is malformed 3.7.x > > The only problem we