#5 Problems with pickid [query] handling

[Jase Roberts]

Using v 0.0.3, downloaded as tarball mid-Jan, 2003

The old F1DB-style dbfield definition in the format
"pickid [SELECT id, name FROM tablename]" no longer
works. Two problems:

1) dbasis does not recognize dbfield field definitions
in this format. Whenever you go to edit a dbfield
record that uses [SELECT id, name FROM tablename],
dbasis assumes that it's getting a normal "pickid
tablename", and tries to run "EXECUTE("SHOW COLUMNS
FROM [select") in".

2) the same thing happens in pxdb.input when you try to
display a showform for an object using a dbfield in the
above format. Again, it tries to execute EXECUTE("SHOW
COLUMNS FROM [select")

[Hans Lellelid]

Logged In: YES
user_id=568541

There are some methods that are supposed to determine the
type of a pickid column -- these are probably in
pxdb_dbfield -- and these are probably simply not being
called by DBasis or even pxdb_input :-/ -- I think there
may be some other implications of that type of pickid,
however ... especially when it comes to searching,
filtering. It was fringe enough (and disruptive enough) to
be relegated to the backburner.

[Jase Roberts]

Logged In: YES
user_id=662162

It definitely is "fringe" functionality, and probably useful
almost exclusively when migrating existing F1DB sites to
Syntax. However, Nyk pointed out that it *can* be useful
when you want to integrate data from an non-Syntax table
(e.g. 3rd party zipcodes, polling, etc.) into a Syntax
installation.

I hadn't considered the search/filter implications. That
definitely does suggest we should use this feature as little
as possible.

[Nyk Cowham]

Logged In: YES
user_id=17438

I can appreciate having put this one on the back-burner with
regard to handling the case of 'filtering and search'.
However, I think as we go forward in trying to encourage
upgrades on older F1DB sites we are probably going to
encounter this more.

Note that I have started adding documentation to the Wiki
giving guidelines for data-modelling with Syntax and one
consideration is that there are no reasons to use this
feature unless you need to integrate data from tables
external to Syntax. I do think however, that such
flexibility is what makes Syntax stand-out from relatively
hard-wired systems (Midgard, ezPublish, Nuke etc.)

I think at the very least DBasis and pxdb_input::showform()
should recognize pickid definintions like this and handle
the display portion correctly. I'm less worried about
integrating it with search and filter. Since it should only
really be used for integration with 'unknown' tables then it
is not unreasonable to expect the user to write custom SQL
to retrieve the data.

Nyk

[Hans Lellelid]

Logged In: YES
user_id=568541

Yes -- that makes sense. Searching / filtering may not even
really be a problem, we'll see. There is clearly a bug in
pxdb_input::showform() if this functionality is not
correctly working; however, the logic should be implemented.
And it should be in pxdb_input::_get_values() primarily.
The other logic that may be causing problems is the
pxdb_dbasis method that returns the pickid type -- the
problem could simply be that it's not detecting the "SQL"
pickid type -- and hence the problem is bubbling up to the
input form, etc.

I know that it's pretty picky about what that SQL-style
field should look like: e.g. modeled on the few instances I
found in old f1db stuff ( e.g., I think the SQL statement
has to be surrounded with [ ] )

( Is there a Syntax mailing list...? :-) )

Hans