~ubuntu-branches/ubuntu/trusty/forked-daapd/trusty-proposed : revision 1.2.3

276

static const char *sort_clause[] =

277

{

278

"",

279

"ORDER BY title_sort ASC",

280

"ORDER BY album_sort ASC, disc ASC, track ASC",

281

"ORDER BY artist_sort ASC",

279

"ORDER BY f.title_sort ASC",

280

"ORDER BY f.album_sort ASC, f.disc ASC, f.track ASC",

281

"ORDER BY f.artist_sort ASC",

282

};

283

284

static char *db_path;

591

}

592

593

594

/* Maintenance and DB hygiene */

595

static void

596

db_analyze(void)

597

{

598

char *query = "ANALYZE;";

599

char *errmsg;

600

int ret;

601

602

DPRINTF(E_DBG, L_DB, "Running query '%s'\n", query);

603

604

ret = db_exec(query, &errmsg);

605

if (ret != SQLITE_OK)

606

{

607

DPRINTF(E_LOG, L_DB, "ANALYZE failed: %s\n", errmsg);

608

609

sqlite3_free(errmsg);

610

}

611

}

612

613

void

614

db_hook_post_scan(void)

615

{

616

DPRINTF(E_DBG, L_DB, "Running post-scan DB maintenance tasks...\n");

617

618

db_analyze();

619

620

DPRINTF(E_DBG, L_DB, "Done with post-scan DB maintenance\n");

621

}

622

594

623

void

595

624

db_purge_cruft(time_t ref)

596

625

