sp.cc 50.2 KB
Newer Older
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19
/* Copyright (C) 2002 MySQL AB

   This program is free software; you can redistribute it and/or modify
   it under the terms of the GNU General Public License as published by
   the Free Software Foundation; either version 2 of the License, or
   (at your option) any later version.

   This program is distributed in the hope that it will be useful,
   but WITHOUT ANY WARRANTY; without even the implied warranty of
   MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
   GNU General Public License for more details.

   You should have received a copy of the GNU General Public License
   along with this program; if not, write to the Free Software
   Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA */

#include "mysql_priv.h"
#include "sp.h"
#include "sp_head.h"
20
#include "sp_cache.h"
21
#include "sql_trigger.h"
22

23 24
static bool
create_string(THD *thd, String *buf,
25
	      int sp_type,
26
	      sp_name *name,
27 28 29 30
	      const char *params, ulong paramslen,
	      const char *returns, ulong returnslen,
	      const char *body, ulong bodylen,
	      st_sp_chistics *chistics);
31 32 33 34 35
static int
db_load_routine(THD *thd, int type, sp_name *name, sp_head **sphp,
                ulong sql_mode, const char *params, const char *returns,
                const char *body, st_sp_chistics &chistics,
                const char *definer, longlong created, longlong modified);
36

37 38 39 40 41 42
/*
 *
 * DB storage of Stored PROCEDUREs and FUNCTIONs
 *
 */

43 44
enum
{
45
  MYSQL_PROC_FIELD_DB = 0,
46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62
  MYSQL_PROC_FIELD_NAME,
  MYSQL_PROC_FIELD_TYPE,
  MYSQL_PROC_FIELD_SPECIFIC_NAME,
  MYSQL_PROC_FIELD_LANGUAGE,
  MYSQL_PROC_FIELD_ACCESS,
  MYSQL_PROC_FIELD_DETERMINISTIC,
  MYSQL_PROC_FIELD_SECURITY_TYPE,
  MYSQL_PROC_FIELD_PARAM_LIST,
  MYSQL_PROC_FIELD_RETURNS,
  MYSQL_PROC_FIELD_BODY,
  MYSQL_PROC_FIELD_DEFINER,
  MYSQL_PROC_FIELD_CREATED,
  MYSQL_PROC_FIELD_MODIFIED,
  MYSQL_PROC_FIELD_SQL_MODE,
  MYSQL_PROC_FIELD_COMMENT,
  MYSQL_PROC_FIELD_COUNT
};
63

64 65
bool mysql_proc_table_exists= 1;

66 67 68
/* Tells what SP_DEFAULT_ACCESS should be mapped to */
#define SP_DEFAULT_ACCESS_MAPPING SP_CONTAINS_SQL

69 70 71 72 73 74

/*
  Close mysql.proc, opened with open_proc_table_for_read().

  SYNOPSIS
    close_proc_table()
75 76 77 78
      thd     Thread context
      backup  Pointer to Open_tables_state instance which holds
              information about tables which were open before we
              decided to access mysql.proc.
79 80
*/

81
void close_proc_table(THD *thd, Open_tables_state *backup)
82
{
83
  close_thread_tables(thd);
84
  thd->restore_backup_open_tables_state(backup);
85
}
86

87 88 89 90 91 92

/*
  Open the mysql.proc table for read.

  SYNOPSIS
    open_proc_table_for_read()
93 94 95 96
      thd     Thread context
      backup  Pointer to Open_tables_state instance where information about
              currently open tables will be saved, and from which will be
              restored when we will end work with mysql.proc.
97 98 99 100 101 102 103 104 105 106 107 108 109

  NOTES
    Thanks to restrictions which we put on opening and locking of
    this table for writing, we can open and lock it for reading
    even when we already have some other tables open and locked.
    One must call close_proc_table() to close table opened with
    this call.

  RETURN
    0	Error
    #	Pointer to TABLE object of mysql.proc
*/

110
TABLE *open_proc_table_for_read(THD *thd, Open_tables_state *backup)
111 112 113
{
  TABLE_LIST tables;
  TABLE *table;
114
  bool not_used;
115
  DBUG_ENTER("open_proc_table");
116

117
  /*
118 119
    Speed up things if mysql.proc doesn't exists. mysql_proc_table_exists
    is set when we create or read stored procedure or on flush privileges.
120
  */
121 122
  if (!mysql_proc_table_exists)
    DBUG_RETURN(0);
123

124
  thd->reset_n_backup_open_tables_state(backup);
125 126 127 128

  bzero((char*) &tables, sizeof(tables));
  tables.db= (char*) "mysql";
  tables.table_name= tables.alias= (char*)"proc";
129
  if (!(table= open_table(thd, &tables, thd->mem_root, &not_used,
130
                          MYSQL_LOCK_IGNORE_FLUSH)))
131
  {
132
    thd->restore_backup_open_tables_state(backup);
133 134
    mysql_proc_table_exists= 0;
    DBUG_RETURN(0);
135
  }
136

137 138 139 140
  DBUG_ASSERT(table->s->system_table);

  table->reginfo.lock_type= TL_READ;
  /*
141 142
    We have to ensure we are not blocked by a flush tables, as this
    could lead to a deadlock if we have other tables opened.
143 144
  */
  if (!(thd->lock= mysql_lock_tables(thd, &table, 1,
145
                                     MYSQL_LOCK_IGNORE_FLUSH, &not_used)))
146
  {
147
    close_proc_table(thd, backup);
148
    DBUG_RETURN(0);
149
  }
150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212
  DBUG_RETURN(table);
}


/*
  Open the mysql.proc table for update.

  SYNOPSIS
    open_proc_table_for_update()
      thd  Thread context

  NOTES
    Table opened with this call should closed using close_thread_tables().

  RETURN
    0	Error
    #	Pointer to TABLE object of mysql.proc
*/

static TABLE *open_proc_table_for_update(THD *thd)
{
  TABLE_LIST tables;
  TABLE *table;
  DBUG_ENTER("open_proc_table");

  bzero((char*) &tables, sizeof(tables));
  tables.db= (char*) "mysql";
  tables.table_name= tables.alias= (char*)"proc";
  tables.lock_type= TL_WRITE;

  table= open_ltable(thd, &tables, TL_WRITE);

  /*
    Under explicit LOCK TABLES or in prelocked mode we should not
    say that mysql.proc table does not exist if we are unable to
    open and lock it for writing since this condition may be
    transient.
  */
  if (!(thd->locked_tables || thd->prelocked_mode) || table)
    mysql_proc_table_exists= test(table);

  DBUG_RETURN(table);
}


/*
  Find row in open mysql.proc table representing stored routine.

  SYNOPSIS
    db_find_routine_aux()
      thd    Thread context
      type   Type of routine to find (function or procedure)
      name   Name of routine
      table  TABLE object for open mysql.proc table.

  RETURN VALUE
    SP_OK           - Routine found
    SP_KEY_NOT_FOUND- No routine with given name
*/

static int
db_find_routine_aux(THD *thd, int type, sp_name *name, TABLE *table)
{
213
  byte key[MAX_KEY_LENGTH];	// db, name, optional key length type
214
  DBUG_ENTER("db_find_routine_aux");
215
  DBUG_PRINT("enter", ("type: %d name: %.*s",
216
		       type, name->m_name.length, name->m_name.str));
217

218 219 220 221 222 223 224
  /*
    Create key to find row. We have to use field->store() to be able to
    handle VARCHAR and CHAR fields.
    Assumption here is that the three first fields in the table are
    'db', 'name' and 'type' and the first key is the primary key over the
    same fields.
  */
225 226
  if (name->m_name.length > table->field[1]->field_length)
    DBUG_RETURN(SP_KEY_NOT_FOUND);
227 228 229
  table->field[0]->store(name->m_db.str, name->m_db.length, &my_charset_bin);
  table->field[1]->store(name->m_name.str, name->m_name.length,
                         &my_charset_bin);
230
  table->field[2]->store((longlong) type, TRUE);
231 232 233
  key_copy(key, table->record[0], table->key_info,
           table->key_info->key_length);

234
  if (table->file->index_read_idx(table->record[0], 0,
235
				  key, table->key_info->key_length,
236
				  HA_READ_KEY_EXACT))
237
    DBUG_RETURN(SP_KEY_NOT_FOUND);
238

239 240 241
  DBUG_RETURN(SP_OK);
}

monty@mysql.com's avatar
monty@mysql.com committed
242

243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263
/*
  Find routine definition in mysql.proc table and create corresponding
  sp_head object for it.

  SYNOPSIS
    db_find_routine()
      thd   Thread context
      type  Type of routine (TYPE_ENUM_PROCEDURE/...)
      name  Name of routine
      sphp  Out parameter in which pointer to created sp_head
            object is returned (0 in case of error).

  NOTE
    This function may damage current LEX during execution, so it is good
    idea to create temporary LEX and make it active before calling it.

  RETURN VALUE
    0     - Success
    non-0 - Error (may be one of special codes like SP_KEY_NOT_FOUND)
*/

