Feature request: Database generator (part 2)

[MauricioUyaguari]

Provide a friendly and seamless user interface for generating a database definition given connection information.

The scope of this PR does not include building the database manually in database editor.

Motivation

To provide a better user experience when creating a store definition. Users should be able to build their store either by manually specifying it in the database editor (not in the scope of this PR) or by importing these from the database using connection details in tandem with an interface to select the subset of schemas/tables/views.

Implementation

• [x] Provide POC of database builder to showcase goal of generating database
• [x] FEEDBACK provide more exploratory view. Users should see schemas and lazy load relevant schemas/tables of interest, i.e. explorer-type navigation
• [x] Refactor and open source discovery API
• [x] Exporatory View
  • [x] Fetch and display all schemas of a given connection
  • [x] Lazy load tables
  • [x] Lazy load columns
  • [x] Allow selecting schema, table, view
  • [x] Show generated read only grammar on right side
• [x] When saving database
  • [x] if stub database create an empty database and set connection store to db
  • [x] write update database function to update generated database with current db
• [x] UI enchancements
  • [x] half partial selected to schema (when some selected)
• [ ] CONSIDER showing diff grammar of the generated db and current db
• [ ] CONSIDER Provide column filtering support

Previewing Data

• [ ] Add context menu to table/column
• [ ] Create Empty Graph with database initialized with given table.
• [ ] Add Class generated from Table (use same column names for property names and create a type mapper)
• [ ] Add Mapping and generate relational class mapping from Class, Table.
• [ ] Add current connection to graph
• [ ] Generate two queries for table/column preview (implementation will be in query builder module)
  • [ ] Table - projection query (shows top 10 of the current table) given a mapping, class, table and runtime. Possible name getExtraDatabaseTableMetadataQuery()
  • [ ] Column - aggregation query (same as query builder preview data see: https://github.com/finos/legend-studio/issues/324). Reuse implementation of aggregation generation query. Possible Names: getExtraDatabaseColumnAggregationQuery()
• [ ] Display grid results

Consideration

• [ ] To call build database from an existing DB editor, do we allow people to pick any DB connection or just ones bound to that store?

Test Grammar

###Relational
Database model::Empty
(
)


###Connection
RelationalDatabaseConnection model::MyConnection
{
  store: model::Empty;
  type: H2;
  specification: LocalH2
  {
    testDataSetupSqls: [
      'Drop table if exists FirmTable;\r\nDrop table if exists PersonTable;\r\nCreate Table FirmTable(id INT, Legal_Name VARCHAR(200));\r\nCreate Table PersonTable(id INT, firm_id INT, lastName VARCHAR(200), firstName VARCHAR(200));\r\nInsert into FirmTable (id, Legal_Name) values (1, \'FirmX\');\r\nInsert into PersonTable (id, firm_id, lastName, firstName) values (1, 1, \'John\', \'Doe\');\r\nDrop Schema if exists OtherSchema;\r\nCreate schema OtherSchema;\r\nDrop table if exists OtherSchema.OtherTable;\r\nCreate Table OtherSchema.OtherTable(id INT, Name VARCHAR(200));\r\nDrop Schema if exists EmptySchema;\r\nCreate schema EmptySchema;'
      ];
  };
  auth: DefaultH2;
}

Future Enchancements

• [ ] Select Specific columns
• [ ] add tests