Merge lp:~j-corwin/openlp/migration into lp:openlp

[Jonathan Corwin]

Attempt to get Bible migration working for Windows. Done it via openlpcnv, rather than bible-1to2-converter which borks on the "import sqlite" on Windows.

Not entirely sure what I was doing in migratebibles, just copying from migratesongs and hoping for the best, so criticism expected, hopefully constructive!

Used an insert statement for the verses, since doing it the same as everything else was far too slow to be practical and I don't know sqlalchemy so didn't know what alternatives I had.

[Raoul Snyman (raoul-snyman)]

The openlpcnv.pyw is a hack at best, and the code from bible-1to2-converter.py is used in an online migration tool I am writing.

The reason the bible-1to2-converter.py script borks on Windows is because that module does not exist, and it requires a full edition of Visual C++ in order to compile (I tried it with the express edition, but it doesn't have one particular tool required in the linking process). I mostly created those two 1to2 scripts for our use as developers to easily convert 1.x stuff to 2.

Personally, I think that the online conversion tool should be the official upgrade tool, it would allow folks to upgrade without needing to install anything else on their computers. Of course folks are welcome to bring their own ideas to the table, but to me an online converter makes the most sense.

[Raoul Snyman (raoul-snyman)]

self.data_path + os.sep + database => os.path.join(self.data_path, database)

It's no wonder your SQLAlchemy models took FOREVER to run through, you were committing after every insert. Just commit a chapter at a time, or all of it at the end, not a verse at a time.

review: Needs Fixing

[Jonathan Corwin (j-corwin)]

Hmm, only committed after every insert since I blindly copied the song code that was committing after every song. I'll try moving things around tonigt and see what difference is makes.

I have full Visual Studio 2003, so if you point me to what needs building to get the -1to2- script working, I'll take a look.

Although the online converter will be good for some, it relies on
1. An internet connection. Many churches in the UK think they are cutting edge if they have running hot water. Broadband is a thing of luxury!
2. Someone operating it who is capable of finding the application data folder and work out which files they need to upload.
3. Also you might find one person somewhere who is overly paranoid about copyright and not want to upload their songs to a website for conversion for ethic/moral reasons!

[Jonathan Corwin (j-corwin)]

Moved the commit, makes quite a difference!

[Raoul Snyman (raoul-snyman)]

Please just fix up the conflict. You probably need to just do a merge from trunk into your branch and resolve the conflict there, and then propose the merge again.

review: Needs Fixing

[Raoul Snyman (raoul-snyman)]

Much better. You could probably still optimise your SQL by grouping a few of those other queries, but the main ones are covered.

review: Approve

[Raoul Snyman (raoul-snyman)]

review: Approve

[Tim Bentley (trb143)]

Approved

review: Approve

=== modified file 'openlp/migration/migratebibles.py'
--- openlp/migration/migratebibles.py	2010-03-21 23:58:01 +0000
+++ openlp/migration/migratebibles.py	2010-03-22 20:02:14 +0000
@@ -23,10 +23,204 @@
 # Temple Place, Suite 330, Boston, MA 02111-1307 USA                          #
 ###############################################################################
 
+import os
+import sys
+import sqlite3
+
+from sqlalchemy import  *
+from sqlalchemy import create_engine
+from sqlalchemy.orm import scoped_session, sessionmaker, mapper, relation
+
+from openlp.core.lib import PluginConfig
+from openlp.plugins.bibles.lib.models import *
+    
+class BaseModel(object):
+    """
+    BaseModel provides a base object with a set of generic functions
+    """
+
+    @classmethod
+    def populate(cls, **kwargs):
+        """
+        Creates an instance of a class and populates it, returning the instance
+        """
+        me = cls()
+        keys = kwargs.keys()
+        for key in keys:
+            me.__setattr__(key, kwargs[key])
+        return me
+
+class TBibleMeta(BaseModel):
+    """
+    Bible Meta Data
+    """
+    pass
+
+class TTestament(BaseModel):
+    """
+    Bible Testaments
+    """
+    pass
+
+class TBook(BaseModel):
+    """
+    Song model
+    """
+    pass
+
+class TVerse(BaseModel):
+    """
+    Topic model
+    """
+    pass
+    
+temp_meta_table = Table(u'metadata_temp', metadata,
+    Column(u'key', types.Unicode(255), primary_key=True),
+    Column(u'value', types.Unicode(255)),
+)
+temp_testament_table = Table(u'testament_temp', metadata,
+    Column(u'id', types.Integer, primary_key=True),
+    Column(u'name', types.Unicode(30)),
+)
+temp_book_table = Table(u'book_temp', metadata,
+    Column(u'id', types.Integer, primary_key=True),
+    Column(u'testament_id', types.Integer),
+    Column(u'name', types.Unicode(30)),
+    Column(u'abbreviation', types.Unicode(5)),
+)
+temp_verse_table = Table(u'verse_temp', metadata,
+    Column(u'id', types.Integer, primary_key=True),
+    Column(u'book_id', types.Integer),
+    Column(u'chapter', types.Integer),
+    Column(u'verse', types.Integer),
+    Column(u'text', types.UnicodeText),
+)
+
+mapper(TBibleMeta, temp_meta_table)
+mapper(TTestament, temp_testament_table)
+mapper(TBook, temp_book_table)
+mapper(TVerse, temp_verse_table)
+
+def init_models(url):
+    engine = create_engine(url)
+    metadata.bind = engine
+    session = scoped_session(sessionmaker(autoflush=False,
+        autocommit=False, bind=engine))
+    return session
+
 class MigrateBibles():
     def __init__(self, display):
         self.display = display
+        self.config = PluginConfig(u'Bibles')
+        self.data_path = self.config.get_data_path()
+        self.database_files = self.config.get_files(u'sqlite')
+        print self.database_files
+
+    def progress(self, text):
+        print text
+        self.display.output(text)
 
     def process(self):
-        self.display.output(u'Bible process started')
-        self.display.output(u'Bible process finished')
+        self.progress(u'Bibles processing started')
+        for f in self.database_files:
+            self.v_1_9_0(f)
+        self.progress(u'Bibles processing finished')
+
+    def v_1_9_0(self, database):
+        self.progress(u'Migration 1.9.0 Started for ' + database)
+        self._v1_9_0_old(database)
+        self._v1_9_0_new(database)
+        self._v1_9_0_cleanup(database)
+        self.progress(u'Migration 1.9.0 Finished for ' + database)
+
+    def _v1_9_0_old(self, database):
+        self.progress(u'Rename Tables ' + database)
+        conn = sqlite3.connect(os.path.join(self.data_path, database))
+        conn.execute(u'alter table book rename to book_temp;')
+        conn.commit()
+        conn.execute(u'alter table testament rename to testament_temp;')
+        conn.commit()
+        conn.execute(u'alter table verse rename to verse_temp;')
+        conn.commit()
+        conn.execute(u'alter table metadata rename to metadata_temp;')
+        conn.commit()
+
+    def _v1_9_0_new(self, database):
+        self.progress(u'Create new Tables ' + database)
+        self.db_url = u'sqlite:///' + self.data_path + u'/' + database 
+        print self.db_url
+        self.session = init_models(self.db_url)
+        metadata.create_all(checkfirst=True)
+        self.progress(u'Create testament table')
+        results = self.session.query(TTestament).order_by(TTestament.id).all()
+        for testament_temp in results:
+            testament = Testament()
+            testament.id = testament_temp.id
+            testament.name = testament_temp.name
+            try:
+                self.session.add(testament)
+                self.session.commit()
+            except:
+                self.session.rollback()
+                print u'Error thrown = ', sys.exc_info()[1]
+        self.progress(u'Create book table')
+        results = self.session.query(TBook).order_by(TBook.id).all()
+        for book_temp in results:
+            book = Book()
+            book.id = book_temp.id
+            book.testament_id = book_temp.testament_id
+            book.name = book_temp.name
+            book.abbreviation = book_temp.abbreviation
+            try:
+                self.session.add(book)
+                self.session.commit()
+            except:
+                self.session.rollback()
+                print u'Error thrown = ', sys.exc_info()[1]
+        self.progress(u'Create verse table')
+        results = self.session.query(TVerse).order_by(TVerse.id).all()
+        for verse_temp in results:
+            verse = Verse()
+            verse.id = verse_temp.id
+            verse.book_id = verse_temp.book_id
+            verse.chapter = verse_temp.chapter
+            verse.verse = verse_temp.verse
+            verse.text = verse_temp.text
+            try:
+                self.session.add(verse)
+            except:
+                self.session.rollback()
+                print u'Error thrown = ', sys.exc_info()[1]
+        try:
+            self.session.commit()
+        except:
+            self.session.rollback()
+            print u'Error thrown = ', sys.exc_info()[1]
+        self.progress(u'Create metadata table')
+        results = self.session.query(TBibleMeta).order_by(TBibleMeta.key).all()
+        for biblemeta_temp in results:
+            biblemeta = BibleMeta()
+            biblemeta.key = biblemeta_temp.key
+            biblemeta.value = biblemeta_temp.value
+            try:
+                self.session.add(biblemeta)
+                self.session.commit()
+            except:
+                self.session.rollback()
+                print u'Error thrown = ', sys.exc_info()[1]
+
+    def _v1_9_0_cleanup(self, database):
+        self.progress(u'Update Internal Data ' + database)
+        conn = sqlite3.connect(os.path.join(self.data_path, database))
+        conn.commit()
+        conn.execute(u'drop table book_temp;')
+        conn.commit()
+        conn.execute(u'drop table testament_temp;')
+        conn.commit()
+        conn.execute(u'drop table verse_temp;')
+        conn.commit()
+        conn.execute(u'drop table metadata_temp;')
+        conn.commit()
+        conn.execute(u'vacuum;')
+        conn.commit()
+

=== modified file 'openlpcnv.pyw'
--- openlpcnv.pyw	2010-03-21 23:58:01 +0000
+++ openlpcnv.pyw	2010-03-22 20:02:14 +0000
@@ -70,7 +70,7 @@
         """
         #MigrateFiles(self.display).process()
         MigrateSongs(self.display).process()
-        #MigrateBibles(self.display).process()
+        MigrateBibles(self.display).process()
 
     def move_log_file(self):
         """
@@ -101,6 +101,7 @@
         writefile.close()
 
     def convert_sqlite2_to_3(self, olddb, newdb):
+        print u'Converting sqlite2 ' + olddb + ' to sqlite3 ' + newdb
         if os.name == u'nt':
             # we can't make this a raw unicode string as the \U within it causes much confusion
             hKey = win32api.RegOpenKey(win32con.HKEY_LOCAL_MACHINE, u'SOFTWARE\\Microsoft\\Windows\\CurrentVersion\\Uninstall\\SQLite ODBC Driver')
@@ -132,19 +133,30 @@
 
 if __name__ == u'__main__':
     mig = Migration()
-    config = PluginConfig(u'Songs')
-    newpath = config.get_data_path()
+    songconfig = PluginConfig(u'Songs')
+    newsongpath = songconfig.get_data_path()
+    bibleconfig = PluginConfig(u'Bibles')
+    newbiblepath = bibleconfig.get_data_path()
     if os.name == u'nt':
-        if not os.path.isdir(newpath):
-            os.makedirs(newpath)
+        if not os.path.isdir(newsongpath):
+            os.makedirs(newsongpath)
+        if not os.path.isdir(newbiblepath):
+            os.makedirs(newbiblepath)
         ALL_USERS_APPLICATION_DATA = 35
         shell = Dispatch(u'Shell.Application')
         folder = shell.Namespace(ALL_USERS_APPLICATION_DATA)
         folderitem = folder.Self
-        olddb = os.path.join(folderitem.path, u'openlp.org', u'Data', u'songs.olp')
+        oldsongdb = os.path.join(folderitem.path, u'openlp.org', u'Data', u'songs.olp')
+        oldbiblepath = os.path.join(folderitem.path, u'openlp.org', u'Data', u'Bibles')
     else:
-        olddb = os.path.join(newpath, u'songs.olp')
-    newdb = os.path.join(newpath, u'songs.sqlite')
-    mig.convert_sqlite2_to_3(olddb, newdb)
+        oldsongdb = os.path.join(newsongpath, u'songs.olp')
+    newsongdb = os.path.join(newsongpath, u'songs.sqlite')
+    mig.convert_sqlite2_to_3(oldsongdb, newsongdb)
+    files = os.listdir(oldbiblepath)
+    for file in files:
+        f = os.path.splitext(os.path.basename(file))[0]
+        if f != 'kjv':   #kjv bible has an autoincrement key not supported in sqlite3
+            mig.convert_sqlite2_to_3(os.path.join(oldbiblepath, file),
+                os.path.join(newbiblepath, f + u'.sqlite'))
     mig.process()
     #mig.move_log_file()