Using Orator migrations

13 files changed, 211 insertions, 83 deletions

diff --git a/README.md b/README.md
index ac2295f..6d44631 100644
--- a/README.md
+++ b/README.md
@@ -11,14 +11,15 @@ Install the necessary packages:
 
     # pip3 install orator click
 
-First we need to setup an SQLite database:
+Edit the `config.py` to suit your needs. Then initialise the database:
 
-    $ ./cli.py init pyble.db
+    $ orator migrations:run -c config.py    # Create schema
+    $ ./cli.py init                         # Fill with standard data
 
 You're going to need one or several translations. One possibility is to [get them from Bible Hub][biblehubdb] (take the "Text file (zipped, 5.5MB)"). Download and unzip the zipped text file, then run:
 
     $ unzip bibles.zip
-    $ ./cli.py load --type=biblehub bibles.txt pyble.db
+    $ ./cli.py load --type=biblehub bibles.txt
     $ rm bibles.zip bibles.txt
 
 ## To do
diff --git a/cli.py b/cli.py
index 921ecf5..f1647e0 100755
--- a/cli.py
+++ b/cli.py
@@ -5,20 +5,14 @@ from orator import DatabaseManager, Model
 import orator
 
 from pyble import *
+import config
 
 import re
 import time
 
-def setup_database(filename):
-    """Setup a connection with an SQLite database in filename"""
-    config = {
-            'default': 'sqlite',
-            'sqlite': {
-                'driver': 'sqlite',
-                'database': filename,
-            }
-        }
-    db = DatabaseManager(config)
+def setup_database():
+    """Setup a connection with the database"""
+    db = DatabaseManager(config.DATABASES)
     Model.set_connection_resolver(db)
     return db
 
@@ -28,12 +22,10 @@ def cli():
     pass
 
 @cli.command()
-@click.argument('filename', type=click.Path())
-def init(filename):
+def init():
     """Initialise an SQLite database in filename, and create tables"""
     click.echo('Initialising pyble database...')
-    db = setup_database(filename)
-    setup_tables(db)
+    db = setup_database()
     click.echo('Inserting common book names...')
     setup_fill_alternative_book_names(db)
     click.echo('Done.')
@@ -103,10 +95,9 @@ def load_biblehub(db, f):
 @cli.command()
 @click.option('--type', required=True)
 @click.argument('filename', type=click.Path())
-@click.argument('dbname', type=click.Path())
-def load(type, filename, dbname):
+def load(type, filename):
     """Load external data into the database"""
-    db = setup_database(dbname)
+    db = setup_database()
     if type == 'biblehub':
         with open(filename) as f:
             load_biblehub(db, f)
