class UserAccount(SQLObject): """A store of user information. A user account is created when a user is registered. """ username = StringCol(length=20, notNone=True, unique=True, alternateID=True) password = StringCol(length=40, notNone=True) # Store SHA-1 hex hashes. allowLogin = BoolCol(default=True) # If False, account login is disabled. email = StringCol(default=None, length=320) # See RFC 2821 section 4.5.3.1. # Don't split name field - see http://people.w3.org/rishida/blog/?p=100 realname = UnicodeCol(default=None, length=40) profile = UnicodeCol(default=None) #country = StringCol(length=2, default=None) # ISO 3166 country code. created = DateTimeCol(default=datetime.now) lastLogin = DateTimeCol(default=None) # friends = MultipleJoin('UserFriend', joinColumn='from_user') def _set_username(self, value): if not isinstance(value, str) or not(1 <= len(value) <= 20): raise ValueError("Invalid specification of username") if re.search("[^A-z0-9_]", value): raise ValueError("Username may only be alphanumeric characters") self._SO_set_username(value) def _set_password(self, value): if not isinstance(value, str) or not(1 <= len(value) <= 40): raise ValueError("Invalid specification of password") self._SO_set_password(value) def _set_email(self, value): # This regexp matches virtually all well-formatted email addresses. if value and not email_re.search(value): raise ValueError("Invalid or ill-formatted email address") self._SO_set_email(value)
class Bandwidth(SQLObject): booted_at = DateTimeCol() retrieved_at = DateTimeCol() received = IntCol() transmitted = IntCol() class sqlmeta: defaultOrder = "-booted_at" def __repr__(self): return '<%s - %s>' % (self.retrieved_at - self.booted_at, self.received + self.transmitted) def total(self): return self.received + self.transmitted def uptime(self): return self.retrieved_at - self.booted_at def at(self, index): return { 0: self.booted_at, 1: self.uptime(), 2: self.received, 3: self.transmitted, 4: self.total() }.get(index)
class BookContent(SQLObject): _connection = conn bookId = IntCol(default=0) chapterId = IntCol(default=0) chapterName = StringCol(length=100) content = StringCol() created_at = DateTimeCol(default=datetime.now()) updated_at = DateTimeCol(default=datetime.now())
class Task(SQLObject): title = UnicodeCol() creationDate = DateTimeCol(notNone=True) dueDate = DateTimeCol(default=None) doneDate = DateTimeCol(default=None) description = UnicodeCol(default="", notNone=True) urgency = IntCol(default=0, notNone=True) status = EnumCol(enumValues=['new', 'started', 'done']) project = ForeignKey("Project") keywords = RelatedJoin("Keyword", createRelatedTable=False, intermediateTable="task_keyword", joinColumn="task_id", otherColumn="keyword_id") recurrence = ForeignKey("Recurrence", default=None) def setKeywordDict(self, dct): """ Defines keywords of a task. Dict is of the form: keywordName => value """ for taskKeyword in TaskKeyword.selectBy(task=self): taskKeyword.destroySelf() for name, value in dct.items(): keyword = Keyword.selectBy(name=name)[0] TaskKeyword(task=self, keyword=keyword, value=value) def getKeywordDict(self): """ Returns all keywords of a task as a dict of the form: keywordName => value """ dct = {} for keyword in TaskKeyword.selectBy(task=self): dct[keyword.keyword.name] = keyword.value return dct def getKeywordsAsString(self): """ Returns all keywords as a string like "key1=value1, key2=value2..." """ return ", ".join( list(("%s=%s" % k for k in self.getKeywordDict().items()))) def getUserKeywordsNameAsString(self): """ Returns all keywords keys as a string like "key1, key2, key3...". Internal keywords (starting with _) are ignored. """ keywords = [ k for k in self.getKeywordDict().keys() if not k.startswith("_") ] keywords.sort() if keywords: return ", ".join(keywords) else: return ""
class Competition(SQLObject): name = UnicodeCol() place = UnicodeCol() plannedStartTime = DateTimeCol(default=None) startTime = DateTimeCol(default=None) finishTime = DateTimeCol(default=None) notes = UnicodeCol(default=None) active = BoolCol(default=None) competitors = MultipleJoin('Competitor') splits = MultipleJoin('Split')
class Book(SQLObject): _connection = conn # debug # _connection.debug = True bookDesc = StringCol(notNone=False) bookName = StringCol(length=100, unique=True, notNone=True) source = StringCol(length=50, notNone=True) author = StringCol(length=50, notNone=True) uploadUser = IntCol(length=10) created_by = DateTimeCol(notNone=False, default=datetime.datetime.now()) update_by = DateTimeCol(notNone=False, default=datetime.datetime.now()) price = DecimalCol(size=5, precision=2)
class Cert(SQLObject): ca = ForeignKey("CA") name = StringCol() key = ForeignKey("Key", cascade=False) serial = IntCol(default=-1) profile = ForeignKey("Profile") cert = StringCol(unique=True) start_time = DateTimeCol(default=None) end_time = DateTimeCol(default=None) def delete_cert(self): self.profile.delete_profile() self.delete(self.id)
class LogStat(ICTVObject): logger_name = StringCol(notNone=True, alternateID=True, length=50) last_debug = DateTimeCol(default=None) last_info = DateTimeCol(default=None) last_warning = DateTimeCol(default=None) last_error = DateTimeCol(default=None) n_entries = IntCol(notNone=True, default=0) @property def last_activity(self): infos = [ i for i in [ self.last_debug, self.last_info, self.last_warning, self.last_error ] if i is not None ] return max(infos) if infos else None @classmethod def dump_log_stats(cls, log_stats): for name, stats in log_stats.items(): try: log_stat = LogStat.byLogger_name(name) except SQLObjectNotFound: log_stat = LogStat(logger_name=name) for attr_name, stat_name in [("last_debug", "DEBUG"), ("last_info", "INFO"), ("last_warning", "WARNING"), ("last_error", "ERROR"), ("n_entries", "n_entries")]: try: setattr(log_stat, attr_name, stats[stat_name]) except KeyError: setattr(log_stat, attr_name, None if attr_name != "n_entries" else 0) @classmethod def load_log_stats(cls): result = {} for log_stat in LogStat.select(): result[log_stat.logger_name] = {} for attr_name, stat_name in [("last_debug", "DEBUG"), ("last_info", "INFO"), ("last_warning", "WARNING"), ("last_error", "ERROR"), ("last_activity", "last_activity"), ("n_entries", "n_entries")]: if getattr(log_stat, attr_name) is not None: result[log_stat.logger_name][stat_name] = getattr( log_stat, attr_name) return result
class TG_Group(InheritableSQLObject): """An ultra-simple group definition.""" class sqlmeta: table = "tg_group" group_name = UnicodeCol(length=16, alternateID=True, alternateMethodName="by_group_name") display_name = UnicodeCol(length=255) created = DateTimeCol(default=datetime.now) # Old names groupId = DeprecatedAttr("groupId", "group_name") displayName = DeprecatedAttr("displayName", "display_name") # collection of all users belonging to this group users = RelatedJoin("TG_User", intermediateTable="tg_user_group", joinColumn="group_id", otherColumn="user_id") # collection of all permissions for this group permissions = RelatedJoin("TG_Permission", joinColumn="group_id", intermediateTable="tg_group_permission", otherColumn="permission_id")
class TG_Visit(SQLObject): class sqlmeta: table = "tg_visit" visit_key = StringCol(length=40, alternateID=True, alternateMethodName="by_visit_key") created = DateTimeCol(default=datetime.now) expiry = DateTimeCol() @classmethod def lookup_visit(cls, visit_key): try: return cls.by_visit_key(visit_key) except SQLObjectNotFound: return None
class Group(SQLObject): """ An ultra-simple group definition. """ # names like "Group", "Order" and "User" are reserved words in SQL # so we set the name to something safe for SQL class sqlmeta: table = 'tg_group' group_name = UnicodeCol(length=16, alternateID=True, alternateMethodName='by_group_name') display_name = UnicodeCol(length=255) created = DateTimeCol(default=datetime.now) # collection of all users belonging to this group users = RelatedJoin('User', intermediateTable='user_group', joinColumn='group_id', otherColumn='user_id') # collection of all permissions for this group permissions = RelatedJoin('Permission', joinColumn='group_id', intermediateTable='group_permission', otherColumn='permission_id')
class SipAccount(SQLObject): class sqlmeta: table = 'sip_accounts_meta' username = StringCol(length=64) domain = StringCol(length=64) firstName = StringCol(length=64) lastName = StringCol(length=64) email = StringCol(length=64) customerId = IntCol(default=0) resellerId = IntCol(default=0) ownerId = IntCol(default=0) changeDate = DateTimeCol(default=DateTimeCol.now) ## joins data = MultipleJoin('SipAccountData', joinColumn='account_id') def _set_profile(self, value): data = list(self.data) if not data: SipAccountData(account=self, profile=value) else: data[0].profile = value def _get_profile(self): return self.data[0].profile def set(self, **kwargs): kwargs = kwargs.copy() profile = kwargs.pop('profile', None) SQLObject.set(self, **kwargs) if profile is not None: self._set_profile(profile)
class User(SQLObject): sourceId = IntCol() site = ForeignKey('Site', cascade=True) reputation = IntCol() creationDate = DateTimeCol(datetimeFormat=ISO_DATE_FORMAT) displayName = UnicodeCol() emailHash = UnicodeCol() lastAccessDate = DateTimeCol(datetimeFormat=ISO_DATE_FORMAT) websiteUrl = UnicodeCol() location = UnicodeCol() age = IntCol() aboutMe = UnicodeCol() views = IntCol() upVotes = IntCol() downVotes = IntCol() siteId_sourceId_index = DatabaseIndex(site, sourceId, unique=True)
class PodEntry(SQLObject): '''A lookup table of all your podcast subscriptions''' pod_title = StringCol() hashed = UnicodeCol() pub_date = DateTimeCol() cover = StringCol() description = StringCol() category = StringCol() active = BoolCol()
class Site(SQLObject): name = UnicodeCol() desc = UnicodeCol() key = UnicodeCol() dump_date = UnicodeCol() import_date = DateTimeCol() base_url = UnicodeCol() siteKey_index = DatabaseIndex(key, unique=True)
class Distribution(SQLObject): class sqlmeta: table = 'ons_distribution' uri = StringCol() national_statistic = BoolCol() version = StringCol() edition = StringCol() release_date = DateTimeCol() next_release = DateCol() contacts = RelatedJoin('Contact')
class SOTestCreateDrop(SQLObject): class sqlmeta(sqlmeta): idName = 'test_id_here' table = 'test_create_drop_table' name = StringCol() number = IntCol() so_time = DateTimeCol() short = StringCol(length=10) blobcol = BLOBCol()
class Visit(SQLObject): """ A visit to your site """ class sqlmeta: table = 'visit' visit_key = StringCol(length=40, alternateID=True, alternateMethodName='by_visit_key') created = DateTimeCol(default=datetime.now) expiry = DateTimeCol() def lookup_visit(cls, visit_key): try: return cls.by_visit_key(visit_key) except SQLObjectNotFound: return None lookup_visit = classmethod(lookup_visit)
class Report(SQLObject): createdAt = DateTimeCol( default=DateTimeCol.now) # Date the ad was first scraped country = StringCol(length=2, default=None) # Country, 2 letter code site = StringCol(length=30, default=None) # Name of the website siteId = StringCol( length=100, default=None) # The unique ID from the site where it's scrapped from name = StringCol( length=100, default=None ) # Name of the report: duplicate, bogus, rent-missing, space-missing, timeout
class Journal(SQLObject): timestamp = DateTimeCol(default=datetime.now, notNone=True) level = IntCol(notNone=True) level_index = DatabaseIndex('level') text = UnicodeCol(length=255, notNone=True) text_index = DatabaseIndex('text') parent = ForeignKey('Journal', default=None) children = MultipleJoin('Journal') related = RelatedJoin('Journal', joinColumn='journal_id', otherColumn='journal_from_id') related_from = RelatedJoin('Journal', joinColumn='journal_from_id', otherColumn='journal_id', createRelatedTable=False)
class SOTestCyclicRefA(SQLObject): class sqlmeta(sqlmeta): idName = 'test_id_here' table = 'test_cyclic_ref_a_table' name = StringCol() number = IntCol() so_time = DateTimeCol() short = StringCol(length=10) blobcol = BLOBCol() fkeyb = ForeignKey('SOTestCyclicRefB')
class Rent(SQLObject): # Total cost totalRent = FloatCol(default=None) # Surface in square meters livingSpace = FloatCol(default=None) # Currency, 3 letter code currency = StringCol(length=3, default='EUR') # Date of arrival moveIn = DateCol(default=None) # IP of the user hashed ipHash = StringCol(length=512, default=None) # Date of creating createdAt = DateTimeCol(default=DateTimeCol.now)
class TG_User(InheritableSQLObject): """Reasonably basic User definition.""" class sqlmeta: table = "tg_user" user_name = UnicodeCol(length=16, alternateID=True, alternateMethodName="by_user_name") email_address = UnicodeCol(length=255, alternateID=True, alternateMethodName="by_email_address") display_name = UnicodeCol(length=255) password = UnicodeCol(length=40) created = DateTimeCol(default=datetime.now) # Old attribute names userId = DeprecatedAttr("userId", "user_name") emailAddress = DeprecatedAttr("emailAddress", "email_address") displayName = DeprecatedAttr("displayName", "display_name") # groups this user belongs to groups = RelatedJoin("TG_Group", intermediateTable="tg_user_group", joinColumn="user_id", otherColumn="group_id") def _get_permissions(self): perms = set() for g in self.groups: perms = perms | set(g.permissions) return perms def _set_password(self, cleartext_password): """Run cleartext_password through the hash algorithm before saving.""" try: hash = identity.current_provider.encrypt_password( cleartext_password) except identity.exceptions.IdentityManagementNotEnabledException: # Creating identity provider just to encrypt password # (so we don't reimplement the encryption step). ip = SqlObjectCsrfIdentityProvider() hash = ip.encrypt_password(cleartext_password) if hash == cleartext_password: log.info("Identity provider not enabled," " and no encryption algorithm specified in config." " Setting password as plaintext.") self._SO_set_password(hash) def set_password_raw(self, password): """Save the password as-is to the database.""" self._SO_set_password(password)
class Log(SQLObject): """ The log object Stores a list of word id's """ entry = StringCol() lex = ForeignKey('Lex') entry_date = DateTimeCol(default=sqlbuilder.func.NOW()) class sqlmeta: defaultOrder = 'entry_date' def wordList(self): """Return a list of word_ids for this log""" return self.entry.split('|')
class Comment(SQLObject): sourceId = IntCol() siteId = IntCol() postId = IntCol() score = IntCol() text = UnicodeCol() creationDate = DateTimeCol(datetimeFormat=ISO_DATE_FORMAT) userId = IntCol() siteId_postId_index = DatabaseIndex(siteId, postId) _connection = comment_db_sqlhub json_fields = [ 'id', 'score', 'text', 'creationDate', 'userId' ]
class Asset(ICTVObject): """ Represents the metadata of a file stored by the StorageManager. """ plugin_channel = ForeignKey('PluginChannel', notNone=True, cascade=True) user = ForeignKey('User') # The user who uploaded the file, if known filename = StringCol( default=None ) # The original filename of the asset, beginning with a period mime_type = StringCol( default=None) # The MIME type associated with the file extension = StringCol(default=None) file_size = BigIntCol(default=None) # File size in kilobytes created = DateTimeCol(default=DateTimeCol.now) last_reference = DateTimeCol(default=DateTimeCol.now) in_flight = BoolCol( default=False) # Is this asset being cached at the moment is_cached = BoolCol( default=False) # Is this asset a cached asset from CacheManager def _get_path(self, force=False): """ Returns the path to the asset on the filesystem or None if the asset file is being cached. """ self.last_reference = datetime.now() if not force and self.in_flight: return None elif force: self.in_flight = False # Prevent failures in the caching process to block asset in flight mode return os.path.join( 'static', 'storage', str(self.plugin_channel.id), str(self.id) + (self.extension if self.extension is not None else '')) def write_to_asset_file(self, content): """ Writes the content to the asset file. """ asset_path = os.path.join(get_root_path(), self.path) os.makedirs(os.path.dirname(asset_path), exist_ok=True) with open(asset_path, 'wb') as f: f.write(content)
class Dataset(SQLObject): class sqlmeta: table = 'wh_dataset' whitehall_id = IntCol(alternateID=True) stats_type = EnumCol(enumValues=[ 'Official Statistics', 'National Statistics', 'Statistical data set', None ], default=None) title = StringCol() url = StringCol(alternateID=True, length=255) orgs = RelatedJoin('Organisation') publication_date = DateTimeCol() government_name = StringCol() collections = RelatedJoin('Collection')
class Journal(SQLObject): timestamp = DateTimeCol(default=datetime.now, notNone=True) level = IntCol(notNone=True) level_index = DatabaseIndex("level") text = UnicodeCol(length=255, notNone=True) text_index = DatabaseIndex("text") parent = ForeignKey("Journal", default=None) children = MultipleJoin("Journal") related = RelatedJoin("Journal", joinColumn="journal_id", otherColumn="journal_from_id") related_from = RelatedJoin( "Journal", joinColumn="journal_from_id", otherColumn="journal_id", createRelatedTable=False, )
class Message(SQLObject): class sqlmeta: table = 'messages' message_id = StringCol() account_id = UnicodeCol(length=128) remote_uri = UnicodeCol(length=128) display_name = UnicodeCol(length=128) uri = UnicodeCol(length=128, default='') timestamp = DateTimeCol() direction = StringCol() content = UnicodeCol(sqlType='LONGTEXT') content_type = StringCol(default='text') state = StringCol(default='pending') encryption_type = StringCol(default='') disposition = StringCol(default='') remote_idx = DatabaseIndex('remote_uri') id_idx = DatabaseIndex('message_id') unq_idx = DatabaseIndex(message_id, account_id, remote_uri, unique=True)
class User(SQLObject): """ Reasonably basic User definition. Probably would want additional attributes. """ # names like "Group", "Order" and "User" are reserved words in SQL # so we set the name to something safe for SQL class sqlmeta: table = 'tg_user' user_name = UnicodeCol(length=16, alternateID=True, alternateMethodName='by_user_name') email_address = UnicodeCol(length=255, alternateID=True, alternateMethodName='by_email_address') display_name = UnicodeCol(length=255) password = UnicodeCol(length=40) created = DateTimeCol(default=datetime.now) # groups this user belongs to groups = RelatedJoin('Group', intermediateTable='user_group', joinColumn='user_id', otherColumn='group_id') def _get_permissions(self): perms = set() for g in self.groups: perms |= set(g.permissions) return perms def _set_password(self, cleartext_password): """Runs cleartext_password through the hash algorithm before saving.""" password_hash = identity.encrypt_password(cleartext_password) self._SO_set_password(password_hash) def set_password_raw(self, password): """Saves the password as-is to the database.""" self._SO_set_password(password)
def save_data(): _, received, transmitted = get_bytes() uptime, _ = get_uptime() now = DateTimeCol.now() boot_time = now - timedelta(seconds=uptime) boot_time = boot_time - timedelta(seconds=boot_time.second, microseconds=boot_time.microsecond) bws = Bandwidth.select(Bandwidth.q.booted_at==boot_time) for bw in bws: dr = received - bw.received dt = transmitted - bw.transmitted bw.received = received bw.transmitted = transmitted bw.retrieved_at = now break else: dr, dt = received, transmitted Bandwidth(booted_at=boot_time, retrieved_at=now, received=received, transmitted=transmitted) return dr, dt
def now(): return DateTimeCol.now()