Value proposition

You should only have to care about the data in the database that you ... care about. But often, you have to care about more because the database has some constraints that you have to meet. This library handles some of those constraints for you by generating data that satisfies them.

Introducing "Foreign Key Generator" or fk-gen for short

This library helps generate sql statments that fulfill two constraints

1. data types : e.g column's values are of type int
2. foreign key : column's values match another value in another table's column

Using the library

It does this with only a little help from you. Mainly you have to tell it:

1. How to connect to the database. So host, port, etc..
2. Which table you wish to generate insert statments for.
3. A function for how to handle the foreign key dependencies

Don't worry about the third point (the function) will provide one of those for you.

Example

Lets say you have a database with two tables

1. dogs
2. persons

where a dog has to have a owner. A constraint that is enforced through a foreign key dependency

dogs -> persons

When you use this library you can expect to get sql that full fills this constraint. E.g a list of sql insert statments like...

1. "insert into persons (id) values (101)"
2. "insert into dogs (id, person) values (1, 101)"

The clojure code for doing this looks like you might expect:

Contains all the functionality to get and transform the foreign key dependencies

(->sql-and-insert! {:table :dogs 
                    :db-info db-info)

Inserts sql insert statements that full fill all the foreign key dependencies of the table. I suggest jumping to either the test (core_test.clj) for a working example or the fk-gen.generate namespace for the public facing functionality

Understanding how the library works

What follows is a overview of how this library works internally and so can be happily ignored if your just a consumer of the functionality.

Get the foreign key dependencies

This is made possible by first extracting foreign key information from the database through a sql function which we can call called get-fk-deps which is brought into our namespace here

Transform foreign key dependencies into a graph

Once we have the foreign key dependencies we need to put them into a structure that is easy to traverse so we turn it into a graph.

Transform the graph into our sql insert statments

Given the graph with the structure like:

{:dogs {:persons {:fk-column :owner 
                  :pk-column :id}}}

we can do a depth first search walk on it and on each node (table) use our table-graph->sql->insert-stmt-plan function which takes the current table and the graph and produces a sql insert statments

Sense these sql insert statments are ordered by walking our dependency graph, we can simply reverse that ordering and insert them to full fill the database constraints.

We wrap that functionality together into a side effect free function

Generators

These functions generate SQL insert statements in honeySQL format, regular SQL and also just insert the generated data for you. This namespace brings together all the functionality and is here users of library should go for public facing functions.

Returns a vector of sql insert statement (honeysql format) necessary to fulfill all the foreign key constraints of the given table table :keyword : the name of the table db-info :hashmap : a map describing the database connection information see https://github.com/clojure/java.jdbc. table-graph->insert-stmt-plan :fn : a two arity fn [table,graph] that returns a vector of honeysql formatted insert statements

Returns a collection of sql insert Statements that full fill all the foreign key dependencies of the table table :keyword : the name of the table db-info :hashmap : a map describing the database connection information see https://github.com/clojure/java.jdbc.

Inserts sql insert statements that full fill all the foreign key dependencies of the table table :keyword : the name of the table db-info :hashmap : a map describing the database connection information see https://github.com/clojure/java.jdbc.

Overcoming the constraints

on the last leg of our journey we need to find a way to actually create sql insert statments. What follows will describe how to do this in general while creating some functions you can use to get you started or serve as a default. We call the function that produces these insert statements: table-graph->insert-stmt-plan

The functions in this namespace can be used to build a table-graph->insert-stmt-plan function which fulfills its contract of taking a graph and table and returning a vector of honeySQL insert statements.

One sensible way to implement the table-graph->insert-stmt-plan fn is to generate a sql select statement (honeySQL format) that will be used to create a value that meets the foreign key dependency constraint. This function is used to achieve just that.

Returns a vector of select any sql statements

Now that you have a way to generate select any statements you still need a way to create the actual sql insert statments. thats where ->insert comes in. Which is really just a honeySQL code.

returns a vector of insert sql statements

If your paying attention you have probably noticed we never showed how to generate values that fullfil the data type constraint. Thats because most of that is done by another library called table-spec. Examples of how to use the functionality in fk-gen can be seen in fk-gen-example