264
static int
265
db_find_routine(THD *thd, int type, sp_name *name, sp_head **sphp)
266 267 268
{
  extern int yyparse(void *thd);
  TABLE *table;
269
  const char *params, *returns, *body;
270
  int ret;
271
  const char *definer;
272 273
  longlong created;
  longlong modified;
274
  st_sp_chistics chistics;
275 276 277
  char *ptr;
  uint length;
  char buff[65];
278
  String str(buff, sizeof(buff), &my_charset_bin);
279
  ulong sql_mode;
280
  Open_tables_state open_tables_state_backup;
monty@mysql.com's avatar
monty@mysql.com committed
281
  DBUG_ENTER("db_find_routine");
282
  DBUG_PRINT("enter", ("type: %d name: %.*s",
283
		       type, name->m_name.length, name->m_name.str));
284

285
  *sphp= 0;                                     // In case of errors
286
  if (!(table= open_proc_table_for_read(thd, &open_tables_state_backup)))
287 288 289
    DBUG_RETURN(SP_OPEN_TABLE_FAILED);

  if ((ret= db_find_routine_aux(thd, type, name, table)) != SP_OK)
290
    goto done;
291

292
  if (table->s->fields != MYSQL_PROC_FIELD_COUNT)
293 294 295 296 297
  {
    ret= SP_GET_FIELD_FAILED;
    goto done;
  }

298
  bzero((char *)&chistics, sizeof(chistics));
monty@mysql.com's avatar
monty@mysql.com committed
299
  if ((ptr= get_field(thd->mem_root,
300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318
		      table->field[MYSQL_PROC_FIELD_ACCESS])) == NULL)
  {
    ret= SP_GET_FIELD_FAILED;
    goto done;
  }
  switch (ptr[0]) {
  case 'N':
    chistics.daccess= SP_NO_SQL;
    break;
  case 'C':
    chistics.daccess= SP_CONTAINS_SQL;
    break;
  case 'R':
    chistics.daccess= SP_READS_SQL_DATA;
    break;
  case 'M':
    chistics.daccess= SP_MODIFIES_SQL_DATA;
    break;
  default:
319
    chistics.daccess= SP_DEFAULT_ACCESS_MAPPING;
320 321
  }

monty@mysql.com's avatar
monty@mysql.com committed
322
  if ((ptr= get_field(thd->mem_root,
323
		      table->field[MYSQL_PROC_FIELD_DETERMINISTIC])) == NULL)
324 325 326 327
  {
    ret= SP_GET_FIELD_FAILED;
    goto done;
  }
328
  chistics.detistic= (ptr[0] == 'N' ? FALSE : TRUE);    
329

monty@mysql.com's avatar
monty@mysql.com committed
330
  if ((ptr= get_field(thd->mem_root,
331
		      table->field[MYSQL_PROC_FIELD_SECURITY_TYPE])) == NULL)
332 333 334 335
  {
    ret= SP_GET_FIELD_FAILED;
    goto done;
  }
336
  chistics.suid= (ptr[0] == 'I' ? SP_IS_NOT_SUID : SP_IS_SUID);
337

monty@mysql.com's avatar
monty@mysql.com committed
338
  if ((params= get_field(thd->mem_root,
339 340 341 342
			 table->field[MYSQL_PROC_FIELD_PARAM_LIST])) == NULL)
  {
    params= "";
  }
343

344 345
  if (type == TYPE_ENUM_PROCEDURE)
    returns= "";
monty@mysql.com's avatar
monty@mysql.com committed
346
  else if ((returns= get_field(thd->mem_root,
347 348 349 350 351 352
			       table->field[MYSQL_PROC_FIELD_RETURNS])) == NULL)
  {
    ret= SP_GET_FIELD_FAILED;
    goto done;
  }

monty@mysql.com's avatar
monty@mysql.com committed
353
  if ((body= get_field(thd->mem_root,
354
		       table->field[MYSQL_PROC_FIELD_BODY])) == NULL)
355 356 357 358
  {
    ret= SP_GET_FIELD_FAILED;
    goto done;
  }
359 360

  // Get additional information
monty@mysql.com's avatar
monty@mysql.com committed
361
  if ((definer= get_field(thd->mem_root,
362 363 364 365 366 367 368 369
			  table->field[MYSQL_PROC_FIELD_DEFINER])) == NULL)
  {
    ret= SP_GET_FIELD_FAILED;
    goto done;
  }

  modified= table->field[MYSQL_PROC_FIELD_MODIFIED]->val_int();
  created= table->field[MYSQL_PROC_FIELD_CREATED]->val_int();
370

monty@mysql.com's avatar
monty@mysql.com committed
371
  sql_mode= (ulong) table->field[MYSQL_PROC_FIELD_SQL_MODE]->val_int();
372

373 374
  table->field[MYSQL_PROC_FIELD_COMMENT]->val_str(&str, &str);

375
  ptr= 0;
376
  if ((length= str.length()))
377
    ptr= thd->strmake(str.ptr(), length);
378 379
  chistics.comment.str= ptr;
  chistics.comment.length= length;
380

381
  close_proc_table(thd, &open_tables_state_backup);
382
  table= 0;
383

384 385 386 387 388 389 390 391 392
  ret= db_load_routine(thd, type, name, sphp,
                       sql_mode, params, returns, body, chistics,
                       definer, created, modified);
                       
 done:
  if (table)
    close_proc_table(thd, &open_tables_state_backup);
  DBUG_RETURN(ret);
}
monty@mysql.com's avatar
monty@mysql.com committed
393

394

395 396 397 398 399 400
static int
db_load_routine(THD *thd, int type, sp_name *name, sp_head **sphp,
                ulong sql_mode, const char *params, const char *returns,
                const char *body, st_sp_chistics &chistics,
                const char *definer, longlong created, longlong modified)
{
401
  LEX *old_lex= thd->lex, newlex;
402 403 404 405
  String defstr;
  char olddb[128];
  bool dbchanged;
  ulong old_sql_mode= thd->variables.sql_mode;
406 407 408
  ha_rows old_select_limit= thd->variables.select_limit;
  sp_rcontext *old_spcont= thd->spcont;
  int ret;
409 410 411 412 413 414 415 416 417 418 419 420 421 422 423

  thd->variables.sql_mode= sql_mode;
  thd->variables.select_limit= HA_POS_ERROR;

  thd->lex= &newlex;
  newlex.current_select= NULL;

  defstr.set_charset(system_charset_info);
  if (!create_string(thd, &defstr,
		     type,
		     name,
		     params, strlen(params),
		     returns, strlen(returns),
		     body, strlen(body),
		     &chistics))
424 425
  {
    ret= SP_INTERNAL_ERROR;
426
    goto end;
427
  }
428

429 430 431 432
  dbchanged= FALSE;
  if ((ret= sp_use_new_db(thd, name->m_db.str, olddb, sizeof(olddb),
			  1, &dbchanged)))
    goto end;
433

434
  lex_start(thd, (uchar*)defstr.c_ptr(), defstr.length());
435

bell@sanja.is.com.ua's avatar
merge  
bell@sanja.is.com.ua committed
436
  thd->spcont= 0;
437 438 439 440 441 442 443 444 445 446 447 448
  if (yyparse(thd) || thd->is_fatal_error || newlex.sphead == NULL)
  {
    sp_head *sp= newlex.sphead;

    if (dbchanged && (ret= mysql_change_db(thd, olddb, 1)))
      goto end;
    delete sp;
    ret= SP_PARSE_ERROR;
  }
  else
  {
    if (dbchanged && (ret= mysql_change_db(thd, olddb, 1)))
bell@sanja.is.com.ua's avatar
bell@sanja.is.com.ua committed
449
      goto end;
450
    *sphp= newlex.sphead;
bell@sanja.is.com.ua's avatar
merge  
bell@sanja.is.com.ua committed
451
    (*sphp)->set_definer((char*) definer, (uint) strlen(definer));
bell@sanja.is.com.ua's avatar
bell@sanja.is.com.ua committed
452
    (*sphp)->set_info(created, modified, &chistics, sql_mode);
453 454
    (*sphp)->optimize();
  }
bell@sanja.is.com.ua's avatar
bell@sanja.is.com.ua committed
455
end:
456
  thd->spcont= old_spcont;
457
  thd->variables.sql_mode= old_sql_mode;
458 459
  thd->variables.select_limit= old_select_limit;
  thd->lex= old_lex;
460
  return ret;
461 462
}

monty@mysql.com's avatar
monty@mysql.com committed
463

464 465 466
static void
sp_returns_type(THD *thd, String &result, sp_head *sp)
{
467
  TABLE table;
468
  TABLE_SHARE share;
469
  Field *field;
470 471
  bzero((char*) &table, sizeof(table));
  bzero((char*) &share, sizeof(share));
472
  table.in_use= thd;
473
  table.s = &share;
474
  field= sp->create_result_field(0, 0, &table);
475 476 477 478
  field->sql_type(result);
  delete field;
}

