zwass
06/19/2018, 8:22 PMselect path, substr(mode, 2, 1) & 2 AS user_write, substr(mode, 3, 1) & 2 AS group_write, substr(mode, 4, 1) & 2 AS world_write from file where path LIKE '/Users/zwass/%';
fritz
10/11/2019, 3:35 PMmode
column:
WITH mode_split AS (
SELECT
SUBSTR(mode, 1, 1) AS pos_1,
SUBSTR(mode, 2, 1) AS pos_2,
SUBSTR(mode, 3, 1) AS pos_3,
SUBSTR(mode, 4, 1) AS pos_4
from file where path = '/path/to/file'),
mode_conversion AS (
SELECT
CASE
WHEN pos_1 = '0' THEN '---'
WHEN pos_1 = '1' THEN '--x'
WHEN pos_1 = '2' THEN '-w-'
WHEN pos_1 = '3' THEN '-wx'
WHEN pos_1 = '4' THEN 'r--'
WHEN pos_1 = '5' THEN 'r-x'
WHEN pos_1 = '6' THEN 'rw-'
WHEN pos_1 = '7' THEN 'rwx'
END AS pos_1_converted,
CASE
WHEN pos_2 = '0' THEN '---'
WHEN pos_2 = '1' THEN '--x'
WHEN pos_2 = '2' THEN '-w-'
WHEN pos_2 = '3' THEN '-wx'
WHEN pos_2 = '4' THEN 'r--'
WHEN pos_2 = '5' THEN 'r-x'
WHEN pos_2 = '6' THEN 'rw-'
WHEN pos_2 = '7' THEN 'rwx'
END AS pos_2_converted,
CASE
WHEN pos_3 = '0' THEN '---'
WHEN pos_3 = '1' THEN '--x'
WHEN pos_3 = '2' THEN '-w-'
WHEN pos_3 = '3' THEN '-wx'
WHEN pos_3 = '4' THEN 'r--'
WHEN pos_3 = '5' THEN 'r-x'
WHEN pos_3 = '6' THEN 'rw-'
WHEN pos_3 = '7' THEN 'rwx'
END AS pos_3_converted,
CASE
WHEN pos_4 = '0' THEN '---'
WHEN pos_4 = '1' THEN '--x'
WHEN pos_4 = '2' THEN '-w-'
WHEN pos_4 = '3' THEN '-wx'
WHEN pos_4 = '4' THEN 'r--'
WHEN pos_4 = '5' THEN 'r-x'
WHEN pos_4 = '6' THEN 'rw-'
WHEN pos_4 = '7' THEN 'rwx'
ELSE ''
END AS pos_4_converted
FROM mode_split
),
mode_reconstructed AS (
SELECT pos_1_converted || pos_2_converted || pos_3_converted || pos_4_converted AS file_permissions FROM mode_conversion
)
SELECT * FROM mode_reconstructed;
WITH mode_split AS (
SELECT
SUBSTR(mode, 2, 1) AS pos_u,
SUBSTR(mode, 3, 1) AS pos_g,
SUBSTR(mode, 4, 1) AS pos_w
from file where path = '/path/to/file'),
mode_conversion AS (
SELECT
CASE
WHEN pos_u = '0' THEN '---'
WHEN pos_u = '1' THEN '--x'
WHEN pos_u = '2' THEN '-w-'
WHEN pos_u = '3' THEN '-wx'
WHEN pos_u = '4' THEN 'r--'
WHEN pos_u = '5' THEN 'r-x'
WHEN pos_u = '6' THEN 'rw-'
WHEN pos_u = '7' THEN 'rwx'
END AS pos_u_converted,
CASE
WHEN pos_g = '0' THEN '---'
WHEN pos_g = '1' THEN '--x'
WHEN pos_g = '2' THEN '-w-'
WHEN pos_g = '3' THEN '-wx'
WHEN pos_g = '4' THEN 'r--'
WHEN pos_g = '5' THEN 'r-x'
WHEN pos_g = '6' THEN 'rw-'
WHEN pos_g = '7' THEN 'rwx'
END AS pos_g_converted,
CASE
WHEN pos_w = '0' THEN '---'
WHEN pos_w = '1' THEN '--x'
WHEN pos_w = '2' THEN '-w-'
WHEN pos_w = '3' THEN '-wx'
WHEN pos_w = '4' THEN 'r--'
WHEN pos_w = '5' THEN 'r-x'
WHEN pos_w = '6' THEN 'rw-'
WHEN pos_w = '7' THEN 'rwx'
END AS pos_w_converted
FROM mode_split
),
mode_reconstructed AS (
SELECT pos_u_converted || pos_g_converted || pos_w_converted AS file_permissions FROM mode_conversion
)
SELECT * FROM mode_reconstructed;