in MySql.Web/src/PersonalizationProviderProcedures.cs [41:173]
internal static int myaspnet_PersonalizationAdministration_FindState(bool allUsersScope, long applicationId, string applicationName, int pageIndex, int pageSize, string path, string userName,
DateTime inactiveSinceDate, MySQLPersonalizationConnectionHelper connection, ref MySqlCommand findStateCommand)
{
// create memory table to store results
var sql = "CREATE TEMPORARY TABLE IF NOT EXISTS pageIndexResults(" +
"IndexId int AUTO_INCREMENT NOT NULL PRIMARY KEY, " +
"ItemId int not null)";
if (!connection.Opened)
throw new Exception("Error: Connection should be open");
var cmd = new MySqlCommand(sql, connection.Connection);
cmd.ExecuteNonQuery();
//make sure table is empty
cmd.CommandText = "TRUNCATE TABLE pageIndexResults";
cmd.Connection = connection.Connection;
cmd.ExecuteNonQuery();
int pageLowerBound = pageSize * pageIndex;
int pageUpperBound = pageSize - 1 + pageLowerBound;
if (allUsersScope)
{
var query = "INSERT INTO pageIndexResults (ItemId) (" +
"SELECT myaspnet_Paths.PathId " +
"FROM myaspnet_Paths, " +
"((SELECT aspnet_Paths.PathId " +
"FROM myaspnet_PersonalizationAllUsers, aspnet_Paths " +
"WHERE myaspnet_Paths.ApplicationId = @ApplicationId " +
"AND aspnet_PersonalizationAllUsers.PathId = aspnet_Paths.PathId " +
"AND (@Path IS NULL OR aspnet_Paths.LoweredPath LIKE @Path) " +
") AS SharedDataPerPath " +
"FULL OUTER JOIN " +
"(SELECT DISTINCT aspnet_Paths.PathId " +
"FROM my_aspnet_personalizationperuser, my_aspnet_paths " +
"WHERE my_aspnet_paths.ApplicationId = @ApplicationId " +
"AND my_aspnet_personalizationperuser.PathId = aspnet_Paths.PathId " +
"AND (@Path IS NULL OR my_aspnet_paths.LoweredPath LIKE @Path) " +
") AS UserDataPerPath " +
"ON SharedDataPerPath.PathId = UserDataPerPath.PathId " +
") " +
"WHERE my_aspnet_Paths.PathId = SharedDataPerPath.PathId OR my_aspnet_Paths.PathId = UserDataPerPath.PathId " +
"ORDER BY my_aspnet_Paths.Path ASC)";
cmd.CommandText = query;
cmd.Parameters.AddWithValue("@ApplicationId", applicationId);
cmd.Parameters.AddWithValue("@Path", path);
cmd.Connection = connection.Connection;
cmd.ExecuteNonQuery();
cmd.CommandText = "SELECT Count(PathId) FROM pageIndexResults";
cmd.Connection = connection.Connection;
int totalRecords = (int)cmd.ExecuteScalar();
query = "SELECT my_aspnet_Paths.Path, " +
"SharedDataPerPath.LastUpdatedDate, " +
"SharedDataPerPath.SharedDataLength, " +
"UserDataPerPath.UserDataLength, " +
"UserDataPerPath.UserCount " +
"FROM aspnet_Paths, " +
"((SELECT pageIndexResults.ItemId AS PathId, " +
"aspnet_PersonalizationAllUsers.LastUpdatedDate AS LastUpdatedDate, " +
"LENGTH(aspnet_PersonalizationAllUsers.PageSettings) AS SharedDataLength " +
"FROM my_aspnet_personalizationallusers, PageIndex " +
"WHERE my_aspnet_personalizationallusers.PathId = pageIndexResults.IndexId " +
"AND pageIndexResults.IndexId >= @PageLowerBound AND pageIndexResults.IndexId <= @PageUpperBound " +
") AS SharedDataPerPath " +
"FULL OUTER JOIN " +
"(SELECT pageIndexResults.ItemId AS PathId, " +
"SUM(LENGTH(my_aspnet_personalizationperuser.PageSettings)) AS UserDataLength, " +
"COUNT(*) AS UserCount " +
"FROM my_aspnet_personalizationperuser, pageIndexResults " +
"WHERE my_aspnet_personalizationperuser.PathId = pageIndexResults.IndexId " +
"AND pageIndexResults.IndexId >= @PageLowerBound AND pageIndexResults.IndexId <= @PageUpperBound " +
"GROUP BY pageIndexResults.IndexId " +
") AS UserDataPerPath " +
"ON SharedDataPerPath.PathId = UserDataPerPath.PathId " +
") " +
"WHERE aspnet_Paths.PathId = SharedDataPerPath.PathId OR aspnet_Paths.PathId = UserDataPerPath.PathId " +
"ORDER BY my_aspnet_Paths.Path ASC ";
findStateCommand.CommandText = query;
findStateCommand.Connection = connection.Connection;
findStateCommand.Parameters.AddWithValue("@PageLowerBound", pageLowerBound);
findStateCommand.Parameters.AddWithValue("@PageUpperBound", pageUpperBound);
return totalRecords;
}
else
{
var query = "INSERT INTO pageIndexResults (ItemId) (" +
"SELECT my_aspnet_personalizationperuser.Id " +
"FROM my_aspnet_personalizationperuser, my_aspnet_users, my_aspnet_paths " +
"WHERE my_aspnet_paths.ApplicationId = @ApplicationId " +
"AND my_aspnet_personalizationperuser.UserId = my_aspnet_Users.Id " +
"AND my_aspnet_personalizationperuser.PathId = my_aspnet_Paths.PathId " +
"AND (@Path IS NULL OR my_aspnet_paths.LoweredPath LIKE @Path) " +
"AND (@UserName IS NULL OR my_aspnet_users.name LIKE @UserName) " +
"AND (@InactiveSinceDate IS NULL OR my_aspnet_users.LastActivityDate <= @InactiveSinceDate) " +
"ORDER BY my_aspnet_paths.Path ASC, my_aspnet_users.name ASC )";
cmd.CommandText = query;
cmd.Parameters.AddWithValue("@ApplicationId", applicationId);
cmd.Parameters.AddWithValue("@Path", path);
cmd.Parameters.AddWithValue("@UserName", userName);
cmd.Parameters.AddWithValue("@InactiveSinceDate", inactiveSinceDate);
cmd.Connection = connection.Connection;
cmd.ExecuteNonQuery();
cmd.CommandText = "SELECT Count(IndexId) FROM pageIndexResults";
cmd.Connection = connection.Connection;
var totalRecords = cmd.ExecuteScalar().ToString();
query = "SELECT my_aspnet_Paths.Path, my_aspnet_personalizationperuser.LastUpdatedDate, LENGTH(my_aspnet_personalizationperuser.PageSettings) as Size, my_aspnet_Users.Name, my_aspnet_Users.LastActivityDate " +
"FROM my_aspnet_personalizationperuser, my_aspnet_users, my_aspnet_paths, pageIndexResults " +
"WHERE my_aspnet_personalizationperuser.Id = PageIndexResults.IndexId " +
"AND my_aspnet_personalizationperuser.UserId = my_aspnet_users.Id " +
"AND my_aspnet_personalizationperuser.PathId = my_aspnet_paths.PathId " +
"AND pageIndexResults.ItemId >= @PageLowerBound AND PageIndexResults.ItemId <= @PageUpperBound " +
"ORDER BY my_aspnet_paths.Path ASC, my_aspnet_users.name ASC ";
findStateCommand.CommandText = query;
findStateCommand.Parameters.AddWithValue("@PageUpperBound", pageUpperBound);
findStateCommand.Parameters.AddWithValue("@PageLowerBound", pageLowerBound);
findStateCommand.Connection = connection.Connection;
return int.Parse(totalRecords, CultureInfo.InvariantCulture);
}
}