From 697927a0d073932438e0c4645ff356c2c15e7b1d Mon Sep 17 00:00:00 2001
From: "Jean-Marc Pigeon (Delson)" <jmp@safe.ca>
Date: Fri, 11 Jul 2025 11:04:07 -0400
Subject: [PATCH] Starting to implement database lock

---
 lib/devsql.c     | 28 ++++++++++++++++++++++++++++
 lib/devsql.h     |  6 ++++++
 lib/gessql.c     | 33 ++++++++++++++++++++++++++++++---
 sql/mailleur.sql |  2 +-
 4 files changed, 65 insertions(+), 4 deletions(-)

diff --git a/lib/devsql.c b/lib/devsql.c
index c996190..d0a4189 100644
--- a/lib/devsql.c
+++ b/lib/devsql.c
@@ -245,6 +245,34 @@ return (SQLPTR *)sql;
 */
 /********************************************************/
 /*                                                      */
+/*      Procedure to get an exclusive access to a       */
+/*      specific database table.                        */
+/*                                                      */
+/********************************************************/
+PUBLIC int sql_lock(SQLPTR *sqlptr,char *tablename)
+
+{
+return 1;
+}
+/*
+
+*/
+/********************************************************/
+/*                                                      */
+/*      Procedure to release an exclusive access to a   */
+/*      specific database table.                        */
+/*                                                      */
+/********************************************************/
+PUBLIC int sql_unlock(SQLPTR *sqlptr,_Bool commit)
+
+{
+return 1;
+}
+/*
+
+*/
+/********************************************************/
+/*                                                      */
 /*      Procedure to make sure a string to be use to    */
 /*      access database is clean.                       */
 /*      (no character to be misunderstood by database)  */
diff --git a/lib/devsql.h b/lib/devsql.h
index 62d3a04..fd04765 100644
--- a/lib/devsql.h
+++ b/lib/devsql.h
@@ -22,6 +22,12 @@ extern SQLPTR *sql_opensql();
 //procedure to close an previously opened SQL channel
 extern SQLPTR *sql_closesql(SQLPTR *sqlptr);
 
+//procedure to LOCK access to a database table
+extern int sql_lock(SQLPTR *sqlptr,char *tablename);
+
+//procedure to UNLOCK access to a database table
+extern int sql_unlock(SQLPTR *sqlptr,_Bool commit);
+
 //procedure to make sure a string is acceptable
 //as key search by database
 extern char *sql_gooddata(SQLPTR *sqlptr,char *key);
diff --git a/lib/gessql.c b/lib/gessql.c
index b0f8448..fb376fa 100644
--- a/lib/gessql.c
+++ b/lib/gessql.c
@@ -468,7 +468,8 @@ PUBLIC _Bool sql_newconnect(SQLPTR *sqlptr,char *rmtip,int delta)
 {
 #define OPEP    "gessql.c:sql_newconnect,"
 
-static const char *updfmt="UPDATE %s SET links=links+%d,lastupdate=now() WHERE remoteip='%s'";
+static const char *upd="UPDATE %s SET links=links+%d,lastupdate=now() WHERE remoteip='%s'";
+static const char *ins="INSERT INTO %s (remoteip) values('%s')";
 
 _Bool done;
 int phase;
@@ -480,8 +481,34 @@ proceed=true;
 while (proceed==true) {
   switch (phase) {
     case 0      :       //updating remotes record with links
-      if (sql_request(sqlptr,updfmt,RMTTBL,delta,rmtip)==1)
-        phase=999;
+      if (sql_request(sqlptr,upd,RMTTBL,delta,rmtip)==1) {
+        phase=999;      //done no probleme
+        }
+      break; 
+    case 1      :       //record does not exist
+      done=false;
+      if (sql_lock(sqlptr,RMTTBL)<0) {
+        (void) rou_alert(0,"%s Unable to dbd_lockhard %s table",OPEP,RMTTBL);
+        phase=999;      //Unable to lock table
+        }      
+      break; 
+    case 2      :       //was record created in the meantime?
+      done=true;
+      if (sql_request(sqlptr,upd,RMTTBL,delta,rmtip)==1) {
+        phase++;        //yes, no need to create it again
+        }
+      break; 
+    case 3      :       //Let create a record
+      if (sql_request(sqlptr,ins,RMTTBL,rmtip)!=1) {
+        (void) rou_alert(0,"%s Unable to create entry "
+                           "for remote ip <%s> in table <%s> (DB trouble?)",
+                            OPEP,rmtip,RMTTBL);
+        done=false;
+        phase=999;      //Unable to lock table
+        }
+      break; 
+    case 4      :       //unlock database
+      (void) sql_unlock(sqlptr,true);
       break; 
     default     :       //SAFE Guard
       proceed=false;
diff --git a/sql/mailleur.sql b/sql/mailleur.sql
index d1c7df6..41158f1 100644
--- a/sql/mailleur.sql
+++ b/sql/mailleur.sql
@@ -103,7 +103,7 @@ CREATE TABLE remotes	(
 			DFLT NOW(),
 	remoteip	TEXTUNIQUE,	//remote IP number
 	links		INTEGER
-			DFLT 0,		//how many time the remote connected
+			DFLT 1,		//how many time the remote connected
 	credit		INTEGER	//Remote IP current credit (-100..+100)
 			DFLT -100,
 	status		TEXT		//'toscan','inscan','valide'
-- 
2.47.3