Swivel

Darn. I didn't think of Swivel. Then again, you didn't think of it, either.

Swivel is described as "YouTube for data". Not just for uploading and viewing data... but also for madly cross-tabulating. Wow!

As a database professional, I'm kind of embarrassed that two physicists came up with it. But I'm the spouse of a physicist... so I'm kind of proud, too.

Anyway, I was interested in seeing what sort of data was in there on gender issues... so I searched Swivel on "gender" and got... no hits. Wow. I decided to look for some data worth uploading - say, that frightening "Balancing the Equation" study showing a steady drop of women working in information technology. Well, that study appears to be in send-us-cash-and-we'll-mail-it-to-you form; in fact, I don't see a lot of raw, upload-worthy data out on the web at all. Hmm. Any ideas?

There's got to be some mailing list of Swivel contributors out there somewhere; I guess I should find it and ask them where they find their raw data.

I'm dying to know what RDBMS they run on...

PyCon 2007

PyCon is coming! February 23-25, 2007 in Dallas!

I had the unexpected honor this year of serving on the Program Committee - the people who read the submitted abstracts and argue about which talks to include. The decisions were not easy! We had more excellent proposals than we could fit into the schedule... I wonder if we'll discuss scheduling four days for PyCon 2008. I think we've outgrown three days! We do have plenty of time set aside for Open Space talks and Lightning Talks, however, and I hope the presenters who didn't get into the regular schedule will show their stuff there.

Just reading the proposals was a great educational experience - I picked up news and ideas that have helped me quite a bit already. The actual conference is going to be incredible - though it will probably include plenty of those "Noooo! I want to be in all three seminars at once!" moments.

The hotel's conference rate is actually a good deal for the quality of the hotel - I know that's not always the case at conferences - but the number of rooms available at that rate is pretty limited, so consider getting your reservation right away. With so much good stuff going on from morning till night, it's really nice to "commute" via the elevator.

I'm all grown up!

I'm finally a serious open-source participant!

... by which I mean...

• I was using a software package (SQLAlchemy),
• found a place where it didn't meet my needs,
• submitted an enhancement, together with test case,
• and had the enhancement taken up into the project trunk.

I've got a second (less trivial) enhancement submitted but not yet incorporated, too. If you're using SQLAlchemy and you need to use reflection on tables that you're reaching through a database link, you'll need to do it via synonyms and grab code from this ticket. I hope it'll get into the trunk eventually, but it's a low-priority specialty need. (Unless, of course, you need it, in which case there's nothing low-priority about it!)

I guess I've been through the process before with SQLPython, via private email with its creator... it just felt so formal this time, with a Bug Tracker and Ticket Numbers and everything.

Anyway, the whole process takes a little puzzling out... but wow, it's FUN!

An agile January to you

There's going to be a fantastic week of agile programming activities in this area this coming January!

Begin with The TurboGears Jam in Ann Arbor, MI, Jan. 14 - 16.

On the 17th, drive two hours southeast to Sandusky, OH.

Then, Jan. 18 - 19, attend CodeMash at Kalahari Resort.

I don't know if they planned these events to dovetail so well, but Bruce Eckel is deeply involved in both, so perhaps they did.

Girls in IT

This Friday, there's a conference at Sinclair Community College here in Dayton (and other sites in Ohio) called We Are IT Day, designed to encourage high-school girls' participation in information technology.

We Are IT Day website
They're still accepting "lunch buddies" - technology professional women who can chat with a small group of girls over lunch.

And, if you know a girl here in Dayton who should be there, try to make sure she can go!

(Sorry I didn't publicize this earlier - I hadn't found a website for it.)

IronPython and Oracle

In my post on "Oracle-free Oracle access", I speculated that using IronPython with Oracle's .NET tools might be an effective way to access the database, but I'd never actually tried it.

Since then, I've been contacted by two people who've not only done it successfully, they've written up nice descriptions with code.

• Oracle Data Provider for .NET under IronPython by Przemek Piotrowski
• A minimal sqlplus clone in IronPython by Bernd Kriszio

I recommend both blogs - they're in my feed reader.

If you can't read Bernd's post, here's my translation of the text that precedes his script:

Here follows the code for a simple IronPython program which enables the interactive input of SQL statements and output from an Oracle database. I use the ODP, which apparently is included with Oracle 10g XE, because I have not installed it separately. In addition, I use the well-known HR demo schema.

After startup, an example session might look like this:

Ubuntu: Linux for Cats

Our kitty knows his Linux. 

TurboGears and Oracle

This weekend saw the fulfillment of a lifelong dream - I got TurboGears working against an Oracle database!

For general information, I recommend the ToDo list tutorial and Splee's post on SQLAlchemy/TG. But there are some particulars you'll need to know to work with Oracle... so here's a super-basic example to demonstrate.

1. After installing TurboGears, run at the command prompt:
   

   tg-admin quickstart --sqlalchemy

2. In dev.cfg, replace

   sqlalchemy.dburi="sqlite:///devdata.sqlite"

   with

   sqlalchemy.dburi="oracle://scott:tiger@orcl"

   
   [EDIT April 25, 2007:] Unless you've specifically configured your Oracle database to support Unicode (and maybe even if you have - I'm still fuzzy on this part), you'll also need to set
   

   sqlalchemy.convert_unicode=True

   
   If you decide to leave it out, then start getting
   

   SQLError: (NotSupportedError) Variable_TypeByValue(): unhandled data
   type unicode

   you'll know you needed this parameter set.
   
