sql2fs

#!/usr/bin/perl

# == SQL 2 Filesystem Abstraction (FS Everything) ==
#    written by Rene K. Mueller <spiritdude@gmail.com>
#
my $APPNAME = "SQL2FS (sql2fs)";
my $VERSION = "0.0.6";
#
# License: MIT (see LICENSE file)
#
# Description:
#     ./sql2fs <dbname>
#     cd <dbname>
#     ls
#
# History:
# 2020/04/23: 0.0.6: adopting URI-like db referencing, e.g. pg://test or sqlite://test.db
# 2020/04/17: 0.0.5: support large content fields, referencing by SHA256 hash reference; removing null entries
# 2020/04/14: 0.0.4: adding sqlite support
# 2020/04/13: 0.0.3: adding mysql support, better usage/help output
# 2020/04/13: 0.0.2: allowing <table>/<col>/<val>#<col> solely, better utf-8 support
# 2020/04/13: 0.0.1: basic functionality works, items return JSON, only PostgreSQL supported

use DBI;
use DBD::Pg;
use DBD::mysql;
use DBD::SQLite;
use Fuse;
use POSIX qw(ENOENT EISDIR EINVAL O_WRONLY);
use JSON;
use Encode;
use Digest::SHA 'sha256_hex';

use strict;
use utf8;
use open qw(:std :utf8);

use constant {
    T_FILE => 0100,
    T_DIR => 0040
};

my $ba2dbi = { pg => "Pg", mysql => "mysql", sqlite => "SQLite" };

my $s2f = { v => 'verbose' };

my $arg = {
   backend => "pg",
   verbose => 0,
   hash_prefix => "x",
   content_max => 16*1024
};

my $dbh;                   # -- database handle
my $item;                  # -- item/file metadata cache
my $st_time = time();

my @a;
foreach(@ARGV) {
   if(/^-(\w+)$/) {
      $arg->{$s2f->{$_}}++ foreach(split(/|/,$1));
      next;
   }
   $arg->{$1}++, next if(/^--(\w+)$/);
   $arg->{$1} = $2, next if(/^--(\w+)=(.*)$/);
   push(@a,$_);
}
@ARGV = @a;

