[Zope-DB] How can I dynamically generate a Z Search Interface?
Mon, 9 Sep 2002 13:43:08 -0700
> On Sat, Sep 07, 2002 at 08:25:39PM +0200, Dieter Maurer wrote:
> > Jim Penny writes:
> > > On Fri, Sep 06, 2002 at 08:33:00PM +0200, Dieter Maurer wrote:
> > > ...
> > > > Database adapters can be called to get a database connection.
> > > >
> > > > A database connection has a "query" method to send SQL commands
> > > > its database.
> > > >
> > > > Going this route lets you leave the well documented path.
> > > > You will probably need to read sources and experiment...
> > >
> > > Moreover, you will lose some things and gain nothing.
> > I disagree...
> > > You gain nothing, because you will still have to worry about SQL
> > > Injection problems, that is, you will still have to properly quote
> > > arguments. Mostly this is easy, but if you use IN clauses in your
> > > queries, it can be a bit tricky.
> > This is true, when he calls the External Method with the generated
> > SQL. But, I expect that he will generate the SQL in the External
> > Method. Then, the input is innocious: a selections of fields
> > that should be output. They can easily be quoted or checked for
> > validity.
> Well, not really. dtml-sqlquote will not work for input like:
> select first_name, last_name from foo where first_name in
> ('john', 'pete', 'dieter')
> (here assuming that string """('john', 'pete', 'dieter')""" is passed
> as single parameter. While the substrings can be sql_quoted
> during the string construction phase, they cannot be easily quoted in
> the ZSQL method or external method.)
I have done just enough coding to appreciate wisdom in
> > > More importantly, you lose two things. Fist, you will probably
> > > connection caching (or have to do it yourself). For many
> > > this is a biggy, as this can take tenths of seconds.
> > No, we are still above this layer.
> > We use the same interface used by Z SQL Methods.
> > We just bypass Z SQL Methods.
> OK, you may well be right, that approach did not occur to me. I have
> used straight DB API 2.0 before, and I thought that was what you were
> referring to.
> > > Second, you lose testability (and thus, most probably,
> > I agree, that Z SQL Methods allow for nice testing.
> > But, it is also not difficult to test the External Method.
> Ahh, but in the realm that Tena seems to want to operate in, it is.
> S/He has the idea, that appears to occur to every newbie (and I was no
> exception), that it is a real problem to define all those dozens of
> ZSQL methods that are used in a folder, and that life would be much
> better if only he could define a single efficient, secure, understanble,
> and completely general way of creating a dynamic ZSQL method that
> generates the body on the fly and therefor frees all the clutter.
For the record, I am he.
You have summed up the essence of my thoughts, ahh, I think ;).
> Alas, my experience is that it does not work that way. You can replace
> everything with a single insecure ZSQL method, or equivalently with a
> single insecure External Method. And, not only do you lose on security,
> you also lose on auditability and/or debugging. If you use straight
> python DBAPI, you also probably lose on speed.
> And, as far as I can tell, it is in fact much harder to debug the
> external method - the SQL is being dynamically generated, and if the
> template is as flexible as what I think this fellow wants, essentially
> completely free form. So, the generated string used to call the SQL
> method is in RAM somewhere, and unless your database supports query
> logging, may be difficult to recover. (Or you have to write your own
> query logging facility.)
Recovery is an issue with me and I certainly cannot
afford to write my logging facility. This sounds
like reinventing a wheel. (But people do reinvent
wheels because existing wheels are less than perfect.
> This is not to say that ZSQL methods are perfect. They are not. Nor
> am I saying that ZSQL methods cannot be abused. I have
> seen at least one horror that relied on triply nested dtml-sqlgroup
> clauses. I thought it to be anathema. Emailing the author, he had the
> same idea this fellow appears to have, that he should develop one very
> complicated but very general method that did everything he could think
> of. But he could no longer understand what his own method did!
> In fact, my advice is that this is a dead end. Develop using many
> simple ZSQL methods. "It is a gift to be simple." But, "be as
> simple as possible, but no simpler."
I hear more wisdom out of experience here.
That said, would you mind giving me a critique of
the skeleton of code I started to write?
}def foo (Columns, table):
} # Columns contain names of columns
} # table is the name of the table ;)
} finalresult = "" # what this method returns as table in
} qry_str = "select " # query string
} results =  # result of query
} # open Oracle connection
} dCon = dco.connect("someone/password@aninstance") # gaping security
} dCur = dCon.cursor()
} # build query string
} qry_str = something_like_ "select bla1, bla2, bla3 from `table`"
} results = dCur.fetchall() # fetch results generated by the query
} # start assembly of the table in html
} finalresult = "<table border>"
} # generate column headings
} finalresult = finalresult + "`each entry in Columns`"
} # build rows of real data
} for each_row in results:
} finalresult = finalresult + "some_real_data </td>"
} finalresult = finalresult + "</table>"
} # all done, just return what got
} return finalresult
> That is, I am not saying that it is not possible to do what is being
> requested - after all, I was stupid enough to write a HOWTO on this -
> but I am saying that it is "unzopish", and probably ultimately, unwise.
I want to do things in a most zopish manner. Would
you mind sharing your HOWTO with me?