aboutsummaryrefslogtreecommitdiff
path: root/src
diff options
context:
space:
mode:
Diffstat (limited to 'src')
-rw-r--r--src/guff/database-updater.cr131
-rw-r--r--src/guff/database.cr199
-rw-r--r--src/guff/model.cr12
3 files changed, 342 insertions, 0 deletions
diff --git a/src/guff/database-updater.cr b/src/guff/database-updater.cr
new file mode 100644
index 0000000..e06aff3
--- /dev/null
+++ b/src/guff/database-updater.cr
@@ -0,0 +1,131 @@
+require "./database"
+
+module Guff
+ class DatabaseUpdater
+ CURRENT_VERSION = 2_i64
+
+ SQL = {
+ :get_version => "
+ SELECT value
+ FROM metadata
+ WHERE name = 'version'
+ ",
+ }
+
+ MIGRATION_IDS = %w{
+ 0-null
+ 1-create-tables
+ 2-create-indices
+ }
+
+ 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')
+ }, %{
+ CREATE TABLE tags (
+ tag_id INTEGER PRIMARY KEY,
+ name TEXT UNIQUE NOT NULL
+ )
+ }, %{
+ CREATE TABLE posts (
+ -- unique id
+ post_id INTEGER PRIMARY KEY,
+
+ -- false if this post has been deleted
+ is_active BOOLEAN NOT NULL
+ DEFAULT true,
+
+ -- when this post was created
+ created_at TIMESTAMP WITH TIME ZONE
+ NOT NULL DEFAULT CURRENT_TIMESTAMP,
+
+ -- when this post was posted
+ -- (that is, the draft status was removed)
+ posted_at TIMESTAMP WITH TIME ZONE
+ NOT NULL DEFAULT CURRENT_TIMESTAMP,
+
+ -- title of post
+ name TEXT NOT NULL
+ CHECK (LENGTH(name) > 0)
+
+ -- slug of post (url fragment)
+ slug TEXT NOT NULL
+ CHECK (LENGTH(slug) > 0)
+
+ -- body (raw text before filters)
+ body TEXT NOT NULL
+ CHECK (LENGTH(body) > 0)
+
+ -- generated html (after filters)
+ html TEXT NOT NULL
+ CHECK (LENGTH(html) > 0)
+ )
+ }],
+ },
+
+ "2-create-indices": {
+ backup: false,
+ sql: [%{
+ CREATE INDEX ON tags(name)
+ }, %{
+ CREATE INDEX ON posts(slug)
+ }, %{
+ UPDATE metadata
+ SET value = '2'
+ WHERE name = 'version'
+ }],
+ },
+ }
+
+ def self.run(path, config)
+ new(path, config).run
+ end
+
+ def initialize(
+ @path : String,
+ @config : Config
+ )
+ @db = Database.new(path)
+ end
+
+ def run
+ version = 0_i64
+
+ if @db.table_exists?("metadata")
+ version = @db.one(SQL[:get_version]) as Int64
+ end
+
+ puts "versions: db = %d, code = %d" % [
+ version,
+ CURRENT_VERSION,
+ ]
+
+ if version < CURRENT_VERSION
+ (version + 1).upto(CURRENT_VERSION) do |v|
+ migrate_to(v)
+ end
+ end
+ end
+
+ private def migrate_to(version : Int64)
+ id = MIGRATION_IDS[version]
+ m = MIGRATIONS[id]
+ puts "migrating: %s" % [id]
+
+ (m[:sql] as Array(String)).each do |sql|
+ @db.query(sql)
+ end
+ end
+
+ private def get_version
+ @db.one(:get_version) as Int64
+ end
+ end
+end
diff --git a/src/guff/database.cr b/src/guff/database.cr
index de11eb2..b21a26a 100644
--- a/src/guff/database.cr
+++ b/src/guff/database.cr
@@ -2,6 +2,205 @@ require "sqlite3"
module Guff
class Database < ::SQLite3::Database
+ SQL = {
+ table_exists: "
+ SELECT tbl_name
+
+ FROM sqlite_master
+
+ WHERE type = 'table'
+ AND tbl_name = ?
+ ",
+ }
+
# TODO (add table_exists?)
+ def table_exists?(table_name : String)
+ one(SQL[:table_exists], [table_name]) == table_name
+ end
+
+ def one(
+ sql : String
+ )
+ r = nil
+
+ bind(sql).execute do |rs|
+ if rs.next
+ # FIXME
+ r = rs[0]
+ end
+ end
+
+ # return result
+ r
+ end
+
+ def one(
+ sql : String,
+ args : Array(String)
+ )
+ r = nil
+
+ bind(sql, args).execute do |rs|
+ if rs.next
+ # FIXME
+ r = rs[0]
+ end
+ end
+
+ # return result
+ r
+ end
+
+ def one(
+ sql : String,
+ args : Hash(String, String),
+ )
+ r = nil
+
+ bind(sql, args).execute do |rs|
+ if rs.next
+ # FIXME
+ r = rs[0]
+ end
+ end
+
+ # return result
+ r
+ end
+
+ def row(sql : String)
+ r = nil
+
+ # exec query
+ bind(sql).execute do |rs|
+ if rs.next
+ r = to_row(rs)
+ end
+ end
+
+ # return result
+ r
+ end
+
+ def row(
+ sql : String,
+ args : Array(String)
+ )
+ r = nil
+
+ # exec query
+ bind(sql, args).execute do |rs|
+ if rs.next
+ r = to_row(rs)
+ end
+ end
+
+ # return result
+ r
+ end
+
+ def row(
+ sql : String,
+ args : Hash(String, String)
+ )
+ r = nil
+
+ # exec query
+ bind(sql, args).execute do |rs|
+ if rs.next
+ r = to_row(rs)
+ end
+ end
+
+ # return result
+ r
+ end
+
+ def all(
+ sql : String,
+ args : Array(String),
+ &block : Proc(Hash(String, ::SQLite3::Value), Nil) \
+ )
+ # build statement
+ bind(sql, args).execute do |rs|
+ # walk results
+ while rs.next
+ # build row and pass it to callback
+ block.call(to_row(rs))
+ end
+ end
+
+ nil
+ end
+
+ def all(
+ sql : String,
+ args : Hash(String, String),
+ &block : Proc(Hash(String, ::SQLite3::Value), Nil) \
+ )
+ bind(sql, args).execute do |rs|
+ # walk results
+ while rs.next
+ # build row and pass it to callback
+ block.call(to_row(rs))
+ end
+ end
+
+ # close statement, return nil
+ nil
+ end
+
+ def transaction(&block)
+ query("BEGIN")
+ block.call(self)
+ query("COMMIT")
+ end
+
+ private def bind(sql : String)
+ prepare(sql)
+ end
+
+ private def bind(
+ sql : String,
+ args : Array(String)?
+ )
+ # build statement
+ st = prepare(sql)
+
+ # build args
+ if args.size > 0
+ args.each_with_index do |val, i|
+ st[i + 1] = val
+ end
+ end
+
+ # return result
+ st
+ end
+
+ private def bind(
+ sql : String,
+ args : Hash(String, String)?
+ )
+ # build statement
+ if args.size > 0
+ st = prepare(sql)
+ end
+
+ # bind args
+ st[0] = args
+
+ # return result
+ st
+ 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
+ end
end
end
diff --git a/src/guff/model.cr b/src/guff/model.cr
index 0bc0e78..1df437b 100644
--- a/src/guff/model.cr
+++ b/src/guff/model.cr
@@ -1,3 +1,6 @@
+require "./database"
+require "./database-updater"
+
module Guff
class Model
getter :config
@@ -6,7 +9,16 @@ module Guff
def initialize(@config : Config)
# create site database
db_path = "%s/site.db" % [config["data"]]
+
+ # update database (if necessary)
+ update_db(db_path)
+
+ # open db
@db = Database.new(db_path)
end
+
+ private def update_db(path)
+ DatabaseUpdater.run(path, @config)
+ end
end
end
generated by cgit v1.2.3 (git 2.39.1) at 2024-11-22 04:18:30 +0000