diff --git a/config.py b/config.py
new file mode 100644
index 0000000..a64f54a
--- /dev/null
+++ b/config.py
@@ -0,0 +1,8 @@
+DATABASES = {
+        'default': 'sqlite',
+        'sqlite': {
+            'driver': 'sqlite',
+            'database': 'pyble.db',
+        }
+    }
+
diff --git a/migrations/2015_10_14_17191444835970_create_settings_table.py b/migrations/2015_10_14_17191444835970_create_settings_table.py
new file mode 100644
index 0000000..96acc92
--- /dev/null
+++ b/migrations/2015_10_14_17191444835970_create_settings_table.py
@@ -0,0 +1,19 @@
+from orator.migrations import Migration
+
+
+class CreateSettingsTable(Migration):
+
+    def up(self):
+        """
+        Run the migrations.
+        """
+        with self.schema.create('settings') as table:
+            table.text('key').primary()
+            table.text('value')
+            table.timestamps()
+
+    def down(self):
+        """
+        Revert the migrations.
+        """
+        self.schema.drop('settings')
diff --git a/migrations/2015_10_14_17201444836021_create_parts_table.py b/migrations/2015_10_14_17201444836021_create_parts_table.py
new file mode 100644
index 0000000..abddc6b
--- /dev/null
+++ b/migrations/2015_10_14_17201444836021_create_parts_table.py
@@ -0,0 +1,18 @@
+from orator.migrations import Migration
+
+
+class CreatePartsTable(Migration):
+
+    def up(self):
+        """
+        Run the migrations.
+        """
+        with self.schema.create('parts') as table:
+            table.string('name')
+            table.primary('name')
+
+    def down(self):
+        """
+        Revert the migrations.
+        """
+        self.schema.drop('parts')
diff --git a/migrations/2015_10_14_17201444836027_create_books_table.py b/migrations/2015_10_14_17201444836027_create_books_table.py
new file mode 100644
index 0000000..62b631f
--- /dev/null
+++ b/migrations/2015_10_14_17201444836027_create_books_table.py
@@ -0,0 +1,17 @@
+from orator.migrations import Migration
+
+
+class CreateBooksTable(Migration):
+
+    def up(self):
+        """
+        Run the migrations.
+        """
+        with self.schema.create('books') as table:
+            table.string('canonical_name').primary()
+
+    def down(self):
+        """
+        Revert the migrations.
+        """
+        self.schema.drop('books')
diff --git a/migrations/2015_10_14_17201444836036_create_alternative_book_names_table.py b/migrations/2015_10_14_17201444836036_create_alternative_book_names_table.py
new file mode 100644
index 0000000..7d62655
--- /dev/null
+++ b/migrations/2015_10_14_17201444836036_create_alternative_book_names_table.py
@@ -0,0 +1,19 @@
+from orator.migrations import Migration
+
+
+class CreateAlternativeBookNamesTable(Migration):
+
+    def up(self):
+        """
+        Run the migrations.
+        """
+        with self.schema.create('alternative_book_names') as table:
+            table.string('name').primary()
+            table.string('book')
+            table.foreign('book').references('canonical_name').on('books')
+
+    def down(self):
+        """
+        Revert the migrations.
+        """
+        self.schema.drop('alternative_book_names')
diff --git a/migrations/2015_10_14_17201444836042_create_verses_table.py b/migrations/2015_10_14_17201444836042_create_verses_table.py
new file mode 100644
index 0000000..b95aa50
--- /dev/null
+++ b/migrations/2015_10_14_17201444836042_create_verses_table.py
@@ -0,0 +1,21 @@
+from orator.migrations import Migration
+
+
+class CreateVersesTable(Migration):
+
+    def up(self):
+        """
+        Run the migrations.
+        """
+        with self.schema.create('verses') as table:
+            table.string('book')
+            table.small_integer('chapter')
+            table.small_integer('nr')
+            table.foreign('book').references('canonical_name').on('books')
+            table.primary(['book', 'chapter', 'nr'])
+
+    def down(self):
+        """
+        Revert the migrations.
+        """
+        self.schema.drop('verses')
diff --git a/migrations/2015_10_14_17201444836048_create_translations_table.py b/migrations/2015_10_14_17201444836048_create_translations_table.py
new file mode 100644
index 0000000..3178feb
--- /dev/null
+++ b/migrations/2015_10_14_17201444836048_create_translations_table.py
@@ -0,0 +1,17 @@
+from orator.migrations import Migration
+
+
+class CreateTranslationsTable(Migration):
+
+    def up(self):
+        """
+        Run the migrations.
+        """
+        with self.schema.create('translations') as table:
+            table.string('name').primary()
+
+    def down(self):
+        """
+        Revert the migrations.
+        """
+        self.schema.drop('translations')
diff --git a/migrations/2015_10_14_17201444836054_create_translated_verses_table.py b/migrations/2015_10_14_17201444836054_create_translated_verses_table.py
new file mode 100644
index 0000000..43703e3
--- /dev/null
+++ b/migrations/2015_10_14_17201444836054_create_translated_verses_table.py
@@ -0,0 +1,26 @@
+from orator.migrations import Migration
+
+
+class CreateTranslatedVersesTable(Migration):
+
+    def up(self):
+        """
+        Run the migrations.
+        """
+        with self.schema.create('translated_verses') as table:
+            table.string('translation')
+            table.string('book')
+            table.small_integer('chapter')
+            table.small_integer('nr')
+            table.string('text')
+            table.foreign('translation').references('name').on('translations')
+            table.foreign(['book', 'chapter', 'nr'])\
+                    .references(['book', 'chapter', 'nr']).on('verses')\
+                    .on_delete('restrict').on_update('cascade')
+            table.primary(['translation', 'book', 'chapter', 'nr'])
+
+    def down(self):
+        """
+        Revert the migrations.
+        """
+        self.schema.drop('translated_verses')
diff --git a/migrations/2015_10_14_17211444836060_create_passages_table.py b/migrations/2015_10_14_17211444836060_create_passages_table.py
new file mode 100644
index 0000000..a044dbb
--- /dev/null
+++ b/migrations/2015_10_14_17211444836060_create_passages_table.py
@@ -0,0 +1,31 @@
+from orator.migrations import Migration
+
+
+class CreatePassagesTable(Migration):
+
+    def up(self):
+        """
+        Run the migrations.
+        """
+        with self.schema.create('passages') as table:
+            table.increments('id')
+            table.string('fst_book')
+            table.small_integer('fst_chapter')
+            table.small_integer('fst_nr')
+            table.string('snd_book')
+            table.small_integer('snd_chapter')
+            table.small_integer('snd_nr')
+            table.foreign(['fst_book', 'fst_chapter', 'fst_nr'])\
+                    .references(['book', 'chapter', 'nr']).on('verses')\
+                    .on_delete('restrict').on_update('cascade')
+            table.foreign(['snd_book', 'snd_chapter', 'snd_nr'])\
+                    .references(['book', 'chapter', 'nr']).on('verses')\
+                    .on_delete('restrict').on_update('cascade')
+            table.unique(['fst_book', 'fst_chapter', 'fst_nr',
+                'snd_book', 'snd_chapter', 'snd_nr'])
+
+    def down(self):
+        """
+        Revert the migrations.
+        """
+        self.schema.drop('passages')
diff --git a/migrations/2015_10_14_17211444836066_create_cross_references_table.py b/migrations/2015_10_14_17211444836066_create_cross_references_table.py
new file mode 100644
index 0000000..a1607f2
--- /dev/null
+++ b/migrations/2015_10_14_17211444836066_create_cross_references_table.py
@@ -0,0 +1,23 @@
+from orator.migrations import Migration
+
+
+class CreateCrossReferencesTable(Migration):
+
+    def up(self):
+        """
+        Run the migrations.
+        """
+        with self.schema.create('crossreferences') as table:
+            table.integer('passage_id1')
+            table.integer('passage_id2')
+            table.timestamps()
+            table.small_integer('relevance').default(0)
+            table.foreign('passage_id1').references('id').on('passages')
+            table.foreign('passage_id2').references('id').on('passages')
+            table.primary(['passage_id1', 'passage_id2'])
+
+    def down(self):
+        """
+        Revert the migrations.
+        """
+        self.schema.drop('crossreferences')
diff --git a/pyble.py b/pyble.py
index 79bed00..187b4ca 100644
--- a/pyble.py
+++ b/pyble.py
@@ -33,69 +33,6 @@ class Passage(Model):
 class CrossReference(Model):
     __fillable__ = ['passage_id1', 'passage_id2', 'relevance']
 
