Skip to content

Embed URL

HTTPS clone URL

Subversion checkout URL

You can clone with
or
.
Download ZIP
Play 2.0/Anorm
# --- !Ups
CREATE TABLE users(
email VARCHAR(255) NOT NULL PRIMARY KEY,
name VARCHAR(255)
);
CREATE TABLE subjects(
id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
title LONGTEXT NOT NULL,
summary LONGTEXT NOT NULL,
imageUrl TEXT,
author VARCHAR(255) not null,
FOREIGN KEY (author) REFERENCES users(email) ON DELETE RESTRICT
);
CREATE TABLE events(
id INTEGER NOT NULL AUTO_INCREMENT primary key,
title LONGTEXT NOT NULL,
summary LONGTEXT NOT NULL,
start BIGINT,
end BIGINT,
place TEXT,
imageUrl TEXT,
author VARCHAR(255) NOT NULL,
FOREIGN KEY (author) REFERENCES users(email) ON DELETE RESTRICT
);
CREATE TABLE events_subjects(
subject_id INTEGER NOT NULL,
event_id INTEGER NOT NULL,
PRIMARY KEY (subject_id, event_id),
FOREIGN KEY (subject_id) REFERENCES subjects(id) ON DELETE CASCADE,
FOREIGN KEY (event_id) REFERENCES events(id) ON DELETE CASCADE
);
CREATE TABLE subscribers(
user_email VARCHAR(255) NOT NULL,
event_id INTEGER NOT NULL,
PRIMARY KEY (user_email, event_id),
FOREIGN KEY (user_email) REFERENCES users(email) ON DELETE CASCADE,
FOREIGN KEY (event_id) REFERENCES events(id) ON DELETE CASCADE
);
CREATE TABLE followers(
user_email VARCHAR(255) NOT NULL,
subject_id INTEGER NOT NULL,
PRIMARY KEY (user_email, subject_id),
FOREIGN KEY (user_email) REFERENCES users(email) ON DELETE CASCADE,
FOREIGN KEY (subject_id) REFERENCES subjects(id) ON DELETE CASCADE
);
CREATE TABLE comments(
id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
content LONGTEXT NOT NULL,
postedAt BIGINT,
event_id INTEGER,
subject_id INTEGER,
author VARCHAR(255) NOT NULL,
FOREIGN KEY (author) REFERENCES users(email) ON DELETE CASCADE,
FOREIGN KEY (event_id) REFERENCES events(id) ON DELETE CASCADE,
FOREIGN KEY (subject_id) REFERENCES subjects(id) ON DELETE CASCADE
);
CREATE TABLE logs(
id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
at BIGINT NOT NULL,
kind VARCHAR(255) NOT NULL,
user_id VARCHAR(255) NOT NULL,
event_id INTEGER,
subject_id INTEGER,
FOREIGN KEY (user_id) REFERENCES users(email) ON DELETE CASCADE,
FOREIGN KEY (event_id) REFERENCES events(id) ON DELETE CASCADE,
FOREIGN KEY (subject_id) REFERENCES subjects(id) ON DELETE CASCADE
);
# --- !Downs
DROP TABLE IF EXISTS logs;
DROP TABLE IF EXISTS comments;
DROP TABLE IF EXISTS followers;
DROP TABLE IF EXISTS subscribers;
DROP TABLE IF EXISTS events_subjects;
DROP TABLE IF EXISTS subjects;
DROP TABLE IF EXISTS events;
DROP TABLE IF EXISTS users;
package models
import play.api._
import play.api.db._
import play.api.Play.current
import anorm._
import anorm.SqlParser._
import java.util.Date
// -- Users
case class User(email: String, name: Option[String]) {
lazy val myEvents: Seq[Event] = DB.withConnection { implicit connection =>
SQL(
"""
select * from events
join users on events.author = users.email
join subscribers on subscribers.event_id = events.id
where subscribers.user_email = {myEmail}
"""
).on(
'myEmail -> email
).as(Event.withAuthor *)
}
lazy val mySubjects: Seq[Subject] = DB.withConnection { implicit connection =>
SQL(
"""
select * from subjects
join users on subjects.author = users.email
join followers on followers.subject_id = subjects.id
where followers.user_email = {myEmail}
"""
).on(
'myEmail -> email
).as(Subject.withAuthor *)
}
}
object User {
val simple = {
get[String]("users.email") ~
get[Option[String]]("users.name") map {
case email ~ name => User(email, name)
}
}
def authenticate(email: String, name: Option[String] = None): User = DB.withConnection { implicit connection =>
SQL(
"""
INSERT INTO users (email,name) VALUES ({email},{name})
ON DUPLICATE KEY UPDATE name = {name}
"""
).on(
'email -> email,
'name -> name
).executeUpdate()
User(email, name)
}
def findByEmail(email: String): Option[User] = DB.withConnection { implicit connection =>
SQL(
"""
select * from users where email = {email}
"""
).on(
'email -> email
).as(User.simple.singleOpt)
}
}
// -- Subjects
case class Subject(id: Long, title: String, summary: String, imageUrl: Option[String], author: Option[User] = None) {
lazy val followers: Seq[User] = DB.withConnection { implicit connection =>
SQL(
"""
select * from users
join followers on followers.user_email = users.email
where followers.subject_id = {id}
"""
).on(
'id -> id
).as(User.simple *)
}
lazy val comments: Seq[Comment] = DB.withConnection { implicit connection =>
SQL(
"""
select * from comments
join subjects on comments.subject_id = subjects.id
where subjects.id = {id}
"""
).on(
'id -> id
).as(Comment.simple *)
}
}
object Subject {
val simple = {
get[Long]("subjects.id") ~
get[String]("subjects.title") ~
get[String]("subjects.summary") ~
get[Option[String]]("subjects.imageUrl") map {
case id~title~summary~image => Subject(id, title, summary, image)
}
}
val withAuthor = {
simple ~ User.simple map {
case subject~author => subject.copy(author = Some(author))
}
}
def findAll: Seq[Subject] = DB.withConnection { implicit connection =>
SQL(
"""
select * from subjects join users on subjects.author = users.email
"""
).as(Subject.withAuthor *)
}
def findById(id: Long): Option[Subject] = DB.withConnection { implicit connection =>
SQL(
"""
select * from subjects join users on subjects.author = users.email where subjects.id = {id}
"""
).on('id -> id).as(Subject.withAuthor.singleOpt)
}
}
// -- Events
case class Event(id: Long, title: String, summary: String, place: String, start: Option[Date], end: Option[Date], imageUrl: Option[String], author: Option[User] = None) {
lazy val subscribers: Seq[User] = DB.withConnection { implicit connection =>
SQL(
"""
select * from users
join subscribers on subscribers.user_email = users.email
where subscribers.event_id = {id}
"""
).on(
'id -> id
).as(User.simple *)
}
lazy val comments: Seq[Comment] = DB.withConnection { implicit connection =>
SQL(
"""
select * from comments
join events on comments.event_id = events.id
join users on comments.author = users.email
where events.id = {id}
"""
).on(
'id -> id
).as(Comment.withAuthor *)
}
}
object Event {
val simple = {
get[Long]("events.id") ~
get[String]("events.title") ~
get[String]("events.summary") ~
get[String]("events.place") ~
get[Option[Long]]("events.start") ~
get[Option[Long]]("events.end") ~
get[Option[String]]("events.imageUrl") map {
case id~title~summary~place~start~end~image => Event(id, title, summary, place, start.map(new Date(_)), end.map(new Date(_)), image)
}
}
val withAuthor = {
simple ~ User.simple map {
case event~author => event.copy(author = Some(author))
}
}
val withAuthorAndSubjects = {
withAuthor ~ Subject.simple map {
case event~subject => (event,subject)
}
}
def next: Option[Event] = DB.withConnection { implicit connection =>
SQL(
"""
select * from events where events.start > {now} order by start limit 0,1
"""
).on('now -> new Date().getTime()).as(Event.simple.singleOpt)
}
def findAll: Seq[Event] = DB.withConnection { implicit connection =>
SQL(
"""
select * from events
join users on events.author = users.email
"""
).as(Event.withAuthor *)
}
def findById(id: Long): Option[(Event,Seq[Subject])] = DB.withConnection { implicit connection =>
SQL(
"""
select * from events
join users on events.author = users.email
join events_subjects on events.id = events_subjects.event_id
join subjects on subjects.id = events_subjects.subject_id
where events.id = {id}
"""
).on('id -> id).as(Event.withAuthorAndSubjects *).groupBy(_._1).headOption.map {
case (event, eventWithSubjects) => (event, eventWithSubjects.map(_._2))
}
}
}
// -- Comments
case class Comment(id: Long, content: String, date: Date, author: Option[User] = None)
object Comment {
val simple = {
get[Long]("comments.id") ~
get[String]("comments.content") ~
get[Long]("comments.postedAt") map {
case id~content~postedAt => Comment(id, content, new Date(postedAt))
}
}
val withAuthor = {
simple ~ User.simple map {
case comment ~ author => comment.copy(author = Some(author))
}
}
}
// -- Wall
case class Log(date: Date, kind: String, user: User, event: Option[Event], subject: Option[Subject])
object Log {
val simple = {
get[Long]("logs.at") ~
get[String]("logs.kind") ~
User.simple ~
(Event.simple?) ~
(Subject.simple?) map {
case at~kind~user~event~subject => Log(new Date(at), kind, user, event, subject)
}
}
def wall: Seq[Log] = DB.withConnection { implicit connection =>
SQL(
"""
select * from logs
join users on logs.user_id = users.email
left join events on events.id = logs.event_id
left join subjects on subjects.id = logs.subject_id
order by logs.at desc
limit 0, 20
"""
).as(Log.simple *)
}
}
@jkransen

Thank you, this is very informative. It even partially makes up for the poor Anorm documentation :-)

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Something went wrong with that request. Please try again.