Blog Exercise in SQLite

SQLite

Note that I decided to use SQLite for this example. It will affect our code in just a few minor ways. Here's how to create and initialize the SQLite database, which I saved in a file called blog.db, which is also in the directory you will copy.

$ sqlite3 blog.db
SQLite version 3.7.17 2013-05-20 00:56:22
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> .read logins.sql
happy|happypass
bashful|bashfulpass
grumpy|grumpypass
sleepy|sleepypass
sneezy|sneezypass
dopey|dopeypass
doc|docpass
sleazy|sleazypass
gropey|gropeypass
dumpy|dumpypass
2020-03-24 12:34:56|dopey|First!
2020-03-24 13:45:00|grumpy|Give it a rest, Dopey.

sqlite>

The .read command is equivalent to the source command that we learned for the mysql client, though possibly more intuitive.

Another useful command is .schema which shows you the schemata (schemas or definitions) of your tables:

sqlite> .schema
CREATE TABLE blog_user(
    user varchar(30) primary key,
    pass varchar(30)
);
CREATE TABLE blog_entry(
    entered timestamp,
    user varchar(30),
    entry text,
    foreign key (user) references blog_user(user));
sqlite>

Using the SQLite client, you can run queries just as usual:

sqlite3 blog.db
sqlite> select * from blog_user;
happy|happypass
bashful|bashfulpass
grumpy|grumpypass
sleepy|sleepypass
sneezy|sneezypass
dopey|dopeypass
doc|docpass
sleazy|sleazypass
gropey|gropeypass
dumpy|dumpypass
sqlite>

The result is formatted a little differently than we are used to, but otherwise this is straightforward.

Running the Blog Exercise Demo

Before we attack the blogging application, we'll download it and I'll demo. You can also run this on your own. Here are the steps:

  1. Go to your cs304 folder
  2. Activate your venv
  3. Copy the app (cp or curl)
  4. start the app and open the SSH tunnel
  5. In the browser, visit the main page
  6. Note the listing of current blog entries
  7. Post something as someone. The blog doesn't check passwords, so anything will work
  8. Login as someone
  9. Post once or twice as that person; notice that you don't have to give your name each time.
  10. You can even try logging in as different people on different browsers

Video of the Blog Exercise

Here's a video of how the completed blog

Blogging Exercise

Before we attack the blogging application, we'll download it and I'll demo. Here's how to download and run the starter version. 

cd ~/cs304
source venv/bin/activate
cp -r ~cs304/pub/downloads/blog-ex blog-ex
cd blog-ex
python start.py

Starting Code

Unlike the cookie example in the reading, where I did all the cases in one Flask route, in the blog-ex example, I decided to parcel out the various cases to different routes. I actually like this code better, though it is more spread out. Let's consider the code in pieces:

API to SQLite

In the blog exercise, I used SQLite. Here's part of the code to import the API and to retrieve the blog entries:

import cs304dbi_sqlite3 as dbi

def recent_entries(conn,limit=10):
    '''Returns the most recent 'limit' entries from the blog'''
    curs = dbi.dict_cursor(conn)
    curs.execute('''SELECT entered as time,user,entry
                    FROM blog_entry
                    ORDER BY entered DESC
                    LIMIT ?''',[limit])
    return curs.fetchall()

Note the different import statements and the ? instead of the %s for the placeholder. Otherwise, pretty much the same.

Initializing SQLite

We saw earlier how to set up our connection to the SQLite database, but here it is again. This code is at the end of the start.py file:

if __name__ == '__main__':
    dbi.cache_cnf()
    dbi.use('blog')
    conn=dbi.connect()
    entries=recent_entries(conn)
    for e in entries:
        print(e)
    import sys,os
    if len(sys.argv) > 1:
        # arg, if any, is the desired port number
        port = int(sys.argv[1])
        assert(port>1024)
    else:
        port = os.getuid()
    app.debug = True
    app.run('0.0.0.0',port)

Notice the use statement. The argument here means that database connections will be made to the file blog.db, which is in this directory.

Main Route

The main route retrieves the latest entries and displays the page with the forms and buttons in it:

@app.route('/')
def index():
    ## TODO: get bloguser value from cookie
    bloguser = ''
    conn = dbi.connect()
    entries = recent_entries(conn)
    return render_template('blog.html',
                           allCookies=request.cookies,
                           bloguser=bloguser,
                           rows=entries)

Note the TODO comment, indicating something you should implement.

Main Template

The blog.html template is pretty straightforward, but notice the urls for routes to process logins, logout and posting of blog entries:

{% extends "base.html" %}
{% block page_content %}

{% if bloguser %}

   <form method="post" action="{{url_for('logout')}}">
        <button>logout {{bloguser}}</button>
   </form>

{% else %}
   <form method="post" action="{{url_for('login')}}">
        <p><label>User: <input type=text name="loginname"></label>
        <p><label>Pass: <input type=password name="loginpass"></label>
        <p><input type="submit" value="login">
  </form>

{% endif %}

