app/models.py

   1 # Content DB
   2 # Copyright (C) 2018  rubenwardy
   3 #
   4 # This program is free software: you can redistribute it and/or modify
   5 # it under the terms of the GNU General Public License as published by
   6 # the Free Software Foundation, either version 3 of the License, or
   7 # (at your option) any later version.
   8 #
   9 # This program is distributed in the hope that it will be useful,
  10 # but WITHOUT ANY WARRANTY; without even the implied warranty of
  11 # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
  12 # GNU General Public License for more details.
  13 #
  14 # You should have received a copy of the GNU General Public License
  15 # along with this program.  If not, see <https://www.gnu.org/licenses/>.
  16
  17
  18 import enum, datetime
  19
  20 from app import app, gravatar
  21 from urllib.parse import urlparse
  22
  23 from flask import Flask, url_for
  24 from flask_sqlalchemy import SQLAlchemy, BaseQuery
  25 from flask_migrate import Migrate
  26 from flask_user import login_required, UserManager, UserMixin
  27 from sqlalchemy import func, CheckConstraint
  28 from sqlalchemy_searchable import SearchQueryMixin
  29 from sqlalchemy_utils.types import TSVectorType
  30 from sqlalchemy_searchable import make_searchable
  31
  32
  33 # Initialise database
  34 db = SQLAlchemy(app)
  35 migrate = Migrate(app, db)
  36 make_searchable(db.metadata)
  37
  38
  39 class ArticleQuery(BaseQuery, SearchQueryMixin):
  40         pass
  41
  42
  43 class UserRank(enum.Enum):
  44         BANNED         = 0
  45         NOT_JOINED     = 1
  46         NEW_MEMBER     = 2
  47         MEMBER         = 3
  48         TRUSTED_MEMBER = 4
  49         EDITOR         = 5
  50         MODERATOR      = 6
  51         ADMIN          = 7
  52
  53         def atLeast(self, min):
  54                 return self.value >= min.value
  55
  56         def getTitle(self):
  57                 return self.name.replace("_", " ").title()
  58
  59         def toName(self):
  60                 return self.name.lower()
  61
  62         def __str__(self):
  63                 return self.name
  64
  65         @classmethod
  66         def choices(cls):
  67                 return [(choice, choice.getTitle()) for choice in cls]
  68
  69         @classmethod
  70         def coerce(cls, item):
  71                 return item if type(item) == UserRank else UserRank[item]
  72
  73
  74 class Permission(enum.Enum):
  75         EDIT_PACKAGE       = "EDIT_PACKAGE"
  76         APPROVE_CHANGES    = "APPROVE_CHANGES"
  77         DELETE_PACKAGE     = "DELETE_PACKAGE"
  78         CHANGE_AUTHOR      = "CHANGE_AUTHOR"
  79         CHANGE_NAME        = "CHANGE_NAME"
  80         MAKE_RELEASE       = "MAKE_RELEASE"
  81         DELETE_RELEASE     = "DELETE_RELEASE"
  82         ADD_SCREENSHOTS    = "ADD_SCREENSHOTS"
  83         APPROVE_SCREENSHOT = "APPROVE_SCREENSHOT"
  84         APPROVE_RELEASE    = "APPROVE_RELEASE"
  85         APPROVE_NEW        = "APPROVE_NEW"
  86         CHANGE_RELEASE_URL = "CHANGE_RELEASE_URL"
  87         CHANGE_DNAME       = "CHANGE_DNAME"
  88         CHANGE_RANK        = "CHANGE_RANK"
  89         CHANGE_EMAIL       = "CHANGE_EMAIL"
  90         EDIT_EDITREQUEST   = "EDIT_EDITREQUEST"
  91         SEE_THREAD         = "SEE_THREAD"
  92         CREATE_THREAD      = "CREATE_THREAD"
  93         UNAPPROVE_PACKAGE  = "UNAPPROVE_PACKAGE"
  94         TOPIC_DISCARD      = "TOPIC_DISCARD"
  95         CREATE_TOKEN       = "CREATE_TOKEN"
  96         CHANGE_PROFILE_URLS = "CHANGE_PROFILE_URLS"
  97
  98         # Only return true if the permission is valid for *all* contexts
  99         # See Package.checkPerm for package-specific contexts
 100         def check(self, user):
 101                 if not user.is_authenticated:
 102                         return False
 103
 104                 if self == Permission.APPROVE_NEW or \
 105                                 self == Permission.APPROVE_CHANGES    or \
 106                                 self == Permission.APPROVE_RELEASE    or \
 107                                 self == Permission.APPROVE_SCREENSHOT or \
 108                                 self == Permission.SEE_THREAD:
 109                         return user.rank.atLeast(UserRank.EDITOR)
 110                 else:
 111                         raise Exception("Non-global permission checked globally. Use Package.checkPerm or User.checkPerm instead.")
 112
 113 def display_name_default(context):
 114     return context.get_current_parameters()["username"]
 115
 116 class User(db.Model, UserMixin):
 117         id           = db.Column(db.Integer, primary_key=True)
 118
 119         # User authentication information
 120         username     = db.Column(db.String(50, collation="NOCASE"), nullable=False, unique=True, index=True)
 121         password     = db.Column(db.String(255), nullable=False, server_default="")
 122         reset_password_token = db.Column(db.String(100), nullable=False, server_default="")
 123
 124         rank         = db.Column(db.Enum(UserRank))
 125
 126         # Account linking
 127         github_username = db.Column(db.String(50, collation="NOCASE"), nullable=True, unique=True)
 128         forums_username = db.Column(db.String(50, collation="NOCASE"), nullable=True, unique=True)
 129
 130         # Access token for webhook setup
 131         github_access_token = db.Column(db.String(50), nullable=True, server_default=None)
 132
 133         # User email information
 134         email         = db.Column(db.String(255), nullable=True, unique=True)
 135         email_confirmed_at  = db.Column(db.DateTime())
 136
 137         # User information
 138         profile_pic   = db.Column(db.String(255), nullable=True, server_default=None)
 139         active        = db.Column("is_active", db.Boolean, nullable=False, server_default="0")
 140         display_name  = db.Column(db.String(100), nullable=False, default=display_name_default)
 141
 142         # Links
 143         website_url   = db.Column(db.String(255), nullable=True, default=None)
 144         donate_url    = db.Column(db.String(255), nullable=True, default=None)
 145
 146         # Content
 147         notifications = db.relationship("Notification", primaryjoin="User.id==Notification.user_id")
 148
 149         # causednotifs  = db.relationship("Notification", backref="causer", lazy="dynamic")
 150         packages      = db.relationship("Package", backref=db.backref("author", lazy="joined"), lazy="dynamic")
 151         requests      = db.relationship("EditRequest", backref="author", lazy="dynamic")
 152         threads       = db.relationship("Thread", backref="author", lazy="dynamic")
 153         tokens        = db.relationship("APIToken", backref="owner", lazy="dynamic")
 154         replies       = db.relationship("ThreadReply", backref="author", lazy="dynamic")
 155
 156         def __init__(self, username=None, active=False, email=None, password=""):
 157                 self.username = username
 158                 self.email_confirmed_at = datetime.datetime.now() - datetime.timedelta(days=6000)
 159                 self.display_name = username
 160                 self.active = active
 161                 self.email = email
 162                 self.password = password
 163                 self.rank = UserRank.NOT_JOINED
 164
 165         def hasPassword(self):
 166                 return self.password != ""
 167
 168         def canAccessTodoList(self):
 169                 return Permission.APPROVE_NEW.check(self) or \
 170                                 Permission.APPROVE_RELEASE.check(self) or \
 171                                 Permission.APPROVE_CHANGES.check(self)
 172
 173         def isClaimed(self):
 174                 return self.rank.atLeast(UserRank.NEW_MEMBER)
 175
 176         def getProfilePicURL(self):
 177                 if self.profile_pic:
 178                         return self.profile_pic
 179                 else:
 180                         return gravatar(self.email or "")
 181
 182         def checkPerm(self, user, perm):
 183                 if not user.is_authenticated:
 184                         return False
 185
 186                 if type(perm) == str:
 187                         perm = Permission[perm]
 188                 elif type(perm) != Permission:
 189                         raise Exception("Unknown permission given to User.checkPerm()")
 190
 191                 # Members can edit their own packages, and editors can edit any packages
 192                 if perm == Permission.CHANGE_AUTHOR:
 193                         return user.rank.atLeast(UserRank.EDITOR)
 194                 elif perm == Permission.CHANGE_RANK or perm == Permission.CHANGE_DNAME:
 195                         return user.rank.atLeast(UserRank.MODERATOR)
 196                 elif perm == Permission.CHANGE_EMAIL or perm == Permission.CHANGE_PROFILE_URLS:
 197                         return user == self or (user.rank.atLeast(UserRank.MODERATOR) and user.rank.atLeast(self.rank))
 198                 elif perm == Permission.CREATE_TOKEN:
 199                         if user == self:
 200                                 return user.rank.atLeast(UserRank.MEMBER)
 201                         else:
 202                                 return user.rank.atLeast(UserRank.MODERATOR) and user.rank.atLeast(self.rank)
 203                 else:
 204                         raise Exception("Permission {} is not related to users".format(perm.name))
 205
 206         def canCommentRL(self):
 207                 hour_ago = datetime.datetime.utcnow() - datetime.timedelta(hours=1)
 208                 return ThreadReply.query.filter_by(author=self) \
 209                         .filter(ThreadReply.created_at > hour_ago).count() < 4
 210
 211         def canOpenThreadRL(self):
 212                 hour_ago = datetime.datetime.utcnow() - datetime.timedelta(hours=1)
 213                 return Thread.query.filter_by(author=self) \
 214                         .filter(Thread.created_at > hour_ago).count() < 2
 215
 216         def __eq__(self, other):
 217                 if other is None:
 218                         return False
 219
 220                 if not self.is_authenticated or not other.is_authenticated:
 221                         return False
 222
 223                 assert self.id > 0
 224                 return self.id == other.id
 225
 226 class UserEmailVerification(db.Model):
 227         id      = db.Column(db.Integer, primary_key=True)
 228         user_id = db.Column(db.Integer, db.ForeignKey("user.id"))
 229         email   = db.Column(db.String(100))
 230         token   = db.Column(db.String(32))
 231         user    = db.relationship("User", foreign_keys=[user_id])
 232
 233 class Notification(db.Model):
 234         id        = db.Column(db.Integer, primary_key=True)
 235         user_id   = db.Column(db.Integer, db.ForeignKey("user.id"))
 236         causer_id = db.Column(db.Integer, db.ForeignKey("user.id"))
 237         user      = db.relationship("User", foreign_keys=[user_id])
 238         causer    = db.relationship("User", foreign_keys=[causer_id])
 239
 240         title     = db.Column(db.String(100), nullable=False)
 241         url       = db.Column(db.String(200), nullable=True)
 242
 243         def __init__(self, us, cau, titl, ur):
 244                 if len(titl) > 100:
 245                         title = title[:99] + "…"
 246
 247                 self.user   = us
 248                 self.causer = cau
 249                 self.title  = titl
 250                 self.url    = ur
 251
 252
 253 class License(db.Model):
 254         id      = db.Column(db.Integer, primary_key=True)
 255         name    = db.Column(db.String(50), nullable=False, unique=True)
 256         is_foss = db.Column(db.Boolean,    nullable=False, default=True)
 257
 258         def __init__(self, v, is_foss=True):
 259                 self.name = v
 260                 self.is_foss = is_foss
 261
 262         def __str__(self):
 263                 return self.name
 264
 265
 266 class PackageType(enum.Enum):
 267         MOD  = "Mod"
 268         GAME = "Game"
 269         TXP  = "Texture Pack"
 270
 271         def toName(self):
 272                 return self.name.lower()
 273
 274         def __str__(self):
 275                 return self.name
 276
 277         @classmethod
 278         def get(cls, name):
 279                 try:
 280                         return PackageType[name.upper()]
 281                 except KeyError:
 282                         return None
 283
 284         @classmethod
 285         def choices(cls):
 286                 return [(choice, choice.value) for choice in cls]
 287
 288         @classmethod
 289         def coerce(cls, item):
 290                 return item if type(item) == PackageType else PackageType[item]
 291
 292
 293 class PackagePropertyKey(enum.Enum):
 294         name          = "Name"
 295         title         = "Title"
 296         short_desc     = "Short Description"
 297         desc          = "Description"
 298         type          = "Type"
 299         license       = "License"
 300         media_license = "Media License"
 301         tags          = "Tags"
 302         provides      = "Provides"
 303         repo          = "Repository"
 304         website       = "Website"
 305         issueTracker  = "Issue Tracker"
 306         forums        = "Forum Topic ID"
 307
 308         def convert(self, value):
 309                 if self == PackagePropertyKey.tags:
 310                         return ",".join([t.title for t in value])
 311                 elif self == PackagePropertyKey.provides:
 312                         return ",".join([t.name for t in value])
 313                 else:
 314                         return str(value)
 315
 316 provides = db.Table("provides",
 317         db.Column("package_id",    db.Integer, db.ForeignKey("package.id"), primary_key=True),
 318     db.Column("metapackage_id", db.Integer, db.ForeignKey("meta_package.id"), primary_key=True)
 319 )
 320
 321 tags = db.Table("tags",
 322     db.Column("tag_id", db.Integer, db.ForeignKey("tag.id"), primary_key=True),
 323     db.Column("package_id", db.Integer, db.ForeignKey("package.id"), primary_key=True)
 324 )
 325
 326 class Dependency(db.Model):
 327         id              = db.Column(db.Integer, primary_key=True)
 328         depender_id     = db.Column(db.Integer, db.ForeignKey("package.id"),     nullable=True)
 329         package_id      = db.Column(db.Integer, db.ForeignKey("package.id"),     nullable=True)
 330         package         = db.relationship("Package", foreign_keys=[package_id])
 331         meta_package_id = db.Column(db.Integer, db.ForeignKey("meta_package.id"), nullable=True)
 332         optional        = db.Column(db.Boolean, nullable=False, default=False)
 333         __table_args__  = (db.UniqueConstraint("depender_id", "package_id", "meta_package_id", name="_dependency_uc"), )
 334
 335         def __init__(self, depender=None, package=None, meta=None):
 336                 if depender is None:
 337                         return
 338
 339                 self.depender = depender
 340
 341                 packageProvided = package is not None
 342                 metaProvided = meta is not None
 343
 344                 if packageProvided and not metaProvided:
 345                         self.package = package
 346                 elif metaProvided and not packageProvided:
 347                         self.meta_package = meta
 348                 else:
 349                         raise Exception("Either meta or package must be given, but not both!")
 350
 351         def getName(self):
 352                 if self.meta_package:
 353                         return self.meta_package.name
 354                 elif self.package:
 355                         return self.package.name
 356                 else:
 357                         assert False
 358
 359         def __str__(self):
 360                 if self.package is not None:
 361                         return self.package.author.username + "/" + self.package.name
 362                 elif self.meta_package is not None:
 363                         return self.meta_package.name
 364                 else:
 365                         raise Exception("Meta and package are both none!")
 366
 367         @staticmethod
 368         def SpecToList(depender, spec, cache={}):
 369                 retval = []
 370                 arr = spec.split(",")
 371
 372                 import re
 373                 pattern1 = re.compile("^([a-z0-9_]+)$")
 374                 pattern2 = re.compile("^([A-Za-z0-9_]+)/([a-z0-9_]+)$")
 375
 376                 for x in arr:
 377                         x = x.strip()
 378                         if x == "":
 379                                 continue
 380
 381                         if pattern1.match(x):
 382                                 meta = MetaPackage.GetOrCreate(x, cache)
 383                                 retval.append(Dependency(depender, meta=meta))
 384                         else:
 385                                 m = pattern2.match(x)
 386                                 username = m.group(1)
 387                                 name     = m.group(2)
 388                                 user = User.query.filter_by(username=username).first()
 389                                 if user is None:
 390                                         raise Exception("Unable to find user " + username)
 391
 392                                 package = Package.query.filter_by(author=user, name=name).first()
 393                                 if package is None:
 394                                         raise Exception("Unable to find package " + name + " by " + username)
 395
 396                                 retval.append(Dependency(depender, package=package))
 397
 398                 return retval
 399
 400
 401 class Package(db.Model):
 402         query_class  = ArticleQuery
 403
 404         id           = db.Column(db.Integer, primary_key=True)
 405
 406         # Basic details
 407         author_id    = db.Column(db.Integer, db.ForeignKey("user.id"))
 408         name         = db.Column(db.Unicode(100), nullable=False)
 409         title        = db.Column(db.Unicode(100), nullable=False)
 410         short_desc   = db.Column(db.Unicode(200), nullable=False)
 411         desc         = db.Column(db.UnicodeText, nullable=True)
 412         type         = db.Column(db.Enum(PackageType))
 413         created_at   = db.Column(db.DateTime, nullable=False, default=datetime.datetime.utcnow)
 414
 415         name_valid = db.CheckConstraint("name ~* '^[a-z0-9_]+$'")
 416
 417         search_vector = db.Column(TSVectorType("name", "title", "short_desc", "desc", \
 418                         weights={ "name": "A", "title": "B", "short_desc": "C", "desc": "D" }))
 419
 420         license_id   = db.Column(db.Integer, db.ForeignKey("license.id"), nullable=False, default=1)
 421         license      = db.relationship("License", foreign_keys=[license_id])
 422         media_license_id = db.Column(db.Integer, db.ForeignKey("license.id"), nullable=False, default=1)
 423         media_license    = db.relationship("License", foreign_keys=[media_license_id])
 424
 425         approved     = db.Column(db.Boolean, nullable=False, default=False)
 426         soft_deleted = db.Column(db.Boolean, nullable=False, default=False)
 427
 428         score        = db.Column(db.Float, nullable=False, default=0)
 429
 430         review_thread_id = db.Column(db.Integer, db.ForeignKey("thread.id"), nullable=True, default=None)
 431         review_thread    = db.relationship("Thread", foreign_keys=[review_thread_id])
 432
 433         # Downloads
 434         repo         = db.Column(db.String(200), nullable=True)
 435         website      = db.Column(db.String(200), nullable=True)
 436         issueTracker = db.Column(db.String(200), nullable=True)
 437         forums       = db.Column(db.Integer,     nullable=True)
 438
 439         provides = db.relationship("MetaPackage", \
 440                         secondary=provides, lazy="select", order_by=db.asc("name"), \
 441                         backref=db.backref("packages", lazy="dynamic", order_by=db.desc("score")))
 442
 443         dependencies = db.relationship("Dependency", backref="depender", lazy="dynamic", foreign_keys=[Dependency.depender_id])
 444
 445         tags = db.relationship("Tag", secondary=tags, lazy="select",
 446                         backref=db.backref("packages", lazy=True))
 447
 448         releases = db.relationship("PackageRelease", backref="package",
 449                         lazy="dynamic", order_by=db.desc("package_release_releaseDate"))
 450
 451         screenshots = db.relationship("PackageScreenshot", backref="package",
 452                         lazy="dynamic", order_by=db.asc("package_screenshot_id"))
 453
 454         requests = db.relationship("EditRequest", backref="package",
 455                         lazy="dynamic")
 456
 457         def __init__(self, package=None):
 458                 if package is None:
 459                         return
 460
 461                 self.author_id = package.author_id
 462                 self.created_at = package.created_at
 463                 self.approved = package.approved
 464
 465                 for e in PackagePropertyKey:
 466                         setattr(self, e.name, getattr(package, e.name))
 467
 468         def getIsFOSS(self):
 469                 return self.license.is_foss and self.media_license.is_foss
 470
 471         def getIsOnGitHub(self):
 472                 if self.repo is None:
 473                         return False
 474
 475                 url = urlparse(self.repo)
 476                 return url.netloc == "github.com"
 477
 478         def getGitHubFullName(self):
 479                 if self.repo is None:
 480                         return None
 481
 482                 url = urlparse(self.repo)
 483                 if url.netloc != "github.com":
 484                         return None
 485
 486                 import re
 487                 m = re.search(r"^\/([^\/]+)\/([^\/]+)\/?$", url.path)
 488                 if m is None:
 489                         return
 490
 491                 user = m.group(1)
 492                 repo = m.group(2).replace(".git", "")
 493
 494                 return (user,repo)
 495
 496         def getSortedDependencies(self, is_hard=None):
 497                 query = self.dependencies
 498                 if is_hard is not None:
 499                         query = query.filter_by(optional=not is_hard)
 500
 501                 deps = query.all()
 502                 deps.sort(key = lambda x: x.getName())
 503                 return deps
 504
 505         def getSortedHardDependencies(self):
 506                 return self.getSortedDependencies(True)
 507
 508         def getSortedOptionalDependencies(self):
 509                 return self.getSortedDependencies(False)
 510
 511         def getState(self):
 512                 if self.approved:
 513                         return "approved"
 514                 elif self.review_thread_id:
 515                         return "thread"
 516                 elif (self.type == PackageType.GAME or \
 517                                         self.type == PackageType.TXP) and \
 518                                 self.screenshots.count() == 0:
 519                         return "wip"
 520                 elif not self.getDownloadRelease():
 521                         return "wip"
 522                 elif "Other" in self.license.name or "Other" in self.media_license.name:
 523                         return "license"
 524                 else:
 525                         return "ready"
 526
 527         def getAsDictionaryKey(self):
 528                 return {
 529                         "name": self.name,
 530                         "author": self.author.display_name,
 531                         "type": self.type.toName(),
 532                 }
 533
 534         def getAsDictionaryShort(self, base_url, version=None, protonum=None):
 535                 tnurl = self.getThumbnailURL(1)
 536                 release = self.getDownloadRelease(version=version, protonum=protonum)
 537                 return {
 538                         "name": self.name,
 539                         "title": self.title,
 540                         "author": self.author.display_name,
 541                         "short_description": self.short_desc,
 542                         "type": self.type.toName(),
 543                         "release": release and release.id,
 544                         "thumbnail": (base_url + tnurl) if tnurl is not None else None
 545                 }
 546
 547         def getAsDictionary(self, base_url, version=None, protonum=None):
 548                 tnurl = self.getThumbnailURL(1)
 549                 release = self.getDownloadRelease(version=version, protonum=protonum)
 550                 return {
 551                         "author": self.author.display_name,
 552                         "name": self.name,
 553                         "title": self.title,
 554                         "short_description": self.short_desc,
 555                         "desc": self.desc,
 556                         "type": self.type.toName(),
 557                         "created_at": self.created_at.isoformat(),
 558
 559                         "license": self.license.name,
 560                         "media_license": self.media_license.name,
 561
 562                         "repo": self.repo,
 563                         "website": self.website,
 564                         "issue_tracker": self.issueTracker,
 565                         "forums": self.forums,
 566
 567                         "provides": [x.name for x in self.provides],
 568                         "thumbnail": (base_url + tnurl) if tnurl is not None else None,
 569                         "screenshots": [base_url + ss.url for ss in self.screenshots],
 570
 571                         "url": base_url + self.getDownloadURL(),
 572                         "release": release and release.id,
 573
 574                         "score": round(self.score * 10) / 10
 575                 }
 576
 577         def getThumbnailURL(self, level=2):
 578                 screenshot = self.screenshots.filter_by(approved=True).order_by(db.asc(PackageScreenshot.id)).first()
 579                 return screenshot.getThumbnailURL(level) if screenshot is not None else None
 580
 581         def getMainScreenshotURL(self):
 582                 screenshot = self.screenshots.filter_by(approved=True).order_by(db.asc(PackageScreenshot.id)).first()
 583                 return screenshot.url if screenshot is not None else None
 584
 585         def getDetailsURL(self):
 586                 return url_for("packages.view",
 587                                 author=self.author.username, name=self.name)
 588
 589         def getEditURL(self):
 590                 return url_for("packages.create_edit",
 591                                 author=self.author.username, name=self.name)
 592
 593         def getApproveURL(self):
 594                 return url_for("packages.approve",
 595                                 author=self.author.username, name=self.name)
 596
 597         def getRemoveURL(self):
 598                 return url_for("packages.remove",
 599                                 author=self.author.username, name=self.name)
 600
 601         def getNewScreenshotURL(self):
 602                 return url_for("packages.create_screenshot",
 603                                 author=self.author.username, name=self.name)
 604
 605         def getCreateReleaseURL(self):
 606                 return url_for("packages.create_release",
 607                                 author=self.author.username, name=self.name)
 608
 609         def getCreateEditRequestURL(self):
 610                 return url_for("create_edit_editrequest_page",
 611                                 author=self.author.username, name=self.name)
 612
 613         def getBulkReleaseURL(self):
 614                 return url_for("packages.bulk_change_release",
 615                         author=self.author.username, name=self.name)
 616
 617         def getDownloadURL(self):
 618                 return url_for("packages.download",
 619                                 author=self.author.username, name=self.name)
 620
 621         def getDownloadRelease(self, version=None, protonum=None):
 622                 if version is None and protonum is not None:
 623                         version = MinetestRelease.query.filter(MinetestRelease.protocol >= int(protonum)).first()
 624                         if version is not None:
 625                                 version = version.id
 626                         else:
 627                                 version = 10000000
 628
 629
 630                 for rel in self.releases:
 631                         if rel.approved and (version is None or
 632                                         ((rel.min_rel is None or rel.min_rel_id <= version) and \
 633                                         (rel.max_rel is None or rel.max_rel_id >= version))):
 634                                 return rel
 635
 636                 return None
 637
 638         def getDownloadCount(self):
 639                 counter = 0
 640                 for release in self.releases:
 641                         counter += release.downloads
 642                 return counter
 643
 644         def checkPerm(self, user, perm):
 645                 if not user.is_authenticated:
 646                         return False
 647
 648                 if type(perm) == str:
 649                         perm = Permission[perm]
 650                 elif type(perm) != Permission:
 651                         raise Exception("Unknown permission given to Package.checkPerm()")
 652
 653                 isOwner = user == self.author
 654
 655                 if perm == Permission.CREATE_THREAD:
 656                         return user.rank.atLeast(UserRank.MEMBER)
 657
 658                 # Members can edit their own packages, and editors can edit any packages
 659                 if perm == Permission.MAKE_RELEASE or perm == Permission.ADD_SCREENSHOTS:
 660                         return isOwner or user.rank.atLeast(UserRank.EDITOR)
 661
 662                 if perm == Permission.EDIT_PACKAGE or perm == Permission.APPROVE_CHANGES or perm == Permission.APPROVE_RELEASE:
 663                         if isOwner:
 664                                 return user.rank.atLeast(UserRank.MEMBER if self.approved else UserRank.NEW_MEMBER)
 665                         else:
 666                                 return user.rank.atLeast(UserRank.EDITOR)
 667
 668                 # Anyone can change the package name when not approved, but only editors when approved
 669                 elif perm == Permission.CHANGE_NAME:
 670                         return not self.approved or user.rank.atLeast(UserRank.EDITOR)
 671
 672                 # Editors can change authors and approve new packages
 673                 elif perm == Permission.APPROVE_NEW or perm == Permission.CHANGE_AUTHOR:
 674                         return user.rank.atLeast(UserRank.EDITOR)
 675
 676                 elif perm == Permission.APPROVE_SCREENSHOT:
 677                         return user.rank.atLeast(UserRank.TRUSTED_MEMBER if isOwner else UserRank.EDITOR)
 678
 679                 # Moderators can delete packages
 680                 elif perm == Permission.DELETE_PACKAGE or perm == Permission.UNAPPROVE_PACKAGE \
 681                                 or perm == Permission.CHANGE_RELEASE_URL:
 682                         return user.rank.atLeast(UserRank.MODERATOR)
 683
 684                 else:
 685                         raise Exception("Permission {} is not related to packages".format(perm.name))
 686
 687         def setStartScore(self):
 688                 downloads = db.session.query(func.sum(PackageRelease.downloads)). \
 689                                 filter(PackageRelease.package_id == self.id).scalar() or 0
 690
 691                 forum_score = 0
 692                 forum_bonus = 0
 693                 topic = self.forums and ForumTopic.query.get(self.forums)
 694                 if topic:
 695                         months = (datetime.datetime.now() - topic.created_at).days / 30
 696                         years  = months / 12
 697                         forum_score = topic.views / max(years, 0.0416) + 80*min(max(months, 0.5), 6)
 698                         forum_bonus = topic.views + topic.posts
 699
 700                 self.score = max(downloads, forum_score * 0.6) + forum_bonus
 701
 702                 if self.getMainScreenshotURL() is None:
 703                         self.score *= 0.8
 704
 705                 if not self.license.is_foss or not self.media_license.is_foss:
 706                         self.score *= 0.1
 707
 708
 709 class MetaPackage(db.Model):
 710         id           = db.Column(db.Integer, primary_key=True)
 711         name         = db.Column(db.String(100), unique=True, nullable=False)
 712         dependencies = db.relationship("Dependency", backref="meta_package", lazy="dynamic")
 713
 714         def __init__(self, name=None):
 715                 self.name = name
 716
 717         def __str__(self):
 718                 return self.name
 719
 720         @staticmethod
 721         def ListToSpec(list):
 722                 return ",".join([str(x) for x in list])
 723
 724         @staticmethod
 725         def GetOrCreate(name, cache={}):
 726                 mp = cache.get(name)
 727                 if mp is None:
 728                         mp = MetaPackage.query.filter_by(name=name).first()
 729
 730                 if mp is None:
 731                         mp = MetaPackage(name)
 732                         db.session.add(mp)
 733
 734                 cache[name] = mp
 735                 return mp
 736
 737         @staticmethod
 738         def SpecToList(spec, cache={}):
 739                 retval = []
 740                 arr = spec.split(",")
 741
 742                 import re
 743                 pattern = re.compile("^([a-z0-9_]+)$")
 744
 745                 for x in arr:
 746                         x = x.strip()
 747                         if x == "":
 748                                 continue
 749
 750                         if not pattern.match(x):
 751                                 continue
 752
 753                         retval.append(MetaPackage.GetOrCreate(x, cache))
 754
 755                 return retval
 756
 757 class Tag(db.Model):
 758         id              = db.Column(db.Integer,    primary_key=True)
 759         name            = db.Column(db.String(100), unique=True, nullable=False)
 760         title           = db.Column(db.String(100), nullable=False)
 761         backgroundColor = db.Column(db.String(6),   nullable=False)
 762         textColor       = db.Column(db.String(6),   nullable=False)
 763
 764         def __init__(self, title, backgroundColor="000000", textColor="ffffff"):
 765                 self.title           = title
 766                 self.backgroundColor = backgroundColor
 767                 self.textColor       = textColor
 768
 769                 import re
 770                 regex = re.compile("[^a-z_]")
 771                 self.name = regex.sub("", self.title.lower().replace(" ", "_"))
 772
 773
 774 class MinetestRelease(db.Model):
 775         id       = db.Column(db.Integer, primary_key=True)
 776         name     = db.Column(db.String(100), unique=True, nullable=False)
 777         protocol = db.Column(db.Integer, nullable=False, default=0)
 778
 779         def __init__(self, name=None, protocol=0):
 780                 self.name = name
 781                 self.protocol = protocol
 782
 783         def getActual(self):
 784                 return None if self.name == "None" else self
 785
 786
 787 class PackageRelease(db.Model):
 788         id           = db.Column(db.Integer, primary_key=True)
 789
 790         package_id   = db.Column(db.Integer, db.ForeignKey("package.id"))
 791         title        = db.Column(db.String(100), nullable=False)
 792         releaseDate  = db.Column(db.DateTime,    nullable=False)
 793         url          = db.Column(db.String(200), nullable=False)
 794         approved     = db.Column(db.Boolean, nullable=False, default=False)
 795         task_id      = db.Column(db.String(37), nullable=True)
 796         commit_hash  = db.Column(db.String(41), nullable=True, default=None)
 797         downloads    = db.Column(db.Integer, nullable=False, default=0)
 798
 799         min_rel_id = db.Column(db.Integer, db.ForeignKey("minetest_release.id"), nullable=True, server_default=None)
 800         min_rel    = db.relationship("MinetestRelease", foreign_keys=[min_rel_id])
 801
 802         max_rel_id = db.Column(db.Integer, db.ForeignKey("minetest_release.id"), nullable=True, server_default=None)
 803         max_rel    = db.relationship("MinetestRelease", foreign_keys=[max_rel_id])
 804
 805         # If the release is approved, then the task_id must be null and the url must be present
 806         CK_approval_valid = db.CheckConstraint("not approved OR (task_id IS NULL AND (url = '') IS NOT FALSE)")
 807
 808         def getAsDictionary(self):
 809                 return {
 810                         "id": self.id,
 811                         "title": self.title,
 812                         "url": self.url if self.url != "" else None,
 813                         "release_date": self.releaseDate.isoformat(),
 814                         "commit": self.commit_hash,
 815                         "downloads": self.downloads,
 816                         "min_protocol": self.min_rel and self.min_rel.protocol,
 817                         "max_protocol": self.max_rel and self.max_rel.protocol
 818                 }
 819
 820         def getEditURL(self):
 821                 return url_for("packages.edit_release",
 822                                 author=self.package.author.username,
 823                                 name=self.package.name,
 824                                 id=self.id)
 825
 826         def getDeleteURL(self):
 827                 return url_for("packages.delete_release",
 828                                 author=self.package.author.username,
 829                                 name=self.package.name,
 830                                 id=self.id)
 831
 832         def getDownloadURL(self):
 833                 return url_for("packages.download_release",
 834                                 author=self.package.author.username,
 835                                 name=self.package.name,
 836                                 id=self.id)
 837
 838
 839         def __init__(self):
 840                 self.releaseDate = datetime.datetime.now()
 841
 842         def approve(self, user):
 843                 if self.package.approved and \
 844                                 not self.package.checkPerm(user, Permission.APPROVE_RELEASE):
 845                         return False
 846
 847                 assert self.task_id is None and self.url is not None and self.url != ""
 848
 849                 self.approved = True
 850                 return True
 851
 852         def checkPerm(self, user, perm):
 853                 if not user.is_authenticated:
 854                         return False
 855
 856                 if type(perm) == str:
 857                         perm = Permission[perm]
 858                 elif type(perm) != Permission:
 859                         raise Exception("Unknown permission given to PackageRelease.checkPerm()")
 860
 861                 isOwner = user == self.package.author
 862
 863                 if perm == Permission.DELETE_RELEASE:
 864                         if user.rank.atLeast(UserRank.ADMIN):
 865                                 return True
 866
 867                         if not (isOwner or user.rank.atLeast(UserRank.EDITOR)):
 868                                 return False
 869
 870                         if not self.package.approved or self.task_id is not None:
 871                                 return True
 872
 873                         count = PackageRelease.query \
 874                                         .filter_by(package_id=self.package_id) \
 875                                         .filter(PackageRelease.id > self.id) \
 876                                         .count()
 877
 878                         return count > 0
 879                 else:
 880                         raise Exception("Permission {} is not related to releases".format(perm.name))
 881
 882
 883 # class PackageReview(db.Model):
 884 #       id         = db.Column(db.Integer, primary_key=True)
 885 #       package_id = db.Column(db.Integer, db.ForeignKey("package.id"))
 886 #       thread_id  = db.Column(db.Integer, db.ForeignKey("thread.id"), nullable=False)
 887 #       recommend  = db.Column(db.Boolean, nullable=False, default=True)
 888
 889
 890 class PackageScreenshot(db.Model):
 891         id         = db.Column(db.Integer, primary_key=True)
 892         package_id = db.Column(db.Integer, db.ForeignKey("package.id"))
 893         title      = db.Column(db.String(100), nullable=False)
 894         url        = db.Column(db.String(100), nullable=False)
 895         approved   = db.Column(db.Boolean, nullable=False, default=False)
 896
 897
 898         def getEditURL(self):
 899                 return url_for("packages.edit_screenshot",
 900                                 author=self.package.author.username,
 901                                 name=self.package.name,
 902                                 id=self.id)
 903
 904         def getThumbnailURL(self, level=2):
 905                 return self.url.replace("/uploads/", ("/thumbnails/{:d}/").format(level))
 906
 907
 908 class APIToken(db.Model):
 909         id           = db.Column(db.Integer, primary_key=True)
 910         access_token = db.Column(db.String(34), unique=True)
 911
 912         name         = db.Column(db.String(100), nullable=False)
 913         owner_id     = db.Column(db.Integer, db.ForeignKey("user.id"), nullable=False)
 914         # owner is created using backref
 915
 916         created_at   = db.Column(db.DateTime, nullable=False, default=datetime.datetime.utcnow)
 917
 918         package_id = db.Column(db.Integer, db.ForeignKey("package.id"), nullable=True)
 919         package    = db.relationship("Package", foreign_keys=[package_id])
 920
 921         def canOperateOnPackage(self, package):
 922                 if self.package and self.package != package:
 923                         return False
 924
 925                 return package.author == self.owner
 926
 927
 928 class EditRequest(db.Model):
 929         id           = db.Column(db.Integer, primary_key=True)
 930
 931         package_id   = db.Column(db.Integer, db.ForeignKey("package.id"))
 932         author_id    = db.Column(db.Integer, db.ForeignKey("user.id"))
 933
 934         title        = db.Column(db.String(100), nullable=False)
 935         desc         = db.Column(db.String(1000), nullable=True)
 936
 937         # 0 - open
 938         # 1 - merged
 939         # 2 - rejected
 940         status       = db.Column(db.Integer, nullable=False, default=0)
 941
 942         changes = db.relationship("EditRequestChange", backref="request",
 943                         lazy="dynamic")
 944
 945         def getURL(self):
 946                 return url_for("view_editrequest_page",
 947                                 author=self.package.author.username,
 948                                 name=self.package.name,
 949                                 id=self.id)
 950
 951         def getApproveURL(self):
 952                 return url_for("approve_editrequest_page",
 953                                 author=self.package.author.username,
 954                                 name=self.package.name,
 955                                 id=self.id)
 956
 957         def getRejectURL(self):
 958                 return url_for("reject_editrequest_page",
 959                                 author=self.package.author.username,
 960                                 name=self.package.name,
 961                                 id=self.id)
 962
 963         def getEditURL(self):
 964                 return url_for("create_edit_editrequest_page",
 965                                 author=self.package.author.username,
 966                                 name=self.package.name,
 967                                 id=self.id)
 968
 969         def applyAll(self, package):
 970                 for change in self.changes:
 971                         change.apply(package)
 972
 973
 974         def checkPerm(self, user, perm):
 975                 if not user.is_authenticated:
 976                         return False
 977
 978                 if type(perm) == str:
 979                         perm = Permission[perm]
 980                 elif type(perm) != Permission:
 981                         raise Exception("Unknown permission given to EditRequest.checkPerm()")
 982
 983                 isOwner = user == self.author
 984
 985                 # Members can edit their own packages, and editors can edit any packages
 986                 if perm == Permission.EDIT_EDITREQUEST:
 987                         return isOwner or user.rank.atLeast(UserRank.EDITOR)
 988
 989                 else:
 990                         raise Exception("Permission {} is not related to packages".format(perm.name))
 991
 992
 993
 994
 995 class EditRequestChange(db.Model):
 996         id           = db.Column(db.Integer, primary_key=True)
 997
 998         request_id   = db.Column(db.Integer, db.ForeignKey("edit_request.id"))
 999         key          = db.Column(db.Enum(PackagePropertyKey), nullable=False)
