entropy.services.ugc.interfaces

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

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

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

228

229 - def get_current_time(self):

230 return int(time.time())

231

232 - def cache_results(self):

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

240

241 - def get_cache_item_key(self, cache_item):

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

243

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

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

248

249 - def _get_geoip_data_from_ip_address(self, ip_address):

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

257 258 # expired get_ugc_alldownloads 0 86400 1228577077

259 - def get_cached_result(self, cache_item):

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

271

272 - def setup_store_path(self, path):

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

284

285 - def initialize_tables(self):

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

294

295 - def initialize_doctypes(self):

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

300

301 - def is_iddoctype_available(self, iddoctype):

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

307

308 - def is_pkgkey_available(self, key):

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

314

315 - def is_iddoc_available(self, iddoc):

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

321

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

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

326

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

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

333

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

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

341

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

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

348

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

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

355

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

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

362

363 - def handle_entropy_ip_locations_id(self, ip_addr):

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

376

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

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

382

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

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

388

389 - def get_date(self):

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

394

395 - def get_datetime(self):

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

400

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

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

409

410 - def get_idkey(self, key):

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

417

418 - def get_iddoctype(self, iddoc):

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

425

426 - def get_entropy_branches_id(self, branch):

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

433

434 - def get_entropy_release_strings_id(self, release_string):

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

441

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

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

452

453 - def get_pkgkey(self, idkey):

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

458

459 - def get_ugc_metadata(self, pkgkey):

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

471

472 - def get_ugc_keywords(self, iddoc):

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

475

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

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

486

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

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

497

498 - def get_ugc_metadata_by_identifiers(self, identifiers):

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

505

506 - def _get_ugc_extra_metadata(self, mydict):

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

535

536 - def get_ugc_vote(self, pkgkey):

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

547

548 - def get_ugc_allvotes(self):

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

566

567 - def get_ugc_downloads(self, pkgkey):

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

580

581 - def get_ugc_alldownloads(self):

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

598

599 - def get_iddoc_userid(self, iddoc):

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

604

605 - def get_total_comments_count(self):

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

613

614 - def get_total_documents_count(self):

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

622

623 - def get_total_votes_count(self):

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

631

632 - def get_total_downloads_count(self):

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

650

651 - def get_user_score_ranking(self, userid):

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

662

663 - def get_users_scored_count(self):

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

681

682 - def is_user_score_available(self, userid):

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

688

689 - def calculate_user_score(self, userid):

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

696

697 - def update_user_score(self, userid):

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

706

707 - def get_user_score(self, userid):

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

715

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

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

731

732 - def get_user_votes_average(self, userid):

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

740

741 - def get_user_alldocs(self, userid):

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

748

749 - def get_user_docs(self, userid):

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

751

752 - def get_user_comments(self, userid):

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

754

755 - def get_user_votes(self, userid):

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

759

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

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

764

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

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

773

774 - def get_user_comments_count(self, userid):

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

776

777 - def get_user_docs_count(self, userid):

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

779

780 - def get_user_images_count(self, userid):

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

782

783 - def get_user_files_count(self, userid):

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

785

786 - def get_user_yt_videos_count(self, userid):

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

788

789 - def get_user_votes_count(self, userid):

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

797

798 - def get_user_stats(self, userid):

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

811

812 - def get_distribution_stats(self):

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

820

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

