MIGXdb schema.xml updated - new database field not indexed as expected?

I added a new field to a schema.xml file for MIGXdb. Everything works as expected and there are no errors during parsing the schema or in the MODX Error Log. However the new field displays differently when using HeidiSQL to view the database.

The screenshot shows the green key icon is missing from package field. The green key indicates normal indexing.

The full schema is below and the sections I added are in the VenueExperience and VenuePackage classes with each commented by packageindexissue: for ease of finding them.

Can anyone advise if there is an error in the changes I made to the schema or if it actually matters that HeidiSQL does not show the green key icon?

Thanks.

<?xml version="1.0" encoding="UTF-8"?>

<!--

    - model/object hierarchy
        - Venue has Events
        - Events have Experiences
        - Experiences have Packages
        - Packages have Components

-->

<model package="venue" baseClass="xPDOObject" platform="mysql" defaultEngine="INNODB" version="1.1">
    <object class="VenueFamily" table="venue__family" extends="xPDOSimpleObject">
        <field key="name" dbtype="varchar" precision="100" phptype="string" null="false" default="" />

        <!-- used by MIGX -->        
        <field key="deleted" dbtype="tinyint" precision="1" attributes="unsigned" phptype="integer" null="false" default="0" />
        <field key="published" dbtype="tinyint" precision="1" attributes="unsigned" phptype="integer" null="false" default="0" />
        <field key="pos" dbtype="int" precision="10" phptype="integer" null="false" default="0" />

        <!-- good practice for tracking -->       
        <field key="createdon" dbtype="datetime" phptype="datetime" null="true"/>
        <field key="createdby" dbtype="int" precision="10" attributes="unsigned" phptype="integer" null="false" default="0" />
        <field key="editedon" dbtype="datetime" phptype="datetime" null="true"/>
        <field key="editedby" dbtype="int" precision="10" attributes="unsigned" phptype="integer" null="false" default="0" />
        
        <!-- track createdby and editedby -->
        <aggregate alias="CreatedBy" class="modUser" local="createdby" foreign="id" cardinality="one" owner="foreign" />
        <aggregate alias="EditedBy" class="modUser" local="editedby" foreign="id" cardinality="one" owner="foreign" />

        <composite alias="Parents" class="VenueParent" local="id" foreign="family" cardinality="many" owner="local" />
        <composite alias="Experiences" class="VenueExperience" local="id" foreign="family" cardinality="many" owner="local" />
        <composite alias="Events" class="VenueEvent" local="id" foreign="family" cardinality="many" owner="local" />
    </object>

    <object class="VenueParent" table="venue__parent" extends="xPDOSimpleObject">
        <field key="family" dbtype="int" precision="10" attributes="unsigned" phptype="integer" null="false" default="0" />
        <field key="name" dbtype="varchar" precision="100" phptype="string" null="false" default="" />

        <!-- used by MIGX -->        
        <field key="deleted" dbtype="tinyint" precision="1" attributes="unsigned" phptype="integer" null="false" default="0" />
        <field key="published" dbtype="tinyint" precision="1" attributes="unsigned" phptype="integer" null="false" default="0" />
        <field key="pos" dbtype="int" precision="10" phptype="integer" null="false" default="0" />

        <!-- good practice for tracking -->       
        <field key="createdon" dbtype="datetime" phptype="datetime" null="true"/>
        <field key="createdby" dbtype="int" precision="10" attributes="unsigned" phptype="integer" null="false" default="0" />
        <field key="editedon" dbtype="datetime" phptype="datetime" null="true"/>
        <field key="editedby" dbtype="int" precision="10" attributes="unsigned" phptype="integer" null="false" default="0" />


        <index alias="family" name="family" primary="false" unique="false" type="BTREE">
            <column key="family" length="" collation="A" null="false" />
        </index>
        <index alias="name" name="name" primary="false" unique="false" type="BTREE">
            <column key="name" length="" collation="A" null="false" />
        </index>
        
        <!-- track createdby and editedby -->
        <aggregate alias="CreatedBy" class="modUser" local="createdby" foreign="id" cardinality="one" owner="foreign"/>
        <aggregate alias="EditedBy" class="modUser" local="editedby" foreign="id" cardinality="one" owner="foreign"/>

        <aggregate alias="Family" class="VenueFamily" local="family" foreign="id" cardinality="one" owner="foreign"/>
        <composite alias="Event" class="VenueEvent" local="id" foreign="parent" cardinality="many" owner="local" />
    </object>


    <object class="VenueExperience" table="venue__experience" extends="xPDOSimpleObject">
        <field key="name" dbtype="varchar" precision="100" phptype="string" null="false" default="" />
        <field key="family" dbtype="int" precision="10" attributes="unsigned" phptype="integer" null="false" default="0" />

        <!-- packageindexissue: added package field -->
        <field key="package" dbtype="int" precision="10" attributes="unsigned" phptype="integer" null="false" default="0" />

        <!-- used by MIGX -->        
        <field key="deleted" dbtype="tinyint" precision="1" attributes="unsigned" phptype="integer" null="false" default="0" />
        <field key="published" dbtype="tinyint" precision="1" attributes="unsigned" phptype="integer" null="false" default="0" />
        <field key="pos" dbtype="int" precision="10" phptype="integer" null="false" default="0" />

        <!-- good practice for tracking -->       
        <field key="createdon" dbtype="datetime" phptype="datetime" null="true"/>
        <field key="createdby" dbtype="int" precision="10" attributes="unsigned" phptype="integer" null="false" default="0" />
        <field key="editedon" dbtype="datetime" phptype="datetime" null="true"/>
        <field key="editedby" dbtype="int" precision="10" attributes="unsigned" phptype="integer" null="false" default="0" />
        
        <index alias="family" name="family" primary="false" unique="false" type="BTREE">
            <column key="family" length="" collation="A" null="false" />
        </index>

        <!-- packageindexissue: added package index -->
        <index alias="package" name="package" primary="false" unique="false" type="BTREE">
            <column key="package" length="" collation="A" null="false" />
        </index>
        <index alias="name" name="name" primary="false" unique="false" type="BTREE">
            <column key="name" length="" collation="A" null="false" />
        </index>
        <index alias="deleted" name="deleted" primary="false" unique="false" type="BTREE">
            <column key="deleted" length="" collation="A" null="false" />
        </index>
        <index alias="published" name="published" primary="false" unique="false" type="BTREE">
            <column key="published" length="" collation="A" null="false" />
        </index>
        <index alias="pos" name="pos" primary="false" unique="false" type="BTREE">
            <column key="pos" length="" collation="A" null="false" />
        </index>

        <!-- track createdby and editedby -->
        <aggregate alias="CreatedBy" class="modUser" local="createdby" foreign="id" cardinality="one" owner="foreign"/>
        <aggregate alias="EditedBy" class="modUser" local="editedby" foreign="id" cardinality="one" owner="foreign"/>

        <aggregate alias="Family" class="VenueFamily" local="family" foreign="id" cardinality="one" owner="foreign"/>

        <!-- packageindexissue: added Package alias -->
        <aggregate alias="Package" class="VenuePackage" local="package" foreign="id" cardinality="one" owner="foreign"/>
        <composite alias="EventExperiences" class="VenueEventExperience" local="id" foreign="experience" cardinality="many" owner="local"/>
    </object>



    <object class="VenueEvent" table="venue__event" extends="xPDOSimpleObject">
        <field key="parent" dbtype="int" precision="10" attributes="unsigned" phptype="integer" null="false" default="0" />
        <field key="family" dbtype="int" precision="10" attributes="unsigned" phptype="integer" null="false" default="0" />

        <field key="title"                 dbtype="varchar" precision="150"  phptype="string"   null="false" default="" />
        <field key="date"                  dbtype="datetime"                 phptype="datetime" null="true"  default="1000-01-01 00:00:00" />
        <field key="host"                  dbtype="varchar" precision="150"  phptype="string"   null="false" default="" />
        <field key="host_img"              dbtype="varchar" precision="255"  phptype="string"   null="false" default="" />
        <field key="guest"                 dbtype="varchar" precision="150"  phptype="string"   null="false" default="" />
        <field key="guest_img"             dbtype="varchar" precision="255"  phptype="string"   null="false" default="" />
        <field key="general_admission_url" dbtype="varchar" precision="255"  phptype="string"   null="false" default="" />

        <!-- used by MIGX -->        
        <field key="deleted" dbtype="tinyint" precision="1" attributes="unsigned" phptype="integer" null="false" default="0" />
        <field key="published" dbtype="tinyint" precision="1" attributes="unsigned" phptype="integer" null="false" default="0" />
        <field key="pos" dbtype="int" precision="10" phptype="integer" null="false" default="0" />

        <!-- good practice for tracking -->       
        <field key="createdon" dbtype="datetime" phptype="datetime" null="true"/>
        <field key="createdby" dbtype="int" precision="10" attributes="unsigned" phptype="integer" null="false" default="0" />
        <field key="editedon" dbtype="datetime" phptype="datetime" null="true"/>
        <field key="editedby" dbtype="int" precision="10" attributes="unsigned" phptype="integer" null="false" default="0" />
        
        <index alias="parent" name="parent" primary="false" unique="false" type="BTREE">
            <column key="parent" length="" collation="A" null="false" />
        </index>
        <index alias="family" name="family" primary="false" unique="false" type="BTREE">
            <column key="family" length="" collation="A" null="false" />
        </index>
        <index alias="title" name="title" primary="false" unique="false" type="BTREE">
            <column key="title" length="" collation="A" null="false" />
        </index>
        <index alias="date" name="date" primary="false" unique="false" type="BTREE">
            <column key="date" length="" collation="A" null="false" />
        </index>

        <!-- track createdby and editedby -->
        <aggregate alias="CreatedBy" class="modUser" local="createdby" foreign="id" cardinality="one" owner="foreign"/>
        <aggregate alias="EditedBy" class="modUser" local="editedby" foreign="id" cardinality="one" owner="foreign"/>

        <aggregate alias="Family" class="VenueFamily" local="family" foreign="id" cardinality="one" owner="foreign"/>
        <aggregate alias="Parent" class="VenueParent" local="parent" foreign="id" cardinality="one" owner="foreign"/>

        <composite alias="Experiences" class="VenueEventExperience" local="id" foreign="event" cardinality="many" owner="local"/>
    </object>


    <object class="VenueEventExperience" table="venue__event_experience" extends="xPDOSimpleObject">
        <field key="event" dbtype="int" precision="10" attributes="unsigned" phptype="integer" null="false" default="0" />
        <field key="experience" dbtype="int" precision="10" attributes="unsigned" phptype="integer" null="false" default="0" />
        <field key="package" dbtype="int" precision="10" attributes="unsigned" phptype="integer" null="false" default="0" />

        <field key="title" dbtype="varchar" precision="150" phptype="string" null="false" default="" index="index" />
        <field key="introtext" dbtype="varchar" precision="150" phptype="string" null="false" default="" index="index" />
        <field key="content" dbtype="varchar" precision="150" phptype="string" null="false" default="" index="index" />
        <field key="superhero_video" dbtype="varchar" precision="150" phptype="string" null="false" default="" index="index" />
        <field key="superhero_img" dbtype="varchar" precision="150" phptype="string" null="false" default="" index="index" />
        <field key="superhero_img_alt" dbtype="varchar" precision="150" phptype="string" null="false" default="" index="index" />
        

        <field key="price" dbtype="varchar" precision="255" phptype="string" null="false" default="" />
        <field key="booking_url" dbtype="varchar" precision="150" phptype="string" null="false" default="" />
        <field key="status" dbtype="varchar" precision="255" phptype="string" null="false" default="" />
        <field key="data_capture" dbtype="varchar" precision="255" phptype="string" null="false" default="" />

        <index alias="event" name="event" primary="false" unique="false" type="BTREE">
            <column key="event" length="" collation="A" null="false" />
        </index>
        <index alias="experience" name="experience" primary="false" unique="false" type="BTREE">
            <column key="experience" length="" collation="A" null="false" />
        </index>
        <index alias="package" name="package" primary="false" unique="false" type="BTREE">
            <column key="package" length="" collation="A" null="false" />
        </index>
        <index alias="title" name="title" primary="false" unique="false" type="BTREE">
            <column key="title" length="" collation="A" null="false" />
        </index>

        <aggregate alias="Event" class="VenueEvent" local="event" foreign="id" cardinality="one" owner="foreign"/>
        <aggregate alias="Experience" class="VenueExperience" local="experience" foreign="id" cardinality="one" owner="foreign"/>
        <aggregate alias="Package" class="VenuePackage" local="package" foreign="id" cardinality="one" owner="foreign"/>
        <aggregate alias="EventExperienceComponents" class="VenueEventExperienceComponent" local="id" foreign="eventexperience" cardinality="many" owner="local"/>
    </object>


    <object class="VenueEventExperienceComponent" table="venue__event_experience_component" extends="xPDOSimpleObject">
        <field key="eventexperience" dbtype="int" precision="10" attributes="unsigned" phptype="integer" null="false" default="0" />
        <field key="component" dbtype="int" precision="10" attributes="unsigned" phptype="integer" null="true" default="0" />

        <field key="title" dbtype="varchar" precision="150" phptype="string" null="false" default="" />
        <field key="introtext" dbtype="varchar" precision="255" phptype="string" null="false" default="" />
        <field key="icon" dbtype="varchar" precision="150" phptype="string" null="false" default="" />
        <field key="icon_hex" dbtype="varchar" precision="6" phptype="string" null="false" default="" />


        <index alias="eventexperience" name="eventexperience" primary="false" unique="false" type="BTREE">
            <column key="eventexperience" length="" collation="A" null="false" />
        </index>
        <index alias="component" name="component" primary="false" unique="false" type="BTREE">
            <column key="component" length="" collation="A" null="false" />
        </index>

        <aggregate alias="EventExperience" class="VenueEventExperience" local="eventexperience" foreign="id" cardinality="one" owner="foreign"/>
        <aggregate alias="Component" class="VenueComponent" local="component" foreign="id" cardinality="one" owner="foreign"/>
    </object>

    <object class="VenuePackage" table="venue__packages" extends="xPDOSimpleObject" >
        <field key="title" dbtype="varchar" precision="150" phptype="string" null="false" default="" index="index" />

        <!-- used by MIGX -->        
        <field key="deleted" dbtype="tinyint" precision="1" attributes="unsigned" phptype="integer" null="false" default="0" />
        <field key="published" dbtype="tinyint" precision="1" attributes="unsigned" phptype="integer" null="false" default="0" />
        <field key="pos" dbtype="int" precision="10" phptype="integer" null="false" default="0" />

        <!-- good practice for tracking -->       
        <field key="createdon" dbtype="datetime" phptype="datetime" null="true"/>
        <field key="createdby" dbtype="int" precision="10" attributes="unsigned" phptype="integer" null="false" default="0" />
        <field key="editedon" dbtype="datetime" phptype="datetime" null="true"/>
        <field key="editedby" dbtype="int" precision="10" attributes="unsigned" phptype="integer" null="false" default="0" />
        

        <index alias="title" name="title" primary="false" unique="false" type="BTREE">
            <column key="title" length="" collation="A" null="false" />
        </index>

        <!-- track createdby and editedby -->
        <aggregate alias="CreatedBy" class="modUser" local="createdby" foreign="id" cardinality="one" owner="foreign"/>
        <aggregate alias="EditedBy" class="modUser" local="editedby" foreign="id" cardinality="one" owner="foreign"/>

        <!-- packageindexissue: added Package alias -->
        <composite alias="Package" class="VenueExperience" local="id" foreign="package" cardinality="one" owner="local" />
        <aggregate alias="EventExperiences" class="VenueEventExperience" local="id" foreign="package" cardinality="many" owner="local" />
        <composite alias="Components" class="VenueComponent" local="id" foreign="package" cardinality="many" owner="local" />
    </object>

    <object class="VenueComponent" table="venue__component" extends="xPDOSimpleObject" >
        <field key="package" dbtype="int" precision="10" attributes="unsigned" phptype="integer" null="false" default="0" />
    
        <field key="title" dbtype="varchar" precision="150" phptype="string" null="false" default="" />
        <field key="introtext" dbtype="varchar" precision="255" phptype="string" null="false" default="" />
        <field key="icon" dbtype="varchar" precision="150" phptype="string" null="false" default="" />
        <field key="icon_hex" dbtype="varchar" precision="6" phptype="string" null="false" default="" />


        <index alias="package" name="package" primary="false" unique="false" type="BTREE">
            <column key="package" length="" collation="A" null="false" />
        </index>
        <index alias="title" name="title" primary="false" unique="false" type="BTREE">
            <column key="title" length="" collation="A" null="false" />
        </index>

        <aggregate alias="Package" class="VenuePackage" local="package" foreign="id" cardinality="many" owner="foreign"/>
    </object>
