Database Homework #4, Fall 2016

In this homework assignment, you will create a new version of the Flask blog application you did during our Flask lab. This new version of the Flask blog allows you to post to the blog using a specific username and password. You can see the final product at this link.

Step 1

Finish the Flask lab if you haven't already. Everything you learn during this lab is the basis for this homework assignment.

You can complete the lab by following the steps at the lab webpage. See me during office hours if you have any questions.

Step 2

In this step, you will get set up with the new Flask app.

Under your flask-apps directory, copy your blog folder to a new folder called blog2.
In blog2, delete blog.db.

In blog2, overwrite schema.sql with the following code:

drop table if exists entries;
drop table if exists users;

create table users (
  username varchar(80) primary key not null,
  password varchar(80) not null
);

create table entries (
  id integer primary key not null,
  username varchar(80) not null,
  date datetime not null,
  title varchar(80) not null,
  content text not null,
    foreign key (username) references users(username)
);

In blog2, overwrite populate.sql with the following code:

insert into users(username, password) values ('alice', 'aaa');
insert into users(username, password) values ('bob', 'bbb');
 
insert into entries(username, date, title, content) 
  values ('alice', datetime('now', '-10 days', 'localtime'), 'Alice''s first post', 
  'This is my first post.  I am Alice.  It is exciting!');
           
insert into entries(username, date, title, content) 
  values ('bob', datetime('now', '-7 days', 'localtime'), 'Bob''s first post', 
  'This is Bob''s first post.  I like this blog.');
           
insert into entries(username, date, title, content) 
  values ('alice', datetime('now', '-6 days', 'localtime'), 'Alice''s second post', 
  'This is the text of Alice''s second post');
           
insert into entries(username, date, title, content) 
  values ('bob', datetime('now', '-5 days', 'localtime'), 'Bob''s second post', 
  'This is the text of Bob''s second post');
           
insert into entries(username, date, title, content) 
  values ('alice', datetime('now', '-4 days', 'localtime'), 'Alice''s third post', 
  'This is the text of Alice''s third post');
           
insert into entries(username, date, title, content) 
  values ('bob', datetime('now', '-3 days', 'localtime'), 'Bob''s third post', 
  'This is the text of Bob''s third post');
           
insert into entries(username, date, title, content) 
  values ('alice', datetime('now', '-2 days', 'localtime'), 'Alice''s fourth post', 
  'This is the text of Alice''s 4th post');
           
insert into entries(username, date, title, content) 
  values ('bob', datetime('now', '-1 days', 'localtime'), 'Bob''s fourth post', 
  'This is the text of Bob''s 4th post');

Step 3

From the command line, run "flask init" then "flask populate" to make sure the database connections are working correctly. This will create a fresh version of blog.db, which you can then view in your SQLite browser, if you want to.

Step 4

You will need to modify blog.py, along with the templates in the templates folder, to add the following capabilities (I suggest doing them in the order listed below):

List the entries in the blog. The existing code in the browse route and browse.html does this, but it doesn't list the author/username of the post. Edit the code to make the HTML output tell you who wrote the post (like you can see at the sample).
Write a new entry in the blog. You should have a drop down list to pick the username, and a password field for the password. Only if the password typed into the web form matches the database user's password should the blog entry be posted. If the password is wrong, show a message to this effect. Duplicate the functionality you see in demo. There are two sample users, alice and bob, whose passwords can be seen in the schema.sql file. Note that in the real world, this is horribly insecure, and you would never do this for many reasons (cleartext passwords in a database and non-encrypted passwords being transmitted over the internet).
Edit an entry in the blog, with the caveat that the edit webpage will ask for the author's password. Basically all you have to do is again verify the password supplied matches the password in the DB for the author of the post. Duplicate the demo functionality.
Delete an entry in the blog, provided the username and password match the username and password of the author of the post. Again, duplicate the demo functionality.
NEW ITEM: Delete all entries for a user. See the functionality online and duplicate it.
You do not need to add in the functionality of "Re-initialize and re-populate database" that you see online. That option is there so when you practice deleting items, you can get them back easily.

Step 5

When you are all done, zip up the entire blog2 directory and upload it to Moodle.