SQL King

21 Aug 2008 10:00

Are you an SQL King?

I once met a person that used to write SQL queries more sophisticated than this:

select *
    from category
    where category_id IN
        (select cat1 as category_id
            from (select min(category_id) as cat1, max(category_id) as cat2, name, site_id, count(*)
                from category
                group by name, site_id
                having count(*) > 1
            ) as c1
        union    select cat2 as category_id
            from (select min(category_id) as cat1, max(category_id) as cat2, name, site_id, count(*)
                from category
                group by name, site_id
                having count(*) > 1
            ) as c2

This one is to check duplicated categories in sites from Wikidot. It's not optimal, but it works. Of course the situation when there exist duplicated categories is a bug, but it seems a really rare one, because from the beginning of Wikidot, there are only 20 such categories, all of them being forum, so this probably means a small bug in create a forum thing as well.

Comments: 2


16 Aug 2008 16:30

Wonder why blog on Wikidot, not on Wordpress?

With Wikidot, you can give your badges a reflection:


(this one was created with Cooltext)

Welcome on Wikidot. Where dreams come true.

How to do this

Put somewhere on your wiki:

[[image http://piotr.gabryjeluk.pl/local--files/blog:side/badge.gif class="reflect"]]
<script type="text/javascript" src="http://static.wikidot.com/common--misc/reflection.js"></script>

Comments: 1

Object Database In Python - Day 2

16 Aug 2008 16:12

Hello again,

as pointed in the previous day comment, there is another IDE for developing in Python, which is BoaConstructor. As a matter of fact, I haven't taken that into consideration, because it was told it is a IDE for GUI development. As I was not about creating a GUI, this was completely skipped.

My impressions of the IDEs:

  • pida, geany, drpython — interesting, but not worth a try for me
  • eric — good overall, but raises many dialog boxes with information of exceptions. These are fatal and are about the GUI, so you have to click OK and continue working

I tried the Eric first, because it was written as a really good program. I like it, but it is not polished and would never be bought (if commercial) by anyone:

  • the support for SVN is really tricky
  • the GUI is really not intuitive — no "delete a file" or "new file (here)" in the navigator context menu
  • the code completion is there, but not really so clever

for example, having the following code:

class aClass:
    def method(self, param):
        assert isinstance(param, list)

the IDE should know, that the param is a list and complete the list methods! I believe the PyDev plugin for Eclipse already did it when I last used it.

After trying all the listed IDEs I got back to Eric as one of two best Python IDEs tried by me (the second is PyDev).

Happy Pythoning
Your Piotr

Comments: 1

Starting Work On Object Database In Python

15 Aug 2008 10:08

Good morning everyone,

today I start working on object database to be implemented in Python. This is my individual project for my classes.

During the study-year I was taught how to parse sophisticated grammar with PLY (Python Lex and Yacc library). I've then learned how to then process the parsed queries, to get the results.

The database query language is similar to the SQL language. The difference is, we don't have definitions of tables and fields. Any object (row in table) can have fields other that other objects with the same name (of the same table).

This seems quite hard, but it has its pros.

Moreover we've analyzed the following query using the simple parsing and processing:

SELECT employee.salary WHERE employee.salary > AVG(employee.salary);

now, having as much as 10000 employees, using the standard processing, this would mean calculating the average (AVG) from ALL salaries for EACH of the 10000 employees.

So the teacher proposed to use tree decoration, to mark the branches of the query syntax tree, that can be run once (i.e. their value does not depend on the rest of the query).

I proposed to do it other way: to don't analyze the tree to much, but instead implement a sort of caching. Each time we try to get the AVG(employee.salary) we check whether this was already calculated, if not (only the first time we get the AVG), we calculate this and populates the cache with the value.

This has the following pros:

  • we can use the cache in successive queries
  • we don't have to decorate query trees — it's not Christmas here

Today work I start with selecting a Python IDE to work with. During the classes I used PyDev — the Python plugin for Eclipse IDE, but I believe there any many new choices out there now — after a few months:

  • eric
  • pida
  • geany
  • drpython

(all from Ubuntu default repository)

More IDEs I found with Google:

Comments: 2

Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License