$arg->{backend} = $1 if($ARGV[0]=~s/^(\w+):\/\///);    # -- URI-like, let's extract backend

if(!defined $ba2dbi->{$arg->{backend}}) {
   print "$APPNAME: ERR: backend '$arg->{backend}' not supported, only ".join(", ",map { "'$_'" } sort keys %$ba2dbi)."\n";
   exit -1;
}

if($arg->{help} || @ARGV!=1) {
   print "USAGE $APPNAME $VERSION: [<options>] <database>
   options:
      --verbose            increase verbosity
        -v or -vvvv           \"       \"
      --backend=<db>       backend db type: ".join(", ",map { "'$_'" } sort keys %$ba2dbi)." (default: $arg->{backend})
                           alternatively use <backend>://<database>, e.g. sqlite://test.db or mysql://test
      --version            print version and exit
      --allow_other        permit other users to use mount (add 'user_allow_other' in /etc/fuse.conf)
      --mount=<dir>        enforce certain mount point
      --hash_prefix=<p>    define hash prefix (default: $arg->{hash_prefix})

   examples:
      % sql2fs pg://wikidata
      % cd wikidata/
      % ls 
      
      % sql2fs mysql://wordpress
      % cd wordpress/
      % ls
      % cd ..; killall sql2fs; sudo umount wordpress/
      
      % sql2fs sqlite://test.db
      % cd test/

   see also https://github.com/Spiritdude/SQL2FS for more information
   
";
   exit 0;
}

if($arg->{version}) {
   print "$APPNAME $VERSION\n";
   exit 0;
}

my $mount = $arg->{db} = shift(@ARGV);    # -- database becomes mount point

$mount =~ s/.+\///, $mount =~ s/\.\w+$// if($arg->{backend}eq'sqlite');    # -- remove path and extension if sqlite (e.g. /../../test.db)

$mount = $arg->{mount} if(defined $arg->{mount});

mkdir $mount;

die "$APPNAME: mount point '$mount' doesn't exist: $!\n" unless(-d $mount);

fork && exit;

$SIG{__DIE__} = sub {
   print "$APPNAME: exited.\n";
   print "$APPNAME: HINT: sudo umount $mount/\n";
   exit -1;
};

Fuse::main( 
   mountpoint => $mount, 
   getattr => \&my_getattr,
   getdir => \&my_getdir,
   open => \&my_open, 
   read => \&my_read, 
   release => \&my_close,
   utime => sub { return 0 },
   mountopts => $arg->{allow_other} ? "allow_other":"",
   threaded => 0
);

sub my_open {
   my($path,$mode) = @_;
   print "INF: open($path)\n" if($arg->{verbose});
   $path =~ s#/##;
   my $fh;
   return 0,$fh;
}

sub my_close {
   my($path,$flags,$fh) = @_;
   print "INF: close($path)\n" if($arg->{verbose});
   $path =~ s#/##;
   return 0;
}

sub my_read {
   my($path,$n,$off,$fh) = @_;
   print "INF: read($path,$n,$off)\n" if($arg->{verbose});
   $path =~ s#/##;
   my(@p) = split(/\//,$path);
   if(@p==3) {
      my $f; $f = $1 if($p[2]=~s/#(\w+)$//);
      $p[2] = fromFilename($p[2]);
      my $c = sql("select * from $p[0] where $p[1] = ?",$p[2]);
      my $e;
      if($f) {
         $e = encode('utf-8',$c->{$f});
      } else {
         $e = encode('utf-8',to_json($c,{pretty=>1,canonical=>1}));        # -- we need true byte stream
      }
      $e = substr($e,$off,$n);
      return $e;

   } if(@p==4) {     # /<table>/<col>/<val>/<n>
      my $f; $f = $1 if($p[3]=~s/#(\w+)$//);   
      $p[2] = fromFilename($p[2]);
      my(@c) = sql("select * from $p[0] where $p[1] = ? limit 1 offset $p[3]",$p[2]);   # -- .. offset ?",$p[3]*1) won't work with mysql, but works with pg
      return -1 if(@c==0||!$c[0]);
      my $e;
      if($f) {
         $e = encode('utf-8',$c[0]->{$f});
      } else {
         $e = encode('utf-8',to_json($c[0],{pretty=>1,canonical=>1}));        # -- we need true byte stream
      }
      $e = substr($e,$off,$n);
      return $e;
   }
   return -1;
}

sub my_getattr {
   my($path) = @_;
   print "INF: getattr($path)\n" if($arg->{verbose});

   $path =~ s#/##;
   $path = decode('utf-8',$path);

   my(@p) = split(/\//,$path);

   print "INF: path: ",join(",",map { "\"$_\"" } @p),"\n" if($arg->{verbose}>1);

   my($dev, $ino, $mode, $nlink, $uid, $gid, $rdev, $size, $atime, $mtime, $ctime, $blksize, $blocks); # = stat(".");

   ($dev, $ino, $rdev, $blocks, $nlink, $blksize) = (0, 0, 0, 1, 1, 1024);

   my $context = Fuse::fuse_get_context();
   my $type = T_DIR;
   my $perm = 0777;
   $size = 0;

   if($p[$#p]=~/^\.{1,2}?$/) {   # . and .. are valid always
      ;
   } elsif(@p<=0) {           # /
      ;
   } elsif(@p==1) {           # /table/
      return ( -ENOENT() ) if($p[0]=~/^\.\w+/);
      my $c;
      if($arg->{backend}eq'mysql') {
         $c = sql("select * from information_schema.tables where table_schema = ? and table_name = ?",$arg->{db},$p[0]);
      } elsif($arg->{backend}eq'sqlite') {
         $c = sql("select * from sqlite_master where type = 'table' and name = ?",$p[0]);
      } else {
         $c = sql("select * from information_schema.tables where table_name = ?",$p[0]);
      }
      return ( -ENOENT() ) if(!$c);
      
   } elsif(@p==2) {           # /table/column/
      return ( -ENOENT() ) if($p[0]=~/^\.\w+/);
      # my $c = sql("select $p[1] from $p[0] limit 1");
      my $c;
      if($arg->{backend}eq'sqlite') {
         $c = sql("select $p[1] from $p[0] limit 1");
      } else {
         $c = sql("select distinct column_name from information_schema.columns where table_name = ? and column_name = ?",$p[0],$p[1]);
      }
      return ( -ENOENT() ) if(!$c);
      
   } elsif(@p==3) {           # /table/column/value/<v>
      return ( -ENOENT() ) if($p[0]=~/^\.\w+/);
      my $f; $f = $1 if($p[2]=~s/#(\w+)$//);
      $p[2] = fromFilename($p[2]);
      my(@c) = sql("select * from $p[0] where $p[1] = ?",$p[2]);
      return ( -ENOENT() ) if(@c==0);
      if(@c==1) {
         my $e;
         if($f) {
            $e = encode('utf-8',$c[0]->{$f});
         } else {
            $e = encode('utf-8',to_json($c[0],{pretty=>1,canonical=>1}));
         }
         $size = length($e);
         $type = T_FILE;
      } else {
         ;                       # -- more values (dir)
      }

   } elsif(@p==4) {              # /table/column/value/<v>/<n>
      return ( -ENOENT() ) if($p[0]=~/^\.\w+/);
      my $f; $f = $1 if($p[3]=~s/#(\w+)$//);
      my(@c) = sql("select * from $p[0] where $p[1] = ? limit 1 offset $p[3]",$p[2]);      # -- .. offset ?",$p[3]*1) won't work with mysql, but works with pg
      return ( -ENOENT() ) if(@c==0||!$c[0]);
      my $e;
      if($f) {
         $e = encode('utf-8',$c[0]->{$f});
      } else {
         $e = encode('utf-8',to_json($c[0],{pretty=>1,canonical=>1}));
      }
      $size = length($e);
      $type = T_FILE;
   }

   print "type: $type\n" if($arg->{verbose}>2);
   $atime = time();
   $mtime = $ctime = $st_time;
   $mode = ($type << 9) | $perm;
   $uid = $context->{uid};
   $gid = $context->{gid};
   print "valid file: ",
      join(",", $dev, $ino, $mode, $nlink, $uid, $gid, $rdev, $size, $atime, $mtime, $ctime, $blksize, $blocks ),"\n" if($arg->{verbose}>3);
   return ($dev, $ino, $mode, $nlink, $uid, $gid, $rdev, $size, $atime, $mtime, $ctime, $blksize, $blocks);
}

sub my_getdir {
   my($path) = @_;
   print "INF: getdir($path)\n" if($arg->{verbose});
   $path =~ s#/##;
   
   my @d;

   push(@d,'.','..');      # -- must be there at all times - we are in UNIX world

   my(@p) = split(/\//,$path);

   if(@p==0) {     # / => /<table*>
      if($arg->{backend}eq'mysql') {
         # @d = sql("show tables");
         # @d = map { $_->{"Tables_in_".$arg->{db}} } @d;
         @d = sql("select table_name from information_schema.tables where table_schema = ?",$arg->{db});
      } elsif($arg->{backend}eq'sqlite') {
         @d = sql("select name as table_name from sqlite_master where type = 'table' and name not like 'sqlite_%'");
      } else {
         # @d = sql("select * from pg_catalog.pg_tables where schemaname != 'pg_catalog' and schemaname != 'information_schema'");
         @d = sql("select table_name from information_schema.tables where table_schema != 'pg_catalog' and table_schema != 'information_schema'");
      }
      print to_json(\@d,{pretty=>1,canonical=>1}) if($arg->{verbose}>3);
      @d = sort map { encode('utf-8',$_->{table_name}) } @d;

   } elsif(@p==1) {     # /<table> => /<table>/<col*>
      if($arg->{backend}eq'sqlite') {
         my $c = sql("select sql from sqlite_master where type = 'table' and name = ?",$p[0]);
         if($c && $c->{sql} =~ /\(([^\)]+)\)/) {      # -- no other way than parsing output "create table (....)"
            my(@c) = split(/,\s?/,$1);                
            @d = map { s/\s.*$//; my $e = $_; $_ = {}; $_->{column_name} = $e; $_ } @c;
         }
      } else {
         @d = sql("select distinct column_name from information_schema.columns where table_name = ?",$p[0]);
      }
      @d = sort map { encode('utf-8',$_->{column_name}) } @d;
      print to_json(\@d,{pretty=>1,canonical=>1}) if($arg->{verbose}>3);

   } elsif(@p==2) {     # /<table>/<col> => /<table>/<col>/<val*>
      @d = sql("select distinct $p[1] from $p[0]");
      # @d = sql("select distinct ? from ?",$p[1],$p[0]);    # -- would be safe ... but doesn't work ... (SQL sucks)
      @d = grep { defined $_->{$p[1]} } @d;                  # -- remove null
      if(0) {
         @d = sort map { encode('utf-8',$_->{$p[1]}) } @d;
      } else {          # future: handle large content (e.g. with binary or \n\r)
         @d = sort map { encode('utf-8',toFilename($_->{$p[1]})) } @d; 
      }
      print to_json(\@d,{pretty=>1,canonical=>1}) if($arg->{verbose}>3);

   } elsif(@p==3) {     # /<table>/<col>/<val> => /<table>/<col>/<val>/<id*>
      @d = sql("select * from $p[0] where $p[1] = ?",$p[2]);
      my $n = 0;
      @d = map { $n++; } @d;
      print to_json(\@d,{pretty=>1,canonical=>1}) if($arg->{verbose}>3);
   }
   push(@d,0);
   return @d;
}

sub sql {
   my($sql) = shift;
   my @a = @_;
   my $opts = ref(@a[$#a])eq'HASH'?pop(@a):{};

   $dbh = DBI->connect("dbi:$ba2dbi->{$arg->{backend}}:dbname=".$arg->{db}) unless($dbh);

   print "INF: sql \"$sql\" ".join(" ",map {"\"$_\""} @a)."\n" if($arg->{verbose}>1);
   my $sth = $dbh->prepare($sql);
   my $s = $sth->execute(@a);
   my @r;

   print STDERR "SQL ERR ".$dbh->errstr()."\n" if($dbh->errstr());
   if(defined $opts->{results} && $opts->{results}==0) {
      $sth->{finish};
      return;
   } else {
      while(my $e = $sth->fetchrow_hashref()) {
         if(ref($opts->{callback})eq'CODE') {
            &{$opts->{callback}}($e);
         } else {
            push(@r,$e);
         }
      }
   }
   print "INF:   ".scalar @r." results, returning ".(wantarray ? "array":"scalar")."\n" if($arg->{verbose}>1);
   $sth->{finish};
   return wantarray ? @r : $r[0];
}

# -- toFilename: transform content into a valid UNIX filename
#  - issues:
#     - ideally all transformation should be reversible
#     - truncate is non-reversible
#     - how to determine if transformation was actually done?
#        - keep track of it internally with a cache
sub toFilename {
   my($d) = @_;
   my $d0 = $d;
   my $sz;
   my $alt;
   
   if(0) {
      $alt++ if($d =~ s/\n/\\n/g);
      $alt++ if($d =~ s/\r/\\r/g);
      if($d =~ /[\x00-\x1f]/) {       # -- non-printable content?
         $d =~ s/([\x00-\x1f])/sprintf("x%02x",ord($1))/eg;
         $alt++;
      }
      if(length($d)>128) {            # -- we truncate, but . . . we need to know, our query needs to be truncated as well
         $d = substr($d,0,126)."..";
         $sz = length($d);
         $alt++;
         $item->{$d}->{size} = $sz;
      }
   } else {
      if($d=~/[\x00-\x1f]/ || length($d)>128) {
         $d = $arg->{hash_prefix} . sha256_hex($d0);
         $item->{$d}->{type} = 'sha256';
         $d0 = substr($d0,0,$arg->{content_max}) if(length($d0)>$arg->{content_max});
         $item->{$d}->{content} = $d0;
      }
   }
   return $d;
}

# -- fromFilename: transform from valid UNIX filename back to content
#    - how to determine if transformation was actually done?
#      - look up some cache to determine such
sub fromFilename {
   my($d) = @_;
   my $d0 = $d;
   my $alt;
   if(0) {
      if(0 && $d =~ /x[0-9a-fA-F]{2}/) {
         $d =~ s/x([0-9a-fA-F]{2})/sprintf("%c",hex($1))/eg;
         $alt++;
      }
      $alt++ if($d =~ s/\\n/\n/g);
      $alt++ if($d =~ s/\\r/\r/g);
   } else {
      if(substr($d,0,1) eq $arg->{hash_prefix} && $item->{$d}->{type} eq 'sha256') {
         $d = $item->{$d}->{content};
      }
   }
   return $d;
}