Is 'ForeignKey' broken in SQLObject?

[Todd Greenwood]

It appears that the ForeignKey feature of SQLObject is broken:

REPRO:
$ tg-admin sql drop
$ sqlite > drop table ... #drop each table if they are still there
$ tg-admin sql sql #see below
$ tg-admin sql create #see below
$ tg-admin shell (starts an ipython session)
1: p = Page(pagename="FooFoo")
4: dir(p)
'addColumn',
'addIndex',
'addJoin',
'byPagename',

ERROR:
- there should be an addEntry in the Page object 'p'

Note: this is a slightly different issue than the other recent
foreignkey post, as here, the tables are being created, but the foreign
key relation is not showing up, that I can see...

#model.py
class Page(SQLObject):
pagename = StringCol(alternateID=True, length=30)
entries = MultipleJoin('Entry')

class Entry(SQLObject):
data = StringCol()
page = ForeignKey('Page')

Page.createTable(ifNotExists=True)
Entry.createTable(ifNotExists=True)

#output from tg-admin sql sql:
Using database URI
sqlite:///home/tgreenwo/working/turbogear/toddswiki/wiki_db
CREATE TABLE entry (
id INTEGER PRIMARY KEY,
data TEXT,
page_id INT
);

CREATE TABLE page (
id INTEGER PRIMARY KEY,
pagename VARCHAR(30) NOT NULL UNIQUE
);

#output from sqlite > .dump
sqlite> .dump
BEGIN TRANSACTION;
CREATE TABLE page (
id INTEGER PRIMARY KEY,
pagename VARCHAR(30) NOT NULL UNIQUE
);
CREATE TABLE entry (
id INTEGER PRIMARY KEY,
data TEXT,
page_id INT
);
COMMIT;

Am I missing something here?

BTW - Yes, I started a new topic, as I have had no response on the
previous thread...

[Kevin Dangoor]

On 10/25/05, Todd Greenwood <t.green...@gmail.com> wrote:
> 1: p = Page(pagename="FooFoo")
> 4: dir(p)
> 'addColumn',
> 'addIndex',
> 'addJoin',
> 'byPagename',
>
> ERROR:
> - there should be an addEntry in the Page object 'p'

I just doubled checked the docs for SQLObject, and this is actually
not the case. Maybe, for consistency's sake, it should be the case,
but it isn't strictly necessary.

In a many-to-many relationship, the add* method is created, because
you have to explicitly add the relationship, since the relationship is
stored in its own table.

In a one-to-many relationship, you don't really need it. Consider:

e = Entry(data="blah", page=p)

p.entries will then list this Entry.

Kevin

--
Kevin Dangoor
Author of the Zesty News RSS newsreader

email: k...@blazingthings.com
company: http://www.BlazingThings.com
blog: http://www.BlueSkyOnMars.com

[Todd Greenwood]

Great, thanks Kevin. I'll put this in my next tutorial extension.
Somehow this escaped me in the docs. The django has an OR mapping more
like what I was expecting...

-Todd

[Todd Greenwood]

On a related note: Is it possible to use orderBy on a secondary table?
For example:

#this is not correct, but shows what I'd like
Page.byPagename('FrontPage').entries(orderBy='revision')

#nor is this correct
Page.byPagename('FrontPage').entries.select(revision=1)

#From the docs, this looks to be the correct path:
for c in Entry.select(AND(Entry.pageID == Page.q.id,
Page.q.pagename=='FrontPage')):
print c

But this gives me:
ValueError: Unknown SQL builtin type: <type 'property'> for <property
object at 0x40842914>

#in a similar fashion...
list(Page.select())
<Page 1 pagename='FrontPage'>]

#again, this doesn't work as expected:
list (Entry.select(AND(Entry.pageID == Page.q.id,
Page.q.pagename=='FrontPage')))
ValueError: Unknown SQL builtin type: <type 'property'> for <property
object at 0x40842914>

