Skip to content

Commit v0.26

Jump to bottom
kwmccabe edited this page Dec 6, 2018 · 10 revisions

v0.26 - Link ItemModel to UserMode with item.owner_id

Files changed (13)

File mysql/scripts/tables.sql MODIFIED

  • Create table user prior to table item.
  • Add column owner_id to table item.
  • Add FOREIGN KEY ('owner_id') REFERENCES user('id') to table item.
 -- -------------------------------------------- --
--- item
--- -------------------------------------------- --
-DROP TABLE IF EXISTS `item`;
-CREATE TABLE `item` (
-    `id`            bigint(20)      NOT NULL AUTO_INCREMENT,
-    `active`        tinyint(1)      NOT NULL DEFAULT '1',
-    `keyname`       varchar(63)     NOT NULL,
-    `item_title`    varchar(255)    DEFAULT NULL,
-    `item_text`     text,
-    `mod_create`    datetime        DEFAULT CURRENT_TIMESTAMP,
-    `mod_update`    datetime        DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
-
-    PRIMARY KEY (`id`),
-    UNIQUE KEY `item_keyname` (`keyname`),
-    KEY `item_active` (`active`),
-    KEY `item_title` (`item_title`),
-    KEY `item_update` (`mod_update`)
-) ENGINE=InnoDB AUTO_INCREMENT=1000 DEFAULT CHARSET=utf8;
-DESCRIBE `item`;
-SELECT "table `item` created" AS MSG;

 -- -------------------------------------------- --
 -- user
 -- -------------------------------------------- --

...

 SELECT "table `user` created" AS MSG;
+
+
+-- -------------------------------------------- --
+-- item
+-- -------------------------------------------- --
+DROP TABLE IF EXISTS `item`;
+CREATE TABLE `item` (
+    `id`            bigint(20)      NOT NULL AUTO_INCREMENT,
+    `active`        tinyint(1)      NOT NULL DEFAULT '1',
+    `keyname`       varchar(63)     NOT NULL,
+    `item_title`    varchar(255)    DEFAULT NULL,
+    `item_text`     text,
+    `mod_create`    datetime        DEFAULT CURRENT_TIMESTAMP,
+    `mod_update`    datetime        DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
+    `owner_id`      bigint(20)      NULL,
+
+    PRIMARY KEY (`id`),
+    UNIQUE KEY `item_keyname` (`keyname`),
+    KEY `item_active` (`active`),
+    KEY `item_title` (`item_title`),
+    KEY `item_update` (`mod_update`),
+    FOREIGN KEY (`owner_id`) REFERENCES user(`id`)
+) ENGINE=InnoDB AUTO_INCREMENT=1000 DEFAULT CHARSET=utf8;
+DESCRIBE `item`;
+SELECT "table `item` created" AS MSG;

File web/app/item/forms.py MODIFIED

  • Add owner_id element to EditItemForm.
  • choices is populated by the view /admin/item/edit/.
  • coerce=int converts id values to integers prior to the update.
     mod_update = DateTimeField('Item Updated')
+    owner_id   = SelectField('Item Owner', choices=[], coerce=int)
     submit     = SubmitField('Update Item')

File web/app/item/models.py MODIFIED

  • Add utility function get_owner_id_choices() to populate choices for EditItemForm.owner_id.
  • Add column ItemModel.owner_id, specified as a ForeignKey.
  • Add relationship for ItemModel.owner to join the UserModel on item.owner_id = user.id.
 from flask import url_for
 from datetime import datetime
 from .. import db
+from ..user.models import UserModel
+
+
+def get_owner_id_choices():
+    result = []
+    rows = UserModel.query.order_by(getattr( UserModel, 'keyname' ).asc())
+    for row in rows:
+        result.append((row.id, "%s (%s)" % (row.keyname,row.user_email)))
+    return result

...

     mod_create = db.Column(db.DateTime, default=datetime.utcnow)
     mod_update = db.Column(db.DateTime, default=datetime.utcnow, onupdate=datetime.utcnow, index=True)
 
+    # 'back_populates' requires reciprocal relationship in UserModel ; 'backref' creates both sides
+    # @see http://docs.sqlalchemy.org/en/latest/orm/relationship_api.html
+    owner_id   = db.Column(db.Integer, db.ForeignKey('user.id'))
+    #owner      = db.relationship('UserModel', back_populates='items')
+    owner      = db.relationship('UserModel', backref='items')
+
     def to_json(self):
         json_item = {
             #'url': url_for('api.get_item', id=self.id),
             '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,
         }
         return json_item

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

  • Place EditItemForm.owner_id within the template item_edit.html.
  • Switch panel style per item.active status.
-<div id="item_edit_panel" class="panel panel-info">
+<div id="item_edit_panel" class="panel {% if form.active.data %}panel-info{% else %}panel-warning{% endif %}">

...

     {{ form.id }}
+    <div class="form-group">
+        {{ form.owner_id.label }}
+        {{ form.owner_id(class_='form-control') }}
+    </div>

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

  • Replace display of item.owner_id with item.owner.keyname.
-    {{ col }}
+    {% if col == 'owner_id' %}Owner{% else %}{{ col }}{% endif %}

...

     {% for col in cols %}
-        <td onclick="window.location='{{ url_for('.item_view', id=row.id) }}';">{{ row[col] }}</td>
+        <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>
+            {% else %}
+                {{ row[col] }}
+            {% endif %}
+        </td>
     {% endfor %}

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

  • Use item.owner_id to link associated user.id.
  • Use item.owner to display associated user.keyname and user.user_email.
  • Switch panel style per item.active status.
