JSON Sample Files
Expense.json
[
{
"WHO": "Joe",
"WEEK": [
{
"NUMBER": 3,
"EXPENSE": [
{
"WHAT": "Beer",
"AMOUNT": 18.00
},
{
"WHAT": "Food",
"AMOUNT": 12.00
},
{
"WHAT": "Food",
"AMOUNT": 19.00
},
{
"WHAT": "Car",
"AMOUNT": 20.00
}
]
},
{
"NUMBER": 4,
"EXPENSE": [
{
"WHAT": "Beer",
"AMOUNT": 19.00
},
{
"WHAT": "Beer",
"AMOUNT": 16.00
},
{
"WHAT": "Food",
"AMOUNT": 17.00
},
{
"WHAT": "Food",
"AMOUNT": 17.00
},
{
"WHAT": "Beer",
"AMOUNT": 14.00
}
]
},
{
"NUMBER": 5,
"EXPENSE": [
{
"WHAT": "Beer",
"AMOUNT": 14.00
},
{
"WHAT": "Food",
"AMOUNT": 12.00
}
]
}
]
},
{
"WHO": "Beth",
"WEEK": [
{
"NUMBER": 3,
"EXPENSE": [
{
"WHAT": "Beer",
"AMOUNT": 16.00
}
]
},
{
"NUMBER": 4,
"EXPENSE": [
{
"WHAT": "Food",
"AMOUNT": 17.00
},
{
"WHAT": "Beer",
"AMOUNT": 15.00
}
]
},
{
"NUMBER": 5,
"EXPENSE": [
{
"WHAT": "Food",
"AMOUNT": 12.00
},
{
"WHAT": "Beer",
"AMOUNT": 20.00
}
]
}
]
},
{
"WHO": "Janet",
"WEEK": [
{
"NUMBER": 3,
"EXPENSE": [
{
"WHAT": "Car",
"AMOUNT": 19.00
},
{
"WHAT": "Food",
"AMOUNT": 18.00
},
{
"WHAT": "Beer",
"AMOUNT": 18.00
}
]
},
{
"NUMBER": 4,
"EXPENSE": [
{
"WHAT": "Car",
"AMOUNT": 17.00
}
]
},
{
"NUMBER": 5,
"EXPENSE": [
{
"WHAT": "Beer",
"AMOUNT": 14.00
},
{
"WHAT": "Car",
"AMOUNT": 12.00
},
{
"WHAT": "Beer",
"AMOUNT": 19.00
},
{
"WHAT": "Food",
"AMOUNT": 12.00
}
]
}
]
}
]
OEM example
This is an example showing how an OEM table can be implemented. It is out of the scope of this document to explain how it works and to be a full guide on writing OEM tables for CONNECT.
tabfic.h
The header File tabfic.h:
// TABFIC.H Olivier Bertrand 2008-2010
// External table type to read FIC files
#define TYPE_AM_FIC (AMT)129
typedef class FICDEF *PFICDEF;
typedef class TDBFIC *PTDBFIC;
typedef class FICCOL *PFICCOL;
/* ------------------------- FIC classes ------------------------- */
/*******************************************************************/
/* FIC: OEM table to read FIC files. */
/*******************************************************************/
/*******************************************************************/
/* This function is exported from the Tabfic.dll */
/*******************************************************************/
extern "C" PTABDEF __stdcall GetFIC(PGLOBAL g, void *memp);
/*******************************************************************/
/* FIC table definition class. */
/*******************************************************************/
class FICDEF : public DOSDEF { /* Logical table description */
friend class TDBFIC;
public:
// Constructor
FICDEF(void) {Pseudo = 3;}
// Implementation
virtual const char *GetType(void) {return "FIC";}
// Methods
virtual BOOL DefineAM(PGLOBAL g, LPCSTR am, int poff);
virtual PTDB GetTable(PGLOBAL g, MODE m);
protected:
// No Members
}; // end of class FICDEF
/*******************************************************************/
/* This is the class declaration for the FIC table. */
/*******************************************************************/
class TDBFIC : public TDBFIX {
friend class FICCOL;
public:
// Constructor
TDBFIC(PFICDEF tdp);
// Implementation
virtual AMT GetAmType(void) {return TYPE_AM_FIC;}
// Methods
virtual void ResetDB(void);
virtual int RowNumber(PGLOBAL g, BOOL b = FALSE);
// Database routines
virtual PCOL MakeCol(PGLOBAL g, PCOLDEF cdp, PCOL cprec, int n);
virtual BOOL OpenDB(PGLOBAL g, PSQL sqlp);
virtual int ReadDB(PGLOBAL g);
virtual int WriteDB(PGLOBAL g);
virtual int DeleteDB(PGLOBAL g, int irc);
protected:
// Members
int ReadMode; // To read soft deleted lines
int Rows; // Used for RowID
}; // end of class TDBFIC
/*******************************************************************/
/* Class FICCOL: for Monetary columns. */
/*******************************************************************/
class FICCOL : public DOSCOL {
public:
// Constructors
FICCOL(PGLOBAL g, PCOLDEF cdp, PTDB tdbp,
PCOL cprec, int i, PSZ am = "FIC");
// Implementation
virtual int GetAmType(void) {return TYPE_AM_FIC;}
// Methods
virtual void ReadColumn(PGLOBAL g);
protected:
// Members
char Fmt; // The column format
}; // end of class FICCOL
tabfic.cpp
The source File tabfic.cpp:
/*******************************************************************/
/* FIC: OEM table to read FIC files. */
/*******************************************************************/
#if defined(WIN32)
#define WIN32_LEAN_AND_MEAN // Exclude rarely-used stuff
#include <windows.h>
#endif // WIN32
#include "global.h"
#include "plgdbsem.h"
#include "reldef.h"
#include "filamfix.h"
#include "tabfix.h"
#include "tabfic.h"
int TDB::Tnum;
int DTVAL::Shift;
/*******************************************************************/
/* Initialize the CSORT static members. */
/*******************************************************************/
int CSORT::Limit = 0;
double CSORT::Lg2 = log(2.0);
size_t CSORT::Cpn[1000] = {0}; /* Precalculated cmpnum values */
/* ------------- Implementation of the FIC subtype --------------- */
/*******************************************************************/
/* This function is exported from the DLL. */
/*******************************************************************/
PTABDEF __stdcall GetFIC(PGLOBAL g, void *memp)
{
return new(g, memp) FICDEF;
} // end of GetFIC
/* -------------- Implementation of the FIC classes -------------- */
/*******************************************************************/
/* DefineAM: define specific AM block values from FIC file. */
/*******************************************************************/
BOOL FICDEF::DefineAM(PGLOBAL g, LPCSTR am, int poff)
{
ReadMode = GetIntCatInfo("Readmode", 0);
// Indicate that we are a BIN format
return DOSDEF::DefineAM(g, "BIN", poff);
} // end of DefineAM
/*******************************************************************/
/* GetTable: makes a new TDB of the proper type. */
/*******************************************************************/
PTDB FICDEF::GetTable(PGLOBAL g, MODE m)
{
return new(g) TDBFIC(this);
} // end of GetTable
/* --------------------------------------------------------------- */
/*******************************************************************/
/* Implementation of the TDBFIC class. */
/*******************************************************************/
TDBFIC::TDBFIC(PFICDEF tdp) : TDBFIX(tdp, NULL)
{
ReadMode = tdp->ReadMode;
Rows = 0;
} // end of TDBFIC constructor
/*******************************************************************/
/* Allocate FIC column description block. */
/*******************************************************************/
PCOL TDBFIC::MakeCol(PGLOBAL g, PCOLDEF cdp, PCOL cprec, int n)
{
PCOL colp;
// BINCOL is alright except for the Monetary format
if (cdp->GetFmt() && toupper(*cdp->GetFmt()) == 'M')
colp = new(g) FICCOL(g, cdp, this, cprec, n);
else
colp = new(g) BINCOL(g, cdp, this, cprec, n);
return colp;
} // end of MakeCol
/*******************************************************************/
/* RowNumber: return the ordinal number of the current row. */
/*******************************************************************/
int TDBFIC::RowNumber(PGLOBAL g, BOOL b)
{
return (b) ? Txfp->GetRowID() : Rows;
} // end of RowNumber
/*******************************************************************/
/* FIC Access Method reset table for re-opening. */
/*******************************************************************/
void TDBFIC::ResetDB(void)
{
Rows = 0;
TDBFIX::ResetDB();
} // end of ResetDB
/*******************************************************************/
/* FIC Access Method opening routine. */
/*******************************************************************/
BOOL TDBFIC::OpenDB(PGLOBAL g, PSQL sqlp)
{
if (Use == USE_OPEN) {
// Table already open, just replace it at its beginning.
return TDBFIX::OpenDB(g);
} // endif use
if (Mode != MODE_READ) {
// Currently FIC tables cannot be modified.
strcpy(g->Message, "FIC tables are read only");
return TRUE;
} // endif Mode
/*****************************************************************/
/* Physically open the FIC file. */
/*****************************************************************/
if (TDBFIX::OpenDB(g))
return TRUE;
Use = USE_OPEN;
return FALSE;
} // end of OpenDB
/*******************************************************************/
/* ReadDB: Data Base read routine for FIC access method. */
/*******************************************************************/
int TDBFIC::ReadDB(PGLOBAL g)
{
int rc;
/*****************************************************************/
/* Now start the reading process. */
/*****************************************************************/
do {
rc = TDBFIX::ReadDB(g);
} while (rc == RC_OK && ((ReadMode == 0 && *To_Line == '*') ||
(ReadMode == 2 && *To_Line != '*')));
Rows++;
return rc;
} // end of ReadDB
/*******************************************************************/
/* WriteDB: Data Base write routine for FIC access methods. */
/*******************************************************************/
int TDBFIC::WriteDB(PGLOBAL g)
{
strcpy(g->Message, "FIC tables are read only");
return RC_FX;
} // end of WriteDB
/*******************************************************************/
/* Data Base delete line routine for FIC access methods. */
/*******************************************************************/
int TDBFIC::DeleteDB(PGLOBAL g, int irc)
{
strcpy(g->Message, "Delete not enabled for FIC tables");
return RC_FX;
} // end of DeleteDB
// ---------------------- FICCOL functions --------------------------
/*******************************************************************/
/* FICCOL public constructor. */
/*******************************************************************/
FICCOL::FICCOL(PGLOBAL g, PCOLDEF cdp, PTDB tdbp, PCOL cprec, int i,
PSZ am) : DOSCOL(g, cdp, tdbp, cprec, i, am)
{
// Set additional FIC access method information for column.
Fmt = toupper(*cdp->GetFmt()); // Column format
} // end of FICCOL constructor
/*******************************************************************/
/* Handle the monetary value of this column. It is a big integer */
/* that represents the value multiplied by 1000. */
/* In this function we translate it to a double float value. */
/*******************************************************************/
void FICCOL::ReadColumn(PGLOBAL g)
{
char *p;
int rc;
uint n;
double fmon;
PTDBFIC tdbp = (PTDBFIC)To_Tdb;
/*****************************************************************/
/* If physical reading of the line was deferred, do it now. */
/*****************************************************************/
if (!tdbp->IsRead())
if ((rc = tdbp->ReadBuffer(g)) != RC_OK) {
if (rc == RC_EF)
sprintf(g->Message, MSG(INV_DEF_READ), rc);
longjmp(g->jumper[g->jump_level], 11);
} // endif
p = tdbp->To_Line + Deplac;
/*****************************************************************/
/* Set Value from the line field. */
/*****************************************************************/
if (*(SHORT*)(p + 8) < 0) {
n = ~*(SHORT*)(p + 8);
fmon = (double)n;
fmon *= 4294967296.0;
n = ~*(int*)(p + 4);
fmon += (double)n;
fmon *= 4294967296.0;
n = ~*(int*)p;
fmon += (double)n;
fmon++;
fmon /= 1000000.0;
fmon = -fmon;
} else {
fmon = ((double)*(USHORT*)(p + 8));
fmon *= 4294967296.0;
fmon += ((double)*(ULONG*)(p + 4));
fmon *= 4294967296.0;
fmon += ((double)*(ULONG*)p);
fmon /= 1000000.0;
} // enif neg
Value->SetValue(fmon);
} // end of ReadColumn
tabfic.def
The file tabfic.def: (required only on Windows)
LIBRARY TABFIC DESCRIPTION 'FIC files' EXPORTS GetFIC @1
JSON UDFs in a separate library
Although the JSON UDF’s can be nicely included in the CONNECT library module, there are cases when you may need to have them in a separate library.
This is when CONNECT is compiled embedded, or if you want to test or use these UDF’s with other MariaDB versions not including them.
To make it, you need to have access to the last MariaDB source code. Then, make a project containing these files:
- jsonudf.cpp
- json.cpp
- value.cpp
- osutil.c
- plugutil.c
- maputil.cpp
- jsonutil.cpp
jsonutil.cpp is not distributed with the source code, you will have to make it from the following:
#include "my_global.h"
#include "mysqld.h"
#include "plugin.h"
#include <stdlib.h>
#include <string.h>
#include <stdio.h>
#include <errno.h>
#include "global.h"
extern "C" int GetTraceValue(void) { return 0; }
uint GetJsonGrpSize(void) { return 100; }
/***********************************************************************/
/* These replace missing function of the (not used) DTVAL class. */
/***********************************************************************/
typedef struct _datpar *PDTP;
PDTP MakeDateFormat(PGLOBAL, PSZ, bool, bool, int) { return NULL; }
int ExtractDate(char*, PDTP, int, int val[6]) { return 0; }
#ifdef __WIN__
my_bool CloseFileHandle(HANDLE h)
{
return !CloseHandle(h);
} /* end of CloseFileHandle */
#else /* UNIX */
my_bool CloseFileHandle(HANDLE h)
{
return (close(h)) ? TRUE : FALSE;
} /* end of CloseFileHandle */
int GetLastError()
{
return errno;
} /* end of GetLastError */
#endif // UNIX
/***********************************************************************/
/* Program for sub-allocating one item in a storage area. */
/* Note: This function is equivalent to PlugSubAlloc except that in */
/* case of insufficient memory, it returns NULL instead of doing a */
/* long jump. The caller must test the return value for error. */
/***********************************************************************/
void *PlgDBSubAlloc(PGLOBAL g, void *memp, size_t size)
{
PPOOLHEADER pph; // Points on area header.
if (!memp) // Allocation is to be done in the Sarea
memp = g->Sarea;
size = ((size + 7) / 8) * 8; /* Round up size to multiple of 8 */
pph = (PPOOLHEADER)memp;
if ((uint)size > pph->FreeBlk) { /* Not enough memory left in pool */
sprintf(g->Message,
"Not enough memory in Work area for request of %d (used=%d free=%d)",
(int)size, pph->To_Free, pph->FreeBlk);
return NULL;
} // endif size
// Do the suballocation the simplest way
memp = MakePtr(memp, pph->To_Free); // Points to sub_allocated block
pph->To_Free += size; // New offset of pool free block
pph->FreeBlk -= size; // New size of pool free block
return (memp);
} // end of PlgDBSubAlloc
You can create the file by copy/paste from the above.
Set all the additional include directories to the MariaDB include directories used in plugin compiling plus the reference of the storage/connect directories, and compile like any other UDF giving any name to the made library module (I used jsonudf.dll on Windows)
Then you can create the functions using this name as the soname parameter.
There are some restrictions when using the UDF’s this way:
- The connect_json_grp_size variable cannot be accessed. The group size is set to 100.
- In case of error, warnings are replaced by messages sent to stderr.
- No trace.
Content reproduced on this site is the property of its respective owners,
and this content is not reviewed in advance by MariaDB. The views, information and opinions
expressed by this content do not necessarily represent those of MariaDB or any other party.
