[sqlite] Merge two rows/records

2007-10-08 Thread Daan van der Sanden

Hi,

Is it possible to merge two rows with SQLite? Say I have the following table":
CREATE TABLE foo(a integer, b integer, c integer, d integer);
CREATE INDEX idx ON foo(a,b);

With the following data:

INSERT INTO foo VALUES (1,1,1,2);
INSERT INTO foo VALUES (1,2,3,4);
INSERT INTO foo VALUES (1,2,5,6);
INSERT INTO foo VALUES (2,1,7,8);
INSERT INTO foo VALUES (3,1,9,10);
INSERT INTO foo VALUES (3,2,11,12);
INSERT INTO foo VALUES (3,2,13,14);
INSERT INTO foo VALUES (3,3,15,16);

What I would like is that when the combenation of a and b are not unique to 
merge the two rows to one and sum the values b and c. So the resulting table 
should become:

  a | b | c | d
 ---+---+---+---+
  1 | 1 | 1 |  2
  1 | 2 | 8 | 10
  2 | 1 | 7 |  8
  3 | 1 | 9 | 10
  3 | 2 | 24| 26
  3 | 3 | 15| 16

I know how to select the two duplicate rows (ensum them, but. I use the query:
SELECT * FROM foo GROUP BY a,b HAVING count(*)>1; to select them.

But how do I merge them? Do I need a self written program to itterate throuw the result of the previous query to merge them or can this be done 
on the sqlite3 program with a few "simple" querys?


Thanks in advance!
Kind Regards
Daan

-
To unsubscribe, send email to [EMAIL PROTECTED]
-



Re: [sqlite] Merge two rows/records

2007-10-08 Thread Simon Davies
On 08/10/2007, Daan van der Sanden <[EMAIL PROTECTED]> wrote:
> Hi,
>
> Is it possible to merge two rows with SQLite? Say I have the following table":
> CREATE TABLE foo(a integer, b integer, c integer, d integer);
> CREATE INDEX idx ON foo(a,b);
>
> With the following data:
>
> INSERT INTO foo VALUES (1,1,1,2);
> INSERT INTO foo VALUES (1,2,3,4);
> INSERT INTO foo VALUES (1,2,5,6);
> INSERT INTO foo VALUES (2,1,7,8);
> INSERT INTO foo VALUES (3,1,9,10);
> INSERT INTO foo VALUES (3,2,11,12);
> INSERT INTO foo VALUES (3,2,13,14);
> INSERT INTO foo VALUES (3,3,15,16);
>
> What I would like is that when the combenation of a and b are not unique to 
> merge the two rows to one and sum the values b and c. So the resulting table 
> should become:
>
>   a | b | c | d
>  ---+---+---+---+
>   1 | 1 | 1 |  2
>   1 | 2 | 8 | 10
>   2 | 1 | 7 |  8
>   3 | 1 | 9 | 10
>   3 | 2 | 24| 26
>   3 | 3 | 15| 16
>
> I know how to select the two duplicate rows (ensum them, but. I use the query:
> SELECT * FROM foo GROUP BY a,b HAVING count(*)>1; to select them.
>
> But how do I merge them? Do I need a self written program to itterate throuw 
> the result of the previous query to merge them or can this be done
> on the sqlite3 program with a few "simple" querys?
>
> Thanks in advance!
> Kind Regards
> Daan

Hi Daan,

sqlite> select a,b,sum(c),sum(d) from foo group by a,b;

gives you the data you are after. This could be used to populate
another table via

sqlite> insert into newFoo select a,b,sum(c),sum(d) from foo group by a,b;

Of course, if you can get the data you want from your existing table
using a simple query, you may not actually need a new table.

Rgds,
Simon

-
To unsubscribe, send email to [EMAIL PROTECTED]
-



Re: [sqlite] Problems with SQLite and PHP

2007-10-08 Thread Markus Wolff - NorthClick
Hi Joe,

standard temp directory in my setup is /tmp, which is both readable and
writeable by Apache. Available space is 21GB, which should be more than
sufficient for the task at hand :-)

CU
 Markus

Am Donnerstag, den 04.10.2007, 16:13 -0700 schrieb Joe Wilson:
> See if the apache/mod_php process' unix account can read/write to at 
> least one of the following directories and there is sufficient disk 
> space in the first such directory that is r/w.
> 
>  pragma temp_store_directory;  -- if present. As run in apache.
>  /var/tmp
>  /usr/tmp
>  /tmp
>  .
> 
> --- Markus Wolff <[EMAIL PROTECTED]> wrote:
> > I'm trying to open an SQLite3 database from a PHP very simple PHP
> > script:
> > 
> > $db = dirname(__FILE__).'/frontend.db';
> > $pdo = new PDO('sqlite:'.$db);
> > $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
> > $pdo->query("SELECT * FROM page LIMIT 1");
> > echo "Deleting pages\n";
> > $pdo->query("DELETE FROM page");
> > echo "Deleting websites\n";
> > $pdo->query("DELETE FROM website");
> > 
> > The database file contains no data whatsoever, just the table
> > definitions (in case you were wondering, this is a stripped-down version
> > of a larger script for debugging purposes, hence the seemingly idiotic
> > DELETE statements that won't do any good in an empty database anyway,
> > but I digress...).
> > 
> > When executed on the command line, this works perfectly. When I execute
> > the same script via Apache and mod_php, I'm getting this exception:
> > 
> > PDOException: SQLSTATE[HY000]: General error: 1 SQL logic error or
> > missing database in /home/mwolff/webs/markus/cms/test.php on line 8
> 
> 
>   
> 
> Shape Yahoo! in your own image.  Join our Network Research Panel today!   
> http://surveylink.yahoo.com/gmrs/yahoo_panel_invite.asp?a=7 
> 
> 
> 
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
> 
-- 
Mit freundlichen Grüßen
Markus Wolff
Development

NorthClick GmbH

Gasstr. 10 - 22761 Hamburg
Tel.: 040 8 22 44 999 - Fax: 040 8 22 44 998
Internet: http://www.northclick.de/

Geschäftsführer: F. Detzner | M. Henze | C. Springub
Amtsgericht Hamburg, HRB 94459


-
To unsubscribe, send email to [EMAIL PROTECTED]
-



Re: [sqlite] Problems with SQLite and PHP

2007-10-08 Thread Markus Wolff - NorthClick
Am Freitag, den 05.10.2007, 01:03 +0200 schrieb Kees Nuyt:
> I can't reproduce the exception (the PDO->query
> version) on an environment I happened to have
> available.
> 
> - MS Windows XP Professional (5.2 build 2600)
> - Apache 2.2.4
> - PHP 5.2.2
> - PDO SQLite 1.0.1 2007/03/23
> - SQLite library 3.3.16
> 
> Perhaps I'll try again tomorrow with the
> same installation, but using
> - SQLite library 3.4.2
> 
> The only thing i can think of (wild guess) is your
> php-cli uses another php.ini than the Apache module does.

