WL#942: Catalogs

Affects: Server-7.1   —   Status: Un-Assigned   —   Priority: Medium

common status:
stage 0 done (proposal is aproved)
stage 1 partially done (goal is achived)
stage 2 almost done (some of replication still fail)

******************************
* Short contents:
******************************

stages:
 +--------------------------------------------------
 | estimation (hrs)
 |   +--------------------------------+---------------
 |   | goal                           | milestone
-+---+--------------------------------+----------------
0| 40| detailed proposal plan         | the plan should
 |   |                                |   be aproved
-+---+--------------------------------+----------------
1| 16| <datadir> should have the      | mysql_install_db should
 |   | catalog level                  | create new structure
-+---+--------------------------------+----------------
2| 16| provide full old functionality | mysql_test_run should
 |   | with new structure of <datadir>| work correctly
-+---+--------------------------------+----------------
3| 8 | script for upgrading           | successeful
 |   | contents of old database to    | test of upgrading
 |   |         new structure          | database
-+---+--------------------------------+----------------
4| 8 | creating and removing          | test for
 |   | catalogues from client         | CREATE CATALOG ...
 |   |                                | DROP CATALOG ...
-+---+--------------------------------+----------------
5| 8 | using of                       | test which mixes tables
 |   | catalog names in all queries   | from different catalogues
 |   | wich use table name            | in one query
-+---+--------------------------------+----------------
6| 16| full syntax for                | test for such queries
 |   | CREATE SCHEMA and DROP SCHEMA  |
-+---+--------------------------------+----------------
7| 16| change grant/revoke            | new test of
 |   | functionality to use catalogs  | using grant-masks:
 |   |                                | *.*.* | catalog.*.* |
 |   |                                | catalog.db_name.*
-+---+--------------------------------+----------------
8| 16| change show procedures to      | test that demonstrate
 |   |  use catalogs                  | finding out full structure
 |   |                                |  of server
-+---+--------------------------------+----------------
9|  8| add replication options for    | new tests should be made
 |   |   using catalogues             |
-+---+--------------------------------+----------------
A| 16| change all client-tools to     | new tests should be made
 |   | use catalogs                   |
-+---+--------------------------------+----------------
B| 8 | add PATH feature               | write special test
-+---+--------------------------------+----------------
Total estimation  : 176
Emergency reserve : 8

******************************
* Full description:
******************************

---------------------------------------------------------------------
stage 0.
  stage goal: should be written detailed proposal plan
  milestone: this document should be aproved
  estimation: my work have took 40 hours, all the others depend on monty
---------------------------------------------------------------------

1. read and analyze all the information about the task
    ("SQL-99 Complete, Really", WL #588, WL #173, WL #516, WL #173,
      sql-standards, manual)
2. make evident changes in mysql, reach compileable state to research
     all dependences.. (done)
3. look at changeset, describe full list of needed changes
     (look at attachment)
4. write full proposal (this document)
5. wait for monty aprove (done)

---------------------------------------------------------------------
stage 1.
  stage goal: structure of <datadir> should be changed to add catalog
              level
  milestone: mysql_install_db should create new structure correctly
  estimation: 16 hrs
---------------------------------------------------------------------

1. change directory and tables structure

   a)
   change scripts/mysql_create_system_tables.sh to add
   field "catalog" to tables db, user, host, tables_priv, columns_priv
   *done*
   
   b)
   change scripts/mysql_install_db.sh to prepare directory
   structure:
   <datadir>
     system
       mysql
     default
       test
   *done*

   c)
   change scripts/mysql_install_db.sh to replace
   "use mysql" by "use system.mysql"
   *done*

2. provide "current" catalog..

   a) add commands on client side:

   SET CATALOG_NAME=<catalog_name>;
    which should be turned into mysql_select_db("<catalog_name>.");
    *todo*

   SET SCHEMA_NAME=<schema_name>;
    full synonym of old USE <schema_name>:
    which should be turned into mysql_select_db("<schema_name>");
    *todo*

   USE <catalog_name>.<schema_name>;
    which should be turned into mysql_select_db("<catalog_name>.<schema_name>");
    *done in sql_yacc.yy*

   b) add field catalog to THD (like field database)
 *done*
   c) add field catalog to ACL_DB and ACL_HOST in sql_acl.h *done*
   d) add parameter catalog to function acl_get in sql/sql_acl.h
 *done*
   e) add parameter catalog to mysql_change_db in sql/sql_db.cc:
           set current_thd->catalog= <catalog_name>;
           if db not presented
           {
              to reset current database
           }
           else
           {
              if (catalog not presented and !thd->catalog)
                throw error!
              to use catalog name for preparing path for load_db_opt and
           }
	   *done only for presented db and catalog*
	   *todo*

   f) change processing of COM_INIT_DB in function dispatch_command
       in sql_parse.cc to split name by '.' and pass left part
       as catalog name and right part as db name in mysql_change_db   
       *todo*

