###############
Getting Started
###############

Installation
************

Firebird-driver is written as pure-Python module (requires Python 3.8+) on top of
Firebird client library (fbclient.so/dll) using ctypes_. Driver supports Firebird version
3.0 and higher.

Firebird-driver is distributed as `setuptools`_ package and the preferred installation
method is via pip_ tool.

Installation from PYPI_
=======================

Run pip::

  $ pip install firebird-driver

Quick-start Guide
*****************

This brief tutorial aims to get the reader started by demonstrating elementary usage of
Firebird-driver. It is not a comprehensive Python Database API tutorial, nor is it
comprehensive in its coverage of anything else.

The numerous advanced features of Firebird-driver are covered in another section of this
documentation, which is not in a tutorial format, though it is replete with examples.

Driver configuration
====================

The driver uses configuration built on top of `configuration system <firebird.base.config>`
provided by `firebird-base`_ package. In addition to global settings, the configuration
also includes the definition of connection parameters to Firebird servers and databases.

The default configuration connects to embedded server using direct/local connection method.
To access remote servers and databases (or local ones through remote protocols), it's
necessary to adjust default configuration, or `register` them in configuration manager.

You can manipulate the configuration objects directly, or load configuration from files or
strings (in `.ini-style` `ConfigParser` format).

Connecting to a Database
========================

**Example 1:**

A simple database connection is typically established with code such as this:

.. sourcecode:: python

    from firebird.driver import connect

    # Attach to 'employee' database/alias using embedded server connection
    con = connect('employee', user='sysdba', password='masterkey')

    # Attach to 'employee' database/alias using local server connection
    from firebird.driver import driver_config
    driver_config.server_defaults.host.value = 'localhost'
    con = connect('employee', user='sysdba', password='masterkey')

    # Set 'user' and 'password' via configuration
    driver_config.server_defaults.user.value = 'SYSDBA'
    driver_config.server_defaults.password.value = 'masterkey'
    con = connect('employee')

**Example 2:**

A database connection typically uses specific configuration, and is established with code
such as this:

.. sourcecode:: python

    from firebird.driver import connect, driver_config

    # Register Firebird server
    srv_cfg = """[local]
    host = localhost
    user = SYSDBA
    password = masterkey
    """
    driver_config.register_server('local', srv_cfg)

    # Register database
    db_cfg = """[employee]
    server = local
    database = employee.fdb
    protocol = inet
    charset = utf8
    """
    driver_config.register_database('employee', db_cfg)

    # Attach to 'employee' database
    con = connect('employee')

.. note::

   Some parameters like 'user' and 'password' could be overridden with keyword parameters.
   Few parameters like 'crypt_callback' or 'no_db_triggers' could be specified **ONLY**
   as keyword arguments.

Creating a Database
===================

A database is created using `~firebird.driver.core.create_database()` function.
Like `~firebird.driver.core.connect()`, this function uses configuration for specification of
database parameters like page size, sweep interval etc.

Executing SQL Statements
========================

For this section, suppose we have a table defined and populated by the following SQL code:

.. sourcecode:: sql

    create table languages
    (
      name               varchar(20),
      year_released      integer
    );

    insert into languages (name, year_released) values ('C',        1972);
    insert into languages (name, year_released) values ('Python',   1991);

**Example 1**

This example shows the *simplest* way to print the entire contents of
the `languages` table:

.. sourcecode:: python

    from firebird.driver import connect

    con = connect('test.fdb', user='sysdba', password='masterkey')

    # Create a Cursor object that operates in the context of Connection con:
    cur = con.cursor()

    # Execute the SELECT statement:
    cur.execute("select * from languages order by year_released")

    # Retrieve all rows as a sequence and print that sequence:
    print(cur.fetchall())

Sample output:

.. sourcecode:: python

    [('C', 1972), ('Python', 1991)]

**Example 2**

Here's another trivial example that demonstrates various ways of fetching a single row at a time from a `SELECT`-cursor:

.. sourcecode:: python

    from firebird.driver import connect

    con = connect('test.fdb', user='sysdba', password='masterkey')

    cur = con.cursor()
    SELECT = "select name, year_released from languages order by year_released"

    # 1. Iterate over the rows available from the cursor, unpacking the
    # resulting sequences to yield their elements (name, year_released):
    cur.execute(SELECT)
    for (name, year_released) in cur:
        print(f'{name} has been publicly available since {year_released}.')

    # 2. Equivalently:
    cur.execute(SELECT)
    for row in cur:
        print(f'{row[0]} has been publicly available since {row[1]}.')