Hi Kees,

it's true that the php.ini is different for the CLI and mod_php variants
- that's actually quite a common setup.

The library version used by PHP seems to be consistent with the SQLite
commandline tool, though:

$ php -i | grep -i sqlite
/etc/php5/cli/conf.d/02_pdo_sqlite.ini,
/etc/php5/cli/conf.d/03_sqlite3.ini,
/etc/php5/cli/conf.d/04_sqlite.ini,
PDO drivers => sqlite, mysql
pdo_sqlite
PDO Driver for SQLite 3.x => enabled
PECL Module version => 1.0.1 $Id: pdo_sqlite.c,v 1.10.2.6 2006/01/01
12:50:12 sniper Exp $
SQLite Library => 3.4.2
SQLITE3
SQLite3 support => enabled
sqlite3 library version => 3.4.2

The phpinfo() output from mod_php states exactly the same version.

CU
 Markus


-
To unsubscribe, send email to [EMAIL PROTECTED]
-



[sqlite] CONFIG_FPE_FASTFPE and the text representation of a float

2007-10-08 Thread Frank van Vugt
L.S.

Here's the patch for those interested:


--- sqlite-3.4.2-orig/src/printf.c  2007-06-28 14:46:19.0 +0200
+++ sqlite-3.4.2/src/printf.c   2007-10-08 11:56:49.0 +0200
@@ -158,7 +158,11 @@
 static int et_getdigit(LONGDOUBLE_TYPE *val, int *cnt){
   int digit;
   LONGDOUBLE_TYPE d;
+#ifdef SQLITE_FPE_FASTFPE
+  if( (*cnt)++ >= 10 ) return '0';
+#else
   if( (*cnt)++ >= 16 ) return '0';
+#endif /* SQLITE_FPE_FASTFPE */
   digit = (int)*val;
   d = digit;
   digit += '0';


and


diff -u -r sqlite-3.4.2-orig/src/vdbemem.c sqlite-3.4.2/src/vdbemem.c
--- sqlite-3.4.2-orig/src/vdbemem.c 2007-06-28 14:46:19.0 +0200
+++ sqlite-3.4.2/src/vdbemem.c  2007-10-08 11:58:07.0 +0200
@@ -217,7 +217,11 @@
 sqlite3_snprintf(NBFS, z, "%lld", pMem->u.i);
   }else{
 assert( fg & MEM_Real );
+#ifdef SQLITE_FPE_FASTFPE
+sqlite3_snprintf(NBFS, z, "%!.9g", pMem->r);
+#else
 sqlite3_snprintf(NBFS, z, "%!.15g", pMem->r);
+#endif /* SQLITE_FPE_FASTFPE */
   }
   pMem->n = strlen(z);
   pMem->z = z;



