Object names with mixed cases #5
Comments
|
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". |
|
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: integerCREATE TABLE my table (
col1 integer);
CREATE TABLE mytable (
my-key integer);Of course, the generated SQL will fail when run through psql. |
|
In my case it was a sequence name, which caused the fail: The project I'm working on is like the worst case Postgre scenario. |
|
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. |
|
@aquilax: I have a partial solution to this problem. It involves adding a function 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-keygenerates 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. |
|
Yes, please make the push. |
…ssue #5. * pyrseas/dbobject/__init__.py (quote_id): New function to quote identifiers if needed. (DbObject.identifier, DbSchemaObject.qualname): Use new quote_id function. * pyrseas/dbobject/column.py (Column.add): Use quote_id. * pyrseas/dbobject/constraint.py (Constraint.{key_columns,create}): Use quote_id. * pyrseas/dbobject/index.py (Index.create): Use quote_id. * pyrseas/dbobject/language.py (Language.create): Use quote_id. (LanguageDict.from_map): Replace str.split() by str.find(' ') to correctly separate the name. * pyrseas/dbobject/rule.py (Rule.{identifier,create}): Use quote_id. * pyrseas/dbobject/schema.py (Schema.create): Use quote_id. (SchemaDict.from_map): Replace str.split() by str.find(' ') to correctly separate the name. * pyrseas/dbobject/table.py (Sequence.get_attrs): Use quote_id. * pyrseas/dbobject/trigger.py (Trigger.{identifier,create}): Use quote_id.
|
@aquilax: I've pushed the changes as branch r0.3. Please let me know how it goes. |
|
The patch solves the previous issue, but causes another one: I also got KeyError (with the master branch) when the object name is in cyrillic. |
|
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. |
|
Tried the workaround: |
* pyrseas/dbobject/__init__.py (split_schema_table): If table name is quoted, strip the quotes. (DbSchemaObject.set_search_path): Use quote_id. * pyrseas/dbobject/column.py (Column.set_sequence_default): Use quote_id. * pyrseas/dbobject/table.py (Sequence.add_owner): Use quote_id.
|
Latest changes should take care of the "SN" KeyError problem. |
|
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. |
|
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. |
Object names with mixed case names should be quoted in DbSchemaObject.qualname.
For example public.Name does not work while public."Name" does.
The text was updated successfully, but these errors were encountered: