-
Notifications
You must be signed in to change notification settings - Fork 1
Specific types
Lukas Cardot edited this page Feb 16, 2020
·
1 revision
When we use database simple types, it is not too complicated to represent them in a JSON file. The JCV-DB library allows to check the data of more complex fields (user-defined type, geometric, JSON,...)
In SQL databases, we can find usage of geometric and JSON fields. JCV-DB serializes these fields to be able to use them in JSON files.
The JSON fields supported by the library are the following:
- PostgreSQL: JSONB and JSON
- MySQL: JSON
- MSSQL: no JSON type defined
Imagine we have the following table and data:
CREATE TABLE table_type (
id UUID NOT NULL CONSTRAINT pk_table_type PRIMARY KEY,
column_jsonb JSONB
);
INSERT INTO table_type (id, column_jsonb) VALUES ('6ba548e8-8d44-4cc6-bdda-0fb6f158dbc6', '{"name": "John", "age": 18}')JCV-DB will internally understand the inserted data this way:
[
{
"id": "6ba548e8-8d44-4cc6-bdda-0fb6f158dbc6",
"column_jsonb": {
"name": "John",
"age": 18
}
}
]The library supports the following geometric types ( doc ) :
- point
- lseg
- box
- path
- polygon
- circle
- line
Imagine we have the following table and data:
CREATE TABLE table_type (
id UUID NOT NULL CONSTRAINT pk_table_type PRIMARY KEY,
column_point point,
column_lseg lseg,
column_box box,
column_path path,
column_polygon polygon,
column_circle circle,
column_line line
);
INSERT INTO table_type (id, column_point, column_lseg, column_box, column_path, column_polygon, column_circle, column_line)
VALUES (
'6ba548e8-8d44-4cc6-bdda-0fb6f158dbc6',
point(1,2),
lseg(point(1,2), point(2,3)),
box(point(1,2), point(2,3)),
path(polygon(box(point(1,2), point(2,3)))),
polygon(box(point(1,2), point(2,3))),
circle(point(1,2), 3),
line(point(1.2, 123.1), point(-5, -123))
);JCV-DB will internally understand the inserted data this way:
[
{
"id": "6ba548e8-8d44-4cc6-bdda-0fb6f158dbc6",
"column_point": { "x": 1, "y": 2 },
"column_line": {
"a": 39.69354838709677,
"b": -1,
"c": 75.46774193548387
},
"column_polygon": [
{ "x": 1, "y": 2 },
{ "x": 1, "y": 3 },
{ "x": 2, "y": 3 },
{ "x": 2, "y": 2 }
],
"column_lseg": [
{ "x": 1, "y": 2 },
{ "x": 2, "y": 3 }
],
"column_circle": {
"center": { "x": 1, "y": 2 },
"radius": 3
},
"column_box": [
{ "x": 2, "y": 3 },
{ "x": 1, "y": 2 }
],
"column_path": [
{ "x": 1, "y": 2 },
{ "x": 1, "y": 3 },
{ "x": 2, "y": 3 },
{ "x": 2, "y": 2 }
]
}
]The library supports the following geometric types:
Imagine we have the following table and data:
CREATE TABLE table_type (
id UUID NOT NULL CONSTRAINT pk_table_type PRIMARY KEY,
column_point POINT,
column_linestring LINESTRING,
column_polygon POLYGON,
column_multipoint MULTIPOINT,
column_multilinestring MULTILINESTRING,
column_multipolygon MULTIPOLYGON
);
INSERT INTO table_type (id, column_point, column_linestring, column_polygon, column_multipoint, column_multilinestring, column_multipolygon)
VALUES (
'6ba548e8-8d44-4cc6-bdda-0fb6f158dbc6',
POINT(1, 3),
LINESTRING(POINT(0, 0) , POINT(1, 2) , POINT(2, 4), POINT(3, 6)),
POLYGON(LINESTRING(POINT(40, 40), POINT(48, 40), POINT(52, 49), POINT(40, 49), POINT(40, 40), POINT(45, 43), POINT(44, 45), POINT(47, 49), POINT(43, 47), POINT(40, 40))),
MULTIPOINT(POINT(0, 0) , POINT(1, 2) , POINT(2, 4)),
MULTILINESTRING(LINESTRING(POINT(12, 12), POINT(22, 22)), LINESTRING(POINT(19, 19), POINT(32, 18))),
MULTIPOLYGON(POLYGON(LINESTRING(POINT(140, 140), POINT(148, 140), POINT(152, 149), POINT(140, 149), POINT(140, 140), POINT(145, 143), POINT(144, 145), POINT(147, 149), POINT(143, 147), POINT(140, 140))))
);JCV-DB will internally understand the inserted data this way:
[
{
"id": "6ba548e8-8d44-4cc6-bdda-0fb6f158dbc6",
"column_point": {
"x": 1,
"y": 3
},
"column_multipoint": [
{ "x": 0, "y": 0 },
{ "x": 1, "y": 2 },
{ "x": 2, "y": 4 }
],
"column_polygon": [
{ "x": 40, "y": 40 },
{ "x": 48, "y": 40 },
{ "x": 52, "y": 49 },
{ "x": 40, "y": 49 },
{ "x": 40, "y": 40 },
{ "x": 45, "y": 43 },
{ "x": 44, "y": 45 },
{ "x": 47, "y": 49 },
{ "x": 43, "y": 47 },
{ "x": 40, "y": 40 }
],
"column_multipolygon": [
[
{ "x": 140, "y": 140 },
{ "x": 148, "y": 140 },
{ "x": 152, "y": 149 },
{ "x": 140, "y": 149 },
{ "x": 140, "y": 140 },
{ "x": 145, "y": 143 },
{ "x": 144, "y": 145 },
{ "x": 147, "y": 149 },
{ "x": 143, "y": 147 },
{ "x": 140, "y": 140 }
]
],
"column_multilinestring": [
[
{ "x": 12, "y": 12 },
{ "x": 22, "y": 22 }
],
[
{ "x": 19, "y": 19 },
{ "x": 32, "y": 18 }
]
],
"column_linestring": [
{ "x": 0, "y": 0 },
{ "x": 1, "y": 2 },
{ "x": 2, "y": 4 },
{ "x": 3, "y": 6 }
]
}
]MSSQL does not have specific datatype for geometry but it can create geometry from their WKT representations. The following WKT representations are supported:
- POINT
- LINESTRING
- POLYGON
- MULTIPOINT
- MULTILINESTRING
- MULTIPOLYGON
CREATE TABLE table_type (
id UUID NOT NULL CONSTRAINT pk_table_type PRIMARY KEY,
column_point geometry,
column_linestring geometry,
column_polygon geometry,
column_multipoint geometry,
column_multilinestring geometry,
column_multipolygon geometry
);
INSERT INTO table_type (id, column_point, column_linestring, column_polygon, column_multipoint, column_multilinestring, column_multipolygon)
VALUES (
'6ba548e8-8d44-4cc6-bdda-0fb6f158dbc6',
geometry::STGeomFromText('POINT (3 4 7)', 0),
geometry::STGeomFromText('LINESTRING(1 1, 2 4, 3 9)', 0),
geometry::STPolyFromText('POLYGON((0 0, 0 3, 3 3, 3 0, 0 0), (1 1, 1 2, 2 1, 1 1))',10),
geometry::STGeomFromText('MULTIPOINT((2 3), (7 8 9.5))', 23),
geometry::Parse('MULTILINESTRING((0 2, 1 1), (1 0, 1 1))'),
geometry::Parse('MULTIPOLYGON(((0 0, 0 3, 3 3, 3 0, 0 0), (1 1, 1 2, 2 1, 1 1)), ((9 9, 9 10, 10 9, 9 9)))')
);JCV-DB will internally understand the inserted data this way:
[
{
"id": "6ba548e8-8d44-4cc6-bdda-0fb6f158dbc6",
"column_point": {
"x": 3,
"y": 4,
"z": 7
},
"column_multipoint": [
{ "x": 2, "y": 3 },
{ "x": 7, "y": 8, "z": 9.5 }
],
"column_polygon": [
{ "x": 0, "y": 0 },
{ "x": 0, "y": 3 },
{ "x": 3, "y": 3 },
{ "x": 3, "y": 0 },
{ "x": 0, "y": 0 },
{ "x": 1, "y": 1 },
{ "x": 1, "y": 2 },
{ "x": 2, "y": 1 },
{ "x": 1, "y": 1 }
],
"column_multipolygon": [
[
{ "x": 0, "y": 0 },
{ "x": 0, "y": 3 },
{ "x": 3, "y": 3 },
{ "x": 3, "y": 0 },
{ "x": 0, "y": 0 },
{ "x": 1, "y": 1 },
{ "x": 1, "y": 2 },
{ "x": 2, "y": 1 },
{ "x": 1, "y": 1 }
],
[
{ "x": 9, "y": 9 },
{ "x": 9, "y": 10 },
{ "x": 10, "y": 9 },
{ "x": 9, "y": 9 }
]
],
"column_multilinestring": [
[
{ "x": 0, "y": 2 },
{ "x": 1, "y": 1 }
],
[
{ "x": 1, "y": 0 },
{ "x": 1, "y": 1 }
]
],
"column_linestring": [
{ "x": 1, "y": 1 },
{ "x": 2, "y": 4 },
{ "x": 3, "y": 9 }
]
}
]In Cassandra, several particular types which are supported:
Imagine we have the following table and data:
CREATE TYPE type_test (
test_field_int int,
test_field_text text
);
CREATE TYPE complex_type (
test_field_int int,
test_type list<frozen<type_test>>
);
CREATE TABLE cassandratest.cassandra_table_type (
id uuid PRIMARY KEY,
map_field map<text, int>,
set_field set<int>,
list_field list<int>,
custom_type_field type_test,
list_udt_field list<frozen<type_test>>,
tuple_field tuple<text, int>,
complex_type list<frozen<complex_type>>
);
INSERT INTO cassandratest.cassandra_table_type (id, map_field, set_field, list_field, custom_type_field, list_udt_field, tuple_field, complex_type) VALUES (
28f4dcde-a221-4133-8d72-4115c4d24038,
{ 'fruit' : 1, 'legume': 2 },
{ 1 , 2, 3, 4 },
[ 1, 2, 3, 4 ],
{ test_field_int : 1, test_field_text: 'test'},
[{ test_field_int: 1, test_field_text: 'test'}],
('fruit', 1),
[{ test_field_int : 1, test_type: [{ test_field_int : 1, test_field_text: 'test'}]}]
);JCV-DB will internally understand the inserted data this way:
[
{
"id": "28f4dcde-a221-4133-8d72-4115c4d24038",
"tuple_field": ["fruit", 1],
"custom_type_field": {
"test_field_text": "test",
"test_field_int": 1
},
"list_udt_field": [
{
"test_field_text": "test",
"test_field_int": 1
}
],
"list_field": [1, 2, 3, 4],
"map_field": { "legume": 2, "fruit": 1 },
"set_field": [1, 2, 3, 4],
"complex_type": [
{
"test_field_int": 1,
"test_type": [
{ "test_field_int": 1, "test_field_text": "test"}
]
}
]
}
]