• 2006
  • July
  • 19
  • Learning and experience days are important (Taking a day to get up close and personal with SQLObject)

Learning and experience days are important (Taking a day to get up close and personal with SQLObject)
Written by on in Knight Errant, Snaking.


Decided this morning that I wanted more experience with TurboGears. I decided to re-implement the http log file analysis project I did a while ago as a TurboGears project (previously it generated static HTML files). The project basically performs whole-set analysis on about 150MB of log files to allow me to view hierarchically organised and summarised usage on the various web-sites I run.

This took far longer than I'd anticipated (so long that the core code is not yet finished after 7.5 hours of work), as I ran into a number of problems where finding the elegant SQLObject solution took a while (or didn't happen at all). The data is loaded and can be viewed as a page-tree. It's very slow, but it does work. No viewer/user views yet, and nothing for sorting/searching the results either (which is the kind of stuff I wanted to play with).

In no particular order here's where I ran into issues:
  • Couldn't use tg-admin sql create (same issue as before, tries to add constraints pointing to not-yet-defined tables). Write to file then use psql to execute.
  • SQL generation isn't producing SQL code to create the indices defined in sqlmeta.indexes. Eventually just wrote an sql file to run after creation.
  • Couldn't figure out how one is supposed to do a distinct query on anything other than the id of the table; that is, how to count the number of unique X where X is other than the table.id: eventually just used a raw SQL query to handle this
  • Couldn't figure out how to make AND( ) take an SQL string and a field==value constraint together, just coded the SQL string operation in Python as post-processing of the data-set
  • Took a while to find the accumulateMany method on sql result objects (to do multiple counting/reporting queries on a table with a single query)
  • Speed is currently unusably slow, likely just an application design problem (currently is calculating the counts on every view, wanted to get the core code working before optimising that)

Dealing with the abstraction layer really frustrated me in a number of places, places where I write dozens of queries a day just like them, but figuring out how to do it with the library took 20 or 30 minutes of googling and/or spelunking through the code. That's the nature of learning abstraction layers with a bit of an impedence mismatch I suppose (and ORMs are known for their impedence mismatch). Of course, the point of the whole exercise was that I was going to go beyond SQLObject and out into the rest of the TurboGears stack... guess I still have to work on the foundation a bit first.

Comments

  1. Rene Dudfield

    Rene Dudfield on 07/19/2006 5:50 p.m. #


    I found with SQLObject that you need to go do your own queries often.<br />
    <br />
    SQL is developed better, and there is more documentation.<br />
    <br />
    So I just use SQLObject as the basic model, and to do basic things. I am finding that I need better performance often too. So I end up rewriting some SQLObject parts not to use objects.<br />
    <br />
    If something is harder then I use SQL querys. Often it is good to put the sql queries in the objects.<br />
    <br />
    I lose some of the benefit of sqlobject by avoiding their abstractions... but SQLObject is not finished yet, so I don't want to waste time on trying to figure something out which isn't done anyway.<br />
    <br />
    At least if I put the queries in the model object, all of the SQL is in the same place. Rather than putting queries in other places.<br />
    <br />
    If you use your own base class, then you can move shared functionality into that too.<br />
    <br />
    I started a page to document sqlobject better. At the moment it is only the things I want documented, not actually documenting them yet. However I am going to fill the answers in soon. If there was a better place to put this page where other people could contribute that would be swell*.<br />
    <a href="http://rene.f0o.com/mywiki/SqlObject">http://rene.f0o.com/mywiki/SqlObject</a><br />
    <br />
    <br />
    <br />
    Have fun!<br />
    <br />
    eg.<br />
    <pre><br />
    class Event(SQLObject):<br />
    shortName = UnicodeCol()<br />
    description = UnicodeCol(length=10000)<br />
    linkTo = UnicodeCol(length=200)<br />
    linkToText = UnicodeCol(length=200)<br />
    active = IntCol()<br />
    <br />
    # ... more stuff cut out for brevity.<br />
    <br />
    def search_events(user_role, active, search_text)<br />
    the_sql = "SELECT event_times_event.event_id as ordered_event_id, max(times_event.end_date) as end_date from times_event, event_times_event, event where times_event.id = event_times_event.times_event_id AND event.id = event_times_event.event_id " <br />
    <br />
    if search_text:<br />
    search_parts = parse_search_text.get_parts(search_text)<br />
    search_text = search_parts['search_text']<br />
    <br />
    <br />
    # ... more complex stuff to modify the query.<br />
    <br />
    </pre><br />
    <br />
    * I love 50s terms!

  2. Cliff Wells

    Cliff Wells on 07/19/2006 9:09 p.m. #


    I found SQLObject to be the weak link in TurboGears as well. I'm planning on giving SQLAlchemy a try as soon as possible as I understand it addresses most of the shortcomings people complain about in SQLObject.<br />
    <br />
    Really, I've found little you can't do with SQLObject, it's simply figuring out how to do it that's painful. The documentation is too sparse, and although once you figure out how to do something it turns out it was actually easy, the figuring out just takes way too long. And unfortunately SQLObject is a bit too abstract to lend itself to reading the code either :P<br />
    <br />
    I also found Kid to be not to my liking, but one of the things that makes TG great is you can easily replace many components with your own (I use Stan rather than Kid for my presentation).<br />
    <br />
    That aside, TurboGears is great, but if you are going to do TurboGears development, I *highly* recommend using SVN, not the release. <br />
    <br />
    Good luck.

Comments are closed.

Pingbacks

Pingbacks are closed.