Not really very difficult, and it does not require another type. You just need
a scalar function to process the URI passed to the function and return the
result -- just like the readfile() (in the fileio.c extension, or included in
the shell) function does for a "file-specifier" which can be opened by the
platform open api.
Of course, the size of the module required to do this might be rather large.
Or it could be very simple. Here is a simple example in python using apsw and
fs (output truncated in various places ...) with no meaningful results (ie,
null) if the URI doesn't parse:
--- apswopener.py ---
import apsw
from fs.opener import opener
import sys
def uricontents(URI):
if not URI:
return
try:
contents = opener.getcontents(URI)
except:
return None
try:
return unicode(contents)
except:
return bytearray(contents)
def defineuricontents(cn):
cn.createscalarfunction('uricontents', uricontents, 1)
apsw.connection_hooks.append(defineuricontents)
db = None
if len(sys.argv) > 1:
db = apsw.Connection(sys.argv[1])
apsw.Shell(db=db).cmdloop()
--- EOF ---
2014-09-08 19:59:08 [D:\Data\Apps\fs]
>apswopener.py
SQLite version 3.8.7 (APSW 3.8.5-r1)
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> select uricontents('d:\source\sqlite\x');
SQLITE_32BIT_ROWID
SQLITE_4_BYTE_ALIGNED_MALLOC
SQLITE_64BIT_STATS
SQLITE_ALLOW_COVERING_INDEX_SCAN
[... pasting truncated ...]
sqlite> select uricontents('http://mail.dessus.com/');
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
"http://www.w3.org/TR/html4/loose.dtd";>
<html lang="en" dir="LTR">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title> CommuniGate Pro dessus.com Entrance</title>
<link rel="stylesheet" href="/SkinFiles/dessus.com//style.css"
type="text/css" />
<meta http-equiv="x-dns-prefetch-control" content="off" />
[... pasting truncated ...]
sqlite> select uricontents('http://www.sqlite.org');
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN"
"http://www.w3.org/TR/html4/strict.dtd";>
<html><head>
<meta http-equiv="content-type" content="text/html; charset=UTF-8">
<title>SQLite Home Page</title>
<style type="text/css">
body {
margin: auto;
font-family: Verdana, sans-serif;
padding: 8px 1%;
}
a { color: #044a64 }
a:visited { color: #734559 }
[... pasting truncated ...]
sqlite> select length(uricontents('http://www.sqlite.org/favicon.ico'));
318
sqlite> select uricontents('http://www.sqlite.org/favicon.ico');
<Binary data>
sqlite> select hex(uricontents('http://www.sqlite.org/favicon.ico'));
000001000100101010000100040028010000160000002800000010000000200000000100040000000000000000000000000000000000000000000000000000000000A8A8A800
98A47800705050000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001322222222222231322222022002
00232222220220202222222222022020000220002202202022022222220100020022222222222022222222222202202222222222222222202222200022200002000000200200
20020022220002002202002200002200220200220022220020020022300002200022002313222222222222310000000000000000000000000000000000000000000000000000
0000000000000000000000000000000000000000000000000000000000000000000000000000
sqlite> select typeof(uricontents('http://www.sqlite.org/favicon.ico'));
blob
sqlite> select typeof(uricontents('http://www.sqlite.org/'));
text
>-----Original Message-----
>From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
>boun...@sqlite.org] On Behalf Of John McKown
>Sent: Monday, 8 September, 2014 19:06
>To: General Discussion of SQLite Database
>Subject: Re: [sqlite] What if OpenDocument were built atop SQLite?
>
>On Mon, Sep 8, 2014 at 6:32 PM, Nico Williams <n...@cryptonector.com>
>wrote:
>> On Mon, Sep 8, 2014 at 6:16 PM, Simon Slavin <slav...@bigfraud.org>
>wrote:
>>> On 8 Sep 2014, at 11:45pm, Nico Williams <n...@cryptonector.com>
>wrote:
>>>> It'd also be good to have JSON and XML support, possibly as an
>>>> loadable extension. That way XPath and similar expressions matching
>>>> document snippets in SQL string values could be used in SQL queries.
>>>
>>> Not so sure about that. There are JSON and XML extensions available
>for pretty-much every language out there. Do you feel they need to be
>tightly integrated with SQLite somehow ?
>>
>> No, I don't. I rather dislike the way Postgres did the hstore and the
>> subsequent JSON support.
>>
>> I should clarify: what I don't like is ad-hoc syntax. I would like a
>> way to embed arbitrary external languages like jq or XPath in SQL
>> expressions.
>>
>>>> It'd be *very* convenient if SQLite3 had a value type that
>corresponds
>>>> to loadable extensions' private types, and a protocol for releasing
>>>> and serializing values of such types.
>>>
>>> Ah, but there you struck gold. If this is to be adopted, SQLite needs
>a DataType of FILE. Complete with functions to do things like get a
>file's name, path, length, contents, and probably half a dozen other
>things that don't come to mind right now. In fact this might be useful
>for SQLite even if it isn't going to be used as an OpenDoc file
>container.
>>
>> Yes. Pretty please, with sugar on top?
>>
>> Nico
>> --
>
>Hum, why not a URI data type instead? ref:
>http://en.wikipedia.org/wiki/URI_scheme or
>http://www.ietf.org/rfc/rfc3986.txt .This is a "proper superset" of a
>URL. And it could encompass many other "access methods". Some that are
>implemented in browsers are things like http://some.webpage.html,
>file:///path/to/local/file, mailto:user.n...@organization.net,
>ftp://user:passw...@host.com/subdir/file and lots of other
>possibilities. The RFC does not attempt to detail all possible scheme
>(the first thing in the name, before the first :). SQLite could
>implement the fetching of contents by doing something like loading a
>dynamic library at run-time which might look like: lib<scheme>.so or
>.dll Where <scheme> is the URI scheme. E.g. libftp.so or libhttp.so .
>If just used in a SELECT, SQLite would just return a string value
>equal to the value such as you might see in a browser. And if the
>contents were needed, a function such as fetch() might be use. E.g.
>SELECT FETCH(urifield) FROM table; would get the URI for each non-NULL
>value of "urifield", inspect the first portion for the scheme, load
>the proper library and then pass the URI as a string value to that
>routine. Of course, this is getting fairly involved. And what is done
>if such a library does not exist? Error out the entire SELECT? Return
>a NULL? It gets very complicated. And I'm sure Dr. Hipp will see more
>problems and implications that I could ever imagine.
>
>
>--
>There is nothing more pleasant than traveling and meeting new people!
>Genghis Khan
>
>Maranatha! <><
>John McKown
>_______________________________________________
>sqlite-users mailing list
>sqlite-users@sqlite.org
>http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
