static void
FITSinitCatalog(mvc *m)
{
sql_schema *sch;
sql_table *fits_tp, *fits_fl, *fits_tbl, *fits_col;
sch = mvc_bind_schema(m, "sys");
fits_fl = mvc_bind_table(m, sch, "fits_files");
if (fits_fl == NULL) {
fits_fl = mvc_create_table(m, sch, "fits_files", tt_table, 0, SQL_PERSIST, 0, 2);
mvc_create_column_(m, fits_fl, "id", "int", 32);
mvc_create_column_(m, fits_fl, "name", "varchar", 80);
}
fits_tbl = mvc_bind_table(m, sch, "fits_tables");
if (fits_tbl == NULL) {
fits_tbl = mvc_create_table(m, sch, "fits_tables", tt_table, 0, SQL_PERSIST, 0, 8);
mvc_create_column_(m, fits_tbl, "id", "int", 32);
mvc_create_column_(m, fits_tbl, "name", "varchar", 80);
mvc_create_column_(m, fits_tbl, "columns", "int", 32);
mvc_create_column_(m, fits_tbl, "file_id", "int", 32);
mvc_create_column_(m, fits_tbl, "hdu", "int", 32);
mvc_create_column_(m, fits_tbl, "date", "varchar", 80);
mvc_create_column_(m, fits_tbl, "origin", "varchar", 80);
mvc_create_column_(m, fits_tbl, "comment", "varchar", 80);
}
fits_col = mvc_bind_table(m, sch, "fits_columns");
if (fits_col == NULL) {
fits_col = mvc_create_table(m, sch, "fits_columns", tt_table, 0, SQL_PERSIST, 0, 6);
mvc_create_column_(m, fits_col, "id", "int", 32);
mvc_create_column_(m, fits_col, "name", "varchar", 80);
mvc_create_column_(m, fits_col, "type", "varchar", 80);
mvc_create_column_(m, fits_col, "units", "varchar", 10);
mvc_create_column_(m, fits_col, "number", "int", 32);
mvc_create_column_(m, fits_col, "table_id", "int", 32);
}
fits_tp = mvc_bind_table(m, sch, "fits_table_properties");
if (fits_tp == NULL) {
fits_tp = mvc_create_table(m, sch, "fits_table_properties", tt_table, 0, SQL_PERSIST, 0, 5);
mvc_create_column_(m, fits_tp, "table_id", "int", 32);
mvc_create_column_(m, fits_tp, "xtension", "varchar", 80);
mvc_create_column_(m, fits_tp, "bitpix", "int", 32);
mvc_create_column_(m, fits_tp, "stilvers", "varchar", 80);
mvc_create_column_(m, fits_tp, "stilclas", "varchar", 80);
}
}
static sql_rel*
create_table_from_loader(mvc *sql, dlist *qname, symbol *fcall)
{
sql_schema *s = NULL;
char *sname = qname_schema(qname);
char *tname = qname_table(qname);
sql_subfunc *loader = NULL;
sql_rel* rel = NULL;
if (sname && !(s = mvc_bind_schema(sql, sname)))
return sql_error(sql, 02, SQLSTATE(3F000) "CREATE TABLE: no such schema '%s'", sname);
if (mvc_bind_table(sql, s, tname)) {
return sql_error(sql, 02, SQLSTATE(42S01) "CREATE TABLE: name '%s' already in use", tname);
} else if (!mvc_schema_privs(sql, s)){
return sql_error(sql, 02, SQLSTATE(42000) "CREATE TABLE: insufficient privileges for user '%s' in schema '%s'", stack_get_string(sql, "current_user"), s->base.name);
}
rel = rel_loader_function(sql, fcall, new_exp_list(sql->sa), &loader);
if (!rel || !loader) {
return NULL;
}
loader->sname = sname ? sa_zalloc(sql->sa, strlen(sname) + 1) : NULL;
loader->tname = tname ? sa_zalloc(sql->sa, strlen(tname) + 1) : NULL;
if (sname) strcpy(loader->sname, sname);
if (tname) strcpy(loader->tname, tname);
return rel;
}
static void
sql_insert_priv(mvc *sql, int auth_id, int obj_id, int privilege, int grantor, int grantable)
{
sql_schema *ss = mvc_bind_schema(sql, "sys");
sql_table *pt = mvc_bind_table(sql, ss, "privileges");
table_funcs.table_insert(sql->session->tr, pt, &obj_id, &auth_id, &privilege, &grantor, &grantable);
}
static lng
SQLgetSpace(mvc *m, MalBlkPtr mb)
{
sql_trans *tr = m->session->tr;
lng space = 0, i;
for (i = 0; i < mb->stop; i++) {
InstrPtr p = mb->stmt[i];
char *f = getFunctionId(p);
if (getModuleId(p) == sqlRef && (f == bindRef || f == bindidxRef)) {
int upd = (p->argc == 7 || p->argc == 9), mode = 0;
char *sname = getVarConstant(mb, getArg(p, 2 + upd)).val.sval;
char *tname = getVarConstant(mb, getArg(p, 3 + upd)).val.sval;
char *cname = NULL;
sql_schema *s = mvc_bind_schema(m, sname);
if (!s || strcmp(s->base.name, dt_schema) == 0)
continue;
cname = getVarConstant(mb, getArg(p, 4 + upd)).val.sval;
mode = getVarConstant(mb, getArg(p, 5 + upd)).val.ival;
if (mode != 0 || !cname || !s)
continue;
if (f == bindidxRef) {
sql_idx *i = mvc_bind_idx(m, s, cname);
if (i && (!isRemote(i->t) && !isMergeTable(i->t))) {
BAT *b = store_funcs.bind_idx(tr, i, RDONLY);
if (b) {
space += getBatSpace(b);
BBPunfix(b->batCacheid);
}
}
} else if (f == bindRef) {
sql_table *t = mvc_bind_table(m, s, tname);
sql_column *c = mvc_bind_column(m, t, cname);
if (c && (!isRemote(c->t) && !isMergeTable(c->t))) {
BAT *b = store_funcs.bind_col(tr, c, RDONLY);
if (b) {
space += getBatSpace(b);
BBPunfix(b->batCacheid);
}
}
}
}
}
return space;
}
char *
sql_revoke_table_privs( mvc *sql, char *grantee, int privs, char *sname, char *tname, char *cname, int grant, int grantor)
{
sql_schema *s = NULL;
sql_table *t = NULL;
sql_column *c = NULL;
int allowed, grantee_id;
int all = PRIV_SELECT | PRIV_UPDATE | PRIV_INSERT | PRIV_DELETE;
if (sname)
s = mvc_bind_schema(sql, sname);
if (s)
t = mvc_bind_table(sql, s, tname);
if (!t)
return sql_message("42S02!REVOKE: no such table '%s'", tname);
allowed = schema_privs(grantor, t->s);
if (!allowed)
allowed = sql_grantable(sql, grantor, t->base.id, all, 0);
if (!allowed)
return sql_message("0L000!REVOKE: grantor '%s' is not allowed to revoke privileges for table '%s'", stack_get_string(sql,"current_user"), tname);
if (cname) {
c = mvc_bind_column(sql, t, cname);
if (!c)
return sql_message("42S22!REVOKE: table %s has no column %s", tname, cname);
/* allowed on column */
if (!allowed)
allowed = sql_grantable(sql, grantor, c->base.id, privs, 0);
if (!allowed)
return sql_message("0L000!REVOKE: grantor %s is not allowed to revoke privilege %s for table %s", stack_get_string(sql, "current_user"), priv2string(privs), tname);
}
grantee_id = sql_find_auth(sql, grantee);
if (grantee_id <= 0)
return sql_message("42M32!REVOKE: user/role '%s' unknown", grantee);
if (privs == all) {
sql_delete_priv(sql, grantee_id, t->base.id, PRIV_SELECT, grantor, grant);
sql_delete_priv(sql, grantee_id, t->base.id, PRIV_UPDATE, grantor, grant);
sql_delete_priv(sql, grantee_id, t->base.id, PRIV_INSERT, grantor, grant);
sql_delete_priv(sql, grantee_id, t->base.id, PRIV_DELETE, grantor, grant);
} else if (!c)
sql_insert_priv(sql, grantee_id, t->base.id, privs, grantor, grant);
else
sql_insert_priv(sql, grantee_id, c->base.id, privs, grantor, grant);
return NULL;
}
str
bind_table(mvc * m, sql_schema * s,
str tablename, sql_table ** ret)
{
sql_table *result;
if ((result = mvc_bind_table(m, s, tablename)) == NULL) {
throw(MAL, "bind_table",
"Could not find table %s", tablename);
}
if (ret)
*ret = result;
return MAL_SUCCEED;
}
static void
sql_delete_priv(mvc *sql, int auth_id, int obj_id, int privilege, int grantor, int grantable)
{
sql_schema *ss = mvc_bind_schema(sql, "sys");
sql_table *privs = mvc_bind_table(sql, ss, "privileges");
sql_column *priv_obj = find_sql_column(privs, "obj_id");
sql_column *priv_auth = find_sql_column(privs, "auth_id");
sql_column *priv_priv = find_sql_column(privs, "privileges");
sql_trans *tr = sql->session->tr;
rids *A;
oid rid = oid_nil;
(void) grantor;
(void) grantable;
/* select privileges of this auth_id, privilege, obj_id */
A = table_funcs.rids_select(tr, priv_auth, &auth_id, &auth_id, priv_priv, &privilege, &privilege, priv_obj, &obj_id, &obj_id, NULL );
/* remove them */
for(rid = table_funcs.rids_next(A); rid != oid_nil; rid = table_funcs.rids_next(A))
table_funcs.table_delete(tr, privs, rid);
table_funcs.rids_destroy(A);
}
sql_table *
mvc_bind_table(mvc *m, sql_schema *s, const char *tname)
{
sql_table *t = NULL;
if (!s) { /* Declared tables during query compilation have no schema */
sql_table *tpe = stack_find_table(m, tname);
if (tpe) {
t = tpe;
} else { /* during exection they are in the declared table schema */
s = mvc_bind_schema(m, dt_schema);
return mvc_bind_table(m, s, tname);
}
} else {
t = find_sql_table(s, tname);
}
if (!t)
return NULL;
if (mvc_debug)
fprintf(stderr, "#mvc_bind_table %s.%s\n", s ? s->base.name : "<noschema>", tname);
return t;
}
str FITSexportTable(Client cntxt, MalBlkPtr mb, MalStkPtr stk, InstrPtr pci)
{
str msg = MAL_SUCCEED;
str tname = *(str*) getArgReference(stk, pci, 1);
mvc *m = NULL;
sql_trans *tr;
sql_schema *sch;
sql_table *tbl, *column, *tables = NULL;
sql_column *col;
oid rid = oid_nil;
str type, name, *colname, *tform;
fitsfile *fptr;
char filename[BUFSIZ];
long nrows = 0, optimal;
rids * rs;
int tm0, texportboolean=0, texportchar=0, texportstring=0, texportshort=0, texportint=0, texportlong=0, texportfloat=0, texportdouble=0;
int numberrow = 0, cc = 0, status = 0, j = 0, columns, fid, dimension = 0, block = 0;
int boolcols = 0, charcols = 0, strcols = 0, shortcols = 0, intcols = 0, longcols = 0, floatcols = 0, doublecols = 0;
int hdutype;
char charvalue, *readcharrows;
str strvalue; char **readstrrows;
short shortvalue, *readshortrows;
int intvalue, *readintrows;
long longvalue, *readlongrows;
float realvalue, *readfloatrows;
double doublevalue, *readdoublerows;
_Bool boolvalue, *readboolrows;
struct list * set;
msg = getSQLContext(cntxt, mb, &m, NULL);
if (msg)
return msg;
tr = m->session->tr;
sch = mvc_bind_schema(m, "sys");
/* First step: look if the table exists in the database. If the table is not in the database, the export function cannot continue */
tbl = mvc_bind_table(m, sch, tname);
if (tbl == NULL) {
msg = createException (MAL, "fits.exporttable", "Table %s is missing.\n", tname);
return msg;
}
set = (*tbl).columns.set;
columns = list_length(set);
colname = (str *) GDKmalloc(columns * sizeof(str));
tform = (str *) GDKmalloc(columns * sizeof(str));
/* mnstr_printf(GDKout,"Number of columns: %d\n", columns);*/
tables = mvc_bind_table(m, sch, "_tables");
col = mvc_bind_column(m, tables, "name");
rid = table_funcs.column_find_row(m->session->tr, col, tname, NULL);
col = mvc_bind_column(m, tables, "id");
fid = *(int*) table_funcs.column_find_value(m->session->tr, col, rid);
column = mvc_bind_table(m, sch, "_columns");
col = mvc_bind_column(m, column, "table_id");
rs = table_funcs.rids_select(m->session->tr, col, (void *) &fid, (void *) &fid, NULL);
while ((rid = table_funcs.rids_next(rs)) != oid_nil)
{
col = mvc_bind_column(m, column, "name");
name = (char *) table_funcs.column_find_value(m->session->tr, col, rid);
colname[j] = toLower(name);
col = mvc_bind_column(m, column, "type");
type = (char *) table_funcs.column_find_value(m->session->tr, col, rid);
if (strcmp(type,"boolean")==0) tform[j] = "1L";
if (strcmp(type,"char")==0) tform[j] = "1S";
if (strcmp(type,"varchar")==0) tform[j] = "8A";
if (strcmp(type,"smallint")==0) tform[j] = "1I";
if (strcmp(type,"int")==0) tform[j] = "1J";
if (strcmp(type,"bigint")==0) tform[j] = "1K";
if (strcmp(type,"real")==0) tform[j] = "1E";
if (strcmp(type,"double")==0) tform[j] = "1D";
j++;
}
col = mvc_bind_column(m, tbl, colname[0]);
nrows = store_funcs.count_col(tr, col, 1);
snprintf(filename,BUFSIZ,"\n%s.fit",tname);
mnstr_printf(GDKout, "Filename: %s\n", filename);
remove(filename);
status=0;
fits_create_file(&fptr, filename, &status);
fits_create_img(fptr, USHORT_IMG, 0, NULL, &status);
fits_close_file(fptr, &status);
fits_open_file(&fptr, filename, READWRITE, &status);
fits_movabs_hdu(fptr, 1, &hdutype, &status);
fits_create_tbl( fptr, BINARY_TBL, 0, columns, colname, tform, NULL, tname, &status);
for (cc = 0; cc < columns; cc++)
{
char * columntype;
col = mvc_bind_column(m, tbl, colname[cc]);
columntype = col -> type.type->sqlname;
if (strcmp(columntype,"boolean")==0)
{
boolcols++; dimension = 0; block = 0;
fits_get_rowsize(fptr,&optimal,&status);
readboolrows = (_Bool *) GDKmalloc (sizeof(_Bool) * optimal);
for (numberrow = 0; numberrow < nrows ; numberrow++)
{
boolvalue = *(_Bool*) table_funcs.column_find_value(m->session->tr, col, numberrow);
readboolrows[dimension] = boolvalue;
dimension++;
if (dimension == optimal)
{
dimension = 0;
tm0 = GDKms();
fits_write_col(fptr, TLOGICAL, cc+1, (optimal*block)+1, 1, optimal, readboolrows, &status);
texportboolean += GDKms() - tm0;
GDKfree(readboolrows);
readboolrows = (_Bool *) GDKmalloc (sizeof(_Bool) * optimal);
block++;
}
}
tm0 = GDKms();
fits_write_col(fptr, TLOGICAL, cc+1, (optimal*block)+1, 1, dimension, readboolrows, &status);
texportboolean += GDKms() - tm0;
GDKfree(readboolrows);
}
if (strcmp(columntype,"char")==0)
{
charcols++; dimension = 0; block = 0;
fits_get_rowsize(fptr,&optimal,&status);
readcharrows = (char *) GDKmalloc (sizeof(char) * optimal);
for (numberrow = 0; numberrow < nrows ; numberrow++)
{
charvalue = *(char*) table_funcs.column_find_value(m->session->tr, col, numberrow);
readcharrows[dimension] = charvalue;
dimension++;
if (dimension == optimal)
{
dimension = 0;
tm0 = GDKms();
fits_write_col(fptr, TBYTE, cc+1, (optimal*block)+1, 1, optimal, readcharrows, &status);
texportchar += GDKms() - tm0;
GDKfree(readcharrows);
readcharrows = (char *) GDKmalloc (sizeof(char) * optimal);
block++;
}
}
tm0 = GDKms();
fits_write_col(fptr, TBYTE, cc+1, (optimal*block)+1, 1, dimension, readcharrows, &status);
texportchar += GDKms() - tm0;
GDKfree(readcharrows);
}
if (strcmp(columntype,"varchar")==0)
{
strcols++; dimension=0; block=0;
fits_get_rowsize(fptr,&optimal,&status);
readstrrows = (char **) GDKmalloc (sizeof(char *) * optimal);
for (numberrow = 0; numberrow < nrows ; numberrow++)
{
strvalue = (char *) table_funcs.column_find_value(m->session->tr, col, numberrow);
readstrrows[dimension] = strvalue;
dimension++;
if (dimension == optimal)
{
dimension = 0;
tm0 = GDKms();
fits_write_col_str(fptr, cc+1, (optimal*block)+1, 1, optimal, readstrrows, &status);
texportstring += GDKms() - tm0;
GDKfree(readstrrows);
readstrrows = (char **) GDKmalloc(sizeof(char *) * optimal);
block++;
}
}
tm0 = GDKms();
fits_write_col_str(fptr, cc+1, (optimal*block)+1, 1, dimension, readstrrows, &status);
texportstring += GDKms() - tm0;
GDKfree(readstrrows);
}
if (strcmp(columntype,"smallint")==0)
{
shortcols++; dimension = 0; block = 0;
fits_get_rowsize(fptr,&optimal,&status);
readshortrows = (short *) GDKmalloc (sizeof(short) * optimal);
for (numberrow = 0; numberrow < nrows ; numberrow++)
{
shortvalue = *(short*) table_funcs.column_find_value(m->session->tr, col, numberrow);
readshortrows[dimension] = shortvalue;
dimension++;
if (dimension == optimal)
{
dimension = 0;
tm0 = GDKms();
fits_write_col(fptr, TSHORT, cc+1, (optimal*block)+1, 1, optimal, readshortrows, &status);
texportshort += GDKms() - tm0;
GDKfree(readshortrows);
readshortrows = (short *) GDKmalloc (sizeof(short) * optimal);
block++;
}
}
tm0 = GDKms();
fits_write_col(fptr, TSHORT, cc+1, (optimal*block)+1, 1, dimension, readshortrows, &status);
texportshort += GDKms() - tm0;
GDKfree(readshortrows);
}
if (strcmp(columntype,"int")==0)
{
intcols++; dimension = 0; block = 0;
fits_get_rowsize(fptr,&optimal,&status);
readintrows = (int *) GDKmalloc (sizeof(int) * optimal);
for (numberrow = 0; numberrow < nrows ; numberrow++)
{
intvalue = *(int*) table_funcs.column_find_value(m->session->tr, col, numberrow);
readintrows[dimension] = intvalue;
dimension++;
if (dimension == optimal)
{
dimension = 0;
tm0 = GDKms();
fits_write_col(fptr, TINT, cc+1, (optimal*block)+1, 1, optimal, readintrows, &status);
texportint += GDKms() - tm0;
GDKfree(readintrows);
readintrows = (int *) GDKmalloc (sizeof(int) * optimal);
block++;
}
}
tm0 = GDKms();
fits_write_col(fptr, TINT, cc+1, (optimal*block)+1, 1, dimension, readintrows, &status);
texportint += GDKms() - tm0;
GDKfree(readintrows);
}
if (strcmp(columntype,"bigint")==0)
{
longcols++; dimension = 0; block = 0;
fits_get_rowsize(fptr,&optimal,&status);
readlongrows = (long *) GDKmalloc (sizeof(long) * optimal);
for (numberrow = 0; numberrow < nrows ; numberrow++)
{
longvalue = *(long*) table_funcs.column_find_value(m->session->tr, col, numberrow);
readlongrows[dimension] = longvalue;
dimension++;
if (dimension == optimal)
{
dimension = 0;
tm0 = GDKms();
fits_write_col(fptr, TLONG, cc+1, (optimal*block)+1, 1, optimal, readlongrows, &status);
texportlong += GDKms() - tm0;
GDKfree(readlongrows);
readlongrows = (long *) GDKmalloc (sizeof(long) * optimal);
block++;
}
}
tm0 = GDKms();
fits_write_col(fptr, TLONG, cc+1, (optimal*block)+1, 1, dimension, readlongrows, &status);
texportlong += GDKms() - tm0;
GDKfree(readlongrows);
}
if (strcmp(columntype,"real")==0)
{
floatcols++; dimension = 0; block = 0;
fits_get_rowsize(fptr,&optimal,&status);
readfloatrows = (float *) GDKmalloc (sizeof(float) * optimal);
for (numberrow = 0; numberrow < nrows ; numberrow++)
{
realvalue = *(float*) table_funcs.column_find_value(m->session->tr, col, numberrow);
readfloatrows[dimension] = realvalue;
dimension++;
if (dimension == optimal)
{
dimension = 0;
tm0 = GDKms();
fits_write_col(fptr, TFLOAT, cc+1, (optimal*block)+1, 1, optimal, readfloatrows, &status);
texportfloat += GDKms() - tm0;
GDKfree(readfloatrows);
readfloatrows = (float *) GDKmalloc (sizeof(float) * optimal);
block++;
}
}
tm0 = GDKms();
fits_write_col(fptr, TFLOAT, cc+1, (optimal*block)+1, 1, dimension, readfloatrows, &status);
texportfloat += GDKms() - tm0;
GDKfree(readfloatrows);
}
if (strcmp(columntype,"double")==0)
{
doublecols++; dimension = 0; block = 0;
fits_get_rowsize(fptr,&optimal,&status);
readdoublerows = (double *) GDKmalloc (sizeof(double) * optimal);
for (numberrow = 0; numberrow < nrows ; numberrow++)
{
doublevalue = *(double*) table_funcs.column_find_value(m->session->tr, col, numberrow);
readdoublerows[dimension] = doublevalue;
dimension++;
if (dimension == optimal)
{
dimension = 0;
tm0 = GDKms();
fits_write_col(fptr, TDOUBLE, cc+1, (optimal*block)+1, 1, optimal, readdoublerows, &status);
texportdouble += GDKms() - tm0;
GDKfree(readdoublerows);
readdoublerows = (double *) GDKmalloc (sizeof(double) * optimal);
block++;
}
}
tm0 = GDKms();
fits_write_col(fptr, TDOUBLE, cc+1, (optimal*block)+1, 1, optimal, readdoublerows, &status);
texportdouble += GDKms() - tm0;
GDKfree(readdoublerows);
}
}
/* print all the times that were needed to export each one of the columns
mnstr_printf(GDKout, "\n\n");
if (texportboolean > 0) mnstr_printf(GDKout, "%d Boolean\tcolumn(s) exported in %d ms\n", boolcols, texportboolean);
if (texportchar > 0) mnstr_printf(GDKout, "%d Char\t\tcolumn(s) exported in %d ms\n", charcols, texportchar);
if (texportstring > 0) mnstr_printf(GDKout, "%d String\tcolumn(s) exported in %d ms\n", strcols, texportstring);
if (texportshort > 0) mnstr_printf(GDKout, "%d Short\t\tcolumn(s) exported in %d ms\n", shortcols, texportshort);
if (texportint > 0) mnstr_printf(GDKout, "%d Integer\tcolumn(s) exported in %d ms\n", intcols, texportint);
if (texportlong > 0) mnstr_printf(GDKout, "%d Long\t\tcolumn(s) exported in %d ms\n", longcols, texportlong);
if (texportfloat > 0) mnstr_printf(GDKout, "%d Float\t\tcolumn(s) exported in %d ms\n", floatcols, texportfloat);
if (texportdouble > 0) mnstr_printf(GDKout, "%d Double\tcolumn(s) exported in %d ms\n", doublecols, texportdouble);
*/
fits_close_file(fptr, &status);
return msg;
}
static sql_rel *
create_trigger(mvc *sql, dlist *qname, int time, symbol *trigger_event, char *table_name, dlist *opt_ref, dlist *triggered_action)
{
char *tname = qname_table(qname);
sql_schema *ss = cur_schema(sql);
sql_table *t = NULL;
int instantiate = (sql->emode == m_instantiate);
int create = (!instantiate && sql->emode != m_deps);
list *sq = NULL;
sql_rel *r = NULL;
dlist *columns = trigger_event->data.lval;
char *old_name = NULL, *new_name = NULL;
dlist *stmts = triggered_action->h->next->next->data.lval;
if (opt_ref) {
dnode *dl = opt_ref->h;
for ( ; dl; dl = dl->next) {
/* list (new(1)/old(0)), char */
char *n = dl->data.lval->h->next->data.sval;
assert(dl->data.lval->h->type == type_int);
if (!dl->data.lval->h->data.i_val) /*?l_val?*/
old_name = n;
else
new_name = n;
}
}
if (create && !schema_privs(sql->role_id, ss))
return sql_error(sql, 02, "CREATE TRIGGER: access denied for %s to schema ;'%s'", stack_get_string(sql, "current_user"), ss->base.name);
if (create && mvc_bind_trigger(sql, ss, tname) != NULL)
return sql_error(sql, 02, "CREATE TRIGGER: name '%s' already in use", tname);
if (create && !(t = mvc_bind_table(sql, ss, table_name)))
return sql_error(sql, 02, "CREATE TRIGGER: unknown table '%s'", table_name);
if (create && isView(t))
return sql_error(sql, 02, "CREATE TRIGGER: cannot create trigger on view '%s'", table_name);
if (create) {
int event = (trigger_event->token == SQL_INSERT)?0:
(trigger_event->token == SQL_DELETE)?1:2;
int orientation = triggered_action->h->data.i_val;
char *condition = triggered_action->h->next->data.sval;
char *q = QUERY(sql->scanner);
assert(triggered_action->h->type == type_int);
return rel_create_trigger(sql, t->s->base.name, t->base.name, tname, time, orientation, event, old_name, new_name, condition, q);
}
t = mvc_bind_table(sql, ss, table_name);
stack_push_frame(sql, "OLD-NEW");
/* we need to add the old and new tables */
if (new_name)
_stack_push_table(sql, new_name, t);
if (old_name)
_stack_push_table(sql, old_name, t);
sq = sequential_block(sql, NULL, NULL, stmts, NULL, 1);
r = rel_psm_block(sql->sa, sq);
/* todo trigger_columns */
(void)columns;
return r;
}
static lng
SQLgetSpace(mvc *m, MalBlkPtr mb, int prepare)
{
sql_trans *tr = m->session->tr;
lng size,space = 0, i;
str lasttable = 0;
for (i = 0; i < mb->stop; i++) {
InstrPtr p = mb->stmt[i];
/* now deal with the update binds, it is only necessary to identify that there are updats
* The actual size is not that important */
if (getModuleId(p) == sqlRef && getFunctionId(p) == bindRef && p->retc <= 2){
char *sname = getVarConstant(mb, getArg(p, 1 + p->retc)).val.sval;
char *tname = getVarConstant(mb, getArg(p, 2 + p->retc)).val.sval;
char *cname = getVarConstant(mb, getArg(p, 3 + p->retc)).val.sval;
int access = getVarConstant(mb, getArg(p, 4 + p->retc)).val.ival;
sql_schema *s = mvc_bind_schema(m, sname);
sql_table *t = 0;
sql_column *c = 0;
if (!s || strcmp(s->base.name, dt_schema) == 0)
continue;
t = mvc_bind_table(m, s, tname);
if (!t)
continue;
c = mvc_bind_column(m, t, cname);
if (!s)
continue;
/* we have to sum the cost of all three components of a BAT */
if (c && (!isRemote(c->t) && !isMergeTable(c->t)) && (lasttable == 0 || strcmp(lasttable,tname)==0)) {
size = SQLgetColumnSize(tr, c, access);
space += size; // accumulate once per table
//lasttable = tname; invalidate this attempt
if( !prepare && size == 0 && ! t->system){
//mnstr_printf(GDKout,"found empty column %s.%s.%s prepare %d size "LLFMT"\n",sname,tname,cname,prepare,size);
setFunctionId(p, emptybindRef);
}
}
}
if (getModuleId(p) == sqlRef && (getFunctionId(p) == bindidxRef)) {
char *sname = getVarConstant(mb, getArg(p, 1 + p->retc)).val.sval;
//char *tname = getVarConstant(mb, getArg(p, 2 + p->retc)).val.sval;
char *idxname = getVarConstant(mb, getArg(p, 3 + p->retc)).val.sval;
int access = getVarConstant(mb, getArg(p, 4 + p->retc)).val.ival;
sql_schema *s = mvc_bind_schema(m, sname);
BAT *b;
if (getFunctionId(p) == bindidxRef) {
sql_idx *i = mvc_bind_idx(m, s, idxname);
if (i && (!isRemote(i->t) && !isMergeTable(i->t))) {
b = store_funcs.bind_idx(tr, i, RDONLY);
if (b) {
space += (size =getBatSpace(b));
if (!size) {
sql_column *c = i->t->columns.set->h->data;
size = SQLgetColumnSize(tr, c, access);
}
if( !prepare && size == 0 && ! i->t->system){
setFunctionId(p, emptybindidxRef);
//mnstr_printf(GDKout,"found empty column %s.%s.%s prepare %d size "LLFMT"\n",sname,tname,idxname,prepare,size);
}
BBPunfix(b->batCacheid);
}
}
}
}
}
return space;
}
static void
SQLgetStatistics(Client cntxt, mvc *m, MalBlkPtr mb)
{
InstrPtr *old = NULL;
int oldtop, i, actions = 0, size = 0;
lng clk = GDKusec();
sql_trans *tr = m->session->tr;
str msg;
old = mb->stmt;
oldtop = mb->stop;
size = (mb->stop * 1.2 < mb->ssize) ? mb->ssize : (int) (mb->stop * 1.2);
mb->stmt = (InstrPtr *) GDKzalloc(size * sizeof(InstrPtr));
mb->ssize = size;
mb->stop = 0;
for (i = 0; i < oldtop; i++) {
InstrPtr p = old[i];
char *f = getFunctionId(p);
ValRecord vr;
if (getModuleId(p) == sqlRef && f == tidRef) {
char *sname = getVarConstant(mb, getArg(p, 2)).val.sval;
char *tname = getVarConstant(mb, getArg(p, 3)).val.sval;
sql_schema *s = mvc_bind_schema(m, sname);
sql_table *t;
if (!s || strcmp(s->base.name, dt_schema) == 0) {
pushInstruction(mb, p);
continue;
}
t = mvc_bind_table(m, s, tname);
if (t && (!isRemote(t) && !isMergeTable(t)) && t->p) {
int k = getArg(p, 0), mt_member = t->p->base.id;
varSetProp(mb, k, mtProp, op_eq, VALset(&vr, TYPE_int, &mt_member));
}
}
if (getModuleId(p) == sqlRef && (f == bindRef || f == bindidxRef)) {
int upd = (p->argc == 7 || p->argc == 9);
char *sname = getVarConstant(mb, getArg(p, 2 + upd)).val.sval;
char *tname = getVarConstant(mb, getArg(p, 3 + upd)).val.sval;
char *cname = NULL;
int not_null = 0, mt_member = 0;
wrd rows = 1; /* default to cope with delta bats */
int mode = 0;
int k = getArg(p, 0);
sql_schema *s = mvc_bind_schema(m, sname);
BAT *b;
if (!s || strcmp(s->base.name, dt_schema) == 0) {
pushInstruction(mb, p);
continue;
}
cname = getVarConstant(mb, getArg(p, 4 + upd)).val.sval;
mode = getVarConstant(mb, getArg(p, 5 + upd)).val.ival;
if (s && f == bindidxRef && cname) {
size_t cnt;
sql_idx *i = mvc_bind_idx(m, s, cname);
if (i && (!isRemote(i->t) && !isMergeTable(i->t))) {
cnt = store_funcs.count_idx(tr, i, 1);
assert(cnt <= (size_t) GDK_oid_max);
b = store_funcs.bind_idx(m->session->tr, i, RDONLY);
if (b) {
str loc;
if (b->batPersistence == PERSISTENT && BATlocation(&loc, &b->batCacheid) && loc)
varSetProp(mb, k, fileProp, op_eq, VALset(&vr, TYPE_str, loc));
cnt = BATcount(b);
BBPunfix(b->batCacheid);
}
rows = (wrd) cnt;
if (i->t->p)
mt_member = i->t->p->base.id;
}
} else if (s && f == bindRef && cname) {
size_t cnt;
sql_table *t = mvc_bind_table(m, s, tname);
sql_column *c = mvc_bind_column(m, t, cname);
if (c && (!isRemote(c->t) && !isMergeTable(c->t))) {
not_null = !c->null;
cnt = store_funcs.count_col(tr, c, 1);
assert(cnt <= (size_t) GDK_oid_max);
b = store_funcs.bind_col(m->session->tr, c, RDONLY);
if (b) {
str loc;
if (b->batPersistence == PERSISTENT && BATlocation(&loc, &b->batCacheid) && loc)
varSetProp(mb, k, fileProp, op_eq, VALset(&vr, TYPE_str, loc));
cnt = BATcount(b);
BBPunfix(b->batCacheid);
}
rows = (wrd) cnt;
if (c->t->p)
mt_member = c->t->p->base.id;
}
}
if (rows > 1 && mode != RD_INS)
varSetProp(mb, k, rowsProp, op_eq, VALset(&vr, TYPE_wrd, &rows));
if (not_null)
varSetProp(mb, k, notnilProp, op_eq, NULL);
if (mt_member && mode != RD_INS)
varSetProp(mb, k, mtProp, op_eq, VALset(&vr, TYPE_int, &mt_member));
{
int lowprop = hlbProp, highprop = hubProp;
/* rows == cnt has been checked above to be <= GDK_oid_max */
oid low = 0, high = low + (oid) rows;
pushInstruction(mb, p);
if (mode == RD_INS) {
low = high;
high += 1024 * 1024;
}
varSetProp(mb, getArg(p, 0), lowprop, op_gte, VALset(&vr, TYPE_oid, &low));
varSetProp(mb, getArg(p, 0), highprop, op_lt, VALset(&vr, TYPE_oid, &high));
}
if (not_null)
actions++;
} else {
pushInstruction(mb, p);
}
}
GDKfree(old);
msg = optimizerCheck(cntxt, mb, "optimizer.SQLgetstatistics", actions, GDKusec() - clk);
if (msg) /* what to do with an error? */
GDKfree(msg);
}
/* import variable given file id and variable name */
str
NCDFimportVariable(Client cntxt, MalBlkPtr mb, MalStkPtr stk, InstrPtr pci)
{
mvc *m = NULL;
sql_schema *sch = NULL;
sql_table *tfiles = NULL, *arr_table = NULL;
sql_column *col;
str msg = MAL_SUCCEED, vname = *(str*)getArgReference(stk, pci, 2);
str fname = NULL, dimtype = NULL, aname_sys = NULL;
int fid = *(int*)getArgReference(stk, pci, 1);
int varid, vndims, vnatts, i, j, retval;
char buf[BUFSIZ], *s= buf, aname[256], **dname;
oid rid = oid_nil;
int vdims[NC_MAX_VAR_DIMS];
nc_type vtype;
int ncid; /* dataset id */
size_t dlen;
bat vbatid = 0, *dim_bids;
BAT *vbat = NULL, *dimbat;
msg = getSQLContext(cntxt, mb, &m, NULL);
if (msg)
return msg;
sch = mvc_bind_schema(m, "sys");
if ( !sch )
return createException(MAL, "netcdf.importvar", "Cannot get schema sys\n");
tfiles = mvc_bind_table(m, sch, "netcdf_files");
if (tfiles == NULL)
return createException(MAL, "netcdf.importvar", "Catalog table missing\n");
/* get the name of the attached NetCDF file */
col = mvc_bind_column(m, tfiles, "file_id");
if (col == NULL)
return createException(MAL, "netcdf.importvar", "Could not find \"netcdf_files\".\"file_id\"\n");
rid = table_funcs.column_find_row(m->session->tr, col, (void *)&fid, NULL);
if (rid == oid_nil)
return createException(MAL, "netcdf.importvar", "File %d not in the NetCDF vault\n", fid);
col = mvc_bind_column(m, tfiles, "location");
fname = (str)table_funcs.column_find_value(m->session->tr, col, rid);
/* Open NetCDF file */
if ((retval = nc_open(fname, NC_NOWRITE, &ncid)))
return createException(MAL, "netcdf.importvar", "Cannot open NetCDF file %s: %s",
fname, nc_strerror(retval));
/* Get info for variable vname from NetCDF file */
if ( (retval = nc_inq_varid(ncid, vname, &varid)) )
return createException(MAL, "netcdf.importvar",
"Cannot read variable %s: %s",
vname, nc_strerror(retval));
if ( (retval = nc_inq_var(ncid, varid, vname, &vtype, &vndims, vdims, &vnatts)))
return createException(MAL, "netcdf.importvar",
"Cannot read variable %d : %s",
varid, nc_strerror(retval));
/* compose 'create table' statement in the buffer */
dname = (char **) GDKzalloc( sizeof(char *) * vndims);
for (i = 0; i < vndims; i++)
dname[i] = (char *) GDKzalloc(NC_MAX_NAME + 1);
snprintf(aname, 256, "%s%d", vname, fid);
j = snprintf(buf, BUFSIZ,"create table %s.%s( ", sch->base.name, aname);
for (i = 0; i < vndims; i++){
if ((retval = nc_inq_dim(ncid, vdims[i], dname[i], &dlen)))
return createException(MAL, "netcdf.importvar",
"Cannot read dimension %d : %s",
vdims[i], nc_strerror(retval));
if ( dlen <= (int) GDK_bte_max )
dimtype = "TINYINT";
else if ( dlen <= (int) GDK_sht_max )
dimtype = "SMALLINT";
else
dimtype = "INT";
(void)dlen;
j += snprintf(buf + j, BUFSIZ - j, "%s %s, ", dname[i], dimtype);
}
j += snprintf(buf + j, BUFSIZ - j, "value %s);", NCDF2SQL(vtype));
/* execute 'create table ' */
msg = SQLstatementIntern(cntxt, &s, "netcdf.importvar", TRUE, FALSE, NULL);
if (msg != MAL_SUCCEED)
return msg;
/* load variable data */
dim_bids = (bat *)GDKmalloc(sizeof(bat) * vndims);
msg = NCDFloadVar(&dim_bids, &vbatid, ncid, varid, vtype, vndims, vdims);
if ( msg != MAL_SUCCEED )
return msg;
/* associate columns in the table with loaded variable data */
aname_sys = toLower(aname);
arr_table = mvc_bind_table(m, sch, aname_sys);
if (arr_table == NULL)
return createException(MAL, "netcdf.importvar", "netcdf table %s missing\n", aname_sys);
col = mvc_bind_column(m, arr_table, "value");
if (col == NULL)
return createException(MAL, "netcdf.importvar", "Cannot find column %s.value\n", aname_sys);
vbat = BATdescriptor(vbatid);
store_funcs.append_col(m->session->tr, col, vbat, TYPE_bat);
BBPunfix(vbatid);
BBPdecref(vbatid, 1);
vbat = NULL;
/* associate dimension bats */
for (i = 0; i < vndims; i++){
col = mvc_bind_column(m, arr_table, dname[i]);
if (col == NULL)
return createException(MAL, "netcdf.importvar", "Cannot find column %s.%s\n", aname_sys, dname[i]);
dimbat = BATdescriptor(dim_bids[i]);
store_funcs.append_col(m->session->tr, col, dimbat, TYPE_bat);
BBPunfix(dim_bids[i]); /* phys. ref from BATdescriptor */
BBPdecref(dim_bids[i], 1); /* log. ref. from loadVar */
dimbat = NULL;
}
for (i = 0; i < vndims; i++)
GDKfree(dname[i]);
GDKfree(dname);
GDKfree(dim_bids);
nc_close(ncid);
return msg;
}
str
NCDFattach(Client cntxt, MalBlkPtr mb, MalStkPtr stk, InstrPtr pci)
{
mvc *m = NULL;
sql_schema *sch = NULL;
sql_table *tfiles = NULL, *tdims = NULL, *tvars = NULL, *tvardim = NULL, *tattrs = NULL;
sql_column *col;
str msg = MAL_SUCCEED;
str fname = *(str*)getArgReference(stk, pci, 1);
char buf[BUFSIZ], *s= buf;
oid fid, rid = oid_nil;
sql_trans *tr;
int ncid; /* dataset id */
int ndims, nvars, ngatts, unlimdim;
int didx, vidx, vndims, vnatts, i, aidx, coord_dim_id = -1;
int vdims[NC_MAX_VAR_DIMS];
size_t dlen, alen;
char dname[NC_MAX_NAME+1], vname[NC_MAX_NAME+1], aname[NC_MAX_NAME +1],
abuf[80], *aval;
char **dims = NULL;
nc_type vtype, atype; /* == int */
int retval, avalint;
float avalfl;
double avaldbl;
msg = getSQLContext(cntxt, mb, &m, NULL);
if (msg)
return msg;
tr = m->session->tr;
sch = mvc_bind_schema(m, "sys");
if ( !sch )
return createException(MAL, "netcdf.attach", "Cannot get schema sys\n");
tfiles = mvc_bind_table(m, sch, "netcdf_files");
tdims = mvc_bind_table(m, sch, "netcdf_dims");
tvars = mvc_bind_table(m, sch, "netcdf_vars");
tvardim = mvc_bind_table(m, sch, "netcdf_vardim");
tattrs = mvc_bind_table(m, sch, "netcdf_attrs");
if (tfiles == NULL || tdims == NULL || tvars == NULL ||
tvardim == NULL || tattrs == NULL)
return createException(MAL, "netcdf.attach", "Catalog table missing\n");
/* check if the file is already attached */
col = mvc_bind_column(m, tfiles, "location");
rid = table_funcs.column_find_row(m->session->tr, col, fname, NULL);
if (rid != oid_nil)
return createException(SQL, "netcdf.attach", "File %s is already attached\n", fname);
/* Open NetCDF file */
if ((retval = nc_open(fname, NC_NOWRITE, &ncid)))
return createException(MAL, "netcdf.test", "Cannot open NetCDF \
file %s: %s", fname, nc_strerror(retval));
if ((retval = nc_inq(ncid, &ndims, &nvars, &ngatts, &unlimdim)))
return createException(MAL, "netcdf.test", "Cannot read NetCDF \
header: %s", nc_strerror(retval));
/* Insert row into netcdf_files table */
col = mvc_bind_column(m, tfiles, "file_id");
fid = store_funcs.count_col(tr, col, 1) + 1;
snprintf(buf, BUFSIZ, INSFILE, (int)fid, fname);
if ( ( msg = SQLstatementIntern(cntxt, &s, "netcdf.attach", TRUE, FALSE, NULL))
!= MAL_SUCCEED )
goto finish;
/* Read dimensions from NetCDF header and insert a row for each one into netcdf_dims table */
dims = (char **)GDKzalloc(sizeof(char *) * ndims);
for (didx = 0; didx < ndims; didx++){
if ((retval = nc_inq_dim(ncid, didx, dname, &dlen)))
return createException(MAL, "netcdf.attach", "Cannot read dimension %d : %s", didx, nc_strerror(retval));
snprintf(buf, BUFSIZ, INSDIM, didx, (int)fid, dname, (int)dlen);
if ( ( msg = SQLstatementIntern(cntxt, &s, "netcdf.attach", TRUE, FALSE, NULL))
!= MAL_SUCCEED )
goto finish;
dims[didx] = GDKstrdup(dname);
}
/* Read variables and attributes from the header and insert rows in netcdf_vars, netcdf_vardims, and netcdf_attrs tables */
for (vidx = 0; vidx < nvars; vidx++){
if ( (retval = nc_inq_var(ncid, vidx, vname, &vtype, &vndims, vdims, &vnatts)))
return createException(MAL, "netcdf.attach",
"Cannot read variable %d : %s",
vidx, nc_strerror(retval));
/* Check if this is coordinate variable */
if ( (vndims == 1) && ( strcmp(vname, dims[vdims[0]]) == 0 ))
coord_dim_id = vdims[0];
else coord_dim_id = -1;
snprintf(buf, BUFSIZ, INSVAR, vidx, (int)fid, vname, prim_type_name(vtype), vndims, coord_dim_id);
if ( ( msg = SQLstatementIntern(cntxt, &s, "netcdf.attach", TRUE, FALSE, NULL))
!= MAL_SUCCEED )
goto finish;
if ( coord_dim_id < 0 ){
for (i = 0; i < vndims; i++){
snprintf(buf, BUFSIZ, INSVARDIM, vidx, vdims[i], (int)fid, i);
if ( ( msg = SQLstatementIntern(cntxt, &s, "netcdf.attach", TRUE, FALSE, NULL))
!= MAL_SUCCEED )
goto finish;
}
}
if ( vnatts > 0 ) { /* fill in netcdf_attrs table */
for (aidx = 0; aidx < vnatts; aidx++){
if ((retval = nc_inq_attname(ncid,vidx,aidx,aname)))
return createException(MAL, "netcdf.attach",
"Cannot read attribute %d of variable %d: %s",
aidx, vidx, nc_strerror(retval));
if ((retval = nc_inq_att(ncid,vidx,aname,&atype,&alen)))
return createException(MAL, "netcdf.attach",
"Cannot read attribute %s type and length: %s",
aname, nc_strerror(retval));
switch ( atype ) {
case NC_CHAR:
aval = (char *) GDKzalloc(alen + 1);
if ((retval = nc_get_att_text(ncid,vidx,aname,aval)))
return createException(MAL, "netcdf.attach",
"Cannot read attribute %s value: %s",
aname, nc_strerror(retval));
fix_quote(aval, alen);
aval[alen] = '\0';
snprintf(buf, BUFSIZ, INSATTR, vname, aname, "string", aval, (int)fid, "root");
GDKfree(aval);
break;
case NC_INT:
if ((retval = nc_get_att_int(ncid,vidx,aname,&avalint)))
return createException(MAL, "netcdf.attach",
"Cannot read attribute %s value: %s",
aname, nc_strerror(retval));
snprintf(abuf,80,"%d",avalint);
snprintf(buf, BUFSIZ, INSATTR, vname, aname, prim_type_name(atype), abuf, (int)fid, "root");
break;
case NC_FLOAT:
if ((retval = nc_get_att_float(ncid,vidx,aname,&avalfl)))
return createException(MAL, "netcdf.attach",
"Cannot read attribute %s value: %s",
aname, nc_strerror(retval));
snprintf(abuf,80,"%7.2f",avalfl);
snprintf(buf, BUFSIZ, INSATTR, vname, aname, prim_type_name(atype), abuf, (int)fid, "root");
break;
case NC_DOUBLE:
if ((retval = nc_get_att_double(ncid,vidx,aname,&avaldbl)))
return createException(MAL, "netcdf.attach",
"Cannot read attribute %s value: %s",
aname, nc_strerror(retval));
snprintf(abuf,80,"%7.2e",avaldbl);
snprintf(buf, BUFSIZ, INSATTR, vname, aname, prim_type_name(atype), abuf, (int)fid, "root");
break;
default: continue; /* next attribute */
}
printf("statement: '%s'\n", s);
if ( ( msg = SQLstatementIntern(cntxt, &s, "netcdf.attach", TRUE, FALSE, NULL))
!= MAL_SUCCEED )
goto finish;
} /* attr loop */
}
} /* var loop */
/* Extract global attributes */
for (aidx = 0; aidx < ngatts; aidx++){
if ((retval = nc_inq_attname(ncid,NC_GLOBAL,aidx,aname)))
return createException(MAL, "netcdf.attach",
"Cannot read global attribute %d: %s",
aidx, nc_strerror(retval));
if ((retval = nc_inq_att(ncid,NC_GLOBAL,aname,&atype,&alen)))
return createException(MAL, "netcdf.attach",
"Cannot read global attribute %s type and length: %s",
aname, nc_strerror(retval));
switch ( atype ) {
case NC_CHAR:
aval = (char *) GDKzalloc(alen + 1);
if ((retval = nc_get_att_text(ncid,NC_GLOBAL,aname,aval)))
return createException(MAL, "netcdf.attach",
"Cannot read global attribute %s value: %s",
aname, nc_strerror(retval));
fix_quote(aval, alen);
aval[alen] = '\0';
snprintf(buf, BUFSIZ, INSATTR, "GLOBAL", aname, "string", aval, (int)fid, "root");
GDKfree(aval);
break;
case NC_INT:
if ((retval = nc_get_att_int(ncid,NC_GLOBAL,aname,&avalint)))
return createException(MAL, "netcdf.attach",
"Cannot read global attribute %s of type %s : %s",
aname, prim_type_name(atype), nc_strerror(retval));
snprintf(abuf,80,"%d",avalint);
snprintf(buf, BUFSIZ, INSATTR, "GLOBAL", aname, prim_type_name(atype), abuf, (int)fid, "root");
break;
case NC_FLOAT:
if ((retval = nc_get_att_float(ncid,NC_GLOBAL,aname,&avalfl)))
return createException(MAL, "netcdf.attach",
"Cannot read global attribute %s of type %s: %s",
aname, prim_type_name(atype), nc_strerror(retval));
snprintf(abuf,80,"%7.2f",avalfl);
snprintf(buf, BUFSIZ, INSATTR, "GLOBAL", aname, prim_type_name(atype), abuf, (int)fid, "root");
break;
case NC_DOUBLE:
if ((retval = nc_get_att_double(ncid,NC_GLOBAL,aname,&avaldbl)))
return createException(MAL, "netcdf.attach",
"Cannot read global attribute %s value: %s",
aname, nc_strerror(retval));
snprintf(abuf,80,"%7.2e",avaldbl);
snprintf(buf, BUFSIZ, INSATTR, "GLOBAL", aname, prim_type_name(atype), abuf, (int)fid, "root");
break;
default: continue; /* next attribute */
}
printf("global: '%s'\n", s);
if ( ( msg = SQLstatementIntern(cntxt, &s, "netcdf.attach", TRUE, FALSE, NULL))
!= MAL_SUCCEED )
goto finish;
} /* global attr loop */
finish:
nc_close(ncid);
if (dims != NULL ){
for (didx = 0; didx < ndims; didx++)
GDKfree(dims[didx]);
GDKfree(dims);
}
return msg;
}
str FITSloadTable(Client cntxt, MalBlkPtr mb, MalStkPtr stk, InstrPtr pci)
{
mvc *m = NULL;
sql_schema *sch;
sql_table *fits_fl, *fits_tbl, *tbl = NULL;
sql_column *col;
sql_subtype tpe;
fitsfile *fptr;
str tname = *(str*)getArgReference(stk, pci, 1);
str fname;
str msg = MAL_SUCCEED;
oid rid = oid_nil, frid = oid_nil;
int status = 0, cnum = 0, fid, hdu, hdutype, i, j, anynull = 0, mtype;
int *tpcode = NULL;
long *rep = NULL, *wid = NULL, rows;
char keywrd[80], **cname, nm[FLEN_VALUE];
ptr nilptr;
msg = getSQLContext(cntxt, mb, &m, NULL);
if (msg)
return msg;
sch = mvc_bind_schema(m, "sys");
fits_tbl = mvc_bind_table(m, sch, "fits_tables");
if (fits_tbl == NULL) {
msg = createException(MAL, "fits.loadtable", "FITS catalog is missing.\n");
return msg;
}
tbl = mvc_bind_table(m, sch, tname);
if (tbl) {
msg = createException(MAL, "fits.loadtable", "Table %s is already created.\n", tname);
return msg;
}
col = mvc_bind_column(m, fits_tbl, "name");
rid = table_funcs.column_find_row(m->session->tr, col, tname, NULL);
if (rid == oid_nil) {
msg = createException(MAL, "fits.loadtable", "Table %s is unknown in FITS catalog. Attach first the containing file\n", tname);
return msg;
}
/* Open FITS file and move to the table HDU */
col = mvc_bind_column(m, fits_tbl, "file_id");
fid = *(int*)table_funcs.column_find_value(m->session->tr, col, rid);
fits_fl = mvc_bind_table(m, sch, "fits_files");
col = mvc_bind_column(m, fits_fl, "id");
frid = table_funcs.column_find_row(m->session->tr, col, (void *)&fid, NULL);
col = mvc_bind_column(m, fits_fl, "name");
fname = (char *)table_funcs.column_find_value(m->session->tr, col, frid);
if (fits_open_file(&fptr, fname, READONLY, &status)) {
msg = createException(MAL, "fits.loadtable", "Missing FITS file %s.\n", fname);
return msg;
}
col = mvc_bind_column(m, fits_tbl, "hdu");
hdu = *(int*)table_funcs.column_find_value(m->session->tr, col, rid);
fits_movabs_hdu(fptr, hdu, &hdutype, &status);
if (hdutype != ASCII_TBL && hdutype != BINARY_TBL) {
msg = createException(MAL, "fits.loadtable", "HDU %d is not a table.\n", hdu);
fits_close_file(fptr, &status);
return msg;
}
/* create a SQL table to hold the FITS table */
/* col = mvc_bind_column(m, fits_tbl, "columns");
cnum = *(int*) table_funcs.column_find_value(m->session->tr, col, rid); */
fits_get_num_cols(fptr, &cnum, &status);
tbl = mvc_create_table(m, sch, tname, tt_table, 0, SQL_PERSIST, 0, cnum);
tpcode = (int *)GDKzalloc(sizeof(int) * cnum);
rep = (long *)GDKzalloc(sizeof(long) * cnum);
wid = (long *)GDKzalloc(sizeof(long) * cnum);
cname = (char **)GDKzalloc(sizeof(char *) * cnum);
for (j = 1; j <= cnum; j++) {
/* fits_get_acolparms(fptr, j, cname, &tbcol, tunit, tform, &tscal, &tzero, tnull, tdisp, &status); */
snprintf(keywrd, 80, "TTYPE%d", j);
fits_read_key(fptr, TSTRING, keywrd, nm, NULL, &status);
if (status) {
snprintf(nm, FLEN_VALUE, "column_%d", j);
status = 0;
}
cname[j - 1] = GDKstrdup(toLower(nm));
fits_get_coltype(fptr, j, &tpcode[j - 1], &rep[j - 1], &wid[j - 1], &status);
fits2subtype(&tpe, tpcode[j - 1], rep[j - 1], wid[j - 1]);
/* mnstr_printf(cntxt->fdout,"#%d %ld %ld - M: %s\n", tpcode[j-1], rep[j-1], wid[j-1], tpe.type->sqlname); */
mvc_create_column(m, tbl, cname[j - 1], &tpe);
}
/* data load */
fits_get_num_rows(fptr, &rows, &status);
mnstr_printf(cntxt->fdout,"#Loading %ld rows in table %s\n", rows, tname);
for (j = 1; j <= cnum; j++) {
BAT *tmp = NULL;
int time0 = GDKms();
mtype = fits2mtype(tpcode[j - 1]);
nilptr = ATOMnil(mtype);
col = mvc_bind_column(m, tbl, cname[j - 1]);
tmp = BATnew(TYPE_void, mtype, rows);
if ( tmp == NULL){
GDKfree(tpcode);
GDKfree(rep);
GDKfree(wid);
GDKfree(cname);
throw(MAL,"fits.load", MAL_MALLOC_FAIL);
}
BATseqbase(tmp, 0);
if (rows > (long)REMAP_PAGE_MAXSIZE)
BATmmap(tmp, STORE_MMAP, STORE_MMAP, STORE_MMAP, STORE_MMAP, 0);
if (mtype != TYPE_str) {
fits_read_col(fptr, tpcode[j - 1], j, 1, 1, rows, nilptr, (void *)BUNtloc(bat_iterator(tmp), BUNfirst(tmp)), &anynull, &status);
BATsetcount(tmp, rows);
tmp->tsorted = 0;
tmp->trevsorted = 0;
} else {
/* char *v = GDKzalloc(wid[j-1]);*/
int bsize = 50;
int tm0, tloadtm = 0, tattachtm = 0;
int batch = bsize, k;
char **v = (char **) GDKzalloc(sizeof(char *) * bsize);
for(i = 0; i < bsize; i++)
v[i] = GDKzalloc(wid[j-1]);
for(i = 0; i < rows; i += batch) {
batch = rows - i < bsize ? rows - i: bsize;
tm0 = GDKms();
fits_read_col(fptr, tpcode[j - 1], j, 1 + i, 1, batch, nilptr, (void *)v, &anynull, &status);
tloadtm += GDKms() - tm0;
tm0 = GDKms();
for(k = 0; k < batch ; k++)
BUNappend(tmp, v[k], TRUE);
tattachtm += GDKms() - tm0;
}
for(i = 0; i < bsize ; i++)
GDKfree(v[i]);
GDKfree(v);
mnstr_printf(cntxt->fdout,"#String column load %d ms, BUNappend %d ms\n", tloadtm, tattachtm);
}
if (status) {
char buf[FLEN_ERRMSG + 1];
fits_read_errmsg(buf);
msg = createException(MAL, "fits.loadtable", "Cannot load column %s of %s table: %s.\n", cname[j - 1], tname, buf);
break;
}
mnstr_printf(cntxt->fdout,"#Column %s loaded for %d ms\t", cname[j-1], GDKms() - time0);
store_funcs.append_col(m->session->tr, col, tmp, TYPE_bat);
mnstr_printf(cntxt->fdout,"#Total %d ms\n", GDKms() - time0);
BBPunfix(tmp->batCacheid);
}
GDKfree(tpcode);
GDKfree(rep);
GDKfree(wid);
GDKfree(cname);
fits_close_file(fptr, &status);
return msg;
}
static str
SHPimportFile(Client cntxt, MalBlkPtr mb, MalStkPtr stk, InstrPtr pci, bool partial) {
mvc *m = NULL;
sql_schema *sch = NULL;
char *sch_name = "sys";
sql_table *shps_table = NULL, *fls_table = NULL, *data_table = NULL;
char *shps_table_name = "shapefiles";
char *fls_table_name = "files";
char *data_table_name = NULL;
sql_column *col;
sql_column **cols;
BAT **colsBAT;
int colsNum = 2; //we will have at least the gid column and a geometry column
int rowsNum = 0; //the number of rows in the shape file that will be imported
//GIntBig rowsNum = 0;
int gidNum = 0;
char *nameToLowerCase = NULL;
str msg = MAL_SUCCEED;
str fname = NULL;
int vid = *(int*)getArgReference(stk, pci, 1);
ptr *p;
wkb *g;
OGRGeometryH geom;
OGREnvelope *mbb;
/* SHP-level descriptor */
OGRFieldDefnH hFieldDefn;
int i=0;
oid irid;
GDALWConnection shp_conn;
GDALWConnection * shp_conn_ptr = NULL;
GDALWSimpleFieldDef * field_definitions;
OGRFeatureH feature;
OGRFeatureDefnH featureDefn;
/* get table columns from shp and create the table */
if((msg = getSQLContext(cntxt, mb, &m, NULL)) != MAL_SUCCEED)
return msg;
if((msg = checkSQLContext(cntxt)) != MAL_SUCCEED)
return msg;
if(!(sch = mvc_bind_schema(m, sch_name)))
return createException(MAL, "shp.import", SQLSTATE(38000) "Schema '%s' missing", sch_name);
/* find the name of the shape file corresponding to the given id */
if(!(fls_table = mvc_bind_table(m, sch, fls_table_name)))
return createException(MAL, "shp.import", SQLSTATE(38000) "Table '%s.%s' missing", sch_name, fls_table_name);
if(!(col = mvc_bind_column(m, fls_table, "id")))
return createException(MAL, "shp.import", SQLSTATE(38000) "Column '%s.%s(id)' missing", sch_name, fls_table_name);
irid = table_funcs.column_find_row(m->session->tr, col, (void *)&vid, NULL);
if (is_oid_nil(irid))
return createException(MAL, "shp.import", SQLSTATE(38000) "Shapefile with id %d not in the %s.%s table\n", vid, sch_name, fls_table_name);
if(!(col = mvc_bind_column(m, fls_table, "path")))
return createException(MAL, "shp.import", SQLSTATE(38000) "Column '%s.%s(path)' missing", sch_name, fls_table_name);
fname = (str)table_funcs.column_find_value(m->session->tr, col, irid);
/* find the name of the table that has been reserved for this shape file */
if(!(shps_table = mvc_bind_table(m, sch, shps_table_name)))
return createException(MAL, "shp.import", SQLSTATE(38000) "Table '%s.%s' missing", sch_name, shps_table_name);
if(!(col = mvc_bind_column(m, shps_table, "fileid")))
return createException(MAL, "shp.import", SQLSTATE(38000) "Column '%s.%s(fileid)' missing", sch_name, shps_table_name);
irid = table_funcs.column_find_row(m->session->tr, col, (void *)&vid, NULL);
if (is_oid_nil(irid))
return createException(MAL, "shp.import", SQLSTATE(38000) "Shapefile with id %d not in the Shapefile catalog\n", vid);
if(!(col = mvc_bind_column(m, shps_table, "datatable")))
return createException(MAL, "shp.import", SQLSTATE(38000) "Column '%s.%s(datatable)' missing", sch_name, shps_table_name);
data_table_name = (str)table_funcs.column_find_value(m->session->tr, col, irid);
/* add the data on the file to the table */
if(!(shp_conn_ptr = GDALWConnect((char *) fname)))
return createException(MAL, "shp.import", SQLSTATE(38000) "Missing shape file %s\n", fname);
shp_conn = *shp_conn_ptr;
/*count the number of lines in the shape file */
if ((rowsNum = OGR_L_GetFeatureCount(shp_conn.layer, false)) == -1) {
if ((rowsNum = OGR_L_GetFeatureCount(shp_conn.layer, true)) == -1) {
OGR_L_ResetReading(shp_conn.layer);
rowsNum = 0;
while ((feature = OGR_L_GetNextFeature(shp_conn.layer)) != NULL ) {
rowsNum++;
OGR_F_Destroy(feature);
}
}
}
/* calculate the mbb of the query geometry */
if (partial) {
p = (ptr*)getArgReference(stk, pci, 2);
g = (wkb*)*p;
geom = OGR_G_CreateGeometry(wkbPolygon);
if (OGR_G_ImportFromWkb(geom, (unsigned char*)g->data, g->len) != OGRERR_NONE) {
msg = createException(MAL, "shp.import", SQLSTATE(38000) "Could not intantiate the query polygon.");
OGR_F_Destroy(geom);
goto final;
}
if (!(mbb = (OGREnvelope*)GDKmalloc(sizeof(OGREnvelope)))) {
msg = createException(MAL, "shp.import", SQLSTATE(HY001) MAL_MALLOC_FAIL);
OGR_F_Destroy(geom);
goto final;
}
/* attach a single shp file given its name, fill in shp catalog tables */
str
SHPattach(Client cntxt, MalBlkPtr mb, MalStkPtr stk, InstrPtr pci)
{
mvc *m = NULL;
sql_schema *sch = NULL;
sql_table *fls = NULL, *shps = NULL, *shps_dbf = NULL;
sql_column *col;
str msg = MAL_SUCCEED;
str fname = *(str*)getArgReference(stk, pci, 1);
/* SHP-level descriptor */
char buf[BUFSIZ], temp_buf[BUFSIZ], *s=buf;
int i=0, shpid = 0;
oid fid, rid = oid_nil;
GDALWConnection shp_conn;
GDALWConnection * shp_conn_ptr = NULL;
GDALWSimpleFieldDef * field_definitions;
GDALWSpatialInfo spatial_info;
char *nameToLowerCase = NULL;
if((msg = getSQLContext(cntxt, mb, &m, NULL)) != MAL_SUCCEED)
return msg;
if((msg = checkSQLContext(cntxt)) != MAL_SUCCEED)
return msg;
if(!(sch = mvc_bind_schema(m, "sys")))
return createException(MAL, "shp.attach", SQLSTATE(38000) "Schema sys missing\n");
fls = mvc_bind_table(m, sch, "files");
shps = mvc_bind_table(m, sch, "shapefiles");
shps_dbf = mvc_bind_table(m, sch, "shapefiles_dbf");
if (fls == NULL || shps == NULL || shps_dbf == NULL )
return createException(MAL, "shp.attach", SQLSTATE(38000) "Catalog table missing\n");
if ((shp_conn_ptr = GDALWConnect((char *) fname)) == NULL) {
return createException(MAL, "shp.attach", SQLSTATE(38000) "Missing shape file %s\n", fname);
}
shp_conn = *shp_conn_ptr;
/* check if the file is already attached */
col = mvc_bind_column(m, fls, "path");
rid = table_funcs.column_find_row(m->session->tr, col, fname, NULL);
if (!is_oid_nil(rid)) {
GDALWClose(shp_conn_ptr);
return createException(MAL, "shp.attach", SQLSTATE(38000) "File %s already attached\n", fname);
}
/* add row in the files(id, path) catalog table */
col = mvc_bind_column(m, fls, "id");
fid = store_funcs.count_col(m->session->tr, col, 1) + 1;
snprintf(buf, BUFSIZ, INSFILE, (int)fid, fname);
if ( ( msg = SQLstatementIntern(cntxt, &s,"shp.attach",TRUE,FALSE,NULL)) != MAL_SUCCEED)
goto finish;
/*if (shp_conn.layer == NULL || shp_conn.source == NULL || shp_conn.handler == NULL || shp_conn.driver == NULL) {
msg = createException(MAL, "shp.attach", SQLSTATE(38000) "lol-1\n");
return msg;
}*/
/* add row in the shapefiles catalog table (e.g. the name of the table that will store tha data of the shapefile) */
spatial_info = GDALWGetSpatialInfo(shp_conn);
col = mvc_bind_column(m, shps, "shapefileid");
shpid = store_funcs.count_col(m->session->tr, col, 1) + 1;
nameToLowerCase = toLower(shp_conn.layername);
snprintf(buf, BUFSIZ, INSSHP, shpid, (int)fid, spatial_info.epsg, nameToLowerCase);
GDKfree(nameToLowerCase);
if ( ( msg = SQLstatementIntern(cntxt, &s,"shp.attach",TRUE,FALSE,NULL)) != MAL_SUCCEED)
goto finish;
/* add information about the fields of the shape file
* one row for each field with info (shapefile_id, field_name, field_type) */
field_definitions = GDALWGetSimpleFieldDefinitions(shp_conn);
if (field_definitions == NULL) {
GDALWClose(&shp_conn);
return createException(MAL, "shp.attach", SQLSTATE(HY001) MAL_MALLOC_FAIL);
}
for (i=0 ; i<shp_conn.numFieldDefinitions ; i++) {
snprintf(buf, BUFSIZ, INSSHPDBF, shpid, field_definitions[i].fieldName, field_definitions[i].fieldType);
if ( ( msg = SQLstatementIntern(cntxt, &s,"shp.attach",TRUE,FALSE,NULL)) != MAL_SUCCEED)
goto fin;
}
/* create the table that will store the data of the shape file */
temp_buf[0]='\0';
for (i=0 ; i<shp_conn.numFieldDefinitions ; i++) {
nameToLowerCase = toLower(field_definitions[i].fieldName);
if (strcmp(field_definitions[i].fieldType, "Integer") == 0) {
sprintf(temp_buf + strlen(temp_buf), "\"%s\" INT, ", nameToLowerCase);
} else if (strcmp(field_definitions[i].fieldType, "Real") == 0) {
sprintf(temp_buf + strlen(temp_buf), "\"%s\" FLOAT, ", nameToLowerCase);
#if 0
} else if (strcmp(field_definitions[i].fieldType, "Date") == 0) {
sprintf(temp_buf + strlen(temp_buf), "\"%s\" STRING, ", nameToLowerCase);
#endif
} else
sprintf(temp_buf + strlen(temp_buf), "\"%s\" STRING, ", nameToLowerCase);
GDKfree(nameToLowerCase);
}
sprintf(temp_buf + strlen(temp_buf), "geom GEOMETRY ");
snprintf(buf, BUFSIZ, CRTTBL, shp_conn.layername, temp_buf);
if ( ( msg = SQLstatementIntern(cntxt, &s,"shp.import",TRUE,FALSE,NULL)) != MAL_SUCCEED)
goto fin;
fin:
free(field_definitions);
finish:
/* if (msg != MAL_SUCCEED){
snprintf(buf, BUFSIZ,"ROLLBACK;");
SQLstatementIntern(cntxt,&s,"geotiff.attach",TRUE,FALSE));
}*/
GDALWClose(&shp_conn);
return msg;
}
static void SQLgetStatistics(Client cntxt, mvc *m, MalBlkPtr mb)
{
InstrPtr *old = NULL;
int oldtop, i, actions = 0, size = 0;
lng clk = GDKusec();
sql_trans *tr = m->session->tr;
str msg;
old = mb->stmt;
oldtop = mb->stop;
size = (mb->stop * 1.2 < mb->ssize) ? mb->ssize : (int) (mb->stop * 1.2);
mb->stmt = (InstrPtr *) GDKzalloc(size * sizeof(InstrPtr));
mb->ssize = size;
mb->stop = 0;
for (i = 0; i < oldtop; i++) {
InstrPtr p = old[i];
char *f = getFunctionId(p);
if (getModuleId(p) == sqlRef && f == tidRef) {
char *sname = getVarConstant(mb, getArg(p, 2)).val.sval;
char *tname = getVarConstant(mb, getArg(p, 3)).val.sval;
sql_schema *s = mvc_bind_schema(m, sname);
sql_table *t;
if (!s || strcmp(s->base.name, dt_schema) == 0) {
pushInstruction(mb, p);
continue;
}
t = mvc_bind_table(m, s, tname);
if (t && (!isRemote(t) && !isMergeTable(t)) && t->p) {
int mt_member = t->p->base.id;
setMitosisPartition(p,mt_member);
}
}
if (getModuleId(p) == sqlRef && (f == bindRef || f == bindidxRef)) {
int upd = (p->argc == 7 || p->argc == 9);
char *sname = getVarConstant(mb, getArg(p, 2 + upd)).val.sval;
char *tname = getVarConstant(mb, getArg(p, 3 + upd)).val.sval;
char *cname = NULL;
int mt_member = 0;
BUN rows = 1; /* default to cope with delta bats */
int mode = 0;
int k = getArg(p, 0);
sql_schema *s = mvc_bind_schema(m, sname);
BAT *b;
if (!s || strcmp(s->base.name, dt_schema) == 0) {
pushInstruction(mb, p);
continue;
}
cname = getVarConstant(mb, getArg(p, 4 + upd)).val.sval;
mode = getVarConstant(mb, getArg(p, 5 + upd)).val.ival;
if (s && f == bindidxRef && cname) {
size_t cnt;
sql_idx *i = mvc_bind_idx(m, s, cname);
if (i && (!isRemote(i->t) && !isMergeTable(i->t))) {
cnt = store_funcs.count_idx(tr, i, 1);
assert(cnt <= (size_t) GDK_oid_max);
b = store_funcs.bind_idx(m->session->tr, i, RDONLY);
if (b) {
cnt = BATcount(b);
BBPunfix(b->batCacheid);
}
rows = (BUN) cnt;
if (i->t->p)
mt_member = i->t->p->base.id;
}
} else if (s && f == bindRef && cname) {
size_t cnt;
sql_table *t = mvc_bind_table(m, s, tname);
sql_column *c = mvc_bind_column(m, t, cname);
if (c && (!isRemote(c->t) && !isMergeTable(c->t))) {
cnt = store_funcs.count_col(tr, c, 1);
assert(cnt <= (size_t) GDK_oid_max);
b = store_funcs.bind_col(m->session->tr, c, RDONLY);
if (b) {
cnt = BATcount(b);
BBPunfix(b->batCacheid);
}
rows = (BUN) cnt;
if (c->t->p)
mt_member = c->t->p->base.id;
}
}
if (rows > 1 && mode != RD_INS)
setRowCnt(mb,k,rows);
if (mt_member && mode != RD_INS)
setMitosisPartition(p,mt_member);
pushInstruction(mb, p);
} else {
pushInstruction(mb, p);
}
}
GDKfree(old);
msg = optimizerCheck(cntxt, mb, "optimizer.SQLgetstatistics", actions, GDKusec() - clk);
if (msg) /* what to do with an error? */
GDKfree(msg);
}
int
mvc_init(int debug, store_type store, backend_stack stk)
{
int first = 0;
char *logdir = "sql_logs";
mvc_debug = debug;
if (mvc_debug)
fprintf(stderr, "#mvc_init logdir %s\n", logdir);
keyword_init();
scanner_init_keywords();
if ((first = store_init(debug, store, logdir, stk)) < 0) {
fprintf(stderr, "!mvc_init: unable to create system tables\n");
return -1;
}
if (first || catalog_version) {
sql_schema *s;
sql_table *t;
mvc *m = mvc_create(0, stk, 0, NULL, NULL);
/* disable caching */
m->caching = 0;
/* disable history */
m->history = 0;
/* disable size header */
m->sizeheader = 0;
mvc_trans(m);
s = m->session->schema = mvc_bind_schema(m, "sys");
assert(m->session->schema != NULL);
if (catalog_version) {
t = mvc_bind_table(m, s, "tables");
mvc_drop_table(m, s, t, 0);
t = mvc_bind_table(m, s, "columns");
mvc_drop_table(m, s, t, 0);
}
t = mvc_create_view(m, s, "tables", SQL_PERSIST, "SELECT * FROM (SELECT p.*, 0 AS \"temporary\" FROM \"sys\".\"_tables\" AS p UNION ALL SELECT t.*, 1 AS \"temporary\" FROM \"tmp\".\"_tables\" AS t) AS tables where tables.type <> 2;", 1);
mvc_create_column_(m, t, "id", "int", 32);
mvc_create_column_(m, t, "name", "varchar", 1024);
mvc_create_column_(m, t, "schema_id", "int", 32);
mvc_create_column_(m, t, "query", "varchar", 2048);
mvc_create_column_(m, t, "type", "smallint", 16);
mvc_create_column_(m, t, "system", "boolean", 1);
mvc_create_column_(m, t, "commit_action", "smallint", 16);
mvc_create_column_(m, t, "readonly", "boolean", 1);
mvc_create_column_(m, t, "temporary", "smallint", 16);
if (catalog_version) {
int pub = ROLE_PUBLIC;
int p = PRIV_SELECT;
int zero = 0;
sql_table *privs = find_sql_table(s, "privileges");
table_funcs.table_insert(m->session->tr, privs, &t->base.id, &pub, &p, &zero, &zero);
}
t = mvc_create_view(m, s, "columns", SQL_PERSIST, "SELECT * FROM (SELECT p.* FROM \"sys\".\"_columns\" AS p UNION ALL SELECT t.* FROM \"tmp\".\"_columns\" AS t) AS columns;", 1);
mvc_create_column_(m, t, "id", "int", 32);
mvc_create_column_(m, t, "name", "varchar", 1024);
mvc_create_column_(m, t, "type", "varchar", 1024);
mvc_create_column_(m, t, "type_digits", "int", 32);
mvc_create_column_(m, t, "type_scale", "int", 32);
mvc_create_column_(m, t, "table_id", "int", 32);
mvc_create_column_(m, t, "default", "varchar", 2048);
mvc_create_column_(m, t, "null", "boolean", 1);
mvc_create_column_(m, t, "number", "int", 32);
/* TODO: the code below is out-of-date. Should be changed into the
* following with the next major catalogue change:
* mvc_create_column(m, t, "storage", "varchar", 2048);
*/
mvc_create_column_(m, t, "storage_type", "int", 32);
if (catalog_version) {
int pub = ROLE_PUBLIC;
int p = PRIV_SELECT;
int zero = 0;
sql_table *privs = find_sql_table(s, "privileges");
table_funcs.table_insert(m->session->tr, privs, &t->base.id, &pub, &p, &zero, &zero);
}
if (!catalog_version) {
sql_create_env(m, s);
sql_create_privileges(m, s);
}
s = m->session->schema = mvc_bind_schema(m, "tmp");
assert(m->session->schema != NULL);
if (mvc_commit(m, 0, NULL) < 0) {
fprintf(stderr, "!mvc_init: unable to commit system tables\n");
return -1;
}
mvc_destroy(m);
}
return first;
}
int
mvc_init(int debug, store_type store, int ro, int su, backend_stack stk)
{
int first = 0;
logger_settings *log_settings = (struct logger_settings *) GDKmalloc(sizeof(struct logger_settings));
/* Set the default WAL directory. "sql_logs" by default */
log_settings->logdir = "sql_logs";
/* Get and pass on the WAL directory location, if set */
if (GDKgetenv("gdk_logdir") != NULL) {
log_settings->logdir = GDKgetenv("gdk_logdir");
}
/* Get and pass on the shared WAL directory location, if set */
log_settings->shared_logdir = GDKgetenv("gdk_shared_logdir");
/* Get and pass on the shared WAL drift threshold, if set.
* -1 by default, meaning it should be ignored, since it is not set */
log_settings->shared_drift_threshold = GDKgetenv_int("gdk_shared_drift_threshold", -1);
/* Get and pass on the flag how many WAL files should be preserved.
* 0 by default - keeps only the current WAL file. */
log_settings->keep_persisted_log_files = GDKgetenv_int("gdk_keep_persisted_log_files", 0);
mvc_debug = debug&4;
if (mvc_debug) {
fprintf(stderr, "#mvc_init logdir %s\n", log_settings->logdir);
fprintf(stderr, "#mvc_init keep_persisted_log_files %d\n", log_settings->keep_persisted_log_files);
if (log_settings->shared_logdir != NULL) {
fprintf(stderr, "#mvc_init shared_logdir %s\n", log_settings->shared_logdir);
}
fprintf(stderr, "#mvc_init shared_drift_threshold %d\n", log_settings->shared_drift_threshold);
}
keyword_init();
scanner_init_keywords();
if ((first = store_init(debug, store, ro, su, log_settings, stk)) < 0) {
fprintf(stderr, "!mvc_init: unable to create system tables\n");
return -1;
}
if (first || catalog_version) {
sql_schema *s;
sql_table *t;
mvc *m = mvc_create(0, stk, 0, NULL, NULL);
m->sa = sa_create();
/* disable caching */
m->caching = 0;
/* disable history */
m->history = 0;
/* disable size header */
m->sizeheader = 0;
mvc_trans(m);
s = m->session->schema = mvc_bind_schema(m, "sys");
assert(m->session->schema != NULL);
if (!first) {
t = mvc_bind_table(m, s, "tables");
mvc_drop_table(m, s, t, 0);
t = mvc_bind_table(m, s, "columns");
mvc_drop_table(m, s, t, 0);
}
t = mvc_create_view(m, s, "tables", SQL_PERSIST, "SELECT \"id\", \"name\", \"schema_id\", \"query\", CAST(CASE WHEN \"system\" THEN \"type\" + 10 /* system table/view */ ELSE (CASE WHEN \"commit_action\" = 0 THEN \"type\" /* table/view */ ELSE \"type\" + 20 /* global temp table */ END) END AS SMALLINT) AS \"type\", \"system\", \"commit_action\", \"access\", CASE WHEN (NOT \"system\" AND \"commit_action\" > 0) THEN 1 ELSE 0 END AS \"temporary\" FROM \"sys\".\"_tables\" WHERE \"type\" <> 2 UNION ALL SELECT \"id\", \"name\", \"schema_id\", \"query\", CAST(\"type\" + 30 /* local temp table */ AS SMALLINT) AS \"type\", \"system\", \"commit_action\", \"access\", 1 AS \"temporary\" FROM \"tmp\".\"_tables\";", 1);
mvc_create_column_(m, t, "id", "int", 32);
mvc_create_column_(m, t, "name", "varchar", 1024);
mvc_create_column_(m, t, "schema_id", "int", 32);
mvc_create_column_(m, t, "query", "varchar", 2048);
mvc_create_column_(m, t, "type", "smallint", 16);
mvc_create_column_(m, t, "system", "boolean", 1);
mvc_create_column_(m, t, "commit_action", "smallint", 16);
mvc_create_column_(m, t, "access", "smallint", 16);
mvc_create_column_(m, t, "temporary", "smallint", 16);
if (!first) {
int pub = ROLE_PUBLIC;
int p = PRIV_SELECT;
int zero = 0;
sql_table *privs = find_sql_table(s, "privileges");
table_funcs.table_insert(m->session->tr, privs, &t->base.id, &pub, &p, &zero, &zero);
}
t = mvc_create_view(m, s, "columns", SQL_PERSIST, "SELECT * FROM (SELECT p.* FROM \"sys\".\"_columns\" AS p UNION ALL SELECT t.* FROM \"tmp\".\"_columns\" AS t) AS columns;", 1);
mvc_create_column_(m, t, "id", "int", 32);
mvc_create_column_(m, t, "name", "varchar", 1024);
mvc_create_column_(m, t, "type", "varchar", 1024);
mvc_create_column_(m, t, "type_digits", "int", 32);
mvc_create_column_(m, t, "type_scale", "int", 32);
mvc_create_column_(m, t, "table_id", "int", 32);
mvc_create_column_(m, t, "default", "varchar", 2048);
mvc_create_column_(m, t, "null", "boolean", 1);
mvc_create_column_(m, t, "number", "int", 32);
mvc_create_column_(m, t, "storage", "varchar", 2048);
if (!first) {
int pub = ROLE_PUBLIC;
int p = PRIV_SELECT;
int zero = 0;
sql_table *privs = find_sql_table(s, "privileges");
table_funcs.table_insert(m->session->tr, privs, &t->base.id, &pub, &p, &zero, &zero);
} else {
sql_create_env(m, s);
sql_create_privileges(m, s);
}
s = m->session->schema = mvc_bind_schema(m, "tmp");
assert(m->session->schema != NULL);
if (mvc_commit(m, 0, NULL) < 0) {
fprintf(stderr, "!mvc_init: unable to commit system tables\n");
return -1;
}
mvc_destroy(m);
}
return first;
}
static sql_rel *
create_trigger(mvc *sql, dlist *qname, int time, symbol *trigger_event, dlist *tqname, dlist *opt_ref, dlist *triggered_action, int replace)
{
const char *triggerschema = qname_schema(qname);
const char *triggername = qname_table(qname);
const char *sname = qname_schema(tqname);
const char *tname = qname_table(tqname);
sql_schema *ss = cur_schema(sql);
sql_table *t = NULL;
sql_trigger *st = NULL;
int instantiate = (sql->emode == m_instantiate);
int create = (!instantiate && sql->emode != m_deps), event, orientation;
list *sq = NULL;
sql_rel *r = NULL;
char *q, *base = replace ? "CREATE OR REPLACE" : "CREATE";
dlist *columns = trigger_event->data.lval;
const char *old_name = NULL, *new_name = NULL;
dlist *stmts = triggered_action->h->next->next->data.lval;
symbol *condition = triggered_action->h->next->data.sym;
if (!sname)
sname = ss->base.name;
if (sname && !(ss = mvc_bind_schema(sql, sname)))
return sql_error(sql, 02, SQLSTATE(3F000) "%s TRIGGER: no such schema '%s'", base, sname);
if (opt_ref) {
dnode *dl = opt_ref->h;
for ( ; dl; dl = dl->next) {
/* list (new(1)/old(0)), char */
char *n = dl->data.lval->h->next->data.sval;
assert(dl->data.lval->h->type == type_int);
if (!dl->data.lval->h->data.i_val) /*?l_val?*/
old_name = n;
else
new_name = n;
}
}
if (create && !mvc_schema_privs(sql, ss))
return sql_error(sql, 02, SQLSTATE(42000) "%s TRIGGER: access denied for %s to schema ;'%s'", base, stack_get_string(sql, "current_user"), ss->base.name);
if (create && !(t = mvc_bind_table(sql, ss, tname)))
return sql_error(sql, 02, SQLSTATE(42000) "%s TRIGGER: unknown table '%s'", base, tname);
if (create && isView(t))
return sql_error(sql, 02, SQLSTATE(42000) "%s TRIGGER: cannot create trigger on view '%s'", base, tname);
if (triggerschema && strcmp(triggerschema, sname) != 0)
return sql_error(sql, 02, SQLSTATE(42000) "%s TRIGGER: trigger and respective table must belong to the same schema", base);
if (create && (st = mvc_bind_trigger(sql, ss, triggername)) != NULL) {
if (replace) {
if(mvc_drop_trigger(sql, ss, st))
return sql_error(sql, 02, SQLSTATE(HY001) "%s TRIGGER: %s", base, MAL_MALLOC_FAIL);
} else {
return sql_error(sql, 02, SQLSTATE(42000) "%s TRIGGER: name '%s' already in use", base, triggername);
}
}
if (create) {
switch (trigger_event->token) {
case SQL_INSERT:
event = 0;
break;
case SQL_DELETE:
event = 1;
break;
case SQL_TRUNCATE:
event = 3;
break;
default:
event = 2;
break;
}
orientation = triggered_action->h->data.i_val;
q = query_cleaned(QUERY(sql->scanner));
assert(triggered_action->h->type == type_int);
r = rel_create_trigger(sql, t->s->base.name, t->base.name, triggername, time, orientation, event, old_name, new_name, condition, q);
GDKfree(q);
return r;
}
if (!instantiate) {
t = mvc_bind_table(sql, ss, tname);
if(!stack_push_frame(sql, "OLD-NEW"))
return sql_error(sql, 02, SQLSTATE(HY001) MAL_MALLOC_FAIL);
/* we need to add the old and new tables */
if (!instantiate && new_name) {
if(!_stack_push_table(sql, new_name, t))
return sql_error(sql, 02, SQLSTATE(HY001) MAL_MALLOC_FAIL);
}
if (!instantiate && old_name) {
if(!_stack_push_table(sql, old_name, t))
return sql_error(sql, 02, SQLSTATE(HY001) MAL_MALLOC_FAIL);
}
}
if (condition) {
sql_rel *rel = NULL;
if (new_name) /* in case of updates same relations is available via both names */
rel = stack_find_rel_view(sql, new_name);
if (!rel && old_name)
rel = stack_find_rel_view(sql, old_name);
if (rel)
rel = rel_logical_exp(sql, rel, condition, sql_where);
if (!rel)
return NULL;
/* transition tables */
/* insert: rel_select(table [new], searchcondition) */
/* delete: rel_select(table [old], searchcondition) */
/* update: rel_select(table [old,new]), searchcondition) */
if (new_name)
stack_update_rel_view(sql, new_name, rel);
if (old_name)
stack_update_rel_view(sql, old_name, new_name?rel_dup(rel):rel);
}
sq = sequential_block(sql, NULL, NULL, stmts, NULL, 1);
r = rel_psm_block(sql->sa, sq);
/* todo trigger_columns */
(void)columns;
return r;
}
str
SQLinitClient(Client c)
{
mvc *m;
str schema;
str msg = MAL_SUCCEED;
backend *be;
bstream *bfd = NULL;
stream *fd = NULL;
static int maybeupgrade = 1;
#ifdef _SQL_SCENARIO_DEBUG
mnstr_printf(GDKout, "#SQLinitClient\n");
#endif
if (SQLinitialized == 0 && (msg = SQLprelude(NULL)) != MAL_SUCCEED)
return msg;
MT_lock_set(&sql_contextLock);
/*
* Based on the initialization return value we can prepare a SQLinit
* string with all information needed to initialize the catalog
* based on the mandatory scripts to be executed.
*/
if (sqlinit) { /* add sqlinit to the fdin stack */
buffer *b = (buffer *) GDKmalloc(sizeof(buffer));
size_t len = strlen(sqlinit);
bstream *fdin;
buffer_init(b, _STRDUP(sqlinit), len);
fdin = bstream_create(buffer_rastream(b, "si"), b->len);
bstream_next(fdin);
MCpushClientInput(c, fdin, 0, "");
}
if (c->sqlcontext == 0) {
m = mvc_create(c->idx, 0, SQLdebug, c->fdin, c->fdout);
global_variables(m, "monetdb", "sys");
if (isAdministrator(c) || strcmp(c->scenario, "msql") == 0) /* console should return everything */
m->reply_size = -1;
be = (void *) backend_create(m, c);
} else {
be = c->sqlcontext;
m = be->mvc;
mvc_reset(m, c->fdin, c->fdout, SQLdebug, NR_GLOBAL_VARS);
backend_reset(be);
}
if (m->session->tr)
reset_functions(m->session->tr);
/* pass through credentials of the user if not console */
schema = monet5_user_set_def_schema(m, c->user);
if (!schema) {
_DELETE(schema);
throw(PERMD, "SQLinitClient", "08004!schema authorization error");
}
_DELETE(schema);
/*expect SQL text first */
be->language = 'S';
/* Set state, this indicates an initialized client scenario */
c->state[MAL_SCENARIO_READER] = c;
c->state[MAL_SCENARIO_PARSER] = c;
c->state[MAL_SCENARIO_OPTIMIZE] = c;
c->sqlcontext = be;
initSQLreferences();
/* initialize the database with predefined SQL functions */
if (SQLnewcatalog == 0) {
/* check whether table sys.systemfunctions exists: if
* it doesn't, this is probably a restart of the
* server after an incomplete initialization */
sql_schema *s = mvc_bind_schema(m, "sys");
sql_table *t = s ? mvc_bind_table(m, s, "systemfunctions") : NULL;
if (t == NULL)
SQLnewcatalog = 1;
}
if (SQLnewcatalog > 0) {
char path[PATHLENGTH];
str fullname;
SQLnewcatalog = 0;
maybeupgrade = 0;
snprintf(path, PATHLENGTH, "createdb");
slash_2_dir_sep(path);
fullname = MSP_locate_sqlscript(path, 1);
if (fullname) {
str filename = fullname;
str p, n;
fprintf(stdout, "# SQL catalog created, loading sql scripts once\n");
do {
p = strchr(filename, PATH_SEP);
if (p)
*p = '\0';
if ((n = strrchr(filename, DIR_SEP)) == NULL) {
n = filename;
} else {
n++;
}
fprintf(stdout, "# loading sql script: %s\n", n);
fd = open_rastream(filename);
if (p)
filename = p + 1;
if (fd) {
size_t sz;
sz = getFileSize(fd);
if (sz > (size_t) 1 << 29) {
mnstr_destroy(fd);
msg = createException(MAL, "createdb", "file %s too large to process", filename);
} else {
bfd = bstream_create(fd, sz == 0 ? (size_t) (128 * BLOCK) : sz);
if (bfd && bstream_next(bfd) >= 0)
msg = SQLstatementIntern(c, &bfd->buf, "sql.init", TRUE, FALSE, NULL);
bstream_destroy(bfd);
}
if (m->sa)
sa_destroy(m->sa);
m->sa = NULL;
if (msg)
p = NULL;
}
} while (p);
GDKfree(fullname);
} else
fprintf(stderr, "!could not read createdb.sql\n");
} else { /* handle upgrades */
if (!m->sa)
m->sa = sa_create();
if (maybeupgrade)
SQLupgrades(c,m);
maybeupgrade = 0;
}
MT_lock_unset(&sql_contextLock);
fflush(stdout);
fflush(stderr);
/* send error from create scripts back to the first client */
if (msg) {
error(c->fdout, msg);
handle_error(m, c->fdout, 0);
sqlcleanup(m, mvc_status(m));
}
return msg;
}
str FITSattach(Client cntxt, MalBlkPtr mb, MalStkPtr stk, InstrPtr pci)
{
mvc *m = NULL;
sql_trans *tr;
sql_schema *sch;
sql_table *fits_tp, *fits_fl, *fits_tbl, *fits_col, *tbl = NULL;
sql_column *col;
str msg = MAL_SUCCEED;
str fname = *(str*)getArgReference(stk, pci, 1);
fitsfile *fptr; /* pointer to the FITS file */
int status = 0, i, j, hdutype, hdunum = 1, cnum = 0, bitpixnumber = 0;
oid fid, tid, cid, rid = oid_nil;
char tname[BUFSIZ], *tname_low = NULL, *s, bname[BUFSIZ], stmt[BUFSIZ];
long tbcol;
char cname[BUFSIZ], tform[BUFSIZ], tunit[BUFSIZ], tnull[BUFSIZ], tdisp[BUFSIZ];
double tscal, tzero;
char xtensionname[BUFSIZ] = "", stilversion[BUFSIZ] = "";
char stilclass[BUFSIZ] = "", tdate[BUFSIZ] = "", orig[BUFSIZ] = "", comm[BUFSIZ] = "";
msg = getSQLContext(cntxt, mb, &m, NULL);
if (msg)
return msg;
if (fits_open_file(&fptr, fname, READONLY, &status)) {
msg = createException(MAL, "fits.attach", "Missing FITS file %s.\n", fname);
return msg;
}
tr = m->session->tr;
sch = mvc_bind_schema(m, "sys");
fits_fl = mvc_bind_table(m, sch, "fits_files");
if (fits_fl == NULL)
FITSinitCatalog(m);
fits_fl = mvc_bind_table(m, sch, "fits_files");
fits_tbl = mvc_bind_table(m, sch, "fits_tables");
fits_col = mvc_bind_table(m, sch, "fits_columns");
fits_tp = mvc_bind_table(m, sch, "fits_table_properties");
/* check if the file is already attached */
col = mvc_bind_column(m, fits_fl, "name");
rid = table_funcs.column_find_row(m->session->tr, col, fname, NULL);
if (rid != oid_nil) {
fits_close_file(fptr, &status);
msg = createException(SQL, "fits.attach", "File %s already attached\n", fname);
return msg;
}
/* add row in the fits_files catalog table */
col = mvc_bind_column(m, fits_fl, "id");
fid = store_funcs.count_col(tr, col, 1) + 1;
store_funcs.append_col(m->session->tr,
mvc_bind_column(m, fits_fl, "id"), &fid, TYPE_int);
store_funcs.append_col(m->session->tr,
mvc_bind_column(m, fits_fl, "name"), fname, TYPE_str);
col = mvc_bind_column(m, fits_tbl, "id");
tid = store_funcs.count_col(tr, col, 1) + 1;
if ((s = strrchr(fname, DIR_SEP)) == NULL)
s = fname;
else
s++;
strcpy(bname, s);
s = strrchr(bname, '.');
if (s) *s = 0;
fits_get_num_hdus(fptr, &hdunum, &status);
for (i = 1; i <= hdunum; i++) {
fits_movabs_hdu(fptr, i, &hdutype, &status);
if (hdutype != ASCII_TBL && hdutype != BINARY_TBL)
continue;
/* SQL table name - the name of FITS extention */
fits_read_key(fptr, TSTRING, "EXTNAME", tname, NULL, &status);
if (status) {
snprintf(tname, BUFSIZ, "%s_%d", bname, i);
tname_low = toLower(tname);
status = 0;
}else { /* check table name for existence in the fits catalog */
tname_low = toLower(tname);
col = mvc_bind_column(m, fits_tbl, "name");
rid = table_funcs.column_find_row(m->session->tr, col, tname_low, NULL);
/* or as regular SQL table */
tbl = mvc_bind_table(m, sch, tname_low);
if (rid != oid_nil || tbl) {
snprintf(tname, BUFSIZ, "%s_%d", bname, i);
tname_low = toLower(tname);
}
}
fits_read_key(fptr, TSTRING, "BITPIX", &bitpixnumber, NULL, &status);
if (status) {
status = 0;
}
fits_read_key(fptr, TSTRING, "DATE-HDU", tdate, NULL, &status);
if (status) {
status = 0;
}
fits_read_key(fptr, TSTRING, "XTENSION", xtensionname, NULL, &status);
if (status) {
status = 0;
}
fits_read_key(fptr, TSTRING, "STILVERS", stilversion, NULL, &status);
if (status) {
status = 0;
}
fits_read_key(fptr, TSTRING, "STILCLAS", stilclass, NULL, &status);
if (status) {
status = 0;
}
fits_read_key(fptr, TSTRING, "ORIGIN", orig, NULL, &status);
if (status) {
status = 0;
}
fits_read_key(fptr, TSTRING, "COMMENT", comm, NULL, &status);
if (status) {
status = 0;
}
fits_get_num_cols(fptr, &cnum, &status);
store_funcs.append_col(m->session->tr,
mvc_bind_column(m, fits_tbl, "id"), &tid, TYPE_int);
store_funcs.append_col(m->session->tr,
mvc_bind_column(m, fits_tbl, "name"), tname_low, TYPE_str);
store_funcs.append_col(m->session->tr,
mvc_bind_column(m, fits_tbl, "columns"), &cnum, TYPE_int);
store_funcs.append_col(m->session->tr,
mvc_bind_column(m, fits_tbl, "file_id"), &fid, TYPE_int);
store_funcs.append_col(m->session->tr,
mvc_bind_column(m, fits_tbl, "hdu"), &i, TYPE_int);
store_funcs.append_col(m->session->tr,
mvc_bind_column(m, fits_tbl, "date"), tdate, TYPE_str);
store_funcs.append_col(m->session->tr,
mvc_bind_column(m, fits_tbl, "origin"), orig, TYPE_str);
store_funcs.append_col(m->session->tr,
mvc_bind_column(m, fits_tbl, "comment"), comm, TYPE_str);
store_funcs.append_col(m->session->tr,
mvc_bind_column(m, fits_tp, "table_id"), &tid, TYPE_int);
store_funcs.append_col(m->session->tr,
mvc_bind_column(m, fits_tp, "xtension"), xtensionname, TYPE_str);
store_funcs.append_col(m->session->tr,
mvc_bind_column(m, fits_tp, "bitpix"), &bitpixnumber, TYPE_int);
store_funcs.append_col(m->session->tr,
mvc_bind_column(m, fits_tp, "stilvers"), stilversion, TYPE_str);
store_funcs.append_col(m->session->tr,
mvc_bind_column(m, fits_tp, "stilclas"), stilclass, TYPE_str);
/* read columns description */
s = stmt;
col = mvc_bind_column(m, fits_col, "id");
cid = store_funcs.count_col(tr, col, 1) + 1;
for (j = 1; j <= cnum; j++, cid++) {
fits_get_acolparms(fptr, j, cname, &tbcol, tunit, tform, &tscal, &tzero, tnull, tdisp, &status);
snprintf(stmt, BUFSIZ, FITS_INS_COL, (int)cid, cname, tform, tunit, j, (int)tid);
msg = SQLstatementIntern(cntxt, &s, "fits.attach", TRUE, FALSE);
if (msg != MAL_SUCCEED) {
fits_close_file(fptr, &status);
return msg;
}
}
tid++;
}
fits_close_file(fptr, &status);
return MAL_SUCCEED;
}
