MySQL++

Best practice to work with auto incremented columns and SSQLS
Login

Best practice to work with auto incremented columns and SSQLS

(1) By Angelo (aacirino) on 2020-03-02 18:09:10 [link] [source]

I have a unique ID that identifies a row. If I include the ID column in my SSQLS I must know in advance, before inserting the row into the DB, what was the last auto incremented ID. If I leave the ID out of the SSQLS declaration insert(row) works as intended, inserting a new row and letting MySQL do the auto increment. But then if I need to use replace(row) the ID column is absent from the generated SQL clause and I have a useless SQL clause.

I expect to use a SSQLS class and change the columns in-place, like row.phone = new_phone and then use replace(row). I can not use update(original_row, row) because there will be no original_row. For instance, in the destructor of my controller class I would like to automatically update the modified rows that I keep in a std::unordered_map<int, std::pair<bool, row>>, where the int value is the auto-incremented ID and the bool value indicates if the row was updated. I know that I can construct a custom query, but then what is the point of having API functions like replace if I need to duplicate it in my code?

What are the recommendations?

(2) By Warren Young (tangent) on 2020-03-02 19:13:51 [link] [source] in reply to 1

(3) By Angelo (aacirino) on 2020-03-02 19:33:24 [link] [source] in reply to 2

It only works after the row is inserted. Either I need it beforehand as the last inserted ID to insert a new row with the ID column as a property in the SSQLS or I need it to be accessible to be included in the replace() function when the ID column is excluded from the SSQLS. I am aware of the result.insert_id() use but it is not helping me. My issue is that with SSQLS classes we either let MySQL deal with the auto-incremented columns by leaving them out of the SSQLS class definition or we must deal with them manually if we include them in the SSQLS class definition. And dealing with the auto-increment by hand has been a tough task so far.

(4) By Warren Young (tangent) on 2020-03-02 19:54:52 [link] [source] in reply to 3

I suspect you aren't using the COMPCOUNT parameter in the SSQLS correctly. SSQLS gives you an object constructor without the key fields, knowing they're auto-generated.

You should also study examples/ssqls3.cpp which does a related update. The stock.id field is populated by the SELECT query above, but you could set it via Query.insert_id() instead before calling replace().

(5) By Angelo (aacirino) on 2020-03-02 21:04:54 [link] [source] in reply to 4

I had to put the id as the last property in the SSQLS class declaration with a COMPCOUNT as minus one of the total field count:

sql_create_9(investor, 1, 8,
  mysqlpp::sql_char, name,
  mysqlpp::sql_char, phone,
  mysqlpp::sql_char, email,
  mysqlpp::sql_double, funds,
  mysqlpp::sql_double, risk,
  mysqlpp::sql_double, stop_loss,
  mysqlpp::sql_int, leverage,
  mysqlpp::sql_double, initial_funds,
  mysqlpp::sql_int, id
)

Now the insert and replace API functions work as I need. But this isn't optimal, since the id field is the natural way to compare two objects.

(6) By Angelo (aacirino) on 2020-03-02 21:27:58 [link] [source] in reply to 5

Unfortunately this isn't a solution. Since the id field now is not initialised it is undefined behavior and there can be duplicate ids. And also it creates a mess with the auto increment values kept by MySQL, it isn't a sequence anymore.

(7) By Warren Young (tangent) on 2020-03-02 21:52:55 [link] [source] in reply to 6

...and if you look at examples/ssqls2.cpp, you will see it insert a record with an ID field at the front without needing to know it first.

Please apply the principle of charity to the library's design before you criticize it. It's 22 years old now, it's in dozens of package repositories, and yet this forum is not overrun with support requests. Does this not suggest that the library addresses common needs very well without needing programmer acrobatics?

Read the manual.

Read the examples.

(9) By Angelo (aacirino) on 2020-03-02 22:17:12 [link] [source] in reply to 7

I am sorry but I didn't mean to criticize your effort or the library. I did read the manual and the examples and the ssqls2.cpp example doesn't have an auto-increment id from its declaration here 5.1. sql_create. I have been trying really hard to understand and use MySQL++ for the last week. I've devoted a lot of time reading the documentation and the source files to understand it.

(13) By Warren Young (tangent) on 2020-03-03 00:09:14 and edited on 2020-03-03 00:57:42 [link] [source] in reply to 9

the ssqls2.cpp example doesn't have an auto-increment id

Yes, I forgot, it uses a string item value, but it's easy to add it. Making it an auto-increment integer changes the code only slightly.

Here's a trivial patch to the examples to add an auto-increment field:

