#!/usr/bin/python # -*- coding: latin-1 -*- import copy class QueryBase: def __init__(self): # The form of the dict is: # {'String showed the user as sortorder': # [IsAsubselect,Table alias, field, datatype], ...} # The sort alternatives on id are only for debugging. self.sources={ 'Artist genre': [1,'Genre', 'name', 'string'], 'Artist rate': [2,'UserRate', 'rate', 'number'], 'Album artist name': [0,'AArtist', 'name', 'string'], 'Album artist id': [0,'AlbumArtist','artistId', 'number'], 'Album title': [0,'Album', 'name', 'string'], 'Album id': [0,'Album', 'albumId', 'number'], 'Album release year': [0,'Album', 'releaseDate', 'year'], 'Album cd': [0,'Record', 'volume', 'number'], 'Album genre': [1,'Genre', 'name', 'string'], 'Album rate': [2,'UserRate', 'rate', 'number'], 'Track name': [0,'Track', 'title', 'string'], 'Track id': [0,'Track', 'trackId', 'number'], 'Track number': [0,'Track', 'trackno', 'number'], 'Track length': [0,'Track', 'length', 'time'], 'Track artist name': [0,'TArtist', 'name', 'string'], 'Track artist id': [0,'TrackArtist','artistId', 'number'], 'Track rate': [2,'UserRate', 'rate', 'number'], 'Track genre': [1,'Genre', 'name', 'string'], } def isSubSelect(self,key): return self.sources[key][0] def isRate(self,key): return self.sources[key][0]==2 def getTable(self,key): return self.sources[key][1] def getField(self,key): return self.sources[key][2] def getType(self,key): return self.sources[key][3] class QueryComponent(QueryBase): # Component (composite pattern, gang of 4) def __init__(self): QueryBase.__init__(self) def BuildWhereClause(self): # is abstract return "" def findTable(self,table): # is abstract return False class QueryOperator(QueryComponent): # Composite (compsite pattern, gang of 4) def __init__(self,operator): QueryComponent.__init__(self) self.operator=operator self.children=[] def Add(self,child): self.children.append(child) def BuildWhereClause(self): # is abstract return "" def findTable(self,table): for i in self.children: if i.findTable(table): return True return False class QueryUnaryOperator(QueryOperator): def __init__(self,operator): QueryOperator.__init__(self,operator) def Add(self,child): if len(self.children)>0: #___FIX raise exception pass else: QueryOperator.Add(self,child) def BuildWhereClause(self): return " "+self.operator + " " + self.children[0].BuildWhereClause() class QueryBinaryOperator(QueryOperator): def __init__(self,operator): QueryOperator.__init__(self,operator) def BuildWhereClause(self): where="(" for child in self.children: where += " "+child.BuildWhereClause() + " " + self.operator where=where[:-(len(self.operator)+1)]+")" return where class QueryRow(QueryComponent): # leaf (compsite pattern,gang of 4) # Does not support case insensitive completly. # Prepared to handle year, but not finnished. # Nothing done with genre. # time datatype will possibly not work. # A query row is a simple search condition, that can be aggregated compound by Query # into a advanced select query. # # A query row contains of three attributes: # * source - Actually a string that defines table and field in the table to be # used. The table is specified in its context. Ie an artist can either # be related to an Album or a Track. # * operator - How the values in source and in the argument shall be compared. # * argument - The value you want to find, not find in the source. def __init__(self): QueryComponent.__init__(self) self.casesens=False # The form of the dict is: # {'String showed the user as source': [Table alias, field, datatype], ...} # Datatype can be: # string, then like operator must be used. # number # year # time # The search alternatives on id are only for debugging. self.operators={'contains': {'string': ' LIKE '}, 'not contains': {'string': ' NOT LIKE '}, 'is': {'string': ' LIKE ', 'number': '=', 'year':'=', 'time': '='}, 'is not': {'string': ' NOT LIKE ', 'number': '!=', 'year':'!=', 'time': '!='}, 'greater than': {'number': '>=', 'year':'>=', 'time': '>='}, 'lesser than': {'number': '<=', 'year':'<=', 'time':'<='}} def getOperator(self): return self.operators[self.operator][self.getType(self.source)] def getArgument(self): arg=self.argument if (self.operator=='contains') or (self.operator=='not contains'): arg='%'+self.argument+'%' if self.getType(self.source)=='string': arg='\''+arg+'\'' return arg # select distinct Track.trackId,Track.title # from Track # where # trackId in (select referenceId from GenreMapping,Genre # where # Genre.name='pop' and # Genre.genreId=GenreMapping.genreId and # ReferenceType='track'); def BuildWhereClause(self): whereStr='' if (self.isSubSelect(self.source)): type = self.source.split(' ')[0].lower() if (self.isRate(self.source)): whereStr = type + 'Id ' +\ 'in (select referenceId from UserRate ' +\ 'where rate=\'' + self.getArgument() + '\' and ' +\ 'ReferenceType=\'' + type + '\')' else: whereStr = type + 'Id ' +\ 'in (select referenceId from GenreMapping,Genre ' +\ 'where Genre.name like ' + \ self.getArgument() + ' and ' +\ 'Genre.genreId=GenreMapping.genreId and ' +\ 'ReferenceType=\'' + type + '\')' else: whereStr = self.getTable(self.source)+ "." + \ self.getField(self.source) + self.getOperator() + self.getArgument() return whereStr def setSource(self, source): if source in self.sources: self.source=source else: print "Wrong source!" def setArgument(self, argument): self.argument=argument def setOperator(self, operator): if operator in self.operators: self.operator=operator else: print "Wrong operator!" def setCaseSensitive(self): # Actually it looks like MySQL always search case insensitive. self.casesens=True def setCaseInsensitive(self): self.casesens=False def setRow(self, source, operator, argument): self.setSource(source) self.setOperator(operator) self.setArgument(argument) def findTable(self,table): return (table==self.getTable(self.source)) class Query(QueryBase): # Query compund a number of QueryRows into a SQL SELECT statement. # It also contains attributes for result data etc. def __init__(self): QueryBase.__init__(self) self.tree=None self.result='Track' self.casesensitive=True self.sortOrder=None # What order to join the table aliases. self.defjoinorder=['TArtist', 'TrackArtist', 'Track', 'Record', 'Album', 'AlbumArtist', 'AArtist'] self.defsubjoins={'Artist':[['Genre'], ['Rate','User']], 'Track':[['Genre'], ['Rate','User'],['TrackLog']], 'Album':[['Genre'], ['Rate','User']], 'Genre':[['Rate','User'],['User']], 'Record':[], 'User':[], 'Rate':[], 'TrackLog':[]} # How to do the join and make alias for the tables. # The form of the dict is: # ('Alias used i queryRows and joinorder'):['Alias definition', # 'Field to do the join with', # 'Field to get the resulyts from'] self.tablealiasdef={'TArtist': ['Artist AS TArtist', 'ERROR', 'TArtist.artistId'], 'TrackArtist': ['TrackArtist', 'artistId' , 'ERROR'], 'Track': ['Track', 'trackId', 'Track.trackId'], 'Record': ['Record', 'recordId', 'ERROR'], 'Album': ['Album', 'albumId', 'Album.albumId'], 'AlbumArtist': ['AlbumArtist', 'albumId', 'ERROR'], 'AArtist': ['Artist AS AArtist' , 'artistId', 'AArtist.artistId']} def setResult(self, result): # If you just want to search on Artist, use TArtist. if result in ['Track','Album','Artist','AArtist', 'TArtist']: self.result=result else: #___FIX: raise exception? pass def resolvResult(self): if self.result == 'Artist': if self.sortOrder is None: # and what should we do it the user hasn't added a sort column? # for the moment we default to AlbumArtist self.resolvedResult="AArtist" else: if self.sortOrder.startswith("Album artist"): self.resolvedResult="AArtist" elif self.sortOrder.startswith("Track artist"): self.resolvedResult="TArtist" else: #do more work check query rows. pass else: self.resolvedResult=self.result def setOrder(self, order): if order!='': self.sortOrder=order; def setCasesensitive(self, casesensitive): # Actually not used by MySQL. self.casesensitive=casesensitive def getResultType(self): return self.tablealiasdef[self.result][2] def usingTable(self, table): # Check if the query needs a specific table in the database. if self.resolvedResult==table: return True try: if self.sources[self.sortOrder][0]==table: return True; except KeyError: pass if self.tree.findTable(table): return True return False def usingRestTable(self,rest): # Same as usingTable, but takes a list with tables. for t in rest: if self.usingTable(t)==True: return True return False def getTableAliasDef(self, table): return self.tablealiasdef[table][0] def getJoinField(self, table): return self.tablealiasdef[table][1] def getSelect(self): # Generates the select. self.resolvResult() # joinstr is the part between FROM and WHERE in the SELECT statement. joinstr=None # wherestr is the string after WHERE in the SELECT statement. wherestr=None # rest is all the tables in joinorder not used yet. rest=copy.copy(self.defjoinorder) print "rest1: ",rest for j in self.defjoinorder: if (joinstr==None) and (self.usingTable(j)==True): # If no table used yet but current table(j) must be used in the query. # create the alias for the table and put it in join str. joinstr=self.getTableAliasDef(j) elif (joinstr!=None) and (self.usingRestTable(rest)): # If at least one table is added to the join string and we have to # use any of the remaining tables in joinorder including the current j. # Then LEFT JOIN with the previous table and join with the join field. joinstr=joinstr + ' LEFT JOIN ' + self.getTableAliasDef(j) joinstr=joinstr + ' USING (' + self.getJoinField(j) + ') ' rest.remove(j) print "rest2: ",rest print "join: ",joinstr wherestr=self.tree.BuildWhereClause() if self.sortOrder!=None: wherestr = wherestr + ' ORDER BY ' + self.getTable(self.sortOrder) + \ '.' + self.getField(self.sortOrder) return 'SELECT DISTINCT ' + self.tablealiasdef[self.resolvedResult][2] + \ ' FROM ' + joinstr + ' WHERE ' + wherestr + ';' def getTree(self): return self.tree def setTree(self, tree): self.tree=tree def test2(): ### This string should I get with the following search: ### SELECT DISTINCT Artist.artistId,Artist.name FROM Artist,TrackArtist WHERE TrackArtist.artistId IN (SELECT referenceId FROM GenreMapping,Genre WHERE Genre.name LIKE 'pop' AND Genre.genreId=GenreMapping.genreId AND ReferenceType='track') AND Artist.artistId=TrackArtist.artistId ORDER BY Artist.name; artistName="pop" print "Search for tracks with genre",artistName,"and get their Artist" query=Query() query.setResult('Artist') row=QueryRow() row.setRow('Track genre', 'is', artistName) query.setTree(row) print query.getSelect(); print '###############################' ### This string should I get with the following search: ### SELECT DISTINCT Album.albumId,Album.name FROM Album,Record,Track WHERE Track.trackId in (select referenceId from GenreMapping,Genre where Genre.name like 'pop' and Genre.genreId=GenreMapping.genreId and ReferenceType='track') AND Track.recordId=Record.recordId AND Record.albumId=Album.albumId; artistName="pop" print "Search for Albums with a track that has genre",artistName query=Query() query.setResult('Album') row=QueryRow() row.setRow('Track genre', 'is', artistName) query.setTree(row) print query.getSelect(); print '###############################' #artistName="pop" #print "Search for tracks with genre",artistName #query=Query() #query.setResult('Track') #row=QueryRow() #row.setRow('Track genre', 'is', artistName) #query.setTree(row) #print query.getSelect(); #print '###############################' #artistName="2" #print "Search for tracks with rate ",artistName #query=Query() #query.setResult('Album') #row=QueryRow() #row.setRow('Album rate', 'is', artistName) #query.setTree(row) #print query.getSelect(); #print '###############################' def test(): artistName="ABBA" print "Search for",artistName,"tracks" query=Query() query.setResult('Track') row=QueryRow() row.setRow('Track artist name', 'is', artistName) query.setTree(row) print query.getSelect(); print '###############################' print "Search for",artistName,"albums" query=Query() query.setResult('Album') row=QueryRow() row.setRow('Album artist name', 'contains', artistName) query.setTree(row) print query.getSelect(); print '###############################' print "Search for albums artists containing",artistName query=Query() query.setResult('Artist') row=QueryRow() row.setRow('Album artist name', 'contains', artistName) query.setTree(row) print query.getSelect(); print '###############################' print "Search for tracks made by",artistName,"with 'SOS' in the title" query=Query() query.setResult('Track') query.setOrder('Track number') tree=QueryBinaryOperator("AND") row=QueryRow() row.setRow('Track artist name', 'contains', artistName) tree.Add(row) row=QueryRow() row.setRow('Track name', 'contains', 'SOS') tree.Add(row) query.setTree(tree) print query.getSelect(); print '###############################' if __name__=='__main__': test2()