Obis4SQL Wiki

Tato stránka je pouze pro čtení. Můžete si pouze prohlédnout zdrojový kód, ale ne ho měnit. Zeptejte se správce, pokud si myslíte, že něco není v pořádku.
====== FBLUtil ======

===== Import ceniku z CSV =====

<code LUA>
--../bin64/fblutil scripts/import_cenik.lua -debug 1 -- testcen

local DBName = "localhost:/home/firebird/data.fdb"
local CenikName=arg[1]
local ImportFN = "/tmp/"..CenikName..".csv"
local IDDruh="x-"..CenikName
local Prefix=string.upper(string.sub(CenikName,1,2))
local PrefixNumChars=4
local ISC_PASSWORD=os.getenv("ISC_PASSWORD")

print("Starting with", _VERSION)
print("Parameters after -- param: ", arg)
print("Connecting "..DBName)
local  db = FBLUtil.ConnectDB(DBName, "sysdba", ISC_PASSWORD)

function CloneTable(db1, TableName, CloneTableName)
  local r=db1:GetTable(CloneTableName)
  if #r>0 then --tabulka existuje
    print("Drop "..CloneTableName)
    db1:Execute(string.format("DROP TABLE %s", CloneTableName))
    db1:Commit()
  end
  local t = db1:GetTable(TableName)
  local flds = ""
  for i, v in ipairs(t) do
    local s = string.format("%s %s", v.Field, v.Typ)
    if flds ~= "" then
      flds = flds .. ", "
    end
    flds = flds .. s
  end
  print("Create "..CloneTableName)
  db1:Execute(string.format("CREATE TABLE %s (%s)", CloneTableName, flds))
  db1:Commit()
  local t = db1:GetIndices(TableName)
  for i, v in ipairs(t) do
    db1:Execute(string.format("CREATE %s INDEX %s ON %s (%s)", v.Opts, "V_"..v.Name, CloneTableName, v.Fields))
    if v.Inactive then
      db1:Execute(string.format("ALTER INDEX %s INACTIVE", "V_"..v.Name))
    end
  end
  db1:Commit()
end

function CloneCenik()
  CloneTable(db, "rozpocty_cenik", "v_rozpocty_cenik")
end

function GetNextPrefixNum(NumChars)
  local r=db:SelectToTable(string.format("select first 1 IDCen from rozpocty_cenik where IDDruh='%s' order by IDCen desc", IDDruh))
  --print(r)
  local p=string.format("%%0%dd", NumChars)
  local n=0
  if #r.Rows>0 then
    local f=string.len(Prefix)+1
    local t=string.len(Prefix)+NumChars
    n=string.sub(r.Rows[1][1], f,t)+1
    --print(p,f,t,n)
  end
  return string.format(p, n)
end

local PrefixNum=GetNextPrefixNum(PrefixNumChars)

function Import_testcen()
  print("Import from "..ImportFN)
  local t = os.time()
  print(string.format("PrefixNum = %d", PrefixNum))
  local n = db:ImportTable(ImportFN, ";", "UTF-8", "base64", [[insert into v_rozpocty_cenik 
         (IDCen,               IDDruh,                      CisRozp, ObjCis, Popis, MJ, Naklady, Hmotnost, v_IDDod, DatumZmeny) 
  values (']]..Prefix..PrefixNum..[['||$COUNTER(%6.6d), ']]..IDDruh..[[', $(5), $(2), $(7), $(3), $(6),   $(4),    $(1), CURRENT_TIMESTAMP)]])
  db:Commit()
  print(string.format("Done %d rows in %ds", n, os.difftime(os.time(), t)))
end

