text:
<tool name="CollectRnaSeqMetrics" id="picard_CollectRnaSeqMetrics" version="@TOOL_VERSION@.1">
    <description> collect metrics about the alignment of RNA to various functional classes of loci in the genome</description>
    <macros>
        <import>picard_macros.xml</import>
    </macros>
    <expand macro="requirements">
        <requirement type="package" version="3.3.1">r</requirement>
        <requirement type="package" version="324">ucsc-gff3togenepred</requirement>
        <requirement type="package" version="324">ucsc-gtftogenepred</requirement>
    </expand>
    <command detect_errors="exit_code"><![CDATA[
      ## Set up input files
      @symlink_element_identifier@
      ## Reference sequences

      #set $reference_fasta_filename = "localref.fa"

      #if str( $reference_source.reference_source_selector ) == "history":
        ln -s "${reference_source.ref_file}" "${reference_fasta_filename}" &&
      #else:
        #set $reference_fasta_filename = str( $reference_source.ref_file.fields.path )
      #end if

      ## refFlat data
      ## The awk line below converts a file obtained from UCSC as specified in the tool help to refFlat format

      #if str($gene_reference_source.gene_reference_source_selector) == "gtf"
        #if $gene_reference_source.refFlat.ext != 'gff3'
            gtfToGenePred '${gene_reference_source.refFlat}' refFlat.tab.raw &&
        #else
            gff3ToGenePred '${gene_reference_source.refFlat}' refFlat.tab.raw &&
        #end if

        grep -v '^#' refFlat.tab.raw | awk '{print $12"\t"$1"\t"$2"\t"$3"\t"$4"\t"$5"\t"$6"\t"$7"\t"$8"\t"$9"\t"$10}' > refFlat.tab &&
      #else
        grep -v '^#' ${refFlat} | awk '{print $11"\t"$1"\t"$2"\t"$3"\t"$4"\t"$5"\t"$6"\t"$7"\t"$8"\t"$9"\t"$10}' > refFlat.tab &&
      #end if

      ## Start picard command

      @java_options@
      picard
      CollectRnaSeqMetrics
      REF_FLAT=refFlat.tab

      #if str( $ribosomal_intervals ) != "None":
        RIBOSOMAL_INTERVALS="${ribosomal_intervals}"
      #end if

      STRAND_SPECIFICITY="${strand_specificity}"
      MINIMUM_LENGTH="${minimum_length}"
      CHART_OUTPUT="${pdfFile}"

      #for $sequence_to_ignore in $ignore_list:
        IGNORE_SEQUENCE="${sequence_to_ignore.sequence}"
      #end for

      RRNA_FRAGMENT_PERCENTAGE="${rrna_fragment_percentage}"
      METRIC_ACCUMULATION_LEVEL="${metric_accumulation_level}"
      INPUT='$escaped_element_identifier'
      OUTPUT="${outFile}"
      REFERENCE_SEQUENCE="${reference_fasta_filename}"
      ASSUME_SORTED="${assume_sorted}"

      VALIDATION_STRINGENCY=${validation_stringency}

   ]]></command>

   <inputs>
      <param format="sam,bam" type="data" name="inputFile" label="Select SAM/BAM dataset or dataset collection" help="If empty, upload or import a SAM/BAM dataset" />
      <conditional name="reference_source">
         <param name="reference_source_selector" type="select" label="Load reference genome from">
            <option value="cached">Local cache</option>
            <option value="history">History</option>
         </param>
         <when value="cached">
            <param name="ref_file" type="select" label="Using reference genome" help="REFERENCE_SEQUENCE">
               <options from_data_table="all_fasta"></options>
               <validator type="no_options" message="A built-in reference genome is not available for the build associated with the selected input file"/>
            </param>
         </when>
         <when value="history">
            <param name="ref_file" type="data" format="fasta" label="Use the folloing dataset as the reference sequence" help="REFERENCE_SEQUENCE; You can upload a FASTA sequence to the history and use it as reference" />
         </when>
      </conditional>

      <conditional name="gene_reference_source">
         <param name="gene_reference_source_selector" type="select" label="Load gene annotation from">
            <option value="gtf">GTF/GFF3</option>
            <option value="refflat">refFlat</option>
         </param>
         <when value="gtf">
              <param name="refFlat"
                     format="gtf,gff3"
                     type="data"
                     label="Gene annotation (GTF/GFF3)"/>
         </when>
         <when value="refflat">
              <param name="refFlat"
                     format="tabular"
                     type="data"
                     label="Gene annotations in refFlat form"
                     help="See "Obtaining gene annotations in refFlat format" below for help"/>
         </when>
      </conditional>

      <param name="ribosomal_intervals" format="picard_interval_list" type="data" optional="True" label="Location of rRNA sequences in genome, in interval_list format" help="RIBOSOMAL_INTERVALS; If not specified no bases will be identified as being ribosomal. The list of intervals can be geberated from BED or Interval datasets using Galaxy BedToIntervalList tool"/>
      <param name="strand_specificity" type="select" label="What is the RNA-seq library strand specificity" help="STRAND_SPECIFICITY; For unpaired reads, use FIRST_READ_TRANSCRIPTION_STRAND if the reads are expected to be on the transcription strand.">
	 <option value="NONE" selected="True">None</option>
	 <option value="FIRST_READ_TRANSCRIPTION_STRAND">First read transcription strand</option>
	 <option value="SECOND_READ_TRANSCRIPTION_STRAND">Second read transcription strand</option>
      </param>
      <param name="minimum_length" type="integer" value="500" label="When calculating coverage based values use only use transcripts of this length or greater" help="MINIMUM_LENGTH; default=500"/>
      <repeat name="ignore_list" title="Sequences to ignore" min="0" help="You can provide multiple sequences by clicking the button below">
          <param name="sequence" type="text" label="Ignore reads matching this sequence"/>
      </repeat>
      <param name="rrna_fragment_percentage" type="float" value="0.8" label="This percentage of the length of a fragment must overlap one of the ribosomal intervals for a read or read pair to be considered rRNA." help="RRNA_FRAGMENT_PERCENTAGE; default=0.8"/>
      <param name="metric_accumulation_level" type="select" label="The level(s) at which to accumulate metrics" multiple="true" help="METRIC_ACCUMULATION_LEVEL">
	 <option value="ALL_READS" selected="True">All reads</option>
	 <option value="SAMPLE">Sample</option>
	 <option value="LIBRARY">Library</option>
	 <option value="READ_GROUP">Read group</option>
      </param>
      <param name="assume_sorted" type="boolean" label="Assume the input file is already sorted" checked="true" truevalue="true" falsevalue="false" help="ASSUME_SORTED"/>

      <expand macro="VS" />

  </inputs>
  <outputs>
      <data format="pdf" name="pdfFile" label="${tool.name} on ${on_string}: Chart PDF"/>
      <data format="tabular" name="outFile" label="${tool.name} on ${on_string}: Summary stats"/>
  </outputs>

  <tests>
    <test>
      <param name="reference_source_selector" value="history"/>
      <param name="ref_file" value="picard_CollectRnaSeqMetrics_ref.fa" ftype="fasta"/>
      <param name="inputFile" value="picard_CollectRnaSeqMetrics.bam" ftype="bam"/>
      <param name="assume_sorted" value="true" />

      <param name="gene_reference_source_selector" value="refflat" />
      <param name="refFlat" value="picard_CollectRnaSeqMetrics.refFlat" />
      <param name="metric_accumulation_level" value="ALL_READS" />
      <param name="minimum_length" value="500" />
      <param name="strand_specificity" value="NONE" />
      <param name="rrna_fragment_percentage" value="0.8" />
      <output name="outFile" file="picard_CollectRnaSeqMetrics_test1.tab" ftype="tabular" lines_diff="4"/>
    </test>

    <test>
      <param name="reference_source_selector" value="history"/>
      <param name="ref_file" value="picard_CollectRnaSeqMetrics_ref.fa" ftype="fasta"/>
      <param name="inputFile" value="picard_CollectRnaSeqMetrics.bam" ftype="bam"/>
      <param name="assume_sorted" value="true" />

      <param name="gene_reference_source_selector" value="gtf" />
      <param name="refFlat" value="picard_CollectRnaSeqMetrics.gtf" ftype="gtf" />
      <param name="metric_accumulation_level" value="ALL_READS" />
      <param name="minimum_length" value="500" />
      <param name="strand_specificity" value="NONE" />
      <param name="rrna_fragment_percentage" value="0.8" />
      <output name="outFile" file="picard_CollectRnaSeqMetrics_test2.tab" ftype="tabular" lines_diff="4"/>
    </test>

    <test>
      <param name="reference_source_selector" value="history"/>
      <param name="ref_file" value="picard_CollectRnaSeqMetrics_ref.fa" ftype="fasta"/>
      <param name="inputFile" value="picard_CollectRnaSeqMetrics.bam" ftype="bam"/>
      <param name="assume_sorted" value="true" />

      <param name="gene_reference_source_selector" value="gtf" />
      <param name="refFlat" value="picard_CollectRnaSeqMetrics.gff3" ftype="gff3" />
      <param name="metric_accumulation_level" value="ALL_READS" />
      <param name="minimum_length" value="500" />
      <param name="strand_specificity" value="NONE" />
      <param name="rrna_fragment_percentage" value="0.8" />
      <output name="outFile" file="picard_CollectRnaSeqMetrics_test3.tab" ftype="tabular" lines_diff="4"/>
    </test>
  </tests>
  <help>

.. class:: infomark

**Purpose**

Collects metrics about the alignment of RNA to various functional classes of loci in the genome: coding, intronic, UTR, intergenic, ribosomal.

@dataset_collections@

-----

.. class:: warningmark

**Obtaining gene annotations in refFlat format**

This tool requires gene annotations in refFlat_ format. These data can be obtained from UCSC table browser directly through Galaxy by following these steps:

   1. Click on **Get Data** in the upper part of left pane of Galaxy interface
   2. Click on **UCSC Main** link
   3. Set your genome and dataset of interest. It **must** be the same genome build against which you have mapped the reads contained in the BAM file you are analyzing
   4. In the **output format** field choose **selected fields from primary and related tables**
   5. Click **get output** button
   6. In the first table presented at the top of the page select (using checkboxes) first 11 fields:
      name
      chrom
      strand
      txStart
      txEnd
      cdsStart
      cdsEnd
      exonCount
      exonStarts
      exonEnds
      proteinId
   7. Click **done with selection**
   8. Click **Send query to Galaxy**
   9. A new dataset will appear in the current Galaxy history
   10. Use this dataset as the input for **Gene annotations in refFlat form** dropdown of this tool

