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|  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 | 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  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:
   
     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=;
    which should be turned into mysql_select_db(".");
    *todo*

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

   USE .;
    which should be turned into mysql_select_db(".");
    *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= ;
           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
      "\0\0\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 
  milestone: mysql-test-run should work correctly with new structure
              of 
  estimation: 16 hrs
---------------------------------------------------------------------

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

   if test ! -d /mysql -a -d /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 ;

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

   if test ! -d /
   then
      mkdir /
      chmod 700 /
   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]  [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=;
     delete from user         where catalog=;
     delete from host         where catalog=;
     delete from tables_priv  where catalog=;
     delete from columns_priv where catalog=;
   }
   else if (RESTRICT)
   {
    if (exist(
     select catalog from db           where catalog=
     union
     select catalog from user         where catalog=
     union
     select catalog from host         where catalog=
     union
     select catalog from tables_priv  where catalog=
     union
     select catalog from columns_priv where catalog=
    ) or
    catalog not empty)
    {
      drop error;
      return;
    }
   }
   rmdir /
      
   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 .. ...
      DROP TABLE ..
... ALTER TABLE ..
... INSERT INTO ..
... SELECT ... FROM .., .. DELETE FROM ..
........... estimation: 8 hrs --------------------------------------------------------------------- 1. change description of table_ident in sql_yacc.yy to provide variant ..
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 .. CREATE TABLE .. .... ; DROP SCHEMA ...; estimation: 16 hrs --------------------------------------------------------------------- 1. a) add class Create_table_info { const char *catalog, *db, *name; HA_CREATE_INFO create_info; List create_list; List key_list; }; to sql/sql_lex.h b) add List 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_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 "." 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 . write test 2. provide new syntaxes for mysqladmin mysqladmin create-schema [.] such a syntax should result in warning "the schema is created in the 'default' catalog" mysqladmin create-catalog mysqladmin drop-schema [.] mysqladmin drop-catalog 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 . ... mysqlcheck --[databases|schemas] . ... mysqlcheck --[all-schemas-in-catalogs|all-databases-in-catalogs] write test 4. provide syntaxes for mysqldump mysqldump . ... mysqldump --[databases|schemas] . ... 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 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 LEX::path_items; 4) add SQLCOM_SET_PATH 5) add new syntax SET PATH {[.],}+ ; 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