entropy.services.ugc.interfaces

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

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

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

219

220 - def get_current_time(self):

221 return int(time.time())

222

223 - def cache_results(self):

224 for cache_item in self.cached_results: 225 fdata = self.cached_results.get(cache_item) 226 if fdata == None: return 227 func, args, kwargs, exp_time = fdata 228 key = self.get_cache_item_key(cache_item) 229 r = func(*args,**kwargs) 230 self.dumpTools.dumpobj(key, r)

231

232 - def get_cache_item_key(self, cache_item):

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

234

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

236 if not self.cached_results.get(cache_item): return None 237 key = self.get_cache_item_key(cache_item) 238 self.dumpTools.dumpobj(key, r)

239

240 - def _get_geoip_data_from_ip_address(self, ip_address):

241 geoip_dbpath = self.connection_data.get('geoip_dbpath', '') 242 if os.path.isfile(geoip_dbpath) and os.access(geoip_dbpath, os.R_OK): 243 try: 244 geo = self.EntropyGeoIP(geoip_dbpath) 245 return geo.get_geoip_record_from_ip(ip_address) 246 except: # lame, but I don't know what exceptions are thrown 247 pass

248 249 # expired get_ugc_alldownloads 0 86400 1228577077

250 - def get_cached_result(self, cache_item):

251 fdata = self.cached_results.get(cache_item) 252 if fdata == None: return None 253 func, args, kwargs, exp_time = fdata 254 255 key = self.get_cache_item_key(cache_item) 256 cur_time = self.get_current_time() 257 cache_time = self.dumpTools.getobjmtime(key) 258 if (cache_time + exp_time) < cur_time: 259 # expired 260 return None 261 return self.dumpTools.loadobj(key)

262

263 - def setup_store_path(self, path):

264 path = os.path.realpath(path) 265 if not os.path.isabs(path): 266 raise PermissionDenied('PermissionDenied: %s' % (_("not a valid directory path"),)) 267 if not os.path.isdir(path): 268 try: 269 os.makedirs(path) 270 except OSError as e: 271 raise PermissionDenied('PermissionDenied: %s' % (e,)) 272 if etpConst['entropygid'] != None: 273 const_setup_perms(path, etpConst['entropygid']) 274 self.STORE_PATH = path

275

276 - def initialize_tables(self):

277 notable = False 278 for table in self.SQL_TABLES: 279 if self.table_exists(table): 280 continue 281 notable = True 282 self.execute_script(self.SQL_TABLES[table]) 283 if notable: 284 self.commit()

285

286 - def initialize_doctypes(self):

287 for mydoctype in self.DOC_TYPES: 288 if self.is_iddoctype_available(self.DOC_TYPES[mydoctype]): 289 continue 290 self.insert_iddoctype(self.DOC_TYPES[mydoctype], mydoctype)

291

292 - def is_iddoctype_available(self, iddoctype):

293 self.check_connection() 294 rows = self.execute_query('SELECT `iddoctype` FROM entropy_doctypes WHERE `iddoctype` = %s', (iddoctype,)) 295 if rows: 296 return True 297 return False

298

299 - def is_pkgkey_available(self, key):

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

305

306 - def is_iddoc_available(self, iddoc):

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

312

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

314 self.check_connection() 315 self.execute_query('INSERT INTO entropy_doctypes VALUES (%s,%s)', (iddoctype, description,)) 316 if do_commit: self.commit()

317

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

319 self.check_connection() 320 self.execute_query('INSERT INTO entropy_base VALUES (%s,%s)', (None, key,)) 321 myid = self.lastrowid() 322 if do_commit: self.commit() 323 return myid

324

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

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

332

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

334 self.check_connection() 335 self.execute_query('INSERT INTO entropy_branches VALUES (%s,%s)', (None, branch,)) 336 myid = self.lastrowid() 337 if do_commit: self.commit() 338 return myid

339

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

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

346

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

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

353

354 - def handle_entropy_ip_locations_id(self, ip_addr):

