dashjoin/platform

Dashjoin Is an Open Source & Cloud Native Low Code Development and Integration Platform that helps teams deliver applications faster 🚀 Uses a data-driven approach inspired by Linked Data to make use of your existing assets

Open Source Low Code & AI Development Platform

For anyone who is planning a development project, faces a tight schedule, needs to present results quickly, or has limited development resources available. Dashjoin taps into and integrates your existing data sources and allows you to intuitively browse, search, edit, and visualize your integrated information. Add AI empowered business logic to enable users and automation to act on the information. Unlike other Low Code Development Platforms, Dashjoin offers a free open source version with a commercial PaaS and bases on a unique, linked-data inspired approach to scalable data integration.

Features

Universal DB Frontend Connect to any SQL or NoSQL database, browse, search, and edit its contents. Extend the schema on the fly.	Artificial Intelligence Seamlessly incorporate large language models, image classification, translation, and entity reconciliation services in your App.	Query Catalog & Editor Automatically collect schema information that powers an intuitive graphical query editor!
Layout Designer Graphically customize the layout for different types found in the database. Leverage the query editor to include meaningful charts.	Functions and Processes Seamlessly start and integrate REST services from the application and monitor progress.	Data Integration and Federation Visually map external data sources to your model. Load the data into a warehouse or federate the source into a virtual linked-data graph.
Rich Support Matrix Dashjoin supports a wide variety of SQL and NoSQL databases. You can define relationships between databases in order to enable a seamless browsing experience.

Getting Started

• Docker: docker run -p 8080:8080 -e DJ_ADMIN_PASS=djdjdj dashjoin/platform
• Download
• Playground
• Book your fully managed PaaS tenant (Platform plus PostgreSQL)

Learn

• Online Documentation
• Live demo
• Blog
• Training course
• Video tutorials

Contribute

We welcome contributions. If you are interested in contributing to Dashjoin, let us know! You'll get to know an open-minded and motivated team working together to build the next generation platform.

• Join our Slack and say hello
• Follow us on Twitter
• Submit your ideas by opening an issue with the enhancement label
• Help out by fixing "a good first issue"

platform's Issues

going into edit mode on the Info page initially does not show the if-display=false block

if user is admin, edit should show all conditionals such they can be modified

Support OpenLink Virtuoso

some fixes in the JDBC code required:

1. IS_AUTOINCREMENT is not supported

2. limit / offset not supported: use scrollable cursors
   http://docs.openlinksw.com/virtuoso/vscrlcursors/
   http://docs.openlinksw.com/virtuoso/openfetchetcintostmts/

RDF4J: items per page incorrect

see
http://localhost:4200/#/resource/config/Table/dj%2Frdf4j%2Fhttp:%2F%2Fwww.semwebtech.org%2Fmondial%2F10%2Fmeta%23City

counts triples, not table rows

Provide example for DB triggers

Typical use case is to track changes (user, timestamp) in DB columns.

foreach jsonata evaluation broken

foreach sets the page context value
in this case, the value must be sent to the backend in order to be evaluated

Indication that filter in graphical editor incompletely shows SQL query

I can understand that the filter line in the graphical editor cannot show all SQL clauses completely. In cases where the graphical filter is incomplete, would it be possible to have an asterisk or some other indication to point this out?

In the picture, the SQL is >40000 and <50000 while the graphical editor only shows >40000.

Need an approach to caching for RestJson

Many APIs have a significant delay and call quotas.
Therefore, we should avoid calling an API with the same parameters over and over.

Possible approaches are:

1. Specify cache timeout with the function
   maintain an in memory cache: Map<function, Map<parameters, result>>
   pro: fast & simple to implement
   con: might end up with redundant caches on different cluster nodes

2. allow some form of "download" in order to persist API results in a data lake

Approach 1) is probably better since it should be transparent to the user how caching is done. The cache might actually persist the data in a cluster object store also.
Maybe start with a simple in mem impl. and allow switching to a more powerful caching strategy later

allow configuring some basic UI settings

background color

changed in instance component:

<mat-sidenav-content style="... background-color: #...;">

sidenavOpen condition

changed in appservice:

  sidenavOpen = false;

