sql_database.py

Go to the documentation of this file.

# -*- coding: utf-8 -*-
import sqlite3
import roslib
roslib.load_manifest('face_contour_detector')
import rospy
import os
import image_information
import cv
import cv_bridge
from face_contour_detector.srv import *
from face_contour_detector.msg import *

## @package face_contour_detector.image_information.sql_database
#  This package contains only one class: SQLImageDatabase

## @brief maximum image count that is allowed in the database (keep file small and increase runtime)
MAX_IMAGE_SIZE = 50000

## @brief class that is used to interact with a sqlite database that stores image information.
#  The image information are stored in a sqlite database with two tables: one holding the images itself. For an image we save its brightness, the standard deviation of the brightness and the user raiting. There's also an id field used as primary key.
#  The second tables hold information about image areas. For each area we save the image id this area belongs to, the name of the area and parameter values for the blur and canny filter.
#  The ER-diagram looks like this (not with the exact names as used in the tables):
#  @image html image_er.png
class SQLImageDatabase:
    def __init__(self, bridge=None):
        if bridge is None:
            bridge = cv_bridge.CvBridge()
        self.bridge = bridge
        self.connection = None
    
    # variable documentation
    ## @var bridge 
    #  cv_bridge that is used to convert images
    
    ## @var connection
    #  a sqlite Connection object - the connection to the image database. Is initially set to None

        ## @brief try to load the sqlite database with the given file name.
        #  @param self the object pointer
        #  @param file_name the file name of the database. If the file_name is None a default path is used as file name. The path is package_dir/data/image_information.db
        #
        #  If file_name does not exists a new database is created on that location. May throw an exception if something went wrong.
    def load_file(self, file_name=None):
        if file_name is None:
            pkg_dir = roslib.packages.get_pkg_dir("face_contour_detector")
            data_dir = os.path.join(pkg_dir, "data")
            
            if not os.path.exists(data_dir):
                try:
                    os.mkdir(data_dir)
                    rospy.loginfo("created directory " + data_dir + " required for image information database")
                except Exception as e:
                    rospy.logerr("cant create directory " + data_dir + " required for image information database")
                    raise e
            file_name = os.path.join(data_dir, "image_information.db")

        existed = os.path.exists(file_name)
        if not existed:
            rospy.loginfo("no database found. Try to create new one...")
        try:
            self.connection = sqlite3.connect(file_name)
            rospy.loginfo("opened / created image information database " + file_name)
        except Exception as e:
            rospy.logerr("unable to open / generate image information database file " + file_name)
            raise e
        if not existed:
            self._create_tables()
        return existed
    
    ## @brief Create on self.connection the two tables
    #  @param self the object pointer
    def _create_tables(self):
        cmd = """
            CREATE TABLE images
            (id INTEGER PRIMARY KEY,
            brightness FLOAT,
            brightness_sdeviation FLOAT,
            raiting INT
            )"""
        self.connection.execute(cmd)
        cmd = """
            CREATE TABLE image_areas
            (
            image_id INTEGER,
            area_name TEXT,
            blur_width INTEGER,
            blur_height INTEGER,
            c_threshold1 FLOAT,
            c_threshold2 FLOAT
            )"""
        self.connection.execute(cmd)
        self.connection.commit()
    
    ## @brief add information about a new image to the database
    #  @param self the object pointer
    #  @param image: a cvMat (RGB color) the image to store information about
    #  @param raiting the raiting of the image (between 1 and 6)
    #  @param areas the image areas with their settings
    def add_image(self, image, raiting, areas):
        extractor = image_information.InformationExtractor()
        extractor.information_extractors = [ image_information.AverageValueExtractor() ]
        information = extractor.to_dict(image)
        cmd = "SELECT COUNT(*) FROM images"
        cursor = self.connection.execute(cmd)
        count = None
        # there's only one result row...
        for row in cursor:
            count = row[0]
        rospy.loginfo("Image database contains information about " + str(count) + " images")
        # check if table is full according to MAX_IMAGE_SIZE
        if count >= MAX_IMAGE_SIZE:
            cmd = "SELECT id FROM images LIMIT 1"
            cursor = self.connection.execute(cmd)
            delete_id = None
            # only one entry...
            for row in cursor:
                delete_id = row[0]
            # delete all information associated with this id
            cmd = "DELETE FROM images WHERE id = ?"
            self.connection.execute(cmd, (delete_id,))
            cmd = "DELETE FROM image_areas WHERE image_id = ?"
            self.connection.execute(cmd, (delete_id,))
        t = information["AveragePixelBrightness"][0], information["StandardBrightnessDeviation"][0], raiting
        cmd = """
            INSERT INTO images (brightness, brightness_sdeviation, raiting)
            VALUES(?, ?, ?)
            """
        cursor = self.connection.execute(cmd, t)
        _id = cursor.lastrowid
        for area in areas:
            blur_width, blur_height, treshold1, threshold2 = None, None, None, None
            for _filter in area.filters:
                if _filter.name == "GaussianBlur":
                    blur_width, blur_height = 0, 0
                    for parameter in _filter.parameters:
                        if parameter.name == "blurwidth":
                            blur_width = int(parameter.value)
                        elif parameter.name == "blurheight":
                            blur_height = int(parameter.value)
                elif _filter.name == "Canny":
                    for parameter in _filter.parameters:
                        if parameter.name == "threshold1":
                            threshold1 = float(parameter.value)
                        elif parameter.name == "threshold2":
                            threshold2 = float(parameter.value)
            cmd = "INSERT INTO image_areas (image_id, area_name, blur_width, blur_height, c_threshold1, c_threshold2) VALUES (?, ?, ?, ?, ?, ?)"
            t = _id, area.area.name, blur_width, blur_height, threshold1, threshold2
            self.connection.execute(cmd, t)
        self.connection.commit()
    
    ## @brief apply the filters on image and return the result image
    def _apply_filter(self, image, areas):
        img_msg = self.bridge.cv_to_imgmsg(image)
        f = rospy.ServiceProxy("apply_filters", ApplyFilters)
        resp = f(img_msg, areas)
        return self.bridge.imgmsg_to_cv(resp.result_image, "mono8")
    
    ## @brief returns a list of image information from the database
    #  @param self the object pointer
    #
    #  The return value is a list of dictionaries. Each dictionary stores data about an image.
    #  The keys of the dictionary are: "AveragePixelBrightness" : float, "StandardBrightnessDeviation" : float, "Raiting" : int between 1 and 6, "Areas" : a new dictionary.
    #  "Areas" is a dictionary that stores the areas. Each entry of this dictionary is again a dictionary with the filter settings of this area.
    #  It's values are: "blurwidth", "blurheight", "threshold1", "threshold2"
    def get_images(self):
        cmd = """
                SELECT * FROM images
              """
        cursor = self.connection.execute(cmd)
        images = []
        for _id, brightness, brightness_sderivation, raiting in cursor:
            #print(_id)
            image = {}
            image["AveragePixelBrightness"] = brightness
            image["StandardBrightnessDeviation"] = brightness_sderivation
            image["Raiting"] = raiting
            image["Areas"] = areas = {}
            # get all associated areas
            cmd = """
                    SELECT * FROM image_areas WHERE image_id = ?
                  """
            cursor2 = self.connection.execute(cmd, (_id,))
            for image_id, name, blur_width, blur_height, threshold1, threshold2 in cursor2:
                area = {}
                area["blurwidth"] = blur_width
                area["blurheight"] = blur_height
                area["threshold1"] = threshold1
                area["threshold2"] = threshold2
                areas[name] = area
            images.append(image)
        return images