diff options
Diffstat (limited to 'src/main/java/org/openslx/imagemaster/db')
5 files changed, 277 insertions, 15 deletions
diff --git a/src/main/java/org/openslx/imagemaster/db/Paginator.java b/src/main/java/org/openslx/imagemaster/db/Paginator.java index 267a1a3..ad6bb9f 100644 --- a/src/main/java/org/openslx/imagemaster/db/Paginator.java +++ b/src/main/java/org/openslx/imagemaster/db/Paginator.java @@ -1,11 +1,13 @@ package org.openslx.imagemaster.db; -public class Paginator { +public class Paginator +{ - public static final int PER_PAGE = 200; + public static final int PER_PAGE = 400; - public static String limitStatement(int page) { - return " LIMIT " + (page * PER_PAGE) + ", " + PER_PAGE; + public static String limitStatement( int page ) + { + return " LIMIT " + ( page * PER_PAGE ) + ", " + PER_PAGE; } } diff --git a/src/main/java/org/openslx/imagemaster/db/mappers/DbImage.java b/src/main/java/org/openslx/imagemaster/db/mappers/DbImage.java index f4c3ddc..6fddf76 100644 --- a/src/main/java/org/openslx/imagemaster/db/mappers/DbImage.java +++ b/src/main/java/org/openslx/imagemaster/db/mappers/DbImage.java @@ -1,16 +1,27 @@ package org.openslx.imagemaster.db.mappers; +import java.nio.ByteBuffer; import java.sql.ResultSet; import java.sql.SQLException; +import java.util.ArrayList; +import java.util.List; import org.apache.log4j.Logger; +import org.openslx.bwlp.thrift.iface.ImageDetailsRead; +import org.openslx.bwlp.thrift.iface.ImagePermissions; import org.openslx.bwlp.thrift.iface.ImagePublishData; +import org.openslx.bwlp.thrift.iface.ImageSummaryRead; +import org.openslx.bwlp.thrift.iface.ImageVersionDetails; import org.openslx.bwlp.thrift.iface.InvocationError; +import org.openslx.bwlp.thrift.iface.ShareMode; import org.openslx.bwlp.thrift.iface.TInvocationException; +import org.openslx.bwlp.thrift.iface.TNotFoundException; import org.openslx.imagemaster.Globals; import org.openslx.imagemaster.db.Database; import org.openslx.imagemaster.db.MysqlConnection; import org.openslx.imagemaster.db.MysqlStatement; +import org.openslx.imagemaster.db.Paginator; +import org.openslx.thrifthelper.ImagePublishDataEx; import org.openslx.util.Util; /** @@ -22,9 +33,40 @@ public class DbImage private static final Logger LOGGER = Logger.getLogger( DbImage.class ); - public static ImagePublishData getImageVersion( String imageVersionId ) + private static final ImagePermissions emptyPermissions = new ImagePermissions(); + + public static ImagePublishDataEx getImageVersion( String imageVersionId ) throws SQLException { - return null; + try ( MysqlConnection connection = Database.getConnection() ) { + MysqlStatement stmt = connection.prepareStatement( "SELECT v.imageversionid, v.imagebaseid, v.createtime, v.filesize, v.filepath," + + " v.uploaderid, v.isvalid, v.virtualizerconfig, b.displayname, b.description, b.osid, b.virtid, b.ownerid, b.istemplate" + + " FROM imageversion v INNER JOIN imagebase b USING (imagebaseid)" + + " WHERE v.imageversionid = :imageversionid" ); + stmt.setString( "imageversionid", imageVersionId ); + ResultSet rs = stmt.executeQuery(); + if ( !rs.next() ) + return null; + ImagePublishDataEx img = new ImagePublishDataEx(); + img.imageVersionId = rs.getString( "imageversionid" ); + img.imageBaseId = rs.getString( "imagebaseid" ); + img.createTime = rs.getLong( "createtime" ); + img.fileSize = rs.getLong( "filesize" ); + img.uploader = DbUser.getUserInfoOrNull( connection, rs.getString( "uploaderid" ) ); + img.imageName = rs.getString( "displayname" ); + img.description = rs.getString( "description" ); + img.osId = rs.getInt( "osid" ); + img.virtId = rs.getString( "virtid" ); + img.owner = DbUser.getUserInfoOrNull( connection, rs.getString( "ownerid" ) ); + img.isTemplate = rs.getBoolean( "istemplate" ); + img.machineDescription = ByteBuffer.wrap( rs.getBytes( "virtualizerconfig" ) ); + // Ex + img.exImagePath = rs.getString( "filepath" ); + img.exIsValid = rs.getBoolean( "isvalid" ); + return img; + } catch ( SQLException e ) { + LOGGER.error( "Query failed in DbImage.getImageVersion()", e ); + throw e; + } } public static void createImageBase( ImagePublishData img ) throws TInvocationException @@ -44,7 +86,7 @@ public class DbImage + " istemplate = :istemplate WHERE imagebaseid = :baseid" ); stmt2.setString( "baseid", img.imageBaseId ); stmt2.setString( "displayname", img.imageName ); - stmt2.setString( "updaterid", img.user.userId ); + stmt2.setString( "updaterid", img.uploader.userId ); stmt2.setString( "description", img.description ); stmt2.setInt( "osid", img.osId ); stmt2.setBoolean( "istemplate", img.isTemplate ); @@ -62,8 +104,8 @@ public class DbImage stmt2.setInt( "osid", img.osId ); stmt2.setString( "virtid", img.virtId ); stmt2.setLong( "createtime", img.createTime ); - stmt2.setString( "ownerid", img.user.userId ); - stmt2.setString( "updaterid", img.user.userId ); + stmt2.setString( "ownerid", img.owner.userId ); + stmt2.setString( "updaterid", img.uploader.userId ); stmt2.setBoolean( "istemplate", img.isTemplate ); stmt2.executeUpdate(); } @@ -83,14 +125,15 @@ public class DbImage + " filepath, uploaderid, isvalid, isprocessed, mastersha1, virtualizerconfig)" + " VALUES " + " (:imageversionid, :imagebaseid, :createtime, :expiretime, :filesize," - + " :filepath, :uploaderid, 0, 0, NULL, NULL)" ); + + " :filepath, :uploaderid, 0, 0, NULL, :virtualizerconfig)" ); verStmt.setString( "imageversionid", img.imageVersionId ); verStmt.setString( "imagebaseid", img.imageBaseId ); verStmt.setLong( "createtime", img.createTime ); verStmt.setLong( "expiretime", Util.unixTime() + Globals.getImageValiditySeconds() ); verStmt.setLong( "filesize", img.fileSize ); verStmt.setString( "filepath", relLocalPath ); - verStmt.setString( "uploaderid", img.user.userId ); + verStmt.setString( "uploaderid", img.uploader.userId ); + verStmt.setBinary( "virtualizerconfig", img.getMachineDescription() ); verStmt.execute(); connection.commit(); } catch ( SQLException e ) { @@ -99,4 +142,143 @@ public class DbImage } } + public static void markValid( String imageVersionId, boolean isValid ) throws SQLException + { + try ( MysqlConnection connection = Database.getConnection() ) { + MysqlStatement stmt = connection.prepareStatement( "UPDATE imageversion" + + " SET isvalid = :isvalid WHERE imageversionid = :imageversionid" ); + stmt.setBoolean( "isvalid", isValid ); + stmt.setString( "imageversionid", imageVersionId ); + stmt.executeUpdate(); + updateLatestForVersion( connection, imageVersionId ); + connection.commit(); + } catch ( SQLException e ) { + LOGGER.error( "Query failed in DbImage.markValid()", e ); + throw e; + } + } + + private static void updateLatestForVersion( MysqlConnection connection, String imageVersionId ) throws SQLException + { + MysqlStatement stmt = connection.prepareStatement( "SELECT imagebaseid FROM imageversion" + + " WHERE imageversionid = :imageversionid" ); + stmt.setString( "imageversionid", imageVersionId ); + ResultSet rs = stmt.executeQuery(); + if ( !rs.next() ) + return; + updateLatestForBase( connection, rs.getString( "imagebaseid" ) ); + } + + private static void updateLatestForBase( MysqlConnection connection, String imageBaseId ) throws SQLException + { + MysqlStatement stmt = connection.prepareStatement( "SELECT imageversionid FROM imageversion" + + " WHERE isvalid <> 0 AND expiretime > UNIX_TIMESTAMP() AND imagebaseid = :imagebaseid" + + " ORDER BY createtime DESC LIMIT 1" ); + stmt.setString( "imagebaseid", imageBaseId ); + ResultSet rs = stmt.executeQuery(); + String latestVersionId = null; + if ( rs.next() ) { + latestVersionId = rs.getString( "imageversionid" ); + } + MysqlStatement updateLatestRef = connection.prepareStatement( "UPDATE imagebase SET latestversionid = :latestversionid" + + " WHERE imagebaseid = :imagebaseid" ); + updateLatestRef.setString( "latestversionid", latestVersionId ); + updateLatestRef.setString( "imagebaseid", imageBaseId ); + updateLatestRef.executeUpdate(); + MysqlStatement expireStmt = connection.prepareStatement( "UPDATE imageversion SET expiretime = :expiretime" + + " WHERE imagebaseid = :imagebaseid AND imageversionid <> :latestversionid" ); + expireStmt.setString( "imagebaseid", imageBaseId ); + expireStmt.setString( "latestversionid", latestVersionId ); + expireStmt.setLong( "expiretime", Util.unixTime() + Globals.getOldImageExpireTimeSeconds() ); + expireStmt.executeUpdate(); + } + + public static List<ImageSummaryRead> getPublicList( int page ) throws SQLException, TInvocationException + { + if ( page < 0 ) + throw new TInvocationException( InvocationError.INVALID_DATA, "page must be >= 0" ); + try ( MysqlConnection connection = Database.getConnection() ) { + MysqlStatement stmt = connection.prepareStatement( "SELECT v.imageversionid, v.imagebaseid, v.createtime, v.filesize," + + " v.uploaderid, b.displayname, b.description, b.osid, b.virtid, b.ownerid, b.istemplate FROM imagebase b" + + " INNER JOIN imageversion v ON (b.latestversionid = v.imageversionid)" + + " WHERE v.isvalid = 1" + + " ORDER BY imageversionid " + Paginator.limitStatement( page ) ); + ResultSet rs = stmt.executeQuery(); + List<ImageSummaryRead> list = new ArrayList<>(); + while ( rs.next() ) { + ImageSummaryRead img = new ImageSummaryRead(); + img.createTime = rs.getLong( "createtime" ); + //img.description = rs.getString( "description" ); + img.fileSize = rs.getLong( "filesize" ); + img.imageBaseId = rs.getString( "imagebaseid" ); + img.imageName = rs.getString( "displayname" ); + img.latestVersionId = rs.getString( "imageversionid" ); + img.isTemplate = rs.getBoolean( "istemplate" ); + img.osId = rs.getInt( "osid" ); + img.ownerId = rs.getString( "ownerid" ); + //img.software = rs.get( "" ); TODO + //img.tags = rs.get( "" ); TODO + img.uploaderId = rs.getString( "uploaderid" ); + img.virtId = rs.getString( "virtid" ); + img.userPermissions = emptyPermissions; + img.defaultPermissions = emptyPermissions; + list.add( img ); + } + return list; + } catch ( SQLException e ) { + LOGGER.error( "Query failed in DbImage.getPublicList()", e ); + throw e; + } + } + + protected static List<ImageVersionDetails> getImageVersions( MysqlConnection connection, String imageBaseId ) + throws SQLException + { + List<ImageVersionDetails> versionList = new ArrayList<>(); + MysqlStatement stmt = connection.prepareStatement( "SELECT" + + " imageversionid, createtime, expiretime, filesize, uploaderid," + + " isprocessed FROM imageversion" + + " WHERE imagebaseid = :imagebaseid AND isvalid = 1" ); + stmt.setString( "imagebaseid", imageBaseId ); + ResultSet rs = stmt.executeQuery(); + while ( rs.next() ) { + List<String> software = null; // DbSoftwareTag.getImageVersionSoftwareList(connection, imageVersionId); + String imageVersionId = rs.getString( "imageversionid" ); + versionList.add( new ImageVersionDetails( imageVersionId, rs.getLong( "createtime" ), + rs.getLong( "expiretime" ), rs.getLong( "filesize" ), rs.getString( "uploaderid" ), + true, true, + rs.getBoolean( "isprocessed" ), software ) ); + } + stmt.close(); + return versionList; + } + + public static ImageDetailsRead getImageDetails( String imageBaseId ) throws TNotFoundException, SQLException + { + try ( MysqlConnection connection = Database.getConnection() ) { + MysqlStatement stmt = connection.prepareStatement( "SELECT i.imagebaseid, i.latestversionid, i.displayname," + + " i.description, i.osid, i.virtid, i.createtime, i.updatetime, i.ownerid, i.updaterid, i.istemplate" + + " FROM imagebase i" + + " WHERE i.imagebaseid = :imagebaseid" ); + stmt.setString( "imagebaseid", imageBaseId ); + ResultSet rs = stmt.executeQuery(); + if ( !rs.next() ) + throw new TNotFoundException(); + // Exists: + List<String> tags = null; // DbSoftwareTag.getImageTags(connection, imageBaseId); + List<ImageVersionDetails> versions = getImageVersions( connection, imageBaseId ); + ImageDetailsRead image = new ImageDetailsRead( rs.getString( "imagebaseid" ), + rs.getString( "latestversionid" ), versions, rs.getString( "displayname" ), + rs.getString( "description" ), tags, rs.getInt( "osid" ), rs.getString( "virtid" ), + rs.getLong( "createtime" ), rs.getLong( "updatetime" ), rs.getString( "ownerid" ), + rs.getString( "updaterid" ), ShareMode.FROZEN, + rs.getBoolean( "istemplate" ), emptyPermissions ); + image.userPermissions = emptyPermissions; + return image; + } catch ( SQLException e ) { + LOGGER.error( "Query failed in DbImage.getImageDetails()", e ); + throw e; + } + } + } diff --git a/src/main/java/org/openslx/imagemaster/db/mappers/DbImageBlock.java b/src/main/java/org/openslx/imagemaster/db/mappers/DbImageBlock.java index 7986d87..155f522 100644 --- a/src/main/java/org/openslx/imagemaster/db/mappers/DbImageBlock.java +++ b/src/main/java/org/openslx/imagemaster/db/mappers/DbImageBlock.java @@ -1,6 +1,9 @@ package org.openslx.imagemaster.db.mappers; +import java.nio.ByteBuffer; +import java.sql.ResultSet; import java.sql.SQLException; +import java.util.ArrayList; import java.util.List; import java.util.concurrent.ArrayBlockingQueue; @@ -109,4 +112,68 @@ public class DbImageBlock } } + public static List<Boolean> getMissingStatusList( String imageVersionId ) throws SQLException + { + try ( MysqlConnection connection = Database.getConnection() ) { + MysqlStatement stmt = connection.prepareStatement( "SELECT startbyte, ismissing FROM imageblock" + + " WHERE imageversionid = :imageversionid ORDER BY startbyte ASC" ); + stmt.setString( "imageversionid", imageVersionId ); + ResultSet rs = stmt.executeQuery(); + List<Boolean> list = new ArrayList<>(); + long expectedOffset = 0; + while ( rs.next() ) { + long currentOffset = rs.getLong( "startbyte" ); + if ( currentOffset < expectedOffset ) + continue; + while ( currentOffset > expectedOffset ) { + list.add( Boolean.TRUE ); + expectedOffset += FileChunk.CHUNK_SIZE; + } + if ( currentOffset == expectedOffset ) { + list.add( rs.getBoolean( "ismissing" ) ); + expectedOffset += FileChunk.CHUNK_SIZE; + } + } + return list; + } catch ( SQLException e ) { + LOGGER.error( "Query failed in DbImageBlock.getBlockStatuses()", e ); + throw e; + } + } + + public static List<ByteBuffer> getBlockHashes( String imageVersionId ) throws SQLException + { + try ( MysqlConnection connection = Database.getConnection() ) { + return getBlockHashes( connection, imageVersionId ); + } catch ( SQLException e ) { + LOGGER.error( "Query failed in DbImageBlock.getBlockHashes()", e ); + throw e; + } + } + + private static List<ByteBuffer> getBlockHashes( MysqlConnection connection, String imageVersionId ) + throws SQLException + { + MysqlStatement stmt = connection.prepareStatement( "SELECT startbyte, blocksha1 FROM imageblock" + + " WHERE imageversionid = :imageversionid ORDER BY startbyte ASC" ); + stmt.setString( "imageversionid", imageVersionId ); + ResultSet rs = stmt.executeQuery(); + List<ByteBuffer> list = new ArrayList<>(); + long expectedOffset = 0; + while ( rs.next() ) { + long currentOffset = rs.getLong( "startbyte" ); + if ( currentOffset < expectedOffset ) + continue; + while ( currentOffset > expectedOffset ) { + list.add( null ); + expectedOffset += FileChunk.CHUNK_SIZE; + } + if ( currentOffset == expectedOffset ) { + list.add( ByteBuffer.wrap( rs.getBytes( "blocksha1" ) ) ); + expectedOffset += FileChunk.CHUNK_SIZE; + } + } + return list; + } + } diff --git a/src/main/java/org/openslx/imagemaster/db/mappers/DbUser.java b/src/main/java/org/openslx/imagemaster/db/mappers/DbUser.java index b6040e7..00ec45e 100644 --- a/src/main/java/org/openslx/imagemaster/db/mappers/DbUser.java +++ b/src/main/java/org/openslx/imagemaster/db/mappers/DbUser.java @@ -73,7 +73,18 @@ public class DbUser throw new TNotFoundException(); return user.toUserInfo(); } - + + static UserInfo getUserInfoOrNull( MysqlConnection connection, String userId ) throws SQLException + { + MysqlStatement stmt = connection.prepareStatement( localUserSql + + " WHERE user.userid = :userid" ); + stmt.setString( "userid", userId ); + ResultSet rs = stmt.executeQuery(); + if ( !rs.next() ) + return null; + return localFromRs( rs ).toUserInfo(); + } + public static List<UserInfo> findUser( String organizationId, String searchTerm ) { // TODO Implement @@ -84,7 +95,7 @@ public class DbUser { return exists( user, false ); } - + public static boolean exists( UserInfo user, boolean withIdentity ) { if ( user == null ) diff --git a/src/main/java/org/openslx/imagemaster/db/models/LocalUser.java b/src/main/java/org/openslx/imagemaster/db/models/LocalUser.java index bc9289a..39468c4 100644 --- a/src/main/java/org/openslx/imagemaster/db/models/LocalUser.java +++ b/src/main/java/org/openslx/imagemaster/db/models/LocalUser.java @@ -18,7 +18,7 @@ public class LocalUser public final Role role; public LocalUser( String login, String password, String organizationId, String firstName, String lastName, String eMail, - Role tutor ) + Role role ) { this.login = login; this.organizationId = organizationId; @@ -26,7 +26,7 @@ public class LocalUser this.firstName = firstName; this.lastName = lastName; this.eMail = eMail; - this.role = tutor; + this.role = role; } @Override |