3. Add the following to model.py:
   

   from turbogears.database import bind_meta_data
   bind_meta_data()
   from sqlalchemy.ext.assignmapper import assign_mapper
   emp_table = Table("emp", metadata, autoload=True)
   class Emp(object):
       pass
   assign_mapper(session.context, Emp, emp_table)

4. To controllers.py, add

   
   import model

   then add to the Root class

   
       @expose(template="myProjectName.templates.emps")
       def emps(self):
           emps_list = model.Emp.select()
           return dict(emps=emps_list)

5. Copy templates/welcome.kid to templates/emps.kid, and replace the document body with

   
     <ul>
       <li py:for="emp in emps">
         ${emp.ename} : ${emp.job}
       </li>
     </ul>

6. From the command prompt, run
   

   python start-myProjectName.py

7. Point a browser at http://localhost:8080/emps

There - you're listing data from SCOTT.EMP!

You can, of course, manually define the columns of your tables; using autoload is simply more convenient and error-proof. It'll only work against tables that have a primary key, though. If you don't use autoload, you don't need to call bind_meta_data in model.py.

TurboGears has recently added SQLAlchemy, as an alternative to SQLObject, for its database-access layer. I don't know much about their relative merits, but it seems like SQLAlchemy may be more friendly to a database-centered (as opposed to object-programming-centered) point of view. In any case, SQLAlchemy has Oracle support, whereas SQLObject's Oracle support still hasn't been integrated into the main codebase. Thus, I'm using the SQLAlchemy flavor of TurboGears.

Oracle Net unaccountability

I have a complaint. After seven years of Oracle experience, ORA-12154: TNS:could not resolve the connect identifier specified ought to be in my past.

I installed a standard Oracle 10.2 client on a fresh, new machine, only to find that the sqlplus.exe in {ORACLE_HOME}/bin was not looking in {ORACLE_HOME}/network/admin for its TNSNAMES.ORA. I don't know where it was looking, or why. I searched for any of those annoying stray TNSNAMES.ORA files, and there weren't any, but that doesn't mean that Oracle Net wasn't looking for TNSNAMES in all the wrong places. I eventually gave up and set the TNS_ADMIN environment variable, but that was an unsatisfying brute-force solution; I want to know why SQLPLUS wasn't looking in the standard place in its own home for TNSNAMES.ORA, but apparently I'll never know.

The problem is that Oracle Net gives you no feedback about what went wrong when something goes wrong. Did it find a TNSNAMES.ORA, but hit a syntax error in it? Did it find a TNSNAMES.ORA, but not the one you expected it to? Did it not find a TNSNAMES.ORA at all? Well, you'll just have to guess. Yes, you can trace Oracle Net; you need to insert directives like TRACE_LEVEL_CLIENT=user and TRACE_DIRECTORY_CLIENT into SQLNET.ORA. Ah, but which SQLNET.ORA? Well, that's the problem - if Oracle Net isn't looking where you expect it to for TNSNAMES.ORA, it won't see your SQLNET.ORA either.

