[OT] recutils

[OT] recutils

[Jose E. Marchesi]

Hi.

I apologize in advance for the off-topic, but this could be of interest
to people liking to "organize their life" in plain text.  This is about
"storing your data" in plain text :D

I just made the first release of the GNU recutils, a set of tools and
libraries to access human-editable, text-based databases called
recfiles.  The data is stored as a sequence of records, each record
containing an arbitrary number of named fields.  Advanced capabilities
usually found in other data storage systems are supported by GNU
recutils: data types, data integrity (keys, mandatory fields, etc) as
well as the ability of records to refer to other records (sort of
foreign keys).  Despite its simplicity, recfiles can be used to store
medium-sized databases.

I have been using org-mode for years and it rocks.  In some way it
changed my life.  It is perfect to store all kind of hierarchical data.
At some point I though about applying a similar philosophy to
"sequential" repetitive data with relationships.  Data that we would
store in a relational database.  A nice complement to org.  The recutils
are the result of those thoughts.

I would highly appreciate any comment or suggestion on improving the
format, the utilities, or whatever.

Please see the GNU recutils homepage for more information:
http://www.gnu.org/software/recutils (due to the crack of savannah the
webpages replication facility is not yet working, so the page there is
old and does not reflect the release).  The tarball can be downloaded
from ftp://ftp.gnu.org/gnu/recutils/recutils-1.0.tar.gz

The GNU recutils suite comprises:

- A texinfo manual, describing the Rec format and the accompaying
  software.

- A C library (librec) that provides a rich set of functions to
  manipulate rec data.

- A set of C utilities that can be used in shell scripts and in the
  command line to operate on rec files:

  + `recinf' prints information about the contents of recfiles.
  + `recsel' prints records and field values.
  + `recins' adds new records to a recfile.
  + `recdel' deletes records.
  + `recset' manipulates fields.
  + `recfix' checks and fixes recfiles.
  + `csv2rec' converts comma-separated-values files to recfiles.
  + `mdb2rec' converts mdb files (used by MS-Access) to recfiles.

- rec-mode.el - An emacs mode to edit and navigate recfiles.  Still
  experimental and without a manual.

Whatever... sorry for the spam! XD

-- 
Jose E. Marchesi    jemarch@gnu.org
GNU Project         http://www.gnu.org

Re: [OT] recutils

[Michael Brand]

On Fri, Dec 3, 2010 at 22:16, Jose E. Marchesi <jemarch@gnu.org> wrote:
> I apologize in advance for the off-topic, but this could be of interest
> to people liking to "organize their life" in plain text.  This is about
> "storing your data" in plain text :D
>
> I just made the first release of the GNU recutils, a set of tools and
> libraries to access human-editable, text-based databases called
> recfiles.  The data is stored as a sequence of records, each record
> containing an arbitrary number of named fields.  Advanced capabilities
> usually found in other data storage systems are supported by GNU
> recutils: data types, data integrity (keys, mandatory fields, etc) as
> well as the ability of records to refer to other records (sort of
> foreign keys).  Despite its simplicity, recfiles can be used to store
> medium-sized databases.
>
> I have been using org-mode for years and it rocks.  In some way it
> changed my life.  It is perfect to store all kind of hierarchical data.
> At some point I though about applying a similar philosophy to
> "sequential" repetitive data with relationships.  Data that we would
> store in a relational database.  A nice complement to org.  The recutils
> are the result of those thoughts.
>
> I would highly appreciate any comment or suggestion on improving the
> format, the utilities, or whatever.

Hi Jose

Wow! The ability to have a _relational_ database with foreign keys in
a simple text file is so great news for me. A dream I had for many
years now. Or is it lack of knowledge from my side about already
existing solutions before recutils? Thank you a lot for your work,
sharing it to the community, posting it also on this list here and for
asking for suggestions which I would like to give at the end.

I was thinking about using sqlite from the command line and together
with shell scripts for stuff like my collection of music with
recordings, MIDI files, scores and so on. Because at least for me
editing a text file is by far simpler, more interactive and more
convenient than editing with SQL I will prefer recutils over sqlite.

But since recfiles, the text files of recutils, are not hierarchically
organized I am still considering to use only Org for the case of my
collection of music. Org would have the advantages of outlining,
hyperlinks, column view, todo, tags, agenda view, export for
publishing and many others.

Thus my wish for the file format would be to somehow keep in mind Org
to potentially
- convert the files bidirectionally between the format of recfiles and
  Org, with or without something like "literate programming" of
  org-babel
