bool Database::GetChatrooms(const Server &Host, std::vector<Chatroom> &Out)
{
Out.clear();
const std::string Query = "SELECT * FROM Chatroom WHERE ServerIP = :ServerIP' AND ServerPort = :ServerPort AND ServerFamily = :ServerFamily";
Statement s;
if (s.Prepare(Inner, Query))
return false;
if (!s.Bind(":ServerIP", Host.Address.GetPrintableIP()) || !s.Bind(":ServerPort", Host.Address.Port) || !s.Bind(":ServerFamily", Host.Address.Family))
return false;
bool MoreData = true;
while (MoreData)
{
if (!s.Step(MoreData))
return false;
Chatroom c;
if (!_GetChatroom(s, c))
return false;
Out.push_back(c);
}
return true;
}
bool Database::InsertServer(const Server &Server)
{
const std::string Query = "INSERT OR REPLACE INTO Server VALUES (:IP, :Port, :Family, :Name)";
Statement s;
if (!s.Prepare(Inner, Query))
return false;
if (!s.Bind(":IP", Server.Address.GetPrintableIP()) || !s.Bind(":Port", Server.Address.Port) || !s.Bind(":Family", Server.Address.Family) || !s.Bind(":Name", Server.Name))
return false;
if (!s.Execute())
return false;
return true;
}
bool Database::InsertUser(const User &User)
{
const std::string Query = "INSERT OR REPLACE INTO User VALUES (:Username, :Password)";
Statement s;
if (!s.Prepare(Inner, Query))
return false;
if (!s.Bind(":Username", User.Username) || !s.Bind(":Password", User.Password))
return false;
if (!s.Execute())
return false;
return true;
}
bool Database::GetChatrooms(const User &Owner, std::vector<Chatroom> &Out)
{
Out.clear();
const std::string Query = "SELECT * FROM Chatroom WHERE OwnerUsername = :OwnerUsername";
Statement s;
if (s.Prepare(Inner, Query))
return false;
if (!s.Bind(":OwnerUsername", Owner.Username))
return false;
bool MoreData = true;
while (MoreData)
{
if (!s.Step(MoreData))
return false;
Chatroom c;
if (!_GetChatroom(s, c))
return false;
Out.push_back(c);
}
return true;
}
bool Database::GetServer(const Net::Address Address, Server &Out)
{
const std::string Query = "SELECT * FROM Server WHERE Family = :Family AND IP = :IP AND Port = :Port";
Statement s;
if (!s.Prepare(Inner, Query))
return false;
if (!s.Bind(":Family", Address.Family) || !s.Bind(":IP", Address.GetPrintableIP()) || !s.Bind(":Port", Address.Port))
return false;
if (!s.Step())
return false;
if (!_GetServer(s, Out))
return false;
return true;
}
bool Database::GetUser(const std::string &Username, User &Out)
{
const std::string Query = "SELECT * FROM User WHERE Username = :Username";
Statement s;
if (!s.Prepare(Inner, Query))
return false;
if (!s.Bind(":Username", Username.c_str()))
return false;
if (!s.Step())
return false;
if (!_GetUser(s, Out))
return false;
return true;
}
bool Database::GetChatroom(const std::string &Name, Chatroom &Out)
{
const std::string Query = "SELECT * FROM Chatroom WHERE Name = :Name";
Statement s;
if (!s.Prepare(Inner, Query))
return false;
if (!s.Bind(":Name", Name))
return false;
if (!s.Step())
return false;
if (!_GetChatroom(s, Out))
return false;
return true;
}
bool Writer::IsGeographic(boost::int32_t srid)
{
typedef boost::shared_ptr<long> shared_long;
typedef boost::shared_ptr<char> shared_char;
std::ostringstream oss;
// char* kind = (char* ) malloc (OWNAME * sizeof(char));
shared_char kind = boost::shared_ptr<char>(new char[OWNAME]);
oss << "SELECT COORD_REF_SYS_KIND from MDSYS.SDO_COORD_REF_SYSTEM WHERE SRID = :1";
Statement statement = Statement(m_connection->CreateStatement(oss.str().c_str()));
shared_long p_srid = boost::shared_ptr<long>(new long);
*p_srid = srid;
statement->Bind(p_srid.get());
statement->Define(kind.get());
try
{
statement->Execute();
}
catch (pdal_error const& e)
{
std::ostringstream oss;
oss << "Failed to fetch geographicness of srid " << srid << std::endl << e.what() << std::endl;
throw std::runtime_error(oss.str());
}
if (boost::iequals(kind.get(), "GEOGRAPHIC2D"))
{
return true;
}
if (boost::iequals(kind.get(), "GEOGRAPHIC3D"))
{
return true;
}
return false;
}
bool Database::InsertChatroom(const Chatroom &Chatroom)
{
const std::string Query = "INSERT OR REPLACE INTO Chatroom VALUES (:Name, :OwnerUsername, :ServerIP, :ServerPort, :ServerFamily, :Password, :Description)";
Statement s;
if (!s.Prepare(Inner, Query))
return false;
if (!s.Bind(":Name", Chatroom.Name) || !s.Bind(":OwnerUsername", Chatroom.OwnerUsername) || !s.Bind(":ServerIP", Chatroom.ServerAddress.GetPrintableIP()) ||
!s.Bind(":ServerPort", Chatroom.ServerAddress.Port) || !s.Bind(":ServerFamily", Chatroom.ServerAddress.Family))
return false;
if (!Chatroom.Password.Null)
{
if (!s.Bind(":Password", Chatroom.Password.Value))
return false;
}
else
{
if (!s.BindNull(":Password"))
return false;
}
if (!Chatroom.Description.Null)
{
if (!s.Bind(":Description", Chatroom.Description.Value))
return false;
}
else
{
if (!s.BindNull(":Description"))
return false;
}
if (!s.Execute())
return false;
return true;
}
void Writer::CreatePCEntry(Schema const& buffer_schema)
{
boost::uint32_t precision = getDefaultedOption<boost::uint32_t>("stream_output_precision");
boost::uint32_t capacity = getDefaultedOption<boost::uint32_t>("capacity");
std::ostringstream oss;
oss.setf(std::ios_base::fixed, std::ios_base::floatfield);
oss.precision(precision);
std::ostringstream columns;
std::ostringstream values;
if (!m_base_table_aux_columns.empty())
{
columns << m_cloud_column_name << "," << m_base_table_aux_columns;
values << "pc," << m_base_table_aux_values;
}
else
{
columns << m_cloud_column_name;
values << "pc";
}
int nPCPos = 1;
int nSchemaPos = 1;
nSchemaPos++;
int nPos = nSchemaPos; // Bind column position
if (!m_base_table_boundary_column.empty())
{
columns << "," << m_base_table_boundary_column;
nPos++;
values <<", SDO_GEOMETRY(:"<<nPos;
nPos++;
values <<", :"<<nPos<<")";
}
std::ostringstream s_srid;
std::ostringstream s_geom;
std::ostringstream s_schema;
// IsGeographic(srid);
if (m_srid == 0)
{
s_srid << "NULL";
}
else
{
s_srid << m_srid;
}
s_schema << "xmltype(:"<<nSchemaPos<<")";
std::string eleminfo = CreatePCElemInfo();
pdal::Bounds<double> base_table_bounds = getDefaultedOption<pdal::Bounds<double> >("base_table_bounds");
if (base_table_bounds.empty())
{
if (IsGeographic(m_srid))
{
base_table_bounds.setMinimum(0, -179.99);
base_table_bounds.setMinimum(1, -89.99);
base_table_bounds.setMinimum(2, 0.0);
base_table_bounds.setMaximum(0, 179.99);
base_table_bounds.setMaximum(1, 89.99);
base_table_bounds.setMaximum(2, 20000.0);
}
else
{
base_table_bounds.setMinimum(0, 0.0);
base_table_bounds.setMinimum(1, 0.0);
base_table_bounds.setMinimum(2, 0.0);
base_table_bounds.setMaximum(0, 100.0);
base_table_bounds.setMaximum(1, 100.0);
base_table_bounds.setMaximum(2, 20000.0);
}
}
s_geom << " mdsys.sdo_geometry("<< m_gtype <<", "<<s_srid.str()<<", null,\n"
" mdsys.sdo_elem_info_array"<< eleminfo <<",\n"
" mdsys.sdo_ordinate_array(\n";
s_geom << base_table_bounds.getMinimum(0) << "," << base_table_bounds.getMinimum(1) << ",";
if (m_is3d)
{
s_geom << base_table_bounds.getMinimum(2) << ",";
}
s_geom << base_table_bounds.getMaximum(0) << "," << base_table_bounds.getMaximum(1);
if (m_is3d)
{
s_geom << "," << base_table_bounds.getMaximum(2);
}
s_geom << "))";
boost::uint32_t dimensions = 8;
oss << "declare\n"
" pc_id NUMBER := :"<<nPCPos<<";\n"
" pc sdo_pc;\n"
"begin\n"
" -- Initialize the Point Cloud object.\n"
" pc := sdo_pc_pkg.init( \n"
" '"<< m_base_table_name<<"', -- Table that has the SDO_POINT_CLOUD column defined\n"
" '"<< m_cloud_column_name<<"', -- Column name of the SDO_POINT_CLOUD object\n"
" '"<< m_block_table_name <<"', -- Table to store blocks of the point cloud\n"
" 'blk_capacity="<< capacity <<"', -- max # of points per block\n"
<< s_geom.str() <<
", -- Extent\n"
" 0.5, -- Tolerance for point cloud\n"
" "<<dimensions<<", -- Total number of dimensions\n"
" NULL,"
" NULL,"
" "<< s_schema.str() <<");\n"
" :"<<nPCPos<<" := pc.pc_id;\n"
" -- Insert the Point Cloud object into the \"base\" table.\n"
" insert into " << m_base_table_name << " ( ID, "<< columns.str() <<
") values ( pc.pc_id, " << values.str() << ");\n"
" "
"end;\n";
Statement statement = Statement(m_connection->CreateStatement(oss.str().c_str()));
statement->Bind(&m_pc_id);
OCILobLocator* schema_locator ;
OCILobLocator* boundary_locator ;
std::string schema_data;
bool pack = getOptions().getValueOrDefault<bool>("pack_ignored_fields", true);
if (pack)
{
schema::index_by_index const& idx = buffer_schema.getDimensions().get<schema::index>();
log()->get(logDEBUG3) << "Packing ignored dimension from PointBuffer " << std::endl;
boost::uint32_t position(0);
pdal::Schema clean_schema;
schema::index_by_index::size_type i(0);
for (i = 0; i < idx.size(); ++i)
{
if (! idx[i].isIgnored())
{
Dimension d(idx[i]);
d.setPosition(position);
// Wipe off parent/child relationships if we're ignoring
// same-named dimensions
d.setParent(boost::uuids::nil_uuid());
clean_schema.appendDimension(d);
position++;
}
}
schema_data = pdal::Schema::to_xml(clean_schema);
} else
{
schema_data = pdal::Schema::to_xml(buffer_schema);
}
char* schema = (char*) malloc(schema_data.size() * sizeof(char) + 1);
strncpy(schema, schema_data.c_str(), schema_data.size());
schema[schema_data.size()] = '\0';
statement->WriteCLob(&schema_locator, schema);
statement->Bind(&schema_locator);
std::ostringstream wkt_s;
if (!m_base_table_boundary_column.empty())
{
if (!FileUtils::fileExists(m_base_table_boundary_wkt))
{
if (!IsValidWKT(m_base_table_boundary_wkt))
{
std::ostringstream oss;
oss << "WKT for base_table_boundary_wkt was not valid and '" << m_base_table_boundary_wkt
<< "' doesn't exist as a file";
throw pdal::pdal_error(oss.str());
}
wkt_s << m_base_table_boundary_wkt;
}
else
{
std::string wkt = LoadSQLData(m_base_table_boundary_wkt);
if (!IsValidWKT(wkt))
{
std::ostringstream oss;
oss << "WKT for base_table_boundary_wkt was from file '" << m_base_table_boundary_wkt
<< "' is not valid";
throw pdal::pdal_error(oss.str());
}
wkt_s << wkt;
}
}
std::string wkt_string = wkt_s.str();
char* wkt = (char*) malloc(wkt_string.size() * sizeof(char)+1);
strncpy(wkt, wkt_string.c_str(), wkt_string.size());
wkt[wkt_string.size()] = '\0';
if (!m_base_table_boundary_column.empty())
{
statement->WriteCLob(&boundary_locator, wkt);
statement->Bind(&boundary_locator);
statement->Bind((int*)&m_srid);
}
try
{
statement->Execute();
}
catch (std::runtime_error const& e)
{
std::ostringstream oss;
oss << "Failed at creating Point Cloud entry into " << m_base_table_name << " table. Does the table exist? " << e.what();
throw pdal_error(oss.str());
}
free(wkt);
try
{
Option& pc_id = getOptions().getOptionByRef("pc_id");
pc_id.setValue(m_pc_id);
}
catch (pdal::option_not_found&)
{
Option pc_id("pc_id", m_pc_id, "Point Cloud Id");
getOptions().add(pc_id);
}
}
bool Writer::WriteBlock(PointBuffer const& buffer)
{
bool bUsePartition = m_block_table_partition_column.size() != 0;
// Pluck the block id out of the first point in the buffer
pdal::Schema const& schema = buffer.getSchema();
Dimension const& blockDim = schema.getDimension("BlockID");
boost::int32_t block_id = buffer.getField<boost::int32_t>(blockDim, 0);
std::ostringstream oss;
std::ostringstream partition;
if (bUsePartition)
{
partition << "," << m_block_table_partition_column;
}
oss << "INSERT INTO "<< m_block_table_name <<
"(OBJ_ID, BLK_ID, NUM_POINTS, POINTS, "
"PCBLK_MIN_RES, BLK_EXTENT, PCBLK_MAX_RES, NUM_UNSORTED_POINTS, PT_SORT_DIM";
if (bUsePartition)
oss << partition.str();
oss << ") "
"VALUES ( :1, :2, :3, :4, 1, mdsys.sdo_geometry(:5, :6, null,:7, :8)"
", 1, 0, 1";
if (bUsePartition)
oss << ", :9";
oss <<")";
// TODO: If gotdata == false below, this memory probably leaks --mloskot
OCILobLocator** locator =(OCILobLocator**) VSIMalloc(sizeof(OCILobLocator*) * 1);
Statement statement = Statement(m_connection->CreateStatement(oss.str().c_str()));
long* p_pc_id = (long*) malloc(1 * sizeof(long));
p_pc_id[0] = m_pc_id;
long* p_result_id = (long*) malloc(1 * sizeof(long));
p_result_id[0] = (long)block_id;
long* p_num_points = (long*) malloc(1 * sizeof(long));
p_num_points[0] = (long)buffer.getNumPoints();
// std::cout << "point count on write: " << buffer.getNumPoints() << std::endl;
// :1
statement->Bind(&m_pc_id);
// :2
statement->Bind(p_result_id);
// :3
statement->Bind(p_num_points);
// :4
statement->Define(locator, 1);
// std::vector<liblas::uint8_t> data;
// bool gotdata = GetResultData(result, reader, data, 3);
// if (! gotdata) throw std::runtime_error("unable to fetch point data byte array");
// boost::uint8_t* point_data = buffer.getData(0);
boost::uint8_t* point_data;
boost::uint32_t point_data_length;
boost::uint32_t schema_byte_size;
bool pack = getOptions().getValueOrDefault<bool>("pack_ignored_fields", true);
if (pack)
PackPointData(buffer, &point_data, point_data_length, schema_byte_size);
else
{
point_data = buffer.getData(0);
point_data_length = buffer.getSchema().getByteSize() * buffer.getNumPoints();
}
// statement->Bind((char*)point_data,(long)(buffer.getSchema().getByteSize()*buffer.getNumPoints()));
statement->Bind((char*)point_data,(long)(point_data_length));
// :5
long* p_gtype = (long*) malloc(1 * sizeof(long));
p_gtype[0] = m_gtype;
statement->Bind(p_gtype);
// :6
long* p_srid = 0;
if (m_srid != 0)
{
p_srid = (long*) malloc(1 * sizeof(long));
p_srid[0] = m_srid;
}
statement->Bind(p_srid);
// :7
OCIArray* sdo_elem_info=0;
m_connection->CreateType(&sdo_elem_info, m_connection->GetElemInfoType());
SetElements(statement, sdo_elem_info);
statement->Bind(&sdo_elem_info, m_connection->GetElemInfoType());
// :8
OCIArray* sdo_ordinates=0;
m_connection->CreateType(&sdo_ordinates, m_connection->GetOrdinateType());
// x0, x1, y0, y1, z0, z1, bUse3d
pdal::Bounds<double> bounds = CalculateBounds(buffer);
SetOrdinates(statement, sdo_ordinates, bounds);
statement->Bind(&sdo_ordinates, m_connection->GetOrdinateType());
// :9
long* p_partition_d = 0;
if (bUsePartition)
{
p_partition_d = (long*) malloc(1 * sizeof(long));
p_partition_d[0] = m_block_table_partition_value;
statement->Bind(p_partition_d);
}
try
{
statement->Execute();
}
catch (std::runtime_error const& e)
{
std::ostringstream oss;
oss << "Failed to insert block # into '" << m_block_table_name << "' table. Does the table exist? " << std::endl << e.what() << std::endl;
throw std::runtime_error(oss.str());
}
oss.str("");
OWStatement::Free(locator, 1);
if (p_pc_id != 0) free(p_pc_id);
if (p_result_id != 0) free(p_result_id);
if (p_num_points != 0) free(p_num_points);
if (p_gtype != 0) free(p_gtype);
if (p_srid != 0) free(p_srid);
if (p_partition_d != 0) free(p_partition_d);
m_connection->DestroyType(&sdo_elem_info);
m_connection->DestroyType(&sdo_ordinates);
return true;
}