changing the font

in my-theme.scss

@import '~@angular/material/theming';

@font-face {
    font-family: din;
    src: url(assets/DINRegular.ttf);
}

$custom-typography: mat-typography-config($font-family: 'din');
@include mat-core($custom-typography);

changing theme colors

in my-theme.scss

$candy-app-primary: mat-palette($mat-orange,800);

cookie text and links

cookieconsent.ts

login screen

login.component.html

Doughnut chart colours change randomly on page reload

The query results are constant. The first couple of colors stay the same, starting from around the 10th position, the colors change

Allow defining table and column display names

We can use the json schema title attribute for this.
The value can be computed with a heuristic:

• CamelCase => Camel Case
• lowercase => Lowercase
• UPPER => Upper

The user can override this value in the table / column metadata section.

Charts / tables must use the title also.

Search results from different types point to wrong resource

Save layout deletes the DB password

happens whenever the table or instance layout is written for the first time (i.e. key "tables") is not yet present

Fix dirty setTimeout code in InstanceComp

Happens in 3 places. Need to get access to underlying Observable/Promise

SQL metadata extraction ignores fks pointing to the same table

The structure
org.dashjoin.service.Metadata.MdTable.fks
maps table to fk and thus ignores columns in the case where multiple columns point to the same table.
An example is a "case" table that defines two fks (creator, responsible) pointing to person(id)

changes to the JSON upload

when uploading a file, the behavior for JSON files should be to contain an array of objects.
As with CSV, the table name is derived from the file name.

For the config DB we should support uploading a model folder containing the config DB.

Also add this to the documentation of the demo application (how to install), and remove some the unused java code

Add log for any changes made on databases through the platform

Such an audit trail would make the query-editor acceptable to companies with strong internal audit departments (such as banks).

Which fields should be included?

• Operation: create, update, delete, read? (could lead to very excessive logging)
• User
• Timestamp
• Result (success, ACL violation)

Operation: Create, update, delete. I would omit read (would only be useful for extremely sensitive data; but you presumably would protect that differently.

• User / timestamp / result as listed above.

Query performance table shows "null" query

caused by DDL statements

Yugabyte SQL: GROUP_CONCAT not supported

Function is called string_aggr
https://blog.yugabyte.com/distributed-sql-tips-and-tricks-april-3-2020/

org.dashjoin.service.ExMapper toResponse
INFO: REST exception
org.postgresql.util.PSQLException: ERROR: function group_concat(character varying) does not exist
Hint: No function matches the given name and argument types. You might need to add explicit type casts.
Position: 65
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2533)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2268)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:313)
at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:448)
at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:369)
at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:310)
at org.postgresql.jdbc.PgStatement.executeCachedSql(PgStatement.java:296)
at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:273)
at org.postgresql.jdbc.PgStatement.executeQuery(PgStatement.java:226)
at org.apache.commons.dbcp2.DelegatingStatement.executeQuery(DelegatingStatement.java:329)
at org.apache.commons.dbcp2.DelegatingStatement.executeQuery(DelegatingStatement.java:329)
at org.dashjoin.service.SQLDatabase.getMetadata(SQLDatabase.java:680)
at org.dashjoin.service.SQLEditor.prettyPrint(SQLEditor.java:600)
at org.dashjoin.service.SQLEditor.setGroupBy(SQLEditor.java:352)
at org.dashjoin.service.QueryEditor$Delegate.setGroupBy(QueryEditor.java:78)

serialization exception when function type is changed and "old" properties are still in the JSON config

Yugabyte SQL: filtering in query editor UI throws SQL error

Data source: Chinook
Table: Artist

choosing filter "AC/DC" (actually any) leads to:

