Database package: Difference between revisions

From Octave
Jump to navigation Jump to search
(import of messages I had on my inbox about database package)
 
 
(25 intermediate revisions by 7 users not shown)
Line 1: Line 1:
This Octave Forge has currently no maintainer and is known to nor well anymore.
== Octave Forge ==


Official GNU/Octave Forge Database package supports only '''Postgres'''


== osdbi ==
* https://octave.sourceforge.io/database/index.html


<!-- Posted on the mailing list by Stuart Edwards <sedwards2@cinci.rr.com> -->
== 3rd Party ==


There's another package called osdbi that is released under the BSD license that I was able to make work, but only for sqlite3.
On github/gitlab are several open source projects which supports different open source database solutions for GNU/Octave.
You can find it here:
http://jriedy.users.sonic.net/cgi/jriedy/cgit/cgit.cgi/osdbi/tree/doc/osdbi.texi
JRiedy the author will answer emails.  I use it on OS X 10.6 and octave 3.4.0.  Also on 10.5.8 but no luck on 10.7


* '''sqlite'''
** [https://github.com/rmartinjak/mex-sqlite3 mex-sqlite3] – An extension for MATLAB® or GNU/octave to access sqlite3 databases
** [https://github.com/Andy1978/octave-sqlite octave-sqlite] - sqlite3 wrapper as an .oct file for GNU/Octave
** [https://gnu-octave.github.io/octave-sqlite/ sqlite] - MATLAB® native sqlite compatible extension for GNU/octave.
* '''redis'''
** [https://gitlab.com/markuman/go-redis go-redis] - mex file extension for GNU/Octave to access Redis
* '''MariaDB''' / MySQL
** [https://gitlab.com/markuman/mex-mariadb mex-mariadb] - mex file extension for GNU/Octave to access MariaDB


== postgreSQL ==
==Installation==
<small>This install guide covers the official (postgres) database package only.</small>


<!-- Posted on the mailing list by Miguel <nunocavalheiromarques@gmail.com> -->
Requires octave struct, libpq-dev, and liboctave-dev. In ubuntu/debian based systems this is handled with
apt install octave-struct libpq-dev liboctave-dev
And if you want postgres
apt install postgresql postgresql-client


After some hours trying to connect to postgresql, I am geting convinced
Next octave command line:
there is currently no DB connection to postgresql DB from octave. Indeed,
there is a very nice small code for mkoctfile:


  http://dirk.eddelbuettel.com/code/octave-pg/
>> pkg install -forge database


But,  I found there is no longer a libpq++ in postgresql
Can take a long time on low power devices. Packages are compiled. Monitor process with top.
(http://blog.gmane.org/gmane.comp.db.postgresql.interfaces/month=20080701)...
Anyone knows if there is a libpqxx interface (I found nothing on google)?
In another approach, maybe "extern C" can be used to reimplement a libpq
(standard C) based oct of Dirk Eddelbuettel's code?


== Embedded SQL ==
==Usage==
Load it before any usage:


A [https://sourceforge.net/p/octave/feature-requests/30/ feature request] was open on the tracker to include it as a package.
>> pkg load database


== SQLite ==
===Connecting to a Database===


<!-- Posted on the mailing list by Paul Dreik <slask@pauldreik.se> -->
The user running octave must have permissions to access the pg db, or you can pass the username and pass via pg_connect. Here is an example where the user has access to pg without passing credentials.
conn = pq_connect (setdbopts ("dbname", "mydatabase"));
Here, you are specifying a key-value pair. So you want to fill in a key (dbname), and that dbname is mydatabase.


Note: If you get an error similar to:
<pre>could not connect to server: No such file or directory
        Is the server running locally and accepting
        connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?
</pre>
Try connecting via the IP/port instead of the unix socket. e.g.
conn = pq_connect (setdbopts ("dbname", "testdatabase", "host", "localhost", "port", "5433", "user", "pguser", "password", "pguserpassword"))
Now, you are ready to read the db:
N = pq_exec_params (conn, "select * from Table1;")
Values are returned in a struct, e.g.:
<pre>
<pre>
On 22 December 2011 20:53, Paul Dreik <slask@pauldreik.se> wrote:
  N =
> 2011-12-21 13:02, dirk skrev:
 
>> On Wed, 2011-12-21 at 09:34 +0100, Paul Dreik wrote:
  scalar structure containing the fields:
>>> 2011-12-20 23:37, dirkmayhew@comcast.net skrev:
 
>>>> Hi all -
    data =
>>>> Couldn't make the database package work, and after a reasonable amount
    {
>>>> of googling I concluded I was not the only one.
      [1,1] = 3895
>>>> As a solution I made my own .oct file that queries SQLite & returns a
      [2,1] = 3942
>>>> cell array of the results; kudos to SQLite's "amalgamation" file and
      [3,1] = 3919
>>>> Octave's "mkoctfile."
      [4,1] = 3866
>>>>
      [5,1] = 3923
>>>> - is this the right forum to ask the next 2 questions?
      [6,1] = 3969
>>>> - is anyone interested in connecting SQLite to octave?
      [1,2] = 1324612180367
>>>> - what's my next reasonable step (if any): make a package, post code
      [2,2] = 1324612180369
>>>> somewhere (here?), or... ?
      [3,2] = 1324612188073
>>>>
      [4,2] = 1324612190313
>>>> thanks,
      [5,2] = 1324612191841
>>>> dirk mayhew
      [6,2] = 1324612192922
>>>> ps and thank you also to Xavier Delacour - I would use your code if I could
    }
>>>>
    columns =
>>> Hi, no you are certainly not the only one!
    {
>>> I have also made an sqlite octave package, but only for private use. I
      [1,1] = value
>>> think it is wise to discuss on how to implement the interface to such a
      [1,2] = unixtime
>>> toolbox.
    }
>>> * should one mimic the c interface?
    types =
>>> * should one mimic the perl dbi interface?
>>> * mimic the matlab database interface?
>>> * make a new one?
>>> What is your opinion?
>>>
>>> This is the right place to discuss and send code suggestions.
>>>
>>> Paul Dreik
>>>
>> Thanks, Paul - those are good questions.
>>
>> I assume by "c interface" you mean ODBC?  I believe that would be best,
>> but requires more work... I suspect this is the direction Xavier took
>> with his database package.
>>
>> Given a choice between perl, Matlab and "create your own," the community
>> would probably prefer a solution that "looks like Matlab." That is,
>> without a GUI and no Matlab-specific I/O, I assume.
>>
>> What do you think?
>>
> No, I mean the sqlite c interface described in
> http://sqlite.org/c3ref/intro.html
>
> Using odbc seems to be yet another method to connect. It seems like
> there is an odbc driver for sqlite.
> http://www.sqlite.org/cvstrac/wiki?p=SqliteOdbc
> I know the matlab database toolbox can connect through odbc, that is how
> I used it a few years ago.
>
>
> I assembled the list below with my comments and links to documentation.
>
> I think the matlab database toolbox is a bit messy. It is also quite
> big, so users expecting to use octave database as a dropin replacement
> will probably never be satisfied unless they only use a very basic
> subset. It handles multiple databases, which is good.
> The documentation is at
> http://www.mathworks.se/help/toolbox/database/ug/f4-6010.html
>
> I like the relatively clean interface of the sqlite c interface. This is
> how I built my toolbox. (It was a while ago, so I do not remember how
> closely I followed the c interface). Obviously this only supports
> sqlite, so It may be a poor choise for octave database toolbox unless it
> is an octave sqlite toolbox.
> Documentation for api introduction is at http://sqlite.org/cintro.html
>
> I also like the perl dbi interface. It handles multiple databases, so it
> may be a good example on how to make an octave interface.
> An easy to read introduction exists at
> http://www.perl.com/pub/1999/10/DBI.html
>
> I have never worked directly to odbc. Looking at the introductory
> material on
> http://msdn.microsoft.com/en-us/library/windows/desktop/ms714078%28v=vs.85%29.aspx
> I say lets stick to any other method...
> The good thing is that it handles multiple databases and it is cross
> platform.
>
> The existing octave database package seems to offer both a generic
> interface and wrap the database specific apis. I do not know how well it
> performs. Installation failed for me two years ago or so, and it still
> fails now (octave 3.4.2 from macports. See attached errors). I assume I
> do not have all dependencies installed, but the errors seem to point to
> other problems. Have not looked deeper into it.
> I do not know if there are any plans for it or what the current status
> is. Last update on http://octave-swig.sourceforge.net/octave-db.html
> (linked to from http://octave.sourceforge.net/database/overview.html)
> was 2008, but the package was released 2009.
> The swig operations seem like black magic to me.
>
> I do not currently have the time or need to work on this now, but I am
> willing to share my octave package (sqlite c interface wrapped, more or
> less).


      1x2 struct array containing the fields:


On 23 December 2011 13:42, dirk <dirkmayhew@comcast.net> wrote:
        name
> On Wed, 2011-12-21 at 19:56 +0000, Carnë Draug wrote:
        is_array
>> Are you able to indetify what's broken in the current package?
        is_composite
>
        is_enum
> I am not calling it broken, but "difficult to install" or "not
        elements
> self-contained" would be fair descriptions.
</pre>
Display specific elements e.g.:
  display(N.data)


Convert to a format which can be plotted/graphed on an x,y axis:
p = cell2mat (N.data)


Graph:
plot (p(:, 2), p(:, 1))


On 29 December 2011 16:21, dirk <dirkmayhew@comcast.net> wrote:
==Tips/Tricks==
> Unfortunately the file size limit prevented me uploading the required
===Cell2mat: Database Data Types Should be the Same===
> sqlite3.c source file; it is available via
Note that you must have all database columns be the same type in order for the values to not get potentially changed when using cell2mat. In the example above, if you have the UnixTime column above be a bigint, and the Value column be an Int, the UnixTime will get truncated. Changing the Value column to a bigint will resolve this.
> http://www.sqlite.org/sqlite-amalgamation-3070900.zip
===Built in Help Documentation===
>
octave:1> database_doc()
> The resulting .oct acts like a dedicated database server.
 
> Functionally it mimics the existing database "sql" function, but
==Further Reading==
> *without* the pre-requirements of SWIG & an additional external database
* https://octave.sourceforge.io/database/index.html
> server oprogram.
 
>
[[Category:Octave Forge]]
> Can anyone can point me to a "beginner's guide to package creation?"
> mkoctfile worked fine for me on Ubuntu and Win7, but I'd guess a tar.gz
> supporting pkg install would be better/cleaner/more useful.
</pre>

Latest revision as of 13:22, 3 January 2023

Octave Forge[edit]

Official GNU/Octave Forge Database package supports only Postgres

3rd Party[edit]

On github/gitlab are several open source projects which supports different open source database solutions for GNU/Octave.

  • sqlite
    • mex-sqlite3 – An extension for MATLAB® or GNU/octave to access sqlite3 databases
    • octave-sqlite - sqlite3 wrapper as an .oct file for GNU/Octave
    • sqlite - MATLAB® native sqlite compatible extension for GNU/octave.
  • redis
    • go-redis - mex file extension for GNU/Octave to access Redis
  • MariaDB / MySQL
    • mex-mariadb - mex file extension for GNU/Octave to access MariaDB

Installation[edit]

This install guide covers the official (postgres) database package only.

Requires octave struct, libpq-dev, and liboctave-dev. In ubuntu/debian based systems this is handled with

apt install octave-struct libpq-dev liboctave-dev

And if you want postgres

apt install postgresql postgresql-client

Next octave command line:

>> pkg install -forge database

Can take a long time on low power devices. Packages are compiled. Monitor process with top.

Usage[edit]

Load it before any usage:

>> pkg load database

Connecting to a Database[edit]

The user running octave must have permissions to access the pg db, or you can pass the username and pass via pg_connect. Here is an example where the user has access to pg without passing credentials.

conn = pq_connect (setdbopts ("dbname", "mydatabase"));

Here, you are specifying a key-value pair. So you want to fill in a key (dbname), and that dbname is mydatabase.

Note: If you get an error similar to:

could not connect to server: No such file or directory
        Is the server running locally and accepting
        connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?

Try connecting via the IP/port instead of the unix socket. e.g.

conn = pq_connect (setdbopts ("dbname", "testdatabase", "host", "localhost", "port", "5433", "user", "pguser", "password", "pguserpassword"))

Now, you are ready to read the db:

N = pq_exec_params (conn, "select * from Table1;")

Values are returned in a struct, e.g.:

 N =

  scalar structure containing the fields:

    data =
    {
      [1,1] = 3895
      [2,1] = 3942
      [3,1] = 3919
      [4,1] = 3866
      [5,1] = 3923
      [6,1] = 3969
      [1,2] = 1324612180367
      [2,2] = 1324612180369
      [3,2] = 1324612188073
      [4,2] = 1324612190313
      [5,2] = 1324612191841
      [6,2] = 1324612192922
    }
    columns =
    {
      [1,1] = value
      [1,2] = unixtime
    }
    types =

      1x2 struct array containing the fields:

        name
        is_array
        is_composite
        is_enum
        elements

Display specific elements e.g.:

display(N.data)

Convert to a format which can be plotted/graphed on an x,y axis:

p = cell2mat (N.data)

Graph:

plot (p(:, 2), p(:, 1))

Tips/Tricks[edit]

Cell2mat: Database Data Types Should be the Same[edit]

Note that you must have all database columns be the same type in order for the values to not get potentially changed when using cell2mat. In the example above, if you have the UnixTime column above be a bigint, and the Value column be an Int, the UnixTime will get truncated. Changing the Value column to a bigint will resolve this.

Built in Help Documentation[edit]

octave:1> database_doc()

Further Reading[edit]