Export/Import data-store variables

groov View
1

Hello experts,
I know there is a way for import/export Device tags, but seems there is no such an option for “Data Store” tags?
For a project I need to replicate multiple Data Store dbs, so anyone knows a way to import/export 'Data Store" tags?

2

Not at the moment, sorry. It’s high on my personal wish-list (we should have easy import/export of any device you have to manually configure), but I haven’t had time to get to it yet.

3

Thanks for the quick reply, but it should be a way to hack into db?

4

I don’t know if anyone at Opto can give you the steps for that, but if you want to attempt it, the database is SQLite and the tables you want to look at are:

devices (one entry per datastore) id gets incremented, kind is 8, ipAddress is null, port is -1
devices_scratch_pad (one entry per datastore) id from above, give it a name
tags (enter your tags here first, id increments, controllerID is the id from devices, type is 10
tags_scratch_pad (enter your tags here with the id from tags table)

Hardest part is going to be keeping the IDs straight, if you really have to do this a lot, writing a script/program for it would be best.

5

Ok. These instructions are completely unsupported, and not guaranteed to work in any release, and are
only tested/marginally valid for groov View 4.2.

First, back up your groov project in the normal way. Then we’re going to crack open the .tar.gz
file and take a look at the database. Uncompress the backup into a clean directory somewhere and
find project.grv. It should be at usr/share/jetty/optoapps/project.grv for legacy reasons,
but that won’t really matter when restoring it later.

Save a copy of that backup, btw. We don’t want to be stuck if we hose the project.

You can use any SQLite tool to look into it. I’m just using sqlite3 on the command line. The
device and tag schema looks like this at the moment. (I’m leaving out the cleanup triggers.)

sqlite> .schema devices
CREATE TABLE devices (id INTEGER PRIMARY KEY ASC AUTOINCREMENT, kind TEXT, ipAddress TEXT, port TEXT);

sqlite> .schema devices_scratch_pad
CREATE TABLE devices_scratch_pad (id INTEGER PRIMARY KEY ASC AUTOINCREMENT, name TEXT );

sqlite> .schema tags
CREATE TABLE tags (id INTEGER PRIMARY KEY ASC AUTOINCREMENT, controllerID INTEGER, refCount INTEGER, type INTEGER DEFAULT 1);

sqlite> .schema tags_scratch_pad
CREATE TABLE tags_scratch_pad  (id INTEGER PRIMARY KEY ASC AUTOINCREMENT, name TEXT, dataType INTEGER, tableLength INTEGER, value TEXT );

Run SELECT id, name FROM devices_scratch_pad; and find the id of the data store you want to copy. Using my
project as an example, it’s device id 6.

sqlite> SELECT id, name FROM devices_scratch_pad;
6|Why Did I Rename This Data Store?

And then SELECT kind, ipAddress, port FROM devices WHERE id=<your-device-id> to find the rest
of the information you need to copy the device. The important bit here is kind: it’s an integer
flag that tells groov View what sort of device it is. So I guess I could have just said:
Data Store devices have kind = 8, but this shows what the rest of the device needs to look
like.

sqlite> SELECT kind, ipAddress, port FROM devices WHERE id=6;
8||-1

So let’s go ahead and make a new device. You’ll need to insert into two tables: the devices
one and the devices_scratch_pad one, in that order. devices first:

sqlite> INSERT INTO devices (kind, ipAddress, port) VALUES (8, '', -1);

And now immediately grab the automatically generated id:

sqlite> SELECT last_insert_rowid();
25

So my new device is id 25. With that, let’s insert the rest of the Data Store:

sqlite> INSERT INTO devices_scratch_pad (id, name) VALUES (25, 'My New Data Store');

Where ‘My New Data Store’ is the name you’d like to use. At this point, if you restore
this project into groov View you’ll have a new Data Store with no tags. So let’s copy all
of them.

First, find them all by looking them up by controllerID in the tags table.

sqlite> SELECT id FROM tags WHERE controllerID = 6;
255
256

I only have 2 on that Data Store at the moment. Now, for each id that was returned there,
we’ll need to do two inserts. (Someone better w/ SQL than me can probably do this more easily.)

sqlite> INSERT INTO tags (controllerID, refCount, type) SELECT <new-device-id>, refCount, type FROM tags WHERE id=<tag-id-to-copy>;
sqlite> SELECT last_insert_rowid();
317

We’ve inserted the one row and gotten back its rowid, now we need the other.

sqlite> INSERT INTO tags_scratch_pad (id, name, dataType, tableLength, value) SELECT <new-tag-id>, name, dataType, tableLength, value FROM tags_scratch_pad WHERE id=<tag-id-to-copy>;

Importantly: the new tag id goes immediately after the SELECT in that statement, and the original tag id goes at the end.

Actually, wait, duh, you just query the insert ID as you go:

sqlite> INSERT INTO tags (controllerID, refCount, type) SELECT <new-device-id>, refCount, type FROM tags WHERE id=<tag-id-to-copy>;
sqlite> INSERT INTO tags_scratch_pad (id, name, dataType, tableLength, value) SELECT last_insert_rowid(), name, dataType, tableLength, value FROM tags_scratch_pad WHERE id=<tag-id-to-copy>;

That copies one tag: repeat for the remaining tag ids.

Once you’re done, re-compress that directory structure using a tool that’ll give you a tar.gz file,
restore it onto your groov instance, and you should be good to go.

1 Like