Carte is a project mainly written in ..., based on the EPL-1.0 license.
Relational persistence for Clojure
h1. Carte
Carte is relational mapping for Clojure. It maps nested immutable data structures to an underlying relational database management system. It is meant only to provide simple access and manipulation of relational data, it is not meant to be a replacement for SQL or relational algebra.
The basic idea is that you tell Carte about the structure of the database that you are dealing with, when a query is executed, Carte will figure out a query plan, execute it and then compile the results into a nested data structure. Carte provides functions to make it easier to manipulate these data structures. Finally, Carte can save entire data structures, or even lists of data structures, with one API call, automatically figuring out which records need to be saved, updated and deleted.
The project is currently being developed against MySQL but the plan is to make it database independent. I plan to implement support for MySQL, Derby and ProsgreSQL. Once this is done, it should be easy for others to add support for additional RDMSs.
As a side feature, Carte also includes some very simple support for database migrations.
Every disclaimer goes here...
This document walks through some examples, showing the current functionality of Carte. If you would like to follow along with the examples then you will need to have MySQL, Git and "Leiningen":http://github.com/technomancy/leiningen installed.
NOTE: The examples below worked with version 0.1.1 of Carte. They do not work very well with the current version. I will update this documentation when I release version 0.2.0 as well as create a stable branch where the README will always be accurate.
h3. Table of Contents
h2. Leiningen Usage
The current version of Carte is an early prototype so it is not recommended that you use it in a production project. Having said that, I am using it in a production project and therefore need to have it in Clojures. You may add Carte to your project Leiningen project by adding the following to your :dependences:
[carte/carte "0.1.1"]
Back to Top
h2. Setting up MySQL and Cloning Carte
First, create the example database.
$ mysql -u root -p
$ CREATE DATABASE carte_test_db;
$ GRANT ALL PRIVILEGES ON carte_test_db.* TO carte_db_user@localhost IDENTIFIED BY '123456789';
$ exit
Next, clone this repository and run the tests.
$ git clone git://github.com/brentonashworth/carte.git
$ cd carte
$ lein deps
$ lein test
Running the tests will use Carte's migrations to create all of the tables in the database. It will also run a bunch of tests against this database. If all of the tests pass then you should be able to follow along with the examples below.
Back to Top
h2. Configuring Carte
This section shows what you would need to do in order to configure Carte for use in your project. If you are just following along and want to run some queries then you may skip this section. In the next section we will run some code that does all of this for us.
Two things are required in order for Carte to work, a database configuration and a data model definition. Below is the configuration for connecting to the example database that was created above.
(def db {:connection {:classname "com.mysql.jdbc.Driver"
:subprotocol "mysql"
:subname "//localhost/carte_test_db"
:user "carte_db_user"
:password "123456789"}})
For all of the following examples, we are working with a database which has the following structure.
A data model is created using the model macro. Here we create the data model for the above schema.
(def data-model
(model
(genre [:id :name])
(track [:id :name]
(belongs-to :album))
(album [:id :title]
(many-to-many :artist)
(many-to-one :genre))
(artist [:id :name])))
The above model assumes that we are using the convention of singular table names and can therefore figure out the plural names and the names of the link tables and keys. Some people like to use plural table names. In order to solve this problem, I plan to add a wrapper macro or function that will allow the developer to specify the convention that should be used.
We may also be explicit in defining the model.
(def data-model
(model
(genre [:id :name]
(one-to-many albums :album :genre_id))
(track [:id :name]
(belongs-to :album tracks :album_id))
(album [:id :title]
(many-to-many artists :artist => :album_artist :album_id :artist_id)
(many-to-one :genre :genre_id)))
(artist [:id :name]
(many-to-many albums :album => :album_artist :artist_id :album_id))))
belongs-to creates a one-to-many association between albums and tracks. When an album is deleted, all associated tracks will be deleted. If you were to use one-to-many in the above model to create an association between album and tracks then, if there is a constraint on the foreign key, you would not be allowed to delete an album if it had associated tracks.
The API for Carte provides the functions save-or-update, fetch and fetch-one. The first argument to each of these functions is a single map containing the database configuration and the model. Below, we merge the model and database configuration together and then create a few helper functions which will simplify the examples which follow.
(def db-and-model (merge db data-model))
(def ! (partial save-or-update db-and-model))
(def $ (partial fetch db-and-model))
(def $1 (partial fetch-one db-and-model))
Back to Top
h2. Example Queries
If you would like to follow along with these examples then you may start up a REPL and execute the following expressions. The carte.fixtures namespace will set up our database configuration, create a data model and create the helper functions described above. default-test-data will load sample data that we may experiment with.
Many of the results shown below are pretty printed but the call to pprint is not shown in order to keep the examples simple.
user> (use 'carte.core)
user> (use 'carte.fixtures)
user> (default-test-data)
First we see a basic query for all records in a table. Also notice that each map that we get back from a query has metadata which contains the table that the record came from as well as the original value of the record. This allows Carte to know where each record should be saved and if it needs to be saved. It also means that, in order to get the full benefit of Carte, when you modify a data structure, you should be careful to preserve the metadata.
user> ($ :album)
[{:title "Magic Potion", :id 16}
{:title "Broken Boy Soldiers", :id 17}
{:title "Elephant", :id 18}
{:title "White Blood Cells", :id 19}
{:title "Thickfreakness", :id 20}]
user> (meta (first ($ :album)))
{:carte.core/table :album,
:carte.core/original {:title "Magic Potion", :id 16}}
The queries below show various ways of selecting specific records from a single table. Note that we can select specific columns by including a vector of column names after the table. As it is now implemented, the :id is always returned because it is needed to save or update a record. The corresponding SQL is shown above each query.
;; select * from album where title like "Mag%"
user> ($ :album {:title "Mag*"})
[{:title "Magic Potion", :id 16}]
;; select title, id from album where title like "Mag%"
user> ($ :album [:title :id] {:title "Mag*"})
[{:title "Magic Potion", :id 16}]
;; select * from album where title like "Mag%" OR title = "Elephant"
user> ($ :album {:title "Mag*"} {:title "Elephant"})
[{:title "Magic Potion", :id 16} {:title "Elephant", :id 18}]
;; select * from album where (title like "Mag%" AND id = 16) OR title = "Elephant"
user> ($ :album {:title "Mag*" :id 16} {:title "Elephant"})
[{:title "Magic Potion", :id 16} {:title "Elephant", :id 18}]
;; select * from album where title <= "Magic Potion"
user> ($ :album {:where ["title <= ?" "Magic Potion"]})
[{:title "Magic Potion", :id 16}
{:title "Broken Boy Soldiers", :id 17}
{:title "Elephant", :id 18}]
;; select * from album order by title"
user> ($ :album :order-by :title)
[{:id 18, :title "Broken Boy Soldiers"}
{:id 19, :title "Elephant"}
{:id 17, :title "Magic Potion"}
{:id 21, :title "Thickfreakness"}
{:id 20, :title "White Blood Cells"}]
;; select * from album order by title desc"
user> ($ :album :order-by [:title :desc])
[{:id 20, :title "White Blood Cells"}
{:id 21, :title "Thickfreakness"}
{:id 17, :title "Magic Potion"}
{:id 19, :title "Elephant"}
{:id 18, :title "Broken Boy Soldiers"}]
This is all very basic, let's get more complex and do some joins. The two queries below show a query going in both directions through a many-to-many relationship.
user> ($ :album :with :artists)
[{:artists
[{:name "Patrick Carney", :id 24} {:name "Dan Auerbach", :id 26}],
:title "Magic Potion",
:id 16}
{:artists
[{:name "Jack White", :id 22}
{:name "Patrick Keeler", :id 23}
{:name "Brenden Benson", :id 25}
{:name "Jack Lawrence", :id 28}],
:title "Broken Boy Soldiers",
:id 17}
{:artists [{:name "Jack White", :id 22} {:name "Meg White", :id 27}],
:title "Elephant",
:id 18}
{:artists [], :title "White Blood Cells", :id 19}
{:artists
[{:name "Patrick Carney", :id 24} {:name "Dan Auerbach", :id 26}],
:title "Thickfreakness",
:id 20}]
user> ($ :artist :with :albums)
[{:albums
[{:title "Elephant", :id 18} {:title "Broken Boy Soldiers", :id 17}],
:name "Jack White",
:id 22}
{:albums [{:title "Broken Boy Soldiers", :id 17}],
:name "Patrick Keeler",
:id 23}
{:albums
[{:title "Magic Potion", :id 16} {:title "Thickfreakness", :id 20}],
:name "Patrick Carney",
:id 24}
{:albums [{:title "Broken Boy Soldiers", :id 17}],
:name "Brenden Benson",
:id 25}
{:albums
[{:title "Magic Potion", :id 16} {:title "Thickfreakness", :id 20}],
:name "Dan Auerbach",
:id 26}
{:albums [{:title "Elephant", :id 18}], :name "Meg White", :id 27}
{:albums [{:title "Broken Boy Soldiers", :id 17}],
:name "Jack Lawrence",
:id 28}]
Set carte.sql/debug to true in order to view the SQL that is generated to preform these queries.
user> (binding [carte.sql/*debug* true]
($ :album :with :artists))
["SELECT album.id AS album_id, album.title AS album_title, artist.id AS artist_id, artist.name AS artist_name
FROM album
LEFT JOIN album_artist ON album.id = album_artist.album_id
LEFT JOIN artist ON album_artist.artist_id = artist.id"]
...
That's not bad. Let's turn our queries up to 11.
user> ($ :album :with :tracks :artists)
[{:artists
[{:name "Patrick Carney", :id 24} {:name "Dan Auerbach", :id 26}],
:tracks
[{:name "Just Got To Be", :id 37}
{:name "Strange Desire", :id 38}
{:name "Your Touch", :id 39}
{:name "You're the One", :id 40}],
:title "Magic Potion",
:id 16}
{:artists
[{:name "Jack White", :id 22}
{:name "Patrick Keeler", :id 23}
{:name "Brenden Benson", :id 25}
{:name "Jack Lawrence", :id 28}],
:tracks
[{:name "Hands", :id 44}
{:name "Level", :id 45}
{:name "Steady As She Goes", :id 46}
{:name "Call It a Day", :id 47}
{:name "Together", :id 48}],
:title "Broken Boy Soldiers",
:id 17}
{:artists [{:name "Jack White", :id 22} {:name "Meg White", :id 27}],
:tracks
[{:name "Seven Nation Army", :id 41}
{:name "Black Math", :id 42}
{:name "Girl, You Have No Faith In Medicine", :id 43}],
:title "Elephant",
:id 18}
{:artists [], :tracks [], :title "White Blood Cells", :id 19}
{:artists
[{:name "Patrick Carney", :id 24} {:name "Dan Auerbach", :id 26}],
:tracks [],
:title "Thickfreakness",
:id 20}]
user> ($ :artist :with [:album :with :tracks])
[{:albums
[{:tracks
[{:name "Seven Nation Army", :id 41}
{:name "Black Math", :id 42}
{:name "Girl, You Have No Faith In Medicine", :id 43}],
:title "Elephant",
:id 18}
{:tracks
[{:name "Hands", :id 44}
{:name "Level", :id 45}
{:name "Steady As She Goes", :id 46}
{:name "Call It a Day", :id 47}
{:name "Together", :id 48}],
:title "Broken Boy Soldiers",
:id 17}],
:name "Jack White",
:id 22}
{:albums
[{:tracks
[{:name "Hands", :id 44}
{:name "Level", :id 45}
{:name "Steady As She Goes", :id 46}
{:name "Call It a Day", :id 47}
{:name "Together", :id 48}],
:title "Broken Boy Soldiers",
:id 17}],
:name "Patrick Keeler",
:id 23}
...]
user> ($ :artist :order-by :name :with [:album :with [:track :order-by :name]])
[{:id 25,
:name "Brenden Benson",
:albums
[{:id 18,
:title "Broken Boy Soldiers",
:tracks
[{:id 47, :name "Call It a Day"}
{:id 44, :name "Hands"}
{:id 45, :name "Level"}
{:id 46, :name "Steady As She Goes"}
{:id 48, :name "Together"}]}]}
{:id 26,
:name "Dan Auerbach",
:albums
[{:id 21, :title "Thickfreakness", :tracks []}
{:id 17,
:title "Magic Potion",
:tracks
[{:id 37, :name "Just Got To Be"}
{:id 38, :name "Strange Desire"}
{:id 40, :name "You're the One"}
{:id 39, :name "Your Touch"}]}]}
...]
user> ($ :artist :with [:album :with [:track {:name "Call*"}]])
[{:albums
[{:tracks [{:name "Call It a Day", :id 47}],
:title "Broken Boy Soldiers",
:id 17}],
:name "Jack White",
:id 22}
{:albums
[{:tracks [{:name "Call It a Day", :id 47}],
:title "Broken Boy Soldiers",
:id 17}],
:name "Patrick Keeler",
:id 23}
{:albums
[{:tracks [{:name "Call It a Day", :id 47}],
:title "Broken Boy Soldiers",
:id 17}],
:name "Brenden Benson",
:id 25}
{:albums
[{:tracks [{:name "Call It a Day", :id 47}],
:title "Broken Boy Soldiers",
:id 17}],
:name "Jack Lawrence",
:id 28}]
Finally, take a look at the SQL generated by our last query.
user> (binding [carte.sql/*debug* true]
($ :artist :with [:album :with [:track {:name "Call*"}]]))
["SELECT artist.id AS artist_id, artist.name AS artist_name, album.id AS album_id, album.title AS album_title,
track.id AS track_id, track.name AS track_name
FROM artist
LEFT JOIN album_artist ON artist.id = album_artist.artist_id
LEFT JOIN album ON album_artist.album_id = album.id
LEFT JOIN track ON album.id = track.album_id WHERE track.name like ?" "Call%"]
...
Back to Top
h2. Query Syntax and Composition
From the above examples we can see two important things about queries: they are just data and they have a very regular syntax. There are several specific examples above of query syntax. Here is the general syntax in psudo-BNF.
::= [] [] [] *
::= keyword
::= a vector of keywords (:id will always be included in query results)
::= one or more Clojure maps ('or' within a map, 'and' each map)
::= ":order-by" +
::= keyword for column | "[" keyword for column ":asc" | ":desc" "]"
::= ":with" +
::= | "[" "]"
Because queries are just data and there is regular syntax, it is very easy to programmatically generate and manipulate queries. For example, the following sequence of expressions will work.
user> (def track-query [:track {:name "Call*"}])
user> (apply $ track-query)
user> (def album-query [:album :with track-query])
user> (apply $ album-query)
user> ($ :artist :with album-query)
There is also a much better way to compose queries using the query function. This function allows us to create reusable query templates. It takes a model and then a table-spec or one or more compiled queries. If it is passed a table-spec, it will return a compiled query that can be executed using fetch. If it is passed a number of compiled queries, it will compose them into a new compiled query. As we did for fetch and save-or-update, we will create a shorthand closure.
(def & (partial query sample-db))
user> (def album-query (& :album :with :artists :tracks :genre))
#'user/album-query
user> album-query
{:joins {:album (:artists :tracks :genre)}, :root-table :album}
user> ($ album-query)
[{:id 117,
:title "Magic Potion",
:artists
[{:id 157, :name "Patrick Carney"} {:id 159, :name "Dan Auerbach"}],
:tracks
[{:id 265, :name "Just Got To Be"}
{:id 266, :name "Strange Desire"}
{:id 267, :name "Your Touch"}
{:id 268, :name "You're the One"}],
:genre {:name "Blues/Rock", :id 46}}
...]
user> (def ordered-artist-query (& album-query (& :artist :order-by :name)))
#'user/ordered-artist-query
user> ordered-artist-query
{:order-by [:artist [:name :asc]],
:joins {:album (:artists :tracks :genre)},
:root-table :album}
user> ($ ordered-artist-query)
[{:id 120,
:title "White Blood Cells",
:artists [],
:tracks [],
:genre nil}
{:id 118,
:title "Broken Boy Soldiers",
:artists
[{:id 158, :name "Brenden Benson"}
{:id 161, :name "Jack Lawrence"}
{:id 155, :name "Jack White"}
{:id 156, :name "Patrick Keeler"}],
:tracks
[{:id 272, :name "Hands"}
{:id 273, :name "Level"}
{:id 274, :name "Steady As She Goes"}
{:id 275, :name "Call It a Day"}
{:id 276, :name "Together"}],
:genre {:name "Rock", :id 45}}
...]
user> ($ (& ordered-artist-query (& :genre {:name "Rock"})))
[{:id 118,
:title "Broken Boy Soldiers",
:artists
[{:id 158, :name "Brenden Benson"}
{:id 161, :name "Jack Lawrence"}
{:id 155, :name "Jack White"}
{:id 156, :name "Patrick Keeler"}],
:tracks
[{:id 272, :name "Hands"}
{:id 273, :name "Level"}
{:id 274, :name "Steady As She Goes"}
{:id 275, :name "Call It a Day"}
{:id 276, :name "Together"}],
:genre {:name "Rock", :id 45}}
{:id 119,
:title "Elephant",
:artists
[{:id 155, :name "Jack White"} {:id 160, :name "Meg White"}],
:tracks
[{:id 269, :name "Seven Nation Army"}
{:id 270, :name "Black Math"}
{:id 271, :name "Girl, You Have No Faith In Medicine"}],
:genre {:name "Rock", :id 45}}]
h2. "Updating" Data
Carte is great for generating massive nested data structures. That is only half of the problem. How do we update them in a way that keeps the data consistent and also allows us to easily save the changes that we have made?
Let's see how Carte can help make this easier by adding some information to the test database. If you noticed in the example data above, the album "White Blood Cells" did not have any data associated with it. Below we will add a new track to the database and then associate that track, and the correct artists, with this album.
;; Get the album we are working with
user> (def album ($1 :album {:title "White Blood Cells"} :with :tracks :artists))
;; Add the new track to the database
user> (def new-track (! :track {:name "Fell in Love with a Girl"}))
;; Add the new track and the artists to the album
user> (def album (-> album
(conj-in [:tracks] new-track)
(concat-in [:artists] ($ :artist {:name "Jack White"} {:name "Meg White"}))))
;; Save changes
user> (binding [carte.sql/*debug* true]
(! album))
["SELECT track.id AS track_id, track.name AS track_name FROM track WHERE track.album_id = ?" 125]
updating record in :track: {:album_id 125, :name "Fell in Love with a Girl"}
["SELECT * FROM album_artist WHERE album_artist.album_id = ?" 125]
inserting record into :album_artist: {:artist_id 162, :album_id 125}
["SELECT * FROM album_artist WHERE album_artist.artist_id = ? AND album_artist.album_id = ?" 162 125]
inserting record into :album_artist: {:artist_id 167, :album_id 125}
["SELECT * FROM album_artist WHERE album_artist.artist_id = ? AND album_artist.album_id = ?" 167 125]
{:id 125, :title "White Blood Cells"}
It is also easy to delete nested elements.
user> ($ :album :with :artists)
[{:id 127,
:title "Magic Potion",
:artists
[{:id 171, :name "Patrick Carney"} {:id 173, :name "Dan Auerbach"}]}
{:id 128,
:title "Broken Boy Soldiers",
:artists
[{:id 169, :name "Jack White"}
{:id 170, :name "Patrick Keeler"}
{:id 172, :name "Brenden Benson"}
{:id 175, :name "Jack Lawrence"}]}
{:id 129,
:title "Elephant",
:artists
[{:id 169, :name "Jack White"} {:id 174, :name "Meg White"}]}
{:id 130,
:title "White Blood Cells",
:artists
[{:id 169, :name "Jack White"} {:id 174, :name "Meg White"}]}
{:id 131,
:title "Thickfreakness",
:artists
[{:id 171, :name "Patrick Carney"} {:id 173, :name "Dan Auerbach"}]}]
user> (first *1)
{:artists [{:name "Patrick Carney", :id 24} {:name "Dan Auerbach", :id 26}], :title "Magic Potion", :id 16}
user> (remove-in *1 [:artists] {:name "Patrick Carney"})
{:artists ({:name "Dan Auerbach", :id 26}), :title "Magic Potion", :id 16}
user> (binding [carte.sql/*debug* true]
(! *1))
updating record in :album: {:title "Magic Potion"}
["SELECT * FROM album_artist WHERE album_artist.album_id = ?" 16]
deleting record in :album_artist where ["id = ?" 31]
Because of the belongs-to association between album and track, when we delete an album, all associated tracks will be deleted. In the example below, we first have to remove all artists from the album or database constraints will prevent us from deleting it.
user> (def r (-> ($1 :album {:title "Magic Potion"} :with :artists)
(assoc :artists [])))
user> (! r)
user> (binding [carte.sql/*debug* true]
(delete-record sample-db r))
["SELECT track.id AS track_id, track.name AS track_name FROM track WHERE track.album_id = ?" 116]
deleting record in :track where ["id = ?" 277]
deleting record in :track where ["id = ?" 278]
deleting record in :track where ["id = ?" 279]
deleting record in :track where ["id = ?" 280]
deleting record in :album where ["id = ?" 116]
Back to Top
h2. Working with Nested Data Structures
In the examples above we have seen how to use conj-in, concat-in and remove-in to "modify" nested data structures. There are two more functions that can help us here: find-in and vary-in.
find-in allows us to query a nested data structure in order to find all values with a common path. Compare the result of the call to find-in below with the query results for ($ :album :with :artists) above.
user> (distinct
(find-in [:artists :name] ($ :album :with :artists)))
("Dan Auerbach" "Jack White" "Patrick Keeler" "Brenden Benson" "Jack Lawrence" "Meg White" "Patrick Carney")
vary-in produces a new data structure where all records from the given table which match the given pattern have been updated using a map of replacement values or a function. Suppose that we have the data structure produced by ($ :album :with :artists) and we would like the change the name of the artist "Jack White" to "Jack Daniels".
user> (def data ($ :album :with :artists))
#'user/data
user> (vary-in data :artist {:name "Jack White"} {:name "Jack Daniels"})
[{:artists
[{:name "Patrick Carney", :id 171} {:name "Dan Auerbach", :id 173}],
:title "Magic Potion",
:id 127}
{:artists
[{:name "Jack Daniels", :id 169}
{:name "Patrick Keeler", :id 170}
{:name "Brenden Benson", :id 172}
{:name "Jack Lawrence", :id 175}],
:title "Broken Boy Soldiers",
:id 128}
{:artists
[{:name "Jack Daniels", :id 169} {:name "Meg White", :id 174}],
:title "Elephant",
:id 129}
{:artists
[{:name "Jack Daniels", :id 169} {:name "Meg White", :id 174}],
:title "White Blood Cells",
:id 130}
{:artists
[{:name "Patrick Carney", :id 171} {:name "Dan Auerbach", :id 173}],
:title "Thickfreakness",
:id 131}]
The other forms of vary-in allow us to use a function of either the value or the entire map to produce the new value.
(vary-in data :artist {:name "Jack White"} {:name #(.substring % 1)})
(vary-in data :artist {:name "Jack White"} #(assoc % :name (str (:id %))))
That's it for now. There is still much work to be done.
h2. Cleaning Up
Before moving on, clean up the database so that the unit tests will be able to run against it.
user> (delete-all-test-data)
h2. TODO
- Add support for limiting and paging results.
- Raw SQL query results should still have metadata so that save-or-update may be used on the resulting data structure.
- Clean up the code and tests.
- There are times when a user might want to avoid a join and perform two separate queries, provide a function that will merge the results into one data structure which may be used like any other.
- Add support for Derby and PostgreSQL
- Smarter Query Planning - currently there are some unnecessary queries being performed.
h2. License
Copyright (C) 2010 Brenton Ashworth
Distributed under the Eclipse Public License, the same as Clojure uses. See the file COPYING.
Previous：cl-osgNext：DSB-assignment-1