Obis4SQL Wiki

Uživatelské nástroje

Nástroje pro tento web

Umístění: index » progs » fblutil
Historie:
progs:fblutil

Rozdíly

Zde můžete vidět rozdíly mezi vybranou verzí a aktuální verzí dané stránky.

Odkaz na výstup diff

Následující verze		Předchozí verze
progs:fblutil [2020/01/17 08:57] – vytvořeno Karel Petrůprogs:fblutil [2020/01/31 14:23] (aktuální) – Karel Petrů
Řádek 1: Řádek 1:
 ====== FBLUtil ====== ====== FBLUtil ======
  
-===== Klonovani mezi DB =====+===== Import ceniku z CSV =====
  
 <code LUA> <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) function CloneTable(db1, TableName, db2)
   local t = db1:GetTable(TableName)   local t = db1:GetTable(TableName)
Řádek 39: Řádek 166:
   return n   return n
 end 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()   local t = os.time()
   print("Test DataPumpTo")   print("Test DataPumpTo")
-  CloneTable(db1, TableName, db2) +  CloneTable(db, TableName, db2) 
-  local n = db1:DataPumpTo(string.format("select * from %s", TableName), db2, string.format("insert into %s", TableName))+  local n = db:DataPumpTo(string.format("select * from %s", TableName), db2, string.format("insert into %s", TableName))
   db2:Commit()   db2:Commit()
-  CloneIndices(db1, TableName, db2)+  CloneIndices(db, TableName, db2)
   print(string.format("Done %d rows in %ds", n, os.difftime(os.time(), t)))   print(string.format("Done %d rows in %ds", n, os.difftime(os.time(), t)))
   local n = ExportAndDiff(db2, TableName, flds)   local n = ExportAndDiff(db2, TableName, flds)
Řádek 51: Řádek 226:
   db2:Execute(string.format("drop table %s", TableName))   db2:Execute(string.format("drop table %s", TableName))
   db2:Commit()   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> </code>
progs/fblutil.1579251441.txt.gz · Poslední úprava: 2020/01/17 08:57 autor: Karel Petrů