FYI, the (latest?) 2.6 kernels seem to have ditched FASTFPE completely, so the 
above is probably a 2.4 - issue only.




-- 
Best,




Frank.

-
To unsubscribe, send email to [EMAIL PROTECTED]
-



Re: [sqlite] Re: Merge two rows/records

2007-10-08 Thread Simon Davies
Hi Daan,

You could try this:

sqlite> CREATE TABLE foo(a integer, b integer, c integer, d integer,
   ...> unique (a, b) on conflict ignore);
sqlite> CREATE TRIGGER fooUnique before insert on foo
   ...> when exists (select a from foo where a=new.a and b=new.b)
   ...> begin update foo set c=c+new.c, d=d+new.d where
a=new.a and b=new.b;
   ...> end;
sqlite>
sqlite>
sqlite> INSERT INTO foo VALUES (1,1,1,2);
sqlite> INSERT INTO foo VALUES (1,2,3,4);
sqlite> INSERT INTO foo VALUES (1,2,5,6);
sqlite> INSERT INTO foo VALUES (2,1,7,8);
sqlite> INSERT INTO foo VALUES (3,1,9,10);
sqlite> INSERT INTO foo VALUES (3,2,11,12);
sqlite> INSERT INTO foo VALUES (3,2,13,14);
sqlite> INSERT INTO foo VALUES (3,3,15,16);
sqlite>
sqlite> select * from foo;
1|1|1|2
1|2|8|10
2|1|7|8
3|1|9|10
3|2|24|26
3|3|15|16
sqlite>

Rgds,
Simon


On 08/10/2007, Daan van der Sanden <[EMAIL PROTECTED]> wrote:
> Thanks for the quick reply!
>
.
.
.
> At the moment I've got a database with values gathered from multiple
> inputs that generated "duplicate entries" for the "what should be unique"
> a,b combination. So I was wondering if they could be "easily" merged
> without creating a new table.
>
> Now I'm going to first copy all unique samples to a new database and then
> insert the summed values using the given query. But this solution seems a
> bit awkward, since I'm copying 6 million unique records to a new database
> and adding a small 22.000 records that are summed. So that's why I was
> wondering if it could be done in the same table.
>
> I hope my problem is a bit clearer now.
>
> Kind regards
> Daan
>

-
To unsubscribe, send email to [EMAIL PROTECTED]
-



RE: [sqlite] Re: Merge two rows/records

2007-10-08 Thread B V, Phanisekhar
Hi Daan,
You can make the columns (a, b) unique across (a, b), but not
separately unique; by that whenever you are trying to insert a row with
same (a, b) combination it will give an error and at that time you can
update the column values c and d. I hope this will solve your problem.

Regards,
Phani