3. change creation of tables..

   a) add field "LEX_STRING catalog;"
      to class Table_ident in sql_class.h
 *done*
   b) add parameter catalog to function check_access in sql_parse
 *done*
   c) add field catalog to struct st_table_list in table.h
 *done*
   d) add parameter catalog to functions check_merge_table_acces 
         in sql/sql_parse.cc 
*done*
   e) add parameter catalog to function find_real_table_in_list in
         sql/sql_base.h
 *done*
   f) change open_and_lock_tables, open_tables and all appears from
      it (see full list in attachment).
      in particular: add field db to structure TABLE in table.h so as:

      struct st_table  {

      table_cache_key
      |                db
      |                |               table_name
      |                |               |
      V                V               V
      "<catalog_name>\0<database_name>\0<table_name>\0"

   g) add parameter catalog to functions create_table_from_items,
       mysql_create_table and quick_rm_table in sql\sql_table.cc
   h) add field catalog to select_create
   k) add correspondend fields to sql_cache module (latest change)
   j) add correspondend changes to replication
   
stage is partially done (the goal is reach, but some of the 
intermediate points aren't done totally)
   
---------------------------------------------------------------------
stage 2.
  stage goal: should be allowed full old functionality with new
              structure of <datadir>
  milestone: mysql-test-run should work correctly with new structure
              of <datadir>
  estimation: 16 hrs
---------------------------------------------------------------------

1. add global variable to mysqld.cc : bool catalog_mode_on;
2. add to main() function code like:

   if test ! -d <datadir>/mysql -a -d <datadir>/system
   then
      catalog_mode_on= true;
   else
      catalog_mode_on= false;
   fi

3. only one change is needed: turn 'mysql.table' to 'system.mysql.table'
   (it is reached by changing sql_yacc.yy)
   no any extra changes (except stage 1) supposed
   no any syntax changes supposed.
   will look at actual changes :)) (look at full list of core changes)..
   

4. debug all test on new and old structure of datadir

---------------------------------------------------------------------
stage 3.
  stage goal: should be allowed upgrade of contents of old database to
               new structure
  milestoune: should be written special test
  estimation: 8 hrs
---------------------------------------------------------------------

1. write script for filling test database
2. make dump of test database
3. write script for converting database
4. convert test database and make dump
5. compare test dump and converted dump

---------------------------------------------------------------------
stage 4.
  stage goal: should be allowed creating and removing of catalogues
  milestoune: should be done new mysql-test for queries
      CREATE CATALOG ...
      DROP CATALOG ...
  estimation: 8 hrs
---------------------------------------------------------------------

1) add file sql/sql_catalog.cc

2) CREATE CATALOG <catalog_name>;

   a) add function
      int mysql_create_catalog(THD* thd,const char *catalog, bool
            silent)
      to sql/sql_catalog.cc with implementation:

   if test ! -d <datadir>/<catalog_name>
   then
      mkdir <datadir>/<catalog_name>
      chmod 700 <datadir>/<catalog_name>
   fi
         
   b) add SQLCOM_CREATE_CATALOG
   c) add processing of SQLCOM_CREATE_CATALOG to function
      mysql_execute_command to use mysql_create_catalog
   d) add new syntax in lex.h and sql_yacc.h

2) DROP CATALOG [IF EXISTS] <catalog_name> [CASCADE|RESTRICT];

   a) add function
      int mysql_drop_catalog(THD* thd,const char *catalog,
            bool if_exists, bool cascade, bool silent)
      to sql/sql_catalog.cc with implementation:

   if (CASCADE)
   {
     delete from db           where catalog=<catalog_name>;
     delete from user         where catalog=<catalog_name>;
     delete from host         where catalog=<catalog_name>;
     delete from tables_priv  where catalog=<catalog_name>;
     delete from columns_priv where catalog=<catalog_name>;
   }
   else if (RESTRICT)
   {
    if (exist(
     select catalog from db           where catalog=<catalog_name>
     union
     select catalog from user         where catalog=<catalog_name>
     union
     select catalog from host         where catalog=<catalog_name>
     union
     select catalog from tables_priv  where catalog=<catalog_name>
     union
     select catalog from columns_priv where catalog=<catalog_name>
    ) or
    catalog not empty)
    {
      drop error;
      return;
    }
   }
   rmdir <datadir>/<catalog_name>
      
   b) add SQLCOM_DROP_CATALOG
   c) add processing of SQLCOM_DROP_CATALOG to function
      mysql_execute_command to use mysql_drop_catalog
   d) add new syntax in lex.h and sql_yacc.h

