entropy.services.ugc.interfaces

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

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

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

227

228 - def get_current_time(self):

229 return int(time.time())

230

231 - def cache_results(self):

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

239

240 - def get_cache_item_key(self, cache_item):

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

242

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

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

247

248 - def _get_geoip_data_from_ip_address(self, ip_address):

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

256 257 # expired get_ugc_alldownloads 0 86400 1228577077

258 - def get_cached_result(self, cache_item):

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

270

271 - def setup_store_path(self, path):

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

283

284 - def initialize_tables(self):

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

293

294 - def initialize_doctypes(self):

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

299

300 - def is_iddoctype_available(self, iddoctype):

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

306

307 - def is_pkgkey_available(self, key):

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

313

314 - def is_iddoc_available(self, iddoc):

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

320

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

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

325

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

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

332

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

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

340

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

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

347

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

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

354

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

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

361

362 - def handle_entropy_ip_locations_id(self, ip_addr):

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

375

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

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

381

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

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

387

388 - def get_date(self):

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

393

394 - def get_datetime(self):

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

399

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

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

408

409 - def get_idkey(self, key):

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

416

417 - def get_iddoctype(self, iddoc):

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

424

425 - def get_entropy_branches_id(self, branch):

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

432

433 - def get_entropy_release_strings_id(self, release_string):

434 self.check_connection() 435 self.execute_query('SELECT `entropy_release_strings_id` FROM entropy_release_strings WHERE `release_string` = %s', (release_string,)) 436 data = self.fetchone() 437 if data: 438 return data['entropy_release_strings_id'] 439 return -1

440

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

442 self.check_connection() 443 self.execute_query(""" 444 SELECT `entropy_ip_locations_id` FROM 445 entropy_ip_locations WHERE 446 `ip_latitude` = %s AND `ip_longitude` = %s""", (ip_latitude,ip_longitude,)) 447 data = self.fetchone() 448 if data: 449 return data['entropy_ip_locations_id'] 450 return -1

451

452 - def get_pkgkey(self, idkey):

453 self.check_connection() 454 self.execute_query('SELECT `key` FROM entropy_base WHERE `idkey` = %s', (idkey,)) 455 data = self.fetchone() 456 if data: return data['key']

457

458 - def get_ugc_metadata(self, pkgkey):

459 self.check_connection() 460 metadata = { 461 'vote': 0.0, 462 'downloads': 0, 463 } 464 self.execute_query('SELECT * FROM entropy_docs,entropy_base WHERE entropy_base.`idkey` = entropy_docs.`idkey` AND entropy_base.`key` = %s', (pkgkey,)) 465 metadata['docs'] = [self._get_ugc_extra_metadata(x) for x \ 466 in self.fetchall()] 467 metadata['vote'] = self.get_ugc_vote(pkgkey) 468 metadata['downloads'] = self.get_ugc_downloads(pkgkey) 469 return metadata

470

471 - def get_ugc_keywords(self, iddoc):

472 self.execute_query('SELECT `keyword` FROM entropy_docs_keywords WHERE `iddoc` = %s order by `keyword`', (iddoc,)) 473 return [x.get('keyword') for x in self.fetchall() if x.get('keyword')]

474

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

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

485

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

487 self.check_connection() 488 identifiers = list(identifiers) 489 if len(identifiers) < 2: 490 identifiers += [0] 491 typeslist = list(typeslist) 492 if len(typeslist) < 2: 493 typeslist += [0] 494 self.execute_query('SELECT * FROM entropy_docs WHERE `iddoc` IN %s AND `iddoctype` IN %s', (identifiers,typeslist,)) 495 return [self._get_ugc_extra_metadata(x) for x in self.fetchall()]

496

497 - def get_ugc_metadata_by_identifiers(self, identifiers):

498 self.check_connection() 499 identifiers = list(identifiers) 500 if len(identifiers) < 2: 501 identifiers += [0] 502 self.execute_query('SELECT * FROM entropy_docs WHERE `iddoc` IN %s', (identifiers,)) 503 return [self._get_ugc_extra_metadata(x) for x in self.fetchall()]

504

505 - def _get_ugc_extra_metadata(self, mydict):

506 mydict['store_url'] = None 507 mydict['keywords'] = self.get_ugc_keywords(mydict['iddoc']) 508 if mydict.has_key("key"): 509 mydict['pkgkey'] = mydict['key'] 510 else: 511 mydict['pkgkey'] = self.get_pkgkey(mydict['idkey']) 512 # for binary files, get size too 513 mydict['size'] = 0 514 if mydict['iddoctype'] in self.UPLOADED_DOC_TYPES: 515 myfilename = mydict['ddata'] 516 if not isinstance(myfilename, basestring): 517 myfilename = myfilename.tostring() 518 mypath = os.path.join(self.STORE_PATH, myfilename) 519 if os.path.isfile(mypath) and os.access(mypath,os.R_OK): 520 try: 521 mydict['size'] = self.entropyTools.get_file_size(mypath) 522 except OSError: 523 pass 524 mydict['store_url'] = os.path.join(self.store_url, myfilename) 525 else: 526 mydata = mydict['ddata'] 527 if not isinstance(mydata,basestring): 528 mydata = mydata.tostring() 529 try: 530 mydict['size'] = len(mydata) 531 except: 532 pass 533 return mydict

534

535 - def get_ugc_vote(self, pkgkey):

536 self.check_connection() 537 self.execute_query(""" 538 SELECT avg(entropy_votes.`vote`) as avg_vote FROM entropy_votes,entropy_base WHERE 539 entropy_base.`key` = %s AND 540 entropy_base.idkey = entropy_votes.idkey""", (pkgkey,)) 541 data = self.fetchone() or {} 542 avg_vote = data.get('avg_vote') 543 if not avg_vote: 544 return 0.0 545 return avg_vote

546

547 - def get_ugc_allvotes(self):

548 549 # cached? 550 cache_item = 'get_ugc_allvotes' 551 cached = self.get_cached_result(cache_item) 552 if cached != None: 553 return cached 554 555 self.check_connection() 556 self.execute_query(""" 557 SELECT entropy_base.`key` as `vkey`,avg(entropy_votes.vote) as `avg_vote` FROM 558 entropy_votes,entropy_base WHERE 559 entropy_votes.`idkey` = entropy_base.`idkey` GROUP BY entropy_base.`key`""") 560 vote_data = dict( ((x['vkey'], x['avg_vote'],) for x in self.fetchall()) ) 561 562 # do cache 563 self.cache_result(cache_item, vote_data) 564 return vote_data

565

566 - def get_ugc_downloads(self, pkgkey):

567 self.check_connection() 568 569 self.execute_query(""" 570 SELECT SQL_CACHE sum(entropy_downloads.`count`) as `tot_downloads` FROM 571 entropy_downloads,entropy_base WHERE entropy_base.key = %s AND 572 entropy_base.idkey = entropy_downloads.idkey""", (pkgkey,)) 573 574 data = self.fetchone() or {} 575 downloads = data.get('tot_downloads') 576 if not downloads: 577 return 0 578 return downloads

579

580 - def get_ugc_alldownloads(self):

581 # cached? 582 cache_item = 'get_ugc_alldownloads' 583 cached = self.get_cached_result(cache_item) 584 if cached != None: return cached 585 586 self.check_connection() 587 self.execute_query(""" 588 SELECT SQL_CACHE entropy_base.key as vkey, tot_downloads from entropy_base, 589 (SELECT idkey as idp1, sum(entropy_downloads.`count`) AS `tot_downloads` FROM 590 entropy_downloads GROUP BY entropy_downloads.`idkey`) as tmp where idkey = tmp.idp1; 591 """) 592 down_data = dict( ((x['vkey'], x['tot_downloads'],) for x in self.fetchall()) ) 593 594 # do cache 595 self.cache_result(cache_item, down_data) 596 return down_data

597

598 - def get_iddoc_userid(self, iddoc):

599 self.check_connection() 600 self.execute_query('SELECT `userid` FROM entropy_docs WHERE `iddoc` = %s', (iddoc,)) 601 data = self.fetchone() or {} 602 return data.get('userid', None)

603

604 - def get_total_comments_count(self):

605 self.check_connection() 606 self.execute_query('SELECT count(`iddoc`) as comments FROM entropy_docs WHERE `iddoctype` = %s', (self.DOC_TYPES['comments'],)) 607 data = self.fetchone() or {} 608 comments = data.get('comments') 609 if not comments: 610 return 0 611 return comments

612

613 - def get_total_documents_count(self):

614 self.check_connection() 615 self.execute_query('SELECT count(`iddoc`) as comments FROM entropy_docs WHERE `iddoctype` != %s', (self.DOC_TYPES['comments'],)) 616 data = self.fetchone() or {} 617 comments = data.get('comments') 618 if not comments: 619 return 0 620 return comments

621

622 - def get_total_votes_count(self):

623 self.check_connection() 624 self.execute_query('SELECT count(`idvote`) as votes FROM entropy_votes') 625 data = self.fetchone() or {} 626 votes = data.get('votes') 627 if not votes: 628 return 0 629 return votes

630

631 - def get_total_downloads_count(self):

632 633 # cached? 634 cache_item = 'get_total_downloads_count' 635 cached = self.get_cached_result(cache_item) 636 if cached != None: return cached 637 638 self.check_connection() 639 self.execute_query('SELECT SQL_CACHE sum(entropy_downloads.`count`) as downloads FROM entropy_downloads') 640 data = self.fetchone() or {} 641 downloads = data.get('downloads', 0) 642 if not downloads: 643 return 0 644 result = int(downloads) 645 646 # do cache 647 self.cache_result(cache_item, result) 648 return result

649

650 - def get_user_score_ranking(self, userid):

651 self.check_connection() 652 self.execute_query('SET @row = 0') 653 self.execute_query(""" 654 SELECT Row, col_a FROM (SELECT @row := @row + 1 AS Row, userid AS col_a FROM 655 entropy_user_scores ORDER BY score DESC) As derived1 WHERE col_a = %s""", (userid,)) 656 data = self.fetchone() or {} 657 ranking = data.get('Row', 0) # key can be avail but == None 658 if not ranking: 659 return 0 660 return ranking

661

662 - def get_users_scored_count(self):

663 664 # cached? 665 cache_item = 'get_users_scored_count' 666 cached = self.get_cached_result(cache_item) 667 if cached != None: 668 return cached 669 670 self.check_connection() 671 self.execute_query('SELECT SQL_CACHE count(`userid`) as mycount FROM entropy_user_scores') 672 data = self.fetchone() or {} 673 count = data.get('mycount', 0) 674 if not count: 675 return 0 676 677 # do cache 678 self.cache_result(cache_item, count) 679 return count

680

681 - def is_user_score_available(self, userid):

682 self.check_connection() 683 rows = self.execute_query('SELECT `userid` FROM entropy_user_scores WHERE `userid` = %s', (userid,)) 684 if rows: 685 return True 686 return False

687

688 - def calculate_user_score(self, userid):

689 comments = self.get_user_comments_count(userid) 690 docs = self.get_user_docs_count(userid) 691 votes = self.get_user_votes_count(userid) 692 return (comments*self.COMMENTS_SCORE_WEIGHT) + \ 693 (docs*self.DOCS_SCORE_WEIGHT) + \ 694 (votes*self.VOTES_SCORE_WEIGHT)

695

696 - def update_user_score(self, userid):

697 self.check_connection() 698 avail = self.is_user_score_available(userid) 699 myscore = self.calculate_user_score(userid) 700 if avail: 701 self.execute_query('UPDATE entropy_user_scores SET score = %s WHERE `userid` = %s', (myscore,userid,)) 702 else: 703 self.execute_query('INSERT INTO entropy_user_scores VALUES (%s,%s)', (userid,myscore,)) 704 return myscore

705

706 - def get_user_score(self, userid):

707 self.check_connection() 708 self.execute_query('SELECT score FROM entropy_user_scores WHERE userid = %s',(userid,)) 709 data = self.fetchone() or {} 710 myscore = data.get('score') 711 if myscore == None: 712 myscore = self.update_user_score(userid) 713 return myscore

714

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

716 self.check_connection() 717 limit_string = '' 718 if count: 719 limit_string += ' LIMIT %s,%s' % (offset,count,) 720 self.execute_query('SELECT SQL_CALC_FOUND_ROWS *, userid, score FROM entropy_user_scores ORDER BY score DESC' + limit_string) 721 data = self.fetchall() 722 723 self.execute_query('SELECT FOUND_ROWS() as count') 724 rdata = self.fetchone() or {} 725 found_rows = rdata.get('count', 0) 726 if found_rows is None: 727 found_rows = 0 728 729 return found_rows, data

730

731 - def get_user_votes_average(self, userid):

732 self.check_connection() 733 self.execute_query('SELECT avg(`vote`) as vote_avg FROM entropy_votes WHERE `userid` = %s', (userid,)) 734 data = self.fetchone() or {} 735 vote_avg = data.get('vote_avg', 0.0) 736 if not vote_avg: 737 return 0.0 738 return round(float(vote_avg), 2)

739

740 - def get_user_alldocs(self, userid):

741 self.check_connection() 742 self.execute_query(""" 743 SELECT * FROM entropy_docs,entropy_base WHERE 744 entropy_docs.`userid` = %s AND 745 entropy_base.idkey = entropy_docs.idkey ORDER BY entropy_base.`key`""", (userid,)) 746 return self.fetchall()

747

748 - def get_user_docs(self, userid):

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

750

751 - def get_user_comments(self, userid):

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

753

754 - def get_user_votes(self, userid):

755 self.check_connection() 756 self.execute_query('SELECT * FROM entropy_votes WHERE `userid` = %s', (userid,)) 757 return self.fetchall()

758

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

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

763

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

765 self.check_connection() 766 self.execute_query('SELECT count(`iddoc`) as docs FROM entropy_docs WHERE `userid` = %s AND `iddoctype` '+doctype_sql_cmp+' %s', (userid,doctype,)) 767 data = self.fetchone() or {} 768 docs = data.get('docs', 0) 769 if docs is None: 770 docs = 0 771 return docs

772

773 - def get_user_comments_count(self, userid):

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

775

776 - def get_user_docs_count(self, userid):

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

778

779 - def get_user_images_count(self, userid):

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

781

782 - def get_user_files_count(self, userid):

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

784

785 - def get_user_yt_videos_count(self, userid):

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

787

788 - def get_user_votes_count(self, userid):

789 self.check_connection() 790 self.execute_query('SELECT count(`idvote`) as votes FROM entropy_votes WHERE `userid` = %s', (userid,)) 791 data = self.fetchone() or {} 792 votes = data.get('votes', 0) 793 if votes is None: 794 votes = 0 795 return votes

796

797 - def get_user_stats(self, userid):

798 mydict = {} 799 mydict['comments'] = self.get_user_comments_count(userid) 800 mydict['docs'] = self.get_user_docs_count(userid) 801 mydict['images'] = self.get_user_images_count(userid) 802 mydict['files'] = self.get_user_files_count(userid) 803 mydict['yt_videos'] = self.get_user_yt_videos_count(userid) 804 mydict['votes'] = self.get_user_votes_count(userid) 805 mydict['votes_avg'] = self.get_user_votes_average(userid) 806 mydict['total_docs'] = mydict['comments'] + mydict['docs'] 807 mydict['score'] = self.get_user_score(userid) 808 mydict['ranking'] = self.get_user_score_ranking(userid) 809 return mydict

810

811 - def get_distribution_stats(self):

812 self.check_connection() 813 mydict = {} 814 mydict['comments'] = self.get_total_comments_count() 815 mydict['documents'] = self.get_total_documents_count() 816 mydict['votes'] = self.get_total_votes_count() 817 mydict['downloads'] = self.get_total_downloads_count() 818 return mydict

819

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

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

860

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

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

900

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

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

940

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

942 self.check_connection() 943 944 if iddoctypes == None: 945 iddoctypes = [self.DOC_TYPES[x] for x in self.DOC_TYPES] 946 iddoctypes = "("+', '.join([str(self.DOC_TYPES[x]) for x in self.DOC_TYPES])+")" 947 myterm = "%"+keyword_string+"%" 948 949 search_params = [myterm,results_offset,results_limit] 950 951 order_by_string = '' 952 if order_by == "key": 953 order_by_string = 'ORDER BY entropy_base.`key`' 954 elif order_by == "username": 955 order_by_string = 'ORDER BY entropy_docs.`username`' 956 elif order_by == "vote": 957 order_by_string = 'ORDER BY avg_vote DESC' 958 elif order_by == "downloads": 959 order_by_string = 'ORDER BY tot_downloads DESC' 960 961 self.execute_query(""" 962 SELECT SQL_CALC_FOUND_ROWS *, avg(entropy_votes.`vote`) as avg_vote, 963 sum(entropy_downloads.`count`) as `tot_downloads`, 964 entropy_user_scores.`score` as `score` FROM 965 entropy_docs,entropy_base,entropy_docs_keywords,entropy_votes,entropy_downloads,entropy_user_scores WHERE 966 entropy_docs_keywords.`keyword` LIKE %s AND 967 entropy_docs.`iddoctype` IN """+iddoctypes+""" AND 968 entropy_docs.`idkey` = entropy_base.`idkey` AND 969 entropy_docs_keywords.`iddoc` = entropy_docs.`iddoc` AND 970 entropy_votes.`idkey` = entropy_base.`idkey` AND 971 entropy_downloads.`idkey` = entropy_base.`idkey` AND 972 entropy_docs.`userid` = entropy_user_scores.`userid` 973 GROUP BY entropy_docs.`iddoc` """+order_by_string+""" LIMIT %s,%s""", search_params) 974 975 results = self.fetchall() 976 self.execute_query('SELECT FOUND_ROWS() as count') 977 data = self.fetchone() or {} 978 count = data.get('count', 0) 979 if count is None: 980 count = 0 981 return results, count

982

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

984 self.check_connection() 985 986 if iddoctypes == None: 987 iddoctypes = [self.DOC_TYPES[x] for x in self.DOC_TYPES] 988 iddoctypes = "("+', '.join([str(self.DOC_TYPES[x]) for x in self.DOC_TYPES])+")" 989 try: 990 myterm = int(iddoc_string) 991 except ValueError: 992 return [], 0 993 994 search_params = [myterm,results_offset,results_limit] 995 996 order_by_string = '' 997 if order_by == "key": 998 order_by_string = 'ORDER BY entropy_base.`key`' 999 elif order_by == "username": 1000 order_by_string = 'ORDER BY entropy_docs.`username`' 1001 elif order_by == "vote": 1002 order_by_string = 'ORDER BY avg_vote DESC' 1003 elif order_by == "downloads": 1004 order_by_string = 'ORDER BY tot_downloads DESC' 1005 1006 self.execute_query(""" 1007 SELECT SQL_CALC_FOUND_ROWS *, avg(entropy_votes.`vote`) as avg_vote, 1008 sum(entropy_downloads.`count`) as `tot_downloads`, 1009 entropy_user_scores.`score` as `score` FROM 1010 entropy_docs,entropy_base,entropy_votes,entropy_downloads,entropy_user_scores WHERE 1011 entropy_docs.`iddoc` = %s AND 1012 entropy_docs.`iddoctype` IN """+iddoctypes+""" AND 1013 entropy_docs.`idkey` = entropy_base.`idkey` AND 1014 entropy_votes.`idkey` = entropy_base.`idkey` AND 1015 entropy_downloads.`idkey` = entropy_base.`idkey` AND 1016 entropy_docs.`userid` = entropy_user_scores.`userid` 1017 GROUP BY entropy_docs.`iddoc` """+order_by_string+""" LIMIT %s,%s""", search_params) 1018 1019 results = self.fetchall() 1020 self.execute_query('SELECT FOUND_ROWS() as count') 1021 data = self.fetchone() or {} 1022 count = data.get('count', 0) 1023 if count is None: 1024 count = 0 1025 return results, count

