Learning Advanced SQL
October 7, 2006
The more I get into Current the more and more SQL I need to know. Right now I’m working with implementing a tree with SQL using nested sets. There are some great examples and tutorials on the internet about doing exactly this and most of these end up creating some pretty serious SQL. I’d really like to implement some of my operations in Current with just one SQL execute call however I’m directly working with 3 different databases so my SQL must be standards compliant.
MySQL can create and assign data to variables in SQL using something that looks like this:
SELECT @myRight := rgt FROM nested_category
See the MySQL docs. Now what about SQLite? Or PostgreSQL?
PostgreSQL will let you embed SQL in your C code. Hey, that’s gross! I should stop googling.
In the long term in Current becomes useful, I would like to migrate to using a database tool kit like SQLAlchemy. I’d really like to be able to wrap my head around what will be possible with SQLAlchemy. What level will my SQL be confined to? It does look like I can do bind variables with it. But what about more advanced SQL usage? Every database is so different.