[SQL]
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=?
AdminHits=SELECT h.pageid,h.photoid,h.counter FROM hits AS h ORDER BY $orderby h.pageid,h.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 (?,?,?)
GetAHit=SELECT counter FROM hits WHERE area=? AND query=?
SetAHit=UPDATE hits SET counter=? WHERE area=? AND pageid=? AND photoid=?
AddAHit=INSERT INTO hits (counter,area,pageid,photoid,query,createdate) VALUES (?,?,?,?,?,?)
PageHits=SELECT h.* \
FROM hits AS h \
WHERE h.pageid=0 AND h.photoid=0 \
ORDER BY h.counter DESC,h.pageid LIMIT 20
PageHits2=SELECT NULL
AllHits=SELECT * FROM hits
DeleteHits=DELETE FROM hits WHERE pageid=? AND area=? AND photoid=?
CheckUser=SELECT userid,nickname,realname,realm,locked FROM users WHERE email=? AND password=SHA1(?)
CheckUserOld=SELECT userid,nickname,realname,realm,locked FROM users WHERE email=? AND password=OLD_PASSWORD(?)
CreateSession=INSERT INTO sessions (timeout,name,userid,realm,folderid,langcode,optionid,labyrinth) VALUES (?,?,?,?,1,?,?,?)
UpdateSession=UPDATE sessions SET $field=? WHERE labyrinth=?
UpdateSessionX=UPDATE sessions SET timeout=?,name=?,userid=?,realm=?,langcode=?,optionid=? WHERE labyrinth=?
CheckSession=SELECT userid,name,realm,folderid FROM sessions WHERE labyrinth=?
TimeStampSession=UPDATE sessions SET timeout=? 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=?
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 u.userid > 4 AND u.accessid < 4 AND (u.email LIKE ? OR u.realname LIKE ?) $where \
ORDER BY $order
SearchUsers=SELECT * FROM users AS u \
LEFT JOIN images AS i ON u.imageid=i.imageid \
WHERE u.userid > 3 AND u.accessid < 4 AND (u.nickname LIKE ? OR u.realname LIKE ?) $where \
ORDER BY $order
#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)
GetGroupUserMap=SELECT groupid FROM ixusergroup WHERE type=1 AND linkid=?
GetGroupParents=SELECT groupid FROM ixusergroup WHERE type=2 \
AND linkid IN ($groups)
# 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
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=?
AllRealms=SELECT * FROM realms
GetRealmByID=SELECT * FROM realms WHERE realmid=?
GetRealmByName=SELECT * FROM realms WHERE realm=?
AllMenus=SELECT * FROM menus
GetMenus=SELECT * FROM menus WHERE realmid=?
GetMenuByID=SELECT * FROM menus WHERE menuid=?
GetOptions=SELECT * FROM options WHERE menuid=?
FindOptions=SELECT * FROM options WHERE menuid IN ($ids)
AddMenu=INSERT INTO menus (title,typeid,realmid) VALUES (?,?,?)
AddOption=INSERT INTO options (menuid,orderno) VALUES (?,?)
SaveMenu=UPDATE menus SET title=?,typeid=?,realmid=? WHERE menuid=?
SaveOption=UPDATE options SET orderno=?,text=?,href=? WHERE optionid=?
DeleteMenu=DELETE FROM menu 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)
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)
MetaDetailArt=SELECT a.articleid AS id,a.* FROM articles AS a \
INNER JOIN paragraphs AS p ON a.articleid=p.articleid \
WHERE a.title REGEXP '$meta' OR p.body REGEXP '$meta'
MetaDeleteArt=DELETE FROM mxarticles WHERE articleid=?
MetaUpdateArt=INSERT INTO mxarticles (articleid,metadata) VALUES (?,?)
MetaGetArt=SELECT * FROM mxarticles WHERE articleid=?
MetaUpdateImage=INSERT INTO imetadata (imageid,tag) VALUES (?,?)
MetaDeleteImage=DELETE FROM imetadata WHERE imageid=?
MetaSearchImage=SELECT * FROM photos AS i \
INNER JOIN imetadata AS m ON i.photoid=m.imageid \
WHERE tag IN (?)
MetaGetImage=SELECT * FROM imetadata WHERE imageid=?
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 barbie_articles \
(folderid,title,userid,sectionid,quickname,snippet,imageid,front,latest,publish,createdate) \
VALUES (?,?,?,?,?,'',0,0,0,?,?)
SaveArticle=UPDATE barbie_articles \
SET folderid=?,title=?,userid=?,sectionid=?,quickname=?,snippet=?,imageid=?,front=?,latest=?,publish=?,createdate=? \
WHERE articleid=?
DeleteArticle=DELETE FROM articles WHERE articleid IN ($ids)
GetContent=SELECT * FROM paragraphs WHERE articleid=? ORDER BY 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=?
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=?
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
Gallery=SELECT * FROM photos AS i \
WHERE i.photoid >= ? $where \
ORDER BY i.photoid \
LIMIT 10
MetaGallery=SELECT i.* FROM photos AS i \
INNER JOIN imetadata AS m ON i.photoid=m.imageid \
WHERE i.photoid >= ? $where \
ORDER BY i.photoid \
LIMIT 10
GalleryMin=SELECT i.photoid FROM photos AS i \
WHERE i.photoid < ? $where \
ORDER BY i.photoid DESC LIMIT 9
MetaGalleryMin=SELECT i.photoid FROM photos AS i \
INNER JOIN imetadata AS m ON i.photoid=m.imageid \
WHERE i.photoid < ? $where \
ORDER BY i.photoid DESC LIMIT 9
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 ($ids)
ChangePassword=UPDATE users SET password=SHA1(?) WHERE userid=?
BanUser=UPDATE users SET password=? WHERE userid in ($ids)
FindUser=SELECT userid,password,realname FROM users WHERE email = ?
LockUser=UPDATE users SET locked=1 WHERE userid=?
UnLockUser=UPDATE users SET locked=0 WHERE userid=?
SetRealm=UPDATE users SET realm=? WHERE userid=?
FindIPAddress=SELECT * FROM ipindex WHERE ipaddr=?
AddIPAddress=INSERT INTO ipindex (author,type,ipaddr) VALUES (?,?,?)
SaveIPAddress=UPDATE ipindex SET author=?,type=? WHERE ipaddr=?
GetTesterAddress=SELECT * FROM ixtester WHERE userid=? AND confirmed=1
GetTesterAddressIndex=SELECT xa.id FROM testers.ixreport AS xa \
INNER JOIN testers.address AS ta ON xa.addressid = ta.addressid \
INNER JOIN ixtester AS xt ON ta.email = xt.email \
WHERE xt.userid=? AND xt.confirmed=1
GetTesterProfile=SELECT * FROM testers.ixreport AS xa \
INNER JOIN testers.address AS ta ON xa.addressid = ta.addressid \
LEFT JOIN testers.profile AS tp ON tp.testerid = ta.testerid \
WHERE xa.guid=?
FindTesterProfile=SELECT * FROM testers.address AS ta \
LEFT JOIN testers.profile AS tp ON tp.testerid = ta.testerid \
WHERE ta.address=?
MapAddresses=REPLACE ixaddress SET userid=?,addressid=?
RegisteredEmails=SELECT * FROM ixtester WHERE userid=?
FindTesterIndex=SELECT xt.*,ta.addressid FROM ixtester xt \
LEFT JOIN testers.address AS ta ON xa.email = xt.email \
WHERE xt.email=?
RemoveEmail=DELETE FROM ixtester WHERE userid=? AND email IN ($mails)
ConfirmedEmail=UPDATE ixtester SET confirmed=1,confirm='' WHERE userid=? AND email=? AND confirm=?
UnConfirmedEmail=INSERT INTO ixtester (userid,email,confirm,confirmed) VALUES (?,?,?,0)
CheckConfirmedEmail=SELECT * FROM ixtester WHERE userid=? AND email=?
CheckConfirmedCode=SELECT * FROM ixtester WHERE confirm=?
GetContact=SELECT p.testerid,p.contact FROM testers.profile p \
LEFT JOIN testers.address z ON z.testerid=p.testerid \
LEFT JOIN ixaddress x ON x.addressid=z.addressid \
WHERE x.userid = ?
SetContact=UPDATE testers.profile SET contact=? WHERE testerid=?
ListAllMarkedReports=SELECT x.*,cs.*,u.author FROM ixmarked AS x \
INNER JOIN cpanstats.cpanstats AS cs ON x.id=cs.id \
inner join cpanstats.uploads u on u.dist=cs.dist and u.version=cs.version \
ORDER BY cs.fulldate
ListMarkedReports=SELECT * FROM ixaddress AS xa \
INNER JOIN ixmarked AS xm ON xm.addressid=xa.addressid \
INNER JOIN cpanstats.cpanstats AS cs ON cs.id=xm.id \
WHERE xa.userid=? \
ORDER BY cs.fulldate
ListMarkedAuthorReports=SELECT * FROM ixmarked AS x \
INNER JOIN cpanstats.cpanstats AS cs ON x.id=cs.id \
WHERE x.cpanid = ? \
ORDER BY cs.fulldate
MarkReport=REPLACE INTO ixmarked (id,addressid,address,cpanid,markdate) VALUES (?,?,?,?,?)
UnmarkTesterReports=DELETE FROM ixmarked WHERE id IN ($ids)
UnmarkAuthorReports=DELETE FROM ixmarked WHERE id IN ($ids) AND cpanid=?
[CPANSTATS]
# Testers
XGetReportDates=SELECT DISTINCT(fulldate) FROM cpanstats WHERE id IN ($ids)
GetReportDates=SELECT DISTINCT(xa.fulldate) FROM ixaddress AS xa \
INNER JOIN testers.address AS ta ON xa.addressid = ta.addressid \
INNER JOIN cpanadmin.ixtester AS xt ON ta.email = xt.email \
WHERE xt.userid=? AND xt.confirmed=1
XGetReportList=SELECT * FROM cpanstats WHERE fulldate LIKE ? AND type=2 AND tester REGEXP "$addrs"
GetReportList=SELECT xa.id,xm.id as marked FROM ixaddress AS xa \
INNER JOIN testers.address AS ta ON xa.addressid = ta.addressid \
INNER JOIN cpanadmin.ixtester AS xt ON ta.email = xt.email \
LEFT JOIN cpanadmin.ixmarked xm ON xm.id=xa.id \
WHERE xt.userid=? AND xt.confirmed=1 AND xa.fulldate LIKE ?
GetAuthor=SELECT author FROM uploads WHERE dist = ? AND version = ?
GetReport=SELECT * FROM cpanstats WHERE id = ?
GetReports=SELECT c.*,u.author,a.addressid FROM cpanstats c \
LEFT JOIN testers.address a ON a.address=c.tester \
LEFT JOIN uploads u ON u.dist=c.dist AND u.version=c.version \
WHERE id IN ($ids)
FindReport=SELECT * FROM cpanstats WHERE guid=?
FindDistro=SELECT * FROM ixlatest WHERE dist=?
ListReports2=SELECT c.*,u.author FROM ixaddress x \
INNER JOIN cpanadmin.ixaddress xa ON xa.addressid=x.addressid \
INNER JOIN cpanstats c ON c.id=x.id \
LEFT JOIN uploads u ON u.dist=c.dist AND u.version=c.version \
WHERE xa.userid=? AND c.dist=? $prev $next \
ORDER BY fulldate $order LIMIT 1000
CountReports2=SELECT COUNT(*) AS count FROM ixaddress x \
INNER JOIN cpanadmin.ixaddress xa ON xa.addressid=x.addressid \
INNER JOIN cpanstats c ON c.id=x.id \
WHERE xa.userid=? AND c.dist=? $prev $next \
UpdateGrade=UPDATE cpanstats SET type=? WHERE id=?
PageRequest=INSERT INTO page_requests SET type=?,name=?,weight=1,created=NOW(),id=?
DeleteReportHistory=INSERT INTO reports_history SET statsid=?,savedate=NOW(),state=?,postdate=?,dist=?,version=?,oldtype=?,newtype=?
# Authors
GetAuthorDists=SELECT DISTINCT(dist) FROM uploads WHERE author = ?
GetAuthorDistVersions=SELECT DISTINCT(version) FROM uploads WHERE author = ? AND dist = ?
GetAuthorReports=SELECT * FROM cpanstats WHERE dist = ? AND version = ? AND type=2 ORDER BY id DESC
GetAuthorReportDates=SELECT DISTINCT(fulldate) FROM cpanstats \
WHERE dist IN ($dists) AND type=2
GetAuthorReportList=SELECT c.*,xm.id as marked FROM cpanstats c \
LEFT JOIN cpanadmin.ixmarked xm ON xm.id=c.id \
WHERE fulldate LIKE ? AND type=2 AND dist IN ($dists)
ListTesters=SELECT * FROM testers.profile WHERE name like '$letter%'
ListTesters9=SELECT * FROM testers.profile WHERE name REGEXP '^[^A-Z]'
ListTestersbyID=SELECT * FROM testers.profile WHERE testerid IN ($ids)
ListAddressbyID=SELECT a.*,p.name,p.pause FROM testers.address a \
LEFT JOIN testers.profile p ON p.testerid=a.testerid \
WHERE a.addressid IN ($ids)
ListReports=SELECT c.* FROM testers.ixreport x \
INNER JOIN testers.address a ON a.addressid=x.addressid \
INNER JOIN cpanstats c ON c.id=x.id \
INNER JOIN uploads u ON u.dist=c.dist AND u.version=c.version AND u.author=? \
WHERE a.testerid=? $prev $next \
ORDER BY x.fulldate $order LIMIT 1000
GetTesterByID=SELECT * FROM testers.profile WHERE testerid=?
CountReports=SELECT COUNT(*) AS count FROM ixaddress x \
INNER JOIN testers.address a ON a.addressid=x.addressid \
WHERE a.testerid=? $prev $next
# Admin
FindAddresses=SELECT * FROM testers.address WHERE email=?
FindTesters=SELECT distinct p.testerid,p.name,p.pause FROM testers.profile p \
LEFT JOIN testers.address a ON a.testerid=p.testerid \
WHERE p.name like ? OR p.pause LIKE ? OR a.email LIKE ? \
ORDER BY p.name,p.pause
FindAddress=SELECT distinct a.addressid,a.address,a.email,p.name,p.pause \
FROM testers.address a \
LEFT JOIN testers.profile p ON a.testerid=p.testerid \
WHERE a.address LIKE ? \
ORDER BY p.name,p.pause,a.address
FindAddressUnassigned=SELECT a.* FROM testers.address a \
LEFT JOIN testers.profile p ON a.testerid=p.testerid \
WHERE p.testerid IS NULL
AssignTesters=UPDATE testers.address SET testerid=? WHERE addressid IN ($ids)
MergeTesters=UPDATE testers.address SET testerid=? WHERE testerid IN ($ids)
DeleteProfile=DELETE FROM testers.profile WHERE testerid IN ($ids)
UpdateProfile=UPDATE testers.profile SET name=?, pause=? WHERE testerid=?
CreateProfile=INSERT INTO testers.profile SET name=?, pause=?