355 entropy_ip_locations_id = 0 356 geo_data = self._get_geoip_data_from_ip_address(ip_addr) 357 if isinstance(geo_data, dict): 358 ip_lat = geo_data.get('latitude') 359 ip_long = geo_data.get('longitude') 360 if isinstance(ip_lat, float) and isinstance(ip_long, float): 361 ip_lat = round(ip_lat, 5) 362 ip_long = round(ip_long, 5) 363 entropy_ip_locations_id = self.get_entropy_ip_locations_id(ip_lat, ip_long) 364 if entropy_ip_locations_id == -1: 365 entropy_ip_locations_id = self.insert_entropy_ip_locations_id(ip_lat, ip_long) 366 return entropy_ip_locations_id

367

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

369 self.check_connection() 370 self.execute_query('UPDATE entropy_downloads SET `count` = `count`+1 WHERE `iddownload` = %s', (iddownload,)) 371 if do_commit: self.commit() 372 return iddownload

373

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

375 entropy_ip_locations_id = self.handle_entropy_ip_locations_id(ip_addr) 376 mydata = [(x, ip_addr, entropy_ip_locations_id,) for x in iddownloads] 377 self.execute_many('INSERT INTO entropy_downloads_data VALUES (%s,%s,%s)', mydata) 378 if do_commit: self.commit()

379

380 - def get_date(self):

381 mytime = time.time() 382 mydate = self.datetime.fromtimestamp(mytime) 383 mydate = self.datetime(mydate.year, mydate.month, mydate.day) 384 return mydate

385

386 - def get_datetime(self):

387 mytime = time.time() 388 mydate = self.datetime.fromtimestamp(mytime) 389 mydate = self.datetime(mydate.year, mydate.month, mydate.day, mydate.hour, mydate.minute, mydate.second) 390 return mydate

391

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

393 self.check_connection() 394 idkey = self.handle_pkgkey(key) 395 self.execute_query('SELECT `iddownload` FROM entropy_downloads WHERE `idkey` = %s AND `ddate` = %s', (idkey, ddate,)) 396 data = self.fetchone() 397 if data: 398 return data['iddownload'] 399 return -1

400

401 - def get_idkey(self, key):

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

408

409 - def get_iddoctype(self, iddoc):

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

416

417 - def get_entropy_branches_id(self, branch):

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

424

425 - def get_entropy_release_strings_id(self, release_string):

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

432

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

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

443

444 - def get_pkgkey(self, idkey):

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

449

450 - def get_ugc_metadata(self, pkgkey):

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

462

463 - def get_ugc_keywords(self, iddoc):

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

466

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

468 self.check_connection() 469 self.execute_query(""" 470 SELECT * FROM entropy_docs,entropy_base WHERE 471 entropy_docs.`idkey` = entropy_base.`idkey` AND 472 entropy_base.`key` = %s AND 473 entropy_docs.`iddoctype` IN %s 474 ORDER BY entropy_docs.`ts` ASC""", (pkgkey, typeslist,)) 475 return [self._get_ugc_extra_metadata(x) for x in self.fetchall()]

476

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

478 self.check_connection() 479 identifiers = list(identifiers) 480 if len(identifiers) < 2: 481 identifiers += [0] 482 typeslist = list(typeslist) 483 if len(typeslist) < 2: 484 typeslist += [0] 485 self.execute_query('SELECT * FROM entropy_docs WHERE `iddoc` IN %s AND `iddoctype` IN %s', (identifiers, typeslist,)) 486 return [self._get_ugc_extra_metadata(x) for x in self.fetchall()]

487

488 - def get_ugc_metadata_by_identifiers(self, identifiers):

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

495

496 - def _get_ugc_extra_metadata(self, mydict):

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

525

526 - def get_ugc_vote(self, pkgkey):

527 self.check_connection() 528 self.execute_query(""" 529 SELECT avg(entropy_votes.`vote`) as avg_vote FROM entropy_votes,entropy_base WHERE 530 entropy_base.`key` = %s AND 531 entropy_base.idkey = entropy_votes.idkey""", (pkgkey,)) 532 data = self.fetchone() or {} 533 avg_vote = data.get('avg_vote') 534 if not avg_vote: 535 return 0.0 536 return avg_vote

