= Connecting to a database

All Sequel activity begins with connecting to a database, which creates a
Sequel::Database object. The Database object is used to create datasets and execute
queries. Sequel provides a powerful and flexible mechanism for connecting to
databases.  There are two main ways to establish database connections:

1. Using the Sequel.connect method
2. Using the specialized adapter method (Sequel.sqlite, Sequel.postgres, etc.)

The connection options needed depend on the adapter being used, though most adapters
share the same basic connection options.

If you are only connecting to a single database, it is recommended that you store the
database object in a constant named DB.  This is not required, but it is the
convention that most Sequel code uses.

== Using the Sequel.connect method

The connect method usually takes a well-formed URI, which is parsed into connection options needed to open
the database connection.  The scheme/protocol part of the URI is used to determine the adapter to use:

  DB = Sequel.connect('postgres://user:password@localhost/blog') # Uses the postgres adapter

You can use URI query parameters to specify options:

  DB = Sequel.connect('postgres://localhost/blog?user=user&password=password')

You can also pass an additional option hash with the connection string:

  DB = Sequel.connect('postgres://localhost/blog', :user=>'user', :password=>'password')

You can also just use an options hash without a connection string.  If you do this, you must
provide the adapter to use:

  DB = Sequel.connect(:adapter=>'postgres', :host=>'localhost', :database=>'blog', :user=>'user', :password=>'password')

All of the above statements are equivalent.

== Using the specialized adapter method

The specialized adapter method is similar to Sequel.connect with an options hash, except that it
automatically populates the :adapter option and assumes the first argument is the :database option,
unless the first argument is a hash. So the following statements are equivalent to the previous statements.

  DB = Sequel.postgres('blog', :host=>'localhost', :user=>'user', :password=>'password')
  DB = Sequel.postgres(:host=>'localhost', :user=>'user', :password=>'password', :database=>'blog')

Note that using an adapter method forces the use of the specified adapter, not a database type, even
though some adapters have the same name as the database type.  So if you
want to connect to SQLite, for example, you can do so using the sqlite, do, jdbc, and swift adapters.
If you want to connect to SQLite on JRuby using the jdbc adapter, you should not use <tt>Sequel.sqlite</tt>
for example, as that uses the C-based sqlite3 gem. Instead, the <tt>Sequel.jdbc</tt> would be appropriate (though
as mentioned below, using <tt>Sequel.connect</tt> is recommended instead of <tt>Sequel.jdbc</tt>). 

== Passing a block to either method

Both the Sequel.connect method and the specialized adapter methods take a block.  If you
provide a block to the method, Sequel will create a Database object and pass it as an argument
to the block.  When the block returns, Sequel will disconnect the database connection.
For example:

  Sequel.connect('sqlite://blog.db'){|db| puts db[:users].count}  

== General connection options

These options are shared by all adapters unless otherwise noted.

:adapter :: The adapter to use
:database :: The name of the database to which to connect
:host :: The hostname of the database server to which to connect
:loggers :: An array of SQL loggers to log to
:password :: The password for the user account
:servers :: A hash with symbol keys and hash or proc values, used with master/slave/partitioned database configurations
:single_threaded :: Whether to use a single-threaded (non-thread safe) connection pool
:test :: Whether to test that a valid database connection can be made (false by default)
:user :: The user account name to use logging in

The following options can be specified and are passed to the database's internal connection pool.

:after_connect :: A callable object called after each new connection is made, with the
                  connection object (and server argument if the callable accepts 2 arguments),
                  useful for customizations that you want to apply to all connections (default: nil).
:max_connections :: The maximum size of the connection pool (default: 4 connections on most databases)
:pool_sleep_time :: The number of seconds to sleep before trying to acquire a connection again (default: 0.001 seconds)
:pool_timeout :: The number of seconds to wait if a connection cannot be acquired before raising an error (default: 5 seconds)

== Adapter specific connection options

