Merge pull request #1948 from LazyDBA247-Anyvision/master

add enableUpsert=true

7 files changed, 46 insertions, 34 deletions

diff --git a/weed/command/scaffold.go b/weed/command/scaffold.go
index 337adaa22..7df69e211 100644
--- a/weed/command/scaffold.go
+++ b/weed/command/scaffold.go
@@ -120,10 +120,9 @@ connection_max_idle = 2
 connection_max_open = 100
 connection_max_lifetime_seconds = 0
 interpolateParams = false
-# Empty insertQuery will use the default insert query
-# example insertQuery can be for UPSERT syntax:
-# insertQuery = """INSERT INTO ` + "`%s`" + ` (dirhash,name,directory,meta) VALUES(?,?,?,?) ON DUPLICATE KEY UPDATE meta = VALUES(meta)"""
-insertQuery = ""
+# if insert/upsert failing, you can disable upsert or update query syntax to match your RDBMS syntax:
+upsertQuery = """INSERT INTO ` + "`%s`" + ` (dirhash,name,directory,meta) VALUES(?,?,?,?) ON DUPLICATE KEY UPDATE meta = VALUES(meta)"""
+enableUpsert = true
 
 [mysql2]  # or memsql, tidb
 enabled = false
@@ -145,10 +144,9 @@ connection_max_idle = 2
 connection_max_open = 100
 connection_max_lifetime_seconds = 0
 interpolateParams = false
-# Empty insertQuery will use the default insert query
-# example insertQuery can be for UPSERT syntax:
-# insertQuery = """INSERT INTO ` + "`%s`" + ` (dirhash,name,directory,meta) VALUES(?,?,?,?) ON DUPLICATE KEY UPDATE meta = VALUES(meta)"""
-insertQuery = ""
+# if insert/upsert failing, you can disable upsert or update query syntax to match your RDBMS syntax:
+upsertQuery = """INSERT INTO ` + "`%s`" + ` (dirhash,name,directory,meta) VALUES(?,?,?,?) ON DUPLICATE KEY UPDATE meta = VALUES(meta)"""
+enableUpsert = true
 
 [postgres] # or cockroachdb, YugabyteDB
 # CREATE TABLE IF NOT EXISTS filemeta (
@@ -169,10 +167,9 @@ sslmode = "disable"
 connection_max_idle = 100
 connection_max_open = 100
 connection_max_lifetime_seconds = 0
-# Empty insertQuery will use the default insert query
-# example insertQuery can be for UPSERT syntax:
-# insertQuery = """INSERT INTO "%[1]s" (dirhash,name,directory,meta) VALUES($1,$2,$3,$4) ON CONFLICT ON CONSTRAINT "%[1]s_pkey" DO UPDATE SET meta = EXCLUDED.meta WHERE "%[1]s".meta != EXCLUDED.meta"""
-insertQuery = ""
+# if insert/upsert failing, you can disable upsert or update query syntax to match your RDBMS syntax:
+upsertQuery = """INSERT INTO "%[1]s" (dirhash,name,directory,meta) VALUES($1,$2,$3,$4) ON CONFLICT (dirhash,name) DO UPDATE SET meta = EXCLUDED.meta WHERE "%[1]s".meta != EXCLUDED.meta"""
+enableUpsert = true
 
 [postgres2]
 enabled = false
@@ -195,10 +192,9 @@ sslmode = "disable"
 connection_max_idle = 100
 connection_max_open = 100
 connection_max_lifetime_seconds = 0
-# Empty insertQuery will use the default insert query
-# example insertQuery can be for UPSERT syntax:
-# insertQuery = """INSERT INTO "%[1]s" (dirhash,name,directory,meta) VALUES($1,$2,$3,$4) ON CONFLICT ON CONSTRAINT "%[1]s_pkey" DO UPDATE SET meta = EXCLUDED.meta WHERE "%[1]s".meta != EXCLUDED.meta"""
-insertQuery = ""
+# if insert/upsert failing, you can disable upsert or update query syntax to match your RDBMS syntax:
+upsertQuery = """INSERT INTO "%[1]s" (dirhash,name,directory,meta) VALUES($1,$2,$3,$4) ON CONFLICT ON CONSTRAINT "%[1]s_pkey" DO UPDATE SET meta = EXCLUDED.meta WHERE "%[1]s".meta != EXCLUDED.meta"""
+enableUpsert = true
 
 [cassandra]
 # CREATE TABLE filemeta (
