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
|
using System.Text.Json;
using Dapper;
using I2R.LightNews.Utilities;
using Microsoft.Data.Sqlite;
namespace I2R.LightNews;
public static class RadioIndexDb
{
private static readonly string ConnectionString = "data source=AppData/radio-index.db";
public static int AddSeries(RadioSeries entry) {
using var db = new SqliteConnection(ConnectionString);
if (!db.TableExists("series")) return -1;
return db.ExecuteScalar<int>(@"
insert into series(name,description,canonical_url,nrk_id) values (@name,@description,@canonical_url,@nrk_id);
select last_insert_rowid();", new {
name = entry.Name,
description = entry.Description,
canonical_url = entry.CanonicalUrl,
nrk_id = entry.NrkId
});
}
public static int AddSeason(RadioSeason entry) {
using var db = new SqliteConnection(ConnectionString);
if (!db.TableExists("seasons")) return -1;
return db.ExecuteScalar<int>(@"
insert into seasons(name,description,canonical_url,nrk_id,series_id) values (@name,@description,@canonical_url,@nrk_id,@series_id);
select last_insert_rowid();", new {
name = entry.Name,
description = entry.Description,
canonical_url = entry.CanonicalUrl,
nrk_id = entry.NrkId,
series_id = entry.SeriesId
});
}
public static int AddEpisode(RadioEpisode entry) {
using var db = new SqliteConnection(ConnectionString);
if (!db.TableExists("episodes")) return -1;
return db.ExecuteScalar<int>(@"
insert into episodes(name,description,canonical_url,nrk_id,series_id,season_id,source_url) values (@name,@description,@canonical_url,@nrk_id,@series_id,@season_id,@source_url);
select last_insert_rowid();", new {
name = entry.Name,
description = entry.Description,
canonical_url = entry.CanonicalUrl,
nrk_id = entry.NrkId,
series_id = entry.SeriesId,
season_id = entry.SeasonId,
source_url = entry.SourceUrl,
});
}
public static RadioSeries GetSeriesByNrkId(string nrkId) {
using var db = new SqliteConnection(ConnectionString);
if (!db.TableExists("series")) return default;
return db.QueryFirstOrDefault<RadioSeries>(@"select * from series where nrk_id=@nrkId", new {nrkId});
}
public static RadioSeries GetSeriesById(int id) {
using var db = new SqliteConnection(ConnectionString);
if (!db.TableExists("series")) return default;
return db.QueryFirstOrDefault<RadioSeries>(@"select * from series where id=@id", new {id});
}
public static SeriesDetails GetSeriesDetailsById(int id) {
using var db = new SqliteConnection(ConnectionString);
if (!db.TableExists("series")) return default;
return db.QueryFirstOrDefault<SeriesDetails>(@"
select id as s_id, name as s_name from seasons where series_id=1;
select id as e_id, name as e_name, source_url as e_source from episodes where series_id=1;
", new {id});
}
public class SeriesDetails
{
public List<RadioSeason> Seasons { get; set; }
public List<RadioEpisode> Episodes { get; set; }
}
public static RadioSeason GetSeasonByNrkId(string nrkId) {
using var db = new SqliteConnection(ConnectionString);
if (!db.TableExists("seasons")) return default;
return db.QueryFirstOrDefault<RadioSeason>(@"select * from seasons where nrk_id=@nrkId", new {nrkId});
}
public static RadioEpisode GetEpisodeByNrkId(string nrkId) {
using var db = new SqliteConnection(ConnectionString);
if (!db.TableExists("episodes")) return default;
return db.QueryFirstOrDefault<RadioEpisode>(@"select * from episodes where nrk_id=@nrkId", new {nrkId});
}
public static List<RadioSeries> GetSeries(string query, bool includeEpisodes = false) {
using var db = new SqliteConnection(ConnectionString);
if (!db.TableExists("series")) return default;
var selectSet = includeEpisodes ? "*" : "id,name,description,type,canonical_url";
var result = query.HasValue()
? db.Query<RadioSeries>(@$"select {selectSet} from series where name like '@query' || description like '@query' order by name")
: db.Query<RadioSeries>(@$"select {selectSet} from series order by name");
return result.ToList();
}
public static void CreateIfNotExists() {
using var db = new SqliteConnection(ConnectionString);
if (!db.TableExists("series")) {
db.Execute(@"
create table series(
id integer primary key autoincrement,
name text,
description text,
type text,
canonical_url text,
nrk_id text
)
");
}
if (!db.TableExists("seasons")) {
db.Execute(@"
create table seasons(
id integer primary key autoincrement,
series_id integer,
name text,
description text,
canonical_url text,
nrk_id text
)
");
}
if (!db.TableExists("episodes")) {
db.Execute(@"
create table episodes(
id integer primary key autoincrement,
series_id integer,
season_id integer,
name text,
description text,
canonical_url text,
source_url text,
nrk_id text
)
");
}
}
}
|
