Externalizing Notes Data

By default, LogicNets stores notes in the local SQLite database of your logicnets installation. However, you can also store this data in an external databases by configuring the data source in the part editor of the Notes part. You can enter the database connection string, pointing it to an ODBC data source. You can also specify the names of Notes tables in your external database if they need to be called something different than they are in LogicNets.

Supported Databases and Database Schemas

The LogicNets platform supports the following external databases: Oracle, MS SQL Server, and MySQL. The notes functionality requires three tables: notes, replies, and notes_mapping. You can also create custom types notes_types and a table to store additional meta data with notes: notes_meta. The different database schemas for the notes tables are as follows:

LogicNets Notes Tables - Oracle

CREATE TABLE NOTES
("ID" NUMBER,
"STAMP" DATE,
"USER_ID" VARCHAR2(255),
"USER_EMAIL" VARCHAR2(255),
"TITLE" VARCHAR2(4000),
"PKG_NAME" VARCHAR2(255),
"PKG_CATEGORY" VARCHAR2(255),
"PKG_VERSION" VARCHAR2(255),
"STATUS" VARCHAR2(255),
"SESSIONID" VARCHAR2(255),
"ADDITIONALINFO" VARCHAR2(4000),
"SCREENSHOT" BLOB,
"TEXT" VARCHAR2(4000),
"NR_OF_REPLIES" NUMBER,
PRIMARY KEY (ID)
)

CREATE TABLE REPLIES
("ID" NUMBER,
"NOTE_ID" NUMBER,
"STAMP" DATE,
"USER_ID" VARCHAR2(255),
"USER_EMAIL" VARCHAR2(255),
"TEXT" VARCHAR2(255),
PRIMARY KEY (ID)
)

CREATE TABLE NOTES_MAPPING
("ID" NUMBER,
"NOTE_ID" NUMBER,
"LOGICNET" VARCHAR2(255),
"NODE" NUMBER,
"PARTID" VARCHAR2(4000),
"PKG_DATA" VARCHAR2(4000),
"VIEW_TYPE" VARCHAR2(255),
"TYPE" VARCHAR2(255),
"REFERENCE" VARCHAR2(4000),
"TAB_DATA" VARCHAR2(4000),
"TAB" VARCHAR2(255),
"SOURCE_ID" VARCHAR2(4000),
"TAB_ID" VARCHAR2(255),
PRIMARY KEY (ID)
)

CREATE TABLE NOTES_TYPES
("ID" NUMBER,
"TYPE" VARCHAR2(255),
"CAPTION" VARCHAR2(255),
"PARENT" VARCHAR2(255),
PRIMARY KEY (ID)
)

CREATE TABLE NOTES_META
("ID" NUMBER,
"NOTE_ID" NUMBER,
...
PRIMARY KEY (ID)
)

LogicNets Notes Tables - Microsoft SQL Server

CREATE TABLE notes
([id] BIGINT,
[stamp] DATETIME,
[user_id] NVARCHAR(255),
[user_email] NVARCHAR(255),
[title] NVARCHAR(MAX),
[pkg_name] NVARCHAR(255),
[pkg_category] NVARCHAR(255),
[pkg_version] NVARCHAR(255),
[status] NVARCHAR(255),
[sessionid] NVARCHAR(255),
[additionalinfo] NVARCHAR(MAX),
[screenshot] VARBINARY(MAX),
[text] NVARCHAR(MAX),
[nr_of_replies] FLOAT,
PRIMARY KEY (id)
)

CREATE TABLE replies
([id] BIGINT,
[note_id] BIGINT,
[stamp] DATETIME,
[user_id] NVARCHAR(255),
[user_email] NVARCHAR(255),
[text] NVARCHAR(255),
PRIMARY KEY (id)
)

CREATE TABLE notes_mapping
([id] BIGINT,
[note_id] BIGINT,
[logicnet] NVARCHAR(255),
[node] BIGINT,
[partid] NVARCHAR(MAX),
[pkg_data] NVARCHAR(MAX),
[view_type] NVARCHAR(255),
[type] NVARCHAR(255),
[reference] NVARCHAR(MAX),
[tab_data] NVARCHAR(MAX),
[tab] NVARCHAR(255),
[source_id] NVARCHAR(MAX),
[tab_id] NVARCHAR(255),
PRIMARY KEY (id)
)

CREATE TABLE notes_types
([id] BIGINT,
[type] NVARCHAR(255),
[caption] NVARCHAR(255),
[parent] NVARCHAR(255),
PRIMARY KEY (id)
)

CREATE TABLE notes_meta
([id] BIGINT,
[note_id] BIGINT,
...
PRIMARY KEY (id)
)

LogicNets Notes Tables - MySQL

CREATE TABLE notes
(`id` BIGINT,
`stamp` DATE,
`user_id` varchar(255),
`user_email` varchar(255),
`title` varchar(4000),
`pkg_name` varchar(255),
`pkg_category` varchar(255),
`pkg_version` varchar(255),
`status` varchar(255),
`sessionid` varchar(255),
`additionalinfo` varchar(4000),
`screenshot` blob,
`text` varchar(4000),
`nr_of_replies` FLOAT,
PRIMARY KEY (`id`)
);

CREATE TABLE replies
(`id` BIGINT,
`note_id` BIGINT,
`stamp` DATE,
`user_id` varchar(255),
`user_email` varchar(255),
`text` varchar(255),
PRIMARY KEY (`id`)
);

CREATE TABLE notes_mapping
(`id` BIGINT,
`note_id` BIGINT,
`logicnet` varchar(255),
`node` BIGINT,
`partid` varchar(4000),
`pkg_data` varchar(4000),
`view_type` varchar(255),
`type` varchar(255),
`reference` varchar(4000),
`tab_data` varchar(4000),
`tab` varchar(255),
`source_id` varchar(4000),
`tab_id` varchar(255),
PRIMARY KEY (`id`)
);

CREATE TABLE notes_types
(`id` BIGINT,
`type` varchar(255),
`caption` varchar(255),
`parent` varchar(255),
PRIMARY KEY (`id`)
);

CREATE TABLE notes_meta
(`id` BIGINT,
`note_id` BIGINT,
...
PRIMARY KEY (`id`)
)