diff --git a/weed/filer/mysql/mysql_sql_gen.go b/weed/filer/mysql/mysql_sql_gen.go
index 105f3e62a..93d3e3f9e 100644
--- a/weed/filer/mysql/mysql_sql_gen.go
+++ b/weed/filer/mysql/mysql_sql_gen.go
@@ -10,7 +10,7 @@ import (
 type SqlGenMysql struct {
 	CreateTableSqlTemplate string
 	DropTableSqlTemplate   string
-	InsertQueryTemplate    string
+	UpsertQueryTemplate    string
 }
 
 var (
@@ -18,8 +18,8 @@ var (
 )
 
 func (gen *SqlGenMysql) GetSqlInsert(tableName string) string {
-	if gen.InsertQueryTemplate != "" {
-		return fmt.Sprintf(gen.InsertQueryTemplate, tableName)
+	if gen.UpsertQueryTemplate != "" {
+		return fmt.Sprintf(gen.UpsertQueryTemplate, tableName)
 	} else {
 		return fmt.Sprintf("INSERT INTO `%s` (dirhash,name,directory,meta) VALUES(?,?,?,?)", tableName)
 	}
diff --git a/weed/filer/mysql/mysql_store.go b/weed/filer/mysql/mysql_store.go
index 4b73faacf..423b01392 100644
--- a/weed/filer/mysql/mysql_store.go
+++ b/weed/filer/mysql/mysql_store.go
@@ -30,7 +30,8 @@ func (store *MysqlStore) GetName() string {
 
 func (store *MysqlStore) Initialize(configuration util.Configuration, prefix string) (err error) {
 	return store.initialize(
-		configuration.GetString(prefix+"insertQuery"),
+		configuration.GetString(prefix+"upsertQuery"),
+		configuration.GetBool(prefix+"enableUpsert"),
 		configuration.GetString(prefix+"username"),
 		configuration.GetString(prefix+"password"),
 		configuration.GetString(prefix+"hostname"),
@@ -43,14 +44,17 @@ func (store *MysqlStore) Initialize(configuration util.Configuration, prefix str
 	)
 }
 
-func (store *MysqlStore) initialize(insertQuery, user, password, hostname string, port int, database string, maxIdle, maxOpen,
+func (store *MysqlStore) initialize(upsertQuery string, enableUpsert bool, user, password, hostname string, port int, database string, maxIdle, maxOpen,
 	maxLifetimeSeconds int, interpolateParams bool) (err error) {
 
 	store.SupportBucketTable = false
+	if !enableUpsert {
+	    upsertQuery = ""
+	}
 	store.SqlGenerator = &SqlGenMysql{
 		CreateTableSqlTemplate: "",
 		DropTableSqlTemplate:   "drop table `%s`",
-		InsertQueryTemplate:    insertQuery,
+		UpsertQueryTemplate:    upsertQuery,
 	}
 
 	sqlUrl := fmt.Sprintf(CONNECTION_URL_PATTERN, user, password, hostname, port, database)
diff --git a/weed/filer/mysql2/mysql2_store.go b/weed/filer/mysql2/mysql2_store.go
index 3c51f9363..071352111 100644
--- a/weed/filer/mysql2/mysql2_store.go
+++ b/weed/filer/mysql2/mysql2_store.go
@@ -32,7 +32,8 @@ func (store *MysqlStore2) GetName() string {
 func (store *MysqlStore2) Initialize(configuration util.Configuration, prefix string) (err error) {
 	return store.initialize(
 		configuration.GetString(prefix+"createTable"),
-		configuration.GetString(prefix+"insertQuery"),
+		configuration.GetString(prefix+"upsertQuery"),
+		configuration.GetBool(prefix+"enableUpsert"),
 		configuration.GetString(prefix+"username"),
 		configuration.GetString(prefix+"password"),
 		configuration.GetString(prefix+"hostname"),
@@ -45,14 +46,17 @@ func (store *MysqlStore2) Initialize(configuration util.Configuration, prefix st
 	)
 }
 
-func (store *MysqlStore2) initialize(createTable, insertQuery, user, password, hostname string, port int, database string, maxIdle, maxOpen,
+func (store *MysqlStore2) initialize(createTable, upsertQuery string, enableUpsert bool, user, password, hostname string, port int, database string, maxIdle, maxOpen,
 	maxLifetimeSeconds int, interpolateParams bool) (err error) {
 
 	store.SupportBucketTable = true
+	if !enableUpsert {
+	    upsertQuery = ""
+	}
 	store.SqlGenerator = &mysql.SqlGenMysql{
 		CreateTableSqlTemplate: createTable,
 		DropTableSqlTemplate:   "drop table `%s`",
-		InsertQueryTemplate:    insertQuery,
+		UpsertQueryTemplate:    upsertQuery,
 	}
 
 	sqlUrl := fmt.Sprintf(CONNECTION_URL_PATTERN, user, password, hostname, port, database)
diff --git a/weed/filer/postgres/postgres_sql_gen.go b/weed/filer/postgres/postgres_sql_gen.go
index 21a87ef5a..6cee3d2da 100644
--- a/weed/filer/postgres/postgres_sql_gen.go
+++ b/weed/filer/postgres/postgres_sql_gen.go
@@ -10,7 +10,7 @@ import (
 type SqlGenPostgres struct {
 	CreateTableSqlTemplate string
 	DropTableSqlTemplate   string
-	InsertQueryTemplate    string
+	UpsertQueryTemplate    string
 }
 
 var (
@@ -18,8 +18,8 @@ var (
 )
 
 func (gen *SqlGenPostgres) GetSqlInsert(tableName string) string {
-	if gen.InsertQueryTemplate != "" {
-		return fmt.Sprintf(gen.InsertQueryTemplate, tableName)
+	if gen.UpsertQueryTemplate != "" {
+		return fmt.Sprintf(gen.UpsertQueryTemplate, tableName)
 	} else {
 		return fmt.Sprintf(`INSERT INTO "%s" (dirhash,name,directory,meta) VALUES($1,$2,$3,$4)`, tableName)
 	}
diff --git a/weed/filer/postgres/postgres_store.go b/weed/filer/postgres/postgres_store.go
index ea9b1c71e..21b79d1fe 100644
--- a/weed/filer/postgres/postgres_store.go
+++ b/weed/filer/postgres/postgres_store.go
@@ -29,7 +29,8 @@ func (store *PostgresStore) GetName() string {
 
 func (store *PostgresStore) Initialize(configuration util.Configuration, prefix string) (err error) {
 	return store.initialize(
-		configuration.GetString(prefix+"insertQuery"),
+		configuration.GetString(prefix+"upsertQuery"),
+		configuration.GetBool(prefix+"enableUpsert"),
 		configuration.GetString(prefix+"username"),
 		configuration.GetString(prefix+"password"),
 		configuration.GetString(prefix+"hostname"),
@@ -43,13 +44,16 @@ func (store *PostgresStore) Initialize(configuration util.Configuration, prefix
 	)
 }
 
-func (store *PostgresStore) initialize(insertQuery, user, password, hostname string, port int, database, schema, sslmode string, maxIdle, maxOpen, maxLifetimeSeconds int) (err error) {
+func (store *PostgresStore) initialize(upsertQuery string, enableUpsert bool, user, password, hostname string, port int, database, schema, sslmode string, maxIdle, maxOpen, maxLifetimeSeconds int) (err error) {
 
 	store.SupportBucketTable = false
+	if !enableUpsert {
+	    upsertQuery = ""
+	}
 	store.SqlGenerator = &SqlGenPostgres{
 		CreateTableSqlTemplate: "",
 		DropTableSqlTemplate:   `drop table "%s"`,
-		InsertQueryTemplate:    insertQuery,
+		UpsertQueryTemplate:    upsertQuery,
 	}
 
 	sqlUrl := fmt.Sprintf(CONNECTION_URL_PATTERN, hostname, port, sslmode)
diff --git a/weed/filer/postgres2/postgres2_store.go b/weed/filer/postgres2/postgres2_store.go
index b83945db6..01fc4a869 100644
--- a/weed/filer/postgres2/postgres2_store.go
+++ b/weed/filer/postgres2/postgres2_store.go
@@ -32,7 +32,8 @@ func (store *PostgresStore2) GetName() string {
 func (store *PostgresStore2) Initialize(configuration util.Configuration, prefix string) (err error) {
 	return store.initialize(
 		configuration.GetString(prefix+"createTable"),
-		configuration.GetString(prefix+"insertQuery"),
+		configuration.GetString(prefix+"upsertQuery"),
+		configuration.GetBool(prefix+"enableUpsert"),
 		configuration.GetString(prefix+"username"),
 		configuration.GetString(prefix+"password"),
 		configuration.GetString(prefix+"hostname"),
@@ -46,13 +47,16 @@ func (store *PostgresStore2) Initialize(configuration util.Configuration, prefix
 	)
 }
 
-func (store *PostgresStore2) initialize(createTable, insertQuery, user, password, hostname string, port int, database, schema, sslmode string, maxIdle, maxOpen, maxLifetimeSeconds int) (err error) {
+func (store *PostgresStore2) initialize(createTable, upsertQuery string, enableUpsert bool, user, password, hostname string, port int, database, schema, sslmode string, maxIdle, maxOpen, maxLifetimeSeconds int) (err error) {
 
 	store.SupportBucketTable = true
+	if !enableUpsert {
+	    upsertQuery = ""
+	}
 	store.SqlGenerator = &postgres.SqlGenPostgres{
 		CreateTableSqlTemplate: createTable,
 		DropTableSqlTemplate:   `drop table "%s"`,
-		InsertQueryTemplate:    insertQuery,
+		UpsertQueryTemplate:    upsertQuery,
 	}
 
 	sqlUrl := fmt.Sprintf(CONNECTION_URL_PATTERN, hostname, port, sslmode)