File: schema3.sql

package info (click to toggle)
mysql-connector-net 6.4.3-4
  • links: PTS, VCS
  • area: main
  • in suites: stretch
  • size: 6,160 kB
  • ctags: 8,552
  • sloc: cs: 63,689; xml: 7,505; sql: 345; makefile: 50; ansic: 40
file content (85 lines) | stat: -rw-r--r-- 4,065 bytes parent folder | download | duplicates (2)
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);