-<div id="item_list_panel" class="panel panel-info">
+<div id="item_list_panel" class="panel {% if item.active %}panel-info{% else %}panel-warning{% endif %}">

...

+<tr>
+<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>
+    {% else %}
+        None
+    {% endif %}
+</td>
+</tr>
+
 </table>

File web/app/item/views.py MODIFIED

  • Set item.owner_id = current_user.id automatically in item_create().
  • Populate owner choices for EditItemForm via form.owner_id.choices = get_owner_id_choices().
  • Update sorting options in item_list() to accommodate owner relationship column.
  • Update test function hello_orm() to reflect possible join(UserModel).
-from flask_login import login_required
+from flask_login import current_user, login_required

...

-from .models import ItemModel
+from .models import ItemModel, get_owner_id_choices

...

     form = CreatItemForm(item)
     if form.validate_on_submit():
         form.populate_obj(item)
+        item.owner_id = current_user.id
         db.session.add(item)
         db.session.commit()

...

 def item_edit( id ):
     item = ItemModel.query.get_or_404(id)
     form = EditItemForm(item)
+    form.owner_id.choices = get_owner_id_choices()
     if form.validate_on_submit():
         del form.mod_create, form.mod_update
         form.populate_obj(item)

...

-@item.route('/admin/item/list')
+@item.route('/admin/item/list', methods=['GET','POST'])
 @get_list_opts('item_list_opts')
 @login_required
 def item_list():

....

-    if S['sort'] in cols:
+    if S['sort'] == 'owner_id':
+        from ..user.models import UserModel
+        rows = rows.outerjoin(UserModel)
+        if S['order'] == 'desc':
+            rows = rows.order_by(getattr( UserModel, 'keyname' ).desc())
+        else:
+            rows = rows.order_by(getattr( UserModel, 'keyname' ).asc())
+    elif S['sort'] in cols:
         if S['order'] == 'desc':

...

 @item.route('/hello_orm')
 def hello_orm():
-    cols  = ItemModel.__table__.columns.keys()
+
     rows = db.session.query(ItemModel)
+    #rows = rows.filter(ItemModel.id == 1000)
+
+    #from ..user.models import UserModel
+    #rows = rows.join(UserModel)
+    #rows = rows.order_by(getattr( UserModel, 'keyname' ).asc())
+    #rows = rows.filter(UserModel.id == 1000)
 
+    #rows = rows.join('owner')
+    rows = rows.outerjoin('owner')
+    rows = rows.order_by("item.owner_id IS NULL, user.keyname ASC")
+    #rows = rows.filter("user.id = 1000")
+
+    i = 0
+    cols_item = None
+    cols_user = None
     result = '<b>db.session.query(ItemModel)</b>'
-    result += '<br/>| '
-    for col in cols:
-        result += '<b>'+str(col)+'</b> | '
     for row in rows:
+        if cols_item == None and row.__table__.columns:
+            cols_item  = row.__table__.columns.keys()
+        if cols_user == None and row.owner.__table__.columns:
+            cols_user  = row.owner.__table__.columns.keys()
+        if i == 0:
+            result += '<br/>| '
+            for col in cols_item:
+                result += '<b>item.'+str(col)+'</b> | '
+            for col in cols_user:
+                result += '<b>user.'+str(col)+'</b> | '
+
         result += '<br/>| '
-        for col in cols:
+        for col in cols_item:
             result += '%s | ' % getattr( row, col )
+        for col in cols_user:
+            result += '%s | ' % getattr( row.owner, col ) if row.owner else 'None | '
+
+        i += 1
+
     return result

File web/app/user/models.py MODIFIED

  • Add db.relationship() notes.
  • Insure proper init of UserModel.cnt_login.
  • Add items_count to JSON representation, using the items relationship.
 from .. import db, login_manager
+#from ..item.models import ItemModel
 
...

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

...

             'mod_login' : self.mod_login,
             'mod_create': self.mod_create,
             'mod_update': self.mod_update,
+            #'items_url': url_for('api.get_user_items', id=self.id),
+            'item_count': self.items.count()
         }
         return json_user
 
     def __repr__(self):
-        return '<UserModel %r>' % (self.id)
+        return '<UserModel: id="%r", keyname="%r">' % (self.id, self.keyname)
+
+    def __str__(self):
+        return 'User: "%r"' % (self.keyname)

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

  • Switch panel style per user.active status.
-<div id="item_edit_panel" class="panel panel-info">
+<div id="item_edit_panel" class="panel {% if form.active.data %}panel-info{% else %}panel-warning{% endif %}">

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

  • Switch panel style per user.active status.
-<div id="item_list_panel" class="panel panel-info">
+<div id="item_list_panel" class="panel {% if user.active %}panel-info{% else %}panel-warning{% endif %}">

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

  • Display list of Items owned by this user.
  • Switch panel style per user.active status.
-<div id="item_list_panel" class="panel panel-info">
+<div id="item_list_panel" class="panel {% if user.active %}panel-info{% else %}panel-warning{% endif %}">

...

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

File web/app/user/views.py MODIFIED

  • Add cnt_login and mod_login to list of fields not updated in user_edit().
-    del form.mod_create, form.mod_update
+    del form.cnt_login, form.mod_login, form.mod_create, form.mod_update

File web/flaskapp.py MODIFIED

  • Add testing note to hello_db() re possible item-to-user join.
+    #stmt = "select * from user join item on user.id=item.owner_id"

Commit-v0.25 | Commit-v0.26 | Commit-v0.27

Clone this wiki locally