.. _refFlat: http://genome.ucsc.edu/goldenPath/gbdDescriptionsOld.html#RefFlat

@description@

   REF_FLAT=File                 Gene annotations in refFlat form.  Format described here:
                                 http://genome.ucsc.edu/goldenPath/gbdDescriptionsOld.html#RefFlat  Required.

   RIBOSOMAL_INTERVALS=File      Location of rRNA sequences in genome, in interval_list format.  If not specified no bases
                                 will be identified as being ribosomal. Format described here:
                                 https://samtools.github.io/htsjdk/javadoc/htsjdk/htsjdk/samtools/util/IntervalList.html and can be
                                 generated from BED datasetes using Galaxy's wrapper for picard_BedToIntervalList tool

   STRAND_SPECIFICITY=StrandSpecificity
   STRAND=StrandSpecificity      For strand-specific library prep. For unpaired reads, use FIRST_READ_TRANSCRIPTION_STRAND
                                 if the reads are expected to be on the transcription strand.  Required. Possible values:
                                 {NONE, FIRST_READ_TRANSCRIPTION_STRAND, SECOND_READ_TRANSCRIPTION_STRAND}

   MINIMUM_LENGTH=Integer        When calculating coverage based values (e.g. CV of coverage) only use transcripts of this
                                 length or greater.  Default value: 500.

   IGNORE_SEQUENCE=String        If a read maps to a sequence specified with this option, all the bases in the read are
                                 counted as ignored bases.

   RRNA_FRAGMENT_PERCENTAGE=Double
                                 This percentage of the length of a fragment must overlap one of the ribosomal intervals
                                 for a read or read pair by this must in order to be considered rRNA.  Default value: 0.8.

   METRIC_ACCUMULATION_LEVEL=MetricAccumulationLevel
   LEVEL=MetricAccumulationLevel The level(s) at which to accumulate metrics.    Possible values: {ALL_READS, SAMPLE,
                                 LIBRARY, READ_GROUP} This option may be specified 0 or more times.

   ASSUME_SORTED=Boolean
   AS=Boolean                    If true (default), then the sort order in the header file will be ignored.  Default
                                 value: true. Possible values: {true, false}

@more_info@

  </help>
</tool>


text:
<?php
// +----------------------------------------------------------------------+
// | PHP versions 4 and 5                                                 |
// +----------------------------------------------------------------------+
// | Copyright (c) 1998-2006 Manuel Lemos, Paul Cooper                    |
// | All rights reserved.                                                 |
// +----------------------------------------------------------------------+
// | MDB2 is a merge of PEAR DB and Metabases that provides a unified DB  |
// | API as well as database abstraction for PHP applications.            |
// | This LICENSE is in the BSD license style.                            |
// |                                                                      |
// | Redistribution and use in source and binary forms, with or without   |
// | modification, are permitted provided that the following conditions   |
// | are met:                                                             |
// |                                                                      |
// | Redistributions of source code must retain the above copyright       |
// | notice, this list of conditions and the following disclaimer.        |
// |                                                                      |
// | Redistributions in binary form must reproduce the above copyright    |
// | notice, this list of conditions and the following disclaimer in the  |
// | documentation and/or other materials provided with the distribution. |
// |                                                                      |
// | Neither the name of Manuel Lemos, Tomas V.V.Cox, Stig. S. Bakken,    |
// | Lukas Smith nor the names of his contributors may be used to endorse |
// | or promote products derived from this software without specific prior|
// | written permission.                                                  |
// |                                                                      |
// | THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS  |
// | "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT    |
// | LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS    |
// | FOR A PARTICULAR PURPOSE ARE DISCLAIMED.  IN NO EVENT SHALL THE      |
// | REGENTS OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT,          |
// | INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, |
// | BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS|
// |  OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED  |
// | AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT          |
// | LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY|
// | WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE          |
// | POSSIBILITY OF SUCH DAMAGE.                                          |
// +----------------------------------------------------------------------+
// | Author: Paul Cooper <pgc@ucecom.com>                                 |
// +----------------------------------------------------------------------+
//
// $Id: MDB2_usage_testcase.php,v 1.108 2008/03/01 14:02:29 quipo Exp $

require_once 'MDB2_testcase.php';

class MDB2_Usage_TestCase extends MDB2_TestCase {
    /**
     * Test typed data storage and retrieval
     *
     * This tests typed data storage and retrieval by executing a single
     * prepared query and then selecting the data back from the database
     * and comparing the results
     */
    function testStorage() {
        $data = $this->getSampleData(1234);

        $query = 'INSERT INTO users (' . implode(', ', array_keys($this->fields)) . ') VALUES ('.implode(', ', array_fill(0, count($this->fields), '?')).')';
        $stmt = $this->db->prepare($query, array_values($this->fields), MDB2_PREPARE_MANIP);
        $result = $stmt->execute(array_values($data));
        $stmt->free();

        if (PEAR::isError($result)) {
            $this->assertTrue(false, 'Error executing prepared query: '.$result->getMessage());
        }

        $query = 'SELECT ' . implode(', ', array_keys($this->fields)) . ' FROM users';
        $result =& $this->db->query($query, $this->fields);

        if (PEAR::isError($result)) {
            $this->assertTrue(false, 'Error selecting from users'.$result->getMessage());
        }

        $this->verifyFetchedValues($result, 0, $data);
    }

    /**
     * Test fetchOne()
     *
     * This test bulk fetching of result data by using a prepared query to
     * insert an number of rows of data and then retrieving the data columns
     * one by one
     */
    function testFetchOne() {
        $data = array();
        $total_rows = 5;

        $query = 'INSERT INTO users (' . implode(', ', array_keys($this->fields)) . ') VALUES ('.implode(', ', array_fill(0, count($this->fields), '?')).')';
        $stmt = $this->db->prepare($query, array_values($this->fields), MDB2_PREPARE_MANIP);

        for ($row = 0; $row < $total_rows; $row++) {
            $data[$row] = $this->getSampleData($row);
            $result = $stmt->execute(array_values($data[$row]));

            if (PEAR::isError($result)) {
                $this->assertTrue(false, 'Error executing prepared query: '.$result->getMessage());
            }
        }

        $stmt->free();

        foreach ($this->fields as $field => $type) {
            for ($row = 0; $row < $total_rows; $row++) {
                $result =& $this->db->query('SELECT '.$field.' FROM users WHERE user_id='.$row, $type);
                $value = $result->fetchOne();
                if (PEAR::isError($value)) {
                    $this->assertTrue(false, 'Error fetching row '.$row.' for field '.$field.' of type '.$type);
                } else {
                    $this->assertEquals(strval($data[$row][$field]), strval(trim($value)), 'the query field '.$field.' of type '.$type.' for row '.$row);
                    $result->free();
                }
            }
        }
    }

    /**
     * Test fetchCol()
     *
     * Test fetching a column of result data. Two different columns are retrieved
     */
    function testFetchCol() {
        $data = array();
        $total_rows = 5;

        $query = 'INSERT INTO users (' . implode(', ', array_keys($this->fields)) . ') VALUES ('.implode(', ', array_fill(0, count($this->fields), '?')).')';
        $stmt = $this->db->prepare($query, array_values($this->fields), MDB2_PREPARE_MANIP);

        for ($row = 0; $row < $total_rows; $row++) {
            $data[$row] = $this->getSampleData($row);
            $result = $stmt->execute(array_values($data[$row]));

            if (PEAR::isError($result)) {
                $this->assertTrue(false, 'Error executing prepared query: '.$result->getMessage());
            }
        }

        $stmt->free();

        $first_col = array();
        for ($row = 0; $row < $total_rows; $row++) {
            $first_col[$row] = "user_$row";
        }

        $second_col = array();
        for ($row = 0; $row < $total_rows; $row++) {
            $second_col[$row] = $row;
        }

        $query = 'SELECT user_name, user_id FROM users ORDER BY user_name';
        $result =& $this->db->query($query, array('text', 'integer'));
        if (PEAR::isError($result)) {
            $this->assertTrue(false, 'Error during query: '.$result->getMessage());
        }
        $values = $result->fetchCol(0);
        if (PEAR::isError($values)) {
            $this->assertTrue(false, 'Error fetching first column');
        } else {
            $this->assertEquals($first_col, $values);
        }
        $result->free();

        $query = 'SELECT user_name, user_id FROM users ORDER BY user_name';
        $result =& $this->db->query($query, array('text', 'integer'));
        if (PEAR::isError($result)) {
            $this->assertTrue(false, 'Error during query: '.$result->getMessage());
        }
        $values = $result->fetchCol(1);
        if (PEAR::isError($values)) {
            $this->assertTrue(false, 'Error fetching second column');
        } else {
            $this->assertEquals($second_col, $values);
        }
        $result->free();
    }