479
static int
480
db_create_routine(THD *thd, int type, sp_head *sp)
481
{
482
  int ret;
483
  TABLE *table;
484
  char definer[HOSTNAME_LENGTH+USERNAME_LENGTH+2];
485 486
  char olddb[128];
  bool dbchanged;
monty@mysql.com's avatar
monty@mysql.com committed
487
  DBUG_ENTER("db_create_routine");
488 489
  DBUG_PRINT("enter", ("type: %d name: %.*s",type,sp->m_name.length,
                       sp->m_name.str));
490

491 492 493 494 495 496 497 498
  dbchanged= FALSE;
  if ((ret= sp_use_new_db(thd, sp->m_db.str, olddb, sizeof(olddb),
			  0, &dbchanged)))
  {
    ret= SP_NO_DB_ERROR;
    goto done;
  }

499
  if (!(table= open_proc_table_for_update(thd)))
500 501
    ret= SP_OPEN_TABLE_FAILED;
  else
502
  {
503
    restore_record(table, s->default_values); // Get default values for fields
504 505
    strxmov(definer, thd->security_ctx->priv_user, "@",
            thd->security_ctx->priv_host, NullS);
506

507
    if (table->s->fields != MYSQL_PROC_FIELD_COUNT)
508 509 510 511
    {
      ret= SP_GET_FIELD_FAILED;
      goto done;
    }
512 513 514 515 516
    if (sp->m_name.length > table->field[MYSQL_PROC_FIELD_NAME]->field_length)
    {
      ret= SP_BAD_IDENTIFIER;
      goto done;
    }
517 518 519 520 521
    if (sp->m_body.length > table->field[MYSQL_PROC_FIELD_BODY]->field_length)
    {
      ret= SP_BODY_TOO_LONG;
      goto done;
    }
522 523
    table->field[MYSQL_PROC_FIELD_DB]->
      store(sp->m_db.str, sp->m_db.length, system_charset_info);
524 525 526 527 528 529
    table->field[MYSQL_PROC_FIELD_NAME]->
      store(sp->m_name.str, sp->m_name.length, system_charset_info);
    table->field[MYSQL_PROC_FIELD_TYPE]->
      store((longlong)type);
    table->field[MYSQL_PROC_FIELD_SPECIFIC_NAME]->
      store(sp->m_name.str, sp->m_name.length, system_charset_info);
530 531 532
    if (sp->m_chistics->daccess != SP_DEFAULT_ACCESS)
      table->field[MYSQL_PROC_FIELD_ACCESS]->
	store((longlong)sp->m_chistics->daccess);
533 534
    table->field[MYSQL_PROC_FIELD_DETERMINISTIC]->
      store((longlong)(sp->m_chistics->detistic ? 1 : 2));
535
    if (sp->m_chistics->suid != SP_IS_DEFAULT_SUID)
536 537 538 539
      table->field[MYSQL_PROC_FIELD_SECURITY_TYPE]->
	store((longlong)sp->m_chistics->suid);
    table->field[MYSQL_PROC_FIELD_PARAM_LIST]->
      store(sp->m_params.str, sp->m_params.length, system_charset_info);
540 541 542 543
    if (sp->m_type == TYPE_ENUM_FUNCTION)
    {
      String retstr(64);
      sp_returns_type(thd, retstr, sp);
544
      table->field[MYSQL_PROC_FIELD_RETURNS]->
545 546
	store(retstr.ptr(), retstr.length(), system_charset_info);
    }
547 548 549
    table->field[MYSQL_PROC_FIELD_BODY]->
      store(sp->m_body.str, sp->m_body.length, system_charset_info);
    table->field[MYSQL_PROC_FIELD_DEFINER]->
550
      store(definer, (uint)strlen(definer), system_charset_info);
551
    ((Field_timestamp *)table->field[MYSQL_PROC_FIELD_CREATED])->set_time();
552
    ((Field_timestamp *)table->field[MYSQL_PROC_FIELD_MODIFIED])->set_time();
553 554 555 556 557 558
    table->field[MYSQL_PROC_FIELD_SQL_MODE]->
      store((longlong)thd->variables.sql_mode);
    if (sp->m_chistics->comment.str)
      table->field[MYSQL_PROC_FIELD_COMMENT]->
	store(sp->m_chistics->comment.str, sp->m_chistics->comment.length,
	      system_charset_info);
559

560 561
    if ((sp->m_type == TYPE_ENUM_FUNCTION) &&
        !trust_function_creators && mysql_bin_log.is_open())
562 563 564 565
    {
      if (!sp->m_chistics->detistic)
      {
	/*
566
	  Note that this test is not perfect; one could use
567 568 569 570 571 572 573 574 575 576 577 578 579 580
	  a non-deterministic read-only function in an update statement.
	*/
	enum enum_sp_data_access access=
	  (sp->m_chistics->daccess == SP_DEFAULT_ACCESS) ?
	  SP_DEFAULT_ACCESS_MAPPING : sp->m_chistics->daccess;
	if (access == SP_CONTAINS_SQL ||
	    access == SP_MODIFIES_SQL_DATA)
	{
	  my_message(ER_BINLOG_UNSAFE_ROUTINE,
		     ER(ER_BINLOG_UNSAFE_ROUTINE), MYF(0));
	  ret= SP_INTERNAL_ERROR;
	  goto done;
	}
      }
581
      if (!(thd->security_ctx->master_access & SUPER_ACL))
582 583 584 585 586 587 588 589
      {
	my_message(ER_BINLOG_CREATE_ROUTINE_NEED_SUPER,
		   ER(ER_BINLOG_CREATE_ROUTINE_NEED_SUPER), MYF(0));
	ret= SP_INTERNAL_ERROR;
	goto done;
      }
    }

monty@mysql.com's avatar
monty@mysql.com committed
590
    ret= SP_OK;
591
    if (table->file->ha_write_row(table->record[0]))
592
      ret= SP_WRITE_ROW_FAILED;
593 594 595 596
    else if (mysql_bin_log.is_open())
    {
      thd->clear_error();
      /* Such a statement can always go directly to binlog, no trans cache */
597 598
      thd->binlog_query(THD::MYSQL_QUERY_TYPE,
                        thd->query, thd->query_length, FALSE, FALSE);
599 600
    }

601 602
  }

603
done:
604
  close_thread_tables(thd);
605
  if (dbchanged)
606
    (void)mysql_change_db(thd, olddb, 1);
607 608
  DBUG_RETURN(ret);
}
609

monty@mysql.com's avatar
monty@mysql.com committed
610

611
static int
612
db_drop_routine(THD *thd, int type, sp_name *name)
613 614 615
{
  TABLE *table;
  int ret;
monty@mysql.com's avatar
monty@mysql.com committed
616
  DBUG_ENTER("db_drop_routine");
617
  DBUG_PRINT("enter", ("type: %d name: %.*s",
618
		       type, name->m_name.length, name->m_name.str));
619

620 621 622
  if (!(table= open_proc_table_for_update(thd)))
    DBUG_RETURN(SP_OPEN_TABLE_FAILED);
  if ((ret= db_find_routine_aux(thd, type, name, table)) == SP_OK)
623
  {
624
    if (table->file->ha_delete_row(table->record[0]))
625 626
      ret= SP_DELETE_ROW_FAILED;
  }
627
  close_thread_tables(thd);
628 629 630
  DBUG_RETURN(ret);
}

monty@mysql.com's avatar
monty@mysql.com committed
631

632
static int
633
db_update_routine(THD *thd, int type, sp_name *name, st_sp_chistics *chistics)
634 635 636 637
{
  TABLE *table;
  int ret;
  bool opened;
monty@mysql.com's avatar
monty@mysql.com committed
638
  DBUG_ENTER("db_update_routine");
639
  DBUG_PRINT("enter", ("type: %d name: %.*s",
640
		       type, name->m_name.length, name->m_name.str));
641

642 643 644
  if (!(table= open_proc_table_for_update(thd)))
    DBUG_RETURN(SP_OPEN_TABLE_FAILED);
  if ((ret= db_find_routine_aux(thd, type, name, table)) == SP_OK)
645 646
  {
    store_record(table,record[1]);
monty@mysql.com's avatar
monty@mysql.com committed
647
    table->timestamp_field_type= TIMESTAMP_NO_AUTO_SET;
648
    ((Field_timestamp *)table->field[MYSQL_PROC_FIELD_MODIFIED])->set_time();
649 650 651 652 653 654
    if (chistics->suid != SP_IS_DEFAULT_SUID)
      table->field[MYSQL_PROC_FIELD_SECURITY_TYPE]->
	store((longlong)chistics->suid);
    if (chistics->daccess != SP_DEFAULT_ACCESS)
      table->field[MYSQL_PROC_FIELD_ACCESS]->
	store((longlong)chistics->daccess);
655 656 657
    if (chistics->comment.str)
      table->field[MYSQL_PROC_FIELD_COMMENT]->store(chistics->comment.str,
						    chistics->comment.length,
658
						    system_charset_info);
659
    if ((table->file->ha_update_row(table->record[1],table->record[0])))
660 661
      ret= SP_WRITE_ROW_FAILED;
  }
662
  close_thread_tables(thd);
663 664 665
  DBUG_RETURN(ret);
}

monty@mysql.com's avatar
monty@mysql.com committed
666

