aboutsummaryrefslogtreecommitdiff
path: root/src
diff options
context:
space:
mode:
Diffstat (limited to 'src')
-rw-r--r--src/guff/database-updater.cr71
-rw-r--r--src/guff/database.cr141
2 files changed, 90 insertions, 122 deletions
diff --git a/src/guff/database-updater.cr b/src/guff/database-updater.cr
index 9a7bf97..b7a8af6 100644
--- a/src/guff/database-updater.cr
+++ b/src/guff/database-updater.cr
@@ -5,14 +5,22 @@ module Guff
CURRENT_VERSION = 2_i64
SQL = {
- :get_version => "
+ get_version: "
SELECT value
FROM metadata
WHERE name = 'version'
",
+
+ set_version_delete: "
+ DELETE FROM metadata where name = 'version'
+ ",
+
+ set_version_insert: "
+ INSERT INTO metadata(name, value) VALUES ('version', ?)
+ ",
}
- MIGRATION_IDS = %w{
+ IDS = %w{
0-null
1-create-tables
2-create-indices
@@ -21,13 +29,12 @@ module Guff
MIGRATIONS = {
"1-create-tables": {
backup: false,
+
sql: [%{
CREATE TABLE metadata (
name TEXT PRIMARY KEY,
- value TEXT NOT NULL
- );
- }, %{
- INSERT INTO metadata(name, value) VALUES ('version', '1')
+ value TEXT NOT NULL
+ )
}, %{
CREATE TABLE tags (
tag_id INTEGER PRIMARY KEY,
@@ -39,7 +46,7 @@ module Guff
post_id INTEGER PRIMARY KEY,
-- false if this post has been deleted
- is_active BOOLEAN NOT NULL
+ is_active BOOLEAN NOT NULL
DEFAULT true,
-- when this post was created
@@ -53,15 +60,15 @@ module Guff
-- title of post
name TEXT NOT NULL
- CHECK (LENGTH(name) > 0)
+ CHECK (LENGTH(name) > 0),
-- slug of post (url fragment)
slug TEXT NOT NULL
- CHECK (LENGTH(slug) > 0)
+ CHECK (LENGTH(slug) > 0),
-- body (raw text before filters)
body TEXT NOT NULL
- CHECK (LENGTH(body) > 0)
+ CHECK (LENGTH(body) > 0),
-- generated html (after filters)
html TEXT NOT NULL
@@ -72,17 +79,14 @@ module Guff
"2-create-indices": {
backup: false,
+
sql: [%{
- CREATE INDEX ON tags(name)
+ CREATE INDEX in_tags_name ON tags(name)
}, %{
- CREATE INDEX ON posts(slug)
- }, %{
- UPDATE metadata
- SET value = '2'
- WHERE name = 'version'
+ CREATE INDEX in_posts_slug ON posts(slug)
}],
},
- }
+ }
def self.run(path, config)
new(path, config).run
@@ -92,17 +96,14 @@ module Guff
@path : String,
@config : Config
)
- @db = Database.new(path)
+ @db = Database.new(@path)
end
def run
- version = 0_i64
-
- if @db.table_exists?("metadata")
- version = @db.one(SQL[:get_version]) as Int64
- end
+ # get version from database
+ version = @db.table_exists?("metadata") ? get_version : 0_i32
- puts "versions: db = %d, code = %d" % [
+ puts "versions: db = %d, app = %d" % [
version,
CURRENT_VERSION,
]
@@ -114,18 +115,30 @@ module Guff
end
end
- private def migrate_to(version : Int64)
- id = MIGRATION_IDS[version]
+ private def migrate_to(version)
+ # map version number to migration id
+ id = IDS[version]
m = MIGRATIONS[id]
puts "migrating: %s" % [id]
- (m[:sql] as Array(String)).each do |sql|
- @db.query(sql)
+ @db.transaction do
+ # exec queries
+ (m[:sql] as Array(String)).each do |sql|
+ @db.query(sql)
+ end
+
+ # update database version
+ set_version(version)
end
end
private def get_version
- @db.one(:get_version) as Int64
+ (@db.one(SQL[:get_version]) || 0).to_i
+ end
+
+ private def set_version(version)
+ @db.query(SQL[:set_version_delete])
+ @db.query(SQL[:set_version_insert], [version])
end
end
end
diff --git a/src/guff/database.cr b/src/guff/database.cr
index e94a085..67a7a1a 100644
--- a/src/guff/database.cr
+++ b/src/guff/database.cr
@@ -4,60 +4,28 @@ module Guff
class Database < ::SQLite3::Database
SQL = {
table_exists: "
- SELECT tbl_name
+ SELECT name
FROM sqlite_master
WHERE type = 'table'
- AND tbl_name = ?
+ AND name = ?
",
}
- # TODO (add table_exists?)
- def table_exists?(table_name : String)
- one(SQL[:table_exists], [table_name]) == table_name
+ def table_exists?(table : String) : Bool
+ one(SQL[:table_exists], [table]) == table
end
def one(
- sql : String
- )
- r = nil
-
- run(sql).execute do |rs|
- if rs.next
- # FIXME
- r = rs[0]
- end
- end
-
- # return result
- r
- end
-
- def one(
- sql : String,
- args : Array(String) | Hash(String, String)
+ sql : String,
+ args = nil : Array(String) | Hash(String, String) | Nil
)
r = nil
- run(sql, args).execute do |rs|
+ run(sql, args) do |rs|
if rs.next
- # FIXME
- r = rs[0]
- end
- end
-
- # return result
- r
- end
-
- def row(sql : String)
- r = nil
-
- # exec query
- run(sql).execute do |rs|
- if rs.next
- r = to_row(rs)
+ r = rs[0] as String
end
end
@@ -66,16 +34,14 @@ module Guff
end
def row(
- sql : String,
- args : Array(String) | Hash(String, String)
+ sql : String,
+ args = nil : Array(String) | Hash(String, String) | Nil
)
r = nil
# exec query
- run(sql, args).execute do |rs|
- if rs.next
- r = to_row(rs)
- end
+ run(sql, args) do |rs|
+ r = to_row(rs) if rs.next
end
# return result
@@ -83,10 +49,12 @@ module Guff
end
def all(
- sql : String,
- &block : Proc(Hash(String, ::SQLite3::Value), Nil) \
+ sql : String,
+ args = nil : Array(String) | Hash(String, String) | Nil,
+ &block : Proc(Hash(String, ::SQLite3::Value), Nil) \
)
- run(sql).execute do |rs|
+ # build statement
+ run(sql, args) do |rs|
# walk results
while rs.next
# build row and pass it to callback
@@ -94,79 +62,66 @@ module Guff
end
end
- # close statement, return nil
nil
end
- def all(
- sql : String,
- args : Array(String) | Hash(String, String),
- &block : Proc(Hash(String, ::SQLite3::Value), Nil) \
+ def query(
+ sql : String
)
- # build statement
- run(sql, args).execute do |rs|
- # walk results
- while rs.next
- # build row and pass it to callback
- block.call(to_row(rs))
- end
+ run(sql, nil) do |rs|
+ # make sure query executes
+ rs.next
+ nil
end
+ end
- nil
+ #
+ # NOTE: if you pass a block, be sure to call rs.next at least once,
+ # or the query will _not_ execute!!!
+ #
+ def query(
+ sql : String,
+ args = nil : Array(String) | Hash(String, String) | Nil,
+ &block : Proc(::SQLite3::ResultSet, Nil) \
+ )
+ run(sql, args, &block)
end
def transaction(&block)
query("BEGIN")
- block.call(self)
+ block.call
query("COMMIT")
end
- private def run(sql : String)
- # build statement
- prepare(sql)
- end
-
private def run(
sql : String,
- args : Array(String)?
+ args : Hash(String, String),
+ &block : Proc(::SQLite3::ResultSet, Nil) \
)
- # build statement
- st = prepare(sql)
-
- # bind args
- if args && args.size > 0
- args.each_with_index do |val, i|
- st[i + 1] = val
- end
- end
-
- # return result
- st
+ run(sql, [args], &block)
end
private def run(
- sql : String,
- args : Hash(String, String)?
+ sql : String,
+ args = nil : Array(String | Hash(String, String))?,
+ &block : Proc(::SQLite3::ResultSet, Nil) \
)
# build statement
st = prepare(sql)
- # bind args
+ # exec and close statement
if args && args.size > 0
- st[0] = args
+ st.execute(args, &block)
+ else
+ st.execute(&block)
end
# return result
- st
+ nil
end
- private def to_row(rs : SQLite3::ResultSet) : Hash(String, ::SQLite3::Value)
- 0.upto(rs.column_count).inject(
- {} of String => ::SQLite3::Value
- ) do |r, i|
- r[rs.columns[i]] = r[i]
- r
- end
+ private def to_row(rs)
+ Hash(String, ::SQLite3::Value).zip(rs.columns, rs.to_a)
end
end
end