Some problems that I got...

[Dguia]

Hello there...

I running Fedora C6 with:

- Python 2.4.4;
- gvpr (dot version 2.8 (Wed Oct  4 21:39:46 UTC 2006));
- PYparsing 1.4.6;

My first problem was de "()" in the Class declaration (over lines 455 and 869) like:

line 445 - class Simplifier(): TO class Simplifier:

line 869 - class SingleSelect(): TO class SingleSelect:

Them running the "selftest" I got:
python revj.py
E........................................E.....F..F.
======================================================================
ERROR: testAmbiguousColumns (__main__.DotOutputTestCase)
----------------------------------------------------------------------
Traceback (most recent call last):
  File "revj.py", line 1671, in testAmbiguousColumns
    raise Exception('ambiguous columns')
Exception: ambiguous columns

======================================================================
ERROR: testFuncOverSum (__main__.SingleSelectTestCase)
----------------------------------------------------------------------
Traceback (most recent call last):
  File "revj.py", line 1401, in testFuncOverSum
    res = self.process(s)
  File "revj.py", line 1191, in process
    return self.ss.process(
  File "revj.py", line 629, in process
    res = self.runRemovers(self.removers, x)
  File "revj.py", line 556, in runRemovers
    raise Exception('%s in %s' % (bad, x))
Exception: nvl in select nvl(sum(x)) from t;

======================================================================
FAIL: testReverseOrderFilter (__main__.SingleSelectTestCase)
----------------------------------------------------------------------
Traceback (most recent call last):
  File "revj.py", line 1396, in testReverseOrderFilter
    assert '> 0' in self.ss.filters['x']
AssertionError

======================================================================
FAIL: testSumGroupHaving (__main__.SingleSelectTestCase)
----------------------------------------------------------------------
Traceback (most recent call last):
  File "revj.py", line 1359, in testSumGroupHaving
    assert 'sum(e55)> 0' in self.ss.havings['e55']
AssertionError

----------------------------------------------------------------------
Ran 52 tests in 7.987s

FAILED (failures=2, errors=2)

Finally I run over a query and got:

gvpr: "./dir.g", line 1: <<<
-- syntax error
Traceback (most recent call last):
  File "revj.py", line 1709, in ?
    print query2Dot(openAnything(sys.argv[1]))
  File "revj.py", line 1697, in query2Dot
    dummy = ss.process(
  File "revj.py", line 629, in process
    res = self.runRemovers(self.removers, x)
  File "revj.py", line 556, in runRemovers
    raise Exception('%s in %s' % (bad, x))
Exception: round in ( print de SQL statement)

Second I notice that readme is in Windows format (so its rise me that somethings else could be with the ^M to).

Sorry if miss somethings but I dont have knowledge in Python... (and of corse my poor english)

Best regards.

[far away]

I get the same error when running Self-test
Using Ubuntu 8.04
   - Graphviz 2.20.2
   - Python 2.5
   - Python-pyparsing 1.4.7

user@home:~/Desktop/revj/revj$ python revj.py

E........................................E.....F..F.

ERROR: testAmbiguousColumns (__main__.DotOutputTestCase)
----------------------------------------------------------------------
Traceback (most recent call last):
  File "revj.py", line 1669, in testAmbiguousColumns
    raise Exception('ambiguous columns')
Exception: ambiguous columns

======================================================================
ERROR: testFuncOverSum (__main__.SingleSelectTestCase)
----------------------------------------------------------------------
Traceback (most recent call last):
  File "revj.py", line 1399, in testFuncOverSum
    res = self.process(s)
  File "revj.py", line 1191, in process
    self.qr.process(s)))
  File "revj.py", line 628, in process
    res = self.runRemovers(self.removers, x)
  File "revj.py", line 555, in runRemovers
    raise Exception('%s in %s' % (bad, x))
Exception: nvl in select nvl(sum(x)) from t;

======================================================================
FAIL: testReverseOrderFilter (__main__.SingleSelectTestCase)
----------------------------------------------------------------------
Traceback (most recent call last):
  File "revj.py", line 1394, in testReverseOrderFilter
    assert '> 0' in self.ss.filters['x']