1026

1027 - def handle_pkgkey(self, key):

1028 idkey = self.get_idkey(key) 1029 if idkey == -1: 1030 return self.insert_pkgkey(key) 1031 return idkey

1032

1033 - def insert_flood_control_check(self, userid):

1034 self.check_connection() 1035 self.execute_query('SELECT max(`ts`) as ts FROM entropy_docs WHERE `userid` = %s', (userid,)) 1036 data = self.fetchone() 1037 if not data: 1038 return False 1039 elif not data.has_key('ts'): 1040 return False 1041 elif data['ts'] == None: 1042 return False 1043 delta = self.datetime.fromtimestamp(time.time()) - data['ts'] 1044 if (delta.days == 0) and (delta.seconds <= self.FLOOD_INTERVAL): 1045 return True 1046 return False

1047

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

1049 self.check_connection() 1050 1051 title = title[:self.entropy_docs_title_len] 1052 description = description[:self.entropy_docs_description_len] 1053 1054 # flood control 1055 flood_risk = self.insert_flood_control_check(userid) 1056 if flood_risk: 1057 return 'flooding detected' 1058 1059 self.execute_query('INSERT INTO entropy_docs VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s)',( 1060 None, 1061 idkey, 1062 userid, 1063 username, 1064 doc_type, 1065 data, 1066 title, 1067 description, 1068 None, 1069 ) 1070 ) 1071 if do_commit: 1072 self.commit() 1073 iddoc = self.lastrowid() 1074 self.insert_keywords(iddoc, keywords) 1075 self.update_user_score(userid) 1076 return iddoc

1077

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

1079 keywords = keywords.split(",") 1080 clean_keys = [] 1081 for key in keywords: 1082 try: 1083 key = key.strip().split()[0] 1084 except IndexError: 1085 continue 1086 if not key: continue 1087 if not key.isalnum(): continue 1088 key = key[:self.entropy_docs_keyword_len] 1089 if key in clean_keys: continue 1090 clean_keys.append(key) 1091 1092 if not clean_keys: 1093 return 1094 1095 mydata = [] 1096 for key in clean_keys: 1097 mydata.append((iddoc, key,)) 1098 1099 self.execute_many('INSERT INTO entropy_docs_keywords VALUES (%s,%s)', mydata)

1100

1101 - def remove_keywords(self, iddoc):

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

1103

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

1105 self.check_connection() 1106 idkey = self.handle_pkgkey(pkgkey) 1107 iddoc = self.insert_generic_doc(idkey, userid, username, 1108 self.DOC_TYPES['comments'], comment, title, '', keywords) 1109 if isinstance(iddoc, basestring): 1110 return False, iddoc 1111 if do_commit: 1112 self.commit() 1113 return True, iddoc

1114

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

1116 self.check_connection() 1117 if not self.is_iddoc_available(iddoc): 1118 return False 1119 new_title = new_title[:self.entropy_docs_title_len] 1120 self.execute_query('UPDATE entropy_docs SET `ddata` = %s, `title` = %s WHERE `iddoc` = %s AND `iddoctype` = %s',( 1121 new_comment, 1122 new_title, 1123 iddoc, 1124 self.DOC_TYPES['comments'], 1125 ) 1126 ) 1127 self.remove_keywords(iddoc) 1128 self.insert_keywords(iddoc, new_keywords) 1129 if do_commit: 1130 self.commit() 1131 return True, iddoc

1132

1133 - def remove_comment(self, iddoc):

1134 self.check_connection() 1135 userid = self.get_iddoc_userid(iddoc) 1136 self.remove_keywords(iddoc) 1137 self.execute_query('DELETE FROM entropy_docs WHERE `iddoc` = %s AND `iddoctype` = %s',( 1138 iddoc, 1139 self.DOC_TYPES['comments'], 1140 ) 1141 ) 1142 if userid: 1143 self.update_user_score(userid) 1144 return True, iddoc

