536 lines
17 KiB
Python
536 lines
17 KiB
Python
from datetime import datetime
|
||
|
||
from sqlalchemy import (
|
||
TIMESTAMP,
|
||
BigInteger,
|
||
Boolean,
|
||
Column,
|
||
Float,
|
||
ForeignKey,
|
||
Integer,
|
||
SmallInteger,
|
||
String,
|
||
UniqueConstraint,
|
||
)
|
||
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
|
||
from sqlalchemy.ext.declarative import declarative_base, declared_attr
|
||
from sqlalchemy.orm import relationship
|
||
|
||
Base = declarative_base()
|
||
|
||
|
||
class Participant(Base):
|
||
"""
|
||
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)?
|
||
"""
|
||
|
||
__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):
|
||
return "Participant()"
|
||
|
||
def __str__(self):
|
||
return "<Participant {}>".format(self.username)
|
||
|
||
|
||
class AWAREsensor(object):
|
||
"""
|
||
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.
|
||
"""
|
||
|
||
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()
|
||
|
||
@declared_attr
|
||
def participant_id(self):
|
||
return Column(
|
||
Integer, ForeignKey("participants.id"), nullable=False, index=True
|
||
)
|
||
|
||
@declared_attr
|
||
def participant(self):
|
||
return relationship("Participant", lazy="select", backref=self.__tablename__)
|
||
|
||
@declared_attr
|
||
def __table_args__(self):
|
||
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.
|
||
# _id is a primary key, auto incremented by AWARE.
|
||
# However, I would expect it to reset back to 1
|
||
# if the application was reinstalled,
|
||
# 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"
|
||
# package_name = Column(String, nullable=False)
|
||
# application_name = Column(String)
|
||
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
|
||
Sensor’s accuracy level, either 1, 2, or 3 (see [SensorManager](https://developer.android.com/reference/android/hardware/SensorManager.html#SENSOR_STATUS_ACCURACY_HIGH))
|
||
"""
|
||
|
||
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
|
||
Sensor’s power drain in mA
|
||
double_sensor_resolution: float
|
||
Sensor’s resolution in sensor’s units
|
||
sensor_type: str
|
||
sensor_vendor: str
|
||
Sensor’s manufacturer
|
||
sensor_version: str
|
||
"""
|
||
|
||
__tablename__ = "barometer_sensor"
|
||
# Since this table is not really important,
|
||
# I will leave all columns as nullable. (nullable=True by default.)
|
||
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):
|
||
"""
|
||
Contains the calls sensor information.
|
||
|
||
Attributes
|
||
----------
|
||
call_type: int
|
||
One of the Android’s call types (1 – incoming, 2 – outgoing, 3 – missed).
|
||
call_duration: int
|
||
Length of the call session in seconds.
|
||
trace: str(40)
|
||
A hash value SHA-1 of the phone number (source or target) of the call
|
||
"""
|
||
|
||
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)
|
||
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,
|
||
}
|
||
|
||
|
||
class Imperfection(Base):
|
||
__tablename__ = "imperfection"
|
||
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
|
||
Sensor’s accuracy level, either 1, 2, or 3 (see [SensorManager](https://developer.android.com/reference/android/hardware/SensorManager.html#SENSOR_STATUS_ACCURACY_HIGH))
|
||
"""
|
||
|
||
__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"
|
||
# package_name = Column(String, nullable=False)
|
||
# application_name = Column(String, nullable=False)
|
||
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):
|
||
"""
|
||
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
|
||
"""
|
||
|
||
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
|
||
Sensor’s accuracy level, either 1, 2, or 3 (see [SensorManager](https://developer.android.com/reference/android/hardware/SensorManager.html#SENSOR_STATUS_ACCURACY_HIGH))
|
||
"""
|
||
|
||
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
|
||
Sensor’s power drain in mA
|
||
double_sensor_resolution: float
|
||
Sensor’s resolution in sensor’s units
|
||
sensor_type: str
|
||
sensor_vendor: str
|
||
Sensor’s manufacturer
|
||
sensor_version: str
|
||
"""
|
||
|
||
# I left all of these nullable,
|
||
# as we haven't seen any data from this sensor anyway.
|
||
__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)
|
||
|
||
|
||
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,
|
||
]
|
||
|
||
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)
|
||
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,
|
||
)
|