function UpdateCenik()
  print("Update "..IDDruh)
  local t = os.time()
  local n=db:ExecuteParams([[update rozpocty_cenik c set
    naklady=(select naklady from v_rozpocty_cenik v where v.v_iddod=c.v_iddod and c.iddruh=v.iddruh),
    DatumZmeny=CURRENT_TIMESTAMP
    where 
      exists (select * from v_rozpocty_cenik v where v.v_iddod=c.v_iddod)]],{})
  db:Commit()
  print(string.format("Update %d rown in %ds", n, os.difftime(os.time(), t)))
  t = os.time()
  local n=db:ExecuteParams([[insert into rozpocty_cenik select * from v_rozpocty_cenik v
  where not exists (select * from rozpocty_cenik c where c.v_iddod=v.v_iddod and c.iddruh=v.iddruh)
    ]],{})
  db:Commit()
  print(string.format("Insert %d rows in %ds", n, os.difftime(os.time(), t)))
end

function CreateCenik()
  print("Create new "..IDDruh)
  local t = os.time()
  db:Execute(string.format([[update or insert into rozpocty_cdruh (IDDruh, Typ) values ('%s', 'd') matching (IDDruh)]], IDDruh))
  local n=db:ExecuteParams([[insert into rozpocty_cenik select * from v_rozpocty_cenik]],{})
  db:Commit()
  print(string.format("Insert %d rows in %ds", n, os.difftime(os.time(), t)))
end


CloneCenik()

assert(load("Import_"..CenikName.."()"))()

if tonumber(PrefixNum) == 0 then
  CreateCenik()
else
  UpdateCenik()
end

db:Disconnect()

</code>

===== Prenos dat mezi DB, Import, Export =====

<code LUA>
local Path = "localhost:/home/firebird/"
local DBName1 = "data_test1.fdb"
local DBName2 = "data_test2.fdb"
local Tmp = "/tmp/"
local FName0 = "fbutil_0.csv"
local FName1 = "fbutil_1.csv"

function CloneTable(db1, TableName, db2)
  local t = db1:GetTable(TableName)
  local flds = ""
  for i, v in ipairs(t) do
    local s = string.format("%s %s", v.Field, v.Typ)
    if flds ~= "" then
      flds = flds .. ", "
    end
    flds = flds .. s
  end
  db2:Execute(string.format("CREATE TABLE %s (%s)", TableName, flds))
  db2:Commit()
end

function CloneIndices(db1, TableName, db2)
  local t = db1:GetIndices(TableName)
  for i, v in ipairs(t) do
    db2:Execute(string.format("CREATE %s INDEX %s ON %s (%s)", v.Opts, v.Name, TableName, v.Fields))
    if v.Inactive then
      db2:Execute(string.format("ALTER INDEX %s INACTIVE", v.Name))
    end
  end
  db2:Commit()
end

function ExportAndDiff(DB, TableName, flds)
  local n = DB:ExportTable(Tmp..FName1, ";", "UTF-8", "base64", string.format("select %s from %s", flds, TableName))
  local err, name, num = os.execute(string.format("diff %s %s >/dev/null 2>&1", Tmp..FName0, Tmp..FName1))
  --LUA5.1 vraci cislo, >=5.2 vraci true nebo nil
  print("Diff", err, name, num)
  if (type(err) == "number" and err ~= 0) or not err then
    error(string.format("ERROR: Diff table %s (%s)", TableName, tostring(err)))
  end
  return n
end

print("Starting with", _VERSION)
print("Parameters after -- param: ", arg)
local  db = FBLUtil.ConnectDB(Path..DBName1, "sysdba", "Andromeda")
local  ok, db2 = pcall(FBLUtil.ConnectDB, Path..DBName2, "sysdba", "Andromeda")
if not ok then
  print("DB2 not found, creating...")
  db2 = FBLUtil.CreateDB(Path..DBName2, "sysdba", "Andromeda", "PAGE_SIZE 16384 DEFAULT CHARACTER SET UTF8")
  --db2 = FBLUtil.CreateDB(Path..DBName2, "sysdba", "Andromeda", "PAGE_SIZE 16384 DEFAULT CHARACTER SET WIN1250")
end

