1 /*
2 
3   S M S D
4 
5   A Linux/Unix tool for the mobile phones.
6 
7   This file is part of gnokii.
8 
9   Gnokii is free software; you can redistribute it and/or modify
10   it under the terms of the GNU General Public License as published by
11   the Free Software Foundation; either version 2 of the License, or
12   (at your option) any later version.
13 
14   Gnokii is distributed in the hope that it will be useful,
15   but WITHOUT ANY WARRANTY; without even the implied warranty of
16   MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
17   GNU General Public License for more details.
18 
19   You should have received a copy of the GNU General Public License
20   along with gnokii; if not, write to the Free Software
21   Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA
22 
23   Copyright (C) 1999 Pavel Jan�k ml., Hugh Blemings
24   Copyright (C) 1999-2011 Jan Derfinak
25 
26   This file is a module to smsd for PostgreSQL db server.
27 
28 */
29 
30 #include "config.h"
31 #include <string.h>
32 #include <stdlib.h>
33 #include <glib.h>
34 #include <libpq-fe.h>
35 #include "smsd.h"
36 #include "gnokii.h"
37 #include "compat.h"
38 //#include "utils.h"
39 
40 static PGconn *connIn = NULL;
41 static PGconn *connOut = NULL;
42 static gchar *schema = NULL;		/* database schema */
43 
DB_Bye(void)44 GNOKII_API void DB_Bye (void)
45 {
46   if (connIn)
47     PQfinish (connIn);
48 
49   if (connOut)
50     PQfinish (connOut);
51 }
52 
53 
Connect(const DBConfig connect,PGconn ** conn)54 static gint Connect (const DBConfig connect, PGconn **conn)
55 {
56   *conn = PQsetdbLogin (connect.host[0] != '\0' ? connect.host : NULL,
57                          NULL,
58                          NULL,
59                          NULL,
60                          connect.db,
61                          connect.user[0] != '\0' ? connect.user : NULL,
62                          connect.password[0] != '\0' ? connect.password : NULL);
63 
64   if (PQstatus (*conn) == CONNECTION_BAD)
65   {
66     g_print (_("Connection to database '%s' on host '%s' failed.\n"),
67              connect.db, connect.host);
68     g_print (_("Error: %s\n"), PQerrorMessage (*conn));
69     return (SMSD_NOK);
70   }
71 
72   if (connect.clientEncoding[0] != '\0')
73     if (PQsetClientEncoding (*conn, connect.clientEncoding))
74     {
75       g_print (_("Setting client charset '%s' for database '%s' on host '%s' failed.\n"),
76                connect.clientEncoding, connect.db, connect.host);
77       g_print (_("Error: %s\n"), PQerrorMessage (*conn));
78     }
79 
80   if (schema == NULL)
81     schema = g_strdup (connect.schema);
82 
83   return (SMSD_OK);
84 }
85 
86 
DB_ConnectInbox(const DBConfig connect)87 GNOKII_API gint DB_ConnectInbox (const DBConfig connect)
88 {
89   return (Connect (connect, &connIn));
90 }
91 
92 
DB_ConnectOutbox(const DBConfig connect)93 GNOKII_API gint DB_ConnectOutbox (const DBConfig connect)
94 {
95   return (Connect (connect, &connOut));
96 }
97 
98 
DB_InsertSMS(const gn_sms * const data,const gchar * const phone)99 GNOKII_API gint DB_InsertSMS (const gn_sms * const data, const gchar * const phone)
100 {
101   GString *buf, *phnStr;
102   gchar *text;
103   PGresult *res;
104   gint err;
105 
106 
107   if (phone[0] == '\0')
108     phnStr = g_string_new ("");
109   else
110   {
111     phnStr = g_string_sized_new (32);
112     g_string_printf (phnStr, "'%s',", phone);
113   }
114 
115   text = g_malloc (strlen ((gchar *)data->user_data[0].u.text) * 2 + 1);
116   PQescapeStringConn (connIn, text, (gchar *) data->user_data[0].u.text,
117                       strlen ((gchar *)data->user_data[0].u.text), &err);
118   if (err)
119   {
120     g_print (_("%d: Escaping string failed.\n"), __LINE__);
121     g_print (_("Error: %s\n"), PQerrorMessage (connIn));
122     g_string_free (phnStr, TRUE);
123     g_free (text);
124     return (SMSD_NOK);
125   }
126 
127 //  text = strEscape ((gchar *) data->user_data[0].u.text);
128   buf = g_string_sized_new (256);
129 
130   if (data->udh.udh[0].type == GN_SMS_UDH_ConcatenatedMessages)
131   { // Multipart Message !
132     gn_log_xdebug ("Multipart message\n");
133     /* Check for duplicates */
134     g_string_printf (buf, "SELECT count(id) FROM %s.multipartinbox \
135                            WHERE number = '%s' AND text = '%s' AND \
136                            refnum = %d AND maxnum = %d AND curnum = %d AND \
137                            smsdate = '%04d-%02d-%02d %02d:%02d:%02d'",
138                      schema, data->remote.number, text,
139                      data->udh.udh[0].u.concatenated_short_message.reference_number,
140                      data->udh.udh[0].u.concatenated_short_message.maximum_number,
141                      data->udh.udh[0].u.concatenated_short_message.current_number,
142                      data->smsc_time.year, data->smsc_time.month,
143                      data->smsc_time.day, data->smsc_time.hour,
144                      data->smsc_time.minute, data->smsc_time.second);
145     res = PQexec (connIn, buf->str);
146     if (!res || PQresultStatus (res) != PGRES_TUPLES_OK)
147     {
148       g_print (_("%d: SELECT FROM %s.multipart command failed.\n"), __LINE__, schema);
149       gn_log_xdebug ("%s\n", buf->str);
150       g_print (_("Error: %s\n"), PQerrorMessage (connIn));
151       PQclear (res);
152       g_string_free (buf, TRUE);
153       g_string_free (phnStr, TRUE);
154       g_free (text);
155       return (SMSD_NOK);
156     }
157 
158     if (atoi (PQgetvalue (res, 0, 0)) > 0)
159     {
160       gn_log_xdebug ("%d: SMS already stored in the database (refnum=%d, maxnum=%d, curnum=%d).\n", __LINE__,
161                      data->udh.udh[0].u.concatenated_short_message.reference_number,
162                      data->udh.udh[0].u.concatenated_short_message.maximum_number,
163                      data->udh.udh[0].u.concatenated_short_message.current_number);
164       PQclear (res);
165       g_string_free (buf, TRUE);
166       g_string_free (phnStr, TRUE);
167       g_free (text);
168       return (SMSD_DUPLICATE);
169     }
170 
171     PQclear (res);
172 
173     /* insert into multipart */
174     g_string_printf (buf, "INSERT INTO %s.multipartinbox (number, smsdate, \
175                            text, refnum , maxnum , curnum, %s processed) VALUES ('%s', \
176                            '%04d-%02d-%02d %02d:%02d:%02d', '%s', %d, %d, %d, %s '0')",
177                      schema,
178                      phone[0] != '\0' ? "phone," : "", data->remote.number,
179                      data->smsc_time.year, data->smsc_time.month,
180                      data->smsc_time.day, data->smsc_time.hour,
181                      data->smsc_time.minute, data->smsc_time.second, text,
182                      data->udh.udh[0].u.concatenated_short_message.reference_number,
183                      data->udh.udh[0].u.concatenated_short_message.maximum_number,
184                      data->udh.udh[0].u.concatenated_short_message.current_number, phnStr->str);
185     res = PQexec (connIn, buf->str);
186     if (!res || PQresultStatus(res) != PGRES_COMMAND_OK)
187     {
188       g_print (_("%d: INSERT INTO %s.multipartinbox failed.\n"), __LINE__, schema);
189       gn_log_xdebug ("%s\n", buf->str);
190       g_print (_("Error: %s\n"), PQerrorMessage (connIn));
191       PQclear (res);
192       g_string_free (buf, TRUE);
193       g_string_free (phnStr, TRUE);
194       g_free (text);
195       return (SMSD_NOK);
196     }
197 
198     PQclear (res);
199 
200     /* If all parts are already in multipart inbox, move it into inbox */
201     g_string_printf (buf, "SELECT text FROM %s.multipartinbox \
202                            WHERE number='%s' AND refnum=%d AND maxnum=%d \
203                            ORDER BY curnum",
204                      schema, data->remote.number,
205                      data->udh.udh[0].u.concatenated_short_message.reference_number,
206                      data->udh.udh[0].u.concatenated_short_message.maximum_number);
207     res = PQexec (connIn, buf->str);
208     if (!res || PQresultStatus (res) != PGRES_TUPLES_OK)
209     {
210       g_print (_("%d: SELECT FROM %s.multipart command failed.\n"), __LINE__, schema);
211       gn_log_xdebug ("%s\n", buf->str);
212       g_print (_("Error: %s\n"), PQerrorMessage (connIn));
213       PQclear (res);
214       g_string_free (buf, TRUE);
215       g_string_free (phnStr, TRUE);
216       g_free (text);
217       return (SMSD_NOK);
218     }
219 
220     gn_log_xdebug ("maxnumber: %d - count: %d\n", data->udh.udh[0].u.concatenated_short_message.maximum_number,  PQntuples (res));
221     if (PQntuples (res) == data->udh.udh[0].u.concatenated_short_message.maximum_number ) /* all parts collected */
222     {
223       gint i;
224       GString *mbuf = g_string_sized_new (256);
225 
226       for (i = 0; i < PQntuples (res); i++)
227         g_string_append (mbuf, PQgetvalue (res, i, 0));
228 
229       PQclear (res);
230 
231       g_string_printf(buf, "DELETE from %s.multipartinbox \
232                             WHERE number='%s' AND refnum=%d AND maxnum=%d",
233                       schema, data->remote.number,
234                       data->udh.udh[0].u.concatenated_short_message.reference_number,
235                       data->udh.udh[0].u.concatenated_short_message.maximum_number);
236       res = PQexec (connIn, buf->str);
237       if (!res || PQresultStatus (res) != PGRES_COMMAND_OK)
238       {
239         g_print (_("%d: DELETE FROM %s.multipartinbox command failed.\n"), __LINE__, schema);
240         gn_log_xdebug ("%s\n", buf->str);
241         g_print (_("Error: %s\n"), PQerrorMessage (connIn));
242         PQclear (res);
243         g_string_free (buf, TRUE);
244         g_string_free (mbuf, TRUE);
245         g_string_free (phnStr, TRUE);
246         g_free (text);
247         return (SMSD_NOK);
248       }
249 
250       PQclear (res);
251       g_free (text);
252 
253       text = g_malloc (strlen ((gchar *)data->user_data[0].u.text) * 2 + 1);
254       PQescapeStringConn (connIn, text, mbuf->str, mbuf->len, &err);
255       if (err)
256       {
257         g_print (_("%d: Escaping string failed.\n"), __LINE__);
258         g_print (_("Error: %s\n"), PQerrorMessage (connIn));
259         g_string_free (buf, TRUE);
260         g_string_free (mbuf, TRUE);
261         g_string_free (phnStr, TRUE);
262         g_free (text);
263         return (SMSD_NOK);
264       }
265 
266 //      text = strEscape (mbuf->str);
267       g_string_free (mbuf, TRUE);
268     }
269     else
270     {
271       PQclear (res);
272       gn_log_xdebug ("Not whole message collected.\n");
273       g_string_free (buf, TRUE);
274       g_string_free (phnStr, TRUE);
275       g_free (text);
276       return (SMSD_WAITING);
277     }
278   }
279 
280   gn_log_xdebug ("Message: %s\n", text);
281 
282   /* Detect duplicates */
283   g_string_printf (buf, "SELECT count(id) FROM %s.inbox \
284                          WHERE number = '%s' AND text = '%s' AND \
285                          smsdate = '%04d-%02d-%02d %02d:%02d:%02d'",
286 		   schema,
287                    data->remote.number, text, data->smsc_time.year,
288                    data->smsc_time.month, data->smsc_time.day,
289                    data->smsc_time.hour, data->smsc_time.minute,
290                    data->smsc_time.second);
291   res = PQexec (connIn, buf->str);
292   if (!res || PQresultStatus (res) != PGRES_TUPLES_OK)
293   {
294     g_print (_("%d: SELECT FROM %s.inbox command failed.\n"), __LINE__, schema);
295     gn_log_xdebug ("%s\n", buf->str);
296     g_print (_("Error: %s\n"), PQerrorMessage (connIn));
297     PQclear (res);
298     g_string_free (buf, TRUE);
299     g_string_free (phnStr, TRUE);
300     g_free (text);
301     return (SMSD_NOK);
302   }
303 
304   if (atoi (PQgetvalue (res, 0, 0)) > 0)
305   {
306     gn_log_xdebug ("%d: MSG already stored in database.\n", __LINE__);
307     PQclear (res);
308     g_string_free (buf, TRUE);
309     g_string_free (phnStr, TRUE);
310     g_free (text);
311     return (SMSD_DUPLICATE);
312   }
313 
314   PQclear (res);
315 
316   g_string_printf (buf, "INSERT INTO %s.inbox (\"number\", \"smsdate\", \"insertdate\",\
317                          \"text\", %s \"processed\") VALUES ('%s', \
318                          '%04d-%02d-%02d %02d:%02d:%02d', 'now', '%s', %s 'f')",
319                    schema,
320                    phone[0] != '\0' ? "\"phone\"," : "", data->remote.number,
321                    data->smsc_time.year, data->smsc_time.month,
322                    data->smsc_time.day, data->smsc_time.hour,
323                    data->smsc_time.minute, data->smsc_time.second, text, phnStr->str);
324   g_free (text);
325   g_string_free (phnStr, TRUE);
326 
327   res = PQexec (connIn, buf->str);
328   g_string_free (buf, TRUE);
329   if (!res || PQresultStatus(res) != PGRES_COMMAND_OK)
330   {
331     g_print (_("%d: INSERT INTO %s.inbox failed.\n"), __LINE__, schema);
332     gn_log_xdebug ("%s\n", buf->str);
333     g_print (_("Error: %s\n"), PQerrorMessage (connIn));
334     PQclear (res);
335     return (SMSD_NOK);
336   }
337 
338   PQclear (res);
339 
340   return (SMSD_OK);
341 }
342 
343 
DB_Look(const gchar * const phone)344 GNOKII_API gint DB_Look (const gchar * const phone)
345 {
346   GString *buf, *phnStr;
347   PGresult *res1, *res2;
348   register int i;
349   gint numError, error;
350   gint empty = 1;
351 
352   if (phone[0] == '\0')
353     phnStr = g_string_new ("");
354   else
355   {
356     phnStr = g_string_sized_new (32);
357     g_string_printf (phnStr, "AND phone = '%s'", phone);
358   }
359 
360   buf = g_string_sized_new (256);
361 
362   res1 = PQexec (connOut, "BEGIN");
363   PQclear (res1);
364 
365   g_string_printf (buf, "SELECT id, number, text, dreport FROM %s.outbox \
366                          WHERE processed='f' AND localtime(0) >= not_before \
367                          AND localtime(0) <= not_after %s LIMIT 1 FOR UPDATE",
368                    schema, phnStr->str);
369   g_string_free (phnStr, TRUE);
370 
371   res1 = PQexec (connOut, buf->str);
372   if (!res1 || PQresultStatus (res1) != PGRES_TUPLES_OK)
373   {
374     g_print (_("%d: SELECT FROM %s.outbox command failed.\n"), __LINE__, schema);
375     gn_log_xdebug ("%s\n", buf->str);
376     g_print (_("Error: %s\n"), PQerrorMessage (connOut));
377     PQclear (res1);
378     res1 = PQexec (connOut, "ROLLBACK TRANSACTION");
379     PQclear (res1);
380     g_string_free (buf, TRUE);
381     return (SMSD_NOK);
382   }
383 
384   for (i = 0; i < PQntuples (res1); i++)
385   {
386     gn_sms sms;
387 
388     empty = 0;
389     gn_sms_default_submit (&sms);
390     memset (&sms.remote.number, 0, sizeof (sms.remote.number));
391     sms.delivery_report = atoi (PQgetvalue (res1, i, 3));
392 
393     strncpy (sms.remote.number, PQgetvalue (res1, i, 1), sizeof (sms.remote.number) - 1);
394     sms.remote.number[sizeof(sms.remote.number) - 1] = '\0';
395     if (sms.remote.number[0] == '+')
396       sms.remote.type = GN_GSM_NUMBER_International;
397     else
398       sms.remote.type = GN_GSM_NUMBER_Unknown;
399 
400     strncpy ((gchar *) sms.user_data[0].u.text, PQgetvalue (res1, i, 2), 10 * GN_SMS_MAX_LENGTH + 1);
401     sms.user_data[0].u.text[10 * GN_SMS_MAX_LENGTH] = '\0';
402     sms.user_data[0].length = strlen ((gchar *) sms.user_data[0].u.text);
403     sms.user_data[0].type = GN_SMS_DATA_Text;
404     sms.user_data[1].type = GN_SMS_DATA_None;
405     if (!gn_char_def_alphabet (sms.user_data[0].u.text))
406        sms.dcs.u.general.alphabet = GN_SMS_DCS_UCS2;
407 
408     gn_log_xdebug ("Sending SMS: %s, %s\n", sms.remote.number, sms.user_data[0].u.text);
409 
410     numError = 0;
411     do
412     {
413       error = WriteSMS (&sms);
414       sleep (1);
415     }
416     while ((error == GN_ERR_TIMEOUT || error == GN_ERR_FAILED) && numError++ < 3);
417 
418     g_string_printf (buf, "UPDATE %s.outbox SET processed='t', error='%d', \
419                            processed_date='now' WHERE id='%s'",
420                      schema, error, PQgetvalue (res1, i, 0));
421 
422     res2 = PQexec (connOut, buf->str);
423     if (!res2 || PQresultStatus (res2) != PGRES_COMMAND_OK)
424     {
425       g_print (_("%d: UPDATE command failed.\n"), __LINE__);
426       gn_log_xdebug ("%s\n", buf->str);
427       g_print (_("Error: %s\n"), PQerrorMessage (connOut));
428     }
429 
430     PQclear (res2);
431   }
432 
433   PQclear (res1);
434   res1 = PQexec (connOut, "COMMIT");
435   PQclear (res1);
436 
437   g_string_free (buf, TRUE);
438 
439   if (empty)
440     return (SMSD_OUTBOXEMPTY);
441   else
442     return (SMSD_OK);
443 }
444