Nevar pievienot vairāk kā 25 tēmas Tēmai ir jāsākas ar burtu vai ciparu, tā var saturēt domu zīmes ('-') un var būt līdz 35 simboliem gara.

pirms 1 gada
pirms 1 gada
pirms 1 gada
pirms 1 gada
123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149
  1. #!/bin/bash
  2. # @name: database_functions.sh
  3. # @creation_date: 2022-11-02
  4. # @license: The MIT License <https://opensource.org/licenses/MIT>
  5. # @author: Simon Bowie <ad7588@coventry.ac.uk>
  6. # @purpose: Runs database functions for the Experimental Publishing Compendium
  7. # @acknowledgements:
  8. # https://www.redhat.com/sysadmin/arguments-options-bash-scripts
  9. ############################################################
  10. # subprograms #
  11. ############################################################
  12. License()
  13. {
  14. echo 'Copyright 2022 Simon Bowie <ad7588@coventry.ac.uk>'
  15. echo
  16. echo 'Permission is hereby granted, free of charge, to any person obtaining a copy of this software and associated documentation files (the "Software"), to deal in the Software without restriction, including without limitation the rights to use, copy, modify, merge, publish, distribute, sublicense, and/or sell copies of the Software, and to permit persons to whom the Software is furnished to do so, subject to the following conditions:'
  17. echo
  18. echo 'The above copyright notice and this permission notice shall be included in all copies or substantial portions of the Software.'
  19. echo
  20. echo 'THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.'
  21. }
  22. Help()
  23. {
  24. # Display Help
  25. echo "This script performs database functions for the Experimental Publishing Compendium"
  26. echo
  27. echo "Syntax: database_functions.sh [-l|h|e|i|c|v|d]"
  28. echo "options:"
  29. echo "l Print the MIT License notification."
  30. echo "h Print this Help."
  31. echo "e Export whole database."
  32. echo "i Import whole database."
  33. echo "c Export single table as tab-delimited txt."
  34. echo "v Import tab-delimited txt file to table."
  35. echo "d Drop table."
  36. echo
  37. }
  38. Export()
  39. {
  40. docker exec -it $CONTAINER mysqldump --single-transaction -u $USERNAME -p$PASSWORD $DATABASE > $EXPORT_DIRECTORY/$EXPORT_SQL_FILENAME`date +"%Y%m%d"`.sql
  41. }
  42. Import()
  43. {
  44. docker exec -i $CONTAINER mysql -u $USERNAME -p$PASSWORD $DATABASE < $IMPORT_SQL_FILE
  45. }
  46. Table_export()
  47. {
  48. docker exec -it $CONTAINER bash -c "mysql -u $USERNAME -p$PASSWORD $DATABASE --batch -e 'SELECT * FROM $TABLE'" > $EXPORT_TXT_DIRECTORY/$EXPORT_TXT_FILENAME.txt
  49. }
  50. Table_import()
  51. {
  52. docker cp $IMPORT_TXT_FILE $CONTAINER:/tmp/import_file
  53. docker exec -i $CONTAINER bash -c "mysql -u $USERNAME -p$PASSWORD $DATABASE -e 'LOAD DATA LOCAL INFILE '\''/tmp/import_file'\'' REPLACE INTO TABLE $TABLE FIELDS TERMINATED BY '\''\t'\'' LINES TERMINATED BY '\''\r'\'' IGNORE 1 ROWS;'"
  54. }
  55. Drop_table()
  56. {
  57. docker exec -i $CONTAINER bash -c "mysql -u $USERNAME -p$PASSWORD $DATABASE -e 'DROP TABLE IF EXISTS $TABLE;'"
  58. }
  59. ############################################################
  60. ############################################################
  61. # main program #
  62. ############################################################
  63. ############################################################
  64. # set variables
  65. CONTAINER=mariadb
  66. DATABASE=compendium
  67. USERNAME=xxxxxxxx
  68. PASSWORD=xxxxxxxx
  69. EXPORT_DIRECTORY="./db_exports"
  70. EXPORT_SQL_FILENAME=compendium_db_
  71. EXPORT_TXT_FILENAME=$2`date +"%Y%m%d"`
  72. # error message for no flags
  73. if (( $# == 0 )); then
  74. Help
  75. exit 1
  76. fi
  77. # get the options
  78. while getopts ":hleicvd" flag; do
  79. case $flag in
  80. l) # display License
  81. License
  82. exit;;
  83. h) # display Help
  84. Help
  85. exit;;
  86. e) # export whole database
  87. Export
  88. exit;;
  89. i) # import database from file
  90. if [ -z "$2" ]
  91. then
  92. echo "-i requires a file as an argument"
  93. echo
  94. echo "Syntax: database_functions.sh -i [file]"
  95. else
  96. IMPORT_SQL_FILE=$2
  97. Import
  98. exit 1
  99. fi;;
  100. c) # export single table as tab-delimited txt
  101. if [ -z "$2" ]
  102. then
  103. echo "-c requires a table name as an argument"
  104. echo
  105. echo "Syntax: database_functions.sh -c [table name]"
  106. else
  107. TABLE=$2
  108. Table_export
  109. exit 1
  110. fi;;
  111. v) # import single tab-delimited txt to table
  112. if [ -z "$2" ] || [ -z "$3" ]
  113. then
  114. echo "-v requires a table name as an argument and the file to be imported"
  115. echo
  116. echo "Syntax: database_functions.sh -v [table name] [file]"
  117. else
  118. TABLE=$2
  119. IMPORT_TXT_FILE=$3
  120. Table_import
  121. exit 1
  122. fi;;
  123. d) # drop table
  124. if [ -z "$2" ]
  125. then
  126. echo "-d requires a table name as an argument"
  127. echo
  128. echo "Syntax: database_functions.sh -d [table name]"
  129. else
  130. TABLE=$2
  131. Drop_table
  132. exit 1
  133. fi;;
  134. \?) # Invalid option
  135. Help
  136. exit;;
  137. esac
  138. done