Chapter 11

Issue here is that the microblog has no processing. The classes tend to be rather anemic.

The upside is that it has all of the relevant relationships So it shows SQL key handling nicely.

If we add, say, Lat/Lon for geolocating each blog post, then, we might have something more sophisticated. The Lat/Lon point and differences between lat/lon points becomes a much more interesting object. Much more interesting.

Part I. SQL Basics

Some Example Table Declarations

sql_cleanup="""\
DROP TABLE IF EXISTS BLOG;
DROP TABLE IF EXISTS POST;
DROP TABLE IF EXISTS TAG;
DROP TABLE IF EXISTS ASSOC_POST_TAG;
"""

sql_ddl="""\
CREATE TABLE BLOG(
    ID INTEGER PRIMARY KEY AUTOINCREMENT,
    TITLE TEXT );
CREATE TABLE POST(
    ID INTEGER PRIMARY KEY AUTOINCREMENT,
    DATE TIMESTAMP,
    TITLE TEXT,
    RST_TEXT TEXT,
    BLOG_ID INTEGER REFERENCES BLOG(ID)  );
CREATE TABLE TAG(
    ID INTEGER PRIMARY KEY AUTOINCREMENT,
    PHRASE TEXT UNIQUE ON CONFLICT FAIL );
CREATE TABLE ASSOC_POST_TAG(
        POST_ID INTEGER REFERENCES POST(ID),
        TAG_ID INTEGER REFERENCES TAG(ID) );
"""

import sqlite3
database = sqlite3.connect('p2_c11_blog.db')

database.executescript( sql_cleanup )

for stmt in (stmt.rstrip() for stmt in sql_ddl.split(';')):
    print( stmt )
    database.execute( stmt )

database.close()

Part II. Simple SQL

import datetime

database = sqlite3.connect('p2_c11_blog.db')

get_last_id= """\
SELECT last_insert_rowid()
"""

database.execute( "BEGIN" )

create_blog= """\
INSERT INTO BLOG( TITLE ) VALUES( ? )
"""
database.execute( create_blog, ("Travel Blog",) )
row = database.execute( get_last_id ).fetchone()
blog_id= row[0]

create_post= """\
INSERT INTO POST( DATE, TITLE, RST_TEXT, BLOG_ID ) VALUES( ?, ?, ?, ? )
"""
database.execute( create_post,
    (datetime.datetime(2013,11,14,17,25),
    "Hard Aground",
    """Some embarrassing revelation. Including ☹ and ⚓︎""",
    blog_id) )
row = database.execute( get_last_id ).fetchone()
post_id= row[0]

create_tag= """\
INSERT INTO TAG( PHRASE ) VALUES( ? )
"""
retrieve_tag= """\
SELECT ID, PHRASE FROM TAG WHERE PHRASE = ?
"""
create_tag_post_association= """\
INSERT INTO ASSOC_POST_TAG( POST_ID, TAG_ID ) VALUES ( ?, ? )
"""
for tag in ("#RedRanger", "#Whitby42", "#ICW"):
    row= database.execute( retrieve_tag, (tag,) ).fetchone()
    if row:
        tag_id= row[0]
    else:
        database.execute( create_tag, (tag,) )
        row = database.execute( get_last_id ).fetchone()
        tag_id= row[0]
    database.execute( create_tag_post_association, (post_id, tag_id) )

database.commit()

update_blog="""\
UPDATE BLOG SET TITLE=:new_title WHERE TITLE=:old_title
"""
database.execute( "BEGIN" )
database.execute( update_blog,
    dict(new_title="2013-2014 Travel", old_title="Travel Blog") )
database.commit()

delete_post_tag_by_blog_title= """\
DELETE FROM ASSOC_POST_TAG
WHERE POST_ID IN (
    SELECT DISTINCT POST_ID
    FROM BLOG JOIN POST ON BLOG.ID = POST.BLOG_ID
    WHERE BLOG.TITLE=:old_title)
"""
delete_post_by_blog_title= """\
DELETE FROM POST WHERE BLOG_ID IN (
    SELECT ID FROM BLOG WHERE TITLE=:old_title)
"""
delete_blog_by_title="""\
DELETE FROM BLOG WHERE TITLE=:old_title
"""
database.execute( "BEGIN" )
title= dict(old_title="2013-2014 Travel")
database.execute( delete_post_tag_by_blog_title, title )
database.execute( delete_post_by_blog_title, title )
database.execute( delete_blog_by_title, title )
print( "Post Delete; Pre Commit" )
for row in database.execute( "SELECT * FROM BLOG" ):
    print( row )
for row in database.execute( "SELECT * FROM POST" ):
    print( row )
for row in database.execute( "SELECT * FROM ASSOC_POST_TAG" ):
    print( row )
print( "Rolling Back" )
database.rollback()

query_blog_by_title= """\
SELECT * FROM BLOG WHERE TITLE=?
"""
query_post_by_blog_id= """\
SELECT * FROM POST WHERE BLOG_ID=?
"""
for blog in database.execute( query_blog_by_title, ("2013-2014 Travel",) ):
    print( blog )
    for post in database.execute( query_post_by_blog_id, (blog[0],) ):
        print( post )

for row in database.execute( "SELECT * FROM BLOG" ):
    print( row )
for row in database.execute( "SELECT * FROM POST" ):
    print( row )
for row in database.execute( "SELECT * FROM TAG" ):
    print( row )
for row in database.execute( "SELECT * FROM POST JOIN ASSOC_POST_TAG ON POST.ID=ASSOC_POST_TAG.POST_ID JOIN TAG ON TAG.ID=ASSOC_POST_TAG.TAG_ID" ):
    print( row )

query_blog_post_by_title= """\
SELECT *
FROM BLOG JOIN POST ON BLOG.ID = POST.BLOG_ID
WHERE BLOG.TITLE = ?
"""
for blog_post in database.execute(
    query_blog_post_by_title, ("2013-2014 Travel",) ):
    pass
    # print( [ "{0}={1}".format(k,blog_post[k]) for k in blog_post.keys() ] )