libfossil  Artifact Content

Artifact 2609b771c7af59f81a8445a0e4942e22c8b6aab3:

Wiki page [DbFunctions] by stephan 2016-01-13 17:53:01.
D 2016-01-13T17:53:01.030
L DbFunctions
P bb35813357b16f55dc00b7000218a6258e5bb9e5
U stephan
W 5176
<h1>Fossil Database Schemas</h1>

The Fossil DB schemas can be perused, in the form of
commented SQL, in [http://fossil.wanderinghorse.net/repos/libfossil/doxygen/|the API docs].

The library reserves the db symbol prefixes "fsl_" and "fx_fsl_" (case-insensitive) for its own use - clients should not define any functions or tables with those name prefixes. Fossil(1) reserves <em>all</em> table names which do not start with "fx_" ("fossil extension"). During a rebuild, fossil(1) will <em>drop</em> any repo tables it does not know about unless their names start with "fx_".


<h1>Fossil DB User-defined Functions</h1>

A fossil-bound DB handle gets a few extra SQL-callable functions, as
listed below in alphabetical order...

<h3>FSL_CKOUT_DIR()</h3>

<tt>FSL_CKOUT_DIR(<nowiki>[INTEGER=1]</nowiki>)</tt> returns the path to the current checkout directory, or NULL if no checkout is opened. If passed no argument or passed a value which evaluates to non-0 in an integer context, the trailing slash is included in the returned value (which is Fossil's historical convention with regard to directory names). If passed any value which evaluates to integer 0, the slash is not included.

e.g. to get the path to the ".fossil-settings" (versionable settings) directory for the current checkout:

<nowiki><pre>SELECT FSL_CKOUT_DIR() || '.fossil-settings';
</pre></nowiki>


<h3>FSL_CI_MTIME()</h3>

<tt>FSL_CI_MTIME(INT,INT)</tt> takes two RIDs as arguments: the manifest (checkin) version RID and the blob.rid value of a file which part of the first RID's checkin.

It behaves like <tt>fsl_mtime_of_manifest_file()</tt>, returning the calculated (and highly synthetic!) mtime as an SQL integer (Unix epoch timestamp). This is primarily
for internal use.


<h3>FSL_CONTENT()</h3>

<tt>FSL_CONTENT(INTEGER|STRING)</tt> returns the undeltified,
uncompressed content for the blob record with the given RID (if
the argument is an integer) or symbolic name (as per
fsl_sym_to_rid()). If the argument does not resolve to an
in-repo blob, a db-level error is triggered. If passed an
integer, no validation is done on its validity, but such
checking can be enforced by instead passing the the RID as
a string in the form "rid:THE_RID".


<h3>FSL_DIRPART()</h3>

<tt><nowiki>FSL_DIRPART(STRING[, BOOL=0])</nowiki></tt> behaves like
fsl_file_dirpart(), returning the result as a string
unless it is empty, in which case the result is an
SQL NULL.

An example of getting all directory names in the repository (across all
file versions, for simplicity):

<nowiki><pre>
SELECT DISTINCT(fsl_dirpart(name)) n
FROM filename WHERE n IS NOT NULL
ORDER BY n
</pre></nowiki>

To get all the dirs for a specific version one needs to do more work. We'll leave that as an exercise for... me, and once i figure it out i'll post it. It seems that getting that information requires C-level code for the time being.

<h3>FSL_IS_ENQUEUED() and FSL_IF_ENQUEUED()</h3>

<tt>FSL_IS_ENQUEUED(INT)</tt> determines whether a given file is "enqueued" in a pending checkin operation. This is normally only used internally, but "might" have some uses elsewhere. If no files have explicitly been queued up for checkin (via the <tt>fsl_checkin_file_enqueue()</tt> C function) then <em>all files</em> are considered to be selected (though only <em>modified</em> files would actually be checked in if a commit were made).

As its argument it expects a <tt>vfile.id</tt> field value (vfile is the table where fossil tracks the current checkout's status). It returns a truthy value if that file is selected/enqueued, else a falsy value.

<tt>FSL_IF_ENQUEUED(INT,X,Y)</tt> is a close counterpart of <tt>FSL_IS_ENQUEUED()</tt>. If the <tt>vfile.id</tt> passes as the first parameter is enqueued then it resolves to the <tt>X</tt> value, else to the <tt>Y</tt> value, <em>unless</em> Y is NULL, in which case it always resolves to X. Why? Because its only intended usage is to be passed the (id, pathname, origname) fields from the vfile table.


<tt>FSL_IF_ENQUEUED(I,X,Y)</tt> is basically equivalent to this pseudocode:

<nowiki><pre>
result = FSL_IS_ENQUEUED(I) ? X : ((Y IS NULL) ? X : Y)
</pre></nowiki>

<h3>FSL_J2U()</h3>

<tt>FSL_J2U(JULIAN_DAY)</tt> expects a Julian Day value and returns its equivalent in Unix Epoch timestamp as a 64-bit integer, as per <tt>fsl_julian_to_unix()</tt>. Fossil tends to use Julian Days for recording timestamps, but a small few cases use Unix timestamps.


<h3>FSL_SYM2RID()</h3>

<tt>FSL_SYM2RID(STRING)</tt> returns a blob RID for the
given symbol, as per fsl_sym_to_rid(). Triggers an
SQL error if fsl_sym_to_rid() fails.

TODO: add an optional boolean second param (default=true) which tells it to return NULL instead of triggering an error.

<h3>FSL_USER()</h3>

Returns the current value of fsl_cx_user_get(), or NULL
if that is not set.

Example:
<nowiki><pre>
# f-query -e 'select fsl_user()'
fsl_user()
stephan

# f-query -e 'select fsl_user()' --user root
fsl_user()
root
</pre></nowiki>



<h3>NOW()</h3>

Returns the current time as an integer, as per time(2).
Z 649798f9d701dd20a32d20e349c0e352