/**
* SpatialiteNativeDriver constructor.
*
* @param $filename
*/
public function __construct($filename)
{
$isNewDatabase = !file_exists($filename);
$this->db = new \SQLite3($filename);
$this->db->loadExtension('mod_spatialite.so');
if ($isNewDatabase) {
$this->initDbFile();
}
}
public function __construct($filename, $flags = null, $encryptionKey = null, $busyTimeout = null, array $userDefinedFunctions = array(), array $userDefinedExtensions = array())
{
if (null === $flags) {
$flags = \SQLITE3_OPEN_READWRITE | \SQLITE3_OPEN_CREATE;
}
$this->_conn = new SQLite3($filename, $flags, $encryptionKey);
if (null !== $busyTimeout) {
$this->_conn->busyTimeout($busyTimeout);
} else {
$this->_conn->busyTimeout(60000);
}
foreach ($userDefinedFunctions as $fn => $data) {
$this->_conn->createFunction($fn, $data['callback'], $data['numArgs']);
}
foreach ($userDefinedExtensions as $extension) {
$this->_conn->loadExtension($extension);
}
}
function getWidthRiver($coordinates)
{
$units = json_decode($coordinates);
$unit_1 = $units[0][0] . ',' . $units[0][1];
$unit_2 = $units[1][0] . ',' . $units[1][1];
if (file_exists('poland.sqlite')) {
$db = new SQLite3("poland.sqlite");
}
if (file_exists('mod_spatialite.so.7.1.0')) {
$db->loadExtension('mod_spatialite.so.7.1.0');
}
$db->exec("SELECT InitSpatialMetadata()");
# Запрос на поиск пересечения линии между юнитами и рек.
# Переменная out выходной словарь с данными
$rs = $db->query("SELECT id, name, sub_type, AsGeoJSON(Geometry) AS json FROM ln_waterway WHERE Crosses(MakeLine(MakePoint({$unit_1}), MakePoint({$unit_2})), Geometry)");
# Если тип найденной реки "riverbank", для нее находим ширину реки. Иначе width = -1;
# В выдачу добавлены точки пересечения прямой между юнитами с рекой, т.е. по сути точки берега реки, через который,
# проходит бой юнитов.
# В переменной $row[0] находятся точки персечения
while ($river = $rs->fetchArray()) {
if ($river['sub_type'] == 'riverbank') {
$points = $db->query("SELECT AsGeoJSON(Intersection(MakeLine(MakePoint(" . $unit_1 . "), MakePoint(" . $unit_2 . ")), GeomFromGeoJSON('" . $river['json'] . "')))");
$row = $points->fetchArray();
$jdecode = json_decode($row[0]);
$i = 0;
foreach ($jdecode->coordinates as $value) {
$lng[$i] = $value[0];
$lat[$i] = $value[1];
$i++;
}
$width = widthRiver($lat[0], $lng[0], $lat[1], $lng[1]);
$out[$river['name']] = ['id' => $river['id'], 'width' => $width, 'x_cross1' => $lng[0], 'y_cross1' => $lat[0], 'x_cross2' => $lng[1], 'y_cross2' => $lat[1]];
} else {
if (!$out[$river['name']]) {
$out[$river['name']] = ['id' => $river['id'], 'width' => -1];
}
}
}
$jencode = json_encode($out);
return $jencode;
}
protected function _connect()
{
$db = $this->profile['database'];
if (preg_match('/^(app|lib|var|temp|www)\\:/', $db)) {
$path = jFile::parseJelixPath($db);
} else {
if ($db[0] == '/' || preg_match('!^[a-z]\\:(\\\\|/)[a-z]!i', $db)) {
if (file_exists($db) || file_exists(dirname($db))) {
$path = $db;
} else {
throw new Exception('sqlite3 connector: unknown database path scheme');
}
} else {
$path = jApp::varPath('db/sqlite3/' . $db);
}
}
$sqlite = new SQLite3($path);
// Load extensions if needed
if (isset($this->profile['extensions'])) {
$list = preg_split('/ *, */', $this->profile['extensions']);
foreach ($list as $ext) {
try {
$sqlite->loadExtension($ext);
} catch (Exception $e) {
throw new Exception('sqlite3 connector: error while loading sqlite extension ' . $ext);
}
}
}
// set timeout
if (isset($this->profile['busytimeout'])) {
$timeout = intval($this->profile['busytimeout']);
if ($timeout) {
$sqlite->busyTimeout($timeout);
}
}
return $sqlite;
}
public function getUpdatedConfig()
{
$qgsLoad = $this->xml;
$legend = $qgsLoad->xpath('//legend');
$legendZero = $legend[0];
$updateDrawingOrder = (string) $legendZero->attributes()->updateDrawingOrder;
$layersOrder = array();
if ($updateDrawingOrder == 'false') {
// For QGIS >=2.4, new item layer-tree-canvas
if ($this->qgisProjectVersion >= 204000) {
$customeOrder = $qgsLoad->xpath('//layer-tree-canvas/custom-order');
$customeOrderZero = $customeOrder[0];
if ($customeOrderZero->attributes()->enabled == 1) {
$items = $customeOrderZero->xpath('//item');
$lo = 0;
foreach ($items as $layerI) {
# Get layer name from config instead of XML for possible embedded layers
$name = $this->getLayerNameByIdFromConfig($layerI);
if ($name) {
$layersOrder[$name] = $lo;
}
$lo += 1;
}
}
} else {
$layers = $qgsLoad->xpath('//legendlayer');
foreach ($layers as $layer) {
if ($layer->attributes()->drawingOrder and $layer->attributes()->drawingOrder >= 0) {
$layersOrder[(string) $layer->attributes()->name] = (int) $layer->attributes()->drawingOrder;
}
}
}
}
$configRead = json_encode($this->cfg);
$configJson = json_decode($configRead);
// Add an option to display buttons to remove the cache for cached layer
// Only if appropriate right is found
if (jAcl2::check('lizmap.admin.repositories.delete')) {
$configJson->options->removeCache = 'True';
}
// Remove layerOrder option from config if not required
if (!empty($layersOrder)) {
$configJson->layersOrder = $layersOrder;
}
// Update print Capabilities
if (property_exists($configJson->options, 'print') && $configJson->options->print == 'True') {
$printTemplates = array();
// get restricted composers
$rComposers = array();
$restrictedComposers = $this->xml->xpath("//properties/WMSRestrictedComposers/value");
foreach ($restrictedComposers as $restrictedComposer) {
$rComposers[] = (string) $restrictedComposer;
}
// get composer
$composers = $qgsLoad->xpath('//Composer');
foreach ($composers as $composer) {
// test restriction
if (in_array((string) $composer['title'], $rComposers)) {
continue;
}
// get composition element
$composition = $composer->xpath('Composition');
if (count($composition) == 0) {
continue;
}
$composition = $composition[0];
// init print template element
$printTemplate = array('title' => (string) $composer['title'], 'width' => (int) $composition['paperWidth'], 'height' => (int) $composition['paperHeight'], 'maps' => array(), 'labels' => array());
// get composer maps
$cMaps = $composer->xpath('.//ComposerMap');
foreach ($cMaps as $cMap) {
$cMapItem = $cMap->xpath('ComposerItem');
if (count($cMapItem) == 0) {
continue;
}
$cMapItem = $cMapItem[0];
$ptMap = array('id' => 'map' . (string) $cMap['id'], 'width' => (int) $cMapItem['width'], 'height' => (int) $cMapItem['height']);
// Before 2.6
if (property_exists($cMap->attributes(), 'overviewFrameMap') and (string) $cMap['overviewFrameMap'] != '-1') {
$ptMap['overviewMap'] = 'map' . (string) $cMap['overviewFrameMap'];
}
// >= 2.6
$cMapOverviews = $cMap->xpath('ComposerMapOverview');
foreach ($cMapOverviews as $cMapOverview) {
if ($cMapOverview and (string) $cMapOverview->attributes()->frameMap != '-1') {
$ptMap['overviewMap'] = 'map' . (string) $cMapOverview->attributes()->frameMap;
}
}
$printTemplate['maps'][] = $ptMap;
}
// get composer labels
$cLabels = $composer->xpath('.//ComposerLabel');
foreach ($cLabels as $cLabel) {
$cLabelItem = $cLabel->xpath('ComposerItem');
if (count($cLabelItem) == 0) {
continue;
}
$cLabelItem = $cLabelItem[0];
if ((string) $cLabelItem['id'] == '') {
continue;
}
$printTemplate['labels'][] = array('id' => (string) $cLabelItem['id'], 'htmlState' => (int) $cLabel['htmlState'], 'text' => (string) $cLabel['labelText']);
}
$printTemplates[] = $printTemplate;
}
// set printTemplates in config
$configJson->printTemplates = $printTemplates;
}
// Update locate by layer with vecctorjoins
if (property_exists($configJson, 'locateByLayer')) {
// collect layerIds
$locateLayerIds = array();
foreach ($configJson->locateByLayer as $k => $v) {
$locateLayerIds[] = $v->layerId;
}
// update locateByLayer with alias and filter information
foreach ($configJson->locateByLayer as $k => $v) {
$xmlLayer = $this->getXmlLayer($v->layerId);
$xmlLayerZero = $xmlLayer[0];
// aliases
$alias = $xmlLayerZero->xpath("aliases/alias[@field='" . $v->fieldName . "']");
if (count($alias) != 0) {
$alias = $alias[0];
$v->fieldAlias = (string) $alias['name'];
$configJson->{$k} = $v;
}
if (property_exists($v, 'filterFieldName')) {
$alias = $xmlLayerZero->xpath("aliases/alias[@field='" . $v->filterFieldName . "']");
if (count($alias) != 0) {
$alias = $alias[0];
$v->filterFieldAlias = (string) $alias['name'];
$configJson->{$k} = $v;
}
}
// vectorjoins
$vectorjoins = $xmlLayerZero->xpath('vectorjoins/join');
if (count($vectorjoins) != 0) {
if (!property_exists($v, 'vectorjoins')) {
$v->vectorjoins = array();
}
foreach ($vectorjoins as $vectorjoin) {
$joinLayerId = (string) $vectorjoin['joinLayerId'];
if (in_array($joinLayerId, $locateLayerIds)) {
$v->vectorjoins[] = (object) array("joinFieldName" => (string) $vectorjoin['joinFieldName'], "targetFieldName" => (string) $vectorjoin['targetFieldName'], "joinLayerId" => (string) $vectorjoin['joinLayerId']);
}
}
$configJson->{$k} = $v;
}
}
}
// Remove FTP remote directory
if (property_exists($configJson->options, 'remoteDir')) {
unset($configJson->options->remoteDir);
}
// Remove editionLayers from config if no right to access this tool
// Or if no ability to load spatialite extension
if (property_exists($configJson, 'editionLayers')) {
if (jAcl2::check('lizmap.tools.edition.use', $this->repository->getKey())) {
$spatial = false;
if (class_exists('SQLite3')) {
try {
$db = new SQLite3(':memory:');
$spatial = $db->loadExtension('libspatialite.so');
# loading SpatiaLite as an extension
} catch (Exception $e) {
$spatial = False;
}
}
if (!$spatial) {
foreach ($configJson->editionLayers as $key => $obj) {
$layerXml = $this->getXmlLayer($obj->layerId);
$layerXmlZero = $layerXml[0];
$provider = $layerXmlZero->xpath('provider');
$provider = (string) $provider[0];
if ($provider == 'spatialite') {
unset($configJson->editionLayers->{$key});
}
}
}
} else {
unset($configJson->editionLayers);
}
}
// Add export layer right
if (jAcl2::check('lizmap.tools.layer.export', $this->repository->getKey())) {
$configJson->options->exportLayers = 'True';
}
// Update config with layer relations
$relations = $this->getRelations();
if ($relations) {
$configJson->relations = $relations;
}
$configRead = json_encode($configJson);
return $configRead;
}
$insert_final->bindValue('region', $p->region);
$insert_final->bindValue('realm', $p->realm);
$insert_final->bindValue('geom', $endpoly);
$insert_final->execute();
}
}
break;
case 'exportsql':
if (empty($args->command->options['spatialite'])) {
throw new Exception("Must specify spatialite DB");
}
if (empty($args->command->options['table'])) {
throw new Exception("Must specify Table Name");
}
$sdb = new SQLite3($args->command->options['spatialite']);
$sdb->loadExtension($config->spatialite);
$allzones = $sdb->prepare(sprintf("SELECT zone_id FROM %s" . (empty($args->command->options['filter']) ? "" : " WHERE %s"), $args->command->options['table'], $args->command->options['filter']));
$getFullPolygon = $sdb->prepare(sprintf("SELECT AsText(polygon) FROM %s WHERE zone_id = :zone", $args->command->options['table']));
$res = $allzones->execute();
$zones = array();
$fp = fopen($args->command->options['outputfile'], "w");
fputs($fp, "DROP TABLE IF EXISTS zonetable;\n");
fputs($fp, "CREATE TABLE zonetable (zone_id INT UNSIGNED NOT NULL PRIMARY KEY, poly GEOMETRY NOT NULL);\n");
while ($row = $res->fetchArray(SQLITE3_NUM)) {
$getFullPolygon->bindValue('zone', $row[0], SQLITE3_INTEGER);
$res2 = $getFullPolygon->execute();
$poly = $res2->fetchArray(SQLITE3_NUM);
$res2->finalize();
fprintf($fp, "INSERT INTO zonetable (zone_id, poly) VALUES (%d,GeomFromText(%s));\n", $row[0], $dbh->quote($poly[0]));
}
fclose($fp);
function openSQLiteDb($dbname_faims)
{
$dbfaims = new SQLite3($dbname_faims);
//':memory:');
if (!$dbfaims) {
print "<div style='color:red; font-weight:bold;padding:10px'>Cannot open the database</div>";
return false;
}
# reporting some version info
$rs = $dbfaims->query('SELECT sqlite_version()');
while ($row = $rs->fetchArray()) {
print "<h3>Server running: SQLite version: {$row['0']}</h3> ";
}
$use_Spatialite = true;
if ($use_Spatialite) {
//TEMP
# loading SpatiaLite as an extension
$dbfaims->loadExtension('libspatialite.so');
# enabling Spatial Metadata
# using v.2.4.0 this automatically initializes SPATIAL_REF_SYS
# and GEOMETRY_COLUMNS
$dbfaims->exec("SELECT InitSpatialMetadata()");
$rs = $dbfaims->query('SELECT spatialite_version()');
if ($rs) {
while ($row = $rs->fetchArray()) {
print "<h3>Server running: SpatiaLite version: {$row['0']}</h3>";
}
} else {
print "<div style='color:red; font-weight:bold;padding:10px'>\n Sorry, we are unable to sync the Heurist database with the FAIMS SQLite database\n <br/>as we cannot detect SpatiaLite (spatial functions for SQLite).\n <br/>Please ask your sysadmin to SpatiaLite on the server.</div>";
return false;
}
}
//use spatialite
return $dbfaims;
}
<?php
$db = new SQLite3(':memory:');
try {
$db->loadExtension("");
} catch (Extension $ex) {
var_dump($ex->getMessage());
}
<?php
$db = new SQLite3(':memory:');
var_dump($db->loadExtension(array()));
echo "Done\n";
protected function readEditionLayers($xml, $cfg)
{
$editionLayers = array();
if (property_exists($cfg, 'editionLayers')) {
// Add data into editionLayers from configuration
$editionLayers = $cfg->editionLayers;
// Check ability to load spatialite extension
// And remove ONLY spatialite layers if no extension found
$spatial = false;
if (class_exists('SQLite3')) {
// Try with libspatialite
try {
$db = new SQLite3(':memory:');
$spatial = @$db->loadExtension('libspatialite.so');
# loading SpatiaLite as an extension
} catch (Exception $e) {
$spatial = False;
}
// Try with mod_spatialite
if (!$spatial) {
try {
$db = new SQLite3(':memory:');
$spatial = @$db->loadExtension('mod_spatialite.so');
# loading SpatiaLite as an extension
} catch (Exception $e) {
//jLog::log($e->getMessage(), 'error');
$spatial = False;
}
}
}
if (!$spatial) {
jLog::log('Spatialite is not available', 'error');
foreach ($editionLayers as $key => $obj) {
$layerXml = $this->getXmlLayer2($xml, $obj->layerId);
$layerXmlZero = $layerXml[0];
$provider = $layerXmlZero->xpath('provider');
$provider = (string) $provider[0];
if ($provider == 'spatialite') {
unset($editionLayers->{$key});
}
}
}
}
return $editionLayers;
}