822 self.check_connection() 823 824 if iddoctypes is None: 825 iddoctypes = self.DOC_TYPES.values() 826 827 if len(iddoctypes) == 1: 828 iddoctypes_str = " = %s" % (iddoctypes[0],) 829 else: 830 iddoctypes_str = "IN ("+', '.join([str(x) for x in iddoctypes])+")" 831 832 myterm = "%"+pkgkey_string+"%" 833 834 search_params = [myterm,results_offset,results_limit] 835 836 order_by_string = '' 837 if order_by == "key": 838 order_by_string = 'ORDER BY entropy_base.`key`' 839 elif order_by == "username": 840 order_by_string = 'ORDER BY entropy_docs.`username`' 841 elif order_by == "vote": 842 order_by_string = 'ORDER BY avg_vote DESC' 843 elif order_by == "downloads": 844 order_by_string = 'ORDER BY tot_downloads DESC' 845 846 self.execute_query(""" 847 SELECT 848 SQL_CALC_FOUND_ROWS entropy_base.`key`, 849 entropy_docs.*, 850 entropy_downloads.iddownload, 851 entropy_downloads.ddate, 852 entropy_votes.idvote, 853 entropy_votes.vdate, 854 entropy_votes.vote, 855 avg(entropy_votes.vote) as avg_vote, 856 sum(entropy_downloads.`count`) as tot_downloads, 857 entropy_user_scores.score as `score` 858 FROM 859 entropy_base 860 LEFT JOIN entropy_votes ON entropy_base.idkey = entropy_votes.idkey 861 LEFT JOIN entropy_docs ON entropy_base.idkey = entropy_docs.idkey 862 LEFT JOIN entropy_docs as ed ON entropy_base.idkey = ed.idkey 863 LEFT JOIN entropy_user_scores on entropy_docs.userid = entropy_user_scores.userid 864 LEFT JOIN entropy_downloads on entropy_base.idkey = entropy_downloads.idkey 865 WHERE 866 entropy_base.`key` LIKE %s AND 867 entropy_docs.iddoctype """+iddoctypes_str+""" 868 GROUP BY entropy_docs.`iddoc` 869 """+order_by_string+""" 870 LIMIT %s,%s 871 """, search_params) 872 873 results = self.fetchall() 874 for item in results: 875 if item['avg_vote'] is None: 876 item['avg_vote'] = 0.0 877 878 self.execute_query('SELECT FOUND_ROWS() as count') 879 data = self.fetchone() or {} 880 count = data.get('count', 0) 881 if count is None: 882 count = 0 883 return results, count

884

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

886 self.check_connection() 887 888 if iddoctypes is None: 889 iddoctypes = self.DOC_TYPES.values() 890 891 if len(iddoctypes) == 1: 892 iddoctypes_str = " = %s" % (iddoctypes[0],) 893 else: 894 iddoctypes_str = "IN ("+', '.join([str(x) for x in iddoctypes])+")" 895 896 myterm = "%"+pkgkey_string+"%" 897 898 search_params = [myterm,results_offset,results_limit] 899 900 order_by_string = '' 901 if order_by == "key": 902 order_by_string = 'ORDER BY entropy_base.`key`' 903 elif order_by == "username": 904 order_by_string = 'ORDER BY entropy_docs.`username`' 905 elif order_by == "vote": 906 order_by_string = 'ORDER BY avg_vote DESC' 907 elif order_by == "downloads": 908 order_by_string = 'ORDER BY tot_downloads DESC' 909 910 self.execute_query(""" 911 SELECT 912 SQL_CALC_FOUND_ROWS entropy_base.`key`, 913 entropy_docs.*, 914 entropy_downloads.iddownload, 915 entropy_downloads.ddate, 916 entropy_votes.idvote, 917 entropy_votes.vdate, 918 entropy_votes.vote, 919 avg(entropy_votes.vote) as avg_vote, 920 sum(entropy_downloads.`count`) as tot_downloads, 921 entropy_user_scores.score as `score` 922 FROM 923 entropy_base 924 LEFT JOIN entropy_votes ON entropy_base.idkey = entropy_votes.idkey 925 LEFT JOIN entropy_docs ON entropy_base.idkey = entropy_docs.idkey 926 LEFT JOIN entropy_docs as ed ON entropy_base.idkey = ed.idkey 927 LEFT JOIN entropy_user_scores on entropy_docs.userid = entropy_user_scores.userid 928 LEFT JOIN entropy_downloads on entropy_base.idkey = entropy_downloads.idkey 929 WHERE 930 entropy_docs.`username` LIKE %s AND 931 entropy_docs.iddoctype """+iddoctypes_str+""" 932 GROUP BY entropy_docs.`iddoc` 933 """+order_by_string+""" 934 LIMIT %s,%s 935 """, search_params) 936 937 results = self.fetchall() 938 # avg_vote can be POTENTIALLY None, we need to change it to float(0.0) 939 for item in results: 940 if item['avg_vote'] is None: 941 item['avg_vote'] = 0.0 942 943 self.execute_query('SELECT FOUND_ROWS() as count') 944 data = self.fetchone() or {} 945 count = data.get('count', 0) 946 if count is None: 947 count = 0 948 return results, count

949

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