The following sections explain the options and behavior specific to each adapter.
If the library the adapter requires is different from the name of the adapter
scheme, it is listed specifically, otherwise you can assume that is requires the
library with the same name.

=== ado

Requires: win32ole 

The ADO adapter provides connectivity to ADO databases in Windows. It relies
on WIN32OLE library, so it isn't usable on other operating systems (except
possibly through WINE, but that's unlikely).

The following options are supported:

:command_timeout :: Sets the time in seconds to wait while attempting
                    to execute a command before cancelling the attempt and generating
                    an error. Specifically, it sets the ADO CommandTimeout property.
                    If this property is not set, the default of 30 seconds is used.
:driver :: The driver to use in the ADO connection string.  If not provided, a default
           of "SQL Server" is used.
:conn_string :: The full ADO connection string.  If this is provided,
                the usual options are ignored.
:provider :: Sets the Provider of this ADO connection (for example, "SQLOLEDB").
             If you don't specify a provider, the default one used by WIN32OLE
             has major problems, such as creating a new native database connection
             for every query, which breaks things such as transactions and temporary tables.

Pay special attention to the :provider option, as without specifying a provider,
many things will be broken.  The SQLNCLI10 provider appears to work well if you
are connecting to Microsoft SQL Server, but it is not the default as that would
break backwards compatability.

Example connections:

  # SQL Server
  Sequel.connect('ado:///sequel_test?host=server%5cdb_instance')
  Sequel.connect('ado://user:password@server/database?host=server%5cdb_instance&provider=SQLNCLI10')
  # Access 2007
  Sequel.ado(:conn_string=>'Provider=Microsoft.ACE.OLEDB.12.0;Data Source=drive:\\path\\filename.accdb')
  # Access 2000
  Sequel.ado(:conn_string=>'Provider=Microsoft.Jet.OLEDB.4.0;Data Source=drive:\\path\\filename.mdb')
  # Excel 2000 (for table names, use a dollar after the sheet name, e.g. Sheet1$)
  Sequel.ado(:conn_string=>'Provider=Microsoft.Jet.OLEDB.4.0;Data Source=drive:\\path\\filename.xls;Extended Properties=Excel 8.0;')

=== amalgalite 

Amalgalite is an ruby extension that provides self contained access to SQLite,
so you don't need to install SQLite separately.  As amalgalite is a file backed
database, the :host, :user, and :password options are not used.

:database :: The name of the database file
:timeout :: The busy timeout period given in milliseconds

Without a database argument, assumes a memory database, so you can do:

  Sequel.amalgalite

Handles paths in the connection string similar to the SQLite adapter, so see
the sqlite section below for details.

=== cubrid

cubrid is a ruby extension for accessing a CUBRID database.  Currently,
the ruby cubrid gem is in fairly rough state, with broken transaction
support and some other issues, but most things work.

=== db2 

Requires: db2/db2cli

This is the older DB2 adapter.  It's recommended you try the ibmdb adapter
instead for new DB2 work, as it is better supported.

=== dbi 

Allows access to a multitude of databases via ruby-dbi.  Additional options:

:db_type :: Specifying 'mssql' allows Microsoft SQL Server specific syntax to
            be used.  Otherwise has no effect.

DBI connection strings are a preprocessed a bit, and are specified with a dbi-
in front of the protocol.  Examples:

  dbi-ado://...
  dbi-db2://...
  dbi-frontbase://...
  dbi-interbase://...
  dbi-msql://...
  dbi-mysql://...
  dbi-odbc://...
  dbi-oracle://...
  dbi-pg://...
  dbi-proxy://...
  dbi-sqlite://...
  dbi-sqlrelay://...

While the DBI adapter does work, it is recommended that you use another adapter
if your database supports it.

=== do 

Requires: data_objects

The DataObjects adapter supports PostgreSQL, MySQL, and SQLite.  One possible
advantage of using DataObjects is that it does the typecasting in C, which may
be faster than the other adapters.

