1

I migrated the database table between mssql to mysql. And now I want to migrate a view between mssql to mysql database and I try to do that manually. For mysql I use the workbench. The view in mssql database is the below:

    SELECT  
    u.Name, 
    u.Rights, 
    u.FirstName, 
    u.LastName, 
    u.IsDeleted, 
    activities.SiteName, 
    activities.RegionID, 
    activities.RegionName, 
    activities.ActivityID, 
    activities.DateTime, 
    activities.UserID, 
    activities.IPAddress, 
    activities.ClientID, 
    activities.SiteID, 
    activities.VariableID, 
    activities.ActivityActionID, 
    activities.Description, 
    activities.LogActionDescription, 
    activities.DeviceName, 
    activities.VariableName
FROM    Security.[User] AS u 
INNER JOIN
(SELECT a.SiteName, 
        a.RegionID, 
        a.RegionName, 
        a.ActivityID, 
        a.DateTime, 
        a.UserID, 
        a.IPAddress, 
        a.ClientID, 
        a.SiteID, 
        a.VariableID, 
        a.ActivityActionID, 
        a.Description, 
        a.LogActionDescription, 
        v.DeviceName, 
        v.VariableName
FROM  dbo.v_ReportVariables AS v 
RIGHT OUTER JOIN
    (SELECT s.SiteName, 
            s.RegionID, s.RegionName, [Log].Activity.ActivityID, 
            [Log].Activity.DateTime, [Log].Activity.UserID, [Log].Activity.IPAddress, 
            [Log].Activity.ClientID, [Log].Activity.SiteID, [Log].Activity.VariableID, 
            [Log].Activity.ActivityActionID, [Log].Activity.Description, 
            [Log].ActivityAction.Description AS LogActionDescription
        FROM  dbo.v_Sites AS s 
        RIGHT OUTER JOIN
            [Log].Activity ON s.SiteID = [Log].Activity.SiteID 
        INNER JOIN [Log].ActivityAction ON [Log].Activity.ActivityActionID = [Log].ActivityAction.ActivityActionID) AS a 
        ON v.VariableID = a.VariableID
    ) AS activities ON u.UserID = activities.UserID

when I migrated the database tables in mysql the names of tables changed. For example when the name in mssql it was 'Security.User' the name in mysql changed to security_user. I tried to create manually this above view from mssql to myssql changing the name of tables as below:

 SELECT  
    u.Name, 
    u.Rights, 
    u.FirstName, 
    u.LastName, 
    u.IsDeleted, 
    activities.SiteName, 
    activities.RegionID, 
    activities.RegionName, 
    activities.ActivityID, 
    activities.DateTime, 
    activities.UserID, 
    activities.IPAddress, 
    activities.ClientID, 
    activities.SiteID, 
    activities.VariableID, 
    activities.ActivityActionID, 
    activities.Description, 
    activities.LogActionDescription, 
    activities.DeviceName, 
    activities.VariableName
FROM    Security_User AS u 
INNER JOIN
(SELECT a.SiteName, 
        a.RegionID, 
        a.RegionName, 
        a.ActivityID, 
        a.DateTime, 
        a.UserID, 
        a.IPAddress, 
        a.ClientID, 
        a.SiteID, 
        a.VariableID, 
        a.ActivityActionID, 
        a.Description, 
        a.LogActionDescription, 
        v.DeviceName, 
        v.VariableName
FROM  dbo_v_ReportVariables AS v 
RIGHT OUTER JOIN
    (SELECT s.SiteName, 
            s.RegionID, s.RegionName, Log_Activity.ActivityID, 
            Log_Activity.DateTime, Log_Activity.UserID, Log_Activity.IPAddress, 
            Log_Activity.ClientID, Log_Activity.SiteID, Log_Activity.VariableID, 
            Log_Activity.ActivityActionID, Log_Activity.Description, 
            Log_ActivityAction.Description AS LogActionDescription
        FROM  dbo_v_Sites AS s 
        RIGHT OUTER JOIN
            Log_Activity ON s.SiteID = Log_Activity.SiteID 
        INNER JOIN Log_ActivityAction ON Log_Activity.ActivityActionID = Log_ActivityAction.ActivityActionID) AS a 
        ON v.VariableID = a.VariableID
    ) AS activities ON u.UserID = activities.UserID

and I created a view with no syntax errors in mysql. But the code view after creation changed in mysql to :

