Skip to content

Commit v0.28

Jump to bottom
kwmccabe edited this page Apr 17, 2018 · 8 revisions

v0.28 - Many-to-many link through ItemUserModel and new item_user table

Files changed (12)

File mysql/scripts/seeddata.sql MODIFIED

  • Add second test user.
  • Link item one to user and user2.
 DELETE FROM `user`;
 INSERT INTO `user` (active,keyname,user_email) VALUES (True,"admin","[email protected]");
 INSERT INTO `user` (active,keyname,user_email) VALUES (False,"user","[email protected]");
+INSERT INTO `user` (active,keyname,user_email) VALUES (False,"user2","[email protected]");
 OPTIMIZE TABLE `user`;
 
...

+DELETE FROM `item_user`;
+insert into `item_user` (item_id,user_id) values (1000,1001);
+insert into `item_user` (item_id,user_id) values (1000,1002);
+OPTIMIZE TABLE `item_user`;

File mysql/scripts/tables.sql MODIFIED

  • Add new relational table item_user.
  • item_id references the item table.
  • user_id references the user table.
  • relation allows the link to be typed - 'editor' will be the default.
+-- -------------------------------------------- --
+-- item_user
+-- -------------------------------------------- --
+DROP TABLE IF EXISTS `item_user`;
+CREATE TABLE `item_user` (
+    `item_id`       bigint(20)      NOT NULL,
+    `user_id`       bigint(20)      NOT NULL,
+    `relation`      varchar(31)     NOT NULL DEFAULT 'editor',
+    PRIMARY KEY (`item_id`,`user_id`),
+    KEY `user_item` (`user_id`,`item_id`),
+    KEY `user_relation` (`relation`),
+    FOREIGN KEY (`item_id`) REFERENCES item(`id`) ON DELETE CASCADE,
+    FOREIGN KEY (`user_id`) REFERENCES user(`id`) ON DELETE CASCADE
+) ENGINE=InnoDB AUTO_INCREMENT=1000 DEFAULT CHARSET=utf8;
+DESCRIBE `item_user`;
+SELECT "table `item_user` created" AS MSG;

File web/app/item/forms.py MODIFIED

  • Add a SelectMultipleField to the EditItemForm, corresponding with the users_id element of ItemModel.
  • This interface will not work with any significant number of system users - we'll need to upgrade it later.
     owner_id   = SelectField('Item Owner', choices=[], coerce=int)
+    users_id   = SelectMultipleField('Editors', choices=[], coerce=int)
     submit     = SubmitField('Update Item')

File web/app/item/models.py MODIFIED

  • Create new class ItemUserModel with relationships to ItemModel and UserModel.
  • Add users_id element to ItemModel using sqlalchemy's association_proxy.
+import logging
 from flask import url_for
 from datetime import datetime
+from sqlalchemy.ext.associationproxy import association_proxy
 from .. import db
 from ..user.models import UserModel

...

     owner_id   = db.Column(db.Integer, db.ForeignKey('user.id'))
     owner      = db.relationship('UserModel', backref='items')
 
+    # association proxy of "item_users" collection to "users_id" attribute for EditItemForm
+    # @see http://docs.sqlalchemy.org/en/rel_0_9/orm/extensions/associationproxy.html
+    users_id = association_proxy('item_users', 'user.id')
+
     def to_json(self):
         json_item = {
             #'url': url_for('api.get_item', id=self.id),
             'id'        : self.id,
-            'keyname'   : self.keyname,
             'active'    : self.active,
+            'keyname'   : self.keyname,
             'item_title': self.item_title,
             'item_text' : self.item_text,
             'mod_create': self.mod_create,
             'mod_update': self.mod_update,
             #'owner_url': url_for('api.get_user', id=self.owner_id),
             'owner_id'  : self.owner_id,
+            #'users_url': url_for('api.get_item_users', id=self.id),
+            'users_count': self.item_users.count()
         }
         return json_item
 
