app/models.py

   1 # ContentDB
   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         REIMPORT_META      = "REIMPORT_META"
  84         APPROVE_SCREENSHOT = "APPROVE_SCREENSHOT"
  85         APPROVE_RELEASE    = "APPROVE_RELEASE"
  86         APPROVE_NEW        = "APPROVE_NEW"
  87         EDIT_TAGS          = "EDIT_TAGS"
  88         CREATE_TAG         = "CREATE_TAG"
  89         CHANGE_RELEASE_URL = "CHANGE_RELEASE_URL"
  90         CHANGE_USERNAMES   = "CHANGE_USERNAMES"
  91         CHANGE_RANK        = "CHANGE_RANK"
  92         CHANGE_EMAIL       = "CHANGE_EMAIL"
  93         EDIT_EDITREQUEST   = "EDIT_EDITREQUEST"
  94         SEE_THREAD         = "SEE_THREAD"
  95         CREATE_THREAD      = "CREATE_THREAD"
  96         COMMENT_THREAD     = "COMMENT_THREAD"
  97         LOCK_THREAD        = "LOCK_THREAD"
  98         DELETE_REPLY       = "DELETE_REPLY"
  99         EDIT_REPLY         = "EDIT_REPLY"
 100         UNAPPROVE_PACKAGE  = "UNAPPROVE_PACKAGE"
 101         TOPIC_DISCARD      = "TOPIC_DISCARD"
 102         CREATE_TOKEN       = "CREATE_TOKEN"
 103         EDIT_MAINTAINERS   = "EDIT_MAINTAINERS"
 104         CHANGE_PROFILE_URLS = "CHANGE_PROFILE_URLS"
 105
 106         # Only return true if the permission is valid for *all* contexts
 107         # See Package.checkPerm for package-specific contexts
 108         def check(self, user):
 109                 if not user.is_authenticated:
 110                         return False
 111
 112                 if self == Permission.APPROVE_NEW or \
 113                                 self == Permission.APPROVE_CHANGES    or \
 114                                 self == Permission.APPROVE_RELEASE    or \
 115                                 self == Permission.APPROVE_SCREENSHOT or \
 116                                 self == Permission.EDIT_TAGS or \
 117                                 self == Permission.CREATE_TAG or \
 118                                 self == Permission.SEE_THREAD:
 119                         return user.rank.atLeast(UserRank.EDITOR)
 120                 else:
 121                         raise Exception("Non-global permission checked globally. Use Package.checkPerm or User.checkPerm instead.")
 122
 123         @staticmethod
 124         def checkPerm(user, perm):
 125                 if type(perm) == str:
 126                         perm = Permission[perm]
 127                 elif type(perm) != Permission:
 128                         raise Exception("Unknown permission given to Permission.check")
 129
 130                 return perm.check(user)
 131
 132 def display_name_default(context):
 133     return context.get_current_parameters()["username"]
 134
 135 class User(db.Model, UserMixin):
 136         id           = db.Column(db.Integer, primary_key=True)
 137
 138         # User authentication information
 139         username     = db.Column(db.String(50, collation="NOCASE"), nullable=False, unique=True, index=True)
 140         password     = db.Column(db.String(255), nullable=False, server_default="")
 141         reset_password_token = db.Column(db.String(100), nullable=False, server_default="")
 142
 143         rank         = db.Column(db.Enum(UserRank))
 144
 145         # Account linking
 146         github_username = db.Column(db.String(50, collation="NOCASE"), nullable=True, unique=True)
 147         forums_username = db.Column(db.String(50, collation="NOCASE"), nullable=True, unique=True)
 148
 149         # Access token for webhook setup
 150         github_access_token = db.Column(db.String(50), nullable=True, server_default=None)
 151
 152         # User email information
 153         email         = db.Column(db.String(255), nullable=True, unique=True)
 154         email_confirmed_at  = db.Column(db.DateTime())
 155
 156         # User information
 157         profile_pic   = db.Column(db.String(255), nullable=True, server_default=None)
 158         active        = db.Column("is_active", db.Boolean, nullable=False, server_default="0")
 159         display_name  = db.Column(db.String(100), nullable=False, default=display_name_default)
 160
 161         # Links
 162         website_url   = db.Column(db.String(255), nullable=True, default=None)
 163         donate_url    = db.Column(db.String(255), nullable=True, default=None)
 164
 165         # Content
 166         notifications = db.relationship("Notification", primaryjoin="User.id==Notification.user_id", \
 167                         order_by="Notification.created_at")
 168
 169         packages      = db.relationship("Package", backref=db.backref("author", lazy="joined"), lazy="dynamic")
 170         requests      = db.relationship("EditRequest", backref="author", lazy="dynamic")
 171         threads       = db.relationship("Thread", backref="author", lazy="dynamic")
 172         tokens        = db.relationship("APIToken", backref="owner", lazy="dynamic")
 173         replies       = db.relationship("ThreadReply", backref="author", lazy="dynamic")
 174
 175         def __init__(self, username=None, active=False, email=None, password=""):
 176                 self.username = username
 177                 self.email_confirmed_at = datetime.datetime.now() - datetime.timedelta(days=6000)
 178                 self.display_name = username
 179                 self.active = active
 180                 self.email = email
 181                 self.password = password
 182                 self.rank = UserRank.NOT_JOINED
 183
 184         def hasPassword(self):
 185                 return self.password != ""
 186
 187         def canAccessTodoList(self):
 188                 return Permission.APPROVE_NEW.check(self) or \
 189                                 Permission.APPROVE_RELEASE.check(self) or \
 190                                 Permission.APPROVE_CHANGES.check(self)
 191
 192         def isClaimed(self):
 193                 return self.rank.atLeast(UserRank.NEW_MEMBER)
 194
 195         def getProfilePicURL(self):
 196                 if self.profile_pic:
 197                         return self.profile_pic
 198                 else:
 199                         return gravatar(self.email or "")
 200
 201         def checkPerm(self, user, perm):
 202                 if not user.is_authenticated:
 203                         return False
 204
 205                 if type(perm) == str:
 206                         perm = Permission[perm]
 207                 elif type(perm) != Permission:
 208                         raise Exception("Unknown permission given to User.checkPerm()")
 209
 210                 # Members can edit their own packages, and editors can edit any packages
 211                 if perm == Permission.CHANGE_AUTHOR:
 212                         return user.rank.atLeast(UserRank.EDITOR)
 213                 elif perm == Permission.CHANGE_RANK or perm == Permission.CHANGE_USERNAMES:
 214                         return user.rank.atLeast(UserRank.MODERATOR)
 215                 elif perm == Permission.CHANGE_EMAIL or perm == Permission.CHANGE_PROFILE_URLS:
 216                         return user == self or user.rank.atLeast(UserRank.ADMIN)
 217                 elif perm == Permission.CREATE_TOKEN:
 218                         if user == self:
 219                                 return user.rank.atLeast(UserRank.MEMBER)
 220                         else:
 221                                 return user.rank.atLeast(UserRank.MODERATOR) and user.rank.atLeast(self.rank)
 222                 else:
 223                         raise Exception("Permission {} is not related to users".format(perm.name))
 224
 225         def canCommentRL(self):
 226                 factor = 1
 227                 if self.rank.atLeast(UserRank.ADMIN):
 228                         return True
 229                 elif self.rank.atLeast(UserRank.TRUSTED_MEMBER):
 230                         factor *= 2
 231
 232                 one_min_ago = datetime.datetime.utcnow() - datetime.timedelta(minutes=1)
 233                 if ThreadReply.query.filter_by(author=self) \
 234                                 .filter(ThreadReply.created_at > one_min_ago).count() >= 3 * factor:
 235                         return False
 236
 237                 hour_ago = datetime.datetime.utcnow() - datetime.timedelta(hours=1)
 238                 if ThreadReply.query.filter_by(author=self) \
 239                                 .filter(ThreadReply.created_at > hour_ago).count() >= 20 * factor:
 240                         return False
 241
 242                 return True
 243
 244         def canOpenThreadRL(self):
 245                 factor = 1
 246                 if self.rank.atLeast(UserRank.ADMIN):
 247                         return True
 248                 elif self.rank.atLeast(UserRank.TRUSTED_MEMBER):
 249                         factor *= 5
 250
 251                 hour_ago = datetime.datetime.utcnow() - datetime.timedelta(hours=1)
 252                 return Thread.query.filter_by(author=self) \
 253                         .filter(Thread.created_at > hour_ago).count() < 2 * factor
 254
 255         def __eq__(self, other):
 256                 if other is None:
 257                         return False
 258
 259                 if not self.is_authenticated or not other.is_authenticated:
 260                         return False
 261
 262                 assert self.id > 0
 263                 return self.id == other.id
 264
 265 class UserEmailVerification(db.Model):
 266         id      = db.Column(db.Integer, primary_key=True)
 267         user_id = db.Column(db.Integer, db.ForeignKey("user.id"))
 268         email   = db.Column(db.String(100))
 269         token   = db.Column(db.String(32))
 270         user    = db.relationship("User", foreign_keys=[user_id])
 271
 272 class Notification(db.Model):
 273         id         = db.Column(db.Integer, primary_key=True)
 274
 275         user_id    = db.Column(db.Integer, db.ForeignKey("user.id"), nullable=False)
 276         user       = db.relationship("User", foreign_keys=[user_id])
 277
 278         causer_id  = db.Column(db.Integer, db.ForeignKey("user.id"), nullable=False)
 279         causer     = db.relationship("User", foreign_keys=[causer_id])
 280
 281         title      = db.Column(db.String(100), nullable=False)
 282         url        = db.Column(db.String(200), nullable=True)
 283
 284         package_id = db.Column(db.Integer, db.ForeignKey("package.id"), nullable=True)
 285         package    = db.relationship("Package", foreign_keys=[package_id])
 286
 287         created_at = db.Column(db.DateTime, nullable=True, default=datetime.datetime.utcnow)
 288
 289         def __init__(self, user, causer, title, url, package=None):
 290                 if len(title) > 100:
 291                         title = title[:99] + "…"
 292
 293                 self.user    = user
 294                 self.causer  = causer
 295                 self.title   = title
 296                 self.url     = url
 297                 self.package = package
 298
 299
 300 class License(db.Model):
 301         id      = db.Column(db.Integer, primary_key=True)
 302         name    = db.Column(db.String(50), nullable=False, unique=True)
 303         is_foss = db.Column(db.Boolean,    nullable=False, default=True)
 304
 305         def __init__(self, v, is_foss=True):
 306                 self.name = v
 307                 self.is_foss = is_foss
 308
 309         def __str__(self):
 310                 return self.name
 311
 312
 313 class PackageType(enum.Enum):
 314         MOD  = "Mod"
 315         GAME = "Game"
 316         TXP  = "Texture Pack"
 317
 318         def toName(self):
 319                 return self.name.lower()
 320
 321         def __str__(self):
 322                 return self.name
 323
 324         @classmethod
 325         def get(cls, name):
 326                 try:
 327                         return PackageType[name.upper()]
 328                 except KeyError:
 329                         return None
 330
 331         @classmethod
 332         def choices(cls):
 333                 return [(choice, choice.value) for choice in cls]
 334
 335         @classmethod
 336         def coerce(cls, item):
 337                 return item if type(item) == PackageType else PackageType[item]
 338
 339
 340 class PackageState(enum.Enum):
 341         WIP = "Work in Progress"
 342         CHANGES_NEEDED  = "Changes Needed"
 343         READY_FOR_REVIEW = "Ready for Review"
 344         APPROVED  = "Approved"
 345         DELETED = "Deleted"
 346
 347         def toName(self):
 348                 return self.name.lower()
 349
 350         def verb(self):
 351                 if self == self.READY_FOR_REVIEW:
 352                         return "Submit for Review"
 353                 elif self == self.APPROVED:
 354                         return "Approve"
 355                 elif self == self.DELETED:
 356                         return "Delete"
 357                 else:
 358                         return self.value
 359
 360         def __str__(self):
 361                 return self.name
 362
 363         @classmethod
 364         def get(cls, name):
 365                 try:
 366                         return PackageState[name.upper()]
 367                 except KeyError:
 368                         return None
 369
 370         @classmethod
 371         def choices(cls):
 372                 return [(choice, choice.value) for choice in cls]
 373
 374         @classmethod
 375         def coerce(cls, item):
 376                 return item if type(item) == PackageState else PackageState[item]
 377
 378
 379 PACKAGE_STATE_FLOW = {
 380         PackageState.WIP: set([ PackageState.READY_FOR_REVIEW ]),
 381         PackageState.CHANGES_NEEDED: set([ PackageState.READY_FOR_REVIEW ]),
 382         PackageState.READY_FOR_REVIEW: set([ PackageState.WIP, PackageState.CHANGES_NEEDED, PackageState.APPROVED ]),
 383         PackageState.APPROVED: set([ PackageState.CHANGES_NEEDED ]),
 384         PackageState.DELETED: set([ PackageState.READY_FOR_REVIEW ]),
 385 }
 386
 387
 388 class PackagePropertyKey(enum.Enum):
 389         name          = "Name"
 390         title         = "Title"
 391         short_desc     = "Short Description"
 392         desc          = "Description"
 393         type          = "Type"
 394         license       = "License"
 395         media_license = "Media License"
 396         tags          = "Tags"
 397         provides      = "Provides"
 398         repo          = "Repository"
 399         website       = "Website"
 400         issueTracker  = "Issue Tracker"
 401         forums        = "Forum Topic ID"
 402
 403         def convert(self, value):
 404                 if self == PackagePropertyKey.tags:
 405                         return ",".join([t.title for t in value])
 406                 elif self == PackagePropertyKey.provides:
 407                         return ",".join([t.name for t in value])
 408                 else:
 409                         return str(value)
 410
 411 provides = db.Table("provides",
 412         db.Column("package_id",    db.Integer, db.ForeignKey("package.id"), primary_key=True),
 413     db.Column("metapackage_id", db.Integer, db.ForeignKey("meta_package.id"), primary_key=True)
 414 )
 415
 416 Tags = db.Table("tags",
 417     db.Column("tag_id", db.Integer, db.ForeignKey("tag.id"), primary_key=True),
 418     db.Column("package_id", db.Integer, db.ForeignKey("package.id"), primary_key=True)
 419 )
 420
 421 ContentWarnings = db.Table("content_warnings",
 422     db.Column("content_warning_id", db.Integer, db.ForeignKey("content_warning.id"), primary_key=True),
 423     db.Column("package_id", db.Integer, db.ForeignKey("package.id"), primary_key=True)
 424 )
 425
 426 maintainers = db.Table("maintainers",
 427     db.Column("user_id", db.Integer, db.ForeignKey("user.id"), primary_key=True),
 428     db.Column("package_id", db.Integer, db.ForeignKey("package.id"), primary_key=True)
 429 )
 430
 431 class Dependency(db.Model):
 432         id              = db.Column(db.Integer, primary_key=True)
 433         depender_id     = db.Column(db.Integer, db.ForeignKey("package.id"),     nullable=True)
 434         package_id      = db.Column(db.Integer, db.ForeignKey("package.id"),     nullable=True)
 435         package         = db.relationship("Package", foreign_keys=[package_id])
 436         meta_package_id = db.Column(db.Integer, db.ForeignKey("meta_package.id"), nullable=True)
 437         optional        = db.Column(db.Boolean, nullable=False, default=False)
 438         __table_args__  = (db.UniqueConstraint("depender_id", "package_id", "meta_package_id", name="_dependency_uc"), )
 439
 440         def __init__(self, depender=None, package=None, meta=None, optional=False):
 441                 if depender is None:
 442                         return
 443
 444                 self.depender = depender
 445                 self.optional = optional
 446
 447                 packageProvided = package is not None
 448                 metaProvided = meta is not None
 449
 450                 if packageProvided and not metaProvided:
 451                         self.package = package
 452                 elif metaProvided and not packageProvided:
 453                         self.meta_package = meta
 454                 else:
 455                         raise Exception("Either meta or package must be given, but not both!")
 456
 457         def getName(self):
 458                 if self.meta_package:
 459                         return self.meta_package.name
 460                 elif self.package:
 461                         return self.package.name
 462                 else:
 463                         assert False
 464
 465         def __str__(self):
 466                 if self.package is not None:
 467                         return self.package.author.username + "/" + self.package.name
 468                 elif self.meta_package is not None:
 469                         return self.meta_package.name
 470                 else:
 471                         raise Exception("Meta and package are both none!")
 472
 473         @staticmethod
 474         def SpecToList(depender, spec, cache={}):
 475                 retval = []
 476                 arr = spec.split(",")
 477
 478                 import re
 479                 pattern1 = re.compile("^([a-z0-9_]+)$")
 480                 pattern2 = re.compile("^([A-Za-z0-9_]+)/([a-z0-9_]+)$")
 481
 482                 for x in arr:
 483                         x = x.strip()
 484                         if x == "":
 485                                 continue
 486
 487                         if pattern1.match(x):
 488                                 meta = MetaPackage.GetOrCreate(x, cache)
 489                                 retval.append(Dependency(depender, meta=meta))
 490                         else:
 491                                 m = pattern2.match(x)
 492                                 username = m.group(1)
 493                                 name     = m.group(2)
 494                                 user = User.query.filter_by(username=username).first()
 495                                 if user is None:
 496                                         raise Exception("Unable to find user " + username)
 497
 498                                 package = Package.query.filter_by(author=user, name=name).first()
 499                                 if package is None:
 500                                         raise Exception("Unable to find package " + name + " by " + username)
 501
 502                                 retval.append(Dependency(depender, package=package))
 503
 504                 return retval
 505
 506
 507 class Package(db.Model):
 508         query_class  = ArticleQuery
 509
 510         id           = db.Column(db.Integer, primary_key=True)
 511
 512         # Basic details
 513         author_id    = db.Column(db.Integer, db.ForeignKey("user.id"))
 514         name         = db.Column(db.Unicode(100), nullable=False)
 515         title        = db.Column(db.Unicode(100), nullable=False)
 516         short_desc   = db.Column(db.Unicode(200), nullable=False)
 517         desc         = db.Column(db.UnicodeText, nullable=True)
 518         type         = db.Column(db.Enum(PackageType))
 519         created_at   = db.Column(db.DateTime, nullable=False, default=datetime.datetime.utcnow)
 520         approved_at  = db.Column(db.DateTime, nullable=True, default=None)
 521
 522         name_valid = db.CheckConstraint("name ~* '^[a-z0-9_]+$'")
 523
 524         search_vector = db.Column(TSVectorType("name", "title", "short_desc", "desc", \
 525                         weights={ "name": "A", "title": "B", "short_desc": "C", "desc": "D" }))
 526
 527         license_id   = db.Column(db.Integer, db.ForeignKey("license.id"), nullable=False, default=1)
 528         license      = db.relationship("License", foreign_keys=[license_id])
 529         media_license_id = db.Column(db.Integer, db.ForeignKey("license.id"), nullable=False, default=1)
 530         media_license    = db.relationship("License", foreign_keys=[media_license_id])
 531
 532         state         = db.Column(db.Enum(PackageState), default=PackageState.WIP)
 533
 534         @property
 535         def approved(self):
 536                 return self.state == PackageState.APPROVED
 537
 538         score        = db.Column(db.Float, nullable=False, default=0)
 539         score_downloads = db.Column(db.Float, nullable=False, default=0)
 540         downloads     = db.Column(db.Integer, nullable=False, default=0)
 541
 542         review_thread_id = db.Column(db.Integer, db.ForeignKey("thread.id"), nullable=True, default=None)
 543         review_thread    = db.relationship("Thread", foreign_keys=[review_thread_id])
 544
 545         # Downloads
 546         repo         = db.Column(db.String(200), nullable=True)
 547         website      = db.Column(db.String(200), nullable=True)
 548         issueTracker = db.Column(db.String(200), nullable=True)
 549         forums       = db.Column(db.Integer,     nullable=True)
 550
 551         provides = db.relationship("MetaPackage", \
 552                         secondary=provides, lazy="select", order_by=db.asc("name"), \
 553                         backref=db.backref("packages", lazy="dynamic", order_by=db.desc("score")))
 554
 555         dependencies = db.relationship("Dependency", backref="depender", lazy="dynamic", foreign_keys=[Dependency.depender_id])
 556
 557         tags = db.relationship("Tag", secondary=Tags, lazy="select",
 558                         backref=db.backref("packages", lazy=True))
 559
 560         content_warnings = db.relationship("ContentWarning", secondary=ContentWarnings, lazy="select",
 561                         backref=db.backref("packages", lazy=True))
 562
 563         releases = db.relationship("PackageRelease", backref="package",
 564                         lazy="dynamic", order_by=db.desc("package_release_releaseDate"))
 565
 566         screenshots = db.relationship("PackageScreenshot", backref="package",
 567                         lazy="dynamic", order_by=db.asc("package_screenshot_id"))
 568
 569         requests = db.relationship("EditRequest", backref="package",
 570                         lazy="dynamic")
 571
 572         maintainers = db.relationship("User", secondary=maintainers, lazy="subquery")
 573
 574         def __init__(self, package=None):
 575                 if package is None:
 576                         return
 577
 578                 self.author_id = package.author_id
 579                 self.created_at = package.created_at
 580                 self.state = package.state
 581
 582                 self.maintainers.append(self.author)
 583
 584                 for e in PackagePropertyKey:
 585                         setattr(self, e.name, getattr(package, e.name))
 586
 587         def getId(self):
 588                 return "{}/{}".format(self.author.username, self.name)
 589
 590         def getIsFOSS(self):
 591                 return self.license.is_foss and self.media_license.is_foss
 592
 593         def getIsOnGitHub(self):
 594                 if self.repo is None:
 595                         return False
 596
 597                 url = urlparse(self.repo)
 598                 return url.netloc == "github.com"
 599
 600         def getGitHubFullName(self):
 601                 if self.repo is None:
 602                         return None
 603
 604                 url = urlparse(self.repo)
 605                 if url.netloc != "github.com":
 606                         return None
 607
 608                 import re
 609                 m = re.search(r"^\/([^\/]+)\/([^\/]+)\/?$", url.path)
 610                 if m is None:
 611                         return
 612
 613                 user = m.group(1)
 614                 repo = m.group(2).replace(".git", "")
 615
 616                 return (user,repo)
 617
 618         def getSortedDependencies(self, is_hard=None):
 619                 query = self.dependencies
 620                 if is_hard is not None:
 621                         query = query.filter_by(optional=not is_hard)
 622
 623                 deps = query.all()
 624                 deps.sort(key = lambda x: x.getName())
 625                 return deps
 626
 627         def getSortedHardDependencies(self):
 628                 return self.getSortedDependencies(True)
 629
 630         def getSortedOptionalDependencies(self):
 631                 return self.getSortedDependencies(False)
 632
 633         def getAsDictionaryKey(self):
 634                 return {
 635                         "name": self.name,
 636                         "author": self.author.display_name,
 637                         "type": self.type.toName(),
 638                 }
 639
 640         def getAsDictionaryShort(self, base_url, version=None):
 641                 tnurl = self.getThumbnailURL(1)
 642                 release = self.getDownloadRelease(version=version)
 643                 return {
 644                         "name": self.name,
 645                         "title": self.title,
 646                         "author": self.author.username,
 647                         "short_description": self.short_desc,
 648                         "type": self.type.toName(),
 649                         "release": release and release.id,
 650                         "thumbnail": (base_url + tnurl) if tnurl is not None else None
 651                 }
 652
 653         def getAsDictionary(self, base_url, version=None):
 654                 tnurl = self.getThumbnailURL(1)
 655                 release = self.getDownloadRelease(version=version)
 656                 return {
 657                         "author": self.author.username,
 658                         "name": self.name,
 659                         "title": self.title,
 660                         "short_description": self.short_desc,
 661                         "desc": self.desc,
 662                         "type": self.type.toName(),
 663                         "created_at": self.created_at.isoformat(),
 664
 665                         "license": self.license.name,
 666                         "media_license": self.media_license.name,
 667
 668                         "repo": self.repo,
 669                         "website": self.website,
 670                         "issue_tracker": self.issueTracker,
 671                         "forums": self.forums,
 672
 673                         "provides": [x.name for x in self.provides],
 674                         "thumbnail": (base_url + tnurl) if tnurl is not None else None,
 675                         "screenshots": [base_url + ss.url for ss in self.screenshots],
 676
 677                         "url": base_url + self.getDownloadURL(),
 678                         "release": release and release.id,
 679
 680                         "score": round(self.score * 10) / 10,
 681                         "downloads": self.downloads
 682                 }
 683
 684         def getThumbnailURL(self, level=2):
 685                 screenshot = self.screenshots.filter_by(approved=True).order_by(db.asc(PackageScreenshot.id)).first()
 686                 return screenshot.getThumbnailURL(level) if screenshot is not None else None
 687
 688         def getMainScreenshotURL(self, absolute=False):
 689                 screenshot = self.screenshots.filter_by(approved=True).order_by(db.asc(PackageScreenshot.id)).first()
 690                 if screenshot is None:
 691                         return None
 692
 693                 if absolute:
 694                         from app.utils import abs_url
 695                         return abs_url(screenshot.url)
 696                 else:
 697                         return screenshot.url
 698
 699         def getDetailsURL(self, absolute=False):
 700                 if absolute:
 701                         from app.utils import abs_url_for
 702                         return abs_url_for("packages.view",
 703                                         author=self.author.username, name=self.name)
 704                 else:
 705                         return url_for("packages.view",
 706                                         author=self.author.username, name=self.name)
 707
 708         def getShieldURL(self, type):
 709                 from app.utils import abs_url_for
 710                 return abs_url_for("packages.shield",
 711                                 author=self.author.username, name=self.name, type=type)
 712
 713         def makeShield(self, type):
 714                 return "[![ContentDB]({})]({})" \
 715                         .format(self.getShieldURL(type), self.getDetailsURL(True))
 716
 717         def getEditURL(self):
 718                 return url_for("packages.create_edit",
 719                                 author=self.author.username, name=self.name)
 720
 721         def getSetStateURL(self, state):
 722                 if type(state) == str:
 723                         state = PackageState[perm]
 724                 elif type(state) != PackageState:
 725                         raise Exception("Unknown state given to Package.canMoveToState()")
 726
 727                 return url_for("packages.move_to_state",
 728                                 author=self.author.username, name=self.name, state=state.name.lower())
 729
 730         def getRemoveURL(self):
 731                 return url_for("packages.remove",
 732                                 author=self.author.username, name=self.name)
 733
 734         def getNewScreenshotURL(self):
 735                 return url_for("packages.create_screenshot",
 736                                 author=self.author.username, name=self.name)
 737
 738         def getCreateReleaseURL(self):
 739                 return url_for("packages.create_release",
 740                                 author=self.author.username, name=self.name)
 741
 742         def getCreateEditRequestURL(self):
 743                 return url_for("create_edit_editrequest_page",
 744                                 author=self.author.username, name=self.name)
 745
 746         def getBulkReleaseURL(self):
 747                 return url_for("packages.bulk_change_release",
 748                         author=self.author.username, name=self.name)
 749
 750         def getDownloadURL(self):
 751                 return url_for("packages.download",
 752                                 author=self.author.username, name=self.name)
 753
 754         def getEditMaintainersURL(self):
 755                 return url_for("packages.edit_maintainers",
 756                                 author=self.author.username, name=self.name)
 757
 758         def getRemoveSelfMaintainerURL(self):
 759                 return url_for("packages.remove_self_maintainers",
 760                                 author=self.author.username, name=self.name)
 761
 762         def getUpdateFromReleaseURL(self):
 763                 return url_for("packages.update_from_release",
 764                                 author=self.author.username, name=self.name)
 765
 766         def getReviewURL(self):
 767                 return url_for('packages.review',
 768                                 author=self.author.username, name=self.name)
 769
 770         def getDownloadRelease(self, version=None):
 771                 for rel in self.releases:
 772                         if rel.approved and (version is None or
 773                                         ((rel.min_rel is None or rel.min_rel_id <= version.id) and \
 774                                         (rel.max_rel is None or rel.max_rel_id >= version.id))):
 775                                 return rel
 776
 777                 return None
 778
 779         def checkPerm(self, user, perm):
 780                 if not user.is_authenticated:
 781                         return False
 782
 783                 if type(perm) == str:
 784                         perm = Permission[perm]
 785                 elif type(perm) != Permission:
 786                         raise Exception("Unknown permission given to Package.checkPerm()")
 787
 788                 isOwner = user == self.author
 789                 isMaintainer = isOwner or user.rank.atLeast(UserRank.EDITOR) or user in self.maintainers
 790
 791                 if perm == Permission.CREATE_THREAD:
 792                         return user.rank.atLeast(UserRank.MEMBER)
 793
 794                 # Members can edit their own packages, and editors can edit any packages
 795                 elif perm == Permission.MAKE_RELEASE or perm == Permission.ADD_SCREENSHOTS:
 796                         return isMaintainer
 797
 798                 elif perm == Permission.EDIT_PACKAGE or \
 799                                 perm == Permission.APPROVE_CHANGES or perm == Permission.APPROVE_RELEASE:
 800                         return isMaintainer and user.rank.atLeast(UserRank.MEMBER if self.approved else UserRank.NEW_MEMBER)
 801
 802                 # Anyone can change the package name when not approved, but only editors when approved
 803                 elif perm == Permission.CHANGE_NAME:
 804                         return not self.approved or user.rank.atLeast(UserRank.EDITOR)
 805
 806                 # Editors can change authors and approve new packages
 807                 elif perm == Permission.APPROVE_NEW or perm == Permission.CHANGE_AUTHOR:
 808                         return user.rank.atLeast(UserRank.EDITOR)
 809
 810                 elif perm == Permission.APPROVE_SCREENSHOT:
 811                         return isMaintainer and user.rank.atLeast(UserRank.TRUSTED_MEMBER if self.approved else UserRank.NEW_MEMBER)
 812
 813                 elif perm == Permission.EDIT_MAINTAINERS:
 814                         return isOwner or user.rank.atLeast(UserRank.MODERATOR)
 815
 816                 elif perm == Permission.UNAPPROVE_PACKAGE or perm == Permission.DELETE_PACKAGE:
 817                         return user.rank.atLeast(UserRank.MEMBER if isOwner else UserRank.EDITOR)
 818
 819                 elif perm == Permission.CHANGE_RELEASE_URL:
 820                         return user.rank.atLeast(UserRank.MODERATOR)
 821
 822                 elif perm == Permission.REIMPORT_META:
 823                         return user.rank.atLeast(UserRank.ADMIN)
 824
 825                 else:
 826                         raise Exception("Permission {} is not related to packages".format(perm.name))
 827
 828
 829         def canMoveToState(self, user, state):
 830                 if not user.is_authenticated:
 831                         return False
 832
 833                 if type(state) == str:
 834                         state = PackageState[perm]
 835                 elif type(state) != PackageState:
 836                         raise Exception("Unknown state given to Package.canMoveToState()")
 837
 838                 if state not in PACKAGE_STATE_FLOW[self.state]:
 839                         return False
 840
 841                 if state == PackageState.READY_FOR_REVIEW or state == PackageState.APPROVED:
 842                         requiredPerm = Permission.APPROVE_NEW if state == PackageState.APPROVED else Permission.EDIT_PACKAGE
 843
 844                         if not self.checkPerm(user, requiredPerm):
 845                                 return False
 846
 847                         if state == PackageState.APPROVED and \
 848                                         ("Other" in self.license.name or "Other" in self.media_license.name):
 849                                 return False
 850
 851                         needsScreenshot = \
 852                                 (self.type == self.type.GAME or self.type == self.type.TXP) and \
 853                                         self.screenshots.count() == 0
 854                         return self.releases.count() > 0 and not needsScreenshot
 855
 856                 elif state == PackageState.CHANGES_NEEDED:
 857                         return self.checkPerm(user, Permission.APPROVE_NEW)
 858
 859                 elif state == PackageState.WIP:
 860                         return self.checkPerm(user, Permission.EDIT_PACKAGE) and user in self.maintainers
 861
 862                 return True
 863
 864
 865         def getNextStates(self, user):
 866                 states = []
 867
 868                 for state in PackageState:
 869                         if self.canMoveToState(user, state):
 870                                 states.append(state)
 871
 872                 return states
 873
 874
 875         def getScoreDict(self):
 876                 return {
 877                         "author": self.author.username,
 878                         "name": self.name,
 879                         "score": self.score,
 880                         "score_downloads": self.score_downloads,
 881                         "score_reviews": self.score - self.score_downloads,
 882                         "downloads": self.downloads
 883                 }
 884
 885         def recalcScore(self):
 886                 review_scores = [ 100 * r.asSign() for r in self.reviews ]
 887                 self.score = self.score_downloads + sum(review_scores)
 888
 889
 890 class MetaPackage(db.Model):
 891         id           = db.Column(db.Integer, primary_key=True)
 892         name         = db.Column(db.String(100), unique=True, nullable=False)
 893         dependencies = db.relationship("Dependency", backref="meta_package", lazy="dynamic")
 894
 895         mp_name_valid = db.CheckConstraint("name ~* '^[a-z0-9_]+$'")
 896
 897         def __init__(self, name=None):
 898                 self.name = name
 899
 900         def __str__(self):
 901                 return self.name
 902
 903         @staticmethod
 904         def ListToSpec(list):
 905                 return ",".join([str(x) for x in list])
 906
 907         @staticmethod
 908         def GetOrCreate(name, cache={}):
 909                 mp = cache.get(name)
 910                 if mp is None:
 911                         mp = MetaPackage.query.filter_by(name=name).first()
 912
 913                 if mp is None:
 914                         mp = MetaPackage(name)
 915                         db.session.add(mp)
 916
 917                 cache[name] = mp
 918                 return mp
 919
 920         @staticmethod
 921         def SpecToList(spec, cache={}):
 922                 retval = []
 923                 arr = spec.split(",")
 924
 925                 import re
 926                 pattern = re.compile("^([a-z0-9_]+)$")
 927
 928                 for x in arr:
 929                         x = x.strip()
 930                         if x == "":
 931                                 continue
 932
 933                         if not pattern.match(x):
 934                                 continue
 935
 936                         retval.append(MetaPackage.GetOrCreate(x, cache))
 937
 938                 return retval
 939
 940
 941 class ContentWarning(db.Model):
 942         id              = db.Column(db.Integer, primary_key=True)
 943         name            = db.Column(db.String(100), unique=True, nullable=False)
 944         title           = db.Column(db.String(100), nullable=False)
 945         description     = db.Column(db.String(500), nullable=False)
 946
 947         def __init__(self, title, description=""):
 948                 self.title       = title
 949                 self.description = description
 950
 951                 import re
 952                 regex = re.compile("[^a-z_]")
 953                 self.name = regex.sub("", self.title.lower().replace(" ", "_"))
 954
 955
 956 class Tag(db.Model):
 957         id              = db.Column(db.Integer, primary_key=True)
 958         name            = db.Column(db.String(100), unique=True, nullable=False)
 959         title           = db.Column(db.String(100), nullable=False)
 960         description     = db.Column(db.String(500), nullable=True, default=None)
 961         backgroundColor = db.Column(db.String(6), nullable=False)
 962         textColor       = db.Column(db.String(6), nullable=False)
 963         views           = db.Column(db.Integer, nullable=False, default=0)
 964
 965         def __init__(self, title, backgroundColor="000000", textColor="ffffff"):
 966                 self.title           = title
 967                 self.backgroundColor = backgroundColor
 968                 self.textColor       = textColor
 969
 970                 import re
 971                 regex = re.compile("[^a-z_]")
 972                 self.name = regex.sub("", self.title.lower().replace(" ", "_"))
 973
 974
 975 class MinetestRelease(db.Model):
 976         id       = db.Column(db.Integer, primary_key=True)
 977         name     = db.Column(db.String(100), unique=True, nullable=False)
 978         protocol = db.Column(db.Integer, nullable=False, default=0)
 979
 980         def __init__(self, name=None, protocol=0):
 981                 self.name = name
 982                 self.protocol = protocol
 983
 984         def getActual(self):
 985                 return None if self.name == "None" else self
 986
 987         @classmethod
 988         def get(cls, version, protocol_num):
 989                 if version:
 990                         parts = version.strip().split(".")
 991                         if len(parts) >= 2:
 992                                 major_minor = parts[0] + "." + parts[1]
 993                                 query = MinetestRelease.query.filter(MinetestRelease.name.like("{}%".format(major_minor)))
 994                                 if protocol_num:
 995                                         query = query.filter_by(protocol=protocol_num)
 996
 997                                 release = query.one_or_none()
 998                                 if release:
 999                                         return release
