The order of fields in the table and unique constrains is alphabetic instead of the being the same as in class

[ComBatVision]

I have a class with unique and index annotation for fields.

The issue is that order of fields in table, unique constrains and indexes is alphabetic instead of being the same as attibutes in class.

Is it by design?

[j256]

Order where @ComBatVision ? What method on what class are you calling?

[ComBatVision]

Order of generated fields in table, in unique constrain of table and in indexes generated from annotations.

Should they keep an order from class attributes?
Becaus now they all are alphabetically sorted.

[j256]

If I run QueryBuilderTest.testInnerCountOf(), I see the following:

CREATE TABLE `foo` (`id` INTEGER AUTO_INCREMENT , `val` INTEGER , `equal` INTEGER ,
    `string` VARCHAR(255) , PRIMARY KEY (`id`) ) 
INSERT INTO `foo` (`val` ,`equal` ,`string` ) VALUES (?,?,?)' and 3 args, changed 1 rows

Etc. Notice that the create and inserts don't have sorted fields. What DB are you using? Can you show me a code example that sorts?

[ComBatVision]

I am using SQLite on Android with Kotlin classes as models.

Here is a model:

@DatabaseTable(tableName = ActualEntity.TABLE_NAME)
class ActualEntity : Serializable {
    @DatabaseField(dataType = DataType.LONG, columnName = ID, generatedId = true)
    var id: Long = 0
    @DatabaseField(dataType = DataType.SERIALIZABLE, columnName = USER_SESSION, columnDefinition = LocalUserSession.DELETE_CASCADE, foreign = true, uniqueCombo = true)
    lateinit var userSession: LocalUserSession
    @DatabaseField(dataType = DataType.STRING, columnName = URI, uniqueCombo = true)
    lateinit var uri: String
    @DatabaseField(dataType = DataType.DATE_LONG, columnName = START_DATE)
    lateinit var startDate: Date
    @DatabaseField(dataType = DataType.DATE_LONG, columnName = END_DATE)
    var endDate: Date? = null
    @DatabaseField(dataType = DataType.DATE_LONG, columnName = UPDATE_DATE)
    lateinit var updateDate: Date
    @DatabaseField(dataType = DataType.INTEGER, columnName = CHECKSUM)
    var checksum = 0
    @DatabaseField(dataType = DataType.STRING, columnName = CONTENT_TYPE)
    var contentType: String? = null
    @DatabaseField(dataType = DataType.BYTE_ARRAY, columnName = INITIAL_VECTOR)
    var initialVector: ByteArray? = null
    @DatabaseField(dataType = DataType.BYTE_ARRAY, columnName = BINARY_CONTENT)
    lateinit var binaryContent: ByteArray
    @DatabaseField(dataType = DataType.SERIALIZABLE, columnName = ORIGINAL, columnDefinition = HistoricalEntity.DELETE_SET_NULL, foreign = true)
    var original: HistoricalEntity? = null
    @DatabaseField(dataType = DataType.BOOLEAN, columnName = IS_ON_GATE)
    var isOnGate = false
    @DatabaseField(dataType = DataType.ENUM_INTEGER, columnName = ASSOCIATION_STATE)
    var associationState = AssociationState.NOT_RELEVANT
    @DatabaseField(dataType = DataType.BOOLEAN, columnName = IS_PROTECTED)
    var isProtected = false

    companion object {
        const val TABLE_NAME = "ACTUAL_ENTITY"
        const val ID = "ID"
        const val USER_SESSION = "USER_SESSION"
        const val URI = "URI"
        const val START_DATE = "START_DATE"
        const val END_DATE = "END_DATE"
        const val UPDATE_DATE = "UPDATE_DATE"
        const val CONTENT_TYPE = "CONTENT_TYPE"
        const val INITIAL_VECTOR = "INITIAL_VECTOR"
        const val BINARY_CONTENT = "BINARY_CONTENT"
        const val CHECKSUM = "CHECKSUM"
        const val ORIGINAL = "ORIGINAL"
        const val IS_ON_GATE = "IS_ON_GATE"
        const val ASSOCIATION_STATE = "ASSOCIATION_STATE"
        const val IS_PROTECTED = "IS_PROTECTED"
        const val DELETE_CASCADE = "BIGINT REFERENCES $TABLE_NAME($ID) ON DELETE CASCADE"
    }
}

Here is a create statement from this model:

CREATE TABLE IF NOT EXISTS `ACTUAL_ENTITY_NEW` (
                    `ASSOCIATION_STATE` INTEGER,
                    `BINARY_CONTENT` BLOB,
                    `CHECKSUM` INTEGER,
                    `CONTENT_TYPE` VARCHAR,
                    `END_DATE` BIGINT,
                    `ID` INTEGER PRIMARY KEY AUTOINCREMENT,
                    `INITIAL_VECTOR` BLOB,
                    `IS_ON_GATE` SMALLINT,
                    `IS_PROTECTED` SMALLINT,
                    `ORIGINAL` BIGINT REFERENCES HISTORICAL_ENTITY(ID) ON DELETE SET NULL,
                    `START_DATE` BIGINT,
                    `UPDATE_DATE` BIGINT,
                    `URI` VARCHAR ,
                    `USER_SESSION` BIGINT REFERENCES LOCAL_USER_SESSION(ID) ON DELETE CASCADE,
                    UNIQUE (`URI`,`USER_SESSION`)
                )

My investigation shows me that class.getDeclaredFields return them in aplhabetic order.

Is it a bug or feature? Is it possible to add special anotation to control order of fields?

[ComBatVision]

If I delcare any indexes via field annotations they will also in alphabetic oeder like UNIQUE constrain above: UNIQUE (URI,USER_SESSION)

[j256]

Not according to the javadocs: https://docs.oracle.com/javase/8/docs/api/java/lang/Class.html#getDeclaredFields--

The elements in the returned array are not sorted and are not in any particular order." That was my OSX Java 8. Same for openjdk 11.

What JVM are you using?

[ComBatVision]

I am using Kotlin on Android API 29-34. All of them give me a sorted result.

[j256]

Not sure what to do. You can configure your classes using code. See here: https://ormlite.com/docs/config-code

[j256]

You might also want to look at the table-config file that was an early Android hack when the annotation parsing was dog slow: https://ormlite.com/docs/table-config

[j256]

Lastly, I'm curious why the sorted order is getting in your way? What part of the system doesn't work if the fields are sorted by the JVM?

[ComBatVision]

Can it be a specific of the Java reflection on Kotlin?

Am I right that your code generates the fields in the order returned by reflection, and you do not sort them later?

[ComBatVision]

https://stackoverflow.com/questions/69932555/kotlin-get-members-of-a-data-class-by-reflection-in-the-order-they-have-been-de

May be it is a good idea to add order annotation attribute and sort fields by order if it is specified for Kotlin?

[j256]

Yes, ormlite does nothing to the field order. You posted the code above. No Collections.sort().

[j256]

Should the order field in @DatabaseFIeld be a integer number or would it be String afterColumn?

[ComBatVision]

I quickly read some articles, and all of them state that the order of attributes in reflection is not guaranteed and can vary on different Java.

So the best way will be to have an order attribute in annotations. Are there any other ideas, except configuriong tables manually?

Now, I have performance problems because I found that all my indexes are not in the expected order in production :(

[ComBatVision]

I think it is enough to be integer, like in ServiceStack OrmLite https://stackoverflow.com/questions/74895170/columns-physical-order-in-ormlite

Are there any particular use cases for string ordering?

[j256]

Funny example! My "brand" nemesis.

I just thought that if you were creating the class and adding fields you would be always running around changing the numbers. Adding a 2nd field in a class with 30 fields would be a pain. That's what I did with my SimpleCSV CsvColumn annotation . Here's another afterColumn reference: https://liquibase.jira.com/browse/CORE-1745

It's interesting that in their example they have just one with with Order = 4. I guess the first 3 fields would default to 0 or something and their order would not be guaranteed?

[ComBatVision]

I do not know how it behaves in their case.
For my purposes, both variants will work.

[ComBatVision]

Hi. @j256 Any news on this issue? Will you add an order to anotation fro Kotlin?
I prefer numeric order, but any kind of sorting criteria will help.
Thanks.

[j256]

Sorry for the delay. I had a couple of other changes queued up that I have to clear. I may back some stuff out. Hope to get out a new release this week.

[ComBatVision]

Hi, @j256 could you add field ordering, please. It is very important for our project.

[ComBatVision]

Hi. Is there any updates on this issue?

[ComBatVision]

Hi, @j256. Do you plan to add this ordering in the nearest future?

[j256]

I've actually already added this to main but I'm having problems with a subtle change in H2/MySQL around creating new entities with generated ids that I need a larger amount of time to solve.

[ComBatVision]

Hi, @j256.
Any estimates when you release an update?