class OctoPrintSchema:
"""
Provide methods for OctoPrint profile management
"""
def __init__(self):
self.conn = Database().get_conn()
self.curs = self.conn.cursor()
self.create_octoprint_table()
def __del__(self):
self.conn.close()
def create_octoprint_table(self):
"""
Init octoprint table
:return: void
"""
query = """
CREATE TABLE IF NOT EXISTS octoprint (
ip CHAR(22) NOT NULL UNIQUE,
host VARCHAR(255) NOT NULL,
x_api_key CHAR(32) NOT NULL UNIQUE PRIMARY KEY
);
"""
self.curs.execute(query)
self.conn.commit()
class OctoPrintModel:
"""
Provide methods for OctoPrint profile management into the DB
"""
def __init__(self):
self.conn = Database().get_conn()
self.curs = self.conn.cursor()
def create(self, ip, host, x_api_key):
"""
Create new OctoPrint profile
:param ip: IP of the device with OctoPrint
:param host: The domain on which to make requests
:param x_api_key: X_API_KEY of OctoPrint
:return: True | False
"""
query = """
INSERT INTO octoprint(ip, host, x_api_key)
VALUES (%s, %s, %s)
"""
try:
self.curs.execute(query, (ip, host, x_api_key))
self.conn.commit()
return True
except mariadb.Error:
logger.exception("octoprint_model -> create")
return False
def delete(self, x_api_key):
"""
Delete an OctoPrint profile by x_api_key
:param x_api_key: X_API_KEY of OctoPrint
:return: True | False
"""
query = """
DELETE FROM octoprint
WHERE x_api_key=%s
"""
try:
self.curs.execute(query, (x_api_key, ))
self.conn.commit()
return True
except mariadb.Error:
logger.exception("octoprint_model -> delete")
return False
def updateIdEndereco(self, endereco, idEndereco):
try:
conn = Database().conexao()
cur = conn.cursor()
sql = "UPDATE contato\
SET idEndereco = ?\
WHERE id = ?;"
cur.execute(sql, (idEndereco, endereco.getIdContato()))
#aux = cur.lastrowid
conn.commit()
except expression as identifier:
pass
finally:
pass
class DeviceSchema:
def __init__(self):
self.conn = Database().get_conn()
self.curs = self.conn.cursor()
self.create_device_table()
self.create_power_strip_table()
def __del__(self):
self.conn.close()
def create_device_table(self):
query = """
CREATE TABLE IF NOT EXISTS device (
id CHAR(40) NOT NULL UNIQUE PRIMARY KEY,
ip CHAR(22) NOT NULL,
type VARCHAR(255) NOT NULL,
path VARCHAR(10)
)
"""
self.curs.execute(query)
self.conn.commit()
def create_power_strip_table(self):
"""
Init power_strip table
:return: void
"""
query = """
CREATE TABLE IF NOT EXISTS power_strip (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
device_id CHAR(40),
name VARCHAR(255) NOT NULL,
switch_number INT NOT NULL,
switch_name VARCHAR(255) NOT NULL,
FOREIGN KEY (device_id)
REFERENCES device(id)
ON DELETE CASCADE
);
"""
self.curs.execute(query)
self.conn.commit()
def getAll():
try:
conn = Database().conexao()
cur = conn.cursor()
sql = "SELECT contato.id, contato.nome, contato.celular,\
contato.telefone, contato.email, endereco.cep, endereco.logradouro,\
endereco.bairro, endereco.numero, endereco.complemento, endereco.localidade,\
endereco.uf \
FROM contato INNER JOIN endereco ON\
contato.idEndereco = endereco.id"
cur.execute(sql)
rows = cur.fetchall()
return rows
except Exception as e:
print("Erro ao consultar todos - "+e.__str__())
finally:
cur.close()
conn.close()
def insere(self, endereco):
try:
conn = Database().conexao()
cur = conn.cursor()
sql = 'INSERT INTO endereco (idContato, cep, logradouro, bairro, numero, complemento, uf)\
values(?,?,?,?,?,?,?)'
cur.execute(sql, (endereco.idContato, endereco.cep,
endereco.logradouro, endereco.bairro, endereco.numero, endereco.complemento, endereco.uf ))
aux = cur.lastrowid
conn.commit()
except Exception as e:
print("Erro ao inserir CONTATO - "+e.__str__())
finally:
cur.close()
conn.close()
return aux
def insere(self, contato):
try:
conn = Database().conexao()
cur = conn.cursor()
sql = "INSERT INTO contato (nome, celular, telefone, email, idEndereco)\
values(?, ?, ?, ?, ?)"
cur.execute(sql, (contato.getNome(), contato.getCelular(),
contato.getTelefone(), contato.getEmail(), 0))
aux = cur.lastrowid
conn.commit()
except Exception as e:
print("Erro ao inserir CONTATO - "+e.__str__())
finally:
cur.close()
conn.close()
return aux
class UserSchema:
"""
Provide methods for user data management
"""
def __init__(self):
self.conn = Database().get_conn()
self.curs = self.conn.cursor()
self.create_user_table()
self.create_otp_table()
def __del__(self):
"""
Body of destructor
:return: void
"""
self.conn.close()
def create_user_table(self):
"""
Init user table
:return: void
"""
query = """
CREATE TABLE IF NOT EXISTS user (
name VARCHAR(50) NOT NULL,
surname VARCHAR(50) NOT NULL,
username VARCHAR(50) NOT NULL UNIQUE,
email VARCHAR(100) NOT NULL UNIQUE,
api_key CHAR(64) NOT NULL UNIQUE PRIMARY KEY,
admin TINYINT NOT NULL DEFAULT 0,
created_on DATE DEFAULT (CURRENT_DATE),
otp_requests INT DEFAULT 0,
last_otp_timestamp TIMESTAMP NULL
);
"""
self.curs.execute(query)
self.conn.commit()
def create_otp_table(self):
"""
Init otp table
:return: void
"""
query = """
CREATE TABLE IF NOT EXISTS otp (
api_key CHAR(64) NOT NULL UNIQUE PRIMARY KEY,
otp_code INT(6) NOT NULL,
otp_timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
FOREIGN KEY (api_key)
REFERENCES user(api_key)
ON DELETE CASCADE
);
"""
self.curs.execute(query)
self.conn.commit()
class UserModel:
"""
Provide methods for user management into the DB
"""
def __init__(self):
self.conn = Database().get_conn()
self.curs = self.conn.cursor()
def create(self, name: str, surname: str, username: str, email: str,
api_key: str):
"""
Create new user.
:param name:
:param surname:
:param username:
:param email:
:param api_key:
:return: api_key | False
"""
query = """
INSERT INTO user(name, surname, username, email, api_key)
VALUES (%s, %s, %s, %s, %s)
"""
try:
self.curs.execute(query, (name, surname, username, email, api_key))
self.conn.commit()
return api_key
except mariadb.Error:
logger.exception("user_model -> create")
return False
# TODO
def delete(self):
pass
def update_otp(self, otp_requests, otp_timestamp, api_key):
query = """
UPDATE user
SET otp_requests=%s, last_otp_timestamp=%s
WHERE api_key=%s
"""
try:
self.curs.execute(query, (otp_requests, otp_timestamp, api_key))
self.conn.commit()
return True
except mariadb.Error:
logger.exception("user_model -> update_otp")
return False
def reset_otp_requests(self, api_key):
query = """
UPDATE user
SET otp_requests=0
WHERE api_key=%s
"""
try:
self.curs.execute(query, (api_key, ))
self.conn.commit()
return True
except mariadb.Error:
logger.exception("user_model -> reset_otp_requests")
return False
def get_otp_requests(self, api_key):
query = """
SELECT otp_requests
FROM user
WHERE api_key=%s
"""
try:
self.curs.execute(query, (api_key, ))
res = self.curs.fetchone()
self.conn.commit()
return res[0]
except mariadb.Error:
logger.exception("user_model -> get_otp_requests")
return False
def check_user(self, email: str, api_key: str):
"""
Find user by:
:param email:
:param api_key:
:returns tuple(email, api_key) | False
"""
query = """
SELECT EXISTS (
SELECT *
FROM user
WHERE email=%s AND api_key=%s
)
"""
try:
self.curs.execute(query, (email, api_key))
res = self.curs.fetchone()
self.conn.commit()
return bool(res[0])
except mariadb.Error:
logger.exception("user_model -> check_user")
return False
def check_username(self, username: str):
"""
Check if username already exists
:param username:
:return: True | False
"""
query = """
SELECT EXISTS (
SELECT *
FROM user
WHERE username=%s
)
"""
try:
self.curs.execute(query, (username, ))
res = self.curs.fetchone()
self.conn.commit()
return bool(res[0])
except mariadb.Error:
logger.exception("user_model -> check_username")
return False
def check_api_key(self, api_key: str):
"""
Find user by:
:param api_key:
:return True | False
"""
query = """
SELECT EXISTS (
SELECT *
FROM user
WHERE api_key=%s
)
"""
try:
self.curs.execute(query, (api_key, ))
res = self.curs.fetchone()
self.conn.commit()
return bool(res[0])
except mariadb.Error:
logger.exception("user_model -> check_api_key")
return False
def get_user_with_otp(self, email, api_key, otp):
"""
Find user by:
:param email:
:param api_key:
:param otp:
:return: True | False
"""
# NOTE: "SELECT EXISTS" -> Not work
query = """
SELECT user.email, user.api_key, otp.otp_code
FROM user INNER JOIN otp ON otp.api_key = user.api_key
WHERE user.email=%s AND user.api_key=%s AND otp.otp_code=%s
AND otp.otp_timestamp >= NOW() - INTERVAL 5 MINUTE
"""
try:
self.curs.execute(query, (email, api_key, otp))
res = self.curs.fetchone()
self.conn.commit()
if res is not None:
return True
return False
except mariadb.Error:
logger.exception("user_model -> get_user_with_otp")
return False
def check_user_otp_timestamp(self, api_key):
"""
Check if the last OTP was sent at least 10 minutes ago
:param api_key:
:return: True | False
"""
query = """
SELECT EXISTS (
SELECT name
FROM user
WHERE api_key=%s AND last_otp_timestamp <= NOW() - INTERVAL 10 MINUTE
)
"""
try:
self.curs.execute(query, (api_key, ))
res = self.curs.fetchone()
self.conn.commit()
return bool(res[0])
except mariadb.Error:
logger.exception("user_model -> check_user_otp_timestamp")
return False
def create_otp(self, api_key: str, otp_code: int):
"""
Store new otp code.
:param api_key:
:param otp_code:
:return: True | False
"""
query = """
REPLACE INTO otp(api_key, otp_code)
VALUES (%s, %s)
"""
try:
self.curs.execute(query, (api_key, otp_code))
self.conn.commit()
return True
except mariadb.Error:
logger.exception("user_model -> create_otp")
return False
def delete_otp(self, api_key: str, otp_code: int):
"""
Store new otp code.
:param api_key:
:param otp_code:
:return: True | False
"""
query = """
DELETE FROM otp
WHERE api_key=%s AND otp_code=%s
"""
try:
self.curs.execute(query, (api_key, otp_code))
self.conn.commit()
return True
except mariadb.Error:
logger.exception("user_model -> delete_otp")
return False
def get_otp_timestamp(self, api_key):
query = """
SELECT otp_timestamp
FROM otp
WHERE api_key=%s
"""
try:
self.curs.execute(query, (api_key, ))
res = self.curs.fetchone()
self.conn.commit()
return res[0]
except mariadb.Error:
logger.exception("user_model -> get_otp_info")
return False
import pytest
from config import database_config
from database.database import Database
from database.tables.api_progress_table import ApiProgressTable
from utilities import random_utilities
db = Database(database_config.test_database)
cursor = db.cursor()
table_name = 'test_api_progress'
api_progress_table = ApiProgressTable(cursor, table_name)
test_ticker = 'TEST'
def test_exists():
assert api_progress_table.exists()
def test_create():
assert api_progress_table.create()
def test_add_tickers():
tickers = []
for _ in range(1, 3):
tickers.append(random_utilities.random_letters())
assert api_progress_table.add_tickers(tickers) is True
def test_add_tickers_one_duplicate():
from config import database_config
from database.database import Database
from database.tables.tickers_table import TickersTable
from utilities import random_utilities
db = Database(database_config.test_database)
tickers_table = TickersTable(db.cursor())
def test_exists():
assert tickers_table.exists() is True
def test_create():
assert tickers_table.create() is True
def test_add_tickers():
tickers = []
for _ in range(1, 3):
tickers.append(random_utilities.random_letters())
assert tickers_table.add_tickers(tickers) is True
def test_add_tickers_one_duplicate():
new_ticker = random_utilities.random_letters()
tickers_table.add_ticker('test')
tickers = ['test', new_ticker]
assert tickers_table.add_tickers(tickers) is True
retrieved_tickers = tickers_table.get_tickers()
assert new_ticker in retrieved_tickers
import urllib2
import re
import time
import sys
import traceback
sys.path.append('.')
from database.database import Database
# init database
cursor = Database.cursor()
cursor.execute('''create table if not exists pictures (
id integer primary key auto_increment,
url text not null,
status char(10) not null,
collection_id integer not null,
filename text,
foreign key (collection_id) references collections(id)
)'''
)
Database.commit()
def get_html(url_target, socket_timeout):
data = urllib2.urlopen(url = url_target, timeout = socket_timeout)
return data.read()
def get_pictures(html):
pictures_content_reg = re.compile('<div class="postContent">.+?</div>', re.S)
picture_content = re.findall(pictures_content_reg, html)
if (len(picture_content) != 1):
raise Exception('No post content found')
import urllib2
import re
import time
import sys
import os
import hashlib
import traceback
sys.path.append('.')
from database.database import Database
if (len(sys.argv) != 2):
print 'should get base path'
quit()
base_path = sys.argv[1]
cursor = Database.cursor()
cursor.execute('select id, url from pictures where status = "%s"' % 'new')
row = cursor.fetchone()
socket_timeout = 5
while row:
try:
update_cursor = Database.cursor()
update_cursor.execute('update pictures set status = "%s" where id = %d' % ('waiting', row[0]))
Database.commit()
print row[1]
picture_data = urllib2.urlopen(url = row[1], timeout = socket_timeout).read()
sha1sum = hashlib.sha1(picture_data).hexdigest()
filename = sha1sum + '.' + row[1].split('.')[-1]
print filename
picture_file = open(base_path + '/' + filename, 'wb')
class DeviceModel:
"""
Provide methods for device data management into the DB
"""
def __init__(self):
self.conn = Database().get_conn()
self.curs = self.conn.cursor()
def create(self, id, ip, type, path):
"""
Create new device
:param id:
:param ip:
:param type:
:return: True | False
"""
query = """
INSERT INTO device (id, ip, type, path)
VALUES (%s, %s, %s, %s)
"""
try:
self.curs.execute(query, (id, ip, type, path))
self.conn.commit()
return True
except mysql.Error:
logger.exception("device_model -> create_device")
return False
def delete(self, id):
"""
Delete device by:
:param id:
:return: True | False
"""
query = """
DELETE FROM device
WHERE id=%s
"""
try:
self.curs.execute(query, (id, ))
self.conn.commit()
return True
except mysql.Error:
logger.exception("device_model -> delete_device")
return False
def check_id(self, id):
"""
Check if id exists into the DB
:param id:
:return: True | False
"""
query = """
SELECT EXISTS (
SELECT *
FROM device
WHERE id=%s
)
"""
try:
self.curs.execute(query, (id, ))
res = self.curs.fetchone()
self.conn.commit()
return bool(res[0])
except mysql.Error:
logger.exception("device_model -> check_id")
return False
def get_ip(self, id):
query = """
SELECT ip
FROM device
WHERE id=%s
"""
try:
self.curs.execute(query, (id, ))
res = self.curs.fetchone()
self.conn.commit()
return res[0]
except mysql.Error:
logger.exception("device_model -> get_ip")
return False
def get_path(self, id):
query = """
SELECT path
FROM device
WHERE id=%s
"""
try:
self.curs.execute(query, (id, ))
res = self.curs.fetchone()
self.conn.commit()
return res[0]
except mysql.Error:
logger.exception("device_model -> get_path")
return False
class PowerStripModel:
"""
Provide methods for power_strip data management into the DB
"""
def __init__(self):
self.conn = Database().get_conn()
self.curs = self.conn.cursor()
def create(self, device_id, name, switch_number, switch_name):
"""
Create new power strip
:param device_id:
:param name: The name of the power strip
:param switch_number:
:param switch_name:
:return: True | False
"""
query = """
INSERT INTO power_strip(device_id, name, switch_number, switch_name)
VALUES (%s, %s, %s, %s)
"""
try:
self.curs.execute(query,
(device_id, name, switch_number, switch_name))
self.conn.commit()
return True
except mysql.Error:
logger.exception("power_strip_model -> create")
return False
def delete(self, device_id):
"""
Delete power strip by device_id
:param device_id:
:return: True | False
"""
query = """
DELETE FROM power_strip
WHERE device_id=%s
"""
try:
self.curs.execute(query, (device_id, ))
self.conn.commit()
return True
except mysql.Error:
logger.exception("power_strip_model -> delete")
return False
def update_name(self, id, new_name):
"""
Update name of a specific power strip
:param id: ID of device
:param new_name:
:return: True | False
"""
query = """
UPDATE power_strip
SET name=%s
WHERE device_id=%s
"""
try:
self.curs.execute(query, (new_name, id))
self.conn.commit()
return True
except mysql.Error:
logger.exception("power_strip_model -> update_name")
return False
def update_switch_name(self, id, switch_number, new_name):
"""
Update name of a specific switch of specific power strip
:param id: ID of device
:param switch_number:
:param new_name:
:return: True | False
"""
query = """
UPDATE power_strip
SET switch_name=%s
WHERE device_id=%s AND switch_number=%s
"""
try:
self.curs.execute(query, (new_name, id, switch_number))
self.conn.commit()
return True
except mysql.Error:
logger.exception("power_strip_model -> update_switch_name")
return False
def check_name(self, name):
"""
Check if name exists into the DB
:param name:
:return: True | False
"""
query = """
SELECT EXISTS (
SELECT *
FROM power_strip
WHERE name=%s
)
"""
try:
self.curs.execute(query, (name, ))
res = self.curs.fetchone()
self.conn.commit()
return bool(res[0])
except mysql.Error:
logger.exception("power_strip_model -> check_name")
return False
def get_switch_number(self, id):
"""
Count how many switch have a specific power strip
:param id: ID of device
:return: Number of switchs | False
"""
query = """
SELECT COUNT(*)
FROM power_strip
WHERE device_id=%s
"""
try:
self.curs.execute(query, (id, ))
res = self.curs.fetchone()
self.conn.commit()
return res[0]
except mysql.Error:
logger.exception("power_strip_model -> get_switch_number")
return False
import datetime
from config import database_config
from database.database import Database
from database.tables.table_progress_table import TableProgressTable
from utilities import random_utilities
db = Database(database_config.test_database)
table_progress_table = TableProgressTable(db.cursor())
def test_exists():
assert table_progress_table.exists() is True
def test_create():
assert table_progress_table.create() is True
def test_add_row():
table_name = random_utilities.random_letters()
assert table_progress_table.add_row(table_name) is True
assert table_progress_table.get_last_updated(table_name) is None
def test_update_progress():
table_name = random_utilities.random_letters()
table_progress_table.add_row(table_name)
assert table_progress_table.update_progress(table_name) is True
def test_get_last_updated():
