[Zubersoft]

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