---------------------------------------------------------------------
stage 5.
  stage goal: should be allowed using of catalog names in queries
  milestone: should be done new mysql-test for queries:
      CREATE TABLE <catalog>.<scheme>.<table> ...
      DROP TABLE <catalog>.<scheme>.<table> ...
      ALTER TABLE <catalog>.<scheme>.<table> ...
      INSERT INTO <catalog>.<scheme>.<table> ...
      SELECT ... FROM
         <catalog1>.<scheme1>.<table1>,
         <catalog2>.<scheme2>.<table2>
      DELETE FROM <catalog>.<scheme>.<table>
      ...........
  estimation: 8 hrs
---------------------------------------------------------------------

1. change description of table_ident in sql_yacc.yy
     to provide variant <catalog>.<database>.<table>
2. write test, debug it.

---------------------------------------------------------------------
stage 6.
  stage goal: provide full syntax for CREATE SCHEMA
      and DROP SCHEMA
  milestone: should be done new mysql-test for queries:
    CREATE SCHEMA <schema_name>..
       CREATE TABLE <table_name>..
       ....
    ;
    DROP SCHEMA <schema_name>...;
  estimation: 16 hrs
---------------------------------------------------------------------

1.
   a) add

   class Create_table_info
   {
     const char *catalog, *db, *name;
     HA_CREATE_INFO create_info;
     List<create_field> create_list;
     List<key> key_list;
   };

   to sql/sql_lex.h

   b) add
   List<Create_table_info> create_tables;
   to struct LEX

   c) add SQLCOM_CREATE_SCHEME

   d) change sql_yacc.yy to parse new syntax of CREATE SCHEMA and fill 
create_tables
   (test it)
   
2. a) add function
      int mysql_create_scheme(THD *thd, const char *name, bool
                               List<Create_table_info> create_tables)
      to sql/sql_catalog.cc
   b) add processing of SQLCOM_CREATE_SCHEME to mysql_execute_command
      to use mysql_create_scheme (test it)
      and mysql_change_db (to change current schema)

3. change sql_yacc.yy to parse new syntax of DROP SCHEMA
   like DROP DATABASE..

---------------------------------------------------------------------
stage 7.
  stage goal: change grant/revoke functionality to use catalogs
  milestone: should be allowed syntax
    GRANT ...
    ON {tbl_name | * | *.* | db_name.* |
           *.*.* | catalog.*.* | catalog.db_name.* }
    ...
  estimation: 16 hrs
---------------------------------------------------------------------

1. add new system table "catalog"
    with fields host, catalog, user, privilefies
2. add new structure ACL_CATALOG in sql_acl.h
3. add function check_grant_catalog
5. change other acl-procedure to take into account check_grant_catalog
4. change sql_yacc.yy to provide new syntax
5. write new tests

---------------------------------------------------------------------
stage 8.
  stage goal: change show procedures to use catalogs
  milestone: should be allowed to find out
                         structure of server from client
  estimation: 16 hrs
---------------------------------------------------------------------

1. add syntax SHOW CATALOGS [LIKE wild]
   a) add function int mysql_show_catalogs(THD *thd, const char *wild);
   b) add SQLCOM_SHOW_CATALOGS
   c) add processing of SQLCOM_SHOW_CATALOGS to mysql_execute_command
      to use mysql_show_catalogs
   d) add new syntax in sql_yacc.yy, lex.h

2. SHOW DATABASES [LIKE wild]

  change int mysqld_show_catalogs(THD *thd, const char *wild);
  to use wilds like "<catalog_name>.<database_name>"

3. SHOW [OPEN] TABLES [FROM [catalog.][db_name]] [LIKE wild]
   SHOW TABLE STATUS [FROM [catalog.][db_name]] [LIKE wild]

   a) change processing of db_opt to use [FROM [catalog.][db_name]]
      syntax (store catalog name in lex->select_lex.catalog)
   b) add parameter catalog to
      mysqld_show_tables, mysqld_extend_show_tables, mysqld_show_open_tables
   c) change invocation of above functions to use catalog
   d) add to output of above functions column "catalog"

4. SHOW [FULL] COLUMNS FROM tbl_name [FROM [catalog.][db_name]] [LIKE wild]
   SHOW INDEX FROM tbl_name [FROM [catalog.][db_name]]

   change syntax of commands to use catalog in created TABLE

5. SHOW [FULL] PROCESSLIST

   add column "catalog" to output of mysql_process_list

6. SHOW GRANTS FOR user

   modify function mysql_show_grants in sql_acl to output catalog grants

7. add CURRENT_SCHEMA, CURRENT_CATALOG local and global variables

8. modify tests to check new features

