MySQL: Returning unix file modes as a string of flags like 'rwxr-xr-x'

[ATC]

Storing information about files in a MySQL table, file permissions can be efficiently stored as a single integer just like in the file system.
For humans however, the file permissions are easier to read when shown as a string of characters.

[ATC]

Create a stored function.


create function filemode (mode integer)
returns char(9)
return concat(
  if(mode& 256, 'r', '-'),
  if(mode& 128, 'w', '-'),
  if(mode&2048, 'S', if(mode&  64, 'x', '-')),
  if(mode&  32, 'r', '-'),
  if(mode&  16, 'w', '-'),
  if(mode&1024, 'S', if(mode&  8, 'x', '-')),
  if(mode&   4, 'r', '-'),
  if(mode&   2, 'w', '-'),
  if(mode& 512, 'T', if(mode&   1, 'x', '-'))
);
Query OK, 0 rows affected (0.00 sec)


select filemode(conv(755,8,10)) as flags;
+-----------+
| flags     |
+-----------+
| rwxr-xr-x |
+-----------+
1 row in set (0.00 sec)


show function status;
+---------+----------+----------+----------------+---------------------+---------------------+---------------+---------+
| Db      | Name     | Type     | Definer        | Modified            | Created             | Security_type | Comment |
+---------+----------+----------+----------------+---------------------+---------------------+---------------+---------+
| jukebox | filemode | FUNCTION | root@localhost | 2009-01-10 15:52:11 | 2009-01-10 15:52:11 | DEFINER       |         |
+---------+----------+----------+----------------+---------------------+---------------------+---------------+---------+
1 row in set (0.00 sec)


select logical,mode,filemode(mode) as flags from filemap limit 10;
+-------------------------+------+-----------+
| logical                 | mode | flags     |
+-------------------------+------+-----------+
| Movies                  |  488 | rwxr-x--- |
| The Iron Giant.divx.avi |  488 | rwxr-x--- |
| help0013.gif            |  488 | rwxr-x--- |
| help0014.gif            |  488 | rwxr-x--- |
| iis4_02.cab             |  488 | rwxr-x--- |
| iis4_01.cab             |  488 | rwxr-x--- |
| iirnlink.htm            |  488 | rwxr-x--- |
| ie401.url               |  488 | rwxr-x--- |
| getfile.dll             |  488 | rwxr-x--- |
| fp30ext.inf             |  488 | rwxr-x--- |
+-------------------------+------+-----------+
10 rows in set (0.00 sec)


drop function filemode;
Query OK, 0 rows affected (0.05 sec)