27 #include "../common/Globals.h"
28 #include "../common/Translator.h"
29 #include "../dataaccess/dataset/PrimaryKey.h"
30 #include "../dataaccess/datasource/ScopedTransaction.h"
31 #include "../dataaccess/query/Select.h"
32 #include "../dataaccess/utils/Utils.h"
33 #include "../geometry/Envelope.h"
34 #include "../geometry/GeometryProperty.h"
35 #include "../memory/DataSet.h"
48 #include <boost/algorithm/string/case_conv.hpp>
49 #include <boost/format.hpp>
50 #include <boost/lexical_cast.hpp>
71 m_isInTransaction(false)
79 int ret = sqlite3_prepare_v2(m_db, query.c_str(), -1, &stmt, 0);
84 sqlite3_finalize(stmt);
86 throw te::common::Exception((boost::format(TR_COMMON(
"Could not excute the given query due to the following error: %1%.")) % sqlite3_errmsg(m_db)).str());
105 return m_pImpl->m_parent;
110 execute(
"BEGIN TRANSACTION");
111 m_pImpl->m_isInTransaction =
true;
116 m_pImpl->m_isInTransaction =
false;
117 execute(
"COMMIT TRANSACTION");
122 m_pImpl->m_isInTransaction =
false;
126 sqlite3_exec(m_pImpl->m_db,
"ROLLBACK TRANSACTION", 0, 0, &errmsg);
131 return m_pImpl->m_isInTransaction;
134 std::auto_ptr<te::da::DataSet>
140 std::string sql(
"SELECT * FROM ");
143 return query(sql, travType, connected, accessPolicy);
146 std::auto_ptr<te::da::DataSet>
148 const std::string& propertyName,
158 if(propertyName.empty())
162 std::string sql (
"SELECT * FROM ");
164 sql +=
" WHERE ROWID IN (SELECT rowid FROM SpatialIndex WHERE f_table_name = '";
165 sql += boost::to_lower_copy(name) +
"' AND f_geometry_column = '";
166 sql += boost::to_lower_copy(propertyName) +
"' AND ";
167 sql +=
"search_frame = BuildMbr(" + boost::lexical_cast<std::string>(e->
m_llx);
168 sql +=
", " + boost::lexical_cast<std::string>(e->
m_lly);
169 sql +=
", " + boost::lexical_cast<std::string>(e->
m_urx);
170 sql +=
", " + boost::lexical_cast<std::string>(e->
m_ury);
173 return query(sql, travType, connected, accessPolicy);
176 std::auto_ptr<te::da::DataSet>
178 const std::string& propertyName,
188 if(propertyName.empty())
194 std::string sql (
"SELECT * FROM ");
198 sql +=
" AND ROWID IN (SELECT rowid FROM SpatialIndex WHERE f_table_name = '";
199 sql += boost::to_lower_copy(name) +
"' AND f_geometry_column = '";
200 sql += boost::to_lower_copy(propertyName) +
"' AND ";
201 sql +=
"search_frame = BuildMbr(" + boost::lexical_cast<std::string>(e->
m_llx);
202 sql +=
", " + boost::lexical_cast<std::string>(e->
m_lly);
203 sql +=
", " + boost::lexical_cast<std::string>(e->
m_urx);
204 sql +=
", " + boost::lexical_cast<std::string>(e->
m_ury);
211 unsigned char* ewkb =
new unsigned char[ewkbSize];
215 int retval = sqlite3_bind_blob(stmt, 1, ewkb, ewkbSize, SQLITE_TRANSIENT);
219 if(retval != SQLITE_OK)
221 sqlite3_finalize(stmt);
226 std::auto_ptr<te::da::DataSet> litedataset(
new FwDataSet(stmt,
this));
231 std::auto_ptr<te::da::DataSet> fatdataset(
new te::mem::DataSet(*litedataset,
true));
236 std::auto_ptr<te::da::DataSet>
244 SQLVisitor visitor(*(m_pImpl->m_parent->getDialect()), sql);
247 return query(sql, travType, connected);
250 std::auto_ptr<te::da::DataSet>
258 std::auto_ptr<te::da::DataSet> litedataset(
new FwDataSet(result,
this));
263 std::auto_ptr<te::da::DataSet> fatdataset(
new te::mem::DataSet(*litedataset,
true));
272 SQLVisitor visitor(*(m_pImpl->m_parent->getDialect()), sql);
282 int rc = sqlite3_exec(m_pImpl->m_db, command.c_str(), 0, 0, &errmsg);
286 boost::format msg(TR_COMMON(
"Could not execute the SQL command due to the following error: %1%."));
290 sqlite3_free(errmsg);
296 std::auto_ptr<te::da::PreparedQuery>
299 return std::auto_ptr<te::da::PreparedQuery>(
new PreparedQuery(
this, m_pImpl->m_db));
304 return std::auto_ptr<te::da::BatchExecutor>(
new BatchExecutor(
this));
309 sqlite3_interrupt(m_pImpl->m_db);
332 std::vector<std::string>
342 return getDataSetNames().size();
345 std::auto_ptr<te::da::DataSetType>
350 return std::auto_ptr<te::da::DataSetType>(cloader.
getDataSetType(name));
353 boost::ptr_vector<te::dt::Property>
361 std::auto_ptr<te::dt::Property>
363 const std::string& name)
367 std::auto_ptr<te::da::DataSetType> dt(cloader.
getDataSetType(datasetName));
371 return std::auto_ptr<te::dt::Property>(p->clone());
378 std::auto_ptr<te::da::DataSetType> dt(cloader.
getDataSetType(datasetName));
382 return std::auto_ptr<te::dt::Property>(p->clone());
389 std::auto_ptr<te::da::DataSetType> dt(cloader.
getDataSetType(datasetName));
391 std::vector<std::string> pnames;
393 for(std::size_t i = 0; i != dt->size(); ++i)
394 pnames.push_back(dt->getProperty(i)->getName());
403 std::auto_ptr<te::da::DataSetType> dt(cloader.
getDataSetType(datasetName));
412 std::auto_ptr<te::da::DataSetType> dt(cloader.
getDataSetType(datasetName));
414 return dt->getProperty(name) != 0;
430 sql =
"SELECT AddGeometryColumn('";
432 sql += boost::to_lower_copy(datasetName);
434 sql += boost::to_lower_copy(p->
getName());
436 sql += boost::lexical_cast<std::string>(gp->
getSRID());
449 sql =
"ALTER TABLE ";
450 sql += boost::to_lower_copy(datasetName);
451 sql +=
" ADD COLUMN ";
452 sql += boost::to_lower_copy(p->
getName());
466 const std::string& propertyName,
467 const std::string& newPropertyName)
476 std::auto_ptr<te::da::DataSetType> dt(cloader.
getDataSetType(datasetName));
478 return std::auto_ptr<te::da::PrimaryKey>(
static_cast<te::da::PrimaryKey*
>(dt->getPrimaryKey()->clone()));
483 std::string sql =
"PRAGMA table_info(";
487 std::auto_ptr<te::da::DataSet> attributes(query(sql));
489 while(attributes->moveNext())
491 bool isPK = attributes->getInt32(5) != 0;
513 std::string sql(
"PRAGMA foreign_key_list(");
517 std::auto_ptr<te::da::DataSet> result(query(sql));
519 int id = boost::lexical_cast<
int>(name);
523 while(result->moveNext())
525 if(
id != result->getInt32(0))
528 std::string onUpdate = result->getString(5);
529 std::string onDeletion = result->getString(6);
531 fk->setOnUpdateAction(
GetAction(onUpdate));
532 fk->setOnDeleteAction(
GetAction(onDeletion));
549 std::string sql(
"PRAGMA foreign_key_list(");
553 std::auto_ptr<te::da::DataSet> result(query(sql));
557 std::vector<std::string> fkNames;
559 while(result->moveNext())
561 int fkid = result->getInt32(0);
565 std::string fkname = boost::lexical_cast<std::string>(result->getInt32(0));
567 fkNames.push_back(fkname);
578 std::string sql(
"PRAGMA foreign_key_list(");
582 std::auto_ptr<te::da::DataSet> result(query(sql));
584 while(result->moveNext())
586 if(name == boost::lexical_cast<std::string>(result->getInt32(0)))
666 std::string ukname = uk->
getName();
668 std::string sql =
"CREATE UNIQUE INDEX IF NOT EXISTS ";
676 for(std::size_t i = 0; i < size; ++i)
691 std::string ukname = name;
693 std::size_t pos = ukname.find(
".");
695 if(pos != std::string::npos)
696 ukname = ukname.substr(pos + 1);
698 std::string sql =
"DROP INDEX IF EXISTS ";
799 const std::map<std::string, std::string>& options)
802 std::string idxname = idx->
getName();
813 sql +=
"INDEX IF NOT EXISTS ";
821 for(std::size_t i = 0; i < size; ++i)
834 throw te::common::Exception(TR_COMMON(
"In order to create a spatial index you must inform just one spatial column in index definition!"));
836 sql =
"SELECT CreateSpatialIndex('";
901 const std::string& propertyName)
905 return std::auto_ptr<te::gm::Envelope>(cloader.
getExtent(datasetName, propertyName));
909 std::size_t propertyPos)
913 std::auto_ptr<te::da::DataSetType> dt(cloader.
getDataSetType(datasetName));
915 return std::auto_ptr<te::gm::Envelope>(cloader.
getExtent(datasetName, dt->getProperty(propertyPos)->getName()));
936 const std::map<std::string, std::string>& options)
938 const std::size_t nproperties = dt->
size();
943 std::string sql =
"CREATE TABLE ";
947 for(std::size_t i = 0; i < nproperties; ++i)
960 sql +=
", PRIMARY KEY(";
964 for(std::size_t i = 0; i < nkeys; ++i)
978 for(std::size_t i = 0; i < nfks; ++i)
982 fk->
setName(dt->
getName() +
"." + boost::lexical_cast<std::string>(i));
987 sql +=
"FOREIGN KEY(";
991 for(std::size_t j = 0; j < nkeys; ++j)
999 sql +=
") REFERENCES ";
1005 for(std::size_t j = 0; j < nkeys; ++j)
1021 for(std::size_t i = 0; i < nproperties; ++i)
1035 sql =
"SELECT RecoverGeometryColumn('";
1040 sql += boost::lexical_cast<std::string>(gp->
getSRID());
1070 const std::string& cloneName,
1071 const std::map<std::string, std::string>& options)
1088 const std::map<std::string, std::string>& options,
1092 limit = std::string::npos;
1094 std::string sql =
"INSERT INTO ";
1102 std::auto_ptr<PreparedQuery> pq(
new PreparedQuery(
this, m_pImpl->m_db));
1106 std::size_t nProcessedRows = 0;
1128 const std::vector<std::size_t>& properties,
1130 const std::map<std::string, std::string>& options,
virtual void setName(const std::string &name)
It sets the constraint name.
Property * getProperty(std::size_t i) const
It returns the i-th property.
te::da::DataSetType * getDataSetType(const std::string &datasetName)
std::vector< std::string > getSequenceNames()
It gets the sequence names available in the data source.
void dropProperty(const std::string &datasetName, const std::string &name)
It removes a property from the given dataset.
std::auto_ptr< te::da::Sequence > getSequence(const std::string &name)
It gets the sequence with the given name in the data source.
std::auto_ptr< te::da::UniqueKey > getUniqueKey(const std::string &datasetName, const std::string &name)
It gets the unique key in the dataset with the given name.
std::auto_ptr< te::da::Index > getIndex(const std::string &datasetName, const std::string &name)
It gets the index with the given name from the dataset.
A visitor for building an SQL statement using SQLite dialect.
void Convert2SpatiaLiteGeom(const te::gm::GeomType t, std::string &geomType, std::string &dimension)
Utility functions for the data access module.
void addCheckConstraint(const std::string &datasetName, te::da::CheckConstraint *cc)
It adds a check constraint to the dataset.
void dropUniqueKey(const std::string &datasetName, const std::string &name)
It removes the unique key constraint from the dataset.
Impl(DataSource *parent, sqlite3 *db)
void cloneDataSet(const std::string &name, const std::string &cloneName, const std::map< std::string, std::string > &options)
It clones the dataset in the data source.
~DataSourceTransactor()
Virtual destructor.
std::auto_ptr< te::da::CheckConstraint > getCheckConstraint(const std::string &datasetName, const std::string &name)
It gets the check constraint of the dataset with the given name.
DataSourceTransactor()
Default constructor that can be called by subclasses.
A class that models the description of a dataset.
DataSourceCatalogLoader manages metadata information for the TerraLib SQLite Data Access Driver...
std::string escape(const std::string &value)
It escapes a string for using in commands and queries.
void remove(const std::string &datasetName, const te::da::ObjectIdSet *oids=0)
It removes all the informed items from the dataset.
bool isDataSetNameValid(const std::string &datasetName)
It returns true if the given string is a valid dataset name.
bool isInTransaction() const
It returns true if a transaction is in progress, otherwise, it returns false.
std::auto_ptr< te::da::DataSet > getDataSet(const std::string &name, te::common::TraverseType travType=te::common::FORWARDONLY, bool connected=false, const te::common::AccessPolicy accessPolicy=te::common::RAccess)
It gets the dataset identified by the given name. A dataset can be connected or disconnected. A connected dataset, after its creation through the data source transactor, continues to depend on the connection given by its associated data source. Differently, a disconnected dataset, after its creation, no more depends of the connection given by the data source, and it continues to live after the connection has been released to the data source.
double m_urx
Upper right corner x-coordinate.
static const MachineByteOrder sm_machineByteOrder
A flag that indicates the machine byte order (Big Endian or Little Endian).
PrimaryKey * getPrimaryKey() const
It returns the primary key associated to the dataset type.
An utility class to coordinate transactions.
std::vector< std::string > getUniqueKeyNames(const std::string &datasetName)
It gets the unique key names of the given dataset.
SpatialRelation
Spatial relations between geometric objects.
void addIndex(const std::string &datasetName, te::da::Index *idx, const std::map< std::string, std::string > &options)
It adds an index to the dataset.
std::auto_ptr< te::da::DataSet > query(const te::da::Select &q, te::common::TraverseType travType=te::common::FORWARDONLY, bool connected=false, const te::common::AccessPolicy accessPolicy=te::common::RAccess)
It executes a query that may return some data using a generic query. A dataset can be connected or di...
It describes a sequence (a number generator).
bool foreignKeyExists(const std::string &datasetName, const std::string &name)
It checks if a foreign key with the given name exists in the data source.
TEDATAACCESSEXPORT std::string GetSQLValueNames(const DataSetType *dt)
boost::ptr_vector< te::dt::Property > getProperties(const std::string &datasetName)
std::string Convert2SQLCreate(const te::dt::Property *p)
void dropSequence(const std::string &name)
It removes the sequence from the data source.
A class that describes a check constraint.
An abstract class for data providers like a DBMS, Web Services or a regular file. ...
boost::ptr_vector< te::dt::Property > getProperties(const std::string &datasetName)
It retrieves the properties of the dataset.
std::auto_ptr< te::da::BatchExecutor > getBatchExecutor()
It creates a batch command executor.
void dropPrimaryKey(const std::string &datasetName)
It removes the primary key constraint from the dataset schema.
void dropDataSet(const std::string &name)
It removes the dataset schema from the data source.
It models a property definition.
A class that implements a prepared query for the TerraLib SQLite Data Access Driver.
void optimize(const std::map< std::string, std::string > &opInfo)
For some data access drivers, this method will perform some operations to optimize the data storage...
const std::vector< te::dt::Property * > & getProperties() const
It returns the properties that take part of the primary key.
void commit()
It commits the transaction.
void addProperty(const std::string &datasetName, te::dt::Property *p)
It adds a new property to the dataset schema.
bool propertyExists(const std::string &datasetName, const std::string &name)
It checks if a property with the given name exists in the dataset.
std::auto_ptr< te::dt::Property > getProperty(const std::string &datasetName, const std::string &name)
It retrieves the property with the given name from the dataset.
void begin()
It starts a new transaction.
bool indexExists(const std::string &datasetName, const std::string &name)
It checks if an index with the given name exists in the dataset.
virtual bool moveNext()=0
It moves the internal pointer to the next item of the collection.
AccessPolicy
Supported data access policies (can be used as bitfield).
bool primaryKeyExists(const std::string &datasetName, const std::string &name)
It checks if a primary key exists in the dataset.
TraverseType
A dataset can be traversed in two ways:
Implementation of a random-access dataset class for the TerraLib In-Memory Data Access driver...
te::da::DataSource * getDataSource() const
It returns the parent data source of the transactor.
double m_llx
Lower left corner x-coordinate.
void dropForeignKey(const std::string &datasetName, const std::string &fkName)
It removes the foreign key constraint from the dataset schema.
std::size_t getNumberOfForeignKeys() const
It returns the number of foreign keys defined for the dataset type.
void dropIndex(const std::string &datasetName, const std::string &idxName)
It removes the index from the dataset schema.
void createDataSet(te::da::DataSetType *dt, const std::map< std::string, std::string > &options)
It creates the dataset schema definition in the target data source.
int getSRID() const
It returns the spatial reference system identifier associated to this property.
void addPrimaryKey(const std::string &datasetName, te::da::PrimaryKey *pk)
It adds a primary key constraint to the dataset schema.
void renameDataSet(const std::string &name, const std::string &newName)
It renames a dataset.
An Envelope defines a 2D rectangular region.
std::size_t getNumberOfDataSets()
It retrieves the number of data sets available in the data source.
This class represents a set of unique ids created in the same context. i.e. from the same data set...
bool checkConstraintExists(const std::string &datasetName, const std::string &name)
It checks if a check-constraint with the given name exists in the data source.
A class that helps to determine the size of a SpatiaLite geometry.
GeomType getGeometryType() const
It returns the geometry subtype allowed for the property.
sqlite3_stmt * queryLite(const std::string &query)
void addSequence(te::da::Sequence *sequence)
It creates a new sequence in the data source.
std::vector< std::string > getCheckConstraintNames(const std::string &datasetName)
It gets the check constraint names of the given dataset.
bool isPropertyNameValid(const std::string &propertyName)
It checks if the given property name is valid.
DataSetType * getReferencedDataSetType() const
It returns the referenced DataSetType of this foreign key constraint.
std::auto_ptr< te::da::PreparedQuery > getPrepared(const std::string &qName=std::string(""))
It creates a prepared query object that may be used for query commands (select, insert, update and delete) that are used repeatedly.
std::string GetSQLType(const te::dt::Property *p)
It models a foreign key constraint for a DataSetType.
bool hasDataSets()
It checks if the data source has any dataset.
bool isRequired() const
It returns true if the attribute is required, otherwise it returns false.
bool sequenceExists(const std::string &name)
It checks if a sequence with the given name exists in the data source.
It describes a unique key (uk) constraint.
te::gm::Envelope * getExtent(const std::string &tableName, const std::string &geomColName)
std::size_t size() const
It returns the number of properties of the CompositeProperty.
std::size_t getNumberOfProperties(const std::string &datasetName)
It gets the number of properties of the given dataset.
std::vector< std::string > getPropertyNames(const std::string &datasetName)
It gets the property names of the given dataset.
void execute(const te::da::Query &command)
It executes the specified command using a generic query representation.
A class that implements a prepared query for the TerraLib SQLite Data Access Driver.
std::size_t getNumberOfItems(const std::string &datasetName)
It retrieves the number of items of the given dataset.
This class is designed to declare objects to be thrown as exceptions by TerraLib. ...
Geometry is the root class of the geometries hierarchy, it follows OGC and ISO standards.
A Select models a query to be used when retrieving data from a DataSource.
boost::int64_t getLastGeneratedId()
It returns the last id generated by an insertion command.
std::auto_ptr< te::da::PrimaryKey > getPrimaryKey(const std::string &datasetName)
It retrieves the primary key of the dataset.
double m_lly
Lower left corner y-coordinate.
std::string GetBindableSpatialRelation(const std::string &colName, const te::gm::SpatialRelation r)
A class that serializes a geometry to the SpatiaLite EWKB format.
A visitor for building an SQL statement using SQLite dialect.
int getType() const
It returns the property data type.
A dataset is the unit of information manipulated by the data access module of TerraLib.
void addForeignKey(const std::string &datasetName, te::da::ForeignKey *fk)
It adds a foreign key constraint to a dataset.
const std::vector< te::dt::Property * > & getProperties() const
It returns the properties that form the unique key.
ForeignKey * getForeignKey(std::size_t i) const
It returns the i-th foreign key associated to the dataset type.
double m_ury
Upper right corner y-coordinate.
virtual ReturnType accept(VisitorType &guest) const =0
It call the visit method from the guest object.
const std::vector< te::dt::Property * > & getProperties() const
It returns the properties that take part of the index.
It describes a primary key (pk) constraint.
std::vector< std::string > getDataSetNames()
It It gets the dataset names available in the data source.
void dropCheckConstraint(const std::string &datasetName, const std::string &name)
It removes the check constraint from the dataset.
std::vector< std::string > getDataSetNames()
std::vector< std::string > getForeignKeyNames(const std::string &datasetName)
It gets the foreign key names of the given dataset.
std::auto_ptr< te::gm::Envelope > getExtent(const std::string &datasetName, const std::string &propertyName)
It retrieves the bounding rectangle of the spatial property for the given dataset.
std::auto_ptr< te::da::ForeignKey > getForeignKey(const std::string &datasetName, const std::string &name)
It retrieves the foreign key from the given dataset.
void renameProperty(const std::string &datasetName, const std::string &propertyName, const std::string &newPropertyName)
It renames a property of the given dataset.
Implements the DataSource class for the SQLite Data Access Driver.
struct sqlite3_stmt sqlite3_stmt
std::vector< std::string > getIndexNames(const std::string &datasetName)
It gets the index names of the given dataset.
void addUniqueKey(const std::string &datasetName, te::da::UniqueKey *uk)
It adds a unique key constraint to the dataset.
An implementation of DataSourceTransactor class for the TerraLib SQLite Data Access Driver...
void commit()
It commits the transaction.
std::auto_ptr< te::da::DataSetType > getDataSetType(const std::string &name)
It gets information about the given dataset.
std::string GetSQLBindValues(const te::da::DataSet *dataset)
void update(const std::string &datasetName, te::da::DataSet *dataset, const std::vector< std::size_t > &properties, const te::da::ObjectIdSet *oids, const std::map< std::string, std::string > &options, std::size_t limit=0)
It updates the contents of a dataset for the set of data items.
void rollBack()
It aborts the transaction. Any changes will be rolled-back.
virtual const std::string & getName() const
It returns the constraint name.
void add(const std::string &datasetName, te::da::DataSet *d, const std::map< std::string, std::string > &options, std::size_t limit=0)
It adds data items to the dataset in the data source.
te::da::FKActionType GetAction(const std::string &action)
IndexType getIndexType() const
It gets the index type.
const std::vector< te::dt::Property * > & getProperties() const
It returns the properties that take part of the foreign key constraint.
static std::size_t getEWKBSize(const te::gm::Geometry *g)
const std::vector< te::dt::Property * > & getReferencedProperties() const
It returns the referenced properties (on the referenced DataSetType) of this foreign key constraint...
bool dataSetExists(const std::string &name)
It checks if a dataset with the given name exists in the data source.
const Envelope * getMBR() const
It returns the minimum bounding rectangle for the geometry in an internal representation.
void cancel()
It requests that the data source stop the processing of the current command.
bool uniqueKeyExists(const std::string &datasetName, const std::string &name)
It checks if a unique key with the given name exists in the dataset.
Implementation of the BatchExecutor class for the TerraLib SQLite Data Access driver.
A Query is independent from the data source language/dialect.
It describes an index associated to a DataSetType.
Implementation of a forward-only dataset for the TerraLib SQLite Data Access driver.
const std::string & getName() const
It returns the property name.
const std::string & getName() const
It returns the index name.