AssertionError

======================================================================
FAIL: testSumGroupHaving (__main__.SingleSelectTestCase)
----------------------------------------------------------------------
Traceback (most recent call last):
  File "revj.py", line 1357, in testSumGroupHaving
    assert 'sum(e55)> 0' in self.ss.havings['e55']
AssertionError

----------------------------------------------------------------------
Ran 52 tests in 19.293s

When running a query, I get:

gvpr: "./dir.g", line 1: <<<
-- syntax error
Format: "png" not recognized. Use one of: canon cmap cmapx cmapx_np dot eps fig gd gif hpgl imap imap_np ismap mif mp pcl pic plain plain-ext ps ps2 svg tk vml vtx wbmp xdot
Traceback (most recent call last):
  File "revj.py", line 1707, in <module>
    print query2Dot(openAnything(sys.argv[1]))
  File "revj.py", line 1697, in query2Dot
    qr.process(s)))
  File "revj.py", line 351, in process
    res = self.removeQuotedIdent(res)
  File "revj.py", line 312, in removeQuotedIdent
    % (e, s) )

and if I change to gif format:

gvpr: "./dir.g", line 1: <<<
-- syntax error
Traceback (most recent call last):
  File "revj.py", line 1707, in <module>
    print query2Dot(openAnything(sys.argv[1]))
  File "revj.py", line 1697, in query2Dot
    qr.process(s)))
  File "revj.py", line 351, in process
    res = self.removeQuotedIdent(res)
  File "revj.py", line 312, in removeQuotedIdent
    % (e, s) )

Any suggestion for this errors?...

[alxtoth]

Hi,

Thanks for the feedback. I am testing the tool on a Mac and on Windows. The Mac should behave like Linux for most of the aspects, so this is not related to OSes.

The auto-test contains some cases that currently fail, to document incorrect behaviour (look in the readme file). As such, if you pipe these exceptions into dot, output will not be valid. However many SELECTs work just fine, and that's the reason to release it early.

When changing output format,  mind the -T parameter : "-T xxx filename.xxx"

Please check the tool understands all of the SQL. If not, please send it to me:

    python revj.py -

Only then use the long command line:

     python revj.py - | fdp | gvpr -fdir.g | dot -Grankdir=LR -Edir=none -T png -o current.png

-Alex

[Dguia]

Some requests/input...

About readme file... it did tell that "some tests may fail" but a list of possible "pre-defined" failures will be nicer or better yet "testing messages" (so users will only complain about real errors)

Aperantly no subquery are suported at this point. (if true would be nice to know that on project comments and readme files)...