-Original Message-
From: Daan van der Sanden [mailto:[EMAIL PROTECTED] 
Sent: Monday, October 08, 2007 3:06 PM
To: sqlite-users@sqlite.org
Subject: [sqlite] Re: Merge two rows/records

Thanks for the quick reply!

Simon Davies writes:
> > sqlite> select a,b,sum(c),sum(d) from foo group by a,b;
> >
> > gives you the data you are after. This could be used to populate
> > another table via
> >
> > sqlite> insert into newFoo select a,b,sum(c),sum(d) from foo group
by
> > a,b;
> >
> > Of course, if you can get the data you want from your existing table
> > using a simple query, you may not actually need a new table.

At the moment I've got a database with values gathered from multiple 
inputs that generated "duplicate entries" for the "what should be
unique" 
a,b combination. So I was wondering if they could be "easily" merged 
without creating a new table.

Now I'm going to first copy all unique samples to a new database and
then 
insert the summed values using the given query. But this solution seems
a 
bit awkward, since I'm copying 6 million unique records to a new
database 
and adding a small 22.000 records that are summed. So that's why I was 
wondering if it could be done in the same table.

I hope my problem is a bit clearer now.

Kind regards
Daan


-
To unsubscribe, send email to [EMAIL PROTECTED]

-


-
To unsubscribe, send email to [EMAIL PROTECTED]
-



Re: [sqlite] Problems with SQLite and PHP

2007-10-08 Thread Markus Wolff - NorthClick
I have found the problem. PHP was happily reporting that the frontend.db
file itself is writeable (which was true all the time), but the
directory containing the database was not writeable by the webserver.

I find this to be a bit irritating - shouldn't it be enough that the
server can dive into that subdir and find a file it can actually write
to within the dir? How does it make sense that the entire directory
containing the directory must be writeable? Is this an SQLite
requirement or a PHP quirk?

This way I will have to make a new directory exclusively for the
database, because I do not want all scripts to have write access to the
whole main dir...

CU
 Markus

Am Donnerstag, den 04.10.2007, 23:37 +0200 schrieb Markus Wolff:
> Hello everone,
> 
> I'm having a really weird problem with SQLite when used with PHP - I'm 
> pretty sure it's not SQLite that's at fault here, but since in the 
> PHP-DB mailingliste nobody seemed to be able to help me, I'm hoping to 
> get some insights here :-)
> 
> I'm trying to open an SQLite3 database from a PHP very simple PHP
> script:
> 
> $db = dirname(__FILE__).'/frontend.db';
> $pdo = new PDO('sqlite:'.$db);
> $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
> $pdo->query("SELECT * FROM page LIMIT 1");
> echo "Deleting pages\n";
> $pdo->query("DELETE FROM page");
> echo "Deleting websites\n";
> $pdo->query("DELETE FROM website");
> 
> The database file contains no data whatsoever, just the table
> definitions (in case you were wondering, this is a stripped-down version
> of a larger script for debugging purposes, hence the seemingly idiotic
> DELETE statements that won't do any good in an empty database anyway,
> but I digress...).
> 
> When executed on the command line, this works perfectly. When I execute
> the same script via Apache and mod_php, I'm getting this exception:
> 
> PDOException: SQLSTATE[HY000]: General error: 1 SQL logic error or
> missing database in /home/mwolff/webs/markus/cms/test.php on line 8
> 
> Getting experimental, I've tried to change the calls for the DELETE
> statements from $pdo->query() to $pdo->exec(), just to see what happens.
> Well, what happens is that I'm getting a different error:
> 
> PDOException: SQLSTATE[HY000]: General error: 14 unable to open database
> file in /home/mwolff/webs/markus/cms/test.php on line 6
> 
> Argh... what can possibly be wrong here? The script works from the
> commandline, with the exact same PHP version (Debian package, PHP
> 5.2.0-8+etch7, and we also tried upgrading to the latest Debian package
> of 5.2.4, to no avail).
> 
> It can't be file permissions, I've even tried to set the database file
> to 777... no change at all.
> 
> 
> For the previous discussion in the PHP-DB mailinglist, see here 
> (starting point, same content as above):
> http://marc.info/?l=php-db=119134768316086=2
> 
> A detailed step-by-step list for reproducing the problem, including the 
> used SQL schema, can be found here:
> http://marc.info/?l=php-db=119143000125909=2
> 
> I hope someone here can point me in the right direction, I'm getting 
> somewhat desperate :-)
> 
> Thanks,
> Markus
> 
> 
-- 
Mit freundlichen Grüßen
Markus Wolff
Development

NorthClick GmbH

Gasstr. 10 - 22761 Hamburg
Tel.: 040 8 22 44 999 - Fax: 040 8 22 44 998
Internet: http://www.northclick.de/

Geschäftsführer: F. Detzner | M. Henze | C. Springub
Amtsgericht Hamburg, HRB 94459


-
To unsubscribe, send email to [EMAIL PROTECTED]
-



[sqlite] Re: Null-ifying the columns

2007-10-08 Thread Igor Tandetnik

chetana bhargav
 wrote:

Want to know is there any easy way to make all the columns in a
particular row to NULL other than specifying column name and NULL
value, no delete as that would change the row-id


Well, you could remember the ROWID, then delete the row and insert the 
new blank one with the old ROWID. Realize that you can explicitly 
specify a ROWID in the INSERT statement: insert into tableName(rowid) 
values(123)


Igor Tandetnik 



-
To unsubscribe, send email to [EMAIL PROTECTED]
-



[sqlite] many-one relation

2007-10-08 Thread chetana bhargav
Hi All,

We are designing a data base landed in some problems would like to know what 
approach is the best.

We got two tables, tableA & tableB. 

tableB is turning out to be a many-one relation where in we have many rows of 
tableA mapping to one row of tableB, would like to know what is the best way to 
delete a row in tableB

1. Keep a reference count of the number of rows that are referring to this ( to 
be honest I dont think this is good)
2. More on similar lines instead of count have row-ids and attach a trigger


-x-
Chetana


  

Catch up on fall's hot new shows on Yahoo! TV. Watch previews, get listings, 
and more!
http://tv.yahoo.com/collections/3658 

