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 void
monet5_create_privileges(ptr _mvc, sql_schema *s)
{
sql_table *t, *uinfo;
mvc *m = (mvc *) _mvc;
char *err = NULL;
int schema_id = 0;
str monetdbuser = "******";
list *res, *ops;
/* create the authorisation related tables */
t = mvc_create_table(m, s, "db_user_info", tt_table, 1, SQL_PERSIST, 0, -1);
mvc_create_column_(m, t, "name", "varchar", 1024);
mvc_create_column_(m, t, "fullname", "varchar", 2048);
mvc_create_column_(m, t, "default_schema", "int", 9);
uinfo = t;
(void) err;
res = sa_list(m->sa);
list_append(res, sql_create_arg(m->sa, "name", sql_bind_subtype(m->sa, "varchar", 2048, 0), ARG_OUT));
/* add function */
ops = sa_list(m->sa);
/* following funcion returns a table (single column) of user names
with the approriate scenario (sql) */
mvc_create_func(m, NULL, s, "db_users", ops, res, F_UNION, FUNC_LANG_SQL, "sql", "db_users", "CREATE FUNCTION db_users () RETURNS TABLE( name varchar(2048)) EXTERNAL NAME sql.db_users;", FALSE, FALSE);
t = mvc_create_view(m, s, "users", SQL_PERSIST,
"SELECT u.\"name\" AS \"name\", "
"ui.\"fullname\", ui.\"default_schema\" "
"FROM db_users() AS u LEFT JOIN "
"\"sys\".\"db_user_info\" AS ui "
"ON u.\"name\" = ui.\"name\" "
";", 1);
mvc_create_column_(m, t, "name", "varchar", 1024);
mvc_create_column_(m, t, "fullname", "varchar", 2024);
mvc_create_column_(m, t, "default_schema", "int", 9);
schema_id = sql_find_schema(m, "sys");
assert(schema_id >= 0);
table_funcs.table_insert(m->session->tr, uinfo, monetdbuser, "MonetDB Admin", &schema_id);
}
int
sql_create_privileges(mvc *m, sql_schema *s)
{
int pub, p, zero = 0;
sql_table *t, *privs;
backend_create_privileges(m, s);
t = mvc_create_table(m, s, "user_role", tt_table, 1, SQL_PERSIST, 0, -1);
mvc_create_column_(m, t, "login_id", "int", 32);
mvc_create_column_(m, t, "role_id", "int", 32);
/*
mvc_create_column_(m, t, "grantor", "int", 32);
mvc_create_column_(m, t, "admin", "int", 32);
*/
/* all roles and users are in the auths table */
t = mvc_create_table(m, s, "auths", tt_table, 1, SQL_PERSIST, 0, -1);
mvc_create_column_(m, t, "id", "int", 32);
mvc_create_column_(m, t, "name", "varchar", 1024);
mvc_create_column_(m, t, "grantor", "int", 32);
t = mvc_create_table(m, s, "privileges", tt_table, 1, SQL_PERSIST, 0, -1);
mvc_create_column_(m, t, "obj_id", "int", 32);
mvc_create_column_(m, t, "auth_id", "int", 32);
mvc_create_column_(m, t, "privileges", "int", 32);
mvc_create_column_(m, t, "grantor", "int", 32);
mvc_create_column_(m, t, "grantable", "int", 32);
/* add sysadmin roles */
sql_create_role_id(m, ROLE_PUBLIC, "public", 0);
sql_create_role_id(m, ROLE_SYSADMIN, "sysadmin", 0);
sql_create_role_id(m, USER_MONETDB, "monetdb", 0);
pub = ROLE_PUBLIC;
p = PRIV_SELECT;
privs = find_sql_table(s, "privileges");
t = find_sql_table(s, "schemas");
table_funcs.table_insert(m->session->tr, privs, &t->base.id, &pub, &p, &zero, &zero);
t = find_sql_table(s, "types");
table_funcs.table_insert(m->session->tr, privs, &t->base.id, &pub, &p, &zero, &zero);
t = find_sql_table(s, "functions");
table_funcs.table_insert(m->session->tr, privs, &t->base.id, &pub, &p, &zero, &zero);
t = find_sql_table(s, "args");
table_funcs.table_insert(m->session->tr, privs, &t->base.id, &pub, &p, &zero, &zero);
t = find_sql_table(s, "sequences");
table_funcs.table_insert(m->session->tr, privs, &t->base.id, &pub, &p, &zero, &zero);
t = find_sql_table(s, "dependencies");
table_funcs.table_insert(m->session->tr, privs, &t->base.id, &pub, &p, &zero, &zero);
t = find_sql_table(s, "connections");
table_funcs.table_insert(m->session->tr, privs, &t->base.id, &pub, &p, &zero, &zero);
t = find_sql_table(s, "_tables");
table_funcs.table_insert(m->session->tr, privs, &t->base.id, &pub, &p, &zero, &zero);
t = find_sql_table(s, "_columns");
table_funcs.table_insert(m->session->tr, privs, &t->base.id, &pub, &p, &zero, &zero);
t = find_sql_table(s, "keys");
table_funcs.table_insert(m->session->tr, privs, &t->base.id, &pub, &p, &zero, &zero);
t = find_sql_table(s, "idxs");
table_funcs.table_insert(m->session->tr, privs, &t->base.id, &pub, &p, &zero, &zero);
t = find_sql_table(s, "triggers");
table_funcs.table_insert(m->session->tr, privs, &t->base.id, &pub, &p, &zero, &zero);
t = find_sql_table(s, "objects");
table_funcs.table_insert(m->session->tr, privs, &t->base.id, &pub, &p, &zero, &zero);
t = find_sql_table(s, "tables");
table_funcs.table_insert(m->session->tr, privs, &t->base.id, &pub, &p, &zero, &zero);
t = find_sql_table(s, "columns");
table_funcs.table_insert(m->session->tr, privs, &t->base.id, &pub, &p, &zero, &zero);
t = find_sql_table(s, "user_role");
table_funcs.table_insert(m->session->tr, privs, &t->base.id, &pub, &p, &zero, &zero);
t = find_sql_table(s, "auths");
table_funcs.table_insert(m->session->tr, privs, &t->base.id, &pub, &p, &zero, &zero);
t = find_sql_table(s, "privileges");
table_funcs.table_insert(m->session->tr, privs, &t->base.id, &pub, &p, &zero, &zero);
/* owned by the users anyway
s = mvc_bind_schema(m, "tmp");
t = find_sql_table(s, "profile");
table_funcs.table_insert(m->session->tr, privs, &t->base.id, &pub, &p, &zero, &zero);
t = find_sql_table(s, "_tables");
table_funcs.table_insert(m->session->tr, privs, &t->base.id, &pub, &p, &zero, &zero);
t = find_sql_table(s, "_columns");
table_funcs.table_insert(m->session->tr, privs, &t->base.id, &pub, &p, &zero, &zero);
t = find_sql_table(s, "keys");
table_funcs.table_insert(m->session->tr, privs, &t->base.id, &pub, &p, &zero, &zero);
t = find_sql_table(s, "idxs");
table_funcs.table_insert(m->session->tr, privs, &t->base.id, &pub, &p, &zero, &zero);
t = find_sql_table(s, "triggers");
table_funcs.table_insert(m->session->tr, privs, &t->base.id, &pub, &p, &zero, &zero);
t = find_sql_table(s, "objects");
table_funcs.table_insert(m->session->tr, privs, &t->base.id, &pub, &p, &zero, &zero);
*/
return 0;
}
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;
}
