str
monet5_user_get_def_schema(mvc *m, int user)
{
oid rid;
sqlid schema_id;
sql_schema *sys = NULL;
sql_table *user_info = NULL;
sql_column *users_name = NULL;
sql_column *users_schema = NULL;
sql_table *schemas = NULL;
sql_column *schemas_name = NULL;
sql_column *schemas_id = NULL;
sql_table *auths = NULL;
sql_column *auths_id = NULL;
sql_column *auths_name = NULL;
void *p = 0;
str username = NULL;
str schema = NULL;
sys = find_sql_schema(m->session->tr, "sys");
auths = find_sql_table(sys, "auths");
auths_id = find_sql_column(auths, "id");
auths_name = find_sql_column(auths, "name");
rid = table_funcs.column_find_row(m->session->tr, auths_id, &user, NULL);
if (!is_oid_nil(rid))
username = table_funcs.column_find_value(m->session->tr, auths_name, rid);
user_info = find_sql_table(sys, "db_user_info");
users_name = find_sql_column(user_info, "name");
users_schema = find_sql_column(user_info, "default_schema");
rid = table_funcs.column_find_row(m->session->tr, users_name, username, NULL);
if (!is_oid_nil(rid))
p = table_funcs.column_find_value(m->session->tr, users_schema, rid);
_DELETE(username);
assert(p);
schema_id = *(sqlid *) p;
_DELETE(p);
schemas = find_sql_table(sys, "schemas");
schemas_name = find_sql_column(schemas, "name");
schemas_id = find_sql_column(schemas, "id");
rid = table_funcs.column_find_row(m->session->tr, schemas_id, &schema_id, NULL);
if (!is_oid_nil(rid))
schema = table_funcs.column_find_value(m->session->tr, schemas_name, rid);
if(!stack_set_string(m, "current_schema", schema))
return NULL;
return schema;
}
static int
table_delete(sql_trans *tr, sql_table *t, oid rid)
{
assert(!is_oid_nil(rid));
return store_funcs.delete_tab(tr, t, &rid, TYPE_oid);
}
static int
column_update_value(sql_trans *tr, sql_column *c, oid rid, void *value)
{
assert(!is_oid_nil(rid));
return store_funcs.update_col(tr, c, &rid, value, c->type.type->localtype);
}
static int
monet5_drop_user(ptr _mvc, str user)
{
mvc *m = (mvc *) _mvc;
oid rid;
sql_schema *sys;
sql_table *users;
sql_column *users_name;
str err;
Client c = MCgetClient(m->clientid);
err = AUTHremoveUser(c, user);
if (err !=MAL_SUCCEED) {
(void) sql_error(m, 02, "DROP USER: %s", getExceptionMessage(err));
_DELETE(err);
return FALSE;
}
sys = find_sql_schema(m->session->tr, "sys");
users = find_sql_table(sys, "db_user_info");
users_name = find_sql_column(users, "name");
rid = table_funcs.column_find_row(m->session->tr, users_name, user, NULL);
if (!is_oid_nil(rid))
table_funcs.table_delete(m->session->tr, users, rid);
/* FIXME: We have to ignore this inconsistency here, because the
* user was already removed from the system authorisation. Once
* we have warnings, we could issue a warning about this
* (seemingly) inconsistency between system and sql shadow
* administration. */
return TRUE;
}
/* Return TRUE if the value in V is NIL. */
int
VALisnil(const ValRecord *v)
{
switch (v->vtype) {
case TYPE_void:
return 1;
case TYPE_bte:
return is_bte_nil(v->val.btval);
case TYPE_sht:
return is_sht_nil(v->val.shval);
case TYPE_int:
return is_int_nil(v->val.ival);
case TYPE_lng:
return is_lng_nil(v->val.lval);
#ifdef HAVE_HGE
case TYPE_hge:
return is_hge_nil(v->val.hval);
#endif
case TYPE_flt:
return is_flt_nil(v->val.fval);
case TYPE_dbl:
return is_dbl_nil(v->val.dval);
case TYPE_oid:
return is_oid_nil(v->val.oval);
case TYPE_bat:
return is_bat_nil(v->val.bval);
default:
break;
}
return (*ATOMcompare(v->vtype))(VALptr(v), ATOMnilptr(v->vtype)) == 0;
}
ssize_t
OIDtoStr(char **dst, size_t *len, const oid *src, bool external)
{
atommem(oidStrlen);
if (is_oid_nil(*src)) {
if (external) {
strcpy(*dst, "nil");
return 3;
}
strcpy(*dst, str_nil);
return 1;
}
return snprintf(*dst, *len, OIDFMT "@0", *src);
}
int
sql_find_auth_schema(mvc *m, str auth)
{
int res = -1;
oid rid;
sql_schema *sys = find_sql_schema(m->session->tr, "sys");
sql_table *users = find_sql_table(sys, "db_user_info");
sql_column *users_name = find_sql_column(users, "name");
rid = table_funcs.column_find_row(m->session->tr, users_name, auth, NULL);
if (!is_oid_nil(rid)) {
sql_column *users_schema = find_sql_column(users, "default_schema");
int *p = (int *) table_funcs.column_find_value(m->session->tr, users_schema, rid);
if (p) {
res = *p;
_DELETE(p);
}
}
return res;
}
str
monet5_user_set_def_schema(mvc *m, oid user)
{
oid rid;
sqlid schema_id;
sql_schema *sys = NULL;
sql_table *user_info = NULL;
sql_column *users_name = NULL;
sql_column *users_schema = NULL;
sql_table *schemas = NULL;
sql_column *schemas_name = NULL;
sql_column *schemas_id = NULL;
sql_table *auths = NULL;
sql_column *auths_name = NULL;
void *p = 0;
str schema = NULL;
str username = NULL;
if (m->debug &1)
fprintf(stderr, "monet5_user_set_def_schema " OIDFMT "\n", user);
mvc_trans(m);
sys = find_sql_schema(m->session->tr, "sys");
user_info = find_sql_table(sys, "db_user_info");
users_name = find_sql_column(user_info, "name");
users_schema = find_sql_column(user_info, "default_schema");
rid = table_funcs.column_find_row(m->session->tr, users_name, username, NULL);
if (!is_oid_nil(rid))
p = table_funcs.column_find_value(m->session->tr, users_schema, rid);
assert(p);
schema_id = *(sqlid *) p;
_DELETE(p);
schemas = find_sql_table(sys, "schemas");
schemas_name = find_sql_column(schemas, "name");
schemas_id = find_sql_column(schemas, "id");
auths = find_sql_table(sys, "auths");
auths_name = find_sql_column(auths, "name");
rid = table_funcs.column_find_row(m->session->tr, schemas_id, &schema_id, NULL);
if (!is_oid_nil(rid))
schema = table_funcs.column_find_value(m->session->tr, schemas_name, rid);
/* only set schema if user is found */
rid = table_funcs.column_find_row(m->session->tr, auths_name, username, NULL);
if (!is_oid_nil(rid)) {
sql_column *auths_id = find_sql_column(auths, "id");
int id;
p = table_funcs.column_find_value(m->session->tr, auths_id, rid);
id = *(int *) p;
_DELETE(p);
m->user_id = m->role_id = id;
} else {
schema = NULL;
}
if (!schema || !mvc_set_schema(m, schema)) {
if (m->session->active)
mvc_rollback(m, 0, NULL);
return NULL;
}
/* reset the user and schema names */
if(!stack_set_string(m, "current_schema", schema) ||
!stack_set_string(m, "current_user", username) ||
!stack_set_string(m, "current_role", username)) {
schema = NULL;
}
GDKfree(username);
mvc_rollback(m, 0, NULL);
return schema;
}
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;
}