Re: [sqlite] Problems with SQLite and PHP

2007-10-08 Thread Andy Goth
On Mon, 08 Oct 2007 13:12:08 +0200, Markus Wolff - NorthClick wrote
> How does it make sense that the entire directory containing the 
> directory must be writeable?

Sounds like a temporary file or lock file is being created.

Somebody who knows more about PHP or SQLite can tell you which is taking
place.  Me, I'd find out what's really happening by running SQLite in strace
and looking for open() calls.

-- 
Andy Goth
<[EMAIL PROTECTED]>


-
To unsubscribe, send email to [EMAIL PROTECTED]
-



[sqlite] user defined function

2007-10-08 Thread Aviad Harell
Hey,

I tried to create user defined function called Rank. which gets partition
columns and order by column. this function should create rank according to
its parameters. i.e.:

select customer, product, sales, Rank(customer,  sales)
from table

should return:

customerA productA 100 0
customerA productB 300 2
customerA productC 200 1
customerA productD 400 3
customerB productB 300 1
customerB productA 400 2
customerB productC 100 0

how can i do it? should i use FunctionType scalar or aggregate?


Re: [sqlite] Problems with SQLite and PHP

2007-10-08 Thread Trevor Talbot
On 10/8/07, Markus Wolff - NorthClick <[EMAIL PROTECTED]> wrote:

> I find this to be a bit irritating - shouldn't it be enough that the
> server can dive into that subdir and find a file it can actually write
> to within the dir? How does it make sense that the entire directory
> containing the directory must be writeable? Is this an SQLite
> requirement or a PHP quirk?

SQLite needs to be able to create and delete a journal file during
database modification.

-
To unsubscribe, send email to [EMAIL PROTECTED]
-



Re: [sqlite] many-one relation

2007-10-08 Thread Trevor Talbot
On 10/8/07, chetana bhargav <[EMAIL PROTECTED]> wrote:

> We got two tables, tableA & tableB.
>
> tableB is turning out to be a many-one relation where in we have many rows of 
> tableA mapping to one row of tableB, would like to know what is the best way 
> to delete a row in tableB
>
> 1. Keep a reference count of the number of rows that are referring to this ( 
> to be honest I dont think this is good)
> 2. More on similar lines instead of count have row-ids and attach a trigger

I'm not clear on the scenario here.  Is this a like a foreign key
relationship, so you want a delete of a row in table B to delete all
referring rows from A?  Is this a garbage collection situation, where
you want a row in B to go away when all referring rows in A are
deleted?  Something else?

-
To unsubscribe, send email to [EMAIL PROTECTED]
-



Re: [sqlite] many-one relation

2007-10-08 Thread chetana bhargav
>> Is this a garbage collection situation, where you want a row in B to go away 
>> when all referring rows in A are deleted? 

Yes exactly this is what I wanted :)

Sorry for the confusing message earlier

-x-
Chetana



- Original Message 
From: Trevor Talbot <[EMAIL PROTECTED]>
To: sqlite-users@sqlite.org
Sent: Monday, October 8, 2007 9:10:22 PM
Subject: Re: [sqlite] many-one relation


On 10/8/07, chetana bhargav <[EMAIL PROTECTED]> wrote:

> We got two tables, tableA & tableB.
>
> tableB is turning out to be a many-one relation where in we have many rows of 
> tableA mapping to one row of tableB, would like to know what is the best way 
> to delete a row in tableB
>
> 1. Keep a reference count of the number of rows that are referring to this ( 
> to be honest I dont think this is good)
> 2. More on similar lines instead of count have row-ids and attach a trigger

I'm not clear on the scenario here.  Is this a like a foreign key
relationship, so you want a delete of a row in table B to delete all
referring rows from A?   Something else?

-
To unsubscribe, send email to [EMAIL PROTECTED]
-


  

Tonight's top picks. What will you watch tonight? Preview the hottest shows on 
Yahoo! TV.
http://tv.yahoo.com/ 


[sqlite] A Question: Use of the sqlite3_exe( ) function?

2007-10-08 Thread Lee Crain
Igor,

I have a question. 

Why is it "highly recommended" to use the function call sequence you
iterate in preference to the sqlite3_exe call, since it is implemented
using that sequence?

Lee Crain

_

