Difference between revisions of "Semantic MediaWiki/data/smw di wikipage"
< Semantic MediaWiki | data
Jump to navigation
Jump to search
(Created page with "==About== * '''Purpose''': specifications for all SMW Objects that point to wiki pages (existing or not) * '''Refers to''': {{l/same|smw_object_ids}} ==SQL== <mysql> delimiter...") |
(→relationships: improved query) |
||
| (5 intermediate revisions by the same user not shown) | |||
| Line 1: | Line 1: | ||
==About== | ==About== | ||
| − | * '''Purpose''': specifications for all SMW Objects | + | * '''Purpose''': specifications for all SMW Objects where the value is a possible wiki page title (existing or not) |
* '''Refers to''': {{l/same|smw_object_ids}} | * '''Refers to''': {{l/same|smw_object_ids}} | ||
| + | ==Fields== | ||
| + | * '''p_id''': page on which the property appears | ||
| + | * '''s_id''': page for property name | ||
| + | * '''o_od''': page for property value | ||
==SQL== | ==SQL== | ||
| − | <mysql> | + | <syntaxhighlight lang=mysql> |
| − | |||
| − | |||
CREATE TABLE `smw_di_wikipage` ( | CREATE TABLE `smw_di_wikipage` ( | ||
`s_id` int(8) unsigned NOT NULL, | `s_id` int(8) unsigned NOT NULL, | ||
| Line 13: | Line 15: | ||
KEY `p_id` (`p_id`,`o_id`), | KEY `p_id` (`p_id`,`o_id`), | ||
KEY `o_id` (`o_id`) | KEY `o_id` (`o_id`) | ||
| − | ) ENGINE=InnoDB DEFAULT CHARSET=binary | + | ) ENGINE=InnoDB DEFAULT CHARSET=binary |
| − | </ | + | </syntaxhighlight> |
===Example=== | ===Example=== | ||
| − | <mysql> | + | ====values==== |
| − | SELECT s_id | + | <syntaxhighlight lang=mysql> |
| − | , s.smw_namespace AS | + | SELECT |
| + | s_id, | ||
| + | p_id, | ||
| + | o_id, | ||
| + | o.smw_namespace AS o_namespace, | ||
| + | CAST(o.smw_title AS CHAR) AS o_title | ||
| + | FROM | ||
| + | smw_di_wikipage AS r | ||
| + | LEFT JOIN | ||
| + | smw_object_ids AS o ON r.o_id = o.smw_id; | ||
| + | </syntaxhighlight> | ||
| + | ====relationships==== | ||
| + | <source lang=mysql> | ||
| + | SELECT | ||
| + | s_id | ||
| + | , s.smw_namespace AS s_ns | ||
, CAST(s.smw_title AS char) AS s_title | , CAST(s.smw_title AS char) AS s_title | ||
| − | , p.smw_namespace AS | + | , p_id |
| + | , p.smw_namespace AS p_ns | ||
, CAST(p.smw_title AS char) AS p_title | , CAST(p.smw_title AS char) AS p_title | ||
| − | , o.smw_namespace AS | + | , o_id |
| + | , o.smw_namespace AS o_ns | ||
, CAST(o.smw_title AS char) AS o_title | , CAST(o.smw_title AS char) AS o_title | ||
FROM | FROM | ||
| Line 29: | Line 48: | ||
LEFT JOIN smw_object_ids AS p ON r.p_id=p.smw_id) | LEFT JOIN smw_object_ids AS p ON r.p_id=p.smw_id) | ||
LEFT JOIN smw_object_ids AS o ON r.o_id=o.smw_id | LEFT JOIN smw_object_ids AS o ON r.o_id=o.smw_id | ||
| − | ORDER BY s_id,p_title, o_title | + | ORDER BY s.smw_title, s_id, p_title, o_title</source> |
| − | </ | ||
* p_namespace will probably always be the "property:" namespace, which defaults to 102 (unless there's some way to use a page outside that namespace as a property) | * p_namespace will probably always be the "property:" namespace, which defaults to 102 (unless there's some way to use a page outside that namespace as a property) | ||
Latest revision as of 21:11, 9 July 2020
About
- Purpose: specifications for all SMW Objects where the value is a possible wiki page title (existing or not)
- Refers to: smw_object_ids
Fields
- p_id: page on which the property appears
- s_id: page for property name
- o_od: page for property value
SQL
CREATE TABLE `smw_di_wikipage` (
`s_id` int(8) unsigned NOT NULL,
`p_id` int(8) unsigned NOT NULL,
`o_id` int(8) unsigned DEFAULT NULL,
KEY `s_id` (`s_id`,`p_id`),
KEY `p_id` (`p_id`,`o_id`),
KEY `o_id` (`o_id`)
) ENGINE=InnoDB DEFAULT CHARSET=binary
Example
values
SELECT
s_id,
p_id,
o_id,
o.smw_namespace AS o_namespace,
CAST(o.smw_title AS CHAR) AS o_title
FROM
smw_di_wikipage AS r
LEFT JOIN
smw_object_ids AS o ON r.o_id = o.smw_id;
relationships
SELECT
s_id
, s.smw_namespace AS s_ns
, CAST(s.smw_title AS char) AS s_title
, p_id
, p.smw_namespace AS p_ns
, CAST(p.smw_title AS char) AS p_title
, o_id
, o.smw_namespace AS o_ns
, CAST(o.smw_title AS char) AS o_title
FROM
((smw_di_wikipage AS r
LEFT JOIN smw_object_ids AS s ON r.s_id=s.smw_id)
LEFT JOIN smw_object_ids AS p ON r.p_id=p.smw_id)
LEFT JOIN smw_object_ids AS o ON r.o_id=o.smw_id
ORDER BY s.smw_title, s_id, p_title, o_title
- p_namespace will probably always be the "property:" namespace, which defaults to 102 (unless there's some way to use a page outside that namespace as a property)