Isn't there any way to see how the data is actually stored in the database.
Eg.
* a boolean value or True/False - is it stored as 0/1 or true/false string etc.
* a checkboxes values are they stored as comma delimited string values, prevalue id values etc.
* whether empty linked media file is stored as empty string, 0 value etc.
There are many questions like this, what exactly it is in the database. From what we saw in v7 the actual db stored values can be sometimes surprising and are important to know to use the API correctly.
I would agree with everyone, going via the API is always best. but if for whatever reason you need to see the content data in the database. the SQL below will tell you it
(assuming you know the node Id of the content item you want).
SELECT pt.alias, pd.* FROM umbracoPropertyData as pd
inner join cmsPropertyType as pt
on pd.propertyTypeiD = pt.id
inner join umbracoContentVersion as cv on pd.versionId = cv.id
where cv.NodeId = 1097 and cv.[current] = 1
with all the caveats, might not be the same as what is live, changing things here doesn't update site caches, etc etc…
Do you know if there is a way to refine or filter this query to differentiate two things?
Get the current saved and published content.
Get the most recent saved but not published content.
It looks like every time you save or save and publish the current field is updated to 1, so you can't use it to differentiate if that row is the published row of content in umbracoContentVersion.
Looks like cmsContentNu gets a row for published and a row for non published but there is not a foreign key on the contentVersion id, only nodeId.
Is there someplace to figure this out looking only at the data in the tables?
Found what I am looking for:
SELECT pd.textValue, dv.published, cv.[current]
FROM umbracoPropertyData as pd
INNER JOIN cmsPropertyType as pt
ON pd.propertyTypeiD = pt.id
INNER JOIN umbracoContentVersion as cv on pd.versionId = cv.id
INNER JOIN [dbo].[umbracoDocumentVersion] dv
ON dv.id = cv.id
WHERE pt.Alias = 'bodyText' AND cv.nodeId = 1069 AND (dv.published = 1 OR cv.[current] = 1)
Between the four of us at Candid Contributions, we knew plenty about .NET Framework but not all of us had worked with Umbraco 9 or .NET 5 yet. So, we started by taking a look at our current site and having a think about what the different frameworks might mean for us. Then, we decided what could be migrated without any changes, what couldn’t be migrated and what would need some changes before it ‘worked’. Finally, we made an inventory of integrations, Umbraco packages and anything else of note. That looked like this:
If migrating from v8, you'll be able to use a similar method as was available in that version. You register a component in a composer, create a migration plan and run the plan to add the database table to the database. Learn more about composers in the
How to get content from the database in umbraco 8 or 9?
As per the subject - I would like to look up the data structure and the data stored for each content in Umbraco 8 or 9 database.
I guess it is the cmsContentNu table, I can read the dataRow binary but it just outputs to something like
Do I need to use some kind of complex query using other tables?
Anyone knows?
You need to receive data by using API and not by using dataabse
I still don't know how
You can see it here - https://our.umbraco.com/documentation/Reference/Querying/IPublishedContent/Collections/
That I know and it's not what I am looking for.
Isn't there any way to see how the data is actually stored in the database. Eg. * a boolean value or True/False - is it stored as 0/1 or true/false string etc. * a checkboxes values are they stored as comma delimited string values, prevalue id values etc. * whether empty linked media file is stored as empty string, 0 value etc.
There are many questions like this, what exactly it is in the database. From what we saw in v7 the actual db stored values can be sometimes surprising and are important to know to use the API correctly.
Hi
I would agree with everyone, going via the API is always best. but if for whatever reason you need to see the content data in the database. the SQL below will tell you it
(assuming you know the node Id of the content item you want).
Do you know if there is a way to refine or filter this query to differentiate two things?
It looks like every time you save or save and publish the current field is updated to 1, so you can't use it to differentiate if that row is the published row of content in umbracoContentVersion.
Looks like cmsContentNu gets a row for published and a row for non published but there is not a foreign key on the contentVersion id, only nodeId.
Is there someplace to figure this out looking only at the data in the tables?
Found what I am looking for:
SELECT pd.textValue, dv.published, cv.[current]
FROM umbracoPropertyData as pd
INNER JOIN cmsPropertyType as pt
ON pd.propertyTypeiD = pt.id
INNER JOIN umbracoContentVersion as cv on pd.versionId = cv.id
INNER JOIN [dbo].[umbracoDocumentVersion] dv
ON dv.id = cv.id
WHERE pt.Alias = 'bodyText' AND cv.nodeId = 1069 AND (dv.published = 1 OR cv.[current] = 1)
Between the four of us at Candid Contributions, we knew plenty about .NET Framework but not all of us had worked with Umbraco 9 or .NET 5 yet. So, we started by taking a look at our current site and having a think about what the different frameworks might mean for us. Then, we decided what could be migrated without any changes, what couldn’t be migrated and what would need some changes before it ‘worked’. Finally, we made an inventory of integrations, Umbraco packages and anything else of note. That looked like this:
If migrating from v8, you'll be able to use a similar method as was available in that version. You register a component in a composer, create a migration plan and run the plan to add the database table to the database. Learn more about composers in the
???
My question about a different issue.
is working on a reply...