I am getting the following error when installing my app stating that the view does not match, even though expected and found are identical:
java.lang.IllegalStateException: Pre-packaged database has an invalid schema: history_view(..data.schema.entity.view.HistoryItemView).
Expected:
ViewInfo{name='history_view', sql='CREATE VIEW `history_view` AS SELECT
d.entity_id AS organisationId,
m._id AS measurementId,
m.corrected_datetime AS timestamp,
ec.ECValue AS ecValue,
ec.ECStatus AS ecStatus,
mc.MCValue AS mcValue,
mc.MCStatus AS mcStatus,
ph.pHValue AS phValue,
ph.pHStatus AS phStatus,
pm.temperature AS temperatureInCelsius,
m_nt.text_content as noteText,
tv.value AS mediaName,
tt.type AS mediaNameType,
m_nt._id AS noteId
FROM device AS d
LEFT JOIN measurement AS m on d._id = m.device_id
LEFT JOIN pulse_measurement AS pm ON pm.measurement_id = m._id
LEFT JOIN ( SELECT
rv_ec.measurement_id as ECMeasurementId,
rv_ec.value AS ECValue,
st_ec.type AS ECStatus
FROM ranged_value AS rv_ec
LEFT JOIN ranged_value_type AS rvt_ec ON rv_ec.type_id = rvt_ec._id
LEFT JOIN status_type AS st_ec ON rv_ec.status_id = st_ec._id
WHERE rvt_ec.type = 'EC' COLLATE NOCASE) AS ec ON ec.ECMeasurementId = m._id
LEFT JOIN ( SELECT
rv_mc.measurement_id as MCMeasurementId,
rv_mc.value AS MCValue,
st_mc.type AS MCStatus
FROM ranged_value AS rv_mc
LEFT JOIN ranged_value_type AS rvt_mc ON rv_mc.type_id = rvt_mc._id
LEFT JOIN status_type AS st_mc ON rv_mc.status_id = st_mc._id
WHERE rvt_mc.type = 'MC' COLLATE NOCASE) as mc ON mc.MCMeasurementId = m._id
LEFT JOIN ( SELECT
rv_ph.measurement_id as pHMeasurementId,
rv_ph.value AS pHValue,
st_ph.type AS pHStatus
FROM ranged_value AS rv_ph
LEFT JOIN ranged_value_type AS rvt_ph ON rv_ph.type_id = rvt_ph._id
LEFT JOIN status_type AS st_ph ON rv_ph.status_id = st_ph._id
WHERE rvt_ph.type = 'pH' COLLATE NOCASE) as ph ON ph.pHMeasurementId = m._id
LEFT JOIN measurement_note AS mn ON mn.measurement_id = m._id
LEFT JOIN note AS m_nt ON m_nt._id = mn.note_id
LEFT JOIN media AS med ON pm.model_global_id LIKE med.global_id
LEFT JOIN text_value AS tv ON med.name_id = tv._id
LEFT JOIN text_type AS tt on tv.text_type_id = tt._id
WHERE m.deleted = 0
UNION
SELECT
u.entity_id AS organisationId,
NULL AS measurementId,
nt.time_stamp AS timestamp,
NULL AS ecValue,
NULL AS ecStatus,
NULL AS mcValue,
NULL AS mcStatus,
NULL AS phValue,
NULL AS phStatus,
NULL AS temperatureInCelsius,
nt.text_content as noteText,
NULL AS mediaName,
NULL AS mediaNameType,
nt._id as noteId
FROM note AS nt
LEFT JOIN user AS u ON nt.user_id = u._id
WHERE nt._id NOT IN ( SELECT note_id from measurement_note ) AND nt.deleted = 0
ORDER BY timestamp DESC'}
Found:
ViewInfo{name='history_view', sql='CREATE VIEW history_view AS SELECT
d.entity_id AS organisationId,
m._id AS measurementId,
m.corrected_datetime AS timestamp,
ec.ECValue AS ecValue,
ec.ECStatus AS ecStatus,
mc.MCValue AS mcValue,
mc.MCStatus AS mcStatus,
ph.pHValue AS phValue,
ph.pHStatus AS phStatus,
pm.temperature AS temperatureInCelsius,
m_nt.text_content as noteText,
tv.value AS mediaName,
tt.type AS mediaNameType,
m_nt._id AS noteId
FROM device AS d
LEFT JOIN measurement AS m on d._id = m.device_id
LEFT JOIN pulse_measurement AS pm ON pm.measurement_id = m._id
LEFT JOIN ( SELECT
rv_ec.measurement_id as ECMeasurementId,
rv_ec.value AS ECValue,
st_ec.type AS ECStatus
FROM ranged_value AS rv_ec
LEFT JOIN ranged_value_type AS rvt_ec ON rv_ec.type_id = rvt_ec._id
LEFT JOIN status_type AS st_ec ON rv_ec.status_id = st_ec._id
WHERE rvt_ec.type = 'EC' COLLATE NOCASE) AS ec ON ec.ECMeasurementId = m._id
LEFT JOIN ( SELECT
rv_mc.measurement_id as MCMeasurementId,
rv_mc.value AS MCValue,
st_mc.type AS MCStatus
FROM ranged_value AS rv_mc
LEFT JOIN ranged_value_type AS rvt_mc ON rv_mc.type_id = rvt_mc._id
LEFT JOIN status_type AS st_mc ON rv_mc.status_id = st_mc._id
WHERE rvt_mc.type = 'MC' COLLATE NOCASE) as mc ON mc.MCMeasurementId = m._id
LEFT JOIN ( SELECT
rv_ph.measurement_id as pHMeasurementId,
rv_ph.value AS pHValue,
st_ph.type AS pHStatus
FROM ranged_value AS rv_ph
LEFT JOIN ranged_value_type AS rvt_ph ON rv_ph.type_id = rvt_ph._id
LEFT JOIN status_type AS st_ph ON rv_ph.status_id = st_ph._id
WHERE rvt_ph.type = 'pH' COLLATE NOCASE) as ph ON ph.pHMeasurementId = m._id
LEFT JOIN measurement_note AS mn ON mn.measurement_id = m._id
LEFT JOIN note AS m_nt ON m_nt._id = mn.note_id
LEFT JOIN media AS med ON pm.model_global_id LIKE med.global_id
LEFT JOIN text_value AS tv ON med.name_id = tv._id
LEFT JOIN text_type AS tt on tv.text_type_id = tt._id
WHERE m.deleted = 0
UNION
SELECT
u.entity_id AS organisationId,
NULL AS measurementId,
nt.time_stamp AS timestamp,
NULL AS ecValue,
NULL AS ecStatus,
NULL AS mcValue,
NULL AS mcStatus,
NULL AS phValue,
NULL AS phStatus,
NULL AS temperatureInCelsius,
nt.text_content as noteText,
NULL AS mediaName,
NULL AS mediaNameType,
nt._id as noteId
FROM note AS nt
LEFT JOIN user AS u ON nt.user_id = u._id
WHERE nt._id NOT IN ( SELECT note_id from measurement_note ) AND nt.deleted = 0
ORDER BY timestamp DESC'}
This is the view class:
@DatabaseView(value = """
SELECT
d.entity_id AS organisationId,
m._id AS measurementId,
m.corrected_datetime AS timestamp,
ec.ECValue AS ecValue,
ec.ECStatus AS ecStatus,
mc.MCValue AS mcValue,
mc.MCStatus AS mcStatus,
ph.pHValue AS phValue,
ph.pHStatus AS phStatus,
pm.temperature AS temperatureInCelsius,
m_nt.text_content as noteText,
tv.value AS mediaName,
tt.type AS mediaNameType,
m_nt._id AS noteId
FROM device AS d
LEFT JOIN measurement AS m on d._id = m.device_id
LEFT JOIN pulse_measurement AS pm ON pm.measurement_id = m._id
LEFT JOIN ( SELECT
rv_ec.measurement_id as ECMeasurementId,
rv_ec.value AS ECValue,
st_ec.type AS ECStatus
FROM ranged_value AS rv_ec
LEFT JOIN ranged_value_type AS rvt_ec ON rv_ec.type_id = rvt_ec._id
LEFT JOIN status_type AS st_ec ON rv_ec.status_id = st_ec._id
WHERE rvt_ec.type = 'EC' COLLATE NOCASE) AS ec ON ec.ECMeasurementId = m._id
LEFT JOIN ( SELECT
rv_mc.measurement_id as MCMeasurementId,
rv_mc.value AS MCValue,
st_mc.type AS MCStatus
FROM ranged_value AS rv_mc
LEFT JOIN ranged_value_type AS rvt_mc ON rv_mc.type_id = rvt_mc._id
LEFT JOIN status_type AS st_mc ON rv_mc.status_id = st_mc._id
WHERE rvt_mc.type = 'MC' COLLATE NOCASE) as mc ON mc.MCMeasurementId = m._id
LEFT JOIN ( SELECT
rv_ph.measurement_id as pHMeasurementId,
rv_ph.value AS pHValue,
st_ph.type AS pHStatus
FROM ranged_value AS rv_ph
LEFT JOIN ranged_value_type AS rvt_ph ON rv_ph.type_id = rvt_ph._id
LEFT JOIN status_type AS st_ph ON rv_ph.status_id = st_ph._id
WHERE rvt_ph.type = 'pH' COLLATE NOCASE) as ph ON ph.pHMeasurementId = m._id
LEFT JOIN measurement_note AS mn ON mn.measurement_id = m._id
LEFT JOIN note AS m_nt ON m_nt._id = mn.note_id
LEFT JOIN media AS med ON pm.model_global_id LIKE med.global_id
LEFT JOIN text_value AS tv ON med.name_id = tv._id
LEFT JOIN text_type AS tt on tv.text_type_id = tt._id
WHERE m.deleted = 0
UNION
SELECT
u.entity_id AS organisationId,
NULL AS measurementId,
nt.time_stamp AS timestamp,
NULL AS ecValue,
NULL AS ecStatus,
NULL AS mcValue,
NULL AS mcStatus,
NULL AS phValue,
NULL AS phStatus,
NULL AS temperatureInCelsius,
nt.text_content as noteText,
NULL AS mediaName,
NULL AS mediaNameType,
nt._id as noteId
FROM note AS nt
LEFT JOIN user AS u ON nt.user_id = u._id
WHERE nt._id NOT IN ( SELECT note_id from measurement_note ) AND nt.deleted = 0
ORDER BY timestamp DESC
""",
viewName = "history_view")
data class HistoryItemView(
@ColumnInfo(name = "organisationId", typeAffinity = ColumnInfo.INTEGER)
val organisationId: Long,
@ColumnInfo(name = "measurementId", typeAffinity = ColumnInfo.INTEGER)
val measurementId: Long?,
@ColumnInfo(name = "timestamp", typeAffinity = ColumnInfo.INTEGER)
val timestamp: Long,
@ColumnInfo(name = "ecValue", typeAffinity = ColumnInfo.REAL)
val ecValue: Float?,
@ColumnInfo(name = "ecStatus", typeAffinity = ColumnInfo.TEXT)
val ecStatus: String?,
@ColumnInfo(name = "mcValue", typeAffinity = ColumnInfo.REAL)
val mcValue: Float?,
@ColumnInfo(name = "mcStatus", typeAffinity = ColumnInfo.TEXT)
val mcStatus: String?,
@ColumnInfo(name = "phValue", typeAffinity = ColumnInfo.REAL)
val phValue: Float?,
@ColumnInfo(name = "phStatus", typeAffinity = ColumnInfo.TEXT)
val phStatus: String?,
@ColumnInfo(name = "temperatureInCelsius", typeAffinity = ColumnInfo.REAL)
val temperatureInCelsius: Double?,
@ColumnInfo(name = "noteText", typeAffinity = ColumnInfo.TEXT)
val noteText: String?,
@ColumnInfo(name = "mediaName", typeAffinity = ColumnInfo.TEXT)
val mediaName: String?,
@ColumnInfo(name = "mediaNameType", typeAffinity = ColumnInfo.TEXT)
val mediaNameType: String?,
@ColumnInfo(name = "noteId", typeAffinity = ColumnInfo.INTEGER)
val noteId: Long?
)
The problem appeared after adding the following lines: WHERE m.deleted = 0 and AND nt.deleted = 0 and ORDER BY timestamp DESC
Running the select query directly on the database returns the values as expected.
from Room schema validation fails for view in pre-packaged database on adding where clause
No comments:
Post a Comment