in2sql: Working with a variety of ODBC sources

I continue a series of stories about OpenSource development of In2sql , which visualizes SQL objects for exporting data to Excel (in fact, this is a series of articles - documentation for development).



In the previous parts:





In this part, we'll talk about how to create a list of objects that are displayed in the navigation tree.



image



As a standard, we select 4 types of basic objects



  • Tables
  • Representation
  • Functions
  • Procedures.


Also, each database has its own objects for storing entities - for example:



  • MS SQL - stores data in sys.schemas, where it is separated by type (type = 'V' - View, type = 'U' - tables)
  • Oracle - everything is quite simple here - there are user_views and user_tables objects that store a description of the corresponding user settings
  • Vertica - v_catalog.views and v_catalog.tables
  • PostegreSQL - pg_catalog.pg_views and pg_catalog.pg_tables
  • MySQL - information_schema.views and information_schema.tables
  • DB2 - all data is stored in SYSIBM.tables where table_type = 'VIEW' are views and table_type = 'BASE TABLE' are tables.
  • ClickHouse all objects are in system.tables, the division into tables and views occurs on the engine = 'View' field


This manifold is managed by the in2SqlLibrary class, in which happens:



  • determining the type of ODBC connection, based on the driver file name (getDBType)
  • distribution of tables (getSqlTables) and views (getSqlViews) according to the corresponding types.


In order to speed up the loading of the excel plugin (addin), this data is accessed at the moment of expanding the branch of the corresponding artifact (I will talk about this in another article).



All Articles