Errors when importing entities of different schemas.

Forum for posting problems using QxEntityEditor

Errors when importing entities of different schemas.

Postby jackson » Thu Mar 20, 2014 8:43 am

Hi

I am new to QxOrm and the entityEditor

The first thing i wanted to do, was to import some entities from an existing db.

Environment
Postgres 9.3
EntityEditor 1.1.3
DB structure with public and isoxml2 schema and the tables:
- isoxml2.wkr_worker
- public.wkr_worker (other columns than isoxml2.wkr_worker to be able to see differences)
- public.lsg
- all tables have a primary key which sequence is defined in isoxml2 schema.


The first try was without any searchpath on postgres session and user (default on postgres)
Code: Select all
ALTER USER postgres SET search_path = '';

- all columns are recoginized (OK)
- public.wkr_worker was not shown in dialog (Not OK)
- primary key was not recognized on any table (public, isoxml2) (Not OK)
Error
Entity 'variodoc::wkr_worker' is not valid :
- Primary key property is empty

no_searchpath.jpg
no_searchpath.jpg (60.03 KiB) Viewed 43418 times





Due to the sequence is defined in isoxml2 schema I changed the searchpath to
Code: Select all
ALTER USER postgres SET search_path = 'isoxml2', 'public'

- all columns are recognized for isoxml2 (OK)
- the columns for public.lsg are not recognized (not OK)
- public.wkr_worker was not shown in dialog (Not OK)
- primary keys are recognized on any table in isoxml2 (OK)
searchpath_isoxml2_public.jpg
searchpath_isoxml2_public.jpg (54.33 KiB) Viewed 43418 times


The third try was to change the searchpath order to
Code: Select all
ALTER USER postgres SET search_path = 'public', 'isoxml2'

- all columns are recognized for public (OK)
- the columns for isoxml2 are not recognized (not OK)
- isoxml2.wkr_worker was not shown in dialog (Not OK)
searchpath_public_isoxml2.jpg
searchpath_public_isoxml2.jpg (63.48 KiB) Viewed 43418 times


I think the schema have to be concerned by building the dialog to avoid throwing away tables with duplicate names.
Further the pk search when the pk is defined in a schema is not working.

Exists there any solution to be able to import the entities correctly with many schemas and without setting any searchpath?

Regards
jackson
jackson
 
Posts: 4
Joined: Thu Mar 20, 2014 7:22 am

Re: Errors when importing entities of different schemas.

Postby qxorm » Thu Mar 20, 2014 11:05 am

Hi,

As it is written on the import by ODBC dialog, this plugin needs to be improved : for now, this plugin uses Qt API to find all items in a database (in a generic way).
This Qt API is fine for simple cases.

Now, we need to improve this import plugin to use directly the INFORMATION_SCHEMA views (see http://www.postgresql.org/docs/9.1/stat ... chema.html for example for PostgreSQL database).
Even if INFORMATION_SCHEMA is a SQL standard, each database type has implemented it in a different way, so the plugin needs specific code for each database type to retreive information from INFORMATION_SCHEMA views.

Thx very much for your detailed topic, it will help us to improve the import plugin for PostgreSQL database.
Could you export please your database structure in a file ?
This way, we could import it in our server and test it before releasing a new plugin...

Another question : which version of QxEntityEditor are you using (Windows, Linux, Mac OS X) ?
We could try to provide a new import plugin for your version, so you will be able to test it and validate it...

Thx !
qxorm
Site Admin
 
Posts: 481
Joined: Mon Apr 12, 2010 7:45 am

Re: Errors when importing entities of different schemas.

Postby jackson » Thu Mar 20, 2014 2:40 pm

Thanks for you quick reply.

Environment is windows 7 - 64
EntityEditor 1.1.3

"We could try to provide a new import plugin for your version, so you will be able to test it and validate it..."
You are welcome :=)

By the way, when do you expect to support importing relations from database? In my oppinion this is the main missing feature.

Below is a simple schema, which should be sufficient for testing.

Code: Select all
create schema isoxml2;
ALTER USER postgres SET search_path = '';
-- Sequence: isoxml2.pk_seq

-- DROP SEQUENCE isoxml2.pk_seq;

CREATE SEQUENCE isoxml2.pk_seq
  INCREMENT 1
  MINVALUE 1
  MAXVALUE 9223372036854775807
  START 13821
  CACHE 1;
ALTER TABLE isoxml2.pk_seq
  OWNER TO postgres;
COMMENT ON SEQUENCE isoxml2.pk_seq
  IS 'General sequence for all tables';


-- Table: lsg

-- DROP TABLE lsg;

CREATE TABLE public.lsg
(
  pk bigint NOT NULL DEFAULT nextval('isoxml2.pk_seq'::regclass),
  a_type smallint NOT NULL,
  b_designator character varying(32),
  c_width integer,
  d_length integer,
  e_colour smallint,
  f_id character varying(14),
  CONSTRAINT pk_lsg_linestring PRIMARY KEY (pk)
)
WITH (
  OIDS=FALSE
);

-- Table: wkr_worker

