[silva-dev] Silva SQL Data Sources problems

[Marc Petitmermet]

dear developers

i had and still have a hard time with porting Silva SQL Data Sources  
from silva-0.9.1.13 to Silva SQL Sources of silva-1.1.5 (this is the  
version we are currently using here). checking the same stuff with  
silva-1.3beta3 i noted that some of the problems are still not fixed,  
although i thought so reading the history.txt, or new ones were  
introduced.

this is my sql query for silva-0.9.1.13 (this is a simplified version  
where i removed the order by part):

select records.author as 'Author(s)', concat(records.title, ' ', '<a  
href="/research/publications/details?id=', records.id ,'">', '»»', '</ 
a>') as Title, records.year as Year from records, reference_types  
where records.reference_type=reference_types.id and <dtml-var  
get_query_endnote_search>


for the newer silva versions i have to pull the above query apparat  
(and i think that the silva documentation needs improvement about how  
to do this):

(a) sql statement (zmi); this is the base structure of the query:

select <dtml-var columns> from <dtml-var table> where  
records.reference_type=reference_types.id and <dtml-var  
get_query_endnote_search>

(b) columns (smi); this is actually the "select" part of the sql query:

records.author as 'Author(s)', concat(records.title, ' ', '<a href="/ 
research/publications/details?id=', records.id ,'">', '»»', '</a>')  
as Title, records.year as Year

(c) table (smi); this is actually the "from" part of the sql query:

records, reference_types


now moving on to the problems:

encoding errors
---------------
(do we ever get rid of these errors;-)

1. theoretically, you can place the complete sql query in the zmi and  
ignore what authors set in columns and table fields. if you do this  
you get a nasty encoding error:

   Error Type: UnicodeEncodeError
   Error Value: 'ascii' codec = can't encode
   characters in position 131-132: ordinal not = in range(128)
   (traceback 1 below)

replacing the strings '»»' with '>>' resolves the problem but these  
are not the strings i want to have displayed. therefore, i replaced  
'»»' with = '&#x00BB'. not really user friendly. i have used the same  
strings in silva-0.9.1.13 without problems.

2. somehow along the development silva-1.3b3 has lost the ability to  
except non us-ascii strings in the "columns" field. when i enter the  
statment (b) in this field you get an even nastier error upon saving  
the document:

   Error saving your data.
   Response status: 500
   Check your server log for more information.

the log file says:

   Exception Type  UnicodeEncodeError
   Exception Value 'ascii' codec can't encode characters
                   in position 124-125: ordinal not in range(128)
   (traceback 2 below)

this error cannot be found in silva-0.9.1.13 and silva-1.1.5. i guess  
this is a kupu problem here because the forms editor does not complain.


html tags
---------
in silva-0.9.1.13 the html code in a sql query was correctly  
interpreted. in > silva-1.1.5 this has changed and the html code is  
not interpreted anymore as tags but as standard text. to change this  
behavior i had to modify the layout page template:

   original: <td tal:content="cell" />
   modified: = <td tal:content="structure cell" />


caption
-------
whatever setting i try i cannot get rid of the caption. even setting  
the default to be unchecked in the parameters. my solution was to  
just delete the part with the title in the layout page template. if i  
remember correctly this has something to do with a bug in silva  
external sources and checkboxes. this seems not to be fixed in  
silva-1.1.5 and silva-1.3beta3.


column ordering
---------------
silva-1.1.5 ignores the order of columns i set in the columns field;  
whatever i set there, the outcome seems to be random. as this has  
already been reported the problem is fixed in silva-1.3beta3. i'm  
"forced" to use silva-1.1.5 and can work around the encoding and  
caption problem. however, i cannot figure out how to resolve this  
random ordering. any hints?


suggestion
----------
make the batch size configurable by the author or even better, the  
user visiting the page which contains the Silva SQL Sources.


regards,
marc


1.
Traceback (innermost = last):
=A0 Module ZPublisher.Publish, line 101, in = publish
=A0 Module ZPublisher.mapply, line 88, in = mapply
=A0 Module ZPublisher.Publish, line 39, in = call_object
=A0 Module = Products.SilvaExternalSources.ExternalSource, line 215,  
in = index_html
=A0 Module Products.SilvaExternalSources.SQLSource, = line 68, in  
to_html
=A0 Module = Products.SilvaExternalSources.SQLSource, line 85, in =  
_get_data
=A0 Module Products.SilvaExternalSources.SQLSource, = line 124, in  
_set_sql_method
UnicodeEncodeError: 'ascii' codec = can't encode characters in  
position 131-132: ordinal not in = range(128)

2.
Traceback (innermost last):
   Module ZPublisher.Publish, line 101, in publish
   Module ZPublisher.mapply, line 88, in mapply
   Module ZPublisher.Publish, line 39, in call_object
   Module Products.SilvaDocument.Document, line 306, in PUT
   Module Products.SilvaDocument.Document, line 269, in  
transform_and_store
   Module Products.SilvaDocument.Document, line 180, in editor_storage
   Module Products.SilvaDocument.transform.Transformer, line 51, in  
to_source
   Module Products.SilvaDocument.transform.base, line 197, in convert
   Module Products.SilvaDocument.transform.kupu.html, line 237, in  
convert
   Module Products.SilvaDocument.transform.kupu.html, line 272, in  
convert
   Module Products.SilvaDocument.transform.kupu.html, line 198, in  
fix_structure
   Module Products.SilvaDocument.transform.kupu.html, line 112, in  
fix_toplevel
   Module Products.SilvaDocument.transform.kupu.html, line 797, in  
convert
UnicodeEncodeError: 'ascii' codec can't encode characters in position  
124-125: ordinal not in range(128)