# Copyright (C) 2008 Libresoft Research Group
#
# This program is free software; you can redistribute it and/or modify
# it under the terms of the GNU General Public License as published by
# the Free Software Foundation; either version 2 of the License, or
# (at your option) any later version.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
# GNU Library General Public License for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program; if not, write to the Free Software
# Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA 02111-1307, USA.
#
# Authors : Israel Herraiz <herraiz@gsyc.escet.urjc.es>

import MySQLdb
import datetime

LOGFILE = "/tmp/ports_query_errors"

class MyDatabase:

    def  __init__(self, logfilename = LOGFILE):

        self.user = ''
        self.password = ''
        self.host = ''
        self.name = ''
        self.logfilename = logfilename

        self.connection = None
        self.cursor_get = None
        self.cursor_set = None


    def __writeToLog(self,text):
        logfile = open(self.logfilename,'a')
        content = datetime.datetime.now().isoformat(' ')
        content += ' *** '+text
        content += '\n'
        logfile.write(content)
        logfile.close()
        print "ERROR: CHECK LOG"
        
    def connect(self):
        self.connection = MySQLdb.connect(user = self.user,
                                          passwd = self.password,
                                          host = self.host,
                                          db = self.name)

        self.cursor_get = self.connection.cursor()
        self.cursor_set = self.connection.cursor()

    def getListOfCategories(self):

        query = 'SELECT portsection, sum(slocsize) as totalsize from Projects where slocsize group by portsection order by totalsize desc;'
        self.cursor_get.execute(query)

        return self.cursor_get.fetchall()

    def getMetricsForCategory(self,category,tempfilename,type_of_file="global"):

        query = "select sloc, loc, blk_lines, comment_lines, comments, mccabe, returns, halstead_length, halstead_volume, halstead_level, halstead_md, functions into outfile "
        query += "'"+tempfilename+"'"
        query += " fields terminated by ' ' optionally enclosed by '' lines terminated by '\n' from CMetrics, UniqueFiles, Projects where md5=filemd5 and slocsize and UniqueFiles.portname=Projects.portname and lang='l_ansic' and "

        if 'header' == type_of_file:
            query += "filepath regexp '.h$'"
        elif 'nonheader' == type_of_file:
            query += "filepath regexp '.c$'"
        else:
            query += "(filepath regexp '.h$' or filepath regexp '.c$')"

        query += " and Projects.portsection='"+category+"';"

        self.cursor_get.execute(query)
        
    def insertProject(self,properties_dict):

        fields = properties_dict.keys()

        query_left = 'INSERT INTO Projects ('
        query_right = ') VALUES ('

        for f in fields:

            value = properties_dict[f]

            query_left += f + ','
            query_right += '"' + value + '",'

        query = query_left.rstrip(',') + query_right.rstrip(',') + ');'

        try:
            self.cursor_set.execute(query)
            self.cursor_set.execute("SET FOREIGN_KEY_CHECKS = 1;")
            return True
        except:
            self.__writeToLog(query)
            return False

    def updateProject(self,tarname,totalsize):

        query = 'UPDATE Projects SET slocsize="%s" WHERE tarfilename="%s"' % (str(totalsize),tarname)

        try:
            self.cursor_set.execute(query)
            self.cursor_set.execute("SET FOREIGN_KEY_CHECKS = 1;")
            return True
        except:
            self.__writeToLog(query)
            return False

    def getNameSection(self,tarname):

        query = 'SELECT portname, portsection FROM Projects WHERE tarfilename="%s"' % tarname

        self.cursor_get.execute(query)

        results = self.cursor_get.fetchall()

        portname_list = []
        portsection_list = []
        for r in results:
            portname = r[0]
            portsection = r[1]

            portname_list.append(portname)
            portsection_list.append(portsection)

        return portname_list, portsection_list
        
    def insertFile(self,properties_dict):

        fields = properties_dict.keys()

        query_left = 'INSERT INTO Files ('
        query_right = ' VALUES ('

        for f in fields:

            value = properties_dict[f]

            query_left += f + ','
            query_right += '"' + value + '",'

        query_left = query_left.rstrip(',')
        query_right = query_right.rstrip(',')
        
        query_left += ') '
        query_right += ');'

        query = query_left + query_right

        try:
            self.cursor_set.execute("SET FOREIGN_KEY_CHECKS = 0;")
            self.cursor_set.execute(query)
            return True
        except:
            self.__writeToLog(query)
            return False

    def getCFiles(self):

        query = 'SELECT filemd5, filepath, size from Files where lang="l_ansic" and filemd5 not in (select md5 from CMetrics) group by filemd5;'
        self.cursor_get.execute(query)

    def getCWrongFiles(self):
        #query = 'SELECT filemd5, filepath, size from Files, CMetrics where lang="l_ansic" and filemd5=md5 and (loc=-1 or blk_lines=-1 or mccabe=-1 or functions=-1);'
        query = 'SELECT filemd5, filepath, size from Files, CMetrics where lang="l_ansic" and filemd5=md5 and (halstead_length=-1);'
        self.cursor_get.execute(query)

    def updateMetrics(self,metrics_dict):

        fields = metrics_dict.keys()

        md5 = metrics_dict['md5']

        query_left = 'UPDATE CMetrics SET '
        query_right = ' WHERE md5="'+md5+'"'

        for f in fields:
            value = metrics_dict[f]
            query_left += f+'="'+str(value)+'",'

        query_left = query_left.rstrip(',')

        query = query_left + query_right

        self.cursor_set.execute(query)
        self.connection.commit()

    def insertMetrics(self,metrics_dict):

        fields = metrics_dict.keys()

        query_left = 'INSERT INTO CMetrics ('
        query_right = ' VALUES (';

        for f in fields:
            value = metrics_dict[f]
            query_left += f+','
            query_right += "'"+str(value)+"',"

        query_left = query_left.rstrip(',') + ")"
        query_right = query_right.strip(',') + ");"

        query = query_left+query_right

        self.cursor_set.execute(query)
        self.connection.commit()


    def getProjectsNames(self):

        query = 'SELECT projectname FROM Projects;'
        self.cursor_get.execute(query)