Feature Request: Combined Indices

Wish to see a specific feature/change in future releases? Feel free to post it here, and if it gets enough "likes", we'd definitely include it in future releases!
Post Reply
User avatar
onoehring
AppGini Super Hero
AppGini Super Hero
Posts: 1201
Joined: 2019-05-21 22:42
Location: Germany
Contact:

Feature Request: Combined Indices

Post by onoehring » 2023-12-17 07:52

Hi,

I am aware, that one can make a field unique. This results in an index being created in the database.
There are times, when a combination of multiple fields has to be unique. Currently there is no way (at least that I know of) to create such an index directly from AG.
Thus my request:
Make it possible to define indices containing multiple fields directly from the table design in AG.

Thanks
Olaf

User avatar
jsetzer
AppGini Super Hero
AppGini Super Hero
Posts: 1869
Joined: 2018-07-06 06:03
Location: Kiel, Germany
Contact:

Re: Feature Request: Combined Indices

Post by jsetzer » 2023-12-17 09:59

I totally agree!

Especially with lookups so often I need a unique constraint on parent_id + one more field.
Kind regards,
<js />

My AppGini Blog:
https://appgini.bizzworxx.de/blog

You can help us helping you:
Please always put code fragments inside [code]...[/code] blocks for better readability

AppGini 24.14 Revision 1665 + all AppGini Helper tools

User avatar
jsetzer
AppGini Super Hero
AppGini Super Hero
Posts: 1869
Joined: 2018-07-06 06:03
Location: Kiel, Germany
Contact:

Re: Feature Request: Combined Indices

Post by jsetzer » 2023-12-17 11:33

Thinking it over: Could become complex in UI if we need to configure multiple multi-column unique constraints on the same table, for example

IX_1 as parent_id + position
AND
IX_2 as parent_id + name

I'm afraid programming a configuration-UI for many useful combinations could be a big invest (time and cost) for BigProf. So, maybe it would be easier AND more flexible for us to have some _after_upgrade hook in __global plus some database functions for dropping and/or (re-)creating indexes. Such a place would allow any SQL changes, also pre-populating new tables with some default-data etc.

Just a few thoughts.
Kind regards,
<js />

My AppGini Blog:
https://appgini.bizzworxx.de/blog

You can help us helping you:
Please always put code fragments inside [code]...[/code] blocks for better readability

AppGini 24.14 Revision 1665 + all AppGini Helper tools

User avatar
onoehring
AppGini Super Hero
AppGini Super Hero
Posts: 1201
Joined: 2019-05-21 22:42
Location: Germany
Contact:

Re: Feature Request: Combined Indices

Post by onoehring » 2023-12-18 08:56

Hi Jan,

I was just thinking about
database functions for dropping and/or (re-)creating indexes
so far.
Your idea that taking indices into account in lookup fields etc. is a "bonus" :-)

So far I have been creating such constraints directly via phpmyadmin / adminer. But as a result, AG and it's generated code does not take such constraints into account which sometimes results in errors that could hold more explaining text for the enduser - instead of the SQL error.

Olaf

User avatar
jsetzer
AppGini Super Hero
AppGini Super Hero
Posts: 1869
Joined: 2018-07-06 06:03
Location: Kiel, Germany
Contact:

Re: Feature Request: Combined Indices

Post by jsetzer » 2023-12-18 10:12

Hi Olaf,

I've checked and tested that new function addIndex() this morning. This works just fine for me:

Multi-column unique constraint on a table

Code: Select all

addIndex("values", ["class_id", "object_id", "property_id"], true);
chrome_zewS2RrZf0.png
chrome_zewS2RrZf0.png (4.02 KiB) Viewed 4803 times

Multiple Multi-column unique constraints on the same table

Code: Select all

addIndex("properties", ["class_id","name"], true);
addIndex("properties", ["class_id", "position"], true);
chrome_rXVQyJA9yI.png
chrome_rXVQyJA9yI.png (4.32 KiB) Viewed 4803 times

---

Backdraws