function TestTable(TableName)
  print("TableName: ", TableName)
  local Fields = db:GetTable(TableName)

  local flds = ""
  local vals = ""
  local parms = ""
  for i, v in ipairs(Fields) do
    if flds ~= "" then flds = flds .. "," end
    flds = flds .. v.Field
    if vals ~= "" then vals = vals .. "," end
    vals = vals .. "$("..tostring(i)..")"
    if parms ~= "" then parms = parms .. "," end
    parms = parms .. "?"
  end
  local match = ""
  local ix = db:GetIndices(TableName)
  for i, v in ipairs(ix) do
    if v.Opts == "UNIQUE" then
      match = v.Fields
      break
    end
  end

  local t = os.time()
  --local TableRows = db:ExportTable(Tmp..FName0, ";", "UTF-8", "base64", string.format("select %s from %s where idzmena='OB1154523686' and id=733", flds, TableName))
  local TableRows = db:ExportTable(Tmp..FName0, ";", "UTF-8", "base64", string.format("select %s from %s", flds, TableName))
  print(string.format("Exported %d rows %ds", TableRows, os.difftime(os.time(), t)))

  local t2idx = {}
  for i, v in ipairs(db2:GetTables()) do
    t2idx[v] = true
  end
  if t2idx[TableName] then
    db2:Execute(string.format("drop table %s", TableName))
    db2:Commit()
  end

  local t = os.time()
  print("Test DataPumpTo")
  CloneTable(db, TableName, db2)
  local n = db:DataPumpTo(string.format("select * from %s", TableName), db2, string.format("insert into %s", TableName))
  db2:Commit()
  CloneIndices(db, TableName, db2)
  print(string.format("Done %d rows in %ds", n, os.difftime(os.time(), t)))
  local n = ExportAndDiff(db2, TableName, flds)
  assert(TableRows == n)
  db2:Execute(string.format("drop table %s", TableName))
  db2:Commit()

  local t = os.time()
  print("Test ImportTable")
  CloneTable(db, TableName, db2)
  CloneIndices(db, TableName, db2)
  --local n = db2:ImportTable(Tmp..FName0, ";", "UTF-8", "base64", string.format("update or insert into %s (%s) values (%s) matching (%s)", TableName, flds, vals, match)) --neni-li index, desne pomaly
  local n = db2:ImportTable(Tmp..FName0, ";", "UTF-8", "base64", string.format("insert into %s (%s) values (%s)", TableName, flds, vals))
  db2:Commit()
  --CloneIndices(db, TableName, db2)
  print(string.format("Done %d rows in %ds", n, os.difftime(os.time(), t)))
  assert(TableRows == n)
  local n = ExportAndDiff(db2, TableName, flds)
  assert(TableRows == n)
  db2:Execute(string.format("drop table %s", TableName))
  db2:Commit()

  local t = os.time()
  print("Test SelectCallback+ExecuteParams")
  CloneTable(db, TableName, db2)
  local Struct = {}
  --local n = db:SelectCallback(string.format("select %s from %s where idvykp='VYK05000811'", flds, TableName),
  local n = db:SelectCallback(string.format("select %s from %s", flds, TableName),
    function(t)
      if t.Structure then
        for i, v in ipairs(t.Structure) do
          local tt = {}
          for kk, vv in pairs(v) do
            tt[kk] = vv
          end
          table.insert(Struct, tt)
        end
      else
        --print(t)
        --db2:ExecuteParams(string.format("update or insert into %s (%s) values (%s) matching (%s)", TableName, flds, parms, match), t)
        db2:ExecuteParams(string.format("insert into %s (%s) values (%s)", TableName, flds, parms), t)
        --return false --ukoncit
      end
    end
  )
  db2:Commit()
  CloneIndices(db, TableName, db2)
  print(string.format("Done %d rows in %ds", n, os.difftime(os.time(), t)))
  assert(TableRows == n)
  --print("------------") print(Struct)
  local n = ExportAndDiff(db2, TableName, flds)
  assert(TableRows == n)
end

--print(db:GetTable("TEST1"))

local t = db:GetTables()
if arg and arg[1] then
  TestTable(arg[1])
else
  for i, v in ipairs(t) do
    TestTable(v)
  end
end

db2:Drop()
--db2:Disconnect()

db:Disconnect()

print("Set exit code")
return 6

</code>