537

538 - def get_ugc_allvotes(self):

539 540 # cached? 541 cache_item = 'get_ugc_allvotes' 542 cached = self.get_cached_result(cache_item) 543 if cached != None: 544 return cached 545 546 self.check_connection() 547 self.execute_query(""" 548 SELECT entropy_base.`key` as `vkey`,avg(entropy_votes.vote) as `avg_vote` FROM 549 entropy_votes,entropy_base WHERE 550 entropy_votes.`idkey` = entropy_base.`idkey` GROUP BY entropy_base.`key`""") 551 vote_data = dict( ((x['vkey'], x['avg_vote'],) for x in self.fetchall()) ) 552 553 # do cache 554 self.cache_result(cache_item, vote_data) 555 return vote_data

556

557 - def get_ugc_downloads(self, pkgkey):

558 self.check_connection() 559 560 self.execute_query(""" 561 SELECT SQL_CACHE sum(entropy_downloads.`count`) as `tot_downloads` FROM 562 entropy_downloads,entropy_base WHERE entropy_base.key = %s AND 563 entropy_base.idkey = entropy_downloads.idkey""", (pkgkey,)) 564 565 data = self.fetchone() or {} 566 downloads = data.get('tot_downloads') 567 if not downloads: 568 return 0 569 return downloads

570

571 - def get_ugc_alldownloads(self):

572 # cached? 573 cache_item = 'get_ugc_alldownloads' 574 cached = self.get_cached_result(cache_item) 575 if cached != None: return cached 576 577 self.check_connection() 578 self.execute_query(""" 579 SELECT SQL_CACHE entropy_base.key as vkey, tot_downloads from entropy_base, 580 (SELECT idkey as idp1, sum(entropy_downloads.`count`) AS `tot_downloads` FROM 581 entropy_downloads GROUP BY entropy_downloads.`idkey`) as tmp where idkey = tmp.idp1; 582 """) 583 down_data = dict( ((x['vkey'], x['tot_downloads'],) for x in self.fetchall()) ) 584 585 # do cache 586 self.cache_result(cache_item, down_data) 587 return down_data

588

589 - def get_iddoc_userid(self, iddoc):

590 self.check_connection() 591 self.execute_query('SELECT `userid` FROM entropy_docs WHERE `iddoc` = %s', (iddoc,)) 592 data = self.fetchone() or {} 593 return data.get('userid', None)

594

595 - def get_total_comments_count(self):

596 self.check_connection() 597 self.execute_query('SELECT count(`iddoc`) as comments FROM entropy_docs WHERE `iddoctype` = %s', (self.DOC_TYPES['comments'],)) 598 data = self.fetchone() or {} 599 comments = data.get('comments') 600 if not comments: 601 return 0 602 return comments

603

604 - def get_total_documents_count(self):

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

612

613 - def get_total_votes_count(self):

614 self.check_connection() 615 self.execute_query('SELECT count(`idvote`) as votes FROM entropy_votes') 616 data = self.fetchone() or {} 617 votes = data.get('votes') 618 if not votes: 619 return 0 620 return votes

621

622 - def get_total_downloads_count(self):

623 624 # cached? 625 cache_item = 'get_total_downloads_count' 626 cached = self.get_cached_result(cache_item) 627 if cached != None: return cached 628 629 self.check_connection() 630 self.execute_query('SELECT SQL_CACHE sum(entropy_downloads.`count`) as downloads FROM entropy_downloads') 631 data = self.fetchone() or {} 632 downloads = data.get('downloads', 0) 633 if not downloads: 634 return 0 635 result = int(downloads) 636 637 # do cache 638 self.cache_result(cache_item, result) 639 return result

640

641 - def get_user_score_ranking(self, userid):

642 self.check_connection() 643 self.execute_query('SET @row = 0') 644 self.execute_query(""" 645 SELECT Row, col_a FROM (SELECT @row := @row + 1 AS Row, userid AS col_a FROM 646 entropy_user_scores ORDER BY score DESC) As derived1 WHERE col_a = %s""", (userid,)) 647 data = self.fetchone() or {} 648 ranking = data.get('Row', 0) # key can be avail but == None 649 if not ranking: 650 return 0 651 return ranking