org.dashjoin.service.ExMapper toResponse INFO: REST exception
org.postgresql.util.PSQLException: ERROR: missing FROM-clause entry for table "artist"
Position: 251
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2533)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2268)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:313)
at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:448)
at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:369)
at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:310)
at org.postgresql.jdbc.PgStatement.executeCachedSql(PgStatement.java:296)
at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:273)
at org.postgresql.jdbc.PgStatement.executeQuery(PgStatement.java:226)
at org.apache.commons.dbcp2.DelegatingStatement.executeQuery(DelegatingStatement.java:329)
at org.apache.commons.dbcp2.DelegatingStatement.executeQuery(DelegatingStatement.java:329)
at org.dashjoin.service.SQLDatabase.getMetadata(SQLDatabase.java:680)
at org.dashjoin.service.SQLEditor.prettyPrint(SQLEditor.java:600)
at org.dashjoin.service.SQLEditor.setWhere(SQLEditor.java:263)

upload features should be available for ETL also

handling of SQLite and XLS

Add map widget

https://github.com/ultrasonicsoft/ng-openstreetmap-demo

gmaps is also possible but more complex since it requires an API key setup

Stale DB metadata causes several issues

Consider the following scenario:

1. table is create in the DB
2. user defines metadata like table label / fk relationships
3. table is deleted

in this case, the table still "exists" but only with partial data. It also is ok not to delete the metadata, in case the table is only temporarily unavailable (e.g. a temporary ACL issue).

The platform should therefore filter this partial table info and ignore it in places where it would lead to errors. We already have some checks in certain places, but this should be centralized since it is not obvious to the developer.

Ability to directly run queries in Query Catalog

Useful for tasks like data refresh, index creation, temp data cleanup etc.

aggregation on SQL server broken

SELECT
COUNT("ProductCategory"."ProductCategoryID"), COUNT("ProductCategory"."ParentProductCategoryID"), COUNT("ProductCategory"."ModifiedDate"), "ProductCategory"."Name"
FROM
SalesLT."ProductCategory"
GROUP BY
"ProductCategory"."Name"

ERROR Error: Could not find column with id "".
at getTableUnknownColumnError (table.js:1277)
at table.js:2436
at Function.from ()

most likely the column metadata is broken

Container widget does not check roles

&& isInRole(layout) missing in container.component.html

RDF4J: edit does not show all values

see
http://localhost:4200/#/resource/rdf4j/http:%2F%2Fwww.semwebtech.org%2Fmondial%2F10%2Fmeta%23City/http:%2F%2Fwww.semwebtech.org%2Fmondial%2F10%2Fcountries%2FAL%2Fcities%2FKor%C3%A7%C3%AB%2F

editing page root throws JS error and open a dyfunctional dialog

core.js:6210 ERROR TypeError: Cannot read property 'icon' of undefined
at WidgetListComponent_Template (widgetlist.component.html:5)

dialog shows only an empty select panel with ok / cancel

by design, the page widget shows toolbar and sidenav and does not allow editing them

home button redirects to http://localhost:4200/#/ and shows empty page

also a custom role start page is ignored

better example for demo app

include a second database with IOT like data that maps to customers
add a real email example
add an aggregation firebase query

XLS upload shows formula

probably need to use another API call to get the computed value

firestore DB should not appear

Clicking search icon allows empty / null search

RDF does not show outgoing links

see
http://localhost:4200/#/resource/rdf4j/http:%2F%2Fwww.semwebtech.org%2Fmondial%2F10%2Fmeta%23City/http:%2F%2Fwww.semwebtech.org%2Fmondial%2F10%2Fcountries%2FTR%2Fprovinces%2FVan%2Fcities%2FVan%2F

npe during ETL

2021-06-21 13:28:34,714 INFO [org.das.ser.ExMapper] (executor-thread-99) REST exception: java.lang.NullPointerException
at org.dashjoin.mapping.AbstractSource.runInternal(AbstractSource.java:153)
at org.dashjoin.mapping.AbstractSource.run(AbstractSource.java:85)
at org.dashjoin.mapping.AbstractSource.run(AbstractSource.java:29)
at org.dashjoin.function.FunctionService.callInternal(FunctionService.java:70)
at org.dashjoin.function.FunctionService.callInternal(FunctionService.java:57)
at org.dashjoin.expression.ExpressionService$Call.invoke(ExpressionService.java:495)
at com.api.jsonata4java.expressions.ExpressionsVisitor.visitFunction_call(ExpressionsVisitor.java:1754)

most likely caused by partial table metadata

cannot click on pk with value 0

