Generic Editor for Hierarchical SQL Data Running in the Browser

Generic Editor for
Hierarchical SQL Data
Running in the Browser
Roland Sonnenschein
Hesotech GmbH
automatisieren – visualisieren
http://www.hesotech.de
Agenda
●
Metadata in industrial production
Hierchical SQL-data
My solutions to edit metadata
–
●
1995 – 2015
– Changes of requirements
– ForeignKey ↔ Tree ↔ URL
Some thoughts about ORMs
–
●
30.10.2015
© Hesotech GmbH / R. Sonnenschein
2
How it started (1995):
Refurbishment of Turbins
Anealing
Wear during runtime
Refurbish by welding
Task:
Documentation of annealing
● Of each turbine-blade
● Identified by it's serial
●
30.10.2015
© Hesotech GmbH / R. Sonnenschein
3
At that time ...
Switchboard
SCADA
Supervisory
Control
Journal
And
Data
Aquisition
30.10.2015
© Hesotech GmbH / R. Sonnenschein
4
Treatment-Data-Administration
SCADA
Treatment-Unit
Measurements
MetaData
…
Treatment:
● Articles
● Serials
● Started
● Ended
Reporting
30.10.2015
© Hesotech GmbH / R. Sonnenschein
5
Reanimation
With a Little Help of my Friend Slony
OS/2 Warp 3.0
Pentium S
● 133 MHz
● 32 MByte RAM
● 800 Mbyte HD
●
30.10.2015
© Hesotech GmbH / R. Sonnenschein
6
First Run (1995): OS/2
SCADA
30.10.2015
TDA-Kernel
C++
© Hesotech GmbH / R. Sonnenschein
GUI
REXX
7
TDA: Basic-Data-Structure
Designed with
http://www.pgmodeler.com.br/
By Raphael Araújo e Silva
30.10.2015
© Hesotech GmbH / R. Sonnenschein
8
Raw Hierarchical Data: TabView
fk
30.10.2015
fk
© Hesotech GmbH / R. Sonnenschein
9
OS/2-Screenshot
30.10.2015
© Hesotech GmbH / R. Sonnenschein
10
Circumstances Changed (1997)
●
OS/2
●
Windows NT 4.0: 1996-July
●
Watcom SQL 1994
→ PowerSoft 1995
→ Sybase2010
→ SAP
●
PostgreSQL 6: 1997-Jan (not Windows)
DBMS
30.10.2015
GUI-Tool: C++
Data Window
© Hesotech GmbH / R. Sonnenschein
http://www.postgresql.org/doc
s/9.4/static/release.html
DB-Designer
11
From TabView to Tree
30.10.2015
© Hesotech GmbH / R. Sonnenschein
12
Structure
●
Task-Node (Root):
–
–
Children: Table-Nodes
Renderer: Tree-Control
–
Table-Nodes
●
●
●
●
–
Row-Nodes
●
●
●
●
30.10.2015
Key, Label: Table-Name
Children: Row-Nodes
Detail-Data: Table-Data
Renderer: Table-Control
Key, Label: Key of Row
Children: Talble-Nodes
Detail-Data.: Row-Data
Renderer: List of Cell-Renderers
© Hesotech GmbH / R. Sonnenschein
13
DbExplorer: Power++
Real Application (Still in Use!)
Article
Position
Customers
Articles
Order
Orders
Serial-ID
Content
Treatments
Treatment-Units
Apply
30.10.2015
Delete
© Hesotech GmbH / R. Sonnenschein
Add
Print
14
Configuration via
Powersoft DataWindow
●
Clever Combination
–
High Level Grid with
●
Lots of configurable cell-renderers and -editors
–
–
●
Date/Time, Numeric, Boolean, Select-Box = DataWindow
Report – Generator
SQL – Based
–
Select, Update, Insert, Delete with Parameters
–
Graphic or textual
30.10.2015
© Hesotech GmbH / R. Sonnenschein
15
Circumstances Changed (2002)
●
Power++
●
Windows XP: 2001-Oct
●
PostgreSQL 7: 2000-May (not Windows)
●
DBMS
Programming-Tool: C#
Implementing
DataWindow Functionality
by
● Infragistics UltraGrid
● Crystal Reports
30.10.2015
© Hesotech GmbH / R. Sonnenschein
16
DbExplorer .NET
●
No Example, because
–
Nothing new concerning this talk
–
Programming was awful
30.10.2015
© Hesotech GmbH / R. Sonnenschein
17
New Demands (2006)
●
PostgreSQL 8 (native Windows): 2005-Jan
●
Multicore-Processors
●
Operation in Office + Intranet: No Installation !
Client-Server Architecture
●
●
30.10.2015
DBMS
Server-Programming
Client-Programming
Server: C#
Npgsql
Client Java SE
JDBC
© Hesotech GmbH / R. Sonnenschein
18
DbExplorer: Java-SE
Real Application
Tasks
30.10.2015
© Hesotech GmbH / R. Sonnenschein
19
A Lot has Happened (2014) / 1
Mobile Devices: iPhone, Android, Tablets, …
● Browser (OS-Independant):
Dynamic Webpages / Web 2.0,
●
●
AJAX: W3C XMLHttpRequest (2005-Apr)
● JIT-Compiler in Browser
Demand: Operation Everywhere
●
●
●
●
●
30.10.2015
Stationary: Treatment-Unit + Office + Home
Inside Company
Outside Comany for Emergency Service
© Hesotech GmbH / R. Sonnenschein
20
A Lot has Happened (2014) / 2
●
GUI in Browser, on Tablet, Mobile …
●
●
●
Prevent SQL- Injection
Security-Problem: SQL- Comands via internet
Middleware needed
Kernel
C#
Npgsql
30.10.2015
DBMS
Middleware: DbGate
Client: DbExplorer
node.js
Brianc: Node-postgres
HTML5 / CSS
REST Web Services
https://github.com/b
rianc/node-postgres
http://qooxdoo.org/
© Hesotech GmbH / R. Sonnenschein
21
From Tree to URI
fk
fk
treatment_unit / “Unit 02” / treatment / ”tmt 2015-01-01/2-9” / content / ”SN 2-09”
30.10.2015
© Hesotech GmbH / R. Sonnenschein
22
DbExplorer: JavaScript
Demo
30.10.2015
© Hesotech GmbH / R. Sonnenschein
23
Task- and Tree-Configuration
Tasks
Task and Tree
{
"label": "2015.PgConf.eu",
"database": [
{ "label": "2015.PgConf.eu", "dbKey":"tda",
"color": "#0040FF" ,
"connectionString": "pg://postgres:pwd@localhost:5434/tda" }
],
"tree": {
"article": {"label": "Articles"},
"treatment_unit": {"label": "Treatment-Units"},
"treatment_unit/treatment": {"label": "Treatments"},
"treatment_unit/treatment/content": {"label": "Content"}
},
"variants": {}
}
30.10.2015
© Hesotech GmbH / R. Sonnenschein
24
Table-Configuration: Article
{ "label": "Article",
"select": {
"label": [1],
"keys": [1],
"hide": [2],
"readOnly": [2],
"primaryKeys": [1],
"order": [1],
"command": [ "SELECT article_name, properties FROM article" ],
"columns": [ { "1": { "label": "Name", "width": 200 } } ]
},
"update": {
"command": [ "UPDATE article",
"SET article_name=${article_name}, properties=${properties}",
"WHERE article_name=#{article_name};" ]
},
"insert": {
"command": [ "INSERT INTO article(article_name, properties)",
"VALUES (${article_name}, ${properties})" ]
},
"delete": {
"command": [ "DELETE FROM article WHERE article_name=#{article_name}" ]
}
}
30.10.2015
© Hesotech GmbH / R. Sonnenschein
25
Table-Configuration: Content
{
"label": "Content",
"select": {
"label": [2],
"keys": [2],
"hide": [1,5],
"readOnly": [2],
"primaryKeys": [1],
"order": [2],
"command": [ "SELECT treatment_name, \"position\", article_name, serial_id, properties",
"FROM content WHERE treatment_name = ':{1,1}'" ],
"columns": [
{ "2": { "label": "Position", "width": 200 },
"3": { "label": "Article", "width": 200, "type":"SelectBox", "uri":"article"},
"4": { "label": "Serial-ID", "width": 200 } } ]
},
"update": {
"command": [ "UPDATE content SET treatment_name=${treatment_name}, \"position\"=${position}, ",
"article_name=${article_name}, serial_id=${serial_id}, properties=${properties}",
"WHERE treatment_name = #{treatment_name} AND position = #{position}" ]
},
"insert": {
"command": [ "INSERT INTO content(treatment_name, \"position\", article_name, serial_id,
properties)",
"VALUES (':{1,1}', ${position}, ${article_name}, ${serial_id}, ${properties})" ],
"columns": {
"treatment_name": { "default": ":{1,1}" }
}
},
"delete": {
"command": [ "DELETE FROM content WHERE treatment_name = #{treatment_name}",
"AND position = #{position}" ]
}
}
30.10.2015
© Hesotech GmbH / R. Sonnenschein
26
Next ?
Create the REST interface by
●
CREATE EXTENSION db_gate ...
30.10.2015
© Hesotech GmbH / R. Sonnenschein
30
SQL is the Constant
Because Logic is the same
Client
OS / VM
ProgrammingLanguage
DBMS
OS/2
REXX
SQL: Watcom
Win-NT
C++
SQL: Sybase
Win-XP ...
C#
SQL: Sybase
Java VM
Java
SQL: PostgreSQL
2015
Browser
JavaScript
SQL: PostgreSQL
2020
?
?
SQL: PostgreSQL
1995
30.10.2015
© Hesotech GmbH / R. Sonnenschein
31
Why using an ORM?
Quoted from Video Tutorial on ...
●
●
As a programmer, we are dealing
with classes and objects.
At a certain point, we need
to store and retrieve our data.
●
Best into a database.
●
For this, an ORM is a very appropriate tool
–
It takes over a lot of work and
–
It saves you a lot of time
30.10.2015
© Hesotech GmbH / R. Sonnenschein
32
My View
Users
Very
stable
Can be
discussed
with the
customer
30.10.2015
Real World
Customer
Buisiness Logic
Data
DB
=
Mirror of
DB-Structure
=
Interest of
Customer
© Hesotech GmbH / R. Sonnenschein
Strongly
dependent on
requirements
Program
33
http://www.changemakrs.com/LinusTorvalds
30.10.2015
© Hesotech GmbH / R. Sonnenschein
34