+    def __init__(self, **kwargs):
+        super(ItemModel, self).__init__(**kwargs)
+        self.id         = kwargs.get('id',        None)
+        self.active     = kwargs.get('active',    True)
+        self.keyname    = kwargs.get('keyname',   None)
+        self.item_title = kwargs.get('item_title',None)
+        self.item_text  = kwargs.get('item_text', 0)
+        self.mod_create = kwargs.get('mod_create',None)
+        self.mod_update = kwargs.get('mod_update',None)
+        logging.debug( "ItemModel.__init__: %r" %  (self))
+        logging.debug( "ItemModel.__init__: owner=%r" %  (self.owner))
+        logging.debug( "ItemModel.__init__: item_users=%r" %  (self.item_users))
+
     def __repr__(self):
-        return '<ItemModel: id="%r", keyname="%r">' % (self.id, self.keyname)
+        return '<ItemModel(id=%r,active=%r,keyname=%r,item_title=%r,item_text=%r,mod_create=%r,mod_update=%r)>' \
+                % (self.id,self.active,self.keyname,self.item_title,self.item_text,self.mod_create,self.mod_update)
 
     def __str__(self):
-        return 'Item: "%r"' % (self.keyname)
+        return 'ItemModel:%r,%r' % (self.id,self.keyname)
+
+
+class ItemUserModel(db.Model):
+    __tablename__ = 'item_user'
+    item_id  = db.Column(db.BigInteger, db.ForeignKey('item.id'), primary_key=True)
+    user_id  = db.Column(db.BigInteger, db.ForeignKey('user.id'), primary_key=True)
+    relation = db.Column(db.String(31), nullable=False, default='editor')
+
+    item = db.relationship("ItemModel", backref='item_users')
+    user = db.relationship("UserModel", backref='user_items')
+
+    def __init__(self, **kwargs):
+        super(ItemUserModel, self).__init__(**kwargs)
+        self.item_id = kwargs.get('item_id', None)
+        self.user_id = kwargs.get('user_id', None)
+        self.relation = kwargs.get('relation', 'editor')
+        logging.debug( "ItemUserModel.__init__: %r" %  (self))
+        logging.debug( "ItemUserModel.__init__: item=%r" %  (self.item))
+        logging.debug( "ItemUserModel.__init__: user=%r" %  (self.user))
+
+    def __repr__(self):
+        return '<ItemUserModel(item_id=%r,user_id=%r,relation=%r)>' % (self.item_id,self.user_id,self.relation)
+
+    def __str__(self):
+        return 'ItemUserModel:%r,%r,%r' % (self.item_id,self.user_id,self.relation)
+

File web/app/item/templates/item_edit.html MODIFIED

  • Place the new SelectMultipleField within the edit page.
+    <div class="form-group">
+        {{ form.users_id.label }}
+        {{ form.users_id(class_='form-control',size='5') }}
+    </div>

File web/app/item/templates/item_list.html MODIFIED

     {% for col in cols %}
         <td onclick="window.location='{{ url_for('.item_view', id=row.id) }}';">
-            {% if col == 'owner_id' and row[col] %}
-                <a href="{{ url_for('user.user_view', id=row[col]) }}">{{ row['owner'].keyname }}</a>
+            {% if col == 'owner_id' and row.owner %}
+                <a href="{{ url_for('user.user_view', id=row[col]) }}">{{ row.owner.keyname }}</a>
             {% else %}
                 {{ row[col] }}
             {% endif %}

File web/app/item/templates/item_view.html MODIFIED

  • List all Item Editors on the item_view() page.
-        <h3 class="condensed">Item '{{ item.keyname }}'</h3>
+        <h3 class="condensed">Item '{{ item.keyname }}'{% if not item.active %} - inactive{% endif %}</h3>

...

 <td>Item Owner</td>
 <td>
     {% if item.owner %}
