Ticket #215: dbhelper.cpp

File dbhelper.cpp, 19.0 KB (added by fvilas@…, 20 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}