From a8ae9a29c1434f10e22262f3c66637977fe3b713 Mon Sep 17 00:00:00 2001 From: Elrond Date: Thu, 15 Dec 2011 21:15:21 +0100 Subject: Move sql models into db/sql/ So we can play with the sql models, let's put them in a proper place. --- mediagoblin/db/sql/models.py | 95 ++++++++++++++++++++++++++++++++++++++++++++ 1 file changed, 95 insertions(+) create mode 100644 mediagoblin/db/sql/models.py (limited to 'mediagoblin/db/sql/models.py') diff --git a/mediagoblin/db/sql/models.py b/mediagoblin/db/sql/models.py new file mode 100644 index 00000000..31ebfbf4 --- /dev/null +++ b/mediagoblin/db/sql/models.py @@ -0,0 +1,95 @@ +import datetime + +from sqlalchemy.ext.declarative import declarative_base +from sqlalchemy import ( + Column, Integer, Unicode, UnicodeText, DateTime, Boolean, ForeignKey, + UniqueConstraint) + + +Base = declarative_base() + + +class User(Base): + __tablename__ = "users" + + id = Column(Integer, primary_key=True) + username = Column(Unicode, nullable=False, unique=True) + email = Column(Unicode, nullable=False) + created = Column(DateTime, nullable=False, default=datetime.datetime.now) + pw_hash = Column(Unicode, nullable=False) + email_verified = Column(Boolean) + status = Column(Unicode, default="needs_email_verification", nullable=False) + verification_key = Column(Unicode) + is_admin = Column(Boolean, default=False, nullable=False) + url = Column(Unicode) + bio = Column(UnicodeText) # ?? + bio_html = Column(UnicodeText) # ?? + fp_verification_key = Column(Unicode) + fp_verification_expire = Column(DateTime) + + ## TODO + # plugin data would be in a separate model + + +class MediaEntry(Base): + __tablename__ = "media_entries" + + id = Column(Integer, primary_key=True) + uploader = Column(Integer, ForeignKey('users.id'), nullable=False) + slug = Column(Unicode, nullable=False) + created = Column(DateTime, nullable=False, default=datetime.datetime.now) + description = Column(UnicodeText) # ?? + description_html = Column(UnicodeText) # ?? + media_type = Column(Unicode, nullable=False) + + fail_error = Column(Unicode) + fail_metadata = Column(UnicodeText) + + queued_media_file = Column(Unicode) + + queued_task_id = Column(Unicode) + + __table_args__ = ( + UniqueConstraint('uploader', 'slug'), + {}) + + ## TODO + # media_files + # media_data + # attachment_files + # fail_error + + +class Tag(Base): + __tablename__ = "tags" + + id = Column(Integer, primary_key=True) + slug = Column(Unicode, nullable=False, unique=True) + + +class MediaTag(Base): + __tablename__ = "media_tags" + + id = Column(Integer, primary_key=True) + tag = Column(Integer, ForeignKey('tags.id'), nullable=False) + name = Column(Unicode) + media_entry = Column( + Integer, ForeignKey('media_entries.id'), + nullable=False) + # created = Column(DateTime, nullable=False, default=datetime.datetime.now) + + __table_args__ = ( + UniqueConstraint('tag', 'media_entry'), + {}) + + +class MediaComment(Base): + __tablename__ = "media_comments" + + id = Column(Integer, primary_key=True) + media_entry = Column( + Integer, ForeignKey('media_entries.id'), nullable=False) + author = Column(Integer, ForeignKey('users.id'), nullable=False) + created = Column(DateTime, nullable=False, default=datetime.datetime.now) + content = Column(UnicodeText, nullable=False) + content_html = Column(UnicodeText) -- cgit v1.2.3 From e365f980ac21a403a50f61ae687d7dc04760f8bb Mon Sep 17 00:00:00 2001 From: Elrond Date: Thu, 15 Dec 2011 22:11:49 +0100 Subject: SQL: Some toys and little fix Run bin/python mediagoblin/db/sql/models.py and watch the create tables on a memory sqlite db. Also unicode strings need unicode defauls. Warning by sqlalchemy. --- mediagoblin/db/sql/models.py | 13 ++++++++++++- 1 file changed, 12 insertions(+), 1 deletion(-) (limited to 'mediagoblin/db/sql/models.py') diff --git a/mediagoblin/db/sql/models.py b/mediagoblin/db/sql/models.py index 31ebfbf4..a38be1cc 100644 --- a/mediagoblin/db/sql/models.py +++ b/mediagoblin/db/sql/models.py @@ -18,7 +18,7 @@ class User(Base): created = Column(DateTime, nullable=False, default=datetime.datetime.now) pw_hash = Column(Unicode, nullable=False) email_verified = Column(Boolean) - status = Column(Unicode, default="needs_email_verification", nullable=False) + status = Column(Unicode, default=u"needs_email_verification", nullable=False) verification_key = Column(Unicode) is_admin = Column(Boolean, default=False, nullable=False) url = Column(Unicode) @@ -93,3 +93,14 @@ class MediaComment(Base): created = Column(DateTime, nullable=False, default=datetime.datetime.now) content = Column(UnicodeText, nullable=False) content_html = Column(UnicodeText) + + +def show_table_init(): + from sqlalchemy import create_engine + engine = create_engine('sqlite:///:memory:', echo=True) + + Base.metadata.create_all(engine) + + +if __name__ == '__main__': + show_table_init() -- cgit v1.2.3 From 7c2c56a5ff0cb229cd3a64451368bf1e72646bc5 Mon Sep 17 00:00:00 2001 From: Elrond Date: Sat, 17 Dec 2011 17:34:55 +0100 Subject: Little sql model update - Add title to the MediaEntry - Rename fp_verification_expire to fp_token_expire to follow the mongo model. --- mediagoblin/db/sql/models.py | 3 ++- 1 file changed, 2 insertions(+), 1 deletion(-) (limited to 'mediagoblin/db/sql/models.py') diff --git a/mediagoblin/db/sql/models.py b/mediagoblin/db/sql/models.py index a38be1cc..7723a753 100644 --- a/mediagoblin/db/sql/models.py +++ b/mediagoblin/db/sql/models.py @@ -25,7 +25,7 @@ class User(Base): bio = Column(UnicodeText) # ?? bio_html = Column(UnicodeText) # ?? fp_verification_key = Column(Unicode) - fp_verification_expire = Column(DateTime) + fp_token_expire = Column(DateTime) ## TODO # plugin data would be in a separate model @@ -36,6 +36,7 @@ class MediaEntry(Base): id = Column(Integer, primary_key=True) uploader = Column(Integer, ForeignKey('users.id'), nullable=False) + title = Column(Unicode, nullable=False) slug = Column(Unicode, nullable=False) created = Column(DateTime, nullable=False, default=datetime.datetime.now) description = Column(UnicodeText) # ?? -- cgit v1.2.3 From 7b194a79f0ad789309b9c34340f19c5a962b0915 Mon Sep 17 00:00:00 2001 From: Elrond Date: Sun, 18 Dec 2011 17:02:27 +0100 Subject: SQL: mongokit like interface In trying to ease the migration to SQL, created an interface to sqlalchemy that looks a lot like the interface that is currently in use. *WARNING* Work in progress --- mediagoblin/db/sql/models.py | 4 +++- 1 file changed, 3 insertions(+), 1 deletion(-) (limited to 'mediagoblin/db/sql/models.py') diff --git a/mediagoblin/db/sql/models.py b/mediagoblin/db/sql/models.py index 7723a753..b87ff3aa 100644 --- a/mediagoblin/db/sql/models.py +++ b/mediagoblin/db/sql/models.py @@ -5,8 +5,10 @@ from sqlalchemy import ( Column, Integer, Unicode, UnicodeText, DateTime, Boolean, ForeignKey, UniqueConstraint) +from mediagoblin.db.sql.base import GMGTableBase -Base = declarative_base() + +Base = declarative_base(cls=GMGTableBase) class User(Base): -- cgit v1.2.3 From 19ed039ba6d65cecfd6e8ad6e47b5cb008350b04 Mon Sep 17 00:00:00 2001 From: Elrond Date: Sun, 25 Dec 2011 20:03:11 +0100 Subject: Implement _id proxy on sql objects (on User for now) So that the old code can access the primary key still as "._id". Quite simple Python Descriptor thing. Very generic. --- mediagoblin/db/sql/models.py | 14 ++++++++++++++ 1 file changed, 14 insertions(+) (limited to 'mediagoblin/db/sql/models.py') diff --git a/mediagoblin/db/sql/models.py b/mediagoblin/db/sql/models.py index b87ff3aa..68b078a5 100644 --- a/mediagoblin/db/sql/models.py +++ b/mediagoblin/db/sql/models.py @@ -11,6 +11,18 @@ from mediagoblin.db.sql.base import GMGTableBase Base = declarative_base(cls=GMGTableBase) +class SimpleFieldAlias(object): + """An alias for any field""" + def __init__(self, fieldname): + self.fieldname = fieldname + + def __get__(self, instance, cls): + return getattr(instance, self.fieldname) + + def __set__(self, instance, val): + setattr(instance, self.fieldname, val) + + class User(Base): __tablename__ = "users" @@ -32,6 +44,8 @@ class User(Base): ## TODO # plugin data would be in a separate model + _id = SimpleFieldAlias("id") + class MediaEntry(Base): __tablename__ = "media_entries" -- cgit v1.2.3 From c6263400cfd334a820122bd1b22eaa4d4d6765cd Mon Sep 17 00:00:00 2001 From: Elrond Date: Sat, 24 Dec 2011 18:12:38 +0100 Subject: SQL Model: Forgot MediaEntry.state field While creating the new SQL model, the "state" field of MediaEntry was left out. Currently using a plain unicode string for it. Maybe should use sqlalchemy.types.Enum? --- mediagoblin/db/sql/models.py | 1 + 1 file changed, 1 insertion(+) (limited to 'mediagoblin/db/sql/models.py') diff --git a/mediagoblin/db/sql/models.py b/mediagoblin/db/sql/models.py index 68b078a5..268f5715 100644 --- a/mediagoblin/db/sql/models.py +++ b/mediagoblin/db/sql/models.py @@ -58,6 +58,7 @@ class MediaEntry(Base): description = Column(UnicodeText) # ?? description_html = Column(UnicodeText) # ?? media_type = Column(Unicode, nullable=False) + state = Column(Unicode, nullable=False) # or use sqlalchemy.types.Enum? fail_error = Column(Unicode) fail_metadata = Column(UnicodeText) -- cgit v1.2.3 From 88e90f41eb86b8aa1fcfef1e0585f314afb5180d Mon Sep 17 00:00:00 2001 From: Elrond Date: Sat, 24 Dec 2011 16:00:05 +0100 Subject: SQL Model: Add relationship properties MediaEntry now has a get_uploader (property) loading the appropiate User object for the MediaEntry (and caches it). MediaComment has the same for author as get_author. --- mediagoblin/db/sql/models.py | 5 +++++ 1 file changed, 5 insertions(+) (limited to 'mediagoblin/db/sql/models.py') diff --git a/mediagoblin/db/sql/models.py b/mediagoblin/db/sql/models.py index 268f5715..31a6ed3b 100644 --- a/mediagoblin/db/sql/models.py +++ b/mediagoblin/db/sql/models.py @@ -4,6 +4,7 @@ from sqlalchemy.ext.declarative import declarative_base from sqlalchemy import ( Column, Integer, Unicode, UnicodeText, DateTime, Boolean, ForeignKey, UniqueConstraint) +from sqlalchemy.orm import relationship from mediagoblin.db.sql.base import GMGTableBase @@ -71,6 +72,8 @@ class MediaEntry(Base): UniqueConstraint('uploader', 'slug'), {}) + get_uploader = relationship(User) + ## TODO # media_files # media_data @@ -112,6 +115,8 @@ class MediaComment(Base): content = Column(UnicodeText, nullable=False) content_html = Column(UnicodeText) + get_author = relationship(User) + def show_table_init(): from sqlalchemy import create_engine -- cgit v1.2.3 From f42e49c3ad1c446e7899e7b76cd7fa381d5bba7c Mon Sep 17 00:00:00 2001 From: Elrond Date: Thu, 5 Jan 2012 00:18:17 +0100 Subject: Add DB Mixin classes and use them A bunch of functions on the db objects are really more like "utility functions": They could live outside the classes and be called "by hand" passing the appropiate reference. They usually only use the public API of the object and rarely use database related stuff. Goals: - First, simple: Share the code with the SQL objects, so that the code doesn't need to be duplicated. - Second, it might unclutter the db models and make them more into "model only" stuff. - Doesn't really hurt. --- mediagoblin/db/sql/models.py | 5 +++-- 1 file changed, 3 insertions(+), 2 deletions(-) (limited to 'mediagoblin/db/sql/models.py') diff --git a/mediagoblin/db/sql/models.py b/mediagoblin/db/sql/models.py index 31a6ed3b..95821b4f 100644 --- a/mediagoblin/db/sql/models.py +++ b/mediagoblin/db/sql/models.py @@ -7,6 +7,7 @@ from sqlalchemy import ( from sqlalchemy.orm import relationship from mediagoblin.db.sql.base import GMGTableBase +from mediagoblin.db.mixin import UserMixin, MediaEntryMixin Base = declarative_base(cls=GMGTableBase) @@ -24,7 +25,7 @@ class SimpleFieldAlias(object): setattr(instance, self.fieldname, val) -class User(Base): +class User(Base, UserMixin): __tablename__ = "users" id = Column(Integer, primary_key=True) @@ -48,7 +49,7 @@ class User(Base): _id = SimpleFieldAlias("id") -class MediaEntry(Base): +class MediaEntry(Base, MediaEntryMixin): __tablename__ = "media_entries" id = Column(Integer, primary_key=True) -- cgit v1.2.3 From 02db7e0a83fc06eaa8e96888f6c9e4fb44e7cbe2 Mon Sep 17 00:00:00 2001 From: Elrond Date: Sat, 31 Dec 2011 23:01:34 +0100 Subject: Add MediaFile table and related infrastructure. - This adds a new SQL table field type for path tuples. They're stored as '/' separated unicode strings. - Uses it to implement a MediaFile table. - Add relationship and proxy fields on MediaEntry to give a nice media_files "view" there. - Let the converter fill the MediaFile. --- mediagoblin/db/sql/models.py | 27 +++++++++++++++++++++++++-- 1 file changed, 25 insertions(+), 2 deletions(-) (limited to 'mediagoblin/db/sql/models.py') diff --git a/mediagoblin/db/sql/models.py b/mediagoblin/db/sql/models.py index 95821b4f..91092f33 100644 --- a/mediagoblin/db/sql/models.py +++ b/mediagoblin/db/sql/models.py @@ -5,7 +5,10 @@ from sqlalchemy import ( Column, Integer, Unicode, UnicodeText, DateTime, Boolean, ForeignKey, UniqueConstraint) from sqlalchemy.orm import relationship +from sqlalchemy.orm.collections import attribute_mapped_collection +from sqlalchemy.ext.associationproxy import association_proxy +from mediagoblin.db.sql.extratypes import PathTupleWithSlashes from mediagoblin.db.sql.base import GMGTableBase from mediagoblin.db.mixin import UserMixin, MediaEntryMixin @@ -65,7 +68,7 @@ class MediaEntry(Base, MediaEntryMixin): fail_error = Column(Unicode) fail_metadata = Column(UnicodeText) - queued_media_file = Column(Unicode) + queued_media_file = Column(PathTupleWithSlashes) queued_task_id = Column(Unicode) @@ -75,13 +78,33 @@ class MediaEntry(Base, MediaEntryMixin): get_uploader = relationship(User) + media_files_helper = relationship("MediaFile", + collection_class=attribute_mapped_collection("name"), + cascade="all, delete-orphan" + ) + media_files = association_proxy('media_files_helper', 'file_path', + creator=lambda k,v: MediaFile(name=k, file_path=v) + ) + ## TODO - # media_files # media_data # attachment_files # fail_error +class MediaFile(Base): + __tablename__ = "mediafiles" + + media_entry = Column( + Integer, ForeignKey(MediaEntry.id), + nullable=False, primary_key=True) + name = Column(Unicode, primary_key=True) + file_path = Column(PathTupleWithSlashes) + + def __repr__(self): + return "" % (self.name, self.file_path) + + class Tag(Base): __tablename__ = "tags" -- cgit v1.2.3