652

653 - def get_users_scored_count(self):

654 655 # cached? 656 cache_item = 'get_users_scored_count' 657 cached = self.get_cached_result(cache_item) 658 if cached != None: 659 return cached 660 661 self.check_connection() 662 self.execute_query('SELECT SQL_CACHE count(`userid`) as mycount FROM entropy_user_scores') 663 data = self.fetchone() or {} 664 count = data.get('mycount', 0) 665 if not count: 666 return 0 667 668 # do cache 669 self.cache_result(cache_item, count) 670 return count

671

672 - def is_user_score_available(self, userid):

673 self.check_connection() 674 rows = self.execute_query('SELECT `userid` FROM entropy_user_scores WHERE `userid` = %s', (userid,)) 675 if rows: 676 return True 677 return False

678

679 - def calculate_user_score(self, userid):

680 comments = self.get_user_comments_count(userid) 681 docs = self.get_user_docs_count(userid) 682 votes = self.get_user_votes_count(userid) 683 return (comments*self.COMMENTS_SCORE_WEIGHT) + \ 684 (docs*self.DOCS_SCORE_WEIGHT) + \ 685 (votes*self.VOTES_SCORE_WEIGHT)

686

687 - def update_user_score(self, userid):

688 self.check_connection() 689 avail = self.is_user_score_available(userid) 690 myscore = self.calculate_user_score(userid) 691 if avail: 692 self.execute_query('UPDATE entropy_user_scores SET score = %s WHERE `userid` = %s', (myscore, userid,)) 693 else: 694 self.execute_query('INSERT INTO entropy_user_scores VALUES (%s,%s)', (userid, myscore,)) 695 return myscore

696

697 - def get_user_score(self, userid):

698 self.check_connection() 699 self.execute_query('SELECT score FROM entropy_user_scores WHERE userid = %s', (userid,)) 700 data = self.fetchone() or {} 701 myscore = data.get('score') 702 if myscore == None: 703 myscore = self.update_user_score(userid) 704 return myscore

705

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

707 self.check_connection() 708 limit_string = '' 709 if count: 710 limit_string += ' LIMIT %s,%s' % (offset, count,) 711 self.execute_query('SELECT SQL_CALC_FOUND_ROWS *, userid, score FROM entropy_user_scores ORDER BY score DESC' + limit_string) 712 data = self.fetchall() 713 714 self.execute_query('SELECT FOUND_ROWS() as count') 715 rdata = self.fetchone() or {} 716 found_rows = rdata.get('count', 0) 717 if found_rows is None: 718 found_rows = 0 719 720 return found_rows, data

721

722 - def get_user_votes_average(self, userid):

723 self.check_connection() 724 self.execute_query('SELECT avg(`vote`) as vote_avg FROM entropy_votes WHERE `userid` = %s', (userid,)) 725 data = self.fetchone() or {} 726 vote_avg = data.get('vote_avg', 0.0) 727 if not vote_avg: 728 return 0.0 729 return round(float(vote_avg), 2)

730

731 - def get_user_alldocs(self, userid):

732 self.check_connection() 733 self.execute_query(""" 734 SELECT * FROM entropy_docs,entropy_base WHERE 735 entropy_docs.`userid` = %s AND 736 entropy_base.idkey = entropy_docs.idkey ORDER BY entropy_base.`key`""", (userid,)) 737 return self.fetchall()

738

739 - def get_user_docs(self, userid):

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

741

742 - def get_user_comments(self, userid):

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

744

745 - def get_user_votes(self, userid):

746 self.check_connection() 747 self.execute_query('SELECT * FROM entropy_votes WHERE `userid` = %s', (userid,)) 748 return self.fetchall()

749

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

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

754

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

756 self.check_connection() 757 self.execute_query('SELECT count(`iddoc`) as docs FROM entropy_docs WHERE `userid` = %s AND `iddoctype` '+doctype_sql_cmp+' %s', (userid, doctype,)) 758 data = self.fetchone() or {} 759 docs = data.get('docs', 0) 760 if docs is None: 761 docs = 0 762 return docs

