public ArrayList<File> getFiles(String sql) {
ArrayList<File> list = new ArrayList<>();
Connection conn = null;
ResultSet rs = null;
PreparedStatement ps = null;
try {
conn = getConnection();
ps =
conn.prepareStatement(
sql.toLowerCase().startsWith("select")
? sql
: ("SELECT FILENAME, MODIFIED FROM FILES WHERE " + sql));
rs = ps.executeQuery();
while (rs.next()) {
String filename = rs.getString("FILENAME");
long modified = rs.getTimestamp("MODIFIED").getTime();
File file = new File(filename);
if (file.exists() && file.lastModified() == modified) {
list.add(file);
}
}
} catch (SQLException se) {
LOGGER.error(null, se);
return null;
} finally {
close(rs);
close(ps);
close(conn);
}
return list;
}
public ArrayList<String> getStrings(String sql) {
ArrayList<String> list = new ArrayList<>();
Connection conn = null;
ResultSet rs = null;
PreparedStatement ps = null;
try {
conn = getConnection();
ps = conn.prepareStatement(sql);
rs = ps.executeQuery();
while (rs.next()) {
String str = rs.getString(1);
if (isBlank(str)) {
if (!list.contains(NONAME)) {
list.add(NONAME);
}
} else if (!list.contains(str)) {
list.add(str);
}
}
} catch (SQLException se) {
LOGGER.error(null, se);
return null;
} finally {
close(rs);
close(ps);
close(conn);
}
return list;
}
public void cleanup() {
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
conn = getConnection();
ps = conn.prepareStatement("SELECT COUNT(*) FROM FILES");
rs = ps.executeQuery();
dbCount = 0;
if (rs.next()) {
dbCount = rs.getInt(1);
}
rs.close();
ps.close();
PMS.get().getFrame().setStatusLine(Messages.getString("DLNAMediaDatabase.2") + " 0%");
int i = 0;
int oldpercent = 0;
if (dbCount > 0) {
ps =
conn.prepareStatement(
"SELECT FILENAME, MODIFIED, ID FROM FILES",
ResultSet.TYPE_FORWARD_ONLY,
ResultSet.CONCUR_UPDATABLE);
rs = ps.executeQuery();
while (rs.next()) {
String filename = rs.getString("FILENAME");
long modified = rs.getTimestamp("MODIFIED").getTime();
File file = new File(filename);
if (!file.exists() || file.lastModified() != modified) {
rs.deleteRow();
}
i++;
int newpercent = i * 100 / dbCount;
if (newpercent > oldpercent) {
PMS.get()
.getFrame()
.setStatusLine(Messages.getString("DLNAMediaDatabase.2") + newpercent + "%");
oldpercent = newpercent;
}
}
}
} catch (SQLException se) {
LOGGER.error(null, se);
} finally {
close(rs);
close(ps);
close(conn);
}
}
public ArrayList<DLNAMediaInfo> getData(String name, long modified) {
ArrayList<DLNAMediaInfo> list = new ArrayList<>();
Connection conn = null;
ResultSet rs = null;
PreparedStatement stmt = null;
try {
conn = getConnection();
stmt = conn.prepareStatement("SELECT * FROM FILES WHERE FILENAME = ? AND MODIFIED = ?");
stmt.setString(1, name);
stmt.setTimestamp(2, new Timestamp(modified));
rs = stmt.executeQuery();
while (rs.next()) {
DLNAMediaInfo media = new DLNAMediaInfo();
int id = rs.getInt("ID");
media.setDuration(toDouble(rs, "DURATION"));
media.setBitrate(rs.getInt("BITRATE"));
media.setWidth(rs.getInt("WIDTH"));
media.setHeight(rs.getInt("HEIGHT"));
media.setSize(rs.getLong("SIZE"));
media.setCodecV(rs.getString("CODECV"));
media.setFrameRate(rs.getString("FRAMERATE"));
media.setAspectRatioDvdIso(rs.getString("ASPECT"));
media.setAspectRatioContainer(rs.getString("ASPECTRATIOCONTAINER"));
media.setAspectRatioVideoTrack(rs.getString("ASPECTRATIOVIDEOTRACK"));
media.setReferenceFrameCount(rs.getByte("REFRAMES"));
media.setAvcLevel(rs.getString("AVCLEVEL"));
media.setBitsPerPixel(rs.getInt("BITSPERPIXEL"));
media.setThumb(rs.getBytes("THUMB"));
media.setContainer(rs.getString("CONTAINER"));
media.setModel(rs.getString("MODEL"));
if (media.getModel() != null && !FormatConfiguration.JPG.equals(media.getContainer())) {
media.setExtrasAsString(media.getModel());
}
media.setExposure(rs.getInt("EXPOSURE"));
media.setOrientation(rs.getInt("ORIENTATION"));
media.setIso(rs.getInt("ISO"));
media.setMuxingMode(rs.getString("MUXINGMODE"));
media.setFrameRateMode(rs.getString("FRAMERATEMODE"));
media.setStereoscopy(rs.getString("STEREOSCOPY"));
media.setMatrixCoefficients(rs.getString("MATRIXCOEFFICIENTS"));
media.setFileTitleFromMetadata(rs.getString("TITLECONTAINER"));
media.setVideoTrackTitleFromMetadata(rs.getString("TITLEVIDEOTRACK"));
media.setVideoTrackCount(rs.getInt("VIDEOTRACKCOUNT"));
media.setImageCount(rs.getInt("IMAGECOUNT"));
media.setVideoBitDepth(rs.getInt("BITDEPTH"));
media.setMediaparsed(true);
ResultSet subrs;
try (PreparedStatement audios =
conn.prepareStatement("SELECT * FROM AUDIOTRACKS WHERE FILEID = ?")) {
audios.setInt(1, id);
subrs = audios.executeQuery();
while (subrs.next()) {
DLNAMediaAudio audio = new DLNAMediaAudio();
audio.setId(subrs.getInt("ID"));
audio.setLang(subrs.getString("LANG"));
audio.setAudioTrackTitleFromMetadata(subrs.getString("TITLE"));
audio.getAudioProperties().setNumberOfChannels(subrs.getInt("NRAUDIOCHANNELS"));
audio.setSampleFrequency(subrs.getString("SAMPLEFREQ"));
audio.setCodecA(subrs.getString("CODECA"));
audio.setBitsperSample(subrs.getInt("BITSPERSAMPLE"));
audio.setAlbum(subrs.getString("ALBUM"));
audio.setArtist(subrs.getString("ARTIST"));
audio.setSongname(subrs.getString("SONGNAME"));
audio.setGenre(subrs.getString("GENRE"));
audio.setYear(subrs.getInt("YEAR"));
audio.setTrack(subrs.getInt("TRACK"));
audio.getAudioProperties().setAudioDelay(subrs.getInt("DELAY"));
audio.setMuxingModeAudio(subrs.getString("MUXINGMODE"));
audio.setBitRate(subrs.getInt("BITRATE"));
media.getAudioTracksList().add(audio);
}
subrs.close();
}
try (PreparedStatement subs =
conn.prepareStatement("SELECT * FROM SUBTRACKS WHERE FILEID = ?")) {
subs.setInt(1, id);
subrs = subs.executeQuery();
while (subrs.next()) {
DLNAMediaSubtitle sub = new DLNAMediaSubtitle();
sub.setId(subrs.getInt("ID"));
sub.setLang(subrs.getString("LANG"));
sub.setSubtitlesTrackTitleFromMetadata(subrs.getString("TITLE"));
sub.setType(SubtitleType.valueOfStableIndex(subrs.getInt("TYPE")));
media.getSubtitleTracksList().add(sub);
}
subrs.close();
}
list.add(media);
}
} catch (SQLException se) {
LOGGER.error(null, se);
return null;
} finally {
close(rs);
close(stmt);
close(conn);
}
return list;
}
public void init(boolean force) {
dbCount = -1;
String version = null;
Connection conn = null;
ResultSet rs = null;
Statement stmt = null;
try {
conn = getConnection();
} catch (SQLException se) {
final File dbFile = new File(dbDir + File.separator + dbName + ".data.db");
final File dbDirectory = new File(dbDir);
if (dbFile.exists()
|| (se.getErrorCode() == 90048)) { // Cache is corrupt or a wrong version, so delete it
FileUtils.deleteQuietly(dbDirectory);
if (!dbDirectory.exists()) {
LOGGER.info(
"The database has been deleted because it was corrupt or had the wrong version");
} else {
if (!net.pms.PMS.isHeadless()) {
JOptionPane.showMessageDialog(
SwingUtilities.getWindowAncestor((Component) PMS.get().getFrame()),
String.format(Messages.getString("DLNAMediaDatabase.5"), dbDir),
Messages.getString("Dialog.Error"),
JOptionPane.ERROR_MESSAGE);
}
LOGGER.error(
"Damaged cache can't be deleted. Stop the program and delete the folder \""
+ dbDir
+ "\" manually");
PMS.get().getRootFolder(null).stopScan();
configuration.setUseCache(false);
return;
}
} else {
LOGGER.error("Database connection error: " + se.getMessage());
LOGGER.trace("", se);
RootFolder rootFolder = PMS.get().getRootFolder(null);
if (rootFolder != null) {
rootFolder.stopScan();
}
configuration.setUseCache(false);
return;
}
} finally {
close(conn);
}
try {
conn = getConnection();
stmt = conn.createStatement();
rs = stmt.executeQuery("SELECT count(*) FROM FILES");
if (rs.next()) {
dbCount = rs.getInt(1);
}
rs.close();
stmt.close();
stmt = conn.createStatement();
rs = stmt.executeQuery("SELECT VALUE FROM METADATA WHERE KEY = 'VERSION'");
if (rs.next()) {
version = rs.getString(1);
}
} catch (SQLException se) {
if (se.getErrorCode()
!= 42102) { // Don't log exception "Table "FILES" not found" which will be corrected in
// following step
LOGGER.error(null, se);
}
} finally {
close(rs);
close(stmt);
close(conn);
}
// Recreate database if it is not the latest version.
boolean force_reinit = !latestVersion.equals(version);
if (force || dbCount == -1 || force_reinit) {
LOGGER.debug("Database will be (re)initialized");
try {
conn = getConnection();
executeUpdate(conn, "DROP TABLE FILES");
executeUpdate(conn, "DROP TABLE METADATA");
executeUpdate(conn, "DROP TABLE REGEXP_RULES");
executeUpdate(conn, "DROP TABLE AUDIOTRACKS");
executeUpdate(conn, "DROP TABLE SUBTRACKS");
} catch (SQLException se) {
if (se.getErrorCode()
!= 42102) { // Don't log exception "Table "FILES" not found" which will be corrected in
// following step
LOGGER.error(null, se);
}
}
try {
StringBuilder sb = new StringBuilder();
sb.append("CREATE TABLE FILES (");
sb.append(" ID INT AUTO_INCREMENT");
sb.append(", FILENAME VARCHAR2(1024) NOT NULL");
sb.append(", MODIFIED TIMESTAMP NOT NULL");
sb.append(", TYPE INT");
sb.append(", DURATION DOUBLE");
sb.append(", BITRATE INT");
sb.append(", WIDTH INT");
sb.append(", HEIGHT INT");
sb.append(", SIZE NUMERIC");
sb.append(", CODECV VARCHAR2(").append(SIZE_CODECV).append(')');
sb.append(", FRAMERATE VARCHAR2(").append(SIZE_FRAMERATE).append(')');
sb.append(", ASPECT VARCHAR2(")
.append(SIZE_ASPECTRATIO_DVDISO)
.append(')');
sb.append(", ASPECTRATIOCONTAINER VARCHAR2(")
.append(SIZE_ASPECTRATIO_CONTAINER)
.append(')');
sb.append(", ASPECTRATIOVIDEOTRACK VARCHAR2(")
.append(SIZE_ASPECTRATIO_VIDEOTRACK)
.append(')');
sb.append(", REFRAMES TINYINT");
sb.append(", AVCLEVEL VARCHAR2(").append(SIZE_AVC_LEVEL).append(')');
sb.append(", BITSPERPIXEL INT");
sb.append(", THUMB BINARY");
sb.append(", CONTAINER VARCHAR2(").append(SIZE_CONTAINER).append(')');
sb.append(", MODEL VARCHAR2(").append(SIZE_MODEL).append(')');
sb.append(", EXPOSURE INT");
sb.append(", ORIENTATION INT");
sb.append(", ISO INT");
sb.append(", MUXINGMODE VARCHAR2(").append(SIZE_MUXINGMODE).append(')');
sb.append(", FRAMERATEMODE VARCHAR2(").append(SIZE_FRAMERATE_MODE).append(')');
sb.append(", STEREOSCOPY VARCHAR2(").append(SIZE_STEREOSCOPY).append(')');
sb.append(", MATRIXCOEFFICIENTS VARCHAR2(")
.append(SIZE_MATRIX_COEFFICIENTS)
.append(')');
sb.append(", TITLECONTAINER VARCHAR2(").append(SIZE_TITLE).append(')');
sb.append(", TITLEVIDEOTRACK VARCHAR2(").append(SIZE_TITLE).append(')');
sb.append(", VIDEOTRACKCOUNT INT");
sb.append(", IMAGECOUNT INT");
sb.append(", BITDEPTH INT");
sb.append(", constraint PK1 primary key (FILENAME, MODIFIED, ID))");
executeUpdate(conn, sb.toString());
sb = new StringBuilder();
sb.append("CREATE TABLE AUDIOTRACKS (");
sb.append(" FILEID INT NOT NULL");
sb.append(", ID INT NOT NULL");
sb.append(", LANG VARCHAR2(").append(SIZE_LANG).append(')');
sb.append(", TITLE VARCHAR2(").append(SIZE_TITLE).append(')');
sb.append(", NRAUDIOCHANNELS NUMERIC");
sb.append(", SAMPLEFREQ VARCHAR2(").append(SIZE_SAMPLEFREQ).append(')');
sb.append(", CODECA VARCHAR2(").append(SIZE_CODECA).append(')');
sb.append(", BITSPERSAMPLE INT");
sb.append(", ALBUM VARCHAR2(").append(SIZE_ALBUM).append(')');
sb.append(", ARTIST VARCHAR2(").append(SIZE_ARTIST).append(')');
sb.append(", SONGNAME VARCHAR2(").append(SIZE_SONGNAME).append(')');
sb.append(", GENRE VARCHAR2(").append(SIZE_GENRE).append(')');
sb.append(", YEAR INT");
sb.append(", TRACK INT");
sb.append(", DELAY INT");
sb.append(", MUXINGMODE VARCHAR2(").append(SIZE_MUXINGMODE).append(')');
sb.append(", BITRATE INT");
sb.append(", constraint PKAUDIO primary key (FILEID, ID))");
executeUpdate(conn, sb.toString());
sb = new StringBuilder();
sb.append("CREATE TABLE SUBTRACKS (");
sb.append(" FILEID INT NOT NULL");
sb.append(", ID INT NOT NULL");
sb.append(", LANG VARCHAR2(").append(SIZE_LANG).append(')');
sb.append(", TITLE VARCHAR2(").append(SIZE_TITLE).append(')');
sb.append(", TYPE INT");
sb.append(", constraint PKSUB primary key (FILEID, ID))");
executeUpdate(conn, sb.toString());
executeUpdate(
conn,
"CREATE TABLE METADATA (KEY VARCHAR2(255) NOT NULL, VALUE VARCHAR2(255) NOT NULL)");
executeUpdate(conn, "INSERT INTO METADATA VALUES ('VERSION', '" + latestVersion + "')");
executeUpdate(conn, "CREATE INDEX IDXARTIST on AUDIOTRACKS (ARTIST asc);");
executeUpdate(conn, "CREATE INDEX IDXALBUM on AUDIOTRACKS (ALBUM asc);");
executeUpdate(conn, "CREATE INDEX IDXGENRE on AUDIOTRACKS (GENRE asc);");
executeUpdate(conn, "CREATE INDEX IDXYEAR on AUDIOTRACKS (YEAR asc);");
executeUpdate(
conn,
"CREATE TABLE REGEXP_RULES ( ID VARCHAR2(255) PRIMARY KEY, RULE VARCHAR2(255), ORDR NUMERIC);");
executeUpdate(conn, "INSERT INTO REGEXP_RULES VALUES ( '###', '(?i)^\\W.+', 0 );");
executeUpdate(conn, "INSERT INTO REGEXP_RULES VALUES ( '0-9', '(?i)^\\d.+', 1 );");
// Retrieve the alphabet property value and split it
String[] chars = Messages.getString("DLNAMediaDatabase.1").split(",");
for (int i = 0; i < chars.length; i++) {
// Create regexp rules for characters with a sort order based on the property value
executeUpdate(
conn,
"INSERT INTO REGEXP_RULES VALUES ( '"
+ chars[i]
+ "', '(?i)^"
+ chars[i]
+ ".+', "
+ (i + 2)
+ " );");
}
LOGGER.debug("Database initialized");
} catch (SQLException se) {
LOGGER.info("Error in table creation: " + se.getMessage());
} finally {
close(conn);
}
} else {
LOGGER.debug("Database file count: " + dbCount);
LOGGER.debug("Database version: " + latestVersion);
}
}
