def get_all(
cls,
db_conn: SQLConnection,
project_id: Optional[int] = None,
sensor_id: Optional[int] = None,
) -> List["DataPoints"]:
if project_id is not None:
data_cursor = db_conn.execute(
'''
select id, project_id, sensor_id, angle, temperature, battery, timestamp
from Datapoint
where project_id = ?;
''',
(project_id, ),
)
elif sensor_id is not None:
data_cursor = db_conn.execute(
'''
select id, project_id, sensor_id, angle, temperature, battery, timestamp
from Datapoint
where sensor_id = ?;
''',
(sensor_id, ),
)
else:
# Needs either a project or a sensor id.
raise NotImplementedError
data_cursor.row_factory = DataPoints.row_factory
return data_cursor.fetchall()
def delete(cls, db_conn: SQLConnection, u_id: int):
db_conn.execute(
'''
delete from User
where id = ?;
''',
(u_id, ),
)
def create(cls, db_conn: SQLConnection, username: AnyStr, password: AnyStr,
is_admin: bool):
salt = bcrypt.gensalt()
hashed_password = bcrypt.hashpw(password.encode('utf8'), salt)
db_conn.execute(
'''
insert into User (username, password, is_admin)
values (?, ?, ?);
''', (username, hashed_password, is_admin))
def delete(cls, db_conn: SQLConnection, p_id: int):
db_conn.execute(
"""
Delete from Datapoint where project_id=?;
""", (p_id, ))
db_conn.execute(
"""
Delete from Project where id=?;
""", (p_id, ))
def edit(cls, db_conn: SQLConnection, p_id: int, new_name: AnyStr,
new_owner_id: int):
db_conn.execute(
"""
Update Project
set name=?, owner=?
where id=?;
""",
(new_name, new_owner_id, p_id),
)
def from_db(cls, db_conn: SQLConnection, u_id: int) -> "User":
data = db_conn.execute(
'''
select username, is_admin
from User
where id = ?;
''',
(u_id, ),
).fetchone()
return cls(u_id, data[0], data[1])
def edit(
cls,
db_conn: SQLConnection,
s_id: int,
new_name: AnyStr,
new_secret: AnyStr,
new_owner_id: int,
new_max_battery: int,
new_min_battery: int,
):
db_conn.execute(
"""
Update Sensor
set name=?, secret=?, owner=?, max_battery=?, min_battery=?
where id=?;
""",
(new_name, new_secret, new_owner_id, new_max_battery,
new_min_battery, s_id),
)
def create_new(cls, db_conn: SQLConnection, name: AnyStr, secret: AnyStr,
owner: int) -> int:
cursor = db_conn.cursor()
cursor.execute(
'''
insert into Sensor (name, secret, owner) values (?, ?, ?);
''',
(name, secret, owner),
)
# lastrowid is the last successful insert on that cursor
return cursor.lastrowid
def get_all(cls, db_conn: SQLConnection) -> List["Sensor"]:
cursor = db_conn.execute('''
select id, name, secret, max_battery, min_battery,
(select battery from Datapoint where sensor_id = Sensor.id order by timestamp desc limit 1) as last_battery,
(select timestamp from Datapoint where sensor_id = Sensor.id order by timestamp desc limit 1) as last_active,
(select username from User where id = Sensor.owner limit 1) as owner,
(select id from Project where active_sensor = Sensor.id) as linked_project
from Sensor
order by id desc;
''')
cursor.row_factory = cls.row_factory
return cursor.fetchall()
def attach_sensor(self,
db_conn: SQLConnection,
sensor_id: Optional[int] = None) -> None:
"""
Update the active sensor of a project. If None is provided as a sensor id then only detach
the current sensor.
"""
if sensor_id is not None:
# Remove the sensor from any projects its currently attached to.
db_conn.execute(
'''
update Project set active_sensor = null
where active_sensor = ?;
''',
(sensor_id, ),
)
db_conn.execute(
'''
update Project set active_sensor = ?
where id = ?;
''',
(sensor_id, self.id),
)
else:
db_conn.execute(
'''
update Project set active_sensor = null
where id = ?;
''',
(self.id, ),
)
def find(cls, db_conn: SQLConnection,
sensor_id: int) -> Optional["Sensor"]:
sens_cursor = db_conn.execute(
'''
select id, name, secret, max_battery, min_battery,
(select battery from Datapoint where sensor_id = Sensor.id order by timestamp desc limit 1) as last_battery,
(select timestamp from Datapoint where sensor_id = Sensor.id order by timestamp desc limit 1) as last_active,
(select username from User where id = Sensor.owner limit 1) as owner,
(select id from Project where active_sensor = Sensor.id) as linked_project
from Sensor where id = ?;
''', (sensor_id, ))
sens_cursor.row_factory = cls.row_factory
return sens_cursor.fetchone()
def get_users(cls, db_conn: SQLConnection) -> Iterator[Dict]:
data = db_conn.execute(
'''
select id, username, is_admin
from User;
''', ).fetchall()
def index_to_name(n):
return {
'id': n[0],
'username': n[1],
'is_admin': n[2],
}
return map(index_to_name, data)
def get_all(cls, db_conn: SQLConnection) -> List["Project"]:
cursor = db_conn.execute('''
select id, name, active_sensor,
(select timestamp from Datapoint where project_id = Project.id order by timestamp asc limit 1) as first_active,
(select timestamp from Datapoint where project_id = Project.id order by timestamp desc limit 1) as last_active,
(select angle from Datapoint where project_id = Project.id order by timestamp asc limit 1) as first_angle,
(select angle from Datapoint where project_id = Project.id order by timestamp desc limit 1) as last_angle,
(select temperature from Datapoint where project_id = Project.id order by timestamp desc limit 1) as last_temperature,
(select username from User where id = Project.owner limit 1) as owner
from Project
order by id desc;
''')
# TODO(tr) Add order by last activity
cursor.row_factory = cls.row_factory
return cursor.fetchall()
def find(cls, db_conn: SQLConnection, project_id: int) -> "Project":
cursor = db_conn.execute(
'''
select id, name, active_sensor,
(select timestamp from Datapoint where project_id = Project.id order by timestamp asc limit 1) as first_active,
(select timestamp from Datapoint where project_id = Project.id order by timestamp desc limit 1) as last_active,
(select angle from Datapoint where project_id = Project.id order by timestamp asc limit 1) as first_angle,
(select angle from Datapoint where project_id = Project.id order by timestamp desc limit 1) as last_angle,
(select temperature from Datapoint where project_id = Project.id order by timestamp desc limit 1) as last_temperature,
(select username from User where id = Project.owner limit 1) as owner
from Project
where id = ?;
''', (project_id, ))
cursor.row_factory = cls.row_factory
return cursor.fetchone()
def by_active_sensor(cls, db_conn: SQLConnection,
sensor_id: int) -> Optional["Project"]:
proj_cursor = db_conn.execute(
'''
select id, name, active_sensor,
(select timestamp from Datapoint where project_id = Project.id order by timestamp asc limit 1) as first_active,
(select timestamp from Datapoint where project_id = Project.id order by timestamp desc limit 1) as last_active,
(select angle from Datapoint where project_id = Project.id order by timestamp asc limit 1) as first_angle,
(select angle from Datapoint where project_id = Project.id order by timestamp desc limit 1) as last_angle,
(select temperature from Datapoint where project_id = Project.id order by timestamp desc limit 1) as last_temperature,
(select username from User where id = Project.owner limit 1) as owner
from Project
where active_sensor = ?
order by id desc;
''', (sensor_id, ))
proj_cursor.row_factory = cls.row_factory
return proj_cursor.fetchone()
def verify(cls, db_conn: SQLConnection, username: AnyStr,
password: AnyStr) -> Optional["User"]:
data = db_conn.execute(
'''
select id, is_admin, password
from User
where username = ?;
''',
(username, ),
).fetchone()
if data is not None:
hashed_password = data[2]
if bcrypt.checkpw(password.encode('utf8'), hashed_password):
return User(
data[0],
username,
data[1],
)
return None
def delete(cls, db_conn: SQLConnection, s_id: int):
db_conn.execute(
"""
Delete from Datapoint where sensor_id=?;
""",
(s_id, ),
)
db_conn.execute(
"""
Update Project set 'active_sensor' = NULL where active_sensor=?;
""",
(s_id, ),
)
db_conn.execute(
"""
Delete from Sensor where id=?;
""",
(s_id, ),
)
