public
Last active

Play 2.0/Anorm

  • Download Gist
1.sql
SQL
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86
# --- !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;
Models.scala
Scala
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284
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 *)
}
}

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

Please sign in to comment on this gist.

Something went wrong with that request. Please try again.