Semantic MediaWiki/data/smw object ids - Revision history

Woozle at 01:08, 11 July 2020

2020-07-11T01:08:17Z

@@ Line 18: / Line 18: @@
 ) ENGINE=InnoDB CHARSET=binary;
 </syntaxhighlight>
-===Examples===
+===Example: simple===
 Showing the blob values:
 <source lang=mysql>SELECT
@@ Line 30: / Line 30: @@
 FROM
      smw_object_ids;</source>
 Looking up titles for each property tuple:
 <source lang=mysql>
@@ Line 103: / Line 104: @@
 * '''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

Woozle: /* SQL */ more useful field in blob table

2020-07-10T13:48:24Z

SQL: more useful field in blob table

← Older revision		Revision as of 13:48, 10 July 2020
Line 41:		Line 41:
	FROM		FROM
	((SELECT		((SELECT
	s_id, p_id, 'blob' AS fType, CAST(~~o_blob~~ AS CHAR) AS val FROM smw_di_blob		s_id, p_id, 'blob' AS fType, CAST(o_hash AS CHAR) AS val FROM smw_di_blob
	UNION SELECT		UNION SELECT
	s_id, p_id, 'time' AS fType, CAST(o_serialized AS CHAR) as val FROM smw_di_time		s_id, p_id, 'time' AS fType, CAST(o_serialized AS CHAR) as val FROM smw_di_time
Line 63:		Line 63:
	SELECT ID, GROUP_CONCAT(DISTINCT sRole) AS sRoles, GROUP_CONCAT(DISTINCT fType) AS sTypes		SELECT ID, GROUP_CONCAT(DISTINCT sRole) AS sRoles, GROUP_CONCAT(DISTINCT fType) AS sTypes
	FROM (SELECT		FROM (SELECT
	p_id AS ID, 'has' AS sRole, 'blob' AS fType, ~~o_blob~~ AS val FROM smw_di_blob		p_id AS ID, 'has' AS sRole, 'blob' AS fType, o_hash AS val FROM smw_di_blob
	UNION SELECT		UNION SELECT
	p_id AS ID, 'has' AS sRole, 'time' AS fType, o_serialized as val FROM smw_di_time		p_id AS ID, 'has' AS sRole, 'time' AS fType, o_serialized as val FROM smw_di_time
Line 78:		Line 78:

	UNION SELECT		UNION SELECT
	s_id AS ID, 'for' AS sRole, 'blob' AS fType, ~~o_blob~~ AS val FROM smw_di_blob		s_id AS ID, 'for' AS sRole, 'blob' AS fType, o_hash AS val FROM smw_di_blob
	UNION SELECT		UNION SELECT
	s_id AS ID, 'for' AS sRole, 'time' AS fType, o_serialized as val FROM smw_di_time		s_id AS ID, 'for' AS sRole, 'time' AS fType, o_serialized as val FROM smw_di_time

Woozle at 13:36, 10 July 2020

2020-07-10T13:36:05Z

← Older revision		Revision as of 13:36, 10 July 2020
Line 58:		Line 58:
	LEFT JOIN smw_object_ids AS p ON u.p_id = p.smw_id		LEFT JOIN smw_object_ids AS p ON u.p_id = p.smw_id
	ORDER BY p_title</source>		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_blob 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_blob 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

Woozle: title lookup SQL

2020-07-10T12:19:37Z

title lookup SQL

@@ Line 18: / Line 18: @@
 ) ENGINE=InnoDB CHARSET=binary;
 </syntaxhighlight>
-===Example===
+===Examples===
-<syntaxhighlight lang=mysql>SELECT
+Showing the blob values:
      smw_id,
      smw_namespace,
@@ Line 28: / Line 29: @@
      CAST(smw_proptable_hash AS CHAR) AS pthash
 FROM
-     smw_object_ids;</syntaxhighlight>
+     smw_object_ids;</source>

Woozle: updated highlight tags; latest schema

2018-01-26T13:03:52Z

updated highlight tags; latest schema

← Older revision		Revision as of 13:03, 26 January 2018
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===

Woozle: fixed syntax tag; better example

2018-01-26T12:59:12Z

fixed syntax tag; better example

← Older revision		Revision as of 12:59, 26 January 2018
Line 1:		Line 1:
	==About==		==About==
	* '''Purpose''': registry of all SMW object IDs; ~~similar to~~ {{l/same\|smw_ids}} in schema version 2		* '''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.
	==SQL==		==SQL==
	<mysql>		<syntaxhighlight lang=mysql>
	~~delimiter $$~~

	CREATE TABLE `smw_object_ids` (		CREATE TABLE `smw_object_ids` (
	`smw_id` int(8) unsigned NOT NULL AUTO_INCREMENT,		`smw_id` int(8) unsigned NOT NULL AUTO_INCREMENT,
Line 18:		Line 16:
	KEY `smw_sortkey` (`smw_sortkey`)		KEY `smw_sortkey` (`smw_sortkey`)
	) ENGINE=InnoDB AUTO_INCREMENT=4599 DEFAULT CHARSET=binary$$		) ENGINE=InnoDB AUTO_INCREMENT=4599 DEFAULT CHARSET=binary$$
	</~~mysql~~>		</syntaxhighlight>
	===Example===		===Example===
	<mysql>SELECT smw_id, smw_namespace, CAST(smw_title AS CHAR) as title FROM smw_object_ids;</~~mysql~~>		<syntaxhighlight lang=mysql>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;</syntaxhighlight>

Woozle: Created page with "==About== * '''Purpose''': registry of all SMW object IDs; similar to {{l/same|smw_ids}} in schema version 2 ==SQL== delimiter $$ CREATE TABLE `smw_object_ids` ( `s..."

2013-02-14T15:15:40Z

Created page with "==About== * '''Purpose''': registry of all SMW object IDs; similar to {{l/same|smw_ids}} in schema version 2 ==SQL== <mysql> delimiter $$ CREATE TABLE `smw_object_ids` ( `s..."

New page

==About==
* '''Purpose''': registry of all SMW object IDs; similar to {{l/same|smw_ids}} in schema version 2
==SQL==
<mysql>
delimiter $$

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`)
) ENGINE=InnoDB AUTO_INCREMENT=4599 DEFAULT CHARSET=binary$$
</mysql>
===Example===
<mysql>SELECT smw_id, smw_namespace, CAST(smw_title AS CHAR) as title FROM smw_object_ids;</mysql>