1
=== modified file 'openlp/migration/migratesongs.py'
2
--- openlp/migration/migratesongs.py	2009-02-04 20:16:56 +0000
3
+++ openlp/migration/migratesongs.py	2009-05-02 19:32:15 +0000
4
@@ -48,10 +48,10 @@
5
48
        print self.database_files
48
        print self.database_files
6
49
49
7
50
    def process(self):
50
    def process(self):
9
51
        self.display.output("Songs processing started");
51
        self.display.output("Songs processing started")
10
52
        for f in self.database_files:
52
        for f in self.database_files:
11
53
            self.v_1_9_0(f)
53
            self.v_1_9_0(f)
13
54
        self.display.output("Songs processing finished");
54
        self.display.output("Songs processing finished")
14
55
55
15
56
    def v_1_9_0(self, database):
56
    def v_1_9_0(self, database):
16
57
        self.display.output("Migration 1.9.0 Started for "+database);
57
        self.display.output("Migration 1.9.0 Started for "+database);
17
@@ -61,83 +61,69 @@
18
61
        self._v1_9_0_songauthors(database)
61
        self._v1_9_0_songauthors(database)
19
62
        self._v1_9_0_songtopics(database)
62
        self._v1_9_0_songtopics(database)
20
63
        self._v1_9_0_songs(database)
63
        self._v1_9_0_songs(database)
24
64
        self._v1_9_0_songs_update(database)
64
        self.display.output("Migration 1.9.0 Finished for " + database)
22
65
23
66
        self.display.output("Migration 1.9.0 Finished for " + database);
25
67
65
26
68
    def _v1_9_0_authors(self, database):
66
    def _v1_9_0_authors(self, database):
28
69
        self.display.sub_output("Authors Started for "+database);
67
        self.display.sub_output("Authors Started for "+database)
29
70
        conn = sqlite3.connect(self.data_path+os.sep+database)
68
        conn = sqlite3.connect(self.data_path+os.sep+database)
30
71
        conn.execute("""alter table authors rename to authors_temp;""")
69
        conn.execute("""alter table authors rename to authors_temp;""")
31
72
        conn.commit()
70
        conn.commit()
34
73
71
        conn.execute("""create table authors (id integer primary key ASC AUTOINCREMENT);""")
33
74
        conn.execute("""create table authors add column display_name varchar(255);""")
35
75
        conn.commit()
72
        conn.commit()
37
76
        self.display.sub_output("first name created")
73
        self.display.sub_output("authors table created")
38
77
        conn.execute("""alter table authors add column first_name varchar(128);""")
74
        conn.execute("""alter table authors add column first_name varchar(128);""")
39
78
        conn.commit()
75
        conn.commit()
41
79
        self.display.sub_output("first name created")
76
        self.display.sub_output("first_name added")
42
80
        conn.execute("""alter table authors add column last_name varchar(128);""")
77
        conn.execute("""alter table authors add column last_name varchar(128);""")
43
81
        conn.commit()
78
        conn.commit()
46
82
        self.display.sub_output("last name created")
79
        self.display.sub_output("last_name added")
47
83
        conn.execute("""create index if not exists author_display_name on authors (display_name ASC,id ASC);""")
80
        conn.execute("""alter table authors add column display_name varchar(255);""")
48
81
        conn.commit()
49
82
        self.display.sub_output("display_name added")
50
83
        conn.execute("""create index if not exists author1 on authors (display_name ASC,id ASC);""")
51
84
        conn.commit()
84
        conn.commit()
52
85
        self.display.sub_output("index author1 created")
85
        self.display.sub_output("index author1 created")
54
86
        conn.execute("""create index if not exists author_last_name on authors (last_name ASC,id ASC);""")
86
        conn.execute("""create index if not exists author2 on authors (last_name ASC,id ASC);""")
55
87
        conn.commit()
87
        conn.commit()
56
88
        self.display.sub_output("index author2 created")
88
        self.display.sub_output("index author2 created")
