1 Read Me First
1.1 What is Plisqin?
Plisqin is a Racket library that helps you rearrange fragments of SQL queries in a coherent way.
Plisqin is a query language for relational databases that offers better semantics than SQL.
Both of these statements are a valid mental model. If you are very familiar with SQL, you might prefer the former; if not, you will probably prefer the latter. Eventually you will find both mental models useful.
Plisqin is a research language.
As far as I know, my ideal query language has not yet been developed. Plisqin is a step in the right direction.
1.2 Installation
Intall Racket. If you’ve never used Racket before, follow these instructions to install Racket and learn the basics
From your shell, run raco pkg install plisqin
Using #lang racket, verify that the following code works:
(require plisqin (prefix-in aw: plisqin-examples/adventure-works) plisqin-examples/adventure-works/schema)
> (aw:show-table (from p Product (limit 3) (select (ProductName p)) (select (CategoryName p)) (select (TotalSales p)) (order-by 'desc (TotalSales p))))
Show TableShow SQL
select p.Name as ProductName
, cat.Name as CategoryName
, round(detailsG.__INJECT1, 2) as TotalSales
from Product p
left join (
select detailsG.ProductID as __INJECT0
, sum(detailsG.LineTotal) as __INJECT1
from SalesOrderDetail detailsG
group by detailsG.ProductID
) detailsG
on (detailsG.__INJECT0 = p.ProductID)
left join ProductSubcategory subcat
on (p.ProductSubcategoryID is not null and (subcat.ProductSubcategoryID = p.ProductSubcategoryID))
left join ProductCategory cat
on (subcat.ProductCategoryID is not null and (cat.ProductCategoryID = subcat.ProductCategoryID))
order by round(detailsG.__INJECT1, 2) desc
limit 3
ProductName
CategoryName
TotalSales
Mountain-200 Black, 38
Bikes
4400592.8
Mountain-200 Black, 42
Bikes
4009494.76
Mountain-200 Silver, 38
Bikes
3693678.03
Click on "Show SQL" and you might be surprised! The generated SQL is much larger than the Plisqin query. This may seem like "too much magic" right now, but once you learn how it works it is actually pretty formulaic.
1.3 Motivation
Imagine the ideal result set(s) for the task.
Use SQL to produce those result sets.
Use something else to display the results.
It is step 2 that Plisqin aims to improve. SQL and all the SQL alternatives I have tried are lacking in some way. I always end up duplicating fragments of queries and joins all over the place. Using Plisqin allows me to reduce duplication to what I suspect is the theoretical minimum.
1.4 Project Scope
Plisqin is now stable but incomplete.
Plisqin’s ideal scope includes all "application-level SQL", basically equivalent to the CRUD operations (create, read, update, delete). All "DBA-level SQL", such as creating tables, indexes, logins, etc... is explicitly a non-goal.
Right now Plisqin only supports queries. Create, update, and delete operations are not provided yet. If you have an urgent need, please open an issue.
1.5 Variants
> (aw:show-table (from cat ProductCategory (select (Name cat)) (%%select "substr("(Name cat)", 2) as SubstringDemo")))
Show TableShow SQL
select cat.Name as Name
, substr(cat.Name, 2) as SubstringDemo
from ProductCategory cat
Name
SubstringDemo
Bikes
ikes
Components
omponents
Clothing
lothing
Accessories
ccessories
I suggest that you use the strict variant most of the time, and resort to the unsafe variant only when the strict variant fails you. But using only the unsafe variant is also a valid approach, especially in situations where SQL injection is impossible such as static SQL generation.
1.6 Usage Patterns
As a static SQL generator. Simply use Plisqin’s to-sql and Racket’s file IO to generate SQL files, then do whatever you want with those SQL files. Any application that consumes the SQL isn’t even aware that Plisqin exists.
As a "Racket-aware" static SQL generator. Use compile-statements to generate the SQL and hide it inside a normal Racket procedure. This procedure cooperates with the db module so that your Racket application can fetch data at runtime.
As a dynamic SQL generator. In this situation, you construct queries and call to-sql at runtime. I have not tested this usage pattern and don’t recommend it, but sometimes it is unavoidable. Be very careful – even using the strict variant to avoid SQL injection does not guarantee that you are safe. For example, an attacker might be able to construct a computationally expensive query that renders your database unresponsive.
1.7 What Should I Read Next?
You might want to peek at Modules and Prefixes to get the lay of the land and see some other prefix conventions that this documentation uses. Then continue reading at Using define-schema.