Tag: parser

Script slicing by PgMDD

slicer

Preface

November, 4th. Release Candidate 1 of Database Designer for PostgreSQL 1.2.9 become available. Among three changes comparing to the last beta there is the one which attracts attention — “Execute Script In Single Transaction (Alt + F9)” functionality added. World community shocked.

“What means added? We thought it always was executed in single transaction…” – resounded from all sides.

November, 14th. MicroOLAP Headquarters. Explanatory mission entrusted to the best agent… Me. ;)

pgadmin-multiple-set
pgAdmin shows the last result set

Right now there is no any opportunity to remember who got the idea about script slicing in SQL Executor. The gist was — each returned result set must be displayed.

Have a look how pgAdmin handles multiple result sets. As you can see only the last is available while others are discarded (we may read about this on the Messages tab).

One more notice. Multiple statements in pgAdmin always executed in the single transaction context. This is not a miracle since PQsendQuery function from client library used.

By the way, the fact that PQsendQuery used give us a hope that someday pgAdmin will handle all result sets.

pgmdd-multiple-set
PgMDD shows all result sets in separate tabs
note Just for note, I’m not saying pgAdmin is a dinosaur or something. I like it a lot. This is “must have” tool for sure. I’m using it because of other GUI administration utility absence. :)

As you probably guessed PgMDD creates separate tab for each result set from the very first release.

There is one more important issue why script slicing was implemented. Database Designer is some kind of ideal world. You may use any names, any functions, any data types for model creating.

But real life is cruel. Generated script must work in any conditions even if some statements may fail, e.g. old server version, non-existent role, lack of privileges for some operations, object with the same name already exists etc. That’s why PgMDD’s SQL Executor should give the developer right of choice — abort execution or proceed anyway.

How it’s made

Let me one phrase before I begin: there is no any SQL parser library (or suite) on the market which suits even the basic needs (I mean PostgreSQL dialect of course). I guarantee this!

God is my witness, our team tried every 3rd party library we meet. Without success.

In Russian speaking IT folklore there is an adage “Переписать всё нахрен!”. Loose translation is “Rewrite all from scratch!”

OK, the moment of glory. We made it ourselves using the native PostgreSQL grammar. Yeah, bite me unbelievers!

Let’s omit technical details. I know nobody cares anyway. ;) Our parser is absolutely… no, I mean absolutely compatible with PostgreSQL. But with 8.3.x version. :) It’s just a matter of time to update it, but we missed the moment.

So we have two reasons to add “Execute in Single Transaction” functionality:

  • Ability to ROLLBACK all changes made by script in case of need
  • PgMDD parser cannot proceed with some PostgreSQL 8.4.x features

That’s all folks!

Simple Parse Tree

It was amazing cognac time and talking about parser with a good old college friend. E.g. to demonstrate how PostgreSQL analyses some queries.

Statement

SELECT field1 AS customer FROM “FooSchema”.”Bar”

inside server’s brain will become:

stmtmulti
    stmt
        SelectStmt
            select_no_parens
                simple_select
                    SELECT
                    target_list
                        target_el
                            a_expr
                                c_expr
                                    columnref
                                        relation_name
                                            ColId
                                                field1
                            AS
                            ColLabel
                                customer
                    from_clause
                        FROM
                        from_list
                            table_ref
                                relation_expr
                                    qualified_name
                                        relation_name
                                            ColId
                                                "FooSchema"
                                        indirection
                                            indirection_el
                                                .
                                                attr_name
                                                    ColLabel
                                                        "Bar"

Will add some more in case of interest :)