951 self.check_connection() 952 953 if iddoctypes is None: 954 iddoctypes = self.DOC_TYPES.values() 955 956 if len(iddoctypes) == 1: 957 iddoctypes_str = " = %s" % (iddoctypes[0],) 958 else: 959 iddoctypes_str = "IN ("+', '.join([str(x) for x in iddoctypes])+")" 960 961 myterm = "%"+pkgkey_string+"%" 962 search_params = [myterm,myterm,myterm,results_offset,results_limit] 963 964 order_by_string = '' 965 if order_by == "key": 966 order_by_string = 'ORDER BY entropy_base.`key`' 967 elif order_by == "username": 968 order_by_string = 'ORDER BY entropy_docs.`username`' 969 elif order_by == "vote": 970 order_by_string = 'ORDER BY avg_vote DESC' 971 elif order_by == "downloads": 972 order_by_string = 'ORDER BY tot_downloads DESC' 973 974 self.execute_query(""" 975 SELECT 976 SQL_CALC_FOUND_ROWS entropy_base.`key`, 977 entropy_docs.*, 978 entropy_downloads.iddownload, 979 entropy_downloads.ddate, 980 entropy_votes.idvote, 981 entropy_votes.vdate, 982 entropy_votes.vote, 983 avg(entropy_votes.vote) as avg_vote, 984 sum(entropy_downloads.`count`) as tot_downloads, 985 entropy_user_scores.score as `score` 986 FROM 987 entropy_base 988 LEFT JOIN entropy_votes ON entropy_base.idkey = entropy_votes.idkey 989 LEFT JOIN entropy_docs ON entropy_base.idkey = entropy_docs.idkey 990 LEFT JOIN entropy_docs as ed ON entropy_base.idkey = ed.idkey 991 LEFT JOIN entropy_user_scores on entropy_docs.userid = entropy_user_scores.userid 992 LEFT JOIN entropy_downloads on entropy_base.idkey = entropy_downloads.idkey 993 WHERE 994 (entropy_docs.`title` LIKE %s OR entropy_docs.`description` LIKE %s OR entropy_docs.`ddata` LIKE %s) AND 995 entropy_docs.iddoctype """+iddoctypes_str+""" 996 GROUP BY entropy_docs.`iddoc` 997 """+order_by_string+""" 998 LIMIT %s,%s 999 """, search_params) 1000 1001 results = self.fetchall() 1002 # avg_vote can be POTENTIALLY None, we need to change it to float(0.0) 1003 for item in results: 1004 if item['avg_vote'] is None: 1005 item['avg_vote'] = 0.0 1006 1007 self.execute_query('SELECT FOUND_ROWS() as count') 1008 data = self.fetchone() or {} 1009 count = data.get('count', 0) 1010 if count is None: 1011 count = 0 1012 return results, count

1013

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

1015 self.check_connection() 1016 1017 if iddoctypes is None: 1018 iddoctypes = self.DOC_TYPES.values() 1019 1020 if len(iddoctypes) == 1: 1021 iddoctypes_str = " = %s" % (iddoctypes[0],) 1022 else: 1023 iddoctypes_str = "IN ("+', '.join([str(x) for x in iddoctypes])+")" 1024 1025 myterm = "%"+keyword_string+"%" 1026 1027 search_params = [myterm,results_offset,results_limit] 1028 1029 order_by_string = '' 1030 if order_by == "key": 1031 order_by_string = 'ORDER BY entropy_base.`key`' 1032 elif order_by == "username": 1033 order_by_string = 'ORDER BY entropy_docs.`username`' 1034 elif order_by == "vote": 1035 order_by_string = 'ORDER BY avg_vote DESC' 1036 elif order_by == "downloads": 1037 order_by_string = 'ORDER BY tot_downloads DESC' 1038 1039 self.execute_query(""" 1040 SELECT 1041 SQL_CALC_FOUND_ROWS entropy_base.`key`, 1042 entropy_docs.*, 1043 entropy_downloads.iddownload, 1044 entropy_downloads.ddate, 1045 entropy_votes.idvote, 1046 entropy_votes.vdate, 1047 entropy_votes.vote, 1048 avg(entropy_votes.vote) as avg_vote, 1049 sum(entropy_downloads.`count`) as tot_downloads, 1050 entropy_user_scores.score as `score` 1051 FROM 1052 entropy_base 1053 LEFT JOIN entropy_votes ON entropy_base.idkey = entropy_votes.idkey 1054 LEFT JOIN entropy_docs ON entropy_base.idkey = entropy_docs.idkey 1055 LEFT JOIN entropy_docs as ed ON entropy_base.idkey = ed.idkey 1056 LEFT JOIN entropy_user_scores on entropy_docs.userid = entropy_user_scores.userid 1057 LEFT JOIN entropy_downloads on entropy_base.idkey = entropy_downloads.idkey 1058 LEFT JOIN entropy_docs_keywords on entropy_docs_keywords.iddoc = entropy_docs.iddoc 1059 WHERE 1060 entropy_docs_keywords.`keyword` LIKE %s AND 1061 entropy_docs.iddoctype """+iddoctypes_str+""" 1062 GROUP BY entropy_docs.`iddoc` 1063 """+order_by_string+""" 1064 LIMIT %s,%s 1065 """, search_params) 1066 1067 results = self.fetchall() 1068 # avg_vote can be POTENTIALLY None, we need to change it to float(0.0) 1069 for item in results: 1070 if item['avg_vote'] is None: 1071 item['avg_vote'] = 0.0 1072 1073 self.execute_query('SELECT FOUND_ROWS() as count') 1074 data = self.fetchone() or {} 1075 count = data.get('count', 0) 1076 if count is None: 1077 count = 0 1078 return results, count

