-
Notifications
You must be signed in to change notification settings - Fork 0
Dump from MS Access Database
The Northwestern Bat Hub currently maintains a Microsoft Access database containing all of the data to be moved to Batabase. Since the Access database is continually receiving new data we need a solution in which we can continually spin up new Batabase databases from a db dump from the Access database.
The tool we used to create the dump from the Access database is Bullzip.
Once you have obtained the dump you can create a local Postgres database:
- Drop database if currently exists
psql -d postgres -c "DROP DATABASE batabase_development"
- Create fresh db
createdb batabase_development
- Execute dump on new database
psql -d batabase_development -f dump.sql
🎉You now have a Postgres copy of the MS Access database.
The Batabase db has significant differences to the original Access db. Tables and columns have been removed or renamed either at the client's request or to reflect Ruby on Rails conventions. The changes the client requested can be viewed in this doc https://public.3.basecamp.com/p/jsp4CgHjNvkToqkJBVyebwpE in Basecamp.
To ensure we can update the db reliably every time we have created a SQL script to execute against your local database. The script is located at <root>/lib/update-db.sql. From the root of the project you can run:
psql -d batabase_development -f lib/update-db.sql
Not all of the data is available within the Access database and needs to be added to the database manually. We have created rake tasks in lib/tasks/seed.rake to do so. These tasks either add hardcoded values or parse CSV's to add the data. Additionally, we create users so we do not have to sign up with the application every time we re-seed the db. Feel free to add you own email and password to the create users task.
Rake tasks can be executed by:
bundle exec rake seed:<your-task>
To make it easy on ourselves we have placed this entire process to in single script. From the root of the project execute:
sh script/createdb.sh