763

764 - def get_user_comments_count(self, userid):

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

766

767 - def get_user_docs_count(self, userid):

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

769

770 - def get_user_images_count(self, userid):

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

772

773 - def get_user_files_count(self, userid):

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

775

776 - def get_user_yt_videos_count(self, userid):

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

778

779 - def get_user_votes_count(self, userid):

780 self.check_connection() 781 self.execute_query('SELECT count(`idvote`) as votes FROM entropy_votes WHERE `userid` = %s', (userid,)) 782 data = self.fetchone() or {} 783 votes = data.get('votes', 0) 784 if votes is None: 785 votes = 0 786 return votes

787

788 - def get_user_stats(self, userid):

789 mydict = {} 790 mydict['comments'] = self.get_user_comments_count(userid) 791 mydict['docs'] = self.get_user_docs_count(userid) 792 mydict['images'] = self.get_user_images_count(userid) 793 mydict['files'] = self.get_user_files_count(userid) 794 mydict['yt_videos'] = self.get_user_yt_videos_count(userid) 795 mydict['votes'] = self.get_user_votes_count(userid) 796 mydict['votes_avg'] = self.get_user_votes_average(userid) 797 mydict['total_docs'] = mydict['comments'] + mydict['docs'] 798 mydict['score'] = self.get_user_score(userid) 799 mydict['ranking'] = self.get_user_score_ranking(userid) 800 return mydict

801

802 - def get_distribution_stats(self):

803 self.check_connection() 804 mydict = {} 805 mydict['comments'] = self.get_total_comments_count() 806 mydict['documents'] = self.get_total_documents_count() 807 mydict['votes'] = self.get_total_votes_count() 808 mydict['downloads'] = self.get_total_downloads_count() 809 return mydict

810

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

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

874

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

876 self.check_connection() 877 878 if iddoctypes is None: 879 iddoctypes = list(self.DOC_TYPES.values()) 880 881 if len(iddoctypes) == 1: 882 iddoctypes_str = " = %s" % (iddoctypes[0],) 883 else: 884 iddoctypes_str = "IN ("+', '.join([str(x) for x in iddoctypes])+")" 885 886 myterm = "%"+pkgkey_string+"%" 887 888 search_params = [myterm, results_offset, results_limit] 889 890 order_by_string = '' 891 if order_by == "key": 892 order_by_string = 'ORDER BY entropy_base.`key`' 893 elif order_by == "username": 894 order_by_string = 'ORDER BY entropy_docs.`username`' 895 elif order_by == "vote": 896 order_by_string = 'ORDER BY avg_vote DESC' 897 elif order_by == "downloads": 898 order_by_string = 'ORDER BY tot_downloads DESC' 899 900 self.execute_query(""" 901 SELECT 902 SQL_CALC_FOUND_ROWS entropy_base.`key`, 903 entropy_docs.*, 904 entropy_downloads.iddownload, 905 entropy_downloads.ddate, 906 entropy_votes.idvote, 907 entropy_votes.vdate, 908 entropy_votes.vote, 909 avg(entropy_votes.vote) as avg_vote, 910 sum(entropy_downloads.`count`) as tot_downloads, 911 entropy_user_scores.score as `score` 912 FROM 913 entropy_base 914 LEFT JOIN entropy_votes ON entropy_base.idkey = entropy_votes.idkey 915 LEFT JOIN entropy_docs ON entropy_base.idkey = entropy_docs.idkey 916 LEFT JOIN entropy_docs as ed ON entropy_base.idkey = ed.idkey 917 LEFT JOIN entropy_user_scores on entropy_docs.userid = entropy_user_scores.userid 918 LEFT JOIN entropy_downloads on entropy_base.idkey = entropy_downloads.idkey 919 WHERE 920 entropy_docs.`username` LIKE %s AND 921 entropy_docs.iddoctype """+iddoctypes_str+""" 922 GROUP BY entropy_docs.`iddoc` 923 """+order_by_string+""" 924 LIMIT %s,%s 925 """, search_params) 926 927 results = self.fetchall() 928 # avg_vote can be POTENTIALLY None, we need to change it to float(0.0) 929 for item in results: 930 if item['avg_vote'] is None: 931 item['avg_vote'] = 0.0 932 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_content_items(self, pkgkey_string, iddoctypes = None, results_offset = 0, results_limit = 30, order_by = None):

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

