Feature Request: Combined Indices

[onoehring]

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

[jsetzer]

I totally agree!

Especially with lookups so often I need a unique constraint on parent_id + one more field.

[jsetzer]

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.

[onoehring]

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

[jsetzer]

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 (4.02 KiB) Viewed 4791 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 (4.32 KiB) Viewed 4791 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 (5.49 KiB) Viewed 4791 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 (2.73 KiB) Viewed 4791 times

   For those we perfectly get an AJAX request on change and an immediate response in UI:
   

   

   chrome_awMj0Hqaw5.png (3.7 KiB) Viewed 4791 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.

[onoehring]

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

[jsetzer]

• 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!