1 module dpq2.query;
2 
3 @trusted:
4 
5 import dpq2;
6 
7 enum ValueFormat : ubyte {
8     TEXT,
9     BINARY
10 }
11 
12 /// Query parameters
13 struct QueryParams
14 {
15     string sqlCommand; /// SQL command
16     QueryArg[] args; /// SQL command arguments
17     ValueFormat resultFormat = ValueFormat.BINARY; /// Result value format
18 }
19 
20 /// Query argument
21 struct QueryArg
22 {
23     Oid type = 0;
24     private ubyte[] valueBin;
25     
26     /// s can be null for SQL NULL value
27     @property void value( string s )
28     {
29         if( s == null )
30             valueBin = null;
31         else
32             valueBin = cast(ubyte[])( s ~ '\0' );
33     }
34 }
35 
36 /// Connection
37 // Inheritance used here for separation of query code from connection code
38 final class Connection: BaseConnection
39 {
40     /// Perform SQL query to DB
41     immutable (Answer) exec( string SQLcmd )
42     {
43         auto pgResult = PQexec(conn, toStringz( SQLcmd ));
44 
45         // is guaranteed by libpq that the result will not be changed until it will not be destroyed
46         auto container = createResultContainer(cast(immutable) pgResult);
47 
48         return new immutable Answer(container);
49     }
50     
51     /// Perform SQL query to DB
52     immutable (Answer) exec(in QueryParams p)
53     {
54         auto a = prepareArgs( p );
55         auto pgResult = PQexecParams (
56                 conn,
57                 cast(const char*)toStringz( p.sqlCommand ),
58                 cast(int)p.args.length,
59                 a.types.ptr,
60                 a.values.ptr,
61                 cast(int*)a.lengths.ptr,
62                 cast(int*)a.formats.ptr,
63                 cast(int)p.resultFormat
64         );
65 
66         // is guaranteed by libpq that the result will not be changed until it will not be destroyed
67         auto container = createResultContainer(cast(immutable) pgResult);
68 
69         return new immutable Answer(container);
70     }
71     
72     /// Submits a command to the server without waiting for the result(s)
73     void sendQuery( string SQLcmd )
74     {
75         const size_t r = PQsendQuery( conn, toStringz(SQLcmd) );
76         if( r != 1 ) throw new ConnectionException(this, __FILE__, __LINE__);
77     }
78     
79     /// Submits a command and separate parameters to the server without waiting for the result(s)
80     void sendQuery( in QueryParams p )
81     {
82         auto a = prepareArgs( p );
83         size_t r = PQsendQueryParams (
84                     conn,
85                     cast(const char*)toStringz( p.sqlCommand ),
86                     cast(int)p.args.length,
87                     a.types.ptr,
88                     a.values.ptr,
89                     cast(int*)a.lengths.ptr,
90                     cast(int*)a.formats.ptr,
91                     cast(int)p.resultFormat                    
92                     );
93                     
94         if( !r ) throw new ConnectionException(this, __FILE__, __LINE__);
95     }
96 
97     /// Returns null if no notifies was received
98     Notify getNextNotify()
99     {
100         consumeInput();
101         auto n = PQnotifies(conn);
102         return n is null ? null : new Notify( n );
103     }
104     
105     private struct PreparedArgs
106     {
107         Oid[] types;
108         size_t[] formats;
109         size_t[] lengths;
110         const(ubyte)*[] values;
111     }
112     
113     // For PQxxxParams need especially prepared arguments
114     private PreparedArgs* prepareArgs(ref const QueryParams p)
115     {
116         PreparedArgs* a = new PreparedArgs;
117         a.types = new Oid[p.args.length];
118         a.formats = new size_t[p.args.length];
119         a.lengths = new size_t[p.args.length];
120         a.values = new const(ubyte)*[p.args.length];
121         
122         for( int i = 0; i < p.args.length; ++i )
123         {
124             a.types[i] = p.args[i].type;
125             a.formats[i] = ValueFormat.TEXT;
126             a.values[i] = p.args[i].valueBin.ptr;
127             a.lengths[i] = p.args[i].valueBin.length;
128         }
129         
130         return a;
131     }
132 }
133 
134 void _integration_test( string connParam )
135 {
136     auto conn = new Connection;
137 	conn.connString = connParam;
138     conn.connect();
139 
140     {    
141         string sql_query =
142         "select now() as time, 'abc'::text as string, 123, 456.78\n"~
143         "union all\n"~
144         "select now(), 'абвгд'::text, 777, 910.11\n"~
145         "union all\n"~
146         "select NULL, 'ijk'::text, 789, 12345.115345";
147 
148         auto a = conn.exec( sql_query );
149 
150         assert( a.cmdStatus.length > 2 );
151         assert( a.columnCount == 4 );
152         assert( a.length == 3 );
153         assert( a.columnFormat(1) == ValueFormat.TEXT );
154         assert( a.columnFormat(2) == ValueFormat.TEXT );
155     }
156 
157     {
158         const string sql_query =
159         "select $1::text, $2::integer, $3::text";
160 
161         QueryArg[3] args;
162         args[0].value = "абвгд";
163         args[1].value = null;
164         args[2].value = "123";
165 
166         QueryParams p;
167         p.sqlCommand = sql_query;
168         p.args = args[];
169 
170         auto a = conn.exec( p );
171 
172         assert( a.columnFormat(1) == ValueFormat.BINARY );
173         assert( a.columnFormat(2) == ValueFormat.BINARY );
174 
175         destroy(a);
176     }
177 
178     conn.disconnect();
179 
180     {
181         bool exceptionFlag = false;
182 
183         try conn.exec("SELECT 'abc'::text").getAnswer;
184         catch(ConnectionException e)
185         {
186             exceptionFlag = true;
187             assert(e.msg.length > 15); // error message check
188         }
189         finally
190             assert(exceptionFlag);
191     }
192 }