-
Notifications
You must be signed in to change notification settings - Fork 345
/
dbcheck.cpp
6748 lines (6185 loc) · 247 KB
/
dbcheck.cpp
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
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784
785
786
787
788
789
790
791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
806
807
808
809
810
811
812
813
814
815
816
817
818
819
820
821
822
823
824
825
826
827
828
829
830
831
832
833
834
835
836
837
838
839
840
841
842
843
844
845
846
847
848
849
850
851
852
853
854
855
856
857
858
859
860
861
862
863
864
865
866
867
868
869
870
871
872
873
874
875
876
877
878
879
880
881
882
883
884
885
886
887
888
889
890
891
892
893
894
895
896
897
898
899
900
901
902
903
904
905
906
907
908
909
910
911
912
913
914
915
916
917
918
919
920
921
922
923
924
925
926
927
928
929
930
931
932
933
934
935
936
937
938
939
940
941
942
943
944
945
946
947
948
949
950
951
952
953
954
955
956
957
958
959
960
961
962
963
964
965
966
967
968
969
970
971
972
973
974
975
976
977
978
979
980
981
982
983
984
985
986
987
988
989
990
991
992
993
994
995
996
997
998
999
1000
#include <iostream>
#include <stdio.h>
using namespace std;
#include <QString>
#include <QSqlError>
#include "dbcheck.h"
#include "videodisplayprofile.h" // for "1214"
#include "dbutil.h"
#include "mythcorecontext.h"
#include "schemawizard.h"
#include "mythdb.h"
#include "mythlogging.h"
#include "diseqcsettings.h" // for convert_diseqc_db()
#include "videodbcheck.h"
#define MINIMUM_DBMS_VERSION 5,0,15
/* If currentDatabaseVersion gets updated, the following files need updating:
mythtv/bindings/python/MythTV/static.py
mythtv/bindings/perl/MythTV.pm
*/
/// This is the DB schema version expected by the running MythTV instance.
const QString currentDatabaseVersion = "1279";
static bool UpdateDBVersionNumber(const QString &newnumber, QString &dbver);
static bool performActualUpdate(
const char **updates, const char *version, QString &dbver);
static bool doUpgradeTVDatabaseSchema(void);
/** \defgroup db_schema MythTV Database Schema
\section db_intro Introduction
\verbatim
The schema contains the following tables:
\endverbatim
\htmlonly
<table>
<tr><td>callsignnetworkmap <td>pk(id)
<tr><td>capturecard <td>pk(cardid)
<tr><td>cardinput <td>pk(cardinputid)
<tr><td>channel <td>pk(chanid) k(channum,sourceid)
<tr><td>channelgroup <td>pk(id)
<tr><td>channelgroupnames <td>pk(grpid)
<tr><td>codecparams <td>pk(profile,name)
<tr><td>conflictresolutionany <td>k(prefertitle) k(disliketitle)
<tr><td>conflictresolutionoverride <td>k(chanid,starttime) k(endtime)
<tr><td>conflictresolutionsingle <td>k(preferchanid,preferstarttime) k(preferendtime)
<tr><td>credits <td>uk(chanid,starttime,person,role) k(person,role)
<tr><td>dtv_multiplex <td>pk(mplexid)
<tr><td>dtv_privatetypes
<tr><td>dvb_signal_quality <td>pk(id) k(sampletime,cardid)
<tr><td>housekeeping <td>pk(tag)
<tr><td>jobqueue <td>pk(id) uk(chanid,starttime,type,inserttime)
<tr><td>jumppoints <td>pk(destination,hostname)
<tr><td>keybindings <td>pk(context,action,hostname)
<tr><td>keyword <td>uk(phrase,searchtype)
<tr><td>mythlog <td>pk(logid)
<tr><td>networkiconmap <td>pk(id) uk(network)
<tr><td>oldprogram <td>pk(oldtitle)
<tr><td>oldrecorded <td>k(endtime) k(title) k(seriesid) k(programid)
pk(station,starttime,title)
<tr><td>people <td>pk(person) uk(name)
<tr><td>pidcache <td>
<tr><td>profilegroups <td>pk(id) uk(name,hostname)
<tr><td>program <td>k(endtime) k(title) k(title_pronounce) k(seriesid)
k(programid) k(chanid,starttime,endtime)
<tr><td>programgenres <td>pk(chanid,starttime,relevance)
<tr><td>programrating <td>uk(chanid,starttime,system,rating)
k(starttime,system)
<tr><td>recgrouppassword <td>pk(recgroup) uk(recgroup)
<tr><td>record <td>pk(recordid) k(chanid,starttime) k(title)
k(seriesid) k(programid)
<tr><td>recorded <td>k(endtime) k(seriesid) k(programid)
<tr><td>recordedmarkup <td>pk(chanid,starttime,mark,type)
<tr><td>recordingprofiles <td>pk(id)
<tr><td>recordoverride <td>
<tr><td>settings <td>k(value,hostname)
<tr><td>videosource <td>pk(sourceid) uk(name)
<tr><td>displayprofilegroups <td>pk(name, host), uk(profileid)
<tr><td>displayprofiles <td>pk(profileid),
</table>
\endhtmlonly
Where pk means PRIMARY KEY, uk means "UNIQUE KEY", and k means "KEY".
BUSQ refers to the Big Ugly SQL Query in scheduler.cpp
\section capturecard_table Capture Card Table (capturecard)
This table describes the attributes of a capture card used by MythTV.
The primary key 'cardid' is automatically generated by the database.
The 'videodevice' field normally points to a device used to capture
the video, but for DVB where multiple devices are used it is simply
a number indicating which set of devices to use. The 'audiodevice'
is only used by framegrabber cards, encoding cards like the Hauppauge
PVR line encode the audio as part of the video stream, while DTV
cards like the various ATSC and DVB cards receive the audio as part
of the video stream. The same applies to the 'vbidevice', PVR and
DTV cards have captions encoded in the digital stream, while
framegrabber cards use this device to provide caption and other
data to MythTV.
The 'audioratelimit' field is used to set the audio capture rate
when using the 'audiodevice' field. This is mostly useful for
V4L cards that have troublesome on-board audio, such as some
cards supported by the bttv driver.
The 'cardtype' is an important field for all cards as it guides the
interpretation of the rest of the fields, it has several possible
values: "V4L", "MJPEG", "DVB", "MPEG", "FIREWIRE", and "GO7007".
"V4L" indicates a V4L compatible device, this could be a
true V4L device or something like a Firewire camera with the
"firewire->v4l" driver.
"MJPEG" describes a Matrox G200 or DC10 card which captures
individual frames as JPEG images.
"DVB" can describe any card supported by the Linux DVB
drivers, including the pcHDTV and other ATSC cards.
The "MPEG" type describes a device using the ivtv drivers such as
the Hauppauge PVR[150,250,350,500]s +Freestyle cards, the Yuan MPEG
series, the Avermedia M179, and the Adaptec VideOh! AVC-2410.
The "FIREWIRE" card type does not describe Firewire cameras or
capture cards but instead describes the Firewire port required by
law in the USA on cable boxes for subscribers who receive HDTV
channels and request the Firewire port feature. This port lets
you communicate with your cable box without needing to build an
IR transmitter; in some cases, it also allows you to digitally
capture video.
The "GO7007" card type describes a USB MPEG-4 encoder such
as the Plextor ConvertX.
The 'hostname' field is another important field for all cards
as it specifies which backend the capture card is connected to.
The 'defaultinput' field is a another important field for all
cards except "FIREWIRE", "FREEBOX", "HDHOMERUN", and "IMPORT" cards.
It specifies which
input of the card to use. This does not have to mean a specific
physical input, but may also indicate a different use for the
same physical input.
The 'signal_timeout' and 'channel_timeout' indicate in
milliseconds how long it should take to get a signal and
channel lock respectively.
The 'dvb_swfilter', 'dvb_sat_type', 'dvb_wait_for_seqstart',
'skipbtaudio', 'dvb_on_demand', and 'dvb_diseqc_type' columns
are all "DVB" specific configuration parameters.
Both 'dvb_recordts' and 'dvb_hw_decoder' are unused, and
will be dropped in future versions of MythTV.
The 'firewire_port', 'firewire_node', 'firewire_speed',
'firewire_model', 'firewire_connection' are all "FIREWIRE" specific
configuration parameters. The first three define the connection,
and function much like 'videodevice' does for other capture "cards".
The 'firewire_model' describes the cable box model, for example
"DCT-6000" describes a box that communicates using the same protocol
as the Motorola DCT-6000. The 'firewire_connection' field describes
the overall communication protocol, i.e. are we using "Broadcast"
or "Point-to-Point" communication.
\section dtv_multiplex_table Digital Television Multiplex Table (dtv_multiplex)
This table contains the information needed to tune to a particular
frequency on a particular input.
The primary key, 'mplexid', is automatically generated by the database.
The most important field is the 'frequency' field which contains the
transmission frequency in Hertz. The 'sourceid' which tells us which
card and on what input of that card this multiplex applies to according
to the \ref videosource_table. Along with a channel number 'sourceid'
also uniquely identifies a channel in the \ref channel_table. The
'modulation' field tells us whether the modulation mode is "qpsk",
"qam_16", "qam_32", "qam_64", "qam_128", "qam_256", "8vsb", "16vsb",
or "auto". Finally, the 'sistandard' field is the last field which
applies to both DVB and ATSC, it contains on of four strings, "dvb",
"atsc", "mpeg" or "opencable".
When describing a DVB Multiplex, the networkid, and transportid are
used along with the serviceid in the \ref channel_table to tune to
a particular MPEG stream in DVBChannel.
When describing an ATSC Multiplex, the \ref channel_table contains
the major and minor channels used to tune to a particular stream.
Other fields are used by different DVBx implementations to tune to a
a particular type of service and MythTV just passes them along to the
drivers to take care of.
\section dtv_privatetypes_table Digital Television Private Types Table (dtv_privatetypes)
This table contains free form data pertaining to DVB services, it is
like a settings table for DVB.
\section videosource_table Video Source Table (videosource)
This table tells us about a particular input on a video capture card.
The primary key, 'sourceid', is automatically generated by the database.
The 'name' field gives the input name as passed to the video capture card
driver. The 'xmltvgrabber' field tells us where we get our listings from.
The 'userid' and 'password' fields are optionally used to log in to a
listings service, with the 'lineupid' being used to select a particular
lineup available from the listings service. Finally, the 'freqtable'
tells us which channel number to frequency mapping to use with
analog channels, us, us-cable, pal, etc.
\section channel_table Channel Table (channel)
The primary key, 'chanid' is generated by combining the source id with
a unique number from 0 to 99.
The 'channum' field contains the channel number to be input to jump
to a channel.
The 'sourceid' field contains the \ref videosource_table key, which
specifies which listings service to use and which input on which video
capture card to find this channel.
The 'callsign' field contains the government mandated license number
of the channel's broadcaster if this is an over-the-air channel.
The 'name' field provides the alphanumeric name of this channel for
use in the %MythTV user interface.
The 'icon' field provides the path to an optional channel icon for this
channel.
The 'freqid' field provides the channel number to tune to, as
differentiated from the 'channum' used in the %MythTV user interface.
When the 'mplexid' is set it is used instead. And the 'finetune' field
is used to adjust the frequency provided by the combination of 'freqid'
and 'tvformat', this can be used when there is carrier drift in a cable
system.
The 'xmltvid' field is used to identify this channel to the listings
provider.
The 'tmoffset' field is used to apply an offset (in minutes) from the listings
provided by the provider to a new time in the MythTV program guide database.
This is very handy when the listings provider has listings which are offset
by a few hours on individual channels with the rest of them being correct.
The 'recpriority' field is used tell the scheduler from which of two
otherwise equivalent programs on two different channels should be
preferred, a higher number means this channel is more preferred.
The 'tvformat' is one of "ntsc", "ntsc-jp", "pal", "atsc", etc and is used
for various purposes, such as translating a channel number to a frequency,
setting the recording frame size, etc.
'commfree' tells us whether this is a commercial free channel, such as
those on the BBC and CBC networks. On commercial free channels we
do not need to run the commercial detector.
'commmethod' tells us which commercial flagger to use on the channel, the
default is to use the global setting
'visible' tells us whether we should show this channel in the channel
guide.
'outputfilters' contains a list of filters that should be applied
when playing recordings from this channel.
The 'contrast' 'brightness', 'colour', and 'hue' fields are used by frame
grabbing recorders to adjust the picture before encoding each frame.
And, 'videofilters' contains a list of filters that should be applied
when recording from channel using a frame grabbing recorder.
For digital television, the 'mplexid' field contains the
\ref dtv_multiplex_table key for determining additional digital tuning
parameters. The 'serviceid' field contains the MPEG program number when
describing a DVB service. The 'useonairguide' field is used by DVB to
determine whether to use the on-air-guide data from this channel to
update the listings.
\section program_table Program Entry Table (program)
'category_type' holds one of these exact four strings:
"movie", "series", "sports" or "tvshow".
'airdate' is a string representing the year of release for movies and
may have no meaning for other types of shows.
'stars' is a floating point number from 0.0 to 1.0. On a four star scale,
1.0 would be four stars, 0.75 would be three stars and so on.
'originalairdate' if provided is the date when a show was, or will be,
first televised.
'previouslyshown' is a field created by MythTV to try to determine if
a showing is more than 14 days after its original air date or if the
show was marked as a repeat and did not have a date for the first
airing. If this is "0" it usually means that this is a brand new show
or a rebroadcast within the first two weeks.
'programid' is the Tribune Media Service database record identifier
for each program description. In general, these start with a two
letter prefix, MV, EP, SP or SH that equate to the 'category_type'.
For most, the last four digits are "0000" except EP where the last
four digits are the episode number in the series. Note that these
are generated by TMS and not the show's producers but they are
usually in the same order as the original air dates for the episodes.
Detailed information can be found in the Data Direct documentation
at http://labs.zap2it.com/ .
\section oldrecorded_table Old Recorded Table (oldrecorded)
oldrecorded imposes the restriction of one entry per
title, starttime and callsign. The scheduler relies on this when
restoring any previous status for programs that aren't currently
recording and for catching reactivation requests.
The duplicate field is used to indicate if this record should be used
to check for duplicates in the BUSQ
\section oldfind_table Old Find Table (oldfind)
If a matching entry exists in
the oldfind table, the program is considered a duplicate regardless of
the duplicate setting in recorded and oldrecorded. oldfind is an
imperfect optimization to avoid greatly increasing the time needed for
the big scheduler query.
But the real reason for oldfind is that a search
rule may not be able to match by title. For instance, a FindWeekly
"Bill Murray (People Search)" needs to know that "Meatballs" is a
duplicate for this week if "Stripes" has already been recorded. Oldfind
tracks if the rule (recordid) has recorded in the current time
interval (findid). When a recording has a findid, ForgetHistory()
and DeleteHistory() update oldfind while updating oldrecorded.
\section recorded_table Recorded Table (recorded
The duplicate field is used to indicate if this record should be used
to check for duplicates in the BUSQ
*/
/** \fn UpdateDBVersionNumber(const QString&, QString&)
* \brief Updates the schema version stored in the database.
*
* Updates "DBSchemaVer" property in the settings table.
* \param newnumber New schema version.
* \param dbver the database version at the end of the function is returned
* in this parameter, if things go well this will be 'newnumber'.
*/
static bool UpdateDBVersionNumber(const QString &newnumber, QString &dbver)
{
// delete old schema version
MSqlQuery query(MSqlQuery::InitCon());
QString thequery = "DELETE FROM settings WHERE value='DBSchemaVer';";
query.prepare(thequery);
if (!query.exec())
{
QString msg =
QString("DB Error (Deleting old DB version number): \n"
"Query was: %1 \nError was: %2 \nnew version: %3")
.arg(thequery)
.arg(MythDB::DBErrorMessage(query.lastError()))
.arg(newnumber);
LOG(VB_GENERAL, LOG_ERR, msg);
return false;
}
// set new schema version
thequery = QString("INSERT INTO settings (value, data, hostname) "
"VALUES ('DBSchemaVer', %1, NULL);").arg(newnumber);
query.prepare(thequery);
if (!query.exec())
{
QString msg =
QString("DB Error (Setting new DB version number): \n"
"Query was: %1 \nError was: %2 \nnew version: %3")
.arg(thequery)
.arg(MythDB::DBErrorMessage(query.lastError()))
.arg(newnumber);
LOG(VB_GENERAL, LOG_ERR, msg);
return false;
}
dbver = newnumber;
return true;
}
/** \fn performActualUpdate(const char **, const char*, QString&)
* \brief Runs a number of SQL commands, and updates the schema version.
*
* \param updates array of SQL commands to issue, terminated by a NULL string.
* \param version version we are updating db to.
* \param dbver the database version at the end of the function is returned
* in this parameter, if things go well this will be 'version'.
* \return true on success, false on failure
*/
static bool performActualUpdate(
const char **updates, const char *version, QString &dbver)
{
MSqlQuery query(MSqlQuery::InitCon());
LOG(VB_GENERAL, LOG_CRIT, QString("Upgrading to MythTV schema version ") +
version);
int counter = 0;
const char *thequery = updates[counter];
while (thequery != NULL)
{
if (strlen(thequery) && !query.exec(thequery))
{
QString msg =
QString("DB Error (Performing database upgrade): \n"
"Query was: %1 \nError was: %2 \nnew version: %3")
.arg(thequery)
.arg(MythDB::DBErrorMessage(query.lastError()))
.arg(version);
LOG(VB_GENERAL, LOG_ERR, msg);
return false;
}
counter++;
thequery = updates[counter];
}
if (!UpdateDBVersionNumber(version, dbver))
return false;
return true;
}
/**
* \brief Called from outside dbcheck.cpp to update the schema.
*
* If the "DBSchemaVer" property equals the currentDatabase version this
* returns true immediately. If not, we try to do a database backup,
* prompt the user for permission to do the upgrade,
* lock the schemalock table, call doUpgradeTVDatabaseSchema()
* to do the actual update, and then we unlock the schemalock table.
*
* If the program running this function is killed while
* this is running then the schema may be corrupted.
*
* \return false on failure, error, or if the user selected "Exit."
*/
bool UpgradeTVDatabaseSchema(const bool upgradeAllowed,
const bool upgradeIfNoUI)
{
#ifdef IGNORE_SCHEMA_VER_MISMATCH
return true;
#endif
SchemaUpgradeWizard * DBup;
GetMythDB()->SetSuppressDBMessages(true);
gCoreContext->ActivateSettingsCache(false);
DBup = SchemaUpgradeWizard::Get("DBSchemaVer", "MythTV",
currentDatabaseVersion);
// There may be a race condition where another program (e.g. mythbackend)
// is upgrading, so wait up to 5 seconds for a more accurate version:
DBup->CompareAndWait(5);
if (DBup->versionsBehind == 0) // same schema
{
gCoreContext->ActivateSettingsCache(true);
GetMythDB()->SetSuppressDBMessages(false);
return true;
}
if (!upgradeAllowed)
LOG(VB_GENERAL, LOG_ERR, "Not allowed to upgrade the database.");
// Pop up messages, questions, warnings, etc.
switch (DBup->PromptForUpgrade("TV", upgradeAllowed,
upgradeIfNoUI, MINIMUM_DBMS_VERSION))
{
case MYTH_SCHEMA_USE_EXISTING:
gCoreContext->ActivateSettingsCache(true);
GetMythDB()->SetSuppressDBMessages(false);
return true;
case MYTH_SCHEMA_ERROR:
case MYTH_SCHEMA_EXIT:
GetMythDB()->SetSuppressDBMessages(false);
return false;
case MYTH_SCHEMA_UPGRADE:
break;
}
MSqlQuery query(MSqlQuery::InitCon());
if (!query.exec(QString("ALTER DATABASE %1 DEFAULT"
" CHARACTER SET utf8 COLLATE utf8_general_ci;")
.arg(gCoreContext->GetDatabaseParams().dbName)))
MythDB::DBError("UpgradeTVDatabaseSchema -- alter charset", query);
LOG(VB_GENERAL, LOG_CRIT, "Newest MythTV Schema Version : "+
currentDatabaseVersion);
if (!DBUtil::lockSchema(query))
{
GetMythDB()->SetSuppressDBMessages(false);
return false;
}
bool ret = doUpgradeTVDatabaseSchema();
if (ret)
LOG(VB_GENERAL, LOG_NOTICE,
"Database Schema upgrade complete, unlocking.");
else
LOG(VB_GENERAL, LOG_ERR, "Database Schema upgrade FAILED, unlocking.");
DBUtil::unlockSchema(query);
gCoreContext->ActivateSettingsCache(true);
GetMythDB()->SetSuppressDBMessages(false);
return ret;
}
/** \fn doUpgradeTVDatabaseSchema(void)
* \brief This is called by UpgradeTVDatabaseSchema() to actually upgrade
* the schema to what MythTV expects.
*
* This function Initializes the database if the "DBSchemaVer" property
* does not exist in the database. Otherwise it upgrades the schema
* from the one in that property to the currentDatabaseVersion, by
* running the SQL commands needed for each upgrade between the two
* versions. This will only upgrade the schema, so bad things may
* happen if you upgrade and then downgrade MythTV across DB Schema
* versions.
*
*/
static bool doUpgradeTVDatabaseSchema(void)
{
QString dbver = gCoreContext->GetSetting("DBSchemaVer");
if (dbver == currentDatabaseVersion)
{
return true;
}
if (DBUtil::IsNewDatabase())
{
if (!InitializeMythSchema())
return false;
dbver = gCoreContext->GetSetting("DBSchemaVer");
}
if (dbver.isEmpty() || dbver.toInt() < 1027)
{
LOG(VB_GENERAL, LOG_ERR, "Unrecognized database schema version. "
"Unable to upgrade database.");
return false;
}
if (dbver == "1027")
{
const char *updates[] = {
"CREATE TABLE IF NOT EXISTS dvb_signal_quality ("
" id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,"
" sampletime TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP "
" ON UPDATE CURRENT_TIMESTAMP,"
" cardid INT UNSIGNED NOT NULL,"
" fe_snr INT UNSIGNED NOT NULL,"
" fe_ss INT UNSIGNED NOT NULL,"
" fe_ber INT UNSIGNED NOT NULL,"
" fe_unc INT UNSIGNED NOT NULL,"
" myth_cont INT UNSIGNED NOT NULL,"
" myth_over INT UNSIGNED NOT NULL,"
" myth_pkts INT UNSIGNED NOT NULL,"
" FOREIGN KEY(cardid) REFERENCES capturecard(id),"
" INDEX (sampletime,cardid)"
");",
"ALTER TABLE capturecard ADD skipbtaudio TINYINT(1) DEFAULT 0;",
NULL
};
if (!performActualUpdate(updates, "1028", dbver))
return false;
}
if (dbver == "1028") {
const char *updates[] = {
"ALTER TABLE channel ADD COLUMN commfree TINYINT NOT NULL default '0';",
"ALTER TABLE record ADD COLUMN recgroup VARCHAR(32) default 'Default';",
"ALTER TABLE record ADD COLUMN dupmethod INT NOT NULL DEFAULT 6;",
"ALTER TABLE record ADD COLUMN dupin INT NOT NULL DEFAULT 15;",
"UPDATE record SET dupmethod = 1 WHERE recorddups = 2;",
"UPDATE record SET dupin = 2 WHERE recorddups = 1;",
"ALTER TABLE record DROP COLUMN recorddups;",
"ALTER TABLE recorded ADD COLUMN recgroup VARCHAR(32) default 'Default';",
"ALTER TABLE recorded ADD COLUMN recordid INT DEFAULT NULL;",
"CREATE TABLE recgrouppassword ("
" recgroup VARCHAR(32) NOT NULL PRIMARY KEY, "
" password VARCHAR(10) NOT NULL, "
" UNIQUE(recgroup)"
");",
NULL
};
if (!performActualUpdate(updates, "1029", dbver))
return false;
}
if (dbver == "1029")
{
const char *updates[] = {
"ALTER TABLE record CHANGE preroll startoffset INT DEFAULT 0 NOT NULL;",
"ALTER TABLE record CHANGE postroll endoffset INT DEFAULT 0 NOT NULL;",
NULL
};
if (!performActualUpdate(updates, "1030", dbver))
return false;
}
if (dbver == "1030")
{
const char *updates[] = {
"ALTER TABLE channel ADD COLUMN visible TINYINT(1) NOT NULL default '1';",
"UPDATE channel SET visible = 1;",
NULL
};
if (!performActualUpdate(updates, "1031", dbver))
return false;
}
if (dbver == "1031") {
const char *updates[] = {
"ALTER TABLE capturecard ADD dvb_on_demand TINYINT NOT NULL DEFAULT 0;",
NULL
};
if (!performActualUpdate(updates, "1032", dbver))
return false;
}
if (dbver == "1032")
{
const char *updates[] = {
"UPDATE record SET dupmethod = 6 WHERE dupmethod = 22;",
NULL
};
if (!performActualUpdate(updates, "1033", dbver))
return false;
}
if (dbver == "1033")
{
const char *updates[] = {
"ALTER TABLE program ADD title_pronounce VARCHAR(128) NULL;",
"ALTER TABLE program ADD INDEX (title_pronounce);",
NULL
};
if (!performActualUpdate(updates, "1034", dbver))
return false;
}
if (dbver == "1034")
{
const char *updates[] = {
"CREATE TABLE mythlog ("
" logid int(10) unsigned PRIMARY KEY NOT NULL auto_increment,"
" module char(32) NOT NULL,"
" priority int(11) NOT NULL,"
" acknowledged bool default 0,"
" logdate datetime,"
" host varchar(128),"
" message varchar(255) NOT NULL,"
" details text"
");",
"CREATE TABLE housekeeping ("
" tag varchar(64) PRIMARY KEY NOT NULL,"
" lastrun datetime"
");",
NULL
};
if (!performActualUpdate(updates, "1035", dbver))
return false;
}
if (dbver == "1035")
{
const char *updates[] = {
"ALTER TABLE dvb_sat CHANGE pos pos FLOAT;",
"ALTER TABLE dvb_sat ADD diseqc_pos SMALLINT DEFAULT 0 AFTER diseqc_port;",
NULL
};
if (!performActualUpdate(updates,"1036", dbver))
return false;
}
if (dbver == "1036")
{
const char *updates[] = {
"UPDATE channel SET callsign=chanid WHERE callsign IS NULL OR callsign='';",
"ALTER TABLE record ADD COLUMN station VARCHAR(20) NOT NULL DEFAULT '';",
"ALTER TABLE recordoverride ADD COLUMN station VARCHAR(20) NOT NULL DEFAULT '';",
NULL
};
if (!performActualUpdate(updates, "1037", dbver))
return false;
}
if (dbver == "1037")
{
const char *updates[] = {
"ALTER TABLE videosource ADD lineupid VARCHAR(64) NULL;",
"ALTER TABLE videosource ADD password VARCHAR(64) NULL;",
"ALTER TABLE program ADD ( "
" stereo bool, "
" subtitled bool, "
" hdtv bool, "
" closecaptioned bool, "
" partnumber int, "
" parttotal int, "
" seriesid char(12), "
" originalairdate date, "
" showtype varchar(30), "
" colorcode varchar(20), "
" syndicatedepisodenumber varchar(20), "
" programid char(12) "
");",
"DELETE FROM credits;",
"ALTER TABLE credits DROP INDEX chanid;",
"ALTER TABLE credits ADD UNIQUE chanid (chanid, starttime, person, role);",
"DELETE FROM people;",
"ALTER TABLE people DROP INDEX name;",
"ALTER TABLE people ADD UNIQUE name (name(41));",
"CREATE TABLE programgenres ( "
" chanid int unsigned NOT NULL, "
" starttime timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, "
" relevance char(1) NOT NULL, "
" genre char(30), "
" PRIMARY KEY (chanid, starttime, relevance) "
");",
NULL
};
if (!performActualUpdate(updates, "1038", dbver))
return false;
}
if (dbver == "1038")
{
const char *updates[] = {
"CREATE TABLE IF NOT EXISTS programgenres ( "
" chanid int unsigned NOT NULL, "
" starttime timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, "
" relevance char(1) NOT NULL, "
" genre char(30), "
" PRIMARY KEY (chanid, starttime, relevance) "
");",
NULL
};
if (!performActualUpdate(updates, "1039", dbver))
return false;
}
if (dbver == "1039")
{
const char *updates[] = {
"ALTER TABLE channel CHANGE name name VARCHAR(64);",
NULL
};
if (!performActualUpdate(updates, "1040", dbver))
return false;
}
if (dbver == "1040")
{
const char *updates[] = {
"ALTER TABLE channel ADD outputfilters VARCHAR(255) NULL",
NULL
};
if (!performActualUpdate(updates, "1041", dbver))
return false;
}
if (dbver == "1041")
{
const char *updates[] = {
"ALTER TABLE record ADD seriesid varchar(12) NULL;",
"ALTER TABLE record ADD programid varchar(12) NULL;",
"ALTER TABLE recorded ADD seriesid varchar(12) NULL;",
"ALTER TABLE recorded ADD programid varchar(12) NULL;",
"ALTER TABLE oldrecorded ADD seriesid varchar(12) NULL;",
"ALTER TABLE oldrecorded ADD programid varchar(12) NULL;",
"ALTER TABLE program ADD INDEX (seriesid);",
"ALTER TABLE program ADD INDEX (programid);",
"ALTER TABLE record ADD INDEX (seriesid);",
"ALTER TABLE record ADD INDEX (programid);",
"ALTER TABLE recorded ADD INDEX (seriesid);",
"ALTER TABLE recorded ADD INDEX (programid);",
"ALTER TABLE oldrecorded ADD INDEX (seriesid);",
"ALTER TABLE oldrecorded ADD INDEX (programid);",
NULL
};
if (!performActualUpdate(updates, "1042", dbver))
return false;
}
if (dbver == "1042")
{
const char *updates[] = {
"INSERT INTO settings SET value=\"DataDirectMessage\";",
NULL
};
if (!performActualUpdate(updates, "1043", dbver))
return false;
}
if (dbver == "1043")
{
const char *updates[] = {
"ALTER TABLE program CHANGE title title VARCHAR(128) NOT NULL DEFAULT '';",
"ALTER TABLE program CHANGE subtitle subtitle VARCHAR(128) NOT NULL DEFAULT '';",
"ALTER TABLE program CHANGE description description TEXT NOT NULL DEFAULT '';",
"ALTER TABLE program CHANGE category category VARCHAR(64) NOT NULL DEFAULT '';",
"ALTER TABLE program CHANGE category_type category_type VARCHAR(64) NOT NULL DEFAULT '';",
"ALTER TABLE program CHANGE title_pronounce title_pronounce VARCHAR(128) NOT NULL DEFAULT '';",
"ALTER TABLE program CHANGE seriesid seriesid VARCHAR(12) NOT NULL DEFAULT '';",
"ALTER TABLE program CHANGE showtype showtype VARCHAR(30) NOT NULL DEFAULT '';",
"ALTER TABLE program CHANGE colorcode colorcode VARCHAR(20) NOT NULL DEFAULT '';",
"ALTER TABLE program CHANGE syndicatedepisodenumber syndicatedepisodenumber VARCHAR(20) NOT NULL DEFAULT '';",
"ALTER TABLE program CHANGE programid programid VARCHAR(12) NOT NULL DEFAULT '';",
"ALTER TABLE channel CHANGE channum channum VARCHAR(5) NOT NULL DEFAULT '';",
"ALTER TABLE channel CHANGE callsign callsign VARCHAR(20) NOT NULL DEFAULT '';",
"ALTER TABLE channel CHANGE name name VARCHAR(64) NOT NULL DEFAULT '';",
"ALTER TABLE channel CHANGE icon icon VARCHAR(255) NOT NULL DEFAULT 'none';",
"ALTER TABLE channel CHANGE videofilters videofilters VARCHAR(255) NOT NULL DEFAULT '';",
"ALTER TABLE channel CHANGE xmltvid xmltvid VARCHAR(64) NOT NULL DEFAULT '';",
"ALTER TABLE channel CHANGE outputfilters outputfilters VARCHAR(255) NOT NULL DEFAULT '';",
"ALTER TABLE record CHANGE title title VARCHAR(128) NOT NULL DEFAULT '';",
"ALTER TABLE record CHANGE subtitle subtitle VARCHAR(128) NOT NULL DEFAULT '';",
"ALTER TABLE record CHANGE description description TEXT NOT NULL DEFAULT '';",
"ALTER TABLE record CHANGE profile profile VARCHAR(128) NOT NULL DEFAULT 'Default';",
"ALTER TABLE record CHANGE category category VARCHAR(64) NOT NULL DEFAULT '';",
"ALTER TABLE record CHANGE recgroup recgroup VARCHAR(32) NOT NULL DEFAULT 'Default';",
"ALTER TABLE record CHANGE station station VARCHAR(20) NOT NULL DEFAULT '';",
"ALTER TABLE record CHANGE seriesid seriesid VARCHAR(12) NOT NULL DEFAULT '';",
"ALTER TABLE record CHANGE programid programid VARCHAR(12) NOT NULL DEFAULT '';",
"ALTER TABLE recorded CHANGE title title VARCHAR(128) NOT NULL DEFAULT '';",
"ALTER TABLE recorded CHANGE subtitle subtitle VARCHAR(128) NOT NULL DEFAULT '';",
"ALTER TABLE recorded CHANGE description description TEXT NOT NULL DEFAULT '';",
"ALTER TABLE recorded CHANGE hostname hostname varchar(64) NOT NULL DEFAULT '';",
"ALTER TABLE recorded CHANGE category category VARCHAR(64) NOT NULL DEFAULT '';",
"ALTER TABLE recorded CHANGE recgroup recgroup VARCHAR(32) NOT NULL DEFAULT 'Default';",
"ALTER TABLE recorded CHANGE seriesid seriesid VARCHAR(12) NOT NULL DEFAULT '';",
"ALTER TABLE recorded CHANGE programid programid VARCHAR(12) NOT NULL DEFAULT '';",
"ALTER TABLE oldrecorded CHANGE title title VARCHAR(128) NOT NULL DEFAULT '';",
"ALTER TABLE oldrecorded CHANGE subtitle subtitle VARCHAR(128) NOT NULL DEFAULT '';",
"ALTER TABLE oldrecorded CHANGE description description TEXT NOT NULL DEFAULT '';",
"ALTER TABLE oldrecorded CHANGE category category VARCHAR(64) NOT NULL DEFAULT '';",
"ALTER TABLE oldrecorded CHANGE seriesid seriesid VARCHAR(12) NOT NULL DEFAULT '';",
"ALTER TABLE oldrecorded CHANGE programid programid VARCHAR(12) NOT NULL DEFAULT '';",
NULL
};
if (!performActualUpdate(updates, "1044", dbver))
return false;
}
if (dbver == "1044")
{
const char *updates[] = {
"UPDATE channel SET icon = 'none' WHERE icon = '';",
"UPDATE record SET profile = 'Default' WHERE profile = '';",
"UPDATE record SET recgroup = 'Default' WHERE recgroup = '';",
"UPDATE recorded SET recgroup = 'Default', starttime = starttime WHERE recgroup = '';",
NULL
};
if (!performActualUpdate(updates, "1045", dbver))
return false;
}
if (dbver == "1045")
{
const char *updates[] = {
"UPDATE recorded SET recgroup = 'Default', starttime = starttime WHERE recgroup = '';",
NULL
};
if (!performActualUpdate(updates, "1046", dbver))
return false;
}
if (dbver == "1046")
{
const char *updates[] = {
"ALTER TABLE record ADD COLUMN search INT UNSIGNED NOT NULL DEFAULT 0;",
"UPDATE record SET search = 0 WHERE search IS NULL;",
NULL
};
if (!performActualUpdate(updates, "1047", dbver))
return false;
}
if (dbver == "1047")
{
const char *updates[] = {
"CREATE TABLE networkiconmap ("
" id INTEGER NOT NULL AUTO_INCREMENT,"
" network VARCHAR(20) NOT NULL UNIQUE,"
" url VARCHAR(255) NOT NULL,"
" PRIMARY KEY(id)"
");",
"CREATE TABLE callsignnetworkmap ("
" id INTEGER NOT NULL AUTO_INCREMENT,"
" callsign VARCHAR(20) NOT NULL UNIQUE,"
" network VARCHAR(20) NOT NULL,"
" PRIMARY KEY(id)"
");",
NULL
};
if (!performActualUpdate(updates, "1048", dbver))
return false;
}
if (dbver == "1048")
{
const char *updates[] = {
"ALTER TABLE cardinput CHANGE preference preference INT NOT NULL DEFAULT 0;",
"UPDATE cardinput SET preference = 0 WHERE preference IS NULL;",
NULL
};
if (!performActualUpdate(updates, "1049", dbver))
return false;
}
if (dbver == "1049")
{
const char *updates[] = {
"ALTER TABLE keyword ADD COLUMN searchtype INT UNSIGNED NOT NULL DEFAULT 3;",
"ALTER TABLE keyword DROP INDEX phrase;",
"ALTER TABLE keyword DROP PRIMARY KEY;",
"ALTER TABLE keyword ADD UNIQUE(phrase,searchtype);",
NULL
};
if (!performActualUpdate(updates, "1050", dbver))
return false;
}
if (dbver == "1050")
{
const char *updates[] = {
"ALTER TABLE recorded CHANGE starttime starttime DATETIME NOT NULL;",
"ALTER TABLE recorded CHANGE endtime endtime DATETIME NOT NULL;",
"ALTER TABLE recorded ADD COLUMN lastmodified TIMESTAMP NOT NULL "
" DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;",
"ALTER TABLE recorded ADD COLUMN filesize BIGINT(20) DEFAULT 0 NOT NULL;",
"ALTER TABLE credits CHANGE starttime starttime DATETIME NOT NULL;",
"ALTER TABLE oldprogram CHANGE airdate airdate DATETIME NOT NULL;",
"ALTER TABLE oldrecorded CHANGE starttime starttime DATETIME NOT NULL;",
"ALTER TABLE oldrecorded CHANGE endtime endtime DATETIME NOT NULL;",
"ALTER TABLE program CHANGE starttime starttime DATETIME NOT NULL;",
"ALTER TABLE program CHANGE endtime endtime DATETIME NOT NULL;",
"ALTER TABLE programgenres CHANGE starttime starttime DATETIME NOT NULL;",
"ALTER TABLE programrating CHANGE starttime starttime DATETIME NOT NULL;",
"ALTER TABLE recordedmarkup CHANGE starttime starttime DATETIME NOT NULL;",
"ALTER TABLE recordoverride CHANGE starttime starttime DATETIME NOT NULL;",
"ALTER TABLE recordoverride CHANGE endtime endtime DATETIME NOT NULL;",
"ALTER TABLE transcoding CHANGE starttime starttime DATETIME NOT NULL;",
NULL
};
if (!performActualUpdate(updates, "1051", dbver))
return false;
}
if (dbver == "1051")
{
const char *updates[] = {
"update record set dupmethod = 6 where dupmethod = 0;",
"update record set dupin = 15 where dupin = 0;",
NULL
};
if (!performActualUpdate(updates, "1052", dbver))
return false;
}
if (dbver == "1052")
{
const char *updates[] = {
"ALTER TABLE recorded ADD COLUMN stars FLOAT NOT NULL DEFAULT 0;",
"ALTER TABLE recorded ADD COLUMN previouslyshown TINYINT(1) DEFAULT 0;",
"ALTER TABLE recorded ADD COLUMN originalairdate DATE;",
"INSERT INTO settings VALUES ('HaveRepeats', '0', NULL);",
NULL
};
if (!performActualUpdate(updates, "1053", dbver))
return false;
}