Similar to the JDBC adapter, the DO adapter only cares about connection strings,
which can either be the String argument given to Sequel.connect directly or contained
in a :uri or :url option.  The DO adapter passes through the connection string
directly to DataObjects, it does no processing of it (other than removing the do: prefix).

Connection string examples:

  do:sqlite3::memory:
  do:postgres://user:password@host/database
  do:mysql://user:password@host/database

=== fdbsql

Requires: pg

The following additional options are supported:

:connect_timeout :: Set the number of seconds to wait for a connection (default 20).
:notice_receiver :: A proc that be called with the PGresult objects that have notice or warning messages.
                    The default notice receiver just prints the messages to stderr, but this can be used
                    to handle notice/warning messages differently.
:sslmode :: Set to 'disable', 'allow', 'prefer', 'require' to choose how to treat SSL.

=== firebird 

Requires: fb (using code at http://github.com/wishdev/fb)

Does not support the :port option.

=== ibmdb

requires 'ibm_db'

This connects to DB2 using IBM_DB.  This is the recommended adapter if you are
using a C-based ruby to connect to DB2.

=== informix 

Does not support the :host or :port options.  Depending on the configuration of your server
it may be necessary to either set
   
   DB.quote_identifier = false

or set

   export DELIMIDENT=y

in the scripts environment.

The following additional options are supported:

:nolog :: Disable transactions for the database.
 
=== jdbc 

Requires: java

Houses Sequel's JDBC support when running on JRuby.
Support for individual database types is done using sub adapters.
There are currently subadapters for PostgreSQL, MySQL, SQLite, H2, HSQLDB, Derby,
Oracle, MSSQL, JTDS, AS400, Progress, Fdbsql, Firebird, Informix, and DB2.
For PostgreSQL, MySQL, SQLite, H2, HSQLDB, Derby, and JTDS,
this can use the jdbc-* gem, for the others you need to have the .jar in your CLASSPATH
or load the Java class manually before calling Sequel.connect.

You just use the JDBC connection string directly, which can be specified
via the string given to Sequel.connect or via the :uri, :url, or :database options.
Sequel does no preprocessing of the string, it passes it directly to JDBC.
So if you have problems getting a connection string to work, look up the JDBC
documentation.

Note that when using a JDBC adapter, the best way to use Sequel
is via Sequel.connect, NOT Sequel.jdbc.  Use the JDBC connection
string when connecting, which will be in a different format than
the native connection string.  The connection string should start
with 'jdbc:'.  For PostgreSQL, use 'jdbc:postgresql:', and for
SQLite you do not need 2 preceding slashes for the database name
(use no preceding slashes for a relative path, and one preceding
slash for an absolute path).

Example connection strings:

  jdbc:sqlite::memory:
  jdbc:postgresql://localhost/database?user=username
  jdbc:mysql://localhost/test?user=root&password=root
  jdbc:h2:mem:
  jdbc:hsqldb:mem:mymemdb
  jdbc:derby:memory:myDb;create=true
  jdbc:sqlserver://localhost;database=sequel_test;integratedSecurity=true
  jdbc:jtds:sqlserver://localhost/sequel_test;user=sequel_test;password=sequel_test
  jdbc:oracle:thin:user/password@localhost:1521:database
  jdbc:db2://localhost:3700/database:user=user;password=password;
  jdbc:firebirdsql:localhost/3050:/path/to/database.fdb
  jdbc:jdbcprogress:T:hostname:port:database
  jdbc:cubrid:hostname:port:database:::
  jdbc:sqlanywhere://localhost?DBN=Test;UID=user;PWD=password
  jdbc:fdbsql://localhost:15432/user?user=user&password=password

You can also use JNDI connection strings:

  jdbc:jndi:java:comp/env/jndi_resource_name

The following additional options are supported:

:convert_types :: If set to false, does not attempt to convert some Java types to ruby types.
                  Setting to false roughly doubles performance when selecting large numbers of rows.
                  Note that you can't provide this option inside the connection string (as that is passed
                  directly to JDBC), you have to pass it as a separate option.