After some tests I got a pretty basic ANSII querys (SELECT OPERATIONS(FIELDS) FROM TABLE WHERE JOINS ORDER GROUP) working ("Fine") but i coludnt go futher and the "ASCII" (shell) output are not much of a friendly (I would say that it cannot be apliend directly in documentation processes (like maatkit can)... would recomend a somethigs more "treeview" for "shell" output (optionaly graph "obj" output).

Follows my problem to generate graph....

Even with that (what a consider perfect output) i couldnt generate graphics, example:

python revj.py teste.sql
graph
{
        node [shape=record];
        graph [splines=true];
        rankdir=LR;

        A [label="A | (AG) |<id_t> id_t|SUM(ind)"];
        T [label="T | (TP) |<id> id|GROUP BY nome|MAX(val)"];

        A:id_t -- T:id;
}

Running the graphic generation in the same sql file:

python revj.py teste.sql |fdp |gvpr -fdir.g |dot -Grankdir=LR -Edir=nome -T png -o current.png
gvpr: "./dir.g", line 1: <<<
-- syntax error

dir.g contains:

cat dir.g
BEGIN {
  int x0, y0;
  int x1, y1;
  edge_t e;
  node_t t, h;
  graph_t ng = graph ("G","D");
  $tvtype = TV_ne;
}
N {
  clone (ng, $);
}
E{
  t = node (ng, $.tail.name);
  h = node (ng, $.head.name);
  sscanf ($.tail.pos, "%d,%d", &x0, &y0);
  sscanf ($.head.pos, "%d,%d", &x1, &y1);
  if (x0 < x1) {
    e = edge (t, h, "");
    e.tailport = $.tailport + ':e';
    e.headport = $.headport + ':w';
  }
  else {
    e = edge (h, t, "");
    e.tailport = $.headport + ':e';
    e.headport = $.tailport + ':w';
  }
}
END {
  write (ng);
}

[alxtoth]

Hello,

It might be that you have to adjust line endings in gvpr (now they are Windows style), and you seem to be on Unix. I tried it on Mac OS X and it worked fine.

Please try to avoid "select *" since the tool does not connect to the database, and does not understand what columns to generate for "*" . However, it might be a good idea to append a "*" column in each table in future versions.

Further, please qualify columns with aliases. Instead of "select a from table" use "select t.a from table t". This is trivial to fix for one table, however in general it does require access to the database. Instead of "Select name, id, dep_name from person, department..." please use ""Select p.name, p.id, d.dep_name from person p, department d .."

If this does not solve the problems, there must be something wrong with the installed version of Graphviz. Mine is "dot version 2.20.2". I run the fragment in gvpr (gvpr -fdir.g ) and for the input you specified

graph
{
node [shape=record];
graph [splines=true];
rankdir=LR;

A [label="A | (AG) |<id_t> id_t|SUM(ind)"];
T [label="T | (TP) |<id> id|GROUP BY nome|MAX(val)"];

A:id_t -- T:id;
}

The output is valid:

digraph G {
        A        [label="A | (AG) |<id_t> id_t|SUM(ind)",
                shape=record];
        T        [label="T | (TP) |<id> id|GROUP BY nome|MAX(val)",
                shape=record];
        T:id:e -> A:id_t:w;
}

----------
Will update the displayed errors such that is less confusing.

And will update the readme with the subquery limitation, and also the table aliasing. Managed to update the presentation at http://revj.sourceforge.net/

Revj understands a lot of subqueries. For example "select sin(cos(nvl(t.a))) from table t". Please browse the source code and look for methods called testXXXXX, usually the first line is s = """select ... """

-Alex

[Dguia]

Hi,

First Tnks for your time, patience and will to help.

About the tests... I start with some real nasty ones to see how it goes... (alot os subquerys and de-norm) for a Mysql syntax and in all I got a broke result with revj... so to start get some output i try dummy querys... like "select * from dual" and the one I post:
SELECT
        t.nome,
        SUM(a.ind),
        MAX(t.val)
FROM
        TP T,
        AG A
Where
        T.id = A.id_T
        AND T.id in (1,2,3,4)
ORDER BY t.nome
group by t.nome

As U see I was alredy using alias =P I dont know if the posted output was correctly.. (and sorry I forgot to post the source query that generate the revj output)

And about subquery... I might be wrong but I do no consider this "sin(cos(nvl(t.a)))"
as a "subquery" (there is no "sub-process" running just a serial operation) for me is just a nested "operation" over a field... but if u do... "select t.name, (Select sin(cos(nvl(b.a)) from ab, b where b.id = ab.id and ad.number = 100) from table t" U got a nasty subquery at select point...

About taking a look at the source... I will do my best but I know about 0 of python... ( I know a little about php and perl )

About windows carrier return... I was consindering that... but at VI I do not see the "^M" or any other char that could point to this... any way I will run dos2unix to check that.

About my enviroment... Was the first thing I post (including versions) but I dont know if I miss somethings.

best regards...

[alxtoth]

Hello,

T.id in (1,2,3,4)

"In" is not supported yet. Thank you for reminding. Hopefully soon

-Alex

[andy love]

When I run: python revj.py or python revj.py query.sql
------
C:\...\python revj.py
File revj.py line 467
class Simplifier():
                       ^

Syntax Error: Invalid Syntax
-----

Any help is appreciated.

Andy

[alxtoth]

Hi,

This is about a style issue tolerated in Python 2.5, but flagged as error in Python 2.4.   Fixed in the latest version,  0.07 .

-Alex