Difference between revisions of "Semantic MediaWiki/data/smw object ids"

from HTYP, the free directory anyone can edit if they can prove to me that they're not a spambot
Jump to navigation Jump to search
(fixed syntax tag; better example)
 
(4 intermediate revisions by the same user not shown)
Line 1: Line 1:
 
==About==
 
==About==
−
* '''Purpose''': registry of all SMW object IDs; apparently replaces {{l/same|smw_ids}} in schema version 2 but adds <code>smw_proptable_hash</code> field.
+
* '''Purpose''': registry of all SMW object IDs; apparently replaces {{l/same|smw_ids}} in schema version 2 but adds <code>smw_proptable_hash</code> field. A <code>smw_iw</code> field was added sometime between SMW versions v1.9 and 2.5.5.
 
==SQL==
 
==SQL==
 
<syntaxhighlight lang=mysql>
 
<syntaxhighlight lang=mysql>
Line 14: Line 14:
 
   KEY `smw_id` (`smw_id`,`smw_sortkey`),
 
   KEY `smw_id` (`smw_id`,`smw_sortkey`),
 
   KEY `smw_title` (`smw_title`,`smw_namespace`,`smw_iw`,`smw_subobject`),
 
   KEY `smw_title` (`smw_title`,`smw_namespace`,`smw_iw`,`smw_subobject`),
−
   KEY `smw_sortkey` (`smw_sortkey`)
+
   KEY `smw_sortkey` (`smw_sortkey`),
−
) ENGINE=InnoDB AUTO_INCREMENT=4599 DEFAULT CHARSET=binary$$
+
  KEY `smw_iw` (`smw_iw`)
 +
) ENGINE=InnoDB CHARSET=binary;
 
</syntaxhighlight>
 
</syntaxhighlight>
−
===Example===
+
===Example: simple===
−
<syntaxhighlight lang=mysql>SELECT  
+
Showing the blob values:
 +
<source lang=mysql>SELECT  
 
     smw_id,
 
     smw_id,
 
     smw_namespace,
 
     smw_namespace,
Line 27: Line 29:
 
     CAST(smw_proptable_hash AS CHAR) AS pthash
 
     CAST(smw_proptable_hash AS CHAR) AS pthash
 
FROM
 
FROM
−
     smw_object_ids;</syntaxhighlight>
+
     smw_object_ids;</source>
 +
===Examples: title lookups===
 +
Looking up titles for each property tuple:
 +
<source lang=mysql>
 +
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</source>
 +
Determining the roles each SMW ID plays within a given wiki (note: does not yet include wikiPage values):
 +
<source lang=mysql>
 +
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
 +
</source>
 +
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:
 +
<source lang=mysql>
 +
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
 +
</source>

Latest revision as of 01:08, 11 July 2020

About

  • Purpose: registry of all SMW object IDs; apparently replaces smw_ids in schema version 2 but adds smw_proptable_hash field. A smw_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
Retrieved from "https://htyp.org/mw/index.php?title=Semantic_MediaWiki/data/smw_object_ids&oldid=26420"