Re: [sqlite] RE: Maintaining Master-Child relationships

2007-03-29 Thread Martin Jenkins 

Arora, Ajay wrote:

Can anyone please look into my query,

I've tables
 
Master ( id integer primary key,

 field1 text)

And 


Child (id integer, name text)


Might be worth looking for Celko's Hierarchies in SQL. I've not read 
it but I've seen good reviews and Celko seems well regarded.


Martin

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

Re: [sqlite] RE: Maintaining Master-Child relationships

2007-03-29 Thread P Kishor 

On 3/29/07, Arora, Ajay [EMAIL PROTECTED] wrote:

Thanks for your reply.

I'm writing my application in Perl using SQLite as file based database.
Multiple users would be accessing the database simultaneously.But I'll
lock the file prior to any updations to the database. I'm planning to
use a third table to generate ids for the master table. But I don't know
how to select id INTO a variable which can be used while inserting data
into both the tables. Is it possible?



Maybe you are not explaining your problem completely, else, I don't
see what the problem is. Here is some Perl code to do the above
without requiring a third table. You can adjust it accordingly if you
insist on a third table.

# get your values to insert somehow
my $field1 = my field one;
my $name   = my name;

# enable transactions, if possible
$dbh-{AutoCommit = 0, RaiseError = 1};

eval {
 my $sth_master = $dbh-prepare(qq{
   INSERT INTO Master ( field1 ) VALUES ( ? )
 });
 $sth_master-execute( $field1 );

 my $master_id = $dbh-func('last_insert_rowid');

 my $sth_child = $dbh-prepare(qq{
   INSERT INTO Child ( id, name ) VALUES ( ?, ? )
 });
 $sth_master-execute( $master_id, $name );

 # commit the changes if we get this far
 $dbh-commit;
};

if ($@) {
   warn Transaction aborted because $@;

   # now rollback to undo the incomplete changes
   # but do it in an eval{} as it may also fail
   eval { $dbh-rollback };

   # add other application on-error-clean-up code here
}



-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of P
Kishor
Sent: 28 March 2007 17:42
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] RE: Maintaining Master-Child relationships


On 3/28/07, Arora, Ajay [EMAIL PROTECTED] wrote:
 Can anyone please look into my query,

 I've tables

 Master ( id integer primary key,
  field1 text)

 And

 Child (id integer, name text)

 My application receive values for field1 and name.  I need to populate
 master and child with incoming values using the same id.


1. Insert field1 in Master;
2. Get id of record inserted from #1 above;
3. Insert id from #2 and name in Child.

That is about all the help I can provide given that you have not
provide any further context... what language you are using, how you
are developing the application, etc.

SQLite does provide a 'last_insert_rowid' capability. Look on the
sqlite.org website.

if you are 100% sure that no one else will be fiddling with your
database, you can probably use something like

INSERT INTO Child( id, name)
VALUES ( (SELECT Max(id) FROM Master), name)


 A quick reply will be highly appreciated.

 Regards
 Ajay

 -Original Message-
 From: Arora, Ajay
 Sent: 28 March 2007 15:04
 To: 'sqlite-users@sqlite.org'
 Subject: Maintaining Master-Child relationships


 Hi,

 I've two tables in my database, a master and a child with ID  as a
 common key.

 I've created a table with one column to generate the sequence
number.How
 can I insert related records into both the tables using same ID?

 Thanks
 Ajay




--
Puneet Kishor http://punkish.eidesis.org/
Nelson Inst. for Env. Studies, UW-Madison http://www.nelson.wisc.edu/
Open Source Geospatial Foundation http://www.osgeo.org/education/
-
collaborate, communicate, compete
=

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

Re: [sqlite] RE: Maintaining Master-Child relationships

2007-03-29 Thread stephan nies 

I think using triggers would be a good solution:
You good just trigger on inserts and updates to parent
and do whatever  you  like to child.

