<<

. 61
( 132 .)



>>

352 Part IV: Not So Simple Applications




Figure 11-3: Rights and stages page




Figure 11-4: Rights-administration page
Chapter 11: Content-Management System 353




Figure 11-5: Stages administration page


This application also needs a workspace, a page where writers and editors can
create stories, and where stories can work their way through the editorial process.
The workspace will contain a few fields that identify the author, the date, the body
of text, and other necessary information. Additionally, the stage of the editorial
process that the story is in is indicated. This page is shown in Figure 11-6.
Another important aspect of an editorial environment is versioning. It™s very
important to be able to track pieces as they work through the process. We™ll want to
know who is making changes. Figure 11-7 shows the page that tracks versions, or
the story-history page.
This application performs a few more tasks, but they are minor enough to over-
look here. Here we have touched on the major functions of the application.

What do we need to prevent?
The major issue in this application is ensuring that users do only what they are per-
mitted to do, and absolutely no more. To do this, the application makes use of
MySQL administrative privileges.
All the previous applications have a simple header file that calls a function with
which to log in to the database. Each file ends up using the same my_connect()
call, with the same username and password. But that won™t work here because dif-
ferent users need different levels of access.
354 Part IV: Not So Simple Applications




Figure 11-6: Editorial workplace




Figure 11-7: Story-history page
Chapter 11: Content-Management System 355

Moreover, in this application some users are going to need the ability to grant
access to others. Workers will come and go and their responsibilities will change. An
administrator will need to be able to change these rights. Since we don™t want every-
body who logs in to the database to have the same rights, this application will need
the facility to have different people log in using different names and passwords.
Privileges in MySQL are granted and revoked with the aptly named grant and
revoke statements. These processes are fairly painless and are described in
Appendix E. So before you move forward with this application, it might be worth
taking a quick look at that appendix.


In the content-management application you will run into some of the
weirder aspects of MySQL. If some of the design of this application seems a
little strange, that™s because it is. But we™ll cross that bridge as we develop
our application.




Designing the Database
The schema represented in Figure 11-8 shows how this application divides its data,
and Listing 11-1 reveals the MySQL command sequence used to set it up. Keep in
mind as you look at it that in database-development land there is usually more
than one decent way to go about things. You might find a different way to arrange
these types of data that works equally well. In fact, you may even prefer another
way. That™s fine with us. We encourage independent thought and creativity, as long
as it does not result in immoral or ungodly behavior. So normalize your data as you
see fit, but in the process please don™t violate any natural laws. On the other hand,
data normalization makes it easier to grow your databases and adapt them to new
purposes. Bear that in mind, too.

Listing 11-1: create Statements for the Content-Management System
drop database if exists netsloth;
create database netsloth;
use netsloth;

drop table if exists admin;
create table admin
(
username varchar(50) not null
, password varchar(255) not null
, primary key (username)
)
type=InnoDB

Continued
356 Part IV: Not So Simple Applications

Listing 11-1 (Continued)
;

drop table if exists author_seq;
create table author_seq
(
id int not null auto_increment
, primary key (id)
)
type=InnoDB
;
drop table if exists authors;
create table authors
(
author_id integer not null auto_increment
, author varchar(50) null
, email varchar(255) null
, bio text null
, user_id int null
, primary key (author_id)
, key (user_id)
, foreign key (user_id) references users (user_id) on delete cascade
)
type=InnoDB
;
-- read-write tables
grant select,update,insert,delete on stories to fake@localhost;
grant select,update,insert,delete on story_seq to fake@localhost;
grant select,update,insert,delete on story_versions to
fake@localhost;
grant select,update,insert,delete on authors to fake@localhost;
grant select,update,insert,delete on author_seq to fake@localhost;
grant select,update,insert,delete on story_author_map to
fake@localhost;

-- read-only tables
grant select on admin to fake@localhost;
grant select on users to fake@localhost;
grant select on stages to fake@localhost;
grant select on user_stage_map to fake@localhost;

-- build local copy
drop table if exists content_tables;
create table content_tables as
select Table_name, Table_priv
from mysql.tables_priv
where Host = ˜localhost™ and Db = ˜netsloth™ and User = ˜fake™
;
Chapter 11: Content-Management System 357

alter table content_tables add unique (Table_name);

-- delete the slug
delete from mysql.user
where Host = ˜localhost™ and User = ˜fake™
;
delete from mysql.tables_priv
where Host = ˜localhost™ and Db = ˜netsloth™ and User = ˜fake™
;


content_admin

username
password


authors stories

story_id
author_id
stage_id
story_author_map story_versions
author
publish_dt
email
story_id
story_id headline
modify_dt
author_id subtitle
modify_by
byline_prefix
stage_id
summary
publish_dt
body
headline
subtitle
byline_prefix
summary
body

content_users content_stages
user_stage_map
user_id stage_id
user_id
username stage
stage_id
name stage_dsc
email


editing_stories killed_stories proofreading_stories
Note: Each of these
tables corresponds to story_id story_id story_id
an entry in the
content_stages table.
live_stories writing_stories
The tables will change
as stages are added
story_id story_id
and deleted.

Figure 11-8: Content management schema
358 Part IV: Not So Simple Applications

(In these lines, up to ˜flush privileges™, we are setting up basic permissions
for the database, where ˜nobody™ is the account used by the NetSloth site itself,
and ˜content_admin™ is an example of an account for an administrator of the
content management application itself.)

delete from mysql.user where user = ˜content_admin™;
delete from mysql.db where Db = ˜netsloth™;
delete from mysql.tables_priv where Db = ˜netsloth™;
grant select on netsloth.*
to nobody@localhost
identified by ˜ydobon™
;
grant reload on *.*
to content_admin@localhost identified by ˜supersecret™
;
grant delete, insert, select, update, create
on netsloth.*
to content_admin@localhost
with grant option
;
grant delete, insert, select, update, create on mysql.user to
content_admin@localhost ;
grant delete, insert, select, update, create on mysql.db to
content_admin@localhost ;
grant delete, insert, select, update, create on mysql.columns_priv
to content_admin@localhost ;
grant delete, insert, select, update, create on mysql.tables_priv to
content_admin@localhost ;

flush privileges;

drop table if exists stage_seq;
create table stage_seq
(
id int not null auto_increment
, primary key (id)
)
type=InnoDB
;
drop table if exists stages;
create table stages
(
stage_id integer not null auto_increment
, stage varchar(20) not null
, stage_dsc text null
Chapter 11: Content-Management System 359

, stage_table varchar(32) not null
, primary key (stage_id)
)
type=InnoDB
;

drop table if exists stories;

<<

. 61
( 132 .)



>>