[DB-SIG] Re: [Python-Dev] database APIs

[Luke Kenneth Casson Leighton]

On Mon, Feb 03, 2003 at 04:31:28PM -0500, Kevin Jacobs wrote:
> On Mon, 3 Feb 2003, Luke Kenneth Casson Leighton wrote:
> >  my goal ultimately is to be able to write a file, maybe in
> >  XML or other suitable text file format, that specifies all the
> >  reports i want, that they are just tabular display of records,
> >  generation of an input form, or generation of create / delete
> >  input form.
> 
> We've thought about that, but had much more luck rendering reports via
> customer code with many helper objects and functions.  e.g., we have a class
> that emits HTML tables with pluggable data formatters so that our report
> code remains uncluttered.  

 i have started the process of moving away from actually having
 the HTML _in_ the python code, and using a package to template
 the HTML (apt-get install python-htmltmpl).

 what are your thoughts on such a move?

 personally i am not very impressed with python-htmltmpl it smacks
 of phpisms which always makes me shudder.

 is there anything better out there?


> We have very powerful data aggregation functions
> to slice-and-dice information in our OLAP-type reports.  We use CSS style
> sheets extensively so that our rendering code is abstract from many of the
> details of the final rendered output.  

 ah ha!  CSS style sheets.

 i always get confused by style sheets, and always mean to
 investigate them properly.

 netscape, which doesn't print properly it ignores the damn stylesheet
 (!) put me off.


> We even handle the difference between
> 'screen' and 'printed' formatting via style sheets, so that a single report
> renders very differently depending on where you send it.

 coool.

 do you have any advice on how to break up into pages because
 i need to add a "catalogue" / "brochure" and i'm running into
 lack of knowledge as to how to produce a mozilla-compatible
 HTML page that will print on the right A4 page boundaries.


> > >   2) Decode the variable names and map them to schema elements
> > >   3) Validate the variable values against the schema and business logic
> > >   4) Group variables into transactional batches and sub-batches
> > >      based on the physical schema
> > >   5) Render and execute SQL to perform updates
> > 
> > 
> >  what do you define as schema elements?
> >  i think i can work out what you mean by schema by context.
> 
> Schema elements are the relations and columns of your database backend.
> Those elements have type, unique key, foreign key, domain, and business
> logic constraints.

 okay.

 i get it.

 terminology.  okay.


 as i understand it, most of that can be specified _in_ the
 sql file, if you use a good enough sql server.

 e.g. at present i am relying on the ALTER TABLE FOREIGN KEY
 syntax, even though MySQL doesn't support that, to auto-generate
 code for JOINs and LEFT JOINs between tables.

 this Plan doesn't quite cover everything:

 - it doesn't cover reports although if MySQL supported VIEWs
   or even supported them but threw them away i WOULD look at
   specifying reports as VIEWs.

 - weird validation logic such as mentioned in one example:
   what to _do_ on errors in certain fields (red, flashing).

   i described how to deal with this in more detail yesterday,
   it can be bolted in, exactly as i believe you are already
   using.


> >  assuming that code to do all the above is auto-generated from
> >  either a SQL file (with CREATE and ALTER TABLE statements
> >  including FOREIGN KEY statements from which JOINs can be
> >  auto-generated) or from a separate IDL file, then code can
> >  be generated for the following:
> 
> Why not get them from the database at runtime?  
> It is easy to query the
> types, and only slightly harder to extract the unique and foreign keys.

 ... now why didn't i think of that? :)

 the only thing is that MySQL doesn't support foreign key
 constraints so those would be lost.


> However, your forms and reports will end up being very bland and unfriendly
> if you use a one-size-fits-all approach to generating them.

 i realise this.

 the Custom db/custom/html.py class (not the best place to
 have put this code i know) presently supports horizontal and
 vertical table and form display and entry.

 also i haven't put it in properly yet (path names hard-coded)
 but i am starting a move towards HTML-templates so you can
 specify code fragments, i mean it REALLY couldn't be simpler:

	from htmltmpl import TemplateManager, TemplateProcessor

	# Compile or load already precompiled template.
	template = TemplateManager().prepare("template.tmpl")
	tproc = TemplateProcessor()

	# Set the title.
	tproc.set("title", "Our customers")

	# Create the 'Customers' loop.
	customers = []

	# First customer.
	customer = {}
	customer["name"] = "Joe Sixpack"
	customer["city"] = "Los Angeles"
	customer["new"] = 0
	customers.append(customer)

	# Second customer.
	customer = {}
	customer["name"] = "Paul Newman"
	customer["city"] = "New York"
	customer["new"] = 1
	customers.append(customer)

	tproc.set("Customers", customers)

	# Print the processed template.
	print tproc.process(template)

 your HTML template, named (dur template.tmpl) must contain
 a tag <TMPL_VAR title> which will (dur) get substituted with
 the text.

 it's also possible to do loops, where the variable of the
 loop is expected to be None or a list of dictionaries.

 wow, hey what a coincidence, what does fetchall() return? :) :)



> I've looked at some of your code from "Custom" and it looks like a good
> start, though very verbose.  I'm sure you're developing some more powerful
> abstraction interfaces in "pysqldb", though I haven't had a chance to look.

 pysqldb contains the base classes i created that are equivalent
 to SQLbuilder and SQLObject

 it's not as complete as SQLObject although SQLObject i notice
 doesn't handle LEFT JOINs.

 pysqldb doesn't make it easy to do LEFT JOINs but it does cope:
 in any SELECT statement containing a list of tables, if any
 table starts with the text "LEFT JOIN " then it is
 handled differently from the other tables.

 tables=['people', 'addresses',
    """LEFT JOIN account_attributes ON
	 account_attributes.person_id = people.id"""]

 will generate:

 SELECT * FROM people , addresses
 LEFT JOIN account_attributes ON
 account_attributes.person_id = people.id

 rather than:
                                  v
 SELECT * FROM people , addresses ,
                                  ^ 
 LEFT JOIN account_attributes ON
 account_attributes.person_id = people.id

 where the end comma after addresses would obviously cause a
 SQL syntax error.

 stripping out any table name with LEFT JOIN in it into a
 separate list and processing them afterwards seems to
 do the trick:

 ljlist = filter(lambda x: x[:9] == 'LEFT JOIN', tablelist)
 tblist = filter(lambda x: x[:9] != 'LEFT JOIN', tablelist)

 from_stmt = ", ".join(tblist) + "\n" + "\n".join(ljlist)

 [i realise there are better ways to do the above 3 lines]


 also i notice you don't support the INSERT INTO ... SELECT
 syntax (i just had to add that into pysqldb last night due
 to some WEIRD requirements...)


> I'm still working on releasing some of my code so that I can show you how we
> handle some of these cases.  

 great!

> Unfortunately, we have some legally encumbered
> libraries that I have to remove with a chain-saw and then replace with
> clean-room code before I can do that.  Soon though...

 hey, we're not fussy.

 cvs commit it, fix it later _that's_ my motto :)


 if it helps with the decision process, usually the first
 open source project to offer needed functionality is
 the one that becomes the de-facto standard.

 kick up a political fuss about how some nerd named luke
 on the db-sig mailing lists is talking about writing
 alternate code with _his_ name all over it not your
 company's name, that should melt a few objections :)

 open source code is free advertising - there's money to
 be lost by not getting your code out first!

 l.