Example:

CREATE TRIGGER sync_child_with_parent_insert
 BEFORE INSERT ON parent
   FOR EACH ROW BEGIN
 INSERT INTO child
(field_name_a,
 field_name_b,
 field_name_c)
 VALUES
(NEW.field_name_a,
 NEW.field_name_b,
 NEW.field_name_c);
END;

CREATE TRIGGER sync_child_with_parent_update
 BEFORE UPDATE ON parent
   FOR EACH ROW BEGIN
 INSERT INTO child
(field_name_a,
 field_name_b,
 field_name_c)
 VALUES
(NEW.field_name_a,
 NEW.field_name_b,
 NEW.field_name_c);
END;

On 3/29/07, P Kishor [EMAIL PROTECTED] wrote:


On 3/29/07, Arora, Ajay [EMAIL PROTECTED] wrote:
 Thanks for your reply.

 I'm writing my application in Perl using SQLite as file based database.
 Multiple users would be accessing the database simultaneously.But I'll
 lock the file prior to any updations to the database. I'm planning to
 use a third table to generate ids for the master table. But I don't know
 how to select id INTO a variable which can be used while inserting data
 into both the tables. Is it possible?


Maybe you are not explaining your problem completely, else, I don't
see what the problem is. Here is some Perl code to do the above
without requiring a third table. You can adjust it accordingly if you
insist on a third table.

# get your values to insert somehow
my $field1 = my field one;
my $name   = my name;

# enable transactions, if possible
$dbh-{AutoCommit = 0, RaiseError = 1};

eval {
  my $sth_master = $dbh-prepare(qq{
INSERT INTO Master ( field1 ) VALUES ( ? )
  });
  $sth_master-execute( $field1 );

  my $master_id = $dbh-func('last_insert_rowid');

  my $sth_child = $dbh-prepare(qq{
INSERT INTO Child ( id, name ) VALUES ( ?, ? )
  });
  $sth_master-execute( $master_id, $name );

  # commit the changes if we get this far
  $dbh-commit;
};

if ($@) {
warn Transaction aborted because $@;

# now rollback to undo the incomplete changes
# but do it in an eval{} as it may also fail
eval { $dbh-rollback };

# add other application on-error-clean-up code here
}


 -Original Message-
 From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of P
 Kishor
 Sent: 28 March 2007 17:42
 To: sqlite-users@sqlite.org
 Subject: Re: [sqlite] RE: Maintaining Master-Child relationships


 On 3/28/07, Arora, Ajay [EMAIL PROTECTED] wrote:
  Can anyone please look into my query,
 
  I've tables
 
  Master ( id integer primary key,
   field1 text)
 
  And
 
  Child (id integer, name text)
 
  My application receive values for field1 and name.  I need to populate
  master and child with incoming values using the same id.
 

 1. Insert field1 in Master;
 2. Get id of record inserted from #1 above;
 3. Insert id from #2 and name in Child.

 That is about all the help I can provide given that you have not
 provide any further context... what language you are using, how you
 are developing the application, etc.

 SQLite does provide a 'last_insert_rowid' capability. Look on the
 sqlite.org website.

 if you are 100% sure that no one else will be fiddling with your
 database, you can probably use something like

 INSERT INTO Child( id, name)
 VALUES ( (SELECT Max(id) FROM Master), name)


  A quick reply will be highly appreciated.
 
  Regards
  Ajay
 
  -Original Message-
  From: Arora, Ajay
  Sent: 28 March 2007 15:04
  To: 'sqlite-users@sqlite.org'
  Subject: Maintaining Master-Child relationships
 
 
  Hi,
 
  I've two tables in my database, a master and a child with ID  as a
  common key.
 
  I've created a table with one column to generate the sequence
 number.How
  can I insert related records into both the tables using same ID?
 
  Thanks
  Ajay
 


