hasqlator-mysql 0.2.0 → 0.2.1
raw patch · 2 files changed
+195/−3 lines, 2 files
Files
- hasqlator-mysql.cabal +1/−1
- src/Database/MySQL/Hasqlator.hs +194/−2
hasqlator-mysql.cabal view
@@ -1,5 +1,5 @@ Name: hasqlator-mysql-Version: 0.2.0+Version: 0.2.1 Synopsis: composable SQL generation Category: Database Copyright: Kristof Bastiaensen (2020)
src/Database/MySQL/Hasqlator.hs view
@@ -18,7 +18,199 @@ Stability : unstable Portability : ghc +This module provides a lower level SQL combinator language. Hasqlator+works on existing databases, and doesn't do schema creation or+database migration. += example schema++Here is an example schema for a library loan system, that will be used+in the following examples:++@+CREATE TABLE borrowers (+ id INT PRIMARY KEY,+ name VARCHAR(100) NOT NULL,+ date_of_birth DATETIME NOT NULL+);++CREATE TABLE authors (+ id INT PRIMARY KEY,+ name VARCHAR(100) NOT NULL,+ birth_year INT+);++CREATE TABLE books (+ id INT PRIMARY KEY,+ title VARCHAR(255) NOT NULL,+ author_id INT,+ published_year INT,+ age_rating INT,+ FOREIGN KEY (author_id) REFERENCES authors(id)+);++CREATE TABLE loans (+ id INT PRIMARY KEY,+ book_id INT,+ borrower_id INT,+ loan_date DATE NOT NULL,+ return_date DATE,+ FOREIGN KEY (book_id) REFERENCES books(id)+);+@++= inserting data++You can insert data into the database using the `insertValues`+function. This can insert any haskell datatype into the database.+You have to create an `Insertor` first, which maps a haskell datatype+to sql rows. There are several ways to do this:++== extractor function.++Proved an extractor function for each field, match it to the sql field+using `into`, and compose them using monoid append (`<>`):++@+data Author = Author+ { name+ , birth_year+ }++insertValues "authors" (name `into` "name" <> birth_year `into` "birth_year")+ [Author "George Orwell" 1903,+ Author "Aldous Huxley" 1894]++@++== extractor lenses++Similarly, you can use a lens to match the fields, using `lensInto`++> insertValues "authors" (_1 `lensInto` "name" <> _2 `lensInto` "birth_year")+> [("J.K. Rowling", 1965),+> ("Leo Tolstoy", 1828),+> ("Harper Lee", 1926)]++== Data types++The insertData function uses Generics to match the input fields to the+given sql columns. It works with any product type, including data+declarations and records. It uses the position of the field. You can+use skipInsert instead of the table name to skip fields you don't care+about.++> data Borrower = Borrower+> { name :: Text+> , date_of_birth :: Day+> }+>+> insertValues "borrowers" (insertData ("name", "date_of_birth"))+> [Borrower "John Doe" (fromJulian 1985 6 15),+> Borrower "Jane Smith" (fromJulian 1990 11 23),+> Borrower "Emily Johnson" (fromJulian 2000 8 30),+> Borrower "Michael Brown" (fromJulian 1982 2 10),+> Borrower "Sarah Davis" (fromJulian 1995 03 12)]++== Expressions++Insert values can have arbitrary SQL queries, by using the `exprInto`+function. Here I create a subquery, from the query which fetches+author_id from an author name.++> data Book = Book+> { title :: String+> , author :: String+> , publishedYear :: Int+> , ageRating :: Int+> }+>+> getBookAuthorId : Book -> Query Int+> getBookAuthorId Book{author=a} =+> select (sel "id") $+> from "authors" <>+> where_ ["name" =. arg a]+> +> insertValues (title `into` "title" <>+> (subQuery . getBookAuthorId `exprInto` "author_id") <>+> publishedYear `into` "published_year" <>+> ageRating `into` "age_rating")+> [ Book "1984" "George Orwell" 1903 16+> , Book "Brave New World" "Aldous Huxley" 1932 14,+> , Book "Harry Potter and the Philosopher\'s Stone" "J.K. Rowling" 1997 7+> , Book "War and Peace" "Leo Tolstoy" 1869 18+> ]++= Querying++Queries are done using the `select` function. It takes a `Selector`,+which tells how to match SQL fields with haskell values, and a+`QueryClauses`, which contains the rest of the query (the select+statement is emitted by the select function).++For example, a query that gives back all authors:++@+getAllAuthors :: Query Author+getAllAuthors =+ select (Author <$> sel "name" <*> sel "birth_year" ) $+ from "author"+@++Here the selector is composed using the `sel` function for individual+columns, and and applicative functor to turn it into the desired+haskell datastructure.++The query body can be composed using Monoid `mappend` (`<>`). For+example, here is a more complicated expression to get all books loaned+by a specific borrower:++@+booksLoaned :: Borrower -> Query Book+booksLoaned borrower =+ select (Book <$> ++SELECT books.title, loans.loan_date, loans.return_date+FROM loans+JOIN books ON loans.book_id = books.id+JOIN borrowers ON loans.borrower_id = borrowers.id+WHERE borrowers.name = 'Jane Smith';+++= Insert Loans++Finally we can insert the loans in the table.++> data Loan = Loan+> { bookTitle :: Text+> , bookAuthor :: Text+> , borrowerName :: Text+> , loanDate :: Day+> , return_date :: Day+> }+>+> getLoanBookId :: Loan -> Query Int+> getLoanBookId Loan{bookAuthor=bookAuthor, bookTitle=bookTitle} =+> select (sel "id") $+> from "books" <>+> leftJoin ["authors"] ["books.author_id" =. "authors.id"] <>+> where_ ["books.title" =. arg bookTitle,+> "authors.name" =. arg bookAuthor]+>+> getLoanBorrower_id :: Loan -> Query Int+> getLoanBorrower_id Loan{borrowerName = borrowerName} =+> select (sel "id") $+> from "borrowers"+> ++INSERT INTO loans (id, book_id, borrower_id, loan_date, return_date) VALUES +(1, 1, 1, '2024-09-15', '2024-09-30'), -- John Doe borrowed '1984' and returned it+(2, 3, 2, '2024-10-01', NULL), -- Jane Smith borrowed 'Brave New World' and hasn't returned it yet+(3, 4, 3, '2024-10-05', NULL), -- Emily Johnson borrowed 'Harry Potter and the Philosopher\'s Stone'+(4, 6, 4, '2024-09-20', '2024-09-27'), -- Michael Brown borrowed 'To Kill a Mockingbird' and returned it+(5, 5, 5, '2024-10-08', NULL); -- Sarah Davis borrowed 'War and Peace' recen++ -} module Database.MySQL.Hasqlator@@ -92,8 +284,8 @@ import Data.Time import qualified Data.ByteString as StrictBS import qualified Data.ByteString.Lazy as LazyBS-import Data.ByteString.Lazy.Builder (Builder)-import qualified Data.ByteString.Lazy.Builder as Builder+import Data.ByteString.Builder (Builder)+import qualified Data.ByteString.Builder as Builder import qualified Data.Text.Encoding as Text import qualified Data.Text as Text import Data.Text (Text)