Пример #1
0
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()
Пример #2
0
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
Пример #3
0
    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
Пример #4
0
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()
Пример #5
0
 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()
Пример #6
0
    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
Пример #7
0
    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
Пример #8
0
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()
Пример #9
0
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
Пример #10
0
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():
Пример #11
0
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')
Пример #13
0
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')
Пример #14
0
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
Пример #15
0
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
Пример #16
0
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():