{

600

629

char *queries[3] = { NULL, NULL, NULL };

601

630

char *queries_tmpl[3] =

602

631

{

603

"DELETE FROM playlistitems WHERE playlistid IN (SELECT id FROM playlists WHERE type <> 1 AND db_timestamp < %" PRIi64 ");",

632

"DELETE FROM playlistitems WHERE playlistid IN (SELECT id FROM playlists p WHERE p.type <> 1 AND p.db_timestamp < %" PRIi64 ");",

604

633

"DELETE FROM playlists WHERE type <> 1 AND db_timestamp < %" PRIi64 ";",

605

634

"DELETE FROM files WHERE db_timestamp < %" PRIi64 ";"

606

635

};

670

699

671

700

ret = sqlite3_column_int(stmt, 0);

672

701

702

#ifdef DB_PROFILE

703

while (db_blocking_step(stmt) == SQLITE_ROW)

704

; /* EMPTY */

705

#endif

706

673

707

sqlite3_finalize(stmt);

674

708

675

709

return ret;

726

760

int ret;

727

761

728

762

if (qp->filter)

729

count = sqlite3_mprintf("SELECT COUNT(*) FROM files WHERE disabled = 0 AND %s;", qp->filter);

763

count = sqlite3_mprintf("SELECT COUNT(*) FROM files f WHERE f.disabled = 0 AND %s;", qp->filter);

730

764

else

731

count = sqlite3_mprintf("SELECT COUNT(*) FROM files WHERE disabled = 0;");

765

count = sqlite3_mprintf("SELECT COUNT(*) FROM files f WHERE f.disabled = 0;");

732

766

733

767

if (!count)

734

768

{

751

785

sort = sort_clause[qp->sort];

752

786

753

787

if (idx && qp->filter)

754

query = sqlite3_mprintf("SELECT * FROM files WHERE disabled = 0 AND %s %s %s;", qp->filter, sort, idx);

788

query = sqlite3_mprintf("SELECT f.* FROM files f WHERE f.disabled = 0 AND %s %s %s;", qp->filter, sort, idx);

755

789

else if (idx)

756

query = sqlite3_mprintf("SELECT * FROM files WHERE disabled = 0 %s %s;", sort, idx);

790

query = sqlite3_mprintf("SELECT f.* FROM files f WHERE f.disabled = 0 %s %s;", sort, idx);

757

791

else if (qp->filter)

758

query = sqlite3_mprintf("SELECT * FROM files WHERE disabled = 0 AND %s %s;", qp->filter, sort);

792

query = sqlite3_mprintf("SELECT f.* FROM files f WHERE f.disabled = 0 AND %s %s;", qp->filter, sort);

759

793

else

760

query = sqlite3_mprintf("SELECT * FROM files WHERE disabled = 0 %s;", sort);

794

query = sqlite3_mprintf("SELECT f.* FROM files f WHERE f.disabled = 0 %s;", sort);

761

795

762

796

if (!query)

763

797

{

777

811

char *idx;

778

812

int ret;

779

813

780

qp->results = db_get_count("SELECT COUNT(*) FROM playlists WHERE disabled = 0;");

814

qp->results = db_get_count("SELECT COUNT(*) FROM playlists p WHERE p.disabled = 0;");

781

815

if (qp->results < 0)

782

816

return -1;

783

817

787

821

return -1;

788

822

789

823

if (idx && qp->filter)

790

query = sqlite3_mprintf("SELECT * FROM playlists WHERE disabled = 0 AND %s %s;", qp->filter, idx);

824

query = sqlite3_mprintf("SELECT f.* FROM playlists f WHERE f.disabled = 0 AND %s %s;", qp->filter, idx);

791

825

else if (idx)

792

query = sqlite3_mprintf("SELECT * FROM playlists WHERE disabled = 0 %s;", idx);

826

query = sqlite3_mprintf("SELECT f.* FROM playlists f WHERE f.disabled = 0 %s;", idx);

793

827

else if (qp->filter)

794

query = sqlite3_mprintf("SELECT * FROM playlists WHERE disabled = 0 AND %s;", qp->filter);

828

query = sqlite3_mprintf("SELECT f.* FROM playlists f WHERE f.disabled = 0 AND %s;", qp->filter);

795

829

else

796

query = sqlite3_mprintf("SELECT * FROM playlists WHERE disabled = 0;");

830

query = sqlite3_mprintf("SELECT f.* FROM playlists f WHERE f.disabled = 0;");

797

831

798

832

if (!query)

799

833

{

815

849

int ret;

816

850

817

851

if (qp->filter)

818

count = sqlite3_mprintf("SELECT COUNT(*) FROM files JOIN playlistitems ON files.path = playlistitems.filepath"

819

" WHERE playlistitems.playlistid = %d AND files.disabled = 0 AND %s;", qp->id, qp->filter);

852

count = sqlite3_mprintf("SELECT COUNT(*) FROM files f JOIN playlistitems pi ON f.path = pi.filepath"

853

" WHERE pi.playlistid = %d AND f.disabled = 0 AND %s;", qp->id, qp->filter);

820

854

else

821

count = sqlite3_mprintf("SELECT COUNT(*) FROM files JOIN playlistitems ON files.path = playlistitems.filepath"

822

" WHERE playlistitems.playlistid = %d AND files.disabled = 0;", qp->id);

855

count = sqlite3_mprintf("SELECT COUNT(*) FROM files f JOIN playlistitems pi ON f.path = pi.filepath"

856

" WHERE pi.playlistid = %d AND f.disabled = 0;", qp->id);

823

857

824

858

if (!count)

825

859

{

840

874

return -1;

841

875

842

876

if (idx && qp->filter)

843

query = sqlite3_mprintf("SELECT files.* FROM files JOIN playlistitems ON files.path = playlistitems.filepath"

844

" WHERE playlistitems.playlistid = %d AND files.disabled = 0 AND %s ORDER BY playlistitems.id ASC %s;",

877

query = sqlite3_mprintf("SELECT f.* FROM files f JOIN playlistitems pi ON f.path = pi.filepath"

878

" WHERE pi.playlistid = %d AND f.disabled = 0 AND %s ORDER BY pi.id ASC %s;",

845

879

qp->id, qp->filter, idx);

846

880

else if (idx)

847

query = sqlite3_mprintf("SELECT files.* FROM files JOIN playlistitems ON files.path = playlistitems.filepath"

848

" WHERE playlistitems.playlistid = %d AND files.disabled = 0 ORDER BY playlistitems.id ASC %s;",

881

query = sqlite3_mprintf("SELECT f.* FROM files f JOIN playlistitems pi ON f.path = pi.filepath"

882

" WHERE pi.playlistid = %d AND f.disabled = 0 ORDER BY pi.id ASC %s;",

849

883

qp->id, idx);

850

884

else if (qp->filter)

851

query = sqlite3_mprintf("SELECT files.* FROM files JOIN playlistitems ON files.path = playlistitems.filepath"

852

" WHERE playlistitems.playlistid = %d AND files.disabled = 0 AND %s ORDER BY playlistitems.id ASC;",

885

query = sqlite3_mprintf("SELECT f.* FROM files f JOIN playlistitems pi ON f.path = pi.filepath"

886

" WHERE pi.playlistid = %d AND f.disabled = 0 AND %s ORDER BY pi.id ASC;",

853

887

qp->id, qp->filter);

854

888

else

855

query = sqlite3_mprintf("SELECT files.* FROM files JOIN playlistitems ON files.path = playlistitems.filepath"

856

" WHERE playlistitems.playlistid = %d AND files.disabled = 0 ORDER BY playlistitems.id ASC;",

889

query = sqlite3_mprintf("SELECT f.* FROM files f JOIN playlistitems pi ON f.path = pi.filepath"

890

" WHERE pi.playlistid = %d AND f.disabled = 0 ORDER BY pi.id ASC;",

857

891

qp->id);

858

892

859

893

if (!query)

882

916

else

883

917

filter = "1 = 1";

884

918

885

count = sqlite3_mprintf("SELECT COUNT(*) FROM files WHERE disabled = 0 AND %s AND %s;", filter, smartpl_query);

919

count = sqlite3_mprintf("SELECT COUNT(*) FROM files f WHERE f.disabled = 0 AND %s AND %s;", filter, smartpl_query);

886

920

if (!count)

887

921

{

888

922

DPRINTF(E_LOG, L_DB, "Out of memory for count query string\n");

906

940

907

941

sort = sort_clause[qp->sort];

908

942

909

query = sqlite3_mprintf("SELECT * FROM files WHERE disabled = 0 AND %s AND %s %s %s;", smartpl_query, filter, sort, idx);

943

query = sqlite3_mprintf("SELECT f.* FROM files f WHERE f.disabled = 0 AND %s AND %s %s %s;", smartpl_query, filter, sort, idx);

910

944

if (!query)

911

945

{

912

946

DPRINTF(E_LOG, L_DB, "Out of memory for query string\n");

962

996

char *idx;

963

997

int ret;

964

998

965

qp->results = db_get_count("SELECT COUNT(DISTINCT songalbumid) FROM files WHERE disabled = 0;");

999

qp->results = db_get_count("SELECT COUNT(DISTINCT f.songalbumid) FROM files f WHERE f.disabled = 0;");

966

1000

if (qp->results < 0)

967

1001

return -1;

968

1002

972

1006

return -1;

973

1007

974

1008

if (idx && qp->filter)

975

query = sqlite3_mprintf("SELECT COUNT(*), g.id, g.persistentid, f.album_artist, g.name FROM files f JOIN groups g ON f.songalbumid = g.persistentid GROUP BY f.album, g.name HAVING g.type = %d AND disabled = 0 AND %s %s;", G_ALBUMS, qp->filter, idx);

1009

query = sqlite3_mprintf("SELECT COUNT(*), g.id, g.persistentid, f.album_artist, g.name FROM files f, groups g WHERE f.songalbumid = g.persistentid AND g.type = %d AND f.disabled = 0 AND %s GROUP BY f.album, g.name %s;", G_ALBUMS, qp->filter, idx);

976

1010

else if (idx)

977

query = sqlite3_mprintf("SELECT COUNT(*), g.id, g.persistentid, f.album_artist, g.name FROM files f JOIN groups g ON f.songalbumid = g.persistentid GROUP BY f.album, g.name HAVING g.type = %d AND disabled = 0 %s;", G_ALBUMS, idx);

1011

query = sqlite3_mprintf("SELECT COUNT(*), g.id, g.persistentid, f.album_artist, g.name FROM files f, groups g WHERE f.songalbumid = g.persistentid AND g.type = %d AND f.disabled = 0 GROUP BY f.album, g.name %s;", G_ALBUMS, idx);

978

1012

else if (qp->filter)

979

query = sqlite3_mprintf("SELECT COUNT(*), g.id, g.persistentid, f.album_artist, g.name FROM files f JOIN groups g ON f.songalbumid = g.persistentid GROUP BY f.album, g.name HAVING g.type = %d AND disabled = 0 AND %s;", G_ALBUMS, qp->filter);

1013

query = sqlite3_mprintf("SELECT COUNT(*), g.id, g.persistentid, f.album_artist, g.name FROM files f, groups g WHERE f.songalbumid = g.persistentid AND g.type = %d AND f.disabled = 0 AND %s GROUP BY f.album, g.name;", G_ALBUMS, qp->filter);

980

1014

else

981

query = sqlite3_mprintf("SELECT COUNT(*), g.id, g.persistentid, f.album_artist, g.name FROM files f JOIN groups g ON f.songalbumid = g.persistentid GROUP BY f.album, g.name HAVING g.type = %d AND disabled = 0;", G_ALBUMS);

1015

query = sqlite3_mprintf("SELECT COUNT(*), g.id, g.persistentid, f.album_artist, g.name FROM files f, groups g WHERE f.songalbumid = g.persistentid AND g.type = %d AND f.disabled = 0 GROUP BY f.album, g.name;", G_ALBUMS);

982

1016

983

1017

if (!query)

984

1018

{

1003

1037

switch (gt)

1004

1038

{

1005

1039

case G_ALBUMS:

1006

count = sqlite3_mprintf("SELECT COUNT(*) FROM files JOIN groups ON files.songalbumid = groups.persistentid"

1007

" WHERE groups.id = %d AND files.disabled = 0;", qp->id);

1040

count = sqlite3_mprintf("SELECT COUNT(*) FROM files f JOIN groups g ON f.songalbumid = g.persistentid"

1041

" WHERE g.id = %d AND f.disabled = 0;", qp->id);

1008

1042

break;

1009

1043

1010

1044

default:

1028

1062

switch (gt)

1029

1063

{

1030

1064

case G_ALBUMS:

1031

query = sqlite3_mprintf("SELECT files.* FROM files JOIN groups ON files.songalbumid = groups.persistentid"

1032

" WHERE groups.id = %d AND files.disabled = 0;", qp->id);

1065

query = sqlite3_mprintf("SELECT f.* FROM files f JOIN groups g ON f.songalbumid = g.persistentid"

1066

" WHERE g.id = %d AND f.disabled = 0;", qp->id);

1033

1067

break;

1034

1068

}

1035

1069

1056

1090

switch (gt)

1057

1091

{

1058

1092

case G_ALBUMS:

1059

count = sqlite3_mprintf("SELECT COUNT(DISTINCT(SUBSTR(files.path, 1, LENGTH(files.path) - LENGTH(files.fname) - 1)))"

1060

" FROM files JOIN groups ON files.songalbumid = groups.persistentid"

1061

" WHERE groups.id = %d AND files.disabled = 0;", qp->id);

1093

count = sqlite3_mprintf("SELECT COUNT(DISTINCT(SUBSTR(f.path, 1, LENGTH(f.path) - LENGTH(f.fname) - 1)))"

1094

" FROM files f JOIN groups g ON f.songalbumid = g.persistentid"

1095

" WHERE g.id = %d AND f.disabled = 0;", qp->id);

1062

1096

break;

1063

1097

1064

1098

default:

1082

1116

switch (gt)

1083

1117

{

1084

1118

case G_ALBUMS:

1085

query = sqlite3_mprintf("SELECT DISTINCT(SUBSTR(files.path, 1, LENGTH(files.path) - LENGTH(files.fname) - 1))"

1086

" FROM files JOIN groups ON files.songalbumid = groups.persistentid"

1087

" WHERE groups.id = %d AND files.disabled = 0;", qp->id);

1119

query = sqlite3_mprintf("SELECT DISTINCT(SUBSTR(f.path, 1, LENGTH(f.path) - LENGTH(f.fname) - 1))"

1120

" FROM files f JOIN groups g ON f.songalbumid = g.persistentid"

1121

" WHERE g.id = %d AND f.disabled = 0;", qp->id);

1088

1122

break;

1089

1123

}

1090

1124

1108

1142

int ret;

1109

1143

1110

1144

if (qp->filter)

1111

count = sqlite3_mprintf("SELECT COUNT(DISTINCT %s) FROM files WHERE data_kind = 0 AND disabled = 0 AND %s != '' AND %s;",

1145

count = sqlite3_mprintf("SELECT COUNT(DISTINCT f.%s) FROM files f WHERE f.data_kind = 0 AND f.disabled = 0 AND f.%s != '' AND %s;",

1112

1146

field, field, qp->filter);

1113

1147

else

1114

count = sqlite3_mprintf("SELECT COUNT(DISTINCT %s) FROM files WHERE data_kind = 0 AND disabled = 0 AND %s != '';",

1148

count = sqlite3_mprintf("SELECT COUNT(DISTINCT f.%s) FROM files f WHERE f.data_kind = 0 AND f.disabled = 0 AND f.%s != '';",

1115

1149

field, field);

1116

1150

1117

1151

if (!count)

1133

1167

return -1;

1134

1168

1135

1169

if (idx && qp->filter)

1136

query = sqlite3_mprintf("SELECT DISTINCT %s, %s FROM files WHERE data_kind = 0 AND disabled = 0 AND %s != ''"

1170

query = sqlite3_mprintf("SELECT DISTINCT f.%s, f.%s FROM files f WHERE f.data_kind = 0 AND f.disabled = 0 AND f.%s != ''"

1137

1171

" AND %s %s;", field, field, field, qp->filter, idx);

1138

1172

else if (idx)

1139

query = sqlite3_mprintf("SELECT DISTINCT %s, %s FROM files WHERE data_kind = 0 AND disabled = 0 AND %s != ''"

1173

query = sqlite3_mprintf("SELECT DISTINCT f.%s, f.%s FROM files f WHERE f.data_kind = 0 AND f.disabled = 0 AND f.%s != ''"

1140

1174

" %s;", field, field, field, idx);

1141

1175

else if (qp->filter)

1142

query = sqlite3_mprintf("SELECT DISTINCT %s, %s FROM files WHERE data_kind = 0 AND disabled = 0 AND %s != ''"

1176

query = sqlite3_mprintf("SELECT DISTINCT f.%s, f.%s FROM files f WHERE f.data_kind = 0 AND f.disabled = 0 AND f.%s != ''"

1143

1177

" AND %s;", field, field, field, qp->filter);

1144

1178

else

1145

query = sqlite3_mprintf("SELECT DISTINCT %s, %s FROM files WHERE data_kind = 0 AND disabled = 0 AND %s != ''",

1179

query = sqlite3_mprintf("SELECT DISTINCT f.%s, f.%s FROM files f WHERE f.data_kind = 0 AND f.disabled = 0 AND f.%s != ''",

1146

1180

field, field, field);

1147

1181

1148

1182

if (!query)

1510

1544

int

1511

1545

db_files_get_count(void)

1512

1546

{

1513

return db_get_count("SELECT COUNT(*) FROM files WHERE disabled = 0;");

1547

return db_get_count("SELECT COUNT(*) FROM files f WHERE f.disabled = 0;");

1514

1548

}

1515

1549

1516

1550

void

1560

1594

}

1561

1595

1562

1596

void

1563

db_file_ping(char *path)

1597

db_file_ping(int id)

1564

1598

{

1565

#define Q_TMPL "UPDATE files SET db_timestamp = %" PRIi64 ", disabled = 0 WHERE path = '%q';"

1599

#define Q_TMPL "UPDATE files SET db_timestamp = %" PRIi64 ", disabled = 0 WHERE id = %d;"

1566

1600

char *query;

1567

1601

char *errmsg;

1568

1602

int ret;

1569

1603

1570

query = sqlite3_mprintf(Q_TMPL, (int64_t)time(NULL), path);

1604

query = sqlite3_mprintf(Q_TMPL, (int64_t)time(NULL), id);

1571

1605

if (!query)

1572

1606

{

1573

1607

DPRINTF(E_LOG, L_DB, "Out of memory for query string\n");

1579

1613

1580

1614

ret = db_exec(query, &errmsg);

1581

1615

if (ret != SQLITE_OK)

1582

DPRINTF(E_LOG, L_DB, "Error pinging file '%s': %s\n", path, errmsg);

1616

DPRINTF(E_LOG, L_DB, "Error pinging file ID %d: %s\n", id, errmsg);

1583

1617

1584

1618

sqlite3_free(errmsg);

1585

1619

sqlite3_free(query);

1590

1624

char *

1591

1625

db_file_path_byid(int id)

1592

1626

{

1593

#define Q_TMPL "SELECT path FROM files WHERE id = %d;"

1627

#define Q_TMPL "SELECT f.path FROM files f WHERE f.id = %d;"

1594

1628

char *query;

1595

1629

sqlite3_stmt *stmt;

1596

1630

char *res;

1632

1666

if (res)

1633

1667

res = strdup(res);

1634

1668

1669

#ifdef DB_PROFILE

1670

while (db_blocking_step(stmt) == SQLITE_ROW)

1671

; /* EMPTY */

1672

#endif

1673

1635

1674

sqlite3_finalize(stmt);

1636

1675

sqlite3_free(query);

1637

1676

1673

1712

1674

1713

ret = sqlite3_column_int(stmt, 0);

1675

1714

1715

#ifdef DB_PROFILE

1716

while (db_blocking_step(stmt) == SQLITE_ROW)

1717

; /* EMPTY */

1718

#endif

1719

1676

1720

sqlite3_finalize(stmt);

1677

1721

1678

1722

return ret;

1681

1725

int

1682

1726

db_file_id_bypath(char *path)

1683

1727

{

1684

#define Q_TMPL "SELECT id FROM files WHERE path = '%q';"

1728

#define Q_TMPL "SELECT f.id FROM files f WHERE f.path = '%q';"

1685

1729

char *query;

1686

1730

int ret;

1687

1731

1705

1749

int

1706

1750

db_file_id_byfilebase(char *filename, char *base)

1707

1751

{

1708

#define Q_TMPL "SELECT id FROM files WHERE path LIKE '%q/%%/%q';"

1752

#define Q_TMPL "SELECT f.id FROM files f WHERE f.path LIKE '%q/%%/%q';"

1709

1753

char *query;

1710

1754

int ret;

1711

1755

1729

1773

int

1730

1774

db_file_id_byfile(char *filename)

1731

1775

{

1732

#define Q_TMPL "SELECT id FROM files WHERE fname = '%q';"

1776

#define Q_TMPL "SELECT f.id FROM files f WHERE f.fname = '%q';"

1733

1777

char *query;

1734

1778

int ret;

1735

1779

1753

1797

int

1754

1798

db_file_id_byurl(char *url)

1755

1799

{

1756

#define Q_TMPL "SELECT id FROM files WHERE url = '%q';"

1800

#define Q_TMPL "SELECT f.id FROM files f WHERE f.url = '%q';"

1757

1801

char *query;

1758

1802

int ret;

1759

1803

1774

1818

#undef Q_TMPL

1775

1819

}

1776

1820

1777

time_t

1778

db_file_stamp_bypath(char *path)

1821

void

1822

db_file_stamp_bypath(char *path, time_t *stamp, int *id)

1779

1823

{

1780

#define Q_TMPL "SELECT db_timestamp FROM files WHERE path = '%q';"

1824

#define Q_TMPL "SELECT f.id, f.db_timestamp FROM files f WHERE f.path = '%q';"

1781

1825

char *query;

1782

1826

sqlite3_stmt *stmt;

1783

time_t stamp;

1784

1827

int ret;

1785

1828

1829

*stamp = 0;

1830

1786

1831

query = sqlite3_mprintf(Q_TMPL, path);

1787

1832

if (!query)

1788

1833

{

1789

1834

DPRINTF(E_LOG, L_DB, "Out of memory for query string\n");

1790

1835

1791

return 0;

1836

return;

1792

1837

}

1793

1838

1794

1839

DPRINTF(E_DBG, L_DB, "Running query '%s'\n", query);

1799

1844

DPRINTF(E_LOG, L_DB, "Could not prepare statement: %s\n", sqlite3_errmsg(pool_hdl->hdl));

1800

1845

1801

1846

sqlite3_free(query);

1802

return 0;

1847

return;

1803

1848

}

1804

1849

1805

1850

ret = db_blocking_step(stmt);

1812

1857

1813

1858

sqlite3_finalize(stmt);

1814

1859

sqlite3_free(query);

1815

return 0;

1860

return;

1816

1861

}

1817

1862

1818

stamp = (time_t)sqlite3_column_int64(stmt, 0);

1863

*id = sqlite3_column_int(stmt, 0);

1864

*stamp = (time_t)sqlite3_column_int64(stmt, 1);

1865

1866

#ifdef DB_PROFILE

1867

while (db_blocking_step(stmt) == SQLITE_ROW)

1868

; /* EMPTY */

1869

#endif

1819

1870

1820

1871

sqlite3_finalize(stmt);

1821

1872

sqlite3_free(query);

1822

1873

1823

return stamp;

1824

1825

1874

#undef Q_TMPL

1826

1875

}

1827

1876

1931

1980

}

1932

1981

}

1933

1982

1983

#ifdef DB_PROFILE

1984

while (db_blocking_step(stmt) == SQLITE_ROW)

1985

; /* EMPTY */

1986

#endif

1987

1934

1988

sqlite3_finalize(stmt);

1935

1989

1936

1990

return mfi;

1939

1993

struct media_file_info *

1940

1994

db_file_fetch_byid(int id)

1941

1995

{

1942

#define Q_TMPL "SELECT * FROM files WHERE id = %d;"

1996

#define Q_TMPL "SELECT f.* FROM files f WHERE f.id = %d;"

1943

1997

struct media_file_info *mfi;

1944

1998

char *query;

1945

1999

2245

2299

int

2246

2300

db_pl_get_count(void)

2247

2301

{

2248

return db_get_count("SELECT COUNT(*) FROM playlists WHERE disabled = 0;");

2302

return db_get_count("SELECT COUNT(*) FROM playlists p WHERE p.disabled = 0;");

2249

2303

}

2250

2304

2251

2305

static int

2252

2306

db_pl_count_items(int id)

2253

2307

{

2254

#define Q_TMPL "SELECT COUNT(*) FROM playlistitems JOIN files" \

2255

" ON playlistitems.filepath = files.path WHERE files.disabled = 0 AND playlistitems.playlistid = %d;"

2308

#define Q_TMPL "SELECT COUNT(*) FROM playlistitems pi JOIN files f" \

2309

" ON pi.filepath = f.path WHERE f.disabled = 0 AND pi.playlistid = %d;"

2256

2310

char *query;

2257

2311

int ret;

2258

2312

2276

2330

static int

2277

2331

db_smartpl_count_items(const char *smartpl_query)

2278

2332

{

2279

#define Q_TMPL "SELECT COUNT(*) FROM files WHERE disabled = 0 AND %s;"

2333

#define Q_TMPL "SELECT COUNT(*) FROM files f WHERE f.disabled = 0 AND %s;"

2280

2334

char *query;

2281

2335

int ret;

2282

2336

2328

2382

static int

2329

2383

db_pl_id_bypath(char *path, int *id)

2330

2384

{

2331

#define Q_TMPL "SELECT id FROM playlists WHERE path = '%q';"

2385

#define Q_TMPL "SELECT p.id FROM playlists p WHERE p.path = '%q';"

2332

2386

char *query;

2333

2387

sqlite3_stmt *stmt;

2334

2388

int ret;

2367

2421

2368

2422

*id = sqlite3_column_int(stmt, 0);

2369

2423

2424

#ifdef DB_PROFILE

2425

while (db_blocking_step(stmt) == SQLITE_ROW)

2426

; /* EMPTY */

2427

#endif

2428

2370

2429

sqlite3_finalize(stmt);

2371

2430

sqlite3_free(query);

2372

2431

2501

2560

struct playlist_info *

2502

2561

db_pl_fetch_bypath(char *path)

2503

2562

{

2504

#define Q_TMPL "SELECT * FROM playlists WHERE path = '%q';"

2563

#define Q_TMPL "SELECT p.* FROM playlists p WHERE p.path = '%q';"

2505

2564

struct playlist_info *pli;

2506

2565

char *query;

2507

2566

2525

2584

struct playlist_info *

2526

2585

db_pl_fetch_byid(int id)

2527

2586

{

2528

#define Q_TMPL "SELECT * FROM playlists WHERE id = %d;"

2587

#define Q_TMPL "SELECT p.* FROM playlists p WHERE p.id = %d;"

2529

2588

struct playlist_info *pli;

2530

2589

char *query;

2531

2590

2549

2608

struct playlist_info *

2550

2609

db_pl_fetch_bytitlepath(char *title, char *path)

2551

2610

{

2552

#define Q_TMPL "SELECT * FROM playlists WHERE title = '%q' AND path = '%q';"

2611

#define Q_TMPL "SELECT p.* FROM playlists p WHERE p.title = '%q' AND p.path = '%q';"

2553

2612

struct playlist_info *pli;

2554

2613

char *query;

2555

2614

2573

2632

int

2574

2633

db_pl_add(char *title, char *path, int *id)

2575

2634

{

2576

#define QDUP_TMPL "SELECT COUNT(*) FROM playlists WHERE title = '%q' AND path = '%q';"

2635

#define QDUP_TMPL "SELECT COUNT(*) FROM playlists p WHERE p.title = '%q' AND p.path = '%q';"

2577

2636

#define QADD_TMPL "INSERT INTO playlists (title, type, query, db_timestamp, disabled, path, idx, special_id)" \

2578

2637

" VALUES ('%q', 0, NULL, %" PRIi64 ", 0, '%q', 0, 0);"

2579

2638

char *query;

2672

2731

int

2673

2732

db_pl_add_item_byid(int plid, int fileid)

2674

2733

{

2675

#define Q_TMPL "INSERT INTO playlistitems (playlistid, filepath) VALUES (%d, (SELECT path FROM files WHERE id = %d));"

2734

#define Q_TMPL "INSERT INTO playlistitems (playlistid, filepath) VALUES (%d, (SELECT f.path FROM files f WHERE f.id = %d));"

2676

2735

char *query;

2677

2736

char *errmsg;

2678

2737

int ret;

2905

2964

enum group_type

2906

2965

db_group_type_byid(int id)

2907

2966

{

2908

#define Q_TMPL "SELECT type FROM groups WHERE id = '%d';"

2967

#define Q_TMPL "SELECT g.type FROM groups g WHERE g.id = '%d';"

2909

2968

char *query;

2910

2969

sqlite3_stmt *stmt;

2911

2970

int ret;

2944

3003

2945

3004

ret = sqlite3_column_int(stmt, 0);

2946

3005

3006

#ifdef DB_PROFILE

3007

while (db_blocking_step(stmt) == SQLITE_ROW)

3008

; /* EMPTY */

3009

#endif

3010

2947

3011

sqlite3_finalize(stmt);

2948

3012

sqlite3_free(query);

2949

3013

3030

3094

int

3031

3095

db_pairing_fetch_byguid(struct pairing_info *pi)

3032

3096

{

3033

#define Q_TMPL "SELECT * FROM pairings WHERE guid = '%q';"

3097

#define Q_TMPL "SELECT p.* FROM pairings p WHERE p.guid = '%q';"

3034

3098

char *query;

3035

3099

sqlite3_stmt *stmt;

3036

3100

int ret;

3067

3131

pi->remote_id = strdup((char *)sqlite3_column_text(stmt, 0));

3068

3132

pi->name = strdup((char *)sqlite3_column_text(stmt, 1));

3069

3133

3134

#ifdef DB_PROFILE

3135

while (db_blocking_step(stmt) == SQLITE_ROW)

3136

; /* EMPTY */

3137

#endif

3138

3070

3139

sqlite3_finalize(stmt);

3071

3140

sqlite3_free(query);

3072

3141

3116

3185

int

3117

3186

db_speaker_get(uint64_t id, int *selected, int *volume)

3118

3187

{

3119

#define Q_TMPL "SELECT selected, volume FROM speakers WHERE id = %" PRIi64 ";"

3188

#define Q_TMPL "SELECT s.selected, s.volume FROM speakers s WHERE s.id = %" PRIi64 ";"

3120

3189

sqlite3_stmt *stmt;

3121

3190

char *query;

3122

3191

int ret;

3155

3224

*selected = sqlite3_column_int(stmt, 0);

3156

3225

*volume = sqlite3_column_int(stmt, 1);

3157

3226

3227

#ifdef DB_PROFILE

3228

while (db_blocking_step(stmt) == SQLITE_ROW)

3229

; /* EMPTY */

3230

#endif

3231

3158

3232

sqlite3_finalize(stmt);

3159

3233

3160

3234

ret = 0;

3445

3519

}

3446

3520

}

3447

3521

3522

#ifdef DB_PROFILE

3523

while (db_blocking_step(stmt) == SQLITE_ROW)

3524

; /* EMPTY */

3525

#endif

3526

3448

3527

sqlite3_finalize(stmt);

3449

3528

sqlite3_free(query);

3450

3529

3669

3748

static void

3670

3749

db_xprofile(void *notused, const char *pquery, sqlite3_uint64 ptime)

3671

3750

{

3672

DPRINTF(E_DBG, L_DB, "SQL PROFILE query: %s\n", pquery);

3673

DPRINTF(E_DBG, L_DB, "SQL PROFILE time: %" PRIu64 "\n", (uint64_t)ptime);

3751

sqlite3_stmt *stmt;

3752

char *query;

3753

int ret;

3754

3755

DPRINTF(E_DBG, L_DBPERF, "SQL PROFILE query: %s\n", pquery);

3756

DPRINTF(E_DBG, L_DBPERF, "SQL PROFILE time: %" PRIu64 " ms\n", ((uint64_t)ptime / 1000000));

3757

3758

if ((strncmp(pquery, "SELECT", 6) != 0)

3759

&& (strncmp(pquery, "UPDATE", 6) != 0)

3760

&& (strncmp(pquery, "DELETE", 6) != 0))

3761

return;

3762

3763

/* Disable profiling callback */

3764

sqlite3_profile(pool_hdl->hdl, NULL, NULL);

3765

3766

query = sqlite3_mprintf("EXPLAIN QUERY PLAN %s", pquery);

3767

if (!query)

3768

{

3769

DPRINTF(E_DBG, L_DBPERF, "Query plan: Out of memory\n");

3770

3771

goto out;

3772

}

3773

3774

ret = db_blocking_prepare_v2(query, -1, &stmt, NULL);

3775

sqlite3_free(query);

3776

if (ret != SQLITE_OK)

3777

{

3778

DPRINTF(E_DBG, L_DBPERF, "Query plan: Could not prepare statement: %s\n", sqlite3_errmsg(pool_hdl->hdl));

3779

3780

goto out;

3781

}

3782

3783

DPRINTF(E_DBG, L_DBPERF, "Query plan:\n");

3784

3785

while ((ret = db_blocking_step(stmt)) == SQLITE_ROW)

3786

{

3787

DPRINTF(E_DBG, L_DBPERF, "(%d,%d,%d) %s\n",

3788

sqlite3_column_int(stmt, 0), sqlite3_column_int(stmt, 1), sqlite3_column_int(stmt, 2),

3789

sqlite3_column_text(stmt, 3));

3790

}

3791

3792

if (ret != SQLITE_DONE)

3793

DPRINTF(E_DBG, L_DBPERF, "Query plan: Could not step: %s\n", sqlite3_errmsg(pool_hdl->hdl));

3794

3795

DPRINTF(E_DBG, L_DBPERF, "---\n");

3796

3797

sqlite3_finalize(stmt);

3798

3799

out:

3800

/* Reenable profiling callback */

3801

sqlite3_profile(pool_hdl->hdl, db_xprofile, NULL);

3674

3802

}

3675

3803

#endif

3676

3804

4161

4289

" path VARCHAR(4096) NOT NULL" \

4162

4290

");"

4163

4291

4164

#define I_PATH \

4165

"CREATE INDEX IF NOT EXISTS idx_path ON files(path, idx);"

4292

#define I_RESCAN \

4293

"CREATE INDEX IF NOT EXISTS idx_rescan ON files(path, db_timestamp);"

4294

4295

#define I_SONGALBUMID \

4296

"CREATE INDEX IF NOT EXISTS idx_sai ON files(songalbumid);"

4297

4298

#define I_STATEMKINDSAI \

4299

"CREATE INDEX IF NOT EXISTS idx_state_mkind_sai ON files(disabled, media_kind, songalbumid);"

4300

4301

#define I_ARTIST \

4302

"CREATE INDEX IF NOT EXISTS idx_artist ON files(artist, artist_sort);"

4303

4304

#define I_ALBUMARTIST \

4305

"CREATE INDEX IF NOT EXISTS idx_albumartist ON files(album_artist, album_artist_sort);"

4306

4307

#define I_COMPOSER \

4308

"CREATE INDEX IF NOT EXISTS idx_composer ON files(composer, composer_sort);"

4309

4310

#define I_TITLE \

4311

"CREATE INDEX IF NOT EXISTS idx_title ON files(title, title_sort);"

4312

4313

#define I_ALBUM \

4314

"CREATE INDEX IF NOT EXISTS idx_album ON files(album, album_sort);"

4315

4316

#define I_PL_PATH \

4317

"CREATE INDEX IF NOT EXISTS idx_pl_path ON playlists(path);"

4318

4319

#define I_PL_DISABLED \

4320

"CREATE INDEX IF NOT EXISTS idx_pl_disabled ON playlists(disabled);"

4166

4321

4167

4322

#define I_FILEPATH \

4168

4323

"CREATE INDEX IF NOT EXISTS idx_filepath ON playlistitems(filepath ASC);"

4170

4325

#define I_PLITEMID \

4171

4326

"CREATE INDEX IF NOT EXISTS idx_playlistid ON playlistitems(playlistid, filepath);"

4172

4327

4328

#define I_GRP_TYPE_PERSIST \

4329

"CREATE INDEX IF NOT EXISTS idx_grp_type_persist ON groups(type, persistentid);"

4330

4173

4331

#define I_PAIRING \

4174

4332

"CREATE INDEX IF NOT EXISTS idx_pairingguid ON pairings(guid);"

4175

4333

4176

#define I_TITLESORT \

4177

"CREATE INDEX IF NOT EXISTS idx_titlesort ON files(title_sort);"

4178

4179

#define I_ARTISTSORT \

4180

"CREATE INDEX IF NOT EXISTS idx_artistsort ON files(artist_sort);"

4181

4182

#define I_ALBUMSORT \

4183

"CREATE INDEX IF NOT EXISTS idx_albumsort ON files(album_sort);"

4184

4334

4185

4335

#define TRG_GROUPS_INSERT_FILES \

4186

4336

"CREATE TRIGGER update_groups_new_file AFTER INSERT ON files FOR EACH ROW" \

4200

4350

4201

4351

#define Q_PL2 \

4202

4352

"INSERT INTO playlists (id, title, type, query, db_timestamp, path, idx, special_id)" \

4203

" VALUES(2, 'Music', 1, 'media_kind = 1', 0, '', 0, 6);"

4353

" VALUES(2, 'Music', 1, 'f.media_kind = 1', 0, '', 0, 6);"

4204

4354

4205

4355

#define Q_PL3 \

4206

4356

"INSERT INTO playlists (id, title, type, query, db_timestamp, path, idx, special_id)" \

4207

" VALUES(3, 'Movies', 1, 'media_kind = 2', 0, '', 0, 4);"

4357

" VALUES(3, 'Movies', 1, 'f.media_kind = 2', 0, '', 0, 4);"

4208

4358

4209

4359

#define Q_PL4 \

4210

4360

"INSERT INTO playlists (id, title, type, query, db_timestamp, path, idx, special_id)" \

4211

" VALUES(4, 'TV Shows', 1, 'media_kind = 64', 0, '', 0, 5);"

4361

" VALUES(4, 'TV Shows', 1, 'f.media_kind = 64', 0, '', 0, 5);"

4212

4362

4213

4363

/* These are the remaining automatically-created iTunes playlists, but

4214

4364

* their query is unknown

4218

4368

" VALUES(8, 'Purchased', 0, 'media_kind = 1024', 0, '', 0, 8);"

4219

4369

*/

4220

4370

4221

#define SCHEMA_VERSION 12

4371

#define SCHEMA_VERSION 13

4222

4372

#define Q_SCVER \

4223

"INSERT INTO admin (key, value) VALUES ('schema_version', '12');"

4373

"INSERT INTO admin (key, value) VALUES ('schema_version', '13');"

4224

4374

4225

4375

struct db_init_query {

4226

4376

char *query;

4238

4388

{ T_SPEAKERS, "create table speakers" },

4239

4389

{ T_INOTIFY, "create table inotify" },

4240

4390

4241

{ I_PATH, "create file path index" },

4391

{ I_RESCAN, "create rescan index" },

4392

{ I_SONGALBUMID, "create songalbumid index" },

4393

{ I_STATEMKINDSAI, "create state/mkind/sai index" },

4394

4395

{ I_ARTIST, "create artist index" },

4396

{ I_ALBUMARTIST, "create album_artist index" },

4397

{ I_COMPOSER, "create composer index" },

4398

{ I_TITLE, "create title index" },

4399

{ I_ALBUM, "create album index" },

4400

4401

{ I_PL_PATH, "create playlist path index" },

4402

{ I_PL_DISABLED, "create playlist state index" },

4403

4242

4404

{ I_FILEPATH, "create file path index" },

4243

4405

{ I_PLITEMID, "create playlist id index" },

4406

4407

{ I_GRP_TYPE_PERSIST, "create groups type/persistentid index" },

4408

4244

4409

{ I_PAIRING, "create pairing guid index" },

4245

{ I_TITLESORT, "create file titlesort index" },

4246

{ I_ARTISTSORT,"create file artistsort index" },

4247

{ I_ALBUMSORT, "create file albumsort index" },

4248

4410

4249

4411

{ TRG_GROUPS_INSERT_FILES, "create trigger update_groups_new_file" },

4250

4412

{ TRG_GROUPS_UPDATE_FILES, "create trigger update_groups_update_file" },

4784

4946

#undef Q_DUMP

4785

4947

}

4786

4948

4949

/* Upgrade from schema v12 to v13 */

4950

4951

#define U_V13_DROP_IDX_PATH \

4952

"DROP INDEX idx_path;"

4953

4954

#define U_V13_DROP_IDX_TS \

4955

"DROP INDEX idx_titlesort;"

4956

4957

#define U_V13_DROP_IDX_AS \

4958

"DROP INDEX idx_artistsort;"

4959

4960

#define U_V13_DROP_IDX_BS \

4961

"DROP INDEX idx_albumsort;"

4962

4963

#define U_V13_IDX_RESCAN \

4964

"CREATE INDEX IF NOT EXISTS idx_rescan ON files(path, db_timestamp);"

4965

4966

#define U_V13_IDX_SONGALBUMID \

4967

"CREATE INDEX IF NOT EXISTS idx_sai ON files(songalbumid);"

4968

4969

#define U_V13_IDX_STATEMKINDSAI \

4970

"CREATE INDEX IF NOT EXISTS idx_state_mkind_sai ON files(disabled, media_kind, songalbumid);"

4971

4972

#define U_V13_IDX_ARTIST \

4973

"CREATE INDEX IF NOT EXISTS idx_artist ON files(artist, artist_sort);"

4974

4975

#define U_V13_IDX_ALBUMARTIST \

4976

"CREATE INDEX IF NOT EXISTS idx_albumartist ON files(album_artist, album_artist_sort);"

4977

4978

#define U_V13_IDX_COMPOSER \

4979

"CREATE INDEX IF NOT EXISTS idx_composer ON files(composer, composer_sort);"

4980

4981

#define U_V13_IDX_TITLE \

4982

"CREATE INDEX IF NOT EXISTS idx_title ON files(title, title_sort);"

4983

4984

#define U_V13_IDX_ALBUM \

4985

"CREATE INDEX IF NOT EXISTS idx_album ON files(album, album_sort);"

4986

4987

#define U_V13_IDX_GRP_TYPE_PERSIST \

4988

"CREATE INDEX IF NOT EXISTS idx_grp_type_persist ON groups(type, persistentid);"

4989

4990

#define U_V13_IDX_PL_PATH \

4991

"CREATE INDEX IF NOT EXISTS idx_pl_path ON playlists(path);"

4992

4993

#define U_V13_IDX_PL_DISABLED \

4994

"CREATE INDEX IF NOT EXISTS idx_pl_disabled ON playlists(disabled);"

4995

4996

#define U_V13_PL2 \

4997

"UPDATE playlists SET query = 'f.media_kind = 1' where id = 2;"

4998

4999

#define U_V13_PL3 \

5000

"UPDATE playlists SET query = 'f.media_kind = 2' where id = 3;"

5001

5002

#define U_V13_PL4 \

5003

"UPDATE playlists SET query = 'f.media_kind = 64' where id = 4;"

5004

5005

#define U_V13_SCVER \

5006

"UPDATE admin SET value = '13' WHERE key = 'schema_version';"

5007

5008

static const struct db_init_query db_upgrade_v13_queries[] =

5009

{

5010

{ U_V13_DROP_IDX_PATH, "drop index path table files" },

5011

{ U_V13_DROP_IDX_TS, "drop index titlesort table files" },

5012

{ U_V13_DROP_IDX_AS, "drop index artistsort table files" },

5013

{ U_V13_DROP_IDX_BS, "drop index albumsort table files" },

5014

5015

{ U_V13_IDX_RESCAN, "create rescan index" },

5016

{ U_V13_IDX_SONGALBUMID, "create songalbumid index" },

5017

{ U_V13_IDX_STATEMKINDSAI, "create state/mkind/sai index" },

5018

{ U_V13_IDX_ARTIST, "create artist index" },

5019

{ U_V13_IDX_ALBUMARTIST, "create album_artist index" },

5020

{ U_V13_IDX_COMPOSER, "create composer index" },

5021

{ U_V13_IDX_TITLE, "create title index" },

5022

{ U_V13_IDX_ALBUM, "create album index" },

5023

5024

{ U_V13_IDX_GRP_TYPE_PERSIST, "create groups type/persistentid index" },

5025

5026

{ U_V13_IDX_PL_PATH, "create playlist path index" },

5027

{ U_V13_IDX_PL_DISABLED, "create playlist state index" },

5028

5029

{ U_V13_PL2, "update default smart playlist 'Music'" },

5030

{ U_V13_PL3, "update default smart playlist 'Movies'" },

5031

{ U_V13_PL4, "update default smart playlist 'TV Shows'" },

5032

5033

{ U_V13_SCVER, "set schema_version to 13" },

5034

};

4787

5035

4788

5036

static int

4789

5037

db_check_version(void)

4849

5097

if (ret < 0)

4850

5098

return -1;

4851

5099

5100

/* FALLTHROUGH */

5101

5102

case 12:

5103

ret = db_generic_upgrade(db_upgrade_v13_queries, sizeof(db_upgrade_v13_queries) / sizeof(db_upgrade_v13_queries[0]));

5104

if (ret < 0)

5105

return -1;

5106

4852

5107

break;

4853

5108

4854

5109

default:

4937

5192

}

4938

5193

}

4939

5194

5195

db_analyze();

5196

4940

5197

files = db_files_get_count();

4941

5198

pls = db_pl_get_count();

4942

5199