It was a big improvement when TNSPING was upgraded to report on which SQLNET.ORA it was using. (And, indeed, in my case, TNSPING reported that it was using {ORACLE_HOME}/network/admin/sqlnet.ora, as you would expect, which is probably why TNSPING could resolve my service names just fine.) We really need a similar improvement in Oracle Net in general - some troubleshooting information that's transmitted in the error message every time TNS resolution fails.

Have I simply missed the memo on some good way to troubleshoot these problems? If you know of one, please let me know.

While researching it, I did find a pretty nice resource - ora-code.com. Their ORA-12154 page is a more concise and relevant checklist than anything I know of on MetaLink. Unfortunately, they lack a search box, so the best way to find their pages is simply to Google for "ora-code 12154".

There. Now if Oracle magazine ever asks me "what one improvement I'd like to see in Oracle", I have my answer ready. That and PL/Python, of course.

resetPwd.py

I just posted a little script at http://sqlwrappy.sourceforge.net/resetPwd.py for password changing; a full description is at http://sqlwrappy.sourceforge.net/resetPwd.html. You may like to use it / borrow from it if

• You need to provide for nontechnical users who must field "Can you reset my password?" requests (the designed purpose)
• You want a relatively robust command-line way to collect Oracle account login information
• You want to crib code for a command-line pick-from-this-list loop

I packaged it into the 0.1.2 release of sqlWrap.py, too.

The destructive power of stereotypes

I hope that this study receives all the attention it deserves.

"Women exposed to bogus scientific theories linking their gender to poor math skills did worse on arithmetic tests than others..."

This is why people who care get so upset about "harmless personal opinions" about women being inherently worse at acience/math. They're not harmless. They push women away from technical excellence. Yes, that bothers me, and no passive-aggressive whining about "political correctness" will make me accept it in demure silence.

It's especially remarkable that the study showed an easily measurable effect of just one claim of male superiority. Now imagine the cumulative effect of hearing such claims, again and again, over an entire lifetime... it's no wonder that the women who do end up in technology are the exceptionally flinty ones.

meaning of LAMP

Among the great things I learned at Ohio LinuxFest came from Jeff Waugh's talk. I bet you've seen that LAMP acronym around, and wondered what it stood for.

Linux
Apache
Most scripting languages begin with "P"
PostgreSQL

Now, that makes a lot more sense.

Oracle BoF at LinuxFest

Thank God, the babysitter, and a wonderful spouse, I did get to go to Ohio LinuxFest after all. Hooray!

With one talk each on MySQL and PostgreSQL, it was a good day for database enthusiasts. Maybe that's why I saw quite a few Oracle users there.

The organizers also invited attendees to put together impromptu Birds-of-a-Feather (BoF) sessions. I was tempted to throw together one for Oracle, but I felt like my impromptu idea needed more preparation... I wanted to have a list of topics to seed discussion if it drags.

So, what kind of topics would you suggest for an Oracle-Linux BoF? Here are some that occurred to me.

• Experiences with Oracle on various Linux distributions
• Oracle's ancient Apache 1.3 HTTPServer; is it ever going to get to 2.0? Can you get mod_plsql working on Apache 2.0?
• Open-source SQL*Plus alternatives (actually, someday I hope to talk at OOUG on this)
• Experiences with RAC on Linux, Oracle Cluster File System, etc.?

Please, add topics to this list! And, if you're going to be at next year's Ohio LinuxFest, come be part of an OraBoF!

Oracle-free Oracle access

I got an interesting question from Guido d'Amico... he wants to use Python scripts to access Oracle databases from machines with no Oracle software installed. Between us, we came up with these options.

• cx_Oracle and DCOracle2: These "classic" DB-API2 modules both rely on the OCI (Oracle Call Interface), a piece of software distributed by Oracle. (I believe all comparable means for accessing Oracle from other languages rely on the OCI, too.) There's just no way around that - you need some sort of Oracle client installed on the machine you're using them from.
  You don't have to bulk up your machine with a full-blown standard Oracle client, though.
  • Oracle Instant Client is lightweight (85 MB on my Windows box), free, and redistributable. For better and worse, it comes as a simple zipped set of files - if you want any environment variables set (ORACLE_HOME, PATH), you need to do that yourself.
  • OracleXEClient is likewise lightweight (72 MB) and free, and very easy to install.
  Neither of these options comes with a /network/admin/ folder, which might be a little confusing - unless you want to make your connections with Easy Connect, you'll need to set up $ORACLE_HOME/network/admin/tnsnames.ora by yourself.
