HYTOS

import sqlite3

import os

import sys

import symbol

import SymbolBase

from sqlite3 import Error

class SG_DbHelper():

    ROOT_DIR = ""

    DB_PATH = "db"

    DB_NAME = "ITI_PID.db"

    CREATE_SYMBOLS_TABLE_SQL = '''

        CREATE TABLE IF NOT EXISTS 'Symbol' (

            'uid'        INTEGER PRIMARY KEY AUTOINCREMENT,

                'symId'        INTEGER NOT NULL,

                'name'        TEXT NOT NULL,

                'type'        TEXT,

                'threshold'        NUMERIC NOT NULL DEFAULT 0.4,

                'minMatchPoint'        INTEGER NOT NULL DEFAULT 0,

                'isDetectOrigin'        INTEGER NOT NULL DEFAULT 0,

                'rotationCount'        INTEGER NOT NULL DEFAULT 4,

                'ocrOption'        INTEGER NOT NULL DEFAULT 0,

                'isContainChild'        INTEGER NOT NULL DEFAULT 0,

                'originalPoint'        TEXT,

                'connectionPoint'        TEXT,

                'baseSymbol'        TEXT,

                'additionalSymbol'        TEXT

        );

    '''

    #CREATE_SYMBOLS_TABLE_SQL = '''

    #    CREATE TABLE 'Symbol' (

    #        'uid'        INTEGER PRIMARY KEY AUTOINCREMENT,

           #     'symId'        INTEGER NOT NULL,

           #     'name'        TEXT NOT NULL,

           #     'type'        TEXT,

           #     'path'        TEXT,

           #     'threshold'        NUMERIC NOT NULL DEFAULT 0.4,

           #     'minMatchPoint'        INTEGER NOT NULL DEFAULT 0,

           #     'isDetectOrigin'        INTEGER NOT NULL DEFAULT 0,

           #     'rotationCount'        INTEGER NOT NULL DEFAULT 4,

           #     'ocrOption'        INTEGER NOT NULL DEFAULT 0,

           #     'isContainChild'        INTEGER NOT NULL DEFAULT 0,

           #     'originalPoint'        TEXT,

           #     'connectionPoint'        TEXT,

           #     'baseSymbol'        TEXT,

           #     'additionalSymbol'        TEXT

    #    );

    #'''

    INSERT_SYMBOL_SQL = '''

        INSERT INTO Symbol(symId, name, type, threshold, minMatchPoint, isDetectOrigin, rotationCount, ocrOption, isContainChild, originalPoint, connectionPoint, baseSymbol, additionalSymbol) 

        VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?);

    '''

    #INSERT_SYMBOL_SQL = '''

    #    INSERT INTO Symbol(symId, name, type, path, threshold, minMatchPoint, isDetectOrigin, rotationCount, ocrOption, isContainChild, originalPoint, connectionPoint, baseSymbol, additionalSymbol) 

    #    VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?);

    #'''

    UPDATE_SYMBOL_SQL = '''

        UPDATE Symbol

        SET

            symId = ?, name = ?, type = ?, threshold = ?, minMatchPoint = ?, isDetectOrigin = ?,

            rotationCount = ?, ocrOption = ?, isContainChild = ?, originalPoint = ?, connectionPoint = ?,

            baseSymbol = ?, additionalSymbol = ?

        WHERE uid = ?

    '''

    #UPDATE_SYMBOL_SQL = '''

    #    UPDATE Symbol

    #    SET

    #        symId = ?, name = ?, type = ?, path = ?, threshold = ?, minMatchPoint = ?, isDetectOrigin = ?,

    #        rotationCount = ?, ocrOption = ?, isContainChild = ?, originalPoint = ?, connectionPoint = ?,

    #        baseSymbol = ?, additionalSymbol = ?

    #    WHERE uid = ?

    #'''

    def __init__(self, rootDir):

        print("DB Helper __init__")

        self.ROOT_DIR = rootDir

        self.dbFullPath = self.ROOT_DIR+"/"+self.DB_PATH+"/"+self.DB_NAME

        #if not os.path.isfile(dbFullPath):

        #    print("DB doesn't exist")

        try:

            conn = sqlite3.connect(self.dbFullPath)

            print(sqlite3.version)

            cursor = conn.cursor()

            cursor.execute(self.CREATE_SYMBOLS_TABLE_SQL)

        except Error as e:

            print(e)

        finally:

            conn.close()

    def isExistData(self, fieldName, data):

        rows = None

        try:

            conn = sqlite3.connect(self.dbFullPath)

            cursor = conn.cursor()

            sql = ""

            if isinstance(data, str):

                sql = "SELECT * FROM Symbol WHERE " + fieldName + " = '"+ data +"'"

            else:

                sql = "SELECT * FROM Symbol WHERE " + fieldName + " = "+ str(data) +""

            cursor.execute(sql)

            rows = cursor.fetchall()

        except Error as e:

            print(e)

        finally:

            conn.close()

            if rows is not None and len(rows) > 0:

                return True

            else:

                return False

    def isExistFileName(self, imageDir):

        rows = None

        try:

            conn = sqlite3.connect(self.dbFullPath)

            cursor = conn.cursor()

            sql = "SELECT * FROM Symbol WHERE path = '"+ imageDir +"'"

            cursor.execute(sql)

            rows = cursor.fetchall()

        except Error as e:

            print(e)

        finally:

            conn.close()

            if rows is not None and len(rows) > 0:

                return True

            else:

                return False

    def insertSymbol(self, symbol):

        isAdded = False

        try:

            conn = sqlite3.connect(self.dbFullPath)

            cursor = conn.cursor()

            query = (symbol.getId(), symbol.getName(), symbol.getType(), symbol.getThreshold()

                           , symbol.getMinMatchCount(), symbol.getIsDetectOnOrigin(), symbol.getRotationCount()

                           , symbol.getOcrOption(), symbol.getIsContainChild()

                           , symbol.getOriginalPoint(), symbol.getConnectionPoint()

                           , symbol.getBaseSymbol(), symbol.getAdditionalSymbol())

            cursor.execute(self.INSERT_SYMBOL_SQL, query)

            conn.commit()

            isAdded = True

        except Error as e:

            print(e)

        finally:

            conn.close()

            return (isAdded, symbol.getType(), symbol.getName(), symbol.getPath())

    def updateSymbol(self, symbol):

        isUpdated = False

        try:

            conn = sqlite3.connect(self.dbFullPath)

            cursor = conn.cursor()

            query = (symbol.getId(), symbol.getName(), symbol.getType(), symbol.getThreshold()

                           , symbol.getMinMatchCount(), symbol.getIsDetectOnOrigin(), symbol.getRotationCount()

                           , symbol.getOcrOption(), symbol.getIsContainChild()

                           , symbol.getOriginalPoint(), symbol.getConnectionPoint()

                           , symbol.getBaseSymbol(), symbol.getAdditionalSymbol(), symbol.getUid())

            cursor.execute(self.UPDATE_SYMBOL_SQL, query)

            conn.commit()

            isUpdated = True

        except Error as e:

            print(e)

        finally:

            conn.close()

            return (isUpdated, symbol.getType(), symbol.getName(), symbol.getPath())