1079

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

1081 self.check_connection() 1082 1083 if iddoctypes is None: 1084 iddoctypes = self.DOC_TYPES.values() 1085 1086 if len(iddoctypes) == 1: 1087 iddoctypes_str = " = %s" % (iddoctypes[0],) 1088 else: 1089 iddoctypes_str = "IN ("+', '.join([str(x) for x in iddoctypes])+")" 1090 1091 try: 1092 myterm = int(iddoc_string) 1093 except ValueError: 1094 return [], 0 1095 1096 search_params = [myterm,results_offset,results_limit] 1097 1098 order_by_string = '' 1099 if order_by == "key": 1100 order_by_string = 'ORDER BY entropy_base.`key`' 1101 elif order_by == "username": 1102 order_by_string = 'ORDER BY entropy_docs.`username`' 1103 elif order_by == "vote": 1104 order_by_string = 'ORDER BY avg_vote DESC' 1105 elif order_by == "downloads": 1106 order_by_string = 'ORDER BY tot_downloads DESC' 1107 1108 self.execute_query(""" 1109 SELECT 1110 SQL_CALC_FOUND_ROWS entropy_base.`key`, 1111 entropy_docs.*, 1112 entropy_downloads.iddownload, 1113 entropy_downloads.ddate, 1114 entropy_votes.idvote, 1115 entropy_votes.vdate, 1116 entropy_votes.vote, 1117 avg(entropy_votes.vote) as avg_vote, 1118 sum(entropy_downloads.`count`) as tot_downloads, 1119 entropy_user_scores.score as `score` 1120 FROM 1121 entropy_base 1122 LEFT JOIN entropy_votes ON entropy_base.idkey = entropy_votes.idkey 1123 LEFT JOIN entropy_docs ON entropy_base.idkey = entropy_docs.idkey 1124 LEFT JOIN entropy_docs as ed ON entropy_base.idkey = ed.idkey 1125 LEFT JOIN entropy_user_scores on entropy_docs.userid = entropy_user_scores.userid 1126 LEFT JOIN entropy_downloads on entropy_base.idkey = entropy_downloads.idkey 1127 WHERE 1128 entropy_docs.`iddoc` = %s AND 1129 entropy_docs.iddoctype """+iddoctypes_str+""" 1130 GROUP BY entropy_docs.`iddoc` 1131 """+order_by_string+""" 1132 LIMIT %s,%s 1133 """, search_params) 1134 1135 results = self.fetchall() 1136 # avg_vote can be POTENTIALLY None, we need to change it to float(0.0) 1137 for item in results: 1138 if item['avg_vote'] is None: 1139 item['avg_vote'] = 0.0 1140 1141 self.execute_query('SELECT FOUND_ROWS() as count') 1142 data = self.fetchone() or {} 1143 count = data.get('count', 0) 1144 if count is None: 1145 count = 0 1146 return results, count

1147

1148 - def handle_pkgkey(self, key):

1149 idkey = self.get_idkey(key) 1150 if idkey == -1: 1151 return self.insert_pkgkey(key) 1152 return idkey

1153

1154 - def insert_flood_control_check(self, userid):

1155 self.check_connection() 1156 self.execute_query('SELECT max(`ts`) as ts FROM entropy_docs WHERE `userid` = %s', (userid,)) 1157 data = self.fetchone() 1158 if not data: 1159 return False 1160 elif not data.has_key('ts'): 1161 return False 1162 elif data['ts'] == None: 1163 return False 1164 delta = self.datetime.fromtimestamp(time.time()) - data['ts'] 1165 if (delta.days == 0) and (delta.seconds <= self.FLOOD_INTERVAL): 1166 return True 1167 return False

1168

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

1170 self.check_connection() 1171 1172 title = title[:self.entropy_docs_title_len] 1173 description = description[:self.entropy_docs_description_len] 1174 1175 # flood control 1176 flood_risk = self.insert_flood_control_check(userid) 1177 if flood_risk: 1178 return 'flooding detected' 1179 1180 self.execute_query('INSERT INTO entropy_docs VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s)',( 1181 None, 1182 idkey, 1183 userid, 1184 username, 1185 doc_type, 1186 data, 1187 title, 1188 description, 1189 None, 1190 ) 1191 ) 1192 if do_commit: 1193 self.commit() 1194 iddoc = self.lastrowid() 1195 self.insert_keywords(iddoc, keywords) 1196 self.update_user_score(userid) 1197 return iddoc

