Comments (13)
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.
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.
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.
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.
@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.
Yes, please make the push.
from pyrseas.
@aquilax: I've pushed the changes as branch r0.3. Please let me know how it goes.
from pyrseas.
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.
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.
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.
Latest changes should take care of the "SN" KeyError problem.
from pyrseas.
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.
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)
- ALTER SEQUENCE OWNER comes before CREATE TABLE HOT 3
- SSL Connection to remote database HOT 3
- datacopy CSV with null trailing columns not importing with -u HOT 7
- CREATE EXTENSION statements in the change script HOT 5
- [Contribution] Docker support HOT 2
- Sequences processing doesn't skip schemas defined with '-N' option. HOT 3
- dbtoyaml fails when dealing with extension-owned triggers/functions HOT 7
- Multiline Strings (source: |2) Output Unstable HOT 5
- Enable Github Discussions HOT 2
- Fails to Install: Error: pg_config executable not found HOT 5
- Error Using dbtoyaml With Supabase Schema HOT 67
- dbtoyaml Fails When PG Password Has Spaces (Improper Escaping) HOT 1
- Convert .travis.yml to GH Actions
- Fold pgdbconn back into Pyrseas HOT 1
- Key error when converting aggregate to YAML HOT 4
- plv8 to extension and fix reserved words in the primary key HOT 5
- Add REFERENCING tables in trigger HOT 3
- Support columns GENERATED AS (x) STORED HOT 2
- Support --revert for tables HOT 4
- the yamltodb tool is not placing the names of schemas, tables and columns in double quotes HOT 2
Recommend Projects
-
React
A declarative, efficient, and flexible JavaScript library for building user interfaces.
-
Vue.js
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
-
Typescript
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
-
TensorFlow
An Open Source Machine Learning Framework for Everyone
-
Django
The Web framework for perfectionists with deadlines.
-
Laravel
A PHP framework for web artisans
-
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.
-
Visualization
Some thing interesting about visualization, use data art
-
Game
Some thing interesting about game, make everyone happy.
Recommend Org
-
Facebook
We are working to build community through open source technology. NB: members must have two-factor auth.
-
Microsoft
Open source projects and samples from Microsoft.
-
Google
Google ❤️ Open Source for everyone.
-
Alibaba
Alibaba Open Source for everyone
-
D3
Data-Driven Documents codes.
-
Tencent
China tencent open source team.
from pyrseas.