    /**
     * Test fetchAll()
     *
     * Test fetching an entire result set in one shot.
     */
    function testFetchAll() {
        $data = array();
        $total_rows = 5;

        $query = 'INSERT INTO users (' . implode(', ', array_keys($this->fields)) . ') VALUES ('.implode(', ', array_fill(0, count($this->fields), '?')).')';
        $stmt = $this->db->prepare($query, array_values($this->fields), MDB2_PREPARE_MANIP);

        for ($row = 0; $row < $total_rows; $row++) {
            $data[$row] = $this->getSampleData($row);
            $result = $stmt->execute(array_values($data[$row]));

            if (PEAR::isError($result)) {
                $this->assertTrue(false, 'Error executing prepared query: '.$result->getMessage());
            }
        }
        $fields = array_keys($data[0]);
        $query = 'SELECT '. implode (', ', $fields). ' FROM users ORDER BY user_name';

        $stmt->free();

        $result =& $this->db->query($query, $this->fields);
        if (PEAR::isError($result)) {
            $this->assertTrue(false, 'Error during query: ' .$result->getMessage());
        }
        $values = $result->fetchAll(MDB2_FETCHMODE_ASSOC);
        if (PEAR::isError($values)) {
            $this->assertTrue(false, 'Error fetching the result set');
        } else {
            for ($i=0; $i<$total_rows; $i++) {
                foreach ($data[$i] as $key => $val) {
                    $this->assertEquals(strval($val), strval($values[$i][$key]), 'Row #'.$i.' ['.$key.']');
                }
            }
        }
        $result->free();

        //test $rekey=true
        $result =& $this->db->query('SELECT user_id, user_name FROM users ORDER BY user_id', $this->fields);
        if (PEAR::isError($result)) {
            $this->assertTrue(false, 'Error during query: '.$result->getMessage());
        }
        $values = $result->fetchAll(MDB2_FETCHMODE_ASSOC, true);
        if (PEAR::isError($values)) {
            $this->assertTrue(false, 'Error fetching the result set');
        } else {
            for ($i=0; $i<$total_rows; $i++) {
                list($id, $name) = each($values);
                $this->assertEquals($data[$i]['user_id'],   $id,   'Row #'.$i.' ["user_id"]');
                $this->assertEquals($data[$i]['user_name'], $name, 'Row #'.$i.' ["user_name"]');
            }
        }
        $result->free();

        //test $rekey=true, $force_array=true
        $result =& $this->db->query('SELECT user_id, user_name FROM users ORDER BY user_id', $this->fields);
        if (PEAR::isError($result)) {
            $this->assertTrue(false, 'Error during query: '.$result->getMessage());
        }
        $values = $result->fetchAll(MDB2_FETCHMODE_ASSOC, true, true);
        if (PEAR::isError($values)) {
            $this->assertTrue(false, 'Error fetching the result set');
        } else {
            for ($i=0; $i<$total_rows; $i++) {
                list($id, $value) = each($values);
                $this->assertEquals($data[$i]['user_id'],   $id,                 'Row #'.$i.' ["user_id"]');
                $this->assertEquals($data[$i]['user_name'], $value['user_name'], 'Row #'.$i.' ["user_name"]');
            }
        }
        $result->free();

        //test $rekey=true, $force_array=true, $group=true
        $result =& $this->db->query('SELECT user_password, user_name FROM users ORDER BY user_name', $this->fields);
        if (PEAR::isError($result)) {
            $this->assertTrue(false, 'Error during query: '.$result->getMessage());
        }
        $values = $result->fetchAll(MDB2_FETCHMODE_ASSOC, true, true, true);
        if (PEAR::isError($values)) {
            $this->assertTrue(false, 'Error fetching the result set');
        } else {
            //all the records have the same user_password value
            $this->assertEquals(1, count($values), 'Error: incorrect number of returned rows');
            $values = $values[$data[0]['user_password']];
            for ($i=0; $i<$total_rows; $i++) {
                $this->assertEquals($data[$i]['user_name'], $values[$i]['user_name'], 'Row #'.$i.' ["user_name"]');
            }
        }
        $result->free();

        //test $rekey=true, $force_array=true, $group=false (with non unique key)
        $result =& $this->db->query('SELECT user_password, user_name FROM users ORDER BY user_name', $this->fields);
        if (PEAR::isError($result)) {
            $this->assertTrue(false, 'Error during query: '.$result->getMessage());
        }
        $values = $result->fetchAll(MDB2_FETCHMODE_ASSOC, true, true, false);
        if (PEAR::isError($values)) {
            $this->assertTrue(false, 'Error fetching the result set');
        } else {
            //all the records have the same user_password value, they are overwritten
            $this->assertEquals(1, count($values), 'Error: incorrect number of returned rows');
            $key = $data[0]['user_password'];
            $this->assertEquals(1, count($values[$key]), 'Error: incorrect number of returned rows');
            $this->assertEquals($data[4]['user_name'], $values[$key]['user_name']);
        }
        $result->free();
    }

    /**
     * Test different fetch modes
     *
     * Test fetching results using different fetch modes
     * NOTE: several tests still missing
     */
    function testFetchModes() {
        $data = array();
        $total_rows = 5;

        $query = 'INSERT INTO users (' . implode(', ', array_keys($this->fields)) . ') VALUES ('.implode(', ', array_fill(0, count($this->fields), '?')).')';
        $stmt = $this->db->prepare($query, array_values($this->fields), MDB2_PREPARE_MANIP);

        for ($row = 0; $row < $total_rows; $row++) {
            $data[$row] = $this->getSampleData($row);
            $result = $stmt->execute(array_values($data[$row]));

            if (PEAR::isError($result)) {
                $this->assertTrue(false, 'Error executing prepared query: '.$result->getMessage());
            }
        }

        $stmt->free();

        // test ASSOC
        $query = 'SELECT A.user_name FROM users A, users B WHERE A.user_id = B.user_id';
        $value = $this->db->queryRow($query, array($this->fields['user_name']), MDB2_FETCHMODE_ASSOC);
        if (PEAR::isError($value)) {
            $this->assertTrue(false, 'Error fetching the result set');
        } else {
            $this->assertTrue(!empty($value['user_name']), 'Error fetching the associative result set from join');
        }
    }

    /**
     * Test multi_query option
     *
     * This test attempts to send multiple queries at once using the multi_query
     * option and then retrieves each result.
     */
    function testMultiQuery() {
        $multi_query_orig = $this->db->getOption('multi_query');
        if (PEAR::isError($multi_query_orig)) {
            $this->assertTrue(false, 'Error getting multi_query option value: '.$multi_query_orig->getMessage());
            return;
        }

        $this->db->setOption('multi_query', true);

        $data = array();
        $total_rows = 5;

        $query = 'INSERT INTO users (' . implode(', ', array_keys($this->fields)) . ') VALUES ('.implode(', ', array_fill(0, count($this->fields), '?')).')';
        $stmt = $this->db->prepare($query, array_values($this->fields), MDB2_PREPARE_MANIP);

        for ($row = 0; $row < $total_rows; $row++) {
            $data[$row] = $this->getSampleData($row);
            $result = $stmt->execute(array_values($data[$row]));

            if (PEAR::isError($result)) {
                $this->assertTrue(false, 'Error executing prepared query: '.$result->getMessage());
            }
        }

        $stmt->free();

        $query = '';
        for ($row = 0; $row < $total_rows; $row++) {
            $query.= 'SELECT user_name FROM users WHERE user_id='.$row.';';
        }
        $result =& $this->db->query($query, 'text');

        for ($row = 0; $row < $total_rows; $row++) {
            $value = $result->fetchOne();
            if (PEAR::isError($value)) {
                $this->assertTrue(false, 'Error fetching row '.$row);
            } else {
                $this->assertEquals(strval($data[$row]['user_name']), strval(trim($value)), 'the query field username of type "text" for row '.$row);
            }
            if (PEAR::isError($result->nextResult())) {
                $this->assertTrue(false, 'Error moving result pointer');
            }
        }

        $result->free();
        $this->db->setOption('multi_query', $multi_query_orig);
    }

    /**
     * Test prepared queries
     *
     * Tests prepared queries, making sure they correctly deal with ?, !, and '
     */
    function testPreparedQueries() {
        $data = array(
            array(
                'user_name' => 'Sure!',
                'user_password' => 'Do work?',
                'user_id' => 1,
            ),
            array(
                'user_name' => 'For Sure!',
                'user_password' => "Doesn't?",
                'user_id' => 2,
            ),
        );

        $query = "INSERT INTO users (user_name, user_password, user_id) VALUES (?, ?, ?)";
        $stmt = $this->db->prepare($query, array('text', 'text', 'integer'), MDB2_PREPARE_MANIP);

        $text = $data[0]['user_name'];
        $question = $data[0]['user_password'];
        $userid = $data[0]['user_id'];

        // bind out of order
        $stmt->bindParam(0, $text);
        $stmt->bindParam(2, $userid);
        $stmt->bindParam(1, $question);

        $result = $stmt->execute();
        if (PEAR::isError($result)) {
            $this->assertTrue(true, 'Could not execute prepared query with question mark placeholders. Error: '.$error);
        }

        $text = $data[1]['user_name'];
        $question = $data[1]['user_password'];
        $userid = $data[1]['user_id'];

        $result = $stmt->execute();
        if (PEAR::isError($result)) {
            $this->assertTrue(true, 'Could not execute prepared query with bound parameters. Error: '.$error);
        }
        $stmt->free();
        $this->clearTables();

        $query = "INSERT INTO users (user_name, user_password, user_id) VALUES (:text, :question, :userid)";
        $stmt = $this->db->prepare($query, array('text', 'text', 'integer'), MDB2_PREPARE_MANIP);
        if (PEAR::isError($stmt)) {
            $this->assertTrue(false, 'Error preparing query: ' . $stmt->getMessage. ' :: '.$stmt->getUserInfo());
        }

        $stmt->bindValue('text', $data[0]['user_name']);
        $stmt->bindValue('question', $data[0]['user_password']);
        $stmt->bindValue('userid', $data[0]['user_id']);

        $result = $stmt->execute();
        if (PEAR::isError($result)) {
            $this->assertTrue(true, 'Could not execute prepared query with named placeholders. Error: '.$error);
        }
        $stmt->free();

        $query = "INSERT INTO users (user_name, user_password, user_id) VALUES (".$this->db->quote($data[1]['user_name'], 'text').", :question, :userid)";
        $stmt = $this->db->prepare($query, array('text', 'integer'), MDB2_PREPARE_MANIP);

        $stmt->bindValue('question', $data[1]['user_password']);
        $stmt->bindValue('userid', $data[1]['user_id']);

        $result = $stmt->execute();
        if (PEAR::isError($result)) {
            $this->assertTrue(true, 'Could not execute prepared query with named placeholders and a quoted text value in front. Error: '.$result->getMessage());
        }
        $stmt->free();

        $query = 'SELECT user_name, user_password, user_id FROM users WHERE user_id=:user_id';
        $stmt = $this->db->prepare($query, array('integer'), array('text', 'text', 'integer'));
        foreach ($data as $row_data) {
            $result =& $stmt->execute(array('user_id' => $row_data['user_id']));
            if (PEAR::isError($result)) {
                $this->assertTrue(!PEAR::isError($result), 'Could not execute prepared. Error: '.$result->getUserinfo());
                break;
            }
            $row = $result->fetchRow(MDB2_FETCHMODE_ASSOC);
            if (!is_array($row)) {
                $this->assertTrue(false, 'Prepared SELECT failed');
            } else {
                $diff = (array)array_diff($row, $row_data);
                $this->assertTrue(empty($diff), 'Prepared SELECT failed for fields: '.implode(', ', array_keys($diff)));
            }
        }
        $stmt->free();

        $row_data = reset($data);
        $query = 'SELECT user_name, user_password, user_id FROM users WHERE user_id='.$this->db->quote($row_data['user_id'], 'integer');
        $stmt = $this->db->prepare($query, null, array('text', 'text', 'integer'));
        $result =& $stmt->execute(array());
        if (PEAR::isError($result)) {
            $this->assertTrue(!PEAR::isError($result), 'Could not execute prepared statement with no placeholders. Error: '.$result->getUserinfo());
            break;
        }
        $row = $result->fetchRow(MDB2_FETCHMODE_ASSOC);
        if (!is_array($row)) {
            $this->assertTrue(false, 'Prepared SELECT failed');
        } else {
            $diff = (array)array_diff($row, $row_data);
            $this->assertTrue(empty($diff), 'Prepared SELECT failed for fields: '.implode(', ', array_keys($diff)));
        }
        $stmt->free();

        $row_data = reset($data);
        $query = 'SELECT user_name, user_password, user_id FROM users WHERE user_name='.$this->db->quote($row_data['user_name'], 'text').' AND user_id = ? AND user_password='.$this->db->quote($row_data['user_password'], 'text');
        $stmt = $this->db->prepare($query, array('integer'), array('text', 'text', 'integer'));
        $result =& $stmt->execute(array($row_data['user_id']));
        if (PEAR::isError($result)) {
            $this->assertTrue(!PEAR::isError($result), 'Could not execute prepared with quoted text fields around a placeholder. Error: '.$result->getUserinfo());
            break;
        }
        $row = $result->fetchRow(MDB2_FETCHMODE_ASSOC);
        if (!is_array($row)) {
            $this->assertTrue(false, 'Prepared SELECT failed');
        } else {
            $diff = (array)array_diff($row, $row_data);
            $this->assertTrue(empty($diff), 'Prepared SELECT failed for fields: '.implode(', ', array_keys($diff)));
        }
        $stmt->free();

        foreach ($this->db->sql_comments as $comment) {
            $query = 'SELECT user_name, user_password, user_id FROM users WHERE '.$comment['start'].' maps to class::foo() '.$comment['end'].' user_name=:username';
            $row_data = reset($data);
            $stmt = $this->db->prepare($query, array('text'), array('text', 'text', 'integer'));
            $result =& $stmt->execute(array('username' => $row_data['user_name']));
            if (PEAR::isError($result)) {
                $this->assertTrue(!PEAR::isError($result), 'Could not execute prepared where a name parameter is contained in an SQL comment ('.$comment['start'].'). Error: '.$result->getUserinfo());
                break;
            }
            $row = $result->fetchRow(MDB2_FETCHMODE_ASSOC);
            if (!is_array($row)) {
                $this->assertTrue(false, 'Prepared SELECT failed');
            } else {
                $diff = (array)array_diff($row, $row_data);
                $this->assertTrue(empty($diff), 'Prepared SELECT failed for fields: '.implode(', ', array_keys($diff)));
            }
            $stmt->free();
        }

        $row_data = reset($data);
        $query = 'SELECT user_name, user_password, user_id FROM users WHERE user_name=:username OR user_password=:username';
        $stmt = $this->db->prepare($query, array('text'), array('text', 'text', 'integer'));
        $result =& $stmt->execute(array('username' => $row_data['user_name']));
        if (PEAR::isError($result)) {
            $this->assertTrue(!PEAR::isError($result), 'Could not execute prepared where the same named parameter is used twice. Error: '.$result->getUserinfo());
            break;
        }
        $row = $result->fetchRow(MDB2_FETCHMODE_ASSOC);
        if (!is_array($row)) {
            $this->assertTrue(false, 'Prepared SELECT failed');
        } else {
            $diff = (array)array_diff($row, $row_data);
            $this->assertTrue(empty($diff), 'Prepared SELECT failed for fields: '.implode(', ', array_keys($diff)));
        }
        $stmt->free();
    }