667 668 669 670 671 672 673 674 675 676
struct st_used_field
{
  const char *field_name;
  uint field_length;
  enum enum_field_types field_type;
  Field *field;
};

static struct st_used_field init_fields[]=
{
677
  { "Db",       NAME_LEN, MYSQL_TYPE_STRING,    0},
678 679
  { "Name",     NAME_LEN, MYSQL_TYPE_STRING,    0},
  { "Type",            9, MYSQL_TYPE_STRING,    0},
680
  { "Definer",        77, MYSQL_TYPE_STRING,    0},
681 682
  { "Modified",        0, MYSQL_TYPE_TIMESTAMP, 0},
  { "Created",         0, MYSQL_TYPE_TIMESTAMP, 0},
683
  { "Security_type",   1, MYSQL_TYPE_STRING,    0},
684 685 686 687
  { "Comment",  NAME_LEN, MYSQL_TYPE_STRING,    0},
  { 0,                 0, MYSQL_TYPE_STRING,    0}
};

monty@mysql.com's avatar
monty@mysql.com committed
688

689 690 691 692
static int
print_field_values(THD *thd, TABLE *table,
		   struct st_used_field *used_fields,
		   int type, const char *wild)
693 694 695 696 697
{
  Protocol *protocol= thd->protocol;

  if (table->field[MYSQL_PROC_FIELD_TYPE]->val_int() == type)
  {
698 699
    String db_string;
    String name_string;
700
    struct st_used_field *used_field= used_fields;
701

monty@mysql.com's avatar
monty@mysql.com committed
702
    if (get_field(thd->mem_root, used_field->field, &db_string))
703 704
      db_string.set_ascii("", 0);
    used_field+= 1;
monty@mysql.com's avatar
monty@mysql.com committed
705
    get_field(thd->mem_root, used_field->field, &name_string);
706 707

    if (!wild || !wild[0] || !wild_compare(name_string.ptr(), wild, 0))
708 709
    {
      protocol->prepare_for_resend();
710 711
      protocol->store(&db_string);
      protocol->store(&name_string);
712 713 714 715 716 717
      for (used_field++;
	   used_field->field_name;
	   used_field++)
      {
	switch (used_field->field_type) {
	case MYSQL_TYPE_TIMESTAMP:
718 719
	  {
	    TIME tmp_time;
720 721

	    bzero((char *)&tmp_time, sizeof(tmp_time));
722 723 724 725
	    ((Field_timestamp *) used_field->field)->get_time(&tmp_time);
	    protocol->store(&tmp_time);
	  }
	  break;
726
	default:
727
	  {
728
	    String tmp_string;
729

monty@mysql.com's avatar
monty@mysql.com committed
730
	    get_field(thd->mem_root, used_field->field, &tmp_string);
731
	    protocol->store(&tmp_string);
732 733
	  }
	  break;
734 735 736
	}
      }
      if (protocol->write())
737
	return SP_INTERNAL_ERROR;
738 739
    }
  }
740
  return SP_OK;
741 742
}

monty@mysql.com's avatar
monty@mysql.com committed
743

744
static int
745 746 747 748
db_show_routine_status(THD *thd, int type, const char *wild)
{
  TABLE *table;
  TABLE_LIST tables;
749
  int res;
monty@mysql.com's avatar
monty@mysql.com committed
750
  DBUG_ENTER("db_show_routine_status");
751 752 753

  memset(&tables, 0, sizeof(tables));
  tables.db= (char*)"mysql";
754
  tables.table_name= tables.alias= (char*)"proc";
755 756 757

  if (! (table= open_ltable(thd, &tables, TL_READ)))
  {
758 759
    res= SP_OPEN_TABLE_FAILED;
    goto done;
760 761 762 763 764 765
  }
  else
  {
    Item *item;
    List<Item> field_list;
    struct st_used_field *used_field;
766
    TABLE_LIST *leaves= 0;
767
    st_used_field used_fields[array_elements(init_fields)];
768

769 770 771 772 773 774 775 776 777 778 779 780 781 782 783 784 785 786
    memcpy((char*) used_fields, (char*) init_fields, sizeof(used_fields));
    /* Init header */
    for (used_field= &used_fields[0];
	 used_field->field_name;
	 used_field++)
    {
      switch (used_field->field_type) {
      case MYSQL_TYPE_TIMESTAMP:
	field_list.push_back(item=new Item_datetime(used_field->field_name));
	break;
      default:
	field_list.push_back(item=new Item_empty_string(used_field->field_name,
							used_field->
							field_length));
	break;
      }
    }
    /* Print header */
787 788
    if (thd->protocol->send_fields(&field_list, Protocol::SEND_NUM_ROWS |
                                                Protocol::SEND_EOF))
789 790
    {
      res= SP_INTERNAL_ERROR;
791
      goto err_case;
792
    }
793

bell@sanja.is.com.ua's avatar
VIEW  
bell@sanja.is.com.ua committed
794 795 796 797 798
    /*
      Init fields

      tables is not VIEW for sure => we can pass 0 as condition
    */
799 800
    thd->lex->select_lex.context.resolve_in_table_list_only(&tables);
    setup_tables(thd, &thd->lex->select_lex.context,
801
                 &thd->lex->select_lex.top_join_list,
802
                 &tables, 0, &leaves, FALSE);
803 804 805 806
    for (used_field= &used_fields[0];
	 used_field->field_name;
	 used_field++)
    {
807 808
      Item_field *field= new Item_field(&thd->lex->select_lex.context,
                                        "mysql", "proc",
809
					used_field->field_name);
810
      if (!field ||
811
          !(used_field->field= find_field_in_tables(thd, field, &tables, NULL,
812 813
						    0, REPORT_ALL_ERRORS, 1,
                                                    TRUE)))
814 815
      {
	res= SP_INTERNAL_ERROR;
816
	goto err_case1;
817
      }
818 819
    }

820
    table->file->ha_index_init(0, 1);
821 822
    if ((res= table->file->index_first(table->record[0])))
    {
monty@mysql.com's avatar
monty@mysql.com committed
823
      res= (res == HA_ERR_END_OF_FILE) ? 0 : SP_INTERNAL_ERROR;
824 825 826
      goto err_case1;
    }
    if ((res= print_field_values(thd, table, used_fields, type, wild)))
827 828 829
      goto err_case1;
    while (!table->file->index_next(table->record[0]))
    {
830
      if ((res= print_field_values(thd, table, used_fields, type, wild)))
831 832
	goto err_case1;
    }
833
    res= SP_OK;
834
  }
835

monty@mysql.com's avatar
monty@mysql.com committed
836
err_case1:
837
  send_eof(thd);
monty@mysql.com's avatar
monty@mysql.com committed
838
err_case:
monty@mysql.com's avatar
monty@mysql.com committed
839
  table->file->ha_index_end();
840
  close_thread_tables(thd);
monty@mysql.com's avatar
monty@mysql.com committed
841
done:
842
  DBUG_RETURN(res);
843 844
}

845

846 847 848 849 850 851 852 853 854 855 856 857 858 859 860 861 862 863 864
/* Drop all routines in database 'db' */
int
sp_drop_db_routines(THD *thd, char *db)
{
  TABLE *table;
  byte key[64];			// db
  uint keylen;
  int ret;
  DBUG_ENTER("sp_drop_db_routines");
  DBUG_PRINT("enter", ("db: %s", db));

  // Put the key used to read the row together
  keylen= strlen(db);
  if (keylen > 64)
    keylen= 64;
  memcpy(key, db, keylen);
  memset(key+keylen, (int)' ', 64-keylen); // Pad with space
  keylen= sizeof(key);

865 866 867
  ret= SP_OPEN_TABLE_FAILED;
  if (!(table= open_proc_table_for_update(thd)))
    goto err;
868 869

  ret= SP_OK;
870
  table->file->ha_index_init(0, 1);
871 872 873 874 875 876
  if (! table->file->index_read(table->record[0],
				key, keylen, HA_READ_KEY_EXACT))
  {
    int nxtres;
    bool deleted= FALSE;

877 878
    do
    {
879
      if (! table->file->ha_delete_row(table->record[0]))
880 881 882 883
	deleted= TRUE;		/* We deleted something */
      else
      {
	ret= SP_DELETE_ROW_FAILED;
884
	nxtres= 0;
885 886 887 888 889 890 891 892 893
	break;
      }
    } while (! (nxtres= table->file->index_next_same(table->record[0],
						     key, keylen)));
    if (nxtres != HA_ERR_END_OF_FILE)
      ret= SP_KEY_NOT_FOUND;
    if (deleted)
      sp_cache_invalidate();
  }
monty@mysql.com's avatar
monty@mysql.com committed
894
  table->file->ha_index_end();
895 896 897

  close_thread_tables(thd);

898
err:
899 900 901 902
  DBUG_RETURN(ret);
}


monty@mysql.com's avatar
monty@mysql.com committed
903 904 905
/*****************************************************************************
  PROCEDURE
******************************************************************************/
906

