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()