</model>

I believe I had the same problem once (with the index being ignored) when updating a table.

Maybe try deleting the whole database table first and then recreate it from the new schema.
If you can’t delete the table (because it already contains data), then just add the index manually.

This resolved the issue.

Thanks again.

ClassExtender has the same issue. MODX doesn’t actually use the schemas for anything but creating the DB table and the class and map files. Once that’s done, the schema is ignored.

The fields are put in place when the table is created, but if you need to update them, you have to regenerate the class and map files, and then either delete the DB table, alter it manually, or use a custom query to update it.

MODX (xPDO, actualaly), has the createObjectContainer() method to create a table based on a schema, but AFAIK, there’s no equivalent method for updating the table from a modified schema.

Just to be sure I’m understanding correctly, after adding the new field with index and aliases as indicated in the schema from the first post, is it enough to

  • delete the existing table to which the new field was added in the schema
  • save the schema in MIGX Package Manager
  • select Parse Schema in MIGX Package Manager?

MIGX notes the Parse Schema action will

Create xpdo-classes and maps if new or manipulate existing maps from schema with Parse Schema

Or does my process mean the class and map files (generated by MIGX from the original schema) are now incorrect (although on the surface appear to be working)?

I don’t know enough about MIGX to answer that. That would be true of ClassExtender, but if the new fields appear to be working (especially if the new field is being filled in the DB), MIGX may be smarter about this than ClassExtender.

This is not conclusive but checking the modified timestamp on the Package Model files shows all the

\core\components\venue\model\venue\mysql\*.map.inc.php files were updated today but the *.class.php files have their original creation date.

So it looks like MIGX does indeed update them when the schema is parsed.

Will keep this in mind if any bugs present in the next few days while working further on this.

Thanks for reporting back. I’ll have to look at the MIGX code to see if I can do the same for ClassExtender. Though viewing the ClassExtender resource again would update the class files and it sounds like MIGX did not update the DB table.