Custom function for date time stamping

[Bern Ertl]

Add this to a SQLiteningProcs[Letter] file:

Code Select Expand

UNION Quad_FileTime
   Q AS QUAD
   F AS FILETIME
END UNION

FUNCTION qFormat( BYVAL q AS QUAD) AS STRING

   IF q > 100000&& THEN
       FUNCTION = FORMAT$( q \ 100000&&) + FORMAT$( q MOD 100000&&)
   ELSE
       FUNCTION = FORMAT$( q)
   END IF

END FUNCTION

SUB  sqlFnGetTimeStamp CDECL ( BYVAL hContext AS DWORD, BYVAL iArgs AS LONG, BYVAL pArgv AS DWORD PTR) EXPORT

   'Returns FORMAT$( FILETIME) = current server date/time

   LOCAL sResult AS STRING
   LOCAL ST as SYSTEMTIME
   LOCAL CurTime AS Quad_FileTime

   GetLocalTime ST
   SystemTimeToFileTime ST, CurTime.F

   sResult = qFormat( CurTime.q) + $NUL

   sqlite3_result_text hContext, BYVAL STRPTR( sResult), -1, %SQLITE_TRANSIENT

END SUB

FUNCTION ICF ALIAS "ICF" (BYVAL rhDab AS DWORD, BYVAL rlTcpFileNumber AS LONG, blParm1 AS LONG, blParm2 AS LONG, bsParm3 AS STRING, bsParm4 AS STRING) EXPORT AS LONG

   'ICF = Initialize Custom Functions

   'In = None

   'Out:  FUNCTION = 0 for success, <>0 if error
   '         blParm1 = where in execution path SQLite error occured (if any)
   '         bsParm3 = error message if error occurred

   LOCAL lResult AS LONG, zText AS ASCIIZ * 32

   zText = "ServerTimeStamp"
   lResult = sqlite3_create_function( rhDab, zText, 0, %SQLITE_UTF8, 0, CODEPTR( sqlFnGetTimeStamp), 0, 0)

   IF ISTRUE lResult THEN
      bsParm3 = "Failed to create ServerTimeStamp"
      blParm1 = 500  'Return "error line number"
      FUNCTION = lResult 'Set return code
      EXIT FUNCTION
   END IF

END FUNCTION

In your application:

Code Select Expand

...
   'Register functions and keep them persistent ...
   lResult = slRunProc( "[letter]ICF", lReturn1, lReturn2, sBuffer1, sBuffer2, "Eu")
...
   'Application Code
...
   'Unload custom functions when no longer needed
   slRunProc "[letter]", lReturn1, lReturn2, sBuffer1, sBuffer2, "U"
...

and finally, in your SQL wherever date time stamping is required...

Code Select Expand

CREATE TABLE IF NOT EXISTS TransactionLog (RowID INTEGER PRIMARY KEY, SQL_Statement, User, TimeStamp);
CREATE TRIGGER fki_TransactionLog_Update_TimeStamp AFTER INSERT ON TransactionLog FOR EACH ROW
   BEGIN
      UPDATE TransactionLog SET TimeStamp = ServerTimeStamp() WHERE RowID = NEW.RowID;
   END;

[Fredrick Ughimi]

Hello Bern,

This is exactly what I have been looking for.

What is the difference SQLiteningProcsA and SQLiteningProcsB?

Which should I used to implement your codes here.

[cj]

Fredrick,

I haven't used procs, but was thinking of making it easier to insert functions into the server.
I'm not convinced adding code to the server is harder than using a proc once a function is added to demonstrate it that can be duplicated and modified.

After the modification below, SQliteningServer.bas compiles with a click.

1. Did you download and install this:
https://sqlitening.planetsquires.com/index.php?topic=9697.msg26453;topicseen#msg26453

2. Is this to add a time stamp to
  Every insert (unlikely)
  Create a table logging all entries?
  Create a flat file text log on disk?

3. This is for the server, right?

I've noticed running without the server is much faster on a local network
and not sure it is even needed by most.

[Fredrick Ughimi]

Hello CJ,

Thank you for your response.

>>1. Did you download and install this:
https://sqlitening.planetsquires.com/index.php?topic=9697.msg26453;topicseen#msg26453

I think I downloaded it. But I haven't installed it. I will now. How do you use it?

>>Is this to add a time stamp to
  Every insert (unlikely)

Yes. Every Insert, Update, Delete, etc

>>Create a table logging all entries?

Yes. Something like that.

>>Create a flat file text log on disk?

Yes.

>>3. This is for the server, right?

Yes.

>>I've noticed running without the server is much faster on a local network
and not sure it is even needed by most.

Really? How does that work?

Kind regards.

[cj]

The link is to a modified SQLiteningServer.bas that might already do what you want.
Compile it and copy  SqliteningServer.exe to whereever your current SQLiteningServer.exe is.

If you installed the modified SQLiteningServer.Bas
the log (SqliteningServer.log) would look like this:

07-21-2019 14:45:23  SELECT * FROM parts LIMIT 1
07-21-2019 14:45:55  SELECT * FROM parts LIMIT 2 ORDER BY PRICE

Also, if you run the filewatcher.exe program, every time someone
accesses the server you would see a scrolling window move up one
line to show you the last line in the log file (SqliteningServer.log)

Sounds like all you need to do is install the modification
and optionally run file watcher to see the changes to the log in real-time.

I've noticed running without the server is much faster on a local network
and not sure it is even needed by most.

>> Really? How does that work?

REM slConnect

[Fredrick Ughimi]

Thanks CJ,

All done now. Would do some testing in the days to come.

>>REM slConnect

And it would still run on a LAN?

[cj]

Definitely.  See the first post in the general forum.

[Fredrick Ughimi]

Hello CJ,

>>the log (SqliteningServer.log) would look like this:
07-21-2019 14:45:23  SELECT * FROM parts LIMIT 1
07-21-2019 14:45:55  SELECT * FROM parts LIMIT 2 ORDER BY PRICE

I guess it be great if the user or computer on the network is also shown in the log.

[Fredrick Ughimi]

>>Definitely.  See the first post in the general forum.

That means no need for port number and the server computer name and ipadress don't matter?

Code Select Expand

sServer = "Server"
sPort = "Default"
slConnect(sServer, Val(sPort))


[cj]

Fredrick,
Download/extract SQLiteningServer.bas https://sqlitening.planetsquires.com/index.php?topic=9697.0

SUB LogIt() can be modified to whatever you want.
Compile and replace SQLiteningServer.exe
'===================================================================================

Bypassing the server is much faster and less to distribute.
I tested without using SQLiteningServer to a mapped drive without issue.

slOpen "z:\test\database.db3","C"
Always use "begin immediate" or "begin exclusive" instead of just "begin"

I've used the server for many years and thinking of swithing to simple networking.

Is the server really needed or better on a small LAN?

[Fredrick Ughimi]

>>Is the server really needed or better on a small LAN?

How do we implement this in practice in relation to installation? I guess all systems on the network would be client computers with one of the clients computers hosting the database. That means no need to install the servers files where the database resides?

I really like to try this out.

[cj]

True, just a normal local area network.
Share a folder on one computer, and map to that folder on the clients.
The SQLitening server/service would not be needed nor the SQLiteingServer.exe be distributed.
Clients would use something like this: slOpen "z:\test\database.db3","C"

[cj]

'Test multiple clients without multiple computers or SQLitening server

#INCLUDE "sqlitening.inc"  'fredrick9.bas

FUNCTION PBMAIN AS LONG

 LOCAL x,hthread AS LONG

 'Simulate 3 client workstations
 FOR x = 1 TO 3
  THREAD CREATE WorkStation(x) TO hThread
  THREAD CLOSE hThread  TO hThread
 NEXT
 DO:SLEEP 100:LOOP UNTIL THREADCOUNT=1
END FUNCTION

 ViewAll

'===========================================================
THREAD FUNCTION WorkStation(BYVAL x AS LONG)
 Helper x
END FUNCTION

SUB Helper(x AS LONG) THREADSAFE
 slopen "hello.db3","C"   '<--- would be in a shared folder on network
 slexe  "create table if not exists t1(column1)"
 slexe  USING$("insert into t1 values('write from thread#')",x)
 slclose
END SUB


'===========================================================
FUNCTION ViewAll AS STRING
 LOCAL s() AS STRING
 slopen "hello.db3"   
 slselary "select * from t1",s(),"Q9c"
 ? JOIN$(s(),$CR),,"All users done"
END FUNCTION

[Bern Ertl]

...
What is the difference SQLiteningProcsA and SQLiteningProcsB?
...

It doesn't matter. 

SQLiteningProcsA
SQLiteningProcsB
SQLiteningProcsC
SQLiteningProcsD
SQLiteningProcsE
SQLiteningProcsF
...
SQLiteningProcsZ


You can use any of them.  SQLitening allows you to organize your server side code libraries into separate files with this naming convention, but there is no difference as to which you use.  It's just for you to organize your code.

I mainly use SQLiteningProcsT and SQLiteningProcsP for my server code.  The "T" library contains server code that is loaded, used and unloaded (ie. "Temporary").  The "P" library contains code that is loaded and stays loaded until the app closes (ie. "Permanent").

[Bern Ertl]

2. Is this to add a time stamp to
  Every insert (unlikely)
  Create a table logging all entries?
  Create a flat file text log on disk?

3. This is for the server, right?

My original code is for generating time stamps on the server.  The SQLite custom function can be called/used from within SQL statements.  This allows for transaction processing using TRIGGERs.  For example, when modifying data in a table (insert, update, delete), a trigger could log the change made and server time in another (transaction) table.  The use of a TRIGGER with the custom function ensures that the logging is atomic with the change.