Hello everyone.
I am working on FINERACT-723
(https://issues.apache.org/jira/browse/FINERACT-723
<https://issues.apache.org/jira/browse/FINERACT-723>) regarding issues where
the tenant's time zone is sometimes not considered when calculating
dates/times. So the goal is to refactor all usages of MySQL date/time functions
to use Fineract's DateUtils class instead.
I started by looking at MySQL docs and other resources and made a list of 60
MySQL date/time functions.
I then wrote a script (pseudocode below) to grep recursively, ignoring case,
outputting the list of files where the given function name was found. The ".*"
regex accounts for any [0, n] function arguments.
sqlDateTimeFunctions = [ "ADDDATE", "ADDTIME", "CONVERT_TZ", "CURDATE",
"CURRENT_DATE", "CURRENT_TIME", "CURRENT_TIMESTAMP", "CURTIME", "DATE_ADD",
"DATE_FORMAT", "DATE_SUB", "DATE", "DATEDIFF", "DAY", "DAYNAME", "DAYOFMONTH",
"DAYOFWEEK", "DAYOFYEAR", "EXTRACT", "FROM_DAYS", "FROM_UNIXTIME",
"GET_FORMAT", "HOUR", "LAST_DAY", "LOCALTIME", "LOCALTIMESTAMP", "MAKEDATE",
"MAKETIME", "MICROSECOND", "MINUTE", "MONTH", "MONTHNAME", "NOW", "PERIOD_ADD",
"PERIOD_DIFF", "QUARTER", "SEC_TO_TIME", "SECOND", "STR_TO_DATE", "SUBDATE",
"SUBTIME", "SYSDATE", "TIME_FORMAT", "TIME_TO_SEC", "TIME", "TIMEDIFF",
"TIMESTAMP", "TIMESTAMPADD", "TIMESTAMPDIFF", "TO_DAYS", "TO_SECONDS",
"UNIX_TIMESTAMP", "UTC_DATE", "UTC_TIME", "UTC_TIMESTAMP", "WEEK", "WEEKDAY",
"WEEKOFYEAR", "YEAR", "YEARWEEK" ]
for (func in sqlDateTimeFunctions)
grep -ril funcName + "(.*)" ../../fineract-FORK >>
./funcNames/funcName.txt
Question (1): Does this approach seem reasonable to find all problems where
tenant TZ is not considered? I realize there might be some false-positives for
example because the DATE() and NOW() syntax is used both in MySQL and Java. But
these false positives can be ruled out by manually inspecting each file when
refactoring.
(I also think some of these functions, like TIMESTAMPADD() might not be
problems because they probably don’t use the date/time from the MySQL server,
but I searched them anyways to be safe).
From this script, I found 1360 different files total, and each file might have
more than 1 MySQL date/time function that needs to be refactored.
But it seems that many of these 1360 files are from the migrations directory as
.sql files.
It could lessen this workload if someone can help me understand these migration
files.
(I've ignored .sql files so far as I've been working through the .java files,
because I think they're more relevant).
On the Wikipedia page about schema migrations I found this: "Production
databases are usually huge, old and full of surprises. The surprises can come
from many sources: [e.g.] Corrupt data that was written by old versions of the
software and not cleaned properly".
Question (2): Do these migration files only contain "corrupt" row values e.g.
NOW() because previous Fineract versions inserted that row using NOW() as a
value?
i.e. once a given service layer is refactored to not use the MySQL NOW()
function, will future migration files no longer contain the NOW() function?
Thanks and regards
--Dylan