-        <a href="{{ url_for('user.user_view', id=item.owner_id) }}">{{ item.owner['keyname'] }} ({{ item.owner['user_email'] }})</a>
+        <a href="{{ url_for('user.user_view', id=item.owner_id) }}">{{ item.owner.keyname }} ({{ item.owner.user_email }})</a>
+    {% else %}
+        None
+    {% endif %}
+</td>
+</tr>
+
+<tr>
+<td>Item Editors</td>
+<td>
+    {% if item.item_users %}
+        <ul>
+        {% for iu in item.item_users %}
+            <li><a href="{{ url_for('user.user_view', id=iu.user_id) }}">{{ iu.user.keyname }} ({{ iu.user.user_email }})</a></li>
+        {% endfor %}
+        </ul>
     {% else %}
         None
     {% endif %}

File web/app/item/views.py MODIFIED

  • Import the new ItemUserModel for use in item_edit().
  • Test route to list items, followed by linked users.
-from .models import ItemModel, get_owner_id_choices
+from .models import ItemModel, ItemUserModel, get_owner_id_choices
+from ..user.models import UserModel

...

     item = ItemModel.query.get_or_404(id)
     form = EditItemForm(item)
     form.owner_id.choices = get_owner_id_choices()
+    form.users_id.choices = get_owner_id_choices()
     if form.validate_on_submit():
         del form.mod_create, form.mod_update
+
+        # convert user_id data to ItemUserModel objects
+        # delete previous relations not in current selection
+        item_users = ItemUserModel.query.filter_by(item_id=id).all()
+        for item_user in item_users:
+            if not item_user.user_id in form.users_id.data:
+                logging.debug('1- item_edit( delete:%s )' % (item_user))
+                db.session.delete(item_user)
+                db.session.commit()
+        # insert/update current relations
+        for user_id in form.users_id.data:
+            item_user = ItemUserModel.query.filter_by(item_id=id,user_id=user_id).first()
+            if not item_user:
+                item_user = ItemUserModel(item_id=id,user_id=user_id,relation='editor')
+                logging.debug('2- item_edit( insert:%s )' % (item_user))
+                db.session.add(item_user)
+            elif item_user.relation != 'editor':
+                item_user.relation = 'editor'
+                logging.debug('3- item_edit( update:%s )' % (item_user))
+                db.session.add(item_user)
+        # remove form.users_id prior to populate_obj(item)
+        del form.users_id
+
         form.populate_obj(item)
         db.session.add(item)
         db.session.commit()

...
 
     if S['sort'] == 'owner_id':
-        from ..user.models import UserModel
         rows = rows.outerjoin(UserModel)

...
 
-    #rows = rows.all()
-    #rowcnt = len(rows)
     rowcnt = rows.count()

...

+@item.route('/hello_item_users')
+def hello_item_users():
+    rows = db.session.query(ItemModel)
+
+    result = '<b>db.session.query(ItemModel)</b>'
+    for row in rows:
+        result += '<br/>| %s | ' % (row)
+        for iu in row.item_users:
+            result += ' %s | ' % (iu.user)
+    return result

File web/app/user/models.py MODIFIED

 def load_user(user_id):
     logging.info( "load_user(%s)" %  user_id)
-    return UserModel.query.filter_by(user_email=user_id).first()
+    return UserModel.query.filter_by(id=user_id).first()
 
...

     mod_update = db.Column(db.DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)
 
-    # 'back_populates' requires reciprocal relationship in ItemModel
-    # 'backref' creates both sides ; needs 'primaryjoin' because foreign key is on item table
-    # @see http://docs.sqlalchemy.org/en/latest/orm/relationship_api.html
-    #items = db.relationship('ItemModel', back_populates='owner')
-    #items = db.relationship('ItemModel', backref='owner', primaryjoin='ItemModel.owner_id == UserModel.id')
-
-    def __init__(self, **kwargs):
-        super(UserModel, self).__init__(**kwargs)
-        if self.cnt_login is None:
-            self.cnt_login = 0
-
     @property
     def password(self):

