|
|
Rank: Addict
Joined: 7/19/2006
Posts: 815
Location: Århus, Denmark
|
Does anyone have a sql script or dashboard control that can list all unpublished pages / nodes in a solution? I need it to extract a list for a site before we publish the entire content for a URL update. Thanks :-)
|
|
Rank: Addict
Joined: 7/19/2006
Posts: 815
Location: Århus, Denmark
|
I think I got it using SQL: EDIT: DOES NOT WORKCode:
select [cmsContent].nodeId from [cmsContent]
left join (select * from [cmsDocument] where published = 1)
as [pubdocs] on [cmsContent].[nodeId] = [pubdocs].[nodeId]
where [pubdocs].[published] IS NULL
order by [pubdocs].[published]
This will return a list of nodeId's that have no published versions.
|
|
Rank: Addict
Joined: 7/19/2006
Posts: 815
Location: Århus, Denmark
|
The above does not work! It includes all sorts of content such as members and so on. Instead I created this sql statment that seems to get it right. It finds nodes that do not have a published version and that are not in the recycle bin. It also writes out the path so that you can find the node in the site tree for manual processing: Code:
select * from
(select distinct [cmsDocument].[nodeId], [cmsDocument].[published], [cmsDocument].[versionId], [cmsDocument].[text], [cmsDocument].[updateDate] from [cmsDocument] where [cmsDocument].[published] = 0 and [cmsDocument].[newest] = 1)
as [unpub]
left join
(select distinct [cmsDocument].[nodeId], [cmsDocument].[published] from [cmsDocument] where [cmsDocument].[published] = 1)
as [pub]
on [unpub].[nodeId] = [pub].[nodeId]
left join
(select [umbracoNode].[path], [umbracoNode].[id] from [umbracoNode] where [umbracoNode].[path] LIKE '-1,-20,%')
as [trash]
on [trash].[id] = [unpub].[nodeId]
left join
(select [umbracoNode].[path], [umbracoNode].[id] from [umbracoNode])
as [find]
on [find].[id] = [unpub].[nodeId]
where [pub].[published] IS NULL
and [trash].[path] IS NULL
order by [find].[path]
|
|
Rank: Aficionado
Joined: 6/5/2008
Posts: 148
Location: United Kingdom
|
Tried it on an Umbraco3 database, and 4 Umbraco v4 databases, and this query seems to work for all of those. All the best Adz
Adam Perry ( blog, twitter), developing Umbraco based websites and applications for ConnectDigital.
|
|
Rank: Addict
Joined: 7/19/2006
Posts: 815
Location: Århus, Denmark
|
Great. Thanks for testing it out :-)
|
|
Rank: Addict
Joined: 7/19/2006
Posts: 610
Location: Bad Homburg, Germany
|
Hi Morten, Take a look into the umbracoNode table. All nodes whatever type of are stored in the umbracoNode. Via GUID you can see which nodes are documents or not So with Code:SELECT *
FROM umbracoNode
WHERE nodeObjectType = 'c66ba18e-eaf3-4cff-8a22-41b16d66a972' -- Documents
AND path not like '%-20%'
you get all content which is not in the receycle bin. Here a snapshot from a mailing with Jesper Ordrup: Quote:Just an explenation of the datatables and the values stored in it.
The main table where all informations about all nodes (from which type ever) are stored is the 'umbracoNode' table. Via the column 'nodeObjectType' it is defined from which type the node is. For example: media nodes have the guid 'b796f64c-1f99-4ffb-b886-4bf4bc011a9c'. Below you can see a list of all types which I have in our intranet database. The guids are in the sourcecode from umbraco in the namespace umbraco.cms.businesslogic.*.
'c66ba18e-eaf3-4cff-8a22-41b16d66a972' -- Documents
'b796f64c-1f99-4ffb-b886-4bf4bc011a9c' -- Media
'30a2a501-1978-4ddb-a57b-f7efed43ba3c' -- Data Type Definition
'9F68DA4F-A3A8-44C2-8226-DCBD125E4840' -- Stylsheet
'A2CB7800-F571-4787-9638-BC48539A0EFB' -- Document Type
'6FBDE604-4178-42CE-A10B-8A2600A2F07D' -- Template
'5555DA4F-A123-42B2-4488-DCDFB25E4111' -- Stylesheet Property
'4EA4382B-2F5A-4C2B-9587-AE9B3CF3602E' -- Media Type
'EA7D8624-4CFE-4578-A871-24AA946BF34D' -- Umbraco Root
'01BB7FF2-24DC-4C0C-95A2-C24EF72BBAC8' -- Receycle Bin
For our content we have several tables which are necessary for us:
- cmsContent
- cmsDocument
- cmsContentVersion
- cmsContentXml
- cmsPropertyData
On detail:
- cmsContent: In this table are stored the informations which content nodes are available and on which content type they are based. But it is confusing that not only the content nodes are stored in this table also the media nodes are in here. But per nodeid there is only one row in here
- cmsDocument: In this table all content which is really our content shown in the content area of umbrac is stored here, For each nodeid it can hold several rows related to the cmsContentVersion. Therefore that for medias only one version is stored and no publishing or unpublishing is needed the medias don't need to be here I guess.
- cmsContentVersion: here are stored all versions for each content and media node. For media nodes there is only one row in here. For the content nodes there can be several rows, for published content nodes there have to be at least 2 rows in here: the published version and the newer one on which the umbraco backend workes for changes etc.
- cmsContentXml: here are stored all published nodes (alle published content and all media nodes) with id and the xml representation. For each node there is one row. unpublished content is not in here.
- cmsPropertyData: Same as for the cmsContent, but related to the generic properties of the content.
Conclusion: to get the content you have to take the cmsDocument table (weird)
So I hope that this will help you to reflect your database problems.
Soon I will set up my own blog where I will write some of these hints and I got new ideas for the client tools... hth, Thomas
• 2007/2008 MVP • www.thoehler.com • Bad Homburg, Germany
|
|
Rank: Addict
Joined: 7/19/2006
Posts: 815
Location: Århus, Denmark
|
Thank you Thomas I didn't know that. That way I could probably save a Join or two in my query.
|
|
|
Guest |