Giter Club home page Giter Club logo

Comments (13)

jmafc avatar jmafc commented on September 17, 2024

This is a known issue (it should've been added to the TODO). Pyrseas currently doesn't work with delimited identifiers, i.e., not just mixed case identifiers, which require quoting, but also those with embedded spaces or other symbols, e.g., "my table", "my-key".

from pyrseas.

jmafc avatar jmafc commented on September 17, 2024

Interestingly, neither dbtoyaml nor yamltodb fail with my examples of delimited identifiers:

schema public:
  description: standard public schema
  table my table:
    columns:
    - col1:
        type: integer
  table mytable:
    columns:
    - my-key:
        type: integer
CREATE TABLE my table (
    col1 integer);
CREATE TABLE mytable (
    my-key integer);

Of course, the generated SQL will fail when run through psql.

from pyrseas.

aquilax avatar aquilax commented on September 17, 2024

In my case it was a sequence name, which caused the fail:

aquilax@zelda /tmp> dbtoyaml -U unity -H 10.10.11.3 -n baza unity
Traceback (most recent call last):
  File "/usr/local/bin/dbtoyaml", line 9, in <module>
    load_entry_point('Pyrseas==0.3.0', 'console_scripts', 'dbtoyaml')()
  File "/usr/local/lib/python2.6/dist-packages/pyrseas/dbtoyaml.py", line 40, in main
    dbmap = db.to_map()
  File "/usr/local/lib/python2.6/dist-packages/pyrseas/database.py", line 116, in to_map
    self.from_catalog()
  File "/usr/local/lib/python2.6/dist-packages/pyrseas/database.py", line 81, in from_catalog
    self.db = self.Dicts(self.dbconn)
  File "/usr/local/lib/python2.6/dist-packages/pyrseas/database.py", line 49, in __init__
    self.tables = ClassDict(dbconn)
  File "/usr/local/lib/python2.6/dist-packages/pyrseas/dbobject/__init__.py", line 185, in __init__
    self._from_catalog()
  File "/usr/local/lib/python2.6/dist-packages/pyrseas/dbobject/table.py", line 386, in _from_catalog
    inst.get_dependent_table(self.dbconn)
  File "/usr/local/lib/python2.6/dist-packages/pyrseas/dbobject/table.py", line 51, in get_dependent_table
    AND refclassid = 'pg_class'::regclass""" % self.qualname())
  File "/usr/local/lib/python2.6/dist-packages/pyrseas/dbconn.py", line 72, in fetchone
    curs = self._execute(query)
  File "/usr/local/lib/python2.6/dist-packages/pyrseas/dbconn.py", line 58, in _execute
    curs.execute(query)
  File "/usr/local/lib/python2.6/dist-packages/psycopg2/extras.py", line 121, in execute
    return _cursor.execute(self, query, vars)
psycopg2.ProgrammingError: ("invalid name syntax\nLINE 3:                WHERE objid = 'baza.Banks_Bank ID_seq'::regcl...\n                                     ^\n", "SELECT refobjid::regclass, refobjsubid\n               FROM pg_depend\n               WHERE objid = 'baza.Banks_Bank ID_seq'::regclass\n                 AND refclassid = 'pg_class'::regclass")

The project I'm working on is like the worst case Postgre scenario.

from pyrseas.

jmafc avatar jmafc commented on September 17, 2024

Thanks for the additional info. Sequence.get_dependent_table is indeed one of the few places in Pyrseas where DbSchemaObject.qualname() is used internally, i.e., not to generate YAML or SQL but to query other catalogs.

from pyrseas.

jmafc avatar jmafc commented on September 17, 2024

@aquilax: I have a partial solution to this problem. It involves adding a function quote_id and using it in DbSchemaObject.qualname() as well as several other places. With these changes, the following YAML:

schema public:
  description: standard public schema
schema my sch:
  sequence my seq:
    cache_value: 1
    increment_by: 1
    max_value: null
    min_value: null
    start_value: 1
  table my table:
    columns:
    - Col1:
        type: integer
  table MyTable:
    columns:
    - my-key:
        not_null: true
        type: integer
    primary_key:
      mytable pkey:
        access_method: btree
        columns:
        - my-key

generates the following SQL:

BEGIN;
CREATE SCHEMA "my sch";
CREATE TABLE "my sch"."MyTable" (
    "my-key" integer NOT NULL);
CREATE TABLE "my sch"."my table" (
    "Col1" integer);
CREATE SEQUENCE "my sch"."my seq"
    START WITH 1
    INCREMENT BY 1
    NO MAXVALUE
    NO MINVALUE
    CACHE 1;
ALTER TABLE "my sch"."MyTable" ADD CONSTRAINT "mytable pkey" PRIMARY KEY ("my-key");
COMMIT;

The solution also solves the specific problem that you saw when calling Sequence.get_dependent_table.

I call it a partial solution because it still doesn't handle quoting of SQL keywords, but hopefully your "worst case" scenario isn't that bad.

I could push this to GitHub as branch r0.3 if you'd like to try it out.

from pyrseas.

aquilax avatar aquilax commented on September 17, 2024

Yes, please make the push.

from pyrseas.

jmafc avatar jmafc commented on September 17, 2024

@aquilax: I've pushed the changes as branch r0.3. Please let me know how it goes.

from pyrseas.

aquilax avatar aquilax commented on September 17, 2024

The patch solves the previous issue, but causes another one:

aquilax@zelda /t/Pyrseas> dbtoyaml -U unity -H 10.10.11.3 -n baza unity
Traceback (most recent call last):
  File "/usr/local/bin/dbtoyaml", line 9, in <module>
    load_entry_point('Pyrseas==0.3.0', 'console_scripts', 'dbtoyaml')()
  File "/usr/local/lib/python2.6/dist-packages/Pyrseas-0.3.0-py2.6.egg/pyrseas/dbtoyaml.py", line 40, in main
    dbmap = db.to_map()
  File "/usr/local/lib/python2.6/dist-packages/Pyrseas-0.3.0-py2.6.egg/pyrseas/database.py", line 116, in to_map
    self.from_catalog()
  File "/usr/local/lib/python2.6/dist-packages/Pyrseas-0.3.0-py2.6.egg/pyrseas/database.py", line 84, in from_catalog
    self._link_refs(self.db)
  File "/usr/local/lib/python2.6/dist-packages/Pyrseas-0.3.0-py2.6.egg/pyrseas/database.py", line 70, in _link_refs
    db.rules, db.triggers)
  File "/usr/local/lib/python2.6/dist-packages/Pyrseas-0.3.0-py2.6.egg/pyrseas/dbobject/table.py", line 482, in link_refs
    table.owner_column = self[(sch, table.owner_table)]. \
KeyError: ('baza', '"Position"')

I also got KeyError (with the master branch) when the object name is in cyrillic.

from pyrseas.

jmafc avatar jmafc commented on September 17, 2024

The following is a workaround for the owner_table problem in table.py:

@@ -52,6 +52,8 @@ class Sequence(DbClass):
                  AND refclassid = 'pg_class'::regclass""" % self.qualname())
         if data:
             (sch, self.owner_table) = split_schema_table(data[0], self.schema)