There are a few things which could be better from my point of view and that's the reaons why I am using my own implementation instead:
  1. I'm nerdy and I love naming conventions. Therefore my personal implementation generates index names by a given naming convention:

    IX_VALUES_class_id_object_id_property_id
    which is IX_ + TABLENAME + ...fieldnames

    ---
  2. When changing order of fieldnames in function-call, AppGini creates a completely new index. This is not required and negatively effects performance, especially on larger database tables.

    You can check it out:

    Code: Select all

    addIndex("values", ["class_id", "object_id", "property_id"], true);
    addIndex("values", ["object_id", "class_id", "property_id"], true);
    
    chrome_Q4OH6JovB6.png
    chrome_Q4OH6JovB6.png (5.49 KiB) Viewed 4803 times

    The reason why this is not required:
    If (a+b) has to be unique, then (b+a) implicetly has to be unique. So, one of them is redundant.
    I think AppGini should automatically detect such a situation and skip creation. I my personal implementation I elimit such.

    ---
  3. One last backdraw: In Detail View AppGini checks for uniqueness for the constraints we have modeled like this:
    AppGini_WPsQb715bP.png
    AppGini_WPsQb715bP.png (2.73 KiB) Viewed 4803 times
    For those we perfectly get an AJAX request on change and an immediate response in UI:
    chrome_awMj0Hqaw5.png
    chrome_awMj0Hqaw5.png (3.7 KiB) Viewed 4803 times
    But not for any other constraints.

    I think AppGini, on form-change (client side), should also server-sidely check against database constraonts and feed back response to UI.

    There is especially one very important reason for me:
    When user submits data and INSERT fails due to database constraints, the entered data is lost.

    Too bad for users. Therefore, from my point of view, there should be client-side validation before!

---

In my personal implementation this is how I create indexes:

Code: Select all

$ixb = new \Extensions\IndexBuilder();
$ixb->run();

Code: Select all

// file: hooks/__global.php
function database_indexes()
{
  return [
    "values" => ["class_id", "object_id", "property_id"],
    "properties" => [
      ["class_id", "name"],
      ["class_id", "position"]
    ],
    "objects" => ["class_id", "name"]
  ];
}
The function in __global.php can be extended at any time and allows options for creating unique constraints as single-column/multi-column and multiple single-/multi-column index.
Kind regards,
<js />

My AppGini Blog:
https://appgini.bizzworxx.de/blog

You can help us helping you:
Please always put code fragments inside [code]...[/code] blocks for better readability

AppGini 24.14 Revision 1665 + all AppGini Helper tools

User avatar
onoehring
AppGini Super Hero
AppGini Super Hero
Posts: 1201
Joined: 2019-05-21 22:42
Location: Germany
Contact:

Re: Feature Request: Combined Indices

Post by onoehring » 2023-12-19 07:17

Hi Jan,

as always: A very detailed and thought through answer. I very much like this.

First a question: What new function "addIndex"? Where can this be found? I guess I missed something.

I think your suggestion "IX_ + TABLENAME + ...fieldnames" is for AG and in general a good idea.

ad 2: You are correct. I think this should be fixed in AG internally. Oh, does this happen when the order of fields in the GUI is changed? Probably not (yet) as there are no multi-column constraints.

ad 3: This was my initial intention: The "Unique" checkbox is nice, but for more complex applications just not enough.
Also: If AG recognizes, that insertion (or update) of a record fails, it should present the user with the data he tried to save - instead of loosing the data as you mention.

Olaf

User avatar
jsetzer
AppGini Super Hero
AppGini Super Hero
Posts: 1869
Joined: 2018-07-06 06:03
Location: Kiel, Germany
Contact:

Re: Feature Request: Combined Indices

Post by jsetzer » 2023-12-19 07:32

  • First a question: What new function "addIndex"?

    Sorry, I thought you were referring to the new function mentioned in changelog where it says:
    Add addIndex() function to create or update specified index only if necessary
  • Where can this be found?
    admin/incFunctions.php
  • Oh, does this happen when the order of fields in the GUI is changed?

    No, this just happens when you call addIndex() in your own code.
  • If AG recognizes, that insertion (or update) of a record fails, it should present the user with the data he tried to save - instead of loosing the data as you mention.
    I absolutely agree!
Kind regards,
<js />

My AppGini Blog:
https://appgini.bizzworxx.de/blog

You can help us helping you:
Please always put code fragments inside [code]...[/code] blocks for better readability

AppGini 24.14 Revision 1665 + all AppGini Helper tools

Post Reply