-Original Message-
From: Igor Tandetnik [mailto:[EMAIL PROTECTED] 
Sent: Sunday, October 07, 2007 9:44 AM
To: SQLite
Subject: [sqlite] Re: Callback fonction really not flexible to use

[EMAIL PROTECTED] wrote:
>Here a sample (in c) of the use i would like to do with sqlite
>fucntion1() call fonction2() where is sqlite3_exec()
>Callback function is the function3() and i would like to add data
>in an array, which is retuned to function1() after the call of
> function(2).
>How i can do that ? does the Callback function can return
> something else than an int ?

A callback function must return 0. Any non-zero return value is an error 
indicator.

However, the callback can, and usually does, have side effects. The 
void* parameter you pass to sqlite3_exec is passed through to the 
callback. Normally, this points to some kind of a data structure that 
the callback modifies.


Having said that, be aware that sqlite3_exec is retained for backward 
compatibility only. It is highly recommended for new code to use API 
like sqlite3_prepare, sqlite3_step, sqlite3_finalize, sqlite3_column_* 
to iterate over the resultset. In fact, sqlite3_exec itself is 
implemented entirely in terms of these public API functions.

Igor Tandetnik 


--
---
To unsubscribe, send email to [EMAIL PROTECTED]
--
---



-
To unsubscribe, send email to [EMAIL PROTECTED]
-



[sqlite] Re: A Question: Use of the sqlite3_exe( ) function?

2007-10-08 Thread Igor Tandetnik

Lee Crain <[EMAIL PROTECTED]> wrote:

Why is it "highly recommended" to use the function call sequence you
iterate in preference to the sqlite3_exe call, since it is implemented
using that sequence?


1) It's usually easier to write a loop, than to break out the body of 
the loop into a separate callback function that can only communicate 
with the mainline by way of side effects.


2) It avoids unnecessary type conversions. The callback always gets the 
data as strings: sqlite3_column_* can get data in its original format 
(e.g. as integers).


3) sqlite3_exec doesn't support parameters (see sqlite3_param_*) which 
leads to excessive use of sprintf or similar, which in turn creates 
opportunities for SQL injection attacks.


Igor Tandetnik 



-
To unsubscribe, send email to [EMAIL PROTECTED]
-



[sqlite] built in function hex

2007-10-08 Thread Ken
is there a built in function to display numeric values as hexidecibmal strings?

to_hexstr (40)would display 0x28 

Thanks,
Ken



Re: [sqlite] many-one relation

2007-10-08 Thread Kees Nuyt
On Mon, 8 Oct 2007 09:04:53 -0700 (PDT), you wrote:


>>> Is this a garbage collection situation, where
>>> you want a row in B to go away when all 
>>> referring rows in A are deleted? 
>
>Yes exactly this is what I wanted :)
>
>Sorry for the confusing message earlier
>
>-x-
>Chetana

This might be of help:

http://www.sqlite.org/cvstrac/wiki?p=ForeignKeyTriggers

http://www.rcs-comp.com/site/index.php/view/Utilities-SQLite_foreign_key_trigger_generator
-- 
  (  Kees Nuyt
  )
c[_]

-
To unsubscribe, send email to [EMAIL PROTECTED]
-



Re: [sqlite] many-one relation

2007-10-08 Thread Trevor Talbot
On 10/8/07, chetana bhargav <[EMAIL PROTECTED]> wrote:
> >> Is this a garbage collection situation, where you want a row in B to go 
> >> away when all referring rows in A are deleted?
>
> Yes exactly this is what I wanted :)

Okay.  A trigger on table A can accomplish it without any extra tracking:

CREATE TRIGGER CleanUpOrphans AFTER DELETE ON A
  WHEN NOT EXISTS (SELECT 1 FROM A WHERE refkey = OLD.refkey)
  BEGIN
DELETE FROM B WHERE key = OLD.refkey;
  END;

If table A has an index on the refkey column, it should be reasonably efficient.

Maintaining a reference count in table B may be more efficient from an
I/O standpoint, but you'll probably have to do checking within app
code (instead of using SQL triggers directly), and it would likely
only pay off if table A has both a vey large number of total rows, as
well as many references to the same key in table B.  It's also not as
clean from an SQL standpoint, since it's an additional item that must
be tracked, yet has nothing to do with your actual data dependencies.

Keeping a list of referring rowids within a table B row would be hard
to maintain; you'd probably instead end up with a relationship table
that maps keys in table A to keys in table B -- the same thing you'd
use for a classic many-to-many relationship. I don't see this being a
win in any form, since it's essentially just a manual version of an
index on table A's refkey column.

I'd start with the no-extra-tracking approach, and only test other
methods if it turns out to be too slow for your data.

-
To unsubscribe, send email to [EMAIL PROTECTED]
-



Re: [sqlite] many-one relation

2007-10-08 Thread Trevor Talbot
I wrote:
> On 10/8/07, chetana bhargav <[EMAIL PROTECTED]> wrote:
> > >> Is this a garbage collection situation, where you want a row in B to go 
> > >> away when all referring rows in A are deleted?
> >
> > Yes exactly this is what I wanted :)