- use recutils as a language extension to org-babel, recutils reading
  data streamed to its stdin from org-babel and piping back to into
  org-babel result (no writing by recutils to the file)
- as an imagination: run recutils on a file in the format of Org, only
  reading or even read/write.

I will follow up with an example of what I plan to do for my
collection of music, the database table relations squeezed
hierarchically into Org as a first attempt.

Michael

Re: [OT] recutils

[Russell Adams]

> But since recfiles, the text files of recutils, are not hierarchically
> organized I am still considering to use only Org for the case of my
> collection of music. Org would have the advantages of outlining,
> hyperlinks, column view, todo, tags, agenda view, export for
> publishing and many others.
>
> Thus my wish for the file format would be to somehow keep in mind Org
> to potentially

I noticed the file format is very similar to the layout of property
drawers.

Could recutils read an org file using those drawers with minor
changes?

That'd be a neat way to externally cross reference Org files and do
reporting!

Thanks.


------------------------------------------------------------------
Russell Adams                            RLAdams@AdamsInfoServ.com

PGP Key ID:     0x1160DCB3           http://www.adamsinfoserv.com/

Fingerprint:    1723 D8CA 4280 1EC9 557F  66E8 1154 E018 1160 DCB3

Re: [OT] recutils

[Jose E. Marchesi]

    > Thus my wish for the file format would be to somehow keep in mind Org
    > to potentially
    
    I noticed the file format is very similar to the layout of property
    drawers.
    
    Could recutils read an org file using those drawers with minor
    changes?
    
    That'd be a neat way to externally cross reference Org files and do
    reporting!

The parser implemented by librec would need a lot of changes to achieve
this.  But you can always pre-process the org files in a wrapper, that
would remove anything that is not a drawer and the indentation.

-- 
Jose E. Marchesi    jemarch@gnu.org
GNU Project         http://www.gnu.org

Re: [OT] recutils

[Jose E. Marchesi]

    > I would highly appreciate any comment or suggestion on improving the
    > format, the utilities, or whatever.
    
    Wow! The ability to have a _relational_ database with foreign keys
    in a simple text file is so great news for me. A dream I had for
    many years now. Or is it lack of knowledge from my side about
    already existing solutions before recutils?

I don't think there is anything similar.  Initially I just wanted a
simple format to store fields in readable and writable files.  Something
really simple.  Then additional features came to my mind and I
implemented them.  But note that the "relational" characteristics in
recutils are not very sophisticated.  You can't have keys composed by
more than one field, for example.

The rule here is: if you need something more complex then you probably
should be using a real relational dbms instead :)
  
    I was thinking about using sqlite from the command line and together
    with shell scripts for stuff like my collection of music with
    recordings, MIDI files, scores and so on. Because at least for me
    editing a text file is by far simpler, more interactive and more
    convenient than editing with SQL I will prefer recutils over sqlite.

Additionally, text files are tool-independent.  You could even print
your database :)
    
    But since recfiles, the text files of recutils, are not
    hierarchically organized I am still considering to use only Org for
    the case of my collection of music. Org would have the advantages of
    outlining, hyperlinks, column view, todo, tags, agenda view, export
    for publishing and many others.

    Thus my wish for the file format would be to somehow keep in mind Org
    to potentially
    - convert the files bidirectionally between the format of recfiles and
      Org, with or without something like "literate programming" of
      org-babel

I don't think it is generally possible to map the relationships between
record types in a recfile to hierarchies in an org file.  For example,
in a recfile you could have something like:

    %rec: Album
    %key: Name
    %type: Year date
    
    Name: Loving You
    Year: 1957
    Author:Name: Elvis Presley
    
    ...
    
    %rec: Author
    %key: Name
    
    Name: Elvis Presley
    
You could extract:

    * Albums
    ** Elvis Presley
    *** Loving You
    
But then, what if several authors authored an album?

    %rec: Album

    Name: Sounds of Silence
    Author:Name: Simon
    Author:Name: Garfunkel

You could group by Album or by Author then.

    - use recutils as a language extension to org-babel, recutils reading
      data streamed to its stdin from org-babel and piping back to into
      org-babel result (no writing by recutils to the file)

That sounds interesting.  Would be nice to store rec data into the org
files.

Thanks for the suggestions :)

-- 
Jose E. Marchesi    jemarch@gnu.org
GNU Project         http://www.gnu.org

Re: [OT] recutils

[Russell Adams]