1003

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

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

1069

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

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

1137

1138 - def handle_pkgkey(self, key):

1139 idkey = self.get_idkey(key) 1140 if idkey == -1: 1141 return self.insert_pkgkey(key) 1142 return idkey

1143

1144 - def insert_flood_control_check(self, userid):

1145 self.check_connection() 1146 self.execute_query('SELECT max(`ts`) as ts FROM entropy_docs WHERE `userid` = %s', (userid,)) 1147 data = self.fetchone() 1148 if not data: 1149 return False 1150 elif 'ts' not in data: 1151 return False 1152 elif data['ts'] == None: 1153 return False 1154 delta = self.datetime.fromtimestamp(time.time()) - data['ts'] 1155 if (delta.days == 0) and (delta.seconds <= self.FLOOD_INTERVAL): 1156 return True 1157 return False

1158

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

1160 self.check_connection() 1161 1162 title = title[:self.entropy_docs_title_len] 1163 description = description[:self.entropy_docs_description_len] 1164 1165 # flood control 1166 flood_risk = self.insert_flood_control_check(userid) 1167 if flood_risk: 1168 return 'flooding detected' 1169 1170 self.execute_query('INSERT INTO entropy_docs VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s)', ( 1171 None, 1172 idkey, 1173 userid, 1174 username, 1175 doc_type, 1176 data, 1177 title, 1178 description, 1179 None, 1180 ) 1181 ) 1182 if do_commit: 1183 self.commit() 1184 iddoc = self.lastrowid() 1185 self.insert_keywords(iddoc, keywords) 1186 self.update_user_score(userid) 1187 return iddoc

1188

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

1190 keywords = keywords.split(",") 1191 clean_keys = [] 1192 for key in keywords: 1193 try: 1194 key = key.strip().split()[0] 1195 except IndexError: 1196 continue 1197 if not key: continue 1198 if not key.isalnum(): continue 1199 key = key[:self.entropy_docs_keyword_len] 1200 if key in clean_keys: continue 1201 clean_keys.append(key) 1202 1203 if not clean_keys: 1204 return 1205 1206 mydata = [] 1207 for key in clean_keys: 1208 mydata.append((iddoc, key,)) 1209 1210 self.execute_many('INSERT INTO entropy_docs_keywords VALUES (%s,%s)', mydata)

1211

1212 - def remove_keywords(self, iddoc):

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

1214

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

1216 self.check_connection() 1217 idkey = self.handle_pkgkey(pkgkey) 1218 iddoc = self.insert_generic_doc(idkey, userid, username, 1219 self.DOC_TYPES['comments'], comment, title, '', keywords) 1220 if isinstance(iddoc, const_get_stringtype()): 1221 return False, iddoc 1222 if do_commit: 1223 self.commit() 1224 return True, iddoc

1225

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

1227 self.check_connection() 1228 if not self.is_iddoc_available(iddoc): 1229 return False 1230 new_title = new_title[:self.entropy_docs_title_len] 1231 self.execute_query('UPDATE entropy_docs SET `ddata` = %s, `title` = %s WHERE `iddoc` = %s AND `iddoctype` = %s', ( 1232 new_comment, 1233 new_title, 1234 iddoc, 1235 self.DOC_TYPES['comments'], 1236 ) 1237 ) 1238 self.remove_keywords(iddoc) 1239 self.insert_keywords(iddoc, new_keywords) 1240 if do_commit: 1241 self.commit() 1242 return True, iddoc

1243

1244 - def remove_comment(self, iddoc):

1245 self.check_connection() 1246 userid = self.get_iddoc_userid(iddoc) 1247 self.remove_keywords(iddoc) 1248 self.execute_query('DELETE FROM entropy_docs WHERE `iddoc` = %s AND `iddoctype` = %s', ( 1249 iddoc, 1250 self.DOC_TYPES['comments'], 1251 ) 1252 ) 1253 if userid: 1254 self.update_user_score(userid) 1255 return True, iddoc