> Maintaining a reference count in table B may be more efficient from an
> I/O standpoint, but you'll probably have to do checking within app
> code (instead of using SQL triggers directly), and it would likely
> only pay off if table A has both a vey large number of total rows, as
> well as many references to the same key in table B.  It's also not as
> clean from an SQL standpoint, since it's an additional item that must
> be tracked, yet has nothing to do with your actual data dependencies.

After thinking a bit more about implementing this, a pair of triggers
can accomplish it without doing anything special in the app:

CREATE TRIGGER DeRef AFTER DELETE ON A
  BEGIN
UPDATE B SET refcount = refcount-1 WHERE key = OLD.refkey;
  END;

CREATE TRIGGER DelOrphans AFTER UPDATE ON B
  WHEN NEW.refcount = 0
  BEGIN
DELETE FROM B WHERE rowid = NEW.rowid;
  END;

I still favor the simpler approach, but at least this one is workable.

I also choose lousy trigger names :)

-
To unsubscribe, send email to [EMAIL PROTECTED]
-



FW: [sqlite] 3.5.1 build issues

2007-10-08 Thread Evans, Mark (Tandem)
Addendum:  There is a new file, src/test_onefile.c, that needs to be
added to Makefile.in.

-Original Message-
From: Evans, Mark (Tandem) 
Sent: Monday, October 08, 2007 12:23 PM
To: sqlite-users@sqlite.org
Subject: [sqlite] 3.5.1 build issues

A glitch or two got introduced into the 3.5.1 build for cygwin and
possibly other platforms.  Please advise if I did something wrong:

There are undefined symbols when building testfixture:

/cygdrive/c/mse/ccm_wa/hsspla/SDM/SQLite-REL01/SQLite/build/cygwin/build
/bld/../../../../Source/sqlite-3.5.1-hp/src/tclsqlite.c:2511: undefined
reference to `_Sqlitetest7_Init'
/cygdrive/c/mse/ccm_wa/hsspla/SDM/SQLite-REL01/SQLite/build/cygwin/build
/bld/../../../../Source/sqlite-3.5.1-hp/src/tclsqlite.c:2520: undefined
reference to `_SqlitetestThread_Init'
/cygdrive/c/mse/ccm_wa/hsspla/SDM/SQLite-REL01/SQLite/build/cygwin/build
/bld/../../../../Source/sqlite-3.5.1-hp/src/tclsqlite.c:2521: undefined
reference to `_SqlitetestOnefile_Init'


The first function, Sqlitetest7_Init, turns up undefined due to change
in #if-s in test7.c:
from:
#if OS_UNIX && SQLITE_THREADSAFE && \
defined(SQLITE_SERVER) && !defined(SQLITE_OMIT_SHARED_CACHE)

to:

#if defined(SQLITE_SERVER) && !defined(SQLITE_OMIT_SHARED_CACHE)
#if defined(OS_UNIX) && OS_UNIX && SQLITE_THREADSAFE

The latter two functions were moved to test_thread.c which does not
appear in Makefile.in but they are referenced by tclsqlite.c. 


Thanks,
Mark


-
To unsubscribe, send email to [EMAIL PROTECTED]
-



Re: [sqlite] 3.5.1 build issues

2007-10-08 Thread Joe Wilson
"make test" already fixed in CVS.

http://www.sqlite.org/cvstrac/tktview?tn=2689

--- "Evans, Mark (Tandem)" <[EMAIL PROTECTED]> wrote:
> A glitch or two got introduced into the 3.5.1 build for cygwin and
> possibly other platforms.  Please advise if I did something wrong:
> 
> There are undefined symbols when building testfixture:
> 
> /cygdrive/c/mse/ccm_wa/hsspla/SDM/SQLite-REL01/SQLite/build/cygwin/build
> /bld/../../../../Source/sqlite-3.5.1-hp/src/tclsqlite.c:2511: undefined
> reference to `_Sqlitetest7_Init'
> /cygdrive/c/mse/ccm_wa/hsspla/SDM/SQLite-REL01/SQLite/build/cygwin/build
> /bld/../../../../Source/sqlite-3.5.1-hp/src/tclsqlite.c:2520: undefined
> reference to `_SqlitetestThread_Init'
> /cygdrive/c/mse/ccm_wa/hsspla/SDM/SQLite-REL01/SQLite/build/cygwin/build
> /bld/../../../../Source/sqlite-3.5.1-hp/src/tclsqlite.c:2521: undefined
> reference to `_SqlitetestOnefile_Init'



   

