openlp-core team mailing list archive

[Jonathan Corwin <j@xxxxxxxxxxxx>]

Jonathan Corwin has proposed merging lp:~j-corwin/openlp/migration into lp:openlp.

Requested reviews:
  OpenLP Core (openlp-core)


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.
-- 
https://code.launchpad.net/~j-corwin/openlp/migration/+merge/21818
Your team OpenLP Core is subscribed to branch lp:openlp.

=== modified file 'openlp/migration/migratebibles.py'
--- openlp/migration/migratebibles.py	2010-02-26 23:47:59 +0000
+++ openlp/migration/migratebibles.py	2010-03-21 23:11:18 +0000
@@ -23,10 +23,213 @@
 # 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')
\ No newline at end of file
+        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(self.data_path + os.sep + 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)
+        #        self.session.commit()
+        #    except:
+        #        self.session.rollback()
+        #        print u'Error thrown = ', sys.exc_info()[1]
+        #
+        # The above is too slow, took hours to not finish one bible
+        # For now, just do it the old fashioned way
+        #---
+        try:
+            conn = sqlite3.connect(self.data_path + os.sep + database)
+            conn.execute(u'insert into verse select * from verse_temp;')
+            conn.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(self.data_path + os.sep + 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-01-24 23:16:15 +0000
+++ openlpcnv.pyw	2010-03-21 23:11:18 +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')
@@ -127,24 +128,35 @@
             subprocess.call(cmd, stdin=open(u'sqlite3.dmp', u'r'))
         else:
             subprocess.call(cmd, stdin=open(u'sqlite3.dmp', u'r'), shell=True)
-        os.remove(u'sqlite.dmp')
-        os.remove(u'sqlite3.dmp')
+#        os.remove(u'sqlite.dmp')
+#        os.remove(u'sqlite3.dmp')
 
 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()