    /**
     * Test _skipDelimitedStrings(), used by prepare()
     *
     * If the placeholder is contained within a delimited string, it must be skipped,
     * and the cursor position must be advanced
     */
    function testSkipDelimitedStrings() {
        //test correct placeholder
        $query = 'SELECT what FROM tbl WHERE x = ?';
        $position = 0;
        $p_position = strpos($query, '?');
        $this->assertEquals($position, $this->db->_skipDelimitedStrings($query, $position, $p_position), 'Error: the cursor position has changed');

        //test placeholder within a quoted string
        $query = 'SELECT what FROM tbl WHERE x = '. $this->db->string_quoting['start'] .'blah?blah'. $this->db->string_quoting['end'] .' AND y = ?';
        $position = 0;
        $p_position = strpos($query, '?');
        $new_pos = $this->db->_skipDelimitedStrings($query, $position, $p_position);
        $this->assertTrue($position !=$new_pos, 'Error: the cursor position was not advanced');

        //test placeholder within a comment
        foreach ($this->db->sql_comments as $comment) {
            $query = 'SELECT what FROM tbl WHERE x = '. $comment['start'] .'blah?blah'. $comment['end'] .' AND y = ?';
            $position = 0;
            $p_position = strpos($query, '?');
            $new_pos = $this->db->_skipDelimitedStrings($query, $position, $p_position);
            $this->assertTrue($position != $new_pos, 'Error: the cursor position was not advanced');
        }

        //add some tests for named placeholders and for identifier_quoting
    }

    /**
     * Test retrieval of result metadata
     *
     * This tests the result metadata by executing a prepared query and
     * select the data, and checking the result contains the correct
     * number of columns and that the column names are in the correct order
     */
    function testMetadata() {
        $data = $this->getSampleData(1234);

        $query = 'INSERT INTO users (' . implode(', ', array_keys($this->fields)) . ') VALUES ('.implode(', ', array_fill(0, count($this->fields), '?')).')';
        $stmt = $this->db->prepare($query, array_values($this->fields), MDB2_PREPARE_MANIP);

        $result = $stmt->execute(array_values($data));
        $stmt->free();

        if (PEAR::isError($result)) {
            $this->assertTrue(false, 'Error executing prepared query: '.$result->getMessage());
        }

        $query = 'SELECT ' . implode(', ', array_keys($this->fields)) . ' FROM users';
        $result =& $this->db->query($query, $this->fields);

        if (PEAR::isError($result)) {
            $this->assertTrue(false, 'Error selecting from users'.$result->getMessage());
        }

        $numcols = $result->numCols();

        $this->assertEquals(count($this->fields), $numcols, "The query result returned an incorrect number of columns unlike expected");

        $column_names = $result->getColumnNames();
        $fields = array_keys($this->fields);
        for ($column = 0; $column < $numcols; $column++) {
            $this->assertEquals($column, $column_names[$fields[$column]], "The query result column \"".$fields[$column]."\" was returned in an incorrect position");
        }

    }

    /**
     * Test storage and retrieval of nulls
     *
     * This tests null storage and retrieval by successively inserting,
     * selecting, and testing a number of null / not null values
     */
    function testNulls() {
        $portability = $this->db->getOption('portability');
        if ($portability & MDB2_PORTABILITY_EMPTY_TO_NULL) {
            $nullisempty = true;
        } else {
            $nullisempty = false;
        }
        $test_values = array(
            array('test', false),
            array('NULL', false),
            array('null', false),
            array('', $nullisempty),
            array(null, true)
        );

        for ($test_value = 0; $test_value <= count($test_values); $test_value++) {
            if ($test_value == count($test_values)) {
                $value = 'NULL';
                $is_null = true;
            } else {
                $value = $this->db->quote($test_values[$test_value][0], 'text');
                $is_null = $test_values[$test_value][1];
            }

            $this->clearTables();

            $result = $this->db->exec("INSERT INTO users (user_name,user_password,user_id) VALUES ($value,$value,0)");

            if (PEAR::isError($result)) {
                $this->assertTrue(false, 'Error executing insert query: '.$result->getMessage());
            }

            $result =& $this->db->query('SELECT user_name,user_password FROM users', array('text', 'text'));

            if (PEAR::isError($result)) {
                $this->assertTrue(false, 'Error executing select query: '.$result->getMessage());
            }

            if ($is_null) {
                $error_message = 'A query result column is not NULL unlike what was expected';
            } else {
                $error_message = 'A query result column is NULL even though it was expected to be different';
            }

            $row = $result->fetchRow();
            $this->assertTrue((is_null($row[0]) == $is_null), $error_message);
            $this->assertTrue((is_null($row[1]) == $is_null), $error_message);

            $result->free();
        }

        $methods = array('fetchOne', 'fetchRow');

        foreach ($methods as $method) {
            $result =& $this->db->query('SELECT user_name FROM users WHERE user_id=123', array('text'));
            $value = $result->$method();
            if (PEAR::isError($value)) {
                $this->assertTrue(false, 'Error fetching non existent row');
            } else {
                $this->assertNull($value, 'selecting non existent row with "'.$method.'()" did not return NULL');
                $result->free();
            }
        }

        $methods = array('fetchCol', 'fetchAll');

        foreach ($methods as $method) {
            $result =& $this->db->query('SELECT user_name FROM users WHERE user_id=123', array('text'));
            $value = $result->$method();
            if (PEAR::isError($value)) {
                $this->assertTrue(false, 'Error fetching non existent row');
            } else {
                $this->assertTrue((is_array($value) && empty($value)), 'selecting non existent row with "'.$method.'()" did not return empty array');
                $result->free();
            }
        }

        $methods = array('queryOne', 'queryRow');

        foreach ($methods as $method) {
            $value = $this->db->$method('SELECT user_name FROM users WHERE user_id=123', array('text'));
            if (PEAR::isError($value)) {
                $this->assertTrue(false, 'Error fetching non existent row');
            } else {
                $this->assertNull($value, 'selecting non existent row with "'.$method.'()" did not return NULL');
                $result->free();
            }
        }

        $methods = array('queryCol', 'queryAll');

        foreach ($methods as $method) {
            $value = $this->db->$method('SELECT user_name FROM users WHERE user_id=123', array('text'));
            if (PEAR::isError($value)) {
                $this->assertTrue(false, 'Error fetching non existent row');
            } else {
                $this->assertTrue((is_array($value) && empty($value)), 'selecting non existent row with "'.$method.'()" did not return empty array');
                $result->free();
            }
        }
    }

