Safely manipulate SQL database directly (bypassing redmine API) ?

Added by Marc Cramer about 1 month ago

Hi,

is there a safe way to access the underlaying SQL database and change the
database directly, thus bypassing redmine's API?

Why: I am kind of doing various mass data manipulation over redmine's API, but
wrapping each action into a http/json request is slow and a bit tedious to
program. For example: I'd change the due_date of various issues having the
same parent issue_id. Over SQL, this should be a single UPDATE query.

But I am not sure whether this may lead to undefinded behaviour, e.g. when it
is happening in parallel to redmine and not, say, serialized by database
transactions.

I am using redmine 4.1.2 + postgresql.

Thanks for any hints or notes.

Replies (2)

RE: Safely manipulate SQL database directly (bypassing redmine API) ? - Added by Felix Schäfer about 1 month ago

Unfortunately all the validation and wellformedness checks for the data happen in Redmine/Rails, there's not even foreign key constraints in the schema. Long story short, you won't have any guarantee that Redmine can handle the data you yourself change in the DB.

Regarding any concurrency problems, as long as you wrap your updates in transactions Redmine should be fine.

Please note that going through the DB to change the due_date for example will also not generate the issue history entries showing the change. This will cause the data shown to be inconsistent, say the issue being created with a due_date of May 1st, a history entry changing this to May 2nd, and if you change that to May 3rd in the DB directly the last change in the history will show "due_date changed to May 2nd" but the issue will show May 3rd.

Long story short, I would strongly advise against changing anything in the DB directly, as you won't have any check permission- or data-wise that Redmine performs, and might end up with data which your current or any future version of Redmine might break on.

RE: Safely manipulate SQL database directly (bypassing redmine API) ? - Added by Marc Cramer about 1 month ago

What a pitty. So I stick to:

- Read-only SQL access
- Write through Redmine's API

Thanks a lot for that detailed answer. That helps preventing from any direct
write access via SQL definitly. ;-)

(1-2/2)