907
/*
908
  Obtain object representing stored procedure/function by its name from
909 910 911
  stored procedures cache and looking into mysql.proc if needed.

  SYNOPSIS
912
    sp_find_routine()
913
      thd        - thread context
914
      type       - type of object (TYPE_ENUM_FUNCTION or TYPE_ENUM_PROCEDURE)
915
      name       - name of procedure
916
      cp         - hash to look routine in
917 918 919 920 921 922 923 924
      cache_only - if true perform cache-only lookup
                   (Don't look in mysql.proc).

  RETURN VALUE
    Non-0 pointer to sp_head object for the procedure, or
    0 - in case of error.
*/

925
sp_head *
926 927
sp_find_routine(THD *thd, int type, sp_name *name, sp_cache **cp,
                bool cache_only)
928 929
{
  sp_head *sp;
930 931 932 933 934 935 936 937
  ulong depth= (type == TYPE_ENUM_PROCEDURE ?
                thd->variables.max_sp_recursion_depth :
                0);
  DBUG_ENTER("sp_find_routine");
  DBUG_PRINT("enter", ("name:  %.*s.%.*s, type: %d, cache only %d",
                       name->m_db.length, name->m_db.str,
                       name->m_name.length, name->m_name.str,
                       type, cache_only));
938

939
  if ((sp= sp_cache_lookup(cp, name)))
940
  {
941
    ulong level;
942 943 944 945 946
    sp_head *new_sp;
    const char *returns= "";
    char definer[HOSTNAME_LENGTH+USERNAME_LENGTH+2];
    String retstr(64);

947 948 949 950 951 952 953 954 955 956
    DBUG_PRINT("info", ("found: 0x%lx", (ulong)sp));
    if (sp->m_first_free_instance)
    {
      DBUG_PRINT("info", ("first free: 0x%lx, level: %lu, flags %x",
                          (ulong)sp->m_first_free_instance,
                          sp->m_first_free_instance->m_recursion_level,
                          sp->m_first_free_instance->m_flags));
      DBUG_ASSERT(!(sp->m_first_free_instance->m_flags & sp_head::IS_INVOKED));
      if (sp->m_first_free_instance->m_recursion_level > depth)
      {
957
        sp->recursion_level_error(thd);
958 959 960 961 962 963 964
        DBUG_RETURN(0);
      }
      DBUG_RETURN(sp->m_first_free_instance);
    }
    level= sp->m_last_cached_sp->m_recursion_level + 1;
    if (level > depth)
    {
965
      sp->recursion_level_error(thd);
966 967
      DBUG_RETURN(0);
    }
968 969 970 971

    strxmov(definer, sp->m_definer_user.str, "@",
            sp->m_definer_host.str, NullS);
    if (type == TYPE_ENUM_FUNCTION)
972
    {
973 974 975 976 977 978 979 980 981 982 983 984 985
      sp_returns_type(thd, retstr, sp);
      returns= retstr.ptr();
    }
    if (db_load_routine(thd, type, name, &new_sp,
                        sp->m_sql_mode, sp->m_params.str, returns,
                        sp->m_body.str, *sp->m_chistics, definer,
                        sp->m_created, sp->m_modified) == SP_OK)
    {
      sp->m_last_cached_sp->m_next_cached_sp= new_sp;
      new_sp->m_recursion_level= level;
      new_sp->m_first_instance= sp;
      sp->m_last_cached_sp= sp->m_first_free_instance= new_sp;
      DBUG_PRINT("info", ("added level: 0x%lx, level: %lu, flags %x",
986 987
                          (ulong)new_sp, new_sp->m_recursion_level,
                          new_sp->m_flags));
988
      DBUG_RETURN(new_sp);
989
    }
990
    DBUG_RETURN(0);
991 992
  }
  if (!cache_only)
993
  {
994 995 996 997 998 999 1000
    if (db_find_routine(thd, type, name, &sp) == SP_OK)
    {
      sp_cache_insert(cp, sp);
      DBUG_PRINT("info", ("added new: 0x%lx, level: %lu, flags %x",
                          (ulong)sp, sp->m_recursion_level,
                          sp->m_flags));
    }
1001
  }
1002 1003 1004
  DBUG_RETURN(sp);
}

monty@mysql.com's avatar
monty@mysql.com committed
1005

1006

1007 1008 1009 1010 1011 1012 1013 1014 1015 1016 1017 1018
int
sp_exists_routine(THD *thd, TABLE_LIST *tables, bool any, bool no_error)
{
  TABLE_LIST *table;
  bool result= 0;
  DBUG_ENTER("sp_exists_routine");
  for (table= tables; table; table= table->next_global)
  {
    sp_name *name;
    LEX_STRING lex_db;
    LEX_STRING lex_name;
    lex_db.length= strlen(table->db);
1019
    lex_name.length= strlen(table->table_name);
1020
    lex_db.str= thd->strmake(table->db, lex_db.length);
1021
    lex_name.str= thd->strmake(table->table_name, lex_name.length);
1022 1023
    name= new sp_name(lex_db, lex_name);
    name->init_qname(thd);
1024 1025 1026 1027
    if (sp_find_routine(thd, TYPE_ENUM_PROCEDURE, name,
                        &thd->sp_proc_cache, FALSE) != NULL ||
        sp_find_routine(thd, TYPE_ENUM_FUNCTION, name,
                        &thd->sp_func_cache, FALSE) != NULL)
1028 1029 1030 1031 1032 1033 1034 1035 1036 1037
    {
      if (any)
        DBUG_RETURN(1);
      result= 1;
    }
    else if (!any)
    {
      if (!no_error)
      {
	my_error(ER_SP_DOES_NOT_EXIST, MYF(0), "FUNCTION or PROCEDURE", 
1038
		 table->table_name);
1039 1040 1041 1042 1043 1044 1045 1046 1047
	DBUG_RETURN(-1);
      }
      DBUG_RETURN(0);
    }
  }
  DBUG_RETURN(result);
}


1048
int
1049
sp_create_procedure(THD *thd, sp_head *sp)
1050
{
1051
  int ret;
1052
  DBUG_ENTER("sp_create_procedure");
1053
  DBUG_PRINT("enter", ("name: %.*s", sp->m_name.length, sp->m_name.str));
1054 1055 1056

  ret= db_create_routine(thd, TYPE_ENUM_PROCEDURE, sp);
  DBUG_RETURN(ret);
1057 1058
}

monty@mysql.com's avatar
monty@mysql.com committed
1059

1060
int
1061
sp_drop_procedure(THD *thd, sp_name *name)
1062
{
1063
  int ret;
1064
  DBUG_ENTER("sp_drop_procedure");
1065
  DBUG_PRINT("enter", ("name: %.*s", name->m_name.length, name->m_name.str));
1066

1067
  ret= db_drop_routine(thd, TYPE_ENUM_PROCEDURE, name);
1068
  if (!ret)
1069
    sp_cache_invalidate();
1070
  DBUG_RETURN(ret);
1071
}
1072

monty@mysql.com's avatar
monty@mysql.com committed
1073

1074
int
1075
sp_update_procedure(THD *thd, sp_name *name, st_sp_chistics *chistics)
1076
{
1077
  int ret;
1078
  DBUG_ENTER("sp_update_procedure");
1079
  DBUG_PRINT("enter", ("name: %.*s", name->m_name.length, name->m_name.str));
1080

1081
  ret= db_update_routine(thd, TYPE_ENUM_PROCEDURE, name, chistics);
1082
  if (!ret)
1083
    sp_cache_invalidate();
1084
  DBUG_RETURN(ret);
1085 1086
}

monty@mysql.com's avatar
monty@mysql.com committed
1087

1088
int
1089
sp_show_create_procedure(THD *thd, sp_name *name)
1090
{
monty@mysql.com's avatar
monty@mysql.com committed
1091
  sp_head *sp;
1092
  DBUG_ENTER("sp_show_create_procedure");
1093
  DBUG_PRINT("enter", ("name: %.*s", name->m_name.length, name->m_name.str));
1094

1095 1096
  if ((sp= sp_find_routine(thd, TYPE_ENUM_PROCEDURE, name,
                           &thd->sp_proc_cache, FALSE)))
1097 1098 1099 1100 1101
  {
    int ret= sp->show_create_procedure(thd);

    DBUG_RETURN(ret);
  }
1102

1103
  DBUG_RETURN(SP_KEY_NOT_FOUND);
1104 1105
}

monty@mysql.com's avatar
monty@mysql.com committed
1106

1107
int
1108
sp_show_status_procedure(THD *thd, const char *wild)
1109
{
1110
  int ret;
1111
  DBUG_ENTER("sp_show_status_procedure");
1112 1113 1114

  ret= db_show_routine_status(thd, TYPE_ENUM_PROCEDURE, wild);
  DBUG_RETURN(ret);
1115
}
1116

monty@mysql.com's avatar
monty@mysql.com committed
1117 1118 1119 1120

/*****************************************************************************
  FUNCTION
******************************************************************************/
1121 1122

int
1123
sp_create_function(THD *thd, sp_head *sp)
1124
{
1125
  int ret;
1126
  DBUG_ENTER("sp_create_function");
1127
  DBUG_PRINT("enter", ("name: %.*s", sp->m_name.length, sp->m_name.str));
1128

1129 1130
  ret= db_create_routine(thd, TYPE_ENUM_FUNCTION, sp);
  DBUG_RETURN(ret);
1131 1132
}

