This PR enhances our support for primary keys. It adds a lot of new features and configurability options to table models with regards to how we handle rowid bookkeeping and primary keys. For backwards compatibility reasons, a few known limitations remain, but these can be removed in the next major version of SquiDB.
These changes were inspired by the discussion in issue #188.
Background
Up until now, SquiDB has always generated an ID property by default for every table model. This property is declared as an INTEGER PRIMARY KEY, which in other words makes it an alias for the SQLite rowid column that all tables have (see this SQLite doc). This PR is a step towards decoupling the idea of rowid (which we need for SquiDB bookkeeping) from the idea of primary key.
Changes
The @PrimaryKey annotation can now annotate any field, not just long fields.
If the annotated field is an integer type, that makes the generated property an INTEGER PRIMARY KEY. This field will replace the default ID field and be used for bookkeeping. This is basically how `@PrimaryKey works now.
If the annotated field is some other type, that means the user has not declared an INTEGER PRIMARY KEY. In this case, SquiDB will generate a ROWID property explicitly referencing the rowid column, and this column will be used for bookkeeping purposes. It will be part of the PROPERTIES array for the model, but not explicitly created in the table (SQLite creates the column implicitly for all tables).
The @TableModelSpec annotation has a new option, boolean noRowIdAlias(). If this flag is set, SquiDB will not generate the ID property as an INTEGER PRIMARY KEY alias to the rowid, but instead generate a ROWID property explicitly referencing the rowid column. This property will be used for bookkeeping instead of ID. Users who wish not to declare an INTEGER PRIMARY KEY should use this option.
Note: this should probably be the default behavior in the next major version of SquiDB, at which time this flag will become unnecessary and be removed
Users who desire a multi-column primary key can declare the key using the tableConstraints() field in @TableModelSpec. Note that this also requires enabling the noRowIdAlias() flag, as SQLite only allows a single PRIMARY KEY declaration when creating the table.
If none of the above apply, SquiDB will continue to generate the ID property as an INTEGER PRIMARY KEY alias to rowid. However, it will also log a warning since users will need to explicitly declare these rowid aliases in a future major version of SquiDB. If they update their model specs to explicitly declare these aliases now, this warning will go away and things will work as they always have. Updating existing model specs to remove the warning would look like this:
@TableModelSpec(...)
class MyModel {
// This replaces the legacy ID property that was generated by default
// with one that is semantically identical
@PrimaryKey
@ColumnSpec(name = "_id")
long id;
}
The existing getId() and setId() methods in TableModel have been deprecated and renamed to getRowId() and setRowId() to more accurately reflect their intended purpose.
Caveats
In order to maintain backwards compatibility, user-defined properties cannot be named ID, as their getter/setter would conflict with the deprecated TableModel getId() and setId().
This restriction does not apply if the field is an INTEGER PRIMARY KEY, as it then replaces the default ID property.
This restriction will be removed in a future version of SquiDB.
User-defined columns named rowid are not permitted.
SquiDB does not support WITHOUT ROWID tables. (This has always been the case, but is worth mentioning explicitly).
Users who wish to migrate existing tables in order to define some other primary key or remove the default rowid alias will have to run some kind of migration to drop the _id column. Dropping columns in SQLite is a pain, but I don't think there's much else to be done about this.
This PR enhances our support for primary keys. It adds a lot of new features and configurability options to table models with regards to how we handle rowid bookkeeping and primary keys. For backwards compatibility reasons, a few known limitations remain, but these can be removed in the next major version of SquiDB.
These changes were inspired by the discussion in issue #188.
Background
Up until now, SquiDB has always generated an
ID
property by default for every table model. This property is declared as anINTEGER PRIMARY KEY
, which in other words makes it an alias for the SQLite rowid column that all tables have (see this SQLite doc). This PR is a step towards decoupling the idea of rowid (which we need for SquiDB bookkeeping) from the idea of primary key.Changes
@PrimaryKey
annotation can now annotate any field, not justlong
fields.INTEGER PRIMARY KEY
. This field will replace the defaultID
field and be used for bookkeeping. This is basically how `@PrimaryKey works now.INTEGER PRIMARY KEY
. In this case, SquiDB will generate aROWID
property explicitly referencing therowid
column, and this column will be used for bookkeeping purposes. It will be part of thePROPERTIES
array for the model, but not explicitly created in the table (SQLite creates the column implicitly for all tables).@TableModelSpec
annotation has a new option,boolean noRowIdAlias()
. If this flag is set, SquiDB will not generate theID
property as anINTEGER PRIMARY KEY
alias to the rowid, but instead generate aROWID
property explicitly referencing the rowid column. This property will be used for bookkeeping instead ofID
. Users who wish not to declare anINTEGER PRIMARY KEY
should use this option.tableConstraints()
field in@TableModelSpec
. Note that this also requires enabling thenoRowIdAlias()
flag, as SQLite only allows a single PRIMARY KEY declaration when creating the table.ID
property as anINTEGER PRIMARY KEY
alias to rowid. However, it will also log a warning since users will need to explicitly declare these rowid aliases in a future major version of SquiDB. If they update their model specs to explicitly declare these aliases now, this warning will go away and things will work as they always have. Updating existing model specs to remove the warning would look like this:getId()
andsetId()
methods inTableModel
have been deprecated and renamed togetRowId()
andsetRowId()
to more accurately reflect their intended purpose.Caveats
ID
, as their getter/setter would conflict with the deprecated TableModelgetId()
andsetId()
.INTEGER PRIMARY KEY
, as it then replaces the defaultID
property.rowid
are not permitted.WITHOUT ROWID
tables. (This has always been the case, but is worth mentioning explicitly)._id
column. Dropping columns in SQLite is a pain, but I don't think there's much else to be done about this.