MobileSheets Forums

MobileSheets Forums > Support > MobileSheets Companion > DB-Column Edit
Full Version: DB-Column Edit
You're currently viewing a stripped down version of our content. View the full version with proper formatting.

Vibraphon

07-13-2016, 05:49 PM
Hi,
  1. I am missing some of the song edit fields as column (Keywords, Tempos, ...)
  2. I am missing the possibility to move content for a selection between columns, for example from "keywords" to "genre", or from "composer" to "artists". Background: During growing of the database one of my friends used for the songs he added "keywords" instead of "genre". I would like to correct this. Maybe there is a possibility to use other database programs as f.e. MS-ACCESS to do such modifications?
Paul

Zubersoft

07-14-2016, 02:01 AM
It's just a SQLite database that is used to store all of the data in the library, so a simple query can transfer all of the data fairly quickly. If you expose the database, copy it to your PC, and then use a program to execute the queries to update the appropriate tables, it shouldn't take long at all to do what you desire. I can provide the query for you too if you'd like.

Vibraphon

07-18-2016, 03:40 AM
(07-14-2016, 02:01 AM)Zuberman Wrote: [ -> ]...  I can provide the query for you too if you'd like. ...
Thank you for your answer and your offer.

I have installed the firefox addon for SQLite and I am now able to modify the database.
But I haven't yet found the table for the "keywords".
It would be very nice, if you would provide a query to move the conten of "keywords" to "genre".

Thank you
Paul

Zubersoft

07-18-2016, 06:50 PM
This took me quite awhile to figure out (it was a good learning experience), but if you run these two queries, it will convert the keywords into genres, and update the genres to link back to the songs:


Code:
With RECURSIVE list( element, remainder ) AS (
   SELECT NULL AS element, Keywords From Songs AS remainder
       UNION ALL
   SELECT
       CASE
           WHEN INSTR( remainder, ',' )>0 THEN
               SUBSTR( remainder, 0, INSTR( remainder, ',' ) )
           ELSE
               remainder
       END AS element,
       CASE
           WHEN INSTR( remainder, ',' )>0 THEN
               SUBSTR( remainder, INSTR( remainder, ',' )+1 )
           ELSE
               NULL
       END AS remainder
   FROM list
   WHERE remainder IS NOT NULL
) INSERT INTO Genres(Type) select DISTINCT trim(element) from list WHERE element IS NOT NULL and NOT EXISTS(Select Type FROM Genres where Type=trim(element))
       
With RECURSIVE list( element, remainder, sid, genreid ) AS (
   SELECT NULL AS element, s.Keywords, s.Id, g.Id From Songs s JOIN Genres g On s.Keywords LIKE '%'||g.Type||'%'
       UNION ALL
   SELECT
       CASE
           WHEN INSTR( remainder, ',' )>0 THEN
               SUBSTR( remainder, 0, INSTR( remainder, ',' ) )
           ELSE
               remainder
       END AS element,
       CASE
           WHEN INSTR( remainder, ',' )>0 THEN
               SUBSTR( remainder, INSTR( remainder, ',' )+1 )
           ELSE
               NULL
       END AS remainder,sid,genreid
   FROM list
   WHERE remainder IS NOT NULL
) INSERT INTO GenresSongs (GenreId,SongId) Select DISTINCT genreid,sid From list WHERE element IS NOT NULL


Note that those queries split up the keywords by commas, and create genres from each part of the keywords. If you don't want this behavior (i.e. you don't use commas to specify multiple keywords), then you can run the simplified queries below:


Code:
INSERT INTO Genres(Type) select DISTINCT Keywords From Songs WHERE NOT EXISTS(Select Type FROM Genres where Type=Keywords)

INSERT INTO GenresSongs (GenreId,SongId) Select DISTINCT s.Id, g.Id From Songs s JOIN Genres g On s.Keywords=g.Type


I hope that helps you out.

Mike

Vibraphon

07-21-2016, 03:30 AM
Thank you, Mike.

It took me some time to recognize that I have to make the two queries step by step, but something went wrong.

As I did not use commas to separate keywords, I first tried the two simple queries. But they created genres for nearly all songs with incredible many genres (see picture attached).
So I replaced the database by my backup and tried the more complex queries.

The complex queries created more new genres (from 46 to 101) including one "<blank>" genre.
Several genres got doubled "Med.", "Med." on songs.
A Song with the keyword "Bright Latin or Swing" at the end had the following genres:
"Bright", "BrighT", "Bright Latin", "Bright Latin or Swing", "Latin", "Swing", "<Blank>"

So the query created from every word (instead of "or") and every combination a genre and added this to the song.
I am not used with SQL so I don't know how to modify the query.

If everything would work well it would be good to have an additional query to delete all "keywords". I think that is easy for you unfortunaly not for me.

Thank you
Paul

Zubersoft

07-21-2016, 08:19 AM
If you want to send me your database file, I can fix it for you. I can also remove the keywords after converting them to genres. For reference, I believe that query would be:

UPDATE Songs SET Keywords = ''

That would set every Keywords field for every song to blank

Vibraphon

08-09-2016, 11:33 PM
(07-21-2016, 08:19 AM)Zuberman Wrote: [ -> ]If you want to send me your database file, I can fix it for you.  
Thank You Mike. I think I got it by myself.
MobileSheets Forums > Support > MobileSheets Companion > DB-Column Edit