Using ref_system_id fields without ticks causes incompatibility with MariaDB

[Acetylator]

As already noticed in issue #132 ref_system_id field is now a system column in MariaDB. Therefore it should be used with backticks. My issue is an addition to #132 - not only code should be corrected, but also /schemas/create_mysql. Until this is corrected, Barnyard2 is UNUSABLE with MariaDB, which is one of the leading database servers today. Please consider fixing it.

[eriroley]

--- spo_database_cache.h 2017-02-06 15:18:47.481447620 -0500 +++ spo_database_cache.h.orig 2017-02-06 15:17:32.246083674 -0500 @@ -117,9 +117,9 @@

define SQL_INSERT_SPECIFIC_REFERENCE_SYSTEM "INSERT INTO reference_system (ref_system_name) VALUES ('%s');"

-#define SQL_SELECT_SPECIFIC_REFERENCE_SYSTEM "SELECT ref_system_id FROM reference_system WHERE ref_system_name = '%s';" -#define SQL_INSERT_SPECIFIC_REF "INSERT INTO reference (ref_system_id,ref_tag) VALUES ('%u','%s');" -#define SQL_SELECT_SPECIFIC_REF "SELECT ref_id FROM reference WHERE ref_system_id = '%u' AND ref_tag = '%s';" +#define SQL_SELECT_SPECIFIC_REFERENCE_SYSTEM "SELECT ref_system_id FROM reference_system WHERE ref_system_name = '%s';" +#define SQL_INSERT_SPECIFIC_REF "INSERT INTO reference (ref_system_id,ref_tag) VALUES ('%u','%s');" +#define SQL_SELECT_SPECIFIC_REF "SELECT ref_id FROM reference WHERE ref_system_id = '%u' AND ref_tag = '%s';"

define SQL_INSERT_CLASSIFICATION "INSERT INTO sig_class (sig_class_name) VALUES ('%s');"

define SQL_SELECT_SPECIFIC_CLASSIFICATION "SELECT sig_class_id FROM sig_class WHERE sig_class_name = '%s';"

define SQL_INSERT_SIGNATURE "INSERT INTO signature (sig_sid, sig_gid, sig_rev, sig_class_id, sig_priority, sig_name) VALUES ('%u','%u','%u','%u','%u','%s');"

@@ -145,8 +145,8 @@

define SQL_SELECT_ALL_SIGREF "SELECT ref_id, sig_id, ref_seq FROM sig_reference ORDER BY sig_id,ref_seq;"

define SQL_INSERT_SIGREF "INSERT INTO sig_reference (ref_id,sig_id,ref_seq) VALUES ('%u','%u','%u');"

define SQL_SELECT_SPECIFIC_SIGREF "SELECT ref_id FROM sig_reference WHERE (ref_id = '%u') AND (sig_id = '%u') AND (ref_seq='%u');"

-#define SQL_SELECT_ALL_REFERENCE_SYSTEM "SELECT ref_system_id, ref_system_name FROM reference_system;" -#define SQL_SELECT_ALL_REF "SELECT ref_id, ref_system_id, ref_tag FROM reference; " +#define SQL_SELECT_ALL_REFERENCE_SYSTEM "SELECT ref_system_id, ref_system_name FROM reference_system;" +#define SQL_SELECT_ALL_REF "SELECT ref_id, ref_system_id, ref_tag FROM reference; "

define SQL_SELECT_ALL_CLASSIFICATION "SELECT sig_class_id, sig_class_name FROM sig_class ORDER BY sig_class_id ASC; "

define SQL_SELECT_ALL_SIGNATURE "SELECT sig_id, sig_sid, sig_gid,sig_rev, sig_class_id, sig_priority, sig_name FROM signature;"

define SQL_UPDATE_SPECIFIC_SIGNATURE "UPDATE signature SET " \

spi_database_cache.h.diff.zip

[NathanGibbs3]

I think the merging of PR #186 fixed this on Apr 12, 2016