View indexes script

[rupertkiwi]

Hi there,

Does anyone know how to capture the indexes for a View in EA using a script?

Thanks,
Rupert

[Arshad]

Hi rupertkiwi

From my understanding i think indexes you mean is position in tree .
If you need to get the position of a view from ea through API from script then you need to use TreePos property from package or element class.
Usually by default EA won't set any values in this property.If you rearranged objects manually in ea you can see the value get updated in the Tpos column of t_package or t_object table.

[rupertkiwi]

Hi EAUser3200,

Apologies, I probably didn't explain it very well.

It is for an element with the stereotype of view that I want to return the indexes for.

Thanks,
Rupert

[Geert Bellekens]

Rupert,

Do you mean the indexes like the ones on tables?
These are stored as operations.
See https://github.com/GeertBellekens/Enterprise-Architect-Add-in-Framework/blob/master/EAAddinFramework/Databases/Index.cs for a wrapper class.

Geert

[Paolo F Cantoni]

I didn't know Views had indexes.  They use indexes (in the underlying tables), but don't have any themselves.  Even "Materialized" Views aren't Views, per se, but dynamic tables.

Paolo

[Geert Bellekens]

I didn't know Views had indexes.  They use indexes (in the underlying tables), but don't have any themselves.  Even "Materialized" Views aren't Views, per se, but dynamic tables.

Paolo

"Have" might be the wrong term, but you can create indexes on views, yes.
Here's an explanation about that: https://stackoverflow.com/questions/1721448/how-do-indexes-work-on-views

And since in EA indexes are stored as part of the table/view I guess you could say a view "has" an index.

Geert

[Paolo F Cantoni]

As I read the whole set of responses, my original assertion stands.  (If you accept that "materialised views" are dynamic tables - as per response #5 in the link).

What is the syntax for defining an index directly on a view (that is not materialised)?

Paolo

[Geert Bellekens]

I don't know what "materialized views" are, but here's an example on how to create indexed views in SQL server.

https://docs.microsoft.com/en-us/sql/relational-databases/views/create-indexed-views

Geert

[Paolo F Cantoni]

I don't know what "materialized views" are, but here's an example on how to create indexed views in SQL server.

https://docs.microsoft.com/en-us/sql/relational-databases/views/create-indexed-views

Geert

Thanks, Geert,

It looks like a SQL Server "Indexed View" is functionally equivalent to an Oracle "Materialized View" and consequently is a dynamic table in all but name.  Like Trump's Media Spokesperson calling a non-fact an "alternative fact".  It's either an objective fact or it isn't.  These kinds of objects are tables whose contents are maintained by means of triggers in other tables that respond to the "query specification".  They are VERY USEFUL but are just tables - dynamically maintained by the RDBMS.

The problem appears to be that EA doesn't recognise them as "tables" and, consequently, doesn't reverse engineer their indexes.

Paolo

[rupertkiwi]

Found it.

The indexes for a view are kept in the Methods collection for an element:

'Dumps the indexes for an element
Sub DumpIndexes(indent, currentElement, thePackage, j As Integer)
    Dim currentPackage As EA.Package
    Set currentPackage = thePackage
    Dim methods As EA.Collection
    Set methods = currentElement.methods
        For i = 0 To methods.Count - 1
            Dim currentmethod As EA.Method
            Set currentmethod = methods.GetAt(i)
            outputws.Cells(j, 1) = thePackage.Name
            outputws.Cells(j, 2) = currentmethod.Name
            outputws.Cells(j, 3) = currentmethod.Type
            outputws.Cells(j, 4) = currentmethod.AttributeID
            outputws.Cells(j, 5) = currentmethod.Stereotype
            outputws.Cells(j, 6) = currentmethod.ParentID
            j = j + 1
        Next
End Sub

Thanks,
Rupert

[Arshad]

Hi Rupert

So in your function you're iterating method collection through index.
This can be done even in elements and packages level too.

More sample scripting you can find in scripting tab.
Manage Attributes and Methods or Recursive Dump scripts