-- DROP TABLE wkr_worker;

CREATE TABLE public.wkr_worker
(
  pk bigint NOT NULL DEFAULT nextval('isoxml2.pk_seq'::regclass),
  a_start timestamp without time zone NOT NULL,
  b_stop timestamp without time zone,
  c_duration bigint,
  d_type smallint NOT NULL,
  CONSTRAINT pk_wkr_worker2 PRIMARY KEY (pk)
)
WITH (
  OIDS=FALSE
);
-- Table: isoxml2.wkr_worker

-- DROP TABLE isoxml2.wkr_worker;

CREATE TABLE isoxml2.wkr_worker
(
  pk bigint NOT NULL DEFAULT nextval('isoxml2.pk_seq'::regclass),
  a_id character varying(14) NOT NULL, -- Unique identifier of worker...
  b_last_name character varying(32) NOT NULL, -- Last name
  c_first_name character varying(32), -- First name
  d_street character varying(32), -- Street
  e_po_box character varying(32), -- Postbox
  f_postal_code character varying(10), -- Postal code
  g_city character varying(32), -- City
  h_state character varying(32), -- State, province
  i_country character varying(32), -- Country
  j_phone character varying(20), -- Telephone number
  k_mobile character varying(20), -- Mobile phone number
  l_license_number character varying(32), -- Worker-specific licence number
  m_email character varying(64), -- E-Mail
  CONSTRAINT pk_wkr_worker PRIMARY KEY (pk)
)
WITH (
  OIDS=FALSE
);

-- Table: isoxml2.wan_worker_allocation

-- DROP TABLE isoxml2.wan_worker_allocation;

CREATE TABLE isoxml2.wan_worker_allocation
(
  pk bigint NOT NULL DEFAULT nextval('isoxml2.pk_seq'::regclass),
  a_worker_id_ref character varying(14) NOT NULL, -- Reference to XML element Worker Format: (WKR|WKR-)([0-9])+
  task_id_ref character varying(14) NOT NULL
)
WITH (
  OIDS=FALSE
);

jackson
 
Posts: 4
Joined: Thu Mar 20, 2014 7:22 am

Re: Errors when importing entities of different schemas.

Postby qxorm » Thu Mar 20, 2014 2:57 pm

Environment is windows 7 - 64
EntityEditor 1.1.3

Yes but on Win 7 64b, you could download 3 versions : 32b, 64b or XP.
Which version of QxEntityEditor 1.1.3 are you using (I guess 64b) ?

when do you expect to support importing relations from database?

Using INFORMATION_SCHEMA views, it should be possible to manage relationships.
qxorm
Site Admin
 
Posts: 481
Joined: Mon Apr 12, 2010 7:45 am

Re: Errors when importing entities of different schemas.

Postby jackson » Thu Mar 20, 2014 3:18 pm

Sorry,

QxEntityEditor_1.1.3_win_32b.zip the portable version.


when do you expect to support importing relations from database?

Using INFORMATION_SCHEMA views, it should be possible to manage relationships.


What do you mean?
While importing from postgres, no relations were found. Adding them in the designer works, but that's not my goal.
jackson
 
Posts: 4
Joined: Thu Mar 20, 2014 7:22 am

Re: Errors when importing entities of different schemas.

Postby qxorm » Thu Mar 20, 2014 3:43 pm

What do you mean? While importing from postgres, no relations were found. Adding them in the designer works, but that's not my goal.

I mean :
* for now, the plugin uses the Qt API in a generic way to fetch details from database : this way, there is not enough information to build relationships ;
* when we will improve this plugin using INFORMATION_SCHEMA views, first we will fix your issue, and we will have more details about the database structure, so it should be possible to import automatically all relationships.

QxEntityEditor_1.1.3_win_32b.zip the portable version.

Ok, I can't give you a date to test this improvment (because I'm very busy for now), but I'll upload on this topic a new plugin (for the version QxEntityEditor_1.1.3_win_32b.zip) when all will be ready...
qxorm
Site Admin
 
Posts: 481
Joined: Mon Apr 12, 2010 7:45 am

Re: Errors when importing entities of different schemas.

Postby qxorm » Fri Mar 28, 2014 10:15 pm

Here is a first try to improve the import process for PostgreSQL database.
You can download QxEntityEditor_1.1.4_BETA_06 (portable win 32) here :
http://www.qxorm.com/version/QxEntityEd ... in_32b.zip

There are 2 new fields in the import by ODBC screen :
* Database type : select your database type ("Generic" means the old process, "PostgreSQL" uses specific queries to get database structure) ;
* Delete all entities in the namespace before importing : checkbox to delete entities automatically.

Using "Database type" equals to "PostgreSQL" :
* schema should be managed correctly : you could have a same table name in different schema for example ;
* relationships should be imported.

There is certainly some things to improve, but this is a first try and you can test it...
qxorm
Site Admin
 
Posts: 481
Joined: Mon Apr 12, 2010 7:45 am


Return to QxEntityEditor - Help

Who is online

Users browsing this forum: No registered users and 2 guests

cron