Example

Let's define a model

This is a simple model. There are four new types to introduce: Model, ForeignKey, DBId and Foreign.

  • Model is the abstact type every struct to be persisted should inherit from
  • ForeignKey is a generic type that represents a reference to other Model
  • DBId is other generic type that encodes the struct identifier.
  • Foreign is used as datatype in the constructor of a struct that contains a ForeignKey field. That datatype is bit of hack I don't like, but I couldn't find a better way.

Each type <: Model must have a construcor with named parameters.

The DBMapper type is the main type of the library. The mapper is constructed with a function passed as an argument.

using StructDatabaseMapping
using Dates
using SQLite
using Test

mutable struct Author <: Model
    id::DBId{Integer}
    name::String
    age::Integer
    date::DateTime
end
function Author(;id::Union{Integer, Nothing} = nothing,
                name::String="",
                age::Integer=0,
                date::DateTime=now())
    return Author(id, name, age, date)
end
mutable struct Book <: Model
    id::DBId{String}
    author::ForeignKey{Author}
    data::Dict{String, Integer}
end
function Book(;id::Union{String, Nothing}=nothing,
               author::Foreign{Author}=Author(),
               data::Dict{String, Integer}=Dict())
    return Book(id, author, data)
end

First we should create the DBMapper and register our types

DB_FILE = "test_db"
using SQLite
creator = ()->SQLite.DB(DB_FILE)
mapper = DBMapper(creator)

register!(mapper, Author)
register!(mapper, Book)

configure_relation(mapper, Book, :author, on_delete=Cascade())

@test haskey(mapper.tables, Author)
@test haskey(mapper.tables, Book)

create_table(mapper, Author)
create_table(mapper, Book)
[ Info: CREATE TABLE IF NOT EXISTS author (age INTEGER NOT NULL, date DATETIME NOT NULL, id INTEGER PRIMARY KEY, name VARCHAR NOT NULL)
[ Info: CREATE TABLE IF NOT EXISTS book (author_id INTEGER NOT NULL, data JSON NOT NULL, id VARCHAR PRIMARY KEY, FOREIGN KEY(author_id) REFERENCES author(id) ON DELETE CASCADE ON UPDATE NO ACTION)
author = Author(name="pirulo", age=50)
insert!(mapper, author)
@test !isnothing(getid(author, mapper))
println(author)
┌ Info: INSERT INTO author (age,date,name)
└ VALUES (?,?,?)
Main.ex-code_1.Author(DBId{Integer}(1), "pirulo", 50, 2020-04-03T23:09:37.025)
id = getid(author, mapper)
a = select_one(mapper, Author, id=999)
println(a)
┌ Info: SELECT age, date, id, name
│ FROM author
│
└ WHERE id=? LIMIT 1
nothing
a = select_one(mapper, Author, id=id)
println(a)
┌ Info: SELECT age, date, id, name
│ FROM author
│
└ WHERE id=? LIMIT 1
Main.ex-code_1.Author(DBId{Integer}(1), "pirulo", 50, 2020-04-03T23:09:37.025)

Existence

author = Author(name="Author 1", age=2)
insert!(mapper, author)

author = Author(name="Author 2", age=3)
insert!(mapper, author)

author = Author(name="Author 3", age=4)
insert!(mapper, author)

println(exists(mapper, Author, name="Enrique Banch"))
println(exists(mapper, Author, name="pirulo"))
println(exists(mapper, author))

println(exists(mapper, Author, name="Author 3", age=4))
println(exists(mapper, Author, name="Author 3", age=3))
println(exists(mapper, Author, pk=author.id.x, age=3))
println(exists(mapper, Author, pk=author.id.x, age=4))
┌ Info: INSERT INTO author (age,date,name)
└ VALUES (?,?,?)
┌ Info: INSERT INTO author (age,date,name)
└ VALUES (?,?,?)
┌ Info: INSERT INTO author (age,date,name)
└ VALUES (?,?,?)
┌ Info: SELECT COUNT(1) as count
│ FROM author
└ WHERE name = ?
false
┌ Info: SELECT COUNT(1) as count
│ FROM author
└ WHERE name = ?
true
┌ Info: SELECT COUNT(1) as count
│ FROM author
└ WHERE id = ?
true
┌ Info: SELECT COUNT(1) as count
│ FROM author
└ WHERE age = ? AND name = ?
true
┌ Info: SELECT COUNT(1) as count
│ FROM author
└ WHERE age = ? AND name = ?
false
┌ Info: SELECT COUNT(1) as count
│ FROM author
└ WHERE age = ? AND id = ?
false
┌ Info: SELECT COUNT(1) as count
│ FROM author
└ WHERE age = ? AND id = ?
true

Update

the function update! receives a named argument fields that indicates the fields to be updated

a.name = "otro_pirulo"
a.age = 5
update!(mapper, a; fields=[:name])
a = select_one(mapper, Author, id=id)
println(a)
┌ Info: UPDATE author
│ SET name=?
│ WHERE
└ id = ?
┌ Info: SELECT age, date, id, name
│ FROM author
│
└ WHERE id=? LIMIT 1
Main.ex-code_1.Author(DBId{Integer}(1), "otro_pirulo", 50, 2020-04-03T23:09:37.025)

If fields is omitted all the fields are updated

a.name = "some_other_name"
a.age = 5
update!(mapper, a)
a = select_one(mapper, Author, id=id)
println(a)
┌ Info: UPDATE author
│ SET age=?,date=?,name=?
│ WHERE
└ id = ?
┌ Info: SELECT age, date, id, name
│ FROM author
│
└ WHERE id=? LIMIT 1
Main.ex-code_1.Author(DBId{Integer}(1), "some_other_name", 5, 2020-04-03T23:09:37.025)

Insert element with foreign key and dict

book = Book(id="super_string_id", author=author,
            data=Dict{String, Integer}("some_data"=>5))
insert!(mapper, book)
Main.ex-code_1.Book(DBId{String}("super_string_id"), ForeignKey{Main.ex-code_1.Author}(Main.ex-code_1.Author(DBId{Integer}(4), "Author 3", 4, 2020-04-03T23:09:40.302), true), Dict{String,Integer}("some_data" => 5))
book = select_one(mapper, Book, id="bbb")
println(book)
┌ Info: SELECT author_id, data, id
│ FROM book
│
└ WHERE id=? LIMIT 1
nothing
book = select_one(mapper, Book, id="super_string_id")
println(book)
┌ Info: SELECT author_id, data, id
│ FROM book
│
└ WHERE id=? LIMIT 1
Main.ex-code_1.Book(DBId{String}("super_string_id"), ForeignKey{Main.ex-code_1.Author}(Main.ex-code_1.Author(DBId{Integer}(4), "", 0, 2020-04-03T23:09:43.115), false), Dict{String,Integer}("some_data" => 5))

Delete

book = select_one(mapper, Book, id="super_string_id")
delete!(mapper, book)
┌ Info: SELECT author_id, data, id
│ FROM book
│
└ WHERE id=? LIMIT 1
┌ Info: DELETE FROM book
└ WHERE id=?

Removing tables

drop_table!(mapper, Author)
drop_table!(mapper, Book)
[ Info: DROP TABLE author
[ Info: DROP TABLE book