1 // This may look like C code, but it's really -*- C++ -*- 2 /* 3 * Copyright (C) 2008 Emweb bv, Herent, Belgium. 4 * 5 * See the LICENSE file for terms of use. 6 */ 7 #ifndef WT_DBO_SQL_CONNECTION_H_ 8 #define WT_DBO_SQL_CONNECTION_H_ 9 10 #include <map> 11 #include <memory> 12 #include <string> 13 #include <vector> 14 #include <Wt/Dbo/WDboDllDefs.h> 15 16 namespace Wt { 17 namespace Dbo { 18 19 /*! \brief Enum that defines a date time type. 20 */ 21 enum class SqlDateTimeType { 22 Date, //!< Date only 23 DateTime,//!< Date and time 24 Time //!< Time duration 25 }; 26 27 /*! \brief Enum that defines a limit query type. 28 * 29 * Oracle is using Rownum, Firebird is using RowsFromTo, 30 * and Microsoft SQL Server is using Top instead of limit and 31 * offset in SQL 32 */ 33 enum class LimitQuery{ 34 Limit, //!< Use LIMIT and OFFSET 35 RowsFromTo, //!< Use ROWS ? TO ? (for Firebird) 36 Rownum, //!< Use rownum (for Oracle) 37 OffsetFetch, //!< Use OFFSET (?) ROWS FETCH FIRST (?) ROWS ONLY (adding ORDER BY (SELECT NULL) for SQL Server) 38 NotSupported // !< Not supported 39 }; 40 41 class SqlStatement; 42 43 /*! \class SqlConnection Wt/Dbo/SqlConnection.h Wt/Dbo/SqlConnection.h 44 * \brief Abstract base class for an SQL connection. 45 * 46 * An sql connection manages a single connection to a database. It 47 * also manages a map of previously prepared statements indexed by 48 * id's. 49 * 50 * This class is part of Wt::Dbo's backend API, and should not be used 51 * directly. 52 * 53 * All methods will throw an exception if they could not be completed. 54 * 55 * \ingroup dbo 56 */ 57 class WTDBO_API SqlConnection 58 { 59 public: 60 /*! \brief Destructor. 61 */ 62 virtual ~SqlConnection(); 63 64 /*! \brief Clones the connection. 65 * 66 * Returns a new connection object that is configured like this 67 * object. This is used by connection pool implementations to create 68 * its connections. 69 */ 70 virtual std::unique_ptr<SqlConnection> clone() const = 0; 71 72 /*! \brief Executes an SQL statement. 73 * 74 * This is a convenience method for preparing a statement, executing 75 * it, and deleting it. 76 */ 77 virtual void executeSql(const std::string& sql); 78 79 /*! \brief Executes a connection-stateful SQL statement. 80 * 81 * This executes a statement, but also remembers the statement for 82 * when the native connection would be closed and reopened during 83 * the lifetime of this connection object. Then the statements are 84 * redone on the newly opened connection. 85 * 86 * Such statements could be for example 'LISTEN' in a postgresql 87 * connection. 88 * 89 * \note These statements are only executed upon a reconnect for 90 * those backends that support automatic reconnect, but 91 * not when a connection is \link clone() cloned\endlink. 92 */ 93 virtual void executeSqlStateful(const std::string& sql); 94 95 /*! \brief Starts a transaction 96 * 97 * This function starts a transaction. 98 */ 99 virtual void startTransaction() = 0; 100 101 /*! \brief Commits a transaction 102 * 103 * This function commits a transaction. 104 */ 105 virtual void commitTransaction() = 0; 106 107 /*! \brief Rolls back a transaction 108 * 109 * This function rolls back a transaction. 110 */ 111 virtual void rollbackTransaction() = 0; 112 113 /*! \brief Returns the statement with the given id. 114 * 115 * Returns \c nullptr if no such statement was already added. 116 * 117 * \sa saveStatement() 118 */ 119 virtual SqlStatement *getStatement(const std::string& id); 120 121 /*! \brief Saves a statement with the given id. 122 * 123 * Saves the statement for future reuse using getStatement() 124 */ 125 virtual void saveStatement(const std::string& id, 126 std::unique_ptr<SqlStatement> statement); 127 128 /*! \brief Prepares a statement. 129 * 130 * Returns the prepared statement. 131 */ 132 virtual std::unique_ptr<SqlStatement> prepareStatement(const std::string& sql) = 0; 133 134 /*! \brief Sets a property. 135 * 136 * Properties may tailor the backend behavior. Some properties are 137 * applicable to all backends, while some are backend specific. 138 * 139 * General properties are: 140 * - <tt>show-queries</tt>: when value is "true", queries are shown 141 * as they are executed. 142 */ 143 void setProperty(const std::string& name, const std::string& value); 144 145 /*! \brief Returns a property. 146 * 147 * Returns the property value, or an empty string if the property was 148 * not set. 149 * 150 * \sa setProperty() 151 */ 152 std::string property(const std::string& name) const; 153 154 /** @name Methods that return dialect information 155 */ 156 //!@{ 157 /*! \brief Returns the 'autoincrement' SQL type modifier. 158 * 159 * This is used by Session::createTables() to create the <i>id</i> 160 * column. 161 */ 162 virtual std::string autoincrementSql() const = 0; 163 164 /*! \brief Returns the SQL statement(s) required to create an id sequence. 165 * 166 * This is used by Session::createTables() to create the id 167 * sequence for a table. 168 * The table's name and primary key are passed as arguments to this function 169 * and can be used to construct an SQL sequence that is unique for the table. 170 */ 171 virtual std::vector<std::string> 172 autoincrementCreateSequenceSql(const std::string &table, 173 const std::string &id) const = 0; 174 175 /*! \brief Returns the SQL statement(s) required to drop an id sequence. 176 * 177 * This is used by Session::dropTables() to drop the id sequence for a table. 178 * The table's name and primary key are passed as arguments to this function 179 * and can be used to construct an SQL sequence that is unique for the table. 180 */ 181 virtual std::vector<std::string> 182 autoincrementDropSequenceSql(const std::string &table, 183 const std::string &id) const = 0; 184 185 /*! \brief Returns the 'autoincrement' SQL type. 186 * 187 * This is used by Session::createTables() to create the <i>id</i> 188 * column. 189 */ 190 virtual std::string autoincrementType() const = 0; 191 192 /*! \brief Returns the infix for an 'autoincrement' insert statement. 193 * 194 * This is inserted before the <tt>values</tt> part of the <tt>insert</tt> 195 * statement, since Microsoft SQL Server requires that the autoincrement id 196 * is returned with <tt>OUTPUT</tt>. 197 * 198 * Returns an empty string by default. 199 */ 200 virtual std::string autoincrementInsertInfix(const std::string& id) const; 201 202 /*! \brief Returns the suffix for an 'autoincrement' insert statement. 203 * 204 * This is appended to the <tt>insert</tt> statement, since some back-ends 205 * need to be indicated that they should return the autoincrement id. 206 */ 207 virtual std::string autoincrementInsertSuffix(const std::string& id) const = 0; 208 209 /*! \brief Execute code before dropping the tables. 210 * 211 * This method is called before calling Session::dropTables(). 212 * The default implementation is empty. 213 */ 214 virtual void prepareForDropTables(); 215 216 /*! \brief Returns the date/time type. 217 * 218 * \sa SqlStatement::bind(int, const std::chrono::system_clock::time_point&, SqlDateTimeType) 219 */ 220 virtual const char *dateTimeType(SqlDateTimeType type) const = 0; 221 222 /*! \brief Returns the blob type. 223 * 224 * \sa SqlStatement::bind(int, const std::vector<unsigned char>&) 225 */ 226 virtual const char *blobType() const = 0; 227 228 /*! \brief Returns the text type. 229 * 230 * This is the text type for a string. If \p size = -1, then a type 231 * should be returned which does not require size information, otherwise 232 * a type should be returned that limits the size of the stored string 233 * to \p size. 234 * 235 * This method will return "text" by default if size = -1, and 236 * "varchar(size)" otherwise. 237 * 238 * \sa SqlStatement::bind(int column, const std::string& value) 239 */ 240 virtual std::string textType(int size) const; 241 242 /*! \brief Returns the 64-bit integer type. 243 * 244 * This method will return "bigint" by default. 245 * 246 */ 247 virtual std::string longLongType() const; 248 249 /*! \brief Returns the boolean type. 250 * 251 * This method will return "boolean" by default. 252 */ 253 virtual const char *booleanType() const; 254 255 /*! \brief Returns true if the database supports Update Cascade. 256 * 257 * This method will return true by default. 258 * Was created for the oracle database which does not support 259 * Update Cascade. 260 */ 261 virtual bool supportUpdateCascade() const; 262 263 /*! \brief Returns the true if the database require subquery alias. 264 * 265 * This method will return false by default. 266 */ 267 virtual bool requireSubqueryAlias() const; 268 269 virtual LimitQuery limitQueryMethod() const; 270 271 /*! \brief Returns whether the SQL dialect uses 'ROWS ? TO ?', limit or 272 * rownum for partial select results. 273 * 274 * This is an alternative SQL dialect option to the (non-standard) 'OFFSET ? 275 * LIMIT ?' syntax. 276 * 277 * The default implementation returns \c LimitQuery::Limit. 278 */ 279 virtual bool usesRowsFromTo() const; 280 281 /*! \brief Returns true if the backend support Alter Table 282 * 283 * This method will return false by default. 284 */ 285 virtual bool supportAlterTable() const; 286 287 /*! \brief Returns true if the backend supports "deferrable initially 288 * deferred" foreign key constraints 289 * 290 * This method will return false by default. 291 */ 292 virtual bool supportDeferrableFKConstraint() const; 293 294 /*! \brief Returns the command used in alter table .. drop constraint .. 295 * 296 * This method will return "constraint" by default. 297 * Default: ALTER TABLE .. DROP CONSTRAINT .. 298 */ 299 virtual const char *alterTableConstraintString() const; 300 //!@} 301 302 bool showQueries() const; 303 304 protected: 305 SqlConnection(); 306 SqlConnection(const SqlConnection& other); 307 SqlConnection& operator=(const SqlConnection&) = delete; 308 309 void clearStatementCache(); 310 311 std::vector<SqlStatement *> getStatements() const; getStatefulSql()312 const std::vector<std::string>& getStatefulSql() const { return statefulSql_; } 313 314 private: 315 typedef std::multimap<std::string, std::unique_ptr<SqlStatement>> StatementMap; 316 317 StatementMap statementCache_; 318 std::map<std::string, std::string> properties_; 319 std::vector<std::string> statefulSql_; 320 }; 321 322 } 323 } 324 325 #endif // WT_DBO_SQL_STATEMENT_H_ 326