New post
Question
· Feb 11, 2022

How to get the list of indices in a class in order to manually delete some or all of them given some rules

#Databases #InterSystems IRIS for Health

Hi, the class %Library.Storage has several methods such as  %BuildIndices(pIndexList As %RawString = "" ...), %ValidateIndices(idxList As %List = ""...), ... which take as a parameter (amongst others) a list of indices (in the example, the signature of the list is not the same but I guess it does not matter).

My question is: can get this list in code ? I know that is it possible to get the list of properties of a class in code, but can I have the same for indices ?

For those wondering what I'm trying to achieve:

I want to be able to update the schema of my database between two version of a product. For some reasons, we used DocDb. As I haven't been able to find something like Liquibase, the best I could come up with was to write a method that gets all my existing tables, remove its properties and indices and then rebuild properties and indices according to the new schema.

Product version: IRIS 2021.1
Discussion (7)3
Log in or sign up to continue
Michel Liberado · Feb 11, 2022

Thanks, this works.
But, my question did not exactly reflect my thoughts, I needed the ID of a class' indices in order to delete them manually since I haven't found any method that would be %RemoveIndices(pIndexList As %List=""...).


So, in order to get the IDs, I use 

SELECT ID
FROM %Dictionary.IndexDefinition
WHERE  parent = 'your.class'

Then we use %DeleteId to delete the found indices. This is not fully tested yet.




 

0 0
Cristiano Silva · Feb 13, 2022

Hi Michel,

Yes, your SELECT statement returns the ID of he class. The ID of the %Dictionary.IndexDefinition class is compounded by parent||Name. The property parent is a reference to the class definition that contains the index.

See:

See the documentation for more information: 

https://docs.intersystems.com/irislatest/csp/documatic/%25CSP.Documatic....

0 0
Benjamin De Boe · Feb 14, 2022

Two small notes:

  • The difference between %Dictionary.IndexDefinition and %Dictionary.CompiledIndex is that the former has all the indices you defined in that class itself, and the latter also the ones it got through class inheritance. Obviously, you can only delete the ones in the former list, unless you're sure you're not accidentally dropping one that still matters for another subclass of the same parent in which it was defined.
  • If you use %DeleteId() to drop an index, you'll need to call %PurgeIndices() for those indices first (iianm) and recompile the affected class(es) afterwards. A slightly more elegant approach would be to call DROP INDEX for each entry in the list. That'll take care of the recompiling and data-dropping for you.
2 0
Arturo Masero · Feb 14, 2022

Hi,

In the Library.ClassDefinition you have an Indices property you can iterate and get info about. I don't know if it fits to your needs.

You can code something like:

set myClassDef = ##class(%Library.ClassDefinition).%OpenId("MyClass") 
for anIndex = 1 : 1 : myClassDef.Indices.Count() { 
     set indexes = myClassDef.Indices.GetAt(anIndex).Properties 
     // and operate with the indexes variable 
}

PS: Sorry, I don't get the highlight code feature to work properly.

0 0
Michel Liberado · Feb 14, 2022

Thanks everyone, I've read all your answers.


I​​ am using a %DocDB.Database. I have many colons created using %CreateProperty and some of them have the UNIQUE indicator set to true, therefore an index is created.
BUT, the %CreateProperty method is quite limited and I couldn't find how to make indexes on multiple columns/properties without doing some kind of trick (adding a property whose propertyExpression is the combination of other properties).

Anyway, I ended up building some indexes manually like that:

SET className = tfullDatabaseName
SET indexDefinition = ##class(%Dictionary.IndexDefinition).%New()
DO indexDefinition.parentSetObjectId(className)
SET indexDefinition.Name = "col1AndColB"
SET indexDefinition.Properties = "col1,colB"
SET indexDefinition.Unique = 1
SET status = indexDefinition.%Save()


Whenever I want to update my database schema, our strategy is to remove all properties and indexes.
Of course, the original %DropProperty drops indexes associated to the property to drop using an internal server only method dropIndex:
 

/// dropIndex() - delete an index definition and its structure. This method returns an oref referencing the index definition
/// document that was removed from the index definition database.
/// <br>
/// <pre>
/// SAMPLES>set people = $system.DocDB.GetDatabase("People")
/// SAMPLES>set index = people.%DropIndex("HC")
/// SAMPLES>write index.%ToJSON()
/// {"database":"People","name":"HC","type":"bitmap","class":"%DocDB.Server.Index.Bitmap","key":[["HomeCity","string"]]}
/// </pre>

Method dropIndex(indexName As %RawString = "") As %Library.DynamicAbstractObject [ Internal, ServerOnly = 1 ]

{
    TRY {
        SET response = $THIS
        $$$THROWONERROR(status,$CLASSMETHOD(..ClassName,"%PurgeIndices",$LISTBUILD(indexName)))
        $$$THROWONERROR(status,##class(%Dictionary.IndexDefinition).%Delete($$$oidForm(..ClassName_"||"_indexName)))
// check status?

    } CATCH exception {
        SET response = ""
        THROW exception
}
RETURN response
}


By the way, the documentation of this method suggest that you can use %DropIndex ... maybe this is an error.

Note that this instance method is called only within the %DropProperty method, within a TSTART TCOMMIT block and the class is also locked.

About my initial question, my original intent was to drop ALL indexes on the database, even those NOT created with %CreateProperty.
I tried to mimic the code of %DroptProperty (TSTART TCOMMIT, lock class, ...) with no success. When I try to recreate properties and columns for all my %DocDb.Databases it fails randomly.


 

0 0