[SQL]
# Core
StartTrans=BEGIN
CommitTrans=COMMIT
CheckUser=SELECT userid,nickname,realname,realm,email FROM users WHERE email=? AND password=SHA1(?)
CheckUserOld=SELECT userid,nickname,realname,realm,email FROM users WHERE email=? AND password=OLD_PASSWORD(?)
CreateSession=INSERT INTO sessions (timeout,name,userid,realm,folderid,langcode,optionid,labyrinth) VALUES (?,?,?,?,1,?,?,?)
SaveSession=UPDATE sessions SET timeout=?,name=?,userid=?,realm=?,optionid=? WHERE labyrinth=?
CheckSession=SELECT userid,name,realm,folderid,langcode,optionid FROM sessions WHERE labyrinth=?
DeleteSessions=DELETE FROM sessions WHERE timeout < ?
DeleteSession=DELETE FROM sessions WHERE labyrinth=?
CountSessions=SELECT DISTINCT s.userid,s.name as realname FROM sessions AS s \
LEFT JOIN users AS u ON u.userid=s.userid
RetrieveSession=SELECT query FROM sessions WHERE labyrinth=?
StoreSession=UPDATE sessions SET query=? WHERE labyrinth=?
UpdateSession=UPDATE sessions SET $field=? WHERE labyrinth=?
UpdateSessionX=UPDATE sessions SET timeout=?,name=?,userid=?,realm=?,langcode=?,optionid=? WHERE labyrinth=?
AllRealms=SELECT * FROM realms
GetRealmByID=SELECT * FROM realms WHERE realmid=?
GetRealmByName=SELECT * FROM realms WHERE realm=?
#Articles
AllArticles=SELECT * FROM articles $where ORDER BY createdate DESC $limit
GetArticlesLatest=SELECT title,userid,quickname FROM articles \
WHERE sectionid=1 AND publish=3 ORDER BY createdate DESC
GetArticlesFrontPage=SELECT title,userid,quickname,snippet,imageid,createdate \
FROM articles \
WHERE sectionid=1 AND publish=3 AND front=1 \
ORDER BY createdate DESC
GetArticleByName=SELECT * FROM articles WHERE quickname=?
GetArticleByID=SELECT * FROM articles WHERE articleid=?
AddArticle=INSERT INTO articles (folderid,title,userid,createdate, \
sectionid,quickname,publish) VALUES (?,?,?,?,?,?,?)
SaveArticle=UPDATE articles SET folderid=?,title=?,userid=?, \
sectionid=?,quickname=?,snippet=?,imageid=?,front=?,publish=?, \
createdate=? \
WHERE articleid=?
DeleteArticle=DELETE FROM articles WHERE articleid IN ($ids)
GetContent=SELECT p.*,i.link FROM paragraphs AS p \
LEFT JOIN images AS i ON i.imageid=p.imageid \
WHERE p.articleid=? ORDER BY p.orderno
AddContent=INSERT INTO paragraphs (articleid,orderno,type,imageid,href, \
body,align) \
VALUES (?,?,?,?,?,?,?)
SaveContent=UPDATE paragraphs SET articleid=?,orderno=?,type=?,imageid=?, \
href=?,body=?,align=? WHERE paraid=?
Relocate=UPDATE paragraphs SET orderno=? WHERE paraid=?
DeleteContent=DELETE FROM paragraphs WHERE paraid=?
DeleteArticleContent=DELETE FROM paragraphs WHERE articleid IN ($ids)
PromoteArticle=UPDATE articles SET publish=? WHERE articleid=?
FindTitle=SELECT articleid FROM articles WHERE title=?
CheckFrontPageArticles=SELECT articleid FROM articles \
WHERE sectionid=1 AND publish=3 AND front=1
SetFrontPageArticle=UPDATE articles SET front=1 WHERE articleid=?
ClearFrontPageArticle=UPDATE articles SET front=0 WHERE articleid=?
# IP Address
FindIPAddress=SELECT * FROM ipindex WHERE ipaddr=?
AddIPAddress=INSERT INTO ipindex (author,type,ipaddr) VALUES (?,?,?)
SaveIPAddress=UPDATE ipindex SET author=?,type=? WHERE ipaddr=?
#Folders
AllFolders=SELECT f.*,a.accessname,f2.path as parentname,f.path as foldername FROM folders f \
INNER JOIN access a ON a.accessid=f.accessid \
LEFT JOIN folders f2 ON f2.folderid=f.parent \
ORDER BY foldername
GetFolder=SELECT * FROM folders WHERE folderid=?
GetFolderByPath=SELECT * FROM folders WHERE path=?
GetFolderAccess=SELECT folderid FROM acls WHERE groupid IN ($groups) OR userid=$userid) AND accessid >= $access
InsertFolder=INSERT INTO folders SET path=?,accessid=?,parent=?
UpdateFolder=UPDATE folders SET path=?,accessid=?,parent=? WHERE folderid=?
DeleteFolder=DELETE FROM folders WHERE folderid IN ($ids)
# Access Permissions
AllAccess=SELECT * FROM access WHERE accessid <= ? ORDER BY accessid
GetPermission=SELECT a.folderid,a.groupid,a.userid,a.accessid \
FROM acls AS a \
INNER JOIN folders AS f ON a.folderid=f.folderid \
WHERE a.folderid IN ($folders) AND (a.groupid IN ($groups) \
OR a.userid = $user) \
ORDER BY f.parent
UserACLs=SELECT l.*,f.path,a.accessname,b.accessname accesspath FROM acls AS l \
INNER JOIN access AS a ON a.accessid=l.accessid \
INNER JOIN folders AS f ON f.folderid=l.folderid \
INNER JOIN access AS b ON b.accessid=f.accessid \
WHERE l.userid=? ORDER BY f.path
UserACLCheck=SELECT * FROM acls WHERE userid=? AND folderid=?
UserACLInsert=INSERT INTO acls (accessid,userid,folderid) VALUES (?,?,?)
UserACLUpdate=UPDATE acls SET accessid=? WHERE userid=? AND folderid=?
UserACLDelete=DELETE FROM acls WHERE userid=? AND accessid=? AND folderid=?
GroupACLs=SELECT * FROM acls AS l \
INNER JOIN access AS a ON a.accessid=l.accessid \
INNER JOIN groups AS g ON g.groupid=l.groupid \
WHERE l.groupid=?
GroupACLSave=INSERT INTO acls (groupid,accessid,folderid) VALUES (?,?,?)
GroupACLDelete=DELETE FROM acls WHERE groupid=? AND accessid=? AND folderid=?
#Groups
GetGroupUserMap=SELECT groupid FROM ixusergroup WHERE type=1 AND linkid=?
GetGroupParents=SELECT groupid FROM ixusergroup WHERE type=2 \
AND linkid IN ($groups)
#Hits
PageNow=SELECT now FROM updates WHERE pageid=0 AND area='site'
PageTimeStamp=SELECT pagets FROM updates WHERE area=? AND pageid=?
PageCounter=SELECT counter FROM hits WHERE area=? AND pageid=? AND photoid=?
AdminUpdates=SELECT * FROM updates ORDER BY pagets desc
GetUpdate=SELECT * FROM updates WHERE area=? AND pageid=?
SetUpdate=UPDATE updates SET now=? WHERE area=? AND pageid=?
AddUpdate=INSERT INTO updates (now,area,pageid) VALUES (?,?,?)
AddAHit=INSERT INTO hits (counter,area,pageid,photoid,query,createdate) VALUES (?,?,?,?,?,?)
GetAHit=SELECT SUM(counter) FROM hits WHERE area=? AND query=?
SumHits=SELECT area,query,pageid,photoid,SUM(counter) AS counter,count(*) AS number,max(createdate) AS createdate FROM hits WHERE createdate <= ? AND area=? GROUP BY query,pageid,photoid
DelHits=DELETE FROM hits WHERE createdate <= ? AND area=?
DelAHit=DELETE FROM hits WHERE createdate <= ? AND area=? AND pageid=? AND photoid=? AND query=?
DelAHit2=DELETE FROM hits WHERE createdate <= ? AND area=? AND pageid=? AND photoid=? AND (query='' OR query IS NULL)
GetHitAreas=SELECT area,count(*) AS count FROM hits GROUP BY area ORDER BY count
PageHitsAllTime=SELECT sum(h.counter) AS counter,h.area,h.query \
FROM hits AS h \
WHERE h.pageid=0 AND h.photoid=0 \
GROUP BY h.area, h.query \
ORDER BY counter DESC,h.area,h.query LIMIT 10
PageHitsLastMonth=SELECT sum(h.counter) AS counter,h.area,h.query \
FROM hits AS h \
WHERE h.pageid=0 AND h.photoid=0 AND h.createdate > ? \
GROUP BY h.area, h.query \
ORDER BY counter DESC,h.area,h.query LIMIT 20
AlbumHitsAllTime=SELECT * FROM hits WHERE pageid > 0 AND photoid = 0 LIMIT 10
AlbumHitsLastMonth=SELECT * FROM hits WHERE pageid > 0 AND photoid = 0 AND createdate > ? LIMIT 10
PhotoHitsAllTime=SELECT * FROM hits WHERE photoid > 0 LIMIT 10
PhotoHitsLastMonth=SELECT * FROM hits WHERE photoid > 0 AND createdate > ? LIMIT 10
AllHits=SELECT * FROM hits
#Images
GetAllImages=SELECT * FROM images
GetImagesByType=SELECT * FROM images WHERE type=? ORDER BY link,tag
GetDefaultImages=SELECT * FROM images WHERE tag='DEFAULT'
GetImageByID=SELECT * FROM images WHERE imageid=?
AddImage=INSERT INTO images (tag,link,type,href,dimensions) VALUES (?,?,?,?,?)
SaveImage=UPDATE images SET tag=?,link=?,type=?,href=?,dimensions=? WHERE imageid=?
DeleteImage=DELETE FROM images WHERE imageid=?
AllImageStock=SELECT * FROM imagestock
#Menus
AllMenus=SELECT * FROM menus
GetMenus=SELECT * FROM menus WHERE realmid=?
GetMenuByID=SELECT * FROM menus WHERE menuid=?
GetAllOptions=SELECT o.optionid,m.title,o.text FROM options AS o \
INNER JOIN menus AS m ON o.menuid=m.menuid \
WHERE o.menuid != ? AND o.text != '' ORDER BY m.title,o.name
GetOptions=SELECT * FROM options WHERE menuid=? ORDER BY orderno
FindOptions=SELECT * FROM options WHERE menuid IN ($ids)
AddMenu=INSERT INTO menus (name,title,typeid,realmid,parentid) VALUES (?,?,?,?,?)
AddOption=INSERT INTO options (menuid,orderno) VALUES (?,?)
SaveMenu=UPDATE menus SET name=?,title=?,typeid=?,realmid=?,parentid=? WHERE menuid=?
SaveOption=UPDATE options SET orderno=?,name=?,section=?,text=?,href=?,accessid=? WHERE optionid=?
DeleteMenu=DELETE FROM menus WHERE menuid IN ($ids)
DeleteOptions=DELETE FROM options WHERE optionid IN ($ids)
GetOptImages=SELECT * FROM optimages AS o \
INNER JOIN images AS i ON o.imageid=i.imageid \
WHERE optionid=? ORDER BY typeid
AddOptImage=INSERT INTO optimages (imageid,optionid,typeid) VALUES (?,?,?)
SaveOptImage=UPDATE optimages SET imageid=? WHERE optionid=? AND typeid=?
DeleteOptImages=DELETE FROM optimages WHERE optionid IN ($ids)
# Requests
AllRequests=SELECT * FROM requests ORDER BY section,command
AllSections=SELECT DISTINCT(section) FROM requests ORDER BY section
AllRequestSection=SELECT * FROM requests WHERE section=? ORDER BY section,command
GetRequestByID=SELECT * FROM requests WHERE requestid=?
GetRequest=SELECT * FROM requests WHERE section=? AND command=?
AddRequest=INSERT INTO requests (section,command,actions,layout,content,onsuccess,onerror,onfailure,secure,rewrite) VALUES (?,?,?,?,?,?,?,?,?,?)
SaveRequest=UPDATE requests SET section=?,command=?,actions=?,layout=?,content=?,onsuccess=?,onerror=?,onfailure=?,secure=?,rewrite=? WHERE requestid=?
DeleteRequests=DELETE FROM requests WHERE requestid IN ($ids)
#Metadata
MetaCloudArt=SELECT count(metadata) as count, metadata \
FROM mxarticles AS x \
INNER JOIN articles AS a ON a.articleid=x.articleid \
WHERE sectionid IN ($ids) GROUP BY metadata;
MetaSearchArt=SELECT x.articleid AS id,a.* FROM mxarticles AS x \
INNER JOIN articles AS a ON a.articleid=x.articleid \
WHERE metadata IN ($meta) $where $limit
MetaDetailArt=SELECT DISTINCT(a.articleid) AS id,a.* FROM articles AS a \
INNER JOIN paragraphs AS p ON a.articleid=p.articleid \
INNER JOIN mxarticles AS x ON a.articleid=x.articleid \
WHERE (a.title REGEXP '$data' OR p.body REGEXP '$data' OR x.metadata IN ($meta)) $where $limit
MetaDeleteArt=DELETE FROM mxarticles WHERE articleid=?
MetaUpdateArt=INSERT INTO mxarticles (articleid,metadata) VALUES (?,?)
MetaGetArt=SELECT * FROM mxarticles WHERE articleid=?
MetaSearchImage=SELECT * FROM photos AS i \
INNER JOIN imetadata AS m ON i.photoid=m.imageid \
WHERE tag IN ($meta) $where $limit
MetaUpdateImage=INSERT INTO imetadata (imageid,tag) VALUES (?,?)
MetaDeleteImage=DELETE FROM imetadata WHERE imageid=?
MetaGetImage=SELECT * FROM imetadata WHERE imageid=?
#Users
GetUserByID=SELECT u.*,i.link,i.tag \
FROM users AS u \
INNER JOIN images AS i ON u.imageid=i.imageid \
WHERE u.userid=?
SearchUserNames=SELECT * FROM users AS u \
LEFT JOIN images AS i ON u.imageid=i.imageid \
WHERE (nickname LIKE ? OR realname LIKE ?) $where \
ORDER BY $order
SearchUsers=SELECT * FROM users AS u \
LEFT JOIN images AS i ON u.imageid=i.imageid \
WHERE userid > 1 AND (nickname LIKE ? OR realname LIKE ?) $where \
ORDER BY $order
AllUsers=SELECT * FROM users
NewUser=INSERT INTO users (password,accessid,search,nickname,realname,email,imageid,realm) \
VALUES (SHA1(?),?,?,?,?,?,?,?)
SaveUser=UPDATE users SET nickname=?,realname=?,email=?,imageid=?,realm=? WHERE userid=?
DeleteUser=DELETE FROM users WHERE userid IN ($del)
ChangePassword=UPDATE users SET password=password(?) WHERE userid=?
BanUser=UPDATE users SET password=? WHERE userid=?
FindUser=SELECT userid,password,realname FROM users WHERE email = ?
# Groups
AllGroups=SELECT * FROM groups $where ORDER BY groupname
GroupCount=SELECT COUNT(linkid) AS count FROM ixusergroup WHERE groupid = ? AND type=1 GROUP BY groupid
GetGroup=SELECT * FROM groups WHERE groupid=?
LinkUsers=SELECT i.*,u.email,u.nickname,u.realname FROM ixusergroup AS i \
INNER JOIN users AS u ON i.linkid=u.userid \
WHERE i.type=1 AND i.groupid=? \
ORDER BY u.realname
LinkedUsers=SELECT i.*,g.groupname,g.member FROM ixusergroup AS i \
INNER JOIN groups AS g ON i.groupid=g.groupid \
WHERE i.type=1 AND i.linkid=?
LinkGroups=SELECT i.*,g.groupname FROM ixusergroup AS i \
INNER JOIN groups AS g ON i.linkid=g.groupid \
WHERE i.type=2 AND i.groupid=?
AllGroupIndex=SELECT * FROM ixusergroup WHERE type=2
AddGroup=INSERT INTO groups (groupname,master) VALUES (?,0)
AddLinkIndex=INSERT INTO ixusergroup (type,linkid,groupid) VALUES (?,?,?)
SaveGroup=UPDATE groups SET groupname=? WHERE groupid=?
DeleteGroup=DELETE FROM groups WHERE groupid=?
DeleteGroupIndex=DELETE FROM ixusergroup WHERE groupid=?
DeleteLinkIndices=DELETE FROM ixusergroup WHERE linkid=? AND i.type=?
DeleteLinkIndex=DELETE FROM ixusergroup \
WHERE type=? AND linkid=? AND groupid=?
GetGroupID=SELECT groupid FROM groups WHERE groupname=?
UserGroups=SELECT i.*,g.groupname FROM ixusergroup AS i \
INNER JOIN groups AS g ON i.groupid=g.groupid \
WHERE type=1 AND linkid=?
# General Report Info
AllOSNames=SELECT osname,ostitle FROM cpanstats.osname;
OnCPAN=SELECT type FROM cpanstats.uploads WHERE dist=? AND version=?
GetPerls=SELECT DISTINCT(perl) FROM cpanstats.perl_version;
MaxStatReport=SELECT MAX(id) FROM cpanstats.cpanstats
GetStatReport=SELECT * FROM cpanstats.cpanstats WHERE id=?
AllDistIndices=SELECT uploadid,dist,version,type FROM cpanstats.uploads;
# Author Reports
GetAuthors=SELECT DISTINCT(author) FROM cpanstats.uploads WHERE author LIKE ?
GetAuthorDists=SELECT x.dist,x.released,x.version,u.type FROM cpanstats.ixlatest AS x \
INNER JOIN cpanstats.uploads AS u ON u.dist=x.dist AND u.version=x.version \
WHERE x.author=? ORDER BY x.dist
GetAuthorDistReports=SELECT c.guid, c.id, c.state, c.dist, c.version, c.perl, c.osname, c.osvers, c.platform, c.fulldate FROM cpanstats.cpanstats AS c \
INNER JOIN cpanstats.ixlatest AS x ON x.uploadid=c.uploadid \
WHERE x.author=? AND c.type=2 AND x.uploadid!=0 $fromid ORDER BY id DESC
GetAuthorRequests=SELECT count(*) FROM cpanstats.page_requests WHERE type='author' AND name=?
GetAuthorDistReports2=SELECT c.guid, c.id, c.state, c.dist, c.version, c.perl, c.osname, c.osvers, c.platform, c.fulldate FROM cpanstats.ixlatest AS x \
RIGHT JOIN cpanstats.cpanstats AS c ON x.dist=c.dist AND x.version=c.version \
WHERE x.author=? AND c.type=2 AND c.version IS NOT NULL AND c.version!='' ORDER BY id DESC
GetAuthorDistReports3=SELECT c.guid, c.id, c.state, c.dist, c.version, c.perl, c.osname, c.osvers, c.platform, c.fulldate FROM cpanstats.cpanstats AS c \
WHERE c.type=2 $lookup $fromid ORDER BY id DESC
GetAuthorDistReports4=SELECT c.guid, c.id, c.state, c.dist, c.version, c.perl, c.osname, c.osvers, c.platform, c.fulldate FROM cpanstats.ixlatest AS x \
RIGHT JOIN cpanstats.cpanstats AS c ON x.dist=c.dist AND x.version=c.version \
WHERE x.author=? AND c.type=2 AND c.version IS NOT NULL AND c.version!='' AND id > ? ORDER BY id DESC
CheckLatest=SELECT * FROM cpanstats.ixlatest WHERE dist=? AND version=?
# Distribution Reports
GetDistro=SELECT * FROM cpanstats.uploads WHERE dist=? ORDER BY released
GetDistroByDistVers=SELECT * FROM cpanstats.uploads WHERE dist=? AND version=?
GetDistros=SELECT DISTINCT(dist) FROM cpanstats.uploads WHERE dist LIKE ?
GetDistroRequests=SELECT count(*) FROM cpanstats.page_requests WHERE type='distro' AND name=?
GetDistroReports=SELECT guid, id, state, dist, version, perl, osname, osvers, platform, fulldate FROM cpanstats.cpanstats \
WHERE dist IN ('$dist') $fromid AND type = 2 AND version IS NOT NULL AND version!='' ORDER BY version, id
GetDistVersions=SELECT DISTINCT(version) FROM cpanstats.uploads WHERE dist IN ('$dist') ORDER BY released DESC
GetDistrosPass=SELECT perl, osname, count(*) AS count FROM cpanstats.cpanstats \
WHERE dist IN ('$dist') AND state = 'pass' GROUP BY perl, osname
GetStatsPass=SELECT perl, osname, version FROM cpanstats.cpanstats WHERE dist IN ('$dist') AND state='pass'
GetStatsStore=SELECT * FROM cpanstats.stats_store WHERE dist=?
DelStatsStore=DELETE FROM cpanstats.stats_store WHERE dist=?
UpdStatsStore=UPDATE cpanstats.stats_store SET dist=?,perl=?,osname=?,version=?,counter=?,lastid=? WHERE storeid=?
SetStatsStore=INSERT INTO cpanstats.stats_store (dist,perl,osname,version,counter,lastid) VALUES (?,?,?,?,?,?)
GetStatsPass2=SELECT id, perl, osname, version FROM cpanstats.cpanstats WHERE dist IN ('$dist') AND state='pass' AND id>?
GetDistVersReports=SELECT * FROM cpanstats.cpanstats WHERE dist=? AND version=?
GetReportsByIDs=SELECT * FROM cpanstats.cpanstats WHERE id IN ($ids)
GetRequestIDs=SELECT id FROM cpanstats.page_requests WHERE type=? AND name IN ('$names')
# Page Requests
PushAuthor=INSERT INTO cpanstats.page_requests (type,name,weight) VALUES ('author',?,10)
PushDistro=INSERT INTO cpanstats.page_requests (type,name,weight) VALUES ('distro',?,10)
PushAuthorID=INSERT INTO cpanstats.page_requests (type,name,weight,id) VALUES ('author',?,10,?)
PushDistroID=INSERT INTO cpanstats.page_requests (type,name,weight,id) VALUES ('distro',?,10,?)
GetRequests=SELECT type,name,COUNT(*) AS count,SUM(weight) AS total, min(created) AS created FROM cpanstats.page_requests \
WHERE type IN ($types) GROUP BY name ORDER BY total DESC,count DESC,created LIMIT $limit
GetSmallRequests=SELECT type,name,COUNT(*) AS count,SUM(weight) AS total, min(created) AS created FROM cpanstats.page_requests \
WHERE type IN ($types) GROUP BY name ORDER BY total,count,created LIMIT $limit
GetOlderRequests=SELECT type,name,COUNT(*) AS count,SUM(weight) AS total, min(created) AS created FROM cpanstats.page_requests \
WHERE type IN ($types) GROUP BY name ORDER BY created,total DESC,count DESC LIMIT $limit
GetLargeRequests=SELECT type,name,COUNT(*) AS count,SUM(weight) AS total, min(created) AS created FROM cpanstats.page_requests \
WHERE type IN ($types) GROUP BY name ORDER BY count DESC,total DESC,created LIMIT $limit
GetIndexRequests=SELECT type,name,COUNT(*) AS count FROM cpanstats.page_requests \
WHERE type IN ($types) GROUP BY name,type ORDER BY count DESC
DeletePageRequests=DELETE FROM cpanstats.page_requests WHERE type=? AND name=? AND id IN ($ids)
StatusRequest=SELECT MIN(created) AS created,COUNT(*) AS total,COUNT(DISTINCT name) AS count FROM cpanstats.page_requests
# Summary
GetAuthorSummary=SELECT * FROM cpanstats.summary WHERE type='author' AND name=?
UpdateAuthorSummary=UPDATE cpanstats.summary SET lastid=?,dataset=? WHERE type='author' AND name=?
InsertAuthorSummary=INSERT INTO cpanstats.summary (lastid,dataset,type,name) VALUES (?,?,'author',?)
GetDistroSummary=SELECT * FROM cpanstats.summary WHERE type='distro' AND name=?
UpdateDistroSummary=UPDATE cpanstats.summary SET lastid=?,dataset=? WHERE type='distro' AND name=?
InsertDistroSummary=INSERT INTO cpanstats.summary (lastid,dataset,type,name) VALUES (?,?,'distro',?)
# Pass Matrix Statistics
GetStats=SELECT dist, version, perl, osname FROM cpanstats.cpanstats \
WHERE state = 'pass' AND perl LIKE '5.%' AND perl NOT LIKE '% %' \
AND perl NOT REGEXP '5\.(7|9|11)' AND version REGEXP '[0-9.]'
OldestRequest=SELECT DATEDIFF(NOW(),created) FROM cpanstats.page_requests ORDER BY created LIMIT 1
CountRequests=SELECT count(distinct name) FROM cpanstats.page_requests
GetAllAuthors=SELECT distinct author FROM cpanstats.uploads
GetAllDistros=SELECT distinct dist FROM cpanstats.uploads
GetAllDistrosX=SELECT dist FROM cpanstats.uploads ORDER BY released
CountDistroReports=SELECT count(id) FROM cpanstats.cpanstats WHERE dist=? AND type=2
FindAuthor=SELECT * FROM cpanstats.uploads WHERE author=? LIMIT 1
FindDistro=SELECT type,dist,version,author FROM cpanstats.uploads WHERE dist IN ('$dist') ORDER BY released
GetRecent=SELECT guid, id, state, dist, version, perl, osname, osvers, platform FROM cpanstats.cpanstats WHERE type = 2 ORDER BY id DESC $limit
# Release Data
GetReleaseDataMax=SELECT MAX(id) FROM cpanstats.release_data
GetSummaryBlock=SELECT id FROM cpanstats.release_data WHERE id > ? AND id <= ?
GetReportBlock=SELECT * FROM cpanstats.cpanstats WHERE id > ? AND id <= ? AND type = 2
InsertReleaseData=INSERT INTO cpanstats.release_data (dist,version,id,guid,oncpan,distmat,perlmat,patched,pass,fail,na,unknown,uploadid) \
VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?)
# Release Summary
GetReleaseSummaryMax=SELECT MAX(id) FROM cpanstats.release_summary
GetReleaseData=SELECT dist,version,id,guid,oncpan,distmat,perlmat,patched,pass,fail,na,unknown,uploadid FROM cpanstats.release_data WHERE id > ? AND id <= ?
GetReleaseSummary=SELECT * FROM cpanstats.release_summary WHERE dist=? AND version=? AND oncpan=? AND distmat=? AND perlmat=? AND patched=?
UpdateReleaseSummary=UPDATE cpanstats.release_summary SET id=?,guid=?,pass=?,fail=?,na=?,unknown=?,uploadid=? \
WHERE dist=? AND version=? AND oncpan=? AND distmat=? AND perlmat=? AND patched=?
InsertReleaseSummary=INSERT INTO cpanstats.release_summary (id,guid,pass,fail,na,unknown,uploadid,dist,version,oncpan,distmat,perlmat,patched) \
VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?)
DeleteReleaseSummary=DELETE FROM cpanstats.release_summary WHERE dist=? AND version=? AND oncpan=? AND distmat=? AND perlmat=? AND patched=?
GetReleaseDataByUpload=SELECT dist,version,id,guid,oncpan,distmat,perlmat,patched,pass,fail,na,unknown,uploadid FROM cpanstats.release_data WHERE uploadid=?
GetReleaseDataByDistVers=SELECT dist,version,id,guid,oncpan,distmat,perlmat,patched,pass,fail,na,unknown,uploadid FROM cpanstats.release_data WHERE dist=? AND version=?
GetReleaseDataByDist=SELECT dist,version,id,guid,oncpan,distmat,perlmat,patched,pass,fail,na,unknown,uploadid FROM cpanstats.release_data WHERE dist=?
DeleteReleaseSummaryByUpload=DELETE FROM cpanstats.release_summary WHERE uploadid=?
DeleteReleaseSummaryByDistVers=DELETE FROM cpanstats.release_summary WHERE dist=? AND version=?
DeleteReleaseSummaryByDist=DELETE FROM cpanstats.release_summary WHERE dist=?
GetReleaseDists=select distinct dist from release_summary
# Reports
AllReports=SELECT * FROM cpanstats.cpanstats WHERE type = 2
GetReportMax=SELECT MAX(id) FROM cpanstats.cpanstats
GetReportOS=SELECT osname,platform FROM cpanstats.cpanstats WHERE id=?
GetReportBlankOS=SELECT id,guid FROM cpanstats.cpanstats WHERE osname=''
SetReportOS=UPDATE cpanstats.cpanstats SET osname=? WHERE id=?
# Monitor
InsertSnapshot=INSERT INTO cpanstats.monitor (now,day,month,year,name_count,page_count,page_weight) VALUES (?,?,?,?,?,?,?)
CreateSnapshot=SELECT NOW(),day(created) AS day,month(created) AS month,year(created) AS year, \
count(distinct(name)) AS dist_count,count(*) AS page_count,sum(weight) AS weight \
FROM cpanstats.page_requests GROUP BY day ORDER BY year,month,day
CreateSnapshot0=SELECT NOW(),day(NOW()) AS day,month(NOW()) AS month,year(NOW()) AS year, 0 AS dist_count,0 AS page_count,0 AS weight
GetSnapshots=SELECT * FROM cpanstats.monitor WHERE now > '$timestamp' ORDER BY year,month,day,now
GetArticle=SELECT * FROM articles.articles WHERE id=?
GetReport=SELECT * FROM metabase.reports WHERE guid=?
# Metabase
GetMetabaseByGUID=SELECT * FROM metabase.metabase WHERE guid=?
GetTesterFactX=SELECT * FROM metabase.testers_email WHERE resource=?
GetTesterFact=SELECT mte.*,tp.* FROM metabase.testers_email mte \
LEFT JOIN testers.address ta ON ta.email=mte.email \
LEFT JOIN testers.profile tp ON tp.testerid=ta.testerid \
WHERE mte.resource=? ORDER BY tp.testerid DESC
StartTrans=BEGIN
CommitTrans=COMMIT
# dbfix
ShowDatabases=SHOW databases
ShowTables=SHOW tables
UseDatabase=USE $db
RepairTable=REPAIR TABLE $tab
AnalyzeTable=ANALYZE TABLE $tab
[METABASE]
GetReport=SELECT * FROM metabase WHERE id = ?
GetReports=SELECT * FROM metabase WHERE id >= ? AND id <= ?
UpdateReport=UPDATE metabase SET report=? WHERE id=?