summaryrefslogtreecommitdiff
path: root/program/include/rcube_db_sqlsrv.php
blob: 4ea7e73185943a8d287d2eb780910d8e16e3802f (plain)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
<?php

/*
 +-----------------------------------------------------------------------+
 | program/include/rcube_db_sqlsrv.php                                   |
 |                                                                       |
 | This file is part of the Roundcube Webmail client                     |
 | Copyright (C) 2005-2012, The Roundcube Dev Team                       |
 |                                                                       |
 | Licensed under the GNU General Public License version 3 or            |
 | any later version with exceptions for skins & plugins.                |
 | See the README file for a full license statement.                     |
 |                                                                       |
 | PURPOSE:                                                              |
 |   Database wrapper class that implements PHP PDO functions            |
 |   for MS SQL Server database                                          |
 |                                                                       |
 +-----------------------------------------------------------------------+
 | Author: Aleksander Machniak <alec@alec.pl>                            |
 +-----------------------------------------------------------------------+
*/


/**
 * Database independent query interface
 *
 * This is a wrapper for the PHP PDO
 *
 * @package    Database
 * @version    1.0
 */
class rcube_db_sqlsrv extends rcube_db
{
    protected function init()
    {
        $this->options['identifier_start'] = '[';
        $this->options['identifier_end'] = ']';
    }

    protected function set_charset($charset)
    {
    }


    /**
     * Return SQL function for current time and date
     *
     * @return string SQL function to use in query
     */
    public function now()
    {
        return "getdate()";
    }


    /**
     * Return SQL statement to convert a field value into a unix timestamp
     *
     * This method is deprecated and should not be used anymore due to limitations
     * of timestamp functions in Mysql (year 2038 problem)
     *
     * @param  string $field Field name
     *
     * @return string  SQL statement to use in query
     * @deprecated
     */
    public function unixtimestamp($field)
    {
        return "DATEDIFF(second, '19700101', $field) + DATEDIFF(second, GETDATE(), GETUTCDATE())";
    }


    /**
     * Abstract SQL statement for value concatenation
     *
     * @return string SQL statement to be used in query
     */
    public function concat(/* col1, col2, ... */)
    {
        $args = func_get_args();

        if (is_array($args[0])) {
            $args = $args[0];
        }

        return '(' . join('+', $args) . ')';
    }


    /**
     * Adds TOP (LIMIT,OFFSET) clause to the query
     *
     */
    protected function set_limit($query, $limit = 0, $offset = 0)
    {
        $limit  = intval($limit);
        $offset = intval($offset);

        $orderby = stristr($query, 'ORDER BY');
        if ($orderby !== false) {
            $sort  = (stripos($orderby, ' desc') !== false) ? 'desc' : 'asc';
            $order = str_ireplace('ORDER BY', '', $orderby);
            $order = trim(preg_replace('/\bASC\b|\bDESC\b/i', '', $order));
        }

        $query = preg_replace('/^SELECT\s/i', 'SELECT TOP ' . ($limit + $offset) . ' ', $query);

        $query = 'SELECT * FROM (SELECT TOP ' . $limit . ' * FROM (' . $query . ') AS inner_tbl';
        if ($orderby !== false) {
            $query .= ' ORDER BY ' . $order . ' ';
            $query .= (stripos($sort, 'asc') !== false) ? 'DESC' : 'ASC';
        }
        $query .= ') AS outer_tbl';
        if ($orderby !== false) {
            $query .= ' ORDER BY ' . $order . ' ' . $sort;
        }

        return $query;
    }

    /**
     * Returns PDO DSN string from DSN array (parse_dsn() result)
     */
    protected function dsn_string($dsn)
    {
        $params = array();
        $result = 'sqlsrv:';

        if ($dsn['hostspec']) {
            $host = $dsn['hostspec'];

            if ($dsn['port']) {
                $host .= ',' . $dsn['port'];
            }
            $params[] = 'Server=' . $host;
        }

        if ($dsn['database']) {
            $params[] = 'Database=' . $dsn['database'];
        }

        if (!empty($params)) {
            $result .= implode(';', $params);
        }

        return $result;
    }
}