monty@mysql.com's avatar
monty@mysql.com committed
1133

1134
int
1135
sp_drop_function(THD *thd, sp_name *name)
1136
{
1137
  int ret;
1138
  DBUG_ENTER("sp_drop_function");
1139
  DBUG_PRINT("enter", ("name: %.*s", name->m_name.length, name->m_name.str));
1140

1141
  ret= db_drop_routine(thd, TYPE_ENUM_FUNCTION, name);
1142
  if (!ret)
1143
    sp_cache_invalidate();
1144
  DBUG_RETURN(ret);
1145
}
1146

monty@mysql.com's avatar
monty@mysql.com committed
1147

1148
int
1149
sp_update_function(THD *thd, sp_name *name, st_sp_chistics *chistics)
1150
{
1151
  int ret;
1152
  DBUG_ENTER("sp_update_procedure");
1153
  DBUG_PRINT("enter", ("name: %.*s", name->m_name.length, name->m_name.str));
1154

1155
  ret= db_update_routine(thd, TYPE_ENUM_FUNCTION, name, chistics);
1156
  if (!ret)
1157
    sp_cache_invalidate();
1158
  DBUG_RETURN(ret);
1159 1160
}

monty@mysql.com's avatar
monty@mysql.com committed
1161

1162
int
1163
sp_show_create_function(THD *thd, sp_name *name)
1164
{
monty@mysql.com's avatar
monty@mysql.com committed
1165
  sp_head *sp;
1166
  DBUG_ENTER("sp_show_create_function");
1167
  DBUG_PRINT("enter", ("name: %.*s", name->m_name.length, name->m_name.str));
1168

1169 1170
  if ((sp= sp_find_routine(thd, TYPE_ENUM_FUNCTION, name,
                           &thd->sp_func_cache, FALSE)))
1171 1172 1173 1174 1175
  {
    int ret= sp->show_create_function(thd);

    DBUG_RETURN(ret);
  }
1176
  DBUG_RETURN(SP_KEY_NOT_FOUND);
1177 1178
}

monty@mysql.com's avatar
monty@mysql.com committed
1179

1180
int
1181
sp_show_status_function(THD *thd, const char *wild)
1182
{
1183
  int ret;
1184
  DBUG_ENTER("sp_show_status_function");
1185 1186
  ret= db_show_routine_status(thd, TYPE_ENUM_FUNCTION, wild);
  DBUG_RETURN(ret);
1187 1188
}

monty@mysql.com's avatar
monty@mysql.com committed
1189

1190 1191 1192 1193 1194 1195 1196
/*
  Structure that represents element in the set of stored routines
  used by statement or routine.
*/
struct Sroutine_hash_entry;

struct Sroutine_hash_entry
1197
{
1198 1199 1200 1201 1202 1203 1204
  /* Set key consisting of one-byte routine type and quoted routine name. */
  LEX_STRING key;
  /*
    Next element in list linking all routines in set. See also comments
    for LEX::sroutine/sroutine_list and sp_head::m_sroutines.
  */
  Sroutine_hash_entry *next;
1205 1206 1207 1208 1209 1210
  /*
    Uppermost view which directly or indirectly uses this routine.
    0 if routine is not used in view. Note that it also can be 0 if
    statement uses routine both via view and directly.
  */
  TABLE_LIST *belong_to_view;
1211 1212 1213 1214 1215 1216 1217 1218
};


extern "C" byte* sp_sroutine_key(const byte *ptr, uint *plen, my_bool first)
{
  Sroutine_hash_entry *rn= (Sroutine_hash_entry *)ptr;
  *plen= rn->key.length;
  return (byte *)rn->key.str;
1219 1220
}

monty@mysql.com's avatar
monty@mysql.com committed
1221

1222
/*
1223 1224 1225 1226
  Check if
   - current statement (the one in thd->lex) needs table prelocking
   - first routine in thd->lex->sroutines_list needs to execute its body in
     prelocked mode.
1227 1228

  SYNOPSIS
1229 1230 1231 1232 1233 1234 1235 1236 1237 1238
    sp_get_prelocking_info()
      thd                  Current thread, thd->lex is the statement to be
                           checked.
      need_prelocking      OUT TRUE  - prelocked mode should be activated
                                       before executing the statement
                               FALSE - Don't activate prelocking 
      first_no_prelocking  OUT TRUE  - Tables used by first routine in
                                       thd->lex->sroutines_list should be
                                       prelocked.
                               FALSE - Otherwise.
1239 1240 1241 1242 1243 1244
  NOTES 
    This function assumes that for any "CALL proc(...)" statement routines_list 
    will have 'proc' as first element (it may have several, consider e.g.
    "proc(sp_func(...)))". This property is currently guaranted by the parser.
*/

1245 1246
void sp_get_prelocking_info(THD *thd, bool *need_prelocking, 
                            bool *first_no_prelocking)
1247 1248
{
  Sroutine_hash_entry *routine;
1249
  routine= (Sroutine_hash_entry*)thd->lex->sroutines_list.first;
1250

1251 1252
  DBUG_ASSERT(routine);
  bool first_is_procedure= (routine->key.str[0] == TYPE_ENUM_PROCEDURE);
1253

1254 1255
  *first_no_prelocking= first_is_procedure;
  *need_prelocking= !first_is_procedure || test(routine->next);
1256 1257 1258
}


1259 1260 1261 1262 1263 1264
/*
  Auxilary function that adds new element to the set of stored routines
  used by statement.

  SYNOPSIS
    add_used_routine()
1265 1266 1267 1268 1269
      lex             LEX representing statement
      arena           Arena in which memory for new element will be allocated
      key             Key for the hash representing set
      belong_to_view  Uppermost view which uses this routine
                      (0 if routine is not used by view)
1270 1271 1272 1273 1274 1275 1276 1277 1278 1279 1280 1281 1282 1283 1284 1285 1286 1287 1288 1289 1290

  NOTES
    Will also add element to end of 'LEX::sroutines_list' list.

    In case when statement uses stored routines but does not need
    prelocking (i.e. it does not use any tables) we will access the
    elements of LEX::sroutines set on prepared statement re-execution.
    Because of this we have to allocate memory for both hash element
    and copy of its key in persistent arena.

  TODO
    When we will got rid of these accesses on re-executions we will be
    able to allocate memory for hash elements in non-persitent arena
    and directly use key values from sp_head::m_sroutines sets instead
    of making their copies.

  RETURN VALUE
    TRUE  - new element was added.
    FALSE - element was not added (because it is already present in the set).
*/

1291
static bool add_used_routine(LEX *lex, Query_arena *arena,
1292 1293
                             const LEX_STRING *key,
                             TABLE_LIST *belong_to_view)
1294
{
1295
  if (!hash_search(&lex->sroutines, (byte *)key->str, key->length))
1296
  {
1297 1298 1299 1300 1301 1302 1303 1304 1305 1306
    Sroutine_hash_entry *rn=
      (Sroutine_hash_entry *)arena->alloc(sizeof(Sroutine_hash_entry) +
                                          key->length);
    if (!rn)              // OOM. Error will be reported using fatal_error().
      return FALSE;
    rn->key.length= key->length;
    rn->key.str= (char *)rn + sizeof(Sroutine_hash_entry);
    memcpy(rn->key.str, key->str, key->length);
    my_hash_insert(&lex->sroutines, (byte *)rn);
    lex->sroutines_list.link_in_list((byte *)rn, (byte **)&rn->next);
1307
    rn->belong_to_view= belong_to_view;
1308
    return TRUE;
1309
  }
1310
  return FALSE;
1311 1312
}

monty@mysql.com's avatar
monty@mysql.com committed
1313

1314
/*
1315 1316
  Add routine which is explicitly used by statement to the set of stored
  routines used by this statement.
1317 1318

  SYNOPSIS
1319 1320 1321 1322 1323 1324 1325 1326
    sp_add_used_routine()
      lex     - LEX representing statement
      arena   - arena in which memory for new element of the set
                will be allocated
      rt      - routine name
      rt_type - routine type (one of TYPE_ENUM_PROCEDURE/...)

  NOTES
1327 1328
    Will also add element to end of 'LEX::sroutines_list' list (and will
    take into account that this is explicitly used routine).
1329 1330 1331 1332 1333

    To be friendly towards prepared statements one should pass
    persistent arena as second argument.
*/

1334
void sp_add_used_routine(LEX *lex, Query_arena *arena,
1335 1336 1337
                         sp_name *rt, char rt_type)
{
  rt->set_routine_type(rt_type);
1338
  (void)add_used_routine(lex, arena, &rt->m_sroutines_key, 0);
1339 1340 1341 1342 1343 1344 1345 1346 1347 1348 1349 1350 1351 1352 1353 1354 1355 1356 1357 1358 1359 1360 1361 1362 1363 1364 1365 1366 1367 1368 1369
  lex->sroutines_list_own_last= lex->sroutines_list.next;
  lex->sroutines_list_own_elements= lex->sroutines_list.elements;
}


