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:

 | 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
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
  milestone: mysql_install_db should create new structure correctly
  estimation: 16 hrs

1. change directory and tables structure

   change scripts/mysql_create_system_tables.sh to add
   field "catalog" to tables db, user, host, tables_priv, columns_priv
   change scripts/mysql_install_db.sh to prepare directory

   change scripts/mysql_install_db.sh to replace
   "use mysql" by "use system.mysql"

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>.");

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

   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)
   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
   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
              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*

   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   

3. change creation of tables..

   a) add field "LEX_STRING catalog;"
      to class Table_ident in sql_class.h
   b) add parameter catalog to function check_access in sql_parse
   c) add field catalog to struct st_table_list in table.h
   d) add parameter catalog to functions check_merge_table_acces 
         in sql/sql_parse.cc 
   e) add parameter catalog to function find_real_table_in_list in
   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  {

      |                db
      |                |               table_name
      |                |               |
      V                V               V

   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
      catalog_mode_on= true;
      catalog_mode_on= false;

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
      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
      to sql/sql_catalog.cc with implementation:

   if test ! -d <datadir>/<catalog_name>
      mkdir <datadir>/<catalog_name>
      chmod 700 <datadir>/<catalog_name>
   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


   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>
     select catalog from user         where catalog=<catalog_name>
     select catalog from host         where catalog=<catalog_name>
     select catalog from tables_priv  where catalog=<catalog_name>
     select catalog from columns_priv where catalog=<catalog_name>
    ) or
    catalog not empty)
      drop error;
   rmdir <datadir>/<catalog_name>
   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
      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

   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


   d) change sql_yacc.yy to parse new syntax of CREATE SCHEMA and fill 
   (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

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);
   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


  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


   add column "catalog" to output of mysql_process_list


   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:


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] 
   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] 
   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
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;


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
   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;
   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

      fill thd->current_path.path_items
      with content of thd->lex->path_items

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
12) add CURRENT_PATH sql-variable    
13) write special test of using path