--
Puneet Kishor http://punkish.eidesis.org/
Nelson Inst. for Env. Studies, UW-Madison http://www.nelson.wisc.edu/
Open Source Geospatial Foundation http://www.osgeo.org/education/
-
collaborate, communicate, compete
=


-
To unsubscribe, send email to [EMAIL PROTECTED]

-

Re: [sqlite] RE: Maintaining Master-Child relationships

2007-03-29 Thread stephan nies 

CREATE TRIGGER sync_child_with_parent_update
 BEFORE UPDATE ON parent
   FOR EACH ROW BEGIN
 UPDATE child
SET
 field_name_a = NEW.field_name_a,
field_name_b,
 field_name_c)
 VALUES
(NEW.field_name_a,
 NEW.field_name_b,
 NEW.field_name_c);
END;

On 3/29/07, stephan nies [EMAIL PROTECTED] wrote:


I think using triggers would be a good solution:
You good just trigger on inserts and updates to parent
and do whatever  you  like to child.

Example:

CREATE TRIGGER sync_child_with_parent_insert
  BEFORE INSERT ON parent
FOR EACH ROW BEGIN
  INSERT INTO child
 (field_name_a,
  field_name_b,
  field_name_c)
  VALUES
 (NEW.field_name_a,
  NEW.field_name_b,
  NEW.field_name_c);
END;

CREATE TRIGGER sync_child_with_parent_update
  BEFORE UPDATE ON parent
FOR EACH ROW BEGIN
  INSERT INTO child
 (field_name_a,
  field_name_b,
  field_name_c)
  VALUES
 (NEW.field_name_a,
  NEW.field_name_b,
  NEW.field_name_c);
END;

On 3/29/07, P Kishor [EMAIL PROTECTED] wrote:

 On 3/29/07, Arora, Ajay [EMAIL PROTECTED] wrote:
  Thanks for your reply.
 
  I'm writing my application in Perl using SQLite as file based
 database.
  Multiple users would be accessing the database simultaneously.But I'll
  lock the file prior to any updations to the database. I'm planning to
  use a third table to generate ids for the master table. But I don't
 know
  how to select id INTO a variable which can be used while inserting
 data
  into both the tables. Is it possible?


 Maybe you are not explaining your problem completely, else, I don't
 see what the problem is. Here is some Perl code to do the above
 without requiring a third table. You can adjust it accordingly if you
 insist on a third table.

 # get your values to insert somehow
 my $field1 = my field one;
 my $name   = my name;

 # enable transactions, if possible
 $dbh-{AutoCommit = 0, RaiseError = 1};

 eval {
   my $sth_master = $dbh-prepare(qq{
 INSERT INTO Master ( field1 ) VALUES ( ? )
   });
   $sth_master-execute( $field1 );

   my $master_id = $dbh-func('last_insert_rowid');

   my $sth_child = $dbh-prepare(qq{
 INSERT INTO Child ( id, name ) VALUES ( ?, ? )
   });
   $sth_master-execute( $master_id, $name );

   # commit the changes if we get this far
   $dbh-commit;
 };

 if ($@) {
 warn Transaction aborted because $@;

 # now rollback to undo the incomplete changes
 # but do it in an eval{} as it may also fail
 eval { $dbh-rollback };

 # add other application on-error-clean-up code here
 }

 
  -Original Message-
  From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of P
  Kishor
  Sent: 28 March 2007 17:42
  To: sqlite-users@sqlite.org
  Subject: Re: [sqlite] RE: Maintaining Master-Child relationships
 
 
  On 3/28/07, Arora, Ajay [EMAIL PROTECTED] wrote:
   Can anyone please look into my query,
  
   I've tables
  
   Master ( id integer primary key,
field1 text)
  
   And
  
   Child (id integer, name text)
  
   My application receive values for field1 and name.  I need to
 populate
   master and child with incoming values using the same id.
  
 
  1. Insert field1 in Master;
  2. Get id of record inserted from #1 above;
  3. Insert id from #2 and name in Child.
 
  That is about all the help I can provide given that you have not
  provide any further context... what language you are using, how you
  are developing the application, etc.
 
  SQLite does provide a 'last_insert_rowid' capability. Look on the
  sqlite.org website.
 
  if you are 100% sure that no one else will be fiddling with your
  database, you can probably use something like
 
  INSERT INTO Child( id, name)
  VALUES ( (SELECT Max(id) FROM Master), name)
 
 
   A quick reply will be highly appreciated.
  
   Regards
   Ajay
  
   -Original Message-
   From: Arora, Ajay
   Sent: 28 March 2007 15:04
   To: ' sqlite-users@sqlite.org'
   Subject: Maintaining Master-Child relationships
  
  
   Hi,
  
   I've two tables in my database, a master and a child with ID  as a
   common key.
  
   I've created a table with one column to generate the sequence
  number.How
   can I insert related records into both the tables using same ID?
  
   Thanks
   Ajay
  


 --
 Puneet Kishor http://punkish.eidesis.org/
 Nelson Inst. for Env. Studies, UW-Madison http://www.nelson.wisc.edu/
 Open Source Geospatial Foundation http://www.osgeo.org/education/
 -
 collaborate, communicate, compete
 =


 -
 To unsubscribe, send email to [EMAIL PROTECTED]

 -

