1. Douglas Paes
  2. Valentina Studio
  3. Donnerstag, März 28 2019, 02:27 PM
  4.  Abonnieren via E-Mail
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.
Kommentar
There are no comments made yet.
Sergey Pashkov Akzeptierte Antwort
Hello Douglas,

Are the tables in the same schema or different?
Kommentar
There are no comments made yet.
Douglas Paes Akzeptierte Antwort
Hello Sergey,

The tables are in the same schema.
Kommentar
There are no comments made yet.
Sergey Pashkov Akzeptierte Antwort
Could you please add SQL definitions of both tables?
You can use a context menu for it: "Generate SQL"->"Create"
Kommentar
There are no comments made yet.
Douglas Paes Akzeptierte Antwort
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;
Kommentar
There are no comments made yet.
Sergey Pashkov Akzeptierte Antwort
Thank you, for some reason, the PK field is not defined for the link fk_m_estado.
What version of PostgreSQL is used?
Kommentar
There are no comments made yet.
Douglas Paes Akzeptierte Antwort
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
Kommentar
There are no comments made yet.
Sergey Pashkov Akzeptierte Antwort
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?
Kommentar
There are no comments made yet.
Douglas Paes Akzeptierte Antwort
Does not get.

See the attachment.
Anhänge
Kommentar
There are no comments made yet.
Sergey Pashkov Akzeptierte Antwort
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.
Anhänge
Kommentar
There are no comments made yet.
Douglas Paes Akzeptierte Antwort
Really, very strange.

In attachment, the output for the query SELECT * FROM information_schema.constraint_column_usage
Anhänge
Kommentar
There are no comments made yet.
Sergey Pashkov Akzeptierte Antwort
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) ?
Anhänge
Kommentar
There are no comments made yet.
Douglas Paes Akzeptierte Antwort
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.
Anhänge
Kommentar
There are no comments made yet.
Sergey Pashkov Akzeptierte Antwort
Thank you, I’ll review the pg_dump algorithms, there should be some difference.
Kommentar
There are no comments made yet.
Douglas Paes Akzeptierte Antwort
Thanks, Sergey.
Kommentar
There are no comments made yet.
Douglas Paes Akzeptierte Antwort
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.
Anhänge
Kommentar
There are no comments made yet.
Douglas Paes Akzeptierte Antwort
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.
Kommentar
There are no comments made yet.
  • Seite :
  • 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