SQL to import from an Ombi database into an Overseerr database

README.md

These queries were tested using DB Browser for SQLite, with the Overseerr database as the primary database, and the Ombi database attached as Ombi.

Run the SQL files in order:

1. movies.sql
2. tv.sql
3. seasons.sql

movies.sql

-- Uncomment this line if things look correct!
-- INSERT OR REPLACE INTO media_request
SELECT
	(SELECT media_request.id FROM media_request WHERE media_request.mediaId = media.id) AS "id",
	'2' AS "status",
	datetime(Ombi.MovieRequests.RequestedDate) AS "createdAt",
	datetime(MAX(
		coalesce(Ombi.MovieRequests.MarkedAsAvailable, -1),
		coalesce(Ombi.MovieRequests.MarkedAsApproved, -1),
		coalesce(Ombi.MovieRequests.RequestedDate, -1)
	)) AS "updatedAt",
	'movie' AS "type",
	media.id AS "mediaId",
	user.id AS "requestedById",
	'1' AS "modifiedById",
	'0' AS "is4k",
	'0' AS "serverId",
	'1' AS "profileId",
--	'/movies/' AS "rootFolder"
	NULL AS "rootFolder"
-- 	Ombi.MovieRequests.Title
FROM Ombi.MovieRequests, Ombi.AspNetUsers, user, media
WHERE
	Ombi.AspNetUsers.Id = Ombi.MovieRequests.RequestedUserId
	AND user.email = Ombi.AspNetUsers.Email
	AND media.mediaType = 'movie'
	AND (
		media.imdbId = Ombi.MovieRequests.ImdbId
		OR media.tmdbId = Ombi.MovieRequests.TheMovieDbId
	)
-- ORDER BY Ombi.MovieRequests.Id

seasons.sql

-- Uncomment this line if things look correct!
-- INSERT OR REPLACE INTO season_request
SELECT
	(SELECT season_request.id FROM season_request WHERE season_request.requestId = media_request.id) AS "id",
-- 	Ombi.TvRequests.Title,
	Ombi.SeasonRequests.SeasonNumber AS "seasonNumber",
	'2' AS "status",
	datetime(Ombi.ChildRequests.RequestedDate) AS "createdAt",
	datetime(MAX(
		coalesce(Ombi.ChildRequests.MarkedAsAvailable, -1),
		coalesce(Ombi.ChildRequests.MarkedAsApproved, -1),
		coalesce(Ombi.ChildRequests.RequestedDate, -1)
	)) AS "updatedAt",
	media_request.id AS "requestId"
FROM Ombi.TvRequests, Ombi.AspNetUsers, user, media, Ombi.ChildRequests, Ombi.SeasonRequests, media_request
WHERE
	Ombi.AspNetUsers.Id = Ombi.ChildRequests.RequestedUserId
	AND user.email = Ombi.AspNetUsers.Email
	AND media.mediaType = 'tv'
	AND (
		media.imdbId = Ombi.TvRequests.ImdbId
		OR media.tvdbId = Ombi.TvRequests.TvDbId
	)
	AND Ombi.ChildRequests.ParentRequestId = Ombi.TvRequests.Id
	AND Ombi.SeasonRequests.ChildRequestId = Ombi.ChildRequests.Id
	AND media_request.mediaId = media.id
-- ORDER BY media_request.id, Ombi.SeasonRequests.SeasonNumber

tv.sql

-- Uncomment this line if things look correct!
--INSERT OR REPLACE INTO media_request
SELECT
	(SELECT media_request.id FROM media_request WHERE media_request.mediaId = media.id) AS "id",
	'2' AS "status",
	datetime(Ombi.ChildRequests.RequestedDate) AS "createdAt",
	datetime(MAX(
		coalesce(Ombi.ChildRequests.MarkedAsAvailable, -1),
		coalesce(Ombi.ChildRequests.MarkedAsApproved, -1),
		coalesce(Ombi.ChildRequests.RequestedDate, -1)
	)) AS "updatedAt",
	'tv' AS "type",
	media.id AS "mediaId",
	user.id AS "requestedById",
	'1' AS "modifiedById",
	'0' AS "is4k",
	'0' AS "serverId",
	'1' AS "profileId",
-- 	'/tv/Anime/' AS "rootFolder"
	NULL AS "rootFolder"
-- 	Ombi.TvRequests.Title
FROM Ombi.TvRequests, Ombi.AspNetUsers, user, media, Ombi.ChildRequests
WHERE
	Ombi.AspNetUsers.Id = Ombi.ChildRequests.RequestedUserId
	AND user.email = Ombi.AspNetUsers.Email
	AND media.mediaType = 'tv'
	AND (
		media.imdbId = Ombi.TvRequests.ImdbId
		OR media.tvdbId = Ombi.TvRequests.TvDbId
	)
	AND Ombi.ChildRequests.ParentRequestId = Ombi.TvRequests.Id
-- ORDER BY mediaId
-- ORDER BY Ombi.TvRequests.Id