1000
1001         # TODO: make diff instead
1002         oldValue     = db.Column(db.Text, nullable=True)
1003         newValue     = db.Column(db.Text, nullable=True)
1004
1005         def apply(self, package):
1006                 if self.key == PackagePropertyKey.tags:
1007                         package.tags.clear()
1008                         for tagTitle in self.newValue.split(","):
1009                                 tag = Tag.query.filter_by(title=tagTitle.strip()).first()
1010                                 package.tags.append(tag)
1011
1012                 else:
1013                         setattr(package, self.key.name, self.newValue)
1014
1015
1016 watchers = db.Table("watchers",
1017     db.Column("user_id", db.Integer, db.ForeignKey("user.id"), primary_key=True),
1018     db.Column("thread_id", db.Integer, db.ForeignKey("thread.id"), primary_key=True)
1019 )
1020
1021 class Thread(db.Model):
1022         id         = db.Column(db.Integer, primary_key=True)
1023
1024         package_id = db.Column(db.Integer, db.ForeignKey("package.id"), nullable=True)
1025         package    = db.relationship("Package", foreign_keys=[package_id])
1026
1027         author_id  = db.Column(db.Integer, db.ForeignKey("user.id"), nullable=False)
1028         title      = db.Column(db.String(100), nullable=False)
1029         private    = db.Column(db.Boolean, server_default="0")
1030
1031         created_at = db.Column(db.DateTime, nullable=False, default=datetime.datetime.utcnow)
1032
1033         replies    = db.relationship("ThreadReply", backref="thread", lazy="dynamic")
1034
1035         watchers   = db.relationship("User", secondary=watchers, lazy="subquery", \
1036                                                 backref=db.backref("watching", lazy=True))
1037
1038
1039         def getSubscribeURL(self):
1040                 return url_for("threads.subscribe",
1041                                 id=self.id)
1042
1043         def getUnsubscribeURL(self):
1044                 return url_for("threads.unsubscribe",
1045                                 id=self.id)
1046
1047         def checkPerm(self, user, perm):
1048                 if not user.is_authenticated:
1049                         return not self.private
1050
1051                 if type(perm) == str:
1052                         perm = Permission[perm]
1053                 elif type(perm) != Permission:
1054                         raise Exception("Unknown permission given to Thread.checkPerm()")
1055
1056                 isOwner = user == self.author or (self.package is not None and self.package.author == user)
1057
1058                 if perm == Permission.SEE_THREAD:
1059                         return not self.private or isOwner or user.rank.atLeast(UserRank.EDITOR)
1060
1061                 else:
1062                         raise Exception("Permission {} is not related to threads".format(perm.name))
1063
1064 class ThreadReply(db.Model):
1065         id         = db.Column(db.Integer, primary_key=True)
1066         thread_id  = db.Column(db.Integer, db.ForeignKey("thread.id"), nullable=False)
1067         comment    = db.Column(db.String(500), nullable=False)
1068         author_id  = db.Column(db.Integer, db.ForeignKey("user.id"), nullable=False)
1069         created_at = db.Column(db.DateTime, nullable=False, default=datetime.datetime.utcnow)
1070
1071
1072 REPO_BLACKLIST = [".zip", "mediafire.com", "dropbox.com", "weebly.com", \
1073                 "minetest.net", "dropboxusercontent.com", "4shared.com", \
1074                 "digitalaudioconcepts.com", "hg.intevation.org", "www.wtfpl.net", \
1075                 "imageshack.com", "imgur.com"]
1076
1077 class ForumTopic(db.Model):
1078         topic_id  = db.Column(db.Integer, primary_key=True, autoincrement=False)
1079         author_id = db.Column(db.Integer, db.ForeignKey("user.id"), nullable=False)
1080         author    = db.relationship("User")
1081
1082         wip       = db.Column(db.Boolean, server_default="0")
1083         discarded = db.Column(db.Boolean, server_default="0")
1084
1085         type      = db.Column(db.Enum(PackageType), nullable=False)
1086         title     = db.Column(db.String(200), nullable=False)
1087         name      = db.Column(db.String(30), nullable=True)
1088         link      = db.Column(db.String(200), nullable=True)
1089
1090         posts     = db.Column(db.Integer, nullable=False)
1091         views     = db.Column(db.Integer, nullable=False)
1092
1093         created_at = db.Column(db.DateTime, nullable=False, default=datetime.datetime.utcnow)
1094
1095         def getRepoURL(self):
1096                 if self.link is None:
1097                         return None
1098
1099                 for item in REPO_BLACKLIST:
1100                         if item in self.link:
1101                                 return None
1102
1103                 return self.link.replace("repo.or.cz/w/", "repo.or.cz/")
1104
1105         def getAsDictionary(self):
1106                 return {
1107                         "author": self.author.username,
1108                         "name":   self.name,
1109                         "type":   self.type.toName(),
1110                         "title":  self.title,
1111                         "id":     self.topic_id,
1112                         "link":   self.link,
1113                         "posts":  self.posts,
1114                         "views":  self.views,
1115                         "is_wip": self.wip,
1116                         "discarded":  self.discarded,
1117                         "created_at": self.created_at.isoformat(),
1118                 }
1119
1120         def checkPerm(self, user, perm):
1121                 if not user.is_authenticated:
1122                         return False
1123
1124                 if type(perm) == str:
1125                         perm = Permission[perm]
1126                 elif type(perm) != Permission:
1127                         raise Exception("Unknown permission given to ForumTopic.checkPerm()")
1128
1129                 if perm == Permission.TOPIC_DISCARD:
1130                         return self.author == user or user.rank.atLeast(UserRank.EDITOR)
1131
1132                 else:
1133                         raise Exception("Permission {} is not related to topics".format(perm.name))
1134
1135
1136 # Setup Flask-User
1137 user_manager = UserManager(app, db, User)
1138
1139 if app.config.get("LOG_SQL"):
1140         import logging
1141         logging.basicConfig()
1142         logging.getLogger('sqlalchemy.engine').setLevel(logging.INFO)