2

I'm developing a system in C that commits updates into a MySQL database. The client isn't always online and therefore the application will save the SQL commands that would be executed into a *.sql when the server is inaccessible.

I'm thinking of adding a BOOLEAN field named late_commit to the tables used so I’ll know that those were inserted into the database later when the connection was restored.

I could alter the programming logic within the program to include the late_commit field in the insert queries but I’d rather have it with a default value of false and somehow have it set to true only when the .sql file is be executed.

I thought of intercalating the inserts with alter statements, but this seems a bit clumsy and will offer poor performance.

I've never used triggers but from what I see in this SO question they could work. They seem, however, not to be temporary or local to the session, which would interfere with the concurrent inserts from other clients.

Do you have any idea on how you did/would do this? Not necessarily the query(ies) to use, but the technology/approach that would apply the best.

EDIT: I think that a solution, if no other comes up, could be the creation of a temporary table with the same structure and a late_commit default to true, insert the data into it, then copy into the main table.

NOTICE: I've added an answer with some approaches that I’ve found. I’m still looking for the permanent solution though. So please if you know how to do it better please comment or answer. thank you!

Community
  • 1
  • 1
Bruno Flávio
  • 778
  • 10
  • 27
  • Can you use prepared statements? It's highly recommended for multiple inserts and you can set the value of the new field to `true`. – Dor Aug 09 '11 at 11:20
  • @Dor i can use prepared statements, always do in java but i was a bit overwhelmed by all the bind statements that it takes on the c api. But bear in mind that if i do prepare the statement i won't still be able to execute it, because at that moment there is a network failure, maybe it is possible to extract the prepared statement without executing it though, i'll check the api. - This, the fact that the client can't connect to the server, is why i thought of saving the statements into a sql file, that the system will later execute, once the connection is restored. – Bruno Flávio Aug 09 '11 at 13:33

2 Answers2

1

I would make the default false for late_commit and have all normal code ignore its presence. I would then have the code that writes the SQL to file go through a "decorator" that injects the late_commit stuff, eg normal SQL:

insert into table1 (col1, col2) values (val1, val2);

But when written to file:

insert into table1 (late_commit, col1, col2) values (true, val1, val2);

That way only one piece of code needs to know about it. The SQL parsing to work out where to put the extra bits is fairly straightforward.

Bohemian
  • 412,405
  • 93
  • 575
  • 722
0

I thought i'd leave here what i found so far in order to answer the question.

As stated in the question i'm looking preferably to a sql only approach, that avoids using injectors. Prepared statements won't work as they require a connection to the database that might not exist in the first place.

IFNULL() SOLUTION:

I'm inclined to use a solution I came up with using the IFNULL mysql statement which by setting a local variable would allow me to configure the late_commit column using the same query while doing it live (late_commit = false) or when doing it later (late_commit = true).

so using the query:

INSERT INTO `tmp`.`new_table` (`a`,`b`,`late_commit`)    
VALUES ('abc','def', IFNULL(@LATECOMMIT, FALSE) );

I can insert the values with the late_commit column set to false, making use of the IFNULL statement: because the session variable @latecommit is not defined the code will configure the column to false.

on the other hand, if we are actually doing an offline commit, we just need to preceed all the inserts with: 

SET @LATECOMMIT = TRUE;

and then proceed with all the necessary inserts, which in my case include several different tables, but in all of them there is a late_commit field that is set to IFNULL(@LATECOMMIT, FALSE):

INSERT INTO `tmp`.`new_table` (`a`,`b`,`latecommit`)
VALUES ('abc','def', IFNULL(@LATECOMMIT, FALSE) );

I like this solution because the variable only set for the current session, and its quite easy to implement (eg. you just prepend your .sql file with the SET instruction and proceed executing it).

TIMEDIFF() or Timestamp subtraction SOLUTION:

When discussing this in the chat, @TehShrike also provided me with a sql only solution, my making use of the difference between the time when the query was first generated and the time when it was inserted.

This could be done because my tables are actually being inserted with a clientdate timestamp variable, which is the local unix timestamp.

So for this solution all that would be needed is to determine what would be considered a late_commit (eg. 60 seconds, one hour, ..) and then make our inserts like this:

INSERT INTO `tmp`.`new_table` (`a`,`b`,`clientdate`,`latecommit`)
VALUES ('abc','def', FROM_UNIXTIME(1313489338),
 IF( CURRENT_TIMESTAMP() - clientdate > 3600, TRUE, FALSE) );

In this insert we consider a insert that was generated more than an hour ago (3600 seconds) to be a late_commit.

If you are not using timestamps you could also use datetime fields in which case you probably would use the DATEDIFF() or TIMEDIFF() functions.

Bruno Flávio
  • 778
  • 10
  • 27