[SQL]
# 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,sectionid,quickname,snippet,imageid,front,latest,publish,createdate) \
VALUES (?,?,?,?,?,'',0,0,0,?,?)
SaveArticle=UPDATE 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 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=?
#Diary
WorstOffenders=SELECT ipaddr,count(*) AS count FROM comments WHERE publish < 3 GROUP BY ipaddr ORDER BY count DESC LIMIT 10;
GetAdminCommentIDs=SELECT commentid FROM comments WHERE publish=2 ORDER BY createdate DESC
GetAdminComments=SELECT c.*,a.title FROM comments AS c \
INNER JOIN articles AS a ON a.articleid=c.entryid \
WHERE c.publish=2 ORDER BY c.createdate DESC
GetAdminCommentByIP=SELECT * FROM comments AS c \
WHERE c.publish=2 AND ipaddr=?
GetDiaryComments=SELECT * FROM comments WHERE entryid=? AND publish=3 ORDER BY createdate
CountDiaryComments=SELECT entryid,count(commentid) FROM comments WHERE entryid IN ($ids) AND publish=3 GROUP BY entryid
GetCommentByID=SELECT * FROM comments WHERE commentid=?
AddComment=INSERT INTO comments (entryid,subject,createdate,body,author,href,publish,ipaddr) VALUES (?,?,?,?,?,?,?,?)
SaveComment=UPDATE comments SET subject=?,body=?,author=?,href=? WHERE commentid=?
PromoteComment=UPDATE comments SET publish=? WHERE commentid=?
DeleteComment=DELETE FROM comments WHERE commentid=?
GetCommentsLatest=SELECT * FROM comments AS c \
INNER JOIN articles AS a ON a.articleid=c.entryid \
WHERE c.publish=3 ORDER BY c.createdate DESC LIMIT 5
GetDiaryDates=SELECT createdate FROM articles WHERE sectionid=6 AND publish > 2 ORDER BY createdate DESC
GetVolumes=SELECT * FROM volumes WHERE sectionid=? ORDER BY volumeid DESC
GetVolume=SELECT * FROM volumes WHERE volumeid=? AND sectionid=?
InsertVolume=INSERT INTO volumes (volumeid,sectionid,archdate,counter) VALUES (?,?,?,?)
UpdateVolume=UPDATE volumes SET counter=? WHERE volumeid=? AND sectionid=?
#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=?
# Hit Entries
PageHits=SELECT h.* \
FROM hits AS h \
WHERE h.pageid=0 AND h.photoid=0 \
ORDER BY h.counter DESC,h.pageid LIMIT 10
PageHits2=SELECT h.*,pg.title,pg.year,pg.month,pg.summary \
FROM hits AS h \
LEFT JOIN pages AS pg ON pg.pageid=h.pageid \
WHERE h.pageid > 0 AND h.photoid=0 \
ORDER BY h.counter DESC,h.pageid LIMIT 10
# Metadata Entries
MetaSearchPage=SELECT x.pageid AS id,p.* FROM mxpages AS x INNER JOIN pages AS p ON p.pageid=x.pageid WHERE metadata IN ($meta)
MetaDetailPage=SELECT pageid AS id,* FROM pages WHERE title LIKE '%$meta%' OR summary LIKE '%$meta%'
MetaDeletePage=DELETE FROM mxpages WHERE pageid=?
MetaUpdatePage=INSERT INTO mxpages (pageid,metadata) VALUES (?,?)
MetaSearchPagePhoto=SELECT p.photoid AS id,p.* FROM mxpages AS x \
INNER JOIN photos AS p ON p.pageid=x.pageid \
INNER JOIN pages AS g ON g.pageid=x.pageid \
WHERE metadata IN ($meta)
MetaDetailPagePhoto=SELECT p.photoid AS id,p.tagline,p.thumb,g.pageid,g.title FROM photos AS p \
INNER JOIN pages AS g ON g.pageid=p.pageid \
WHERE tagline LIKE '%$meta%'
MetaSearchPhoto=SELECT x.photoid AS id,p.*,g.* FROM mxphotos AS x \
INNER JOIN photos AS p ON p.photoid=x.photoid \
INNER JOIN pages AS g ON g.pageid=p.pageid \
WHERE metadata IN ($meta)
MetaDetailPhoto=SELECT p.photoid AS id,p.tagline,p.thumb,g.pageid,g.title FROM photos AS p \
INNER JOIN pages AS g ON g.pageid=p.pageid \
WHERE tagline LIKE '%$meta%'
MetaDeletePhoto=DELETE FROM mxphotos WHERE photoid=?
MetaUpdatePhoto=INSERT INTO mxphotos (photoid,metadata) VALUES (?,?)
MetaGetPhoto=SELECT * FROM mxphotos WHERE photoid=?
#Core
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,?,?,?)
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,langcode,optionid FROM sessions WHERE labyrinth=?
SaveSession=UPDATE sessions SET timeout=?,name=?,userid=?,realm=?,optionid=? 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=?
AllRealms=SELECT * FROM realms
GetRealmByID=SELECT * FROM realms WHERE realmid=?
GetRealmByName=SELECT * FROM realms WHERE realm=?
#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
UserACLCheck1=SELECT * FROM acls WHERE userid=? AND folderid=?
UserACLUpdate1=UPDATE acls SET accessid=? WHERE userid=? AND folderid=?
UserACLCheck2=SELECT * FROM acls WHERE userid=? AND aclid=?
UserACLUpdate2=UPDATE acls SET accessid=? WHERE userid=? AND aclid=?
UserACLInsert=INSERT INTO acls (accessid,userid,folderid) VALUES (?,?,?)
UserACLDelete=DELETE FROM acls WHERE userid=? AND accessid=? AND folderid=?
UserACLDefault=UPDATE users SET accessid=? WHERE userid=?
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)
LinkedUsers=SELECT i.*,g.groupname FROM ixusergroup AS i \
INNER JOIN groups AS g ON i.groupid=g.groupid \
WHERE i.type=1 AND i.linkid=?
#Users
GetUserByID=SELECT u.*,i.link,i.tag,a.accessname \
FROM users AS u \
LEFT JOIN images AS i ON u.imageid=i.imageid \
LEFT JOIN access AS a ON a.accessid=u.accessid \
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 (nickname LIKE ? OR realname LIKE ?) $where \
ORDER BY $order
AllUsers=SELECT * FROM users
NewUser=INSERT INTO users (password,accessid,search,realm,nickname,realname,email,imageid) VALUES (SHA1(?),?,?,?,?,?,?,?)
SaveUser=UPDATE users SET nickname=?,realname=?,email=?,imageid=? WHERE userid=?
AdminSaveUser=UPDATE users SET accessid=?,search=?,realm=?,nickname=?,realname=?,email=?,imageid=? WHERE userid=?
SaveUserInfo=UPDATE users SET aboutme=?,url=? WHERE userid=?
DeleteUsers=DELETE FROM users WHERE userid IN ($ids) AND accessid < 5
ChangePassword=UPDATE users SET password=SHA1(?) WHERE userid=?
BanUsers=UPDATE users SET password=? WHERE userid IN ($ids) AND accessid < 5
FindUser=SELECT userid,password,realname FROM users WHERE email = ?
#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