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
|
var db = module.exports
var connection = require("./bookshelf")
var bookshelf = connection.bookshelf
var knex = connection.knex
/* MODELS */
var User = db.User = bookshelf.Model.extend({
tableName: 'users',
hasTimestamps: false,
})
var Thread = db.Thread = bookshelf.Model.extend({
tableName: 'threads',
hasTimestamps: false,
})
var Comment = db.Comment = bookshelf.Model.extend({
tableName: 'comments',
hasTimestamps: false,
})
var File = db.File = bookshelf.Model.extend({
tableName: 'files',
hasTimestamps: false,
})
var Keyword = db.Keyword = bookshelf.Model.extend({
tableName: 'keywords',
hasTimestamps: false,
})
var Mailbox = db.Mailbox = bookshelf.Model.extend({
tableName: 'boxes',
hasTimestamps: false,
})
var Message = db.Message = bookshelf.Model.extend({
tableName: 'messages',
hasTimestamps: false,
})
/* USERS */
db.createUser = function(data){
return new db.User(data).save()
}
db.getUsers = function () {
return User.query(function(qb){
qb.orderBy("id", "desc")
}).fetchAll()
}
db.getUser = function(id) {
var model = new User({'id': id})
return model.fetch()
}
db.getUserByUsername = function(username) {
var model = new User({'username': username})
return model.fetch()
}
db.getLastlog = function(limit){
return knex.column('username').column('lastseen').select().from('users').orderBy('lastseen', 'desc').limit(limit || 10)
}
/* THREADS */
db.getLatestThreads = function () {
return Thread.query(function(qb){
qb.orderBy("id", "desc").limit(50)
}).fetchAll()
}
db.getThreadsForKeyword = function (keyword) {
return Thread.query(function(qb){
qb.where("keyword", "=", keyword).orderBy("id", "desc")
}).fetchAll()
}
db.getThread = function (id) {
return Thread.query("where", "id", "=", id).fetch()
}
/* FILES */
db.getFilesForThread = function (id){
return File.query("where", "thread", "=", id).fetchAll()
}
db.getFileCounts = function(ids){
return knex.column('thread').count('* as count').select().from('files').where('thread', 'in', ids).groupBy('thread')
}
db.getFileSizes = function(ids){
return knex.column('thread').sum('size as size').select().from('files').where('thread', 'in', ids).groupBy('thread')
}
/* COMMENTS */
db.getCommentsForThread = function (id, limit, offset){
return Comment.query(function(qb){
qb.where("thread", "=", id).orderBy("id", "asc")
if (limit) {
qb = qb.limit(limit)
}
if (offset) {
qb = qb.offset(offset)
}
}).fetchAll().then(function(comments){
comments.forEach(function(comment){
comment.set("comment", comment.get("comment").toString() )
})
return comments
})
}
db.getCommentCounts = function(ids){
return knex.column('thread').count('* as count').select().from('comments').where('thread', 'in', ids).groupBy('thread')
}
/* KEYWORDS */
db.getKeywords = function (keywords){
return Keyword.query("where", "keyword", "in", keywords).fetchAll()
}
db.getKeyword = function (keyword) {
return Keyword.query("where", "keyword", "=", keyword).fetch()
}
/* PRIVATE MESSAGES */
db.getMessage = function (id){
var model = new Message({'id': id})
return model.fetch().then(function(message){
message.set("body", message.get("body").toString() )
return message
})
}
|
