[orm-devel] Re: [orm-users] one2one using the primary key as the foriegn key

[Nigel Sim]

Thanks for the prompt reply.

I'll prepend this by saying I really like your work, and I have been
using it successfully for a while, but I need to make it work in a
situation which is slightly different to how it was designed, and really
want to know if there is a quick fix before I build a new one2one
relationship class. In this situation the tables are already in use and
can't be changed.

On Tue, 2004-06-22 at 15:34 +0200, Diedrich Vorberg wrote:
> Hi Nigel,
> 
> > How do you go about using a one2one mapping in the following situation.
> > |--------|   |-----------|
> > | Person |   | Picture   |
> > ----------   -------------
> > | ID     |<->| person_ID |
> > | Name   |   | etc       |
> > | etc    |   |           |
> > |--------|   |-----------|
> this is not the setup needed by a one2one relationship. It should look 
> like this:
> 
> |--------|   |-----------|
> | Person |   | Picture   |
> ----------   -------------
> | ID     |   |           |
> | ImageId|<->|           |
> | Name   |   | etc       |
> | etc    |   |           |
> |--------|   |-----------|
> 

I agree that method works, but really Person.imageId is redundant, there
will only ever be one picture for each person. Having a separate ID for
each one2one table will just get messy, and I am linking into an
existing system so changing this isn't an option.


CREATE TABLE `person` (
  `id` int(11) NOT NULL auto_increment,
  `name` varchar(255) NOT NULL default '',
  `number` varchar(50) NOT NULL default '',
  PRIMARY KEY  (`id`)
)

CREATE TABLE `picture` (
  `person_id` int(11) NOT NULL default '0',
  `date` datetime NOT NULL default '0000-00-00 00:00:00',
  `filename` varchar(255) NOT NULL default '',
  `caption` varchar(255) NOT NULL default '',
  PRIMARY KEY  (`person_id`)
) 

------------------------------------------------

from connect import ds

from orm.dbclass import dbclass
from orm.relationships import *
from orm.errors import *
from orm.columns import *
from orm.adapters.mysql.columns import *

# The datamodel:

class Person(dbclass):
	tableName = "person"
	columns = { "id": autoincrement(),
		 "name": varchar(),
		 "number": varchar() }

class Picture(dbclass):
	tableName = "picture"
	primaryKey = "person"
	columns = { "person": one2one(Person),
		"date": date(),
		"caption": varchar(),
		"filename": varchar()}

Person.columns["picture"] = one2one(Picture)

def main():
	new_person = Person(name="nigel",number="555 1234")
	ds().insert(new_person)

	new_picture = Picture(caption="nigel")
	new_picture.person = new_person        <<< Causes the following error

main()


------------------------------------------------
INSERT INTO person (number, name) VALUES ('555 1234', 'nigel')
SELECT LAST_INSERT_ID()
SELECT LAST_INSERT_ID()
Traceback (most recent call last):
  File "data.py", line 35, in ?
    main()
  File "data.py", line 32, in main
    new_picture.person = new_person
  File "/usr/lib/python2.3/site-packages/orm/dbclass.py", line 354, in
__setattr__
    self._data[name].set(value)
  File "/usr/lib/python2.3/site-packages/orm/relationships.py", line
322, in set    self.ds().execute("UPDATE %s SET %s=NULL WHERE %s=%s" % \
AttributeError: 'NoneType' object has no attribute 'execute'