We are currently solving the following issue within our repository. Here is the issue text:
--- BEGIN ISSUE ---
Repair biotimesql
define NULL

--- END ISSUE ---

Below are some code segments, each from a relevant file. One or more of these files may contain bugs.
--- BEGIN FILES ---
Path: `scripts/biotimesql.py`
Content:
```
1 # -*- coding: utf-8 -*-
2 #retriever
3 
4 import csv
5 from pkg_resources import parse_version
6 
7 from retriever.lib.models import Table
8 from retriever.lib.templates import Script
9 
10 try:
11     from retriever.lib.defaults import VERSION
12 
13     try:
14         from retriever.lib.tools import open_fr, open_fw, open_csvw
15     except ImportError:
16         from retriever.lib.scripts import open_fr, open_fw
17 except ImportError:
18     from retriever import open_fr, open_fw, VERSION
19 
20 
21 class main(Script):
22     def __init__(self, **kwargs):
23         Script.__init__(self, **kwargs)
24         self.title = "Commercial Fisheries Monthly Trade Data by Product, Country/Association"
25         self.name = "biotimesql"
26         self.retriever_minimum_version = "2.2.0"
27         self.urls = {
28             "sql_file": "https://zenodo.org/record/2602708/files/BioTIMESQL02_04_2018.sql?download=1",
29         }
30         self.version = "1.0.0"
31         self.ref = "https://zenodo.org/record/1095628#.WskN7dPwYyn"
32         self.citation = "Dornelas M, Antão LH, Moyes F, et al. BioTIME: A database of biodiversity time series for the Anthropocene. Global Ecology & Biogeography. 2018; 00:1 - 26. https://doi.org/10.1111/geb.12729."
33         self.description = "The BioTIME database has species identities and abundances in ecological assemblages through time."
34         self.keywords = ["Time series", "Anthropocene", "Global"]
35         self.licenses = [{"name": "CC BY 4.0"}]
36         self.encoding = "latin1"
37 
38         if parse_version(VERSION) <= parse_version("2.0.0"):
39             self.shortname = self.name
40             self.name = self.title
41             self.tags = self.keywords
42 
43     def download(self, engine=None, debug=False):
44         Script.download(self, engine, debug)
45         engine = self.engine
46         original_sql_file = "BioTIMESQL02_04_2018.sql"
47         engine.download_file(self.urls["sql_file"], original_sql_file)
48         sql_data = open_fr(self.engine.format_filename(original_sql_file))
49 
50         set_open = False
51         csv_writer = None
52         csv_file = None
53         table_name = None
54         for line in sql_data:
55             table_indicator = "-- Table structure for table "
56             if line.startswith(table_indicator):
57                 st = line[len(table_indicator):].replace("`", "")
58                 table_name = st.strip()
59                 current_file_process = table_name
60                 current_file_open = current_file_process
61                 if set_open and not current_file_process == current_file_open:
62                     csv_file.close()
63                     set_open = False
64                 else:
65                     out_file = "{name}.csv".format(name=table_name)
66                     csv_file = open_fw(engine.format_filename(out_file))
67                     csv_writer = csv.writer(csv_file, quoting=csv.QUOTE_ALL)
68                     set_open = True
69 
70             if line.startswith("INSERT INTO `{table_name}`".format(table_name=table_name)):
71                 row_val = line[line.index("VALUES (") + 8:-3]
72                 table_rows = row_val.replace("\r\n","").split("),(")
73                 for i_row in table_rows:
74                     v = eval('[' + str(i_row) + ']')
75                     csv_writer.writerows([v])
76         if csv_file:
77             csv_file.close()
78 
79         # Create abundance table
80         table = Table("ID_ABUNDANCE", delimiter=",", header_rows=0, contains_pk=False)
81         table.columns = [
82             ("ID_ABUNDANCE", ("int",)),
83             ("ABUNDANCE_TYPE", ("char", "100")),
84         ]
85         engine.table = table
86         engine.create_table()
87         engine.insert_data_from_file(engine.format_filename("abundance.csv"))
88 
89         # Create allrawdata table
90         table = Table("allrawdata", delimiter=",", header_rows=0, contains_pk=False)
91         table.columns = [
92             ("ID_ALL_RAW_DATA", ("int",)),
93             ("ABUNDANCE", ("double",)),
94             ("BIOMASS", ("double",)),
95             ("ID_SPECIES", ("int",)),
96             ("SAMPLE_DESC", ("char", 200)),
97             ("PLOT", ("char", 150)),
98             ("LATITUDE", ("double",)),
99             ("LONGITUDE", ("double",)),
100             ("DEPTH", ("double",)),
101             ("DAY", ("int",)),
102             ("MONTH", ("int",)),
103             ("YEAR", ("int",)),
104             ("STUDY_ID", ("int",)),
105         ]
106         engine.table = table
107         engine.create_table()
108         engine.insert_data_from_file(engine.format_filename("allrawdata.csv"))
109 
110         # Create biomass table
111         table = Table("biomass", delimiter=",", header_rows=0, contains_pk=False)
112         table.columns = [("ID_BIOMASS", ("int",)), ("BIOMASS_TYPE", ("char", "100"))]
113         engine.table = table
114         engine.create_table()
115         engine.insert_data_from_file(engine.format_filename("biomass.csv"))
116 
117         # Create citation1 table
118         table = Table("citation1", delimiter=",", header_rows=0, contains_pk=False)
119         table.columns = [
120             ("ID_CITATION1", ("int",)),
121             ("STUDY_ID", ("int",)),
122             ("CITATION_LINE", ("char",)),
123         ]
124         engine.table = table
125         engine.create_table()
126         engine.insert_data_from_file(engine.format_filename("citation1.csv"))
127 
128         # Create contacts table
129         table = Table("contacts", delimiter=",", header_rows=0, contains_pk=False)
130         table.columns = [
131             ("ID_CONTACTS", ("int",)),
132             ("STUDY_ID", ("int",)),
133             ("CONTACT_1", ("char", 500)),
134             ("CONTACT_2", ("char", 500)),
135             ("CONT_1_MAIL", ("char", 60)),
136             ("CONT_2_MAIL", ("char", 60)),
137             ("LICENSE", ("char", 200)),
138             ("WEB_LINK", ("char", 200)),
139             ("DATA_SOURCE", ("char", 250)),
140         ]
141         engine.table = table
142         engine.create_table()
143         engine.insert_data_from_file(engine.format_filename("contacts.csv"))
144 
145         # Create countries table
146         table = Table("countries", delimiter=",", header_rows=0, contains_pk=False)
147         table.columns = [("COUNT_ID", ("int",)), ("COUNTRY_NAME", ("char", 200))]
148         engine.table = table
149         engine.create_table()
150         engine.insert_data_from_file(engine.format_filename("countries.csv"))
151 
152         # Create curation table
153         table = Table("curation", delimiter=",", header_rows=0, contains_pk=False)
154         table.columns = [
155             ("ID_CURATION", ("int",)),
156             ("STUDY_ID", ("int",)),
157             ("LINK_ID", ("int",)),
158             ("COMMENTS", ("char",)),
159             ("DATE_STUDY_ADDED", ("char", 50)),
160         ]
161         engine.table = table
162         engine.create_table()
163         engine.insert_data_from_file(engine.format_filename("curation.csv"))
164 
165         # Create datasets table
166         table = Table("datasets", delimiter=",", header_rows=0, contains_pk=False)
167         table.columns = [
168             ("ID_DATASETS", ("int",)),
169             ("STUDY_ID", ("int",)),
170             ("TAXA", ("char", 50)),
171             ("ORGANISMS", ("char", 200)),
172             ("TITLE", ("char",800)),
173             ("AB_BIO", ("char", 2)),
174             ("HAS_PLOT", ("char", 10)),
175             ("DATA_POINTS", ("char",)),
176             ("START_YEAR", ("char",)),
177             ("END_YEAR", ("char",)),
178             ("CENT_LAT", ("double",)),
179             ("CENT_LONG", ("double",)),
180             ("NUMBER_OF_SPECIES", ("char",)),
181             ("NUMBER_OF_SAMPLES", ("char",)),
182             ("NUMBER_LAT_LONG", ("char",)),
183             ("TOTAL", ("char",)),
184             ("GRAIN_SIZE_TEXT", ("char",)),
185             ("GRAIN_SQ_KM", ("double",)),
186             ("AREA_SQ_KM", ("double",)),
187             ("AB_TYPE", ("char", )),
188             ("BIO_TYPE", ("char",)),
189             ("SAMPLE_TYPE", ("char",)),
190         ]
191         engine.table = table
192         engine.create_table()
193         engine.insert_data_from_file(engine.format_filename("datasets.csv"))
194 
195         # Create downloads table
196         table = Table("downloads", delimiter=",", header_rows=0, contains_pk=False)
197         table.columns = [
198             ("D_ID", ("int",)),
199             ("STUDY", ("char", 25)),
200             ("NAME", ("char", 150)),
201             ("EMAIL", ("char", 150)),
202             ("COUNTRY", ("char", 200)),
203             ("ROLE", ("char", 150)),
204             ("PURPOSE", ("char", 500)),
205             ("LOCATION", ("char", 250)),
206             ("DATE_STAMP", ("char",)),
207         ]
208         engine.table = table
209         engine.create_table()
210         engine.insert_data_from_file(engine.format_filename("downloads.csv"))
211 
212         # Create methods table
213         table = Table("methods", delimiter=",", header_rows=0, contains_pk=False)
214         table.columns = [
215             ("ID_METHODS", ("int",)),
216             ("STUDY_ID", ("int",)),
217             ("METHODS", ("char",)),
218             ("SUMMARY_METHODS", ("char", 500)),
219         ]
220         engine.table = table
221         engine.create_table()
222         engine.insert_data_from_file(engine.format_filename("methods.csv"))
223 
224         # Create sample table
225         table = Table("sample", delimiter=",", header_rows=0, contains_pk=False)
226         table.columns = [
227             ("ID_SAMPLE", ("int",)),
228             ("ID_TREAT", ("int",)),
229             ("SAMPLE_DESC_NAME", ("char", 200)),
230         ]
231         engine.table = table
232         engine.create_table()
233         engine.insert_data_from_file(engine.format_filename("sample.csv"))
234 
235         # Create site table
236         table = Table("site", delimiter=",", header_rows=0, contains_pk=False)
237         table.columns = [
238             ("ID_SITE", ("int",)),
239             ("STUDY_ID", ("int",)),
240             ("REALM", ("char", 11)),
241             ("CLIMATE", ("char", 20)),
242             ("GENERAL_TREAT", ("char", 200)),
243             ("TREATMENT", ("char", 200)),
244             ("TREAT_COMMENTS", ("char", 250)),
245             ("TREAT_DATE", ("char", 100)),
246             ("CEN_LATITUDE", ("double",)),
247             ("CEN_LONGITUDE", ("double",)),
248             ("HABITAT", ("char", 100)),
249             ("PROTECTED_AREA", ("char", 50)),
250             ("AREA", ("double",)),
251             ("BIOME_MAP", ("char", 500))
252         ]
253         engine.table = table
254         engine.create_table()
255         engine.insert_data_from_file(engine.format_filename("site.csv"))
256 
257         # Create species table
258         table = Table("species", delimiter=",", header_rows=0, contains_pk=False)
259         table.columns = [
260             ("ID_SPECIES", ("int",)),
261             ("GENUS", ("char", 100)),
262             ("SPECIES", ("char", 100)),
263             ("GENUS_SPECIES", ("char", 100))
264         ]
265         engine.table = table
266         engine.create_table()
267         engine.insert_data_from_file(engine.format_filename("species.csv"))
268 
269 
270 SCRIPT = main()
271
```
--- END FILES ---

Please first localize the bug based on the issue statement, and then generate a patch according to the `git diff` format fenced by three backticks.

Here is an example:

```diff
diff --git a/examples/server_async.py b/examples/server_async.py
--- a/examples/server_async.py
+++ b/examples/server_async.py
@@ -313,4 +313,4 @@
 
 
 if __name__ == "__main__":
