database/platal/models.py

   1 # -*- coding: utf-8 -*-
   2 #***************************************************************************
   3 #*  Copyright (C) 2015 Polytechnique.org                                   *
   4 #*  http://opensource.polytechnique.org/                                   *
   5 #*                                                                         *
   6 #*  This program is free software; you can redistribute it and/or modify   *
   7 #*  it under the terms of the GNU General Public License as published by   *
   8 #*  the Free Software Foundation; either version 2 of the License, or      *
   9 #*  (at your option) any later version.                                    *
  10 #*                                                                         *
  11 #*  This program is distributed in the hope that it will be useful,        *
  12 #*  but WITHOUT ANY WARRANTY; without even the implied warranty of         *
  13 #*  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the          *
  14 #*  GNU General Public License for more details.                           *
  15 #*                                                                         *
  16 #*  You should have received a copy of the GNU General Public License      *
  17 #*  along with this program; if not, write to the Free Software            *
  18 #*  Foundation, Inc.,                                                      *
  19 #*  59 Temple Place, Suite 330, Boston, MA  02111-1307  USA                *
  20 #***************************************************************************/
  21 """Django models which fit the latest version of Plat/al database
  22
  23 Latest version synced: Plat/al 1.1.15
  24 https://github.com/Polytechnique-org/platal/tree/xorg/maint/upgrade
  25
  26 cf. https://docs.djangoproject.com/en/dev/howto/legacy-databases/
  27
  28 This requires Django to work.
  29 """
  30 from __future__ import unicode_literals
  31
  32 import collections
  33 from django.db import models
  34 from django.utils.encoding import python_2_unicode_compatible
  35
  36
  37 def is_ax_visible(field):
  38     return field in ('public', 'ax')
  39
  40
  41 # Misc for Account/Profile
  42 # ========================
  43
  44
  45 @python_2_unicode_compatible
  46 class Skin(models.Model):
  47     id = models.IntegerField(primary_key=True)
  48     name = models.CharField(max_length=96)
  49     date = models.DateField()
  50     comment = models.CharField(max_length=765)
  51     auteur = models.CharField(max_length=90)
  52     skin_tpl = models.CharField(max_length=96)
  53     ext = models.CharField(max_length=9)
  54
  55     class Meta:
  56         db_table = 'skins'
  57
  58     def __str__(self):
  59         return self.name
  60
  61
  62 @python_2_unicode_compatible
  63 class EmailVirtualDomain(models.Model):
  64     id = models.IntegerField(primary_key=True)
  65     name = models.CharField(max_length=765)
  66     aliasing = models.ForeignKey('self', db_column='aliasing')
  67
  68     class Meta:
  69         db_table = 'email_virtual_domains'
  70
  71     def __str__(self):
  72         return self.name
  73
  74
  75 @python_2_unicode_compatible
  76 class ProfileSectionEnum(models.Model):
  77     id = models.IntegerField(primary_key=True)
  78     text = models.CharField(max_length=150, unique=True)
  79
  80     class Meta:
  81         db_table = 'profile_section_enum'
  82
  83     def __str__(self):
  84         return self.text
  85
  86
  87 @python_2_unicode_compatible
  88 class GeolocCountry(models.Model):
  89     iso_3166_1_a2 = models.CharField(max_length=6, primary_key=True)
  90     iso_3166_1_a3 = models.CharField(max_length=9, unique=True)
  91     iso_3166_1_num = models.IntegerField(unique=True)
  92     worldregion = models.CharField(max_length=6, db_column='worldRegion', blank=True) # Field name made lowercase.
  93     country = models.CharField(max_length=765, blank=True)
  94     countryen = models.CharField(max_length=765, db_column='countryEn', blank=True) # Field name made lowercase.
  95     capital = models.CharField(max_length=765)
  96     nationality = models.CharField(max_length=765, blank=True)
  97     nationalityen = models.CharField(max_length=765, db_column='nationalityEn', blank=True) # Field name made lowercase.
  98     phoneprefix = models.IntegerField(null=True, db_column='phonePrefix', blank=True) # Field name made lowercase.
  99     phoneformat = models.CharField(max_length=765, db_column='phoneFormat') # Field name made lowercase.
 100     licenseplate = models.CharField(max_length=12, db_column='licensePlate', blank=True) # Field name made lowercase.
 101     belongsto = models.ForeignKey('self', null=True, db_column='belongsTo', blank=True) # Field name made lowercase.
 102     countryplain = models.CharField(max_length=765, db_column='countryPlain', blank=True) # Field name made lowercase.
 103
 104     class Meta:
 105         db_table = 'geoloc_countries'
 106
 107     def __str__(self):
 108         return self.iso_3166_1_a2
 109
 110
 111 # Account/Profile
 112 # ===============
 113
 114
 115 @python_2_unicode_compatible
 116 class AccountType(models.Model):
 117     type = models.CharField(max_length=48, primary_key=True)
 118     perms = models.CharField(max_length=321)
 119     description = models.TextField(blank=True, null=True)
 120
 121     class Meta:
 122         db_table = 'account_types'
 123
 124     def __str__(self):
 125         return self.type
 126
 127
 128 @python_2_unicode_compatible
 129 class Account(models.Model):
 130     uid = models.AutoField(primary_key=True)
 131     hruid = models.CharField(max_length=255, unique=True)
 132     type = models.ForeignKey(AccountType, null=True, db_column='type', blank=True)
 133     user_perms = models.CharField(max_length=288, blank=True)
 134     is_admin = models.BooleanField(default=False)
 135     state = models.CharField(max_length=24)
 136     password = models.CharField(max_length=120, blank=True)
 137     token = models.CharField(max_length=96, blank=True)
 138     weak_password = models.CharField(max_length=768, blank=True)
 139     registration_date = models.DateTimeField()
 140     flags = models.CharField(max_length=15)
 141     comment = models.CharField(max_length=765, blank=True)
 142     email = models.CharField(max_length=765, blank=True)
 143     firstname = models.CharField(max_length=765, blank=True)
 144     lastname = models.CharField(max_length=765, blank=True)
 145     full_name = models.CharField(max_length=765, blank=True)
 146     directory_name = models.CharField(max_length=765, blank=True)
 147     sort_name = models.CharField(max_length=765, blank=True)
 148     display_name = models.CharField(max_length=765, blank=True)
 149     sex = models.CharField(max_length=18)
 150     email_format = models.CharField(max_length=12)
 151     skin = models.ForeignKey(Skin, null=True, db_column='skin', blank=True)
 152     last_version = models.CharField(max_length=48)
 153     best_domain = models.ForeignKey(EmailVirtualDomain, null=True, db_column='best_domain', blank=True)
 154     from_email = models.CharField(max_length=765)
 155     from_format = models.CharField(max_length=12)
 156
 157     class Meta:
 158         db_table = 'accounts'
 159
 160     def __str__(self):
 161         return '%s (%s)' % (self.hruid, self.full_name)
 162
 163     @property
 164     def profile(self):
 165         return self.profiles.filter(perms='owner').get().profile
 166
 167
 168 @python_2_unicode_compatible
 169 class ProfileAlias(object):
 170     def __init__(self, alias_of, kind, lastname, firstname=None):
 171         self.alias_of = alias_of
 172         self.kind = kind
 173         self.lastname = lastname
 174         self.firstname = alias_of.firstname if firstname is None else firstname
 175
 176     ALT_MARITAL = 'marital'
 177     ALT_PSEUDO = 'pseudo'
 178     ALT_ORDINARY = 'ordinary'
 179
 180     @property
 181     def is_pseudo(self):
 182         return self.kind == self.ALT_PSEUDO
 183
 184     def get_kind_display(self):
 185         if self.kind == self.ALT_MARITAL:
 186             if self.female:
 187                 return "Mme"
 188             else:
 189                 return "M."
 190         elif self.kind == self.ALT_PSEUDO:
 191             return "Pseudonyme"
 192         else:
 193             return ""
 194
 195     def __getattr__(self, attr):
 196         return getattr(self.alias_of, attr)
 197
 198     def __repr__(self):
 199         return '<ProfileAlias %s of %r>' % (self.kind, self.alias_of)
 200
 201
 202 @python_2_unicode_compatible
 203 class Profile(models.Model):
 204
 205     alias_of = None
 206
 207     def __init__(self, *args, **kwargs):
 208         super(Profile, self).__init__(*args, **kwargs)
 209         self._aliases = None
 210
 211     pid = models.AutoField(primary_key=True)
 212     hrpid = models.CharField(max_length=255, unique=True)
 213     xorg_id = models.IntegerField()
 214     ax_id = models.CharField(max_length=24, blank=True)
 215     birthdate = models.DateField(null=True, blank=True)
 216     birthdate_ref = models.DateField(null=True, blank=True)
 217     next_birthday = models.DateField(null=True, blank=True)
 218     deathdate = models.DateField(null=True, blank=True)
 219     deathdate_rec = models.DateField(null=True, blank=True)
 220     sex = models.CharField(max_length=18)
 221     section = models.ForeignKey(ProfileSectionEnum, null=True, db_column='section', blank=True)
 222     cv = models.TextField(blank=True)
 223     freetext = models.TextField(blank=True)
 224     freetext_pub = models.CharField(max_length=21)
 225     medals_pub = models.CharField(max_length=21)
 226     alias_pub = models.CharField(max_length=21)
 227     nationality1 = models.ForeignKey(GeolocCountry, null=True, db_column='nationality1', blank=True, related_name='natives')
 228     nationality2 = models.ForeignKey(GeolocCountry, null=True, db_column='nationality2', blank=True, related_name='second_natives')
 229     nationality3 = models.ForeignKey(GeolocCountry, null=True, db_column='nationality3', blank=True, related_name='third_natives')
 230     email_directory = models.CharField(max_length=765, blank=True)
 231     last_change = models.DateField()
 232     title = models.CharField(max_length=12)
 233
 234     class Meta:
 235         db_table = 'profiles'
 236
 237     def __str__(self):
 238         return self.hrpid
 239
 240     @property
 241     def is_alive(self):
 242         return self.deathdate is None
 243
 244     @property
 245     def account(self):
 246         return self.accounts.filter(perms='owner').get().account
 247
 248
 249 @python_2_unicode_compatible
 250 class AccountProfile(models.Model):
 251     account = models.ForeignKey(Account, db_column='uid', related_name='profiles')
 252     profile = models.ForeignKey(Profile, db_column='pid', related_name='accounts')
 253     perms = models.CharField(max_length=15)
 254
 255     class Meta:
 256         db_table = 'account_profiles'
 257         unique_together = (('account', 'profile'),)
 258
 259     def __str__(self):
 260         return '%s -> %s' % (self.account.hruid, self.profile.hrpid)
 261
 262
 263 # Email routing
 264 # =============
 265
 266
 267 @python_2_unicode_compatible
 268 class EmailVirtual(models.Model):
 269     email = models.CharField(max_length=255)
 270     domain = models.ForeignKey(EmailVirtualDomain, db_column='domain')
 271     redirect = models.CharField(max_length=765)
 272     type = models.CharField(max_length=21, blank=True)
 273     expire = models.DateField()
 274
 275     class Meta:
 276         db_table = 'email_virtual'
 277         unique_together = (('email', 'domain'),)
 278
 279     def __str__(self):
 280         return "%s@%s (%s)" % (self.email, self.domain, self.type)
 281
 282
 283 @python_2_unicode_compatible
 284 class EmailRedirectAccount(models.Model):
 285     account = models.ForeignKey(Account, db_column='uid')
 286     redirect = models.CharField(max_length=765)
 287     rewrite = models.CharField(max_length=765)
 288     type = models.CharField(max_length=30)
 289     action = models.CharField(max_length=54)
 290     broken_date = models.DateField()
 291     broken_level = models.IntegerField()
 292     last = models.DateField()
 293     flags = models.CharField(max_length=24)
 294     hash = models.CharField(max_length=96, blank=True)
 295     allow_rewrite = models.BooleanField()
 296
 297     class Meta:
 298         db_table = 'email_redirect_account'
 299         unique_together = (('account', 'redirect'),)
 300
 301     def __str__(self):
 302         return "%s for %s (%s)" % (self.redirect, self.account.hruid, self.type)
 303
 304
 305 @python_2_unicode_compatible
 306 class EmailSourceAccount(models.Model):
 307     email = models.CharField(max_length=255)
 308     domain = models.ForeignKey(EmailVirtualDomain, db_column='domain')
 309     account = models.ForeignKey(Account, db_column='uid')
 310     type = models.CharField(max_length=9)
 311     flags = models.CharField(max_length=23)
 312     expire = models.DateField(blank=True, null=True)
 313
 314     class Meta:
 315         db_table = 'email_source_account'
 316         unique_together = (('email', 'domain'),)
 317
 318     def __str__(self):
 319         return "%s@%s (%s)" % (self.email, self.domain, self.type)
 320
 321
 322 @python_2_unicode_compatible
 323 class EmailSourceOther(models.Model):
 324     email = models.CharField(max_length=255)
 325     domain = models.ForeignKey(EmailVirtualDomain, db_column='domain')
 326     hrmid = models.CharField(max_length=255)
 327     type = models.CharField(max_length=8, blank=True, null=True)
 328     expire = models.DateField(blank=True, null=True)
 329
 330     class Meta:
 331         db_table = 'email_source_other'
 332         unique_together = (('email', 'domain'),)
 333
 334     def __str__(self):
 335         return "%s@%s (%s)" % (self.email, self.domain, self.type)
 336
 337
 338 @python_2_unicode_compatible
 339 class EmailRedirectOther(models.Model):
 340     hrmid = models.ForeignKey(EmailSourceOther, db_column='hrmid')
 341     redirect = models.CharField(max_length=255)
 342     type = models.CharField(max_length=10)
 343     action = models.CharField(max_length=18)
 344
 345     class Meta:
 346         db_table = 'email_redirect_other'
 347         unique_together = (('hrmid', 'redirect'),)
 348
 349     def __str__(self):
 350         return "%s -> %s (%s)" % (self.hrmid, self.redirect, self.type)
 351
 352
 353 # GApps
 354 # =====
 355
 356
 357 class GappsAccount(models.Model):
 358     l_userid = models.ForeignKey(Account, null=True, db_column='l_userid', blank=True)
 359     l_sync_password = models.BooleanField(default=True)
 360     l_activate_mail_redirection = models.BooleanField(default=True)
 361     g_account_id = models.CharField(max_length=48, blank=True)
 362     g_account_name = models.CharField(max_length=255, primary_key=True)
 363     g_domain = models.CharField(max_length=120, blank=True)
 364     g_first_name = models.CharField(max_length=120)
 365     g_last_name = models.CharField(max_length=120)
 366     g_status = models.CharField(max_length=39, blank=True)
 367     g_admin = models.BooleanField()
 368     g_suspension = models.CharField(max_length=768, blank=True)
 369     r_disk_usage = models.BigIntegerField(null=True, blank=True)
 370     r_creation = models.DateField(null=True, blank=True)
 371     r_last_login = models.DateField(null=True, blank=True)
 372     r_last_webmail = models.DateField(null=True, blank=True)
 373
 374     class Meta:
 375         db_table = 'gapps_accounts'
 376
 377
 378 class GappsNickname(models.Model):
 379     l_userid = models.ForeignKey(Account, null=True, db_column='l_userid', blank=True)
 380     g_account_name = models.CharField(max_length=768)
 381     g_nickname = models.CharField(max_length=255, primary_key=True)
 382
 383     class Meta:
 384         db_table = 'gapps_nicknames'
 385
 386
 387 class GappsQueue(models.Model):
 388     q_id = models.AutoField(primary_key=True)
 389     q_owner = models.ForeignKey(Account, null=True, blank=True, related_name='owned_gapps_jobs')
 390     q_recipient = models.ForeignKey(Account, null=True, blank=True, related_name='received_gapps_jobs')
 391     p_entry_date = models.DateTimeField()
 392     p_notbefore_date = models.DateTimeField()
 393     p_start_date = models.DateTimeField(null=True, blank=True)
 394     p_end_date = models.DateTimeField(null=True, blank=True)
 395     p_status = models.CharField(max_length=24)
 396     p_priority = models.CharField(max_length=27)
 397     p_admin_request = models.BooleanField()
 398     j_type = models.CharField(max_length=30)
 399     j_parameters = models.TextField(blank=True)
 400     r_softfail_date = models.DateTimeField(null=True, blank=True)
 401     r_softfail_count = models.IntegerField()
 402     r_result = models.CharField(max_length=768, blank=True)
 403
 404     class Meta:
 405         db_table = 'gapps_queue'
 406
 407
 408 class GappsReporting(models.Model):
 409     date = models.DateField(primary_key=True)
 410     num_accounts = models.IntegerField(null=True, blank=True)
 411     count_1_day_actives = models.IntegerField(null=True, blank=True)
 412     count_7_day_actives = models.IntegerField(null=True, blank=True)
 413     count_14_day_actives = models.IntegerField(null=True, blank=True)
 414     count_30_day_actives = models.IntegerField(null=True, blank=True)
 415     count_30_day_idle = models.IntegerField(null=True, blank=True)
 416     count_60_day_idle = models.IntegerField(null=True, blank=True)
 417     count_90_day_idle = models.IntegerField(null=True, blank=True)
 418     usage_in_bytes = models.BigIntegerField(null=True, blank=True)
 419     quota_in_mb = models.IntegerField(null=True, blank=True)
 420
 421     class Meta:
 422         db_table = 'gapps_reporting'
 423
 424
 425 # Postfix
 426 # =======
 427
 428
 429 class MxWatch(models.Model):
 430     host = models.CharField(max_length=192, primary_key=True)
 431     state = models.CharField(max_length=21, blank=True)
 432     text = models.TextField()
 433
 434     class Meta:
 435         db_table = 'mx_watch'
 436
 437
 438 class PostfixBlacklist(models.Model):
 439     email = models.CharField(max_length=255, primary_key=True)
 440     reject_text = models.CharField(max_length=192)
 441
 442     class Meta:
 443         db_table = 'postfix_blacklist'
 444
 445
 446 class PostfixMailseen(models.Model):
 447     crc = models.CharField(max_length=24, primary_key=True)
 448     nb = models.IntegerField()
 449     update_time = models.DateTimeField()
 450     create_time = models.DateTimeField()
 451     release = models.CharField(max_length=18)
 452
 453     class Meta:
 454         db_table = 'postfix_mailseen'
 455
 456
 457 class PostfixWhitelist(models.Model):
 458     email = models.CharField(max_length=255, primary_key=True)
 459
 460     class Meta:
 461         db_table = 'postfix_whitelist'
 462
 463
 464
 465 # Misc
 466 # ====
 467
 468
 469 class EmailListModerate(models.Model):
 470     ml = models.CharField(max_length=192)
 471     domain = models.CharField(max_length=192)
 472     mid = models.IntegerField()
 473     account = models.ForeignKey(Account, null=True, db_column='uid', blank=True)
 474     action = models.CharField(max_length=18)
 475     ts = models.DateTimeField()
 476     message = models.TextField(blank=True)
 477     handler = models.IntegerField(null=True, blank=True)
 478
 479     class Meta:
 480         db_table = 'email_list_moderate'
 481         unique_together = (('ml', 'domain', 'mid'),)
 482
 483
 484 class EmailSendSave(models.Model):
 485     account = models.OneToOneField(Account, primary_key=True, db_column='uid')
 486     data = models.TextField()
 487
 488     class Meta:
 489         db_table = 'email_send_save'
 490
 491 class HomonymList(models.Model):
 492     hrmid = models.CharField(max_length=765)
 493     account = models.ForeignKey(Account, db_column='uid')
 494
 495     class Meta:
 496         db_table = 'homonyms_list'
 497         unique_together = (('hrmid', 'account'),)