class Collector(Base): '''Represents a collector to collect form entries.''' __tablename__ = "collector" id = id_column(__tablename__) # Inheritance configuration typ = Column('type', UnicodeText(50)) __mapper_args__ = {'polymorphic_on': typ} name = Column(UnicodeText(255), nullable=False) # When an entry is received, we can either display a thanks message, # or redirect to some URL. 3 columns are needed for this: thanks_message = Column(UnicodeText) thanks_url = Column(UnicodeText(2000)) # We define on_completion as a property to validate its possible values: ON_COMPLETION_VALUES = ('msg', 'url') _on_completion = Column('on_completion', Unicode(3)) @hybrid_property def on_completion(self): return self._on_completion @on_completion.setter def on_completion(self, val): if val not in self.ON_COMPLETION_VALUES: raise ValueError \ ('Invalid value for on_completion: "{0}"'.format(val)) self._on_completion = val email_each_entry = Column(Boolean, default=False) limit_by_date = Column(Boolean, default=False) start_date = Column(DateTime) end_date = Column(DateTime) message_after_end = Column(UnicodeText) message_before_start = Column(UnicodeText) # When an instance is persisted, it automatically gets a slug, slug = Column( UnicodeText(10), nullable=False, # a part of the URL. index=True, default=lambda: random_word(10)) form_id = Column(Integer, ForeignKey('form.id'), index=True) form = relationship(Form, backref=backref('collectors', order_by=id, cascade='all')) def __unicode__(self): return self.name def __repr__(self): return 'Collector(id={0}, name="{1}")'.format(self.id, self.name) def to_dict(self, translator=None): d = { k: getattr(self, k) for k in ('id', 'name', 'thanks_message', 'thanks_url', 'on_completion', 'message_before_start', 'message_after_end', 'email_each_entry', 'limit_by_date', 'slug', 'status') } d['start_date'] = unicode(self.start_date)[:16] \ if self.start_date else '' d['end_date'] = unicode(self.end_date)[:16] if self.end_date else '' d['type'] = self.typ d['display_type'] = self.typ.replace("_", " ").capitalize() d['translated_status'] = \ translator(d['status']) if translator else d['status'] return d STATUS_BEFORE = _('pending') # before start date STATUS_DURING = _('published') # entries may be created STATUS_AFTER = _('closed') # after end date @property def status(self): '''Returns a status code.''' if (self.start_date and datetime.utcnow() < self.start_date and self.limit_by_date): return self.STATUS_BEFORE if (self.end_date and datetime.utcnow() > self.end_date and self.limit_by_date): return self.STATUS_AFTER return self.STATUS_DURING
from datetime import datetime from sqlalchemy import Column, ForeignKey, MetaData, Table from sqlalchemy import DateTime, Integer, Unicode, UnicodeText meta = MetaData() milestone_table = Table( 'milestone', meta, Column('id', Integer, primary_key=True), Column('instance_id', Integer, ForeignKey('instance.id'), nullable=False), Column('creator_id', Integer, ForeignKey('user.id'), nullable=False), Column('title', Unicode(255), nullable=True), Column('text', UnicodeText(), nullable=True), Column('time', DateTime), Column('create_time', DateTime, default=datetime.utcnow), Column('delete_time', DateTime)) def upgrade(migrate_engine): meta.bind = migrate_engine Table('user', meta, autoload=True) Table('instance', meta, autoload=True) Table('badge', meta, autoload=True) category_column = Column('category_id', Integer, ForeignKey('badge.id'), nullable=True) category_column.create(milestone_table) modify_time_column = Column('modify_time', DateTime, nullable=True, onupdate=datetime.utcnow)
class Paper(AIDBase): __tablename__ = 'paper' id = Column(Integer(), autoincrement=True, primary_key=True) aid = Column(String(36)) title = Column(UnicodeText())
class DialectTypesTest(fixtures.TestBase, AssertsCompiledSQL): __dialect__ = oracle.OracleDialect() def test_no_clobs_for_string_params(self): """test that simple string params get a DBAPI type of VARCHAR, not CLOB. This is to prevent setinputsizes from setting up cx_oracle.CLOBs on string-based bind params [ticket:793].""" class FakeDBAPI(object): def __getattr__(self, attr): return attr dialect = oracle.OracleDialect() dbapi = FakeDBAPI() b = bindparam("foo", "hello world!") eq_(b.type.dialect_impl(dialect).get_dbapi_type(dbapi), "STRING") b = bindparam("foo", "hello world!") eq_(b.type.dialect_impl(dialect).get_dbapi_type(dbapi), "STRING") def test_long(self): self.assert_compile(oracle.LONG(), "LONG") @testing.combinations( (Date(), cx_oracle._OracleDate), (oracle.OracleRaw(), cx_oracle._OracleRaw), (String(), String), (VARCHAR(), cx_oracle._OracleString), (DATE(), cx_oracle._OracleDate), (oracle.DATE(), oracle.DATE), (String(50), cx_oracle._OracleString), (Unicode(), cx_oracle._OracleUnicodeStringCHAR), (Text(), cx_oracle._OracleText), (UnicodeText(), cx_oracle._OracleUnicodeTextCLOB), (CHAR(), cx_oracle._OracleChar), (NCHAR(), cx_oracle._OracleNChar), (NVARCHAR(), cx_oracle._OracleUnicodeStringNCHAR), (oracle.RAW(50), cx_oracle._OracleRaw), ) def test_type_adapt(self, start, test): dialect = cx_oracle.dialect() assert isinstance( start.dialect_impl(dialect), test), "wanted %r got %r" % (test, start.dialect_impl(dialect)) @testing.combinations( (String(), String), (VARCHAR(), cx_oracle._OracleString), (String(50), cx_oracle._OracleString), (Unicode(), cx_oracle._OracleUnicodeStringNCHAR), (Text(), cx_oracle._OracleText), (UnicodeText(), cx_oracle._OracleUnicodeTextNCLOB), (NCHAR(), cx_oracle._OracleNChar), (NVARCHAR(), cx_oracle._OracleUnicodeStringNCHAR), ) def test_type_adapt_nchar(self, start, test): dialect = cx_oracle.dialect(use_nchar_for_unicode=True) assert isinstance( start.dialect_impl(dialect), test), "wanted %r got %r" % (test, start.dialect_impl(dialect)) def test_raw_compile(self): self.assert_compile(oracle.RAW(), "RAW") self.assert_compile(oracle.RAW(35), "RAW(35)") def test_char_length(self): self.assert_compile(VARCHAR(50), "VARCHAR(50 CHAR)") oracle8dialect = oracle.dialect() oracle8dialect.server_version_info = (8, 0) self.assert_compile(VARCHAR(50), "VARCHAR(50)", dialect=oracle8dialect) self.assert_compile(NVARCHAR(50), "NVARCHAR2(50)") self.assert_compile(CHAR(50), "CHAR(50)") @testing.combinations( (String(50), "VARCHAR2(50 CHAR)"), (Unicode(50), "VARCHAR2(50 CHAR)"), (NVARCHAR(50), "NVARCHAR2(50)"), (VARCHAR(50), "VARCHAR(50 CHAR)"), (oracle.NVARCHAR2(50), "NVARCHAR2(50)"), (oracle.VARCHAR2(50), "VARCHAR2(50 CHAR)"), (String(), "VARCHAR2"), (Unicode(), "VARCHAR2"), (NVARCHAR(), "NVARCHAR2"), (VARCHAR(), "VARCHAR"), (oracle.NVARCHAR2(), "NVARCHAR2"), (oracle.VARCHAR2(), "VARCHAR2"), ) def test_varchar_types(self, typ, exp): dialect = oracle.dialect() self.assert_compile(typ, exp, dialect=dialect) @testing.combinations( (String(50), "VARCHAR2(50 CHAR)"), (Unicode(50), "NVARCHAR2(50)"), (NVARCHAR(50), "NVARCHAR2(50)"), (VARCHAR(50), "VARCHAR(50 CHAR)"), (oracle.NVARCHAR2(50), "NVARCHAR2(50)"), (oracle.VARCHAR2(50), "VARCHAR2(50 CHAR)"), (String(), "VARCHAR2"), (Unicode(), "NVARCHAR2"), (NVARCHAR(), "NVARCHAR2"), (VARCHAR(), "VARCHAR"), (oracle.NVARCHAR2(), "NVARCHAR2"), (oracle.VARCHAR2(), "VARCHAR2"), ) def test_varchar_use_nchar_types(self, typ, exp): dialect = oracle.dialect(use_nchar_for_unicode=True) self.assert_compile(typ, exp, dialect=dialect) @testing.combinations( (oracle.INTERVAL(), "INTERVAL DAY TO SECOND"), (oracle.INTERVAL(day_precision=3), "INTERVAL DAY(3) TO SECOND"), (oracle.INTERVAL(second_precision=5), "INTERVAL DAY TO SECOND(5)"), ( oracle.INTERVAL(day_precision=2, second_precision=5), "INTERVAL DAY(2) TO SECOND(5)", ), ) def test_interval(self, type_, expected): self.assert_compile(type_, expected)
def test_unicode_text_literal_binds(self): self.assert_compile( column("x", UnicodeText()) == "foo", "x = N'foo'", literal_binds=True, )
class WelcomeMsg(Base): __tablename__ = 'welcomes' chat_id = Column(BigInteger, primary_key=True) message = Column(UnicodeText(2500))
class Article(db.Model): __table_args__ = {'mysql_collate': 'utf8_bin'} id = Column(Integer, primary_key=True) title = Column(String(512)) authors = Column(UnicodeText) content = Column(UnicodeText()) summary = Column(UnicodeText) word_count = Column(Integer) published_time = Column(DateTime) fk_difficulty = Column(Integer) broken = Column(Integer) from zeeguu_core.model.url import Url from zeeguu_core.model.feed import RSSFeed from zeeguu_core.model.language import Language rss_feed_id = Column(Integer, ForeignKey(RSSFeed.id)) rss_feed = relationship(RSSFeed) url_id = Column(Integer, ForeignKey(Url.id), unique=True) url = relationship(Url) language_id = Column(Integer, ForeignKey(Language.id)) language = relationship(Language) from zeeguu_core.model.topic import Topic topics = relationship(Topic, secondary="article_topic_map", backref=backref('articles')) # Few words in an article is very often not an # actual article but the caption for a video / comic. # Or maybe an article that's behind a paywall and # has only the first paragraph available MINIMUM_WORD_COUNT = 90 def __init__(self, url, title, authors, content, summary, published_time, rss_feed, language, broken=0): self.url = url self.title = title self.authors = authors self.content = content self.summary = summary self.published_time = published_time self.rss_feed = rss_feed self.language = language self.broken = broken fk_estimator = DifficultyEstimatorFactory.get_difficulty_estimator( "fk") fk_difficulty = fk_estimator.estimate_difficulty( self.content, self.language, None)['grade'] # easier to store integer in the DB # otherwise we have to use Decimal, and it's not supported on all dbs self.fk_difficulty = fk_difficulty self.word_count = len(self.content.split()) def __repr__(self): return f'<Article {self.title} (w: {self.word_count}, d: {self.fk_difficulty}) ({self.url})>' def vote_broken(self): # somebody could vote that this article is broken self.broken += 1 def topics_as_string(self): topics = "" for topic in self.topics: topics += topic.title + " " return topics def contains_any_of(self, keywords: list): for each in keywords: if self.title.find(each) >= 0: return True return False def article_info(self, with_content=False): """ This is the data that is sent over the API to the Reader. Whatever the reader needs must be here. :return: """ result_dict = dict(id=self.id, title=self.title, url=self.url.as_string(), summary=self.summary, language=self.language.code, authors=self.authors, topics=self.topics_as_string(), metrics=dict(difficulty=self.fk_difficulty / 100, word_count=self.word_count)) if self.published_time: result_dict['published'] = self.published_time.strftime( JSON_TIME_FORMAT) if self.rss_feed: result_dict['feed_id'] = self.rss_feed.id, result_dict['icon_name'] = self.rss_feed.icon_name # TO DO: remove feed_image_url from RSSFeed --- this is here for compatibility # until the codebase is moved to zorg. if self.rss_feed.image_url: result_dict[ 'feed_image_url'] = self.rss_feed.image_url.as_string() if with_content: result_dict['content'] = self.content return result_dict def add_topic(self, topic): self.topics.append(topic) def add_search(self, search): self.searches.append(search) def remove_search(self, search): print("trying to remove a search term") self.searches.remove(search) def star_for_user(self, session, user, state=True): from zeeguu_core.model.user_article import UserArticle ua = UserArticle.find_or_create(session, user, self) ua.set_starred(state) session.add(ua) @classmethod def find_or_create(cls, session, _url: str, language=None, sleep_a_bit=False): """ If not found, download and extract all the required info for this article. :param url: :return: """ from zeeguu_core.model import Url, Article, Language import newspaper url = Url.extract_canonical_url(_url) try: found = cls.find(url) if found: return found art = newspaper.Article(url=url) art.download() art.parse() if art.text == '': raise Exception("Newspaper got empty article from: " + url) if sleep_a_bit: import time from random import randint print("GOT: " + url) sleep_time = randint(3, 33) print( f"sleeping for {sleep_time}s... so we don't annoy our friendly servers" ) time.sleep(sleep_time) if not language: if art.meta_lang == '': art.meta_lang = detect(art.text) zeeguu_core.log(f"langdetect: {art.meta_lang} for {url}") language = Language.find_or_create(art.meta_lang) # Create new article and save it to DB url_object = Url.find_or_create(session, url) new_article = Article( url_object, art.title, ', '.join(art.authors), art.text[ 0: 32000], # any article longer than this will be truncated... art.summary, None, None, language) session.add(new_article) session.commit() return new_article except sqlalchemy.exc.IntegrityError or sqlalchemy.exc.DatabaseError: for i in range(10): try: session.rollback() u = cls.find(url) print("Found article by url after recovering from race") return u except: print("Exception of second degree in article..." + str(i)) time.sleep(0.3) continue break @classmethod def find_by_id(cls, id: int): return Article.query.filter(Article.id == id).first() @classmethod def find(cls, url: str): """ Find by url :return: object or None if not found """ from zeeguu_core.model import Url try: url_object = Url.find(url) return (cls.query.filter(cls.url == url_object)).one() except NoResultFound: return None @classmethod def all_older_than(cls, days): import datetime today = datetime.date.today() long_ago = today - datetime.timedelta(days) try: return cls.query.filter(cls.published_time < long_ago).all() except NoResultFound: return [] @classmethod def exists(cls, article): try: cls.query.filter(cls.url == article.url).one() return True except NoResultFound: return False @classmethod def with_title_containing(cls, needle): return cls.query.filter(cls.title.like(f"%{needle}%")).all()
class Release(Base): """ Describe Python Package Release. """ @declared_attr def __table_args__(cls): return (Index('idx_%s_package_id_version' % cls.__tablename__, 'package_id', 'version', unique=True), { 'mysql_engine': 'InnoDB', 'mysql_charset': 'utf8', }) version = Column(Unicode(60), nullable=False) summary = Column(Unicode(255)) downloads = Column(Integer, default=0) package_id = Column(Integer, ForeignKey(Package.id), nullable=False) author_id = Column(Integer, ForeignKey(User.id)) maintainer_id = Column(Integer, ForeignKey(User.id)) stable_version = Column(Unicode(60)) home_page = Column(Unicode(255)) license = Column(UnicodeText()) description = Column(UnicodeText()) keywords = Column(Unicode(255)) platform = Column(Unicode(80)) download_url = Column(Unicode(800)) bugtrack_url = Column(Unicode(800)) docs_url = Column(Unicode(800)) classifiers = relationship(Classifier, secondary=classifier__release, lazy='dynamic', cascade='all, delete') package = relationship(Package, lazy='joined', backref=backref('releases', cascade='all, delete-orphan')) author = relationship(User, primaryjoin=author_id == User.id) maintainer = relationship(User, primaryjoin=maintainer_id == User.id) @property def download_url_file(self): """ Filename of the download_url if any. """ url = self.download_url return url.rsplit('/', 1).pop() if url else None @property def can_download_url_whl(self): filename = self.download_url_file.split('#').pop(0) return (self.filename.endswith('.tar.gz') or self.filename.endswith('.tar.bz2') or self.filename.endswith('.zip')) @property def whlify_download_url_file(self): return _whlify(self.download_url_file.split('#').pop(0)) @classmethod def by_version(cls, session, package_name, version): """ Get release for a given version. :param session: SQLAlchemy session :type session: :class:`sqlalchemy.Session` :param package_name: package name :type package_name: unicode :param version: version :type version: unicode :return: release instance :rtype: :class:`pyshop.models.Release` """ return cls.first(session, join=(Package, ), where=((Package.name == package_name), (cls.version == version))) @classmethod def by_classifiers(cls, session, classifiers): """ Get releases for given classifiers. :param session: SQLAlchemy session :type session: :class:`sqlalchemy.Session` :param classifiers: classifiers :type classifiers: unicode :return: release instances :rtype: generator of :class:`pyshop.models.Release` """ return cls.find( session, join=(cls.classifiers, ), where=(Classifier.name.in_(classifiers), ), ) @classmethod def search(cls, session, opts, operator): """ Get releases for given filters. :param session: SQLAlchemy session :type session: :class:`sqlalchemy.Session` :param opts: filtering options :type opts: dict :param operator: filtering options joining operator (`and` or `or`) :type operator: basestring :return: release instances :rtype: generator of :class:`pyshop.models.Release` """ available = { 'name': Package.name, 'version': cls.version, 'author': User.login, 'author_email': User.email, 'maintainer': User.login, 'maintainer_email': User.email, 'home_page': cls.home_page, 'license': cls.license, 'summary': cls.summary, 'description': cls.description, 'keywords': cls.keywords, 'platform': cls.platform, 'download_url': cls.download_url } oper = {'or': or_, 'and': and_} join_map = { 'name': Package, 'author': cls.author, 'author_email': cls.author, 'maintainer': cls.maintainer, 'maintainer_email': cls.maintainer, } where = [] join = [] for opt, val in opts.items(): field = available[opt] if hasattr(val, '__iter__') and len(val) > 1: stmt = or_(*[field.like(u'%%%s%%' % v) for v in val]) else: stmt = field.like(u'%%%s%%' % val) where.append(stmt) if opt in join_map: join.append(join_map[opt]) return cls.find(session, join=join, where=(oper[operator](*where), ))
class ReleaseFile(Base): """ Describe a release file. """ release_id = Column(Integer, ForeignKey(Release.id), nullable=False) filename = Column(Unicode(200), unique=True, nullable=False) md5_digest = Column(Unicode(50)) size = Column(Integer) package_type = Column(Enum(u'sdist', u'bdist_egg', u'bdist_msi', u'bdist_dmg', u'bdist_rpm', u'bdist_dumb', u'bdist_wininst', u'bdist_wheel', name='enum_release_file_package_type'), nullable=False) python_version = Column(Unicode(25)) url = Column(Unicode(1024)) downloads = Column(Integer, default=0) has_sig = Column(Boolean, default=False) comment_text = Column(UnicodeText()) release = relationship(Release, lazy='joined', backref=backref('files', cascade='all, delete-orphan')) @property def filename_whlified(self): assert self.package_type == 'sdist' return _whlify(self.filename) @classmethod def by_release(cls, session, package_name, version): """ Get release files for a given package name and for a given version. :param session: SQLAlchemy session :type session: :class:`sqlalchemy.Session` :param package_name: package name :type package_name: unicode :param version: version :type version: unicode :return: release files :rtype: generator of :class:`pyshop.models.ReleaseFile` """ return cls.find(session, join=(Release, Package), where=( Package.name == package_name, Release.version == version, )) @classmethod def by_filename(cls, session, release, filename): """ Get a release file for a given release and a given filename. :param session: SQLAlchemy session :type session: :class:`sqlalchemy.Session` :param release: release :type release: :class:`pyshop.models.Release` :param filename: filename of the release file :type filename: unicode :return: release file :rtype: :class:`pyshop.models.ReleaseFile` """ return cls.first(session, where=( ReleaseFile.release_id == release.id, ReleaseFile.filename == filename, ))
import logging from sqlalchemy import Table, Column from sqlalchemy import Unicode, UnicodeText from adhocracy.model import meta log = logging.getLogger(__name__) staticpage_table = Table( 'staticpage', meta.data, Column('key', Unicode(256), primary_key=True), Column('lang', Unicode(7), primary_key=True), Column('title', UnicodeText(), nullable=True), Column('body', UnicodeText()), ) class StaticPageBase(object): private = False nav = u'' description = u'' column_right = u'' css_classes = [] redirect_url = u'' def __init__(self, key, lang,
def test_unicodetext_nchar_mode(self): self._test_setinputsizes(UnicodeText(), u("test"), testing.db.dialect.dbapi.NCLOB, set_nchar_flag=True)
class Ticket(DeclarativeBase): __tablename__ = "ticket" __table_args__ = {"mysql_engine": "InnoDB", "mysql_charset": "utf8"} id = Column(Integer, primary_key=True) # Athena username requestor = Column(Unicode(255), index=True) # Locker name locker = Column(Unicode(255), index=True) # Hostname involved hostname = Column(Unicode(255), index=True) # path path = Column(Unicode(255)) # "open" or "moira" or "dns" or "resolved" state = Column(Unicode(32)) rtid = Column(Integer, index=True, unique=True) # Purpose purpose = Column(UnicodeText()) events = relationship("Event", order_by="Event.timestamp", back_populates="ticket") @staticmethod def create(locker, hostname, path, requestor=None, purpose=""): if requestor is None: requestor = auth.current_user() t = Ticket( requestor=requestor, hostname=hostname, locker=locker, path=path, state="open", purpose=purpose, ) DBSession.add(t) DBSession.flush() t.addEvent(type="request", state="open", target="us") return t def addEvent(self, type, state, by=None, target=None, subject=None, body=None): if by is None: by = auth.current_user() event = Event(ticket=self, type=type, target=target, subject=subject, body=body, by=by) DBSession.add(event) if state != self.state: self.state = state pat = "%s's %s changed the ticket re: %s to %s" else: pat = "%s's %s left the ticket re: %s as %s" try: url = "%s%s" % (tg.request.host_url, tg.url("/queue")) except: # Default to something sane if we're not in the context of a request url = "https://pony.scripts.mit.edu:444/queue" log.zwrite(pat % (by, type, self.hostname, state), instance=self.id, zsig=url) @staticmethod def all(): return Ticket.query.all()
def table_args(): engine_name = urlparse.urlparse(cfg.CONF.database_connection).scheme if engine_name == 'mysql': return { 'mysql_engine': cfg.CONF.mysql_engine, 'mysql_charset': "utf8mb4" } return None # # CUSTOM TYPES # A mysql medium text type. MYSQL_MEDIUM_TEXT = UnicodeText().with_variant(MEDIUMTEXT(), 'mysql') class CommonLength(object): top_large_length = 255 top_middle_length = 100 top_short_length = 50 lower_large_length = 5 lower_middle_length = 3 lower_short_length = 1 name_length = 30 class IdMixin(object): id = Column(Integer, primary_key=True)
class Content(Node): """Content adds some attributes to :class:`Node` that are useful for content objects in a CMS. """ implements(IContent) @classproperty def __mapper_args__(cls): return dict(polymorphic_identity=camel_case_to_name(cls.__name__)) id = Column(Integer, ForeignKey('nodes.id'), primary_key=True) #: Name of the view that should be displayed to the user when #: visiting an URL without a explicit view name appended (String) default_view = Column(String(50)) #: Description of the content object. In default Kotti this is #: used e.g. in the description tag in the HTML, in the search results #: and rendered below the title in most views. (Unicode) description = Column(UnicodeText()) #: Language code (ISO 639) of the content object (Unicode) language = Column(Unicode(10)) #: Owner of the content object (username, Unicode) owner = Column(Unicode(100)) #: Workflow state of the content object (String) state = Column(String(50)) #: Date / time the content was created (DateTime) creation_date = Column(DateTime()) #: Date / time the content was last modified (DateTime) modification_date = Column(DateTime()) #: Shall the content be visible in the navigation? (Boolean) in_navigation = Column(Boolean()) _tags = relation( TagsToContents, backref=backref('item'), order_by=[TagsToContents.position], collection_class=ordering_list("position"), cascade='all, delete-orphan', ) #: Tags assigned to the content object (list of str) tags = association_proxy( '_tags', 'title', creator=TagsToContents._tag_find_or_create, ) #: type_info is a class attribute (:class:`TypeInfo`) type_info = TypeInfo( name=u'Content', title=u'type_info title missing', # BBB add_view=None, addable_to=[], edit_links=[ ViewLink('contents', title=_(u'Contents')), ViewLink('edit', title=_(u'Edit')), ViewLink('share', title=_(u'Share')), ], selectable_default_views=[ ("folder_view", _(u"Folder view")), ], ) def __init__(self, name=None, parent=None, title=u"", annotations=None, default_view=None, description=u"", language=None, owner=None, creation_date=None, modification_date=None, in_navigation=True, tags=None): super(Content, self).__init__(name, parent, title, annotations) self.default_view = default_view self.description = description self.language = language self.owner = owner self.in_navigation = in_navigation # These are set by events if not defined at this point: self.creation_date = creation_date self.modification_date = modification_date self.tags = tags or [] def copy(self, **kwargs): # Same as `Node.copy` with additional tag support. kwargs['tags'] = self.tags return super(Content, self).copy(**kwargs)
class Sketch(AccessControlMixin, LabelMixin, StatusMixin, CommentMixin, BaseModel): """Implements the Sketch model. A Sketch is the collaborative entity in Timesketch. It contains one or more timelines that can be grouped and queried on. """ name = Column(Unicode(255)) description = Column(UnicodeText()) user_id = Column(Integer, ForeignKey('user.id')) timelines = relationship('Timeline', backref='sketch', lazy='select') views = relationship('View', backref='sketch', lazy='select') events = relationship('Event', backref='sketch', lazy='select') stories = relationship('Story', backref='sketch', lazy='select') aggregations = relationship('Aggregation', backref='sketch', lazy='select') analysis = relationship('Analysis', backref='sketch', lazy='select') def __init__(self, name, description, user): """Initialize the Sketch object. Args: name: The name of the sketch description: Description of the sketch user: A user (instance of timesketch.models.user.User) """ super(Sketch, self).__init__() self.name = name self.description = description self.user = user @property def get_named_aggregations(self): """Get named aggregations. Get named aggregations, i.e. only aggregations that have a name. """ return [agg for agg in self.aggregations if agg.name != ''] @property def get_named_views(self): """ Get named views, i.e. only views that has a name. Views without names are used as user state views and should not be visible in the UI. """ views = [ view for view in self.views if view.get_status.status != 'deleted' and view.name != '' ] return views @property def external_url(self): """Get external URL for the sketch. E.g: https://localhost/sketch/42/ Returns: Full URL to the sketch as string. """ url_host = current_app.config.get('EXTERNAL_HOST_URL', 'https://localhost') url_path = url_for('sketch_views.overview', sketch_id=self.id) return url_host + url_path def get_view_urls(self): """Get external URL for all views in the sketch. Returns: Dictionary with url as key and view name as value. """ views = {} for view in self.get_named_views: url_host = current_app.config.get('EXTERNAL_HOST_URL', 'https://localhost') url_path = url_for('sketch_views.explore', sketch_id=self.id, view_id=view.id) url = url_host + url_path views[url] = view.name return views @property def active_timelines(self): """List timelines that are ready for analysis. Returns: List of instances of timesketch.models.sketch.Timeline """ _timelines = [] for timeline in self.timelines: timeline_status = timeline.get_status.status index_status = timeline.searchindex.get_status.status if (timeline_status or index_status) in ['processing', 'fail']: continue _timelines.append(timeline) return _timelines @property def get_search_templates(self): """Get search templates.""" return SearchTemplate.query.all() def get_user_view(self, user): """Get view for user, i.e. view with the state for the user/sketch. Args: user: User (instance of timesketch.models.user.User) Returns: view: Instance of timesketch.models.sketch.View """ view = View.query.filter(View.user == user, View.name == '', View.sketch_id == self.id).order_by( View.created_at.desc()).first() return view
def test_unicodetext(self): self._test_setinputsizes(UnicodeText(), u("test"), testing.db.dialect.dbapi.CLOB)
class Transaction(Base): __tablename__ = 'transaction' #Transaction Types WITHDRAWL = 'WITHDRAWL' DEPOSIT = 'DEPOSIT' #Transaction Status' IN_TRANSIT = 'IN_TRANSIT' COMPLETED = 'COMPLETED' CANCELED = 'CANCELED' transaction_id = Column(Integer, primary_key=True) transaction_type = Column(Unicode(64)) transaction_status = Column(Unicode(64)) unique_id = Column(Unicode(64), nullable=False) time_created = Column(DateTime, nullable=False) time_completed = Column(DateTime, nullable=True) _amount = Column('amount', Numeric(precision=24, scale=14)) _amount_currency = Column('amount_currency', Unicode(3)) _fee = Column('fee', Numeric(precision=24, scale=14)) _fee_currency = Column('fee_currency', Unicode(3)) _transaction_details = Column('transaction_details', UnicodeText(length=2**31)) exchange_id = Column(Integer, ForeignKey('exchange.exchange_id')) # Some Transactions have BTC fees, which reduce our total BTC assets. Every once in a while # we create a "buyback" transaction where we buy back those fees outside our trading system. # We then mark the fee as "bought back" by setting the transaction's fee_buyback_transaction # This one is a bit complex because it is self-referential fee_buyback_transaction_id = Column( Integer, ForeignKey('transaction.transaction_id')) fee_buyback_transaction = relationship("Transaction", remote_side=[transaction_id], backref='fee_buyback_transactions') def __init__(self, transaction_type, transaction_status, amount, exchange, transaction_details, fee=None): self.unique_id = unicode(uuid.uuid4().hex) self.time_created = datetime.utcnow() self.transaction_type = transaction_type self.transaction_status = transaction_status self.amount = amount self.exchange = exchange self.transaction_details = transaction_details if fee: self.fee = fee def __unicode__(self): return u'[TRANSACTION:%s, EXCHANGE:%s, STATUS:%s] Amount:%s (%s) at %s' % ( self.transaction_type, self.exchange.name, self.transaction_status, self.amount, self.fee, self.time_created) def __repr__(self): return self.to_json() def to_json(self): return json.dumps( { 'transaction_id': self.transaction_id, 'transaction_type': self.transaction_type, 'transaction_status': self.transaction_status, 'time_created': unicode(self.time_created), 'unique_id': self.unique_id, 'exchange': self.exchange.name, 'amount': self.amount, 'fee': self.fee, 'transaction_details': self.transaction_details }, ensure_ascii=False) @property def transaction_details(self): return json.loads(self._transaction_details) @transaction_details.setter def transaction_details(self, value): self._transaction_details = json.dumps(value, ensure_ascii=False) @property def amount(self): return Money(self._amount, self._amount_currency) @amount.setter def amount(self, value): self._amount = value.amount self._amount_currency = value.currency @property def fee(self): if self._fee and self._fee_currency: return Money(self._fee, self._fee_currency) else: return None @fee.setter def fee(self, value): self._fee = value.amount self._fee_currency = value.currency @hybrid_property def has_outstanding_btc_fee(self): return self._fee_currency == 'BTC' and self.fee_buyback_transaction_id == None and self.transaction_status != self.CANCELED @has_outstanding_btc_fee.expression def has_outstanding_btc_fee(cls): return and_(cls._fee_currency == 'BTC', cls.fee_buyback_transaction_id == None, cls.transaction_status != cls.CANCELED) def complete(self): if self.transaction_status != self.IN_TRANSIT: raise ValueError("We can only complete an IN_TRANSIT transaction") self.transaction_status = self.COMPLETED self.time_completed = datetime.utcnow() if self.transaction_type == self.DEPOSIT: self.exchange.balance[self.amount.currency] += self.amount elif self.transaction_type == self.WITHDRAWL: self.exchange.balance[self.amount.currency] -= self.amount if self.fee: self.exchange.balance[self.fee.currency] -= self.fee def cancel(self): if self.transaction_status == self.IN_TRANSIT: self.transaction_status = self.CANCELED elif self.transaction_status == self.COMPLETED: self.transaction_status = self.CANCELED if self.transaction_type == self.DEPOSIT: self.exchange.balance[self.amount.currency] -= self.amount elif self.transaction_type == self.WITHDRAWL: self.exchange.balance[self.amount.currency] += self.amount if self.fee: self.exchange.balance[self.fee.currency] += self.fee else: raise ValueError( "We can only cancel an IN_TRANSIT or COMPLETED transaction") def confirmations(self): if self.transaction_details and 'transaction_hash' in self.transaction_details: r = requests.get('https://api.blockcypher.com/v1/btc/main/txs/%s' % self.transaction_details['transaction_hash']) response = r.json() if 'confirmations' in response: return int(response['confirmations']) return None def already_has_tx_hash(self): return (self.transaction_details and 'transaction_hash' in self.transaction_details and self.transaction_details['transaction_hash']) def can_lookup_tx_hash(self): return (self.amount.currency == "BTC" and self.transaction_details and 'deposit_address' in self.transaction_details) def update_tx_hash(self): if not self.already_has_tx_hash() and self.can_lookup_tx_hash(): tx_hash = self.find_on_blockchain() logger.info("Found %s %s on the blockchain: %s" % (self.exchange.name, self.amount, tx_hash)) # can't directly update self.transaction_details because it is a json magic field tx_details = self.transaction_details tx_details['transaction_hash'] = tx_hash self.transaction_details = tx_details def find_on_blockchain(self): deposit_address = self.transaction_details['deposit_address'] satoshi_amount = int(self.amount * 10**8) r = requests.get( 'https://api.blockcypher.com/v1/btc/main/addrs/%s/full' % deposit_address) response = r.json() transactions = response['txs'] for transaction in transactions: for output in transaction['outputs']: if deposit_address in output['addresses'] and output[ 'value'] == satoshi_amount: return transaction['hash'] return None # a transaction is stuck if it has been in transit for more than 3 hours def is_stuck(self): return self.amount.currency == "BTC" and \ self.transaction_status == self.IN_TRANSIT and \ Delorean(self.time_created, "UTC") < Delorean().last_hour(3) @property def position(self): from gryphon.lib.models.exchange import Position position = Position() if self.transaction_type == self.DEPOSIT: position[self.amount.currency] += self.amount elif self.transaction_type == self.WITHDRAWL: position[self.amount.currency] -= self.amount if self.fee: position[self.fee.currency] -= self.fee return position @hybrid_property def magic_time_completed(self): """ Regular property you can call on a loaded trade in python. Returns a USD Money object """ if self.time_completed: return self.time_completed else: return self.time_created @magic_time_completed.expression def magic_time_completed(cls): """ SQL Expression which lets us calculate and use USD prices in SQL. Must have Order joined in queries where it is used. Ex: db.query(func.sum(Trade.price_in_usd)).join(Order).scalar() This gives decimal results, since we don't have our Money objects in SQL """ return func.IF(cls.time_completed, cls.time_completed, cls.time_created)
class Reference(Base): __tablename__ = '4_references' id = Column(Integer, primary_key=True) citation_id = Column(Integer, ForeignKey('3_citations.id'), nullable=False) reference_type_id = Column(Integer, ForeignKey('1_reference_types.id'), nullable=False) national_context_id = Column(Integer, ForeignKey('1_national_context.id'), nullable=False) date = Column(DateTime()) transcription = Column(UnicodeText()) image_url = Column(String(500)) referents = relationship('Referent', backref='reference', lazy=True, cascade="delete") groups = relationship('Group', backref='reference', lazy=True, cascade="delete") def last_edit(self): """ Note: self.edits is possible because of ReferenceEdit() """ edits: list[tuple] = sorted([(e.timestamp, e) for e in self.edits], key=operator.itemgetter(0), reverse=True) log.debug(f'edits, ```{edits}```') if edits: return_edits = edits[0][1] else: return_edits = None log.debug(f'return_edits, ``{return_edits}``') log.debug(f'type(return_edits), ``{type(return_edits)}``') return return_edits def display_date(self): if self.date: return self.date.strftime('%Y %B %d') else: return '' def display_location_info(self): # session = make_session() locations_lst = [] rfrnc_locations = self.locations log.debug(f'rfrnc_locations, ```{rfrnc_locations}```') for rfrnc_location in rfrnc_locations: location_name = rfrnc_location.location.name location_type = None try: location_type = rfrnc_location.location_type.name except: log.exception( f'problem parsing rfrnc_location, ```{rfrnc_location.__dict__}```; traceback follows; processing will continue' ) loc_dct = { 'location_name': location_name, 'location_type': location_type } log.debug(f'loc_dct, ```{loc_dct}```') locations_lst.append(loc_dct) return locations_lst def dictify(self): if self.date: datetime_obj = cast(datetime.datetime, self.date) isodate = datetime.date.isoformat(datetime_obj) else: isodate = '' jsn_referents = [] for rfrnt in self.referents: jsn_referents.append({ 'id': rfrnt.id, 'age': rfrnt.age, 'sex': rfrnt.sex }) last_edit = self.last_edit() if last_edit: last_edit_str: str = last_edit.timestamp.strftime('%Y-%m-%d') else: last_edit_str = '' data = { 'id': self.id, 'citation_id': self.citation_id, 'reference_type_id': self.reference_type_id, 'reference_type_name': self.reference_type. name, # NB: this appears to be an sqlalchemy convention -- that if there is a ForeignKey, I can just go ahead and refernce the property name. 'national_context_id': self.national_context_id, 'date': isodate, 'transcription': self.transcription, 'referents': jsn_referents, 'last_edit': last_edit_str, 'location_info': self.display_location_info() } return data def __repr__(self): return '<Reference {0}>'.format(self.id)
from datetime import datetime import logging from sqlalchemy import Table, Column, ForeignKey from sqlalchemy import Integer, UnicodeText, DateTime import meta log = logging.getLogger(__name__) revision_table = Table( 'revision', meta.data, Column('id', Integer, primary_key=True), Column('create_time', DateTime, default=datetime.utcnow), Column('text', UnicodeText(), nullable=False), Column('sentiment', Integer, default=0), Column('user_id', Integer, ForeignKey('user.id'), nullable=False), Column('comment_id', Integer, ForeignKey('comment.id'), nullable=False), ) class Revision(object): def __init__(self, comment, user, text): self.comment = comment self.user = user self.text = text @property def is_earliest(self): return self == min(self.comment.revisions, key=lambda r: r.create_time)
from datetime import datetime from sqlalchemy import MetaData, Column, Table from sqlalchemy import DateTime, Integer, Unicode, UnicodeText meta = MetaData() requestlog_table = Table( 'requestlog', meta, Column('id', Integer, primary_key=True), Column('access_time', DateTime, default=datetime.utcnow), Column('ip_address', Unicode(255), nullable=True), Column('request_url', UnicodeText()), Column('cookies', UnicodeText(), nullable=True), Column('user_agent', UnicodeText(), nullable=True), ) def upgrade(migrate_engine): meta.bind = migrate_engine requestlog_table.create() def downgrade(migrate_engine): raise NotImplementedError()
class SetInputSizesTest(fixtures.TestBase): __only_on__ = "oracle+cx_oracle" __backend__ = True @testing.combinations( (SmallInteger, 25, int, False), (Integer, 25, int, False), (Numeric(10, 8), decimal.Decimal("25.34534"), None, False), (Float(15), 25.34534, None, False), (oracle.BINARY_DOUBLE, 25.34534, "NATIVE_FLOAT", False), (oracle.BINARY_FLOAT, 25.34534, "NATIVE_FLOAT", False), (oracle.DOUBLE_PRECISION, 25.34534, None, False), (Unicode(30), u("test"), "NCHAR", True), (UnicodeText(), u("test"), "NCLOB", True), (Unicode(30), u("test"), None, False), (UnicodeText(), u("test"), "CLOB", False), (String(30), "test", None, False), (CHAR(30), "test", "FIXED_CHAR", False), (NCHAR(30), u("test"), "FIXED_NCHAR", False), (oracle.LONG(), "test", None, False), ) @testing.provide_metadata def test_setinputsizes(self, datatype, value, sis_value_text, set_nchar_flag): if isinstance(sis_value_text, str): sis_value = getattr(testing.db.dialect.dbapi, sis_value_text) else: sis_value = sis_value_text class TestTypeDec(TypeDecorator): impl = NullType() def load_dialect_impl(self, dialect): if dialect.name == "oracle": return dialect.type_descriptor(datatype) else: return self.impl m = self.metadata # Oracle can have only one column of type LONG so we make three # tables rather than one table w/ three columns t1 = Table("t1", m, Column("foo", datatype)) t2 = Table("t2", m, Column("foo", NullType().with_variant(datatype, "oracle"))) t3 = Table("t3", m, Column("foo", TestTypeDec())) m.create_all() class CursorWrapper(object): # cx_oracle cursor can't be modified so we have to # invent a whole wrapping scheme def __init__(self, connection_fairy): self.cursor = connection_fairy.connection.cursor() self.mock = mock.Mock() connection_fairy.info["mock"] = self.mock def setinputsizes(self, *arg, **kw): self.mock.setinputsizes(*arg, **kw) self.cursor.setinputsizes(*arg, **kw) def __getattr__(self, key): return getattr(self.cursor, key) if set_nchar_flag: engine = testing_engine(options={"use_nchar_for_unicode": True}) else: engine = testing.db with engine.connect() as conn: connection_fairy = conn.connection for tab in [t1, t2, t3]: with mock.patch.object( connection_fairy, "cursor", lambda: CursorWrapper(connection_fairy), ): conn.execute(tab.insert(), {"foo": value}) if sis_value: eq_( conn.info["mock"].mock_calls, [mock.call.setinputsizes(foo=sis_value)], ) else: eq_( conn.info["mock"].mock_calls, [mock.call.setinputsizes()], ) def test_event_no_native_float(self): def _remove_type(inputsizes, cursor, statement, parameters, context): for param, dbapitype in list(inputsizes.items()): if dbapitype is testing.db.dialect.dbapi.NATIVE_FLOAT: del inputsizes[param] event.listen(testing.db, "do_setinputsizes", _remove_type) try: self.test_setinputsizes(oracle.BINARY_FLOAT, 25.34534, None, False) finally: event.remove(testing.db, "do_setinputsizes", _remove_type)
from sqlalchemy import Column, Table, MetaData, UnicodeText, Boolean, ForeignKey, DateTime from sqlalchemy.orm import mapper, relationship from ckan.model.types import make_uuid from ckan.model.package import Package, package_table, PACKAGE_NAME_MAX_LENGTH from datetime import datetime metadata = MetaData() packageendpoint_table = Table('package_endpoint', metadata, Column('package_name', UnicodeText(PACKAGE_NAME_MAX_LENGTH), ForeignKey(package_table.c.name), primary_key=True), Column('endpoint_id', UnicodeText, ForeignKey('sparql_endpoint.id'), primary_key=True) ) sparqlendpoint_table = Table('sparql_endpoint', metadata, Column('id', UnicodeText, primary_key=True, default=make_uuid), Column('name', UnicodeText), Column('sparqlurl', UnicodeText), Column('sparulurl', UnicodeText), Column('graph', UnicodeText), Column('storetype', UnicodeText), Column('username', UnicodeText, default=None), Column('passwd', UnicodeText, default=None), Column('isauthrequired', Boolean, unique=False, default=False), Column('isglobal', Boolean, unique=False, default=False), Column('isdataallowed', Boolean, unique=False, default=False), Column('isenabled', Boolean, unique=False, default=True) ) rdfuploadinglog_table = Table('rdf_uploading_log', metadata, Column('id', UnicodeText, primary_key=True, default=make_uuid),
from sqlalchemy.orm import reconstructor from adhocracy import config import adhocracy.model from adhocracy.model import meta log = logging.getLogger(__name__) instance_table = Table( 'instance', meta.data, Column('id', Integer, primary_key=True), Column('key', Unicode(63), nullable=False, unique=True), Column('label', Unicode(255), nullable=False), Column('description', UnicodeText(), nullable=True), Column('required_majority', Float, nullable=False), Column('activation_delay', Integer, nullable=False), Column('create_time', DateTime, default=func.now()), Column('access_time', DateTime, default=func.now(), onupdate=func.now()), Column('delete_time', DateTime, nullable=True), Column('creator_id', Integer, ForeignKey('user.id'), nullable=False), Column('default_group_id', Integer, ForeignKey('group.id'), nullable=True), Column('allow_adopt', Boolean, default=False), Column('allow_delegate', Boolean, default=True), Column('allow_proposal_pagination', Boolean, default=True), Column('allow_propose', Boolean, default=True), Column('allow_propose_changes', Boolean, default=True),
class Section(Base): # http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/ # http://www.sitepoint.com/hierarchical-data-database-2/ __tablename__ = 'section' id = Column(Integer(), primary_key=True, autoincrement=True) left = Column(Integer(), default=0) right = Column(Integer(), default=0) name = Column(Unicode(50)) description = Column(UnicodeText(), nullable=True) theme_id = Column(String(50), ForeignKey('theme.id'), nullable=False, server_default='default') theme = relationship("Theme") params = relationship("SectionParam", cascade="all, delete-orphan") instances = relationship("WidgetInstance", cascade="all, delete-orphan") _leafs = None _childrens = None _level = None _max_level = None @classmethod def add(cls, parent_id, name, description=None): s = cls(name=name, description=description) parent = session.query(cls).get(parent_id) if parent: s.left = int(parent.right) s.right = int(parent.right) + 1 session.query(cls).filter('left > :sright').\ params({'sright':parent.right}).update({cls.left: cls.left + 2}, synchronize_session='fetch') session.query(cls).filter('right >= :sright').\ params({'sright':parent.right}).update({cls.right: cls.right + 2}, synchronize_session='fetch') session.add(s) session.commit() session.flush() else: logger.info("Section Add: Parent not found") return s @classmethod def delete(cls, id): s = session.query(cls).get(id) if s: # Move all childs to parent q = session.query(cls).filter('left > :sleft AND right < :sright').\ params({'sleft':s.left, 'sright':s.right}).update({cls.left: cls.left - 1, cls.right: cls.right - 1 }, synchronize_session='fetch') session.query(cls).filter('left > :sright').\ params({'sright':s.right}).update({cls.left: cls.left - 2}, synchronize_session='fetch') session.query(cls).filter('right > :sright').\ params({'sright':s.right}).update({cls.right: cls.right - 2}, synchronize_session='fetch') session.delete(s) session.commit() session.flush() else: logger.info("Section Remove: Section not found") return s @classmethod def getAll(cls): s = session.query(cls).order_by('left').all() return s @classmethod def get(cls, id): is_id = True try: id=int(id) except ValueError: is_id = False except TypeError: is_id = False if not is_id: print("NOT AN ID") s_list = session.query(cls).\ options(joinedload('theme')).\ all() for a_s in s_list: print(a_s.name) s = a_s if id.strip() == a_s.name.strip(): return s # if not found, return one page... print(s) return s else: s = session.query(cls).\ options(joinedload('theme')).\ get(id) return s @classmethod def getInstances(cls, id): s = session.query(cls).\ options(joinedload('instances')).\ get(id) return s.instances @classmethod def update(cls, id, name, description=None): s = session.query(cls).get(id) s.name = name s.description = description session.add(s) session.commit() session.flush() return s @classmethod def getParamsDict(cls, id): s = session.query(cls).get(id) # Combine Params for section theme params = Theme.getParamsDict(s.theme.id, ["section", "widget"]) # Override with user params for p in s.params: params[p.key] = p.value session.flush() return params @classmethod def getTree(cls): data = cls.getAll() _current_path = [] top_node = None if data: for obj in data: obj._childrens = [] obj._leafs = 0 if top_node == None: top_node = obj obj._level = 0 obj._max_level = 0 _current_path.append(obj) else: while (obj.left > _current_path[-1].right): # Level down top = _current_path.pop() _current_path[-1]._leafs = _current_path[-1]._leafs + top._leafs obj._level = len(_current_path) if obj._level > top_node._max_level: # Save the number of levels in the root node top_node._max_level = obj._level _current_path[-1]._childrens.append(obj) if not obj._is_leaf(): _current_path.append(obj) # Level up else: _current_path[-1]._leafs = _current_path[-1]._leafs + 1 while (len(_current_path) > 1): # Level down top = _current_path.pop() _current_path[-1]._leafs = _current_path[-1]._leafs + top._leafs return top_node def _is_leaf(self): # If right = left + 1 then it is a leaf return ((self.left + 1) == self.right) is_leaf = property(_is_leaf) def _get_leafs(self): return self._leafs leafs = property(_get_leafs) def _get_childrens(self): return self._childrens childrens = property(_get_childrens) def _get_level(self): return self._level level = property(_get_level) def _get_max_level(self): return self._max_level max_level = property(_get_max_level)
# http://sqlalchemy-utils.readthedocs.org/en/latest/data_types.html#module-sqlalchemy_utils.types.phone_number # 'phone': PhoneNumberType(country_code='UA'), # (country_code='UA') 'phone': String(50), # (country_code='UA') # http://sqlalchemy-utils.readthedocs.org/en/latest/data_types.html#module-sqlalchemy_utils.types.url # read also https://github.com/gruns/furl # 'link': URLType, # user = User(website=u'www.example.com'), 'link': String(100), # user = User(website=u'www.example.com'), 'email': String(100), 'name': String(200), 'subtitle': String(1000), 'string_30': String(30), 'string_500': String(500), 'string_1000': String(1000), 'short_name': String(50), 'title': String(100), 'short_text': String(120), 'keywords': String(1000), 'credentials': String(1000), 'text': UnicodeText(length=65535), 'gender': String(6), 'language': String(3), 'iso': String(2), 'iso3': String(3), 'numcode': String(6), 'phonecode': String(5), 'url': String(1000), # URLType, 'binary': Binary, 'json': JSON }
class Device(Base): """Device""" __tablename__ = '%s_device' % _db_prefix __table_args__ = {'mysql_engine': 'InnoDB'} id = Column(Integer, primary_key=True) name = Column(Unicode(30), nullable=False) description = Column(UnicodeText()) reference = Column(Unicode(30)) address = Column(Unicode(255), nullable=True) device_type_id = Column(Unicode(80), nullable=False, index=True) client_id = Column(Unicode(80), nullable=False) client_version = Column(Unicode(32), nullable=False) params = relationship("DeviceParam", backref=__tablename__, cascade="all", passive_deletes=True) commands = relationship("Command", backref=__tablename__, cascade="all", passive_deletes=True) sensors = relationship("Sensor", backref=__tablename__, cascade="all", passive_deletes=True) xpl_commands = relationship("XplCommand", backref=__tablename__, cascade="all", passive_deletes=True) xpl_stats = relationship("XplStat", backref=__tablename__, cascade="all", passive_deletes=True) def __init__(self, name, reference, device_type_id, client_id, client_version, description=None): """Class constructor @param name : short name of the device @param address : device address (like 'A3' for x10, or '01.123456789ABC' for 1wire) @param reference : internal reference of the device (like AM12 for a X10 device) @param client_id : what plugin controls this device @param device_type_id : 'link to the device type (x10.Switch, x10.Dimmer, Computer.WOL...) @param description : extended description, optional """ self.name = ucode(name) self.reference = ucode(reference) self.device_type_id = ucode(device_type_id) self.client_id = ucode(client_id) self.client_version = ucode(client_version) self.description = ucode(description) def __repr__(self): """Return an internal representation of the class""" return "<Device(id=%s, name='%s', desc='%s', ref='%s', type='%s', client='%s', client_version='%s', commands=%s, sensors=%s, xplcommands=%s, xplstats=%s)>"\ % (self.id, self.name, self.description, self.reference,\ self.device_type_id, self.client_id, self.client_version, \ self.commands, \ self.sensors, self.xpl_commands, self.xpl_stats) @staticmethod def get_tablename(): """Return the table name associated to the class""" return Device.__tablename__
class Person(Base, SerializerMixin): __tablename__ = 'person' __table_args__ = {'extend_existing': True} _id = Column('_id', Unicode(32),primary_key=True, autoincrement=False) index = Column(Integer, nullable=False) guid = Column(Unicode(64), unique=True, nullable=False) has_died = Column(Boolean, default=False) balance = Column(Unicode(255), nullable=True) picture = Column(Unicode(255), nullable=True) age = Column(Integer, nullable=True) eyeColor = Column(Unicode(32), nullable=True) name = Column(Unicode(255), nullable=True) gender = Column(Unicode(10), nullable=True) company_id = Column(Integer,nullable=True,) email = Column(Unicode(255), nullable=True) phone = Column(Unicode(255), nullable=True) address = Column(Unicode(255), nullable=True) about = Column(UnicodeText(500), nullable=True) registered = Column(DateTime, nullable=True) _tags = Column('tags', Unicode(255), nullable=True, default='[]', server_default='[]') _friends = Column('friends',Unicode(500), nullable=True, default='[]', server_default='[]') greeting = Column(Unicode(255), nullable=True) _favourite_food = Column('favourite_food',Unicode(255), nullable=True, default='[]', server_default='[]') def __init__(self, _id=None, index=None, guid=None, has_died=False, balance='0', picture=None, age=None, eyeColor=None, name=None, gender=None, company_id=None, email=None, phone=None, address=None, about=None, registered=None, tags =None, friends=None, greeting=None, favouriteFood=None ): self._id = str(_id) self.index = index self.name = name self.guid = guid self.gender = gender self.has_died = has_died self.balance = balance self.picture = picture self.age = age self.eyeColor = eyeColor self.company_id = company_id self.email = email self.phone = phone self.address = address self.about = about self.registered = datetime.strptime(registered, "%Y-%m-%dT%H:%M:%S %z") if registered is not None else None self.tags = tags self.friends = friends self.greeting = greeting self.favourite_food = favouriteFood @hybrid_property def tags(self): return json.loads(self._tags) @tags.setter def tags(self, tags): self._tags = json.dumps(tags) @hybrid_property def friends(self): return json.loads(self._friends) @friends.setter def friends(self, friends): self._friends = json.dumps(friends) @hybrid_property def favourite_food(self): return json.loads(self._favourite_food) @favourite_food.setter def favourite_food(self, favourite_food): self._favourite_food = json.dumps(favourite_food) def _veges(self): return [food for food in self.favourite_food if DIET_MAP.get( food,'') == 'veg'] def _fruits(self): return [food for food in self.favourite_food if DIET_MAP.get( food,'') == 'fruit'] def get_diet(self): return { 'vegetables': self._veges(), 'fruits': self._fruits(), 'username':self.name, 'age': self.age } def to_dict_full_info(self): return self.to_dict(rules=('-_tags', '-_friends', '-_favourite_food', 'tags', 'friends', 'favourite_food')) def get_common_friends_indexes(self, other_person): self_friends = set(map(lambda f: f.get('index', None),self.friends)) other_friends = set(map(lambda f: f.get('index', None),other_person.friends)) return list(self_friends.intersection(other_friends))
class View(AccessControlMixin, LabelMixin, StatusMixin, CommentMixin, BaseModel): """Implements the View model.""" name = Column(Unicode(255)) query_string = Column(UnicodeText()) query_filter = Column(UnicodeText()) query_dsl = Column(UnicodeText()) user_id = Column(Integer, ForeignKey('user.id')) sketch_id = Column(Integer, ForeignKey('sketch.id')) searchtemplate_id = Column(Integer, ForeignKey('searchtemplate.id')) aggregations = relationship('Aggregation', backref='view', lazy='select') def __init__(self, name, sketch, user, searchtemplate=None, query_string=None, query_filter=None, query_dsl=None): """Initialize the View object. Args: name: The name of the timeline sketch: A sketch (instance of timesketch.models.sketch.Sketch) user: A user (instance of timesketch.models.user.User) searchtemplate: Instance of timesketch.models.sketch.SearchTemplate query_string: The query string query_filter: The filter to apply (JSON format as string) query_dsl: A query DSL document (JSON format as string) """ super(View, self).__init__() self.name = name self.sketch = sketch self.user = user self.searchtemplate = searchtemplate self.query_string = query_string self.query_filter = query_filter self.query_dsl = query_dsl def validate_filter(self, query_filter=None): """Validate the Query Filter. Make sure that we have all expected attributes in the query filter json string. The filter dictionary evolves over time and this function is used to update all filters. Args: query_filter: The query filter (JSON format or dictionary) Returns: query_filter: Query filter dictionary serialized to JSON """ DEFAULT_FROM = 0 DEFAULT_SIZE = 40 # Number of resulting documents to return DEFAULT_LIMIT = DEFAULT_SIZE # Number of resulting documents to return DEFAULT_VALUES = { 'time_start': None, 'time_end': None, 'from': DEFAULT_FROM, 'size': DEFAULT_SIZE, 'terminate_after': DEFAULT_LIMIT, 'indices': [], 'exclude': [], 'order': 'asc' } # If not provided, get the saved filter from the view if not query_filter: query_filter = self.query_filter # Make sure we have the filter as a dictionary if not isinstance(query_filter, dict): filter_dict = json.loads(query_filter) else: filter_dict = query_filter # Get all missing attributes and set them to their default value missing_attributes = list( set(DEFAULT_VALUES.keys()) - set(filter_dict.keys())) for key in missing_attributes: filter_dict[key] = DEFAULT_VALUES[key] return json.dumps(filter_dict, ensure_ascii=False)
def utext(): # hack if os.getenv("DATABASE_CONNECT").startswith("sqlite:"): return UnicodeText() else: return UnicodeText(collation="utf8_bin")
def setup_mappers(meta=None, engine=None): """ Setup SQLAlchemy mappers for the aggregate events database :param meta: SQLAlchemy Metadata :type meta: SQLAlchemy Metadata :returns: SQLAlchemy meta """ global _MAPPED # TODO if _MAPPED: #clear_mappers() return meta _MAPPED = True if meta is None: meta = MetaData() if engine: meta.bind = engine users_tbl = Table( 'users', meta, Column( 'id', GUID(), index=True, primary_key=True, ), Column('username', Unicode(32), index=True, unique=True), Column('first_name', Unicode(1024)), Column('last_name', Unicode(1024)), Column('email', Unicode(1024)), Column('password', PasswordType(schemes=[ 'pbkdf2_sha512', ]), nullable=True), Column('created_on', DateTime, default=datetime.datetime.now), Column('updated_on', DateTime, onupdate=datetime.datetime.now), ) mapper( User, users_tbl, properties={ # 'passphrase': synonym("password") }) commands_tbl = Table( 'commands', meta, Column( 'id', GUID(), index=True, primary_key=True, ), Column('expire_on', DateTime, nullable=False), Column('command_id', Unicode(36), default=lambda: str(uuid4()), unique=True), Column('command_type', Unicode(1024)), Column('command_date', UnicodeText), Column('identity', Unicode(1024)), Column('created_on', DateTime, default=datetime.datetime.now), Column('updated_on', DateTime, onupdate=datetime.datetime.now), ) mapper(Command, commands_tbl, properties={}) queues_tbl = Table( 'queues', meta, Column('id', GUID(), primary_key=True, nullable=False), Column('type', Unicode(length=255), index=True, unique=True), Column('uri', UnicodeText()), Column('label', UnicodeText()), Column('date', DateTime(), index=True, onupdate=datetime.datetime.now), Column('host', UnicodeText()), # default=$(hostname) Column('user', UnicodeText()), # default=$(whoami) Column('created_on', DateTime, default=datetime.datetime.now), Column('updated_on', DateTime, onupdate=datetime.datetime.now), ) mapper(TaskQueue, queues_tbl) tasksources_tbl = Table( 'tasksources', meta, Column('id', GUID(), primary_key=True, nullable=False), Column('queue_id', GUID(), ForeignKey(queues_tbl.c.id), index=True), Column('type', Unicode(length=255), index=True), # taskqueue.type # TODO Column('url', UnicodeText()), Column('label', UnicodeText()), Column('date', DateTime(), index=True, onupdate=datetime.datetime.now), Column('host', UnicodeText()), # default=$(hostname) Column('user', UnicodeText()), # default=$(whoami) Column('created_on', DateTime, default=datetime.datetime.now), Column('updated_on', DateTime, onupdate=datetime.datetime.now), ) mapper(TaskSource, tasksources_tbl, properties={'queue': relation(TaskQueue, backref='sources')}) tasks_tbl = Table( 'tasks', meta, Column('id', GUID(), primary_key=True, nullable=False), Column('source_id', GUID(), ForeignKey(tasksources_tbl.c.id), index=True), Column('args', MutationDict.as_mutable(JSONEncodedDict)), Column('label', UnicodeText()), Column('state', Unicode(), index=True, nullable=True), # TODO: ENUM: celery Column('statemsg', Unicode()), Column( 'date', DateTime(), index=True, onupdate=datetime.datetime.now, ), Column('created_on', DateTime, default=datetime.datetime.now), Column('updated_on', DateTime, onupdate=datetime.datetime.now), ) mapper(Task, tasks_tbl, properties={ 'source': relation(TaskSource, backref='tasks'), }) places_tbl = Table( 'places', meta, Column('id', GUID(), primary_key=True, nullable=False), Column('url', UnicodeText(), index=True), Column('scheme', Unicode()), Column('netloc', UnicodeText(), index=True), Column( 'port', Integer(), ), Column('path', UnicodeText(), index=True), Column('query', UnicodeText(), index=True), Column('fragment', UnicodeText()), Column('eventcount', Integer()), Column('meta', MutationDict.as_mutable(JSONEncodedDict)), Column('created_on', DateTime, default=datetime.datetime.now), Column('updated_on', DateTime, onupdate=datetime.datetime.now), ) mapper(Place, places_tbl) # TODO: tags events_tbl = Table( 'events', meta, Column('id', GUID(), primary_key=True), # TODO, nullable=False), Column('source', Unicode(), index=True), Column('date', DateTime(), index=True), Column('url', UnicodeText()), Column('title', UnicodeText()), Column('meta', MutationDict.as_mutable(JSONEncodedDict)), Column('host', UnicodeText()), Column('user', UnicodeText()), Column('place_id', GUID(), ForeignKey(places_tbl.c.id), nullable=True), Column('source_id', GUID(), ForeignKey(tasksources_tbl.c.id), nullable=False), Column('task_id', GUID(), ForeignKey(tasks_tbl.c.id), nullable=False), Column('created_on', DateTime, default=datetime.datetime.now), Column('updated_on', DateTime, onupdate=datetime.datetime.now), # TODO: sync # UniqueConstraint('date','url','task_id', # name='uix_event_date_url_taskid'), # breaks w/ webkit history on date !? # UniqueConstraint('source','date','url', 'task_id', # name='uix_event_source_task_id'), ) mapper( Event, events_tbl, properties={ 'task': relation(Task, backref='events'), 'place': relation(Place, backref='events'), #'queue': relation(TaskQueue, backref='events'), }) report_types_tbl = Table( 'report_types', meta, Column('id', GUID(), primary_key=True, nullable=False), Column('label', Unicode(), index=True), Column('data', MutationDict.as_mutable(JSONEncodedDict)), Column('created_on', DateTime, default=datetime.datetime.now), Column('updated_on', DateTime, onupdate=datetime.datetime.now), ) mapper(ReportType, report_types_tbl) reports_tbl = Table( 'reports', meta, Column('id', GUID(), primary_key=True, nullable=False), Column('report_type_id', GUID(), ForeignKey(report_types_tbl.c.id), nullable=False), Column('title', Unicode(), nullable=True), Column('data', MutationDict.as_mutable(JSONEncodedDict)), Column('created_on', DateTime, default=datetime.datetime.now), Column('updated_on', DateTime, onupdate=datetime.datetime.now), ) mapper(Report, reports_tbl, properties={ 'report_type': relation(ReportType, backref='reports'), }) return meta