    /**
     * Test paged queries
     *
     * Test the use of setLimit to return paged queries
     */
    function testRanges() {
        if (!$this->supported('limit_queries')) {
            return;
        }

        $data = array();
        $total_rows = 5;

        $query = 'INSERT INTO users (' . implode(', ', array_keys($this->fields)) . ') VALUES ('.implode(', ', array_fill(0, count($this->fields), '?')).')';
        $stmt = $this->db->prepare($query, array_values($this->fields), MDB2_PREPARE_MANIP);

        for ($row = 0; $row < $total_rows; $row++) {
            $data[$row] = $this->getSampleData($row);
            $result = $stmt->execute(array_values($data[$row]));

            if (PEAR::isError($result)) {
                $this->assertTrue(false, 'Error executing prepared query: '.$result->getMessage());
            }
        }

        $stmt->free();

        for ($rows = 2, $start_row = 0; $start_row < $total_rows; $start_row += $rows) {

            $this->db->setLimit($rows, $start_row);

            $query = 'SELECT ' . implode(', ', array_keys($this->fields)) . ' FROM users ORDER BY user_name';
            $result =& $this->db->query($query, $this->fields);

            if (PEAR::isError($result)) {
                $this->assertTrue(false, 'Error executing select query: '.$result->getMessage());
            }

            for ($row = 0; $row < $rows && ($row + $start_row < $total_rows); $row++) {
                $this->verifyFetchedValues($result, $row, $data[$row + $start_row]);
            }
        }

        $this->assertFalse($result->valid(), "The query result did not seem to have reached the end of result as expected starting row $start_row after fetching upto row $row");

        $result->free();

        for ($rows = 2, $start_row = 0; $start_row < $total_rows; $start_row += $rows) {

            $this->db->setLimit($rows, $start_row);

            $query = 'SELECT ' . implode(', ', array_keys($this->fields)) . ' FROM users ORDER BY user_name';
            $result =& $this->db->query($query, $this->fields);

            if (PEAR::isError($result)) {
                $this->assertTrue(false, 'Error executing select query: '.$result->getMessage());
            }

            $result_rows = $result->numRows();

            $expected = ($start_row == ($total_rows-1)) ? 1 : $rows;
            $this->assertEquals($expected, $result_rows, 'invalid number of rows returned');
            $this->assertTrue(($result_rows <= $rows), 'expected a result of no more than '.$rows.' but the returned number of rows is '.$result_rows);

            for ($row = 0; $row < $result_rows; $row++) {
                $this->assertTrue($result->valid(), 'The query result seem to have reached the end of result at row '.$row.' that is before '.$result_rows.' as expected');
                $this->verifyFetchedValues($result, $row, $data[$row + $start_row]);
            }
        }

        $this->assertTrue(!$result->valid(), "The query result did not seem to have reached the end of result as expected starting row $start_row after fetching upto row $row");

        $result->free();
    }

    /**
     * Test the handling of sequences
     */
    function testSequences() {
        if (!$this->supported('sequences')) {
           return;
        }

        $this->db->loadModule('Manager', null, true);

        for ($start_value = 1; $start_value < 4; $start_value++) {
            $sequence_name = "test_sequence_$start_value";

            $result = $this->db->manager->createSequence($sequence_name, $start_value);
            if (PEAR::isError($result)) {
                $this->assertTrue(false, "Error creating sequence $sequence_name with start value $start_value: ".$result->getMessage());
            } else {
                for ($sequence_value = $start_value; $sequence_value < ($start_value + 4); $sequence_value++) {
                    $value = $this->db->nextID($sequence_name, false);

                    $this->assertEquals($sequence_value, $value, "The returned sequence value for $sequence_name is not expected with sequence start value with $start_value");
                }

                $result = $this->db->manager->dropSequence($sequence_name);

                if (PEAR::isError($result)) {
                    $this->assertTrue(false, "Error dropping sequence $sequence_name : ".$result->getMessage());
                }
            }
        }

        // Test ondemand creation of sequences
        $sequence_name = 'test_ondemand';
        $this->db->expectError(MDB2_ERROR_NOSUCHTABLE);
        $this->db->manager->dropSequence($sequence_name);
        $this->db->popExpect();

        for ($sequence_value = 1; $sequence_value < 4; $sequence_value++) {
            $value = $this->db->nextID($sequence_name);

            if (PEAR::isError($result)) {
                $this->assertTrue(false, "Error creating with ondemand sequence: ".$result->getMessage());
            } else {
                $this->assertEquals($sequence_value, $value, "Error in ondemand sequences. The returned sequence value is not expected value");
            }
        }

        $result = $this->db->manager->dropSequence($sequence_name);
        if (PEAR::isError($result)) {
            $this->assertTrue(false, "Error dropping sequence $sequence_name : ".$result->getMessage());
        }

        // Test currId()
        $sequence_name = 'test_currid';

        $next = $this->db->nextID($sequence_name);
        $curr = $this->db->currID($sequence_name);

        if (PEAR::isError($curr)) {
            $this->assertTrue(false, "Error getting the current value of sequence $sequence_name : ".$curr->getMessage());
        } else {
            if ($next != $curr) {
                if ($next+1 == $curr) {
                    $this->assertTrue(false, "Warning: currID() is using nextID() instead of a native implementation");
                } else {
                    $this->assertEquals($next, $curr, "return value if currID() does not match the previous call to nextID()");
                }
            }
        }
        $result = $this->db->manager->dropSequence($sequence_name);
        if (PEAR::isError($result)) {
            $this->assertTrue(false, "Error dropping sequence $sequence_name : ".$result->getMessage());
        }

        // Test lastInsertid()
        if (!$this->db->supports('new_link')) {
           return;
        }

        $sequence_name = 'test_lastinsertid';

        $dsn = MDB2::parseDSN($this->dsn);
        $dsn['new_link'] = true;
        $dsn['database'] = $this->database;
        $db =& MDB2::connect($dsn, $this->options);

        $next = $this->db->nextID($sequence_name);
        $next2 = $db->nextID($sequence_name);
        $last = $this->db->lastInsertID($sequence_name);

        if (PEAR::isError($last)) {
            $this->assertTrue(false, "Error getting the last value of sequence $sequence_name : ".$last->getMessage());
        } else {
            $this->assertEquals($next, $last, "return value if lastInsertID() does not match the previous call to nextID()");
        }
        $result = $this->db->manager->dropSequence($sequence_name);
        if (PEAR::isError($result)) {
            $this->assertTrue(false, "Error dropping sequence $sequence_name : ".$result->getMessage());
        }
    }

    /**
     * Test replace query
     *
     * The replace method emulates the replace query of mysql
     */
    function testReplace() {
        if (!$this->supported('replace')) {
            return;
        }

        $row = 1234;
        $data = $this->getSampleData($row);

        $fields = array(
            'user_name' => array(
                'value' => "user_$row",
                'type' => 'text'
            ),
            'user_password' => array(
                'value' => $data['user_password'],
                'type' => 'text'
            ),
            'subscribed' => array(
                'value' => $data['subscribed'],
                'type' => 'boolean'
            ),
            'user_id' => array(
                'value' => $data['user_id'],
                'type' => 'integer',
                'key' => 1
            ),
            'quota' => array(
                'value' => $data['quota'],
                'type' => 'decimal'
            ),
            'weight' => array(
                'value' => $data['weight'],
                'type' => 'float'
            ),
            'access_date' => array(
                'value' => $data['access_date'],
                'type' => 'date'
            ),
            'access_time' => array(
                'value' => $data['access_time'],
                'type' => 'time'
            ),
            'approved' => array(
                'value' => $data['approved'],
                'type' => 'timestamp'
            )
        );

        $result = $this->db->replace('users', $fields);

        if (PEAR::isError($result)) {
            $this->assertTrue(false, 'Replace failed');
        }

        if ($this->db->supports('affected_rows')) {
            $affected_rows = $result;
            $this->assertEquals(1, $result, "replacing a row in an empty table returned incorrect value");
        } else {
            $this->assertTrue(false, '"affected_rows" is not supported');
        }

        $query = 'SELECT ' . implode(', ', array_keys($this->fields)) . ' FROM users';
        $result =& $this->db->query($query, $this->fields);

        if (PEAR::isError($result)) {
            $this->assertTrue(false, 'Error selecting from users'.$result->getMessage());
        }

        $this->verifyFetchedValues($result, 0, $data);

        $row = 4321;
        $fields['user_name']['value']     = $data['user_name']     = 'user_'.$row;
        $fields['user_password']['value'] = $data['user_password'] = 'somepass';
        $fields['subscribed']['value']    = $data['subscribed']    = $row % 2 ? true : false;
        $fields['quota']['value']         = $data['quota']         = strval($row/100);
        $fields['weight']['value']        = $data['weight']        = sqrt($row);
        $fields['access_date']['value']   = $data['access_date']   = MDB2_Date::mdbToday();
        $fields['access_time']['value']   = $data['access_time']   = MDB2_Date::mdbTime();
        $fields['approved']['value']      = $data['approved']      = MDB2_Date::mdbNow();

        $result = $this->db->replace('users', $fields);

        if (PEAR::isError($result)) {
            $this->assertTrue(false, 'Replace failed');
        }
        if ($this->db->supports('affected_rows')) {
            $this->assertEquals(2, $result, "replacing a row returned incorrect result");
        }

        $query = 'SELECT ' . implode(', ', array_keys($this->fields)) . ' FROM users';
        $result =& $this->db->query($query, $this->fields);

        if (PEAR::isError($result)) {
            $this->assertTrue(false, 'Error selecting from users'.$result->getMessage());
        }

        $this->verifyFetchedValues($result, 0, $data);

        $this->assertTrue(!$result->valid(), 'the query result did not seem to have reached the end of result as expected');

        $result->free();
    }

    /**
     * Test affected rows methods
     */
    function testAffectedRows() {
        if (!$this->supported('affected_rows')) {
            return;
        }

        $data = array();
        $total_rows = 7;

        $query = 'INSERT INTO users (' . implode(', ', array_keys($this->fields)) . ') VALUES ('.implode(', ', array_fill(0, count($this->fields), '?')).')';
        $stmt = $this->db->prepare($query, array_values($this->fields), MDB2_PREPARE_MANIP);

        for ($row = 0; $row < $total_rows; $row++) {
            $data[$row] = $this->getSampleData($row);
            $result = $stmt->execute(array_values($data[$row]));

            if (PEAR::isError($result)) {
                $this->assertTrue(false, 'Error executing prepared query: '.$result->getMessage());
            }

            $this->assertEquals(1, $result, "Inserting the row $row returned incorrect affected row count");
        }

        $stmt->free();

        $query = 'UPDATE users SET user_password=? WHERE user_id < ?';
        $stmt = $this->db->prepare($query, array('text', 'integer'), MDB2_PREPARE_MANIP);

        for ($row = 0; $row < $total_rows; $row++) {
            $password = "pass_$row";
            if ($row == 0) {
                $stmt->bindParam(0, $password);
                $stmt->bindParam(1, $row);
            }

            $result = $stmt->execute();

            if (PEAR::isError($result)) {
                $this->assertTrue(false, 'Error executing prepared query: '.$result->getMessage());
            }

            $this->assertEquals($row, $result, "Updating the $row rows returned incorrect affected row count");
        }

        $stmt->free();

        $query = 'DELETE FROM users WHERE user_id >= ?';
        $stmt = $this->db->prepare($query, array('integer'), MDB2_PREPARE_MANIP);

        $row = intval($total_rows / 2);
        $stmt->bindParam(0, $row);
        for ($row = $total_rows; $total_rows; $total_rows = $row) {
            $row = intval($total_rows / 2);

            $result = $stmt->execute();

            if (PEAR::isError($result)) {
                $this->assertTrue(false, 'Error executing prepared query: '.$result->getMessage());
            }

            $this->assertEquals(($total_rows - $row), $result, 'Deleting rows returned incorrect affected row count');

        }

        $stmt->free();
    }

