Software and Services, Portable Libraries  2019.Mar.01
A library for managing digital certificates
SQL.hpp
1 //
2 // (C) Copyright 2001-2017 Software and Services LLC, All Rights Reserved.
3 //
4 // NOTICE:
5 //
6 // THIS SOURCE CODE BELONGS TO SOFTWARE AND SERVICES LIMITED LIABILITY
7 // CORPORATION, AN IDAHO CORPORATION. THIS CODE IS CONSIDERED A
8 // TRADE SECRET AND IS NOT TO BE DIVULGED OR USED BY PARTIES WHO HAVE
9 // NOT RECEIVED WRITTEN AUTHORIZATION FROM THE OWNER.
10 //
11 // THIS SOURCE CODE IS UNPUBLISHED WORK FULLY PROTECTED BY THE UNITED
12 // STATES COPYRIGHT LAWS AND IS CONSIDERED A TRADE SECRET BELONGING
13 // TO THE COPYRIGHT HOLDER.
14 //
15 // THIS SOFTWARE AND MANUAL ARE BOTH PROTECTED BY UNITED STATES
16 // COPYRIGHT LAW (TITLE 17 UNITED STATES CODE). UNAUTHORIZED REPRODUCTION
17 // AND/OR SALE MAY RESULT IN IMPRISONMENT OF UP TO ONE YEAR AND FINES
18 // OF UP TO $100,000 (17 USC 506). COPYRIGHT INFRINGERS MAY ALSO
19 // BE SUBJECT TO CIVIL LIABILITY.
20 //
21 // ALL OF THIS CODE IS ORIGINAL WORK BY THE COPYRIGHT HOLDER.
22 //
23 
24 #ifndef __SOFTWARE_AND_SERVICES_LIBPCSQL_SQL_HH__
25 #define __SOFTWARE_AND_SERVICES_LIBPCSQL_SQL_HH__
26 
27 #include <SaS/Common/osver.h>
28 #include <SaS/Secure/pem.hpp>
29 #include <vector>
30 #include <map>
31 #include <string>
32 //#include <libxml/encoding.h>
33 //#include <libxml/xmlwriter.h>
34 #include <sys/types.h>
35 #include <stdint.h>
36 
37 #if defined(USE_MYSQL)
38 #define _SAS_ONE_DB_SELECTED 1
39 #endif
40 
41 #if defined(USE_POSTGRES)
42 #ifndef _SAS_ONE_DB_SELECTED
43 #define _SAS_ONE_DB_SELECTED 1
44 #endif
45 #endif
46 
47 #if defined(USE_MSQL)
48 #error MSQL NOT READY YET (SQL.hpp)
49 #endif
50 
51 #if defined(USE_ODBC)
52 #ifndef _SAS_ONE_DB_SELECTED
53 #define _SAS_ONE_DB_SELECTED 1
54 #endif
55 #endif
56 
57 #if defined(USE_ORACLE)
58 #error ORACLE NOT READY YET (SQL.hpp)
59 #endif
60 
61 #include <SaS/Common/osver.h>
62 #ifndef WIN32
63 #include <sys/time.h>
64 #endif
65 
66 #ifdef BUILDING_LIBRARY_SQL
67 #ifndef _SAS_ONE_DB_SELECTED
68 #error No Database Defined (SQL.hpp)
69 #endif
70 #undef _SAS_ONE_DB_SELECTED
71 #endif
72 
73 #include <SaS/Common/Error.hpp>
74 #include <SaS/Common/msgCat2.hpp>
75 #include <stdio.h>
76 
77 #ifndef EXPORT_SASSQL
78 #ifdef BUILDING_LIBRARY_SQL
79 #define EXPORT_SASSQL EXPORT_OUT
80 #else
81 #define EXPORT_SASSQL EXPORT_IN
82 #endif
83 #endif
84 
86 
87 namespace SoftwareAndServices
88 {
89  namespace Library
90  {
91  namespace Database
92  {
93  class SqlAggregate;
94  class SqlCast;
95  class SqlCollation;
96  class SqlColumn;
97  class SqlConversion;
98  class SqlDatabase;
99  class SqlDataType;
100  class SqlDomain;
101  class SqlEventTrigger;
102  class SqlExtension;
103  class SqlForeignDataWrapper;
104  class SqlForeignTable;
105  class SqlForeignServer;
106  class SqlFunction;
107  class SqlGrant;
108  class SqlIndex;
109  class SqlOperator;
110  class SqlRole;
111  class SqlSequence;
112  class SqlSchema;
113  class SqlTable;
114  class SqlTableSpace;
115  class SqlTrigger;
116  class SqlUserMapping;
117  class SqlView;
118 
128  class EXPORT_SASSQL SQL
129  : public instance<SQL>
130  {
131 
132  public:
133 
137  typedef unsigned long long SqlQueryId_t;
138 
150 
154  DbNotDefined_t = 0,
155 
159  MicrosoftDb_t = 1,
160 
164  MySqlDb_t = 2,
165 
169  OdbcDb_t = 3,
170 
174  OracleDb_t = 4,
175 
179  PostgresDb_t = 5,
180 
184  SqlLiteDb_t = 6,
185 
189  SybaseDb_t = 7
190  };
191 
198  SqlUnknown_t = 0,
199  SqlNULL_t = 1,
200 
201  SqlBigInt_t,
202  SqlBigSerial_t,
203  SqlBinary_t,
204  SqlBit_t,
205  SqlBoolean_t,
206  SqlBox_t,
207  SqlByteA_t,
208  SqlChar_t,
209  SqlCidr_t,
210  SqlCircle_t,
211  SqlDate_t,
212  SqlDateRange_t,
213  SqlDateTime_t,
214  SqlDoublePrecision_t,
215  SqlEnum_t,
216  SqlFloat_t,
217  SqlInet_t,
218  SqlInt4Range_t,
219  SqlInt8Range_t,
220  SqlInteger_t,
221  SqlInterval_t,
222  SqlLine_t,
223  SqlLSeg_t,
224  SqlMacAddr_t,
225  SqlMoney_t,
226  SqlNumeric_t,
227  SqlPath_t,
228  SqlPoint_t,
229  SqlPolygon_t,
230  SqlReal_t,
231  SqlSerial_t,
232  SqlSmallInt_t,
233  SqlSmallSerial_t,
234  SqlText_t,
235  SqlTime_t, // WITH OUT TIMEZONE
236  SqlTimeStamp_t, // WITH OUT TIMEZONE
237  SqlTimeStampTz_t, // WITH TIMEZONE
238  SqlTimeTz_t, // WITH TIMEZONE
239  SqlTsRange_t, // WITH OUT TIMEZONE
240  SqlTsTzRange_t, // WITH TIMEZONE
241  SqlTsVector_t,
242  SqlUuid_t,
243  SqlVarBinary_t,
244  SqlVarChar_t,
245 
246  SqlAny_t,
247  SqlAnyElement_t,
248  SqlAnyArray_t,
249  SqlAnyVector_t,
250  SqlAnyNonArray_t,
251  SqlAnyNonVector_t,
252  SqlAnyEnum_t,
253  SqlAnyRange_t,
254  SqlCString_t,
255  SqlInternal_t,
256  SqlLanguageHandler_t,
257  SqlFdwHandler_t,
258  SqlTsmHandler_t,
259  SqlRecord_t,
260  SqlTrigger_t,
261  SqlEventTrigger_t,
262  SqlPgDllCommand_t,
263  SqlVoid_t,
264  SqlOpaque_t,
265  SqlOid_t,
266 
267  /* Transaction ID */
268  SqlTransactionId_t,
269 
270  /* Physical location of tuple */
271  SqlTupleLocation_t,
272 
273  /* Command ID */
274  SqlCommandId_t,
275 
276  /* JSON */
277  SqlJson_t,
278 
279  /* XML */
280  SqlXml_t,
281 
282  /* ACL */
283  SqlAcl_t,
284 
285  /* String representing an internal node tree */
286  SqlInternalNt_t,
287 
288  /* Internal typefor passing collected command */
289  SqlInternalCollected_t,
290 
291  /* Internal Relative Time */
292  SqlInternalRelTime_t,
293 
294  /* Internal Absolute Time */
295  SqlInternalAbsTime_t,
296 
297  /* Internal Time Interval */
298  SqlInternalInterval_t,
299 
300  /* Reference to Cursor */
301  SqlInternalRefCursor_t,
302 
303  /* existing table name */
304  SqlTable_t,
305 
306  /* RETURNS table (...) */
307  SqlTypeTable_t,
308 
309  /* CREATE TYPE .. ENUM */
310  SqlTypeEnum_t,
311 
312  /* CREATE DOMAIN ... */
313  SqlDomain_t,
314 
315  /* CONSTRAINT */
316  SqlConstraint_t,
317 
318  /* USER TYPE - ENUM */
319  SqlUserEnum_t,
320 
321  /* 63-byte system identifier name. */
322  SqlName_t,
323 
324  /* Registered Procedure - No Args*/
325  SqlRegisteredProcedure_t,
326 
327  /* Registered Procedure - With Args*/
328  SqlRegisteredProcedureArgs_t
329  };
330 
336 
341 
347 
364 
378  SecurityTlsNamedCert_t
379  };
380 
386 
394 
408 
425 
426  /*
427  * (Edited from PostgreSQL documentation):
428  *
429  * GSSAPI is an industry-standard protocol for
430  * authentication defined in RFC 2743.If the database
431  * supports GSSAPI with Kerberos authentication
432  * according to RFC 1964. GSSAPI provides automatic
433  * authentication (single sign-on) for systems that
434  * support it. The authentication itself is secure,
435  * but the data sent over the database connection will
436  * be sent encrypted unless SSL/TLS is used.
437  */
438  AuthGssApi_t,
439 
452 
466 
483 
507 
531 
547 
571 
587 
603  TrustAuth_t
604  };
605 
609  SQL();
610 
614  virtual ~SQL();
615 
621  SecurityType_e SecurityType() const;
622 
633  virtual bool SecurityType(SecurityType_e Type) = 0;
634 
640  AuthenticationType_e AuthenticationType() const;
641 
656  virtual bool AuthenticationType(AuthenticationType_e Type) = 0;
657 
666  virtual void
667  SupportedAuthTypes(std::vector<AuthenticationType_e> & Results) = 0;
668 
679  bool PublicKey(const char * FileName,
680  const char * Password);
681 
689  bool PublicKey(Secure::PEM * TheKey);
690 
696  Secure::PEM * PublicKey();
697 
708  bool PrivateKey(const char * FileName,
709  const char * Password);
710 
718  bool PrivateKey(Secure::PEM * TheKey);
719 
725  Secure::PEM * PrivateKey();
726 
748  virtual bool CreateDatabase(const char * DbName,
749  const char * DbOwner,
750  bool DropIfExists,
751  SqlQueryId_t Id) = 0;
752 
781  virtual bool CreateUser(const char * UserName,
782  const char * Password,
783  bool DropIfExists,
784  SqlQueryId_t Id = 0) = 0;
785 
795  virtual bool UserExists(const char * UserName) = 0;
796 
804  virtual bool DatabaseExists(const char * DbName) = 0;
805 
813  virtual bool TableExists(const char * TableName) = 0;
814 
830  virtual bool UserMemberOf(const char * UserName,
831  const char * GroupName) =0;
842  virtual bool NextRow(SqlQueryId_t CursorId) const = 0;
843 
856  virtual const char *
857  GetFieldValue(unsigned int nThField,
858  SqlQueryId_t Id) const = 0;
859 
877  virtual const char *
878  GetFieldBlob(unsigned int nThField,
879  size_t & BlobSize,
880  SqlQueryId_t Id) const = 0;
881 
907  virtual bool
908  EncodeBlob(const char * FilePath,
909  String & OutBlob) = 0;
910 
933  virtual bool EncodeBlob(const uint8_t * InBlob,
934  size_t InSize,
935  String & OutBlob) = 0;
936 
958  virtual bool DecodeBlob(const char * InValue,
959  uint8_t * & OutBlob,
960  size_t & OutSize) = 0;
961 
986  virtual bool EncodeString(const char * In,
987  String & Out) = 0;
988 
1014  virtual bool
1015  GetLargeObject(unsigned int nThField,
1016  String & OutFileName,
1017  SqlQueryId_t Id) const = 0;
1018 
1033  virtual unsigned long long
1034  SaveLargeObject(String & OutFileName,
1035  SqlQueryId_t Id) const = 0;
1036 
1051  virtual unsigned long long
1052  GetSerialValue(const char * Name,
1053  SqlQueryId_t Id) = 0;
1054 
1066  virtual const char * GetInterfaceName() const = 0;
1067 
1081  virtual unsigned long long
1082  GetAffectedRows(SqlQueryId_t Id) const = 0;
1083 
1097  virtual unsigned long long
1098  GetRowsSelected(SqlQueryId_t Id) const = 0;
1099 
1108  virtual unsigned long long
1109  GetFieldCount(SqlQueryId_t Id) const = 0;
1110 
1119  virtual SqlQueryId_t NewQueryId() const = 0;
1120 
1126  virtual void DestroyQueryId(SqlQueryId_t Id) = 0;
1127 
1138  virtual bool Query(const String & Cmd,
1139  SqlQueryId_t Id) = 0;
1140 
1151  virtual bool Query(const char * Cmd,
1152  SqlQueryId_t Id) = 0;
1153 
1182  virtual bool Connect(const char * HostName,
1183  const char * DbName,
1184  const char * UserName,
1185  const char * Password,
1186  const char * HostAddress = NULL,
1187  uint16_t Port = 0) = 0;
1188 
1194  virtual bool Disconnect() = 0;
1195 
1201  virtual bool IsConnected() const = 0;
1202 
1211  virtual Error * GetResults(SqlQueryId_t Id) const = 0;
1212 
1221  static const char * GetGuid();
1222 
1230  TransactionModeNone_t = 0,
1231 
1236 
1241 
1246 
1251 
1256  TransactionModeSerializable_t,
1257  TransactionModeRepeatableRead_t,
1258  TransactionModeReadCommitted_t,
1259  TransactionModeReadUncommitted_t,
1260  TransactionModeReadWrite_t,
1261  TransactionModeReadOnly_t
1262  };
1263 
1293  virtual TransactionMode_t
1294  TestTransactionFeature(TransactionMode_t tMode,
1295  SqlQueryId_t Id)= 0;
1296 
1308  virtual bool
1309  StartTransaction(TransactionMode_t tMode,
1310  SqlQueryId_t Id) = 0;
1311 
1322  virtual bool CreateSavepoint(const char * SavePointName,
1323  SqlQueryId_t Id) = 0;
1324 
1338  virtual bool ReleaseSavepoint(const char * SavePointName,
1339  SqlQueryId_t Id) = 0;
1340 
1354  virtual bool
1355  RollbackTransaction(const char * SavePointName,
1356  SqlQueryId_t Id) = 0;
1357 
1367  virtual bool CommitTransaction(SqlQueryId_t Id) = 0;
1368 
1372  enum LockMode {
1373  READLOCK,
1374  WRITELOCK
1375  };
1376 
1380  static unsigned int DebugLevel;
1381 
1397  virtual Error * GetError(SqlQueryId_t Id) const = 0;
1398 
1415  virtual Error * GetLastError(SqlQueryId_t Id) const = 0;
1416 
1423  virtual uint16_t GetDefaultPort() const = 0;
1424 
1430  uint16_t Port() const;
1431 
1440  virtual bool
1441  SwitchToDatabase(const char * NewDatabaseName) = 0;
1442 
1453  virtual bool operator ==(const SQL & Other) const = 0;
1454 
1464  virtual bool operator !=(const SQL & Other) const = 0;
1465 
1471  virtual DatabaseType_e DbType() const = 0;
1472 
1478  virtual std::vector<const SqlSchema*> *
1479  GetSchemas() const = 0;
1480 
1486  virtual std::vector<const SqlDomain*> *
1487  GetDomains() const = 0;
1488 
1494  virtual std::vector<const SqlTrigger*> *
1495  GetTriggers() const = 0;
1496 
1502  virtual std::vector<const SqlIndex*> *
1503  GetIndexes() const = 0;
1504 
1510  virtual std::vector<const SqlFunction*> *
1511  GetFunctions() const = 0;
1512 
1518  virtual std::vector<const SqlAggregate*> *
1519  GetAggregates() const = 0;
1520 
1526  virtual std::vector<const SqlConversion*> *
1527  GetConversions() const = 0;
1528 
1534  virtual std::vector<const SqlCast*> *
1535  GetCasts() const = 0;
1536 
1542  virtual std::vector<const SqlForeignTable*> *
1543  GetForeignTables() const = 0;
1544 
1550  virtual std::vector<const SqlForeignServer*> *
1551  GetForeignServers() const = 0;
1552 
1558  virtual std::vector<const SqlOperator*> *
1559  GetOperators() const = 0;
1560 
1566  virtual std::vector<const SqlCollation*> *
1567  GetCollations() const = 0;
1568 
1574  virtual std::vector<const SqlSequence*> *
1575  GetSequences() const = 0;
1576 
1577 
1583  virtual std::vector<const SqlDataType*> *
1584  GetDataTypes() const = 0;
1585 
1586 
1592  virtual std::vector<const SqlRole*> *
1593  GetRoles() const = 0;
1594 
1595 
1601  virtual std::vector<const SqlView*> *
1602  GetViews() const = 0;
1603 
1609  virtual std::vector<const SqlExtension*> *
1610  GetExtensions() const = 0;
1611 
1617  virtual std::vector<const SqlEventTrigger*> *
1618  GetEventTriggers() const = 0;
1619 
1625  virtual std::vector<const SqlDatabase*> *
1626  GetDatabases() const = 0;
1627 
1633  virtual std::vector<const SqlUserMapping*> *
1634  GetUserMappings() const = 0;
1635 
1648  virtual uint64_t ServerVersion() const = 0;
1649 
1666  static void ToVector(const char * OneString,
1667  std::vector<char*> & Results);
1668 
1678  static void FromVector(std::vector<char*> & Vector,
1679  String & Results);
1680 
1681  protected:
1682 
1683  // Return the DB error in the form of 'class error'
1684  //
1685  // StringCache...
1686  //
1690  const char * _DatabaseName;
1691 
1695  const char * _HostName;
1696 
1700  const char * _HostAddress;
1701 
1705  const char * _User;
1706 
1711  const char * _Password;
1712 
1717  uint16_t _Port;
1718 
1723 
1728  static const char * _Guid;
1729 
1735 
1741 
1747 
1753 
1754  public:
1755 
1759  struct DbTypeList
1760  {
1764  const char * DbName;
1765 
1770  const char * SaSName;
1771 
1776  const char * Printable;
1777 
1787  };
1788 
1792  static const SQL::DbTypeList DtList[];
1793  };
1794 
1795 
1801  : public instance<DataTypeBase>
1802  {
1803 
1804  public:
1805 
1812 
1816  virtual ~DataTypeBase();
1817 
1821  SQL::SqlDataType_e Type() const;
1822 
1828  void Type(SQL::SqlDataType_e Dt);
1829 
1834  virtual const char * BaseType() const = 0;
1835 
1839  virtual const char * toString() const = 0;
1840 
1846  virtual bool fromString(const char * TheValue) = 0;
1847 
1853  bool fromString(const String & TheValue);
1854 
1860  bool fromString(const std::string & TheValue);
1861 
1862 #ifdef USE_QT5
1863 
1868  bool fromString(const QString & TheValue);
1869 #endif
1870 
1876  virtual DataTypeBase * Copy() const = 0;
1877 
1900  static bool GenExtractUtcTime_t(const char * ColName,
1901  String & Results);
1902 
1913  static bool ToSqlTime(time_t TheTimeT,
1914  String & Results);
1915 
1916 
1923  std::vector<int32_t> * Array() const;
1924 
1935  void Dim(uint32_t Dim,
1936  int32_t DimSize);
1956  const char * ArraySpecs(const char * Format = NULL) const;
1957 
1958  protected:
1959 
1964 
1968  std::vector<int32_t> * _ArrayInfo;
1969 
1973  mutable char * _ToStringCache;
1974 
1975  };
1976 
1977  }
1978  }
1979 }
1980 
1981 #ifdef BUILDING_LIBRARY_SQL
1982 #include "SqlRole.hpp"
1983 #include "SqlFunction.hpp"
1984 #else
1985 #include <SaS/Sql/SqlRole.hpp>
1986 #include <SaS/Sql/SqlFunction.hpp>
1987 #endif
1988 
1989 #endif //_DOUGLAS_MARK_ROYER_SQL_TOOLS_DATATYPEBASE_HPP_
Secure::PEM * _PrivateKey
When non-NULL, path to connection certificate file.
Definition: SQL.hpp:1734
const char * _HostAddress
HostName or address will be used.
Definition: SQL.hpp:1700
A C++ for internationalization (I18N) of messages.
Definition: msgCat2.hpp:91
char * _ToStringCache
When non-NULL, the object value as a (char*).
Definition: SQL.hpp:1973
const char * SaSName
This library object name: SQLINTEGER, SQLTIMESTAMP, ...
Definition: SQL.hpp:1770
SecurityType_e _SecurityType
The connection security type to use, or in use after the connection.
Definition: SQL.hpp:1752
AuthenticationType_e
The authentication type.
Definition: SQL.hpp:385
SQL::SqlDataType_e _Dt
The data type.
Definition: SQL.hpp:1963
const char * Printable
Printable (in a comment or documentation) name.
Definition: SQL.hpp:1776
DatabaseType_e
The vendor (PostgreSQL, MySQL, ...)
Definition: SQL.hpp:149
The SQL class is a pure virtual interface class for SQL access.
Definition: SQL.hpp:128
No special authentication, just connect.
Definition: SQL.hpp:393
Map database types between objects, SQL, and YACC.
Definition: SQL.hpp:1759
(Edited from PostgreSQL documentation):
Definition: SQL.hpp:424
const char * _Password
This is the SQL user password, it is removed as soon as connected.
Definition: SQL.hpp:1711
const char * DbName
The database type.
Definition: SQL.hpp:1764
std::vector< int32_t > * _ArrayInfo
When an array, this pointer will be NON-NULL.
Definition: SQL.hpp:1968
A base object for DataType objects.
Definition: SQL.hpp:1800
Instance counting and tracking.
Definition: instance.hpp:90
SecurityType_e
The connection security type.
Definition: SQL.hpp:335
AuthenticationType_e _AuthType
The authentication type to use, or in use.
Definition: SQL.hpp:1746
TransactionMode_t
SQL Transaction modes.
Definition: SQL.hpp:1226
Secure::PEM * _PublicKey
When non-NULL, path to connection certificate file.
Definition: SQL.hpp:1740
This is a class that allows:
Definition: String.hpp:114
(Edited from PostgreSQL documentation):
Definition: SQL.hpp:407
msgCat * _Msgcat
Message catalog for connection.
Definition: SQL.hpp:1722
Copyright Douglas Mark Royer DouglasRoyer@gmail.com.
Definition: Base.hpp:98
static unsigned int DebugLevel
Set this to higher number for messages to stderr.
Definition: SQL.hpp:1380
static const char * _Guid
Application GUID, some apps authenticate and are licensed in this way.
Definition: SQL.hpp:1728
SqlDataType_e
Generic names for data types.
Definition: SQL.hpp:197
const char * _User
SQL user name used.
Definition: SQL.hpp:1705
SQL::SqlDataType_e Yacc
What YACC creates from token.
Definition: SQL.hpp:1786
const char * _DatabaseName
Name of the currently selected database.
Definition: SQL.hpp:1690
uint16_t _Port
The port number used, if zero, use the default for the server.
Definition: SQL.hpp:1717
A generic error message class.
Definition: Error.hpp:95
const char * _HostName
Name of the currently selected host.
Definition: SQL.hpp:1695