In LainPavot/DBModelR: A Model-Database Interface to Make the Database Layer Abstract

knitr::opts_chunk$set(collapse=TRUE, comment="#>")
options(tibble.print_min=4L, tibble.print_max=4L)
library(RSQLite)
library(DBModelR)
set.seed(1014)

models <- list(
    person=ModelDefinition(
        table="person",
        fields=list(
            name="TEXT",
            family_name="TEXT"
        ), many=list("adress")
        ## One person possibly has multiple adress. Or none...
        ## "many" fields defines the creation of a linkage table
        ## (many_to_many).
        ## "one" fields defines a fk field enforced by a foreign key
        ## restriction (one_to_one)
        ## "many" fields mustn't be duplicated. So, "adress" must not
        ## reference the "person" table.
    ),
    adress=ModelDefinition(
        table="adress",
        fields=list(
            number="INTEGER",
            street="TEXT"
        )
    )
)

DB_PATH <- "./people.sqlite"
if (file.exists(DB_PATH)) {
    file.remove(DB_PATH)
}
orm <- ORM(connection_params=list(DB_PATH), model_definitions=models)

## like this, we'll see the requests generated by the orm
print(orm$create_database())
## the tables has been generated, fks and their restrictions has been
## defined and linked table has been created if necessary

## we've created a person who's name is Alice Smith.
## Alice has been saved into the database.
alice <- orm$person(name="Alice", family_name="smith")$save()

## Alice has been successfully added to and loaded from the database.
print(orm$person()$load_by(name="Alice"))

## The id is 1, because it's the first person to be inserted into the
## "person" table.
## The orm has generated an "INSERT" query.
print(alice$get_id())

## He's a boy, so he's changed his name to "Bob". Suits him better.
## Fields must always be setted with "model$set_field_name(value)".
## otherwise the orm will not see the modifications, and will not save
## them in the database.
bob <- alice$set_name("bob")$save()

## still 1. Because the orm did not add new database entry.
## The orm has generated an "UPDATE" query.
print(bob$get_id())

## prints an empty list()
## no adress has been assigned to him for the moment.
print(bob$get_adress())

## let's give him a home
bob$add_adress(
    ## the orm sanitizes the user's inputs and prevent sql injections.
    orm$adress(number=42, street="Second street ; -- drop table person")
)
## The orm detects that the adress object assigned to bob is not saved
## yet in the database.
## So, the orm will register the adress, and then create a link between
## bob and the adress through a linkage table.
bob$save()


## now Bob is happy because he has an adress
## prints: list(<adress id: 1> ...) etc.
print(adress <- bob$get_adress())

## the street name is still somewat strange...
## let's make it less strange
bob$get_adress(
    ## we select the adress with a strange name...
    street="Second street ; -- drop table person"

## we set a more... usual name. And we save it (the adress).
)[[1]]$set_street("Second street")$save()
print(bob$get_adress())

## never forget to disconnect when your're finished!
orm$disconnect()

## not to forget, there's a little trick:
## this call orm$connect()
## and at the end of the block, it calls orm$disconnect()
## so you never foget to disconnect from the database.
orm <- ORM(connection_params=list(DB_PATH), model_definitions=models, connect=FALSE)
orm$with_connection({
    bob <- orm$person()$load_by(name="bob")$first()
    bob$add_adress(
        ## he's has a second residence
        orm$adress(number=2, street="the squirel's path")
    )$save()
    print(bob$get_adress())

    ## finily he decided to live in his second house, and sold the
    ## first one.
    print(bob$get_adress(street="Second street"))
    bob$remove_adress(bob$get_adress(street="Second street")$first())
    bob$save()
    print(bob$get_adress())

})
## now, you're disconnected from the database.
file.remove(DB_PATH)

LainPavot/DBModelR documentation built on Sept. 29, 2021, 11:04 a.m.