Sample output:

.. sourcecode:: python

    C has been publicly available since 1972.
    Python has been publicly available since 1991.
    C has been publicly available since 1972.
    Python has been publicly available since 1991.
    C has been publicly available since 1972.
    Python has been publicly available since 1991.

**Example 3**

The following program is a simplistic table printer (applied in this example to `languages`):

.. sourcecode:: python

    from firebird.driver import connect, DESCRIPTION_NAME, DESCRIPTION_DISPLAY_SIZE

    TABLE_NAME = 'languages'
    SELECT = f'select * from {TABLE_NAME} order by year_released'

    con = connect('test.fdb', user='sysdba', password='masterkey')

    cur = con.cursor()
    cur.execute(SELECT)

    # Print a header.
    for fieldDesc in cur.description:
        print(fieldDesc[DESCRIPTION_NAME].ljust(fieldDesc[DESCRIPTION_DISPLAY_SIZE]), end='')
    print() # Finish the header with a newline.
    print('-' * 78)

    # For each row, print the value of each field left-justified within
    # the maximum possible width of that field.
    fieldIndices = range(len(cur.description))
    for row in cur:
        for fieldIndex in fieldIndices:
            fieldValue = str(row[fieldIndex])
            fieldMaxWidth = cur.description[fieldIndex][DESCRIPTION_DISPLAY_SIZE]

            print(fieldValue.ljust(fieldMaxWidth), end='')

        print() # Finish the row with a newline.


Sample output:

.. sourcecode:: python

    NAME                 YEAR_RELEASED
    ------------------------------------------------------------------------------
    C                    1972
    Python               1991


**Example 4**

Let's insert more languages:

.. sourcecode:: python

    from firebird.driver import connect

    con = connect('test.fdb', user='sysdba', password='masterkey')

    cur = con.cursor()

    newLanguages = [
        ('Lisp',  1958),
        ('Dylan', 1995),
      ]

    cur.executemany("insert into languages (name, year_released) values (?, ?)",
        newLanguages
      )

    # The changes will not be saved unless the transaction is committed explicitly:
    con.commit()


Note the use of a *parameterized* SQL statement above. When dealing with repetitive
statements, this is much faster and less error-prone than assembling each SQL statement
manually. (You can read more about parameterized SQL statements in the section on
:ref:`Prepared Statements <prepared-statements>`.)

After running Example 4, the table printer from Example 3 would print:

.. sourcecode:: python

    NAME                 YEAR_RELEASED
    ------------------------------------------------------------------------------
    Lisp                 1958
    C                    1972
    Python               1991
    Dylan                1995


Calling Stored Procedures
=========================

Firebird supports stored procedures written in a proprietary procedural SQL language.
Firebird stored procedures can have *input* parameters and/or *output* parameters. Some
databases support *input/output* parameters, where the same parameter is used for both
input and output; Firebird does not support this.

It is important to distinguish between procedures that *return a result set* and procedures
that *populate and return their output parameters exactly once*. Conceptually, the latter
"return their output parameters" like a Python function, whereas the former "yield result
rows" like a Python generator.

Firebird's *server-side* procedural SQL syntax makes no such distinction, but *client-side*
SQL code (and C API code) must. A result set is retrieved from a stored procedure by
`SELECT`-ing from the procedure, whereas output parameters are retrieved with an
`EXECUTE PROCEDURE` statement.

To *retrieve a result set* from a stored procedure with Firebird-driver, use code such as this:

.. sourcecode:: python

    cur.execute("select output1, output2 from the_proc(?, ?)", (input1, input2))

    # Ordinary fetch code here, such as:
    for row in cur:
        ... # process row

    con.commit() # If the procedure had any side effects, commit them.


To *execute* a stored procedure and *access its output parameters*, use code such as this:

.. sourcecode:: python

    cur.callproc("the_proc", (input1, input2))

    # If there are output parameters, retrieve them as though they were the
    # first row of a result set.  For example:
    outputParams = cur.fetchone()

    con.commit() # If the procedure had any side effects, commit them.


This latter is not very elegant; it would be preferable to access the procedure's output
parameters as the return value of `Cursor.callproc()`. The Python DB API specification
requires the current behavior, however.

.. _setuptools: https://pypi.org/project/setuptools/
.. _PYPI: https://pypi.org/
.. _ctypes: http://docs.python.org/library/ctypes.html
.. _pip: https://pypi.org/project/pip/
.. _firebird-base: https://firebird-base.rtfd.io