On Sat, Dec 04, 2010 at 01:28:39PM +0100, Jose E. Marchesi wrote:
> You could extract:
>
>     * Albums
>     ** Elvis Presley
>     *** Loving You
>
> But then, what if several authors authored an album?
>
>     %rec: Album
>
>     Name: Sounds of Silence
>     Author:Name: Simon
>     Author:Name: Garfunkel
>

* Albums

** Album 1
    :PROPERTIES:
    :NAME:  Sounds of Silence
    :BAND:  The Beegees
    :YEAR:  1912
    :NOTE:  I haven't a clue about these bands
    :END:

** Album 2
    :PROPERTIES:
    :NAME:  Ethel the Aardvark goes quantity surveying
    :BAND:  Monty Python
    :YEAR:  1978
    :NOTE:  A musical adaptation of a children's book
    :END:

An operation across all child topics of "Albums" ought not be
difficult.

Just an idea.




------------------------------------------------------------------
Russell Adams                            RLAdams@AdamsInfoServ.com

PGP Key ID:     0x1160DCB3           http://www.adamsinfoserv.com/

Fingerprint:    1723 D8CA 4280 1EC9 557F  66E8 1154 E018 1160 DCB3

Re: [OT] recutils

[Michael Brand]

Hi Jose

On Sat, Dec 4, 2010 at 13:28, Jose E. Marchesi <jemarch@gnu.org> wrote:
> But note that the "relational" characteristics in
> recutils are not very sophisticated.
>
> The rule here is: if you need something more complex then you probably
> should be using a real relational dbms instead :)

Ok, maybe I expected a bit too much for the initial version 1.0. But
is it already possible or planned to allow recsel deliver field values
from two tables, joined together with a foreign key or what does the
manual mean with "the ability of records to refer to other records
(sort of foreign keys)"?

Michael

Re: [OT] recutils

[Jose E. Marchesi]

    
    On Sat, Dec 4, 2010 at 13:28, Jose E. Marchesi <jemarch@gnu.org> wrote:
    > But note that the "relational" characteristics in
    > recutils are not very sophisticated.
    >
    > The rule here is: if you need something more complex then you probably
    > should be using a real relational dbms instead :)
    
    Ok, maybe I expected a bit too much for the initial version 1.0. But
    is it already possible or planned to allow recsel deliver field values
    from two tables, joined together with a foreign key or what does the
    manual mean with "the ability of records to refer to other records
    (sort of foreign keys)"?

Right now the recutils use the information of the "foreign keys"
(compound fields) just to pick the type of the field in the "referenced"
record descriptor.

For the next version it is planned to support joins in recsel.  For
example, having a database like:

  %rec: Hacker
  %key: Name

  Name: Mr. foo

  %rec: Package

  Name: GNU foo
  Hacker:Name: Mr. foo

  Name: GNU bar
  Hacker:Name: Mr. foo

It will be possible to invoke recsel like:

  $ recsel -t Hacker -p Name,Package:Name foo.rec
  Name: Mr. Foobar
  Package:Name: GNU foo

  Name: Mr. Foobar
  Package:Name: GNU bar
    
-- 
Jose E. Marchesi    jemarch@gnu.org
GNU Project         http://www.gnu.org

Re: [OT] recutils

[Jean-Marie Gaillourdet]

Hi,

jemarch@gnu.org (Jose E. Marchesi) writes:

>     > I would highly appreciate any comment or suggestion on improving the
>     > format, the utilities, or whatever.
>     
>     Wow! The ability to have a _relational_ database with foreign keys
>     in a simple text file is so great news for me. A dream I had for
>     many years now. Or is it lack of knowledge from my side about
>     already existing solutions before recutils?
>
> I don't think there is anything similar.  

You might be interested to hear about txt-sushi [1], a command line
utility to apply SQL statements on a set of csv files, each of which
represents one table.

[1] http://keithsheppard.name/txt-sushi/

Cheers,
  Jean-Marie

Re: [OT] recutils

[Michael Brand]

On Mon, Dec 13, 2010 at 13:33, Jean-Marie Gaillourdet
<jmg@gaillourdet.net> wrote:
> You might be interested to hear about txt-sushi [1], a command line
> utility to apply SQL statements on a set of csv files, each of which
> represents one table.
>
> [1] http://keithsheppard.name/txt-sushi/

Thank you for pointing this out. And there in the link section is also
mentioned "You can now use CSV files as the storage backend for
MySQL":
http://dev.mysql.com/tech-resources/articles/csv-storage-engine.html

Michael