AppGini Community Forums

database functions for dropping and/or (re-)creating indexes

addIndex("values", ["class_id", "object_id", "property_id"], true);

addIndex("properties", ["class_id","name"], true);
addIndex("properties", ["class_id", "position"], true);

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 7517 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 7517 times

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

   

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

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

// 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"]
  ];
}

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