    /**
     * Testing transaction support - Test ROLLBACK
     */
    function testTransactionsRollback() {
        if (!$this->supported('transactions')) {
            return;
        }

        $data = $this->getSampleData(0);

        $this->db->beginTransaction();

        $query = 'INSERT INTO users (' . implode(', ', array_keys($this->fields)) . ') VALUES ('.implode(', ', array_fill(0, count($this->fields), '?')).')';
        $stmt = $this->db->prepare($query, array_values($this->fields), MDB2_PREPARE_MANIP);

        $result = $stmt->execute(array_values($data));
        $this->db->rollback();
        $stmt->free();

        $query = 'SELECT ' . implode(', ', array_keys($this->fields)) . ' FROM users';
        $result =& $this->db->query($query);
        if (PEAR::isError($result)) {
            $this->assertTrue(false, 'Error selecting from users'.$result->getMessage());
        }
        $this->assertTrue(!$result->valid(), 'Transaction rollback did not revert the row that was inserted');
        $result->free();
    }

    /**
     * Testing transaction support - Test COMMIT
     */
    function testTransactionsCommit() {
        if (!$this->supported('transactions')) {
            return;
        }

        $data = $this->getSampleData(1);

        $this->db->beginTransaction();

        $query = 'INSERT INTO users (' . implode(', ', array_keys($this->fields)) . ') VALUES ('.implode(', ', array_fill(0, count($this->fields), '?')).')';
        $stmt = $this->db->prepare($query, array_values($this->fields), MDB2_PREPARE_MANIP);

        $result = $stmt->execute(array_values($data));
        $this->db->commit();
        $stmt->free();

        $query = 'SELECT ' . implode(', ', array_keys($this->fields)) . ' FROM users';
        $result =& $this->db->query($query);
        if (PEAR::isError($result)) {
            $this->assertTrue(false, 'Error selecting from users'.$result->getMessage());
        }
        $this->assertTrue($result->valid(), 'Transaction commit did not make permanent the row that was inserted');
        $result->free();
    }

    /**
     * Testing transaction support - Test COMMIT and ROLLBACK
     */
    function testTransactionsBoth()
    {
        if (!$this->supported('transactions')) {
            return;
        }

        $data = $this->getSampleData(0);

        $this->db->beginTransaction();
        $result = $this->db->exec('DELETE FROM users');
        if (PEAR::isError($result)) {
            $this->assertTrue(false, 'Error deleting from users'.$result->getMessage());
            $this->db->rollback();
        } else {
            $this->db->commit();
        }

        $query = 'SELECT ' . implode(', ', array_keys($this->fields)) . ' FROM users';
        $result =& $this->db->query($query);
        if (PEAR::isError($result)) {
            $this->assertTrue(false, 'Error selecting from users'.$result->getMessage());
        }

        $this->assertTrue(!$result->valid(), 'Transaction end with implicit commit when re-enabling auto-commit did not make permanent the rows that were deleted');
        $result->free();
    }

    /**
     * Testing emulated nested transaction support
     */
    function testNestedTransactions() {
        if (!$this->supported('transactions')) {
            return;
        }

        $data = array(
            1 => $this->getSampleData(1234),
            2 => $this->getSampleData(4321),
        );

        $this->db->beginNestedTransaction();

        $query = 'INSERT INTO users (' . implode(', ', array_keys($this->fields)) . ') VALUES ('.implode(', ', array_fill(0, count($this->fields), '?')).')';
        $stmt = $this->db->prepare($query, array_values($this->fields), MDB2_PREPARE_MANIP);

        $result = $stmt->execute(array_values($data[1]));

        $this->db->beginNestedTransaction();

        $result = $stmt->execute(array_values($data[2]));
        $stmt->free();

        $result = $this->db->completeNestedTransaction();
        if (PEAR::isError($result)) {
            $this->assertTrue(false, 'Inner transaction was not committed: '.$result->getMessage());
        }

        $result = $this->db->completeNestedTransaction();
        if (PEAR::isError($result)) {
            $this->assertTrue(false, 'Outer transaction was not committed: '.$result->getMessage());
        }

        $query = 'SELECT ' . implode(', ', array_keys($this->fields)) . ' FROM users';
        $result =& $this->db->query($query);
        if (PEAR::isError($result)) {
            $this->assertTrue(false, 'Error selecting from users'.$result->getMessage());
        }
        $this->assertTrue($result->valid(), 'Transaction commit did not make permanent the row that was inserted');
        $result->free();
    }

    /**
     * Testing savepoints
     */
    function testSavepoint() {
        if (!$this->supported('savepoints')) {
            return;
        }

        $savepoint = 'test_savepoint';

        $data = array(
            1 => $this->getSampleData(1234),
            2 => $this->getSampleData(4321),
        );

        $this->db->beginTransaction();

        $query = 'INSERT INTO users (' . implode(', ', array_keys($this->fields)) . ') VALUES ('.implode(', ', array_fill(0, count($this->fields), '?')).')';
        $stmt = $this->db->prepare($query, array_values($this->fields), MDB2_PREPARE_MANIP);

        $result = $stmt->execute(array_values($data[1]));
        if (PEAR::isError($result)) {
            $this->assertTrue(false, 'Error executing prepared query: '.$result->getMessage());
        }

        $result = $this->db->beginTransaction($savepoint);
        if (PEAR::isError($result)) {
            $this->assertTrue(false, 'Error setting savepoint: '.$result->getMessage());
        }

        $result = $stmt->execute(array_values($data[2]));
        if (PEAR::isError($result)) {
            $this->assertTrue(false, 'Error executing prepared query: '.$result->getMessage());
        }
        $stmt->free();

        $result = $this->db->rollback($savepoint);
        if (PEAR::isError($result)) {
            $this->assertTrue(false, 'Error rolling back to savepoint: '.$result->getMessage());
        }

        $result = $this->db->commit();
        if (PEAR::isError($result)) {
            $this->assertTrue(false, 'Transaction not committed: '.$result->getMessage());
        }

        $query = 'SELECT ' . implode(', ', array_keys($this->fields)) . ' FROM users';
        $result = $this->db->queryAll($query);
        if (PEAR::isError($result)) {
            $this->assertTrue(false, 'Error selecting from users'.$result->getMessage());
        }
        $rows_inserted = count($result);
        $this->assertEquals(1, $rows_inserted, 'Error during transaction, invalid number of records inserted');

        // test release savepoint
        $this->db->beginTransaction();
        $result = $this->db->beginTransaction($savepoint);
        if (PEAR::isError($result)) {
            $this->assertTrue(false, 'Error setting savepoint: '.$result->getMessage());
        }
        $result = $this->db->commit($savepoint);
        if (PEAR::isError($result)) {
            $this->assertTrue(false, 'Error setting savepoint: '.$result->getMessage());
        }
        $result = $this->db->commit();
        if (PEAR::isError($result)) {
            $this->assertTrue(false, 'Transaction not committed: '.$result->getMessage());
        }
    }

    /**
     * Testing LOB storage
     *
     * N.B. for the mssql driver: if this test fails, use an higher limit in these
     * two php.ini settings: "mssql.textlimit" and "mssql.textsize"
     */
    function testLOBStorage() {
        if (!$this->supported('LOBs')) {
            return;
        }

        $query = 'INSERT INTO files (ID, document, picture) VALUES (1, ?, ?)';
        $stmt = $this->db->prepare($query, array('clob', 'blob'), MDB2_PREPARE_MANIP, array('document', 'picture'));
        if (PEAR::isError($stmt)) {
            $this->assertTrue(false, 'Failed prepared statement to insert LOB values: '.$stmt->getUserInfo());
        }

        $character_lob = '';
        $binary_lob = '';

        for ($i = 0; $i < 1000; $i++) {
            for ($code = 32; $code <= 127; $code++) {
                $character_lob.= chr($code);
            }
            for ($code = 0; $code <= 255; $code++) {
                $binary_lob.= chr($code);
            }
        }

        $stmt->bindValue(0, $character_lob);
        $stmt->bindValue(1, $binary_lob);

        $result = $stmt->execute();

        if (PEAR::isError($result)) {
            $this->assertTrue(false, 'Error executing prepared query: '.$result->getUserInfo());
        }

        $stmt->free();

        $result =& $this->db->query('SELECT document, picture FROM files WHERE id = 1', array('clob', 'blob'));
        if (PEAR::isError($result)) {
            $this->assertTrue(false, 'Error selecting from files'.$result->getMessage());
        }

        $this->assertTrue($result->valid(), 'The query result seem to have reached the end of result too soon.');

        $row = $result->fetchRow();
        $clob = $row[0];
        if (!PEAR::isError($clob) && is_resource($clob)) {
            $value = '';
            while (!feof($clob)) {
                $data = fread($clob, 8192);
                $this->assertTrue(strlen($data) >= 0, 'Could not read CLOB');
                $value.= $data;
            }
            $this->db->datatype->destroyLOB($clob);
            $this->assertEquals($character_lob, $value, 'Retrieved character LOB value is different from what was stored');
        } else {
            $this->assertTrue(false, 'Error retrieving CLOB result');
        }

        $blob = $row[1];
        if (!PEAR::isError($blob) && is_resource($blob)) {
            $value = '';
            while (!feof($blob)) {
                $data = fread($blob, 8192);
                $this->assertTrue(strlen($data) >= 0, 'Could not read BLOB');
                $value.= $data;
            }

            $this->db->datatype->destroyLOB($blob);
            $this->assertEquals($binary_lob, $value, 'Retrieved binary LOB value is different from what was stored');
        } else {
            $this->assertTrue(false, 'Error retrieving BLOB result');
        }
        $result->free();
    }

