|=--------------------------------------------------=|
  _________________  .____     ___________________
 /   _____/\_____  \ |    |    \_   _____/\______ \
 \_____  \  /  / \  \|    |     |    __)_  |    |  \
 /        \/   \_/.  \    |___  |        \ |    `   \
/_______  /\_____\ \_/_______ \/_______  //_______  /
        \/        \__>       \/        \/         \/


|=--------------------------------------------------=|
. |03.11 - Data dictionary                           .
|=--------------------------------------------------=|

  Standard  Relational   Database  Management  System
has  metadata  stored  in Data  dictionary.  This  is
collection of tables  or views. This can  be used for
example if You  would like to display  all columns of
given table.

  Let's  assume we  would like  to display  the query
used to created a table.  For example to show the DDL
query  for  table  users2 we  would  issue  following
query:

  select sql
  from sqlite_master
  where name = 'users2';

  Here the sqlite_master table is the data dictionary
metadata  table.  Result  of   that  query  would  be
following:

    sql
  --------------------
  CREATE TABLE users2(
    user_key INT,
    user_name TEXT,
    user_password TEXT
  )

  For  some  reason  we  would  like  work  with  the
dataset of  the columns of  a given table.  In SQLite
we  would use  PRAGMA  keyword and  use the  argument
table_info(TABLE_NAME). This can  be used for example
if we  would like  to generate select  statement from
the list of columns in that table. To get the list of
column we can use:

  pragma table_info('users2'); 

  And we will get the  list of the columns with given
data-type and additional information:

    cid  name           type   ...    pk
  -----  -------------  ------ ...  ----
      0  user_key       INT    ...     0
      1  user_name      TEXT   ...     0
      2  user_password  TEXT   ...     0

  As mentioned we will now try to dynamically compose
the select  query from query to  the data dictionary.
We will  use GROUP_CONCAT to  join the rows  into one
column and use concatenate  operator to join the rows
with the  data dictionary resulting rows.  To achieve
this we will issue following query:

  select
    'select '||group_concat(name)
    || ' from users2' sql
  from pragma_table_info('users2')
  order by cid asc;

  Result  of   above  query   will  be   select  with
dynamically selected  rows in  order in  the database
metadata:

    sql
  ---------------------------------------------------
  select user_key,user_name,user_password from users2

  This  can be  extended in  case we  would have  the
index on the  table. Let's create index  on the table
users2.

  create unique index idx_users2_uk on
  users2 (user_key);  

  And  this operation  is  immediately  shown in  the
corresponding data dictionary view.  We can access it
by using the pragma again like this:

   select * 
   from pragma_index_info('idx_users2_uk');

  And database will return following reply:
  
    seqno    cid  name
  -------  -----  --------
        0      0  user_key

  And we  will get  the list of  columns used  in the
index we queried for.

  That's it for the Data dictionary part.


|=--------------------------------------------------=|
; |  | Thanks Linux and Vim | buy me a coffee   | o |;
|=--------------------------------------------------=|