<< Back

NAME

 YaBB3::DataSource

SYNOPSIS

    # create object
    my $ds = YaBB3::DataSource->new(type => "some_type");
    # SELECT
    my $sth = $ds->do_query("SELECT * FROM somewhere");
    while (defined(my $row = $sth->fetch)) {
        print "@$row\n";
    }
    # INSERT
    my $sth = $ds->prepare("INSERT INTO somewhere ( ?, ?, ?)");
    $ds->execute($val1, $val2, $val3);

DESCRIPTION

YaBB3::DataSource is like our own special version of DBI. It loads the drivers for request DataSources and then gets out of the way. The standard driver API is defined in this document, and all drivers for YaBB3 must implement the functions listed in the API DEFINITION section of this document.

YaBB3 code will call these functions to perform data operations. Where SQL statements are specified as the argument, they will adhere to a standard subset of SQL, which can be found in the VALID SQL section of this document.

FUNCTIONS

new( type => ``SourceType'' );

Creates a DataSource object which supports the data source type requested. Currently, the only argument is type. It defaults to ``File'' if left blank. The DataSource types that are planned to ship with YaBB are:

MySQL -- Recommended wherever possible.
File -- For hosts who do not provide database connectivity.

VALID SQL

This section describes the SQL queries that must be supported by all DataSource drivers. Several portions of it were borrowed from the the SQL::Statement::Syntax manpage document so that I wouldn't have to retype it all.

The basic overview of support is:

   CREATE [TEMP] TABLE <table> <column_def_clause>
   DELETE FROM <table> [<where_clause>]
   DROP TABLE [IF EXISTS] <table>

   INSERT [INTO] <table> [<column_list>] VALUES <value_list>
   SELECT <select_clause>
          <from_clause>
          [<where_clause>] 
          [ ORDER BY ocol1 [ASC|DESC], ... ocolN [ASC|DESC]] ]
          [ GROUP BY gcol1 [, ... gcolN] ]
          [ LIMIT [start,] length ]
   UPDATE <table> SET <set_clause> [<where_clause>]

Comments should not be used in SQL, just comment in the Perl code.

Further detail on supported SQL can be found below.

Joins

The following JOIN types should be supported

SQL Functions

The following SQL functions should be supported

   * Aggregate : MIN, MAX, AVG, SUM, COUNT
   * Date/Time : CURRENT_DATE, CURRENT_TIME, CURRENT_TIMESTAMP
   * String    : CHAR_LENGTH, CONCAT, COALESCE, DECODE, LOWER, POSITION,
                 REGEX, REPLACE, SOUNDEX, SUBSTRING, TRIM, UPPER

Supported Operators

       $op  = |  <> |  < | > | <= | >=
              | IS NULL | IS NOT NULL | LIKE | CLIKE | BETWEEN | IN
        CLIKE is a case-insensitive LIKE. LIKE should support standard
        wildcards, such as %

Concatenation

    Use either ANSI SQL || or the CONCAT() function to concatenate data.

Identifiers and Aliases


   * regular identifiers are case insensitive (though see note on table names)
   * delimited identifiers (inside double quotes) are case sensitive
   * column and table aliases are supported

CREATE

 CREATE TABLE $table
        (
           $col_1 $col_type1 $col_constraints1,
           ...,
           $col_N $col_typeN $col_constraintsN,
        )
        [ ON COMMIT {DELETE|PRESERVE} ROWS ]

col_constriaints may be ``PRIMARY KEY'' or one or both of ``UNIQUE'' and/or ``NOT NULL''

col_type is checked for syntax, but is not neccessarily enforced. col_type must be one of the following types:

CHAR( n )
The CHAR structure is a fixed length string of length n. These are generally limited to a maximum of 255 characters.

VARCHAR( n )
The VARCHAR structure is a variable length string, with a set maximum of n. These are generally limited to a mazimum of 255 characters.

TEXT
While not specifically an ANSI type, we must support storage of blocks larger than 255 characters. TEXT values can store character strings of any length.

BLOB
In addition to large text values, we may occasionally need to store large binary values. BLOB values can store byte strings of any length.

INTEGER
INTEGER values may be any positive or negative whole number.

DECIMAL( p, s )
DECIMAL is a fixed point number.

The total number of digits of the number is limited to p, the precision. The number will have a set number of digits to the right of the decimal point s, the scale. For example: DECIMAL( 5, 3 ) would allow five total digits, with three to the right of the decimal. 50.3333 would be stored as 50.333 and 33.3456 would be stored as 33.346.