1256 1257 # give a vote to an app

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

1259 self.check_connection() 1260 idkey = self.handle_pkgkey(pkgkey) 1261 vote = int(vote) 1262 if vote not in self.VOTE_RANGE: # weird 1263 vote = 3 # avg? 1264 mydate = self.get_date() 1265 if self.has_user_already_voted(pkgkey, userid): 1266 return False 1267 self.execute_query('INSERT INTO entropy_votes VALUES (%s,%s,%s,%s,%s,%s)', ( 1268 None, 1269 idkey, 1270 userid, 1271 mydate, 1272 vote, 1273 None, 1274 ) 1275 ) 1276 if do_commit: 1277 self.commit() 1278 self.update_user_score(userid) 1279 return True

1280

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

1282 self.check_connection() 1283 idkey = self.handle_pkgkey(pkgkey) 1284 self.execute_query('SELECT `idvote` FROM entropy_votes WHERE `idkey` = %s AND `userid` = %s', (idkey, userid,)) 1285 data = self.fetchone() 1286 if data: 1287 return True 1288 return False

1289 1290 # icrement +1 download usage for the provided package keys

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

1292 self.check_connection() 1293 mydate = self.get_date() 1294 iddownloads = set() 1295 for pkgkey in pkgkeys: 1296 iddownload = self.get_iddownload(pkgkey, mydate) 1297 if iddownload == -1: 1298 iddownload = self.insert_download(pkgkey, mydate, count = 1) 1299 else: 1300 self.update_download(iddownload) 1301 if (iddownload > 0) and isinstance(ip_addr, const_get_stringtype()): 1302 iddownloads.add(iddownload) 1303 1304 if iddownloads: 1305 self.store_download_data(iddownloads, ip_addr) 1306 if do_commit: 1307 self.commit() 1308 return True

1309

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

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

1366

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

1368 1369 self.execute_query('INSERT INTO entropy_hardware_usage VALUES (%s,%s,%s)', ( 1370 None, 1371 entropy_distribution_usage_id, 1372 hw_hash, 1373 ) 1374 )

1375

1376 - def is_entropy_hardware_usage_stats_available(self, entropy_distribution_usage_id):

1377 self.check_connection() 1378 self.execute_query('SELECT entropy_hardware_usage_id FROM entropy_hardware_usage WHERE `entropy_distribution_usage_id` = %s', (entropy_distribution_usage_id,)) 1379 data = self.fetchone() 1380 if data: 1381 return True 1382 return False

1383

1384 - def is_user_ip_available_in_entropy_distribution_usage(self, ip_address):

1385 self.check_connection() 1386 self.execute_query('SELECT entropy_distribution_usage_id FROM entropy_distribution_usage WHERE `ip_address` = %s', (ip_address,)) 1387 data = self.fetchone() or {} 1388 myid = data.get('entropy_distribution_usage_id') 1389 if myid is None: 1390 return -1 1391 return myid

1392

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

1395 self.check_connection() 1396 idkey = self.handle_pkgkey(pkgkey) 1397 if doc_type == None: doc_type = self.DOC_TYPES['bbcode_doc'] 1398 iddoc = self.insert_generic_doc(idkey, userid, username, doc_type, 1399 text, title, description, keywords) 1400 if isinstance(iddoc, const_get_stringtype()): 1401 return False, iddoc 1402 if do_commit: 1403 self.commit() 1404 return True, iddoc

1405

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

1407 self.check_connection() 1408 if not self.is_iddoc_available(iddoc): 1409 return False 1410 1411 new_title = new_title[:self.entropy_docs_title_len] 1412 1413 self.execute_query('UPDATE entropy_docs SET `ddata` = %s, `title` = %s WHERE `iddoc` = %s AND `iddoctype` = %s', ( 1414 new_document, 1415 new_title, 1416 iddoc, 1417 self.DOC_TYPES['bbcode_doc'], 1418 ) 1419 ) 1420 self.remove_keywords(iddoc) 1421 self.insert_keywords(iddoc, new_keywords) 1422 if do_commit: 1423 self.commit() 1424 return True, iddoc

