文件迁移到FileTable中

看此文档前，先参考一下文档 https://blog.csdn.net/downmoon/article/details/24374609

环境：阿里云ecs sql server 2017 + delphi7

测试用xcopy，robocopy等命令迁移文件好像不太会用。有感兴趣的朋友，告知一下。

倒是可以通过t-sql的方法，但是需要文件在服务器上面，这就有点难受了。如下：

--我们使用该函数插入一个图片文件到该目录下：这里的路径需要是服务器上的路径。
declare @image1 varbinary(max), @path_locator hierarchyid;
select @image1=cast(bulkcolumn as varbinary(max)) from openrowset(bulk n'c:\1.png', single_blob) as x;
select @path_locator=path_locator from documentstores where [name]='mydir1';
insert into documentstores(name, file_stream, path_locator) 
       values('1.png', @image1, dbo.fngetnewpathlocator(newid(), @path_locator));

--如果你想使用sql server本身提供的hierarchyid层次结构，下面这个函数也许可以帮你：
create function fngetnewpathlocator
        (@child uniqueidentifier, 
         @parent hierarchyid)
returns hierarchyid
as 
begin
  declare @ret hierarchyid, @binid binary(16) = convert(binary(16), @child);
  select @ret=hierarchyid::parse(
            coalesce(@parent.tostring(), n'/') +
            convert(nvarchar, convert(bigint, substring(@binid, 1, 6))) + n'.' +
            convert(nvarchar, convert(bigint, substring(@binid, 7, 6))) + n'.' +
            convert(nvarchar, convert(bigint, substring(@binid, 13, 4))) + n'/');
  return @ret;
end;

通过程序也能实现，只是如果层级太深，生成的path_locator太长，总感觉不太靠谱。

下面是用delphi实现的，insert操作(本地e:\doc目录下所有文件迁移到filetable)。

procedure tform1.bitbtn9click(sender: tobject);
var
  lst, lstcontent: tstrings;
  i: integer;
  strsql: string;
begin
  lst := tstringlist.create;
  lstcontent := tstringlist.create;
  try
    getfilestructurelist('e:\doc', lst);
    strsql := emptystr;
    rzprogressbar1.totalparts := lst.count;
    for i:=0 to lst.count-1 do
    begin
      splitstring(lst.strings[i], '|', lstcontent);
      if sametext(lstcontent.strings[0], '0') then      //目录
        strsql := strsql + format('insert into documentstores(name, path_locator, is_directory, is_archive) values(%s, %s, %d, %d);',
                                   [quotedstr(extractfilename(lstcontent.strings[1])), quotedstr(lstcontent.strings[2]), 1, 0]) + #13#10
      else if sametext(lstcontent.strings[0], '1') then //文件
        strsql := strsql + format('insert into documentstores(name, path_locator, file_stream) values(%s, %s, %s);',
                                   [quotedstr(extractfilename(lstcontent.strings[1])), quotedstr(lstcontent.strings[2]),
                                    strtohex(baseencodefile(lstcontent.strings[1]))]) + #13#10;
      rzprogressbar1.partscomplete := rzprogressbar1.partscomplete + 1;
      application.processmessages;
    end;
    try
      adoconnection1.connected := true;
      adoconnection1.begintrans;
      adoquery1.sql.text := strsql;
      adoquery1.execsql;
      adoconnection1.committrans;
    except
      adoconnection1.rollbacktrans;
    end;
  finally
    lst.free;
    lstcontent.free;
  end;
end;

//下面是公用单元
unit u_commfunc;

interface

uses
  windows, messages, sysutils, variants, classes, graphics, controls, forms,
  dialogs, encddecd, contnrs;

  //生成filetable用的path_locator
  function getpathlocator(root: boolean=true): string;
  function getguid: string;
  function strtohex(astr: string): string;
  //文件转字符串流
  function baseencodefile(fn: tfilename): string;
  procedure splitstring(source,deli:string; var lst :tstrings);
  //获取目录+文件的列表 返回值是文件的个数，顶层为选择的目录 为filetalbe插入用
  function getfilestructurelist(path: pchar; var lst: tstrings): longint;

implementation


function getguid: string;
var
  ltep: tguid;
  sguid: string;
begin
  createguid(ltep);
  sguid := guidtostring(ltep);
  sguid := stringreplace(sguid, '-', '', [rfreplaceall]);
  sguid := copy(sguid, 2, length(sguid) - 2);
  result := sguid;
