Dear All
I am currently trying to find out if kexi is suitable for the data collection issues
that do not warrant full scale database development. Here, I have run across
the issue of column constraints. During the creation of the database structure
I cannot find such an option, other than primary key and a few others.
What about being able to specify lists of allowed values etc. ?

Am I overlooking something somewhere?
Your response would be highly appreciated.

cheers

Tred

Hi Tredlie
Thanks for trying KEXI.
There is no support for entering a simple list of possible values for a column in the current KEXI but this is in the plans. A longer route to achieve the same would be using one-to-many relationships. This feature is present in the GUI of the Table Designer. Add a column of integer type and use the Record source/Bound column/Visible column properties of the Property editor (right hand). Before you start designing such a parent table you need to create a child table first with values (primary key plus at least one "visible value" column).

For example .kexi database you can refer to http://kexi-project.org/download/exampl ... abase.kexi. "Ownership" table is the parent and cars and persons tables are children.

Jarozlav

thank you so much for your response!

yes, that would be solution/workaround for the list issue. Can you share the plans
for the constraint integration? would that be a more generalized constraint
procedure supporting the SQL CONSTRAINT setup?

Already something like lower/upper values would help I would assume.
I am actually sort of surprised that this is not a common requirement also
for KEXI users.

HOW does KEXI handle imports of PG/ORACLE with CONSTRAINTS, simply ignore
them?

greetings

Tred

It's all welcome features, nothing is rejected. KEXI's file database format inherits features from SQLite. Here's the SQL docs: https://www.sqlite.org/lang.html.

KEXI does not import such complex constraints even if they are pretty standard SQL. In the future it's possible that it can import them. But first KEXI needs to offer such feature in its Table Designer and/or in its own SQL parser.

Thing is, because KEXI also works with MySQL and PostgreSQL ("backends" / "drivers"), KEXI does not just pass whatever SQL is defined by the user but carefully acts as a middleman to handled differences between various backends. Example on how complex this task is can be observer in case of SQL functions for example: https://community.kde.org/Kexi/Plugins/ ... _Functions.

Older KEXI versions (1.x) worked with Oracle and MSSQL, and this can come back if there are contributors.

Whatever next SQL feature we add, some analysis is needed on how it can be supported by all the current (and also future) the backends.

Jarosław
(I hope I got it right now)

Thanks for your explanation. Your points are well taken and make sense.
Maybe, a different approach will help, and you have the solution at hand.

This issue of needing to check (numerical) data on input is pervasive I assume.
Lets think about data collection in experiments.

Do you have an idea of how people handle this situation with KEXI?
Data may come in through csv files. Maybe there is a way to pipe them through
a script before it gets into the target tables?
Or is there a way to import into a temp table or flagged as NOT_TESTED and then
have some script run over them. But that would require having a hook somewhere
in KEXI that would allow this.

Or maybe something totally different.

greetings

Tred

Using CSV as input format with a script that verifies extra constraints is general idea for now. KEXI has plans for scripts too (JS or Python) but currently I would say that verification script belongs entirely to the user and his/her responsibility.

Even if we have scripts in KEXI I admit typical constraints or conditions shall be useful to have without a need for programming. Also when we develop queries / SQL more, it will be possible to express conditions or checks using queries, notably in UPDATE / INSERT / DELETE queries.

Yes, doing the check (ranges and the like that do not require old database content
is probably the way to go. Then the useres can also fix the issues right away.

Jarosław, thanks a lot.

Tred