unit_tests.sh

#!/bin/sh

readonly M2S="./mysql2sqlite"

# define newline char as a constant
readonly NL='
'

# intermediary files generated by tests
readonly UT="./.unit_tests"
readonly SQL_DUMP="$UT/dump.sql"
readonly OUT_SCRIPT="$UT/dump.sqlite"
readonly OUT_DATABASE="$UT/db.sqlite"

mkdir -p -- "$UT"


# This function checks if given command is available on the system.
# It will terminate the script if command is not found.
assert_cmd_available() {
  type "$1" >/dev/null 2>&1 || {
    printf 'ERR command "%s" not available\n' "$1" >&2
    exit 1
  }
}

# This function creates a sqlite database using given sql script file. The file is put at $OUT_DATABASE.
# It converts SQL dump into sqlite script, and executes this script with sqlite3 to create a valid database file.
# The second param is optional: if true, errors won't cause the script to end
generate_db() {
  inputFile="$1"
  ignoreErrors="$2"
  rm -- "$OUT_SCRIPT" 2> /dev/null
  rm -- "$OUT_DATABASE" 2> /dev/null
  "$M2S" "$inputFile" > "$OUT_SCRIPT"
  sqlite3 "$OUT_DATABASE" < "$OUT_SCRIPT" > /dev/null
  if [ $? -ne 0 ]; then
    # an error occured during database creation => exit script
    # if ignoreError is true, do not exit script and write "error" on stdout to allow caller to detect the error
    if [ "$ignoreErrors" = true ]; then
        printf "error"
    else
        exit 1
    fi
  fi
}

# This function simply run the given query on $OUT_DATABASE
# It will print the result
# params: 
#     1. the SQL query (as string)
#     2. strict mode (as boolean). When in strict mode, a trailing '_' will be added to result to avoid
#        trailing newlines to be truncated. It will be the caller's responsibility to remove this trailing underscore
#
query() {
  [ $# -eq 2 ] || printf 'USAGE: query <query> <strict>\n'
  query="$1"
  strict="$2"
  result="$(
	sqlite3 "$OUT_DATABASE" 2>&1 <<-END
		$query
	END
	if [ "$strict" = true ]; then 
		printf '_'
	fi
  )"
  printf "%s" "$result"
}

# This function will execute given query on $OUT_DATABASE, and compare the result of this query with given string
# It will print an error and terminate the script if the result does not match given string
# params: 
#     1. the SQL query (as string)
#     2. the expected result of the query (as string)
#
assert_query() {
  [ $# -eq 2 ] || printf 'USAGE: assert_query <query> <expected>\n'
  query="$1"
  expected="$2"
  result="$(query "$query" true)"   # we need to use strict mode to avoid losing newlines at the end of text fields
  # remove trailing underscore and the last newline
  result=${result%_}
  result=${result%"$NL"}
  # compare expected value with retrieved value
  if [ "$result" != "$expected" ]; then
    printf '\nFAILURE:\n\tQuery failed on %s\n\t    query\t"%s"\n\t    expected\t"%s"\n\t    but got\t"%s"\n' "$OUT_DATABASE" "$query" "$expected" "$result" >&2
    exit 1
  fi
}

# This function dump the given hex string as binary in given file.
# params: 
#     1. the hexadecimal representation (as string)
#     2. the file to write (as string)
#
hexstr_to_file() {
  [ $# -eq 2 ] || printf 'USAGE: hexstr_to_file <hexstr> <filename>\n'
  hexstr="$1"
  filename="$2"
  escapedstr="$(
	sed 's/\(.\{2\}\)/\"\\\";\1;/g' <<-END
		$hexstr
	END
  )"
  result="$(
	bc <<-END | tr -d '\n'
		obase=8; ibase=16; $escapedstr
	END
  )"
  printf "$result" > "$2"
}

# =================== check test environment ==================
# ensure mandatory binaries are available

printf "\n================== \nChecking test environment \n"
assert_cmd_available "$M2S"
assert_cmd_available sqlite3
assert_cmd_available bc
assert_cmd_available sed
assert_cmd_available md5sum
assert_cmd_available awk
assert_cmd_available base64


# ================= Tests for types conversion ===================
# No tests for Spatial and JSON data types, as they require an extension of Sqlite

# Create the SQLite database that will be used for the data types conversion tests
printf "\n================== \nCreating dataset for types conversion tests (hex conversion warnings expected) \n\n"