1145 1146 # give a vote to an app

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

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

1169

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

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

1178 1179 # icrement +1 download usage for the provided package keys

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

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

1198

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

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

1255

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

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

1264

1265 - def is_entropy_hardware_usage_stats_available(self, entropy_distribution_usage_id):

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

1272

1273 - def is_user_ip_available_in_entropy_distribution_usage(self, ip_address):

1274 self.check_connection() 1275 self.execute_query('SELECT entropy_distribution_usage_id FROM entropy_distribution_usage WHERE `ip_address` = %s',(ip_address,)) 1276 data = self.fetchone() or {} 1277 myid = data.get('entropy_distribution_usage_id') 1278 if myid is None: 1279 return -1 1280 return myid

1281

1282 - def insert_document(self, pkgkey, userid, username, text, title, 1283 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, 1288 text, title, description, keywords) 1289 if isinstance(iddoc, basestring): 1290 return False, iddoc 1291 if do_commit: 1292 self.commit() 1293 return True, iddoc

1294

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

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

1314

1315 - def remove_document(self, iddoc):

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

1327

1328 - def scan_for_viruses(self, filepath):

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

1343

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

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

1427

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

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

1432

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

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

1438

1439 - def delete_image(self, iddoc):

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

1441

1442 - def delete_file(self, iddoc):

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

1444

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

1446 self.check_connection() 1447 1448 userid = self.get_iddoc_userid(iddoc) 1449 self.execute_query('SELECT `ddata` FROM entropy_docs WHERE `iddoc` = %s AND `iddoctype` = %s',( 1450 iddoc, 1451 doc_type, 1452 ) 1453 ) 1454 data = self.fetchone() or {} 1455 mypath = data.get('ddata') 1456 if not isinstance(mypath, basestring) and (mypath is not None): 1457 mypath = mypath.tostring() 1458 if mypath is not None: 1459 mypath = os.path.join(self.STORE_PATH,mypath) 1460 if os.path.isfile(mypath) and os.access(mypath,os.W_OK): 1461 os.remove(mypath) 1462 1463 self.remove_keywords(iddoc) 1464 self.execute_query('DELETE FROM entropy_docs WHERE `iddoc` = %s AND `iddoctype` = %s',( 1465 iddoc, 1466 doc_type, 1467 ) 1468 ) 1469 if userid: 1470 self.update_user_score(userid) 1471 return True, (iddoc, None)

1472

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

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

1536

1537 - def remove_youtube_video(self, iddoc):

1538 self.check_connection() 1539 if not self.gdata: 1540 return False, None 1541 if not self.is_iddoc_available(iddoc): 1542 return False, None 1543 userid = self.get_iddoc_userid(iddoc) 1544 1545 yt_service = self.get_youtube_service() 1546 if yt_service == None: 1547 return False, None 1548 1549 def do_remove(): 1550 self.remove_keywords(iddoc) 1551 self.execute_query('DELETE FROM entropy_docs WHERE `iddoc` = %s AND `iddoctype` = %s',( 1552 iddoc, 1553 self.DOC_TYPES['youtube_video'], 1554 ) 1555 )

1556 1557 self.execute_query('SELECT `ddata` FROM entropy_docs WHERE `iddoc` = %s AND `iddoctype` = %s',( 1558 iddoc, 1559 self.DOC_TYPES['youtube_video'], 1560 ) 1561 ) 1562 data = self.fetchone() 1563 if data is None: 1564 do_remove() 1565 return False, None 1566 elif not data.has_key('ddata'): 1567 do_remove() 1568 return False, None 1569 1570 video_id = data.get('ddata') 1571 try: 1572 video_entry = yt_service.GetYouTubeVideoEntry(video_id = video_id) 1573 deleted = yt_service.DeleteVideoEntry(video_entry) 1574 except: 1575 deleted = True 1576 1577 if deleted: 1578 do_remove() 1579 if userid: self.update_user_score(userid) 1580 return deleted, (iddoc, video_id,)

Source Code for Module entropy.services.ugc.interfaces