entropy.services.ugc.interfaces

35 36 SQL_TABLES = { 37 'entropy_base': """ 38 CREATE TABLE `entropy_base` ( 39 `idkey` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, 40 `key` VARCHAR( 255 ) collate utf8_bin NOT NULL, 41 KEY `key` (`key`) 42 ); 43 """, 44 'entropy_votes': """ 45 CREATE TABLE `entropy_votes` ( 46 `idvote` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, 47 `idkey` INT UNSIGNED NOT NULL, 48 `userid` INT UNSIGNED NOT NULL, 49 `vdate` DATE NOT NULL, 50 `vote` TINYINT NOT NULL, 51 `ts` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, 52 FOREIGN KEY (`idkey`) REFERENCES `entropy_base` (`idkey`) 53 ); 54 """, 55 'entropy_user_scores': """ 56 CREATE TABLE `entropy_user_scores` ( 57 `userid` INT UNSIGNED NOT NULL PRIMARY KEY, 58 `score` INT UNSIGNED NOT NULL DEFAULT 0 59 ); 60 """, 61 'entropy_downloads': """ 62 CREATE TABLE `entropy_downloads` ( 63 `iddownload` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, 64 `idkey` INT UNSIGNED NOT NULL, 65 `ddate` DATE NOT NULL, 66 `count` INT UNSIGNED NULL DEFAULT '0', 67 KEY `idkey` (`idkey`,`ddate`), 68 KEY `idkey_2` (`idkey`), 69 FOREIGN KEY (`idkey`) REFERENCES `entropy_base` (`idkey`) 70 ); 71 """, 72 'entropy_downloads_data': """ 73 CREATE TABLE `entropy_downloads_data` ( 74 `iddownload` INT UNSIGNED NOT NULL, 75 `ip_address` VARCHAR(40) NULL DEFAULT '', 76 `entropy_ip_locations_id` INT UNSIGNED NULL DEFAULT 0, 77 FOREIGN KEY (`iddownload`) REFERENCES `entropy_downloads` (`iddownload`) 78 ); 79 """, 80 'entropy_docs': """ 81 CREATE TABLE `entropy_docs` ( 82 `iddoc` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, 83 `idkey` INT UNSIGNED NOT NULL, 84 `userid` INT UNSIGNED NOT NULL, 85 `username` VARCHAR( 255 ), 86 `iddoctype` TINYINT NOT NULL, 87 `ddata` TEXT NOT NULL, 88 `title` VARCHAR( 512 ), 89 `description` VARCHAR( 4000 ), 90 `ts` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, 91 KEY `idkey` (`idkey`), 92 KEY `userid` (`userid`), 93 KEY `idkey_2` (`idkey`,`userid`,`iddoctype`), 94 KEY `title` (`title`(333)), 95 KEY `description` (`description`(333)), 96 FOREIGN KEY (`idkey`) REFERENCES `entropy_base` (`idkey`) 97 ); 98 """, 99 'entropy_doctypes': """ 100 CREATE TABLE `entropy_doctypes` ( 101 `iddoctype` TINYINT NOT NULL PRIMARY KEY, 102 `description` TEXT NOT NULL 103 ); 104 """, 105 'entropy_docs_keywords': """ 106 CREATE TABLE `entropy_docs_keywords` ( 107 `iddoc` INT UNSIGNED NOT NULL, 108 `keyword` VARCHAR( 100 ), 109 KEY `keyword` (`keyword`), 110 FOREIGN KEY (`iddoc`) REFERENCES `entropy_docs` (`iddoc`) 111 ); 112 """, 113 'entropy_distribution_usage': """ 114 CREATE TABLE `entropy_distribution_usage` ( 115 `entropy_distribution_usage_id` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, 116 `entropy_branches_id` INT NOT NULL, 117 `entropy_release_strings_id` INT NOT NULL, 118 `ts` TIMESTAMP ON UPDATE CURRENT_TIMESTAMP DEFAULT CURRENT_TIMESTAMP, 119 `ip_address` VARCHAR( 15 ), 120 `entropy_ip_locations_id` INT UNSIGNED NULL DEFAULT 0, 121 `creation_date` DATETIME DEFAULT NULL, 122 `hits` INT UNSIGNED NULL DEFAULT 0, 123 FOREIGN KEY (`entropy_branches_id`) REFERENCES `entropy_branches` (`entropy_branches_id`), 124 FOREIGN KEY (`entropy_release_strings_id`) REFERENCES `entropy_release_strings` (`entropy_release_strings_id`), 125 KEY `ip_address` (`ip_address`), 126 KEY `entropy_ip_locations_id` (`entropy_ip_locations_id`) 127 ); 128 """, 129 'entropy_hardware_usage': """ 130 CREATE TABLE `entropy_hardware_usage` ( 131 `entropy_hardware_usage_id` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, 132 `entropy_distribution_usage_id` INT UNSIGNED NOT NULL, 133 `entropy_hardware_hash` VARCHAR ( 64 ), 134 FOREIGN KEY (`entropy_distribution_usage_id`) REFERENCES `entropy_distribution_usage` (`entropy_distribution_usage_id`) 135 ); 136 """, 137 #'entropy_hardware_store': """ 138 # CREATE TABLE `entropy_hardware_store` ( 139 # `entropy_hardware_store_id` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, 140 # `entropy_hardware_usage_id` INT UNSIGNED NOT NULL, 141 # `uname` VARCHAR ( 64 ), 142 # FOREIGN KEY (`entropy_hardware_usage_id`) REFERENCES `entropy_hardware_usage` (`entropy_hardware_usage_id`) 143 # ); 144 #""", 145 'entropy_branches': """ 146 CREATE TABLE `entropy_branches` ( 147 `entropy_branches_id` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, 148 `entropy_branch` VARCHAR( 100 ) 149 ); 150 """, 151 'entropy_release_strings': """ 152 CREATE TABLE `entropy_release_strings` ( 153 `entropy_release_strings_id` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, 154 `release_string` VARCHAR( 255 ) 155 ); 156 """, 157 'entropy_ip_locations': """ 158 CREATE TABLE `entropy_ip_locations` ( 159 `entropy_ip_locations_id` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, 160 `ip_latitude` FLOAT( 8,5 ), 161 `ip_longitude` FLOAT( 8,5 ), 162 KEY `ip_locations_lat_lon` (`ip_latitude`,`ip_longitude`) 163 ); 164 """, 165 } 166 VOTE_RANGE = etpConst['ugc_voterange'] # [1, 2, 3, 4, 5] 167 VIRUS_CHECK_EXEC = '/usr/bin/clamscan' 168 VIRUS_CHECK_ARGS = [] 169 gdata = None 170 YouTube = None 171 YouTubeService = None 172 entropy_docs_title_len = 512 173 entropy_docs_description_len = 4000 174 entropy_docs_keyword_len = 100 175 COMMENTS_SCORE_WEIGHT = 5 176 DOCS_SCORE_WEIGHT = 10 177 VOTES_SCORE_WEIGHT = 2 178 STATS_MAP = { 179 'installer': "installer", 180 } 181 182 ''' 183 dependencies: 184 dev-python/gdata 185 '''

186 - def __init__(self, connection_data, store_path, store_url = ''):

187 import entropy.dump as dumpTools 188 import entropy.tools as entropyTools 189 from entropy.misc import EntropyGeoIP 190 self.EntropyGeoIP = EntropyGeoIP 191 self.entropyTools, self.dumpTools = entropyTools, dumpTools 192 self.store_url = store_url 193 self.FLOOD_INTERVAL = 30 194 self.DOC_TYPES = etpConst['ugc_doctypes'].copy() 195 self.UPLOADED_DOC_TYPES = [ 196 self.DOC_TYPES['image'], 197 self.DOC_TYPES['generic_file'] 198 ] 199 RemoteDatabase.__init__(self) 200 self.set_connection_data(connection_data) 201 self.connect() 202 self.initialize_tables() 203 self.initialize_doctypes() 204 self.setup_store_path(store_path) 205 from entropy.core import SystemSettings 206 self.__system_settings = SystemSettings() 207 self.system_name = self.__system_settings['system']['name'] 208 from datetime import datetime 209 self.datetime = datetime 210 try: 211 import gdata 212 import gdata.youtube 213 import gdata.youtube.service 214 self.gdata = gdata 215 self.YouTube = gdata.youtube 216 self.YouTubeService = gdata.youtube.service 217 except ImportError: 218 pass 219 220 self.cached_results = { 221 #'get_ugc_allvotes': (self.get_ugc_allvotes, [], {}, 86400), 222 'get_ugc_alldownloads': (self.get_ugc_alldownloads, [], {}, 86400), 223 #'get_users_scored_count': (self.get_users_scored_count, [], {}, 86400), 224 'get_total_downloads_count': (self.get_total_downloads_count, [], {}, 7200), 225 }

226

227 - def get_current_time(self):

228 return int(time.time())

229

230 - def cache_results(self):

231 for cache_item in self.cached_results: 232 fdata = self.cached_results.get(cache_item) 233 if fdata == None: return 234 func, args, kwargs, exp_time = fdata 235 key = self.get_cache_item_key(cache_item) 236 r = func(*args,**kwargs) 237 self.dumpTools.dumpobj(key, r)

238

239 - def get_cache_item_key(self, cache_item):

240 return os.path.join(etpCache['ugc_srv_cache'],cache_item)

241

242 - def cache_result(self, cache_item, r):

243 if not self.cached_results.get(cache_item): return None 244 key = self.get_cache_item_key(cache_item) 245 self.dumpTools.dumpobj(key, r)

246

247 - def _get_geoip_data_from_ip_address(self, ip_address):

248 geoip_dbpath = self.connection_data.get('geoip_dbpath','') 249 if os.path.isfile(geoip_dbpath) and os.access(geoip_dbpath,os.R_OK): 250 try: 251 geo = self.EntropyGeoIP(geoip_dbpath) 252 return geo.get_geoip_record_from_ip(ip_address) 253 except: # lame, but I don't know what exceptions are thrown 254 pass

255 256 # expired get_ugc_alldownloads 0 86400 1228577077

257 - def get_cached_result(self, cache_item):

258 fdata = self.cached_results.get(cache_item) 259 if fdata == None: return None 260 func, args, kwargs, exp_time = fdata 261 262 key = self.get_cache_item_key(cache_item) 263 cur_time = self.get_current_time() 264 cache_time = self.dumpTools.getobjmtime(key) 265 if (cache_time + exp_time) < cur_time: 266 # expired 267 return None 268 return self.dumpTools.loadobj(key)

269

270 - def setup_store_path(self, path):

271 path = os.path.realpath(path) 272 if not os.path.isabs(path): 273 raise PermissionDenied('PermissionDenied: %s' % (_("not a valid directory path"),)) 274 if not os.path.isdir(path): 275 try: 276 os.makedirs(path) 277 except OSError, e: 278 raise PermissionDenied('PermissionDenied: %s' % (e,)) 279 if etpConst['entropygid'] != None: 280 const_setup_perms(path,etpConst['entropygid']) 281 self.STORE_PATH = path

282

283 - def initialize_tables(self):

284 notable = False 285 for table in self.SQL_TABLES: 286 if self.table_exists(table): 287 continue 288 notable = True 289 self.execute_script(self.SQL_TABLES[table]) 290 if notable: 291 self.commit()

292

293 - def initialize_doctypes(self):

294 for mydoctype in self.DOC_TYPES: 295 if self.is_iddoctype_available(self.DOC_TYPES[mydoctype]): 296 continue 297 self.insert_iddoctype(self.DOC_TYPES[mydoctype],mydoctype)

298

299 - def is_iddoctype_available(self, iddoctype):

300 self.check_connection() 301 rows = self.execute_query('SELECT `iddoctype` FROM entropy_doctypes WHERE `iddoctype` = %s', (iddoctype,)) 302 if rows: 303 return True 304 return False

305

306 - def is_pkgkey_available(self, key):

307 self.check_connection() 308 rows = self.execute_query('SELECT `idkey` FROM entropy_base WHERE `key` = %s', (key,)) 309 if rows: 310 return True 311 return False

312

313 - def is_iddoc_available(self, iddoc):

314 self.check_connection() 315 rows = self.execute_query('SELECT `iddoc` FROM entropy_docs WHERE `iddoc` = %s', (iddoc,)) 316 if rows: 317 return True 318 return False

319

320 - def insert_iddoctype(self, iddoctype, description, do_commit = False):

321 self.check_connection() 322 self.execute_query('INSERT INTO entropy_doctypes VALUES (%s,%s)', (iddoctype,description,)) 323 if do_commit: self.commit()

324

325 - def insert_pkgkey(self, key, do_commit = False):

326 self.check_connection() 327 self.execute_query('INSERT INTO entropy_base VALUES (%s,%s)', (None,key,)) 328 myid = self.lastrowid() 329 if do_commit: self.commit() 330 return myid

331

332 - def insert_download(self, key, ddate, count = 0, do_commit = False):

333 self.check_connection() 334 idkey = self.handle_pkgkey(key) 335 self.execute_query('INSERT INTO entropy_downloads VALUES (%s,%s,%s,%s)', (None,idkey,ddate,count)) 336 myid = self.lastrowid() 337 if do_commit: self.commit() 338 return myid

339

340 - def insert_entropy_branch(self, branch, do_commit = False):

341 self.check_connection() 342 self.execute_query('INSERT INTO entropy_branches VALUES (%s,%s)', (None,branch,)) 343 myid = self.lastrowid() 344 if do_commit: self.commit() 345 return myid

346

347 - def insert_entropy_release_string(self, release_string, do_commit = False):

348 self.check_connection() 349 self.execute_query('INSERT INTO entropy_release_strings VALUES (%s,%s)', (None,release_string,)) 350 myid = self.lastrowid() 351 if do_commit: self.commit() 352 return myid

353

354 - def insert_entropy_ip_locations_id(self, ip_latitude, ip_longitude, do_commit = False):

355 self.check_connection() 356 self.execute_query('INSERT INTO entropy_ip_locations VALUES (%s,%s,%s)', (None,ip_latitude,ip_longitude,)) 357 myid = self.lastrowid() 358 if do_commit: self.commit() 359 return myid

360

361 - def handle_entropy_ip_locations_id(self, ip_addr):

362 entropy_ip_locations_id = 0 363 geo_data = self._get_geoip_data_from_ip_address(ip_addr) 364 if isinstance(geo_data,dict): 365 ip_lat = geo_data.get('latitude') 366 ip_long = geo_data.get('longitude') 367 if isinstance(ip_lat,float) and isinstance(ip_long,float): 368 ip_lat = round(ip_lat,5) 369 ip_long = round(ip_long,5) 370 entropy_ip_locations_id = self.get_entropy_ip_locations_id(ip_lat, ip_long) 371 if entropy_ip_locations_id == -1: 372 entropy_ip_locations_id = self.insert_entropy_ip_locations_id(ip_lat, ip_long) 373 return entropy_ip_locations_id

374

375 - def update_download(self, iddownload, do_commit = False):

376 self.check_connection() 377 self.execute_query('UPDATE entropy_downloads SET `count` = `count`+1 WHERE `iddownload` = %s', (iddownload,)) 378 if do_commit: self.commit() 379 return iddownload

380

381 - def store_download_data(self, iddownloads, ip_addr, do_commit = False):

382 entropy_ip_locations_id = self.handle_entropy_ip_locations_id(ip_addr) 383 mydata = [(x,ip_addr,entropy_ip_locations_id,) for x in iddownloads] 384 self.execute_many('INSERT INTO entropy_downloads_data VALUES (%s,%s,%s)', mydata) 385 if do_commit: self.commit()

386

387 - def get_date(self):

388 mytime = time.time() 389 mydate = self.datetime.fromtimestamp(mytime) 390 mydate = self.datetime(mydate.year,mydate.month,mydate.day) 391 return mydate

392

393 - def get_datetime(self):

394 mytime = time.time() 395 mydate = self.datetime.fromtimestamp(mytime) 396 mydate = self.datetime(mydate.year,mydate.month,mydate.day,mydate.hour,mydate.minute,mydate.second) 397 return mydate

398

399 - def get_iddownload(self, key, ddate):

400 self.check_connection() 401 idkey = self.handle_pkgkey(key) 402 self.execute_query('SELECT `iddownload` FROM entropy_downloads WHERE `idkey` = %s AND `ddate` = %s', (idkey,ddate,)) 403 data = self.fetchone() 404 if data: 405 return data['iddownload'] 406 return -1

407

408 - def get_idkey(self, key):

409 self.check_connection() 410 self.execute_query('SELECT `idkey` FROM entropy_base WHERE `key` = %s', (key,)) 411 data = self.fetchone() 412 if data: return data['idkey'] 413 return -1

414

415 - def get_iddoctype(self, iddoc):

416 self.check_connection() 417 self.execute_query('SELECT `iddoctype` FROM entropy_docs WHERE `iddoc` = %s', (iddoc,)) 418 data = self.fetchone() 419 if data: return data['iddoctype'] 420 return -1

421

422 - def get_entropy_branches_id(self, branch):

423 self.check_connection() 424 self.execute_query('SELECT `entropy_branches_id` FROM entropy_branches WHERE `entropy_branch` = %s', (branch,)) 425 data = self.fetchone() 426 if data: return data['entropy_branches_id'] 427 return -1

428

429 - def get_entropy_release_strings_id(self, release_string):

430 self.check_connection() 431 self.execute_query('SELECT `entropy_release_strings_id` FROM entropy_release_strings WHERE `release_string` = %s', (release_string,)) 432 data = self.fetchone() 433 if data: return data['entropy_release_strings_id'] 434 return -1

435

436 - def get_entropy_ip_locations_id(self, ip_latitude, ip_longitude):

437 self.check_connection() 438 self.execute_query(""" 439 SELECT `entropy_ip_locations_id` FROM 440 entropy_ip_locations WHERE 441 `ip_latitude` = %s AND `ip_longitude` = %s""", (ip_latitude,ip_longitude,)) 442 data = self.fetchone() 443 if data: return data['entropy_ip_locations_id'] 444 return -1

445

446 - def get_pkgkey(self, idkey):

447 self.check_connection() 448 self.execute_query('SELECT `key` FROM entropy_base WHERE `idkey` = %s', (idkey,)) 449 data = self.fetchone() 450 if data: return data['key']

451

452 - def get_ugc_metadata(self, pkgkey):

453 self.check_connection() 454 metadata = { 455 'vote': 0.0, 456 'downloads': 0, 457 } 458 self.execute_query('SELECT * FROM entropy_docs,entropy_base WHERE entropy_base.`idkey` = entropy_docs.`idkey` AND entropy_base.`key` = %s', (pkgkey,)) 459 metadata['docs'] = self.fetchall() 460 for mydict in metadata['docs']: 461 mydict = self._get_ugc_extra_metadata(mydict) 462 metadata['vote'] = self.get_ugc_vote(pkgkey) 463 metadata['downloads'] = self.get_ugc_downloads(pkgkey) 464 return metadata

465

466 - def get_ugc_keywords(self, iddoc):

467 self.execute_query('SELECT `keyword` FROM entropy_docs_keywords WHERE `iddoc` = %s order by `keyword`', (iddoc,)) 468 data = self.fetchall() 469 if not data: return [] 470 mykeys = [] 471 for mydict in data: 472 if not mydict.has_key('keyword'): 473 continue 474 mykeys.append(mydict['keyword']) 475 return mykeys

476

477 - def get_ugc_metadata_doctypes(self, pkgkey, typeslist):

478 self.check_connection() 479 metadata = [] 480 self.execute_query(""" 481 SELECT * FROM entropy_docs,entropy_base WHERE 482 entropy_docs.`idkey` = entropy_base.`idkey` AND 483 entropy_base.`key` = %s AND 484 entropy_docs.`iddoctype` IN %s 485 ORDER BY entropy_docs.`ts` ASC""", (pkgkey,typeslist,)) 486 metadata = self.fetchall() 487 for mydict in metadata: 488 mydict = self._get_ugc_extra_metadata(mydict) 489 return metadata

490

491 - def get_ugc_metadata_doctypes_by_identifiers(self, identifiers, typeslist):

492 self.check_connection() 493 identifiers = list(identifiers) 494 if len(identifiers) < 2: 495 identifiers += [0] 496 typeslist = list(typeslist) 497 if len(typeslist) < 2: 498 typeslist += [0] 499 self.execute_query('SELECT * FROM entropy_docs WHERE `iddoc` IN %s AND `iddoctype` IN %s', (identifiers,typeslist,)) 500 metadata = self.fetchall() 501 for mydict in metadata: 502 mydict = self._get_ugc_extra_metadata(mydict) 503 return metadata

504

505 - def get_ugc_metadata_by_identifiers(self, identifiers):

506 self.check_connection() 507 identifiers = list(identifiers) 508 if len(identifiers) < 2: 509 identifiers += [0] 510 self.execute_query('SELECT * FROM entropy_docs WHERE `iddoc` IN %s', (identifiers,)) 511 metadata = self.fetchall() 512 for mydict in metadata: 513 mydict = self._get_ugc_extra_metadata(mydict) 514 return metadata

515

516 - def _get_ugc_extra_metadata(self, mydict):

517 mydict['store_url'] = None 518 mydict['keywords'] = self.get_ugc_keywords(mydict['iddoc']) 519 if mydict.has_key("key"): mydict['pkgkey'] = mydict['key'] 520 else: mydict['pkgkey'] = self.get_pkgkey(mydict['idkey']) 521 # for binary files, get size too 522 mydict['size'] = 0 523 if mydict['iddoctype'] in self.UPLOADED_DOC_TYPES: 524 myfilename = mydict['ddata'] 525 if not isinstance(myfilename,basestring): 526 myfilename = myfilename.tostring() 527 mypath = os.path.join(self.STORE_PATH,myfilename) 528 if os.path.isfile(mypath) and os.access(mypath,os.R_OK): 529 try: 530 mydict['size'] = self.entropyTools.get_file_size(mypath) 531 except OSError: 532 pass 533 mydict['store_url'] = os.path.join(self.store_url,myfilename) 534 else: 535 mydata = mydict['ddata'] 536 if not isinstance(mydata,basestring): 537 mydata = mydata.tostring() 538 try: 539 mydict['size'] = len(mydata) 540 except: 541 pass 542 return mydict

543

544 - def get_ugc_vote(self, pkgkey):

545 self.check_connection() 546 vote = 0.0 547 self.execute_query(""" 548 SELECT avg(entropy_votes.`vote`) as avg_vote FROM entropy_votes,entropy_base WHERE 549 entropy_base.`key` = %s AND 550 entropy_base.idkey = entropy_votes.idkey""", (pkgkey,)) 551 data = self.fetchone() 552 if isinstance(data,dict): 553 if data.get('avg_vote'): 554 return data['avg_vote'] 555 return vote

556

557 - def get_ugc_allvotes(self):

558 559 # cached? 560 cache_item = 'get_ugc_allvotes' 561 cached = self.get_cached_result(cache_item) 562 if cached != None: return cached 563 564 self.check_connection() 565 vote_data = {} 566 self.execute_query(""" 567 SELECT entropy_base.`key` as `vkey`,avg(entropy_votes.vote) as `avg_vote` FROM 568 entropy_votes,entropy_base WHERE 569 entropy_votes.`idkey` = entropy_base.`idkey` GROUP BY entropy_base.`key`""") 570 data = self.fetchall() 571 for d_dict in data: 572 vote_data[d_dict['vkey']] = d_dict['avg_vote'] 573 574 # do cache 575 self.cache_result(cache_item, vote_data) 576 return vote_data

577

578 - def get_ugc_downloads(self, pkgkey):

579 self.check_connection() 580 downloads = 0 581 self.execute_query(""" 582 SELECT SQL_CACHE sum(entropy_downloads.`count`) as `tot_downloads` FROM 583 entropy_downloads,entropy_base WHERE entropy_base.key = %s AND 584 entropy_base.idkey = entropy_downloads.idkey""", (pkgkey,)) 585 data = self.fetchone() 586 if data['tot_downloads'] != None: 587 downloads = data['tot_downloads'] 588 return downloads

589

590 - def get_ugc_alldownloads(self):

591 # cached? 592 cache_item = 'get_ugc_alldownloads' 593 cached = self.get_cached_result(cache_item) 594 if cached != None: return cached 595 596 self.check_connection() 597 down_data = {} 598 self.execute_query(""" 599 SELECT SQL_CACHE entropy_base.`key` as `vkey`,sum(entropy_downloads.`count`) as `tot_downloads` FROM 600 entropy_downloads,entropy_base WHERE 601 entropy_downloads.`idkey` = entropy_base.`idkey` GROUP BY entropy_base.`idkey`""") 602 data = self.fetchall() 603 for d_dict in data: 604 down_data[d_dict['vkey']] = d_dict['tot_downloads'] 605 606 # do cache 607 self.cache_result(cache_item, down_data) 608 return down_data

609

610 - def get_iddoc_userid(self, iddoc):

611 self.check_connection() 612 self.execute_query('SELECT `userid` FROM entropy_docs WHERE `iddoc` = %s', (iddoc,)) 613 data = self.fetchone() 614 if not data: 615 return None 616 elif not data.has_key('userid'): 617 return None 618 return data['userid']

619

620 - def get_total_comments_count(self):

621 self.check_connection() 622 self.execute_query('SELECT count(`iddoc`) as comments FROM entropy_docs WHERE `iddoctype` = %s', (self.DOC_TYPES['comments'],)) 623 data = self.fetchone() 624 if isinstance(data,dict): 625 if data['comments']: return data['comments'] 626 return 0

627

628 - def get_total_documents_count(self):

629 self.check_connection() 630 self.execute_query('SELECT count(`iddoc`) as comments FROM entropy_docs WHERE `iddoctype` != %s', (self.DOC_TYPES['comments'],)) 631 data = self.fetchone() 632 if isinstance(data,dict): 633 if data['comments']: return data['comments'] 634 return 0

635

636 - def get_total_votes_count(self):

637 self.check_connection() 638 self.execute_query('SELECT count(`idvote`) as votes FROM entropy_votes') 639 data = self.fetchone() 640 if isinstance(data,dict): 641 if data['votes']: return data['votes'] 642 return 0

643

644 - def get_total_downloads_count(self):

645 646 # cached? 647 cache_item = 'get_total_downloads_count' 648 cached = self.get_cached_result(cache_item) 649 if cached != None: return cached 650 651 self.check_connection() 652 self.execute_query('SELECT SQL_CACHE sum(entropy_downloads.`count`) as downloads FROM entropy_downloads') 653 data = self.fetchone() 654 r = 0 655 if isinstance(data,dict): 656 if data['downloads']: r = int(data['downloads']) 657 658 # do cache 659 self.cache_result(cache_item, r) 660 return r

661

662 - def get_user_score_ranking(self, userid):

663 self.check_connection() 664 self.execute_query('SET @row = 0') 665 self.execute_query(""" 666 SELECT Row, col_a FROM (SELECT @row := @row + 1 AS Row, userid AS col_a FROM 667 entropy_user_scores ORDER BY score DESC) As derived1 WHERE col_a = %s""", (userid,)) 668 data = self.fetchone() 669 if isinstance(data,dict): 670 if data.get('Row'): return data['Row'] 671 return 0

672

673 - def get_users_scored_count(self):

674 675 # cached? 676 cache_item = 'get_users_scored_count' 677 cached = self.get_cached_result(cache_item) 678 if cached != None: return cached 679 680 self.check_connection() 681 self.execute_query('SELECT SQL_CACHE count(`userid`) as mycount FROM entropy_user_scores') 682 data = self.fetchone() 683 r = 0 684 if isinstance(data,dict): 685 if data.get('mycount'): r = data['mycount'] 686 687 # do cache 688 self.cache_result(cache_item, r) 689 return r

690

691 - def is_user_score_available(self, userid):

692 self.check_connection() 693 rows = self.execute_query('SELECT `userid` FROM entropy_user_scores WHERE `userid` = %s', (userid,)) 694 if rows: 695 return True 696 return False

697

698 - def calculate_user_score(self, userid):

699 comments = self.get_user_comments_count(userid) 700 docs = self.get_user_docs_count(userid) 701 votes = self.get_user_votes_count(userid) 702 return (comments*self.COMMENTS_SCORE_WEIGHT)+(docs*self.DOCS_SCORE_WEIGHT)+(votes*self.VOTES_SCORE_WEIGHT)

703

704 - def update_user_score(self, userid):

705 self.check_connection() 706 avail = self.is_user_score_available(userid) 707 myscore = self.calculate_user_score(userid) 708 if avail: 709 self.execute_query('UPDATE entropy_user_scores SET score = %s WHERE `userid` = %s', (myscore,userid,)) 710 else: 711 self.execute_query('INSERT INTO entropy_user_scores VALUES (%s,%s)', (userid,myscore,)) 712 return myscore

713

714 - def get_user_score(self, userid):

715 self.check_connection() 716 myscore = None 717 self.execute_query('SELECT score FROM entropy_user_scores WHERE userid = %s',(userid,)) 718 data = self.fetchone() 719 if isinstance(data,dict): 720 if data.has_key('score'): myscore = data.get('score') 721 if myscore == None: myscore = self.update_user_score(userid) 722 return myscore

723

724 - def get_users_score_ranking(self, offset = 0, count = 0):

725 self.check_connection() 726 limit_string = '' 727 if count: 728 limit_string = ' LIMIT %s,%s' % (offset,count,) 729 self.execute_query('SELECT SQL_CALC_FOUND_ROWS *,userid,score FROM entropy_user_scores ORDER BY score DESC'+limit_string) 730 data = self.fetchall() 731 732 self.execute_query('SELECT FOUND_ROWS() as count') 733 rdata = self.fetchone() 734 found_rows = 0 735 if isinstance(rdata,dict): 736 if rdata.has_key('count'): 737 found_rows = rdata.get('count') 738 739 return found_rows, data

740

741 - def get_user_votes_average(self, userid):

742 self.check_connection() 743 self.execute_query('SELECT avg(`vote`) as vote_avg FROM entropy_votes WHERE `userid` = %s', (userid,)) 744 data = self.fetchone() 745 if isinstance(data,dict): 746 if data['vote_avg']: return round(float(data['vote_avg']),2) 747 return 0.0

748

749 - def get_user_alldocs(self, userid):

750 self.check_connection() 751 self.execute_query(""" 752 SELECT * FROM entropy_docs,entropy_base WHERE 753 entropy_docs.`userid` = %s AND 754 entropy_base.idkey = entropy_docs.idkey ORDER BY entropy_base.`key`""", (userid,)) 755 return self.fetchall()

756

757 - def get_user_docs(self, userid):

758 return self.get_user_generic_doctype(userid, self.DOC_TYPES['comments'], doctype_sql_cmp = "!=")

759

760 - def get_user_comments(self, userid):

761 return self.get_user_generic_doctype(userid, self.DOC_TYPES['comments'], doctype_sql_cmp = "=")

762

763 - def get_user_votes(self, userid):

764 self.check_connection() 765 self.execute_query('SELECT * FROM entropy_votes WHERE `userid` = %s', (userid,)) 766 return self.fetchall()

767

768 - def get_user_generic_doctype(self, userid, doctype, doctype_sql_cmp = "="):

769 self.check_connection() 770 self.execute_query('SELECT * FROM entropy_docs WHERE `userid` = %s AND `iddoctype` '+doctype_sql_cmp+' %s', (userid,doctype,)) 771 return self.fetchall()

772

773 - def get_user_generic_doctype_count(self, userid, doctype, doctype_sql_cmp = "="):

774 self.check_connection() 775 self.execute_query('SELECT count(`iddoc`) as docs FROM entropy_docs WHERE `userid` = %s AND `iddoctype` '+doctype_sql_cmp+' %s', (userid,doctype,)) 776 data = self.fetchone() 777 if isinstance(data,dict): 778 if data['docs']: return data['docs'] 779 return 0

780

781 - def get_user_comments_count(self, userid):

782 return self.get_user_generic_doctype_count(userid, self.DOC_TYPES['comments'])

783

784 - def get_user_docs_count(self, userid):

785 return self.get_user_generic_doctype_count(userid, self.DOC_TYPES['comments'], doctype_sql_cmp = "!=")

786

787 - def get_user_images_count(self, userid):

788 return self.get_user_generic_doctype_count(userid, self.DOC_TYPES['image'])

789

790 - def get_user_files_count(self, userid):

791 return self.get_user_generic_doctype_count(userid, self.DOC_TYPES['generic_file'])

792

793 - def get_user_yt_videos_count(self, userid):

794 return self.get_user_generic_doctype_count(userid, self.DOC_TYPES['youtube_video'])

795

796 - def get_user_votes_count(self, userid):

797 self.check_connection() 798 self.execute_query('SELECT count(`idvote`) as votes FROM entropy_votes WHERE `userid` = %s', (userid,)) 799 data = self.fetchone() 800 if isinstance(data,dict): 801 if data['votes']: return data['votes'] 802 return 0

803

804 - def get_user_stats(self, userid):

805 mydict = {} 806 mydict['comments'] = self.get_user_comments_count(userid) 807 mydict['docs'] = self.get_user_docs_count(userid) 808 mydict['images'] = self.get_user_images_count(userid) 809 mydict['files'] = self.get_user_files_count(userid) 810 mydict['yt_videos'] = self.get_user_yt_videos_count(userid) 811 mydict['votes'] = self.get_user_votes_count(userid) 812 mydict['votes_avg'] = self.get_user_votes_average(userid) 813 mydict['total_docs'] = mydict['comments'] + mydict['docs'] 814 mydict['score'] = self.get_user_score(userid) 815 mydict['ranking'] = self.get_user_score_ranking(userid) 816 return mydict

817

818 - def get_distribution_stats(self):

819 self.check_connection() 820 mydict = {} 821 mydict['comments'] = self.get_total_comments_count() 822 mydict['documents'] = self.get_total_documents_count() 823 mydict['votes'] = self.get_total_votes_count() 824 mydict['downloads'] = self.get_total_downloads_count() 825 return mydict

826

827 - def search_pkgkey_items(self, pkgkey_string, iddoctypes = None, results_offset = 0, results_limit = 30, order_by = None):

828 self.check_connection() 829 830 if iddoctypes == None: 831 iddoctypes = [self.DOC_TYPES[x] for x in self.DOC_TYPES] 832 iddoctypes = "("+', '.join([str(self.DOC_TYPES[x]) for x in self.DOC_TYPES])+")" 833 myterm = "%"+pkgkey_string+"%" 834 835 search_params = [myterm,results_offset,results_limit] 836 837 order_by_string = '' 838 if order_by == "key": 839 order_by_string = 'ORDER BY entropy_base.`key`' 840 elif order_by == "username": 841 order_by_string = 'ORDER BY entropy_docs.`username`' 842 elif order_by == "vote": 843 order_by_string = 'ORDER BY avg_vote DESC' 844 elif order_by == "downloads": 845 order_by_string = 'ORDER BY tot_downloads DESC' 846 847 self.execute_query(""" 848 SELECT SQL_CALC_FOUND_ROWS *, avg(entropy_votes.`vote`) as avg_vote, 849 sum(entropy_downloads.`count`) as `tot_downloads`, 850 entropy_user_scores.`score` as `score` FROM 851 entropy_docs,entropy_base,entropy_votes,entropy_downloads,entropy_user_scores WHERE 852 entropy_base.`key` LIKE %s AND 853 entropy_docs.`iddoctype` IN """+iddoctypes+""" AND 854 entropy_docs.`idkey` = entropy_base.`idkey` AND 855 entropy_votes.`idkey` = entropy_base.`idkey` AND 856 entropy_downloads.`idkey` = entropy_base.`idkey` AND 857 entropy_docs.`userid` = entropy_user_scores.`userid` 858 GROUP BY entropy_docs.`iddoc` """+order_by_string+""" LIMIT %s,%s""", search_params) 859 860 results = self.fetchall() 861 self.execute_query('SELECT FOUND_ROWS() as count') 862 data = self.fetchone() 863 found_rows = 0 864 if isinstance(data,dict): 865 if data.has_key('count'): 866 found_rows = data.get('count') 867 return results, found_rows

868

869 - def search_username_items(self, pkgkey_string, iddoctypes = None, results_offset = 0, results_limit = 30, order_by = None):

870 self.check_connection() 871 872 if iddoctypes == None: 873 iddoctypes = [self.DOC_TYPES[x] for x in self.DOC_TYPES] 874 iddoctypes = "("+', '.join([str(self.DOC_TYPES[x]) for x in self.DOC_TYPES])+")" 875 myterm = "%"+pkgkey_string+"%" 876 877 search_params = [myterm,results_offset,results_limit] 878 879 order_by_string = '' 880 if order_by == "key": 881 order_by_string = 'ORDER BY entropy_base.`key`' 882 elif order_by == "username": 883 order_by_string = 'ORDER BY entropy_docs.`username`' 884 elif order_by == "vote": 885 order_by_string = 'ORDER BY avg_vote DESC' 886 elif order_by == "downloads": 887 order_by_string = 'ORDER BY tot_downloads DESC' 888 889 self.execute_query(""" 890 SELECT SQL_CALC_FOUND_ROWS *, avg(entropy_votes.`vote`) as avg_vote, 891 sum(entropy_downloads.`count`) as `tot_downloads`, 892 entropy_user_scores.`score` as `score` FROM 893 entropy_docs,entropy_base,entropy_votes,entropy_downloads,entropy_user_scores WHERE 894 entropy_docs.`username` LIKE %s AND entropy_docs.`iddoctype` IN """+iddoctypes+""" AND 895 entropy_docs.`idkey` = entropy_base.`idkey` AND 896 entropy_votes.`idkey` = entropy_base.`idkey` AND 897 entropy_downloads.`idkey` = entropy_base.`idkey` AND 898 entropy_docs.`userid` = entropy_user_scores.`userid` 899 GROUP BY entropy_docs.`iddoc` """+order_by_string+""" LIMIT %s,%s""", search_params) 900 901 results = self.fetchall() 902 self.execute_query('SELECT FOUND_ROWS() as count') 903 data = self.fetchone() 904 found_rows = 0 905 if isinstance(data,dict): 906 if data.has_key('count'): 907 found_rows = data.get('count') 908 return results, found_rows

909

910 - def search_content_items(self, pkgkey_string, iddoctypes = None, results_offset = 0, results_limit = 30, order_by = None):

911 self.check_connection() 912 913 if iddoctypes == None: 914 iddoctypes = [self.DOC_TYPES[x] for x in self.DOC_TYPES] 915 iddoctypes = "("+', '.join([str(self.DOC_TYPES[x]) for x in self.DOC_TYPES])+")" 916 myterm = "%"+pkgkey_string+"%" 917 search_params = [myterm,myterm,myterm,results_offset,results_limit] 918 919 order_by_string = '' 920 if order_by == "key": 921 order_by_string = 'ORDER BY entropy_base.`key`' 922 elif order_by == "username": 923 order_by_string = 'ORDER BY entropy_docs.`username`' 924 elif order_by == "vote": 925 order_by_string = 'ORDER BY avg_vote DESC' 926 elif order_by == "downloads": 927 order_by_string = 'ORDER BY tot_downloads DESC' 928 929 self.execute_query(""" 930 SELECT SQL_CALC_FOUND_ROWS *, avg(entropy_votes.`vote`) as avg_vote, 931 sum(entropy_downloads.`count`) as `tot_downloads`, 932 entropy_user_scores.`score` as `score` FROM 933 entropy_docs,entropy_base,entropy_votes,entropy_downloads,entropy_user_scores WHERE 934 (entropy_docs.`title` LIKE %s OR entropy_docs.`description` LIKE %s OR entropy_docs.`ddata` LIKE %s) AND 935 entropy_docs.`iddoctype` IN """+iddoctypes+""" AND 936 entropy_docs.`idkey` = entropy_base.`idkey` AND 937 entropy_votes.`idkey` = entropy_base.`idkey` AND 938 entropy_downloads.`idkey` = entropy_base.`idkey` AND 939 entropy_docs.`userid` = entropy_user_scores.`userid` 940 GROUP BY entropy_docs.`iddoc` """+order_by_string+""" LIMIT %s,%s""", search_params) 941 942 results = self.fetchall() 943 self.execute_query('SELECT FOUND_ROWS() as count') 944 data = self.fetchone() 945 found_rows = 0 946 if isinstance(data,dict): 947 if data.has_key('count'): 948 found_rows = data.get('count') 949 return results, found_rows

950

951 - def search_keyword_items(self, keyword_string, iddoctypes = None, results_offset = 0, results_limit = 30, order_by = None):

952 self.check_connection() 953 954 if iddoctypes == None: 955 iddoctypes = [self.DOC_TYPES[x] for x in self.DOC_TYPES] 956 iddoctypes = "("+', '.join([str(self.DOC_TYPES[x]) for x in self.DOC_TYPES])+")" 957 myterm = "%"+keyword_string+"%" 958 959 search_params = [myterm,results_offset,results_limit] 960 961 order_by_string = '' 962 if order_by == "key": 963 order_by_string = 'ORDER BY entropy_base.`key`' 964 elif order_by == "username": 965 order_by_string = 'ORDER BY entropy_docs.`username`' 966 elif order_by == "vote": 967 order_by_string = 'ORDER BY avg_vote DESC' 968 elif order_by == "downloads": 969 order_by_string = 'ORDER BY tot_downloads DESC' 970 971 self.execute_query(""" 972 SELECT SQL_CALC_FOUND_ROWS *, avg(entropy_votes.`vote`) as avg_vote, 973 sum(entropy_downloads.`count`) as `tot_downloads`, 974 entropy_user_scores.`score` as `score` FROM 975 entropy_docs,entropy_base,entropy_docs_keywords,entropy_votes,entropy_downloads,entropy_user_scores WHERE 976 entropy_docs_keywords.`keyword` LIKE %s AND 977 entropy_docs.`iddoctype` IN """+iddoctypes+""" AND 978 entropy_docs.`idkey` = entropy_base.`idkey` AND 979 entropy_docs_keywords.`iddoc` = entropy_docs.`iddoc` AND 980 entropy_votes.`idkey` = entropy_base.`idkey` AND 981 entropy_downloads.`idkey` = entropy_base.`idkey` AND 982 entropy_docs.`userid` = entropy_user_scores.`userid` 983 GROUP BY entropy_docs.`iddoc` """+order_by_string+""" LIMIT %s,%s""", search_params) 984 985 results = self.fetchall() 986 self.execute_query('SELECT FOUND_ROWS() as count') 987 data = self.fetchone() 988 found_rows = 0 989 if isinstance(data,dict): 990 if data.has_key('count'): 991 found_rows = data.get('count') 992 return results, found_rows

993

994 - def search_iddoc_item(self, iddoc_string, iddoctypes = None, results_offset = 0, results_limit = 30, order_by = None):

995 self.check_connection() 996 997 if iddoctypes == None: 998 iddoctypes = [self.DOC_TYPES[x] for x in self.DOC_TYPES] 999 iddoctypes = "("+', '.join([str(self.DOC_TYPES[x]) for x in self.DOC_TYPES])+")" 1000 try: 1001 myterm = int(iddoc_string) 1002 except ValueError: 1003 return [],0 1004 1005 search_params = [myterm,results_offset,results_limit] 1006 1007 order_by_string = '' 1008 if order_by == "key": 1009 order_by_string = 'ORDER BY entropy_base.`key`' 1010 elif order_by == "username": 1011 order_by_string = 'ORDER BY entropy_docs.`username`' 1012 elif order_by == "vote": 1013 order_by_string = 'ORDER BY avg_vote DESC' 1014 elif order_by == "downloads": 1015 order_by_string = 'ORDER BY tot_downloads DESC' 1016 1017 self.execute_query(""" 1018 SELECT SQL_CALC_FOUND_ROWS *, avg(entropy_votes.`vote`) as avg_vote, 1019 sum(entropy_downloads.`count`) as `tot_downloads`, 1020 entropy_user_scores.`score` as `score` FROM 1021 entropy_docs,entropy_base,entropy_votes,entropy_downloads,entropy_user_scores WHERE 1022 entropy_docs.`iddoc` = %s AND 1023 entropy_docs.`iddoctype` IN """+iddoctypes+""" AND 1024 entropy_docs.`idkey` = entropy_base.`idkey` AND 1025 entropy_votes.`idkey` = entropy_base.`idkey` AND 1026 entropy_downloads.`idkey` = entropy_base.`idkey` AND 1027 entropy_docs.`userid` = entropy_user_scores.`userid` 1028 GROUP BY entropy_docs.`iddoc` """+order_by_string+""" LIMIT %s,%s""", search_params) 1029 1030 results = self.fetchall() 1031 self.execute_query('SELECT FOUND_ROWS() as count') 1032 data = self.fetchone() 1033 found_rows = 0 1034 if isinstance(data,dict): 1035 if data.has_key('count'): 1036 found_rows = data.get('count') 1037 return results, found_rows

1038

1039 - def handle_pkgkey(self, key):

1040 idkey = self.get_idkey(key) 1041 if idkey == -1: return self.insert_pkgkey(key) 1042 return idkey

1043

1044 - def insert_flood_control_check(self, userid):

1045 self.check_connection() 1046 self.execute_query('SELECT max(`ts`) as ts FROM entropy_docs WHERE `userid` = %s', (userid,)) 1047 data = self.fetchone() 1048 if not data: 1049 return False 1050 elif not data.has_key('ts'): 1051 return False 1052 elif data['ts'] == None: 1053 return False 1054 delta = self.datetime.fromtimestamp(time.time()) - data['ts'] 1055 if (delta.days == 0) and (delta.seconds <= self.FLOOD_INTERVAL): 1056 return True 1057 return False

1058

1059 - def insert_generic_doc(self, idkey, userid, username, doc_type, data, title, description, keywords, do_commit = False):

1060 self.check_connection() 1061 1062 title = title[:self.entropy_docs_title_len] 1063 description = description[:self.entropy_docs_description_len] 1064 1065 # flood control 1066 flood_risk = self.insert_flood_control_check(userid) 1067 if flood_risk: return 'flooding detected' 1068 1069 self.execute_query('INSERT INTO entropy_docs VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s)',( 1070 None, 1071 idkey, 1072 userid, 1073 username, 1074 doc_type, 1075 data, 1076 title, 1077 description, 1078 None, 1079 ) 1080 ) 1081 if do_commit: self.commit() 1082 iddoc = self.lastrowid() 1083 self.insert_keywords(iddoc, keywords) 1084 self.update_user_score(userid) 1085 return iddoc

1086

1087 - def insert_keywords(self, iddoc, keywords):

1088 keywords = keywords.split(",") 1089 clean_keys = [] 1090 for key in keywords: 1091 try: 1092 key = key.strip().split()[0] 1093 except IndexError: 1094 continue 1095 if not key: continue 1096 if not key.isalnum(): continue 1097 key = key[:self.entropy_docs_keyword_len] 1098 if key in clean_keys: continue 1099 clean_keys.append(key) 1100 1101 if not clean_keys: 1102 return 1103 1104 mydata = [] 1105 for key in clean_keys: 1106 mydata.append((iddoc, key,)) 1107 1108 self.execute_many('INSERT INTO entropy_docs_keywords VALUES (%s,%s)', mydata)

1109

1110 - def remove_keywords(self, iddoc):

1111 self.execute_query('DELETE FROM entropy_docs_keywords WHERE `iddoc` = %s',(iddoc,))

1112

1113 - def insert_comment(self, pkgkey, userid, username, comment, title, keywords, do_commit = False):

1114 self.check_connection() 1115 idkey = self.handle_pkgkey(pkgkey) 1116 iddoc = self.insert_generic_doc(idkey, userid, username, self.DOC_TYPES['comments'], comment, title, '', keywords) 1117 if isinstance(iddoc,basestring): 1118 return False, iddoc 1119 if do_commit: self.commit() 1120 return True, iddoc

1121

1122 - def edit_comment(self, iddoc, new_comment, new_title, new_keywords, do_commit = False):

1123 self.check_connection() 1124 if not self.is_iddoc_available(iddoc): 1125 return False 1126 new_title = new_title[:self.entropy_docs_title_len] 1127 self.execute_query('UPDATE entropy_docs SET `ddata` = %s, `title` = %s WHERE `iddoc` = %s AND `iddoctype` = %s',( 1128 new_comment, 1129 new_title, 1130 iddoc, 1131 self.DOC_TYPES['comments'], 1132 ) 1133 ) 1134 self.remove_keywords(iddoc) 1135 self.insert_keywords(iddoc, new_keywords) 1136 if do_commit: self.commit() 1137 return True, iddoc

1138

1139 - def remove_comment(self, iddoc):

1140 self.check_connection() 1141 userid = self.get_iddoc_userid(iddoc) 1142 self.remove_keywords(iddoc) 1143 self.execute_query('DELETE FROM entropy_docs WHERE `iddoc` = %s AND `iddoctype` = %s',( 1144 iddoc, 1145 self.DOC_TYPES['comments'], 1146 ) 1147 ) 1148 if userid: self.update_user_score(userid) 1149 return True, iddoc

1150 1151 # give a vote to an app

1152 - def do_vote(self, pkgkey, userid, vote, do_commit = False):

1153 self.check_connection() 1154 idkey = self.handle_pkgkey(pkgkey) 1155 vote = int(vote) 1156 if vote not in self.VOTE_RANGE: # weird 1157 vote = 3 # avg? 1158 mydate = self.get_date() 1159 if self.has_user_already_voted(pkgkey, userid): 1160 return False 1161 self.execute_query('INSERT INTO entropy_votes VALUES (%s,%s,%s,%s,%s,%s)',( 1162 None, 1163 idkey, 1164 userid, 1165 mydate, 1166 vote, 1167 None, 1168 ) 1169 ) 1170 if do_commit: self.commit() 1171 self.update_user_score(userid) 1172 return True

1173

1174 - def has_user_already_voted(self, pkgkey, userid):

1175 self.check_connection() 1176 idkey = self.handle_pkgkey(pkgkey) 1177 self.execute_query('SELECT `idvote` FROM entropy_votes WHERE `idkey` = %s AND `userid` = %s', (idkey,userid,)) 1178 data = self.fetchone() 1179 if data: 1180 return True 1181 return False

1182 1183 # icrement +1 download usage for the provided package keys

1184 - def do_downloads(self, pkgkeys, ip_addr = None, do_commit = False):

1185 self.check_connection() 1186 mydate = self.get_date() 1187 iddownloads = set() 1188 for pkgkey in pkgkeys: 1189 iddownload = self.get_iddownload(pkgkey, mydate) 1190 if iddownload == -1: 1191 iddownload = self.insert_download(pkgkey, mydate, count = 1) 1192 else: 1193 self.update_download(iddownload) 1194 if (iddownload > 0) and isinstance(ip_addr,basestring): 1195 iddownloads.add(iddownload) 1196 1197 if iddownloads: self.store_download_data(iddownloads, ip_addr) 1198 if do_commit: self.commit() 1199 return True

1200

1201 - def do_download_stats(self, branch, release_string, hw_hash, pkgkeys, 1202 ip_addr, do_commit = False):

1203 1204 self.check_connection() 1205 branch_id = self.get_entropy_branches_id(branch) 1206 if branch_id == -1: 1207 branch_id = self.insert_entropy_branch(branch) 1208 1209 rel_strings_id = self.get_entropy_release_strings_id(release_string) 1210 if rel_strings_id == -1: 1211 rel_strings_id = self.insert_entropy_release_string(release_string) 1212 1213 self.do_downloads(pkgkeys, ip_addr = ip_addr) 1214 1215 entropy_distribution_usage_id = self.is_user_ip_available_in_entropy_distribution_usage(ip_addr) 1216 1217 hits = 1 1218 if self.STATS_MAP['installer'] in pkgkeys: 1219 hits = 0 1220 if entropy_distribution_usage_id == -1: 1221 entropy_ip_locations_id = self.handle_entropy_ip_locations_id(ip_addr) 1222 self.execute_query('INSERT INTO entropy_distribution_usage VALUES (%s,%s,%s,%s,%s,%s,%s,%s)',( 1223 None, 1224 branch_id, 1225 rel_strings_id, 1226 None, 1227 ip_addr, 1228 entropy_ip_locations_id, 1229 self.get_datetime(), 1230 hits, 1231 ) 1232 ) 1233 entropy_distribution_usage_id = self.lastrowid() 1234 else: 1235 self.execute_query(""" 1236 UPDATE entropy_distribution_usage SET `entropy_branches_id` = %s, 1237 `entropy_release_strings_id` = %s, 1238 `hits` = `hits`+%s 1239 WHERE `entropy_distribution_usage_id` = %s 1240 """,( 1241 branch_id, 1242 rel_strings_id, 1243 hits, 1244 entropy_distribution_usage_id, 1245 ) 1246 ) 1247 1248 # store hardware hash if set 1249 if hw_hash and not \ 1250 self.is_entropy_hardware_usage_stats_available(entropy_distribution_usage_id): 1251 1252 self.do_entropy_hardware_usage_stats(entropy_distribution_usage_id, 1253 hw_hash) 1254 1255 if do_commit: self.commit() 1256 return True

1257

1258 - def do_entropy_hardware_usage_stats(self, entropy_distribution_usage_id, hw_hash):

1259 1260 self.execute_query('INSERT INTO entropy_hardware_usage VALUES (%s,%s,%s)', ( 1261 None, 1262 entropy_distribution_usage_id, 1263 hw_hash, 1264 ) 1265 )

1266

1267 - def is_entropy_hardware_usage_stats_available(self, entropy_distribution_usage_id):

1268 self.check_connection() 1269 self.execute_query('SELECT entropy_hardware_usage_id FROM entropy_hardware_usage WHERE `entropy_distribution_usage_id` = %s',(entropy_distribution_usage_id,)) 1270 data = self.fetchone() 1271 if data: 1272 return True 1273 return False

1274

1275 - def is_user_ip_available_in_entropy_distribution_usage(self, ip_address):

1276 self.check_connection() 1277 self.execute_query('SELECT entropy_distribution_usage_id FROM entropy_distribution_usage WHERE `ip_address` = %s',(ip_address,)) 1278 data = self.fetchone() 1279 if data: 1280 return data['entropy_distribution_usage_id'] 1281 return -1

1282

1283 - def insert_document(self, pkgkey, userid, username, text, title, description, keywords, doc_type = None, do_commit = False):

1284 self.check_connection() 1285 idkey = self.handle_pkgkey(pkgkey) 1286 if doc_type == None: doc_type = self.DOC_TYPES['bbcode_doc'] 1287 iddoc = self.insert_generic_doc(idkey, userid, username, doc_type, text, title, description, keywords) 1288 if isinstance(iddoc,basestring): 1289 return False, iddoc 1290 if do_commit: self.commit() 1291 return True, iddoc

1292

1293 - def edit_document(self, iddoc, new_document, new_title, new_keywords, do_commit = False):

1294 self.check_connection() 1295 if not self.is_iddoc_available(iddoc): 1296 return False 1297 1298 new_title = new_title[:self.entropy_docs_title_len] 1299 1300 self.execute_query('UPDATE entropy_docs SET `ddata` = %s, `title` = %s WHERE `iddoc` = %s AND `iddoctype` = %s',( 1301 new_document, 1302 new_title, 1303 iddoc, 1304 self.DOC_TYPES['bbcode_doc'], 1305 ) 1306 ) 1307 self.remove_keywords(iddoc) 1308 self.insert_keywords(iddoc, new_keywords) 1309 if do_commit: self.commit() 1310 return True, iddoc

1311

1312 - def remove_document(self, iddoc):

1313 self.check_connection() 1314 userid = self.get_iddoc_userid(iddoc) 1315 self.remove_keywords(iddoc) 1316 self.execute_query('DELETE FROM entropy_docs WHERE `iddoc` = %s AND `iddoctype` = %s',( 1317 iddoc, 1318 self.DOC_TYPES['bbcode_doc'], 1319 ) 1320 ) 1321 if userid: self.update_user_score(userid) 1322 return True, iddoc

1323

1324 - def scan_for_viruses(self, filepath):

1325 1326 if not os.access(filepath,os.R_OK): 1327 return False,None 1328 1329 args = [self.VIRUS_CHECK_EXEC] 1330 args += self.VIRUS_CHECK_ARGS 1331 args += [filepath] 1332 f = open("/dev/null","w") 1333 p = subprocess.Popen(args, stdout = f, stderr = f) 1334 rc = p.wait() 1335 f.close() 1336 if rc == 1: 1337 return True,None 1338 return False,None

1339

1340 - def insert_generic_file(self, pkgkey, userid, username, file_path, file_name, doc_type, title, description, keywords):

1341 self.check_connection() 1342 file_path = os.path.realpath(file_path) 1343 1344 # do a virus check? 1345 virus_found, virus_type = self.scan_for_viruses(file_path) 1346 if virus_found: 1347 os.remove(file_path) 1348 return False, None 1349 1350 # flood control 1351 flood_risk = self.insert_flood_control_check(userid) 1352 if flood_risk: return False, 'flooding detected' 1353 1354 # validity check 1355 if doc_type == self.DOC_TYPES['image']: 1356 valid = False 1357 if os.path.isfile(file_path) and os.access(file_path,os.R_OK): 1358 valid = self.entropyTools.is_supported_image_file(file_path) 1359 if not valid: 1360 return False, 'not a valid image' 1361 1362 dest_path = os.path.join(self.STORE_PATH,file_name) 1363 1364 # create dir if not exists 1365 dest_dir = os.path.dirname(dest_path) 1366 if not os.path.isdir(dest_dir): 1367 try: 1368 os.makedirs(dest_dir) 1369 except OSError, e: 1370 raise PermissionDenied('PermissionDenied: %s' % (e,)) 1371 if etpConst['entropygid'] != None: 1372 const_setup_perms(dest_dir,etpConst['entropygid']) 1373 1374 orig_dest_path = dest_path 1375 dcount = 0 1376 while os.path.isfile(dest_path): 1377 dcount += 1 1378 dest_path_name = "%s_%s" % (dcount,os.path.basename(orig_dest_path),) 1379 dest_path = os.path.join(os.path.dirname(orig_dest_path),dest_path_name) 1380 1381 if os.path.dirname(file_path) != dest_dir: 1382 shutil.move(file_path,dest_path) 1383 if etpConst['entropygid'] != None: 1384 try: 1385 const_setup_file(dest_path, etpConst['entropygid'], 0664) 1386 except OSError: 1387 pass 1388 # at least set chmod 1389 try: const_set_chmod(dest_path,0664) 1390 except OSError: pass 1391 1392 title = title[:self.entropy_docs_title_len] 1393 description = description[:self.entropy_docs_description_len] 1394 1395 # now store in db 1396 idkey = self.handle_pkgkey(pkgkey) 1397 self.execute_query('INSERT INTO entropy_docs VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s)',( 1398 None, 1399 idkey, 1400 userid, 1401 username, 1402 doc_type, 1403 file_name, 1404 title, 1405 description, 1406 None, 1407 ) 1408 ) 1409 iddoc = self.lastrowid() 1410 self.insert_keywords(iddoc, keywords) 1411 store_url = os.path.basename(dest_path) 1412 if self.store_url: 1413 store_url = os.path.join(self.store_url,store_url) 1414 self.update_user_score(userid) 1415 return True, (iddoc, store_url)

1416

1417 - def insert_image(self, pkgkey, userid, username, image_path, file_name, title, description, keywords):

1418 return self.insert_generic_file(pkgkey, userid, username, image_path, file_name, self.DOC_TYPES['image'], title, description, keywords)

1419

1420 - def insert_file(self, pkgkey, userid, username, file_path, file_name, title, description, keywords):

1421 return self.insert_generic_file(pkgkey, userid, username, file_path, file_name, self.DOC_TYPES['generic_file'], title, description, keywords)

1422

1423 - def delete_image(self, iddoc):

1424 return self.delete_generic_file(iddoc, self.DOC_TYPES['image'])

1425

1426 - def delete_file(self, iddoc):

1427 return self.delete_generic_file(iddoc, self.DOC_TYPES['generic_file'])

1428

1429 - def delete_generic_file(self, iddoc, doc_type):

1430 self.check_connection() 1431 1432 userid = self.get_iddoc_userid(iddoc) 1433 self.execute_query('SELECT `ddata` FROM entropy_docs WHERE `iddoc` = %s AND `iddoctype` = %s',( 1434 iddoc, 1435 doc_type, 1436 ) 1437 ) 1438 data = self.fetchone() 1439 if data != None: 1440 mypath = data.get('ddata') 1441 if not isinstance(mypath,basestring): 1442 mypath = mypath.tostring() 1443 if mypath != None: 1444 mypath = os.path.join(self.STORE_PATH,mypath) 1445 if os.path.isfile(mypath) and os.access(mypath,os.W_OK): 1446 os.remove(mypath) 1447 1448 self.remove_keywords(iddoc) 1449 self.execute_query('DELETE FROM entropy_docs WHERE `iddoc` = %s AND `iddoctype` = %s',( 1450 iddoc, 1451 doc_type, 1452 ) 1453 ) 1454 if userid: self.update_user_score(userid) 1455 return True, (iddoc, None)

1456

1457 - def insert_youtube_video(self, pkgkey, userid, username, video_path, file_name, title, description, keywords):

1458 self.check_connection() 1459 if not self.gdata: 1460 return False, None 1461 1462 idkey = self.handle_pkgkey(pkgkey) 1463 video_path = os.path.realpath(video_path) 1464 if not (os.access(video_path,os.R_OK) and os.path.isfile(video_path)): 1465 return False 1466 virus_found, virus_type = self.scan_for_viruses(video_path) 1467 if virus_found: 1468 os.remove(video_path) 1469 return False, None 1470 1471 new_video_path = video_path 1472 if isinstance(file_name,basestring): 1473 # move file to the new filename 1474 new_video_path = os.path.join(os.path.dirname(video_path),os.path.basename(file_name)) # force basename 1475 scount = 0 1476 while os.path.lexists(new_video_path): 1477 scount += 1 1478 bpath = "%s.%s" % (unicode(scount),os.path.basename(file_name),) 1479 new_video_path = os.path.join(os.path.dirname(video_path),bpath) 1480 shutil.move(video_path,new_video_path) 1481 1482 yt_service = self.get_youtube_service() 1483 if yt_service == None: 1484 return False, None 1485 1486 mykeywords = ', '.join([x.strip().strip(',') for x in keywords.split()+["sabayon"] if (x.strip() and x.strip(",") and (len(x.strip()) > 4))]) 1487 gd_keywords = self.gdata.media.Keywords(text = mykeywords) 1488 1489 mydescription = "%s: %s" % (pkgkey,description,) 1490 mytitle = "%s: %s" % (self.system_name,title,) 1491 my_media_group = self.gdata.media.Group( 1492 title = self.gdata.media.Title(text = mytitle), 1493 description = self.gdata.media.Description( 1494 description_type = 'plain', 1495 text = mydescription 1496 ), 1497 keywords = gd_keywords, 1498 category = self.gdata.media.Category( 1499 text = 'Tech', 1500 scheme = 'http://gdata.youtube.com/schemas/2007/categories.cat', 1501 label = 'Tech' 1502 ), 1503 player = None 1504 ) 1505 video_entry = self.gdata.youtube.YouTubeVideoEntry(media = my_media_group) 1506 new_entry = yt_service.InsertVideoEntry(video_entry, new_video_path) 1507 if not isinstance(new_entry,self.gdata.youtube.YouTubeVideoEntry): 1508 return False, None 1509 video_url = new_entry.GetSwfUrl() 1510 video_id = os.path.basename(video_url) 1511 1512 iddoc = self.insert_generic_doc(idkey, userid, username, self.DOC_TYPES['youtube_video'], video_id, title, description, keywords) 1513 if isinstance(iddoc,basestring): 1514 return False, (iddoc, None,) 1515 return True, (iddoc, video_id,)

1516

1517 - def remove_youtube_video(self, iddoc):

1518 self.check_connection() 1519 if not self.gdata: 1520 return False, None 1521 if not self.is_iddoc_available(iddoc): 1522 return False, None 1523 userid = self.get_iddoc_userid(iddoc) 1524 1525 yt_service = self.get_youtube_service() 1526 if yt_service == None: 1527 return False, None 1528 1529 def do_remove(): 1530 self.remove_keywords(iddoc) 1531 self.execute_query('DELETE FROM entropy_docs WHERE `iddoc` = %s AND `iddoctype` = %s',( 1532 iddoc, 1533 self.DOC_TYPES['youtube_video'], 1534 ) 1535 )

1536 1537 self.execute_query('SELECT `ddata` FROM entropy_docs WHERE `iddoc` = %s AND `iddoctype` = %s',( 1538 iddoc, 1539 self.DOC_TYPES['youtube_video'], 1540 ) 1541 ) 1542 data = self.fetchone() 1543 if data == None: 1544 do_remove() 1545 return False, None 1546 elif not data.has_key('ddata'): 1547 do_remove() 1548 return False, None 1549 1550 video_id = data.get('ddata') 1551 try: 1552 video_entry = yt_service.GetYouTubeVideoEntry(video_id = video_id) 1553 deleted = yt_service.DeleteVideoEntry(video_entry) 1554 except: 1555 deleted = True 1556 1557 if deleted: 1558 do_remove() 1559 if userid: self.update_user_score(userid) 1560 return deleted, (iddoc, video_id,)

Source Code for Module entropy.services.ugc.interfaces