Index: examples/resetdb.cpp
==================================================================
--- examples/resetdb.cpp
+++ examples/resetdb.cpp
@@ -27,10 +27,11 @@
  USA
 ***********************************************************************/
 
 #include "cmdline.h"
 #include "printdata.h"
+#include "stock.h"
 
 #include <mysql++.h>
 
 #include <iostream>
 #include <cstdio>
@@ -132,16 +133,20 @@
 		// Send the query to create the stock table and execute it.
 		cout << "Creating stock table..." << endl;
 		mysqlpp::Query query = con.query();
 		query << 
 				"CREATE TABLE stock (" <<
+				"  id INT AUTO_INCREMENT, " <<
 				"  item CHAR(30) NOT NULL, " <<
 				"  num BIGINT NOT NULL, " <<
 				"  weight DOUBLE NOT NULL, " <<
 				"  price DECIMAL(6,2) NULL, " << // NaN & inf. == NULL
 				"  sdate DATE NOT NULL, " <<
-				"  description MEDIUMTEXT NULL) " <<
+				"  description MEDIUMTEXT NULL " <<
+				"  description MEDIUMTEXT NULL, " <<
+				"  PRIMARY KEY(id)" <<
+				")"
 				"ENGINE = InnoDB" <<
 				" CHARACTER SET " MYSQLPP_UTF8_CS
                 " COLLATE " MYSQLPP_UTF8_COL;
 		query.execute();
 
@@ -160,15 +165,20 @@
 		// so the default value of "stock" is used.  Also notice that
 		// the first row is a UTF-8 encoded Unicode string!  All you
 		// have to do to store Unicode data in recent versions of MySQL
 		// is use UTF-8 encoding.
 		cout << "Populating stock table..." << flush;
-		query.execute("N├╝rnberger Brats", 97, 1.5, 8.79, "2005-03-10");
-		query.execute("Pickle Relish", 87, 1.5, 1.75, "1998-09-04");
-		query.execute("Hot Mustard", 73, .95, .97, "1998-05-25",
-				"good American yellow mustard, not that European stuff");
-		query.execute("Hotdog Buns", 65, 1.1, 1.1, "1998-04-23");
+		query.insert(stock(0, "N├╝rnberger Brats", 97, 1.5, 8.79,
+				mysqlpp::Date("2005-03-10"), mysqlpp::null)).execute();
+		query.insert(stock(0, "Pickle Relish", 87, 1.5, 1.75,
+				mysqlpp::Date("1998-09-04"), mysqlpp::null)).execute();
+		query.insert(stock(0, "Hot Mustard", 73, .95, .97,
+				mysqlpp::Date("1998-05-25"), mysqlpp::sql_mediumtext_null(
+				"good American yellow mustard, not that European stuff"))).
+				execute();
+		query.insert(stock(0, "Hotdog Buns", 65, 1.1, 1.1,
+				mysqlpp::Date("1998-04-23"), mysqlpp::null)).execute();
 
 		// Test that above did what we wanted.
 		cout << "inserted " << con.count_rows("stock") << " rows." << endl;
 
 		// Now create empty images table, for testing BLOB and auto-

Index: examples/ssqls2.cpp
==================================================================
--- examples/ssqls2.cpp
+++ examples/ssqls2.cpp
@@ -49,12 +49,12 @@
 				cmdline.server(), cmdline.user(), cmdline.pass());
 
 		// Create and populate a stock object.  We could also have used
 		// the set() member, which takes the same parameters as this
 		// constructor.
-		stock row("Hot Dogs", 100, 1.5,
-				numeric_limits<double>::infinity(),	// "priceless," ha!
+		stock row(0, "Hot Dogs", 100, 1.5,
+				numeric_limits<double>::infinity(), // "priceless," ha!
 				mysqlpp::sql_date("1998-09-25"), mysqlpp::null);
 
 		// Form the query to insert the row into the stock table.
 		mysqlpp::Query query = con.query();
 		query.insert(row);

Index: examples/ssqls4.cpp
==================================================================
--- examples/ssqls4.cpp
+++ examples/ssqls4.cpp
@@ -66,16 +66,16 @@
 		for (it = res.begin(); it != res.end(); ++it) {
 			print_stock_row(it->item.c_str(), it->num, it->weight,
 					it->price, it->sDate);
 		}
 
-		// Use set's find method to look up a stock item by item name.
+		// Use set's find method to look up a stock item by item ID.
 		// This also uses the SSQLS comparison setup.
-		it = res.find(stock("Hotdog Buns"));
+		it = res.find(stock(1));
 		if (it != res.end()) {
 			cout << endl << "Currently " << it->num <<
-					" hotdog buns in stock." << endl;
+					" items with ID 1 in stock." << endl;
 		}
 		else {
 			cout << endl << "Sorry, no hotdog buns in stock." << endl;
 		}
 	}