probably equality issue in TS code:

if (value) ...

if value = 0, this expression is false

Avoid infinite trigger recursion

It is quite easy to get into an endless recursion on $update triggers if the update trigger calls $update.
This is not unusual, if we want to write some additional fields.

RDF4J: IRIs do not show hyperlinks (e.g. column s:locatedAt)

see
http://localhost:4200/#/resource/config/Table/dj%2Frdf4j%2Fhttp:%2F%2Fwww.semwebtech.org%2Fmondial%2F10%2Fmeta%23City

Need confirmation for layout changes

Edits and deletes need to be protected

better support JSON types in the DB

current version supports basic functionality

missing still:

schema definition so we can use the CRUD editor
support in DBs other than postgres

Query Editor post release issues

https://github.com/dashjoin/query-editor/milestone/7

exasol issues

offset queries are only supported in conjunction with order by
https://docs.exasol.com/sql/select.htm

insert broken because getGeneratedKeys is not yet supported in the JDBC driver

Handling of DB schema for sybase and oracle

SQL Server:

reproduced with the adventureworks sample DB

Catalog = AdventureWorksLT2019 (can be specified via the JDBC URL: jdbc:jtds:sqlserver://localhost:1433/AdventureWorksLT2019)
Schema = SalesLT

The schema cannot be specified. In "org.dashjoin.service.Metadata.getSchema(Connection, String)" we have a default of schema=dbo for SQL server, however this code is never reached since the DB connection pool swallows the AbstractMethodError thrown in net.sourceforge.jtds.jdbc.JtdsConnection.getSchema()

If we want to specify tables from another schema for instance in an SQL statement, we need to write:
select * from schema.table

edit related widget does not use data API, was: multiple issues with edit related widget

https://github.com/dashjoin/query-editor/issues/417

table based on query does not sort

https://github.com/dashjoin/query-editor/issues/414

RestJson function needs to support caching

add a cache timeout value in the function definition.
depending on the type of service, the timeout could be vastly different:
stock market values might need a short timeout where things like geolocations might have almost indefinite timeouts

Strange behavior when session storage is full

ERROR DOMException: Failed to set a named property on 'Storage': Setting the value of 'context' exceeded the quota.
at SafeSubscriber._next (http://localhost:4200/main.js:10799:56)
at SafeSubscriber.__tryOrUnsub (http://localhost:4200/vendor.js:50169:16)
at SafeSubscriber.next (http://localhost:4200/vendor.js:50108:22)
at Subscriber._next (http://localhost:4200/vendor.js:50058:26)

Dev mode showed this trace:

       this.root = true;
            this.init();
            if (this.table) {
                this.http.get(this.url).subscribe(res => {
                    // value loaded
                    if (this.database === 'config' && this.table === 'Table') {
                        // we are on a table page
                        const tmp = _util__WEBPACK_IMPORTED_MODULE_20__["Util"].parseTableID(this.pk1);
                        this.app.getSchema(tmp[1], tmp[2]).subscribe(mycache => {
                            // read myself since there might be unsaved layout edits in the app.cache
                            if (mycache.tableLayout) {
                                this.setLayout(mycache.tableLayout, 'table', null);
                                this.value = res;
                                this.doLayout();
                                this.label().subscribe(l => this.titleService.setTitle(l));
                            }
                            else {
                                // use the default table layout
                                this.app.getSchema('config', 'Table').subscribe(table => {
                                    this.setLayout(table.instanceLayout, 'defaulttable', null);
                                    this.value = res;
                                    this.doLayout();
                                    this.label().subscribe(l => this.titleService.setTitle(l));
                                });
                            }
                            const contextAndValue = this.context();
                            contextAndValue.value = this.value;

                            sessionStorage.context = JSON.stringify(contextAndValue);

<<<
});
}

document Receive / StreamETL functions

Improve timestamp display (e.g. on the Functions page)

currently shows this format:
2021-06-11T16:52:33.633+00:00
in both table and the display widget.

Problems with this are:

• too long, timezone and milliseconds are irrelevant
• the time must be adapted to the browser's time zone

Show preview of related resources vs IDs

Avoid "raw" IDs whenever possible