the yamltodb tool is not placing the names of schemas, tables and columns in double quotes

the yamltodb tool is not placing the names of schemas, tables and columns in double quotes, this causes problems in schemas that use camelCase tables and columns

out of yamltodb

...
CREATE TABLE public.bairros (
    id integer NOT NULL DEFAULT nextval('public.bairros_id_seq'::regclass),
    "idMunicipio" integer NOT NULL,
    nome character varying(80) NOT NULL,
    ibge character varying(10),
    "validacaoCorreio" boolean,
    oficial boolean);

COMMENT ON TABLE public.bairros IS 'Cadastro de Bairros do Municipio de Rio das Ostras (fonte: Correios)';

COMMENT ON COLUMN public.bairros.validacaoCorreio IS 'Se Bairro foi cadastrado ao validar um endereco na base dos Correios';

COMMENT ON COLUMN public.bairros.oficial IS 'Se o nome do bairro e o oficial informado pelo municipio';

CREATE TABLE public.bloqueios_encaminhamentos (
    id integer NOT NULL DEFAULT nextval('public.bloqueios_encaminhamentos_id_seq'::regclass),
    "idVaga" integer NOT NULL,
    justificativa character varying(255) NOT NULL,
    data timestamp(6) without time zone NOT NULL DEFAULT now(),
    "idUsuarioResponsavel" integer,
    acao character varying(255) NOT NULL);

COMMENT ON COLUMN public.bloqueios_encaminhamentos.acao IS 'bloqueio temporario, bloqueio automatico,desbloqueio';

ALTER SEQUENCE public.bloqueios_encaminhamentos_id_seq OWNED BY public.bloqueios_encaminhamentos.id;

CREATE TABLE public.candidatos_conhecimentos_extras (
    id integer NOT NULL DEFAULT nextval('public.candidatos_conhecimenos_extras_id_seq'::regclass),
    "idCandidato" integer NOT NULL,
    "idConhecimentoExtra" integer NOT NULL,
    "nivelConhecimento" character varying(20) NOT NULL);

ALTER SEQUENCE public.candidatos_conhecimenos_extras_id_seq OWNED BY public.candidatos_conhecimentos_extras.id;

CREATE TABLE public.candidatos_cursos (
    id integer NOT NULL DEFAULT nextval('public.candidatos_cursos_id_seq'::regclass),
    "idCandidato" integer NOT NULL,
    "idCurso" integer NOT NULL,
    "dataConclusao" date);
...
ALTER TABLE public.experiencias_candidatos_cargos ADD CONSTRAINT fk_experiencias_candidatos_cargos__idcandidato FOREIGN KEY ("idCandidato") REFERENCES public.candidatos (idCandidato);
...

the correct thing would be

ALTER TABLE "public"."encaminhamentos" ADD CONSTRAINT fk_encaminhamentos__idcandidato FOREIGN KEY ("idCandidato") REFERENCES "public"."candidatos" ("idCandidato");

COMMENT ON COLUMN "public"."experiencias_candidatos_cargos"."tempoExperienciaFormal" IS 'tempo de experiencia em meses com CTPS';

error

...
psql:./db/banco_empregos.sql:501: ERRO:  coluna "idusuario" da relação "public.encaminhamentos" não existe
psql:./db/banco_empregos.sql:503: ERRO:  coluna "dataalteracao" da relação "public.encaminhamentos" não existe
psql:./db/banco_empregos.sql:505: ERRO:  coluna "idusuarioalteracao" da relação "public.encaminhamentos" não existe
...
psql:./db/banco_empregos.sql:889: ERRO:  coluna "idcandidato" referenciada na restrição de chave estrangeira não existe
...

I must say I'm a bit surprised that it took all this time to discover this problem. I'm afraid it's because most people don't use camelCase in naming columns. If you look at the source file pyrseas/database.py and in particular at the Database.to_map() and Database.diff_map() functions you'll notice they both take a quote_reserved argument, which defaults to True. That causes both dbtoyaml and yamltodb to use double quotes around reserved words such as case and false (case-insensitive). But you're right, since PostgreSQL supports case-sensitive (camelCase or otherwise) names for tables, columns and other objects, Pyrseas ought to support that too.

It's easy to say "ought to support", but the fact is that I haven't touched Pyrseas code for about ten months (and neither has anyone else except the person working on #256). If you really want this supported, I would encourage you to create a couple of simple tests, e.g., in the source tests/dbobject/test_table.py. We would need to check both dbtoyaml and yamltodb. For example, a test to CREATE TABLE "TaBeLa" ("cOluNa" integer) should suffice to start. I hope that the dbtoyaml part will work "as is", i.e., the YAML file should not need double quotes to support the mixed case. The yamltodb should fail to produce the expected double-quoted mixed case names, but that should help us track down what needs to change.

As far as accents and encoding, I'm not so sure. I believe we have tested names with Unicode characters like ç, German and even Cyrillic characters (see test_extern_file.py). In your example, the only discrepancy I see is the 'No' which I'm not sure if it's followed by a superscript letter 'o' (which perhaps is encoded as U+1D52?) or a 'º' (ordinal indicator U+00BA).

perseas / Pyrseas