1198

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

1200 keywords = keywords.split(",") 1201 clean_keys = [] 1202 for key in keywords: 1203 try: 1204 key = key.strip().split()[0] 1205 except IndexError: 1206 continue 1207 if not key: continue 1208 if not key.isalnum(): continue 1209 key = key[:self.entropy_docs_keyword_len] 1210 if key in clean_keys: continue 1211 clean_keys.append(key) 1212 1213 if not clean_keys: 1214 return 1215 1216 mydata = [] 1217 for key in clean_keys: 1218 mydata.append((iddoc, key,)) 1219 1220 self.execute_many('INSERT INTO entropy_docs_keywords VALUES (%s,%s)', mydata)

1221

1222 - def remove_keywords(self, iddoc):

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

1224

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

1226 self.check_connection() 1227 idkey = self.handle_pkgkey(pkgkey) 1228 iddoc = self.insert_generic_doc(idkey, userid, username, 1229 self.DOC_TYPES['comments'], comment, title, '', keywords) 1230 if isinstance(iddoc, basestring): 1231 return False, iddoc 1232 if do_commit: 1233 self.commit() 1234 return True, iddoc

1235

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

1237 self.check_connection() 1238 if not self.is_iddoc_available(iddoc): 1239 return False 1240 new_title = new_title[:self.entropy_docs_title_len] 1241 self.execute_query('UPDATE entropy_docs SET `ddata` = %s, `title` = %s WHERE `iddoc` = %s AND `iddoctype` = %s',( 1242 new_comment, 1243 new_title, 1244 iddoc, 1245 self.DOC_TYPES['comments'], 1246 ) 1247 ) 1248 self.remove_keywords(iddoc) 1249 self.insert_keywords(iddoc, new_keywords) 1250 if do_commit: 1251 self.commit() 1252 return True, iddoc

1253

1254 - def remove_comment(self, iddoc):

1255 self.check_connection() 1256 userid = self.get_iddoc_userid(iddoc) 1257 self.remove_keywords(iddoc) 1258 self.execute_query('DELETE FROM entropy_docs WHERE `iddoc` = %s AND `iddoctype` = %s',( 1259 iddoc, 1260 self.DOC_TYPES['comments'], 1261 ) 1262 ) 1263 if userid: 1264 self.update_user_score(userid) 1265 return True, iddoc

1266 1267 # give a vote to an app

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

1269 self.check_connection() 1270 idkey = self.handle_pkgkey(pkgkey) 1271 vote = int(vote) 1272 if vote not in self.VOTE_RANGE: # weird 1273 vote = 3 # avg? 1274 mydate = self.get_date() 1275 if self.has_user_already_voted(pkgkey, userid): 1276 return False 1277 self.execute_query('INSERT INTO entropy_votes VALUES (%s,%s,%s,%s,%s,%s)',( 1278 None, 1279 idkey, 1280 userid, 1281 mydate, 1282 vote, 1283 None, 1284 ) 1285 ) 1286 if do_commit: 1287 self.commit() 1288 self.update_user_score(userid) 1289 return True

1290

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

1292 self.check_connection() 1293 idkey = self.handle_pkgkey(pkgkey) 1294 self.execute_query('SELECT `idvote` FROM entropy_votes WHERE `idkey` = %s AND `userid` = %s', (idkey,userid,)) 1295 data = self.fetchone() 1296 if data: 1297 return True 1298 return False

1299 1300 # icrement +1 download usage for the provided package keys

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

1302 self.check_connection() 1303 mydate = self.get_date() 1304 iddownloads = set() 1305 for pkgkey in pkgkeys: 1306 iddownload = self.get_iddownload(pkgkey, mydate) 1307 if iddownload == -1: 1308 iddownload = self.insert_download(pkgkey, mydate, count = 1) 1309 else: 1310 self.update_download(iddownload) 1311 if (iddownload > 0) and isinstance(ip_addr,basestring): 1312 iddownloads.add(iddownload) 1313 1314 if iddownloads: 1315 self.store_download_data(iddownloads, ip_addr) 1316 if do_commit: 1317 self.commit() 1318 return True

1319

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

