[Zope-dev] simple array-ish things in SQL/Zope

Jeffrey P Shell jeffrey@Digicool.com
Fri, 05 Mar 1999 11:32:21 -0500


>From: Jeff Bauer <jbauer@rubic.com>
> I do something similar with large text fields.  Rather than
> use PostgreSQL's unique (non-portable) array system, I store
> collections of primitives as strings and use eval() when reading
> the fields.
>
>   >>> aList = [1, 'a', 2.5]
>   >>> field_value = repr(aList)
>     ... perform some database insert operation
>     ... read the field back in
>   >>> aList = eval(field_value)
>
> This works well for lists and dictionaries that contain other
> lists, dictionaries, and primitive data types.  It provides a
> possible alternative to storing object data when you're
> required to use a relational database system.

Running eval on any external data source can be potentially risky.  An
option is to write pickles to the RDBMS.  This allows storage of much more
complex objects and it's harder for a third party with access to the
database to write a potentially damaging pickle to a field than it is to
write a potentially damaging expression.

The downside is that storing a string repr of a python list or dict is
still semi-searchable/readable through SQL for other operations.  A stored
pickle is not.

>> [Julian>>]
>> I am using PostgreSQL's array system for a "categories" field
>> in a database, it return records in a format like:
>> '{"foo"}'
>> '{"foo","bar"}'
>> '{"foo"}'
>>
>> I am trying to extract the list of unique categories - in
>> this example: ["foo","bar"]
>>
>> This would be a complete nuisance to do in DTML. I have decided
>> to read the SQL method's results into an external method and edit
>> them there - but first I need to know how to call a SQL method
>> without the "in" DTML tag.

Do you have to store it in an array?  If you can store the data in the
categories field in some sort of delimited list (like comma delimited), you
can use something like::

<!--#in sqlMyResults-->
 <H3><!--#var title--></H3>
 <H4>Categories:</H4>
 <UL>
  <!--#in "_.string.split(categories,',')"-->
  <LI><!--#var sequence-item--></LI>
  <!--#/in-->
 </UL>
 <HR NOSHADE>
<!--#/in-->

Conversely, if you had a user enter data in a form with checkboxes or a
SELECT list to choose categories, assuming that you use the Zope ":list"
type on the input form, you can do the following in an SQL method::

INSERT INTO something (title, categories)
VALUES (
 <!--#sqlvar title type=nb-->,
 '<!--#var "_.string.join(categories,',')"-->'
)

Another (and slightly better) way to do it would be to run the string.join
command in the DTML Method/Document that calls the SQL method that does the
insertion and pass it in as an argument.  Modify the previous SQL code to
look like the following::

INSERT INTO something (title, categories)
VALUES (
 <!--#sqlvar title type=nb-->,
 <!--#sqlvar categories type=string-->
)

And use the following DTML to insert the data that came in from the form::

<!--#call "sqlAddToMyTable(title=title,
             categories=_.string.join(categories,','))"-->

In essence, this is how I've used UserDB in some consulting projects that
allowed the customer to add/edit users in the system.  The column being
used for the users roles is a comma delimited field, and the interface
presented uses checkboxes to select which roles the user has.  The roles
are joined using the above, or split.

An extra benefit of using this is that I can use regular SQL expressions
to, say, list all of the users with role "Admin" by using SQL like the
following:

SELECT * FROM users WHERE user_roles LIKE '%<!--#var lookup_role-->%'

There's a myriad of ways of approaching this situation.  I'm no mega-SQL
guru, but the above approach has worked fine for me in situations where I
have a simple list of objects to store/retrieve from SQL.

.jPS | jeffrey@digicool.com
zope, through the web. www.zope.org