Icon View Thread

The following is the text of the current message along with any replies.
Messages 1 to 5 of 5 total
Thread User defined fields
Thu, Dec 3 2020 2:42 PMPermanent Link

Anthony

I'm looking at options to add user defined fields to my current EDB database, the intention is to allow end users to create new fields for certain tables. I appreciate that there is both database server work and client work to be developed. So wondered if anyone else has implemented such a feature and any considerations to the best way to approach such a feature.

I appreciate any suggestions
Thu, Dec 3 2020 3:10 PMPermanent Link

Terry Swiers


> So wondered if anyone else has implemented such a feature and any considerations to the best way to approach such a feature.

Not sure it's the best way, but this is how we do it.

We start by storing our complete product database structure in an XML file and embed it within the application as a resource.  We then provide our end users with a window where they can add new fields to the specific tables we allow, and we store the custom information in an identical XML file structure.  We do the same thing with custom versions of our product or plug-ins that require custom storage, and store each of those in their own separate XML files.   

With regards to the fields, we only allow the addition of new fields or to increase the length of existing fields.  The end user can never decrease the length of a field.  We also force the new user defined fields start with u_ so that we never have to worry about them changing the data type of an existing field.

When it comes time to implement the changes to the database, we merge each of the custom XML fields into the standard XML field, repeating for each custom XML file.  With this approach, we can accommodate pretty much any number of additional structure changes.   We then use the final merged XML file to build a in-memory database with all of the standard structure and custom changes combined within the same context of the live database.   We then generate a upgrade script using the EDB reverse engineering and apply it to the live database.

Hope this helps.
Sun, Dec 6 2020 2:19 PMPermanent Link

Anthony

Hi Terry, thanks for your comprehensive and swift reply

Your solution certainly allows for a lot more features than I was considering, including extending existing field lengths. My intention was to allow the storing of additional attributes which may be specific to a certain installation.

>>We then generate a upgrade script using the EDB reverse engineering and apply it to the live database.

So you actually update the structure of the live database, is this performed by the end user or something you perform? I was hoping that the end user could add these fields as and when required but concerned about them updating the structure of the database as it would also require the database being taken offline.

I had thought about about adding a CLOB field to each table and storing JSON or key value pairs with the custom fields and processing these on each record load and generating the UI based on a global custom fields definition per table.

I was thinking the UI would have a panel with the custom field names and data values dynamically added at form creation and then populated with each DB read and saved on a DB post.

It seemed like a lot of work if there was a simpler solution out there.

Thanks again for your reply

Anthony
Sun, Dec 6 2020 4:46 PMPermanent Link

Raul

Team Elevate Team Elevate

On 12/3/2020 2:42 PM, Anthony wrote:
> I'm looking at options to add user defined fields to my current EDB database, the intention is to allow end users to create new fields for certain tables. I appreciate that there is both database server work and client work to be developed. So wondered if anyone else has implemented such a feature and any considerations to the best way to approach such a feature.
>
> I appreciate any suggestions
>

Compared to Terry ours is very simplistic.

We just added extra fields to key tables (we call them USER_DEF_X) of
various types - mostly it's 10 string fields but we do have few int ,a blob.

There is mapping table that identifies what to show on UI for the
USER_DEF_X for given table and if that entry exists then on UI we have a
extra section and we show the control for entry with label as defined.

For our use case it works as it was just small number of tables that
needed these. Most of the time it's one time entry of data into the
field and then just ability to use this in reports/printouts and
filtering/search.

Raul
Sun, Dec 6 2020 9:03 PMPermanent Link

Terry Swiers

Hi Anthony

> Your solution certainly allows for a lot more features than I was considering, including extending existing field lengths. My intention was to allow the storing of additional attributes which may be specific to a certain installation.

Same with us.  Each database can have it's own set of custom fields, stored in a blob field, which we just use before running a restructure of the database.

We also found that once we gave this ability to users, the requirements grew.  We even allow the end users to create custom triggers and data views.   Makes for a very very powerful application.

> So you actually update the structure of the live database, is this performed by the end user or something you perform? I was hoping that the end user could add these fields as and when required but concerned about them updating the structure of the database as it would also require the database being taken offline.

Yes.  We just make sure that the user has a backup of the database and exclusive access.  Once we have that, we just run the upgrade script against the live database.

> I had thought about about adding a CLOB field to each table and storing JSON or key value pairs with the custom fields and processing these on each record load and generating the UI based on a global custom fields definition per table.

We opted to go with full table columns so that users can use the custom fields in either reports or their own SQL queries without having to find some way to parse the data from a single CLOB.

> Thanks again for your reply

You are quite welcome.
Image