OpenLP

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

  1. migration
  2. Merge into trunk
Proposed by Jonathan Corwin
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
Reviewer Review Type Date Requested Status
Raoul Snyman Needs Fixing
Review via email: mp+6124@code.launchpad.net

This proposal has been superseded by a proposal from 2009-05-02.

To post a comment you must log in.
Revision history for this message
Jonathan Corwin (j-corwin) wrote :

Attempt to fix song migration so it matches current file definitions.
Remove the need to edit the dmp file as part of the migration

Revision history for this message
Raoul Snyman (raoul-snyman) wrote :

Muhahaha! The Style Police has arrived!

(Yes, this is mostly old code, but it needs to be said!)

All strings are single-quoted and unicode:

  self.display.output("Songs processing started")

  should be:

  self.display.output(u'Songs processing started')

Why are you adding columns one at a time?

  conn.execute("""create table authors (id integer primary key ASC AUTOINCREMENT);""")
  conn.commit()
  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 added")
  conn.execute("""alter table authors add column last_name varchar(128);""")
  conn.commit()

  Shouldn't that rather be:

  conn.execute("""
    create table authors (
      id integer primary key ASC AUTOINCREMENT,
      first_name varchar(128),
      last_name varchar(128),
      display_name varchar(255)
    );""")
  conn.commit()
  self.display.sub_output("authors table created")

Also name variables better:

  # I'm not sure what these two variables are supposed to be
  def run_cmd(self, cmd):
      f_i, f_o = os.popen4(cmd)

review: Needs Fixing
Revision history for this message
Jonathan Corwin (j-corwin) wrote :

On Sat, May 2, 2009 at 9:31 PM, Raoul Snyman <
<email address hidden>> wrote:

> Review: Needs Fixing
> Muhahaha! The Style Police has arrived!
>
> (Yes, this is mostly old code, but it needs to be said!)
>
> All strings are single-quoted and unicode:
>
> self.display.output("Songs processing started")
>
> should be:
>
> self.display.output(u'Songs processing started')

Was already there, nowt to do with me

>
> Why are you adding columns one at a time?
>
> conn.execute("""create table authors (id integer primary key ASC
> AUTOINCREMENT);""")
> conn.commit()
> 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 added")
> conn.execute("""alter table authors add column last_name varchar(128);""")
> conn.commit()
>
> Shouldn't that rather be:

> conn.execute("""
> create table authors (
> id integer primary key ASC AUTOINCREMENT,
> first_name varchar(128),
> last_name varchar(128),
> display_name varchar(255)
> );""")
> conn.commit()
> self.display.sub_output("authors table created")

I agree, but again was just copying what was already there since I didn't
know if the original author had done it that way on purpose

>
> Also name variables better:
>
> # I'm not sure what these two variables are supposed to be
> def run_cmd(self, cmd):
> f_i, f_o = os.popen4(cmd)

I didn't touch this function and have no idea what it does.

All I was doing was updating existing code so it worked... I wasn't planning
on rewriting the whole existing
module.

Revision history for this message
Jonathan Corwin (j-corwin) wrote :

> Muhahaha! The Style Police has arrived!

Changes pushed.

lp:~j-corwin/openlp/migration updated
439. By Raoul Snyman

Added images

440. By Raoul Snyman

Merged Jonathan Corwin's changes to the database migration script.

441. By Tim Bentley

Start adding serviceitem code and SlideController code.
Fix some Theme bugs as well

442. By Raoul Snyman

Added slide images.

443. By Tim Bentley

Various fixes and more additions of SlideController stuff
Add ButtonBar for SlideControllers

444. By Tim Bentley

<email address hidden>

445. By Tim Bentley

<email address hidden>

446. By Tim Bentley

<email address hidden>

447. By Tim Bentley

<email address hidden>

448. By Tim Bentley

<email address hidden>

449. By Tim Bentley

<email address hidden>