+            if self.owner_table[:1] == '"' and self.owner_table[-1:] == '"':
+                self.owner_table = self.owner_table[1:-1]
             self.owner_column = data[1]
             return
         data = dbconn.fetchone(

There is more to it, but at least the above allows dbtoyaml to run. I'll push the rest of the changes later today. I'll also try some Cyrillic table names and let you know.

from pyrseas.

aquilax avatar aquilax commented on September 17, 2024

Tried the workaround:

aquilax@zelda /t/Pyrseas> dbtoyaml -U unity -H 10.10.11.3 -n baza unity
Traceback (most recent call last):
  File "/usr/local/bin/dbtoyaml", line 9, in <module>
    load_entry_point('Pyrseas==0.3.0', 'console_scripts', 'dbtoyaml')()
  File "/usr/local/lib/python2.6/dist-packages/Pyrseas-0.3.0-py2.6.egg/pyrseas/dbtoyaml.py", line 40, in main
    dbmap = db.to_map()
  File "/usr/local/lib/python2.6/dist-packages/Pyrseas-0.3.0-py2.6.egg/pyrseas/database.py", line 116, in to_map
    self.from_catalog()
  File "/usr/local/lib/python2.6/dist-packages/Pyrseas-0.3.0-py2.6.egg/pyrseas/database.py", line 84, in from_catalog
    self._link_refs(self.db)
  File "/usr/local/lib/python2.6/dist-packages/Pyrseas-0.3.0-py2.6.egg/pyrseas/database.py", line 70, in _link_refs
    db.rules, db.triggers)
  File "/usr/local/lib/python2.6/dist-packages/Pyrseas-0.3.0-py2.6.egg/pyrseas/dbobject/table.py", line 498, in link_refs
    assert self[(sch, tbl)]
KeyError: ('baza', '"SN"')

from pyrseas.

jmafc avatar jmafc commented on September 17, 2024

Latest changes should take care of the "SN" KeyError problem.

from pyrseas.

jmafc avatar jmafc commented on September 17, 2024

Regarding Cyrillic names, I tried it against the r0.3 branch and dbtoyaml outputs something like this:

schema my sch:
  !!python/str "table \u043C\u043E\u044F\u0442\u0430 \u043C\u0430\u0441\u0430":

Which of course is hard/impossible to read. However, yamltodb is able to recreate the correct table:

CREATE TABLE "my sch"."моята маса" (
    col1 integer);

I'm tempted to leave things as is for now and revisit this when Pyrseas is ported to Python 3.x.

from pyrseas.

aquilax avatar aquilax commented on September 17, 2024

Thank you. It works fine now.

About the cyrillic characters I don't think their usage in DB design is so widespread, so i also it's ok to leave them as there are now.

from pyrseas.

Related Issues (20)

Recommend Projects

  • React photo React

    A declarative, efficient, and flexible JavaScript library for building user interfaces.

  • Vue.js photo Vue.js

    🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.

  • Typescript photo Typescript

    TypeScript is a superset of JavaScript that compiles to clean JavaScript output.

  • TensorFlow photo TensorFlow

    An Open Source Machine Learning Framework for Everyone

  • Django photo Django

    The Web framework for perfectionists with deadlines.

  • D3 photo D3

    Bring data to life with SVG, Canvas and HTML. 📊📈🎉

Recommend Topics

  • javascript

    JavaScript (JS) is a lightweight interpreted programming language with first-class functions.

  • web

    Some thing interesting about web. New door for the world.

  • server

    A server is a program made to process requests and deliver data to clients.

  • Machine learning

    Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.

  • Game

    Some thing interesting about game, make everyone happy.

Recommend Org

  • Facebook photo Facebook

    We are working to build community through open source technology. NB: members must have two-factor auth.

  • Microsoft photo Microsoft

    Open source projects and samples from Microsoft.

  • Google photo Google

    Google ❤️ Open Source for everyone.

  • D3 photo D3

    Data-Driven Documents codes.