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: 413 return data['idkey'] 414 return -1

415

416 - def get_iddoctype(self, iddoc):

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

423

424 - def get_entropy_branches_id(self, branch):

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

431

432 - def get_entropy_release_strings_id(self, release_string):

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

439

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

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

450

451 - def get_pkgkey(self, idkey):

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

456

457 - def get_ugc_metadata(self, pkgkey):

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

469

470 - def get_ugc_keywords(self, iddoc):

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

473

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

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

484

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

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

495

496 - def get_ugc_metadata_by_identifiers(self, identifiers):

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

503

504 - def _get_ugc_extra_metadata(self, mydict):

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

533

534 - def get_ugc_vote(self, pkgkey):

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

545

546 - def get_ugc_allvotes(self):

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

564

565 - def get_ugc_downloads(self, pkgkey):

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

578

579 - def get_ugc_alldownloads(self):

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

596

597 - def get_iddoc_userid(self, iddoc):

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

602

603 - def get_total_comments_count(self):

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

611

612 - def get_total_documents_count(self):

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

620

621 - def get_total_votes_count(self):

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

629

630 - def get_total_downloads_count(self):

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

648

649 - def get_user_score_ranking(self, userid):

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

660

661 - def get_users_scored_count(self):

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

679

680 - def is_user_score_available(self, userid):

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

686

687 - def calculate_user_score(self, userid):

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

694

695 - def update_user_score(self, userid):

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

704

705 - def get_user_score(self, userid):

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

713

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

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

729

730 - def get_user_votes_average(self, userid):

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

738

739 - def get_user_alldocs(self, userid):

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

746

747 - def get_user_docs(self, userid):

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

749

750 - def get_user_comments(self, userid):

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

752

753 - def get_user_votes(self, userid):

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

757

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

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

762

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

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

771

772 - def get_user_comments_count(self, userid):

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

774

775 - def get_user_docs_count(self, userid):

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

777

778 - def get_user_images_count(self, userid):

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

780

781 - def get_user_files_count(self, userid):

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

783

784 - def get_user_yt_videos_count(self, userid):

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

786

787 - def get_user_votes_count(self, userid):

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

795

796 - def get_user_stats(self, userid):

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

809

810 - def get_distribution_stats(self):

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

818

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

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

859

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

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

899

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

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

939

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

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

981

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

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

1025

1026 - def handle_pkgkey(self, key):

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

1031

1032 - def insert_flood_control_check(self, userid):

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

1046

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

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

1076

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

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

1099

1100 - def remove_keywords(self, iddoc):

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

1102

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

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

1113

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

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

1131

1132 - def remove_comment(self, iddoc):

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

1144 1145 # give a vote to an app

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

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

1168

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

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

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

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

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

1197

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

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

1254

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

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

1263

1264 - def is_entropy_hardware_usage_stats_available(self, entropy_distribution_usage_id):

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

1271

1272 - def is_user_ip_available_in_entropy_distribution_usage(self, ip_address):

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

1280

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

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

1293

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

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

1313

1314 - def remove_document(self, iddoc):

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

1326

1327 - def scan_for_viruses(self, filepath):

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

1342

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

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

1426

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

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

1431

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

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

1437

1438 - def delete_image(self, iddoc):

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

1440

1441 - def delete_file(self, iddoc):

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

1443

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

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

1471

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

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

1535

1536 - def remove_youtube_video(self, iddoc):

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

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

Source Code for Module entropy.services.ugc.interfaces