-def setup_tables(db):
-    schema = Schema(db)
-
-    with schema.create('parts') as table:
-        table.string('name')
-        table.primary('name')
-
-    with schema.create('books') as table:
-        table.string('canonical_name').primary()
-
-    with schema.create('alternative_book_names') as table:
-        table.string('name').primary()
-        table.string('book')
-        table.foreign('book').references('canonical_name').on('books')
-
-    with schema.create('verses') as table:
-        table.string('book')
-        table.small_integer('chapter')
-        table.small_integer('nr')
-        table.foreign('book').references('canonical_name').on('books')
-        table.primary(['book', 'chapter', 'nr'])
-
-    with schema.create('translations') as table:
-        table.string('name').primary()
-    
-    with schema.create('translated_verses') as table:
-        table.string('translation')
-        table.string('book')
-        table.small_integer('chapter')
-        table.small_integer('nr')
-        table.string('text')
-        table.foreign('translation').references('name').on('translations')
-        table.foreign(['book', 'chapter', 'nr'])\
-                .references(['book', 'chapter', 'nr']).on('verses')\
-                .on_delete('restrict').on_update('cascade')
-        table.primary(['translation', 'book', 'chapter', 'nr'])
-
-    with schema.create('passages') as table:
-        table.increments('id')
-        table.string('fst_book')
-        table.small_integer('fst_chapter')
-        table.small_integer('fst_nr')
-        table.string('snd_book')
-        table.small_integer('snd_chapter')
-        table.small_integer('snd_nr')
-        table.foreign(['fst_book', 'fst_chapter', 'fst_nr'])\
-                .references(['book', 'chapter', 'nr']).on('verses')\
-                .on_delete('restrict').on_update('cascade')
-        table.foreign(['snd_book', 'snd_chapter', 'snd_nr'])\
-                .references(['book', 'chapter', 'nr']).on('verses')\
-                .on_delete('restrict').on_update('cascade')
-        table.unique(['fst_book', 'fst_chapter', 'fst_nr',
-            'snd_book', 'snd_chapter', 'snd_nr'])
-
-    with schema.create('crossreferences') as table:
-        table.integer('passage_id1')
-        table.integer('passage_id2')
-        table.timestamps()
-        table.small_integer('relevance').default(0)
-        table.foreign('passage_id1').references('id').on('passages')
-        table.foreign('passage_id2').references('id').on('passages')
-        table.primary(['passage_id1', 'passage_id2'])
-
 def setup_fill_alternative_book_names(db):
     with open('alternative_book_names.csv', 'r') as csvfile:
         reader = csv.reader(csvfile, delimiter=',', quotechar='"')