---------------------------------------------------------------------
stage 9.
  stage goal: add replication options for
  milestone: new tests should be made
  estimation: 8 hrs
---------------------------------------------------------------------

1. options above should be added to mysldd:

--replicate-do-catalog
--replicate-ignore-catalog

2. extend --replicate-wild-do-table to handle catalogs.

---------------------------------------------------------------------
stage A.
  stage goal: change all client-tools to use catalogs
  milestone: check all occurencies of "database" in client-tools and
              allow analogue for "catalog", write tests for them
  estimation: 16 hrs
---------------------------------------------------------------------

1. provide new syntaxes for mysql
   add option --catalog to mysql
   provide syntaxes
     mysql <catalog_name>.<database_name>
   write test
2. provide new syntaxes for mysqladmin
     mysqladmin create-schema [<catalog_name>.]<database_name>
       such a syntax should result in warning 
       "the schema is created in the 'default' catalog"
     mysqladmin create-catalog <catalog_name>
     mysqladmin drop-schema [<catalog_name>.]<database_name>
     mysqladmin drop-catalog <catalog_name>
   write test
3. provide syntaxes for mysqlbinlog
     add option --catalog to mysqlbinlog
     add option --schema as synonym of --datatabase option
   write test
3. provide syntaxes for mysqlcheck
     mysqlcheck <catalog_name>.<database_name> ...
     mysqlcheck --[databases|schemas] <catalog_name>.<database_name> ...
     mysqlcheck --[all-schemas-in-catalogs|all-databases-in-catalogs] 
<catalog_name>
   write test
4. provide syntaxes for mysqldump
     mysqldump <catalog_name>.<database_name> ...
     mysqldump --[databases|schemas] <catalog_name>.<database_name> ...
     mysqldump --[all-schemas-in-catalogs|all-databases-in-catalogs] 
<catalog_name>
   write test
5. provide syntaxes for mysqlhotcopy
     mysqlhotcopy catalog.db_name [/path/to/new_directory]
     mysqlhotcopy catalog1.db_name_1 ... 
catalogn.db_name_n /path/to/new_directory
     mysqlhotcopy catalog.db_name./regex/
     mysqlhotcopy catalog./regex/./regex/
     mysqlhotcopy catalog.
   write test
6. provide syntaxes for mysqlimport
     mysqlimport [options] catalog.database textfile1 [textfile2 ...]
   write test
7. provide syntaxes for mysqlshow
     mysqlshow [OPTIONS] [catalog.database [table [column]]]
   write test
8. 
   
---------------------------------------------------------------------
stage B.
  stage goal: add and test PATH feature
  milestone: should be done special test
  estimation: 8 hrs
---------------------------------------------------------------------

1) add file sql/sql_path.cc
2) add
   struct Path_item
   {
    LEX_STRING catalog;
    LEX_STRING schema;
   };

   to sql_sql_path.cc

3) add List<Path_item> LEX::path_items;

4) add SQLCOM_SET_PATH

5) add new syntax
    SET PATH {[<catalog_name>.]<schema_name>,}+ ;
   this statement should fill LEX::path_items
   and set LEX::sql_command to SQLCOM_SET_PATH

6) add class

class Path_resolver
{
  private:
   HASH path_item_by_table_name; // map 'table_name' -> Path_item;

   /* List of all created resolvers : */
   Path_resolver *next, *prev;
   static Path_resolver *path_resolvers;
  public:
   DYNAMIC_ARRAY path_items; // all items
  
   void reset();

   /* look through all schemas in path and fill hash */
   void fill_hash();

   /* look for catalog and schema name for table name.. */
   bool specify_table(LEX_STRING *table,
                      LEX_STRING *schema, LEX_STRING *catalog);

   /*
      These methods should look through all existed Path_resolver-s
      and correct their hash according to changes of metadata
   */
   static void on_create_table
    (const char *catalog, const char *schema, const char *table);
   static void on_drop_table
    (const char *catalog, const char *schema, const char *table);
};

7) add variable
   Path_resolver THD::current_path;

8) add to mysql_create_table, mysql_rm_table invocations of
   Path_resolver::on_create_table and Path_resolver::on_drop_table

9) add processing of SQLCOM_SET_PATH to mysql_execute_command

    thd->current_path.reset();
    /*
      fill thd->current_path.path_items
      with content of thd->lex->path_items
    */
    .....
    thd->current_path.fill_hash();

10) add new goal to sql_yacc.yy named 'create_table_ident' as full
    copy of table_ident. rewrite goal 'create' to use
    'create_table_ident' instead of 'table_ident'

11) add to goal 'table_ident' invocation of
    thd->current_path.specify_table(....);
    
12) add CURRENT_PATH sql-variable    
    
13) write special test of using path