Update Find Sql for Umbraco v12

[paulsterling]

This is just a placeholder since I started working with the code (the find sql statement in particular) in this package and Umbraco v12. I found that, when running locally using SqlLite that the nested JOIN statements results in a syntax error from SqlLite. The docs seem to indicate nested JOINS are supported...but not for me. I reworked the sql in Cogworks.FindAndReplace.Web.API.FindPhrase to flatten the JOINS.

I also noted that SqlLite report that [umbracoDocumentVersion].[id] does not exist...it clearly does...so that is commented out. When left in the SqlLite compiler returns this error message:

Error while executing SQL query on database 'Umbraco.sqlite': no such column: umbracoDocumentVersion.id

The current sql statement that works as expected with v12 and SqlLite is:

SELECT
    [upd].[varcharValue] AS [VarcharValue],
    [upd].[textValue] AS [TextValue],
    [cv].[id] AS [VersionId],
    [pcv].[text] AS [NodeName],
    [upt].[Alias] AS [PropertyAlias],
    [upt].[Name] AS [PropertyName]
FROM [umbracoDocument]
INNER JOIN [umbracoContent] ON [umbracoContent].[nodeId] = [umbracoDocument].[nodeId]
INNER JOIN [umbracoNode] ON [umbracoNode].[id] = [umbracoContent].[nodeId]
INNER JOIN [umbracoContentVersion] [cv] ON [cv].[nodeId] = [umbracoDocument].[nodeId]
--INNER JOIN [umbracoDocumentVersion] [dv] ON [umbracoDocumentVersion].[id] = [cv].[id]
LEFT JOIN [umbracoContentVersion] [pcv] ON [pcv].[nodeId] = [umbracoDocument].[nodeId]
INNER JOIN [umbracoDocumentVersion] [pdv] ON [pdv].[id] = [pcv].[id] AND [pdv].[published] = 1
LEFT JOIN [umbracoContentVersionCultureVariation] [ccv] ON [cv].[id] = [ccv].[versionId]
--INNER JOIN [umbracoLanguage] [lang] ON [lang].[id] = [ccv].[languageId] AND [lang].[languageISOCode] = 'en-US'
INNER JOIN [umbracoPropertyData] [upd] ON [upd].[versionId] = [cv].[id]
INNER JOIN [cmsPropertyType] [upt] ON [upt].[id] = [upd].[propertyTypeId]
WHERE ([cv].[current] = 1)
    AND ([umbracoDocument].[published] = 1)
    AND ([umbracoNode].[path] LIKE upper('%YOUR HOME NODE ID%'))
    AND
    (
        (
            [upd].[textValue] is not null AND [upd].[textValue] LIKE '%YOUR SEARCH TERM%'
        )
        OR
        (
            [upd].[varcharValue] is not null AND [upd].[varcharValue] LIKE '%YOUR SEARCH TERM%'
        )
    )
ORDER BY ([umbracoDocument].[nodeId])

I should note that I did not try the sql with SqlServer (any version) so the issues I noted may not exist for that RDBMS.