58
89
        conn.execute("""create index if not exists author_first_name on authors (first_name ASC,id ASC);""")
89
        conn.execute("""create index if not exists author3 on authors (first_name ASC,id ASC);""")
59
90
        conn.commit()
90
        conn.commit()
60
91
        self.display.sub_output("index author3 created")
91
        self.display.sub_output("index author3 created")
61
92
        self.display.sub_output("Author Data Migration started")
92
        self.display.sub_output("Author Data Migration started")
64
93
        c = conn.cursor()
93
        conn.execute("""insert into authors (id, display_name) select authorid, authorname from authors_temp;""")
65
94
        text = c.execute("""select * from authors """) .fetchall()
94
        conn.commit()
66
95
        self.display.sub_output("authors populated")
67
96
        c = conn.cursor()        
68
97
        text = c.execute("""select * from authors""") .fetchall()
69
95
        for author in text:
98
        for author in text:
83
96
            dispname = author[1]
99
            dispname = author[3]
84
97
            if author[2] == None:
100
            dispname = dispname.replace("'", "") # remove quotes.
85
98
                dispname = dispname.replace("'", "") # remove quotes.
101
            pos = dispname.rfind(" ")
86
99
                pos = dispname.rfind(" ")
102
            afn = dispname[:pos]
87
100
                afn = dispname[:pos]
103
            aln = dispname[pos + 1:len(dispname)]
88
101
                aln = dispname[pos + 1:len(dispname)]
104
            s = "update authors set first_name = '" \
89
102
        #txt = text[2]
105
            + afn + "', last_name = '" + aln + "' where id = " + str(author[0])
90
103
                s = "update authors set display_name = '" + dispname + "', first_name = '" + afn + "', last_name = '" + aln + "' where id = " +str(author[0])
106
            text1 = c.execute(s)
91
104
                text1 = c.execute(s)
107
        conn.commit()
79
105
        conn.commit()
80
106
        conn.execute("""alter table authors drop column authorname;""")
81
107
        conn.commit()
82
108
        conn.close()
92
109
        self.display.sub_output("Author Data Migration Completed")
108
        self.display.sub_output("Author Data Migration Completed")
106
110
        self.display.sub_output("Authors Completed");
109
        conn.execute("""drop table authors_temp;""")
94
111
95
112
    def _v1_9_0_topics(self, database):
96
113
        self.display.sub_output("Topics Started for "+database);
97
114
        conn = sqlite3.connect(self.data_path+os.sep+database)
98
115
        conn.text_factory = str
99
116
        conn.execute("""create table if not exists topics (id integer Primary Key ASC AUTOINCREMENT);""")
100
117
        conn.commit()
101
118
        self.display.sub_output("Topic table created")
102
119
        conn.execute("""alter table topics add column name varchar(128);""")
103
120
        conn.commit()
104
121
        self.display.sub_output("topicname added")
105
122
        conn.execute("""create index if not exists topic1 on topics (name ASC,id ASC);""")
107
123
        conn.commit()
110
        conn.commit()
108
124
        conn.close()
111
        conn.close()
112
125
        self.display.sub_output("index topic1 created")
112
        self.display.sub_output("author_temp dropped")
113
126
113
        self.display.sub_output("Authors Completed")
111
127
        self.display.sub_output("Topics Completed");
114
128
114
115
129
    def _v1_9_0_songbook(self, database):
115
    def _v1_9_0_songbook(self, database):
117
130
        self.display.sub_output("SongBook Started for "+database);
116
        self.display.sub_output("SongBook Started for "+database)
118
131
        conn = sqlite3.connect(self.data_path+os.sep+database)
117
        conn = sqlite3.connect(self.data_path+os.sep+database)
119
132
        conn.execute("""create table if not exists song_books (id integer Primary Key ASC AUTOINCREMENT);""")
118
        conn.execute("""create table if not exists song_books (id integer Primary Key ASC AUTOINCREMENT);""")
120
133
        conn.commit()
119
        conn.commit()
121
134
        self.display.sub_output("SongBook table created")