-    asyncio.run(run_async_server("."), debug=True)
+    asyncio.run(run_async_server(), debug=True)
diff --git a/examples/server_sync.py b/examples/server_sync.py
--- a/examples/server_sync.py
+++ b/examples/server_sync.py
@@ -313,5 +313,5 @@
 
 
 if __name__ == "__main__":
-    server = run_sync_server(".")
+    server = run_sync_server()
     server.shutdown()

```

diff --git a/scripts/biotimesql.py b/scripts/biotimesql.py
--- a/scripts/biotimesql.py
+++ b/scripts/biotimesql.py
@@ -27,7 +27,7 @@
         self.urls = {
             "sql_file": "https://zenodo.org/record/2602708/files/BioTIMESQL02_04_2018.sql?download=1",
         }
-        self.version = "1.0.0"
+        self.version = "1.0.1"
         self.ref = "https://zenodo.org/record/1095628#.WskN7dPwYyn"
         self.citation = "Dornelas M, Antão LH, Moyes F, et al. BioTIME: A database of biodiversity time series for the Anthropocene. Global Ecology & Biogeography. 2018; 00:1 - 26. https://doi.org/10.1111/geb.12729."
         self.description = "The BioTIME database has species identities and abundances in ecological assemblages through time."
@@ -51,6 +51,7 @@
         csv_writer = None
         csv_file = None
         table_name = None
+        NULL = None
         for line in sql_data:
             table_indicator = "-- Table structure for table "
             if line.startswith(table_indicator):

{"golden_diff": "diff --git a/scripts/biotimesql.py b/scripts/biotimesql.py\n--- a/scripts/biotimesql.py\n+++ b/scripts/biotimesql.py\n@@ -27,7 +27,7 @@\n         self.urls = {\n             \"sql_file\": \"https://zenodo.org/record/2602708/files/BioTIMESQL02_04_2018.sql?download=1\",\n         }\n-        self.version = \"1.0.0\"\n+        self.version = \"1.0.1\"\n         self.ref = \"https://zenodo.org/record/1095628#.WskN7dPwYyn\"\n         self.citation = \"Dornelas M, Ant\u00e3o LH, Moyes F, et al. BioTIME: A database of biodiversity time series for the Anthropocene. Global Ecology & Biogeography. 2018; 00:1 - 26. https://doi.org/10.1111/geb.12729.\"\n         self.description = \"The BioTIME database has species identities and abundances in ecological assemblages through time.\"\n@@ -51,6 +51,7 @@\n         csv_writer = None\n         csv_file = None\n         table_name = None\n+        NULL = None\n         for line in sql_data:\n             table_indicator = \"-- Table structure for table \"\n             if line.startswith(table_indicator):\n", "issue": "Repair biotimesql\ndefine NULL\n", "before_files": [{"content": "# -*- coding: utf-8 -*-\n#retriever\n\nimport csv\nfrom pkg_resources import parse_version\n\nfrom retriever.lib.models import Table\nfrom retriever.lib.templates import Script\n\ntry:\n    from retriever.lib.defaults import VERSION\n\n    try:\n        from retriever.lib.tools import open_fr, open_fw, open_csvw\n    except ImportError:\n        from retriever.lib.scripts import open_fr, open_fw\nexcept ImportError:\n    from retriever import open_fr, open_fw, VERSION\n\n\nclass main(Script):\n    def __init__(self, **kwargs):\n        Script.__init__(self, **kwargs)\n        self.title = \"Commercial Fisheries Monthly Trade Data by Product, Country/Association\"\n        self.name = \"biotimesql\"\n        self.retriever_minimum_version = \"2.2.0\"\n        self.urls = {\n            \"sql_file\": \"https://zenodo.org/record/2602708/files/BioTIMESQL02_04_2018.sql?download=1\",\n        }\n        self.version = \"1.0.0\"\n        self.ref = \"https://zenodo.org/record/1095628#.WskN7dPwYyn\"\n        self.citation = \"Dornelas M, Ant\u00e3o LH, Moyes F, et al. BioTIME: A database of biodiversity time series for the Anthropocene. Global Ecology & Biogeography. 2018; 00:1 - 26. https://doi.org/10.1111/geb.12729.\"\n        self.description = \"The BioTIME database has species identities and abundances in ecological assemblages through time.\"\n        self.keywords = [\"Time series\", \"Anthropocene\", \"Global\"]\n        self.licenses = [{\"name\": \"CC BY 4.0\"}]\n        self.encoding = \"latin1\"\n\n        if parse_version(VERSION) <= parse_version(\"2.0.0\"):\n            self.shortname = self.name\n            self.name = self.title\n            self.tags = self.keywords\n\n    def download(self, engine=None, debug=False):\n        Script.download(self, engine, debug)\n        engine = self.engine\n        original_sql_file = \"BioTIMESQL02_04_2018.sql\"\n        engine.download_file(self.urls[\"sql_file\"], original_sql_file)\n        sql_data = open_fr(self.engine.format_filename(original_sql_file))\n\n        set_open = False\n        csv_writer = None\n        csv_file = None\n        table_name = None\n        for line in sql_data:\n            table_indicator = \"-- Table structure for table \"\n            if line.startswith(table_indicator):\n                st = line[len(table_indicator):].replace(\"`\", \"\")\n                table_name = st.strip()\n                current_file_process = table_name\n                current_file_open = current_file_process\n                if set_open and not current_file_process == current_file_open:\n                    csv_file.close()\n                    set_open = False\n                else:\n                    out_file = \"{name}.csv\".format(name=table_name)\n                    csv_file = open_fw(engine.format_filename(out_file))\n                    csv_writer = csv.writer(csv_file, quoting=csv.QUOTE_ALL)\n                    set_open = True\n\n            if line.startswith(\"INSERT INTO `{table_name}`\".format(table_name=table_name)):\n                row_val = line[line.index(\"VALUES (\") + 8:-3]\n                table_rows = row_val.replace(\"\\r\\n\",\"\").split(\"),(\")\n                for i_row in table_rows:\n                    v = eval('[' + str(i_row) + ']')\n                    csv_writer.writerows([v])\n        if csv_file:\n            csv_file.close()\n\n        # Create abundance table\n        table = Table(\"ID_ABUNDANCE\", delimiter=\",\", header_rows=0, contains_pk=False)\n        table.columns = [\n            (\"ID_ABUNDANCE\", (\"int\",)),\n            (\"ABUNDANCE_TYPE\", (\"char\", \"100\")),\n        ]\n        engine.table = table\n        engine.create_table()\n        engine.insert_data_from_file(engine.format_filename(\"abundance.csv\"))\n\n        # Create allrawdata table\n        table = Table(\"allrawdata\", delimiter=\",\", header_rows=0, contains_pk=False)\n        table.columns = [\n            (\"ID_ALL_RAW_DATA\", (\"int\",)),\n            (\"ABUNDANCE\", (\"double\",)),\n            (\"BIOMASS\", (\"double\",)),\n            (\"ID_SPECIES\", (\"int\",)),\n            (\"SAMPLE_DESC\", (\"char\", 200)),\n            (\"PLOT\", (\"char\", 150)),\n            (\"LATITUDE\", (\"double\",)),\n            (\"LONGITUDE\", (\"double\",)),\n            (\"DEPTH\", (\"double\",)),\n            (\"DAY\", (\"int\",)),\n            (\"MONTH\", (\"int\",)),\n            (\"YEAR\", (\"int\",)),\n            (\"STUDY_ID\", (\"int\",)),\n        ]\n        engine.table = table\n        engine.create_table()\n        engine.insert_data_from_file(engine.format_filename(\"allrawdata.csv\"))\n\n        # Create biomass table\n        table = Table(\"biomass\", delimiter=\",\", header_rows=0, contains_pk=False)\n        table.columns = [(\"ID_BIOMASS\", (\"int\",)), (\"BIOMASS_TYPE\", (\"char\", \"100\"))]\n        engine.table = table\n        engine.create_table()\n        engine.insert_data_from_file(engine.format_filename(\"biomass.csv\"))\n\n        # Create citation1 table\n        table = Table(\"citation1\", delimiter=\",\", header_rows=0, contains_pk=False)\n        table.columns = [\n            (\"ID_CITATION1\", (\"int\",)),\n            (\"STUDY_ID\", (\"int\",)),\n            (\"CITATION_LINE\", (\"char\",)),\n        ]\n        engine.table = table\n        engine.create_table()\n        engine.insert_data_from_file(engine.format_filename(\"citation1.csv\"))\n\n        # Create contacts table\n        table = Table(\"contacts\", delimiter=\",\", header_rows=0, contains_pk=False)\n        table.columns = [\n            (\"ID_CONTACTS\", (\"int\",)),\n            (\"STUDY_ID\", (\"int\",)),\n            (\"CONTACT_1\", (\"char\", 500)),\n            (\"CONTACT_2\", (\"char\", 500)),\n            (\"CONT_1_MAIL\", (\"char\", 60)),\n            (\"CONT_2_MAIL\", (\"char\", 60)),\n            (\"LICENSE\", (\"char\", 200)),\n            (\"WEB_LINK\", (\"char\", 200)),\n            (\"DATA_SOURCE\", (\"char\", 250)),\n        ]\n        engine.table = table\n        engine.create_table()\n        engine.insert_data_from_file(engine.format_filename(\"contacts.csv\"))\n\n        # Create countries table\n        table = Table(\"countries\", delimiter=\",\", header_rows=0, contains_pk=False)\n        table.columns = [(\"COUNT_ID\", (\"int\",)), (\"COUNTRY_NAME\", (\"char\", 200))]\n        engine.table = table\n        engine.create_table()\n        engine.insert_data_from_file(engine.format_filename(\"countries.csv\"))\n\n        # Create curation table\n        table = Table(\"curation\", delimiter=\",\", header_rows=0, contains_pk=False)\n        table.columns = [\n            (\"ID_CURATION\", (\"int\",)),\n            (\"STUDY_ID\", (\"int\",)),\n            (\"LINK_ID\", (\"int\",)),\n            (\"COMMENTS\", (\"char\",)),\n            (\"DATE_STUDY_ADDED\", (\"char\", 50)),\n        ]\n        engine.table = table\n        engine.create_table()\n        engine.insert_data_from_file(engine.format_filename(\"curation.csv\"))\n\n        # Create datasets table\n        table = Table(\"datasets\", delimiter=\",\", header_rows=0, contains_pk=False)\n        table.columns = [\n            (\"ID_DATASETS\", (\"int\",)),\n            (\"STUDY_ID\", (\"int\",)),\n            (\"TAXA\", (\"char\", 50)),\n            (\"ORGANISMS\", (\"char\", 200)),\n            (\"TITLE\", (\"char\",800)),\n            (\"AB_BIO\", (\"char\", 2)),\n            (\"HAS_PLOT\", (\"char\", 10)),\n            (\"DATA_POINTS\", (\"char\",)),\n            (\"START_YEAR\", (\"char\",)),\n            (\"END_YEAR\", (\"char\",)),\n            (\"CENT_LAT\", (\"double\",)),\n            (\"CENT_LONG\", (\"double\",)),\n            (\"NUMBER_OF_SPECIES\", (\"char\",)),\n            (\"NUMBER_OF_SAMPLES\", (\"char\",)),\n            (\"NUMBER_LAT_LONG\", (\"char\",)),\n            (\"TOTAL\", (\"char\",)),\n            (\"GRAIN_SIZE_TEXT\", (\"char\",)),\n            (\"GRAIN_SQ_KM\", (\"double\",)),\n            (\"AREA_SQ_KM\", (\"double\",)),\n            (\"AB_TYPE\", (\"char\", )),\n            (\"BIO_TYPE\", (\"char\",)),\n            (\"SAMPLE_TYPE\", (\"char\",)),\n        ]\n        engine.table = table\n        engine.create_table()\n        engine.insert_data_from_file(engine.format_filename(\"datasets.csv\"))\n\n        # Create downloads table\n        table = Table(\"downloads\", delimiter=\",\", header_rows=0, contains_pk=False)\n        table.columns = [\n            (\"D_ID\", (\"int\",)),\n            (\"STUDY\", (\"char\", 25)),\n            (\"NAME\", (\"char\", 150)),\n            (\"EMAIL\", (\"char\", 150)),\n            (\"COUNTRY\", (\"char\", 200)),\n            (\"ROLE\", (\"char\", 150)),\n            (\"PURPOSE\", (\"char\", 500)),\n            (\"LOCATION\", (\"char\", 250)),\n            (\"DATE_STAMP\", (\"char\",)),\n        ]\n        engine.table = table\n        engine.create_table()\n        engine.insert_data_from_file(engine.format_filename(\"downloads.csv\"))\n\n        # Create methods table\n        table = Table(\"methods\", delimiter=\",\", header_rows=0, contains_pk=False)\n        table.columns = [\n            (\"ID_METHODS\", (\"int\",)),\n            (\"STUDY_ID\", (\"int\",)),\n            (\"METHODS\", (\"char\",)),\n            (\"SUMMARY_METHODS\", (\"char\", 500)),\n        ]\n        engine.table = table\n        engine.create_table()\n        engine.insert_data_from_file(engine.format_filename(\"methods.csv\"))\n\n        # Create sample table\n        table = Table(\"sample\", delimiter=\",\", header_rows=0, contains_pk=False)\n        table.columns = [\n            (\"ID_SAMPLE\", (\"int\",)),\n            (\"ID_TREAT\", (\"int\",)),\n            (\"SAMPLE_DESC_NAME\", (\"char\", 200)),\n        ]\n        engine.table = table\n        engine.create_table()\n        engine.insert_data_from_file(engine.format_filename(\"sample.csv\"))\n\n        # Create site table\n        table = Table(\"site\", delimiter=\",\", header_rows=0, contains_pk=False)\n        table.columns = [\n            (\"ID_SITE\", (\"int\",)),\n            (\"STUDY_ID\", (\"int\",)),\n            (\"REALM\", (\"char\", 11)),\n            (\"CLIMATE\", (\"char\", 20)),\n            (\"GENERAL_TREAT\", (\"char\", 200)),\n            (\"TREATMENT\", (\"char\", 200)),\n            (\"TREAT_COMMENTS\", (\"char\", 250)),\n            (\"TREAT_DATE\", (\"char\", 100)),\n            (\"CEN_LATITUDE\", (\"double\",)),\n            (\"CEN_LONGITUDE\", (\"double\",)),\n            (\"HABITAT\", (\"char\", 100)),\n            (\"PROTECTED_AREA\", (\"char\", 50)),\n            (\"AREA\", (\"double\",)),\n            (\"BIOME_MAP\", (\"char\", 500))\n        ]\n        engine.table = table\n        engine.create_table()\n        engine.insert_data_from_file(engine.format_filename(\"site.csv\"))\n\n        # Create species table\n        table = Table(\"species\", delimiter=\",\", header_rows=0, contains_pk=False)\n        table.columns = [\n            (\"ID_SPECIES\", (\"int\",)),\n            (\"GENUS\", (\"char\", 100)),\n            (\"SPECIES\", (\"char\", 100)),\n            (\"GENUS_SPECIES\", (\"char\", 100))\n        ]\n        engine.table = table\n        engine.create_table()\n        engine.insert_data_from_file(engine.format_filename(\"species.csv\"))\n\n\nSCRIPT = main()\n", "path": "scripts/biotimesql.py"}], "after_files": [{"content": "# -*- coding: utf-8 -*-\n#retriever\n\nimport csv\nfrom pkg_resources import parse_version\n\nfrom retriever.lib.models import Table\nfrom retriever.lib.templates import Script\n\ntry:\n    from retriever.lib.defaults import VERSION\n\n    try:\n        from retriever.lib.tools import open_fr, open_fw, open_csvw\n    except ImportError:\n        from retriever.lib.scripts import open_fr, open_fw\nexcept ImportError:\n    from retriever import open_fr, open_fw, VERSION\n\n\nclass main(Script):\n    def __init__(self, **kwargs):\n        Script.__init__(self, **kwargs)\n        self.title = \"Commercial Fisheries Monthly Trade Data by Product, Country/Association\"\n        self.name = \"biotimesql\"\n        self.retriever_minimum_version = \"2.2.0\"\n        self.urls = {\n            \"sql_file\": \"https://zenodo.org/record/2602708/files/BioTIMESQL02_04_2018.sql?download=1\",\n        }\n        self.version = \"1.0.1\"\n        self.ref = \"https://zenodo.org/record/1095628#.WskN7dPwYyn\"\n        self.citation = \"Dornelas M, Ant\u00e3o LH, Moyes F, et al. BioTIME: A database of biodiversity time series for the Anthropocene. Global Ecology & Biogeography. 2018; 00:1 - 26. https://doi.org/10.1111/geb.12729.\"\n        self.description = \"The BioTIME database has species identities and abundances in ecological assemblages through time.\"\n        self.keywords = [\"Time series\", \"Anthropocene\", \"Global\"]\n        self.licenses = [{\"name\": \"CC BY 4.0\"}]\n        self.encoding = \"latin1\"\n\n        if parse_version(VERSION) <= parse_version(\"2.0.0\"):\n            self.shortname = self.name\n            self.name = self.title\n            self.tags = self.keywords\n\n    def download(self, engine=None, debug=False):\n        Script.download(self, engine, debug)\n        engine = self.engine\n        original_sql_file = \"BioTIMESQL02_04_2018.sql\"\n        engine.download_file(self.urls[\"sql_file\"], original_sql_file)\n        sql_data = open_fr(self.engine.format_filename(original_sql_file))\n\n        set_open = False\n        csv_writer = None\n        csv_file = None\n        table_name = None\n        NULL = None\n        for line in sql_data:\n            table_indicator = \"-- Table structure for table \"\n            if line.startswith(table_indicator):\n                st = line[len(table_indicator):].replace(\"`\", \"\")\n                table_name = st.strip()\n                current_file_process = table_name\n                current_file_open = current_file_process\n                if set_open and not current_file_process == current_file_open:\n                    csv_file.close()\n                    set_open = False\n                else:\n                    out_file = \"{name}.csv\".format(name=table_name)\n                    csv_file = open_fw(engine.format_filename(out_file))\n                    csv_writer = csv.writer(csv_file, quoting=csv.QUOTE_ALL)\n                    set_open = True\n\n            if line.startswith(\"INSERT INTO `{table_name}`\".format(table_name=table_name)):\n                row_val = line[line.index(\"VALUES (\") + 8:-3]\n                table_rows = row_val.replace(\"\\r\\n\",\"\").split(\"),(\")\n                for i_row in table_rows:\n                    v = eval('[' + str(i_row) + ']')\n                    csv_writer.writerows([v])\n        if csv_file:\n            csv_file.close()\n\n        # Create abundance table\n        table = Table(\"ID_ABUNDANCE\", delimiter=\",\", header_rows=0, contains_pk=False)\n        table.columns = [\n            (\"ID_ABUNDANCE\", (\"int\",)),\n            (\"ABUNDANCE_TYPE\", (\"char\", \"100\")),\n        ]\n        engine.table = table\n        engine.create_table()\n        engine.insert_data_from_file(engine.format_filename(\"abundance.csv\"))\n\n        # Create allrawdata table\n        table = Table(\"allrawdata\", delimiter=\",\", header_rows=0, contains_pk=False)\n        table.columns = [\n            (\"ID_ALL_RAW_DATA\", (\"int\",)),\n            (\"ABUNDANCE\", (\"double\",)),\n            (\"BIOMASS\", (\"double\",)),\n            (\"ID_SPECIES\", (\"int\",)),\n            (\"SAMPLE_DESC\", (\"char\", 200)),\n            (\"PLOT\", (\"char\", 150)),\n            (\"LATITUDE\", (\"double\",)),\n            (\"LONGITUDE\", (\"double\",)),\n            (\"DEPTH\", (\"double\",)),\n            (\"DAY\", (\"int\",)),\n            (\"MONTH\", (\"int\",)),\n            (\"YEAR\", (\"int\",)),\n            (\"STUDY_ID\", (\"int\",)),\n        ]\n        engine.table = table\n        engine.create_table()\n        engine.insert_data_from_file(engine.format_filename(\"allrawdata.csv\"))\n\n        # Create biomass table\n        table = Table(\"biomass\", delimiter=\",\", header_rows=0, contains_pk=False)\n        table.columns = [(\"ID_BIOMASS\", (\"int\",)), (\"BIOMASS_TYPE\", (\"char\", \"100\"))]\n        engine.table = table\n        engine.create_table()\n        engine.insert_data_from_file(engine.format_filename(\"biomass.csv\"))\n\n        # Create citation1 table\n        table = Table(\"citation1\", delimiter=\",\", header_rows=0, contains_pk=False)\n        table.columns = [\n            (\"ID_CITATION1\", (\"int\",)),\n            (\"STUDY_ID\", (\"int\",)),\n            (\"CITATION_LINE\", (\"char\",)),\n        ]\n        engine.table = table\n        engine.create_table()\n        engine.insert_data_from_file(engine.format_filename(\"citation1.csv\"))\n\n        # Create contacts table\n        table = Table(\"contacts\", delimiter=\",\", header_rows=0, contains_pk=False)\n        table.columns = [\n            (\"ID_CONTACTS\", (\"int\",)),\n            (\"STUDY_ID\", (\"int\",)),\n            (\"CONTACT_1\", (\"char\", 500)),\n            (\"CONTACT_2\", (\"char\", 500)),\n            (\"CONT_1_MAIL\", (\"char\", 60)),\n            (\"CONT_2_MAIL\", (\"char\", 60)),\n            (\"LICENSE\", (\"char\", 200)),\n            (\"WEB_LINK\", (\"char\", 200)),\n            (\"DATA_SOURCE\", (\"char\", 250)),\n        ]\n        engine.table = table\n        engine.create_table()\n        engine.insert_data_from_file(engine.format_filename(\"contacts.csv\"))\n\n        # Create countries table\n        table = Table(\"countries\", delimiter=\",\", header_rows=0, contains_pk=False)\n        table.columns = [(\"COUNT_ID\", (\"int\",)), (\"COUNTRY_NAME\", (\"char\", 200))]\n        engine.table = table\n        engine.create_table()\n        engine.insert_data_from_file(engine.format_filename(\"countries.csv\"))\n\n        # Create curation table\n        table = Table(\"curation\", delimiter=\",\", header_rows=0, contains_pk=False)\n        table.columns = [\n            (\"ID_CURATION\", (\"int\",)),\n            (\"STUDY_ID\", (\"int\",)),\n            (\"LINK_ID\", (\"int\",)),\n            (\"COMMENTS\", (\"char\",)),\n            (\"DATE_STUDY_ADDED\", (\"char\", 50)),\n        ]\n        engine.table = table\n        engine.create_table()\n        engine.insert_data_from_file(engine.format_filename(\"curation.csv\"))\n\n        # Create datasets table\n        table = Table(\"datasets\", delimiter=\",\", header_rows=0, contains_pk=False)\n        table.columns = [\n            (\"ID_DATASETS\", (\"int\",)),\n            (\"STUDY_ID\", (\"int\",)),\n            (\"TAXA\", (\"char\", 50)),\n            (\"ORGANISMS\", (\"char\", 200)),\n            (\"TITLE\", (\"char\",800)),\n            (\"AB_BIO\", (\"char\", 2)),\n            (\"HAS_PLOT\", (\"char\", 10)),\n            (\"DATA_POINTS\", (\"char\",)),\n            (\"START_YEAR\", (\"char\",)),\n            (\"END_YEAR\", (\"char\",)),\n            (\"CENT_LAT\", (\"double\",)),\n            (\"CENT_LONG\", (\"double\",)),\n            (\"NUMBER_OF_SPECIES\", (\"char\",)),\n            (\"NUMBER_OF_SAMPLES\", (\"char\",)),\n            (\"NUMBER_LAT_LONG\", (\"char\",)),\n            (\"TOTAL\", (\"char\",)),\n            (\"GRAIN_SIZE_TEXT\", (\"char\",)),\n            (\"GRAIN_SQ_KM\", (\"double\",)),\n            (\"AREA_SQ_KM\", (\"double\",)),\n            (\"AB_TYPE\", (\"char\", )),\n            (\"BIO_TYPE\", (\"char\",)),\n            (\"SAMPLE_TYPE\", (\"char\",)),\n        ]\n        engine.table = table\n        engine.create_table()\n        engine.insert_data_from_file(engine.format_filename(\"datasets.csv\"))\n\n        # Create downloads table\n        table = Table(\"downloads\", delimiter=\",\", header_rows=0, contains_pk=False)\n        table.columns = [\n            (\"D_ID\", (\"int\",)),\n            (\"STUDY\", (\"char\", 25)),\n            (\"NAME\", (\"char\", 150)),\n            (\"EMAIL\", (\"char\", 150)),\n            (\"COUNTRY\", (\"char\", 200)),\n            (\"ROLE\", (\"char\", 150)),\n            (\"PURPOSE\", (\"char\", 500)),\n            (\"LOCATION\", (\"char\", 250)),\n            (\"DATE_STAMP\", (\"char\",)),\n        ]\n        engine.table = table\n        engine.create_table()\n        engine.insert_data_from_file(engine.format_filename(\"downloads.csv\"))\n\n        # Create methods table\n        table = Table(\"methods\", delimiter=\",\", header_rows=0, contains_pk=False)\n        table.columns = [\n            (\"ID_METHODS\", (\"int\",)),\n            (\"STUDY_ID\", (\"int\",)),\n            (\"METHODS\", (\"char\",)),\n            (\"SUMMARY_METHODS\", (\"char\", 500)),\n        ]\n        engine.table = table\n        engine.create_table()\n        engine.insert_data_from_file(engine.format_filename(\"methods.csv\"))\n\n        # Create sample table\n        table = Table(\"sample\", delimiter=\",\", header_rows=0, contains_pk=False)\n        table.columns = [\n            (\"ID_SAMPLE\", (\"int\",)),\n            (\"ID_TREAT\", (\"int\",)),\n            (\"SAMPLE_DESC_NAME\", (\"char\", 200)),\n        ]\n        engine.table = table\n        engine.create_table()\n        engine.insert_data_from_file(engine.format_filename(\"sample.csv\"))\n\n        # Create site table\n        table = Table(\"site\", delimiter=\",\", header_rows=0, contains_pk=False)\n        table.columns = [\n            (\"ID_SITE\", (\"int\",)),\n            (\"STUDY_ID\", (\"int\",)),\n            (\"REALM\", (\"char\", 11)),\n            (\"CLIMATE\", (\"char\", 20)),\n            (\"GENERAL_TREAT\", (\"char\", 200)),\n            (\"TREATMENT\", (\"char\", 200)),\n            (\"TREAT_COMMENTS\", (\"char\", 250)),\n            (\"TREAT_DATE\", (\"char\", 100)),\n            (\"CEN_LATITUDE\", (\"double\",)),\n            (\"CEN_LONGITUDE\", (\"double\",)),\n            (\"HABITAT\", (\"char\", 100)),\n            (\"PROTECTED_AREA\", (\"char\", 50)),\n            (\"AREA\", (\"double\",)),\n            (\"BIOME_MAP\", (\"char\", 500))\n        ]\n        engine.table = table\n        engine.create_table()\n        engine.insert_data_from_file(engine.format_filename(\"site.csv\"))\n\n        # Create species table\n        table = Table(\"species\", delimiter=\",\", header_rows=0, contains_pk=False)\n        table.columns = [\n            (\"ID_SPECIES\", (\"int\",)),\n            (\"GENUS\", (\"char\", 100)),\n            (\"SPECIES\", (\"char\", 100)),\n            (\"GENUS_SPECIES\", (\"char\", 100))\n        ]\n        engine.table = table\n        engine.create_table()\n        engine.insert_data_from_file(engine.format_filename(\"species.csv\"))\n\n\nSCRIPT = main()\n", "path": "scripts/biotimesql.py"}]}