stress_at_work_analysis/config/models.py

536 lines
17 KiB
Python
Raw Permalink Normal View History

2020-12-24 16:46:55 +01:00
from datetime import datetime
2020-12-31 14:33:44 +01:00
from sqlalchemy import (
TIMESTAMP,
BigInteger,
Boolean,
Column,
Float,
ForeignKey,
Integer,
SmallInteger,
String,
UniqueConstraint,
)
2020-12-31 14:33:44 +01:00
from sqlalchemy.dialects.postgresql import ARRAY as PSQL_ARRAY
from sqlalchemy.dialects.postgresql import INTEGER as PSQL_INTEGER
from sqlalchemy.dialects.postgresql import JSONB as PSQL_JSONB
from sqlalchemy.dialects.postgresql import TEXT as PSQL_TEXT
2020-12-24 16:46:55 +01:00
from sqlalchemy.ext.declarative import declarative_base, declared_attr
from sqlalchemy.orm import relationship
Base = declarative_base()
class Participant(Base):
2021-04-06 12:58:32 +02:00
"""
A participant class, describing the participants table.
Attributes
----------
id: int
The primary key for this table.
username: str(64)
The primary pseudoanonymous identifier for each participants.
For true participants, these were assigned by E4 Connect as usernames for Empatica Studies
and had the form of uploader_0123456.
password_hash: str(128)
token: str(32)
Authentication is implemented using HTTP Basic Auth and tokens are provided for this purpose.
token_expiration_utc: datetime
Tokens expire after a preset time period.
collection_start_utc: datetime
A datetime of the first data point uploaded.
last_upload_utc: datetime
A datetime of the last data point uploaded.
day_count_uploaded: str
Days between last_upload_utc and collection_start_utc.
last_known_device_id: str(36)
Device (installation) ID that was last seen in the data.
active: bool
Is participant marked as active (i.e. collecting data)?
marked_active_utc: datetime
The time participant was first marked as active.
day_count_active: int
Days from marked_active_utc
tester: bool
Is this a tester (or a true participant)?
"""
2020-12-24 16:46:55 +01:00
__tablename__ = "participants"
id = Column(Integer, primary_key=True)
username = Column(String(64), index=True, unique=True)
password_hash = Column(String(128))
token = Column(String(32), index=True, unique=True)
token_expiration_utc = Column(TIMESTAMP(timezone=False))
collection_start_utc = Column(TIMESTAMP(timezone=False))
last_upload_utc = Column(TIMESTAMP(timezone=False))
day_count_uploaded = Column(SmallInteger)
last_known_device_id = Column(String(length=36), nullable=False, default="")
active = Column(Boolean, index=True, nullable=False, default=False)
marked_active_utc = Column(TIMESTAMP(timezone=False))
day_count_active = Column(SmallInteger)
tester = Column(Boolean, index=True, nullable=False, default=False)
def __repr__(self):
2020-12-31 14:33:44 +01:00
return "Participant()"
2020-12-24 16:46:55 +01:00
def __str__(self):
2020-12-31 14:33:44 +01:00
return "<Participant {}>".format(self.username)
2020-12-24 16:46:55 +01:00
class AWAREsensor(object):
2021-04-06 12:58:32 +02:00
"""
A general AWARE sensor class. It includes fields common to all tables/subclasses.
Attributes
----------
id: int
The primary key for each table.
_id: int
The original primary key, used in device's own MySQL database.
timestamp: int
Unixtime milliseconds since 1970.
device_id: str(36)
AWARE device UUID, a unique string of length 36.
Rather than identifying the device as the name suggests, this string changes on each (re)installation.
It is therefore better considered to be an installation ID.
Declared Attributes
-------------------
__tablename__: str
Table name used in the database.
participant_id: int
The foreign key relating (with the relationship) tables to the participants table.
"""
2020-12-24 16:46:55 +01:00
id = Column(BigInteger, primary_key=True, nullable=False)
_id = Column(BigInteger, nullable=False)
timestamp = Column(BigInteger, nullable=False)
device_id = Column(String(length=36), nullable=False)
@declared_attr
def __tablename__(self):
return self.__name__.lower()
2020-12-24 16:46:55 +01:00
@declared_attr
def participant_id(self):
2020-12-31 14:33:44 +01:00
return Column(
Integer, ForeignKey("participants.id"), nullable=False, index=True
)
2020-12-24 16:46:55 +01:00
@declared_attr
def participant(self):
2020-12-31 14:33:44 +01:00
return relationship("Participant", lazy="select", backref=self.__tablename__)
2020-12-24 16:46:55 +01:00
@declared_attr
def __table_args__(self):
2020-12-31 14:33:44 +01:00
return (
UniqueConstraint("device_id", "_id", name=self.__tablename__ + "_twice"),
)
# I think it makes more sense to create a Constraint
# on device_id and _id rather than relate it to participant_id.
2020-12-24 16:46:55 +01:00
# _id is a primary key, auto incremented by AWARE.
2020-12-31 14:33:44 +01:00
# However, I would expect it to reset back to 1
# if the application was reinstalled,
2020-12-24 16:46:55 +01:00
# similarly to how device_id resets.
class Accelerometer(Base, AWAREsensor):
double_values_0 = Column(Float, nullable=False)
double_values_1 = Column(Float, nullable=False)
double_values_2 = Column(Float, nullable=False)
accuracy = Column(SmallInteger, nullable=True)
label = Column(String, nullable=True)
class GoogleAR(Base, AWAREsensor):
__tablename__ = "google_ar"
activity_name = Column(String(32), nullable=True)
activity_type = Column(SmallInteger, nullable=True)
confidence = Column(SmallInteger, nullable=True)
activities = Column(PSQL_JSONB(none_as_null=False), nullable=True)
class Application(Base, AWAREsensor):
__tablename__ = "applications"
2020-12-31 14:33:44 +01:00
# package_name = Column(String, nullable=False)
# application_name = Column(String)
2020-12-24 16:46:55 +01:00
package_hash = Column(String(64), nullable=False)
play_store_genre = Column(String, nullable=True)
is_system_app = Column(Boolean)
class Barometer(Base, AWAREsensor):
"""
Contains the barometer sensor data.
Attributes
----------
double_values_0: float
The ambient air pressure in mbar (hPa)
accuracy: int
Sensors accuracy level, either 1, 2, or 3 (see [SensorManager](https://developer.android.com/reference/android/hardware/SensorManager.html#SENSOR_STATUS_ACCURACY_HIGH))
"""
2020-12-24 16:46:55 +01:00
double_values_0 = Column(Float, nullable=False)
accuracy = Column(SmallInteger, nullable=True)
label = Column(String, nullable=True)
class BarometerSensor(Base, AWAREsensor):
"""
Contains the barometer sensor capabilities.
Attributes
----------
double_sensor_maximum_range: float
Maximum sensor value possible
double_sensor_minimum_delay: float
Minimum sampling delay in microseconds
sensor_name: str
double_sensor_power_ma: float
Sensors power drain in mA
double_sensor_resolution: float
Sensors resolution in sensors units
sensor_type: str
sensor_vendor: str
Sensors manufacturer
sensor_version: str
"""
2020-12-24 16:46:55 +01:00
__tablename__ = "barometer_sensor"
2020-12-31 14:33:44 +01:00
# Since this table is not really important,
# I will leave all columns as nullable. (nullable=True by default.)
2020-12-24 16:46:55 +01:00
double_sensor_maximum_range = Column(Float)
double_sensor_minimum_delay = Column(Float)
sensor_name = Column(String)
double_sensor_power_ma = Column(Float)
double_sensor_resolution = Column(Float)
sensor_type = Column(String)
sensor_vendor = Column(String)
sensor_version = Column(String)
class Battery(Base, AWAREsensor):
battery_status = Column(SmallInteger, nullable=False)
battery_level = Column(SmallInteger, nullable=False)
battery_scale = Column(SmallInteger, nullable=False)
battery_voltage = Column(Integer, nullable=True)
battery_temperature = Column(Integer, nullable=True)
battery_adaptor = Column(SmallInteger, nullable=True)
battery_health = Column(SmallInteger, nullable=True)
battery_technology = Column(String, nullable=True)
class Bluetooth(Base, AWAREsensor):
bt_address = Column(String(length=40), nullable=False)
bt_name = Column(String, nullable=True)
bt_rssi = Column(Integer, nullable=True)
label = Column(String, nullable=True)
class Call(Base, AWAREsensor):
2021-04-06 12:58:32 +02:00
"""
Contains the calls sensor information.
Attributes
----------
call_type: int
One of the Androids call types (1 incoming, 2 outgoing, 3 missed).
call_duration: int
2021-07-03 18:45:46 +02:00
Length of the call session in seconds.
2021-04-06 12:58:32 +02:00
trace: str(40)
A hash value SHA-1 of the phone number (source or target) of the call
"""
2020-12-24 16:46:55 +01:00
call_type = Column(SmallInteger, nullable=False)
call_duration = Column(Integer, nullable=False)
trace = Column(String(length=40), nullable=True)
class ESM(Base, AWAREsensor):
esm_status = Column(SmallInteger, nullable=False)
esm_user_answer = Column(String, nullable=True)
esm_notification_timeout = Column(SmallInteger)
esm_json = Column(PSQL_JSONB(none_as_null=False), nullable=False)
double_esm_user_answer_timestamp = Column(BigInteger, nullable=False)
esm_trigger = Column(String, nullable=True)
esm_session = Column(Integer, nullable=False)
esm_notification_id = Column(Integer, nullable=False)
esm_expiration_threshold = Column(SmallInteger)
2021-07-04 14:34:57 +02:00
ESM_TYPE = {
"text": 1,
"radio": 2,
"checkbox": 3,
"likert": 4,
"quick_answers": 5,
"scale": 6,
"datetime": 7,
"pam": 8,
"number": 9,
"web": 10,
"date": 11,
}
2020-12-24 16:46:55 +01:00
class Imperfection(Base):
__tablename__ = "imperfection"
2020-12-24 16:46:55 +01:00
id = Column(BigInteger, primary_key=True, nullable=False)
timestamp = Column(BigInteger, nullable=False)
error = Column(String)
data_type = Column(String)
data = Column(String)
class LightSensor(Base, AWAREsensor):
"""
Contains the light sensor data.
Note: Even though this table is named light_sensor, it actually contains what AWARE calls light data
(rather than the data about the sensor's capabilities). Cf. Barometer(Sensor) and Temperature(Sensor).
Attributes
----------
double_light_lux: float
The ambient luminance in lux units
accuracy: int
Sensors accuracy level, either 1, 2, or 3 (see [SensorManager](https://developer.android.com/reference/android/hardware/SensorManager.html#SENSOR_STATUS_ACCURACY_HIGH))
"""
2020-12-24 16:46:55 +01:00
__tablename__ = "light_sensor"
double_light_lux = Column(Float, nullable=False)
accuracy = Column(Integer, nullable=True)
label = Column(String, nullable=True)
class Location(Base, AWAREsensor):
__tablename__ = "locations"
double_latitude = Column(Float, nullable=False)
double_longitude = Column(Float, nullable=False)
double_bearing = Column(Float)
double_speed = Column(Float)
double_altitude = Column(Float)
provider = Column(String)
accuracy = Column(Integer)
label = Column(String, nullable=True)
category = Column(PSQL_ARRAY(PSQL_TEXT), nullable=True)
category_short = Column(PSQL_ARRAY(PSQL_TEXT), nullable=True)
distance = Column(PSQL_ARRAY(PSQL_INTEGER), nullable=True)
api_response_code = Column(SmallInteger, nullable=False)
api_response = Column(String, nullable=True)
class Mic(Base, AWAREsensor):
half_s_speech = Column(Boolean, nullable=False)
rnnoutp = Column(Float, nullable=False)
flatness = Column(Float, nullable=False)
absmax = Column(Float, nullable=False)
max = Column(Float, nullable=False)
min = Column(Float, nullable=False)
# mfcc = Column(PSQL_ARRAY(PSQL_REAL, dimensions=13), nullable=False)
mfcc_0 = Column(Float, nullable=False)
mfcc_1 = Column(Float, nullable=False)
mfcc_2 = Column(Float, nullable=False)
mfcc_3 = Column(Float, nullable=False)
mfcc_4 = Column(Float, nullable=False)
mfcc_5 = Column(Float, nullable=False)
mfcc_6 = Column(Float, nullable=False)
mfcc_7 = Column(Float, nullable=False)
mfcc_8 = Column(Float, nullable=False)
mfcc_9 = Column(Float, nullable=False)
mfcc_10 = Column(Float, nullable=False)
mfcc_11 = Column(Float, nullable=False)
mfcc_12 = Column(Float, nullable=False)
class Speech(Base, AWAREsensor):
speech_proportion = Column(Float, nullable=False)
class NetworkData(Base, AWAREsensor):
__tablename__ = "network_data"
network_type = Column(SmallInteger, nullable=False)
network_subtype = Column(String(10), nullable=False)
network_state = Column(SmallInteger, nullable=True)
class NetworkTraffic(Base, AWAREsensor):
__tablename__ = "network_traffic_data"
network_type = Column(SmallInteger, nullable=False)
double_received_bytes = Column(Float, nullable=True)
double_sent_bytes = Column(Float, nullable=True)
double_received_packets = Column(Float, nullable=True)
double_sent_packets = Column(Float, nullable=True)
class Notification(Base, AWAREsensor):
__tablename__ = "notifications"
2020-12-31 14:33:44 +01:00
# package_name = Column(String, nullable=False)
# application_name = Column(String, nullable=False)
2020-12-24 16:46:55 +01:00
package_hash = Column(String(64), nullable=False)
play_store_genre = Column(String, nullable=True)
sound = Column(String, nullable=False)
vibrate = Column(String, nullable=False)
sound_app = Column(String, nullable=False)
vibrate_app = Column(String, nullable=False)
led_app = Column(String, nullable=False)
category = Column(String, nullable=False)
is_ongoing = Column(SmallInteger, nullable=False)
is_clearable = Column(SmallInteger, nullable=False)
is_group = Column(SmallInteger, nullable=True)
class Processor(Base, AWAREsensor):
double_last_user = Column(Float, nullable=False)
double_last_system = Column(Float, nullable=False)
double_last_idle = Column(Float, nullable=False)
double_user_load = Column(Float, nullable=False)
double_system_load = Column(Float, nullable=False)
double_idle_load = Column(Float, nullable=False)
class Proximity(Base, AWAREsensor):
double_proximity = Column(Float, nullable=False)
accuracy = Column(SmallInteger, nullable=True)
label = Column(String, nullable=True)
class Screen(Base, AWAREsensor):
screen_status = Column(SmallInteger)
class SMS(Base, AWAREsensor):
2021-04-06 13:52:08 +02:00
"""
Contains the messages sensor information.
Attributes
----------
message_type: int
Message type (1 received, 2 sent)
trace: str(40)
A hash value SHA-1 of the phone number (source or target) of the call
"""
2020-12-24 16:46:55 +01:00
message_type = Column(SmallInteger, nullable=False)
trace = Column(String(length=40), nullable=False)
class Temperature(Base, AWAREsensor):
"""
Contains the temperature sensor data.
Attributes
----------
temperature_celsius: float
Measured temperature in °C
accuracy: int
Sensors accuracy level, either 1, 2, or 3 (see [SensorManager](https://developer.android.com/reference/android/hardware/SensorManager.html#SENSOR_STATUS_ACCURACY_HIGH))
"""
2020-12-24 16:46:55 +01:00
temperature_celsius = Column(Float, nullable=False)
accuracy = Column(SmallInteger, nullable=True)
label = Column(String, nullable=True)
class TemperatureSensor(Base, AWAREsensor):
"""
Contains the temperature sensor capabilities.
Attributes
----------
double_sensor_maximum_range: float
Maximum sensor value possible
double_sensor_minimum_delay: float
Minimum sampling delay in microseconds
sensor_name: str
double_sensor_power_ma: float
Sensors power drain in mA
double_sensor_resolution: float
Sensors resolution in sensors units
sensor_type: str
sensor_vendor: str
Sensors manufacturer
sensor_version: str
"""
2020-12-31 14:33:44 +01:00
# I left all of these nullable,
# as we haven't seen any data from this sensor anyway.
2020-12-24 16:46:55 +01:00
__tablename__ = "temperature_sensor"
double_sensor_maximum_range = Column(Float)
double_sensor_minimum_delay = Column(Float)
sensor_name = Column(String)
double_sensor_power_ma = Column(Float)
double_sensor_resolution = Column(Float)
sensor_type = Column(String)
sensor_vendor = Column(String)
sensor_version = Column(String)
class Timezone(Base, AWAREsensor):
timezone = Column(String, nullable=False)
class WiFi(Base, AWAREsensor):
bssid = Column(String(length=40), nullable=False)
ssid = Column(String, nullable=True)
rssi = Column(Integer, nullable=False)
security = Column(String, nullable=True)
frequency = Column(Integer, nullable=True)
label = Column(String, nullable=True)
2020-12-31 14:33:44 +01:00
all_AWARE_tables = [
ESM,
Location,
Screen,
LightSensor,
Call,
SMS,
Application,
Notification,
Battery,
WiFi,
Proximity,
Timezone,
Processor,
NetworkData,
NetworkTraffic,
Barometer,
BarometerSensor,
Temperature,
TemperatureSensor,
Bluetooth,
Accelerometer,
GoogleAR,
Speech,
]
2020-12-24 16:46:55 +01:00
all_AWARE_table_names = [table.__tablename__ for table in all_AWARE_tables]
def increment_one(ii):
return ii + 1
class AppCategories(Base):
__tablename__ = "app_categories"
id = Column(Integer, primary_key=True)
application_name = Column(String, nullable=True)
package_name = Column(String, nullable=False)
package_hash = Column(String(64), index=True, nullable=False, unique=True)
play_store_genre = Column(String, nullable=True)
play_store_response = Column(SmallInteger, nullable=False)
2020-12-31 14:33:44 +01:00
number_of_attempts = Column(
SmallInteger, nullable=False, default=0, onupdate=increment_one
)
last_attempt = Column(
TIMESTAMP(timezone=False),
nullable=False,
default=datetime.utcnow,
onupdate=datetime.utcnow,
)