Database package: Difference between revisions

From Octave
Jump to navigation Jump to search
 
(7 intermediate revisions by 2 users not shown)
Line 12: Line 12:
** [https://github.com/rmartinjak/mex-sqlite3 mex-sqlite3] – An extension for MATLAB® or GNU/octave to access sqlite3 databases
** [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://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'''
* '''redis'''
** [https://gitlab.com/markuman/go-redis go-redis] - mex file extension for GNU/Octave to access Redis
** [https://gitlab.com/markuman/go-redis go-redis] - mex file extension for GNU/Octave to access Redis
Line 18: Line 19:


==Installation==
==Installation==
Requires octave struct. In ubuntu/debian based systems this is handled with
<small>This install guide covers the official (postgres) database package only.</small>
  apt install octave-struct
 
May also need
Requires octave struct, libpq-dev, and liboctave-dev. In ubuntu/debian based systems this is handled with
  apt install libpq-dev
  apt install octave-struct libpq-dev liboctave-dev
And if you want postgres
  apt install postgresql postgresql-client


Next octave command line:
Next octave command line:
Line 27: Line 30:
  >> pkg install -forge database
  >> pkg install -forge database


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


==Usage==
==Usage==
Line 39: Line 42:
  conn = pq_connect (setdbopts ("dbname", "mydatabase"));
  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.  
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:
Now, you are ready to read the db:
  N = pq_exec_params (conn, "select * from Table1;")
  N = pq_exec_params (conn, "select * from Table1;")
Line 89: Line 100:
===Cell2mat: Database Data Types Should be the Same===
===Cell2mat: Database Data Types Should be the Same===
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.
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===
octave:1> database_doc()


==Further Reading==
==Further Reading==

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]