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