1322 1323 self.check_connection() 1324 branch_id = self.get_entropy_branches_id(branch) 1325 if branch_id == -1: 1326 branch_id = self.insert_entropy_branch(branch) 1327 1328 rel_strings_id = self.get_entropy_release_strings_id(release_string) 1329 if rel_strings_id == -1: 1330 rel_strings_id = self.insert_entropy_release_string(release_string) 1331 1332 self.do_downloads(pkgkeys, ip_addr = ip_addr) 1333 1334 entropy_distribution_usage_id = self.is_user_ip_available_in_entropy_distribution_usage(ip_addr) 1335 1336 hits = 1 1337 if self.STATS_MAP['installer'] in pkgkeys: 1338 hits = 0 1339 if entropy_distribution_usage_id == -1: 1340 entropy_ip_locations_id = self.handle_entropy_ip_locations_id(ip_addr) 1341 self.execute_query('INSERT INTO entropy_distribution_usage VALUES (%s,%s,%s,%s,%s,%s,%s,%s)',( 1342 None, 1343 branch_id, 1344 rel_strings_id, 1345 None, 1346 ip_addr, 1347 entropy_ip_locations_id, 1348 self.get_datetime(), 1349 hits, 1350 ) 1351 ) 1352 entropy_distribution_usage_id = self.lastrowid() 1353 else: 1354 self.execute_query(""" 1355 UPDATE entropy_distribution_usage SET `entropy_branches_id` = %s, 1356 `entropy_release_strings_id` = %s, 1357 `hits` = `hits`+%s 1358 WHERE `entropy_distribution_usage_id` = %s 1359 """,( 1360 branch_id, 1361 rel_strings_id, 1362 hits, 1363 entropy_distribution_usage_id, 1364 ) 1365 ) 1366 1367 # store hardware hash if set 1368 if hw_hash and not \ 1369 self.is_entropy_hardware_usage_stats_available(entropy_distribution_usage_id): 1370 1371 self.do_entropy_hardware_usage_stats(entropy_distribution_usage_id, 1372 hw_hash) 1373 1374 if do_commit: self.commit() 1375 return True

1376

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

1378 1379 self.execute_query('INSERT INTO entropy_hardware_usage VALUES (%s,%s,%s)', ( 1380 None, 1381 entropy_distribution_usage_id, 1382 hw_hash, 1383 ) 1384 )

1385

1386 - def is_entropy_hardware_usage_stats_available(self, entropy_distribution_usage_id):

1387 self.check_connection() 1388 self.execute_query('SELECT entropy_hardware_usage_id FROM entropy_hardware_usage WHERE `entropy_distribution_usage_id` = %s',(entropy_distribution_usage_id,)) 1389 data = self.fetchone() 1390 if data: 1391 return True 1392 return False

1393

1394 - def is_user_ip_available_in_entropy_distribution_usage(self, ip_address):

1395 self.check_connection() 1396 self.execute_query('SELECT entropy_distribution_usage_id FROM entropy_distribution_usage WHERE `ip_address` = %s',(ip_address,)) 1397 data = self.fetchone() or {} 1398 myid = data.get('entropy_distribution_usage_id') 1399 if myid is None: 1400 return -1 1401 return myid

1402

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

1405 self.check_connection() 1406 idkey = self.handle_pkgkey(pkgkey) 1407 if doc_type == None: doc_type = self.DOC_TYPES['bbcode_doc'] 1408 iddoc = self.insert_generic_doc(idkey, userid, username, doc_type, 1409 text, title, description, keywords) 1410 if isinstance(iddoc, basestring): 1411 return False, iddoc 1412 if do_commit: 1413 self.commit() 1414 return True, iddoc

1415

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

1417 self.check_connection() 1418 if not self.is_iddoc_available(iddoc): 1419 return False 1420 1421 new_title = new_title[:self.entropy_docs_title_len] 1422 1423 self.execute_query('UPDATE entropy_docs SET `ddata` = %s, `title` = %s WHERE `iddoc` = %s AND `iddoctype` = %s',( 1424 new_document, 1425 new_title, 1426 iddoc, 1427 self.DOC_TYPES['bbcode_doc'], 1428 ) 1429 ) 1430 self.remove_keywords(iddoc) 1431 self.insert_keywords(iddoc, new_keywords) 1432 if do_commit: 1433 self.commit() 1434 return True, iddoc

1435

1436 - def remove_document(self, iddoc):

1437 self.check_connection() 1438 userid = self.get_iddoc_userid(iddoc) 1439 self.remove_keywords(iddoc) 1440 self.execute_query('DELETE FROM entropy_docs WHERE `iddoc` = %s AND `iddoctype` = %s',( 1441 iddoc, 1442 self.DOC_TYPES['bbcode_doc'], 1443 ) 1444 ) 1445 if userid: 1446 self.update_user_score(userid) 1447 return True, iddoc

