search

flashmob / go-guerrilla

Mini SMTP server written in golang
MIT License
2.79k stars 366 forks source link

INSERT SQL is MySQL specific #145

Closed lllama closed 5 years ago

lllama commented 5 years ago

The INSERT statement in the SQL backend is specific. In particular, changing the backend to postgres results in SQL error.

The two main changes to make are:

  1. backticks need to be replaced with double quotes
  2. ? placeholders need to be replaced with $x, where x increments.

I'm not sure whether it's possible to replace the SQL with something more cross-platform or whether there is the need for DB specific backends.

flashmob commented 5 years ago

Thanks for the info, didn't know about the backticks and placeholders. You're welcome to attempt to change it to a more cross platform query, then post it here, so it can be tested with MySQL. Also would welcome to suggest any other solutions, eg. recommend an ORM or something similar that's more cross platform.

flashmob commented 5 years ago

Another idea would be to refactor prepareInsertQuery to allow the SQL query to be configurable via the config?

lllama commented 5 years ago

So I have something working in my backend which takes the SQL from the config. I'm marking it as omitempty, as I think that would allow a fallback to the MySQL statement. The current implementation requires sql_insert and sql_values config values as there is a requirement for multiple row inserts. I don't think this is a major issue as it allows for some additional customisation of the SQL but does require two variables to be configured, which has the potential to be confusing.

I'll look to get a pull request done asap.

flashmob commented 5 years ago

Thanks for the update, sounds good.

lllama commented 5 years ago

The SQL insert statement can now be changed in the config thanks to #157, so I'll close this.

lord-alfred commented 5 years ago

In wiki page: https://github.com/flashmob/go-guerrilla/wiki/Configuration-example:-save-to-Redis-&-MySQL

There are incorrect variables for config. Need sql_insert and sql_values, not SQLInsert and SQLValues

flashmob commented 5 years ago

Thanks. Corrected!

On Fri, 2 Aug 2019 at 23:01, Lord Alfred notifications@github.com wrote:

In wiki page: https://github.com/flashmob/go-guerrilla/wiki/Configuration-example:-save-to-Redis-&-MySQL

There are incorrect variables for config. Need sql_insert and sql_values, not SQLInsert and SQLValues

— You are receiving this because you commented. Reply to this email directly, view it on GitHub https://github.com/flashmob/go-guerrilla/issues/145?email_source=notifications&email_token=AAE6MP3C5X7QNPMCSTNP6C3QCQV33A5CNFSM4HDUGCB2YY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGOD3NVRII#issuecomment-517691553, or mute the thread https://github.com/notifications/unsubscribe-auth/AAE6MP4GRLS2IEONATURCMDQCQV33ANCNFSM4HDUGCBQ .

lllama commented 5 years ago

You can now define your own SQL statement in the config. Have a look at the wiki page or pull request linked above