Automating Wiki Page Creation from a PostgreSQL Table - Revision history

Luca at 07:43, 18 June 2024

2024-06-18T07:43:13Z

← Older revision		Revision as of 07:43, 18 June 2024
Line 1:		Line 1:
	[[Category:mediawiki]]		[[Category:mediawiki]]
			[[Category:postgresql]]
	Managing large datasets can be daunting. A neat solution is to use MediaWiki to transform data from a PostgreSQL table into organized wiki pages. This guide walks you through an automated process to achieve this, saving time and ensuring accurate, user-friendly data representation. Ideal for database administrators and developers, this tutorial bridges the gap between raw data and accessible wiki pages, making information management a breeze.		Managing large datasets can be daunting. A neat solution is to use MediaWiki to transform data from a PostgreSQL table into organized wiki pages. This guide walks you through an automated process to achieve this, saving time and ensuring accurate, user-friendly data representation. Ideal for database administrators and developers, this tutorial bridges the gap between raw data and accessible wiki pages, making information management a breeze.

Luca at 14:57, 7 November 2023

2023-11-07T14:57:33Z

← Older revision		Revision as of 14:57, 7 November 2023
Line 16:		Line 16:
	xmlagg(		xmlagg(
	xmlelement(name page,		xmlelement(name page,
	xmlelement(name title, friendly_name \|\| '_(city)',		xmlelement(name title, friendly_name \|\| '_(city)'),
	xmlelement(name revision,		xmlelement(name revision,
	xmlelement(name text, '{{city_t\|city_name=' \|\| friendly_name \|\| '}}')		xmlelement(name text, '{{city_t\|city_name=' \|\| friendly_name \|\| '}}')

Luca at 15:54, 3 November 2023

2023-11-03T15:54:19Z

← Older revision		Revision as of 15:54, 3 November 2023
Line 1:		Line 1:
	[[Category:mediawiki]]		[[Category:mediawiki]]
	~~This~~ is a ~~rather specific requirement~~.		Managing large datasets can be daunting. A neat solution is to use MediaWiki to transform data from a PostgreSQL table into organized wiki pages. This guide walks you through an automated process to achieve this, saving time and ensuring accurate, user-friendly data representation. Ideal for database administrators and developers, this tutorial bridges the gap between raw data and accessible wiki pages, making information management a breeze.
	~~In my case~~, ~~I found myself having a Postgresql table from which I needed to create a page on an internal mediawiki~~ for ~~each row.~~
	~~For each row~~, ~~I also had to insert a call to~~ a ~~template in the body of the page~~.

	== Initial Data ==		== Initial Data ==

Luca: Luca moved page Create numerous wiki pages from a Postgresql table to Automating Wiki Page Creation from a PostgreSQL Table without leaving a redirect: Titolo migliore

2023-11-03T15:51:27Z

Luca moved page Create numerous wiki pages from a Postgresql table to Automating Wiki Page Creation from a PostgreSQL Table without leaving a redirect: Titolo migliore

← Older revision

Revision as of 15:51, 3 November 2023

Luca: Created page with "Category:mediawiki This is a rather specific requirement. In my case, I found myself having a Postgresql table from which I needed to create a page on an internal mediawiki for each row. For each row, I also had to insert a call to a template in the body of the page. == Initial Data == * In my case, the table is called: public.cities * The only field of interest to me is friendly_name * The mediawiki template in my case is called city_t and has the city name as a pa..."

2023-11-03T15:47:09Z

Created page with "Category:mediawiki This is a rather specific requirement. In my case, I found myself having a Postgresql table from which I needed to create a page on an internal mediawiki for each row. For each row, I also had to insert a call to a template in the body of the page. == Initial Data == * In my case, the table is called: public.cities * The only field of interest to me is friendly_name * The mediawiki template in my case is called city_t and has the city name as a pa..."

New page

[[Category:mediawiki]]
This is a rather specific requirement.
In my case, I found myself having a Postgresql table from which I needed to create a page on an internal mediawiki for each row.
For each row, I also had to insert a call to a template in the body of the page.

== Initial Data ==
* In my case, the table is called: public.cities
* The only field of interest to me is friendly_name
* The mediawiki template in my case is called city_t and has the city name as a parameter. The parameter is called city_name.

== Requirements ==
* The name of the page should not just be the city name, but should be composed similarly to: cityname_(city).
* The parameter passed to the template should instead be just the city name.
== Execution ==
Here is the query executed on PostgreSQL:
<syntaxhighlight lang=postgresql>
SELECT xmlelement(name mediawiki,
xmlagg(
xmlelement(name page,
xmlelement(name title, friendly_name || '_(city)',
xmlelement(name revision,
xmlelement(name text, '{{city_t|city_name=' || friendly_name || '}}')
)
)
)
)::text
FROM public.cities
where active;</syntaxhighlight>

Note: the XML is converted to text because it was more convenient for me with the client I was using (DBeaver) to then copy and paste into a text file.

Having saved the text file with an .xml extension, it was enough to go to the wiki at: "SpecialPages -> Page tools -> Import pages" and upload the XML from there.

Done, hundreds of pages created quickly!