450. By Tim Bentley

fix copyright symbol error

451. By Raoul Snyman

Changed the SlideController to look MUCH nicer.

452. By Tim Bentley

<email address hidden>

453. By Raoul Snyman

A number of style cleanups.

454. By Tim Bentley

Fix Cancel bug on BibleImport screen
Fix odi error on PresentationDialog

455. By Raoul Snyman

Merged config changes into trunk.

456. By Tim Bentley

<email address hidden>

457. By Tim Bentley

Fix crash

458. By Tim Bentley

<email address hidden>

459. By Raoul Snyman

Merged proposal #7122

460. By Raoul Snyman

Removed erroneous "show"ing of the display form.

461. By Tim Bentley

Fix bible bugs and make fomatting corrections

462. By Raoul Snyman

Split ServiceManager toolbar into 2 parts, resized docks to a smaller size again thanks to narrower ServiceManager.

463. By Tim Bentley

Add OOS and speed up Renderer

464. By Raoul Snyman

Pulling in QImage changes from renderer branch

465. By Tim Bentley

add OOS code and speed up renderer
Add re-theming in servicemanager when theme changes.
Sort out Renderer to prevent not relevent calls

466. By Tim Bentley

Songs plugin phase 1 complete

467. By Tim Bentley

Finish the cleanup of Songs.

468. By Tim Bentley

Mass Unicode conversion.

469. By Tim Bentley

ode cleanups from last merge and Bible Custom changes
Remove 2.6 deprication

470. By Tim Bentley

ServiceManager changes
Small bug fixes
New OOS format

471. By Tim Bentley

OOS now works with Images , Bibles, Songs and Custom
Fixes to Bible plugin and clean up of Images
Mennu fixes

472. By Tim Bentley

Change default Unicode handling

473. By Tim Bentley

Page Up/Down works on SlideController
Search for Authors on Songs now work

474. By Raoul Snyman

Merged changed keyboard events.

475. By Tim Bentley

Fix error on Song Saving

476. By Tim Bentley

Various fixes and addition of song migation code

477. By Martin Thompson

Merged media manager refactoring

478. By Raoul Snyman

Merged Andrew Lok's video tests into trunk

479. By Raoul Snyman

Merged assert fixes.

480. By Tim Bentley

Plugable slide controllers
Start of OpenOffice Plugin.
Bug fixes

481. By Tim Bentley

fixups to mediamanager
Bug fixes
Plugable Toolbar

482. By Tim Bentley

Martins mediaItem Changes
SlideController rewrite
Plugable SlideControllerBars
Bug fixes
Better Rendering size

483. By Tim Bentley

Remove unused stuff
Add more bits to ImageSlideController bar
Clean up ThemeManager
Add OOS saving

484. By Raoul Snyman

Fixed up a few logging things, a few bits of indentation, and resolved some conflicts from the last update.

485. By Raoul Snyman

Merged changes to Bible plugin into trunk.

486. By Raoul Snyman

Put the settings tab back in after accidentally removing it... silly me!

487. By Raoul Snyman

Merged docstrings changes.

488. By Raoul Snyman

Merging from docstrings branch.

489. By Tim Bentley

Cleanups
Plugin slidepreviers
documentatation
removal of old files

490. By Raoul Snyman

Removing built documentation - this is not needed, we only need to store the documentation source files in version control.

491. By Raoul Snyman

Added ignore status to directories in the documentation's build directory.

492. By Tim Bentley

Bug fixes from the wiki

493. By Tim Bentley

Various bug fixes and code improvements.

494. By Raoul Snyman

Merged in the changes from the biblefixes branch.

495. By Tim Bentley

Fix song editing so it works
Added now images for Song editors
Fix up Alert code so now works.

496. By Tim Bentley

Fix up song dialog errors
Fix servicemanager key entry
Fix servicemanager state handling

497. By Raoul Snyman

Merged in changes from the songmaintenance branch

