Ano-Tech Computers
Enter keyword:

MySQL: Returning unix file modes as a string of flags like 'rwxr-xr-x'
Problem:
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.
 
Solution:
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)

 
Discuss this solution
Did this article solve your problem? Yes No Did not apply

This is free technical advice without any warranties. Use at your own risk. Solutions offered may not apply to your particular problem at all. Opinions and views expressed in the articles are the authors' personal and may not necessarily reflect or coincide with those of Ano-Tech Computers. We take no responsibility if anything bad happens from following any of the intructions given. If you don't understand and accept this, please contact us for personal assistance instead.
To read our full disclaimer, go here.

We welcome anyone who is willing to contribute to this public knowledge base, contact siteadmin@atc.no if you have information you would like to share. The idea is not to replace the commercial support sites, but to publish those hard-to-find solutions you've found yourself looking for over and over again.

Show all articles