1. Douglas Paes
  2. Valentina Studio
  3. Thursday, March 28 2019, 02:27 PM
  4.  Subscribe via email
Hi!

I currently use version 9.1 (64-bit) of Valentina Studio on Windows 10 and the "Related tables" functionality is not working. Can you help me?

Thanks.
Comment
There are no comments made yet.
Sergey Pashkov Accepted Answer
Thank you, I’ll review the pg_dump algorithms, there should be some difference.
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Studio
  3. # 1
Sergey Pashkov Accepted Answer
Hello Douglas,

Are the tables in the same schema or different?
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Studio
  3. # 2
Douglas Paes Accepted Answer
Hello Sergey,

The tables are in the same schema.
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Studio
  3. # 3
Sergey Pashkov Accepted Answer
Could you please add SQL definitions of both tables?
You can use a context menu for it: "Generate SQL"->"Create"
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Studio
  3. # 4
Douglas Paes Accepted Answer
Table "estado" (Create):

BEGIN;

-- CREATE TABLE "estado" ---------------------------------------
CREATE TABLE "base_corporativa"."estado" (
"id" Integer NOT NULL,
"nome" Character Varying( 200 ) COLLATE "pg_catalog"."default" NOT NULL,
"sigla" Character Varying( 2 ) COLLATE "pg_catalog"."default" NOT NULL,
"area" Numeric( 13, 4 ) NOT NULL,
"the_geom" "public"."geometry",
PRIMARY KEY ( "id" ),
CONSTRAINT "enforce_dims_the_geom" CHECK(public.st_ndims(the_geom) = 2),
CONSTRAINT "enforce_geotype_the_geom" CHECK((public.geometrytype(the_geom) = 'MULTIPOLYGON'::text) OR (the_geom IS NULL)),
CONSTRAINT "enforce_srid_the_geom" CHECK(public.st_srid(the_geom) = 4674) );
;
-- -------------------------------------------------------------

-- CHANGE "COMMENT" OF "FIELD "id" -----------------------------
COMMENT ON COLUMN "base_corporativa"."estado"."id" IS 'Identificador único da entidade estado.';
-- -------------------------------------------------------------

-- CHANGE "COMMENT" OF "FIELD "nome" ---------------------------
COMMENT ON COLUMN "base_corporativa"."estado"."nome" IS 'Armazena o nome do estado.';
-- -------------------------------------------------------------

-- CHANGE "COMMENT" OF "FIELD "sigla" --------------------------
COMMENT ON COLUMN "base_corporativa"."estado"."sigla" IS 'Armazena a sigla do estado.';
-- -------------------------------------------------------------

-- CHANGE "COMMENT" OF "FIELD "area" ---------------------------
COMMENT ON COLUMN "base_corporativa"."estado"."area" IS 'Area do estado (ha).';
-- -------------------------------------------------------------

-- CHANGE "COMMENT" OF "FIELD "the_geom" -----------------------
COMMENT ON COLUMN "base_corporativa"."estado"."the_geom" IS 'Coluna georreferenciada.';
-- -------------------------------------------------------------

-- CHANGE "COMMENT" OF "TABLE "estado" -------------------------
COMMENT ON TABLE "base_corporativa"."estado" IS 'Entidade responsável por armazenar os estados.';
-- -------------------------------------------------------------

-- CREATE INDEX "sidx_estado_the_geom" -------------------------
CREATE INDEX "sidx_estado_the_geom" ON "base_corporativa"."estado" USING gist( "the_geom" );
-- -------------------------------------------------------------

COMMIT;

//

Table "municipio" (Create):

BEGIN;

-- CREATE TABLE "municipio" ------------------------------------
CREATE TABLE "base_corporativa"."municipio" (
"id" Integer NOT NULL,
"nome" Character Varying( 200 ) COLLATE "pg_catalog"."default" NOT NULL,
"id_estado" Integer NOT NULL,
"area" Numeric( 13, 4 ) NOT NULL,
"the_geom" "public"."geometry",
"nome_sem_formatacao" Character Varying( 200 ) COLLATE "pg_catalog"."default",
PRIMARY KEY ( "id" ),
CONSTRAINT "enforce_dims_the_geom" CHECK(public.st_ndims(the_geom) = 2),
CONSTRAINT "enforce_geotype_the_geom" CHECK(((public.geometrytype(the_geom) = 'MULTIPOLYGON'::text) OR (public.geometrytype(the_geom) = 'POLYGON'::text)) OR (the_geom IS NULL)),
CONSTRAINT "enforce_srid_the_geom" CHECK(public.st_srid(the_geom) = 4674) );
;
-- -------------------------------------------------------------

-- CHANGE "COMMENT" OF "FIELD "id" -----------------------------
COMMENT ON COLUMN "base_corporativa"."municipio"."id" IS 'Identificado único da entidade municipio.';
-- -------------------------------------------------------------

-- CHANGE "COMMENT" OF "FIELD "nome" ---------------------------
COMMENT ON COLUMN "base_corporativa"."municipio"."nome" IS 'Armazena o nome do município.';
-- -------------------------------------------------------------