Re: [sqlite] RE: Maintaining Master-Child relationships

2007-03-29 Thread stephan nies 

sorry hit send by accident this should be:

CREATE TRIGGER sync_child_with_parent_update
 BEFORE UPDATE ON parent
   FOR EACH ROW BEGIN
 UPDATE child
SET
 field_name_a = NEW.field_name_a,
 field_name_b = NEW.field_name_b,
 field_name_c = NEW.field_name_c,
   WHERE
 field_name_a = NEW.field_name_a
END;

// assuming  field_name_a is the  id field

On 3/29/07, stephan nies [EMAIL PROTECTED] wrote:


CREATE TRIGGER sync_child_with_parent_update
  BEFORE UPDATE ON parent
FOR EACH ROW BEGIN
  UPDATE child
 SET
  field_name_a = NEW.field_name_a,
 field_name_b,
  field_name_c)
  VALUES
 (NEW.field_name_a,
  NEW.field_name_b,
  NEW.field_name_c);
END;

On 3/29/07, stephan nies [EMAIL PROTECTED] wrote:

 I think using triggers would be a good solution:
 You good just trigger on inserts and updates to parent
 and do whatever  you  like to child.

 Example:

 CREATE TRIGGER sync_child_with_parent_insert
   BEFORE INSERT ON parent
 FOR EACH ROW BEGIN
   INSERT INTO child
  (field_name_a,
   field_name_b,
   field_name_c)
   VALUES
  (NEW.field_name_a,
   NEW.field_name_b,
   NEW.field_name_c);
 END;

 CREATE TRIGGER sync_child_with_parent_update
   BEFORE UPDATE ON parent
 FOR EACH ROW BEGIN
   INSERT INTO child
  (field_name_a,
   field_name_b,
   field_name_c)
   VALUES
  (NEW.field_name_a,
   NEW.field_name_b,
   NEW.field_name_c);
 END;

 On 3/29/07, P Kishor  [EMAIL PROTECTED] wrote:
 
  On 3/29/07, Arora, Ajay [EMAIL PROTECTED] wrote:
   Thanks for your reply.
  
   I'm writing my application in Perl using SQLite as file based
  database.
   Multiple users would be accessing the database simultaneously.ButI'll
   lock the file prior to any updations to the database. I'm planning
  to
   use a third table to generate ids for the master table. But I don't
  know
   how to select id INTO a variable which can be used while inserting
  data
   into both the tables. Is it possible?
 
 
  Maybe you are not explaining your problem completely, else, I don't
  see what the problem is. Here is some Perl code to do the above
  without requiring a third table. You can adjust it accordingly if you
  insist on a third table.
 
  # get your values to insert somehow
  my $field1 = my field one;
  my $name   = my name;
 
  # enable transactions, if possible
  $dbh-{AutoCommit = 0, RaiseError = 1};
 
  eval {
my $sth_master = $dbh-prepare(qq{
  INSERT INTO Master ( field1 ) VALUES ( ? )
});
$sth_master-execute( $field1 );
 
my $master_id = $dbh-func('last_insert_rowid');
 
my $sth_child = $dbh-prepare(qq{
  INSERT INTO Child ( id, name ) VALUES ( ?, ? )
});
$sth_master-execute( $master_id, $name );
 
# commit the changes if we get this far
$dbh-commit;
  };
 
  if ($@) {
  warn Transaction aborted because $@;
 
  # now rollback to undo the incomplete changes
  # but do it in an eval{} as it may also fail
  eval { $dbh-rollback };
 
  # add other application on-error-clean-up code here
  }
 
  
   -Original Message-
   From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of
  P
   Kishor
   Sent: 28 March 2007 17:42
   To: sqlite-users@sqlite.org
   Subject: Re: [sqlite] RE: Maintaining Master-Child relationships
  
  
   On 3/28/07, Arora, Ajay  [EMAIL PROTECTED] wrote:
Can anyone please look into my query,
   
I've tables
   
Master ( id integer primary key,
 field1 text)
   
And
   
Child (id integer, name text)
   
My application receive values for field1 and name.  I need to
  populate
master and child with incoming values using the same id.
   
  
   1. Insert field1 in Master;
   2. Get id of record inserted from #1 above;
   3. Insert id from #2 and name in Child.
  
   That is about all the help I can provide given that you have not
   provide any further context... what language you are using, how you
   are developing the application, etc.
  
   SQLite does provide a 'last_insert_rowid' capability. Look on the
   sqlite.org website.
  
   if you are 100% sure that no one else will be fiddling with your
   database, you can probably use something like
  
   INSERT INTO Child( id, name)
   VALUES ( (SELECT Max(id) FROM Master), name)
  
  
A quick reply will be highly appreciated.
   
Regards
Ajay
   
-Original Message-
From: Arora, Ajay
Sent: 28 March 2007 15:04
To: ' sqlite-users@sqlite.org'
Subject: Maintaining Master-Child relationships
   
   
Hi,
   
I've two tables in my database, a master and a child with ID  as a
 
common key.
   
I've created a table with one column to generate the sequence
   number.How
can I insert related records into both the tables using same ID?
   
Thanks
Ajay
   
 
 
  --
  Puneet

[sqlite] RE: Maintaining Master-Child relationships

2007-03-28 Thread Arora, Ajay 
Can anyone please look into my query,

I've tables
 
Master ( id integer primary key,
 field1 text)

And 

Child (id integer, name text)

My application receive values for field1 and name.  I need to populate
master and child with incoming values using the same id. 

A quick reply will be highly appreciated.

Regards
Ajay

-Original Message-
From: Arora, Ajay 
Sent: 28 March 2007 15:04
To: 'sqlite-users@sqlite.org'
Subject: Maintaining Master-Child relationships


Hi,

I've two tables in my database, a master and a child with ID  as a
common key.

I've created a table with one column to generate the sequence number.How
can I insert related records into both the tables using same ID?

Thanks
Ajay


THE INFORMATION CONTAINED IN THIS MESSAGE AND ANY ATTACHMENT MAY BE PRIVILEGED, 
CONFIDENTIAL, PROPRIETARY OR OTHERWISE PROTECTED FROM DISCLOSURE. If the reader 
of this message is not the intended recipient, you are hereby notified that any 
dissemination, distribution, copying or use of this message and any attachment 
is strictly prohibited. If you have received this message in error, please 
notify us immediately by replying to the message and permanently delete it from 
your computer and destroy any printout thereof.

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