:driver :: Specify the Java driver class to use to connect to the database.  This only has
           an effect if the database type is not recognized from the connection string,
           and only helps cases where <tt>java.sql.DriverManager.getConnection</tt> does not
           return a connection.
:login_timeout :: Set the login timeout on the JDBC connection (in seconds).

=== mysql 

Requires: mysqlplus (or mysql if mysqlplus is not available)

The MySQL adapter does not support the pure-ruby MySQL adapter that used to ship with
ActiveRecord, it requires the native adapter.

The following additional options are supported:

:auto_is_null :: If set to true, makes "WHERE primary_key IS NULL" select the last inserted id.
:charset :: Same as :encoding, :encoding takes precedence.
:compress :: Whether to compress data sent/received via the socket connection.
:config_default_group :: The default group to read from the in the MySQL config file.
:config_local_infile :: If provided, sets the Mysql::OPT_LOCAL_INFILE option on the connection with the given value.
:encoding :: Specify the encoding/character set to use for the connection.
:fractional_seconds :: On MySQL 5.6.5+, this option is recognized and will include fractional seconds in
                       time/timestamp values, as well as have the schema method create columns that can contain
                       fractional seconds by deafult.  This option is also supported on other adapters that connect
                       to MySQL.
:socket :: Can be used to specify a Unix socket file to connect to instead of a TCP host and port.
:sql_mode :: Set the sql_mode(s) for a given connection.  Can be single symbol or string,
             or an array of symbols or strings (e.g. <tt>:sql_mode=>[:no_zero_date, :pipes_as_concat]</tt>).
:timeout :: Sets the wait_timeout for the connection, defaults to 1 month.
:read_timeout :: Set the timeout in seconds for reading back results to a query.
:connect_timeout :: Set the timeout in seconds before a connection attempt is abandoned.

=== mysql2

This is a newer MySQL adapter that does typecasting in C, so it is often faster than the
mysql adapter.  Supports the same additional options as the mysql adapter, except for :compress, and uses
:timeout instead of :read_timeout and :connect_timeout.

The following additional options are supported:

:flags :: Override the flags to use for the connection (e.g. ::Mysql2::Client::MULTI_STATEMENTS)

=== odbc 

The ODBC adapter allows you to connect to any database with the appropriate ODBC drivers installed.  
The :database option given ODBC database should be the DSN (Descriptive Service Name) from the ODBC configuration.

  Sequel.odbc('mydb', :user => "user", :password => "password")

The :host and :port options are not respected. The following additional options are supported:

:db_type :: Can be specified as 'mssql', 'progress', or 'db2' to use SQL syntax specific to those databases.
:drvconnect :: Can be given an ODBC connection string, and will use ODBC::Database#drvconnect to
               do the connection.  Typical usage would be: <tt>Sequel.odbc(:drvconnect=>'driver={...};...')</tt>

=== openbase 

The :port option is ignored.

=== oracle 

Requires: oci8

The following additional options are supported:

:autosequence :: Set to true to use Sequel's conventions to guess the sequence to use for datasets.  False
                 by default.
:prefetch_rows :: The number of rows to prefetch.  Defaults to 100, a larger number can be specified
                  and may improve performance when retrieving a large number of rows.
:privilege :: The Oracle privilege level.

=== postgres

Requires: pg (or postgres if pg is not available)

The Sequel postgres adapter works with the pg, postgres, and postgres-pr ruby libraries.
The pg library is the best supported, as it supports real bound variables and prepared statements.
If the pg library is being used, Sequel will also attempt to load the sequel_pg library, which is
a C extension that optimizes performance when Sequel is used with pg.  All users of Sequel who
use pg are encouraged to install sequel_pg.

The following additional options are supported:

:charset :: Same as :encoding, :encoding takes precedence
:convert_infinite_timestamps :: Whether infinite timestamps/dates should be converted on retrieval.  By default, no
                                conversion is done, so an error is raised if you attempt to retrieve an infinite
                                timestamp/date.  You can set this to :nil to convert to nil, :string to leave
                                as a string, or :float to convert to an infinite float.
:connect_timeout :: Set the number of seconds to wait for a connection (default 20, only respected
                    if using the pg library).
:encoding :: Set the client_encoding to the given string
:notice_receiver :: A proc that be called with the PGresult objects that have notice or warning messages.
                    The default notice receiver just prints the messages to stderr, but this can be used
                    to handle notice/warning messages differently.  Only respected if using the pg library).
:sslmode :: Set to 'disable', 'allow', 'prefer', 'require' to choose how to treat SSL (only
            respected if using the pg library)
:search_path :: Set to the schema search_path.  This can either be a single string containing the schemas
                separated by commas (for use via a URL: <tt>postgres:///?search_path=schema1,schema2</tt>), or it
                can be an array of strings (for use via an option:
                <tt>Sequel.postgres(:search_path=>['schema1', 'schema2'])</tt>).
:use_iso_date_format :: This can be set to false to not force the ISO date format.  Sequel forces
                        it by default to allow for an optimization.

=== sqlanywhere

The sqlanywhere driver works off connection strings, so a connection string
is built based on the url/options hash provided.  The following additional
options are respected:

:commlinks :: specify the CommLinks connection string option
:conn_string :: specify the connection string to use, ignoring all other options
:connection_name :: specify the ConnectionName connection string option
:encoding :: specify the CharSet connection string option

=== sqlite

Requires: sqlite3

As SQLite is a file-based database, the :host and :port options are ignored, and
the :database option should be a path to the file.

Examples:

  # In Memory databases:
  Sequel.sqlite
  Sequel.connect('sqlite:/') 
  Sequel.sqlite(':memory:')

  # Relative Path
  Sequel.sqlite('blog.db')
  Sequel.sqlite('./blog.db')
  Sequel.connect('sqlite://blog.db')

  # Absolute Path
  Sequel.sqlite('/var/sqlite/blog.db')
  Sequel.connect('sqlite:///var/sqlite/blog.db') 

The following additional options are supported:

:readonly :: open database in read-only mode
:timeout :: the busy timeout to use in milliseconds (default: 5000).

=== swift

swift is a ruby 1.9+ library, so you'll need to be running ruby 1.9+.  It
can connect to SQLite, MySQL, and PostgreSQL, and you must specify which
database using the db_type option.

You need to install one of the swift db adapters

* swift-db-sqlite3
* swift-db-mysql
* swift-db-postgres

Examples:

  swift:///?database=:memory:&db_type=sqlite
  swift://root:root@localhost/test?db_type=mysql
  swift://root:root@localhost/test?db_type=postgres

=== tinytds

Requires: tiny_tds

The connection options are passed directly
to tiny_tds, except that the tiny_tds :username option is set to
the Sequel :user option.  If you want to use an entry in the freetds.conf file, you
should specify the :dataserver option with that name as the value.  Some other
options that you may want to set are :login_timeout, :timeout, :tds_version, :azure,
:appname, and :encoding, see the tiny_tds README for details.

Other Sequel specific options:

:server_version :: Override the server version to use (9000000 = SQL Server 2005).
                   This also works on any other adapter that connects to Microsoft
                   SQL Server.
:textsize :: Override the default TEXTSIZE setting for this connection.  The FreeTDS
             default is small (around 64000 bytes), but can be set up to around 2GB.
             This should be specified as an integer.  If you plan on setting large
             text or blob values via tinytds, you should use this option or modify
             your freetds.conf file.

The Sequel tinytds adapter requires tiny_tds >= 0.4.5, and if you are using FreeTDS
0.91, you must at least be using 0.91rc2 (0.91rc1 does not work).