def getSQLStatement(dbHandle, appIdVector, permissionRestrictionList,
restrictionType):
permissionRestrictionSQLQueryList = databaseHandler.convertPythonListToSQLQueryList(
permissionRestrictionList)
appIdVectorSQLQueryList = databaseHandler.convertPythonListToSQLQueryList(
appIdVector)
if permissionRestrictionSQLQueryList == '':
# Get the complete permissions vector and then use that as the vector rep for each app
# If the app has requested said permission then mark that as 1 or else let the vetor index for a permission remain zero
# Select no restrictions doesn't matter what restrictionType we have
#sqlStatement = "SELECT app.`app_pkg_name`, p.`name` FROM `appperm` a, `permissions` p, `appdata` app WHERE a.`perm_id` = p.`id` AND a.`app_id` = app.`id` AND a.`app_id` IN ("+appIdVectorSQLQueryList+");"
sqlStatement = "SELECT app.`app_pkg_name`, a.`perm_id` FROM `appperm` a, `appdata` app WHERE a.`app_id` = app.`id` AND a.`app_id` IN (" + appIdVectorSQLQueryList + ");"
else:
if restrictionType == 'allow':
# Get the complete permissions vector and then use that as the vector rep for each app
# If the app has requested said permission then mark that as 1 or else let the vetor index for a permission remain zero
# Select only permissions which have been allowed
sqlStatement = "SELECT app.`app_pkg_name`, a.`perm_id` FROM `appperm` a, `permissions` p, `appdata` app WHERE a.`perm_id` = p.`id` AND a.`app_id` = app.`id` AND a.`app_id` IN (" + appIdVectorSQLQueryList + ") AND p.`name` IN (" + permissionRestrictionSQLQueryList + ");"
else:
# Get the complete permissions vector and then use that as the vector rep for each app
# If the app has requested said permission then mark that as 1 or else let the vetor index for a permission remain zero
# Select only permissions which have not been restricted
sqlStatement = "SELECT app.`app_pkg_name`, a.`perm_id` FROM `appperm` a, `permissions` p, `appdata` app WHERE a.`perm_id` = p.`id` AND a.`app_id` = app.`id` AND a.`app_id` IN (" + appIdVectorSQLQueryList + ") AND p.`name` NOT IN (" + permissionRestrictionSQLQueryList + ");"
return sqlStatement
def getHMDAppsTopFewThousands(dbHandle,appCategoryList):
appCategorySQLQueryList = databaseHandler.convertPythonListToSQLQueryList(appCategoryList)
sqlStatement = "SELECT a.`id`, a.`app_pkg_name` FROM `appdata` a, `appurls` url, `appcategories` cat WHERE a.`app_pkg_name` = url.`app_pkg_name` AND url.`perm_extracted` = 1 AND cat.`url` IN ("+appCategorySQLQueryList+") AND a.`app_category_id` = cat.`id` ORDER BY a.`installs` DESC, a.`review_rating` DESC, a.`review_count` DESC LIMIT 25000;"
return generateAppDict(dbHandle,sqlStatement)
def getCategoryApps(dbHandle,appCategoryList):
appCategorySQLQueryList = databaseHandler.convertPythonListToSQLQueryList(appCategoryList)
sqlStatement = "SELECT a.`id`, a.`app_pkg_name` FROM `appdata` a, `appurls` url, `appcategories` cat WHERE a.`app_pkg_name` = url.`app_pkg_name` AND url.`perm_extracted` = 1 AND cat.`url` IN ("+appCategorySQLQueryList+") AND a.`app_category_id` = cat.`id`;"
return generateAppDict(dbHandle,sqlStatement)
def getTopApps(dbHandle):
appCategorySQLQueryList = databaseHandler.convertPythonListToSQLQueryList(getTopAppsFromDownloadedJSONs())
sqlStatement = "SELECT a.`id`, a.`app_pkg_name` FROM `appdata` a, `appurls` url WHERE a.`app_pkg_name` = url.`app_pkg_name` AND url.`perm_extracted` = 1 AND a.`app_pkg_name` IN ("+appCategorySQLQueryList+");"
return generateAppDict(dbHandle,sqlStatement)
def getNumberApps(dbHandle, appCategoryList):
appCategorySQLQueryList = databaseHandler.convertPythonListToSQLQueryList(
appCategoryList)
sqlStatement = "SELECT a.`id`, a.`app_pkg_name`, cat.`name` FROM `appdata` a, `appurls` url, `appcategories` cat WHERE a.`app_pkg_name` = url.`app_pkg_name` AND url.`perm_extracted` = 1 AND a.`app_category_id` = cat.`id` ORDER BY RAND() LIMIT " + appCategoryList[
0] + ";"
return generateAppDict(dbHandle, sqlStatement)