1425

1426 - def remove_document(self, iddoc):

1427 self.check_connection() 1428 userid = self.get_iddoc_userid(iddoc) 1429 self.remove_keywords(iddoc) 1430 self.execute_query('DELETE FROM entropy_docs WHERE `iddoc` = %s AND `iddoctype` = %s', ( 1431 iddoc, 1432 self.DOC_TYPES['bbcode_doc'], 1433 ) 1434 ) 1435 if userid: 1436 self.update_user_score(userid) 1437 return True, iddoc

1438

1439 - def scan_for_viruses(self, filepath):

1440 1441 if not os.access(filepath, os.R_OK): 1442 return False, None 1443 1444 args = [self.VIRUS_CHECK_EXEC] 1445 args += self.VIRUS_CHECK_ARGS 1446 args += [filepath] 1447 f = open("/dev/null", "w") 1448 p = subprocess.Popen(args, stdout = f, stderr = f) 1449 rc = p.wait() 1450 f.close() 1451 if rc == 1: 1452 return True, None 1453 return False, None

1454

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

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

1538

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

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

1543

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

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

1549

1550 - def delete_image(self, iddoc):

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

1552

1553 - def delete_file(self, iddoc):

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

1555

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

1557 self.check_connection() 1558 1559 userid = self.get_iddoc_userid(iddoc) 1560 self.execute_query('SELECT `ddata` FROM entropy_docs WHERE `iddoc` = %s AND `iddoctype` = %s', ( 1561 iddoc, 1562 doc_type, 1563 ) 1564 ) 1565 data = self.fetchone() or {} 1566 mypath = data.get('ddata') 1567 if not isinstance(mypath, const_get_stringtype()) and (mypath is not None): 1568 mypath = mypath.tostring() 1569 if mypath is not None: 1570 mypath = os.path.join(self.STORE_PATH, mypath) 1571 if os.path.isfile(mypath) and os.access(mypath, os.W_OK): 1572 os.remove(mypath) 1573 1574 self.remove_keywords(iddoc) 1575 self.execute_query('DELETE FROM entropy_docs WHERE `iddoc` = %s AND `iddoctype` = %s', ( 1576 iddoc, 1577 doc_type, 1578 ) 1579 ) 1580 if userid: 1581 self.update_user_score(userid) 1582 return True, (iddoc, None)

1583

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

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

1647

1648 - def remove_youtube_video(self, iddoc):

1649 self.check_connection() 1650 if not self.gdata: 1651 return False, None 1652 if not self.is_iddoc_available(iddoc): 1653 return False, None 1654 userid = self.get_iddoc_userid(iddoc) 1655 1656 yt_service = self.get_youtube_service() 1657 if yt_service == None: 1658 return False, None 1659 1660 def do_remove(): 1661 self.remove_keywords(iddoc) 1662 self.execute_query('DELETE FROM entropy_docs WHERE `iddoc` = %s AND `iddoctype` = %s', ( 1663 iddoc, 1664 self.DOC_TYPES['youtube_video'], 1665 ) 1666 )

1667 1668 self.execute_query('SELECT `ddata` FROM entropy_docs WHERE `iddoc` = %s AND `iddoctype` = %s', ( 1669 iddoc, 1670 self.DOC_TYPES['youtube_video'], 1671 ) 1672 ) 1673 data = self.fetchone() 1674 if data is None: 1675 do_remove() 1676 return False, None 1677 elif 'ddata' not in data: 1678 do_remove() 1679 return False, None 1680 1681 video_id = data.get('ddata') 1682 try: 1683 video_entry = yt_service.GetYouTubeVideoEntry(video_id = video_id) 1684 deleted = yt_service.DeleteVideoEntry(video_entry) 1685 except: 1686 deleted = True 1687 1688 if deleted: 1689 do_remove() 1690 if userid: self.update_user_score(userid) 1691 return deleted, (iddoc, video_id,)

Source Code for Module entropy.services.ugc.interfaces