1. Douglas Paes
  2. Valentina Studio
  3. 木, 3月 28 2019, 02:27 PM
  4.  メールで購読
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.
コメント
There are no comments made yet.
Sergey Pashkov 承諾済みの回答
Hello Douglas,

Are the tables in the same schema or different?
コメント
There are no comments made yet.
Douglas Paes 承諾済みの回答
Hello Sergey,

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

See the attachment.
添付ファイル
コメント
There are no comments made yet.
Sergey Pashkov 承諾済みの回答
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.
添付ファイル
コメント
There are no comments made yet.
Douglas Paes 承諾済みの回答
Really, very strange.

In attachment, the output for the query SELECT * FROM information_schema.constraint_column_usage
添付ファイル
コメント
There are no comments made yet.
Sergey Pashkov 承諾済みの回答
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) ?
添付ファイル
コメント
There are no comments made yet.
Douglas Paes 承諾済みの回答
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.
添付ファイル
コメント
There are no comments made yet.
Sergey Pashkov 承諾済みの回答
Thank you, I’ll review the pg_dump algorithms, there should be some difference.
コメント
There are no comments made yet.
Douglas Paes 承諾済みの回答
Thanks, Sergey.
コメント
There are no comments made yet.
Douglas Paes 承諾済みの回答
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.
添付ファイル
コメント
There are no comments made yet.
Douglas Paes 承諾済みの回答
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.
コメント
There are no comments made yet.
  • ページ :
  • 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