lib/sql: make the TruncateTable run with cascade and restart identity

On table that contains foreign key, truncate without cascade may cause
the method fail.

Also, since TruncateTable is, and should be only, used on testing, any
identity columns, for example serial, should be reset back to its initial
value. On PostgreSQL this means the truncate table is with
"RESTART IDENTITY".

1 files changed, 13 insertions, 2 deletions

diff --git a/lib/sql/client.go b/lib/sql/client.go
index d0af4482..d4c22abe 100644
--- a/lib/sql/client.go
+++ b/lib/sql/client.go
@@ -300,13 +300,24 @@ func loadSQL(fs http.FileSystem, fi os.FileInfo, filename string) (
 }
 
 //
-// TruncateTable truncate all data on table `tableName`.
+// TruncateTable truncate all data on table `tableName` with cascade option.
+// On PostgreSQL, any identity columns (for example, serial) will be reset
+// back to its initial value.
 //
 func (cl *Client) TruncateTable(tableName string) (err error) {
-	q := `TRUNCATE TABLE ` + tableName
+	q := fmt.Sprintf(`TRUNCATE TABLE %s %s CASCADE;`, tableName,
+		cl.truncateWithRestartIdentity())
+
 	_, err = cl.DB.Exec(q)
 	if err != nil {
 		return fmt.Errorf("TruncateTable %q: %s", tableName, err)
 	}
 	return nil
 }
+
+func (cl *Client) truncateWithRestartIdentity() string {
+	if cl.DriverName == DriverNamePostgres {
+		return " RESTART IDENTITY "
+	}
+	return ""
+}