• You can use ODBC. mxODBC has been around for a while, but is not free for commercial use. pyODBC is free, and I hadn't actually heard of it until I researched this question - maybe I'll review it (or at least find a review) sometime soon.
• You can use JDBC from Jython. Andy Todd and Przemek Piotrowski have blog posts detailing this.
• You can go to IronPython and... um... OK, I've never yet done database access from IronPython, but I assume that using ODT.NET from IronPython is easy enough.
  
  EDIT: Przemek Piotrowski has not just made it work, he's posted a tutorial on ODP under IronPython. Thanks, Przemek!

Ohio LinuxFest

Ohio LinuxFest is coming Sep. 30!

I absolutely loved it last year. It looks like I won't be able to make this year... *weeps bitter tears* - unless, that is, one of you would like to babysit a ten-year-old? No? Bleah, Saturday events are not parent-friendly. (Actually, I did meet one attendee last year who brought his ten-year old. I can't imagine pulling that off with ours, though.)

But anyway, you can give me some comfort by going yourself and having a fantastic time. They've promised extra Linux-newbie stuff this year, too. It will be better than Cats; you will want to see it again and again.

Qnxo price weirdness

Let me say, first off, that I deeply appreciate everything Steven Feuerstein does for the Oracle community.

But the marketing of Qnxo has just gotten weird. My boss asked me for my wishlist for the software budget, and I tried to put Qnxo on it. On Qnxo's "buy it" page, I got

Qnxo is, for the time being, available only on a trial basis. The trial version offers the full range of Qnxo functionality and will work for 30 days after install. If you would like to continue using Qnxo after that point, please visit the Support page and fill out the Contact form. We will then provide you with a key that will enable Qnxo for use until the end of 2006. If you have any questions, please also submit your question through the Support page.

I thought refusing to give customers a straight up-front answer on a price was a hallmark of $10K+ software. Last I heard, Qnxo was $175.

If it were open-source, I'd dive in and use it. If it were proprietary with a clear price, we could decide whether to buy it. But this? I'm supposed to make it a part of my development process based on the hope that, when we're ultimately given a price, it'll be acceptable? I don't see how anybody can do that. I know we can't.