/*
  Remove routines which are only indirectly used by statement from
  the set of routines used by this statement.

  SYNOPSIS
    sp_remove_not_own_routines()
      lex  LEX representing statement
*/

void sp_remove_not_own_routines(LEX *lex)
{
  Sroutine_hash_entry *not_own_rt, *next_rt;
  for (not_own_rt= *(Sroutine_hash_entry **)lex->sroutines_list_own_last;
       not_own_rt; not_own_rt= next_rt)
  {
    /*
      It is safe to obtain not_own_rt->next after calling hash_delete() now
      but we want to be more future-proof.
    */
    next_rt= not_own_rt->next;
    hash_delete(&lex->sroutines, (byte *)not_own_rt);
  }

  *(Sroutine_hash_entry **)lex->sroutines_list_own_last= NULL;
  lex->sroutines_list.next= lex->sroutines_list_own_last;
  lex->sroutines_list.elements= lex->sroutines_list_own_elements;
1370 1371 1372 1373 1374 1375 1376 1377 1378
}


/*
  Merge contents of two hashes representing sets of routines used
  by statements or by other routines.

  SYNOPSIS
    sp_update_sp_used_routines()
1379 1380 1381
      dst - hash to which elements should be added
      src - hash from which elements merged

1382 1383 1384 1385 1386 1387 1388
  NOTE
    This procedure won't create new Sroutine_hash_entry objects,
    instead it will simply add elements from source to destination
    hash. Thus time of life of elements in destination hash becomes
    dependant on time of life of elements from source hash. It also
    won't touch lists linking elements in source and destination
    hashes.
1389 1390
*/

1391
void sp_update_sp_used_routines(HASH *dst, HASH *src)
1392
{
1393
  for (uint i=0 ; i < src->records ; i++)
1394
  {
1395 1396 1397
    Sroutine_hash_entry *rt= (Sroutine_hash_entry *)hash_element(src, i);
    if (!hash_search(dst, (byte *)rt->key.str, rt->key.length))
      my_hash_insert(dst, (byte *)rt);
1398 1399 1400
  }
}

monty@mysql.com's avatar
monty@mysql.com committed
1401

1402
/*
1403 1404
  Add contents of hash representing set of routines to the set of
  routines used by statement.
1405 1406

  SYNOPSIS
1407
    sp_update_stmt_used_routines()
1408 1409 1410 1411
      thd             Thread context
      lex             LEX representing statement
      src             Hash representing set from which routines will be added
      belong_to_view  Uppermost view which uses these routines, 0 if none
1412 1413 1414 1415 1416

  NOTE
    It will also add elements to end of 'LEX::sroutines_list' list.
*/

1417 1418 1419
static void
sp_update_stmt_used_routines(THD *thd, LEX *lex, HASH *src,
                             TABLE_LIST *belong_to_view)
1420 1421 1422 1423
{
  for (uint i=0 ; i < src->records ; i++)
  {
    Sroutine_hash_entry *rt= (Sroutine_hash_entry *)hash_element(src, i);
1424
    (void)add_used_routine(lex, thd->stmt_arena, &rt->key, belong_to_view);
1425 1426 1427 1428
  }
}


1429 1430 1431 1432 1433 1434
/*
  Add contents of list representing set of routines to the set of
  routines used by statement.

  SYNOPSIS
    sp_update_stmt_used_routines()
1435 1436 1437 1438
      thd             Thread context
      lex             LEX representing statement
      src             List representing set from which routines will be added
      belong_to_view  Uppermost view which uses these routines, 0 if none
1439 1440 1441 1442 1443

  NOTE
    It will also add elements to end of 'LEX::sroutines_list' list.
*/

1444 1445
static void sp_update_stmt_used_routines(THD *thd, LEX *lex, SQL_LIST *src,
                                         TABLE_LIST *belong_to_view)
1446 1447 1448
{
  for (Sroutine_hash_entry *rt= (Sroutine_hash_entry *)src->first;
       rt; rt= rt->next)
1449
    (void)add_used_routine(lex, thd->stmt_arena, &rt->key, belong_to_view);
1450 1451 1452
}


1453 1454 1455 1456 1457 1458 1459
/*
  Cache sub-set of routines used by statement, add tables used by these
  routines to statement table list. Do the same for all routines used
  by these routines.

  SYNOPSIS
    sp_cache_routines_and_add_tables_aux()
1460 1461 1462 1463 1464 1465 1466
      thd              - thread context
      lex              - LEX representing statement
      start            - first routine from the list of routines to be cached
                         (this list defines mentioned sub-set).
      first_no_prelock - If true, don't add tables or cache routines used by
                         the body of the first routine (i.e. *start)
                         will be executed in non-prelocked mode.
1467
      tabs_changed     - Set to TRUE some tables were added, FALSE otherwise
1468 1469 1470 1471
  NOTE
    If some function is missing this won't be reported here.
    Instead this fact will be discovered during query execution.

1472
  RETURN VALUE
1473 1474
     0     - success
     non-0 - failure
1475 1476
*/

1477
static int
1478
sp_cache_routines_and_add_tables_aux(THD *thd, LEX *lex,
1479
                                     Sroutine_hash_entry *start, 
1480
                                     bool first_no_prelock, bool *tabs_changed)
1481
{
1482
  int ret= 0;
1483
  bool tabschnd= 0;             /* Set if tables changed */
1484
  bool first= TRUE;
1485
  DBUG_ENTER("sp_cache_routines_and_add_tables_aux");
1486

1487
  for (Sroutine_hash_entry *rt= start; rt; rt= rt->next)
1488
  {
1489 1490 1491
    sp_name name(rt->key.str, rt->key.length);
    int type= rt->key.str[0];
    sp_head *sp;
1492

1493 1494 1495
    if (!(sp= sp_cache_lookup((type == TYPE_ENUM_FUNCTION ?
                              &thd->sp_func_cache : &thd->sp_proc_cache),
                              &name)))
1496
    {
1497 1498 1499 1500 1501 1502 1503
      name.m_name.str= strchr(name.m_qname.str, '.');
      name.m_db.length= name.m_name.str - name.m_qname.str;
      name.m_db.str= strmake_root(thd->mem_root, name.m_qname.str,
                                  name.m_db.length);
      name.m_name.str+= 1;
      name.m_name.length= name.m_qname.length - name.m_db.length - 1;

1504
      switch ((ret= db_find_routine(thd, type, &name, &sp)))
1505
      {
1506 1507 1508 1509 1510 1511 1512 1513 1514 1515 1516 1517 1518 1519 1520 1521 1522 1523 1524 1525 1526
      case SP_OK:
        {
          if (type == TYPE_ENUM_FUNCTION)
            sp_cache_insert(&thd->sp_func_cache, sp);
          else
            sp_cache_insert(&thd->sp_proc_cache, sp);
        }
        break;
      case SP_KEY_NOT_FOUND:
        ret= SP_OK;
        break;
      case SP_OPEN_TABLE_FAILED:
        /*
          Force it to attempt opening it again on subsequent calls;
          otherwise we will get one error message the first time, and
          then ER_SP_PROC_TABLE_CORRUPT (below) on subsequent tries.
        */
        mysql_proc_table_exists= 1;
        /* Fall through */
      default:
        /*
1527 1528 1529 1530 1531 1532
          Any error when loading an existing routine is either some problem
          with the mysql.proc table, or a parse error because the contents
          has been tampered with (in which case we clear that error).
        */
        if (ret == SP_PARSE_ERROR)
          thd->clear_error();
1533 1534 1535 1536 1537
        /*
          If we cleared the parse error, or when db_find_routine() flagged
          an error with it's return value without calling my_error(), we
          set the generic "mysql.proc table corrupt" error here.
         */
1538 1539 1540 1541 1542 1543 1544 1545 1546
        if (!thd->net.report_error)
        {
          char n[NAME_LEN*2+2];

          /* m_qname.str is not always \0 terminated */
          memcpy(n, name.m_qname.str, name.m_qname.length);
          n[name.m_qname.length]= '\0';
          my_error(ER_SP_PROC_TABLE_CORRUPT, MYF(0), n, ret);
        }
1547
        break;
1548 1549 1550 1551
      }
    }
    if (sp)
    {
1552 1553
      if (!(first && first_no_prelock))
      {
1554 1555
        sp_update_stmt_used_routines(thd, lex, &sp->m_sroutines,
                                     rt->belong_to_view);
1556
        tabschnd|=
1557 1558
          sp->add_used_tables_to_table_list(thd, &lex->query_tables_last,
                                            rt->belong_to_view);
1559
      }
1560
    }
1561
    first= FALSE;
1562
  }
1563 1564
  if (tabs_changed)             /* it can be NULL  */
    *tabs_changed= tabschnd;
1565
  DBUG_RETURN(ret);
1566 1567 1568 1569 1570 1571 1572 1573 1574 1575
}