cat <<\SQL > "$SQL_DUMP"
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `testdatetime` (
  `date` date NOT NULL,
  `time` time DEFAULT NULL,
  `datetime` datetime DEFAULT NULL,
  `timestamp` timestamp NULL DEFAULT NULL,
  `year` year(4) DEFAULT NULL,
  PRIMARY KEY (`date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
/*!40101 SET character_set_client = @saved_cs_client */;
INSERT INTO `testdatetime` VALUES ('2020-03-24','838:59:59','9999-12-31 23:59:59','2038-01-19 02:14:07',2155);
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `testmultirows` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(45) DEFAULT NULL,
  `weight` tinyint(4) DEFAULT NULL,
  `picture` blob DEFAULT NULL,
  `base64picture` text DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4;
/*!40101 SET character_set_client = @saved_cs_client */;
INSERT INTO `testmultirows` VALUES (1,'Greg',125,0x89504E470D0A1A0A0000000D494844520000001000000010080200000090916836000004247A5458745261772070726F66696C6520747970652065786966000078DACD975B96E3280C86DF59C52CC14288CB72B89E333B98E5CF8F034AC57549D2DD0F6D972D590681F4099C32FDBF7F87F907873DEC619C84E893F7070E975CB2194A3C6EC74DD2E1CEFB7A38B6F26037FAC2C2C4907C7BF47DB5CFB0CBBD4370CB5E1EED26D4E5272E47A48ECF83E7C8535FEDE272C4F666A7F56CD2EA97DD8770D665EB72BB9C5F9F5D40329AC01F5B633B131FB8C7390ADFAE8C8B71B7ECD0884E8B83CCB086AF736754BD244FB54BEE8EBCECFC980A73F8D5C05F72B4EC24173BEB30F64A6D8FFCF0A2361DE253EEC668718C7E8B2E3B8F4C79B382DAA19C1A1A16A492CF6E1E67C025D0C379269C112156106BA059705643892CB239C851A34C83FA292B554CD1D96E03A4B5D5F2698B1C6CB2952702374F1A3670E26638824D053586D9EA5CE81C379DE3558A18B9115A5A8233428F4FA7F9CAF82BA73A1A63962ED111355798979D358D694C72F38E56004263E554CEFC9EA7F95037C707B00C8272A63922C07C949B8B2274AF2D3E3933DAC9E1CC715B1A14DA728014616CC1648841E0F0C4429E8E606D20421E23F864CC7C967D010112B18DCC001B660F38D1CEB1D127D0D9D68ABD99B1B50084B0E700348933603927A89FE0226A280B8B3322E225489424D9B3775EBCF7C1CF3D2A070E2E48F021841852C891A38B127D0C31C61473B289B18549F2299814534A3963D00CD719BD335AE45C6CE1E28A145F428925955C513ED555A9BE861A6BAAB9D9C60DCBBFF9164C8B2DB5DCA9A394BAEBD27D0F3DF6D4F340AD0D1E6EC8F0238C38D2C84A6D517DA44617723F53A3456D127367BB70A7067308DB05CDED44263310B38E403C4C0228683B991D919CB393DC6476248B452116D448269C46931808BA4E560629BB3BB91FB919716F71B3DF913313DD9F206726BA45EE33B72FA8B57C7E51F8043457E1CCE9C1031B1B1A651BF187FDF8D7A5F95D077FB923CBCBC235FBA920A13DE7AD55BBB49FA479D6E055793A1AECF694F4E5F0F91200ED57F25584E6851458D2C8A56BBC3B07F19602D451D7A16ADD3D4AD91AEFAE8335931D8B67A97EBF36EFE66368645CD4731ED97C134D6DE4049F644A654AFC340B154B7EA92DA9EADB4DC3C6B64CAFCA80DF566B9032D4DD30CFBA1D696B91B6832165A9872C59CD0E13CB7F072C4F2872D51437056A9AEB4FD3AB55862D6B69CDFBC766E6A849A9BAAEEA9D887C3740DEE1FB29CD197463B9C4BC24C57687A6A96D655B7DDA7933B4BB0CEEDBD88BE65AC68B30CD53DA5167D7B4B8BA4ED4EE97A6B3CE98B573A377AB8BCCB76577AF6DB5650D5E74DBA2BCA935855B9B2ED010DEDD24CDB5D862D475BF471D2E5D37141B776E72D96BCDB3A6D06910E5D31A78223FE0C747730FB2D3EFEBAB6EA22EDA10874E6CBC8D0CD2BCDBE1A314E514BDAEFE46BFF72D317FE663F4973AC26F1EFC876CFE07868DA7F25AEB7C0A000000097048597300002E2300002E230178A53F760000000774494D4507E40318092925939F86B7000001484944415428CFB55231AB826014FD7A88A39F1052E01048CD52430D8D82F827F477F407FC0B22CDE1E2EA10B9B854B83944E0E2243D08850FB1064D6E83EFD993D7E3BD8677A67B8773EF39F7DC0E00A057F0865E448B70BBDD2CCB0A82E0AF048AA230C6BBDDAE6E9FAAED00405114A6698EC7E3B22CB7DB6D9AA6F3F99C10E2388E61183CCFB718000000BAAEAFD7EBF74F1042A2284208F9BE0F6D50356D381CB22CDBEFF79B4187C3415194E9748A10AAAA2A8AA2D168F4F09065591CC75F377B9EA7695A5DA769AA695A92240F498490300C9BBDA7D389619824496A878BC522088296248C31C6B819BF5C2E6DDBEE76BB00B05AAD004014C58F4B7E3FDC66B311044196E5EBF5BADFEF2793C9F97CFE3169DFF7ABAA5255B5288ACBE5224952AFD76B65D55465591E8FC7C160309BCD1042344D731CF72441F80D799EBBAEDBB49D7FFFD63BC07DF62FC0391A340000000049454E44AE426082,'iVBORw0KGgoAAAANSUhEUgAAABAAAAAQCAIAAACQkWg2AAAEJHpUWHRSYXcgcHJvZmlsZSB0eXBlIGV4aWYAAHjazZdbluMoDIbfWcUswUKIy3K4njM7mOXPjwNKxXVJ0t0PbZctWQaB9AmcMv2/f4f5B4c97GGchOiT9wcOl1yyGUo8bsdN0uHO+3o4tvJgN/rCwsSQfHv0fbXPsMu9Q3DLXh7tJtTlJy5HpI7Pg+fIU1/t4nLE9man9WzS6pfdh3DWZetyu5xfn11AMprAH1tjOxMfuMc5Ct+ujItxt+zQiE6Lg8ywhq9zZ1S9JE+1S+6OvOz8mApz+NXAX3K07CQXO+sw9kptj/zwojYd4lPuxmhxjH6LLjuPTHmzgtqhnBoaFqSSz24eZ8Al0MN5JpwRIVYQa6BZcFZDiSyyOchRo0yD+ikrVUzR2W4DpLXV8mmLHGyylScCN08aNnDiZjiCTQU1htnqXOgcN53jVYoYuRFaWoIzQo9Pp/nK+CunOhpjli7RETVXmJedNY1pTHLzjlYAQmPlVM78nqf5UDfHB7AMgnKmOSLAfJSbiyJ0ry0+OTPayeHMcVsaFNpygBRhbMFkiEHg8MRCno5gbSBCHiP4ZMx8ln0BARKxjcwAG2YPONHOsdEn0NnWir2ZsbUAhLDnADSJM2A5J6if4CJqKAuLMyLiJUiUJNmzd16898HPPSoHDi5I8CGEGFLIkaOLEn0MMcYUc7KJsYVJ8imYFFNKOWPQDNcZvTNa5Fxs4eKKFF9CiSWVXFE+1VWpvoYaa6q52cYNy7/5FkyLLbXcqaOUuuvSfQ899tTzQK0NHm7I8COMONLISm1RfaRGF3I/U6NFbRJzZ7twpwZzCNsFze1EJjMQs45APEwCKGg7mR2RnLOT3GR2JItFIRbUSCacRpMYCLpOVgYpuzu5H7kZcW9xs9+RMxPdnyBnJrpF7jO3L6i1fH5R+AQ0V+HM6cEDGxsaZRvxh/3416X5XQd/uSPLy8I1+6kgoT3nrVW7tJ+kedbgVXk6Guz2lPTl8PkSAO1X8lWE5oUUWNLIpWu8OwfxlgLUUdehat09Stka766DNZMdi2epfr827+ZjaGRc1HMe2XwTTW3kBJ9kSmVK/DQLFUt+qS2p6ttNw8a2TK/KgN9Wa5Ay1N0wz7odaWuRtoMhZamHLFnNDhPLfwcsTyhy1RQ3BWqa60/Tq1WGLWtpzfvHZuaoSam6ruqdiHw3QN7h+ynNGXRjucS8JMV2h6apbWVbfdp5M7S7DO7b2IvmWsaLMM1T2lFn17S4uk7U7pems86YtXOjd6uLzLdld69ttWUNXnTborypNYVbmy7QEN7dJM212GLUdb9HHS5dNxQbd25y2WvNs6bQaRDl0xp4Ij/gx0dzD7LT7+urbqIu2hCHTmy8jQzSvNvhoxTlFL2u/ka/9y0xf+Zj9Jc6wm8e/Ids/geGjafyWut8CgAAAAlwSFlzAAAuIwAALiMBeKU/dgAAAAd0SU1FB+QDGAkpJZOfhrcAAAFISURBVCjPtVIxq4JgFP16iKOfEFLgEEjNUkMNjYL4J/R39Af8CyLN4eLqELm4VLg5RODiJD0IhQ+xBk1ug+/Zk9fjvYZ3pnuHc+8599wOAKBX8IZeRItwu90sywqC4K8EiqIwxrvdrm6fqu0AQFEUpmmOx+OyLLfbbZqm8/mcEOI4jmEYPM+3GAAAALqur9fr908QQqIoQgj5vg9tUDVtOByyLNvv95tBh8NBUZTpdIoQqqoqiqLRaPTwkGVZHMdfN3uep2laXadpqmlakiQPSYSQMAybvafTiWGYJElqh4vFIgiCliSMMca4Gb9cLm3b7na7ALBarQBAFMWPS34/3GazEQRBluXr9brf7yeTyfl8/jFp3/erqlJVtSiKy+UiSVKv12tl1VRlWR6Px8FgMJvNEEI0TXMc9yRB+A15nruu27Sdf//WO8B99i/AORo0AAAAAElFTkSuQmCC');
INSERT INTO `testmultirows` VALUES (2,'Mireille',52,0x89504E470D0A1A0A0000000D49484452000000100000001008000000003A98A0BD000004147A5458745261772070726F66696C6520747970652065786966000078DADD975176C3280C45FF59C52C0121846039D8C039B38359FE3C6C20AD9D34D3767E66E2C6288A10E25D999E98FAD79FCDFC8197B3148D178D218560F1F2C9279761447BBECE91AC3FEEE3839DC627BF595F20AD658C7C7E0C75C467F8E53141FDF06F9FFD46F791278E4433F348C87D650763C4C59188DDE9A7F1D9A4312FFB0FDB196FD623C50ABE7EF60A318AC0C9CEB8CAC416F73ED1F1F9CE7833EE8E3D82E8F0F8714FCFB533CBBC88B7AC8B76360F3F7F96C2D83002C245A3E12779AEDDA1D085DA5CF9D317FBB696B869D75A89ADD57377D9072815CCD8D4DCCA61217083947C4C0BB8146F81ADC79570456C7187E80534375CBBA1440E6A36F2542853A37A8C3BED28D1BBEA14A373BBE3C317595D723B7704BE5FD49C72E2623882CD0E6A0CB75BB5D0B16E3AD6DB2962E542887484648419B7CB3C73FEE45A895AEBAD4B64E3D20A75B9DE8028A393EB77440108B5A1A91CFA1E97F9D037F60358064139648ED860B6DB9962137AF4161F9C197162BDB167BB9396910012616D4131C4206003B15020ABCE2911748CE09351796FFB0D0448C415320D6C9803E044D7D7C61CA523D6893BDD385A004238B0024DE20C58DE0BFA477D440F6561F1464482A844499203071F2484A0A19F515959BD8A06558D9A34478E3E4A0C51638C29E6E412E3089314929A14534A3963D18CD419B3332272DEDCC69BDF640B9B6E714B5BDED13EBBDF650FBBEE714F7B2EAE70C1E35F42515362492557AA68A5EAABD450B5C69A6A6EE8B5C6CD3769A1698B2DB5BCA80DAA9FA9D185DCD7D46850EBC4FC11A70F6A70ABCE14D48F13E9CC40CC790271ED04D0D0AE33B391BC779D5C676693C343210ED4483A9C429D1808FA4A4E1A2D760F725F7233E2BFC5CDBD22673ABA7F839CE9E806B93BB727D44A3EFEA3F001A83F855D53CB0D071B82B28BF8C379FCF3D1FC36C17F2411252FE4F10F3D501F71B6536CC385CE390DD946348E8C3E42FC92B233678632A2E88763503397AE3A970CA10C9FBB192C5723EB39CDE0399A1B92FD9BDAB8BCCAF0669B59FCFC761972F3849BA1D33075565DCAB49688EFC6C64BF8686657D3C4C37584A5199FC2CDC837C34CABC61955B7F0A6923827355A9DB3345A85E8422537E30B8A0B7F9865608B5791AFB498974A9486CFAC5EE47C29C9B6D71CEF86B981E4626771DBAB860F613510D36CC84725E34B5BAF207DBA19DBD5307EAEBA0C9436B5D4FC96DB5CD63C1E7977D566C9F7A4A2F4BA22494BA4B5DB14DEF6F6A2C671B63985AB58DFE97AB3AC9A579397FC0F1FB67986584DC64E90DEBDA5F7A4A075481AFB5066866DFCDDF31147A1197D159693CBB5A0F2B2A012571986561DED57E7B6E93FAEA6C8ABFF76BA1DA63AA1945929EBC703C31CBB98F2FE62FC9F26C22F8B660A7EA39ABF0144B09C6489CEDC38000000097048597300000B1300000B1301009A9C180000000774494D4507E403180928399E85EBB9000000B04944415418D37D8F314A03011444DF7E7783C1A8A08D293427102CACBC8147F08E9EC00B885D7A0B4105AD1223AC48F267BEC56AA185D3CDC0306F9AE2B7E28FA705404FAFEB9DA369004D01CB9B45D78D4687E793A1D25FBF4899F976F73904B7ABB22C657F0F019BB96D4B99F99C04BC6FCA96A4CCF5072D50556EAC082504ECB5E5B22D893101DD5995CB92F2B823808BFD2ADBD6F6E9303BB99A56D93EB81C7F93A287C77EF764B6F583FEEFDB2FF61864993438FD770000000049454E44AE426082,'iVBORw0KGgoAAAANSUhEUgAAABAAAAAQCAAAAAA6mKC9AAAEFHpUWHRSYXcgcHJvZmlsZSB0eXBlIGV4aWYAAHja3ZdRdsMoDEX/WcUsASGEYDnYwDmzg1n+PGwgrZ0003Z+ZuLGKIoQ4l2Znpj615/N/IGXsxSNF40hhWDx8sknl2FEe77Okaw/7uODncYnv1lfIK1ljHx+DHXEZ/jlMUH98G+f/Ub3kSeORDPzSMh9ZQdjxMWRiN3pp/HZpDEv+w/bGW/WI8UKvn72CjGKwMnOuMrEFvc+0fH5zngz7o49gujw+HFPz7Uzy7yIt6yLdjYPP3+WwtgwAsJFo+Enea7dodCF2lz50xf7tpa4addaia3Vc3fZBygVzNjU3MphIXCDlHxMC7gUb4Gtx5VwRWxxh+gFNDdcu6FEDmo28lQoU6N6jDvtKNG76hSjc7vjwxdZXXI7dwS+X9SccuJiOILNDmoMt1u10LFuOtbbKWLlQoh0hGSEGbfLPHP+5FqJWuutS2Tj0gp1ud6AKKOT63dEAQi1oakc+h6X+dA39gNYBkE5ZI7YYLbbmWITevQWH5wZcWK9sWe7k5aRABJhbUExxCBgA7FQIKvOKRF0jOCTUXlv+w0ESMQVMg1smAPgRNfXxhylI9aJO904WgBCOLACTeIMWN4L+kd9RA9lYfFGRIKoREmSAwcfJISgoZ9RWVm9igZVjZo0R44+SgxRY4wp5uQS4wiTFJKaFFNKOWPRjNQZszMict7cxpvfZAubbnFLW97RPrvfZQ+77nFPey6ucMHjX0JRU2JJJVeqaKXqq9RQtcaaam7otcbNN2mhaYsttbyoDaqfqdGF3NfUaFDrxPwRpw9qcKvOFNSPE+nMQMx5AnHtBNDQrjOzkbx3nVxnZpPDQyEO1Eg6nEKdGAj6Sk4aLXYPcl9yM+K/xc29Imc6un+DnOnoBrk7tyfUSj7+o/ABqD+FXVPLDQcbgrKL+MN5/PPR/DbBfyQRJS/k8Q89UB9xtlNsw4XOOQ3ZRjSOjD5C/JKyM2eGMqLoh2NQM5euOpcMoQyfuxksVyPrOc3gOZobkv2b2ri8yvBmm1n8/HYZcvOEm6HTMHVWXcq0lojvxsZL+GhmV9PEw3WEpRmfws3IN8NMq8YZVbfwppI4JzVanbM0WoXoQiU34wuKC3+YZWCLV5GvtJiXSpSGz6xe5HwpybbXHO+GuYHkYmdx26uGD2E1ENNsyEcl40tbryB9uhnb1TB+rroMlDa11PyW21zWPB55d9VmyfekovS6IklLpLXbFN729qLGcbY5hatY3+l6s6yaV5OX/A8ftnmGWE3GTpDevaX3pKB1SBr7UGaGbfzd8xFHoRl9FZaTy7Wg8rKgElcZhlYd7Vfntuk/rqbIq/92uh2mOqGUWSnrxwPDHLuY8v5i/J8mwi+LZgp+o5q/AUSwnGSJztw4AAAACXBIWXMAAAsTAAALEwEAmpwYAAAAB3RJTUUH5AMYCSg5noXruQAAALBJREFUGNN9jzFKAwEURN9+d4PBqKCNKTQnECysvIFH8I6ewAuIXXoLQQWtEiOsSPJnvsVqoYXTzcAwb5rit+KPpwVAT6/rnaNpAE0By5tF141Gh+eTodJfv0iZ+Xb3OQS3q7IsZX8PAZu5bUuZ+ZwEvG/KlqTM9QctUFVurAglBOy15bItiTEB3VmVy5LyuCOAi/0q29b26TA7uZpW2T64HH+ToofHfvdktvWD/u/bL/YYZJk0OP13AAAAAElFTkSuQmCC');
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `testnumeric` (
  `tinyint` tinyint(4) NOT NULL,
  `smallint` smallint(6) DEFAULT NULL,
  `mediumint` mediumint(9) DEFAULT NULL,
  `int` int(11) DEFAULT NULL,
  `bigint` bigint(20) DEFAULT NULL,
  `decimal` decimal(20,2) DEFAULT NULL,
  `float` float DEFAULT NULL,
  `double` double DEFAULT NULL,
  `bit` bit(32) DEFAULT NULL,
  PRIMARY KEY (`tinyint`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
/*!40101 SET character_set_client = @saved_cs_client */;
INSERT INTO `testnumeric` VALUES (127,32767,8388607,2147483647,9223372036854775807,988888888888888889.99,-1.17549e-38,-2.2250738585072014e-308,0xFFFFFFFF);
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `teststring` (
  `char` char(1) NOT NULL,
  `varchar` varchar(45) DEFAULT NULL,
  `text` text DEFAULT NULL,
  `enum` enum('YES','NO','MAYBE') DEFAULT NULL,
  `set` set('a','b','c','d') DEFAULT NULL,
  `binary` binary(1) DEFAULT NULL,
  `varbinary` varbinary(45) DEFAULT NULL,
  `blob` blob DEFAULT NULL,
  PRIMARY KEY (`char`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
/*!40101 SET character_set_client = @saved_cs_client */;
INSERT INTO `teststring` VALUES ('Z','a varchar for test that can be 0x2D char long','text field content Lorem ipsum parabellum rectum et toutletoutim\n We can also add some quotes \' double quotes \'\' doublequote \" and double doublequote \"\"\n Why not some escape \\\' \\\'\' \\\" \\\"\"\n And some hexa 0xBAD\n\n Now its done\n','MAYBE','c',0x2B,0x61207661726368617220666F72207465737420746861742063616E20626520307832442063686172206C6F6E67,0x74657874206669656C6420636F6E74656E74204C6F72656D20697073756D207061726162656C6C756D2072656374756D20616E6420746F75746C65746F7574696D);
SQL
generate_db "$SQL_DUMP"

# numeric
printf "\t> Testing numeric values conversion \n"
assert_query "SELECT tinyint, smallint, mediumint, int, bigint FROM testnumeric;" \
             "127|32767|8388607|2147483647|9223372036854775807"

assert_query "SELECT decimal, float, double FROM testnumeric;" \
             "988888888888888832|-1.17549e-38|-2.2250738585072e-308"

assert_query "SELECT HEX(bit) FROM testnumeric;" \
             "FFFFFFFF"

# datetime
printf "\t> Testing datetime values conversion \n"
assert_query "SELECT * FROM testdatetime;" \
             "2020-03-24|838:59:59|9999-12-31 23:59:59|2038-01-19 02:14:07|2155"

# strings
printf "\t> Testing strings values conversion \n"
assert_query "SELECT char, varchar, \`set\`, enum FROM teststring;" \
             "Z|a varchar for test that can be 0x2D char long|c|MAYBE"

assert_query "SELECT text FROM teststring;" \
             "text field content Lorem ipsum parabellum rectum et toutletoutim$NL We can also add some quotes ' double quotes '' doublequote \" and double doublequote \"\"$NL Why not some escape \\' \\'' \\\" \\\"\"$NL And some hexa 0xBAD$NL$NL Now its done$NL"

assert_query "SELECT HEX(binary), HEX(varbinary) FROM teststring;" \
             "2B|61207661726368617220666F72207465737420746861742063616E20626520307832442063686172206C6F6E67"

assert_query "SELECT HEX(blob) FROM teststring;" \
             "74657874206669656C6420636F6E74656E74204C6F72656D20697073756D207061726162656C6C756D2072656374756D20616E6420746F75746C65746F7574696D"

# Test mutiple inserts
printf "\t> Testing multiple insert \n"
assert_query "SELECT id, name, weight FROM testmultirows WHERE id=1;" \
             "1|Greg|125"

assert_query "SELECT id, name, weight FROM testmultirows WHERE id=2;" \
             "2|Mireille|52"


# Test that blobs and text in base64 format are not corrupted
# A picture was inserted into database as a binary blob, and the same picture was inserted as base64 text :
# data is retrieved from database, then dumped in 2 files (to ease debug in case of failure), and then
# files hash are compared to ensure that they are identical
printf "\t> Testing images encoding \n"
i=1
while [ "$i" -le 2 ]; do
  out_picblob="$UT/test_picture_blob.png"
  picblob="$(query 'SELECT HEX(picture) FROM testmultirows WHERE id='$i';' false)"  # no need for strict mode because hex cannot contain \n
  hexstr_to_file "$picblob" "$out_picblob"
  md5blob="$(md5sum $out_picblob | awk '{ print $1 }')"

  out_picb64="$UT/test_picture_base64.png"
  picb64="$(query 'SELECT base64picture FROM testmultirows WHERE id='$i';' false)"  # no need for strict mode because base64 cannot contain \n
  printf "%s" "$picb64" | base64 -d > "$out_picb64"
  md5b64="$(md5sum $out_picb64 | awk '{ print $1 }')"

  # compare blob's md5 with md5 of empty file, then with md5 of base64 file
  if [ "$md5blob" = "d41d8cd98f00b204e9800998ecf8427e" ]; then
    printf '\nFAILURE:\n\tPicture %d was erased (BLOB is empty)\n' "$i" >&2
    exit 1
  fi
  if [ "$md5blob" != "$md5b64" ]; then
    printf '\nFAILURE:\n\tPicture %d got corrupted, either as BLOB or as base64 TEXT\n\t    picture from blob   dumped at  %s\n\t    picture from base64 dumped at  %s\n' "$i" "$OUT_picblob" "$OUT_picb64" >&2
    exit 1
  fi
  i=$(( i + 1 ))
done


# ================= Tests for Hex numbers ===================
# Create the SQLite database that will be used for the hex numbers tests
printf "\n================== \nCreating dataset for Hex numbers tests \n\n"

# Hex numbers with 15 and 16 characters 
cat <<\SQL > "$SQL_DUMP"
CREATE TABLE `cache` (
  `cid` VARCHAR(127) NOT NULL,
  `data` BIGINT NOT NULL,
  `expire` TINYINT NOT NULL,
  `created` TIMESTAMP NOT NULL,
  `headers` VARCHAR(45) NOT NULL,
  `serialized` TINYINT NOT NULL,
  PRIMARY KEY (`cid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
INSERT INTO 'cache' ('cid', 'data', 'expire', 'created', 'headers', 'serialized') VALUES
  ('ctools_plugin_files:ctools:style_bases', 0x613a313a7b733a3, 0, 1440573529, '', 1),
  ('ctools_plugin_files:ctools:content_types', 0xa343a226e6f64652, 0, 1440573529, '', 1);
SQL
generate_db "$SQL_DUMP"

# check that bit values are correctly converted
printf "\t> Testing Hex numbers with 15 characters \n"
assert_query "SELECT data FROM cache WHERE cid='ctools_plugin_files:ctools:style_bases';" \
             "437872893598577571"
printf "\t> Testing Hex numbers with 16 characters \n"
assert_query "SELECT data FROM cache WHERE cid='ctools_plugin_files:ctools:content_types';" \
             "-6682319134120327598"

# Hex numbers with 17 characters => will fail because BIGINT is converted to integer (which is 8 bytes max)
# As we don't want to loose data, we don't want to fail silently
printf "\t> Testing Hex numbers with 17 characters \n"
cat <<\SQL > "$SQL_DUMP"
CREATE TABLE `cache` (
  `cid` VARCHAR(127) NOT NULL,
  `data` BIGINT NOT NULL,
  `expire` TINYINT NOT NULL,
  `created` TIMESTAMP NOT NULL,
  `headers` VARCHAR(45) NOT NULL,
  `serialized` TINYINT NOT NULL,
  PRIMARY KEY (`cid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
INSERT INTO 'cache' ('cid', 'data', 'expire', 'created', 'headers', 'serialized') VALUES
  ('theme_registry:my_theme', 0x613a3234353a7b733, 0, 1440572933, '', 1);
SQL
result=$(generate_db "$SQL_DUMP" true 2>/dev/null) # this should return "error"
if [ "$result" != "error" ]; then
  printf "Error: database generation should have failed with 'hex literal too big', but it has succeeded \n\n"
  exit 1
fi


# ================= Tests for bit-fields ===================
# Create the SQLite database that will be used for the bit fields handling tests
printf "\n================== \nCreating dataset for Bit-Fields tests (hex conversion warnings expected) \n\n"

cat <<\SQL > "$SQL_DUMP"
CREATE TABLE "bit_type" (
  "id" int(10) unsigned NOT NULL AUTO_INCREMENT,
  "b" bit(1) NOT NULL DEFAULT b'1',
  "c" bit(8) NOT NULL DEFAULT B'11111111',
  "d" BIT(4) NOT NULL DEFAULT b'1010',
  "e" BIT(64) NOT NULL DEFAULT B'00111111110000111'
);
insert into "bit_type" ("id") values (NULL);
insert into "bit_type" ("id", "b", "c", "d", "e") values (2,0x01,0xEE,0xF,0xA0000000000000C7);
SQL
generate_db "$SQL_DUMP"

# check that bit values are correctly converted
printf "\t> Testing Bit-Fields bit values handling \n"
assert_query "SELECT id, HEX(b), HEX(c), HEX(d), HEX(e) FROM bit_type where id=1;" \
             "1|01|FF|0A|007F87"

printf "\t> Testing Bit-Fields hex values handling \n"
assert_query "SELECT id, HEX(b), HEX(c), HEX(d), HEX(e) FROM bit_type where id=2;" \
             "2|01|EE|0F|A0000000000000C7"


# ================= Tests for comments, keys, timestamps and triggers removal ===================
# Create the SQLite database that will be used for the tests
printf "\n================== \nCreating dataset for comments, keys, timestamps and triggers removal \n\n"

cat <<\SQL > "$SQL_DUMP"
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `category` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `name` varchar(45) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
INSERT INTO `category` VALUES (1,'Croissants');
INSERT INTO `category` VALUES (2,'Chocolatines');
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `article` (
  `id` bigint(32) NOT NULL AUTO_INCREMENT COMMENT 'PK.',
  `llll` varchar(10) NOT NULL COMMENT 'Some Comment',
  `rrrr` timestamp NOT NULL DEFAULT current_timestamp() COMMENT 'Created date',
  `ssss` timestamp NULL DEFAULT NULL ON UPDATE current_timestamp() COMMENT 'Modified Date. Some Comment',
  `tttt` varchar(1) DEFAULT 'A' COMMENT 'Some Comment',
  `bbbb` int(10) NOT NULL COMMENT 'Foreign key',
  PRIMARY KEY (`id`),
  UNIQUE KEY `aaaa_pk` (`id`) COMMENT 'PK Index',
  KEY `bbbb_fk` (`bbbb`) COMMENT 'Index for FK, Reference Category',
  CONSTRAINT `bbbb_fk` FOREIGN KEY (`bbbb`) REFERENCES `category` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
INSERT INTO `article` VALUES (1,'Beurre','2023-10-02 12:47:31',NULL,'A',1);
SQL
generate_db "$SQL_DUMP"

# check that values are correctly inserted
printf "\t> Testing for foreign key insert\n"
assert_query "SELECT id, llll, rrrr, ssss, tttt, bbbb FROM article WHERE id=1;" \
             "1|Beurre|2023-10-02 12:47:31||A|1"

# check current_timestamp
printf "\t> Testing for current_timestamp\n"
query "INSERT INTO article (llll, ssss, tttt, bbbb) VALUES ('Farine', '2023-10-02 12:47:33', 'B', 2);" false
assert_query "SELECT id, llll, rrrr, ssss, tttt, bbbb FROM article WHERE id=2;" \
             "2|Farine|$(date -u "+%F %H:%M:%S")|2023-10-02 12:47:33|B|2"

# check foreign key enforcement, by deleting a constraint and checking for error
printf "\t> Testing for foreign key enforcement\n"
result=$(query "PRAGMA foreign_keys=1; DELETE FROM category WHERE id=1;" false)
if ! grep -q "FOREIGN KEY constraint failed" <<< "$result"; then
  printf "Error: foreign key should have prevent deletion, but it has not \n\n"
  exit 1
fi

# ================= Non-regression tests for specific bugs ===================

printf "\n================== \nCreating dataset for issue #73 Non-regression \n\n"

# issue #73 regression testing
# https://github.com/dumblob/mysql2sqlite/issues/73
cat <<\SQL > "$SQL_DUMP"
/*!40101 SET character_set_client = @saved_cs_client */;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `issue73` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `event_time` datetime(6) NOT NULL,
  `record_type` int(11) NOT NULL,
  `value_text` longtext,
  `value_numerical` double DEFAULT NULL,
  `value_enum` int(11) DEFAULT NULL,
  `created_at` datetime(6) NOT NULL,
  `medical_record_id` int(11) NOT NULL,
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
SQL
generate_db "$SQL_DUMP"

printf "\t> Testing issue #73 non-regression \n"
assert_query ".schema issue73" \
             "CREATE TABLE \`issue73\` ($NL  \`id\` integer NOT NULL PRIMARY KEY AUTOINCREMENT$NL,  \`event_time\` datetime(6) NOT NULL$NL,  \`record_type\` integer NOT NULL$NL,  \`value_text\` longtext$NL,  \`value_numerical\` double DEFAULT NULL$NL,  \`value_enum\` integer DEFAULT NULL$NL,  \`created_at\` datetime(6) NOT NULL$NL,  \`medical_record_id\` integer NOT NULL$NL);"


# ================= TODO ===================
#       the following tests are not yet developped. 
#       The SQL is provided as hints for the future test-cases 
#
printf '\nWARNING: Unit testing not yet fully implemented\n\n' >&2
exit 0
# ===========================================

cat <<\SQL
/*!50100 PARTITION BY RANGE (YEAR(date))
(PARTITION p6 VALUES LESS THAN (2012) ENGINE = InnoDB,
 PARTITION p7 VALUES LESS THAN (2013) ENGINE = InnoDB)
SQL


cat <<\SQLout
PRAGMA synchronous = OFF;
PRAGMA journal_mode = MEMORY;
BEGIN TRANSACTION;
CREATE TABLE `CCC`(
  `created` datetime DEFAULT current_timestamp
,  `updated` datetime DEFAULT current_timestamp 
);
END TRANSACTION;
SQLout


cat <<\SQL
CREATE TABLE `scimag` (
  `ID` int(15) unsigned NOT NULL AUTO_INCREMENT,
  `DOI` varchar(200) NOT NULL,
  PRIMARY KEY (`ID`) USING BTREE,
  UNIQUE KEY `DOIUNIQUE` (`DOI`) USING BTREE,
);
SQL

cat <<\SQLin
CREATE TABLE `scimag` (
  `TEXTFIELD` text DEFAULT (_utf8mb3'text_value'),
);
SQLin

cat <<\SQLout
PRAGMA synchronous = OFF;
PRAGMA journal_mode = MEMORY;
BEGIN TRANSACTION;
CREATE TABLE `scimag` (
  `TEXTFIELD` text DEFAULT ('text_value')
);
END TRANSACTION;
SQLout