end;

function getpathlocator(root: boolean): string;
var
  //locatorpath的三个组成部分 s1,s2,s3;
  sguid, s1, s2, s3: string;
begin
  result := '';
  if root then
    result := '/';
  sguid := getguid;
  s1 := inttostr(strtoint64(strtohex(copy(sguid, 1, 6))));
  s2 := inttostr(strtoint64(strtohex(copy(sguid, 7, 6))));
  s3 := inttostr(strtoint64(strtohex(copy(sguid, 13, 4))));
  result := result + s1 + '.' + s2 + '.' + s3 + '/';
end;

function strtohex(astr: string): string;
var
  i : integer;
  ch:char;
begin
  result:='0x';
  for i:=1 to length(astr)  do
  begin
    ch:=astr[i];
    result:=result+inttohex(ord(ch),2);
  end;
end;

function baseencodefile(fn: tfilename): string;
var
  ms: tmemorystream;
  ss: tstringstream;
  str: string;
begin
  ms := tmemorystream.create;
  ss := tstringstream.create('');
  try
    ms.loadfromfile(fn);
    encddecd.encodestream(ms, ss);                       // 将ms的内容base64到ss中
    str := ss.datastring;
    str := stringreplace(str, #13, '', [rfreplaceall]);  // 这里ss中数据会自动添加回车换行，所以需要将回车换行替换成空字符
    str := stringreplace(str, #10, '', [rfreplaceall]);
    result := str;                                       // 返回值为base64的stream
  finally
    freeandnil(ms);
    freeandnil(ss);
  end;
end;

procedure splitstring(source,deli:string; var lst :tstrings);
var
  endofcurrentstring: integer;
begin
  if  lst = nil then exit;
  lst.clear;
  while pos(deli, source)>0 do
  begin
    endofcurrentstring := pos(deli, source);
    lst.add(copy(source, 1, endofcurrentstring - 1));
    source := copy(source, endofcurrentstring + length(deli), length(source) - endofcurrentstring);
  end;
  lst.add(source);
end;

function getfilestructurelist(path: pchar; var lst: tstrings): longint;
var
  searchrec: tsearchrec;
  found: integer;
  tmpstr, tmplocator: string;
  curdir, dirlocator: pchar;
  dirque: tqueue;
  c: cardinal;
begin
  result := 0;
  if lst = nil then exit;
  dirque := tqueue.create;
  try
    curdir := path;
    dirlocator := pchar(getpathlocator());
    lst.add('0|'+curdir+'|'+dirlocator);
    while curdir <> nil do
    begin
      //搜索后缀，如：c:\*.*;
      tmpstr := includetrailingpathdelimiter(curdir)+'*.*';
      found := findfirst(tmpstr, faanyfile, searchrec);
      while found = 0 do
      begin
        c := getfileattributes(pchar(includetrailingpathdelimiter(curdir) + searchrec.name));
        //if (searchrec.attr and fadirectory)<>0 then  //这个貌似有问题/
        if (c and file_attribute_directory)<> 0 then
        begin
          if (searchrec.name <> '.') and (searchrec.name <> '..') then
          begin
            tmpstr := includetrailingpathdelimiter(curdir)+searchrec.name;
            tmplocator := getpathlocator(false);
            tmplocator := dirlocator + tmplocator;
            lst.add('0|'+tmpstr+'|'+tmplocator);
            dirque.push(strnew(pchar(tmpstr)));
            dirque.push(strnew(pchar(tmplocator)));
          end;
        end else begin
          result:=result+1;
          tmplocator := getpathlocator(false);
          tmplocator := dirlocator + tmplocator;
          lst.add('1|'+includetrailingpathdelimiter(curdir)+searchrec.name+'|'+tmplocator);
        end;
        found:=findnext(searchrec);
      end;
      {当前目录找到后，如果队列中没有数据，则表示全部找到了；
      否则就是还有子目录未查找，取一个出来继续查找。}
      if dirque.count > 0 then
      begin
        curdir := dirque.pop;
        dirlocator := dirque.pop;
      end else begin
        curdir := nil;
        dirlocator := nil;
      end;
    end;
  finally
    dirque.free;
  end;
end;

end.

效果图如下，目录加文件共计20个。

本地文件夹e:\doc：

 

 

 filetable虚拟目录文件doc：

数据库表中存放数据：