    /**
     * Test LOB reading of multiple records both buffered and unbuffered. See bug #8793 for why this must be tested.
     */
    function testLOBRead() {
        if (!$this->supported('LOBs')) {
            return;
        }

        for ($i = 20; $i < 30; ++$i) {
            $query = 'INSERT INTO files (ID, document, picture) VALUES (?, ?, ?)';
            $stmt = $this->db->prepare($query, array('integer', 'clob', 'blob'), MDB2_PREPARE_MANIP, array(1 => 'document', 2 => 'picture'));
            $character_lob = $binary_lob = $i;
            $stmt->bindValue(1, $character_lob);
            $stmt->bindValue(2, $binary_lob);

            $result = $stmt->execute(array($i));

            if (PEAR::isError($result)) {
                $this->assertTrue(false, 'Error executing prepared query: '.$result->getUserInfo());
            }
            $stmt->free();
        }

        $oldBuffered = $this->db->getOption('result_buffering');
        foreach (array(true, false) as $buffered) {
            $this->db->setOption('result_buffering', $buffered);
            $msgPost = ' with result_buffering = '.($buffered ? 'true' : 'false');
            $result =& $this->db->query('SELECT id, document, picture FROM files WHERE id >= 20 AND id <= 30 ORDER BY id ASC', array('integer', 'clob', 'blob'));
            if (PEAR::isError($result)) {
                $this->assertTrue(false, 'Error selecting from files'.$msgPost.$result->getMessage());
            } else {
                if ($buffered) {
                    $this->assertTrue($result->valid(), 'The query result seem to have reached the end of result too soon'.$msgPost);
                    $this->assertEquals('mdb2_bufferedresult_', strtolower(substr(get_class($result), 0, 20)), 'Error: not a buffered result');
                } else {
                    $this->assertEquals('mdb2_result_', strtolower(substr(get_class($result), 0, 12)), 'Error: an unbuffered result was expected');
                }
                for ($i = 1; $i <= ($buffered ? 2 : 1); ++$i) {
                    $result->seek(0);
                    while ($row = $result->fetchRow(MDB2_FETCHMODE_ASSOC)) {
                        foreach (array('document' => 'clob', 'picture' => 'blob') as $field => $type) {
                            $lob = $row[$field];
                            if (is_a($lob, 'oci-lob')) {
                                $lob = $lob->load();
                            } elseif (is_resource($lob)) {
                                $lob = fread($lob, 1000);
                            }
                            $this->assertEquals($lob, $row['id'], 'LOB ('.$type.') field ('.$field.') not equal to expected value ('.$row['id'].')'.$msgPost.' on run-through '.$i);
                        }
                    }
                }
                $result->free();
            }
        }
        $this->db->setOption('result_buffering', $oldBuffered);
    }

    /**
     * Test for lob storage from and to files
     */
    function testLOBFiles() {
        if (!$this->supported('LOBs')) {
            return;
        }

        $query = 'INSERT INTO files (ID, document, picture) VALUES (1, :document, :picture)';
        $stmt = $this->db->prepare($query, array('document' => 'clob', 'picture' => 'blob'), MDB2_PREPARE_MANIP);

        $character_data_file = 'character_data';
        $file = fopen($character_data_file, 'w');
        $this->assertTrue(((bool)$file), 'Error creating clob file to read from');
        $character_data = '';
        for ($i = 0; $i < 1000; $i++) {
            for ($code = 32; $code <= 127; $code++) {
                $character_data.= chr($code);
            }
        }
        $this->assertTrue((fwrite($file, $character_data, strlen($character_data)) == strlen($character_data)), 'Error creating clob file to read from');
        fclose($file);

        $binary_data_file = 'binary_data';
        $file = fopen($binary_data_file, 'wb');
        $this->assertTrue(((bool)$file), 'Error creating blob file to read from');
        $binary_data = '';
        for ($i = 0; $i < 1000; $i++) {
            for ($code = 0; $code <= 255; $code++) {
                $binary_data.= chr($code);
            }
        }
        $this->assertTrue((fwrite($file, $binary_data, strlen($binary_data)) == strlen($binary_data)), 'Error creating blob file to read from');
        fclose($file);

        $character_data_file_tmp = 'file://'.$character_data_file;
        $stmt->bindParam('document', $character_data_file_tmp);
        $binary_data_file_tmp = 'file://'.$binary_data_file;
        $stmt->bindParam('picture', $binary_data_file_tmp);

        $result = $stmt->execute();
        $this->assertTrue(!PEAR::isError($result), 'Error executing prepared query - inserting LOB from files');

        $stmt->free();

        $result =& $this->db->query('SELECT document, picture FROM files WHERE id = 1', array('clob', 'blob'));
        if (PEAR::isError($result)) {
            $this->assertTrue(false, 'Error selecting from files'.$result->getMessage());
        }

        $this->assertTrue($result->valid(), 'The query result seem to have reached the end of result too soon.');

        $row = $result->fetchRow();
        $clob = $row[0];
        if (!PEAR::isError($clob) && is_resource($clob)) {
            unlink($character_data_file);
            $res = $this->db->datatype->writeLOBToFile($clob, $character_data_file);
            $this->db->datatype->destroyLOB($clob);

            if (PEAR::isError($res)) {
                $this->assertTrue(false, 'Error writing character LOB in a file');
            } else {
                $file = fopen($character_data_file, 'r');
                $this->assertTrue($file, "Error opening character data file: $character_data_file");
                $value = '';
                while (!feof($file)) {
                    $value.= fread($file, 8192);
                }
                $this->assertEquals('string', gettype($value), "Could not read from character LOB file: $character_data_file");
                fclose($file);

                $this->assertEquals($character_data, $value, "retrieved character LOB value is different from what was stored");
            }
        } else {
            $this->assertTrue(false, 'Error creating character LOB in a file');
        }

        $blob = $row[1];
        if (!PEAR::isError($blob) && is_resource($blob)) {
            unlink($binary_data_file);
            $res = $this->db->datatype->writeLOBToFile($blob, $binary_data_file);
            $this->db->datatype->destroyLOB($blob);

            if (PEAR::isError($res)) {
                $this->assertTrue(false, 'Error writing binary LOB in a file');
            } else {
                $file = fopen($binary_data_file, 'rb');
                $this->assertTrue($file, "Error opening binary data file: $binary_data_file");
                $value = '';
                while (!feof($file)) {
                    $value.= fread($file, 8192);
                }
                $this->assertEquals('string', gettype($value), "Could not read from binary LOB file: $binary_data_file");
                fclose($file);

                $this->assertEquals($binary_data, $value, "retrieved binary LOB value is different from what was stored");
            }
        } else {
            $this->assertTrue(false, 'Error creating binary LOB in a file');
        }

        $result->free();
    }

    /**
     * Test handling of lob nulls
     */
    function testLOBNulls() {
        if (!$this->supported('LOBs')) {
            return;
        }

        $query = 'INSERT INTO files (ID, document, picture) VALUES (1, :document, :picture)';
        $stmt = $this->db->prepare($query, array('document' => 'clob', 'picture' => 'blob'), MDB2_PREPARE_MANIP);

        $null = null;
        $stmt->bindParam('document', $null);
        $stmt->bindParam('picture', $null);

        $result = $stmt->execute();
        $this->assertTrue(!PEAR::isError($result), 'Error executing prepared query - inserting NULL lobs');

        $stmt->free();

        $result =& $this->db->query('SELECT document, picture FROM files', array('clob', 'blob'));
        if (PEAR::isError($result)) {
            $this->assertTrue(false, 'Error selecting from files'.$result->getMessage());
        }

        $this->assertTrue($result->valid(), 'The query result seem to have reached the end of result too soon.');

        $row = $result->fetchRow(MDB2_FETCHMODE_ASSOC);
        $this->assertTrue(is_null($row['document']), 'A query result large object column document is not NULL unlike what was expected');
        $this->assertTrue(is_null($row['picture']), 'A query result large object column picture is not NULL unlike what was expected');

        $result->free();
    }

    function testLOBUpdate() {
        if (!$this->supported('LOBs')) {
            return;
        }

        $query = 'INSERT INTO files (ID, document, picture) VALUES (1, ?, ?)';
        $stmt = $this->db->prepare($query, array('clob', 'blob'), MDB2_PREPARE_MANIP, array('document', 'picture'));

        $character_lob = '';
        $binary_lob = '';

        for ($i = 0; $i < 1000; $i++) {
            for ($code = 32; $code <= 127; ++$code) {
                $character_lob .= chr($code);
            }
            for ($code = 0; $code <= 255; ++$code) {
                $binary_lob .= chr($code);
            }
        }

        $stmt->bindValue(0, $character_lob);
        $stmt->bindValue(1, $binary_lob);

        $result = $stmt->execute();

        if (PEAR::isError($result)) {
            $this->assertTrue(false, 'Error executing prepared query: '.$result->getUserInfo());
        }

        $stmt->free();

        $query = 'UPDATE files SET document = ?, picture = ? WHERE ID = 1';
        $stmt = $this->db->prepare($query, array('clob', 'blob'), MDB2_PREPARE_MANIP, array('document', 'picture'));

        $character_lob = '';
        $binary_lob = '';

        for ($i = 0; $i < 999; $i++) {
            for ($code = 127; $code >= 32; --$code) {
                $character_lob .= chr($code);
            }
            for ($code = 255; $code >= 0; --$code) {
                $binary_lob .= chr($code);
            }
        }

        $stmt->bindValue(0, $character_lob);
        $stmt->bindValue(1, $binary_lob);

        $result = $stmt->execute();

        if (PEAR::isError($result)) {
            $this->assertTrue(false, 'Error executing prepared query: '.$result->getUserInfo());
        }

        $stmt->free();

        $result =& $this->db->query('SELECT document, picture FROM files WHERE id = 1', array('clob', 'blob'));
        if (PEAR::isError($result)) {
            $this->assertTrue(false, 'Error selecting from files'.$result->getMessage());
        }

        $this->assertTrue($result->valid(), 'The query result seem to have reached the end of result too soon.');

        $row = $result->fetchRow();
        $clob = $row[0];
        if (!PEAR::isError($clob) && is_resource($clob)) {
            $value = '';
            while (!feof($clob)) {
                $data = fread($clob, 8192);
                $this->assertTrue(strlen($data) >= 0, 'Could not read CLOB');
                $value.= $data;
            }
            $this->db->datatype->destroyLOB($clob);
            $this->assertEquals($character_lob, $value, 'Retrieved character LOB value is different from what was stored');
        } else {
            $this->assertTrue(false, 'Error retrieving CLOB result');
        }

        $blob = $row[1];
        if (!PEAR::isError($blob) && is_resource($blob)) {
            $value = '';
            while (!feof($blob)) {
                $data = fread($blob, 8192);
                $this->assertTrue(strlen($data) >= 0, 'Could not read BLOB');
                $value.= $data;
            }

            $this->db->datatype->destroyLOB($blob);
            $this->assertEquals($binary_lob, $value, 'Retrieved binary LOB value is different from what was stored');
        } else {
            $this->assertTrue(false, 'Error retrieving BLOB result');
        }
        $result->free();
    }