1448

1449 - def scan_for_viruses(self, filepath):

1450 1451 if not os.access(filepath,os.R_OK): 1452 return False,None 1453 1454 args = [self.VIRUS_CHECK_EXEC] 1455 args += self.VIRUS_CHECK_ARGS 1456 args += [filepath] 1457 f = open("/dev/null","w") 1458 p = subprocess.Popen(args, stdout = f, stderr = f) 1459 rc = p.wait() 1460 f.close() 1461 if rc == 1: 1462 return True, None 1463 return False, None

1464

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

1467 self.check_connection() 1468 file_path = os.path.realpath(file_path) 1469 1470 # do a virus check? 1471 virus_found, virus_type = self.scan_for_viruses(file_path) 1472 if virus_found: 1473 os.remove(file_path) 1474 return False, None 1475 1476 # flood control 1477 flood_risk = self.insert_flood_control_check(userid) 1478 if flood_risk: return False, 'flooding detected' 1479 1480 # validity check 1481 if doc_type == self.DOC_TYPES['image']: 1482 valid = False 1483 if os.path.isfile(file_path) and os.access(file_path,os.R_OK): 1484 valid = self.entropyTools.is_supported_image_file(file_path) 1485 if not valid: 1486 return False, 'not a valid image' 1487 1488 dest_path = os.path.join(self.STORE_PATH,file_name) 1489 1490 # create dir if not exists 1491 dest_dir = os.path.dirname(dest_path) 1492 if not os.path.isdir(dest_dir): 1493 try: 1494 os.makedirs(dest_dir) 1495 except OSError, e: 1496 raise PermissionDenied('PermissionDenied: %s' % (e,)) 1497 if etpConst['entropygid'] != None: 1498 const_setup_perms(dest_dir,etpConst['entropygid']) 1499 1500 orig_dest_path = dest_path 1501 dcount = 0 1502 while os.path.isfile(dest_path): 1503 dcount += 1 1504 dest_path_name = "%s_%s" % (dcount,os.path.basename(orig_dest_path),) 1505 dest_path = os.path.join(os.path.dirname(orig_dest_path),dest_path_name) 1506 1507 if os.path.dirname(file_path) != dest_dir: 1508 try: 1509 os.rename(file_path, dest_path) 1510 except OSError: 1511 # fallback to non atomic 1512 shutil.move(file_path, dest_path) 1513 if etpConst['entropygid'] != None: 1514 try: 1515 const_setup_file(dest_path, etpConst['entropygid'], 0664) 1516 except OSError: 1517 pass 1518 # at least set chmod 1519 try: 1520 const_set_chmod(dest_path,0664) 1521 except OSError: 1522 pass 1523 1524 title = title[:self.entropy_docs_title_len] 1525 description = description[:self.entropy_docs_description_len] 1526 1527 # now store in db 1528 idkey = self.handle_pkgkey(pkgkey) 1529 self.execute_query('INSERT INTO entropy_docs VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s)',( 1530 None, 1531 idkey, 1532 userid, 1533 username, 1534 doc_type, 1535 file_name, 1536 title, 1537 description, 1538 None, 1539 ) 1540 ) 1541 iddoc = self.lastrowid() 1542 self.insert_keywords(iddoc, keywords) 1543 store_url = os.path.basename(dest_path) 1544 if self.store_url: 1545 store_url = os.path.join(self.store_url,store_url) 1546 self.update_user_score(userid) 1547 return True, (iddoc, store_url)

1548

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

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

1553

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

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

1559

1560 - def delete_image(self, iddoc):

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

1562

1563 - def delete_file(self, iddoc):

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

1565

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

1567 self.check_connection() 1568 1569 userid = self.get_iddoc_userid(iddoc) 1570 self.execute_query('SELECT `ddata` FROM entropy_docs WHERE `iddoc` = %s AND `iddoctype` = %s',( 1571 iddoc, 1572 doc_type, 1573 ) 1574 ) 1575 data = self.fetchone() or {} 1576 mypath = data.get('ddata') 1577 if not isinstance(mypath, basestring) and (mypath is not None): 1578 mypath = mypath.tostring() 1579 if mypath is not None: 1580 mypath = os.path.join(self.STORE_PATH,mypath) 1581 if os.path.isfile(mypath) and os.access(mypath,os.W_OK): 1582 os.remove(mypath) 1583 1584 self.remove_keywords(iddoc) 1585 self.execute_query('DELETE FROM entropy_docs WHERE `iddoc` = %s AND `iddoctype` = %s',( 1586 iddoc, 1587 doc_type, 1588 ) 1589 ) 1590 if userid: 1591 self.update_user_score(userid) 1592 return True, (iddoc, None)