<form method=post action="{{url_for('post_comment')}}">
     {% if bloguser %}
          <p>Name: {{bloguser}}</p>
     {% else %}
          <p><label>Name: <input type=text name="blogname"></label></p>
     {% endif %}
     <p><label>Comment: <br>
         <textarea name="comment" rows="5" cols="30"></textarea></label></p>
     <p><input type="submit" value="post"></p>
  </form>

<dl>
     {% for row in rows %}
         <dt>{{row.user}} at {{row.time}}</dt><dd>{{row.entry}}</dd>
     {% endfor %}
</dl>
{% endblock %}

Base Template

The main template inherits some important stuff from a base.html, such as displaying the cookies, reporting a message, and a little CSS styling.

<!doctype html>
<html>
<head>
<title>Commenting</title>
<style>
    form { border: 1px solid black; }
    #msg { font-weight: bold; color: red; }
</style>
</head>
<body>

<h1>Blog App</h1>

        <p>From base: These are all the cookies the application can see:</p>
        <ul>
            {% for key in allCookies %}
            <li>{{key}} : {{allCookies[key]}}</li>
            {% endfor %}
        </ul>

{% if msg %}
   <p id="msg">{{msg}}</p>
{% endif %}

{% block page_content %}
{% endblock %}        

</body>
</html>

Posting Comments

The starter version, start.py, has working code to post a blog comment. Let's see how that works:

@app.route('/post_comment/', methods=['POST'])
def post_comment():
    ## TODO: get bloguser value from cookie
    bloguser = ''
    if ('comment' in request.form):
        if 'blogname' in request.form:
            user=request.form['blogname']
        else:
            user=bloguser
        entry = request.form['comment']
        conn = dbi.connect()
        curs = dbi.cursor(conn) # a tuple cursor
        curs.execute('''INSERT INTO blog_entry(entered,user,entry) VALUES
                       (CURRENT_TIMESTAMP,?,?)''',
                     [user,entry])
        conn.commit()           # don't forget to commit!
        return redirect(url_for('index'))
    else:
        return render_template('go_home.html',
                               msg='comment form missing comment input')

This code works because it either pulls the username out of the form (starter version) or uses the cookie value (which you will implement). Give those two values, it inserts the database entry, commits it, and redirects to the main route. It uses the CURRENT_TIMESTAMP value which is evaluate to the current date and time in UTC (Greenwich Mean Time).

Login

Let's look at the starter code for logins. There's work you need to add to this, but the code itself is mostly working. The basic login strategy is to take the username and password from the form, and count the number of entries in the table where both match. If the count is 0, the username and password were not correct. If the count is 1, they were. The count can't be more than 1 because the username is a key and therefore must be unique.

@app.route('/login/', methods=['POST'])
def login():
    if ('loginname' in request.form and
        'loginpass' in request.form):
        conn = dbi.connect()
        curs = dbi.cursor(conn)
        curs.execute('''SELECT count(*)
                          FROM blog_user
                          WHERE user=? AND pass=?''',
                       (request.form['loginname'],
                        request.form['loginpass']))
        row = curs.fetchone()
        # remember, we are using a tuple cursor this time
        if row[0] == 1:
            bloguser=request.form.get('loginname')
            ## TODO: make a response object and set the cookie
            resp = render_template('go_home.html',
                                   allCookies=request.cookies,
                                   msg='logged in as '+bloguser)
            return resp
        else:
            errmsg = 'incorrect login'
            # we are not using flash, otherwise we would definitely do this
            # flash(errmsg)
            # instead, we use a message
            return render_template('go_home.html',
                                   allCookies=request.cookies,
                                   msg=errmsg)

Note the TODO lines where your code needs to remember the username using a cookie.

Logout

For completeness, we'll look at the logout code:

@app.route('/logout/', methods=['POST'])
def logout():
    ## TODO: make a response, saying they are logged out,
    ## expire the cookie and return the response object
    return render_template('go_home.html',
                           allCookies=request.cookies,
                           msg='logged out')

It doesn't do much yet. You'll implement code to remove the cookie.

Goals/Approach

  • "posted by" (commenter) field should come from cookie, rather than form
  • login action should set the cookie
  • various routes should read the cookie

Create a login session for the comment app.

Here's how to approach the problem:

  1. Re-read the start.py code to make sure you understand it. The code was described above.
  2. Notice the places where it says TODO and suggests what you need to do.
  3. Copy the start.py file to your own file: 
    cp start.py mycode.py
  4. Modify the mycode.py file application to set a cookie when someone logs in.
  5. Modify the application to read the cookie and use it when adding blog entries, so they are no longer anonymous, yet the user doesn't have to keep authenticating.
  6. Test, test, test
  7. sigh in amazed satisfaction that it really works
  8. Check solutions below.
  9. If you finish before the end of class, you can:
    • Bask in the glow of your success
    • Implement a "logout" button
    • Use the JavaScript console to hack the application: login as, say, "dopey," and then change the username to "grumpy" and continue posting.

Solutions

The solutions are in the done.py.

I strongly suggest using the Unix diff command to compare the start.py with done.py