Immediacy : Useful Database Scripts

Clearing The Audit Log

If you ever find your Immediacy site running very slowly after a period of time, it’s probably because your audit log has become too large and needs clearing out.  This can be done running the following script :

TRUNCATE TABLE AuditTrail
Exec mu_Clearlog;

Check if page is a child page, check for parent ID’s

SELECT TOP 1 * FROM META_Default WHERE MetaID = @METAID AND PageID IN (SELECT * FROM immGetPath(@PAGEID,

Create a completely blank Immediacy database

DELETE Abbreviation
DELETE AccessCheck
DELETE AuditTrail
DELETE content_items
DELETE cust_Forms
DELETE deleted_pages
DELETE Editor_Favourites
DELETE eh_history
DELETE ImageCat
DELETE ImageCategory
DELETE immMailToSend
DELETE [index]
DELETE indexQueue
DELETE License
DELETE Members
DELETE Membership
DELETE Messages
DELETE META_Default
DELETE META_Default_Edited
DELETE META_Extended
DELETE META_Extended_Edited
DELETE META_Description WHERE MetaDataID NOT IN (1,2,5)
IF EXISTS (SELECT * FROM sysobjects WHERE id=OBJECT_ID('META_Delete')) DELETE META_Delete
DELETE ObjectDef
DELETE ObjectDeleted
DELETE Objects
DELETE ObjectTypes
DELETE [Option]
DELETE page_changes
DELETE page_changes_eh
DELETE page_data WHERE p_page_id <> 0
UPDATE page_data SET p_version=1,p_content = '<?xml version="1.0"?><page_data><template>blank.htt</template><template_data></template_data><content><main><![CDATA[<DIV>Welcome to Immediacy</DIV>]]></main></content></page_data>' WHERE p_page_id = 0
DELETE PageLock
DELETE PageMessages
DELETE PageObjects
DELETE PageRoles
DELETE pageurls
DELETE PageXMLArchive
DELETE Session
DELETE SessionData
DELETE Sitetracking
DELETE Submissions
DELETE submitted_changes
DELETE submitted_changes_eh
DELETE subscriptions
UPDATE p_system SET nPageID = 1
UPDATE updatedata SET locked = 0, lastupdatedate = GetDate()
DELETE UploadState
DELETE Upload_Changes
DELETE Userlock
IF EXISTS (SELECT * FROM sysobjects WHERE id=OBJECT_ID('WebKeys'))DELETE WebKeys
IF EXISTS (SELECT * FROM sysobjects WHERE id=OBJECT_ID('WebPerm'))DELETE WebPerm
DELETE Workflow
DELETE WorkflowProgress
DELETE XUGPage
DELETE XUGUser
DELETE XUserGroup
DELETE XUserPage

Get A List Of All URL’s Within Your Websites Content

select p_page_id, p_title from page_data where p_content like '%<a href="%"></a>%'

Get A List Of All Page Templates and Style-sheets Used In Your Site

-- get templates and styles for all pages
select p_page_id as PageID,
substring(p_content,(charindex('<template>',p_content)+10),(charindex('</template>',p_content)-charindex('<template>',p_content)-10)) as Template,
p_stylesheet as Stylesheet
from page_data
where p_content is not null and substring(p_content,1,1) != ''
-- Count each template
SELECT distinct
substring(pd1.p_content,(charindex('<template>',pd1.p_content)+10),(charindex('</template>',pd1.p_content)-charindex('<template>',pd1.p_content)-10)) as Template,
templateCount = (
SELECT Count(*)
FROM page_data pd2
WHERE substring(pd2.p_content,(charindex('<template>',pd2.p_content)+10),(charindex('</template>',pd2.p_content)-charindex('<template>',pd2.p_content)-10)) = substring(pd1.p_content,(charindex('<template>',pd1.p_content)+10),(charindex('</template>',pd1.p_content)-charindex('<template>',pd1.p_content)-10))
)
FROM page_data pd1
WHERE pd1.p_content is not null and substring(pd1.p_content,1,1) != ''
ORDER BY template

Clear Archive

DELETE AccessCheck
DELETE AuditTrail
DELETE deleted_pages
DELETE Editor_Favourites
DELETE eh_history
DELETE immMailToSend
DELETE messages
DELETE page_changes
DELETE page_changes_eh
UPDATE page_data set p_version = '1'
DELETE PageLock
DELETE PageMessages
DELETE PageXMLArchive
DELETE Session
DELETE SessionData
DELETE Submissions
DELETE submitted_changes
DELETE submitted_changes_eh
UPDATE updatedata SET locked = 0, lastupdatedate = GetDate()
DELETE Upload_Changes
DELETE UploadState
DELETE Userlock
DELETE WebKeys
DELETE WebPerm
DELETE WebUpload
DELETE WorkFlowProgress

Get Menu

-- Insert statements for procedure here
SELECT * from dbo.immGetMenuTree(@pageid, @maxlevel) as p, page_data as t where p.pageid = t.p_page_id

Set Orphaned Pages To The Homepage

UPDATE p SET p.p_parent_id = p.p_edit_parent FROM page_data p LEFT JOIN page_data p2 ON p.p_parent_id = p2.p_page_id WHERE p2.p_page_id is null AND p.p_page_id <> 0

Replacing Templates Via The Database

---------------------------------------------------------------
---------------------------------------------------------------
-------              Install Replace sp                --------
---------------------------------------------------------------
---------------------------------------------------------------
create procedure searchreplace(
@otxtStart nvarchar(4000) ,
@otxtEnd nvarchar(4000) ,
@ntxt nvarchar(4000)
)
as
begin tran
print @otxtStart
print @otxtEnd
print @ntxt
declare @txtlen int; set @txtlen = len(@otxtStart)
-- need this stuff here to get round collation problems. there is probably a better way, but this works.
declare @perc char(1); set @perc = '%';
declare @findpattern varchar(1000); set @findpattern = @perc + @otxtStart + @perc + @otxtEnd + @perc;
declare @findstart varchar(1000); set @findstart = @perc + @otxtStart + @perc;
declare @findend varchar(1000); set @findend = @perc + @otxtEnd + @perc;
declare curs cursor
for
select page_data.p_page_id
from page_data
where p_content like @findpattern
open curs
declare @key int
declare @pos int
declare @endpos int
declare @ptr binary(16)
declare @nextpos int
declare @realtxtlen int
fetch next from curs into @key
while @@fetch_status = 0
begin
select @ptr = textptr(page_data.p_content) from page_data where p_page_id = @key
set @nextpos=1
print [email protected]='+cast(@key as varchar(20))
WHILE 1=1
BEGIN
-- Get position of start string
select @pos=patindex(@findstart, p_content) - 1
FROM page_data WHERE p_page_id = @key
print [email protected]='+cast(@pos as varchar(20))
if @pos <= 0 break;
if len(@otxtEnd) > 0
begin
-- end string specified so find it
--print [email protected]='+cast(@nextpos as varchar(20))
--print @otxtEnd
select @endpos=patindex(@findend, p_content) - 1
FROM page_data WHERE p_page_id = @key
print [email protected]='+cast(@endpos as varchar(20))    
set @realtxtlen = @endpos - @pos + len(@otxtEnd)
end
else
begin
-- no end string, so just replace start string
set @realtxtlen = @txtlen
end
if len(@ntxt) > 0
begin
-- if new text exists then replace it
print 'replacing text'
updatetext page_data.p_content @ptr @pos @realtxtlen @ntxt
end
else
begin
-- if no new text, then just remove old text
print 'removing text'
updatetext page_data.p_content @ptr @pos @realtxtlen
end
-- start searching from the end of the last bit of new text
SET @[email protected] + @realtxtlen + 1
END
-- get the next record to process
fetch next from curs into @key
end
close curs
deallocate curs
commit tran
GO
---------------------------------------------------------------
---------------------------------------------------------------
-------               Replace Templates                --------
---------------------------------------------------------------
---------------------------------------------------------------
exec searchreplace 'old1.htt', '', 'new1.htt'
exec searchreplace 'old2.htt', '', 'new2.htt'
exec searchreplace 'old3.htt', '', 'new3.htt'
---------------------------------------------------------------
---------------------------------------------------------------
-------                Remove Replace sp               --------
---------------------------------------------------------------
---------------------------------------------------------------
drop procedure searchreplace

Find all installed Plug-ins

select p_title, substring(p_content, PATINDEX( '%imm:plugin%' , substring(p_content, 1, 4000)), 10) as plugin, substring(p_content, PATINDEX( '%name=%' , substring(p_content, 1, 4000)), 25) as pluginname,
p_page_id
from page_data where p_content like '%imm:plugin%'order by pluginname

Jon D Jones

Software Architect, Programmer and Technologist Jon Jones is founder and CEO of London-based tech firm Digital Prompt. He has been working in the field for nearly a decade, specializing in new technologies and technical solution research in the web business. A passionate blogger by heart , speaker & consultant from England.. always on the hunt for the next challenge

More Posts

0 replies

Leave a Reply

Want to join the discussion?
Feel free to contribute!

Leave a Reply

Your email address will not be published. Required fields are marked *