[Zope-DB] How can I dynamically generate a Z Search Interface?

[Tena Sakai]

      > 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
to
      > >  > > 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
individually
      > 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
your statement.
      >
      > >  > More importantly, you lose two things.  Fist, you will probably
lose
      > >  > connection caching (or have to do it yourself).  For many
databases,
      > >  > 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,
reliability).
      > > 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.
Mmmmmm.

      > 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.
Mumble, mumble...)

      > 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
html
}    qry_str = "select "                  # query string
}    results = []                         # result of query
}
}                                         # open Oracle connection
}    dCon = dco.connect("someone/password@aninstance")      # gaping security
hole!
}    dCur = dCon.cursor()
}                                         # build query string
}    qry_str = something_like_ "select bla1, bla2, bla3 from `table`"
}
}    dCur.execute(qry_str)
}    results = dCur.fetchall()            # fetch results generated by the query
string
}                                         # 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
created
}    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?