...
 
     def get_id(self):
-        return self.user_email
+        return self.id

...

             #'items_url': url_for('api.get_user_items', id=self.id),
-            'item_count': self.items.count()
+            'items_count': self.user_items.count()
         }
         return json_user
 
+    def __init__(self, **kwargs):
+        super(UserModel, self).__init__(**kwargs)
+        self.id         = kwargs.get('id',        None)
+        self.active     = kwargs.get('active',    True)
+        self.keyname    = kwargs.get('keyname',   None)
+        self.user_email = kwargs.get('user_email',None)
+        self.cnt_login  = kwargs.get('cnt_login', 0)
+        self.mod_login  = kwargs.get('mod_login', None)
+        self.mod_create = kwargs.get('mod_create',None)
+        self.mod_update = kwargs.get('mod_update',None)
+        logging.debug( "UserModel.__init__: %r" %  (self))
+        logging.debug( "ItemModel.__init__: items=%r" %  (self.items))
+        logging.debug( "ItemModel.__init__: user_items=%r" %  (self.user_items))
+
     def __repr__(self):
-        return '<UserModel: id="%r", keyname="%r">' % (self.id, self.keyname)
+        return '<UserModel(id=%r,active=%r,keyname=%r,user_email=%r,cnt_login=%r,mod_login=%r,mod_create=%r,mod_update=%r)>' \
+                % (self.id,self.active,self.keyname,self.user_email,self.cnt_login,self.mod_login,self.mod_create,self.mod_update)
 
     def __str__(self):
-        return 'User: "%r"' % (self.keyname)
+        return 'UserModel:%r,%r' % (self.id,self.keyname)

File web/app/user/templates/user_view.html MODIFIED

-        <h3 class="condensed">User '{{ user.keyname }}'</h3>
+        <h3 class="condensed">User '{{ user.keyname }}'{% if not user.active %} - inactive{% endif %}</h3>

...

 <tr>
 <td>User Items</td>
-<td><ol>
+<td><ul>
 {% for item in user.items %}
-    <li><a href="{{ url_for('item.item_view', id=item.id) }}">{{ item['keyname'] }}</a></li>
+    <li><a href="{{ url_for('item.item_view', id=item.id) }}">{{ item.keyname }}</a></li>
 {% endfor %}
-</ol></td>
+</ul></td>
 </tr>

File web/app/user/views.py MODIFIED

  • Test route to list users, followed by linked items.
+@user.route('/hello_user_items')
+def hello_user_items():
+    rows = db.session.query(UserModel)
+
+    result = '<b>db.session.query(UserModel)</b>'
+    for row in rows:
+        result += '<br/>| %s | ' % (row)
+        for iu in row.user_items:
+            result += " %s | " % (iu.item)
+    return result

File web/flaskapp.py MODIFIED

  • Add some statements to our hello_db() method to query the new item_user table.
     #stmt = "select * from user join item on user.id=item.owner_id"
+    #stmt = "select * from item_user"
+
+    #stmt = "SELECT i.id AS item_id, i.keyname, u.id AS user_id, u.keyname FROM item i JOIN item_user iu ON i.id = iu.item_id JOIN user u ON iu.user_id = u.id"
+    # users, cnt_items
+    #stmt = "SELECT u.keyname, COUNT(i.id) AS cnt_items FROM user u JOIN item_user iu ON u.id = iu.user_id JOIN item i ON i.id = iu.item_id GROUP BY u.keyname"
+    # items, cnt_users
+    #stmt = "SELECT i.keyname, COUNT(u.id) AS cnt_users FROM item i JOIN item_user iu ON i.id = iu.item_id JOIN user u ON u.id = iu.user_id GROUP BY i.keyname"

Commit-v0.27 | Commit-v0.28 | Commit-v0.29

Clone this wiki locally