[EDIT 09-09-2007: Steven himself (!) just notified me via his comment that Qnxo is now QCGU, and it's free of charge. Thanks, Steven!]

dual-boot Ubuntu/Windows

I have a new laptop from my boss! A Dell XPS M1710; it feels a bit like an SUV of laptops... it weighs more than I do, comes with an on-board fusion reactor, and emits a menacing red glow from around its edges. It's just chock-full of gigabytes and megahertz and stuff. I will never complain that my boss skimps on our hardware. I think I need a mule to carry it, though.

Anyway, since I'm torn between my love of Linux and my workplace's Windows mandate, I decided to make the machine mirror my split. I need a dual-boot Windows/Ubuntu machine. Since this is the first time I've done so, I'm really glad it was on a clean machine... it was not as smooth as vanilla Ubuntu.

Here's what I did. You should skip step 2.

1. Found good documentation on the Ubuntu wiki
2. Let standard Ubuntu installer attempt to shrink the main Windows (NTFS) partition, to give me room for Linux partitions. I ended up with partitons of type "unknown". Oops!
3. Re-installed Windows from its CD, this time setting its NTFS partition up with about 1/3 the total disk space and leaving the rest uncommitted.
4. Followed the instructions from the "Issues with Windows XP and NTFS" section of WindowsDualBoot: created a System Rescue CD, booted from it, and used run_qtparted to redo the partitioning.
5. Created a single physical partition - the last one of the four I'm allowed.
6. Within the final physical partition, created a linux-swap partition (4 GB for my 2 GB of memory). Divided the rest of the disk between an ext3 partition for Linux root and a FAT32 partition for data that Windows and Linux will be able to share.
7. Started the Ubuntu installer again; chose "manually edit the partition table", and assigned the swap and ext3 partitions to Ubuntu.

Accepting the defaults, my FAT32 partition got labelled "sda5" under Ubuntu and "e:" under Windows. Sure enough, both OS's can access it OK.

Success! One dual boot laptop, hooray! (Yes, I know, if I'd been really bold, I would have used Xen to run both OS's virtually. Maybe next time.)

Blogging tools

I've seen so many blogging tools over the past year or so... many of them seem like interesting projects that have generated lots of excitement.

Here's the part I don't get, though. Why? Had people honestly been going around saying, "Wow, entering my blog entries conventionally is such a time drain"?

I blog a few times a month, and the challenge is having something worth saying and choosing words to say it well - not slinging those words into Blogger's standard posting interface. It's hard to imagine how shaving seconds off that could honestly be worth choosing, installing, and understanding a blogging tool, much less writing one. Maybe if I blogged six times a day, but who would read that?

Maybe someone who "gets it" can explain it to me?

I suspect that this is one of these cases that's being driven by the coolness of the solutions, not the actual need for them. Mind you, I've got nothing against that. I have often spent three hours writing code to avoid a one-hour manual job. (Which is not as illogical as it sounds, because when you finish the manual job, somebody's bound to say, "Oh, I'm so sorry, but we need it done again..." Not that a logical consideration of that possibility is what drives me; I do it because it's fun.)

dumpfile diving

The oracle EXP utility generates a dumpfile which, although technically binary, actually contains lots of readable ASCII. Sometime I need to see this information, especially since dumpfiles don't provide a way (that I know of) of summarizing their contents and the conditions of their generation. In a perfect world, we'd never have to work from with dumpfiles burned onto scratched CDs by unknown parties and abandoned in the dusty corner behind the recycle bin, but this is not a perfect world.

Anyway, I just went through several techniques of examining a mysterious.dmp, and thought I'd share the experience. Much of it would apply to delving into any mixed binary/ascii file.

• The worst way: more mysterious.dmp
  
  gave me frightening glyphs and angry beeps (xset b off to stop those), like R2-D2 invoking dread Cthulhu. Worse, my session would henceforth speak to me only in proto-Sumerian. I could kill the terminal window and open a new one, of course, but I still spent several hours plastering smooth curves over all the office's sharp corners, just to be safe.
  
  
• Not quite so bad: less mysterious.dmp
  
  let me look at the file, making harmless marks of the binary characters, and didn't mangle my session's character set. Yay! I do need to make a habit of using less instead of more.
  
  
• Still pretty painful: grep -a "what I'm looking for" mysterious.dmp
  
  The -a flag makes grep look into a file even though it's binary. It has no proper idea of where lines end in a binary file, though, so your hits can be really long. I had better luck grepping the files that resulted from the operations below.
  
  
• Good: imp me@mydb show=Y file=mysterious.dmp full=y log=mysterious_contents.txt
  
  This gives you a clean-looking file (well, except for all the gratuitous quotation marks). It's also the only technique I know that you can use on Windows (without Cygwin). You don't get data contents, though, just DDL.
  
  
• Best: strings mysterious.dmp > mysterious_contents.txt
  
  This GNU strings utility is really great! You get the ASCII, the whole ASCII, and nothing but the ASCII, quick and clean.

What I still don't have, though, is a way to examine a dumpfile and find out exactly what command was used to generate it. That would give all kinds of fantastic information: What instance was it? Which user did the export? Was it full? ... and so forth. If you know of a way to query a dumpfile for this kind of information, please comment!

xubuntu

Xubuntu is a variant of Ubuntu that uses the lightweight Xfce desktop environment, making it a good choice for low-powered systems.

Or so they say. So I dusted off (literally) a Gateway Solo laptop (Pentium II, 300 MHz, 64 MB RAM) and replaced its Windows 98 with Xubuntu 6.06. And that's what I'm posting from now! Go, Xubuntu! The only problem is that a naughty kitten tore several keys out of the keyboard several years ago, making typing difficult. And he would have to get the 'e', the little stinker.

I'm using a 2-year-old wireless card with it, though. I failed with my first attempt to make its old ethernet card work, and decided to take Tim Almond's advice and just use a known compatible device rather than go into ethernet-card archaeology.

Hmm, now there's a new working laptop in the house. Oh, the possibilities...