Semantic MediaWiki/data/smw object ids
< Semantic MediaWiki | data
Jump to navigation
Jump to search
About
- Purpose: registry of all SMW object IDs; apparently replaces smw_ids in schema version 2 but adds
smw_proptable_hash
field. Asmw_iw
field was added sometime between SMW versions v1.9 and 2.5.5.
SQL
CREATE TABLE `smw_object_ids` (
`smw_id` int(8) unsigned NOT NULL AUTO_INCREMENT,
`smw_namespace` int(11) NOT NULL,
`smw_title` varbinary(255) NOT NULL,
`smw_iw` varbinary(32) NOT NULL,
`smw_subobject` varbinary(255) NOT NULL,
`smw_sortkey` varbinary(255) NOT NULL,
`smw_proptable_hash` mediumblob,
PRIMARY KEY (`smw_id`),
KEY `smw_id` (`smw_id`,`smw_sortkey`),
KEY `smw_title` (`smw_title`,`smw_namespace`,`smw_iw`,`smw_subobject`),
KEY `smw_sortkey` (`smw_sortkey`),
KEY `smw_iw` (`smw_iw`)
) ENGINE=InnoDB CHARSET=binary;
Example: simple
Showing the blob values:
SELECT
smw_id,
smw_namespace,
CAST(smw_title AS CHAR) AS title,
CAST(smw_iw AS CHAR) AS iw,
CAST(smw_subobject AS CHAR) AS sub,
CAST(smw_sortkey AS CHAR) AS sortkey,
CAST(smw_proptable_hash AS CHAR) AS pthash
FROM
smw_object_ids;
Examples: title lookups
Looking up titles for each property tuple:
SELECT
u.s_id,
CAST(s.smw_title AS CHAR) AS s_title,
u.p_id,
CAST(p.smw_title AS CHAR) AS p_title,
u.fType,
CAST(u.val AS CHAR) as uval
FROM
((SELECT
s_id, p_id, 'blob' AS fType, CAST(o_hash AS CHAR) AS val FROM smw_di_blob
UNION SELECT
s_id, p_id, 'time' AS fType, CAST(o_serialized AS CHAR) as val FROM smw_di_time
UNION SELECT
s_id, p_id, 'wikiPage' AS fType, o_id AS val FROM smw_di_wikipage
UNION SELECT
s_id, p_id, 'bool' AS fType, o_value AS val FROM smw_di_bool
UNION SELECT
s_id, p_id, 'coords' AS fType, o_serialized AS val FROM smw_di_coords
UNION SELECT
s_id, p_id, 'number' AS fType, o_serialized AS val FROM smw_di_number
UNION SELECT
s_id, p_id, 'uri' AS fType, CAST(o_blob AS CHAR) AS val FROM smw_di_uri
) AS u
LEFT JOIN smw_object_ids AS s ON u.s_id = s.smw_id)
LEFT JOIN smw_object_ids AS p ON u.p_id = p.smw_id
ORDER BY p_title
Determining the roles each SMW ID plays within a given wiki (note: does not yet include wikiPage values):
SELECT ID, sRoles, sTypes, CAST(s.smw_title AS CHAR) AS sName FROM (
SELECT ID, GROUP_CONCAT(DISTINCT sRole) AS sRoles, GROUP_CONCAT(DISTINCT fType) AS sTypes
FROM (SELECT
p_id AS ID, 'has' AS sRole, 'blob' AS fType, o_hash AS val FROM smw_di_blob
UNION SELECT
p_id AS ID, 'has' AS sRole, 'time' AS fType, o_serialized as val FROM smw_di_time
UNION SELECT
p_id AS ID, 'has' AS sRole, 'wikiPage' AS fType, o_id AS val FROM smw_di_wikipage
UNION SELECT
p_id AS ID, 'has' AS sRole, 'bool' AS fType, o_value AS val FROM smw_di_bool
UNION SELECT
p_id AS ID, 'has' AS sRole, 'coords' AS fType, o_serialized AS val FROM smw_di_coords
UNION SELECT
p_id AS ID, 'has' AS sRole, 'number' AS fType, o_serialized AS val FROM smw_di_number
UNION SELECT
p_id AS ID, 'has' AS sRole, 'uri' AS fType, o_blob AS val FROM smw_di_uri
UNION SELECT
s_id AS ID, 'for' AS sRole, 'blob' AS fType, o_hash AS val FROM smw_di_blob
UNION SELECT
s_id AS ID, 'for' AS sRole, 'time' AS fType, o_serialized as val FROM smw_di_time
UNION SELECT
s_id AS ID, 'for' AS sRole, 'wikiPage' AS fType, o_id AS val FROM smw_di_wikipage
UNION SELECT
s_id AS ID, 'for' AS sRole, 'bool' AS fType, o_value AS val FROM smw_di_bool
UNION SELECT
s_id AS ID, 'for' AS sRole, 'coords' AS fType, o_serialized AS val FROM smw_di_coords
UNION SELECT
s_id AS ID, 'for' AS sRole, 'number' AS fType, o_serialized AS val FROM smw_di_number
UNION SELECT
s_id AS ID, 'for' AS sRole, 'uri' AS fType, o_blob AS val FROM smw_di_uri
UNION SELECT
o_id AS ID, 'val' AS sRole, 'wikiPage' AS fType, o_id AS val FROM smw_di_wikipage
) AS u
GROUP BY ID
) AS g
LEFT JOIN smw_object_ids AS s ON g.ID = s.smw_id
Key:
- has = this is the name of a page that uses one or more properties (in its text)
- for = this is the name for a property's page (which should be the same as the property's name)
- val = this is the name of a page that is also a property's value in one or more places
Same information, but with each occurrence listed out instead of grouped by ID:
SELECT ID, sRole, fType, CAST(s.smw_title AS CHAR) AS sName
FROM (SELECT
p_id AS ID, 'has' AS sRole, 'blob' AS fType, o_hash AS val FROM smw_di_blob
UNION SELECT
p_id AS ID, 'has' AS sRole, 'time' AS fType, o_serialized as val FROM smw_di_time
UNION SELECT
p_id AS ID, 'has' AS sRole, 'wikiPage' AS fType, o_id AS val FROM smw_di_wikipage
UNION SELECT
p_id AS ID, 'has' AS sRole, 'bool' AS fType, o_value AS val FROM smw_di_bool
UNION SELECT
p_id AS ID, 'has' AS sRole, 'coords' AS fType, o_serialized AS val FROM smw_di_coords
UNION SELECT
p_id AS ID, 'has' AS sRole, 'number' AS fType, o_serialized AS val FROM smw_di_number
UNION SELECT
p_id AS ID, 'has' AS sRole, 'uri' AS fType, o_blob AS val FROM smw_di_uri
UNION SELECT
s_id AS ID, 'for' AS sRole, 'blob' AS fType, o_hash AS val FROM smw_di_blob
UNION SELECT
s_id AS ID, 'for' AS sRole, 'time' AS fType, o_serialized as val FROM smw_di_time
UNION SELECT
s_id AS ID, 'for' AS sRole, 'wikiPage' AS fType, o_id AS val FROM smw_di_wikipage
UNION SELECT
s_id AS ID, 'for' AS sRole, 'bool' AS fType, o_value AS val FROM smw_di_bool
UNION SELECT
s_id AS ID, 'for' AS sRole, 'coords' AS fType, o_serialized AS val FROM smw_di_coords
UNION SELECT
s_id AS ID, 'for' AS sRole, 'number' AS fType, o_serialized AS val FROM smw_di_number
UNION SELECT
s_id AS ID, 'for' AS sRole, 'uri' AS fType, o_blob AS val FROM smw_di_uri
UNION SELECT
o_id AS ID, 'val' AS sRole, 'valPage' AS fType, o_id AS val FROM smw_di_wikipage
) AS u
LEFT JOIN smw_object_ids AS s ON u.ID = s.smw_id