Take the Internet to Go: Yahoo!Go puts the Internet in your pocket: mail, news, 
photos & more. 
http://mobile.yahoo.com/go?refer=1GNXIC

-
To unsubscribe, send email to [EMAIL PROTECTED]
-



RE: [sqlite] 3.5.1 build issues

2007-10-08 Thread Evans, Mark (Tandem)
Thanks Joe  

> -Original Message-
> From: Joe Wilson [mailto:[EMAIL PROTECTED] 
> Sent: Monday, October 08, 2007 1:45 PM
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] 3.5.1 build issues
> 
> "make test" already fixed in CVS.
> 
> http://www.sqlite.org/cvstrac/tktview?tn=2689
> 

-
To unsubscribe, send email to [EMAIL PROTECTED]
-



Re: [sqlite] many-one relation

2007-10-08 Thread John Stanton
A brute force method would be to store the rowid of the owner row in 
each of the detail rows.


chetana bhargav wrote:
Is this a garbage collection situation, where you want a row in B to go away when all referring rows in A are deleted? 



Yes exactly this is what I wanted :)

Sorry for the confusing message earlier

-x-
Chetana



- Original Message 
From: Trevor Talbot <[EMAIL PROTECTED]>
To: sqlite-users@sqlite.org
Sent: Monday, October 8, 2007 9:10:22 PM
Subject: Re: [sqlite] many-one relation


On 10/8/07, chetana bhargav <[EMAIL PROTECTED]> wrote:



We got two tables, tableA & tableB.

tableB is turning out to be a many-one relation where in we have many rows of 
tableA mapping to one row of tableB, would like to know what is the best way to 
delete a row in tableB

1. Keep a reference count of the number of rows that are referring to this ( to 
be honest I dont think this is good)
2. More on similar lines instead of count have row-ids and attach a trigger



I'm not clear on the scenario here.  Is this a like a foreign key
relationship, so you want a delete of a row in table B to delete all
referring rows from A?   Something else?

-
To unsubscribe, send email to [EMAIL PROTECTED]
-


  

Tonight's top picks. What will you watch tonight? Preview the hottest shows on 
Yahoo! TV.
http://tv.yahoo.com/ 




-
To unsubscribe, send email to [EMAIL PROTECTED]
-



Re: [sqlite] A Question: Use of the sqlite3_exe( ) function?

2007-10-08 Thread John Stanton

Lee Crain wrote:

Igor,

I have a question. 


Why is it "highly recommended" to use the function call sequence you
iterate in preference to the sqlite3_exe call, since it is implemented
using that sequence?

Lee Crain

The callback method did not turn out to be such a good idea in practice 
and was superseded by a more flexible and powerful approach.  Write a 
few programs and the benefit becomes very obvious.


Using the prepare, step API's also encourages you to write more 
efficient and robust programs by pre-compileing the SQL.


-
To unsubscribe, send email to [EMAIL PROTECTED]
-



Re: [sqlite] 'dump' from the C API

2007-10-08 Thread John Stanton
Does anyone know how to do a database dump from the C API? I'm linking 
to sqlite3.o (the amalgamated file) and was hoping to open a database 
and dump the contents to an sql file.


I know this can be done from the shell, but as of yet, I can't get the 
shell running on AIX.


What version of AIX are you running?  Are you using xlC or gcc?

On some versions of AIX you need to compile Sqlite without debug (no -g) 
when using gcc.


You need to have readline installed to make a nice to use sqlite3 shell 
program.


-
To unsubscribe, send email to [EMAIL PROTECTED]
-



Re: [sqlite] built in function hex

2007-10-08 Thread Andy Goth
On Mon, 8 Oct 2007 10:03:40 -0700 (PDT), Ken wrote
> is there a built in function to display numeric values as 
> hexidecimal strings?  to_hexstr (40) would display 0x28

hex() isn't quite what you want.  hex(40) gives 3430, since '4' is 0x34 and
'0' is 0x30.  Moving on...

This is the sort of work I prefer to leave up to the application using SQLite.
 SQLite is a talented, efficient, obedient, and tireless reference librarian,
but asking SQLite to write your thesis is going too far!  But you can sure ask
it for all the facts, arguments, and bibliographic references you need.  It's
your job (or, ditching the school metaphor, the application's job) to present
the data to the consumer in a format palatable to same.

Of course, that's just my take on things.

If you still think you have a need for to_hexstr(), you can add it very
easily.  See http://www.sqlite.org/tclsqlite.html#function .  (By the way,
Author, you might want to rename the example function to not collide with the
built-in hex() function.)

-- 
Andy Goth
<[EMAIL PROTECTED]>

-
To unsubscribe, send email to [EMAIL PROTECTED]
-