/*
  Cache all routines from the set of used by statement, add tables used
  by those routines to statement table list. Do the same for all routines
  used by those routines.

  SYNOPSIS
    sp_cache_routines_and_add_tables()
1576 1577 1578 1579
      thd              - thread context
      lex              - LEX representing statement
      first_no_prelock - If true, don't add tables or cache routines used by
                         the body of the first routine (i.e. *start)
1580
      tabs_changed     - Set to TRUE some tables were added, FALSE otherwise
1581
                         
1582
  RETURN VALUE
1583 1584
     0     - success
     non-0 - failure
1585 1586
*/

1587 1588 1589
int
sp_cache_routines_and_add_tables(THD *thd, LEX *lex, bool first_no_prelock,
                                 bool *tabs_changed)
1590 1591
{
  return sp_cache_routines_and_add_tables_aux(thd, lex,
1592
           (Sroutine_hash_entry *)lex->sroutines_list.first,
1593
           first_no_prelock, tabs_changed);
1594 1595 1596 1597 1598 1599 1600 1601 1602 1603
}


/*
  Add all routines used by view to the set of routines used by statement.
  Add tables used by those routines to statement table list. Do the same
  for all routines used by these routines.

  SYNOPSIS
    sp_cache_routines_and_add_tables_for_view()
1604 1605 1606
      thd   Thread context
      lex   LEX representing statement
      view  Table list element representing view
1607

1608
  RETURN VALUE
1609 1610
     0     - success
     non-0 - failure
1611 1612
*/

1613
int
1614
sp_cache_routines_and_add_tables_for_view(THD *thd, LEX *lex, TABLE_LIST *view)
1615 1616 1617
{
  Sroutine_hash_entry **last_cached_routine_ptr=
                          (Sroutine_hash_entry **)lex->sroutines_list.next;
1618 1619
  sp_update_stmt_used_routines(thd, lex, &view->view->sroutines_list,
                               view->top_table());
1620 1621 1622
  return sp_cache_routines_and_add_tables_aux(thd, lex, 
                                              *last_cached_routine_ptr, FALSE,
                                              NULL);
1623 1624 1625 1626 1627 1628 1629 1630 1631 1632
}


/*
  Add triggers for table to the set of routines used by statement.
  Add tables used by them to statement table list. Do the same for
  all implicitly used routines.

  SYNOPSIS
    sp_cache_routines_and_add_tables_for_triggers()
1633 1634 1635
      thd    thread context
      lex    LEX respresenting statement
      table  Table list element for table with trigger
1636 1637

  RETURN VALUE
1638 1639
     0     - success
     non-0 - failure
1640 1641
*/

1642
int
1643
sp_cache_routines_and_add_tables_for_triggers(THD *thd, LEX *lex,
1644
                                              TABLE_LIST *table)
1645
{
1646
  int ret= 0;
1647 1648 1649
  Table_triggers_list *triggers= table->table->triggers;
  if (add_used_routine(lex, thd->stmt_arena, &triggers->sroutines_key,
                       table->belong_to_view))
1650 1651 1652
  {
    Sroutine_hash_entry **last_cached_routine_ptr=
                            (Sroutine_hash_entry **)lex->sroutines_list.next;
1653
    for (int i= 0; i < (int)TRG_EVENT_MAX; i++)
1654
    {
1655
      for (int j= 0; j < (int)TRG_ACTION_MAX; j++)
1656
      {
1657
        if (triggers->bodies[i][j])
1658
        {
1659 1660 1661
          (void)triggers->bodies[i][j]->
                add_used_tables_to_table_list(thd, &lex->query_tables_last,
                                              table->belong_to_view);
1662
          sp_update_stmt_used_routines(thd, lex,
1663 1664
                                       &triggers->bodies[i][j]->m_sroutines,
                                       table->belong_to_view);
1665
        }
1666 1667
      }
    }
1668 1669 1670
    ret= sp_cache_routines_and_add_tables_aux(thd, lex,
                                              *last_cached_routine_ptr, 
                                              FALSE, NULL);
1671
  }
1672
  return ret;
1673
}
1674

1675

1676 1677 1678 1679 1680 1681
/*
 * Generates the CREATE... string from the table information.
 * Returns TRUE on success, FALSE on (alloc) failure.
 */
static bool
create_string(THD *thd, String *buf,
1682
	      int type,
1683
	      sp_name *name,
1684 1685 1686 1687 1688
	      const char *params, ulong paramslen,
	      const char *returns, ulong returnslen,
	      const char *body, ulong bodylen,
	      st_sp_chistics *chistics)
{
1689 1690 1691 1692
  /* Make some room to begin with */
  if (buf->alloc(100 + name->m_qname.length + paramslen + returnslen + bodylen +
		 chistics->comment.length))
    return FALSE;
monty@mysql.com's avatar
monty@mysql.com committed
1693

bell@sanja.is.com.ua's avatar
merge  
bell@sanja.is.com.ua committed
1694
  buf->append(STRING_WITH_LEN("CREATE "));
1695
  if (type == TYPE_ENUM_FUNCTION)
bell@sanja.is.com.ua's avatar
merge  
bell@sanja.is.com.ua committed
1696
    buf->append(STRING_WITH_LEN("FUNCTION "));
1697
  else
bell@sanja.is.com.ua's avatar
merge  
bell@sanja.is.com.ua committed
1698
    buf->append(STRING_WITH_LEN("PROCEDURE "));
1699 1700 1701 1702 1703 1704
  append_identifier(thd, buf, name->m_name.str, name->m_name.length);
  buf->append('(');
  buf->append(params, paramslen);
  buf->append(')');
  if (type == TYPE_ENUM_FUNCTION)
  {
bell@sanja.is.com.ua's avatar
merge  
bell@sanja.is.com.ua committed
1705
    buf->append(STRING_WITH_LEN(" RETURNS "));
1706 1707 1708
    buf->append(returns, returnslen);
  }
  buf->append('\n');
1709 1710
  switch (chistics->daccess) {
  case SP_NO_SQL:
bell@sanja.is.com.ua's avatar
merge  
bell@sanja.is.com.ua committed
1711
    buf->append(STRING_WITH_LEN("    NO SQL\n"));
1712 1713
    break;
  case SP_READS_SQL_DATA:
bell@sanja.is.com.ua's avatar
merge  
bell@sanja.is.com.ua committed
1714
    buf->append(STRING_WITH_LEN("    READS SQL DATA\n"));
1715 1716
    break;
  case SP_MODIFIES_SQL_DATA:
bell@sanja.is.com.ua's avatar
merge  
bell@sanja.is.com.ua committed
1717
    buf->append(STRING_WITH_LEN("    MODIFIES SQL DATA\n"));
1718
    break;
monty@mysql.com's avatar
monty@mysql.com committed
1719 1720 1721 1722
  case SP_DEFAULT_ACCESS:
  case SP_CONTAINS_SQL:
    /* Do nothing */
    break;
1723
  }
1724
  if (chistics->detistic)
bell@sanja.is.com.ua's avatar
merge  
bell@sanja.is.com.ua committed
1725
    buf->append(STRING_WITH_LEN("    DETERMINISTIC\n"));
1726
  if (chistics->suid == SP_IS_NOT_SUID)
bell@sanja.is.com.ua's avatar
merge  
bell@sanja.is.com.ua committed
1727
    buf->append(STRING_WITH_LEN("    SQL SECURITY INVOKER\n"));
1728
  if (chistics->comment.length)
monty@mysql.com's avatar
monty@mysql.com committed
1729
  {
bell@sanja.is.com.ua's avatar
merge  
bell@sanja.is.com.ua committed
1730
    buf->append(STRING_WITH_LEN("    COMMENT "));
1731 1732
    append_unescaped(buf, chistics->comment.str, chistics->comment.length);
    buf->append('\n');
monty@mysql.com's avatar
monty@mysql.com committed
1733
  }
1734 1735
  buf->append(body, bodylen);
  return TRUE;
1736
}
1737 1738 1739 1740 1741 1742 1743 1744


//
// Utilities...
//

int
sp_use_new_db(THD *thd, char *newdb, char *olddb, uint olddblen,
1745
	      bool no_access_check, bool *dbchangedp)
1746 1747 1748 1749 1750
{
  bool changeit;
  DBUG_ENTER("sp_use_new_db");
  DBUG_PRINT("enter", ("newdb: %s", newdb));

1751 1752
  if (! newdb)
    newdb= (char *)"";
1753 1754 1755 1756 1757 1758 1759 1760 1761 1762 1763 1764 1765 1766 1767 1768 1769 1770 1771 1772
  if (thd->db && thd->db[0])
  {
    if (my_strcasecmp(system_charset_info, thd->db, newdb) == 0)
      changeit= 0;
    else
    {
      changeit= 1;
      strnmov(olddb, thd->db, olddblen);
    }
  }
  else
  {				// thd->db empty
    if (newdb[0])
      changeit= 1;
    else
      changeit= 0;
    olddb[0] = '\0';
  }
  if (!changeit)
  {
1773
    *dbchangedp= FALSE;
1774 1775 1776 1777
    DBUG_RETURN(0);
  }
  else
  {
1778
    int ret= mysql_change_db(thd, newdb, no_access_check);
1779

1780 1781
    if (! ret)
      *dbchangedp= TRUE;
1782 1783 1784
    DBUG_RETURN(ret);
  }
}