|
|
|
|
When a connection is opened for the first time, the tool get all the possible meta data associated to that connection (list of tables, table's columns and their constraints, views, etc). The meta data are referred to when the user wants for example to list a table's attributes, or for command line completion.
If some modifications to the database structure have been made using a tool not using Libgda, then the meta data must be updated using the .meta command, which does not output anything unless an error occurred. Updates to the meta data are automatic when a shema change is done using Libgda if the connection has been opened using the GDA_CONNECTION_OPTIONS_AUTO_META_DATA flag (which is the case for Libgda's own tools).
As the meta data are also stored in a database, the console tool allows one to directly execute SQL commands in the meta data database associated to a connection. The meta data connection associated to a connection is by convention named as the tilde character concatenated with the connection name (for example if the connection is named "cnc1", then the connection to its meta data will be named "~cnc1"). To open a meta data connection, make sure the current connection is the one for which you want to access the meta data, and then use the .c ~ command (note that the same command will return to the "cnc1" connection):
cnc1> .c ~
Getting database schema information, this may take some time... Done.
~cnc1> .dt
List of tables
Schema | Name | Type | Owner | Description
-------+----------------------------------+------------+-------+------------
main | _attributes | BASE TABLE | |
main | _builtin_data_types | BASE TABLE | |
main | _character_sets | BASE TABLE | |
main | _check_column_usage | BASE TABLE | |
main | _collations | BASE TABLE | |
main | _columns | BASE TABLE | |
main | _domain_constraints | BASE TABLE | |
main | _domains | BASE TABLE | |
main | _element_types | BASE TABLE | |
main | _enums | BASE TABLE | |
main | _information_schema_catalog_name | BASE TABLE | |
main | _key_column_usage | BASE TABLE | |
main | _parameters | BASE TABLE | |
main | _referential_constraints | BASE TABLE | |
main | _routine_columns | BASE TABLE | |
main | _routines | BASE TABLE | |
main | _schemata | BASE TABLE | |
main | _table_constraints | BASE TABLE | |
main | _tables | BASE TABLE | |
main | _triggers | BASE TABLE | |
main | _udt | BASE TABLE | |
main | _udt_columns | BASE TABLE | |
main | _view_column_usage | BASE TABLE | |
main | _views | BASE TABLE | |
main | gda_sql_query_buffers | BASE TABLE | |
(25 rows)
~cnc1> SELECT table_name FROM _tables;
table_name
---------------
customers
locations
orders
order_contents
roles
salesrep
sales_orga
warehouses
categories
products
products_copied
(11 rows)
~cnc1> .c ~
cnc1>
Also note that the meta data's connections are listed among the opened connections, as shown:
cnc1> .c
List of opened connections
Name | Provider | DSN or connection string | Username
------+----------+-------------------------------------------------------+---------
cnc1 | SQLite | SalesTest |
~cnc1 | SQLite | DB_DIR=/home/vivien/.libgda;DB_NAME=gda-sql-SalesTest |
(2 rows)
cnc1>
It is possible for any application to create some extra objects in that database for its own purposes, with the constraint that it should not create objects with names starting with an underscore (these names are reserved for Libgda's own purposes). In the previous example, the table named "gda_sql_query_buffers" is the table internally used by the console tool to store the contents of named query buffers.
Use the .dt command to list all the tables (or list only one table if the table name is specified as an argument to the command):
cnc1> .dt
List of tables
Schema | Name | Type | Owner | Description
-------+-----------------+------------+-------+------------
main | categories | BASE TABLE | |
main | customers | BASE TABLE | |
main | locations | BASE TABLE | |
main | order_contents | BASE TABLE | |
main | orders | BASE TABLE | |
main | products | BASE TABLE | |
main | products_copied | BASE TABLE | |
main | roles | BASE TABLE | |
main | sales_orga | BASE TABLE | |
main | salesrep | BASE TABLE | |
main | warehouses | BASE TABLE | |
(11 rows)
cnc1> .dt customers
List of tables
Schema | Name | Type | Owner | Description
-------+-----------+------------+-------+------------
main | customers | BASE TABLE | |
(1 row)
cnc1>
To display the details about a single table, use the .d <table_name> command:
cnc1> .d customers
List of columns for table 'customers'
Column | Type | Nullable | Default | Extra
------------------+---------+----------+---------+---------------
id | integer | no | | Auto increment
name | string | no | '' |
default_served_by | integer | yes | |
country | string | yes | |
city | string | yes | |
(5 rows)
Primary key 'primary_key' (id)
Foreign key 'fk_locations' (country, city) references main.locations (country, city)
Foreign key 'fk_salesrep' (default_served_by) references main.salesrep (default_served_by)
cnc1>
Finally, the .graph [TABLE1 [TABLE2...]] will create a graph of all the tables (or only the tables mentioned as arguments). The graph creates a GraphViz file named "graph.dot" which can then be processed with the GraphViz'dot command to produce an image or a PDF file for example.
If the GDA_SQL_VIEWER_PNG or GDA_SQL_VIEWER_PDF environment variables are set
(for example to eog or evince) and if the dot command
is installed, then the console tool will perform the transformation and display the graph (if under a graphical session).
The following figure shows an example of graph:
The console tool can report information about views. Use the .dv command to list all the views (or list only one view if the view name is specified as an argument to the command):
cnc1> .dv
List of views
Schema | Name | Type | Owner | Description
-------+--------------+------+-------+------------
main | cust_summary | VIEW | |
(1 row)
cnc1>
One can also get more information for a single view using the .d <view name> command, for example:
cnc1> .d cust_summary List of columns for view 'cust_summary' Column | Type | Nullable | Default | Extra ---------+--------+----------+---------+------ name | string | yes | | shortcut | string | yes | | (2 rows) View definition: CREATE VIEW cust_summary as SELECT c.name, l.shortcut FROM customers c LEFT JOIN locations l ON (c.country=l.country AND c.city=l.city) cnc1>
Some databases feature the notion of schema which is a container for database objects such as tables, views, etc. Use the .dn command to get a list of all the schemas in a database. For example with a PostgreSQL database:
List of schemas
Schema | Owner | Internal
-------------------+----------+---------
information_schema | postgres | yes
pg_catalog | postgres | yes
pg_temp_1 | postgres | yes
pg_toast | postgres | yes
pg_toast_temp_1 | postgres | yes
public | postgres | no
(6 rows)
cnc1>
The meta data database holds a lot of information about the many objects which exist in a database, but the console tool does not provide internal commands to display all of them. The solution is to run SELECT commands in the meta data connection associated to a connection. For example to get a list of triggers where the "cnc2" is a connection opened to a PostgreSQL's database, one first needs to connection to the meta data connection and lookup into the "_triggers" table:
cnc2> .c ~ ~cnc2> select trigger_name, event_manipulation, event_object_table from _triggers; trigger_name | event_manipulation | event_object_table ------------------------+--------------------+------------------- pg_sync_pg_database | INSERT | pg_database pg_sync_pg_database | DELETE | pg_database pg_sync_pg_database | UPDATE | pg_database pg_sync_pg_authid | INSERT | pg_authid pg_sync_pg_authid | DELETE | pg_authid pg_sync_pg_authid | UPDATE | pg_authid pg_sync_pg_auth_members | INSERT | pg_auth_members pg_sync_pg_auth_members | DELETE | pg_auth_members pg_sync_pg_auth_members | UPDATE | pg_auth_members (9 rows) ~cnc2>
The meta data's database structure is described in the related section.