120
        self.display.sub_output("SongBook table created")
122
135
        conn.execute("""alter table song_books add column name varchar(128);""")
121
        conn.execute("""alter table song_books add column name varchar(128);""")
123
136
        conn.commit()
122
        conn.commit()
125
137
        self.display.sub_output("songbook_name added")
123
        self.display.sub_output("songbook name added")
126
138
        conn.execute("""alter table song_books add column publisher varchar(128);""")
124
        conn.execute("""alter table song_books add column publisher varchar(128);""")
127
139
        conn.commit()
125
        conn.commit()
129
140
        self.display.sub_output("songbook_publisher added")
126
        self.display.sub_output("songbook publisher added")
130
141
        conn.execute("""create index if not exists songbook1 on song_books (name ASC,id ASC);""")
127
        conn.execute("""create index if not exists songbook1 on song_books (name ASC,id ASC);""")
131
142
        conn.commit()
128
        conn.commit()
132
143
        self.display.sub_output("index songbook1 created")
129
        self.display.sub_output("index songbook1 created")
133
@@ -145,45 +131,31 @@
134
145
        conn.commit()
131
        conn.commit()
135
146
        conn.close()
132
        conn.close()
136
147
        self.display.sub_output("index songbook2 created")
133
        self.display.sub_output("index songbook2 created")
166
148
        self.display.sub_output("SongBook Completed");
134
        self.display.sub_output("SongBook Completed")
138
149
139
150
    def _v1_9_0_songtopics(self, database):
140
151
        self.display.sub_output("Songtopics Started for "+database);
141
152
        conn = sqlite3.connect(self.data_path+os.sep+database)
142
153
        conn.execute("""create table if not exists song_topics (song_id integer Primary Key ASC );""")
143
154
        conn.commit()
144
155
        self.display.sub_output("Songtopics table created")
145
156
        conn.execute("""alter table song_topics add column topic_id interger;""")
146
157
        conn.commit()
147
158
        self.display.sub_output("songtopics_topic_id added")
148
159
        conn.execute("""create index if not exists songtopic1 on song_topics (topic_id ASC,song_id ASC);""")
149
160
        conn.commit()
150
161
        self.display.sub_output("index songbook1 created")
151
162
        conn.execute("""create index if not exists songbook2 on song_topics (song_id ASC,topic_id ASC);""")
152
163
        conn.commit()
153
164
        conn.close()
154
165
        self.display.sub_output("index songbook2 created")
155
166
        self.display.sub_output("SongTopics Completed");
156
167
157
168
    def _v1_9_0_songauthors(self, database):
158
169
        self.display.sub_output("SongAuthors Started for "+database);
159
170
        conn = sqlite3.connect(self.data_path+os.sep+database)
160
171
        conn.execute("""alter table songauthors rename to authors_songs;""")
161
172
        conn.commit()
162
173
        conn.close()
163
174
        self.display.sub_output("Table Renamed")
164
175
        self.display.sub_output("SongAuthors Completed");
165
176
167
177
135
168
178
    def _v1_9_0_songs(self, database):
136
    def _v1_9_0_songs(self, database):
170
179
        self.display.sub_output("Songs Started for "+database);
137
        self.display.sub_output("Songs Started for "+database)
171
180
        conn = sqlite3.connect(self.data_path+os.sep+database)
138
        conn = sqlite3.connect(self.data_path+os.sep+database)
175
181
        conn.execute("""alter table songs add column song_book_id interger;""")
139
        conn.execute("""alter table songs rename to songs_temp;""")
176
182
        conn.commit()
140
        conn.commit()
177
183
        self.display.sub_output("songs_song_book_id added")
141
        conn.execute("""create table if not exists songs  (id integer Primary Key ASC AUTOINCREMENT);""")
178
142
        conn.commit()
179
143
        self.display.sub_output("songs table created")
180
144
        conn.execute("""alter table songs add column song_book_id integer;""")
181
145
        conn.commit()
182
146
        self.display.sub_output("songs song_book_id added")