-- CHANGE "COMMENT" OF "FIELD "id_estado" ----------------------
COMMENT ON COLUMN "base_corporativa"."municipio"."id_estado" IS 'Identificador da entidade estado que realiza o relacionamento entre as entidades municipio e estado.';
-- -------------------------------------------------------------

-- CHANGE "COMMENT" OF "FIELD "area" ---------------------------
COMMENT ON COLUMN "base_corporativa"."municipio"."area" IS 'Área do município (ha).';
-- -------------------------------------------------------------

-- CHANGE "COMMENT" OF "FIELD "the_geom" -----------------------
COMMENT ON COLUMN "base_corporativa"."municipio"."the_geom" IS 'Coluna georreferenciada.';
-- -------------------------------------------------------------

-- CHANGE "COMMENT" OF "FIELD "nome_sem_formatacao" ------------
COMMENT ON COLUMN "base_corporativa"."municipio"."nome_sem_formatacao" IS 'Nome sem formatação.';
-- -------------------------------------------------------------

-- CHANGE "COMMENT" OF "TABLE "municipio" ----------------------
COMMENT ON TABLE "base_corporativa"."municipio" IS 'Entidade responsável por armazenar os municipios.';
-- -------------------------------------------------------------

-- CREATE INDEX "sidx_municipio_the_geom" ----------------------
CREATE INDEX "sidx_municipio_the_geom" ON "base_corporativa"."municipio" USING gist( "the_geom" );
-- -------------------------------------------------------------

COMMIT;

BEGIN;

-- CREATE LINK "fk_m_estado" -----------------------------------
ALTER TABLE "base_corporativa"."municipio"
ADD CONSTRAINT "fk_m_estado" FOREIGN KEY ( "id_estado" )
REFERENCES "base_corporativa"."estado" ( ) MATCH SIMPLE
ON DELETE No Action
ON UPDATE No Action;
-- -------------------------------------------------------------

COMMIT;
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Studio
  3. # 5
Sergey Pashkov Accepted Answer
Thank you, for some reason, the PK field is not defined for the link fk_m_estado.
What version of PostgreSQL is used?
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Studio
  3. # 6
Douglas Paes Accepted Answer
PostgresSQL 9.2.15 on x86_64-unknown-linux-gnu, compiled by gcc (Ubuntu 4.8.4-2ubuntu1~14.04.1) 4.8.4, 64-bit
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Studio
  3. # 7
Sergey Pashkov Accepted Answer
Can't reproduce yet.

What if you execute the following query:
SELECT column_name FROM information_schema.constraint_column_usage WHERE constraint_schema = 'base_corporativa' AND constraint_name = 'fk_m_estado'

Do you get the primary key column?
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Studio
  3. # 8
Douglas Paes Accepted Answer
Does not get.

See the attachment.
Attachments (1)
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Studio
  3. # 9
Sergey Pashkov Accepted Answer
Thank you, that's very strange.
What if you execute
SELECT * FROM information_schema.constraint_column_usage

I attached my output - id column is here.
Attachments (1)
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Studio
  3. # 10
Douglas Paes Accepted Answer
Really, very strange.

In attachment, the output for the query SELECT * FROM information_schema.constraint_column_usage
Attachments (1)
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Studio
  3. # 11
Sergey Pashkov Accepted Answer
Could you please create a SQL dump of the "municipio" table? If it contains the correct definition:
ALTER TABLE ONLY base_corporativa.municipio
ADD CONSTRAINT fk_m_estado FOREIGN KEY (id_estado) REFERENCES base_corporativa.estado(id);

then I can check the pg_dump sources to see how it gets information about the foreign keys.

Also, if it is possible to create a new test database, uncompress and load attached dump into it - is the issue reproducible (no id column for foreign key in information_schema.constraint_column_usage) ?
Attachments (1)
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Studio
  3. # 12
Douglas Paes Accepted Answer
In the dump of table "municipio" constains the definition in attachment.

About creating the test database, unfortunately I can not do it at the moment. Sorry.
Attachments (1)
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Studio
  3. # 13
Douglas Paes Accepted Answer
Thanks, Sergey.
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Studio
  3. # 14
Douglas Paes Accepted Answer
Sergey, I created the test database and gave the restore in its dump.

I ran the query SELECT * FROM information_schema.constraint_column_usage and the output is attached.
Attachments (1)
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Studio
  3. # 15
Douglas Paes Accepted Answer
I discovered the reason for the problem. It is the permission of a specific user that I was using to connect to the database.

Thanks for your help, Sergey.
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Studio
  3. # 16
  • Page :
  • 1


There are no replies made for this post yet.
However, you are not allowed to reply to this post.

Categories

Announcements & News
  1. 0 subcategories
Valentina Studio
  1. 2 subcategories
Valentina Server
  1. 4 subcategories
Valentina Database ADK
  1. 0 subcategories
Valentina Reports ADK
  1. 0 subcategories
Other Discussions
  1. 0 subcategories
BETA Testing
  1. 0 subcategories
Education & Research
  1. 0 subcategories
Omegabundle
  1. 0 subcategories