eSQLate
Project Purpose
eSQLate is an attempt to give small teams a quick and easy form of administration panel which is significantly more powerful than CRUD based systems but also as easy to set up.
What problem is it solving?
A lot of my work history has been based at small companies with small teams where there is enough resource to build a impressive product using great code, but it is sometimes difficult to justify investing as much time into creating administration panels which nobody other than internal employees will ever see.
There are many times I have had to delve into the database because the narrow range of operations which are available on the administration panels does not cover what I, at that moment, need to do. I don’t think this is always a problem, particularly when the project is at a very early stage but as a project grows having developers or systems administrators log into the database and change settings has some real downsides:
-
You’re taking time away from developers / systems administrators to do things which could, sometimes, be done by less technical employees.
-
Executing database operations is nearly always a bespoke operation on live data, meaning we have to be really, really careful and think things through in great detail.
-
Eventually, everyone will make a mistake, while logged into a live database the unintended effects could be disastrous.
What is it?
eSQLate interprets a simple JSON (or JSON5) file like this:
{
"name": "product_search",
"title": "Product Search",
"description": "List products that include a substring",
"parameters": [
{
"name": "product_name",
"type": "string"
}
],
"statement": "\
SELECT\n\
product_id,\n\
product_name,\n\
suppliers.company_name as supplier_name,\n\
unit_price\n\
FROM products\n\
LEFT JOIN suppliers on suppliers.supplier_id = products.supplier_id\n\
WHERE LOWER(product_name) LIKE CONCAT('%', LOWER($product_name), '%')"
}
Using the above it will produce a fairly usable and reasonable web interface like what is shown below:
If users are of a nervous disposition and scared off by seeing things they don’t understand (like SQL) they can flip the toggle to convert the query based interface into a simple form.
We can specify parameters as strings, dates, datetimes, integers, selects, decimals and even use a popup to allow rich selection of a specific parameter:
Another powerful feature is to provide the user the ability to link these simple JSON documents together together. Doing this giving many possible user journeys allowing basic, but meaningful self-service administration.
The result sets can also be downloaded as CSV files.
Tutorial - Adding a new workflow
The most simple example
About the most simple thing we can do is create a simple select of a table. This can be achieved by writing some simple JSON5 into a file:
example_definition/employee_list.json5
{
"name": "employee_list",
"title": "Employee List",
"parameters": [],
"statement": "\
SELECT\n\
employee_id,\n\
last_name,\n\
first_name,\n\
reports_to\n\
FROM employees"
}
The most simple example - Enhanced
Taking the super simple example it is easy to make it marginally useful
example_definition/employee_list.json5
{
"name": "employee_list",
"title": "Employee List",
"description": "List employee details which include a sub string",
"parameters": [
{
"name": "search_string",
"type": "string",
"highlight_fields": ["last_name", "first_name"]
}
],
"statement": "\
SELECT\n\
employees.employee_id,\n\
concat(employees.first_name, ' ', employees.last_name) as full_name,\n\
concat(reports_to.first_name, ' ', reports_to.last_name) as reports_to\n\
FROM employees\n\
LEFT JOIN employees reports_to ON\n\
reports_to.employee_id = employees.reports_to\n\
WHERE\n\
lower(employees.first_name) like CONCAT('%', lower($search_string) , '%') OR\n\
lower(employees.last_name) like CONCAT('%', lower(${search_string}) , '%')\
",
"statement_type": "SELECT"
}
This will find people who’s first_name or last_name includes $search_string anywhere within.
We have:
-
Defined a parameter named
search_stringwhich could also be wrote as${search_string}if you need to be unambiguous about where the string terminates -
Added a
statement_typewhich really does nothing other than color the button below the query… but it goes a nice red when its aDELETE.
How does it look?
Allowing adding of employees
Taking what we know from The most simple example'' it is trivial to transform it into an `INSERT statement, however I took the liberty of adding some comments due to the SQL separates column names from the VALUES .
example_definition/employee_add.json5
{
"name": "employee_add",
"title": "Add an Employee",
"description": "Add an employee",
"parameters": [
{ "name": "last_name", "type": "string" },
{ "name": "first_name", "type": "string" },
{ "name": "title", "type": "string" },
{ "name": "title_of_courtesy", "type": "string" },
{ "name": "birth_date", "type": "date" },
{ "name": "hire_date", "type": "date" },
{ "name": "address", "type": "string" },
{ "name": "city", "type": "string" },
{ "name": "region", "type": "string" },
{ "name": "postal_code", "type": "string" },
{ "name": "country", "type": "string" },
{ "name": "home_phone", "type": "string" },
{ "name": "extension", "type": "string" },
{ "name": "notes", "type": "string" },
{ "name": "reports_to", "type": "integer" }
],
"statement": "\
INSERT INTO employees (\n\
last_name, first_name, title, title_of_courtesy,\n\
birth_date, hire_date, address, city,\n\
region, postal_code, country, home_phone,\n\
extension, notes, reports_to\n\
)\n\
VALUES (\n\
/* last_name = */ $last_name,\n\
/* first_name = */ $first_name,\n\
/* title = */ $title,\n\
/* title_of_courtesy = */ $title_of_courtesy,\n\
/* birth_date = */ $birth_date,\n\
/* hire_date = */ $hire_date,\n\
/* address = */ $address,\n\
/* city = */ $city,\n\
/* region = */ $region,\n\
/* postal_code = */ $postal_code,\n\
/* country = */ $country,\n\
/* home_phone = */ $home_phone,\n\
/* extension = */ $extension,\n\
/* notes = */ $notes,\n\
/* reports_to = */ $reports_to\n\
)",
"statement_type": "INSERT"
}
The end result looks like the below:
Allowing adding of employees - who they report to - enhanced!
I dislike the way a user would have to take note of the user_id that the new employee would report to…
To fix it all we need to do is change { "name": "reports_to", "type": "integer" } into { "display_field": "full_name", "definition": "employee_list", "value_field": "employee_id", "type": "popup", "name": "reports_to" } . The final file is shown below:
Also converting the input of dates to dates is really easy (just change the "type" ).
example_definition/employee_add.json5
{
"name": "employee_add",
"title": "Add an Employee",
"description": "Add an employee",
"parameters": [
{ "name": "last_name", "type": "string" },
{ "name": "first_name", "type": "string" },
{ "name": "title", "type": "string" },
{ "name": "title_of_courtesy", "type": "string" },
{ "name": "birth_date", "type": "date" }, /* Changed from "string" to "date" */
{ "name": "hire_date", "type": "date" }, /* Changed from "string" to "date" */
{ "name": "address", "type": "string" },
{ "name": "city", "type": "string" },
{ "name": "region", "type": "string" },
{ "name": "postal_code", "type": "string" },
{ "name": "country", "type": "string" },
{ "name": "home_phone", "type": "string" },
{ "name": "extension", "type": "string" },
{ "name": "notes", "type": "string" },
{
"display_field": "full_name", /* Changed from "string" to "popup" */
"definition": "employee_list",
"value_field": "employee_id",
"type": "popup",
"name": "reports_to"
}
],
"statement": "\
INSERT INTO employees (\n\
last_name, first_name, title, title_of_courtesy,\n\
birth_date, hire_date, address, city,\n\
region, postal_code, country, home_phone,\n\
extension, notes, reports_to\n\
)\n\
VALUES (\n\
/* last_name = */ $last_name,\n\
/* first_name = */ $first_name,\n\
/* title = */ $title,\n\
/* title_of_courtesy = */ $title_of_courtesy,\n\
/* birth_date = */ $birth_date,\n\
/* hire_date = */ $hire_date,\n\
/* address = */ $address,\n\
/* city = */ $city,\n\
/* region = */ $region,\n\
/* postal_code = */ $postal_code,\n\
/* country = */ $country,\n\
/* home_phone = */ $home_phone,\n\
/* extension = */ $extension,\n\
/* notes = */ $notes,\n\
/* reports_to = */ $reports_to\n\
)",
"statement_type": "INSERT"
}
The end result is that there will be a new control shown which when clicked will open a pop-up:
Adding Links
The definitions we’ve defined so far do not have to be completely independent. It is possible to define links both on each row of the result set and above/below of the definition / form itself.
Lets first add a link from the Employee List to Employee Add. We can do this in three ways to a definition:
1. We can add to "links"
Links are displayed between the definition and the results:
"links": [
{ "href": "#employee_add", "text": "Add an employee" }
]
2. We can add to "top_links" , which are identical to "links" except that they occur above the displayed definition
Links are displayed above the definition:
"top_links": [
{ "href": "#employee_add", "text": "Add an employee" }
]
3. We can add to "row_links"
This will add a row on every line of the results table. This is useful if the row denotes some information you want to use in the place which you are linking to. To do this add the following to the root of the definition:
"row_links": [
{ "href": "#employee_add?reports_to=${popup employee_id full_name}", "text": "Add Subordinate for ${full_name}" }
]
Hiding the ``Employee Add'' Menu Item
If you wish to hide the Employee Add'' menu item it is simple enough. Just change the name (and filename) from `employee_add.json5 to _employee_add.json5 . Beware any links that exist will also need to updated.
Installation
Desktop Method
If you have a PostgreSQL server and want to run it on your local laptop you may choose to use the desktop method.
This will has the nicety of automagically picking port numbers and opening the browser once everything has started up.
-
Set up a PostgreSQL server and get the hostname, port, username and password.
-
Clone this repository with eSQLate with
git clone https://github.com/forbesmyester/esqlate.git. -
Change into the directory you checked out the code into with
cd esqlate -
Run
npm installto install dependencies etc. -
Run
./esqlate.
Docker Compose Method
If you’re just trying eSQLate out the most simple way to test it out is to use Docker Compose .
If you check out this repository you’ll see a docker-compose.yml file. In the directory you checked it out to all you’ll have to do is:
-
Clone eSQLate Server with
[ ! -d "esqlate-server" ] && git clone git@github.com:forbesmyester/esqlate-server.git esqlate-server. -
Clone eSQLate Front with
[ ! -d "esqlate-front" ] && git clone git@github.com:forbesmyester/esqlate-front.git esqlate-front. -
Bring up the services using
docker-compose build && docker-compose up. -
Customize the docker-compose.yml to meet your needs.
Directly on VM/Metal Method
Installation is relatively simple. All you need to do is install:
-
PostgreSQL The most awesome SQL database.
-
eSQLate Server Which is provides the API functions and does the actual database queries.
-
eSQLate Front talks to eSQLate Server and provides a web based interface to the user.
Running the Integration Tests
There are some basic integration tests that run using Cypress . To run them first bring up testing docker compose and then run the tests:
docker-compose -f ./docker-compose-integration.yml up
What still needs to be done?
-
SERVER: Currently results persisted to the local disk. I plan to add an AWS S3 persistence option.
-
SERVER: Support other types of databases.
以上就是本文的全部内容，希望对大家的学习有所帮助，也希望大家多多支持 码农网
猜你喜欢:
本站部分资源来源于网络，本站转载出于传递更多信息之目的，版权归原作者或者来源机构所有，如转载稿涉及版权问题，请联系我们。
React Native：用JavaScript开发移动应用
【美】Truong Hoang Dung（张皇容） / 奇舞团 / 电子工业出版社 / 2015-9 / 65.00
React Native是当前移动端开发中的优秀解决方案。《React Native：用JavaScript开发移动应用》围绕着如何将一个完整App提交到App Store，讲解了使用React Native开发iOS应用所涉及的方方面面。首先介绍了Flexbox布局，教大家从零开始搭建一个初始应用，以此阐明React Native的基础运行机理；然后介绍了Flux的设计思想，怎么理解和使用Pro......一起来看看 《React Native：用JavaScript开发移动应用》 这本书的介绍吧!
HTML 编码/解码
HTML 编码/解码
MD5 加密
MD5 加密工具