SELECT 
    `u`.`Name` AS `Name`,
    `u`.`Rights` AS `Rights`,
    `u`.`FirstName` AS `FirstName`,
    `u`.`LastName` AS `LastName`,
    `u`.`IsDeleted` AS `IsDeleted`,
    `activities`.`SiteName` AS `SiteName`,
    `activities`.`RegionID` AS `RegionID`,
    `activities`.`RegionName` AS `RegionName`,
    `activities`.`ActivityID` AS `ActivityID`,
    `activities`.`DateTime` AS `DateTime`,
    `activities`.`UserID` AS `UserID`,
    `activities`.`IPAddress` AS `IPAddress`,
    `activities`.`ClientID` AS `ClientID`,
    `activities`.`SiteID` AS `SiteID`,
    `activities`.`VariableID` AS `VariableID`,
    `activities`.`ActivityActionID` AS `ActivityActionID`,
    `activities`.`Description` AS `Description`,
    `activities`.`LogActionDescription` AS `LogActionDescription`,
    `activities`.`DeviceName` AS `DeviceName`,
    `activities`.`VariableName` AS `VariableName`
FROM
    (`eye`.`security_user` `u`
    JOIN (SELECT 
        `a`.`SiteName` AS `SiteName`,
            `a`.`RegionID` AS `RegionID`,
            `a`.`RegionName` AS `RegionName`,
            `a`.`ActivityID` AS `ActivityID`,
            `a`.`DateTime` AS `DateTime`,
            `a`.`UserID` AS `UserID`,
            `a`.`IPAddress` AS `IPAddress`,
            `a`.`ClientID` AS `ClientID`,
            `a`.`SiteID` AS `SiteID`,
            `a`.`VariableID` AS `VariableID`,
            `a`.`ActivityActionID` AS `ActivityActionID`,
            `a`.`Description` AS `Description`,
            `a`.`LogActionDescription` AS `LogActionDescription`,
            `v`.`DeviceName` AS `DeviceName`,
            `v`.`VariableName` AS `VariableName`
    FROM
        (((SELECT 
        `s`.`SiteName` AS `SiteName`,
            `s`.`RegionID` AS `RegionID`,
            `s`.`RegionName` AS `RegionName`,
            `eye`.`log_activity`.`ActivityID` AS `ActivityID`,
            `eye`.`log_activity`.`DateTime` AS `DateTime`,
            `eye`.`log_activity`.`UserID` AS `UserID`,
            `eye`.`log_activity`.`IPAddress` AS `IPAddress`,
            `eye`.`log_activity`.`ClientID` AS `ClientID`,
            `eye`.`log_activity`.`SiteID` AS `SiteID`,
            `eye`.`log_activity`.`VariableID` AS `VariableID`,
            `eye`.`log_activity`.`ActivityActionID` AS `ActivityActionID`,
            `eye`.`log_activity`.`Description` AS `Description`,
            `eye`.`log_activityaction`.`Description` AS `LogActionDescription`
    FROM
        ((`eye`.`log_activity`
    LEFT JOIN `eye`.`dbo_v_sites` `s` ON ((`s`.`SiteID` = `eye`.`log_activity`.`SiteID`)))
    JOIN `eye`.`log_activityaction` ON ((`eye`.`log_activity`.`ActivityActionID` = `eye`.`log_activityaction`.`ActivityActionID`))))) `a`
    LEFT JOIN `eye`.`dbo_v_reportvariables` `v` ON ((`v`.`VariableID` = `a`.`VariableID`)))) `activities` ON ((`u`.`UserID` = `activities`.`UserID`)))

it is not the same as the code i created and i do not know if it has the same result. Can i help me someone with some directions how to migrate views manually?

Improve this question
0

1 Answer 1

Reset to default
1

There will be no reason 99% of the time that you cant just copy over the views word for word, changing table names if you need to. Any syntax error should be obvious enough.

I'd start there because the refactoring that's happened in your third example is not something I'd want to go through. All the quote characters make my eyes water. And the swap from right to left joins will also require careful understanding.

Incidentally I'd also say that in my (longer than I'd care to remember) career in this stuff I have never, ever written a right join as I find it much clearer going the other way, but that might be just me. Even so I'd not like to leave it up to an automated refactor that makes it much harder to read rather tan easier.

So go with your manual version - the sql between the two databases will almost always work. I'm sure you can find resources that detail out the differences (things like data types, conversions, and the like) that might get in the way.

Your view has none of those.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.