Idea of UI for importing about cuba-component-data-import HOT 13 CLOSED

We could develop the following behaviour:

1. Pick master entity e.g. Car.class
2. Map columns respectively to this master entity:

3. Define policy for searching if entity already exists with its behaviour

4. Define column for EOF detection (when meeting first empty cell in it)
5. Define map of default values

from cuba-component-data-import.

Yeah, this sounds good.

1. could be either done via: giving the master entity as a parameter, or make it the first step of the wizard. Probably both should be supported.
2. Yeah, i don't know how the UI should look like. Additionally we should start small with direct String / int attributes. Then we take in ENUMS, then associations or something.
3. I don't really know what you mean. Can you elaborate?
4. this is Excel specific, right? For CSV it would be options like "seperator" e.g.
5. might make sense. Perhaps also not 0.1.0, but sounds good.

from cuba-component-data-import.

3 - reading a line you need to see if you already have this record in the database. And if yes, what should be done? Should data be re-written? Should it be left as is with no action? Should be created a new similar row? E.g. in this case if customer exists we will update all the fields of it to what is specified in the importing file
4 - yes, you are right
5 - map of defaults is extremely useful, and already developed (see here). So, if you cannot parse something, or you have an empty value for a mandatory attribute, then you simply apply defaults.

from cuba-component-data-import.

Alright - so I implemented the first version. Mainly it was with CSV in mind, but I have already thought about XSLX :)

The current wizard has the following steps:

1. upload file
2. select entity
3. setup columns <-> attributes
4. extended import configuration
5. preview

The following features are not included in the first draft:

• setup default values
• support for boolean type
• references to other entities
• preview on the parsed data instead of the raw csv data
• feedback if import was not successful
• select attributes from drop down instead of text field
• a lot of other stuff

Let's talk about it ;)

from cuba-component-data-import.

Looks great!

Form my experience it would be also great to be able to define some kind of transformation script for each column right at step 3 and remove step 4. So, you can (or must for associations attributes or enum types) define a groovy script where you have value as the parameter, then you transform it into the right output format.

What do you think about it? From my perspective, this would make the applicability of such importer much wider.

We also can have prefined scripts for Integers, Doubles, Dates and other widely used types, to convert the value into the corresponding format.

from cuba-component-data-import.

Another improvement I see is an ability to save upload scenario and re-use it later.

from cuba-component-data-import.

So, you can (or must for associations attributes or enum types) define a groovy script where you have value as the parameter, then you transform it into the right output format.

Ah yeah, I thought about that as well. This would be like the escape mode basically to not being restricted by the pre-defined config options. That makes sense.

But it is questionable, which people will mainly use that wizard. Is it a developer? In this case, this feature makes total sense. Is it an end user / administrator? Than this feature will scare people away. Therefore i think we should try to build the main use cases (enum matcher, lookup of another entity by ID / another column etc.) that into the wizard.

But we should also allow this kind of scripting. Perhaps this will not be part of the wizard but instead just part of the regular Editor of the ImportConfiguration, which will then be prepared by the developers and then saved. Afterwards the users will just use the config and import their file with this config

We also can have prefined scripts for Integers, Doubles, Dates and other widely used types, to convert the value into the corresponding format.

this is what i hard-coded for now (and made it configurable via step 4.).

Another improvement I see is an ability to save upload scenario and re-use it later.

will easily be achievable. It already uses the ImportConfiguration under the covers...

from cuba-component-data-import.

But it is questionable, which people will mainly use that wizard. Is it a developer? In this case, this feature makes total sense. Is it an end user / administrator? Than this feature will scare people away. Therefore i think we should try to build the main use cases (enum matcher, lookup of another entity by ID / another column etc.) that into the wizard.

Again, from my experience users will mess everything up using such tool. So, I would say it will be an administrator. A user will be using only pre-configured scenarios.

As for searching by id - this is never the case in the real world. Did you see tables for import with uids from your system in it :)?

At the same time writing a simple script is not a big problem for anyhow educated administrator, e.g.
if (value.equals("high")) return Priority.HIGH else return Priority.LOW
Plus, for all non-string types of attributes, we can have default transformation scripts, like for an int field it would be like:
Integer.valueOf(value)

from cuba-component-data-import.

As for searching by id - this is never the case in the real world. Did you see tables for import with uids from your system in it :)?

Obviously not by ID in case of a UUID. But sometimes it might be a auto increment ID and then it might actually be the case... Whatever - the main point is here to select an association by an attribute of that association. Example:

MlbPlayer N:1 MlbTeam through the attribute team. Now MlbTeam has attributes name and code.

In the CSV file there is a reference to team and it contains the code of the MlbTeam entity.

This is a fairly easy case and it should just work out of the box. The equivalent in Excel is vlookup :)

Here's the full example:

I updated the MLB example in the code...

from cuba-component-data-import.

3 - reading a line you need to see if you already have this record in the database. And if yes, what should be done? Should data be re-written? Should it be left as is with no action? Should be created a new similar row? E.g. in this case if customer exists we will update all the fields of it to what is specified in the importing file

@aleksey-stukalov , you wrote that above that stated it Unique Index in the table (#5 (comment)). But this is not an already existing DB unique constraint that should get introspected by the application, but rather something that can be defined by the user, right?
So the user says "if Driver.firstname + Driver.lastname in this combo already exists, then do nothing / replace / update etc." - correct?

This would require that actually every data row will some amount of queries to the DB. But it sounds reasonable...

from cuba-component-data-import.

@aleksey-stukalov , you wrote that above that stated it Unique Index in the table (#5 (comment)). But this is not an already existing DB unique constraint that should get introspected by the application, but rather something that can be defined by the user, right?

Right, this is some kind of business-key, some set of columns, that specify unique of a record.

This would require that actually every data row will some amount of queries to the DB. But it sounds reasonable...

Yes, but there is no other way...

from cuba-component-data-import.

ok then - i understand. Created a story for that:
#20

from cuba-component-data-import.

Will close this issue now, as we have a solid understanding of how the wizard should look like.
Follow up issues will come in order to improve.

from cuba-component-data-import.