D 2014-03-18T16:03:40.249
L DbFunctions
P 5ca018256b3b8772b762b6dad56e3689edbe2f7f
U stephan
W 4238
<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 reserved 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_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.
<h3>FSL_USER()</h3>
Returns the current value of fsl_cx_user_get(), or NULL
if that is not set.
<h3>NOW()</h3>
Returns the current time as an integer, as per time(2).
Z 6b86a745b8fe17ff0c79d7adedc04a3e