MySQL privileges update for creating procedures, functions, etc.

After creating a new user you need to update his privileges to grant creation of procedures, functions and so on. Here is a SQL for this.

UPDATE user SET 
Select_priv="Y", Insert_priv="Y", Update_priv="Y", Delete_priv="Y", Create_priv="Y", Drop_priv="Y", Reload_priv="Y", Process_priv="Y", File_priv="Y", References_priv="Y", Index_priv="Y", Alter_priv="Y", Super_priv="Y", Create_tmp_table_priv="Y", Lock_tables_priv="Y", Execute_priv="Y", Create_view_priv="Y", Show_view_priv="Y", Create_routine_priv="Y", Alter_routine_priv="Y", Event_priv="Y", Trigger_priv="Y"
WHERE user = "xxxxx";
FLUSH PRIVILEGES;

MySQL PASSWORD() function in JavaScript

If you need to implement a MySQL PASSWORD() function in JavaScript - use http://code.google.com/p/crypto-js/#SHA-1 with 2 encodings.

Here is an example:

var password = "TestPassword";
var result = ("*"+CryptoJS.SHA1(CryptoJS.SHA1(password))).toUpperCase();
console.log("result : " + result);

Result will be *0F437A73F4E4014091B7360F60CF81271FB73180. If you check it with MySQL password() it will be the same:

mysql> select password("TestPassword") as result;
+-------------------------------------------+
| result                                    |
+-------------------------------------------+
| *0F437A73F4E4014091B7360F60CF81271FB73180 |
+-------------------------------------------+

Oracle analog of MySQL GROUP_CONCAT function

I was looking for Oracle analog of MySQL GROUP_CONCAT function. There is a solution on Ask Tom site. So here it is:

create or replace type string_agg_type as object (

  total varchar2(4000),

  static function ODCIAggregateInitialize(sctx IN OUT string_agg_type )

  return number,

  member function ODCIAggregateIterate(self IN OUT string_agg_type, value IN varchar2 )

  return number,

  member function ODCIAggregateTerminate(self IN string_agg_type, returnValue OUT varchar2, flags IN number)

  return number,

  member function ODCIAggregateMerge(self IN OUT string_agg_type, ctx2 IN string_agg_type)

  return number

);

/

create or replace type body string_agg_type is

  static function ODCIAggregateInitialize(sctx IN OUT string_agg_type)

  return number

  is

  begin

    sctx := string_agg_type( null );

    return ODCIConst.Success;

  end;

  member function ODCIAggregateIterate(self IN OUT string_agg_type, value IN varchar2 )

  return number

  is

  begin

    self.total := self.total || ',' || value;

    return ODCIConst.Success;

  end;

  member function ODCIAggregateTerminate(self IN string_agg_type, returnValue OUT varchar2, flags IN number)

  return number

  is

  begin

    returnValue := ltrim(self.total,',');

    return ODCIConst.Success;

  end;

  member function ODCIAggregateMerge(self IN OUT string_agg_type, ctx2 IN string_agg_type)

  return number

  is

  begin

    self.total := self.total || ctx2.total;

    return ODCIConst.Success;

  end;

end;

/

create or replace function stragg(input varchar2)

return varchar2

parallel_enable aggregate using string_agg_type;

/

After this you can use this function like this:

select t1.id, stragg(t2.name) as names from table1 t1, table2 t2 where t1.id=t2.id group by t1.id;

It will produce comma separated list of the names - something like this:

ID NAMES

-- ------------------------------------

10 CLARK,KING,MILLER

20 SMITH,FORD,ADAMS,SCOTT,JONES

30 ALLEN,BLAKE,MARTIN,TURNER,JAMES,WARD

Mac OS & Database development

Since switching to Mac I was looking for good DB modeling/editing tool that will support Oracle and MySQL databases. Finally I got one...

 

SQL4X Manager J from dbSuite

 

Seems to be a really nice tool.