498. By Raoul Snyman

Merged in some changes for the song maintenance form.

499. By Tim Bentley

New features and fixes.

500. By Tim Bentley

Bug fixes from last merge

501. By Raoul Snyman

Merged in changes from my song maintenance branch.

502. By Raoul Snyman

Merge from songmaintenance branch.

503. By Tim Bentley

New bible Import Screen
Transparent Theme backgrounds
Global Theme Handling improvements

-------This line and the following will be ignored --------------

modified:
  openlp/core/lib/renderer.py
  openlp/core/lib/themexmlhandler.py
  openlp/core/ui/amendthemeform.py
  openlp/core/ui/servicemanager.py
  openlp/core/ui/thememanager.py
  openlp/core/ui/themestab.py
  openlp/plugins/bibles/forms/bibleimportdialog.py
  resources/forms/bibleimportdialog.ui
pending merges:
  Tim Bentley 2009-08-07 finish Theme handling corrections
    Tim Bentley 2009-08-07 Default handling allows editing and blocks deletes
    Tim Bentley 2009-08-07 New bible import form
    Tim Bentley 2009-08-06 [merge] Head
    Tim Bentley 2009-08-06 Standardize row hight in renderer

504. By Tim Bentley

Lets save songs now

505. By Raoul Snyman

Merge from bugfixes

506. By Tim Bentley

Remote pluging
EventManager Fixes
Cleanups
Renderer Performance improvements

507. By Tim Bentley

Updates

508. By Tim Bentley

Lets put the bracket in the correct place

509. By Tim Bentley

Plugin Screen added
Presentation Plugin started
Remotes now configurable

510. By Tim Bentley

Many fixes and corrections
Adding Presentation code (start)
Add error message to code

511. By Tim Bentley

SlideController clean up
Image Plugin Cleanup

512. By Tim Bentley

Clean up Event Processing

513. By Jon Tibble

Committing Jon Tibble's merge proposal.

514. By Maikel Stuivenberg

Merged in lp:~maikels/openlp/myfixes

515. By Tim Bentley

Sort out SlideController

516. By Tim Bentley

Various changes see loog for details

517. By Jon Tibble

Merge for John

518. By Jon Tibble

Merge for Jon

519. By Tim Bentley

Performance Improvements and code fixes

520. By Raoul Snyman

Merge from lp:~raoul-snyman/openlp/songmaintenance

521. By Jon Tibble

Merged in lp:~meths/openlp/trivialfixes

522. By Tim Bentley

New features and fixes

523. By Tim Bentley

Add Next slide to Presentations
Start with settings manager

524. By Raoul Snyman

Merged lp:~raoul-snyman/openlp/docstrings into trunk.

525. By Jon Tibble

Latest merge from lp:~meths/openlp/trivialfixes

526. By Tim Bentley

Various changes

527. By Jon Tibble

For Jon

528. By Tim Bentley

Song and Custom cleanups

529. By Jon Tibble

For Jon

530. By Raoul Snyman

Merged changes in from lp:~raoul-snyman/openlp/songmaintenance

531. By Jon Tibble

Merged in lp:~meths/openlp/trivialfixes

532. By Raoul Snyman

Merged in lp:~raoul-snyman/openlp/docstrings

533. By Jon Tibble

Merged in lp:~meths/openlp/trivialfixes

534. By Jon Tibble

For Jon
Fix themes

535. By Jon Tibble

For Jon
Fix themes 2

536. By Jon Tibble

For Jon

537. By Raoul Snyman

Merge from lp:~raoul-snyman/openlp/bugfixes

538. By Tim Bentley

Updates to Presentations etc

539. By Maikel Stuivenberg

For Maikel

540. By Tim Bentley

Themes and Timer points

541. By Tim Bentley

Fix merge error

542. By Jon Tibble

For Jon

543. By Tim Bentley

Renderer handling changes

544. By Jon Tibble