183
147
        conn.execute("""alter table songs add title varchar(255);""")
184
148
        conn.commit()
185
149
        self.display.sub_output("songs title added")
186
150
        conn.execute("""alter table songs add lyrics text;""")
187
151
        conn.commit()
188
152
        self.display.sub_output("songs lyrics added")
189
184
        conn.execute("""alter table songs add column verse_order varchar(128);""")
153
        conn.execute("""alter table songs add column verse_order varchar(128);""")
190
185
        conn.commit()
154
        conn.commit()
191
186
        self.display.sub_output("songs verse_order added")
155
        self.display.sub_output("songs verse_order added")
192
156
        conn.execute("""alter table songs add copyright varchar(255);""")
193
157
        conn.commit()
194
158
        self.display.sub_output("songs copyright added")
195
187
        conn.execute("""alter table songs add column comments text;""")
159
        conn.execute("""alter table songs add column comments text;""")
196
188
        conn.commit()
160
        conn.commit()
197
189
        self.display.sub_output("songs comments added")
161
        self.display.sub_output("songs comments added")
198
@@ -208,50 +180,75 @@
199
208
        self.display.sub_output("index songs1 created")
180
        self.display.sub_output("index songs1 created")
200
209
        conn.execute("""create index if not exists songs2 on songs (search_title ASC,id ASC);""")
181
        conn.execute("""create index if not exists songs2 on songs (search_title ASC,id ASC);""")
201
210
        conn.commit()
182
        conn.commit()
202
211
        conn.close()
203
212
        self.display.sub_output("index songs2 created")
183
        self.display.sub_output("index songs2 created")
246
213
        self.display.sub_output("Songs Completed");
184
247
214
185
        conn.execute("""insert into songs (id, title, lyrics, copyright, search_title, search_lyrics, song_book_id) 
248
215
186
            select songid,  songtitle, lyrics, copyrightinfo, 
249
216
187
            replace(replace(replace(replace(replace(replace(replace(replace(replace(songtitle,  '&', 'and'), ',', ''), ';', ''), ':', ''), '(', ''), ')', ''), '{', ''), '}',''),'?',''), 
250
217
    def _v1_9_0_songs_update(self, database):
188
            replace(replace(replace(replace(replace(replace(replace(replace(replace(lyrics,  '&', 'and'), ',', ''), ';', ''), ':', ''), '(', ''), ')', ''), '{', ''), '}',''),'?',''),
251
218
        self.display.sub_output("Songs Started for "+database);
189
            0
252
219
        self.db = create_engine("sqlite:///"+self.data_path+os.sep+database, encoding='utf-8' , convert_unicode=False, assert_unicode=False)
190
            from songs_temp;""")
253
220
191
254
221
        self.db.echo = True
192
        conn.commit()
255
222
        metadata.bind = self.db
193
        self.display.sub_output("songs populated")
256
223
        metadata.bind.echo = False
194
        conn.execute("""drop table songs_temp;""")
257
224
        Session = scoped_session(sessionmaker(autoflush=True, autocommit=False))
195
        conn.commit()
258
225
        Session.configure(bind=self.db)
196
        conn.close()
259
226
        #create missing table
197
        self.display.sub_output("songs_temp dropped")
260
227
        songs_topics_table.create()
198
261
228
        songs = Session.query(Song).all()
199
        self.display.sub_output("Songs Completed")
262
229
        for song in songs:
200
263
230
            t=song.title.replace("&", "and")
201
    def _v1_9_0_topics(self, database):
264
231
            t=t.replace("'", "")
202
        self.display.sub_output("Topics Started for "+database)
265
232
            t=t.replace(",", "")
203
        conn = sqlite3.connect(self.data_path+os.sep+database)
266
233
            t=t.replace(";", "")
204
        conn.text_factory = str
267
234
            t=t.replace(":", "")
205
        conn.execute("""create table if not exists topics (id integer Primary Key ASC AUTOINCREMENT);""")
268
235
            t=t.replace("(", "")
