Presenting (with) Bruce

When I present at a meeting, I like to use plenty of live demos. I can get away with this, because I type very, very well. (I did get into IT via the secretarial pool, after all.) Still, even I get a little nervous about whether I can keep up typing speed and accuracy with the pressure of a whole audience staring at me.

Fortunately, This blog post by Andy Todd (You do read his blog, don't you?) pointed me to Bruce, a wonderful presentation tool freshly created by Richard Jones.

Bruce lets you wrap up decent-looking slides, graphics, and Python interpreter sessions all into a single presentation that scrolls smoothly along at your command. (Simply being spared the fuss of flipping between Presentation slides and a command-prompt window is a blessing.) For demos, you can pre-enter the text you want to type, then Bruce sends it to the interpreter one character at a time. You type 'dfjkasfjdska;dfsafd', but 'def plusOne(x): return x+1' appears on the screen, and the audience thinks you're a keyboard goddess.

My one disappointment in Bruce - as I understand it (I just found it, you know) - is that the wonderful interactive session can only be used with a Python session. I'll need to dig into Bruce and PyGame to verify that, and to see if it can be extended elsewhere. I dearly want to use it when I'm demonstrating SQL.

Until Bruce, incidentally, my best bet was TPP, the Text Presentation Program. I've seen presentations done with it, and it looks OK, except it's written in Ruby and has dependencies (ncurses) that need C compiling - not very practical where I am, unfortunately.

Search engines are not Zen

I decided that I should make myself a list of questions I've never been able to resolve, or that were very difficult to research, because they don't "search" well. That is, in everything from Metalink's Search bar to Google, it's hard to come up with relevant results.

For instance,

- I'm trying to use RMAN DUPLICATE, and it does indeed produce the data files on the auxiliary database, but it doesn't create a controlfile. The documentation clearly says it should. Searching on terms like RMAN DUPLICATE controlfile not generated only brings up more pages saying that it should.

- All sorts of questions like such-and-such service DOESN'T start, program WON'T run, logfile ISN'T created. Negation - "not" - is clearly a failing of a typical search. Searching for green eggs no ham will find you 10,000 pages on green eggs AND ham, with the one page on "My green eggs came without ham!" buried anonymously as result #4,312.

I'm not sure what I'll ultimately want to do with this list. But I began to wonder, more broadly, if someone out there had already started assembling issues of unsearchable questions. Ironically, Can't Find On Google is very easy to find on Google.

Dayton - Cincinnati Code Camp

Hooray! - to the Dayton .NET Developers Group for putting together the Code Camp on Jan. 21, 2006 - a whole day of community-provided seminars on a variety of programming topics. For free! I'm looking forward to it; in fact, I'm planning to present there (on Python, naturally).

If you're anywhere near southwest Ohio, come and enjoy. Better yet, come and share! Whatever cool programming tricks you have up your sleeve - come and show the rest of us!

LibraryLookup published

My article on LibraryLookup has been published in the October 2005 issue of Technology First, a tech newsletter for the Dayton area. (I'm on p. 8-9). (The print article's content ties in with this blog post)

The sixty-second version: LibraryLookup lets you query your library's catalog directly from a page of a book vendor like Amazon, looking up that particular book for you automatically with just one click. You can get it installed in your browser in, oh, about five seconds. It's really cool, not just for helping you find books, but also as an example of clever and sneaky use of JavaScript.

Why do almost all contracts and job postings specify exactly the software that they want you to use (or to have used)?

When they want to incorporate you tightly into a large project that has already gotten underway with some specific software, OK. But I think such over-specifying is also the custom in cases where that is not at all true. It's very annoying to somebody like me, who bizzarely imagines she might be able to select the best tools for her own work.

When I take my car to the mechanic, I don't say, "You must do all the work using a Sears Craftsman #3 box wrench." But maybe, if I were a writer of contracts, I would.

Announcing Dayton-Oracle mailing list

Q. How do Dayton-area users of Oracle stay in touch with one another and find out what's going on in their community?
A. They don't.

Q. Well, that's lame. What can we do about that?
A. You can sign up for the Dayton-Oracle mailing list I just set up at FreeLists.

Please spread the word!

cx_Oracle and Ubuntu

Naturally, after getting Oracle working, my next move was to get it Python-powered by installing cx_Oracle. Installation seemed to go OK, but when I tried to use it...

>>> import cx_Oracle
Traceback (most recent call last):
  File "", line 1, in ?
ImportError: libclntsh.so.10.1: cannot open shared object file: No such file or directory

which seemed odd, because

catherine@ubuntu:~$ locate libclntsh.so.10.1
/app/oracle/oracle/product/10.2.0/db_1/lib/libclntsh.so.10.1

I extrapolated a solution from an old mailing-list post by Karl Putland: I appended /app/oracle/oracle/product/10.2.0/db_1/lib to /etc/ld.so.conf and ran ldconfig.

That was the only trick - it's working fine now!

Oracle on Ubuntu

I love my new Ubuntu Linux (version 5.10). I love how easy it was to install, how it picked right up on my wireless card.

Oracle doesn't support Ubuntu, but I installed it anyway and it's doing fine. The process was tricky, though. Here's what I learned.

1. RTFM. The Installation Guide is very important, more so than when installing Oracle on Windows. It has to be the primary document you work from, and follow step-by-step. Any other advice on Oracle/Ubuntu that you may Google up is secondary. Where Red Hat and SuSE directions differ, follow the Red Hat ones.
2. This document is useful primarily for reassuring you that the error messages you get are OK.
3. Make sure the Universe is in your Synaptic repository and install these packages: gcc, make, libxp, libaio, lesstif2, lesstif2-dev, rpm, and libdb1.
4. You can't find all the packages that the install documents call for in the Ubuntu Universe. That seems to be OK; just find the ones you can.
5. The magic word is runInstaller -ignoreSysPrereqs.
6. One little misstep and you may get something broken. It took me a couple attempts. Try, try again, following that Installation Guide even more carefully.

Oracle - Python wish list

For my upcoming Oracle - Python talks at OOUG and (maybe) COLLABORATE 06, I want to write a nice Python script in realtime, before the very eyes of the audience, like a cooking show hostess would. I'm having trouble deciding on an objective for the script, though. The ideal script would make Oracle people sit up and scream, "I want that! That power must be mine!" It should also

• Be very Oracle relevant
• Be something that can't be done with SQL or PL/SQL alone
• But still use some SQL (to demonstrate cx_Oracle)
• Not duplicate the function of any existing Oracle utility or Oracle Enterprise Manager (this one worries me since I don't even know everything OEM can do)
• Be writeable within an hour (shouldn't be a problem)

I'm brainstorming for ideas, but I don't know if any of them are really "it".

• Compare parameters in INIT.ORA to live database (this is what I used for my paper)
• Condense or groom a TKPROF output file
• Populate a database table from a website (but what website? What's cool enough?)
• Find and compare TNSNAMES.ORA files scattered across filesystem. Find TNSNAMES entries that are "dead" (unpingable).
• Format alert.ora for better readability; color-code messages?
• Measure log switch frequency from alert.ora
• Find obsolete (unused) configuration files like init.ora, ORACLE_HOMEs, etc.
• Clean up PATH (mine gets cluttered with droppings from various installs/uninstalls)

Any comments on these? Anything you'd like to put on the wish list? Let your imagination run wild - what wishes would you make to a magic Oracle genie? That's the kind of "gotta have it" feeling I'd like to produce.

Ohio LinuxFest

I had a great time at Ohio LinuxFest. Highlights, in my book, were

• Jorge Castro's Ubuntu talk. He pointed us to a lot of great stuff to try out, and his enthusiasm was infectuous.
• Rich Bowen's mod_rewrite talk. I've never encountered Apache documentation that I was truly happy with, but Rich was wonderfully clear. I want his book.
• Ram Rao's virtualization talk. Xen sounds like it has jumped straight to #1 in my List of Reasons My Management Would Let Me Use Linux For The Database Server If They Had A Clue. Moving entire virtual machines easily between production and development servers, with almost no performance impact... wow, that would be incredible.
• The reception. Hey, the only thing better than dancing is dancing with geeks. Too bad I had to leave early, but somebody foolishly located Dayton too far from Columbus.

I heard that the attendance had more than doubled from the last year, and indeed, many of the people I talked to were quite new to Linux. That's the one improvement I'd suggest for the event. There were good talks for newbies, but not enough of them, nor did the schedule provide clear enough guidance to help newbies steer themselves that way.

COLLABORATE 06, featuring me, maybe

I just submitted a proposal for a technical session ("Python and Oracle: Coding that Cooks") at COLLABORATE 06, the IOUG's conference next April. Wish me luck! I'm not sure how hard it is to get a slot; I do know that the speaker quality there has been excellent for the past two years.

introducing sqlWrap.py

A Python script, for your use and suggestions.
Sorry, I know this is awfully long for a blog post - I have to figure out a better place to put it. I wonder if it would be appropriate for the Cheese Shop?

"""
sqlWrap
Adds convenience methods to DB-API2 connection objects.

Sep. 22, 2005 by Catherine Devlin (catherine.devlin@gmail.com, http://catherinedevlin.blogspot.com/)

This script is NOT intended as an object/relational mapper.
Rather, it helps experienced SQL users form their SQL more quickly.

Methods: insert, update, delete, select, genericSelect
These methods accept arguments for the WHERE, SET, etc. clauses
that should generally be provided as dictionaries; i.e.
whereClause = {'col1':'val1','col2':'val2%'} implies
  "WHERE col1 = 'val1' AND col2 LIKE 'val2%'",
setClause = {'col1':'val1'} implies 
  "SET col1 = 'val1'"
You may also pass object instances for whereClause and setClause,
with instance attributes corresponding to column names... but this 
has barely been tested!
They also automatically make use of bind variables, which have
performance and security benefits over hard-coding values in SQL.
The different ways of handling bind variables in various DB-API 
adapters are masked from the user.

Currently supports: Oracle (cx_Oracle), sqlite (pysqlite)

Sample usage:
# setup - unchanged from cx_Oracle
conn = OraConnection('scott/tiger@orcl') 
conn.cursor().execute('CREATE TABLE myTable (column1 varchar2(10), column2 varchar2(10), column3 varchar2(10))')
# now try out the sqlWrap convenience methods
conn.insert('myTable', setClause={'column1':'value1','column2':'value2'})
conn.insert('myTable', setClause={'column1':'value1a','column2':'value2a','column3':'value3a'})  
for row in conn.select(source='myTable'): 
    print row 
for row in conn.select(source='myTable', whereClause={'column1':'value1'}, resultProcessor=conn.dictionaryize):
    print row
conn.update('myTable', setClause={'column1':'value1','column2':'value2'}, whereClause={'column3':'value3'})
# as always, must explicitly commit  
conn.commit()
"""      

Get sqlWrap.py here

If you're in Ohio...

notes to self

Do not smash your head against a problem for consecutive hours. Instead:

• Ask for help. It doesn't matter whether you ever send the request; in the process of putting the problem into language, you're more than likely to solve it. (Yes, I know: "Pair programming!", you say. But I'm all alone here.)
• Stand up and walk away. Do not touch anything resembling a computer for at least ten minutes.

Will someone please remind me of these next time I get stuck?

Python for Oracle Geeks: unplugged!

I've been invited to present at the Oct. 27 Ohio Oracle User Group meeting in Columbus. Hooray! After I've practiced at a local group, I'll feel ready to start trying to get to wider areas, maybe even the national IOUG conference. I will bring tanker-trucks full of Python Kool-Aid!

We have a lot of catching up to do - Oracle's OTN has been publishing a LOT on PHP, and Oracle and Zend are providing a handy-looking integrated package. I salute the PHP folks, but I don't want that to be the only dynamic language active in Oracle-land.

sqlite: a flyswatter to kill flies

I'm now using sqlite to support an Oracle production database. I love it!

When I first went to download sqlite, I went away frustrated. I found an executable described as "A command-line program for accessing and modifying SQLite databases", and thought, "OK, so that's my SQL*Plus equivalent, but where's the actual server? The part that keeps the database process running?"

Because I have been an Oracle-only person so long, I didn't understand that sqlite doesn't need anything like that. It's simply this:

1. a single executable program that creates and modifies a database file
2. A database file

And that's it. Really! You can't imagine how dizzying and refreshing that is to someone who's spent hours before a certification exam memorizing Oracle architecture diagrams.

Anyway, there are other simple database engines, of course, but sqlite has gotten a lot of attention recently (like an Open Source Award) for its efficiency and its support across many languages. There's an excellent and honest rundown of its powers and limitations here.

So, anyway - why sqlite to support Oracle? Well, one of my Oracle instances has a bunch of logic applied to it by a nightly batch job. For every data record, a series of decisions are made, and our users ask questions like, "For record #12945, why did it decide X instead of Y last Tuesday?" And I have to be able to answer, "Well, the seventh of the nine tests conducted on that record determined that, since column 'product' was 'lutefisk' and 'quantity_kg' was 22, blah blah blah..." So all those decisions need to be logged every night.

That generates a quantity of data far outweighing the application data itself. It can be discarded after a week or two, but all that inserting and deleting was causing out-of-control generation of archive log files and making a mess of my disk space allocation. By moving that data out into a sqlite database, it becomes a single simple file that can be moved or deleted as easily as any other file.

So, thanks to sqlite, I'm living happily ever after. Hooray!

more on XML

The problem with my XML-generating views is that they break down for rows whose XML is longer than 4000 characters.

Meanwhile, the troubles I cited with XMLELEMENT really are limited to SQL*Plus. I was deterred at first, because I want to experiment with unfamiliar features through SQL*Plus first, but when I swallowed that reluctance and remembered that there are other ways to experiment with ad-hoc SQL - TOAD's SQL window, for instance - I was OK.

But then, I wanted to create views that would store the particular combinations of XMLELEMENTs I wanted for various circumstances. Unfortunately, I found that a SELECT query that runs fine can't be used to generate a working view when the result is longer than - you guessed it - 4000 characters.

I'm having a little trouble figuring out where exactly the 4000 character problem kicks in - at first I thought CLOBS were simply converted back to VARCHAR2's whenever I used the concatenation operator |, but that doesn't explain why views fail when the equivalent bare SELECT statements don't.

Anyway, the end of my adventure was that I simply wrote Python scripts to get what I want. I'm beginning to get some nice conveniences built into my personal wrapper for cx_Oracle, and maybe I'll eventually float it around for others' use. There are plenty of tools like SQLObject out there, of course, but as far as I know, they're all focused on OO programmers who hate to handle SQL directly or to think of their data as "rows in a table" rather than "instances of an object". I, on the other hand, think very naturally in SQL statements and relational concepts, and just use some functions to streamline my use of SQL.

Finally, there's one cute little possibility for producing XML that I haven't explored. SQL*Plus can generate an HTML table as output really easily. That could be coupled with a XSLT to produce XML.

querying XML without fighting with XDB

My coworker and I want to query some nice, simple XML from my Oracle table. For example, from the table
CREATE TABLE pet (name VARCHAR2(22), species VARCHAR2(22), weight_kg NUMBER);
I want to query up results like

  <pet>
    <name>Martin Luther</name>
    <species>cat</species>
    <weight_kg>6.2</weight_kg>
  </pet>
  <pet>
    <name>Jordache</name>
    <species>horse</species>
    <weight_kg>450</weight_kg>
  </pet>

My first thought was to use an XDB's XMLType View of my table. Okay... that looks... sort of intimidating. But I want to be able to query from it with the same old SQL I'm used to; I want to grab the XML for all rows
WHERE species = 'cat' OR (weight_kg > 100 AND name LIKE 'J%')
Now it looks really intimidating. I don't want to go back to school for a Master's of Science in XPath right now, and neither does my coworker.

Then I thought, "I know! I'll just use XMLForest, like this!"
SELECT XMLForest(name, species, weight_kg) FROM pet

On my platform (Windows 2003 / SQL*Plus 10.1.0.2.0 / Oracle 10.1.0.4.0 ), this produces
"Oracle SQL*Plus has encountered a problem and needs to close."
Attempted workarounds - like
EXEC SELECT XMLForest(name, species, weight_kg) INTO :petxml FROM pet
give me results like
"PLS-00801: Internal error [*** ASSERT at file pdw4.c, line 782; Cannot coerce between type 43 and type 30; _anon__2C9F5D70__AB[1, 7]]"
Okay, I give up - this XDB stuff is not mature enough for me today.

My handmade solution is fairly simple to use.
SELECT xml FROM pet_xmlvw WHERE species = 'cat' OR (weight_kg > 100 AND name LIKE 'J%')

Setting up views like pet_xmlvw is a headache and a half, but my ugly code will do it for you. (Can you hear PL/SQL crying? Can you hear it crying, "Somebody write Cheetah for me!"?)

CREATE OR REPLACE FUNCTION sf_columns_xml(table_name IN VARCHAR2)
RETURN CLOB
IS
  result CLOB := '';
  column_name VARCHAR2(30);
BEGIN
  FOR c IN ( SELECT utc.column_name
             FROM   user_tab_columns utc
             WHERE  utc.table_name = UPPER(sf_columns_xml.table_name) )
  LOOP
    column_name := lower(c.column_name);
    if length(result) > 0 
    then
      result := result || chr(10);
    end if;
    result := result || 
              '    <' || 
              column_name || '> '' || ' || column_name || ' || ''</' || 
              column_name || '>';
  END LOOP;
  RETURN result;
END;
/

CREATE OR REPLACE FUNCTION sf_table_xml(table_name IN VARCHAR2)
RETURN CLOB
IS
BEGIN
  RETURN 
    'CREATE OR REPLACE VIEW ' || lower(substr(table_name,1,24)) || '_xmlvw AS
SELECT ''
  <' || lower(table_name) || '>
' || to_char(sf_columns_xml(table_name)) ||
'
  </' || lower(table_name) || '>'' xml,
  ' || table_name || '.*
FROM   '  || table_name;
END;
/

Now running EXEC execute immediate TO_CHAR(sf_table_xml('PET')) will generate the view pet_xmlvw, whose column xml contains what I want.

To generate such views for all the tables in my schema, I run

spool buildXMLViews.sql
 select 'exec execute immediate to_char(sf_table_xml(''' || table_name || '''));'
 from user_tables
 where dropped = 'NO';
 select 'exec execute immediate to_char(sf_table_xml(''' || view_name || '''));'
 from user_views;
 spool off
 @buildXMLViews 

(Hey, I didn't use q'| |')! Well, you may not all be on Oracle 10g.)

Sometimes, it's a sausage factory in here. Don't look at how it gets done, just pass the ketchup.

Geek event aggregator

Click me!

I've written up an aggregator script (Python, of course) that browses an assortment of event announcement webpages and parses out the name/city/state/date information. Then I made an HTML DB application to serve it up according to your state or province (sorry, non-North-Americans).

No, I haven't written a brilliant AI to figure this out. It's just a bunch of regular expressions.

To do:

1. Mine from more event sources
2. Refactor the source code so it doesn't embarrass me and I can post it
3. Debug multiple hits for some events (InOUG especially)
4. Add non-North-American region support
5. Automate the mining (currently I kick it off and upload to HTML DB by hand)
6. get somebody to host the app in some more prominent site
7. Provide access to the pure XML as generated by the aggregator, so others don't have to go through the heck of parsing I do

Python for Oracle Geeks

IOUG posted my article, Python for Oracle Geeks, and referenced it in their 7/20/2005 "5 MINUTE BRIEFING: Oracle" e-mail. Ah, the fame, the adoring crowds, the paparazzi...