# HG changeset patch
# User František Kučera <franta-hg@frantovo.cz>
# Date 1554723455 -7200
# Node ID 94b533007e77e4f05312ad5b3ff9b54c76413f89
# Parent  23247d93a0122292248b1a3c1b70ca5251de23a4
examples: recfile (GNU Recutils) input and output

diff -r 23247d93a012 -r 94b533007e77 relpipe-data/examples-recfile.xml
--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/relpipe-data/examples-recfile.xml	Mon Apr 08 13:37:35 2019 +0200
@@ -0,0 +1,118 @@
+<stránka
+	xmlns="https://trac.frantovo.cz/xml-web-generator/wiki/xmlns/strana"
+	xmlns:m="https://trac.frantovo.cz/xml-web-generator/wiki/xmlns/makro">
+	
+	<nadpis>Integrating Relational pipes with GNU Recutils</nadpis>
+	<perex>using recfile format as input and output + filtering</perex>
+	<m:pořadí-příkladu>01900</m:pořadí-příkladu>
+
+	<text xmlns="http://www.w3.org/1999/xhtml">
+		
+		<p>
+			Recfile is the native format of <a href="https://www.gnu.org/software/recutils/">GNU Recutils</a>.
+			Recfiles are text files that contain records of various types.
+			They are human-editable and serve as a simple databases.
+			<m:name/> support input and output in this format since v0.11.
+		</p>
+		
+		
+		<p>
+			We can convert any relational data to the recfile format by using <code>relpipe-out-recfile</code> – e.g. our <code>fstab</code> will look like this:
+		</p>
+
+		<m:pre jazyk="text" src="examples/relpipe-out-fstab.rec.txt"/>
+		
+		<p>
+			Then we can edit this data (e.g. in GNU Emacs which has mode for this format) or store it in a version control system like Mercurial or Git.
+			Because it is a text format (like XML, which is also supported and good for this purpose),
+			we can efficiently track changes in data across versions, do <code>diff</code> or (with some care) even <code>patch</code>.
+			And we can use whole GNU Recutils toolchain while working with such data.
+		</p>
+		
+		<p>
+			Obligatory example of filtering our <code>fstab</code>:
+		</p>
+		
+		<m:pre jazyk="bash"><![CDATA[relpipe-in-fstab | relpipe-out-recfile | recsel -e "type = 'btrfs' || type = 'xfs'"]]></m:pre>
+		
+		<p>Will give us a recfile:</p>
+		
+		<m:pre jazyk="text"><![CDATA[scheme: UUID
+device: a2b5f230-a795-4f6f-a39b-9b57686c86d5
+mount_point: /home
+type: btrfs
+options: relatime
+dump: 0
+pass: 2
+
+scheme:
+device: /dev/mapper/sdf_crypt
+mount_point: /mnt/private
+type: xfs
+options: relatime
+dump: 0
+pass: 2]]></m:pre>
+		
+		<p>And we can convert it back to the relational format using <code>relpipe-in-recfile</code>:</p>		
+		<m:pre jazyk="bash"><![CDATA[relpipe-in-fstab \
+	| relpipe-out-recfile \
+	| recsel -e "type = 'btrfs' || type = 'xfs'" \
+	| relpipe-in-recfile \
+	| relpipe-out-tabular]]></m:pre>
+		
+		<p>and print as a table in our terminal:</p>		
+		<m:pre jazyk="text"><![CDATA[recfile:
+ ╭─────────────────┬──────────────────────────────────────┬──────────────────────┬───────────────┬──────────────────┬───────────────┬───────────────╮
+ │ scheme (string) │ device                      (string) │ mount_point (string) │ type (string) │ options (string) │ dump (string) │ pass (string) │
+ ├─────────────────┼──────────────────────────────────────┼──────────────────────┼───────────────┼──────────────────┼───────────────┼───────────────┤
+ │ UUID            │ a2b5f230-a795-4f6f-a39b-9b57686c86d5 │ /home                │ btrfs         │ relatime         │ 0             │ 2             │
+ │                 │ /dev/mapper/sdf_crypt                │ /mnt/private         │ xfs           │ relatime         │ 0             │ 2             │
+ ╰─────────────────┴──────────────────────────────────────┴──────────────────────┴───────────────┴──────────────────┴───────────────┴───────────────╯
+Record count: 2]]></m:pre>
+		
+		<p>
+			n.b. in the v0.11 the conversion to recfiles and back is not 100% lossless (unlike XML)
+			because <m:name/> support only three data types (string, unsigned integer and boolean) in this version;
+			this will be improved in later releases (more data types are planned before v1.0)
+		</p>
+		
+		<p>
+			<m:name/> can be also used together with <a href="https://sql-dk.globalcode.info/">SQL-DK</a> (in 2019-03-05 development version)
+			to pipe data from big relational databases like PostgreSQL or MariaDB to other formats like recfiles.
+			Having a script:
+		</p>
+				
+		<m:pre jazyk="text" src="examples/sql-dk_pg_1.sh" odkaz="ano"/>
+		
+		<p>
+			We can convert result sets from any SQL queries to relational format and then work with such data without connection to the original database.
+			Thus we can cache (<em>materialize</em>) the results locally in a file and use them even offline.
+			Or we can run the SQL query each time and have fresh data:
+		</p>
+		
+		<m:pre jazyk="text"><![CDATA[sql-dk_pg_1.sh | relpipe-out-recfile]]></m:pre>
+		
+		<p>Will result in:</p>
+		<m:pre jazyk="text" src="examples/sql-dk_pg_1.rec.txt"/>
+		
+		<p>Or we can view the data in classic tabular way using <code>relpipe-out-tabular</code>:</p>
+		<m:pre jazyk="text" src="examples/sql-dk_pg_1.tabular.txt"/>
+		
+		<p>
+			Materialized (or fresh) data from the database can be further transformed 
+			using <code>relpipe-tr-*</code> commands like grep, sed, cut, guile, 
+			or (through the recfile conversion) by the <code>recsel</code> command from GNU Recutils.
+		</p>
+		
+		<p>
+			The <code>relpipe-in-recfile</code> will help with conversion of recfiles to various formats like XHTML,
+			pretty-printing or with xargs-like processing
+			(using <code>relpipe-out-nullbyte</code> and regular <code>xargs</code> or <code>read_nullbyte</code> function
+			as described in the <m:a href="examples-out-bash">Writing an output filter in Bash</m:a> example).
+			Thus we can have data-driven Bash scripts based on our recfiles.
+		</p>
+
+		
+	</text>
+
+</stránka>
diff -r 23247d93a012 -r 94b533007e77 relpipe-data/examples/relpipe-out-fstab.rec.txt
--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/relpipe-data/examples/relpipe-out-fstab.rec.txt	Mon Apr 08 13:37:35 2019 +0200
@@ -0,0 +1,44 @@
+%rec: fstab
+%type: dump int
+%type: pass int
+
+scheme: UUID
+device: 29758270-fd25-4a6c-a7bb-9a18302816af
+mount_point: /
+type: ext4
+options: relatime,user_xattr,errors=remount-ro
+dump: 0
+pass: 1
+
+scheme: 
+device: /dev/sr0
+mount_point: /media/cdrom0
+type: udf,iso9660
+options: user,noauto
+dump: 0
+pass: 0
+
+scheme: 
+device: /dev/sde
+mount_point: /mnt/data
+type: ext4
+options: relatime,user_xattr,errors=remount-ro
+dump: 0
+pass: 2
+
+scheme: UUID
+device: a2b5f230-a795-4f6f-a39b-9b57686c86d5
+mount_point: /home
+type: btrfs
+options: relatime
+dump: 0
+pass: 2
+
+scheme: 
+device: /dev/mapper/sdf_crypt
+mount_point: /mnt/private
+type: xfs
+options: relatime
+dump: 0
+pass: 2
+
diff -r 23247d93a012 -r 94b533007e77 relpipe-data/examples/sql-dk_pg_1.rec.txt
--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/relpipe-data/examples/sql-dk_pg_1.rec.txt	Mon Apr 08 13:37:35 2019 +0200
@@ -0,0 +1,33 @@
+%rec: tables
+
+table_schema: pg_catalog
+table_name: pg_type
+table_type: BASE TABLE
+
+table_schema: pg_catalog
+table_name: pg_roles
+table_type: VIEW
+
+table_schema: pg_catalog
+table_name: pg_group
+table_type: VIEW
+
+%rec: types
+%type: typbyval bool
+
+typname: bool
+typcategory: B
+typbyval: true
+
+typname: bytea
+typcategory: U
+typbyval: false
+
+typname: char
+typcategory: S
+typbyval: true
+
+typname: name
+typcategory: S
+typbyval: false
+
diff -r 23247d93a012 -r 94b533007e77 relpipe-data/examples/sql-dk_pg_1.sh
--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/relpipe-data/examples/sql-dk_pg_1.sh	Mon Apr 08 13:37:35 2019 +0200
@@ -0,0 +1,19 @@
+sql-dk --db "$postgresql_db" --sql "
+SELECT
+	table_schema,
+	table_name,
+	table_type
+FROM information_schema.tables
+WHERE table_schema IN ('pg_catalog', 'information_schema')
+LIMIT 3;
+
+SELECT
+	typName,
+	typCategory,
+	typByVal
+FROM pg_type
+LIMIT 4" \
+	--relation tables \
+	--relation types \
+	--formatter xml \
+	| relpipe-in-xml 
diff -r 23247d93a012 -r 94b533007e77 relpipe-data/examples/sql-dk_pg_1.tabular.txt
--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/relpipe-data/examples/sql-dk_pg_1.tabular.txt	Mon Apr 08 13:37:35 2019 +0200
@@ -0,0 +1,19 @@
+tables:
+ ╭───────────────────────┬─────────────────────┬─────────────────────╮
+ │ table_schema (string) │ table_name (string) │ table_type (string) │
+ ├───────────────────────┼─────────────────────┼─────────────────────┤
+ │ pg_catalog            │ pg_type             │ BASE TABLE          │
+ │ pg_catalog            │ pg_roles            │ VIEW                │
+ │ pg_catalog            │ pg_group            │ VIEW                │
+ ╰───────────────────────┴─────────────────────┴─────────────────────╯
+Record count: 3
+types:
+ ╭──────────────────┬──────────────────────┬────────────────────╮
+ │ typname (string) │ typcategory (string) │ typbyval (boolean) │
+ ├──────────────────┼──────────────────────┼────────────────────┤
+ │ bool             │ B                    │               true │
+ │ bytea            │ U                    │              false │
+ │ char             │ S                    │               true │
+ │ name             │ S                    │              false │
+ ╰──────────────────┴──────────────────────┴────────────────────╯
+Record count: 4