    /**
     * Test retrieval of result metadata
     *
     * This tests the result metadata by executing a prepared query and
     * select the data, and checking the result contains the correct
     * number of columns and that the column names are in the correct order
     */
    function testConvertEmpty2Null() {
#$this->db->setOption('portability', MDB2_PORTABILITY_ALL ^ MDB2_PORTABILITY_EMPTY_TO_NULL);

        $data = $this->getSampleData(1234);
        $data['user_password'] = '';

        $query = 'INSERT INTO users (' . implode(', ', array_keys($this->fields)) . ') VALUES ('.implode(', ', array_fill(0, count($this->fields), '?')).')';
        $stmt = $this->db->prepare($query, array_values($this->fields), MDB2_PREPARE_MANIP);
        $result = $stmt->execute(array_values($data));
        $stmt->free();

        if (PEAR::isError($result)) {
            $this->assertTrue(false, 'Error executing prepared query: '.$result->getMessage());
        }

        $row = $this->db->queryRow('SELECT ' . implode(', ', array_keys($this->fields)) . ' FROM users WHERE user_password IS NULL', $this->fields);

        if (PEAR::isError($row)) {
            $this->assertTrue(false, 'Error selecting from users'.$result->getMessage());
        }

        $this->assertEquals(count($this->fields), count($row), "The query result returned a number of columns unlike ".count($this->fields) .' as expected');
    }

    function testPortabilityOptions() {
        // MDB2_PORTABILITY_DELETE_COUNT
        $data = array();
        $total_rows = 5;

        $query = 'INSERT INTO users (' . implode(', ', array_keys($this->fields)) . ') VALUES ('.implode(', ', array_fill(0, count($this->fields), '?')).')';
        $stmt = $this->db->prepare($query, array_values($this->fields), MDB2_PREPARE_MANIP);
        for ($row = 0; $row < $total_rows; $row++) {
            $data[$row] = $this->getSampleData($row);
            $result = $stmt->execute(array_values($data[$row]));
            if (PEAR::isError($result)) {
                $this->assertTrue(false, 'Error executing prepared query: '.$result->getMessage());
            }
        }
        $stmt->free();

        $this->db->setOption('portability', MDB2_PORTABILITY_NONE | MDB2_PORTABILITY_DELETE_COUNT);
        $affected_rows = $this->db->exec('DELETE FROM users');
        if (PEAR::isError($affected_rows)) {
            $this->assertTrue(false, 'Error executing query: '.$affected_rows->getMessage());
        }
        $this->assertEquals($total_rows, $affected_rows, 'MDB2_PORTABILITY_DELETE_COUNT not working');

        // MDB2_PORTABILITY_FIX_CASE
        $fields = array_keys($this->fields);
        $this->db->setOption('portability', MDB2_PORTABILITY_NONE | MDB2_PORTABILITY_FIX_CASE);
        $this->db->setOption('field_case', CASE_UPPER);

        $data = $this->getSampleData(1234);
        $query = 'INSERT INTO users (' . implode(', ', array_keys($this->fields)) . ') VALUES ('.implode(', ', array_fill(0, count($this->fields), '?')).')';
        $stmt = $this->db->prepare($query, array_values($this->fields), MDB2_PREPARE_MANIP);
        $result = $stmt->execute(array_values($data));
        $stmt->free();

        $query = 'SELECT ' . implode(', ', array_keys($this->fields)) . ' FROM users';
        $result =& $this->db->queryRow($query, $this->fields, MDB2_FETCHMODE_ASSOC);
        if (PEAR::isError($result)) {
            $this->assertTrue(false, 'Error selecting from users'.$result->getMessage());
        }
        $field = reset($fields);
        foreach (array_keys($result) as $fieldname) {
            $this->assertEquals(strtoupper($field), $fieldname, 'MDB2_PORTABILITY_FIX_CASE CASE_UPPER not working');
            $field = next($fields);
        }

        $this->db->setOption('field_case', CASE_LOWER);
        $query = 'SELECT ' . implode(', ', array_keys($this->fields)) . ' FROM users';
        $result =& $this->db->queryRow($query, $this->fields, MDB2_FETCHMODE_ASSOC);
        if (PEAR::isError($result)) {
            $this->assertTrue(false, 'Error selecting from users'.$result->getMessage());
        }
        $field = reset($fields);
        foreach (array_keys($result) as $fieldname) {
            $this->assertEquals(strtolower($field), $fieldname, 'MDB2_PORTABILITY_FIX_CASE CASE_LOWER not working');
            $field = next($fields);
        }

        // MDB2_PORTABILITY_RTRIM
        $this->db->setOption('portability', MDB2_PORTABILITY_NONE | MDB2_PORTABILITY_RTRIM);
        $value = 'rtrim   ';
        $query = 'INSERT INTO users (user_id, user_password) VALUES (1, ' . $this->db->quote($value, 'text') .')';
        $res = $this->db->exec($query);
        if (PEAR::isError($res)) {
            $this->assertTrue(false, 'Error executing query: '.$res->getMessage());
        }
        $query = 'SELECT user_password FROM users WHERE user_id = 1';
        $result = $this->db->queryOne($query, array('text'));
        if (PEAR::isError($result)) {
            $this->assertTrue(false, 'Error selecting from users: '.$result->getMessage());
        }
        $this->assertEquals(rtrim($value), $result, '"MDB2_PORTABILITY_RTRIM = on" not working');

        $this->db->setOption('portability', MDB2_PORTABILITY_NONE | MDB2_PORTABILITY_RTRIM);
        $value = ' ';
        $query = 'INSERT INTO users (user_id, user_name) VALUES (2, ' . $this->db->quote($value, 'text') .')';
        $res = $this->db->exec($query);
        if (PEAR::isError($res)) {
            $this->assertTrue(false, 'Error executing query: '.$res->getMessage());
        }
        $query = 'SELECT user_name FROM users WHERE user_id = 2';
        $result = $this->db->queryOne($query, array('text'));
        if (PEAR::isError($result)) {
            $this->assertTrue(false, 'Error selecting from users: '.$result->getMessage());
        }
        $this->assertEquals(0, strlen($result), '"MDB2_PORTABILITY_RTRIM = on" not working');

        if (!$this->supported('LOBs')) {
            return;
        }

        $query = 'INSERT INTO files (ID, document, picture) VALUES (1, ?, ?)';
        $stmt = $this->db->prepare($query, array('clob', 'blob'), MDB2_PREPARE_MANIP, array('document', 'picture'));

        $character_lob = '';
        $binary_lob = '';

        for ($i = 0; $i < 999; $i++) {
            for ($code = 127; $code >= 32; --$code) {
                $character_lob .= chr($code);
            }
            for ($code = 255; $code >= 0; --$code) {
                $binary_lob .= chr($code);
            }
        }

        $stmt->bindValue(0, $character_lob);
        $stmt->bindValue(1, $binary_lob);

        $result = $stmt->execute();

        if (PEAR::isError($result)) {
            $this->assertTrue(false, 'Error executing prepared query: '.$result->getUserInfo());
        }

        $stmt->free();

        $result =& $this->db->query('SELECT document, picture FROM files WHERE id = 1', array('clob', 'blob'));
        if (PEAR::isError($result)) {
            $this->assertTrue(false, 'Error selecting from files'.$result->getMessage());
        }

        $this->assertTrue($result->valid(), 'The query result seem to have reached the end of result too soon.');

        $row = $result->fetchRow();
        $clob = $row[0];
        if (!PEAR::isError($clob) && is_resource($clob)) {
            $value = '';
            while (!feof($clob)) {
                $data = fread($clob, 8192);
                $this->assertTrue(strlen($data) >= 0, 'Could not read CLOB');
                $value.= $data;
            }
            $this->db->datatype->destroyLOB($clob);
            $this->assertEquals($character_lob, $value, '"MDB2_PORTABILITY_RTRIM = on" Retrieved character LOB value is different from what was stored');
        } else {
            $this->assertTrue(false, 'Error retrieving CLOB result');
        }

        $blob = $row[1];
        if (!PEAR::isError($blob) && is_resource($blob)) {
            $value = '';
            while (!feof($blob)) {
                $data = fread($blob, 8192);
                $this->assertTrue(strlen($data) >= 0, 'Could not read BLOB');
                $value.= $data;
            }

            $this->db->datatype->destroyLOB($blob);
            $this->assertEquals($binary_lob, $value, '"MDB2_PORTABILITY_RTRIM = on" Retrieved binary LOB value is different from what was stored');
        } else {
            $this->assertTrue(false, 'Error retrieving BLOB result');
        }
        $result->free();
    }

    /**
     * Test getAsKeyword()
     */
    function testgetAsKeyword()
    {
        $query = 'INSERT INTO users (' . implode(', ', array_keys($this->fields)) . ') VALUES ('.implode(', ', array_fill(0, count($this->fields), '?')).')';
        $stmt = $this->db->prepare($query, array_values($this->fields), MDB2_PREPARE_MANIP);
        $data = $this->getSampleData(1);
        $result = $stmt->execute(array_values($data));
        if (PEAR::isError($result)) {
            $this->assertTrue(false, 'Error executing prepared query: '.$result->getMessage());
        }
        $stmt->free();

        $query = 'SELECT user_id'.$this->db->getAsKeyword().'foo FROM users';
        $result = $this->db->queryRow($query, array('integer'), MDB2_FETCHMODE_ASSOC);
        if (PEAR::isError($result)) {
            $this->assertFalse(true, 'Error getting alias column: '. $result->getMessage());
        } else {
            $this->assertTrue((array_key_exists('foo', $result)), 'Error: could not alias "user_id" with "foo" : '.var_export($result, true));
        }
    }
}

?>

redpajama_set_name: RedPajamaGithub


text:
"use strict";

let fs = require("fs");
let { exec } = require("child_process");

let run = function(command) {
  return new Promise((resolve, reject) => {
    exec(command, (err, stdout, stderr) => {
      if (err != null) reject(new Error(stderr));
      resolve(stdout);
    });
  });
};

let copy = function(source, target) {
  return new Promise((resolve, reject) => {
    fs.access(source, fs.F_OK | fs.R_OK, (err) => {
      if (err != null) reject(err);
      let rs = fs.createReadStream(source);
      rs.on("error", reject);
      let ws = fs.createWriteStream(target);
      ws.on("error", reject);
      ws.on("finish", resolve);
      rs.pipe(ws);
    });
  });
};

console.log("Browserifying game.js ...");
run("browserify --ignore lapack src/client/Game.js > public/game.js")
  .then(() => {
    console.log("Job done!");
  })
  .catch(err => {
    console.error(err.message);
    process.exit(1);
  });


text:
<?php

namespace Alcon\Traits;

/**
 * Trait for Model advanced setting.
 *
 * For Phalcon framework.
 *
 * @farwish
 */
trait ModelAdvanceTrait
{

    public function initialize()
    {
        $this->setReadConnectionService('dbslave');
        $this->setWriteConnectionService('dbmaster');
    }
}