I have spent about 24 hours banging my head against the wall
with what appears to be about 2 related clsql bugs. We are using
clsql 3.6.1 on a linux 32 based ACL 8.0 and the postgres (version 8.x) socket
library. We have a mac version of the same ACL and the bugs are
consistent between the two.
Basically the bugs all have to do with creating joins and
immediate join slots.
We have a many to many table (with a little data of its
own):
(def-view-class subscription ()
((id
:reader id
:initform (clsql:sequence-next
'subscription-id)
:type integer
;; :db-kind :key
:db-constraints (:not-null :unique)
)
(publication-id
:initarg :publication-id
:reader publication-id
:type integer
:db-kind :key
:db-constraints :not-null
)
(sessionid
:initarg :sessionid
:reader sessionid
:type integer
:db-kind :key
:db-constraints :not-null
)
(user-fid
:initarg :user-fid
:reader user-fid
:type string
:db-constraints :not-null
)
(publication
:reader publication
:db-kind :join
:db-info (:join-class publication
:home-key publication-id
:foreign-key id
:set nil
:retrieval :immediate
)
)
(session
:reader session
:db-kind :join
:db-info (:join-class session
:home-key sessionid
:foreign-key id
:set nil
:retrieval :immediate
)
)
)
)
Which has 2 immediate join’s to the attached
“publication” and “session” tables. This itself
works fine. The problems all come when I try and create joins from those
tables into this table with its immediate fields.
For example in the publications table:
(subscriptions
; improper join 6/13
:reader subscriptions
:db-kind :join
:db-info (:join-class subscription
:home-key id
:foreign-key publication-id
:set t
)
)
This should return a list of subscription objects matching
the id binding. However it does a select on the fields of publication,
session, and subscription tables (fine) but with a loose where and no join:
;; 2006-06-13 19:48:07,,0 flektor-db1/flektor/flektor =>
SELECT
subscription.user_fid,subscription.sessionid,subscription.publication_id,subscription.id,publication.maxsequencenumber,publication.cursequencenumber,publication.name,publication.id,session.cm_port,session.cm_address,session.user_id,session.id
FROM subscription,session,publication WHERE ((subscription.publication_id = 5))
This is totally incorrect and pretty much a meaningless
select (with the multiple tables and the single where constraint).
Correct versions could either use an explicit join or could bind the pairs of
ids in the where (i.e. subscription.publication_id=publication.id and …
etc.).
In addition if I use a target slot (say in publication
again):
(subscriber-fids
:reader subscriber-fids
:db-kind :join
:db-info (:join-class subscription
:home-key id
:foreign-key publication-id
:target-slot user-fid
:set t
)
)
If I try this I get a mysterious “nil is not of
expected type hash-table”
If I try a :set nil I get another bogus identical select to
the first one (with all the fields and the loose join).
The closest I can get is:
(subscribers
:reader subscribers
:db-kind :join
:db-info (:join-class
subscription
; the list of (session subscription) pairs
:home-key id
:foreign-key publication-id
:target-slot session
:set t
)
)
Where I properly get a list of pairs of sessions and
subscriptions. And the query uses a inner join!
;; 2006-06-13 19:54:52,,0 flektor-db1/flektor/flektor =>
SELECT session.cm_port,session.cm_address,session.user_id,session.id FROM
session INNER JOIN subscription ON session.id = subscription.sessionid WHERE
(subscription.publication_id = 5)
But the fields of subscription are not fetched, so while it
returns an object of this type, all/most of the slots are bogus (unbound for
objects, stale #’s for numbers).
If I change the :set here to nil I get the bad unconstrained
where which gives me a bunch of properly constructed subscriptions (as
desired), but with the 3 tables and the single loose where there are many
copies of the same object (depending on dataset).
I have tried digging through the code for a bit, nothing
obvious. Am I missing something? Am I setting things up
correctly? It seems about the same as in the employee test.
Andy Gavin
Flektor, Inc.
RSS Feed