Re: [sqlite] RE: Maintaining Master-Child relationships

2007-03-28 Thread Mohd Radzi Ibrahim 

Use sqlite3_last_insert_rowid

- Original Message - 
From: Arora, Ajay [EMAIL PROTECTED]

To: sqlite-users@sqlite.org
Sent: Thursday, March 29, 2007 12:19 AM
Subject: [sqlite] RE: Maintaining Master-Child relationships



Can anyone please look into my query,

I've tables

Master ( id integer primary key,
field1 text)

And

Child (id integer, name text)

My application receive values for field1 and name.  I need to populate
master and child with incoming values using the same id.

A quick reply will be highly appreciated.

Regards
Ajay

-Original Message-
From: Arora, Ajay
Sent: 28 March 2007 15:04
To: 'sqlite-users@sqlite.org'
Subject: Maintaining Master-Child relationships


Hi,

I've two tables in my database, a master and a child with ID  as a
common key.

I've created a table with one column to generate the sequence number.How
can I insert related records into both the tables using same ID?

Thanks
Ajay


THE INFORMATION CONTAINED IN THIS MESSAGE AND ANY ATTACHMENT MAY BE 
PRIVILEGED, CONFIDENTIAL, PROPRIETARY OR OTHERWISE PROTECTED FROM 
DISCLOSURE. If the reader of this message is not the intended recipient, 
you are hereby notified that any dissemination, distribution, copying or 
use of this message and any attachment is strictly prohibited. If you have 
received this message in error, please notify us immediately by replying 
to the message and permanently delete it from your computer and destroy 
any printout thereof.


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






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

Re: [sqlite] RE: Maintaining Master-Child relationships

2007-03-28 Thread P Kishor 

On 3/28/07, Arora, Ajay [EMAIL PROTECTED] wrote:

Can anyone please look into my query,

I've tables

Master ( id integer primary key,
 field1 text)

And

Child (id integer, name text)

My application receive values for field1 and name.  I need to populate
master and child with incoming values using the same id.



1. Insert field1 in Master;
2. Get id of record inserted from #1 above;
3. Insert id from #2 and name in Child.

That is about all the help I can provide given that you have not
provide any further context... what language you are using, how you
are developing the application, etc.

SQLite does provide a 'last_insert_rowid' capability. Look on the
sqlite.org website.

if you are 100% sure that no one else will be fiddling with your
database, you can probably use something like

INSERT INTO Child( id, name)
VALUES ( (SELECT Max(id) FROM Master), name)



A quick reply will be highly appreciated.

Regards
Ajay

-Original Message-
From: Arora, Ajay
Sent: 28 March 2007 15:04
To: 'sqlite-users@sqlite.org'
Subject: Maintaining Master-Child relationships


Hi,

I've two tables in my database, a master and a child with ID  as a
common key.

I've created a table with one column to generate the sequence number.How
can I insert related records into both the tables using same ID?

Thanks
Ajay


THE INFORMATION CONTAINED IN THIS MESSAGE AND ANY ATTACHMENT MAY BE PRIVILEGED, 
CONFIDENTIAL, PROPRIETARY OR OTHERWISE PROTECTED FROM DISCLOSURE. If the reader 
of this message is not the intended recipient, you are hereby notified that any 
dissemination, distribution, copying or use of this message and any attachment 
is strictly prohibited. If you have received this message in error, please 
notify us immediately by replying to the message and permanently delete it from 
your computer and destroy any printout thereof.

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





--
Puneet Kishor http://punkish.eidesis.org/
Nelson Inst. for Env. Studies, UW-Madison http://www.nelson.wisc.edu/
Open Source Geospatial Foundation http://www.osgeo.org/education/
-
collaborate, communicate, compete
=

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