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