206
        conn.commit()
269
236
            t=t.replace(")", "")
207
        self.display.sub_output("Topic table created")
270
237
            t=t.replace("{", "")
208
        conn.execute("""alter table topics add column name varchar(128);""")
271
238
            t=t.replace("}", "")
209
        conn.commit()
272
239
            t=t.replace("?", "")
210
        self.display.sub_output("topicname added")
273
240
            song.search_title = t
211
        conn.execute("""create index if not exists topic1 on topics (name ASC,id ASC);""")
274
241
            t=song.lyrics.replace("&", "and")
212
        conn.commit()
275
242
            t=t.replace("'", "")
213
        conn.close()
276
243
            t=t.replace(",", "")
214
        self.display.sub_output("index topic1 created")
277
244
            t=t.replace(";", "")
215
278
245
            t=t.replace(":", "")
216
        self.display.sub_output("Topics Completed")
279
246
            t=t.replace("(", "")
217
280
247
            t=t.replace(")", "")
218
    def _v1_9_0_songauthors(self, database):
281
248
            t=t.replace("{", "")
219
        self.display.sub_output("SongAuthors Started for "+database);
282
249
            t=t.replace("}", "")
220
        conn = sqlite3.connect(self.data_path+os.sep+database)
283
250
            t=t.replace("?", "")
221
        conn.execute("""create table if not exists authors_songs (author_id integer);""")
284
251
            song.search_lyrics = t
222
        conn.commit()
285
252
            song.song_book_id = 0
223
        self.display.sub_output("authors_songs table created")
286
253
            Session.save_or_update(song)
224
        conn.execute("""alter table authors_songs add column song_id integer;""")
287
254
            Session.commit()
225
        conn.commit()
288
226
        conn.execute("""insert into authors_songs (author_id, song_id) select authorid, songid from songauthors;""")
289
227
        conn.commit()
290
228
        self.display.sub_output("authors_songs populated")
291
229
        conn.execute("""drop table songauthors;""")
292
230
        conn.commit()
293
231
        self.display.sub_output("songauthors dropped")
294
232
        conn.close()
295
233
        self.display.sub_output("SongAuthors Completed")
296
234
297
235
    def _v1_9_0_songtopics(self, database):
298
236
        self.display.sub_output("Songtopics Started for "+database);
299
237
        conn = sqlite3.connect(self.data_path+os.sep+database)
300
238
        conn.execute("""create table if not exists song_topics (song_id integer);""")
301
239
        conn.commit()
302
240
        self.display.sub_output("Songtopics table created")
303
241
        conn.execute("""alter table song_topics add column topic_id integer;""")
304
242
        conn.commit()
305
243
        self.display.sub_output("songtopics_topic_id added")
306
244
        conn.execute("""create index if not exists songtopic1 on song_topics (topic_id ASC,song_id ASC);""")
307
245
        conn.commit()
308
246
        self.display.sub_output("index songtopic1 created")
309
247
        conn.execute("""create index if not exists songtopic2 on song_topics (song_id ASC,topic_id ASC);""")
310
248
        conn.commit()
311
249
        conn.close()
312
250
        self.display.sub_output("index songtopic2 created")
313
251
        self.display.sub_output("SongTopics Completed")
314
255
252
315
256
    def run_cmd(self, cmd):
253
    def run_cmd(self, cmd):
316
257
        f_i, f_o  = os.popen4(cmd)
254
        f_i, f_o  = os.popen4(cmd)
317
258
255
318
=== modified file 'openlpcnv.pyw' (properties changed: -x to +x)
Status:	Superseded
Proposed branch:	lp:~j-corwin/openlp/migration
Merge into:	lp:openlp
Diff against target:	None lines
To merge this branch:	bzr merge lp:~j-corwin/openlp/migration
Related bugs:	Link a bug report
Reviewer	Review Type	Date Requested	Status
Raoul Snyman		2009-05-02	Needs Fixing on 2009-05-02
Review via email: mp+6124@code.launchpad.net