Changeset 52582cf in tol

Timestamp:

Oct 26, 2008, 10:36:44 AM (18 years ago)

Author:

Víctor de Buen <vdebuen@…>

Branches:

1941-docker-image-to-test, devel, master

Children:

6f22f5e

Parents:

e7bf535

Message:

New methods to handle with cached inserting queries to build optimized SQL scripts

git-svn-id: ​https://www.tol-project.org/svn/tolp/trunk@81 be4846ae-f051-483a-831f-b64e7d08527f

Location:

tol/stdlib/math/stat/models/bayesian/bysMcmc/bsr/dynhlm

Files:

• _db_api.tol (modified) (2 diffs)
• _db_api.tools.tol (modified) (4 diffs)

tol/stdlib/math/stat/models/bayesian/bysMcmc/bsr/dynhlm/_db_api.tol

@@ -20 +20 @@
   //Enables or disables SQL tracing
   Real doQueryTrace = False;
-  
+  //Enables or disables automatic SQL cache
+  Real allowAutoCache = False;
+
+  Struct CachedTableReg
+  {
+    Text table_;
+    Text path_;
+    Real file_;
+    Real numReg_
+  };
+
+  Real _autoCache = False;
+  Text _cacheQueryRootPath = "";
+  Set  _cachedTables = Copy(Empty);
+
+  Real SqlExecScript.NotImplemented(Text filePath)
+  {
+    WriteLn("[SqlExecScript.NotImplemented] Sorry assign "
+      "BysMcmc::Bsr::DynHlm::DBApi::SqlExecScript or disable "+
+      "BysMcmc::Bsr::DynHlm::DBApi::allowAutoCache","E");
+    Real Stop
+  };
+
+  Code SqlExecScript = SqlExecScript.NotImplemented;
+
   //Set of TOL alias for database tables
   Set _.tableName = 
@@ -58 +82 @@
   Code SqlTableTrace;
   Code SqlExecTrace;
+  Code SqlCachedTableFile;
+  Code SqlCachedInsert;
+  Code SqlStartCachedInsert;
+  Code SqlStopCachedInsert;
   Code SqlFormatReal;
   Code SqlFormatDate;

tol/stdlib/math/stat/models/bayesian/bysMcmc/bsr/dynhlm/_db_api.tools.tol

@@ -5 +5 @@
 //////////////////////////////////////////////////////////////////////////////
 
+
 //////////////////////////////////////////////////////////////////////////////
 //Set current data base engine to MySQL
@@ -12 +13 @@
   Text _.engine := SqlEngine::GesMys;
   0
+};
+
+//////////////////////////////////////////////////////////////////////////////
+//Trace and execute a non SELECT query returning nothing but success or not
+Real SqlExecTrace(Text query)
+//////////////////////////////////////////////////////////////////////////////
+{
+  If(doQueryTrace,
+    WriteLn("[Bsr::DynHlm::DBApi::SqlExecTrace] "+
+    If(_autoCache,"CACHED","")+" "+
+    "Query=\n"+query)); 
+  DBExecQuery(query)
 };
 
@@ -27 +40 @@
 
 //////////////////////////////////////////////////////////////////////////////
-//Trace and execute a non SELECT query returning nothing but success or not
-Real SqlExecTrace(Text query)
-//////////////////////////////////////////////////////////////////////////////
-{
-  If(doQueryTrace,
-    WriteLn("[Bsr::DynHlm::DBApi::SqlExecTrace] Query=\n"+query)); 
-  Real DBExecQuery(query);
-  1
-};
-
-//////////////////////////////////////////////////////////////////////////////
 Text SqlFormatDate(Date dte)
 //////////////////////////////////////////////////////////////////////////////
@@ -52 +54 @@
 };
 
+//////////////////////////////////////////////////////////////////////////////
+//
+Set SqlCachedTableFile(Text table)
+//////////////////////////////////////////////////////////////////////////////
+{
+  Text id_table = ToName(table);
+  Real idx = FindIndexByName(_cachedTables,id_table);
+  If(idx, _cachedTables[idx],
+  {
+    Text path = _cacheQueryRootPath+id_table+".sql";
+    Real file = FOpen(path,"w");
+    Real FPutText(file,"INSERT INTO "+table+" VALUES");
+    Set aux = CachedTableReg(table,path,file,0);
+    Set entry = SetOfSet(Eval(id_table+"=aux"));
+    Set Append(_cachedTables, entry);
+    Real SetIndexByName(_cachedTables);
+    entry[1]
+  })
+};
+
+//////////////////////////////////////////////////////////////////////////////
+//
+Real SqlCachedInsert(Text table, Text values)
+//////////////////////////////////////////////////////////////////////////////
+{
+  If(!_autoCache,
+  {
+    SqlExecTrace(
+    "INSERT INTO "+table+"\n"+
+    "VALUES("+values+")")
+  },
+  {
+    Set tabReg = SqlCachedTableFile(table);    
+    Real FPutText(tabReg->file_, If(tabReg->numReg_,",","")+"\n"+
+      "("+values+")");
+    Real tabReg->numReg_ := tabReg->numReg_+1;
+    1
+  })
+};
+
+//////////////////////////////////////////////////////////////////////////////
+//Enables automatic cached queries
+Real SqlStartCachedInsert(Real unused)
+//////////////////////////////////////////////////////////////////////////////
+{
+  If(!allowAutoCache,1,
+  {
+    Real _autoCache := True;
+    Set _cachedTables := Copy(Empty);
+  //WriteLn("TRACE [SqlStartCachedInsert] 1");
+    Text _cacheQueryRootPath := 
+       Replace(TolAppDataPath,"\\","/")+"SqlScript/"+
+       "tmp."+IntText(Rand(1,999999999))+"/";
+  //WriteLn("TRACE [SqlStartCachedInsert] 2");
+    WriteLn("[SqlStartCachedInsert] Storing SQL cache into "+
+      _cacheQueryRootPath);
+    Real OSDirMake(_cacheQueryRootPath);
+  //WriteLn("TRACE [SqlStartCachedInsert] 3");
+    1
+  })
+};
+
+//////////////////////////////////////////////////////////////////////////////
+//Disables automatic cached queries
+Real SqlStopCachedInsert(Real unused)
+//////////////////////////////////////////////////////////////////////////////
+{
+  Real ok = If(!allowAutoCache,1,
+  {
+    Real _autoCache := False;
+    Real ok.1 = SetMin(EvalSet(_cachedTables, Real(CachedTableReg tabReg)
+    {
+      Real FClose(tabReg->file_);
+      Text query = ReadFile(tabReg->path_);
+      Real ok.1.1 = SqlExecTrace(query);
+    //Real ok.1 = SqlExecScript(ReplaceSlash(tabReg->path_));
+      Real ok.1.2 = OSFilRemove(tabReg->path_);
+      And(ok.1.1,ok.1.2)
+    }));
+    Real ok.2 = OSDirRemove(_cacheQueryRootPath);
+    Text _cacheQueryRootPath := "";
+    Set _cachedTables := Copy(Empty);
+    And(ok.1,ok.2)
+  });
+  ok
+};
 
 //////////////////////////////////////////////////////////////////////////////