FLOAT( p )
A floating point value. A precision from 1 to 21 will result in a single precision float. A precision from 22-53 will result in a double precision float. Do not attempt to specify a precision beyond 53.

FLOAT
If precision is undefined, the float will be treated as double precision.

NUMERIC(p, s)
A number with precision p and scale s.

NUMERIC( p )
A NUMERIC with precision p with scale set to 0.

NUMERIC
The NUMERIC will only be limited by the specific implementation

DROP

 DROP TABLE $table

INSERT

 INSERT INTO $table [ ( $col1, ..., $colN ) ] VALUES ( $val1, ... $valN )

     * default values are not supported
     * inserting from a subquery is not supported

DELETE

 DELETE FROM $table [ WHERE search_condition ]
     * see "search_condition" below

UPDATE

 UPDATE $table SET $col1 = $val1, ... $colN = $valN [ WHERE search_condition ]
     * default values are not supported
     * see "search_condition" below

SELECT

      SELECT select_clause
        FROM from_clause
     [ WHERE search_condition ]
  [ ORDER BY $ocol1 [ASC|DESC], ... $ocolN [ASC|DESC] ]
     [ LIMIT [start,] length ]
      * select clause ::=
             [DISTINCT|ALL] *
           | [DISTINCT|ALL] col1 [,col2, ... colN]
           | set_function1 [,set_function2, ... set_functionN]
      * set function ::=
             COUNT ( [DISTINCT|ALL] * )
           | COUNT | MIN | MAX | AVG | SUM ( [DISTINCT|ALL] col_name )
      * from clause ::=
             table1 [, table2, ... tableN]
           | table1 NATURAL [join_type] JOIN table2
           | table1 [join_type] table2 USING (col1,col2, ... colN)
           | table1 [join_type] JOIN table2 ON table1.colA = table2.colB
      * join type ::=
             INNER
           | [OUTER] LEFT | RIGHT | FULL
      * search condition ::=
             [NOT] $val1 $op1 $val1 [ ... AND|OR $valN $opN $valN ]
      * $op ::=
                = |  <> |  < | > | <= | >=
              | IS NULL | IS NOT NULL | LIKE | CLIKE | BETWEEN | IN
      * if join_type is not specified, INNER is the default
      * if DISTINCT or ALL is not specified, ALL is the default
      * if start position is omitted from LIMIT clause, position 0 is
        the default
      * ON clauses may only contain equal comparisons and AND combiners
      * self-joins are not currently supported
      * if implicit joins are used, the WHERE clause must contain
        and equijoin condition for each table

API DEFINITION

This section describes the functions that must be implemented in each YaBB3::DataSource::* module.

All functions are to die on an error.

See the SYNOPSIS for how this may be used.

Main DataSource Package

do_query( $statement, [$val1, $val2, ...] )

Executes a query immediatly. Is equivalent to calling:

  my $sth = $ds->prepare( $statement );
  $sth->execute( $val1, $val2, ... );

Arguments

$statement
Contains a query written in SQL.

[$val1, $val2, ...]
Contains the data values to be inserted where placeholders are found.

Return Value

Returns an object which ->fetch() can be called on to return the results of the statement executed.

prepare( $statement )

Preparse an SQL query to be executed. Useful for situations when the same query must executed multiple times with different values

Arguments

$statement
Contains a query written in SQL.

Return Value

Returns an object which ->execute()> and ->fetch() can be called to execute and retrieve query results.

Statement Handler Package

execute( $val1, $val2, ... )

Runs a query using $val, $val2, ... for the placeholder values.

Arguments

$val1, $val2, ...
Contains the values to replace the placeholders.

Return Value

Does not return a value.

fetch( )

Returns the results of the query that has been run.

Arguments

None.

Return Value

Returns an array reference of a single row of data. To get all rows, call ->fetch() until it returns an undefined value.

REFERENCES

This following links were helpful when creating this document and may contain further helpful information:

the SQL::Statement::Syntax manpage, MySQL Documentation, SQL Data Structures, SQL API Portability

LICENSE

This module is licensed under the same terms as YaBB.

AUTHOR

Matthew Siegman Copyright (c) 2000-2009 YaBB (www.yabbforum.com) - All Rights Reserved.

Copyright © 2009 Yet another bulletin Board. All rights reserved.