1000
1001                 if protocol_num:
1002                         return MinetestRelease.query.filter_by(protocol=protocol_num).first()
1003
1004                 return None
1005
1006
1007 class PackageRelease(db.Model):
1008         id           = db.Column(db.Integer, primary_key=True)
1009
1010         package_id   = db.Column(db.Integer, db.ForeignKey("package.id"))
1011         title        = db.Column(db.String(100), nullable=False)
1012         releaseDate  = db.Column(db.DateTime,    nullable=False)
1013         url          = db.Column(db.String(200), nullable=False)
1014         approved     = db.Column(db.Boolean, nullable=False, default=False)
1015         task_id      = db.Column(db.String(37), nullable=True)
1016         commit_hash  = db.Column(db.String(41), nullable=True, default=None)
1017         downloads    = db.Column(db.Integer, nullable=False, default=0)
1018
1019         min_rel_id = db.Column(db.Integer, db.ForeignKey("minetest_release.id"), nullable=True, server_default=None)
1020         min_rel    = db.relationship("MinetestRelease", foreign_keys=[min_rel_id])
1021
1022         max_rel_id = db.Column(db.Integer, db.ForeignKey("minetest_release.id"), nullable=True, server_default=None)
1023         max_rel    = db.relationship("MinetestRelease", foreign_keys=[max_rel_id])
1024
1025         # If the release is approved, then the task_id must be null and the url must be present
1026         CK_approval_valid = db.CheckConstraint("not approved OR (task_id IS NULL AND (url = '') IS NOT FALSE)")
1027
1028         def getAsDictionary(self):
1029                 return {
1030                         "id": self.id,
1031                         "title": self.title,
1032                         "url": self.url if self.url != "" else None,
1033                         "release_date": self.releaseDate.isoformat(),
1034                         "commit": self.commit_hash,
1035                         "downloads": self.downloads,
1036                         "min_protocol": self.min_rel and self.min_rel.protocol,
1037                         "max_protocol": self.max_rel and self.max_rel.protocol
1038                 }
1039
1040         def getEditURL(self):
1041                 return url_for("packages.edit_release",
1042                                 author=self.package.author.username,
1043                                 name=self.package.name,
1044                                 id=self.id)
1045
1046         def getDeleteURL(self):
1047                 return url_for("packages.delete_release",
1048                                 author=self.package.author.username,
1049                                 name=self.package.name,
1050                                 id=self.id)
1051
1052         def getDownloadURL(self):
1053                 return url_for("packages.download_release",
1054                                 author=self.package.author.username,
1055                                 name=self.package.name,
1056                                 id=self.id)
1057
1058         def __init__(self):
1059                 self.releaseDate = datetime.datetime.now()
1060
1061         def approve(self, user):
1062                 if not self.package.checkPerm(user, Permission.APPROVE_RELEASE):
1063                         return False
1064
1065                 assert self.task_id is None and self.url is not None and self.url != ""
1066
1067                 self.approved = True
1068                 return True
1069
1070         def checkPerm(self, user, perm):
1071                 if not user.is_authenticated:
1072                         return False
1073
1074                 if type(perm) == str:
1075                         perm = Permission[perm]
1076                 elif type(perm) != Permission:
1077                         raise Exception("Unknown permission given to PackageRelease.checkPerm()")
1078
1079                 isOwner = user == self.package.author
1080
1081                 if perm == Permission.DELETE_RELEASE:
1082                         if user.rank.atLeast(UserRank.ADMIN):
1083                                 return True
1084
1085                         if not (isOwner or user.rank.atLeast(UserRank.EDITOR)):
1086                                 return False
1087
1088                         if not self.package.approved or self.task_id is not None:
1089                                 return True
1090
1091                         count = PackageRelease.query \
1092                                         .filter_by(package_id=self.package_id) \
1093                                         .filter(PackageRelease.id > self.id) \
1094                                         .count()
1095
1096                         return count > 0
1097                 else:
1098                         raise Exception("Permission {} is not related to releases".format(perm.name))
1099
1100
1101 # class PackageReview(db.Model):
1102 #       id         = db.Column(db.Integer, primary_key=True)
1103 #       package_id = db.Column(db.Integer, db.ForeignKey("package.id"))
1104 #       thread_id  = db.Column(db.Integer, db.ForeignKey("thread.id"), nullable=False)
1105 #       recommend  = db.Column(db.Boolean, nullable=False, default=True)
1106
1107
1108 class PackageScreenshot(db.Model):
1109         id         = db.Column(db.Integer, primary_key=True)
1110         package_id = db.Column(db.Integer, db.ForeignKey("package.id"))
1111         title      = db.Column(db.String(100), nullable=False)
1112         url        = db.Column(db.String(100), nullable=False)
1113         approved   = db.Column(db.Boolean, nullable=False, default=False)
1114
1115
1116         def getEditURL(self):
1117                 return url_for("packages.edit_screenshot",
1118                                 author=self.package.author.username,
1119                                 name=self.package.name,
1120                                 id=self.id)
1121
1122         def getThumbnailURL(self, level=2):
1123                 return self.url.replace("/uploads/", ("/thumbnails/{:d}/").format(level))
1124
1125
1126 class APIToken(db.Model):
1127         id           = db.Column(db.Integer, primary_key=True)
1128         access_token = db.Column(db.String(34), unique=True)
1129
1130         name         = db.Column(db.String(100), nullable=False)
1131         owner_id     = db.Column(db.Integer, db.ForeignKey("user.id"), nullable=False)
1132         # owner is created using backref
1133
1134         created_at   = db.Column(db.DateTime, nullable=False, default=datetime.datetime.utcnow)
1135
1136         package_id = db.Column(db.Integer, db.ForeignKey("package.id"), nullable=True)
1137         package    = db.relationship("Package", foreign_keys=[package_id])
1138
1139         def canOperateOnPackage(self, package):
1140                 if self.package and self.package != package:
1141                         return False
1142
1143                 return package.author == self.owner
1144
1145
1146 class EditRequest(db.Model):
1147         id           = db.Column(db.Integer, primary_key=True)
1148
1149         package_id   = db.Column(db.Integer, db.ForeignKey("package.id"))
1150         author_id    = db.Column(db.Integer, db.ForeignKey("user.id"))
1151
1152         title        = db.Column(db.String(100), nullable=False)
1153         desc         = db.Column(db.String(1000), nullable=True)
1154
1155         # 0 - open
1156         # 1 - merged
1157         # 2 - rejected
1158         status       = db.Column(db.Integer, nullable=False, default=0)
1159
1160         changes = db.relationship("EditRequestChange", backref="request",
1161                         lazy="dynamic")
1162
1163         def getURL(self):
1164                 return url_for("view_editrequest_page",
1165                                 author=self.package.author.username,
1166                                 name=self.package.name,
1167                                 id=self.id)
1168
1169         def getApproveURL(self):
1170                 return url_for("approve_editrequest_page",
1171                                 author=self.package.author.username,
1172                                 name=self.package.name,
1173                                 id=self.id)
1174
1175         def getRejectURL(self):
1176                 return url_for("reject_editrequest_page",
1177                                 author=self.package.author.username,
1178                                 name=self.package.name,
1179                                 id=self.id)
1180
1181         def getEditURL(self):
1182                 return url_for("create_edit_editrequest_page",
1183                                 author=self.package.author.username,
1184                                 name=self.package.name,
1185                                 id=self.id)
1186
1187         def applyAll(self, package):
1188                 for change in self.changes:
1189                         change.apply(package)
1190
1191
1192         def checkPerm(self, user, perm):
1193                 if not user.is_authenticated:
1194                         return False
1195
1196                 if type(perm) == str:
1197                         perm = Permission[perm]
1198                 elif type(perm) != Permission:
1199                         raise Exception("Unknown permission given to EditRequest.checkPerm()")
1200
1201                 isOwner = user == self.author
1202
1203                 # Members can edit their own packages, and editors can edit any packages
1204                 if perm == Permission.EDIT_EDITREQUEST:
1205                         return isOwner or user.rank.atLeast(UserRank.EDITOR)
1206
1207                 else:
1208                         raise Exception("Permission {} is not related to packages".format(perm.name))
1209
1210
1211
1212
1213 class EditRequestChange(db.Model):
1214         id           = db.Column(db.Integer, primary_key=True)
1215
1216         request_id   = db.Column(db.Integer, db.ForeignKey("edit_request.id"))
1217         key          = db.Column(db.Enum(PackagePropertyKey), nullable=False)
1218
1219         # TODO: make diff instead
1220         oldValue     = db.Column(db.Text, nullable=True)
1221         newValue     = db.Column(db.Text, nullable=True)
1222
1223         def apply(self, package):
1224                 if self.key == PackagePropertyKey.tags:
1225                         package.tags.clear()
1226                         for tagTitle in self.newValue.split(","):
1227                                 tag = Tag.query.filter_by(title=tagTitle.strip()).first()
1228                                 package.tags.append(tag)
1229
1230                 else:
1231                         setattr(package, self.key.name, self.newValue)
1232
1233
1234 watchers = db.Table("watchers",
1235     db.Column("user_id", db.Integer, db.ForeignKey("user.id"), primary_key=True),
1236     db.Column("thread_id", db.Integer, db.ForeignKey("thread.id"), primary_key=True)
1237 )
1238
1239 class Thread(db.Model):
1240         id         = db.Column(db.Integer, primary_key=True)
1241
1242         package_id = db.Column(db.Integer, db.ForeignKey("package.id"), nullable=True)
1243         package    = db.relationship("Package", foreign_keys=[package_id])
1244
1245         review_id  = db.Column(db.Integer, db.ForeignKey("package_review.id"), nullable=True)
1246         review     = db.relationship("PackageReview", foreign_keys=[review_id])
1247
1248         author_id  = db.Column(db.Integer, db.ForeignKey("user.id"), nullable=False)
1249         title      = db.Column(db.String(100), nullable=False)
1250         private    = db.Column(db.Boolean, server_default="0", nullable=False)
1251
1252         locked     = db.Column(db.Boolean, server_default="0", nullable=False)
1253
1254         created_at = db.Column(db.DateTime, nullable=False, default=datetime.datetime.utcnow)
1255
1256         replies    = db.relationship("ThreadReply", backref="thread", lazy="dynamic", \
1257                         order_by=db.asc("thread_reply_id"))
1258
1259         watchers   = db.relationship("User", secondary=watchers, lazy="subquery", \
1260                         backref=db.backref("watching", lazy=True))
1261
1262         def getViewURL(self):
1263                 return url_for("threads.view", id=self.id)
1264
1265         def getSubscribeURL(self):
1266                 return url_for("threads.subscribe", id=self.id)
1267
1268         def getUnsubscribeURL(self):
1269                 return url_for("threads.unsubscribe", id=self.id)
1270
1271         def checkPerm(self, user, perm):
1272                 if not user.is_authenticated:
1273                         return perm == Permission.SEE_THREAD and not self.private
1274
1275                 if type(perm) == str:
1276                         perm = Permission[perm]
1277                 elif type(perm) != Permission:
1278                         raise Exception("Unknown permission given to Thread.checkPerm()")
1279
1280                 isMaintainer = user == self.author or (self.package is not None and self.package.author == user)
1281                 if self.package:
1282                         isMaintainer = isMaintainer or user in self.package.maintainers
1283
1284                 canSee = not self.private or isMaintainer or user.rank.atLeast(UserRank.EDITOR)
1285
1286                 if perm == Permission.SEE_THREAD:
1287                         return canSee
1288
1289                 elif perm == Permission.COMMENT_THREAD:
1290                         return canSee and (not self.locked or user.rank.atLeast(UserRank.MODERATOR))
1291
1292                 elif perm == Permission.LOCK_THREAD:
1293                         return user.rank.atLeast(UserRank.MODERATOR)
1294
1295                 else:
1296                         raise Exception("Permission {} is not related to threads".format(perm.name))
1297
1298 class ThreadReply(db.Model):
1299         id         = db.Column(db.Integer, primary_key=True)
1300         thread_id  = db.Column(db.Integer, db.ForeignKey("thread.id"), nullable=False)
1301         comment    = db.Column(db.String(2000), nullable=False)
1302         author_id  = db.Column(db.Integer, db.ForeignKey("user.id"), nullable=False)
1303         created_at = db.Column(db.DateTime, nullable=False, default=datetime.datetime.utcnow)
1304
1305         def checkPerm(self, user, perm):
1306                 if not user.is_authenticated:
1307                         return False
1308
1309                 if type(perm) == str:
1310                         perm = Permission[perm]
1311                 elif type(perm) != Permission:
1312                         raise Exception("Unknown permission given to ThreadReply.checkPerm()")
1313
1314                 if perm == Permission.EDIT_REPLY:
1315                         return user == self.author and user.rank.atLeast(UserRank.MEMBER) and not self.thread.locked
1316
1317                 elif perm == Permission.DELETE_REPLY:
1318                         return user.rank.atLeast(UserRank.MODERATOR) and self.thread.replies[0] != self
1319
1320                 else:
1321                         raise Exception("Permission {} is not related to threads".format(perm.name))
1322
1323
1324 class PackageReview(db.Model):
1325         id         = db.Column(db.Integer, primary_key=True)
1326
1327         package_id = db.Column(db.Integer, db.ForeignKey("package.id"), nullable=True)
1328         package    = db.relationship("Package", foreign_keys=[package_id], backref=db.backref("reviews", lazy=True))
1329
1330         created_at = db.Column(db.DateTime, nullable=False, default=datetime.datetime.utcnow)
1331
1332         author_id  = db.Column(db.Integer, db.ForeignKey("user.id"), nullable=False)
1333         author     = db.relationship("User", foreign_keys=[author_id], backref=db.backref("reviews", lazy=True))
1334
1335         recommends = db.Column(db.Boolean, nullable=False)
1336
1337         thread     = db.relationship("Thread", uselist=False, back_populates="review")
1338
1339         def asSign(self):
1340                 return 1 if self.recommends else -1
1341
1342         def getEditURL(self):
1343                 return self.package.getReviewURL()
1344
1345         def getDeleteURL(self):
1346                 return url_for("packages.delete_review",
1347                                 author=self.package.author.username,
1348                                 name=self.package.name)
1349
1350
1351 class AuditSeverity(enum.Enum):
1352         NORMAL = 0 # Normal user changes
1353         EDITOR = 1 # Editor changes
1354         MODERATION = 2 # Destructive / moderator changes
1355
1356         def __str__(self):
1357                 return self.name
1358
1359         def getTitle(self):
1360                 return self.name.replace("_", " ").title()
1361
1362         @classmethod
1363         def choices(cls):
1364                 return [(choice, choice.getTitle()) for choice in cls]
1365
1366         @classmethod
1367         def coerce(cls, item):
1368                 return item if type(item) == AuditSeverity else AuditSeverity[item]
1369
1370
1371
1372 class AuditLogEntry(db.Model):
1373         id         = db.Column(db.Integer, primary_key=True)
1374
1375         created_at = db.Column(db.DateTime, nullable=False, default=datetime.datetime.utcnow)
1376
1377         causer_id  = db.Column(db.Integer, db.ForeignKey("user.id"), nullable=False)
1378         causer     = db.relationship("User", foreign_keys=[causer_id])
1379
1380         severity   = db.Column(db.Enum(AuditSeverity), nullable=False)
1381
1382         title      = db.Column(db.String(100), nullable=False)
1383         url        = db.Column(db.String(200), nullable=True)
1384
1385         package_id = db.Column(db.Integer, db.ForeignKey("package.id"), nullable=True)
1386         package    = db.relationship("Package", foreign_keys=[package_id])
1387
1388         description = db.Column(db.Text, nullable=True, default=None)
1389
1390         def __init__(self, causer, severity, title, url, package=None, description=None):
1391                 if len(title) > 100:
1392                         title = title[:99] + "…"
1393
1394                 self.causer   = causer
1395                 self.severity = severity
1396                 self.title    = title
1397                 self.url      = url
1398                 self.package  = package
1399                 self.description = description
1400
1401
1402
1403
1404 REPO_BLACKLIST = [".zip", "mediafire.com", "dropbox.com", "weebly.com", \
1405                 "minetest.net", "dropboxusercontent.com", "4shared.com", \
1406                 "digitalaudioconcepts.com", "hg.intevation.org", "www.wtfpl.net", \
1407                 "imageshack.com", "imgur.com"]
1408
1409 class ForumTopic(db.Model):
1410         topic_id  = db.Column(db.Integer, primary_key=True, autoincrement=False)
1411         author_id = db.Column(db.Integer, db.ForeignKey("user.id"), nullable=False)
1412         author    = db.relationship("User")
1413
1414         wip       = db.Column(db.Boolean, server_default="0")
1415         discarded = db.Column(db.Boolean, server_default="0")
1416
1417         type      = db.Column(db.Enum(PackageType), nullable=False)
1418         title     = db.Column(db.String(200), nullable=False)
1419         name      = db.Column(db.String(30), nullable=True)
1420         link      = db.Column(db.String(200), nullable=True)
1421
1422         posts     = db.Column(db.Integer, nullable=False)
1423         views     = db.Column(db.Integer, nullable=False)
1424
1425         created_at = db.Column(db.DateTime, nullable=False, default=datetime.datetime.utcnow)
1426
1427         def getRepoURL(self):
1428                 if self.link is None:
1429                         return None
1430
1431                 for item in REPO_BLACKLIST:
1432                         if item in self.link:
1433                                 return None
1434
1435                 return self.link.replace("repo.or.cz/w/", "repo.or.cz/")
1436
1437         def getAsDictionary(self):
1438                 return {
1439                         "author": self.author.username,
1440                         "name":   self.name,
1441                         "type":   self.type.toName(),
1442                         "title":  self.title,
1443                         "id":     self.topic_id,
1444                         "link":   self.link,
1445                         "posts":  self.posts,
1446                         "views":  self.views,
1447                         "is_wip": self.wip,
1448                         "discarded":  self.discarded,
1449                         "created_at": self.created_at.isoformat(),
1450                 }
1451
1452         def checkPerm(self, user, perm):
1453                 if not user.is_authenticated:
1454                         return False
1455
1456                 if type(perm) == str:
1457                         perm = Permission[perm]
1458                 elif type(perm) != Permission:
1459                         raise Exception("Unknown permission given to ForumTopic.checkPerm()")
1460
1461                 if perm == Permission.TOPIC_DISCARD:
1462                         return self.author == user or user.rank.atLeast(UserRank.EDITOR)
1463
1464                 else:
1465                         raise Exception("Permission {} is not related to topics".format(perm.name))
1466
1467
1468 # Setup Flask-User
1469 user_manager = UserManager(app, db, User)
1470
1471 if app.config.get("LOG_SQL"):
1472         import logging
1473         logging.basicConfig()
1474         logging.getLogger('sqlalchemy.engine').setLevel(logging.INFO)