1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
|
/* Provider schema block -- version 3 */
/* create our application and user tables */
create table my_aspnet_Applications(id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(256), description VARCHAR(256));
create table my_aspnet_Users(id INT PRIMARY KEY AUTO_INCREMENT,
applicationId INT NOT NULL, name VARCHAR(256) NOT NULL,
isAnonymous TINYINT(1) NOT NULL DEFAULT 1, lastActivityDate DATETIME);
create table my_aspnet_Profiles(userId INT PRIMARY KEY, valueindex longtext, stringdata longtext, binarydata longblob, lastUpdatedDate timestamp);
create table my_aspnet_SchemaVersion(version INT);
insert into my_aspnet_SchemaVersion VALUES (3);
/* now we need to migrate all applications into our apps table */
insert into my_aspnet_Applications (name) select ApplicationName from mysql_Membership UNION select ApplicationName from mysql_UsersInRoles;
/* now we make our changes to the existing tables */
alter table mysql_Membership
rename to my_aspnet_Membership,
drop primary key,
drop column pkid,
drop column IsOnline,
add column userId INT FIRST,
add column applicationId INT AFTER userId;
alter table mysql_Roles
rename to my_aspnet_Roles,
drop key Rolename,
add column id INT PRIMARY KEY AUTO_INCREMENT FIRST,
add column applicationId INT NOT NULL AFTER id;
alter table mysql_UsersInRoles
drop key Username,
rename to my_aspnet_UsersInRoles,
add column userId INT FIRST,
add column roleId INT AFTER userId,
add column applicationId INT AFTER roleId;
ALTER TABLE my_aspnet_Membership CONVERT TO CHARACTER SET DEFAULT;
ALTER TABLE my_aspnet_Roles CONVERT TO CHARACTER SET DEFAULT;
ALTER TABLE my_aspnet_UsersInRoles CONVERT TO CHARACTER SET DEFAULT;
/* these next lines set the application Id on our tables appropriately */
update my_aspnet_Membership m, my_aspnet_Applications a set m.applicationId = a.id where a.name=m.ApplicationName;
update my_aspnet_Roles r, my_aspnet_Applications a set r.applicationId = a.id where a.name=r.ApplicationName;
update my_aspnet_UsersInRoles u, my_aspnet_Applications a set u.applicationId = a.id where a.name=u.ApplicationName;
/* now merge our usernames into our users table */
insert into my_aspnet_Users (applicationId, name)
select applicationId, Username from my_aspnet_Membership
UNION select applicationId, Username from my_aspnet_UsersInRoles;
/* now set the user ids in our tables accordingly */
update my_aspnet_Membership m, my_aspnet_Users u set m.userId = u.id where u.name=m.Username AND u.applicationId=m.applicationId;
update my_aspnet_UsersInRoles r, my_aspnet_Users u set r.userId = u.id where u.name=r.Username AND u.applicationId=r.applicationId;
/* now update the isanonymous and last activity date fields for the users */
update my_aspnet_Users u, my_aspnet_Membership m
set u.isAnonymous=0, u.lastActivityDate=m.LastActivityDate
where u.name = m.Username;
/* make final changes to our tables */
alter table my_aspnet_Membership
drop column Username,
drop column ApplicationName,
drop column applicationId,
add primary key (userId);
/* next we set our role id values appropriately */
update my_aspnet_UsersInRoles u, my_aspnet_Roles r set u.roleId = r.id where u.Rolename = r.Rolename and r.applicationId=u.applicationId;
/* now we make the final changes to our roles tables */
alter table my_aspnet_Roles
drop column ApplicationName,
change column Rolename name VARCHAR(255) NOT NULL;
alter table my_aspnet_UsersInRoles
drop column ApplicationName,
drop column applicationId,
drop column Username,
drop column Rolename,
add primary key (userId, roleId);
|
