Channels
  • zwass

    zwass

    4 years ago
    select 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/%';
  • f

    fritz

    2 years ago
    Had to deal with this today, figured i'd paste it here for the next person searching the archives, here is an example of casing the output values of the file table's
    mode
    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;