------------------------------------------------------------------------------ -- G N A T C O L L -- -- -- -- Copyright (C) 2005-2020, AdaCore -- -- -- -- This library is free software; you can redistribute it and/or modify it -- -- under terms of the GNU General Public License as published by the Free -- -- Software Foundation; either version 3, or (at your option) any later -- -- version. This library is distributed in the hope that it will be useful, -- -- but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHAN- -- -- TABILITY or FITNESS FOR A PARTICULAR PURPOSE. -- -- -- -- As a special exception under Section 7 of GPL version 3, you are granted -- -- additional permissions described in the GCC Runtime Library Exception, -- -- version 3.1, as published by the Free Software Foundation. -- -- -- -- You should have received a copy of the GNU General Public License and -- -- a copy of the GCC Runtime Library Exception along with this program; -- -- see the files COPYING3 and COPYING.RUNTIME respectively. If not, see -- -- . -- -- -- ------------------------------------------------------------------------------ -- This package provides subprograms to interact with a database. It provides -- a DBMS-agnostic API, which is further specialized in children packages for -- each supported DBMS system. -- There are various reasons to use this package preferably to the low-level -- package specific to each DBMS: -- - your code is not specialized for a specific system, and can therefore -- more easily be moved from one system to another -- - automatic connection and reconnection to the database. -- If the connection is lost, this package will automatically attempt to -- reconnect (it also automatically connects the first time a query is -- emitted). -- - task safe -- - automatic transactions -- whenever you start modifying the database, a transaction is -- automatically started. That helps ensure that only self-consistent -- changes are performed, and is more efficient on most DBMS. No command -- is sent to the DBMS if the current transaction is in a failure state, -- which is also more efficient -- - support for local caching of some queries -- Very often, a database will contain tables whose contents rarely -- changes, and corresponds to Ada enumerations. In such cases, this -- package can cache the result locally to save round-trips to the DBMS -- system. -- - logging support. -- All sql queries that are executed are traced through GNATCOLL.Traces -- -- There are various ways to execute queries and get their results: -- - two types of cursors (forward_cursor or direct_cursor) depending on -- whether you want to keep all results in memory or not. Using direct -- cursors is more flexible, but slower since there is a need for a lot -- more memory allocations. Examples of timing (executing 10_000 times a -- query joining two tables, returning 400 rows). -- sqlite: -- FORWARD_CURSOR, getting first row => 0.707530000 s -- FORWARD_CURSOR, iterating on all rows => 3.193714000 s -- DIRECT_CURSOR, getting first row => 5.541400000 s -- DIRECT_CURSOR, iterating on all rows => 5.600546000 s -- -- - Prepared statements on the client -- Statements are generally written with GNATCOLL.SQL. They then need to -- be converted to String to be sent to the DBMS server. This conversion -- (along with possible auto-completion of the query) takes some non -- negligible amount of time. You can thus prepare such queries once and -- for all. Here is an example on the same query as above, but the query -- is prepared once, and then executed 10_000 times. This is only looking -- at the first row in the result, so should be compared with the first -- line above. -- sqlite: -- FORWARD_CURSOR => 0.413184000 s -- DIRECT_CURSOR => 5.398043000 s -- -- - Prepared statements on the server -- In addition to the above client-side preparation, most DBMS systems -- support the notion of analyzing the query on the server, and optimize -- it there. Such a preparation is valid for a specific connection (so -- several preparations will be needed if you have multiple concurrent -- connections to the database (but this API takes care of that -- automatically for you). This can provide significant speed up. When -- using direct_cursor, we still need to perform a lot of memory -- allocations to store the results in memory -- (400 rows * 2 columns * 10_000 iterations allocations in the example) -- sqlite: -- FORWARD_CURSOR => 0.047700000 s -- DIRECT_CURSOR => 5.014961000 s -- -- - Caching on the client side -- Last, this API is able to cache the result of a query locally on the -- client, and thus save any round-trip to the server. At the cost of some -- memory, it provides the fastest possible access to the data. This is -- only usable with prepared statements, and only makes sense for direct -- cursors, since by definition the cache should be iterable several -- times). Note also that using server-side preparation does not -- significantly speed things up, since the statement is executed only -- once anyway. Here, we are only doing 400 * 2 allocations to store -- results in memory. -- sqlite: -- DIRECT_CURSOR => 0.015502000 s pragma Ada_2012; with Ada.Calendar; with Ada.Containers.Indefinite_Hashed_Maps; with Ada.Finalization; with Ada.Strings.Equal_Case_Insensitive; with Ada.Strings.Hash_Case_Insensitive; with System; private with GNATCOLL.Refcount; with GNAT.Strings; with GNATCOLL.Plugins; with GNATCOLL.SQL_Impl; with GNATCOLL.Strings; use GNATCOLL.Strings; package GNATCOLL.SQL.Exec is Perform_Queries : Boolean := True; -- If False, no operation is performed on the database, but the queries -- are logged as if the operation took place. This is only intended for -- automatic testsuites. ------------- -- Cursors -- ------------- type Forward_Cursor is tagged private; No_Element : constant Forward_Cursor; -- A cursor that iterates over all rows of the result of an SQL query. A -- single row can be queried at a time, and there is no possibility to go -- back to a previous row (since not all DBMS backends support this). -- This type automatically takes care of memory management and frees its -- memory when no longer in use. -- This type is tagged only so that you can override it in your own -- applications (an example is to add an Element primitive operation which -- converts the current row into a specific Ada record for ease of use) type Abstract_DBMS_Forward_Cursor is abstract tagged private; type Abstract_Cursor_Access is access all Abstract_DBMS_Forward_Cursor'Class; -- Internal contents of a cursor. -- Instead of overriding Cursor directly, the support packages for the DBMS -- must override this type, so users do not have to use unconstrained types -- in their code, thus allowing "Result : Cursor" declarations. -- In practice, DBMS-specific backends will derive from -- gnatcoll-sql-exec-dbms_cursor, which defines the required primitive ops ---------------- -- Parameters -- ---------------- -- All database systems support a way of writing queries without using -- specific values. Instead, those are bound when the query is executed. -- Thus, in sqlite, the query might look like: -- SELECT * FROM table WHERE table.field1 = ?1 -- whereas in postgreSQL it would be -- SELECT * FROM table WHERE table.field1 = $1 -- -- Such queries are created in GNATCOLL through the use of -- GNATCOLL.SQL.Text_Param, GNATCOLL.SQL.Integer_Param,... type SQL_Parameter is new SQL_Impl.SQL_Parameter_Base with null record; Null_Parameter : constant SQL_Parameter; function "+" (Value : access constant String) return SQL_Parameter; function Copy (Value : access constant String) return SQL_Parameter; -- This is like "+", but forces sql engine to make a copy of Value when -- it might be needed after the query executes. This has no effect on -- PostgreSQL, which already systematically does this copy function "+" (Value : String) return SQL_Parameter; function "+" (Value : Unbounded_String) return SQL_Parameter; function "+" (Value : Integer) return SQL_Parameter; function As_Bigint (Value : Long_Long_Integer) return SQL_Parameter; function "+" (Value : Boolean) return SQL_Parameter; function "+" (Value : Float) return SQL_Parameter; function As_Long_Float (Value : Long_Float) return SQL_Parameter; function "+" (Value : Character) return SQL_Parameter; function "+" (Time : Ada.Calendar.Time) return SQL_Parameter; function "+" (Value : T_Money) return SQL_Parameter; type SQL_Parameters is array (Positive range <>) of SQL_Parameter; No_Parameters : constant SQL_Parameters; function Image (Format : Formatter'Class; Params : SQL_Parameters) return String; -- Return a displayable version of the parameters list -------------------------- -- Database_Description -- -------------------------- -- Data common to all the concurrent connections to the database. type Error_Reporter is abstract tagged private; type Error_Reporter_Access is access all Error_Reporter'Class; -- This type is used by the various methods that need to report SQL -- errors or issues with the database. Not all the primitive operations -- are used by all database backends (for instance, postgresql does not -- report an error for a corrupted database). type Database_Description_Record (Caching : Boolean; Errors : access Error_Reporter'Class) is abstract tagged private; type Database_Description is access all Database_Description_Record'Class; -- Describes how to access a database, and stores global caches associated -- with that database. -- This type is derived in each of the DBMS specific packages. See -- GNATCOLL.SQL.Sqlite and GNATCOLL.SQL.Postgres for instance. -- -- If Cache is true, some statements will be cached locally in the -- connection (see the parameter Use_Cache for the Prepare subprograms -- below). package Name_Values is new Ada.Containers.Indefinite_Hashed_Maps (String, String, Hash => Ada.Strings.Hash_Case_Insensitive, Equivalent_Keys => Ada.Strings.Equal_Case_Insensitive); function Setup (Kind : String; Options : Name_Values.Map; Errors : access Error_Reporter'Class) return Database_Description; -- Return description of the database engine defined by Kind parameter. -- If the kind of database was not detected, this function returns null. -- Errors (if specified) will be used to report errors and warnings to the -- application. Errors is never freed. type Database_Connection_Record (Descr : access Database_Description_Record'Class; Always_Use_Transactions : Boolean) is abstract new Formatter with private; type Database_Connection is access all Database_Connection_Record'Class; -- A thread-specific access to a database. Each thread, in an application, -- should have its own access to the database, so that transactions really -- are thread-specific. This also stores the result of the last query -- executed, and takes care of creating and canceling transactions when -- needed. -- This type is really an access to some data, so that all subprograms -- below can take IN parameters. This simplifies user-code, which can -- therefore contain functions. -- This abstract type is specialized in GNATCOLL.SQL.Postgres and other -- child packages. -- Always_Use_Transactions is used internally to indicate whether GNATCOLL -- should always start a SQL transaction even for SELECT statements. This -- might result in significant speed ups for some DBMS (sqlite) function Build_Connection (Self : access Database_Description_Record) return Database_Connection is abstract; -- Returns a new object to represent connection to the database. -- On return, no connection to the DBMS has been made (this will -- be done lazily by the turned object). -- If instead you want to reuse an existing connection, you should use -- Reset_Connection below. procedure Free (Description : in out Database_Description_Record) is null; procedure Free (Description : in out Database_Description); -- Free memory associated with description. -- This should only be called when the last database connection was closed, -- since each connection keeps a handle on the description -------------------- -- Error_Reporter -- -------------------- procedure Free (Self : in out Error_Reporter) is null; -- Free the memory used by Self procedure On_Database_Corrupted (Self : in out Error_Reporter; Connection : access Database_Connection_Record'Class) is null; -- Called when the database is corrupted. -- A call to On_Error will also occur. procedure On_Warning (Self : in out Error_Reporter; Connection : access Database_Connection_Record'Class; Message : String) is null; -- Called when a warning is emitted by the database. procedure On_Error (Self : in out Error_Reporter; Connection : access Database_Connection_Record'Class; Message : String) is null; -- Called when an error is emitted by the database. ------------------------- -- Database_Connection -- ------------------------- function Check_Connection (Connection : access Database_Connection_Record) return Boolean; -- Attempt to connect to the database, and return True if the connection -- was successful. Calling this subprogram is optional, since it will be -- done automatically when calling Execute (see below). This can however be -- used to ensure that the database works properly. procedure Fetch (Result : out Forward_Cursor; Connection : access Database_Connection_Record'Class; Query : String; Params : SQL_Parameters := No_Parameters); procedure Fetch (Result : out Forward_Cursor; Connection : access Database_Connection_Record'Class; Query : GNATCOLL.SQL.SQL_Query; Params : SQL_Parameters := No_Parameters); procedure Execute (Connection : access Database_Connection_Record'Class; Query : GNATCOLL.SQL.SQL_Query; Params : SQL_Parameters := No_Parameters); procedure Execute (Connection : access Database_Connection_Record'Class; Query : String; Params : SQL_Parameters := No_Parameters); -- Submit a query to the database, log it and wait for the result. -- Logs the query, as needed. -- The query can either be written directly as a string, or through a -- SQL_Query (which is encouraged, since it provides additional safety). -- -- We used procedures instead of functions here for several reasons: that -- allows you to extend the Cursor type without overriding these -- procedures, this is slightly more efficient (since Cursor is a -- controlled type), and that forces the user to declare a local variable, -- rather than use Value (Execute (...), ...), which might have -- unpredictable results depending on when the controlled type is -- finalized. This also makes it easier to have your own specialized -- Execute functions in your application that return specific types of -- cursor, without requiring possibly costly copies of the result to -- convert from one type to another. -- -- The names differ (Fetch and Execute) depending on whether the result is -- read or not. This is so that you can use dotted notation, as in: -- Curs.Fetch (Connection, Query) -- which reads better than Curs.Execute (Connection, Query), since we -- execute the query, not the cursor. -- -- Result is always first reset to No_Element, so any custom field you -- might have will also be reset function Insert_And_Get_PK (Connection : access Database_Connection_Record'Class; Query : GNATCOLL.SQL.SQL_Query; Params : SQL_Parameters := No_Parameters; PK : SQL_Field_Integer) return Integer; function Insert_And_Get_PK (Connection : access Database_Connection_Record; Query : String; Params : SQL_Parameters := No_Parameters; PK : SQL_Field_Integer) return Integer; -- Execute the INSERT statement, and retrieve the primary key of the -- newly inserted row. This is similar, but more efficient, to calling -- Fetch (Result, Connection, Query, Params); -- return Last_Id (Result, Connection, Field); -- The primary key must be an integer field. -- The function also exists for prepared queries. procedure Close (Connection : access Database_Connection_Record) is abstract; procedure Free (Connection : in out Database_Connection); -- Close the connection to the database, if needed. -- Only Free needs to be called, and it will automatically call Close. procedure Mark_As_Closed (Connection : access Database_Connection_Record'Class; Closed : Boolean); function Was_Closed (Connection : access Database_Connection_Record'Class) return Boolean; -- This is for internal use only, marks the connection as closed. -- No further operation should be performed on it, in particular -- finalization of prepared statements. -- It is valid to pass a freed pointer to Was_Closed function Error (Connection : access Database_Connection_Record) return String is abstract; -- Return the last error message set by the database function Success (Connection : access Database_Connection_Record) return Boolean; -- Whether the last query succeeded. Note that when a query that modifies -- the database failed, no further query that modifies the database can be -- executed until the current transaction has been rolled back. This -- mirrors the standard behavior of postgres, and avoids sending a query -- that would not be executed anyway. procedure Set_Failure (Connection : access Database_Connection_Record'Class; Error_Msg : String := ""); -- Mark the transaction as failed. In general, this does not need to be -- done, but is needed when you expect for instance a SELECT to return at -- least one row, but it doesn't return any after an insertion. -- Error_Msg has the same semantics as for Rollback. If it isn't specified, -- this subprogram will test whether the database itself currently reports -- an error, and use that one instead. procedure Rollback (Connection : access Database_Connection_Record'Class; Error_Msg : String := ""); -- This command emits a "ROLLBACK" of the current transaction. -- When automatic transactions are enabled, it does nothing if no -- transaction is currently active. When automatic transactions are -- disabled, it will systematically emit the ROLLBACK. -- This should be called as the last operation before the threads ends, to -- clean up the connection. The user must explicitly commit the transaction -- at an appropriate time. -- This resets the "Success" status to True. -- If Error_Msg is specified, that will be the message returned by -- Last_Error_Message, which users can later use to know why the -- transaction was aborted. function Last_Error_Message (Connection : access Database_Connection_Record'Class) return String; -- Reports the last error message on this connection (i.e. the one that -- made the transaction fail) procedure Automatic_Transactions (Connection : access Database_Connection_Record'Class; Active : Boolean := True); function Automatic_Transactions (Connection : access Database_Connection_Record'Class) return Boolean; -- Activate (which is the default) or deactivate automatic SQL -- transactions. When enabled, the first SQL statement that potentially -- modifies the database (basically other than a SELECT) will start a -- transaction first (with BEGIN). It is however, your responsibility to -- finally do a Commit or Rollback. -- When disabled, transactions will never be started automatically (but -- you can use Start_Transaction to start one). -- It is recommended to change this setting when you just retrieved a new -- connection, not while executing SQL statements. function Start_Transaction (Connection : access Database_Connection_Record'Class) return Boolean; -- This command emits a "BEGIN" to start a new transaction. -- When automatic transactions are enabled, it does nothing if a -- transaction is already active. When automatic transactions are -- disabled, it will systematically emit the BEGIN. -- -- This does not need to be called in general, since transactions are -- automatically started when you modify the contents of the database, -- but you might need to start one manually in some cases (declaring a -- cursor with "DECLARE .. CURSOR" for instance). -- -- Return True if a transaction was started, False if one was already in -- progress. function In_Transaction (Connection : access Database_Connection_Record'Class) return Boolean; -- Return True if a transaction is taking place (i.e. at least one -- modification to the database took place, and was not COMMIT'd or -- ROLLBACK'd. procedure Commit_Or_Rollback (Connection : access Database_Connection_Record'Class); procedure Commit (Connection : access Database_Connection_Record'Class) renames Commit_Or_Rollback; -- Commit or rollback the current transaction, depending on whether we had -- an error. This does not affect the result of Success (unless COMMIT -- itself fails), so that you can still know afterward whether the -- transaction was committed or not. procedure Force_Connect (Connection : access Database_Connection_Record) is abstract; -- Force a connection to the DBMS. Normally, this connection is done -- automatically the first time an SQL command is executed, but it might -- be needed sometimes to force a connection earlier. procedure Force_Disconnect (Connection : access Database_Connection_Record) is abstract; -- Force an immediate disconnection of the connection to the DBMS. This -- does not perform any cleanup action, and is intended only for fault -- injection during application testing. function Connected_On (Connection : access Database_Connection_Record) return Ada.Calendar.Time is abstract; -- Timestamp for the connection to the server. This is used to detect -- when a connection has been reconnected (for instance because it was lost -- at some point). procedure Invalidate_Cache; -- Invalid all caches associated with the database (for all connections). -- Some queries can be cached (see Execute below) for more efficiency. procedure Reset_Connection (Connection : access Database_Connection_Record'Class; Username : String := ""); -- Reset the contents of Connection. -- This terminates any on-going transaction and resets various internal -- fields. -- In general, it is better to use Tasking.Get_Task_Connection which does -- the necessary things, but when not in a multi-tasking application it is -- more efficient to have one "global" variable representing the single -- connection, and initialize it with this procedure -- -- Username is used when tracing calls to the database. It is not the same -- as the user used to log in the database (typically, the username would -- be set to a unique identifier for the current application user, for -- instance the login name, whereas the application would always use a -- common user/password to log in the database) function Get_Description (Connection : access Database_Connection_Record'Class) return Database_Description; -- Return the description of the database to which we are connected function Can_Alter_Table_Constraints (Self : access Database_Connection_Record) return Boolean is abstract; -- Whether it is possible to add constraints to an existing table. -- This is intended for use when creating tables (in GNATCOLL.SQL.Inspect) function Has_Pragmas (Self : access Database_Connection_Record) return Boolean is abstract; -- Whether the database knows about the "PRAGMA" command. ---------------------------- -- Transaction_Controller -- ---------------------------- -- This type simplify usage of transactions. A transaction starts -- and does commit or rollback automatically. All that needed for this -- is declare instance of this type. For example: -- -- procedure X -- is -- Transaction : Transaction_Controller (DB); -- begin -- ... -- end X; -- type Transaction_Controller (DB : Database_Connection) is tagged private with Warnings => Off; -- To prevent "Unused" warnings ------------------------------------------ -- Retrieving results - Forward cursors -- ------------------------------------------ -- The following subprograms represent a way to access the various -- columns returned by a query. A single row can be accessed at a time, -- since not all DBMS systems provide ways to query all results in memory -- at once (which might also not be efficient in the case of big tables). -- -- These subprograms do not provide the same generality that DBMS-specific -- functions would, but represent with the most frequent use done with a -- result. type Field_Index is new Natural; No_Field_Index : Field_Index'Base := Field_Index'First - 1; function Processed_Rows (Self : Forward_Cursor) return Natural; -- The number of rows that were returned so far by the cursor. Every time -- you call Next, this is incremented by 1. If you looped until Has_Row -- returned False, this gives you the total number of rows in the result -- (which can not be computed without traversing all the results). -- If the query you executed is a DELETE, INSERT or UPDATE, this returns -- the number of rows modified by the query. function Has_Row (Self : Forward_Cursor) return Boolean; -- Whether there is a row to process. Fetching all the results from a query -- is done in a loop similar to: -- Cursor := Execute (...) -- while Has_Row (Cursor) loop -- ... -- Next (Cursor); -- end loop; procedure Next (Self : in out Forward_Cursor); -- Moves to the next row of results. This is not implemented as a function, -- since once the cursor was moved to the next field, there is no way to -- move back to the previous row. function Current (Self : Forward_Cursor) return Positive; -- Index of the current row. The first row is always numbered 1 function Value (Self : Forward_Cursor; Field : Field_Index) return String; -- Gets the field value as a string function Unbounded_Value (Self : Forward_Cursor; Field : Field_Index) return Unbounded_String; -- Gets the field value as an Unbounded_String function XString_Value (Self : Forward_Cursor; Field : Field_Index) return XString; -- Gets the field value as an XString function Boolean_Value (Self : Forward_Cursor; Field : Field_Index) return Boolean; function Integer_Value (Self : Forward_Cursor; Field : Field_Index; Default : Integer) return Integer; function Integer_Value (Self : Forward_Cursor; Field : Field_Index) return Integer; -- Reads a value as an integer. The second version might raise a -- Constraint_Error if the field is null or does not contain an integer. -- The first version will return the default instead. function Bigint_Value (Self : Forward_Cursor; Field : Field_Index; Default : Long_Long_Integer) return Long_Long_Integer; function Bigint_Value (Self : Forward_Cursor; Field : Field_Index) return Long_Long_Integer; -- Reads a value as a bigint. function Float_Value (Self : Forward_Cursor; Field : Field_Index; Default : Float) return Float; function Float_Value (Self : Forward_Cursor; Field : Field_Index) return Float; -- Reads a value as a float. The second version might raise a -- Constraint_Error if the field is null or does not contain a float. -- The first version will return the default instead. function Long_Float_Value (Self : Forward_Cursor; Field : Field_Index; Default : Long_Float) return Long_Float; function Long_Float_Value (Self : Forward_Cursor; Field : Field_Index) return Long_Float; -- Reads a value as a long float. The second version might raise a -- Constraint_Error if the field is null or does not contain a long float. -- The first version will return the default instead. function Money_Value (Self : Forward_Cursor; Field : Field_Index) return T_Money; function Time_Value (Self : Forward_Cursor; Field : Field_Index) return Ada.Calendar.Time; -- Return a specific cell, converted to the appropriate format function Is_Null (Self : Forward_Cursor; Field : Field_Index) return Boolean; -- True if the corresponding cell is not set function Last_Id (Self : Forward_Cursor; Connection : access Database_Connection_Record'Class; Field : SQL_Field_Integer) return Integer; -- Return the value set for field in the last INSERT command on that -- connection. -- Field must be an automatically incremented field (or a sql sequence). -- Returns -1 if the id could not be queried (perhaps the previous insert -- failed or was never committed). When the last_id could not be retrieved, -- the connection is set to the failure state -- Depending on the backend, this id might be computed through a sql query, -- so it is better to cache it if you need to reuse it several times. function Field_Count (Self : Forward_Cursor) return Field_Index; -- The number of fields per row in Res function Field_Name (Self : Forward_Cursor; Field : Field_Index) return String; -- The name of a specific field in a row of Res ------------------------- -- Prepared statements -- ------------------------- -- Prepared statements are a way to optimize your application and its -- queries. There are several levels of preparation: -- -- * Create parts of queries in advance, for instance a SQL_Field_List. -- This does not save a lot of CPU time, but saves a few system calls -- to malloc. This does not need any of the following subprograms. -- -- * Precompute (and auto-complete) sql queries generated from -- GNATCOLL.SQL. That API is rather heavy, and computing -- auto-completion might be time consuming. This preparation is only -- client side and does not involve the DBMS. -- -- * DBMS systems all have a way to prepare statements (on the server -- this time). This involves optimizing the query and how it should be -- executed. Such prepared statements, however, are only valid while -- the connection to the database lasts (or until you explicitly close -- the prepared statement. -- -- * GNATCOLL.SQL.Exec is also able to cache (on the client) the result -- of some queries. This way, you avoid communication with the DBMS -- altogether, which provides significant speed up for often executed -- queries (like tables of valid values for fields, aka enumerations). -- -- When combined, both of these will significantly speed up execution of -- queries. However, there is often little point in running exactly the -- same query several times. For this reason, queries can be parameterized, -- where the parameters can be changed before each execution. Most DBMS -- support this efficiently -- -- Preparing statements in memory ---------------------------------- -- The first time the resulting statement is executed, the internal tree -- structure will be converted to a string, and kept as is afterward. -- The tree structure is then freed. -- This saves memory, and is more efficient since you are saving a lot in -- terms of malloc and functions returning strings. -- The memory is automatically freed when the statement goes out of scope. -- -- It is better to use such a Cached_Statement rather than simply storing -- the conversion to a string yourself: -- QS : constant String := To_String (DB, QS); -- The conversion to string depends on the specific database backend you -- are using (for instance, sqlite does not encode booleans the same way -- that postgreSQL does). -- Thus the conversion to string needs to be done only when you have an -- actual connection, and thus cannot be done at the library level. -- -- Caching statement results ----------------------------- -- If Use_Cache is True, and you are executing a SELECT query, the result -- of a previous execution of that query will be reused rather than -- executed again. If it was never executed, it will be cached for later -- use (no caching takes place if Use_Cache is False). This should mostly -- be used for queries to tables that almost never change, i.e. that store -- "enumeration types". The cache must be specifically invalidated (see -- Invalidate_Cache and Clear_Cache) to reset it, although it will also -- expire automatically and be refreshed after a while. -- -- Preparing statements on the server -------------------------------------- -- If On_Server is true, then a connection-specific preparation is also -- done on the server, for further optimization. Otherwise, the -- result of this call is to generate the string representation (and auto -- completion) of the query only once, and reuse that later on (that still -- provides a significant speed up). This also provides a way to cache the -- result of the query locally on the client. -- In general, On_Server should only be set if the query contains -- parameters (since otherwise it is too specialized to be worth keeping -- in memory). -- -- There is little gain in having both Use_Cache and On_Server be true: the -- query is executed only once (until the cache expires) on the server -- anyway. -- -- Name is used in the logs (and sometimes in the DBMS) to uniquely show -- the statement. If unspecified, an automatic name is computed. -- -- Global prepared statements ------------------------------- -- -- The idea is that Prepared_Statement could be global variables prepared -- during the elaboration. Internally, they are accessed from within a -- protected record, so it is safe to have them as global variables even in -- a multi-threaded application. It is however possible to only use these -- as local variables, if a little inefficient since the conversion from -- SQL structures to a string has to be done each time. On the other hand, -- it saves memory since you don't need to keep the prepared statements for -- ever in memory. type Prepared_Statement is tagged private; No_Prepared : constant Prepared_Statement; -- A precomputed SQL statement, on the client side. -- This type is reference counted and will automatically free memory or -- release DBMS resources when it goes out of scope. function Prepare (Query : SQL_Query; Auto_Complete : Boolean := False; Use_Cache : Boolean := False; On_Server : Boolean := False; Index_By : Field_Index'Base := No_Field_Index; Name : String := "") return Prepared_Statement; function Prepare (Query : String; Use_Cache : Boolean := False; On_Server : Boolean := False; Index_By : Field_Index'Base := No_Field_Index; Name : String := "") return Prepared_Statement; -- Prepare the statement for multiple executions. -- If Auto_Complete is true, the query is first auto-completed. -- If Index_By is not No_Field_Index, the Direct_Cursors produced from this -- statement would be indexed by the field. It will be possible to call -- Find routine to set cursor position to the record with defined field -- value. If the field value is not unique, the index would contain -- position to only first from the records with same field values. function Is_Prepared_On_Server (Stmt : Prepared_Statement) return Boolean; -- True if the statement was prepared on server side. Even if the On_Server -- was True on call to Prepare, the statement might not be prepared on -- server because not all databases support it or in case when user chooses -- duplicated Name on call to Prepare. See Is_Prepared_On_Server_Supported -- call. Note that the "prepare on server" happens on the first call to -- Execute or Fetch with the prepared statement. procedure Clear_Cache (Stmt : Prepared_Statement); -- Clear cached data related to this statement procedure Fetch (Result : out Forward_Cursor; Connection : access Database_Connection_Record'Class; Stmt : Prepared_Statement'Class; Params : SQL_Parameters := No_Parameters); procedure Execute (Connection : access Database_Connection_Record'Class; Stmt : Prepared_Statement'Class; Params : SQL_Parameters := No_Parameters); -- Execute a prepared statement on the connection. function Insert_And_Get_PK (Connection : access Database_Connection_Record; Stmt : Prepared_Statement'Class; Params : SQL_Parameters := No_Parameters; PK : SQL_Field_Integer) return Integer; -- Execute a prepared insert statement, and return the Id of the newly -- inserted row. See documentation for Insert_And_Get_PK for non-prepared -- statements. -- Stmt must be used at least once through this function before you use -- Execute or Fetch on it, otherwise it might be incorrectly prepared -- (missing returned value) and you would not get the id of the row -- as expected. function To_String (Connection : access Database_Connection_Record; Stmt : Prepared_Statement'Class) return String; -- Return the SQL statement for Stmt. ----------------------------------------- -- Retrieving results - Direct cursors -- ----------------------------------------- type Direct_Cursor is new Forward_Cursor with private; No_Direct_Element : constant Direct_Cursor; -- A direct cursor is a cursor that keeps all its results in memory, and -- gives access to any of the rows in any order. -- As opposed to a Forward_Cursor, you can iterate several times over the -- results. On the other hand, a direct_cursor uses more memory locally, so -- might not be the best choice systematically. function Rows_Count (Self : Direct_Cursor) return Natural renames Processed_Rows; -- Return total number of rows in result. -- Processed_Rows will always return the number read from the database procedure First (Self : in out Direct_Cursor); procedure Last (Self : in out Direct_Cursor); -- Moves the cursor on the first or last row of results; procedure Absolute (Self : in out Direct_Cursor; Row : Positive); -- Moves the cursor on the specific row of results. -- The first row is numbered 1 procedure Relative (Self : in out Direct_Cursor; Step : Integer); -- Moves the cursor by a specified number of rows. Step can be negative to -- move backward. Using Step=1 is the same as using Next procedure Find (Self : in out Direct_Cursor; Value : Integer); procedure Find (Self : in out Direct_Cursor; Value : String); -- Search the record with specified field value over the internal cursor -- index by field defined on Prepare routine call in Index_By parameter. -- Set cursor position to the found row. If rows is not indexed, the -- Constraint_Error will be raised. procedure Fetch (Result : out Direct_Cursor; Connection : access Database_Connection_Record'Class; Stmt : Prepared_Statement'Class; Params : SQL_Parameters := No_Parameters); overriding procedure Fetch (Result : out Direct_Cursor; Connection : access Database_Connection_Record'Class; Query : String; Params : SQL_Parameters := No_Parameters); overriding procedure Fetch (Result : out Direct_Cursor; Connection : access Database_Connection_Record'Class; Query : GNATCOLL.SQL.SQL_Query; Params : SQL_Parameters := No_Parameters); -- Execute the query, and get all results in memory. -------------------------------------------- -- Getting info about the database schema -- -------------------------------------------- -- The following subprograms will provide a view of the database schema -- (i.e. the set of tables and their fields, and the relationships between -- the tables). type Relation_Kind is (Kind_Table, Kind_View); procedure Foreach_Table (Connection : access Database_Connection_Record; Callback : access procedure (Name, Description : String; Kind : Relation_Kind)) is abstract; -- Find all tables in the database. -- For each, call Callback. Description is the comment that was optionally -- stored in the database to describe the role of the table (generally -- through a COMMENT command, which depends on the type of database you are -- using). procedure Foreach_Field (Connection : access Database_Connection_Record; Table_Name : String; Callback : access procedure (Name : String; Typ : String; Index : Natural; Description : String; Default_Value : String; Is_Primary_Key : Boolean; Not_Null : Boolean)) is abstract; -- For each attribute of the table, call Callback. Index is the attribute -- index in the table (column number). Description is the comment that was -- set when the attribute was created (for DBMS systems that support it), -- and can be the empty string. -- Default_Value is the default value for the attribute (the empty string -- is used if there is no default) -- Is_Primary_Key is set to True if the field is part of the primary key -- for this table. -- Not_Null is set to true if the attribute cannot be null procedure Foreach_Foreign_Key (Connection : access Database_Connection_Record; Table_Name : String; Callback : access procedure (Index : Positive; Local_Attribute : Integer; Foreign_Table : String; Foreign_Attribute : Integer)) is abstract; -- For each foreign key in Table_Name: calls the Callback for each -- attribute part of that key. For instance, if the key is a tuple of -- attributes pointing into a foreign table, the callback will be called -- twice, once for each attribute in the tuple. The index will be the same -- in the two calls to help identify foreign keys that are made of multiple -- attributes ------------------------- -- Errors and Warnings -- ------------------------- -- This subprograms are for internal implementation only procedure Print_Warning (Connection : access Database_Connection_Record'Class; Str : String); procedure Print_Error (Connection : access Database_Connection_Record'Class; Str : String); procedure Report_Database_Corrupted (Connection : access Database_Connection_Record'Class); -- Print a warning or message to the appropriate GNATCOLL.Traces stream. ------------------------- -- Private subprograms -- ------------------------- -- These subprograms are meant to be overridden by specific implementations -- for each DBMS. You should not use them directly in your applications, -- since the subprograms above wrap them better. type DBMS_Stmt is new System.Address; No_DBMS_Stmt : constant DBMS_Stmt; -- A statement prepared on the server. This is only valid for a specific -- connection. function Connect_And_Execute (Connection : access Database_Connection_Record; Is_Select : Boolean; Direct : Boolean; Query : String := ""; Stmt : DBMS_Stmt := No_DBMS_Stmt; Params : SQL_Parameters := No_Parameters) return Abstract_Cursor_Access is abstract; -- This is mostly an internal subprogram, overridden by all DBMS-specific -- backends. -- If the connection to the database has not been made yet, connect to it. -- Then perform the query or prepared statement, reconnecting once if the -- connection failed. (If Stmt is set, Query is ignored). -- Will return null if the connection to the database is bad. -- If the query is the empty string, this procedure only connects to -- the database and checks the connection. It returns null if the -- connection is no longer valid. -- If Direct is true, a direct_cursor is created, otherwise a -- Forward_Cursor. The connection is allowed to return a direct cursor even -- if the user only wanted a forward_cursor, but the opposite is not -- allowed. function Connect_And_Prepare (Connection : access Database_Connection_Record; Query : String; Name : String; Direct : Boolean) return DBMS_Stmt; -- Prepare a statement on the server, and return a handle to it. This is -- only valid for the specific Connection. This function can return null -- if prepared statements are not supported on that DBMS. -- Connection to the database is first done if needed function Execute (Connection : access Database_Connection_Record; Prepared : DBMS_Stmt; Is_Select : Boolean; Direct : Boolean; Params : SQL_Parameters := No_Parameters) return Abstract_Cursor_Access; -- Execute a prepared statement on the server procedure Finalize (Connection : access Database_Connection_Record; Prepared : DBMS_Stmt) is null; -- Free memory used by Prepared on the server procedure Reset (Connection : access Database_Connection_Record; Prepared : DBMS_Stmt) is null; -- Reset the prepared statement so that the next call to Element returns -- the first row procedure Post_Execute_And_Log (R : access Abstract_DBMS_Forward_Cursor'Class; Connection : access Database_Connection_Record'Class; Query : String; Prepared : Prepared_Statement'Class := No_Prepared; Is_Select : Boolean; Params : SQL_Parameters := No_Parameters); -- Mark the connection as success or failure depending on R. -- Logs the query function Is_Prepared_On_Server_Supported (Connection : access Database_Connection_Record) return Boolean; -- True if Prepared supported on the server for this connection type Database_Engine is abstract tagged limited record Plugin : Plugins.Plugin := Plugins.No_Plugin; end record; function Setup (Engine : Database_Engine; Options : Name_Values.Map; Errors : access Error_Reporter'Class) return Database_Description is abstract; type Database_Engine_Access is access all Database_Engine'Class; package Database_Engines is new Ada.Containers.Indefinite_Hashed_Maps (Key_Type => String, Element_Type => Database_Engine_Access, Hash => Ada.Strings.Hash_Case_Insensitive, Equivalent_Keys => Ada.Strings.Equal_Case_Insensitive); private type Error_Reporter is abstract tagged null record; type Database_Description_Record (Caching : Boolean; Errors : access Error_Reporter'Class) is abstract tagged null record; type Database_Connection_Record (Descr : access Database_Description_Record'Class; Always_Use_Transactions : Boolean) is abstract new Formatter with record Username : GNATCOLL.Strings.XString; Error_Msg : GNATCOLL.Strings.XString; Automatic_Transactions : Boolean := True; Success : Boolean := True; In_Transaction : Boolean := False; end record; type Transaction_Controller (DB : Database_Connection) is new Ada.Finalization.Controlled with record Started : Boolean := False; end record; overriding procedure Initialize (Self : in out Transaction_Controller); overriding procedure Finalize (Self : in out Transaction_Controller); type Abstract_DBMS_Forward_Cursor is abstract tagged record Refcount : Natural := 1; end record; type Forward_Cursor is new Ada.Finalization.Controlled with record Res : Abstract_Cursor_Access; end record; overriding procedure Adjust (Self : in out Forward_Cursor); overriding procedure Finalize (Self : in out Forward_Cursor); type Direct_Cursor is new Forward_Cursor with null record; -- The contents is of type Abstract_DBMS_Direct_Cursor, defined in -- GNATCOLL.SQL.Exec_Private, and implemented by each backend. All -- primitive ops forward to this contents No_Element : constant Forward_Cursor := (Ada.Finalization.Controlled with null); No_Direct_Element : constant Direct_Cursor := (Ada.Finalization.Controlled with null); Null_Parameter : constant SQL_Parameter := (Parameters.Null_Ref with null record); No_Parameters : constant SQL_Parameters (1 .. 0) := (others => Null_Parameter); ------------------------- -- Prepared statements -- ------------------------- No_DBMS_Stmt : constant DBMS_Stmt := DBMS_Stmt (System.Null_Address); -- A statement prepared on the server. This is only valid for a specific -- connection. type Prepared_In_Session; type Prepared_In_Session_List is access all Prepared_In_Session; type Prepared_In_Session is record Stmt : DBMS_Stmt := No_DBMS_Stmt; DB : Database_Connection; -- The connection used to prepare DB_Timestamp : Ada.Calendar.Time; -- The DB.Connected_On when the statement was prepared. Used to detect -- whether we need to re-prepare it. Next : Prepared_In_Session_List; end record; type Cache_Id is new Natural; No_Cache_Id : constant Cache_Id := Cache_Id'Last; type Prepared_Statement_Data is record Query : SQL_Query; -- Reset to null once prepared Query_Str : GNAT.Strings.String_Access; Name : GNATCOLL.Strings.XString; Prepared : Prepared_In_Session_List; Cached_Result : Cache_Id := No_Cache_Id; Index_By : Field_Index'Base; Is_Select : Boolean; Use_Cache : Boolean := False; On_Server : Boolean := False; end record; -- This type stores a statement as a string, to save time and memory. -- It is reference counted, so that it is automatically released when no -- longer needed. procedure Free (Self : in out Prepared_Statement_Data); package Prepared_Statements is new GNATCOLL.Refcount.Shared_Pointers (Prepared_Statement_Data, Free); type Prepared_Statement is new Prepared_Statements.Ref with null record; No_Prepared : constant Prepared_Statement := (Prepared_Statements.Null_Ref with null record); end GNATCOLL.SQL.Exec;