Index: examples/ssqls6.cpp
==================================================================
--- examples/ssqls6.cpp
+++ examples/ssqls6.cpp
@@ -64,11 +64,11 @@
 
 	string line;
 	vector<mysqlpp::String> strings;
 	while (getline(input, line)) {
 		if (tokenize_line(line, strings) == 6) {
-			stock_vector.push_back(stock(string(strings[0]), strings[1],
+			stock_vector.push_back(stock(0, string(strings[0]), strings[1],
 					strings[2], strings[3], strings[4], strings[5]));
 		}
 		else {
 			cerr << "Error parsing input line (doesn't have 6 fields) " << 
 					"in file '" << filename << "'" << endl;

Index: examples/stock.h
==================================================================
--- examples/stock.h
+++ examples/stock.h
@@ -34,14 +34,15 @@
 //   ...
 //   sql_mediumtext_null description;
 //
 // plus methods to help populate the class from a MySQL row.  See the
 // SSQLS sections in the user manual for further details.
-sql_create_6(stock,
-	1, 6, // The meaning of these values is covered in the user manual
+sql_create_7(stock,
+	1, 7, // The meaning of these values is covered in the user manual
+	mysqlpp::sql_int, id,
 	mysqlpp::sql_char, item,
 	mysqlpp::sql_bigint, num,
 	mysqlpp::sql_double, weight,
 	mysqlpp::sql_double_null, price,
 	mysqlpp::sql_date, sDate,			// SSQLS isn't case-sensitive!
 	mysqlpp::sql_mediumtext_null, description)
 

Index: examples/transaction.cpp
==================================================================
--- examples/transaction.cpp
+++ examples/transaction.cpp
@@ -63,11 +63,11 @@
 			// commit this one.
 			mysqlpp::Transaction trans(con,
 					mysqlpp::Transaction::serializable,
 					mysqlpp::Transaction::session);
 
-			stock row("Sauerkraut", 42, 1.2, 0.75,
+			stock row(0, "Sauerkraut", 42, 1.2, 0.75,
 					mysqlpp::sql_date("2006-03-06"), mysqlpp::null);
 			query.insert(row);
 			query.execute();
 
 			cout << "\nRow inserted, but not committed." << endl;
@@ -85,11 +85,11 @@
 			// Start a new transaction, keeping the same isolation level
 			// we set above, since it was set to affect the session.
 			mysqlpp::Transaction trans(con);
 			cout << "\nNow adding catsup to the database..." << endl;
 
-			stock row("Catsup", 3, 3.9, 2.99,
+			stock row(0, "Catsup", 3, 3.9, 2.99,
 					mysqlpp::sql_date("2006-03-06"), mysqlpp::null);
 			query.insert(row);
 			query.execute();
 		}
 		cout << "\nNo, yuck! We don't like catsup. Rolling it back:" <<

Basically, it just passes 0 for the ID field on insert, knowing it will be overridden by MySQL. With this change, the examples work just fine here with an auto-increment ID column at the front of the SSQLS.

EDIT: If you want SSQLS to work better, maybe you'd be interested in putting some effort behind the SSQLSv2 project. SSQLSv1 is certainly far from perfect; we can make it better, but someone has to care enough about it to make that happen.

(18) By Angelo (aacirino) on 2020-03-03 01:53:41 [link] [source] in reply to 13

Tomorrow I will start to take a look at this. After I come back from the mechanical workshop where I will leave my car I will work on this.

(20) By Angelo (aacirino) on 2020-03-05 02:03:26 [source] in reply to 13

Hi, I cloned the fossil repo and installed the missing tools like bakefile but I was not able to build the library. There are too many errors with the llvm include files and maybe an error in the generated files that seem to mix a shell script with a header file or a source file. See this error for instance:

/Users/aacirino/git/mysqlpp/aacirino/bk-deps ccache /usr/local/opt/llvm/bin/clang++ -c -o mysqlpp_beemutex.o -I. -I/usr/local/include/mysql -dynamic -fPIC -DPIC -I/usr/local/opt/llvm/include -I/usr/local/opt/llvm/include/c++/v1 -I/usr/local/opt/curl/include -I/usr/local/opt/libarchive/include -g -O2 -fno-common ./lib/beemutex.cpp
In file included from ./lib/beemutex.cpp:31:
In file included from ./lib/beemutex.h:48:
In file included from ./lib/exceptions.h:34:
In file included from ./lib/options.h:37:
In file included from ./lib/common.h:231:
In file included from /usr/local/include/mysql/mysql.h:83:
In file included from /usr/local/include/mysql/mysql/client_plugin.h:38:
In file included from /usr/local/opt/llvm/include/c++/v1/stdlib.h:100:
In file included from /usr/local/opt/llvm/include/c++/v1/math.h:311:
In file included from /usr/local/opt/llvm/include/c++/v1/type_traits:417:
In file included from /usr/local/opt/llvm/include/c++/v1/cstddef:37:
./version:1:2: error: invalid preprocessing directive
#!/bin/sh
 ^
./version:2:1: error: unknown type name 'echo'
echo 3.2.5
^
./version:2:6: error: expected unqualified-id
echo 3.2.5

I am not sure about what to do to build MySQL++ in my installation.

(21) By Warren Young (tangent) on 2020-03-05 16:15:51 [link] [source] in reply to 20

Read HACKERS.md.

(22) By Angelo (aacirino) on 2020-03-05 21:16:28 [link] [source] in reply to 21

I've read and followed it ipsis litris. There are differences from its version to the macOS version that I am using and I managed to circumvent them. But I can't see from where do those errors come.

Which install-sh do you use? The bootstrap created a symbolic link to one that doesn't exist and I substituted it for a symlink to libtool's install-sh, that for me was the natural choice.

(10) By Angelo (aacirino) on 2020-03-02 22:45:11 [link] [source] in reply to 7

Maybe my installation with brew is screwed, please take a look at this simple example and help me figure out why the value_list output isn't correctly enclosed.

#include <iostream>

#include <mysql++/mysql++.h>
#include <mysql++/ssqls.h>

sql_create_2(test, 1, 2,
  mysqlpp::sql_int, id,
  mysqlpp::sql_char, name
)

int main() {
  auto t = test{1, "Testing Test"};
  std::cout << t.field_list() << '\n';
  std::cout << t.value_list() << '\n';
}

with this output:

`id`,`name`
1,Testing Test

(12) By Warren Young (tangent) on 2020-03-03 00:18:19 [link] [source] in reply to 10

help me figure out why the value_list output isn't correctly enclosed.

It's covered here in the manual.

Here's a trivial diff to the examples showing the behavior, which is correct, as-defined:

Index: examples/ssqls2.cpp
==================================================================
--- examples/ssqls2.cpp
+++ examples/ssqls2.cpp
@@ -52,10 +52,14 @@
 		// the set() member, which takes the same parameters as this
 		// constructor.
 		stock row("Hot Dogs", 100, 1.5,
 				numeric_limits<double>::infinity(),	// "priceless," ha!
 				mysqlpp::sql_date("1998-09-25"), mysqlpp::null);
+		mysqlpp::SQLStream ss(&con);
+		ss << row.value_list();
+		cout << "SQL VALUES: " << ss.str() << endl;
+		cout << "I/O VALUES: " << row.value_list() << endl;
 
 		// Form the query to insert the row into the stock table.
 		mysqlpp::Query query = con.query();
 		query.insert(row);

It works that way because proper quoting and escaping requires knowledge of the way the remote DBMS is configured, which is in turn why you must pass a Connection object to the SQLStream. Without it, you'd have to go with default guesses, which may be wrong for your particular DBMS.

(19) By Angelo (aacirino) on 2020-03-03 01:45:26 and edited on 2020-03-03 02:12:00 [link] [source] in reply to 12

It is clear now. My problem is that I assumed the above use of SQLStream was the default implementation of value_list. I am in a hurry and I didn't check value_list source code. I only looked at replace at query.h where value_list is used the way I tried to use it:

MYSQLPP_QUERY_THISPTR << std::setprecision(16) <<
  "REPLACE INTO `" << v.table() << "` (" <<
  v.field_list() << ") VALUES (" << v.value_list() << ')';

That's why I thought this behavior would be the default. Again, I didn't pay enough attention to what MYSQLPP_QUERY_THISPTR is.

(8) By Angelo (aacirino) on 2020-03-02 22:07:38 [link] [source] in reply to 4

This is how I coded it to have the correct auto-incremented ID set up:

 auto query = conn_.query();
  auto inv = investor{0,    name,      phone,    email,        funds,
                      risk, stop_loss, leverage, initial_funds};
  query.insert(inv);
  if (not query.exec()) {
    std::cerr << "Error adding object to database\n";
    std::cerr << query.error() << "\n";
    return -1;
  }
  auto old_inv = inv;
  inv.id = query.insert_id();
  query.update(old_inv, inv);
  investors_[inv.id] = inv;
  return inv.id;

Fortunately my DB is very small and the two roundtrips to the DB, due to the insert followed by the update will not have an impact on the performance. But this is very undesirable and for large production DBs unacceptable.