Hey,
While working on the cross-product Advanced Auditing feature, I found myself needing to get the most recent row.
DISCLAIMER
I really would recommend using QueryDSL pocketknife instead if you don’t need to support Bitbucket/Stash, it gives you far more control and fewer headaches. I had to mis-use Active Objects in this way because BB only supports AO (if you try to get the data source BB will throw an exception instead of fulfilling the API).
I make NO guarantees this will continue to work. This also comes with the risk of data destruction (as DB work generally does).
Step 0: Example of existing table
Our existing audit event table looked something like this:
@Table(AoAuditEntity.TABLE_NAME)
@Preload
@Indexes({
@Index(name = "timestampAndId", methodNames = {"getTimestamp", "getId"}),
@Index(name = "action", methodNames = {"getAction"}),
})
public interface AoAuditEntity extends RawEntity<Integer> {
String TABLE_NAME = "AUDIT_ENTITY";
String ACTION_COLUMN = "ACTION";
String ACTION_TKEY_COLUMN = "ACTION_T_KEY";
String LEVEL_COLUMN = "LEVEL";
String AREA_COLUMN = "AREA";
String TIMESTAMP_COLUMN = "ENTITY_TIMESTAMP";
String ID_COLUMN = "ID";
// ... etc.
@AutoIncrement
@NotNull
@PrimaryKey(ID_COLUMN)
Long getId();
@NotNull
@Accessor(ACTION_COLUMN)
String getAction();
@Accessor(ACTION_TKEY_COLUMN)
String getActionI18nKey();
@NotNull
@Accessor(LEVEL_COLUMN)
String getLevel();
@NotNull
@Accessor(AREA_COLUMN)
String getArea();
// ........ etc.
}
This would typically get used with some code like:
ao.find(AoAuditEntity.class, net.java.ao.Query.select().limit(100));
By default, this would preload everything and thus regardless of what we write in the select query, Active Objects would add in every column so it can be loaded.
Step 1: Loading fewer columns
By creating another entity with less columns being preloaded, we can query against a subset of columns.
Note; if we don’t want to be forced to load the primary key, we can set a different column to be it.
@Table(AoAuditEntity.TABLE_NAME)
@Preload({ACTION_COLUMN, ACTION_TKEY_COLUMN})
public interface AoAuditEntityAction extends RawEntity<String> {
String ID_COLUMN = AoAuditEntity.ID_COLUMN;
String ACTION_COLUMN = AoAuditEntity.ACTION_COLUMN;
String ACTION_TKEY_COLUMN = AoAuditEntity.ACTION_TKEY_COLUMN;
@Accessor(ID_COLUMN)
Long getId();
@NotNull
@Accessor(ACTION_COLUMN)
@PrimaryKey(ACTION_COLUMN)
String getAction();
@Accessor(ACTION_TKEY_COLUMN)
String getActionI18nKey();
}
We would be able to query just the ACTION column by doing:
ao.find(AoAuditEntityAction.class, net.java.ao.Query.select().limit(100));
// or
ao.find(AoAuditEntityAction.class, net.java.ao.Query.select(AoAuditEntityAction.ACTION_COLUMN).limit(100));
Note; this only works if the entities are declared in the same ao module description, or the descriptions they belong to have the same namespace, or if no namespace then the plugin key is the same as the namespace.
An example of being in the same description would be:
<ao key="atlassian-audit-ao" name="Active Objects Module" namespace="com.example.namespace" i18n-name-key="example.i18n.key">
<entity>com.atlassian.audit.ao.dao.entity.AoAuditEntity</entity>
<entity>com.atlassian.audit.ao.dao.entity.AoCachedActionEntity</entity>
</ao>
Step 2: Using the max function
By renaming the column after using the function to something that AO expects, it doesn’t throw an exception because it can find the “field” (read: column) information.
An example query that would use and then rename the result of the MAX function.
ao.find(AoAuditEntityAction.class,
select(format("%s, %s, max(%s) as %s",
AoAuditEntityAction.ACTION_COLUMN,
AoAuditEntityAction.ACTION_TKEY_COLUMN,
AoAuditEntityAction.ID_COLUMN,
AoAuditEntityAction.ID_COLUMN
))
.group(format("%s, %s",
AoAuditEntityAction.ACTION_COLUMN,
AoAuditEntityAction.ACTION_TKEY_COLUMN)))
If you’d love to see QueryDSL be usable with Bitbucket, or if you’d like to see further development with AO, or any other way to use the DB, we’d love to hear your feedback!
If you have any other frustrations with AO or other ideas, we’d love to hear that too!
If you’re new to AO, or would just like to poke around some documentation to brush up, give these a try:
- https://developer.atlassian.com/server/framework/atlassian-sdk/developing-your-plugin-with-active-objects/
- https://developer.atlassian.com/server/framework/atlassian-sdk/active-objects/
- activeobjects 4.0.5 javadoc (net.java.dev.activeobjects)
Hope that helps!
Michael