Merged in lp:~meths/openlp/trivialfixes

545. By Jon Tibble

For Jon

546. By Jon Tibble

For Jon

547. By Jonathan Corwin

for jonathan

548. By Tim Bentley

presentation fixes

549. By Tim Bentley

Correction from == to is

550. By Jon Tibble

Merge log.exception() conversion

551. By Tim Bentley

Arrival of the Audit

552. By Tim Bentley

Plugin cleanup

553. By Maikel Stuivenberg

Menu changes

554. By Jon Tibble

Dock Widget Refactoring

555. By Jon Tibble

Fix service loading on Windows

556. By Tim Bentley

Audit Fixes

557. By Jon Tibble

Refactor services

558. By Jon Tibble

Cleanup code and fixes

559. By Tim Bentley

Renderer Fixes

560. By Raoul Snyman

Merged in lp:~raoul-snyman/openlp/bugfixes

561. By Tim Bentley

rendering fixes

562. By Jon Tibble

Song editor fixes

563. By Jon Tibble

Coding Standards

564. By Tim Bentley

Best merge ever

565. By Tim Bentley

Audit changes and clean up

566. By Jonathan Corwin

Merge from lp:~j-corwin/openlp/presentations

567. By Jon Tibble

Code clean ups

568. By Tim Bentley

Audit Changes to database

569. By Jonathan Corwin

Merge from lp:~j-corwin/openlp/presentations

570. By Jon Tibble

PPTViewer fixes

571. By Jon Tibble

Imports clean up

572. By Tim Bentley

Plugin changes to Audit, Song and Custom

573. By Jon Tibble

None testing and clean ups

574. By Tim Bentley

Plugin updates

575. By Jonathan Corwin

Jons Presentation merge with hack to fix conflicts

576. By Tim Bentley

Audit changes and cleanups

577. By Jonathan Corwin

Presentation merge with gotos

578. By Jon Tibble

Fix presentation starting

579. By Jon Tibble

Use buildIcon and fixes

580. By Tim Bentley

Style cleanups

581. By Jonathan Corwin

Presentations made it at last

582. By Jonathan Corwin

Presentation dll

583. By Tim Bentley

Start of about Text

584. By Jon Tibble

Dialogs, Exceptions and Toolbars

585. By Tim Bentley

Update PluginList Window

586. By Jonathan Corwin

Presentation updates

587. By Tim Bentley

Add Ability to hide plugins

588. By Jonathan Corwin

More Presentation improvements

589. By Tim Bentley

More hiding code changes

590. By Tim Bentley

Fix song issue

591. By Jon Tibble

Presentation cleanups

592. By Jon Tibble

Bible rendering fixes

593. By Tim Bentley

Apply fixes for Jon

594. By Raoul Snyman

Merged in lp:~raoul-snyman/openlp/pluginlist

595. By Tim Bentley

Plugin hinding comes to town

596. By Jonathan Corwin

More Presentation fixes

597. By Tim Bentley

Various changes and fixes

598. By Jonathan Corwin

TLC for the asv

599. By Raoul Snyman

Merged in lp:~raoul-snyman/openlp/versionfix

600. By Tim Bentley

Add Web Bibles to Advanced Tab and other nice features

601. By Jonathan Corwin

Presentation changes

602. By Tim Bentley

Update config files for deployment

603. By Jonathan Corwin

Windows/Linux sqlite2 -> sqlite3 migration in one step

604. By Jonathan Corwin

Fix code standard issues. Remove redundant modules

605. By Jonathan Corwin

Head

606. By Jonathan Corwin

Bible migration

607. By Jonathan Corwin

Head

608. By Jonathan Corwin

Bible migration

609. By Jonathan Corwin

Commit verses at end

610. By Jonathan Corwin

Head

611. By Jonathan Corwin

Commented out two lines without realising it. I blame Noah

Unmerged revisions

Preview Diff

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