Ticket #215: dbhelper.cpp

File dbhelper.cpp, 19.0 KB (added by fvilas@…, 19 years ago)

DB helper functions, place in libs/libmythtv

Line 
1#include "dbhelper.h"
2
3#include "mythcontext.h"
4
5QString DBHelper::dbCreateTable(const QString &dbType, const QString &tableName, bool temporary)
6{
7    QString retval;
8
9    if (dbType == DBTYPE_MYSQL)
10    {
11        retval = "CREATE ";
12        if (temporary)
13           retval += "TEMPORARY ";
14        retval += "TABLE IF NOT EXISTS " + tableName;
15    }
16    else if (dbType == DBTYPE_PGSQL)
17    {
18        retval = dbDropTable(dbType, tableName) + " CREATE ";
19        if (temporary)
20           retval += "TEMPORARY ";
21        retval += "TABLE " + tableName;
22    }
23
24    return retval;
25}
26
27QString DBHelper::dbDropTable(const QString &dbType, const QString &tableName)
28{
29   if (dbType == DBTYPE_MYSQL)
30      return QString("DROP TABLE IF EXISTS " + tableName);
31   else if (dbType == DBTYPE_PGSQL)
32      return QString("select drop_table_if_exists ('" + tableName + "', false);");
33   return QString("");
34}
35
36QString DBHelper::dbInt(const QString &dbType, const QString &colName, const QString &width, bool nullable, bool unsignedCol, const QString &defaultVal, bool auto_num)
37{
38      QString retval = colName;
39
40   if (dbType == DBTYPE_MYSQL) {
41      retval += " int";
42      if (width.length() > 0)
43         retval += "(" + width + ")";
44      if (unsignedCol)
45         retval += " unsigned";
46      if (!nullable)
47         retval += " NOT NULL";
48      if (auto_num)
49         retval += " auto_increment";
50      else if (defaultVal.length() > 0)
51         retval += " default " + defaultVal;
52   }
53   else if (dbType == DBTYPE_PGSQL) {
54      if (auto_num)
55         retval += " SERIAL";
56      else {
57         retval += " INT4";
58         if (!nullable)
59            retval += " NOT NULL";
60         if (defaultVal.length() > 0)
61            retval += " default " + defaultVal;
62         if (unsignedCol)
63            retval += " check(" + colName + " >= 0)";
64      }
65   }
66   
67   return retval;
68}
69
70
71QString DBHelper::dbTinyInt(const QString &dbType, const QString &colName, const QString &width, bool nullable, bool unsignedCol, const QString &defaultVal, bool auto_num)
72{
73      QString retval = colName;
74
75   if (dbType == DBTYPE_MYSQL) {
76      retval += " tinyint";
77      if (width.length() > 0)
78         retval += "(" + width + ")";
79      if (unsignedCol)
80         retval += " unsigned";
81      if (!nullable)
82         retval += " NOT NULL";
83      if (auto_num)
84         retval += " auto_increment";
85      else if (defaultVal.length() > 0)
86         retval += " default " + defaultVal;
87   }
88   else if (dbType == DBTYPE_PGSQL) {
89      if (auto_num)
90         retval += " SERIAL";
91      else {
92         retval += " INT2";
93         if (!nullable)
94            retval += " NOT NULL";
95         if (defaultVal.length() > 0)
96            retval += " default " + defaultVal;
97         if (unsignedCol)
98            retval += " check(" + colName + " >= 0)";
99      }
100   }
101   
102   return retval;
103}
104
105
106QString DBHelper::dbBigInt(const QString &dbType, const QString &colName, const QString &width, bool nullable, bool unsignedCol, const QString &defaultVal, bool auto_num)
107{
108      QString retval = colName;
109
110   if (dbType == DBTYPE_MYSQL) {
111      retval += " bigint";
112      if (width.length() > 0)
113         retval += "(" + width + ")";
114      if (unsignedCol)
115         retval += " unsigned";
116      if (!nullable)
117         retval += " NOT NULL";
118      if (auto_num)
119         retval += " auto_increment";
120      else if (defaultVal.length() > 0)
121         retval += " default " + defaultVal;
122   }
123   else if (dbType == DBTYPE_PGSQL) {
124      if (auto_num)
125         retval += " SERIAL";
126      else {
127         retval += " INT8";
128         if (!nullable)
129            retval += " NOT NULL";
130         if (defaultVal.length() > 0)
131            retval += " default " + defaultVal;
132         if (unsignedCol)
133            retval += " check(" + colName + " >= 0)";
134      }
135   }
136   
137   return retval;
138}
139
140
141QString DBHelper::dbSmallInt(const QString &dbType, const QString &colName, const QString &width, bool nullable, bool unsignedCol, const QString &defaultVal, bool auto_num)
142{
143      QString retval = colName;
144
145   if (dbType == DBTYPE_MYSQL) {
146      retval += " smallint";
147      if (width.length() > 0)
148         retval += "(" + width + ")";
149      if (unsignedCol)
150         retval += " unsigned";
151      if (!nullable)
152         retval += " NOT NULL";
153      if (auto_num)
154         retval += " auto_increment";
155      else if (defaultVal.length() > 0)
156         retval += " default " + defaultVal;
157   }
158   else if (dbType == DBTYPE_PGSQL) {
159      if (auto_num)
160         retval += " SERIAL";
161      else {
162         retval += " INT2";
163         if (!nullable)
164            retval += " NOT NULL";
165         if (defaultVal.length() > 0)
166            retval += " default " + defaultVal;
167         if (unsignedCol)
168            retval += " check(" + colName + " >= 0)";
169      }
170   }
171   
172   return retval;
173}
174
175
176QString DBHelper::dbMedInt(const QString &dbType, const QString &colName, const QString &width, bool nullable, bool unsignedCol, const QString &defaultVal, bool auto_num)
177{
178   QString retval = colName;
179
180   if (dbType == DBTYPE_MYSQL) {
181      retval += " mediumint";
182      if (width.length() > 0)
183         retval += "(" + width + ")";
184      if (unsignedCol)
185         retval += " unsigned";
186      if (!nullable)
187         retval += " NOT NULL";
188      if (auto_num)
189         retval += " auto_increment";
190      else if (defaultVal.length() > 0)
191         retval += " default " + defaultVal;
192   }
193   else if (dbType == DBTYPE_PGSQL) {
194      if (auto_num)
195         retval += " SERIAL";
196      else {
197         retval += " INT4";
198         if (!nullable)
199            retval += " NOT NULL";
200         if (defaultVal.length() > 0)
201            retval += " default " + defaultVal;
202         if (unsignedCol)
203            retval += " check(" + colName + " >= 0)";
204      }
205   }
206   
207   return retval;
208}
209
210QString DBHelper::dbFloat(const QString &dbType, const QString &colName, const QString &width, bool nullable, bool unsignedCol, const QString &defaultVal)
211{
212   QString retval = colName + " float";
213
214
215   if (width.length() > 0)
216      retval += "(" + width + ")";
217
218   if (dbType == DBTYPE_MYSQL) {
219      if (unsignedCol)
220         retval += " unsigned";
221      if (!nullable)
222         retval += " NOT NULL";
223      else if (defaultVal.length() > 0)
224         retval += " default " + defaultVal;
225   }
226   else if (dbType == DBTYPE_PGSQL) {
227      if (!nullable)
228         retval += " NOT NULL";
229      if (defaultVal.length() > 0)
230         retval += " default " + defaultVal;
231      if (unsignedCol)
232         retval += " check(" + colName + " >= 0)";
233   }
234   
235   return retval;
236}
237
238QString DBHelper::dbTimeStamp(const QString &dbType, const QString &colName, const QString &width, bool nullable, const QString &defaultVal)
239{
240    QString retval = colName;
241
242    retval += " TIMESTAMP";
243
244    if (dbType == DBTYPE_MYSQL) {
245        if (width.length() > 0)
246            retval += "(" + width + ")";
247    }
248
249    if (!nullable)
250       retval += " NOT NULL";
251
252    if (defaultVal.length() > 0) {
253        if (dbType == DBTYPE_MYSQL) {
254            retval += " default " + defaultVal;
255        }
256        else if (dbType == DBTYPE_PGSQL) {
257            if (defaultVal == "'00000000000000'" || defaultVal == "'0000-00-00 00:00:00'")
258                retval += " default 'epoch'";
259            else
260                retval += " default " + defaultVal;
261        }
262    }
263    else
264    {
265        if (dbType == DBTYPE_PGSQL) {
266            retval += " default now() ";
267        }
268    }
269
270    return retval;
271}
272
273
274QString DBHelper::dbDatetime(const QString &dbType, const QString &colName, bool nullable, const QString &defaultVal)
275{
276    QString retval = colName;
277
278    if (dbType == DBTYPE_MYSQL) {
279        retval += " DATETIME";
280    }
281    else if (dbType == DBTYPE_PGSQL) {
282        retval += " TIMESTAMP";
283    }
284
285    if (!nullable)
286        retval += " NOT NULL";
287
288    if (defaultVal.length() > 0) {
289        if (dbType == DBTYPE_MYSQL) {
290            retval += " default " + defaultVal;
291        }
292        else if (dbType == DBTYPE_PGSQL) {
293            if (defaultVal == "'00000000000000'" || defaultVal == "'0000-00-00 00:00:00'")
294                retval += " default 'epoch'";
295            else
296                retval += " default " + defaultVal;
297        }
298    }
299    else
300    {
301        if (dbType == DBTYPE_PGSQL) {
302            retval += " default now() ";
303        }
304    }
305
306
307   return retval;
308}
309
310
311QString DBHelper::dbDate(const QString &dbType, const QString &colName, bool nullable, const QString &defaultVal)
312{
313   QString retval = colName + " DATE";
314
315   if (!nullable)
316      retval += " NOT NULL";
317
318   if (defaultVal.length() > 0)
319      if (dbType == DBTYPE_MYSQL) {
320         retval += " default " + defaultVal;
321      }
322      else if (dbType == DBTYPE_PGSQL) {
323         if (defaultVal == "'00000000'" || defaultVal == "'0000-00-00'")
324            retval += " default 'epoch'";
325         else
326            retval += " default " + defaultVal;
327      }
328
329   return retval;
330}
331
332
333QString DBHelper::dbYear(const QString &dbType, const QString &colName, const QString &width, bool nullable, const QString &defaultVal)
334{
335    QString retval = colName;
336
337    if (dbType == DBTYPE_MYSQL)
338    {
339        retval += " YEAR";
340
341        if (width.length() > 0)
342            retval += "(" + width + ")";
343
344        if (!nullable)
345            retval += " NOT NULL";
346
347        if (defaultVal.length() > 0)
348            retval += " DEFAULT " + defaultVal;
349    }
350    else if (dbType == DBTYPE_PGSQL)
351    {
352        retval += " INT";
353
354        if (!nullable)
355            retval += " NOT NULL";
356
357        if (defaultVal.length() > 0)
358            retval += " DEFAULT " + defaultVal;
359
360        retval += " check(" + colName + " >= 1901 OR " + colName + " = 0000)";
361    }
362
363    return retval;
364}
365
366QString DBHelper::dbBlob(const QString &dbType, const QString &colName, bool nullable, const QString &defaultVal)
367{
368    QString retval = colName;
369
370    if (dbType == DBTYPE_MYSQL)
371        retval += " BLOB";
372    else if (dbType == DBTYPE_PGSQL)
373        retval += " BYTEA";
374
375    if (!nullable)
376       retval += " NOT NULL";
377
378    if (defaultVal.length() > 0)
379        retval += " DEFAULT " + defaultVal;
380
381    return retval;
382}
383
384QString DBHelper::dbText(const QString &dbType, const QString &colName, bool nullable)
385{
386    QString retval = colName + " TEXT";
387
388    if (!nullable)
389       retval += " NOT NULL";
390
391
392    if (dbType == DBTYPE_PGSQL)
393        retval += " DEFAULT ''";
394
395    return retval;
396}
397
398QString DBHelper::dbAddIndex(const QString &dbType, const QString &tableName, const QString &indexName, const QString &colList, bool unique)
399{
400    QString retval = "CREATE ";
401    QString s;
402
403    int numCols;
404    int colStart;
405    int colEnd;
406    int widthStart;
407
408    if (dbType == DBTYPE_MYSQL)
409    {
410        if (unique)
411            retval += "UNIQUE ";
412
413        retval += "INDEX " + tableName + "_" + indexName + "_idx ON " + tableName + " (" + colList + ")";
414    }
415    else if (dbType == DBTYPE_PGSQL)
416    {
417        if (unique)
418            retval += "UNIQUE ";
419
420        retval += "INDEX " + tableName + "_" + indexName + "_idx ON " + tableName + " (";
421
422        // check to see if there are any "partial columns"
423        // if they exist, just use the whole column
424        // PGSQL won't do partials for an index
425        // but it will index on a function, so there may be a
426        // way to implement this, since indexing a varchar(255)
427        // is slow
428        widthStart = colList.find('(');
429        if (-1 == widthStart)
430            retval += colList + ",";
431        else
432        {
433            numCols = colList.contains(',') + 1;
434
435            colStart = 0;
436            if (1 == numCols)
437                colEnd = colList.length();
438            else
439                colEnd = colList.find(',');
440
441            for (int i = 0 ; i < numCols; ++i)
442            {
443                if (colEnd < widthStart)
444                {
445                    retval += colList.section(',', i, i) + ",";
446                }
447                else
448                {
449                    // this part doesn't work for some reason
450                    // so just use the whole field.
451                    //retval += "substring("
452                    //       + colList.mid(colStart, colStart-widthStart-1)
453                    //       + " from 1 for "
454                    //       + colList.mid(widthStart+1, widthStart-colList.find(')', widthStart)) + ",";
455                    retval += colList.section(',', i, i).section('(', 0, 0) + ",";
456                    //retval += colList.mid(colStart, colStart-widthStart-1) + ",";
457                    widthStart = colList.find('(', colEnd);
458                    if (-1 == widthStart)
459                        widthStart = colList.length() + 1;
460                }
461                colStart = colEnd + 1;
462                colEnd = colList.find(',', colStart);
463                if (-1 == colEnd)
464                    colEnd = colList.length();
465            }
466        }
467
468        // strip the trailing comma
469        retval = retval.left(retval.length()-1);
470        retval += ")";
471    }
472    else
473        retval = "";
474
475    return retval;
476}
477
478QString DBHelper::dbDropPKey(const QString &dbType, const QString &tableName)
479{
480    QString  retval = "ALTER TABLE " + tableName + " DROP ";
481
482    if (dbType == DBTYPE_MYSQL)
483        retval += "PRIMARY KEY";
484    else if (dbType == DBTYPE_PGSQL)
485    {
486        retval += "CONSTRAINT " + tableName + "_pkey";
487    }
488
489    return retval;
490}
491
492
493QString DBHelper::dbChangeColumn(const QString &dbType, const QString &tableName, const QString &oldColName, const QString &newColDef)
494{
495    QString retval = "ALTER TABLE " + tableName;
496    int idx;
497    int idx2;
498
499    if (dbType == DBTYPE_MYSQL)
500    {
501        retval += " CHANGE " + oldColName + " " + newColDef;
502    }
503    else if (dbType == DBTYPE_PGSQL)
504    {
505        QString newColName = newColDef.section(' ', 0, 0);
506
507        retval += " ALTER " + oldColName + " TYPE " + newColDef.section(' ', 1, 1) + ";";
508        retval += "ALTER TABLE " + tableName + " ALTER " + oldColName;
509        if (newColDef.contains("not null", FALSE))
510            retval += " SET NOT NULL;";
511        else
512            retval += " DROP NOT NULL;";
513
514        idx = newColDef.find("default ", 0, FALSE);
515        idx2 = newColDef.find(',', idx);
516        if (-1 < idx2)
517        {
518            retval += "ALTER TABLE " + tableName + " ADD " + newColDef.mid(idx2, newColDef.length() - idx2) + ";";
519        }
520        else
521            idx2 = newColDef.length();
522
523        if (-1 < idx)
524        {
525            idx += 8;
526            retval += "ALTER TABLE " + tableName + " ALTER " + oldColName + " SET DEFAULT " + newColDef.mid(idx, idx2-idx);
527        }
528    }
529
530    return retval;
531}
532
533QString DBHelper::dbReplaceInto(const QString &dbType, const QString &tableName, const QString &fields, const QString &values, unsigned long where)
534{
535    QString retval;
536    int numFields = fields.contains(',') + 1;
537
538    if (dbType == DBTYPE_MYSQL)
539    {
540        retval = "REPLACE INTO " + tableName + " SET ";
541        for (int i = 0; i < numFields ; ++i)
542        {
543            retval += fields.section(',', i, i) + " = " + values.section(",", i, i) + ", ";
544        }
545        retval = retval.left(retval.length()-2);
546    }
547    else if (dbType == DBTYPE_PGSQL)
548    {
549        // the where value is 1 in fields where
550        // the variable is included in a where clause
551        // order is from left to right.
552        QString strWhere = " WHERE ";
553        QString strValues = " VALUES (";
554
555        for (int i = 0; i < numFields ; ++i)
556        {
557            if (where % 2)
558               strWhere += fields.section(',', i, i) + " = " + values.section(",", i, i) + " AND ";
559            where = where >> 1;
560        }
561        strWhere = strWhere.left(strWhere.length()-4);
562
563        // rumor is that MySQL's replace into function
564        // is really a delete and and insert, so let's do that.
565        retval = "delete from " + tableName + strWhere + ";";
566        retval += "insert into " + tableName + "(";
567        for (int i = 0; i < numFields ; ++i)
568        {
569            retval += fields.section(',', i, i) + ", ";
570            strValues += values.section(",", i, i) + ", ";
571        }
572        strValues = strValues.left(strValues.length()-2);
573        strValues += ")";
574        retval = retval.left(retval.length()-2);
575        retval += ") " + strValues;
576    }
577
578    return retval;
579}
580
581QString DBHelper::dbTimetoDOW(const QString &dbType, const QString &colName)
582{
583    QString retval;
584
585    if (dbType == DBTYPE_MYSQL)
586        retval = " DAYOFWEEK(" + colName + ") ";
587    else if (dbType == DBTYPE_PGSQL)
588        retval = " DATE_PART('DOW', " + colName + " ) ";
589
590    return retval;
591}
592
593QString DBHelper::dbTimetoDays(const QString &dbType, const QString &colName)
594{
595    QString retval;
596
597    if (dbType == DBTYPE_MYSQL)
598        retval = " TO_DAYS( " + colName + ") ";
599    else if (dbType == DBTYPE_PGSQL)
600        retval = "DATE_PART('DAY', DATE_TRUNC('DAY', " + colName + " ))::int4 ";
601    return retval;
602}
603
604QString DBHelper::dbTimetoSeconds(const QString &dbType, const QString &colName)
605{
606    QString retval;
607
608if (dbType == DBTYPE_MYSQL)
609        retval = " TIME_TO_SEC( " + colName + ") ";
610    else if (dbType == DBTYPE_PGSQL)
611        retval = " DATE_PART('EPOCH', " + colName
612               + " - DATE_TRUNC('DAY', " + colName + " )) ";
613
614    return retval;
615}
616
617QString DBHelper::dbFromUnixTime(const QString &dbType, const QString &colName)
618{
619    QString retval;
620
621    if (dbType == DBTYPE_MYSQL)
622        //retval = " FROM_UNIXTIME(" + colName + ") ";
623        retval = " interval " + colName + " second ";
624    else if (dbType == DBTYPE_PGSQL)
625        retval = " " + colName + " * INTERVAL '1 second'";
626
627    return retval;
628}
629
630QString DBHelper::dbHoursMinutes(const QString &dbType, const QString &colName)
631{
632    QString retval;
633
634    if (dbType == DBTYPE_MYSQL)
635        retval = " interval time_format(" + colName + ", '%H:%i') hour_minute ";
636    else if (dbType == DBTYPE_PGSQL)
637        retval = " TO_TIMESTAMP(TO_CHAR(" + colName + ", 'HH24:MI' ), 'HH24:MI') ";
638
639    return retval;
640}
641
642QString DBHelper::dbDateSub(const QString &dbType, const QString &colName1, const QString &colName2)
643{
644    QString retval;
645
646    if (dbType == DBTYPE_MYSQL)
647        retval = " DATE_SUB(" + colName1 + ", " + colName2 + ") ";
648    else if (dbType == DBTYPE_PGSQL)
649        retval = " " + colName1 + " - " + colName2 + " ";
650
651    return retval;
652}
653
654
655QString DBHelper::dbDateAdd(const QString &dbType, const QString &colName1, const QString &colName2)
656{
657    QString retval;
658
659    if (dbType == DBTYPE_MYSQL)
660        retval = " DATE_ADD(" + colName1 + ", " + colName2 + ") ";
661    else if (dbType == DBTYPE_PGSQL)
662        retval = " " + colName1 + " + " + colName2 + " ";
663
664    return retval;
665}
666
667QString DBHelper::dbCurrentTimestamp(const QString &dbType)
668{
669    QString retval;
670
671    if (dbType == DBTYPE_MYSQL)
672        retval = " CURRENT_TIMESTAMP() ";
673    else if (dbType == DBTYPE_PGSQL)
674        retval = " NOW() ";
675
676    return retval;
677}
678
679QString DBHelper::dbCurrentDate(const QString &dbType)
680{
681    QString retval;
682
683    if (dbType == DBTYPE_MYSQL)
684        retval = " CURRENT_DATE() ";
685    else if (dbType == DBTYPE_PGSQL)
686        retval = " CURRENT_DATE ";
687
688    return retval;
689}
690
691QString DBHelper::dbHex(const QString &dbType, const QString &value)
692{
693    QString retval;
694
695    if (dbType == DBTYPE_MYSQL)
696        retval = " 0x" + value;
697    else if (dbType == DBTYPE_PGSQL)
698        retval = " X'" + value + "'::int ";
699
700    return retval;
701}