all repos — litestore @ e25b0330e0df0142e2269ee332b687e7bcb7ef57

A minimalist nosql document store.

queries.nim

 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
import db_sqlite


# SQL QUERIES

const SQL_COUNT_DOCUMENTS* = sql"""
SELECT COUNT(id) FROM documents
"""

const SQL_COUNT_TAGS* = sql"""
SELECT COUNT(DISTINCT tag_id) FROM tags 
"""

const SQL_CREATE_DOCUMENTS_TABLE* = sql"""
CREATE TABLE documents (
id TEXT PRIMARY KEY,
data TEXT,
content_type TEXT,
binary INTEGER,
searchable INTEGER,
created TEXT,
modified TEXT)
"""

const SQL_CREATE_SEARCHCONTENTS_TABLE* = sql"""
CREATE VIRTUAL TABLE searchcontents USING fts4(
document_id TEXT,
content TEXT)
"""

const SQL_CREATE_TAGS_TABLE* = sql"""
CREATE TABLE tags (
tag_id TEXT,
document_id TEXT,
PRIMARY KEY (tag_id, document_id))
"""

const SQL_INSERT_DOCUMENT* = sql"""
INSERT INTO documents
(id, data, content_type, binary, searchable, created)
VALUES (?, ?, ?, ?, ?, ?)
"""

const SQL_UPDATE_DOCUMENT* = sql"""
UPDATE documents
SET data = ?,
content_type = ?,
binary = ?,
searchable = ?,
modified = ?
WHERE id = ?
"""

const SQL_DELETE_DOCUMENT* = sql"""
DELETE FROM documents
WHERE id = ? 
"""

const SQL_INSERT_TAG* = sql"""
INSERT INTO tags
(tag_id, document_id)
VALUES (?, ?)
"""

const SQL_DELETE_TAG* = sql"""
DELETE FROM tags
WHERE document_id = ? AND tag_id = ?
"""

const SQL_DELETE_DOCUMENT_TAGS* = sql"""
DELETE FROM tags
WHERE document_id = ?
"""

const SQL_SELECT_DOCUMENT_TAGS* = sql"""
SELECT tag_id FROM tags
WHERE document_id = ?
"""

const SQL_DELETE_DOCUMENT_SYSTEM_TAGS* = sql"""
DELETE FROM tags WHERE
document_id = ? AND tag_id LIKE "$%"
"""

const SQL_INSERT_SEARCHCONTENT* = sql"""
INSERT INTO searchcontents
(document_id, content)
VALUES (?, ?)
"""

const SQL_DELETE_SEARCHCONTENT* = sql"""
DELETE FROM searchcontents WHERE
document_id = ?
"""

const SQL_UPDATE_SEARCHCONTENT* = sql"""
UPDATE searchcontents
SET content = ?
WHERE document_id = ?
"""

const SQL_SELECT_DOCUMENTS_BY_TAG* = sql"""
SELECT * FROM documents, tags
WHERE documents.id = tags.document_id AND
tag_id = ?
"""

const SQL_SELECT_DOCUMENT_IDS_BY_TAG* = sql"""
SELECT id FROM documents, tags
WHERE documents.id = tags.document_id AND
tag_id = ?
"""