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, ), )