1593

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

1595 self.check_connection() 1596 if not self.gdata: 1597 return False, None 1598 1599 idkey = self.handle_pkgkey(pkgkey) 1600 video_path = os.path.realpath(video_path) 1601 if not (os.access(video_path,os.R_OK) and os.path.isfile(video_path)): 1602 return False 1603 virus_found, virus_type = self.scan_for_viruses(video_path) 1604 if virus_found: 1605 os.remove(video_path) 1606 return False, None 1607 1608 new_video_path = video_path 1609 if isinstance(file_name,basestring): 1610 # move file to the new filename 1611 new_video_path = os.path.join(os.path.dirname(video_path),os.path.basename(file_name)) # force basename 1612 scount = 0 1613 while os.path.lexists(new_video_path): 1614 scount += 1 1615 bpath = "%s.%s" % (unicode(scount),os.path.basename(file_name),) 1616 new_video_path = os.path.join(os.path.dirname(video_path),bpath) 1617 shutil.move(video_path,new_video_path) 1618 1619 yt_service = self.get_youtube_service() 1620 if yt_service == None: 1621 return False, None 1622 1623 mykeywords = ', '.join([x.strip().strip(',') for x in \ 1624 keywords.split() + ["sabayon"] if (x.strip() and x.strip(",") and \ 1625 (len(x.strip()) > 4))]) 1626 gd_keywords = self.gdata.media.Keywords(text = mykeywords) 1627 1628 mydescription = "%s: %s" % (pkgkey,description,) 1629 mytitle = "%s: %s" % (self.system_name,title,) 1630 my_media_group = self.gdata.media.Group( 1631 title = self.gdata.media.Title(text = mytitle), 1632 description = self.gdata.media.Description( 1633 description_type = 'plain', 1634 text = mydescription 1635 ), 1636 keywords = gd_keywords, 1637 category = self.gdata.media.Category( 1638 text = 'Tech', 1639 scheme = 'http://gdata.youtube.com/schemas/2007/categories.cat', 1640 label = 'Tech' 1641 ), 1642 player = None 1643 ) 1644 video_entry = self.gdata.youtube.YouTubeVideoEntry(media = my_media_group) 1645 new_entry = yt_service.InsertVideoEntry(video_entry, new_video_path) 1646 if not isinstance(new_entry, self.gdata.youtube.YouTubeVideoEntry): 1647 return False, None 1648 video_url = new_entry.GetSwfUrl() 1649 video_id = os.path.basename(video_url) 1650 1651 iddoc = self.insert_generic_doc(idkey, userid, username, 1652 self.DOC_TYPES['youtube_video'], video_id, title, description, 1653 keywords) 1654 if isinstance(iddoc,basestring): 1655 return False, (iddoc, None,) 1656 return True, (iddoc, video_id,)

1657

1658 - def remove_youtube_video(self, iddoc):

1659 self.check_connection() 1660 if not self.gdata: 1661 return False, None 1662 if not self.is_iddoc_available(iddoc): 1663 return False, None 1664 userid = self.get_iddoc_userid(iddoc) 1665 1666 yt_service = self.get_youtube_service() 1667 if yt_service == None: 1668 return False, None 1669 1670 def do_remove(): 1671 self.remove_keywords(iddoc) 1672 self.execute_query('DELETE FROM entropy_docs WHERE `iddoc` = %s AND `iddoctype` = %s',( 1673 iddoc, 1674 self.DOC_TYPES['youtube_video'], 1675 ) 1676 )

1677 1678 self.execute_query('SELECT `ddata` FROM entropy_docs WHERE `iddoc` = %s AND `iddoctype` = %s',( 1679 iddoc, 1680 self.DOC_TYPES['youtube_video'], 1681 ) 1682 ) 1683 data = self.fetchone() 1684 if data is None: 1685 do_remove() 1686 return False, None 1687 elif not data.has_key('ddata'): 1688 do_remove() 1689 return False, None 1690 1691 video_id = data.get('ddata') 1692 try: 1693 video_entry = yt_service.GetYouTubeVideoEntry(video_id = video_id) 1694 deleted = yt_service.DeleteVideoEntry(video_entry) 1695 except: 1696 deleted = True 1697 1698 if deleted: 1699 do_remove() 1700 if userid: self.update_user_score(userid) 1701 return deleted, (iddoc, video_id,)

Source Code for Module entropy.services.ugc.interfaces