From adddd3ff8630bb8c5fc7355bcd0d09f226353f67 Mon Sep 17 00:00:00 2001 From: Camil Staps Date: Wed, 14 Oct 2015 19:40:29 +0200 Subject: Using Orator migrations --- README.md | 7 +-- cli.py | 25 +++------ config.py | 8 +++ ...5_10_14_17191444835970_create_settings_table.py | 19 +++++++ ...2015_10_14_17201444836021_create_parts_table.py | 18 +++++++ ...2015_10_14_17201444836027_create_books_table.py | 17 ++++++ ...44836036_create_alternative_book_names_table.py | 19 +++++++ ...015_10_14_17201444836042_create_verses_table.py | 21 ++++++++ ..._14_17201444836048_create_translations_table.py | 17 ++++++ ...7201444836054_create_translated_verses_table.py | 26 +++++++++ ...5_10_14_17211444836060_create_passages_table.py | 31 +++++++++++ ...17211444836066_create_cross_references_table.py | 23 ++++++++ pyble.py | 63 ---------------------- 13 files changed, 211 insertions(+), 83 deletions(-) create mode 100644 config.py create mode 100644 migrations/2015_10_14_17191444835970_create_settings_table.py create mode 100644 migrations/2015_10_14_17201444836021_create_parts_table.py create mode 100644 migrations/2015_10_14_17201444836027_create_books_table.py create mode 100644 migrations/2015_10_14_17201444836036_create_alternative_book_names_table.py create mode 100644 migrations/2015_10_14_17201444836042_create_verses_table.py create mode 100644 migrations/2015_10_14_17201444836048_create_translations_table.py create mode 100644 migrations/2015_10_14_17201444836054_create_translated_verses_table.py create mode 100644 migrations/2015_10_14_17211444836060_create_passages_table.py create mode 100644 migrations/2015_10_14_17211444836066_create_cross_references_table.py 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='"') -- cgit v1.2.3