Eventually, I suspect I'll use something like this to get the last
added wiki entry:
Entry.select(AND(Entry.pageID == Page.q.id,
Page.q.pagename=='FrontPage'), orderBy('revision)).reversed()[0:1]

So, how do I get there ?

-Todd

#model.py
class Page(SQLObject):
pagename = StringCol(alternateID=True, length=30)
entries = MultipleJoin('Entry')

class Entry(SQLObject):
data = StringCol()

revision = IntCol()

[Kevin Dangoor]

Which is?

I'm all for API improvements that make things easier. I haven't
studied Django's OR mapper in enough detail to know where it's better
or easier, so it's good to hear from people who've actually used both.

In this particular case, it may actually be confusing to have an
addEntry method. Consider this:

p1 = Page(..)
p2 = Page(..)
e = Entry(page=p1)
p2.addEntry(e)

So, what happens there? Does e's page change to p2?

Since you *have* to specify a page when creating an Entry, the
relationship is dealt with.

[Kevin Dangoor]

On 10/26/05, Todd Greenwood <t.green...@gmail.com> wrote:
>

> On a related note: Is it possible to use orderBy on a secondary table?
> For example:
>
> #this is not correct, but shows what I'd like
> Page.byPagename('FrontPage').entries(orderBy='revision')

Ian is talking about changing how some of the join machinery works for
0.8, so this may be a possible thing to get into that version.

>
> #nor is this correct
> Page.byPagename('FrontPage').entries.select(revision=1)
>
> #From the docs, this looks to be the correct path:
> for c in Entry.select(AND(Entry.pageID == Page.q.id,
> Page.q.pagename=='FrontPage')):
> print c

The trick with this is to take a look at what Class.q.* gets you.
Those things are basically placeholders for query expressions and they
understand the operators. If you do this:

print Entry.q.id == 10

you get:
(entry.id = 10)

which gives you an idea where you want to be. I think you want:

for c in Entry.select(AND(Entry.q.id == Page.q.id,
Page.q.paegname=='FrontPage'))

but I also think there's another parameter needed to tell sqlobject
that you're using the other table.

[Michele Cella]

Kevin Dangoor wrote:
> On 10/26/05, Todd Greenwood <t.green...@gmail.com> wrote:
> >
> > Great, thanks Kevin. I'll put this in my next tutorial extension.
> > Somehow this escaped me in the docs. The django has an OR mapping more
> > like what I was expecting...
>
> Which is?
>
> I'm all for API improvements that make things easier. I haven't
> studied Django's OR mapper in enough detail to know where it's better
> or easier, so it's good to hear from people who've actually used both.
>

Hi Kevin,

some times ago I came across this:

http://tabo.aurealsys.com/software/xmms-pl-patch/?p=101

here you can find some interesting thoughts about django and
turbogears, and also about django ORM vs SQLObject, maybe you (and Ian)
can find something useful.

Ciao
Michele

[Ian Bicking]

Kevin Dangoor wrote:
> On 10/26/05, Todd Greenwood <t.green...@gmail.com> wrote:
>
>>On a related note: Is it possible to use orderBy on a secondary table?
>>For example:
>>
>>#this is not correct, but shows what I'd like
>>Page.byPagename('FrontPage').entries(orderBy='revision')
>
>
> Ian is talking about changing how some of the join machinery works for
> 0.8, so this may be a possible thing to get into that version.

I'm not sure I understand .entries... but I think you can do:

Page.byPagename('Frontpage').entries.orderBy('revision')

*If* entries is a SQLMultipleJoin. SQLMultipleJoin -- in spirit if not
actual code -- will be the default kind of join when joins are
refactored for 0.8.

When using SQLMultipleJoin, aPage.entries will be 100% equivalent to
Entry.selectBy(pageID=aPage.id).

--
Ian Bicking / ia...@colorstudy.com / http://blog.ianbicking.org

[Krys Wilken]

As I recall, Django does give both options and can be confusing.

I went from Django to turbogears, and personally, SQLObject is much more
intuitive and more pythonic.

While there may be features in Django that we might want to look at,
(auto-crud, generic views (generic controllers and templates), etc.) but
I don't think there is much in Django's ORM "to write home about" (at
least compared to SQLObject).

Again, this is my personal view, but Django's model is part of what made
me leave Django for TG.

If I could remember any details about Django's models I would mention
them, but I guess this just means that is was not that remarkable.

Sorry to be negative. I hope there is something constructive in here.
I'm sure others will have differing opinions.

Krys

[Peter Kahle]

Kevin Dangoor wrote:

>On 10/26/05, Todd Greenwood <t.green...@gmail.com> wrote:
>
>
>>Great, thanks Kevin. I'll put this in my next tutorial extension.
>>Somehow this escaped me in the docs. The django has an OR mapping more
>>like what I was expecting...
>>
>>
>
>Which is?
>
>I'm all for API improvements that make things easier. I haven't
>studied Django's OR mapper in enough detail to know where it's better
>or easier, so it's good to hear from people who've actually used both.
>
>In this particular case, it may actually be confusing to have an
>addEntry method. Consider this:
>
>p1 = Page(..)
>p2 = Page(..)
>e = Entry(page=p1)
>p2.addEntry(e)
>
>So, what happens there? Does e's page change to p2?
>
>Since you *have* to specify a page when creating an Entry, the
>relationship is dealt with.
>
>Kevin
>

I've been giving this some thought for other reasons, and I'd think
addEntry would be a thin veneer on Entry() that automatically sets page
to the current page's id. That would clear up the uncertainty above, but
doesn't buy much other than a slightly clearer syntax.

P

[Todd Greenwood]

I'm all for clearer syntax, but let me clear up what I was suggesting.

p1 = Page(...)
p1.addEntry(Entry(...)) or p1.addEntry(...)

Not:

p1 = Page(...)
e = Entry(***page = p1***)
p1.addEntry(e)

Specifically, I'm suggesting that SQLObject introspect the added object
at this point, and make the nec primary key to foreign key mapping
automatically:

p1.addEntry(Entry(...)) #Internally, the Entry.page is set to p1 by
SQLObject, not the programmer

As an example of how Django does this sort of thing, here is a trimmed
down example from their web site:

The Django usage of this would be like so:

# Create a Reporter.
>>> r = reporters.Reporter(first_name='John')

<snip>

# Create an Article via the Reporter object.
>>> new_article = r.add_article(headline="John's second story")

http://www.djangoproject.com/documentation/models/many_to_one/

Model source code

from django.core import meta

class Reporter(meta.Model):
first_name = meta.CharField(maxlength=30)

class Article(meta.Model):
headline = meta.CharField(maxlength=100)
reporter = meta.ForeignKey(Reporter)

API reference
Reporter objects have the following methods:

* add_article()
* delete()
* get_article()
* get_article_count()
* get_article_list()
* save()

Article objects have the following methods:

* delete()
* get_reporter()
* save()

-Todd

[Kevin Dangoor]

p1.addEntry(column1=foo, column2=bar) sounds good to me.

I think p1.destroySelf exists because there's a delete classmethod.

Thanks for the enumeration of methods. I do think these would make a
friendlier API.

Kevin

[Ian Bicking]

Kevin Dangoor wrote:
> p1.addEntry(column1=foo, column2=bar) sounds good to me.

I'm -1 on methods appearing due to other attributes, if it's at all
possible to avoid it.

This would be okay with me, though:

class Page(SQLObject):
entries = ManyToOne('Entry')

p = Page(name='FrontPage')
p.entries.create(log='why I did this')

Another aspect is that 'add' shouldn't create things, it should just add
things, as you would to a set. We already have add methods with
RelatedJoin, and they don't create new objects. Just like sets.Set.add.

This would be a good compliment to demonstrate:

class Page(SQLObject):
authors = ManyToMany('User')

p = Page(name='FrontPage')
p.authors.add(User.byUsername('ianb'))

Though this could also have a "create()" method. I don't really like an
add method for ManyToOne, though, since it mutates its argument; but for
ManyToMany it doesn't.

[Kevin Dangoor]

On 10/28/05, Ian Bicking <ia...@colorstudy.com> wrote:
> Kevin Dangoor wrote:
> > p1.addEntry(column1=foo, column2=bar) sounds good to me.
>
> I'm -1 on methods appearing due to other attributes, if it's at all
> possible to avoid it.
>
> This would be okay with me, though:
>
> class Page(SQLObject):
> entries = ManyToOne('Entry')
>
> p = Page(name='FrontPage')
> p.entries.create(log='why I did this')
>
> Another aspect is that 'add' shouldn't create things, it should just add
> things, as you would to a set. We already have add methods with
> RelatedJoin, and they don't create new objects. Just like sets.Set.add.

I like your example above as well. Doing things like that will allow
for an API that is more consistent.

Kevin