# HG changeset patch
# User František Kučera <franta-hg@frantovo.cz>
# Date 1572363623 -3600
# Node ID de1b49ba06f1d7a9678394ab9f089ea6c9a5a7c1
# Parent  ae17db13569c98637dcd41f2b3dfe993cf9f1270
examples: SELECTing from SQLite databases

diff -r ae17db13569c -r de1b49ba06f1 relpipe-data/examples-in-sql-selecting-existing-database.xml
--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/relpipe-data/examples-in-sql-selecting-existing-database.xml	Tue Oct 29 16:40:23 2019 +0100
@@ -0,0 +1,99 @@
+<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>SELECTing from SQLite databases</nadpis>
+	<perex>read from an existing SQLite file or update it</perex>
+	<m:pořadí-příkladu>03300</m:pořadí-příkladu>
+
+	<text xmlns="http://www.w3.org/1999/xhtml">
+		
+		<p>
+			Both the <code>relpipe-tr-sql</code> and <code>relpipe-in-sql</code>
+			support the option <code>--file</code> and can store data in a file instead of just in-memory.
+			Primary purpose of this option is to offload to HDD and be able to process more data whithout consuming too much RAM.
+			So such file is usually a temporary one and is deleted immediately after the transformation.
+			But we can also direct it to an existing file and append new relations to it.
+			Or we can create a new file and do not delete it – using the <code>--file-keep true</code> option.
+		</p>
+		
+		<p>
+			Thus we can use the <code>relpipe-in-sql</code> as a database client to access existing SQLite files and SELECT from them (and then convert the results to any supported format).
+			An we can use the <code>relpipe-tr-sql</code> as an output filter which converts relational data to a SQLite file that can be queried later.
+		</p>
+		
+		<p>
+			SQLite is a popular format used by many applications to store structured data.
+			Version control systems (VCS) like <a href="https://www.monotone.ca/">Monotone</a> or <a href="https://fossil-scm.org/">Fossil</a>
+			use it as a repository format and put whole history, data and metadata into it.
+		</p>
+		
+		<p>
+			So if we clone a Fossil repository (the SQLite one in this case):
+		</p>
+		
+		<m:pre jazyk="bash"><![CDATA[fossil clone http://www.sqlite.org/cgi/src sqlite.fossil]]></m:pre>
+		
+		<p>
+			We can then query it using <m:name/> tools:
+		</p>
+		
+		<m:pre jazyk="bash"><![CDATA[relpipe-in-sql \
+	--file sqlite.fossil \
+	--relation tickets "
+		SELECT 
+			type,
+			status,
+			count(*) AS count
+		FROM ticket 
+		GROUP BY type, status
+		ORDER BY count DESC;
+	" | relpipe-out-tabular]]></m:pre>
+	
+		<p>and get some aggregated statistics:</p>
+		
+		<m:pre jazyk="text"><![CDATA[tickets:
+ ╭───────────────────┬─────────────────┬────────────────╮
+ │ type     (string) │ status (string) │ count (string) │
+ ├───────────────────┼─────────────────┼────────────────┤
+ │ Code_Defect       │ Fixed           │ 394            │
+ │ Code_Defect       │ Closed          │ 115            │
+ │ Feature_Request   │ Closed          │ 36             │
+ │ Feature_Request   │ Fixed           │ 16             │
+ │ Build_Problem     │ Closed          │ 10             │
+ │ Documentation     │ Fixed           │ 10             │
+ │ Documentation     │ Closed          │ 8              │
+ │ Incident          │ Closed          │ 4              │
+ │ Build_Problem     │ Fixed           │ 3              │
+ │ Performance_Issue │ Closed          │ 3              │
+ │ Support_Request   │ Closed          │ 3              │
+ │ Code_Defect       │ Open            │ 2              │
+ │ Feature_Request   │ Open            │ 2              │
+ │ Performance_Issue │ Fixed           │ 2              │
+ │ Code_Defect       │ Deferred        │ 1              │
+ │ Compiler_Warning  │ Closed          │ 1              │
+ │ Compiler_Warning  │ Fixed           │ 1              │
+ │ Not_A_Bug         │ Closed          │ 1              │
+ │ Performance_Issue │ Open            │ 1              │
+ │ Portability       │ Fixed           │ 1              │
+ ╰───────────────────┴─────────────────┴────────────────╯
+Record count: 20]]></m:pre>
+		
+		<p>
+			Accessing internal data structures of another application might be bit tricky (if the application does not guarantee it as a stable API),
+			but it can be used for ad-hoc digging and even in regular use, there are chances that such „API“ will not break for a reasonable period of time.
+		</p>
+		
+		<p>
+			However, best results can be achieved, if the database scheme is under our control or defined as a stable API by someone else.
+		</p>
+		
+		<p>
+			All relations or their subset can be dumped even without writing any SELECTs: just use <code>--copy '.*'</code>
+			or e.g. <code>--copy 'tickets|tag|evet'</code> or <code>--copy-renamed 'tickets|tag|evet' 'copy_of_$0'</code> and get exported some relations under modified names (using regular expressions).
+			Such export can be converted e.g. to Recfile or XML format using corresponding output filter.
+		</p>
+		
+	</text>
+
+</stránka>