Database

WAKTs database saves information about changes and suggestions that are made through the application. The database contains a 'Events' table and a 'Reported' view.

Events

Contains an event for every change or suggestion made in the application

Column	Datatype	Primary Key	Not NULL	Default
id	VARCHAR(25)
created_at	TIMESTAMP			CURRENT_TIMESTAMP
event_type	TINYINT(1)			'0'
lat	DOUBLE
lon	DOUBLE

Event types stated in history.js:

• corrected: 0 (marked as correct)
• reported: 1 (marked as faulty)
• added: 2 (new coordinates added)
• changed: 3 (coordinates changed)

Reported

Contains all ids of the artworks that are currently reported having faulty data

| Column | Description | | ------ | ----------- | ------ | ----------- | ------ | | id | id from Events where the latest event for that id has event type reported (1) |

Setup

To create these schemas you can use the following SQL code

CREATE TABLE Events (
  id VARCHAR(25) NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  event_type TINYINT(1) DEFAULT 0,
  lat DOUBLE DEFAULT NULL,
  lon DOUBLE DEFAULT NULL,
  PRIMARY KEY(id, created_at)
);

CREATE OR REPLACE VIEW Reported AS
  SELECT r1.id
  FROM Events r1
  LEFT OUTER JOIN Events r2
  ON (r1.id = r2.id and r1.created_at < r2.created_at)
  WHERE r2.id IS NULL AND r1.event_type = 1;