public ulong Nav_GetNearestParent(Title title)
{
string query = @"/* Nav_GetNearestParent */
SELECT page_id FROM pages WHERE page_namespace={0} AND STRCMP(SUBSTRING('{1}', 1, CHAR_LENGTH(page_title) + 1), CONCAT(page_title, '/'))=0 ORDER BY CHAR_LENGTH(page_title) DESC LIMIT 1";
return(Catalog.NewQuery(string.Format(query, (int)title.Namespace, DataCommand.MakeSqlSafe(title.AsUnprefixedDbPath()))).ReadAsULong() ?? Head.Pages_HomePageId);
}
public IList <BanBE> Bans_GetByRequest(uint userid, IList <string> ips)
{
StringBuilder sb = new StringBuilder();
foreach (string ip in ips)
{
if (sb.Length > 0)
{
sb.Append(", ");
}
sb.AppendFormat("'{0}'", DataCommand.MakeSqlSafe(ip));
}
if (sb.Length == 0)
{
sb.Append("NULL");
}
//Note: expiration not applied in query to allow for query caching
string where = string.Format(@"
where b.ban_id in (
select b.ban_id
from bans b
left join banips bi on b.ban_id = bi.banip_ban_id
where bi.banip_ipaddress in ({0})
union
select b.ban_id
from bans b
left join banusers bu on b.ban_id = bu.banuser_ban_id
where bu.banuser_user_id = {1})", sb.ToString(), userid);
return(Bans_GetBansInternal(where));
}
public Dictionary <Title, ResourceBE> Resources_GetFileResourcesByTitlesWithMangling(IList <Title> fileTitles)
{
//A specialized query that retrieves file resources from titles. This is only used from DekiXmlParser.ConvertFileLinks
Dictionary <Title, ResourceBE> ret = new Dictionary <Title, ResourceBE>();
if (ArrayUtil.IsNullOrEmpty(fileTitles))
{
return(ret);
}
string query = @" /* Resources_GetFileResourcesByTitlesWithMangling */
select resources.*, resourcecontents.*, pages.page_id, pages.page_namespace, pages.page_title, pages.page_display_name
from pages
join resources
on resrev_parent_page_id = page_id
left join resourcecontents
on resources.resrev_content_id = resourcecontents.rescontent_id
where
res_type = 2
AND res_deleted = 0
AND(
{0}
)
";
StringBuilder whereQuery = new StringBuilder();
for (int i = 0; i < fileTitles.Count; i++)
{
if (i > 0)
{
whereQuery.Append("\n OR ");
}
whereQuery.AppendFormat("(pages.page_namespace={0} AND pages.page_title='{1}' AND REPLACE(resources.resrev_name, ' ', '_') = REPLACE('{2}', ' ', '_'))",
(uint)fileTitles[i].Namespace, DataCommand.MakeSqlSafe(fileTitles[i].AsUnprefixedDbPath()), DataCommand.MakeSqlSafe(fileTitles[i].Filename));
}
query = string.Format(query, whereQuery);
Catalog.NewQuery(query).Execute(delegate(IDataReader dr) {
while (dr.Read())
{
Title title = DbUtils.TitleFromDataReader(dr, "page_namespace", "page_title", "page_display_name", "resrev_name");
ResourceBE r = Resources_Populate(dr) as ResourceBE;
if (r != null)
{
ret[title] = r;
}
}
});
return(ret);
}
public IList <GroupBE> Groups_GetByQuery(string groupNameFilter, uint?serviceIdFilter, SortDirection sortDir, GroupsSortField sortField, uint?offset, uint?limit, out uint totalCount, out uint queryCount)
{
List <GroupBE> result = new List <GroupBE>();
StringBuilder query = new StringBuilder();
if (groupNameFilter != null)
{
groupNameFilter = "%" + DataCommand.MakeSqlSafe(groupNameFilter) + "%";
}
string sortFieldString = null;
GROUPS_SORT_FIELD_MAPPING.TryGetValue(sortField, out sortFieldString);
if ((sortFieldString ?? string.Empty).StartsWith("roles."))
{
query.Append(@"
left join roles
on groups.group_role_id = roles.role_id");
}
if ((sortFieldString ?? string.Empty).StartsWith("services."))
{
query.AppendFormat(@"
left join services
on groups.group_service_id = services.service_id");
}
if (!string.IsNullOrEmpty(groupNameFilter) || serviceIdFilter != null)
{
query.Append(" where (1=1)");
if (serviceIdFilter != null)
{
query.AppendFormat(" AND group_service_id = {0}", serviceIdFilter.Value);
}
if (!string.IsNullOrEmpty(groupNameFilter))
{
query.AppendFormat(" AND group_name like '{0}'", groupNameFilter);
}
}
if (!string.IsNullOrEmpty(sortFieldString))
{
query.AppendFormat(" order by {0} ", sortFieldString);
if (sortDir != SortDirection.UNDEFINED)
{
query.Append(sortDir.ToString());
}
}
return(Groups_GetInternal(query.ToString(), "Groups_GetByQuery", true, limit, offset, out totalCount, out queryCount));
}
public void Links_UpdateLinksForPage(PageBE page, IList <ulong> outboundLinks, IList <string> brokenLinks)
{
StringBuilder query = new StringBuilder(@" /* Links_UpdateLinksForPage */
DELETE FROM links
WHERE l_from = ?PAGEID;
DELETE FROM brokenlinks
WHERE bl_from = ?PAGEID;
");
// build page's outbound links query
if (!ArrayUtil.IsNullOrEmpty(outboundLinks))
{
query.Append(@"
INSERT IGNORE INTO links (l_from, l_to) VALUES ");
for (int i = 0; i < outboundLinks.Count; i++)
{
query.AppendFormat("{0}(?PAGEID, {1})", i > 0 ? "," : "", outboundLinks[i]);
}
query.Append(";");
}
// build page's broken links query
if (!ArrayUtil.IsNullOrEmpty(brokenLinks))
{
query.Append(@"
INSERT IGNORE INTO brokenlinks (bl_from, bl_to) VALUES ");
for (int i = 0; i < brokenLinks.Count; i++)
{
query.AppendFormat("{0}(?PAGEID, '{1}')", i > 0 ? "," : "", DataCommand.MakeSqlSafe(brokenLinks[i]));
}
query.Append(";");
}
// build query to update any broken links that point to this page
query.Append(@"
INSERT IGNORE INTO links (l_from, l_to)
SELECT brokenlinks.bl_from, ?PAGEID
FROM brokenlinks
WHERE bl_to = ?TITLE;
DELETE FROM brokenlinks
WHERE bl_to = ?TITLE;"
);
Catalog.NewQuery(query.ToString())
.With("PAGEID", page.ID)
.With("TITLE", page.Title.AsPrefixedDbPath())
.Execute();
}
public IList <GroupBE> Groups_GetByNames(IList <string> groupNames)
{
if (ArrayUtil.IsNullOrEmpty(groupNames))
{
return(new List <GroupBE>());
}
var groupNamesStr = new StringBuilder();
for (int i = 0; i < groupNames.Count; i++)
{
if (i > 0)
{
groupNamesStr.Append(",");
}
groupNamesStr.AppendFormat("'{0}'", DataCommand.MakeSqlSafe(groupNames[i]));
}
return(Groups_GetInternal(string.Format("where groups.group_name in ({0})", groupNamesStr), "Groups_GetByNames"));
}
public void Config_WriteInstanceSettings(IList <KeyValuePair <string, string> > keyValues)
{
StringBuilder query = new StringBuilder();
query.Append(@" /* Config_WriteInstanceSettings */
DELETE FROM `config`;
INSERT INTO `config` (`config_key`, `config_value`) VALUES ");
bool first = true;
foreach (KeyValuePair <string, string> setting in keyValues)
{
string key = DataCommand.MakeSqlSafe(setting.Key.TrimEnd(new char[] { '/' }));
string val = DataCommand.MakeSqlSafe(setting.Value);
query.AppendFormat("{0}('{1}', '{2}' )", first ? string.Empty : ",", key, val);
first = false;
}
query.Append(";");
Catalog.NewQuery(query.ToString()).Execute();
}
public static void InsertDWIPBlock(IPBlockBE ipBlock)
{
uint banID = 0;
MediaWikiConverterContext.Current.DWCatalog.NewQuery(String.Format("INSERT into bans (ban_by_user_id, ban_reason, ban_revokemask, ban_last_edit) VALUES ('{0}', '{1}', '{2}', '{3}'); SELECT LAST_INSERT_ID() as banid;",
ipBlock.ByUserID, DataCommand.MakeSqlSafe(ipBlock.Reason), 9223372036854779902, ipBlock.Timestamp)).Execute(delegate(IDataReader dr) {
while (dr.Read())
{
banID = DbUtils.Convert.To <uint>(dr["banid"], 0);
}
});
if (0 == ipBlock.UserID)
{
MediaWikiConverterContext.Current.DWCatalog.NewQuery(String.Format("INSERT into banips (banip_ipaddress, banip_ban_id) VALUES ('{0}', '{1}')",
DataCommand.MakeSqlSafe(ipBlock.Address), banID)).Execute();
}
else
{
MediaWikiConverterContext.Current.DWCatalog.NewQuery(String.Format("INSERT into banusers (banuser_user_id, banuser_ban_id) VALUES ('{0}', '{1}')",
ipBlock.UserID, banID)).Execute();
}
}
//--- Methods ---
public ulong SearchAnalytics_LogQuery(SearchQuery query, string parsedQuery, uint userId, uint resultCount, ulong? previousQueryId) {
var sorted = query.GetOrderedNormalizedTermString();
var hash = query.GetOrderedTermsHash();
var queryId = Catalog.NewQuery(@"/* SearchAnalytics_LogQuery */
INSERT INTO query_log (raw, sorted_terms, sorted_terms_hash, parsed, created, user_id, ref_query_id, result_count)
VALUES (?QUERY, ?SORTED, ?HASH, ?PARSED, ?CREATED, ?USERID, ?REFID, ?RESULTCOUNT);
SELECT LAST_INSERT_ID();")
.With("QUERY", query.Raw)
.With("SORTED", sorted)
.With("HASH", hash)
.With("PARSED", parsedQuery)
.With("USERID", userId)
.With("CREATED", DateTime.UtcNow)
.With("REFID", previousQueryId)
.With("RESULTCOUNT", resultCount)
.ReadAsULong().Value;
var terms = query.GetNormalizedTerms();
if(terms.Any()) {
var quotedTerms = terms.Select(x => "'" + DataCommand.MakeSqlSafe(x) + "'").ToArray();
Catalog.NewQuery(string.Format(@"/* SearchAnalytics_LogQuery */
INSERT IGNORE INTO query_terms (query_term) values
{0};", string.Join(",", quotedTerms.Select(x => "(" + x + ")").ToArray()))).Execute();
var termIds = new List<uint>();
Catalog.NewQuery(string.Format(@"/* SearchAnalytics_LogQuery */
SELECT query_term_id from query_terms where query_term IN({0})",
string.Join(",", quotedTerms))).Execute(r => {
while(r.Read()) {
termIds.Add(r.Read<uint>(0));
}
});
Catalog.NewQuery(string.Format(@"/* SearchAnalytics_LogQuery */
INSERT IGNORE INTO query_term_map (query_term_id,query_id) values
{0};", string.Join(",", termIds.Select(x => "(" + x + "," + queryId + ")").ToArray()))).Execute();
}
return queryId;
}
public IEnumerable <UserBE> Users_GetByQuery(string usernamefilter, string realnamefilter, string usernameemailfilter, string rolefilter, bool?activatedfilter, uint?groupId, uint?serviceIdFilter, bool?seatFilter, SortDirection sortDir, UsersSortField sortField, uint?offset, uint?limit, out uint totalCount, out uint queryCount)
{
List <UserBE> users = new List <UserBE>();
uint totalCountTemp = 0;
uint queryCountTemp = 0;
StringBuilder joinQuery = new StringBuilder();
string sortFieldString;
USERS_SORT_FIELD_MAPPING.TryGetValue(sortField, out sortFieldString);
if (!string.IsNullOrEmpty(rolefilter) || (sortFieldString ?? string.Empty).StartsWith("roles."))
{
joinQuery.Append(@"
left join roles
on users.user_role_id = roles.role_id");
}
if ((sortFieldString ?? string.Empty).StartsWith("services."))
{
joinQuery.AppendFormat(@"
left join services
on users.user_service_id = services.service_id");
}
if (groupId != null)
{
joinQuery.AppendFormat(@"
join groups
on groups.group_id = {0}
join user_groups
on user_groups.group_id = groups.group_id", groupId.Value);
}
StringBuilder whereQuery = new StringBuilder(" where 1=1");
if (groupId != null)
{
whereQuery.AppendFormat(" AND users.user_id = user_groups.user_id");
}
if (!string.IsNullOrEmpty(usernamefilter) && !string.IsNullOrEmpty(realnamefilter))
{
whereQuery.AppendFormat(" AND (user_name like '{0}%' OR user_real_name like '{1}%')", DataCommand.MakeSqlSafe(usernamefilter), DataCommand.MakeSqlSafe(realnamefilter));
}
else if (!string.IsNullOrEmpty(usernamefilter))
{
whereQuery.AppendFormat(" AND user_name like '{0}%'", DataCommand.MakeSqlSafe(usernamefilter));
}
else if (!string.IsNullOrEmpty(realnamefilter))
{
whereQuery.AppendFormat(" AND user_real_name like '{0}%'", DataCommand.MakeSqlSafe(realnamefilter));
}
if (!string.IsNullOrEmpty(usernameemailfilter))
{
whereQuery.AppendFormat(" AND (user_name like '{0}%' OR user_email like '{0}%')", DataCommand.MakeSqlSafe(usernameemailfilter));
}
if (activatedfilter != null)
{
whereQuery.AppendFormat(" AND user_active = {0}", activatedfilter.Value ? "1" : "0");
}
if (!string.IsNullOrEmpty(rolefilter))
{
whereQuery.AppendFormat(" AND role_name = '{0}'", DataCommand.MakeSqlSafe(rolefilter));
}
if (serviceIdFilter != null)
{
whereQuery.AppendFormat(" AND user_service_id = {0}", serviceIdFilter.Value);
}
if (seatFilter != null)
{
whereQuery.AppendFormat(" AND user_seat = {0}", seatFilter.Value ? 1 : 0);
}
StringBuilder sortLimitQuery = new StringBuilder();
if (!string.IsNullOrEmpty(sortFieldString))
{
sortLimitQuery.AppendFormat(" order by {0} ", sortFieldString);
if (sortDir != SortDirection.UNDEFINED)
{
sortLimitQuery.Append(sortDir.ToString());
}
}
if (limit != null || offset != null)
{
sortLimitQuery.AppendFormat(" limit {0} offset {1}", limit ?? int.MaxValue, offset ?? 0);
}
string query = string.Format(@" /* Users_GetByQuery */
select *
from users
{0}
{1}
{2};
select count(*) as totalcount from users {0} {3};
select count(*) as querycount from users {0} {1};",
joinQuery,
whereQuery,
sortLimitQuery,
groupId == null ? string.Empty : "where users.user_id = user_groups.user_id");
Catalog.NewQuery(query)
.Execute(delegate(IDataReader dr) {
while (dr.Read())
{
UserBE u = Users_Populate(dr);
users.Add(u);
}
if (dr.NextResult() && dr.Read())
{
totalCountTemp = DbUtils.Convert.To <uint>(dr["totalcount"], 0);
}
if (dr.NextResult() && dr.Read())
{
queryCountTemp = DbUtils.Convert.To <uint>(dr["querycount"], 0);
}
});
totalCount = totalCountTemp;
queryCount = queryCountTemp;
return(users);
}
public override User GetUser(string user)
{
DataCommand cmd = _catalog.NewQuery(string.Format("SELECT {1}usergroup.title FROM {1}user LEFT JOIN {1}usergroup ON {1}user.usergroupid={1}usergroup.usergroupid WHERE {1}user.username='******' AND {1}usergroup.forumpermissions!=0", DataCommand.MakeSqlSafe(user.ToLowerInvariant()), _tablePrefix));
string title = cmd.Read();
if (title == null)
{
return(null);
}
return(new User(user, string.Empty, new Group[] { new Group(title) }));
}
public override bool CheckUserPassword(string user, string password)
{
DataCommand cmd = _catalog.NewQuery(string.Format("SELECT count(*) FROM {2}user LEFT JOIN {2}usergroup ON {2}user.usergroupid={2}usergroup.usergroupid WHERE {2}user.username='******' AND {2}user.password=MD5(CONCAT(MD5('{1}'), salt)) AND {2}usergroup.forumpermissions!=0", DataCommand.MakeSqlSafe(user.ToLowerInvariant()), DataCommand.MakeSqlSafe(password), _tablePrefix));
long count = cmd.ReadAsLong() ?? 0;
return(count > 0);
}
public override Group GetGroup(string group)
{
string title = _catalog.NewQuery(string.Format("SELECT title FROM {1}usergroup WHERE {1}usergroup.title='{0}' AND {1}usergroup.forumpermissions!=0", DataCommand.MakeSqlSafe(group), _tablePrefix)).Read();
if (title != null)
{
return(new Group(title));
}
return(null);
}
public IList <PageBE> Pages_GetPopular(string language, uint?offset, uint?limit)
{
string limitAndOffset = (limit == null && offset == null) ? string.Empty : string.Format("limit {0} offset {1}", limit ?? int.MaxValue, offset ?? 0);
string query = "INNER JOIN page_viewcount as pv ON p.page_id = pv.page_id WHERE p.page_namespace = 0 AND p.page_is_redirect = 0";
//Add language filter to query
if (!string.IsNullOrEmpty(language))
{
query += string.Format(" AND (p.page_language = '' OR p.page_language = '{0}') ", DataCommand.MakeSqlSafe(language));
}
return(Pages_GetInternal(query, "ORDER BY pv.page_counter DESC", limitAndOffset, "Pages_GetPopular"));
}
//--- Methods ---
public override bool CheckUserPassword(string user, string password)
{
DataCommand cmd = _catalog.NewQuery(string.Format("SELECT count(*) FROM {2}users WHERE name='{0}' AND pass=md5('{1}') LIMIT 1", DataCommand.MakeSqlSafe(user), DataCommand.MakeSqlSafe(password), _prefix));
long count = cmd.ReadAsLong() ?? 0;
if (count > 0)
{
return(true);
}
return(false);
}
public Dictionary <Title, ulong> Pages_GetIdsByTitles(IList <Title> pageTitle)
{
Dictionary <Title, ulong> ids = new Dictionary <Title, ulong>();
if (0 < pageTitle.Count)
{
// generate query to retrieve all pages matching the criteria
// TODO (brigette): this might break for very long queries. We need to test on a page with many links
StringBuilder query = new StringBuilder("SELECT page_id, page_namespace, page_title, page_display_name FROM pages WHERE ");
for (int i = 0; i < pageTitle.Count; i++)
{
if (0 < i)
{
query.Append(" OR ");
}
query.AppendFormat(" (page_namespace={0} AND page_title='{1}')", (uint)pageTitle[i].Namespace, DataCommand.MakeSqlSafe(pageTitle[i].AsUnprefixedDbPath()));
}
query.Append(" GROUP BY page_namespace, page_title");
// execute the query and read the results into a lookup table
Catalog.NewQuery(query.ToString()).Execute(delegate(IDataReader dr) {
while (dr.Read())
{
Title info = DbUtils.TitleFromDataReader(dr, "page_namespace", "page_title", "page_display_name");
ids[info] = dr.Read <ulong>("page_id");
}
});
}
return(ids);
}
public override bool CheckUserPassword(string user, string password)
{
DataCommand cmd = _catalog.NewQuery(string.Format("SELECT COUNT(*) FROM openIDToUser LEFT JOIN users ON openIDToUser.userID = users.userID WHERE ((users.internal = 'Y' AND openIDToUser.openID = CONCAT('{0}', '{2}') AND users.password = MD5(CONCAT('{1}', 'WL758ek0', salt))) OR (users.internal = 'N' AND openIDToUser.openID = '{0}' AND users.password = MD5('{1}'))) AND users.active = 'Y' LIMIT 1", DataCommand.MakeSqlSafe(user.ToLowerInvariant()), DataCommand.MakeSqlSafe(password), DataCommand.MakeSqlSafe(_domain)));
long count = cmd.ReadAsLong() ?? 0;
if (count > 0)
{
return(true);
}
return(false);
}
public override bool CheckUserPassword(string user, string password)
{
DataCommand cmd = _catalog.NewQuery(string.Format("SELECT user_pass FROM {1}users WHERE user_login='******' AND user_status=0 LIMIT 1", DataCommand.MakeSqlSafe(user), _prefix));
XDoc result = cmd.ReadAsXDoc("users", "user");
if (result["user"].IsEmpty)
{
return(false);
}
string hashed = result["user/user_pass"].AsText;
return(CheckPassword(password, hashed));
}
public IList <ServiceBE> Services_GetByQuery(string serviceType, SortDirection sortDir, ServicesSortField sortField, uint?offset, uint?limit, out uint totalCount, out uint queryCount)
{
IList <ServiceBE> services = null;
uint totalCountTemp = 0;
uint queryCountTemp = 0;
StringBuilder whereQuery = new StringBuilder(" where 1=1");
if (!string.IsNullOrEmpty(serviceType))
{
whereQuery.AppendFormat(" AND service_type = '{0}'", DataCommand.MakeSqlSafe(serviceType));
}
StringBuilder sortLimitQuery = new StringBuilder();
string sortFieldString = null;
//Sort by id if no sort specified.
if (sortField == ServicesSortField.UNDEFINED)
{
sortField = ServicesSortField.ID;
}
if (SERVICES_SORT_FIELD_MAPPING.TryGetValue(sortField, out sortFieldString))
{
sortLimitQuery.AppendFormat(" order by {0} ", sortFieldString);
if (sortDir != SortDirection.UNDEFINED)
{
sortLimitQuery.Append(sortDir.ToString());
}
}
if (limit != null || offset != null)
{
sortLimitQuery.AppendFormat(" limit {0} offset {1}", limit ?? int.MaxValue, offset ?? 0);
}
string query = string.Format(@" /* Services_GetByQuery */
select *
from services
{0}
{1};
select service_config.*
from service_config
join (
select service_id
from services
{0}
{1}
) s on service_config.service_id = s.service_id;
select service_prefs.*
from service_prefs
join (
select service_id
from services
{0}
{1}
) s on service_prefs.service_id = s.service_id;
select count(*) as totalcount from services;
select count(*) as querycount from services {0};
", whereQuery, sortLimitQuery);
Catalog.NewQuery(query)
.Execute(delegate(IDataReader dr) {
services = Services_Populate(dr);
if (dr.NextResult() && dr.Read())
{
totalCountTemp = DbUtils.Convert.To <uint>(dr["totalcount"], 0);
}
if (dr.NextResult() && dr.Read())
{
queryCountTemp = DbUtils.Convert.To <uint>(dr["querycount"], 0);
}
});
totalCount = totalCountTemp;
queryCount = queryCountTemp;
return(services == null ? new List <ServiceBE>() : services);
}
public uint Services_Insert(ServiceBE service)
{
StringBuilder query = null;
if (service.Id == 0)
{
//new service
query = new StringBuilder(@" /* Services_Insert */
insert into services (service_type, service_sid, service_uri, service_description, service_local, service_enabled, service_last_edit, service_last_status)
values (?TYPE, ?SID, ?URI, ?DESC, ?LOCAL, ?ENABLED, ?TIMESTAMP, ?LASTSTATUS);
");
query.AppendLine("select LAST_INSERT_ID() into @service_id;");
query.AppendLine("select LAST_INSERT_ID() as service_id;");
}
else
{
//update existing service
query = new StringBuilder(@" /* Services_Insert (with id) */
insert into services (service_id, service_type, service_sid, service_uri, service_description, service_local, service_enabled, service_last_edit, service_last_status)
values (?ID, ?TYPE, ?SID, ?URI, ?DESC, ?LOCAL, ?ENABLED, ?TIMESTAMP, ?LASTSTATUS);
");
query.AppendLine(string.Format("select {0} into @service_id;", service.Id));
query.AppendLine(string.Format("select {0} as service_id;", service.Id));
}
if (service.Preferences != null && service.Preferences.Count > 0)
{
query.Append("insert into service_prefs (service_id, pref_name, pref_value) values ");
for (int i = 0; i < service.Preferences.AllKeys.Length; i++)
{
string key = DataCommand.MakeSqlSafe(service.Preferences.AllKeys[i]);
string val = DataCommand.MakeSqlSafe(service.Preferences[key]);
query.AppendFormat("{0}(@service_id, '{1}', '{2}')\n", i > 0 ? "," : string.Empty, key, val);
}
query.AppendLine(";");
}
if (service.Config != null && service.Config.Count > 0)
{
query.Append("insert into service_config (service_id, config_name, config_value) values ");
for (int i = 0; i < service.Config.AllKeys.Length; i++)
{
string key = DataCommand.MakeSqlSafe(service.Config.AllKeys[i]);
string val = DataCommand.MakeSqlSafe(service.Config[key]);
query.AppendFormat("{0}(@service_id, '{1}', '{2}')\n", i > 0 ? "," : string.Empty, key, val);
}
query.AppendLine(";");
}
uint serviceId = 0;
try {
serviceId = Catalog.NewQuery(query.ToString())
.With("ID", service.Id)
.With("TYPE", service.Type.ToString())
.With("SID", service.SID)
.With("URI", service.Uri)
.With("DESC", service.Description)
.With("LOCAL", service.ServiceLocal)
.With("ENABLED", service.ServiceEnabled)
.With("TIMESTAMP", service.ServiceLastEdit)
.With("LASTSTATUS", service.ServiceLastStatus)
.ReadAsUInt() ?? 0;
} catch (MySqlException e) {
// catch Duplicate Key (1062)
if (e.Number == 1062)
{
serviceId = service.Id;
}
else
{
throw;
}
}
return(serviceId);
}
public override User GetUser(string user)
{
string name = "", email = "";
DataCommand cmd = _catalog.NewQuery(string.Format("SELECT user_login, user_email FROM {1}users WHERE user_login='******' AND user_status=0 LIMIT 1", DataCommand.MakeSqlSafe(user), _prefix));
XDoc result = cmd.ReadAsXDoc("users", "user");
if (result["user"].IsEmpty)
{
return(null);
}
name = result["user/user_login"].AsText;
email = result["user/user_email"].AsText;
// TODO, group should be determined by deserializing the PHP value in wp_usermeta meta_key='wp_capabilities'
return(new User(name, email, new Group[] { _defaultGroup }));
}
private static string BuildNameFilterWhereClause(IList <string> names, string tableAlias)
{
/*
* AND (
* name in (1,2,3)
* OR name like %4%
* OR name like %5%
* )
*/
StringBuilder nameSpecificQuery = new StringBuilder();
StringBuilder nameSubstringQuery = new StringBuilder();
if (!ArrayUtil.IsNullOrEmpty(names))
{
for (int i = 0; i < names.Count; i++)
{
if (!names[i].Contains('*'))
{
if (nameSpecificQuery.Length == 0)
{
nameSpecificQuery.AppendFormat("{0}.resrev_name in (", tableAlias);
}
else
{
nameSpecificQuery.Append(i > 0 ? "," : string.Empty);
}
nameSpecificQuery.AppendFormat("'{0}'", DataCommand.MakeSqlSafe(names[i]));
}
else
{
//Change the name into a mysql substring expression
string nameExpression = DataCommand.MakeSqlSafe(names[i]);
if (nameExpression.StartsWith("*", StringComparison.InvariantCultureIgnoreCase))
{
nameExpression = nameExpression.TrimStart('*').Insert(0, "%");
}
if (nameExpression.EndsWith("*", StringComparison.InvariantCultureIgnoreCase))
{
nameExpression = nameExpression.TrimEnd('*') + '%';
}
if (nameSubstringQuery.Length > 0)
{
nameSubstringQuery.Append(" OR ");
}
nameSubstringQuery.AppendFormat(" {0}.resrev_name like \"{1}\"", tableAlias, nameExpression);
}
}
if (nameSpecificQuery.Length > 0)
{
nameSpecificQuery.Append(")");
}
}
string ret = string.Empty;
if (nameSpecificQuery.Length > 0 || nameSubstringQuery.Length > 0)
{
ret = string.Format(" AND ({0} {1} {2})", nameSpecificQuery, (nameSpecificQuery.Length > 0 && nameSubstringQuery.Length > 0) ? "OR" : string.Empty, nameSubstringQuery);
}
return(ret);
}
public IList <TagBE> Tags_GetByQuery(string partialName, TagType type, DateTime from, DateTime to)
{
// retrieve the tags associated with a specified page id
List <TagBE> tags = new List <TagBE>();
bool hasWhere = false;
StringBuilder query = new StringBuilder();
query.Append(@" /* Tags_GetByQuery */
SELECT `tag_id`, `tag_name`, `tag_type`
FROM tags ");
if (!string.IsNullOrEmpty(partialName))
{
query.AppendFormat("WHERE tag_name LIKE '{0}%' ", DataCommand.MakeSqlSafe(partialName));
hasWhere = true;
}
if (type != TagType.ALL)
{
if (hasWhere)
{
query.Append("AND ");
}
else
{
query.Append("WHERE ");
}
query.AppendFormat(" tag_type={0} ", (int)type);
hasWhere = true;
}
if ((type == TagType.DATE) && (from != DateTime.MinValue))
{
if (hasWhere)
{
query.Append("AND ");
}
else
{
query.Append("WHERE ");
}
query.AppendFormat("tag_name >= '{0}' ", from.ToString("yyyy-MM-dd"));
hasWhere = true;
}
if ((type == TagType.DATE) && (to != DateTime.MaxValue))
{
if (hasWhere)
{
query.Append("AND ");
}
else
{
query.Append("WHERE ");
}
query.AppendFormat("tag_name <= '{0}' ", to.ToString("yyyy-MM-dd"));
}
Catalog.NewQuery(query.ToString())
.Execute(delegate(IDataReader dr) {
while (dr.Read())
{
TagBE t = Tags_Populate(dr);
tags.Add(t);
}
});
return(tags);
}
public uint Bans_Insert(BanBE ban)
{
//build banusers insert query
StringBuilder userIdInsertQuery = new StringBuilder();
if (ban.BanUserIds != null && ban.BanUserIds.Count > 0)
{
userIdInsertQuery.Append("insert into banusers (banuser_user_id, banuser_ban_id) values ");
for (int i = 0; i < ban.BanUserIds.Count; i++)
{
userIdInsertQuery.AppendFormat("{0}({1}, @banid)", i > 0 ? "," : string.Empty, ban.BanUserIds[i]);
}
userIdInsertQuery.Append(";");
}
//build banips insert query
StringBuilder addressesInsertQuery = new StringBuilder();
if (ban.BanAddresses != null && ban.BanAddresses.Count > 0)
{
addressesInsertQuery.Append("insert into banips (banip_ipaddress, banip_ban_id) values ");
for (int i = 0; i < ban.BanAddresses.Count; i++)
{
addressesInsertQuery.AppendFormat("{0}('{1}', @banid)", i > 0 ? "," : string.Empty, DataCommand.MakeSqlSafe(ban.BanAddresses[i]));
}
addressesInsertQuery.Append(";");
}
string query = string.Format(@" /* Bans_Insert */
insert into bans (ban_by_user_id, ban_expires, ban_reason, ban_revokemask, ban_last_edit)
values(?BAN_BY_USER_ID, ?BAN_EXPIRES, ?BAN_REASON, ?BAN_REVOKEMASK, ?BAN_LAST_EDIT);
select LAST_INSERT_ID();
select LAST_INSERT_ID() into @banid;
{0}
{1}", userIdInsertQuery, addressesInsertQuery);
return(uint.Parse(Catalog.NewQuery(query)
.With("BAN_BY_USER_ID", ban.ByUserId)
.With("BAN_EXPIRES", ban.Expires)
.With("BAN_REASON", ban.Reason)
.With("BAN_REVOKEMASK", ban.RevokeMask)
.With("BAN_LAST_EDIT", ban.LastEdit)
.Read()));
}
//--- Class Methods ---
public XDoc RecentChanges_GetPageRecentChanges(PageBE page, DateTime since, bool recurse, bool createOnly, uint?limit)
{
string query = @"/* RecentChanges_GetPageRecentChanges */
SELECT
rc_id, rc_comment, rc_cur_id, rc_last_oldid, rc_this_oldid, rc_namespace, rc_timestamp, rc_title, rc_type, rc_moved_to_ns, rc_moved_to_title,
user_name AS rc_user_name, user_real_name as rc_full_name,
(page_id IS NOT NULL) AS rc_page_exists,
IF(page_id IS NULL, 0, page_revision) AS rc_revision,
cmnt_id, cmnt_number, cmnt_content, cmnt_content_mimetype, (cmnt_delete_date IS NOT NULL) as cmnt_deleted,
old_is_hidden
FROM recentchanges
LEFT JOIN old ON rc_this_oldid=old_id
LEFT JOIN users ON rc_user=user_id
LEFT JOIN comments ON ((rc_type=40 AND cmnt_page_id=rc_cur_id AND rc_user=cmnt_poster_user_id AND STR_TO_DATE(rc_timestamp,'%Y%m%e%H%i%s')=cmnt_create_date) OR (rc_type=41 AND cmnt_page_id=rc_cur_id AND rc_user=cmnt_last_edit_user_id AND STR_TO_DATE(rc_timestamp,'%Y%m%e%H%i%s')=cmnt_last_edit) OR (rc_type=42 AND cmnt_page_id=rc_cur_id AND rc_user=cmnt_deleter_user_id AND STR_TO_DATE(rc_timestamp,'%Y%m%e%H%i%s')=cmnt_delete_date))
JOIN (
( SELECT page_id, page_namespace, page_title, page_revision
FROM pages
WHERE page_id = {0} )
UNION
( SELECT page_id, page_namespace, page_title, page_revision
FROM pages
where ({4}) AND (page_is_redirect=0 OR page_is_redirect IS NULL)
)
)p ON rc_cur_id = p.page_id
{1} {2}
ORDER BY rc_timestamp DESC, rc_id DESC
LIMIT {3}";
query = string.Format(query, page.ID, Nav_GetTimestampQuery(since), Nav_GetChangeTypeLimitQuery(createOnly), limit ?? UInt32.MaxValue, recurse ? string.Format("(page_title like '{1}%') AND (page_namespace={0} AND (('{1}' = '' AND page_title != '') OR (LEFT(page_title, CHAR_LENGTH('{1}') + 1) = CONCAT('{1}', '/') AND SUBSTRING(page_title, CHAR_LENGTH('{1}') + 2, 1) != '/')))", (int)page.Title.Namespace, DataCommand.MakeSqlSafe(page.Title.AsUnprefixedDbPath())) : string.Format("page_id={0}", page.ID));
return(Catalog.NewQuery(query).ReadAsXDoc("table", "change"));
}
public override User GetUser(string user)
{
string name = "", email = "";
DataCommand cmd = _catalog.NewQuery(string.Format("SELECT username, email, usertype FROM {1}users WHERE username='******' AND block=0 LIMIT 1", DataCommand.MakeSqlSafe(user), _prefix));
XDoc result = cmd.ReadAsXDoc("users", "user");
if (result["user"].IsEmpty)
{
return(null);
}
name = result["user/username"].AsText;
email = result["user/email"].AsText;
Group group = new Group(result["user/usertype"].AsText);
return(new User(name, email, new Group[] { group }));
}
public override Group GetGroup(string group)
{
DataCommand cmd = _catalog.NewQuery(string.Format("SELECT name AS role_name FROM {1}role WHERE name='{0}' LIMIT 1", DataCommand.MakeSqlSafe(group), _prefix));
XDoc role = cmd.ReadAsXDoc("roles", "role");
if (role["role/role_name"].IsEmpty)
{
return(null);
}
return(new Group(role["role/role_name"].AsText));
}
public override Group GetGroup(string group)
{
DataCommand cmd;
// Compatability with 1.0 version
switch (_version.Substring(0, 3))
{
case "1.0":
cmd = _catalog.NewQuery(string.Format("SELECT name FROM {1}usertypes where name='{0}' LIMIT 1", DataCommand.MakeSqlSafe(group), _prefix));
break;
default:
cmd = _catalog.NewQuery(string.Format("SELECT name FROM {1}core_acl_aro_groups where name='{0}' LIMIT 1", DataCommand.MakeSqlSafe(group), _prefix));
break;
}
XDoc groupXDoc = cmd.ReadAsXDoc("groups", "group");
if (groupXDoc["group/name"].IsEmpty)
{
return(null);
}
return(new Group(groupXDoc["group/name"].AsText));
}
public override User GetUser(string user)
{
string name = "", email = "";
Group group = new Group("anonymous user");
DataCommand cmd = _catalog.NewQuery(string.Format("SELECT u.name, u.mail AS email, r.name AS group_name FROM {1}users u LEFT OUTER JOIN {1}users_roles ur ON u.uid=ur.uid LEFT OUTER JOIN {1}role r ON ur.rid=r.rid WHERE u.name='{0}' LIMIT 1", DataCommand.MakeSqlSafe(user), _prefix));
XDoc result = cmd.ReadAsXDoc("users", "user");
if (result["user"].IsEmpty)
{
return(null);
}
name = result["user/name"].AsText;
email = result["user/email"].AsText;
string groupName = result["user/group_name"].AsText;
if (!string.IsNullOrEmpty(groupName))
{
group = new Group(groupName);
}
return(new User(name, email, new Group[] { group }));
}
public override bool CheckUserPassword(string user, string password)
{
DataCommand cmd = _catalog.NewQuery(string.Format("SELECT password FROM {1}users WHERE username='******' AND block=0 LIMIT 1", DataCommand.MakeSqlSafe(user), _prefix));
string remotePassword = cmd.Read();
if (remotePassword == null)
{
return(false);
}
if (remotePassword == password)
{
return(true);
}
if (remotePassword.Contains(":"))
{
// Extract Salt password
string[] split = remotePassword.Split(':');
// Encode in MD5
byte[] textBytes = Encoding.UTF8.GetBytes(password + split[1]);
byte[] hash = MD5.Create().ComputeHash(textBytes);
// Create hash to Hex string
StringBuilder s = new StringBuilder();
foreach (byte a in hash)
{
s.Append(a.ToString("x2").ToLower());
}
if (String.Compare(split[0], s.ToString(), true) == 0)
{
return(true);
}
}
return(false);
}
