openlp-core team mailing list archive
-
openlp-core team
-
Mailing list archive
-
Message #00043
[Merge] lp:~j-corwin/openlp/migration into lp:openlp
Jonathan Corwin has proposed merging lp:~j-corwin/openlp/migration into lp:openlp.
Requested reviews:
openlp.org Core (openlp-core)
Attempt to fix song migration so it matches current file definitions.
Remove the need to edit the dmp file as part of the migration
--
https://code.launchpad.net/~j-corwin/openlp/migration/+merge/6124
Your team openlp.org Core is subscribed to branch lp:openlp.
=== modified file 'openlp/migration/migratesongs.py'
--- openlp/migration/migratesongs.py 2009-02-04 20:16:56 +0000
+++ openlp/migration/migratesongs.py 2009-05-02 19:32:15 +0000
@@ -48,10 +48,10 @@
print self.database_files
def process(self):
- self.display.output("Songs processing started");
+ self.display.output("Songs processing started")
for f in self.database_files:
self.v_1_9_0(f)
- self.display.output("Songs processing finished");
+ self.display.output("Songs processing finished")
def v_1_9_0(self, database):
self.display.output("Migration 1.9.0 Started for "+database);
@@ -61,83 +61,69 @@
self._v1_9_0_songauthors(database)
self._v1_9_0_songtopics(database)
self._v1_9_0_songs(database)
- self._v1_9_0_songs_update(database)
-
- self.display.output("Migration 1.9.0 Finished for " + database);
+ self.display.output("Migration 1.9.0 Finished for " + database)
def _v1_9_0_authors(self, database):
- self.display.sub_output("Authors Started for "+database);
+ self.display.sub_output("Authors Started for "+database)
conn = sqlite3.connect(self.data_path+os.sep+database)
conn.execute("""alter table authors rename to authors_temp;""")
conn.commit()
-
- conn.execute("""create table authors add column display_name varchar(255);""")
+ conn.execute("""create table authors (id integer primary key ASC AUTOINCREMENT);""")
conn.commit()
- self.display.sub_output("first name created")
+ self.display.sub_output("authors table created")
conn.execute("""alter table authors add column first_name varchar(128);""")
conn.commit()
- self.display.sub_output("first name created")
+ self.display.sub_output("first_name added")
conn.execute("""alter table authors add column last_name varchar(128);""")
conn.commit()
- self.display.sub_output("last name created")
- conn.execute("""create index if not exists author_display_name on authors (display_name ASC,id ASC);""")
+ self.display.sub_output("last_name added")
+ conn.execute("""alter table authors add column display_name varchar(255);""")
+ conn.commit()
+ self.display.sub_output("display_name added")
+ conn.execute("""create index if not exists author1 on authors (display_name ASC,id ASC);""")
conn.commit()
self.display.sub_output("index author1 created")
- conn.execute("""create index if not exists author_last_name on authors (last_name ASC,id ASC);""")
+ conn.execute("""create index if not exists author2 on authors (last_name ASC,id ASC);""")
conn.commit()
self.display.sub_output("index author2 created")
- conn.execute("""create index if not exists author_first_name on authors (first_name ASC,id ASC);""")
+ conn.execute("""create index if not exists author3 on authors (first_name ASC,id ASC);""")
conn.commit()
self.display.sub_output("index author3 created")
self.display.sub_output("Author Data Migration started")
- c = conn.cursor()
- text = c.execute("""select * from authors """) .fetchall()
+ conn.execute("""insert into authors (id, display_name) select authorid, authorname from authors_temp;""")
+ conn.commit()
+ self.display.sub_output("authors populated")
+ c = conn.cursor()
+ text = c.execute("""select * from authors""") .fetchall()
for author in text:
- dispname = author[1]
- if author[2] == None:
- dispname = dispname.replace("'", "") # remove quotes.
- pos = dispname.rfind(" ")
- afn = dispname[:pos]
- aln = dispname[pos + 1:len(dispname)]
- #txt = text[2]
- s = "update authors set display_name = '" + dispname + "', first_name = '" + afn + "', last_name = '" + aln + "' where id = " +str(author[0])
- text1 = c.execute(s)
- conn.commit()
- conn.execute("""alter table authors drop column authorname;""")
- conn.commit()
- conn.close()
+ dispname = author[3]
+ dispname = dispname.replace("'", "") # remove quotes.
+ pos = dispname.rfind(" ")
+ afn = dispname[:pos]
+ aln = dispname[pos + 1:len(dispname)]
+ s = "update authors set first_name = '" \
+ + afn + "', last_name = '" + aln + "' where id = " + str(author[0])
+ text1 = c.execute(s)
+ conn.commit()
self.display.sub_output("Author Data Migration Completed")
- self.display.sub_output("Authors Completed");
-
- def _v1_9_0_topics(self, database):
- self.display.sub_output("Topics Started for "+database);
- conn = sqlite3.connect(self.data_path+os.sep+database)
- conn.text_factory = str
- conn.execute("""create table if not exists topics (id integer Primary Key ASC AUTOINCREMENT);""")
- conn.commit()
- self.display.sub_output("Topic table created")
- conn.execute("""alter table topics add column name varchar(128);""")
- conn.commit()
- self.display.sub_output("topicname added")
- conn.execute("""create index if not exists topic1 on topics (name ASC,id ASC);""")
+ conn.execute("""drop table authors_temp;""")
conn.commit()
conn.close()
- self.display.sub_output("index topic1 created")
-
- self.display.sub_output("Topics Completed");
+ self.display.sub_output("author_temp dropped")
+ self.display.sub_output("Authors Completed")
def _v1_9_0_songbook(self, database):
- self.display.sub_output("SongBook Started for "+database);
+ self.display.sub_output("SongBook Started for "+database)
conn = sqlite3.connect(self.data_path+os.sep+database)
conn.execute("""create table if not exists song_books (id integer Primary Key ASC AUTOINCREMENT);""")
conn.commit()
self.display.sub_output("SongBook table created")
conn.execute("""alter table song_books add column name varchar(128);""")
conn.commit()
- self.display.sub_output("songbook_name added")
+ self.display.sub_output("songbook name added")
conn.execute("""alter table song_books add column publisher varchar(128);""")
conn.commit()
- self.display.sub_output("songbook_publisher added")
+ self.display.sub_output("songbook publisher added")
conn.execute("""create index if not exists songbook1 on song_books (name ASC,id ASC);""")
conn.commit()
self.display.sub_output("index songbook1 created")
@@ -145,45 +131,31 @@
conn.commit()
conn.close()
self.display.sub_output("index songbook2 created")
- self.display.sub_output("SongBook Completed");
-
- def _v1_9_0_songtopics(self, database):
- self.display.sub_output("Songtopics Started for "+database);
- conn = sqlite3.connect(self.data_path+os.sep+database)
- conn.execute("""create table if not exists song_topics (song_id integer Primary Key ASC );""")
- conn.commit()
- self.display.sub_output("Songtopics table created")
- conn.execute("""alter table song_topics add column topic_id interger;""")
- conn.commit()
- self.display.sub_output("songtopics_topic_id added")
- conn.execute("""create index if not exists songtopic1 on song_topics (topic_id ASC,song_id ASC);""")
- conn.commit()
- self.display.sub_output("index songbook1 created")
- conn.execute("""create index if not exists songbook2 on song_topics (song_id ASC,topic_id ASC);""")
- conn.commit()
- conn.close()
- self.display.sub_output("index songbook2 created")
- self.display.sub_output("SongTopics Completed");
-
- def _v1_9_0_songauthors(self, database):
- self.display.sub_output("SongAuthors Started for "+database);
- conn = sqlite3.connect(self.data_path+os.sep+database)
- conn.execute("""alter table songauthors rename to authors_songs;""")
- conn.commit()
- conn.close()
- self.display.sub_output("Table Renamed")
- self.display.sub_output("SongAuthors Completed");
-
+ self.display.sub_output("SongBook Completed")
def _v1_9_0_songs(self, database):
- self.display.sub_output("Songs Started for "+database);
+ self.display.sub_output("Songs Started for "+database)
conn = sqlite3.connect(self.data_path+os.sep+database)
- conn.execute("""alter table songs add column song_book_id interger;""")
- conn.commit()
- self.display.sub_output("songs_song_book_id added")
+ conn.execute("""alter table songs rename to songs_temp;""")
+ conn.commit()
+ conn.execute("""create table if not exists songs (id integer Primary Key ASC AUTOINCREMENT);""")
+ conn.commit()
+ self.display.sub_output("songs table created")
+ conn.execute("""alter table songs add column song_book_id integer;""")
+ conn.commit()
+ self.display.sub_output("songs song_book_id added")
+ conn.execute("""alter table songs add title varchar(255);""")
+ conn.commit()
+ self.display.sub_output("songs title added")
+ conn.execute("""alter table songs add lyrics text;""")
+ conn.commit()
+ self.display.sub_output("songs lyrics added")
conn.execute("""alter table songs add column verse_order varchar(128);""")
conn.commit()
self.display.sub_output("songs verse_order added")
+ conn.execute("""alter table songs add copyright varchar(255);""")
+ conn.commit()
+ self.display.sub_output("songs copyright added")
conn.execute("""alter table songs add column comments text;""")
conn.commit()
self.display.sub_output("songs comments added")
@@ -208,50 +180,75 @@
self.display.sub_output("index songs1 created")
conn.execute("""create index if not exists songs2 on songs (search_title ASC,id ASC);""")
conn.commit()
- conn.close()
self.display.sub_output("index songs2 created")
- self.display.sub_output("Songs Completed");
-
-
-
- def _v1_9_0_songs_update(self, database):
- self.display.sub_output("Songs Started for "+database);
- self.db = create_engine("sqlite:///"+self.data_path+os.sep+database, encoding='utf-8' , convert_unicode=False, assert_unicode=False)
-
- self.db.echo = True
- metadata.bind = self.db
- metadata.bind.echo = False
- Session = scoped_session(sessionmaker(autoflush=True, autocommit=False))
- Session.configure(bind=self.db)
- #create missing table
- songs_topics_table.create()
- songs = Session.query(Song).all()
- for song in songs:
- t=song.title.replace("&", "and")
- t=t.replace("'", "")
- t=t.replace(",", "")
- t=t.replace(";", "")
- t=t.replace(":", "")
- t=t.replace("(", "")
- t=t.replace(")", "")
- t=t.replace("{", "")
- t=t.replace("}", "")
- t=t.replace("?", "")
- song.search_title = t
- t=song.lyrics.replace("&", "and")
- t=t.replace("'", "")
- t=t.replace(",", "")
- t=t.replace(";", "")
- t=t.replace(":", "")
- t=t.replace("(", "")
- t=t.replace(")", "")
- t=t.replace("{", "")
- t=t.replace("}", "")
- t=t.replace("?", "")
- song.search_lyrics = t
- song.song_book_id = 0
- Session.save_or_update(song)
- Session.commit()
+
+ conn.execute("""insert into songs (id, title, lyrics, copyright, search_title, search_lyrics, song_book_id)
+ select songid, songtitle, lyrics, copyrightinfo,
+ replace(replace(replace(replace(replace(replace(replace(replace(replace(songtitle, '&', 'and'), ',', ''), ';', ''), ':', ''), '(', ''), ')', ''), '{', ''), '}',''),'?',''),
+ replace(replace(replace(replace(replace(replace(replace(replace(replace(lyrics, '&', 'and'), ',', ''), ';', ''), ':', ''), '(', ''), ')', ''), '{', ''), '}',''),'?',''),
+ 0
+ from songs_temp;""")
+
+ conn.commit()
+ self.display.sub_output("songs populated")
+ conn.execute("""drop table songs_temp;""")
+ conn.commit()
+ conn.close()
+ self.display.sub_output("songs_temp dropped")
+
+ self.display.sub_output("Songs Completed")
+
+ def _v1_9_0_topics(self, database):
+ self.display.sub_output("Topics Started for "+database)
+ conn = sqlite3.connect(self.data_path+os.sep+database)
+ conn.text_factory = str
+ conn.execute("""create table if not exists topics (id integer Primary Key ASC AUTOINCREMENT);""")
+ conn.commit()
+ self.display.sub_output("Topic table created")
+ conn.execute("""alter table topics add column name varchar(128);""")
+ conn.commit()
+ self.display.sub_output("topicname added")
+ conn.execute("""create index if not exists topic1 on topics (name ASC,id ASC);""")
+ conn.commit()
+ conn.close()
+ self.display.sub_output("index topic1 created")
+
+ self.display.sub_output("Topics Completed")
+
+ def _v1_9_0_songauthors(self, database):
+ self.display.sub_output("SongAuthors Started for "+database);
+ conn = sqlite3.connect(self.data_path+os.sep+database)
+ conn.execute("""create table if not exists authors_songs (author_id integer);""")
+ conn.commit()
+ self.display.sub_output("authors_songs table created")
+ conn.execute("""alter table authors_songs add column song_id integer;""")
+ conn.commit()
+ conn.execute("""insert into authors_songs (author_id, song_id) select authorid, songid from songauthors;""")
+ conn.commit()
+ self.display.sub_output("authors_songs populated")
+ conn.execute("""drop table songauthors;""")
+ conn.commit()
+ self.display.sub_output("songauthors dropped")
+ conn.close()
+ self.display.sub_output("SongAuthors Completed")
+
+ def _v1_9_0_songtopics(self, database):
+ self.display.sub_output("Songtopics Started for "+database);
+ conn = sqlite3.connect(self.data_path+os.sep+database)
+ conn.execute("""create table if not exists song_topics (song_id integer);""")
+ conn.commit()
+ self.display.sub_output("Songtopics table created")
+ conn.execute("""alter table song_topics add column topic_id integer;""")
+ conn.commit()
+ self.display.sub_output("songtopics_topic_id added")
+ conn.execute("""create index if not exists songtopic1 on song_topics (topic_id ASC,song_id ASC);""")
+ conn.commit()
+ self.display.sub_output("index songtopic1 created")
+ conn.execute("""create index if not exists songtopic2 on song_topics (song_id ASC,topic_id ASC);""")
+ conn.commit()
+ conn.close()
+ self.display.sub_output("index songtopic2 created")
+ self.display.sub_output("SongTopics Completed")
def run_cmd(self, cmd):
f_i, f_o = os.popen4(cmd)
=== modified file 'openlpcnv.pyw' (properties changed: -x to +x)
Follow ups
