org.jetbrains.webdemo.database.MySqlConnector.java Source code

Java tutorial

Introduction

Here is the source code for org.jetbrains.webdemo.database.MySqlConnector.java

Source

/*
 * Copyright 2000-2015 JetBrains s.r.o.
 *
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 *
 * http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */

package org.jetbrains.webdemo.database;

import com.fasterxml.jackson.databind.ObjectMapper;
import com.fasterxml.jackson.databind.node.ArrayNode;
import com.fasterxml.jackson.databind.node.JsonNodeFactory;
import com.fasterxml.jackson.databind.node.ObjectNode;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.apache.naming.NamingContext;
import org.jetbrains.annotations.Nullable;
import org.jetbrains.webdemo.ErrorWriter;
import org.jetbrains.webdemo.Project;
import org.jetbrains.webdemo.ProjectFile;
import org.jetbrains.webdemo.ResponseUtils;
import org.jetbrains.webdemo.examples.ExamplesUtils;
import org.jetbrains.webdemo.session.SessionInfo;
import org.jetbrains.webdemo.session.UserInfo;

import javax.naming.InitialContext;
import javax.sql.DataSource;
import java.io.IOException;
import java.math.BigInteger;
import java.security.SecureRandom;
import java.sql.*;
import java.util.*;

public class MySqlConnector {
    private static Log log = LogFactory.getLog(MySqlConnector.class);
    private static final MySqlConnector connector = new MySqlConnector();
    private DataSource dataSource;
    private String databaseUrl;
    private ObjectMapper objectMapper = new ObjectMapper();
    private IdentifierGenerator idGenerator = new IdentifierGenerator();

    private MySqlConnector() {
        try {
            InitialContext initCtx = new InitialContext();
            NamingContext envCtx = (NamingContext) initCtx.lookup("java:comp/env");
            dataSource = (DataSource) envCtx.lookup("jdbc/kotlin");
            Connection connection = dataSource.getConnection();
            databaseUrl = connection.toString();
            log.info("Connected to database: " + connection.toString());
            ErrorWriter.getInfoForLog("CONNECT_TO_DATABASE", "-1", "Connected to database: " + databaseUrl);
        } catch (Throwable e) {
            ErrorWriter.ERROR_WRITER.writeExceptionToExceptionAnalyzer(e,
                    SessionInfo.TypeOfRequest.WORK_WITH_DATABASE.name(), "unknown", databaseUrl);
        }
    }

    public static MySqlConnector getInstance() {
        return connector;
    }

    public void createTaskList(List<String> tasksIdentifiers) throws DatabaseOperationException {
        PreparedStatement st = null;
        ResultSet rs = null;
        List<String> storedTasksIdentifiers = new ArrayList<String>();
        try (Connection connection = dataSource.getConnection()) {
            st = connection.prepareStatement("SELECT koans_tasks.public_id FROM koans_tasks");
            rs = st.executeQuery();
            while (rs.next()) {
                storedTasksIdentifiers.add(rs.getString("public_id"));
            }
            for (String taskId : tasksIdentifiers) {
                if (storedTasksIdentifiers.contains(taskId))
                    continue;
                try (PreparedStatement insertStatement = connection
                        .prepareStatement("INSERT koans_tasks (public_id) VALUES (?)")) {
                    insertStatement.setString(1, taskId);
                    insertStatement.executeUpdate();
                }
            }
        } catch (SQLException e) {
            throw new DatabaseOperationException("Can't create tasks list", e);
        } finally {
            closeStatementAndResultSet(st, rs);
        }
    }

    public void addNewUser(UserInfo userInfo) throws DatabaseOperationException {
        try (Connection connection = dataSource.getConnection();
                PreparedStatement st = connection
                        .prepareStatement("INSERT INTO users (client_id, provider, username) VALUES (?, ?, ?)")) {
            if (!findUser(userInfo)) {
                st.setString(1, userInfo.getId());
                st.setString(2, userInfo.getType());
                st.setString(3, userInfo.getName());
                st.executeUpdate();
            } else {
                updateUserName(userInfo);
            }

        } catch (SQLException e) {
            throw new DatabaseOperationException("Can't add user with id:" + userInfo.getId() + " type:"
                    + userInfo.getType() + " name:" + userInfo.getName(), e);
        }
    }

    public void updateUserName(UserInfo userInfo) throws DatabaseOperationException {
        try (Connection connection = dataSource.getConnection();
                PreparedStatement st = connection
                        .prepareStatement("UPDATE users  SET username = ? WHERE client_id = ? AND provider = ?")) {
            st.setString(1, userInfo.getName());
            st.setString(2, userInfo.getId());
            st.setString(3, userInfo.getType());
            st.executeUpdate();
        } catch (SQLException e) {
            throw new DatabaseOperationException(
                    "Can't update username of user: " + userInfo.getId() + " type:" + userInfo.getType(), e);
        }
    }

    private void closeStatement(PreparedStatement st) {
        try {
            if (st != null) {
                st.close();
            }
        } catch (SQLException e) {
            ErrorWriter.ERROR_WRITER.writeExceptionToExceptionAnalyzer(e,
                    SessionInfo.TypeOfRequest.WORK_WITH_DATABASE.name(), "unknown", "null");
        }
    }

    private boolean findUser(UserInfo userInfo) throws SQLException {
        try (Connection connection = dataSource.getConnection();
                PreparedStatement st = connection.prepareStatement(
                        "SELECT users.id FROM users WHERE (users.client_id = ? AND users.provider=?)")) {
            st.setString(1, userInfo.getId());
            st.setString(2, userInfo.getType());
            try (ResultSet rs = st.executeQuery()) {
                return rs.next();
            }
        }
    }

    public void saveFile(UserInfo userInfo, ProjectFile file) throws DatabaseOperationException {
        try (Connection connection = dataSource.getConnection();
                PreparedStatement st = connection
                        .prepareStatement("UPDATE files JOIN " + "projects ON files.project_id = projects.id JOIN "
                                + "users ON projects.owner_id = users.id SET" + " files.content = ? WHERE"
                                + " users.client_id = ? AND users.provider = ? AND files.public_id = ?  ")) {
            st.setString(1, file.getText());
            st.setString(2, userInfo.getId());
            st.setString(3, userInfo.getType());
            st.setString(4, file.getPublicId());
            int rowsUpdated = st.executeUpdate();
            if (rowsUpdated != 1) {
                DatabaseOperationException e = new DatabaseOperationException(rowsUpdated + " files were updated");
                ErrorWriter.ERROR_WRITER.writeExceptionToExceptionAnalyzer(e,
                        SessionInfo.TypeOfRequest.WORK_WITH_DATABASE.name(), "unknown",
                        "user_id " + userInfo.getId() + ", client_type " + userInfo.getType() + ", fileId "
                                + file.getPublicId());
                throw e;
            }
        } catch (SQLException e) {
            if (e.getErrorCode() == 1062) {
                throw new DatabaseOperationException("File with this name already exist in this project", e);
            } else {
                ErrorWriter.ERROR_WRITER.writeExceptionToExceptionAnalyzer(e,
                        SessionInfo.TypeOfRequest.WORK_WITH_DATABASE.name(), "unknown",
                        "Add file " + userInfo.getId() + " " + userInfo.getType() + " " + userInfo.getName() + " "
                                + file.getPublicId() + " " + file.getName());
                throw new DatabaseOperationException("Unknown exception", e);
            }
        }
    }

    public void saveFile(String projectId, ProjectFile file) throws DatabaseOperationException {
        try (Connection connection = dataSource.getConnection();
                PreparedStatement st = connection
                        .prepareStatement("UPDATE files JOIN projects " + "ON projects.id = files.project_id SET "
                                + "files.content = ? WHERE " + "files.name = ? AND " + "projects.public_id = ?")) {
            st.setString(1, file.getText());
            st.setString(2, file.getName());
            st.setString(3, projectId);
            int rowsUpdated = st.executeUpdate();
            if (rowsUpdated != 1) {
                DatabaseOperationException e = new DatabaseOperationException(rowsUpdated + " files were updated");
                ErrorWriter.ERROR_WRITER.writeExceptionToExceptionAnalyzer(e,
                        SessionInfo.TypeOfRequest.WORK_WITH_DATABASE.name(), "unknown",
                        "project_id " + projectId + ", fileId " + file.getPublicId());
                throw e;
            }
        } catch (SQLException e) {
            ErrorWriter.ERROR_WRITER.writeExceptionToExceptionAnalyzer(e,
                    SessionInfo.TypeOfRequest.WORK_WITH_DATABASE.name(), "unknown", "Save file");
            throw new DatabaseOperationException("Unknown exception", e);
        }
    }

    private String escape(String str) {
        return str.replaceAll(" ", "%20");
    }

    private String unEscape(String str) {
        return str.replaceAll("%20", " ");
    }

    private boolean checkCountOfFiles(UserInfo userInfo) {
        PreparedStatement st = null;
        ResultSet rs = null;
        try (Connection connection = dataSource.getConnection()) {
            st = connection.prepareStatement(
                    "SELECT count(*) FROM files " + "JOIN projects ON projects.id = files.project_id "
                            + "JOIN users ON users.id = projects.owner_id "
                            + "WHERE (users.client_id=? AND users.provider=?)");
            st.setString(1, userInfo.getId());
            st.setString(2, userInfo.getType());
            rs = st.executeQuery();
            if (!rs.next()) {
                return false;
            }
            int count = Integer.parseInt(rs.getString("count(*)"));
            return count < 100;
        } catch (Throwable e) {
            ErrorWriter.ERROR_WRITER.writeExceptionToExceptionAnalyzer(e,
                    SessionInfo.TypeOfRequest.WORK_WITH_DATABASE.name(), "unknown",
                    userInfo.getId() + " " + userInfo.getType() + " " + userInfo.getName());
            return false;
        } finally {
            closeStatementAndResultSet(st, rs);
        }
    }

    private boolean checkCountOfProjects(UserInfo userInfo) {
        PreparedStatement st = null;
        ResultSet rs = null;
        try (Connection connection = dataSource.getConnection()) {
            st = connection.prepareStatement(
                    "SELECT count(*) FROM projects " + "JOIN users ON users.id = projects.owner_id "
                            + "WHERE (users.client_id=? AND users.provider=?)");
            st.setString(1, userInfo.getId());
            st.setString(2, userInfo.getType());
            rs = st.executeQuery();
            if (!rs.next()) {
                return false;
            }
            int count = Integer.parseInt(rs.getString("count(*)"));
            return count < 100;
        } catch (Throwable e) {
            ErrorWriter.ERROR_WRITER.writeExceptionToExceptionAnalyzer(e,
                    SessionInfo.TypeOfRequest.WORK_WITH_DATABASE.name(), "unknown",
                    userInfo.getId() + " " + userInfo.getType() + " " + userInfo.getName());
            return false;
        } finally {
            closeStatementAndResultSet(st, rs);
        }
    }

    public void saveProject(UserInfo userInfo, String publicId, Project project, String projectType)
            throws DatabaseOperationException {
        int userId = getUserId(userInfo);
        try (Connection connection = dataSource.getConnection();
                PreparedStatement st = connection.prepareStatement(
                        "UPDATE projects SET projects.args = ? , projects.run_configuration = ?, projects.type = ? "
                                + "WHERE projects.owner_id = ?  AND projects.name = ? AND projects.public_id = ?")) {
            st.setString(1, project.args);
            st.setString(2, project.confType);
            st.setString(3, projectType);
            st.setString(4, userId + "");
            st.setString(5, escape(project.name));
            st.setString(6, publicId);
            int rowsUpdated = st.executeUpdate();
            if (rowsUpdated != 1) {
                DatabaseOperationException e = new DatabaseOperationException(
                        rowsUpdated + " projects were updated");
                ErrorWriter.ERROR_WRITER.writeExceptionToExceptionAnalyzer(e,
                        SessionInfo.TypeOfRequest.WORK_WITH_DATABASE.name(), "unknown",
                        "user_id " + userInfo.getId() + ", client_type " + userInfo.getType() + ", projectId "
                                + publicId);
                throw e;
            }
        } catch (SQLException e) {
            if (e.getErrorCode() == 1062) {
                throw new DatabaseOperationException("Project with this name already exist", e);
            } else {
                ErrorWriter.ERROR_WRITER.writeExceptionToExceptionAnalyzer(e,
                        SessionInfo.TypeOfRequest.WORK_WITH_DATABASE.name(), "unknown",
                        "Add project " + userInfo.getId() + " " + userInfo.getType() + " " + userInfo.getName()
                                + " " + project.name);
                throw new DatabaseOperationException("Unknown exception", e);
            }
        }
    }

    public String addProject(UserInfo userInfo, String name, String type) throws DatabaseOperationException {
        try {
            String projectId = addProject(userInfo, new Project(name, "", "java"), type, null);
            String fileId = addFileToProject(userInfo, projectId, name, "fun main(args: Array<String>) {\n\n}");

            ObjectNode response = new ObjectNode(JsonNodeFactory.instance);
            response.put("projectId", projectId);
            response.put("fileId", fileId);
            return objectMapper.writeValueAsString(response);
        } catch (IOException e) {
            throw new DatabaseOperationException("IO exception");
        }
    }

    public String addAdventOfCodeProject(UserInfo userInfo, String name, String inputFileContent)
            throws DatabaseOperationException {
        try {
            String projectId = addProject(userInfo, new Project(name, "", "java"), "ADVENT_OF_CODE_PROJECT", null);
            String fileId = addFileToProject(userInfo, projectId, name,
                    "fun main(args: Array<String>) {\n//your input stored in the `input` variable\n}");
            String inputFileId = addFileToProject(userInfo, projectId, "Input.kt", inputFileContent);

            ObjectNode response = new ObjectNode(JsonNodeFactory.instance);
            response.put("projectId", projectId);
            response.put("fileId", fileId);
            response.put("inputFileId", inputFileId);
            return objectMapper.writeValueAsString(response);
        } catch (IOException e) {
            throw new DatabaseOperationException("IO exception");
        }
    }

    public String addProject(UserInfo userInfo, Project project, String type) throws DatabaseOperationException {
        return addProject(userInfo, project, type, null);
    }

    private String addProject(UserInfo userInfo, Project project, String type, Integer taskId)
            throws DatabaseOperationException {
        if (!checkCountOfProjects(userInfo)) {
            throw new DatabaseOperationException("You can't save more than 100 projects");
        }

        int userId = getUserId(userInfo);

        PreparedStatement st = null;
        try (Connection connection = dataSource.getConnection()) {
            String publicId = idGenerator.nextProjectId();

            st = connection.prepareStatement(
                    "INSERT INTO projects (owner_id, name, args, run_configuration, origin, public_id, read_only_files, type, task_id) VALUES (?,?,?,?,?,?,?,?,?) ");
            st.setString(1, userId + "");
            st.setString(2, escape(project.name));
            st.setString(3, project.args);
            st.setString(4, project.confType);
            st.setString(5, project.originUrl);
            st.setString(6, publicId);
            st.setString(7, objectMapper.writeValueAsString(project.readOnlyFileNames));
            st.setString(8, type);
            if (taskId == null) {
                st.setNull(9, Types.INTEGER);
            } else {
                st.setInt(9, taskId);
            }
            st.execute();

            int projectId = getProjectId(userInfo, publicId);
            for (ProjectFile file : project.files) {
                addFileToProject(userInfo, projectId, file.getName(), file.getText());
            }

            return publicId;
        } catch (SQLException e) {
            if (e.getErrorCode() == 1062) {
                throw new DatabaseOperationException("Project with this name already exist", e);
            } else {
                ErrorWriter.ERROR_WRITER.writeExceptionToExceptionAnalyzer(e,
                        SessionInfo.TypeOfRequest.WORK_WITH_DATABASE.name(), "unknown",
                        "Add project " + userInfo.getId() + " " + userInfo.getType() + " " + userInfo.getName()
                                + " " + project.name);
                throw new DatabaseOperationException("Unknown exception", e);
            }
        } catch (Throwable e) {
            ErrorWriter.ERROR_WRITER.writeExceptionToExceptionAnalyzer(e,
                    SessionInfo.TypeOfRequest.WORK_WITH_DATABASE.name(), "unknown",
                    "Add project " + userInfo.getId() + " " + userInfo.getType() + " " + userInfo.getName() + " "
                            + project.name);
            throw new DatabaseOperationException("Unknown exception", e);
        } finally {
            closeStatement(st);
        }
    }

    public String addFileToProject(UserInfo userInfo, String projectPublicId, String fileName)
            throws DatabaseOperationException {
        return addFileToProject(userInfo, getProjectId(userInfo, projectPublicId), fileName, "");
    }

    public String addFileToProject(UserInfo userInfo, String projectPublicId, String fileName, String content)
            throws DatabaseOperationException {
        return addFileToProject(userInfo, getProjectId(userInfo, projectPublicId), fileName, content);
    }

    private String addFileToProject(UserInfo userInfo, int projectId, String fileName, String content)
            throws DatabaseOperationException {
        if (!checkCountOfFiles(userInfo)) {
            throw new DatabaseOperationException("You can't save more than 100 files");
        }
        fileName = escape(fileName.endsWith(".kt") ? fileName : fileName + ".kt");
        try (Connection connection = dataSource.getConnection();
                PreparedStatement st = connection.prepareStatement(
                        "INSERT INTO files (project_id, public_id, name, content) VALUES (?,?,?,?) ")) {
            String publicId = idGenerator.nextFileId();

            st.setString(1, projectId + "");
            st.setString(2, publicId);
            st.setString(3, fileName);
            st.setString(4, content);
            st.execute();

            return publicId;
        } catch (SQLException e) {
            if (e.getErrorCode() == 1062) {
                throw new DatabaseOperationException("File with this name already exist in this project", e);
            } else {
                ErrorWriter.ERROR_WRITER.writeExceptionToExceptionAnalyzer(e,
                        SessionInfo.TypeOfRequest.WORK_WITH_DATABASE.name(), "unknown",
                        "Add file " + userInfo.getId() + " " + userInfo.getType() + " " + userInfo.getName() + " "
                                + projectId + " " + fileName);
                throw new DatabaseOperationException("Unknown exception", e);
            }
        } catch (Throwable e) {
            ErrorWriter.ERROR_WRITER.writeExceptionToExceptionAnalyzer(e,
                    SessionInfo.TypeOfRequest.WORK_WITH_DATABASE.name(), "unknown",
                    "Add file " + userInfo.getId() + " " + userInfo.getType() + " " + userInfo.getName() + " "
                            + projectId + " " + fileName);
            throw new DatabaseOperationException("Unknown exception", e);
        }
    }

    public ArrayNode getProjectHeaders(UserInfo userInfo, String projectType) throws DatabaseOperationException {
        PreparedStatement st = null;
        ResultSet rs = null;
        try (Connection connection = dataSource.getConnection()) {
            st = connection.prepareStatement("SELECT projects.public_id, projects.name FROM projects JOIN "
                    + "users ON projects.owner_id = users.id WHERE "
                    + "(users.client_id = ? AND users.provider = ? AND projects.type = ?)");
            st.setString(1, userInfo.getId());
            st.setString(2, userInfo.getType());
            st.setString(3, projectType);

            rs = st.executeQuery();

            ArrayNode projects = new ArrayNode(JsonNodeFactory.instance);
            while (rs.next()) {
                ObjectNode object = new ObjectNode(JsonNodeFactory.instance);
                object.put("name", unEscape(rs.getString("name")));
                object.put("publicId", rs.getString("public_id"));
                object.put("modified", false);
                projects.add(object);
            }

            return projects;
        } catch (Throwable e) {
            ErrorWriter.ERROR_WRITER.writeExceptionToExceptionAnalyzer(e,
                    SessionInfo.TypeOfRequest.WORK_WITH_DATABASE.name(), "unknown",
                    userInfo.getId() + " " + userInfo.getType() + " " + userInfo.getName());
            throw new DatabaseOperationException("Unknown error while loading list of your programs");
        } finally {
            closeStatementAndResultSet(st, rs);
        }
    }

    public Project getProjectContent(String id) throws DatabaseOperationException {
        PreparedStatement st = null;
        ResultSet rs = null;
        try (Connection connection = dataSource.getConnection()) {
            st = connection.prepareStatement("SELECT * FROM projects WHERE projects.public_id = ?");
            st.setString(1, id);
            rs = st.executeQuery();

            if (rs.next()) {
                List<String> readOnlyFileNames;
                if (rs.getString("read_only_files") == null || rs.getString("read_only_files").equals("")) {
                    readOnlyFileNames = new ArrayList<>();
                } else {
                    readOnlyFileNames = objectMapper.readValue(rs.getString("read_only_files"), List.class);
                }
                Project project = new Project(id, unEscape(rs.getString("name")), rs.getString("args"),
                        rs.getString("run_configuration"), rs.getString("origin"), readOnlyFileNames);
                ExamplesUtils.addUnmodifiableFilesToProject(project);

                st = connection.prepareStatement("SELECT * FROM files WHERE project_id = ?");
                st.setString(1, rs.getInt("id") + "");
                rs = st.executeQuery();
                while (rs.next()) {
                    ProjectFile file = new ProjectFile(unEscape(rs.getString("name")), rs.getString("content"),
                            true, rs.getString("public_id"), ProjectFile.Type.KOTLIN_FILE);
                    project.files.add(file);
                }
                return project;
            } else {
                return null;
            }
        } catch (Throwable e) {
            ErrorWriter.ERROR_WRITER.writeExceptionToExceptionAnalyzer(e,
                    SessionInfo.TypeOfRequest.WORK_WITH_DATABASE.name(), "unknown", id);
            throw new DatabaseOperationException("Unknown error while loading your project", e);
        } finally {
            closeStatementAndResultSet(st, rs);
        }
    }

    public Project getStoredSolution(UserInfo userInfo, String taskId) throws DatabaseOperationException {
        String solutionId = getSolutionId(userInfo, taskId);
        if (solutionId == null)
            return null;
        return getProjectContent(solutionId);
    }

    public boolean isProjectExists(String publicId) throws DatabaseOperationException {
        PreparedStatement st = null;
        ResultSet rs = null;
        try (Connection connection = dataSource.getConnection()) {
            st = connection.prepareStatement("SELECT projects.id FROM projects WHERE projects.public_id = ?");
            st.setString(1, publicId);
            rs = st.executeQuery();
            if (rs.next()) {
                return true;
            } else {
                return false;
            }

        } catch (Throwable e) {
            ErrorWriter.ERROR_WRITER.writeExceptionToExceptionAnalyzer(e,
                    SessionInfo.TypeOfRequest.WORK_WITH_DATABASE.name(), "unknown", publicId);
            throw new DatabaseOperationException("Unknown exception");
        } finally {
            closeStatementAndResultSet(st, rs);
        }
    }

    private void closeStatementAndResultSet(PreparedStatement st, ResultSet rs) {
        try {
            if (st != null) {
                st.close();
            }
            if (rs != null) {
                rs.close();
            }
        } catch (SQLException e) {
            ErrorWriter.ERROR_WRITER.writeExceptionToExceptionAnalyzer(e,
                    SessionInfo.TypeOfRequest.WORK_WITH_DATABASE.name(), "unknown", "null");
        }
    }

    public void deleteFile(UserInfo userInfo, String publicId) throws DatabaseOperationException {
        try (Connection connection = dataSource.getConnection();
                PreparedStatement st = connection.prepareStatement(
                        "DELETE files.* FROM files JOIN" + " projects ON files.project_id = projects.id JOIN "
                                + " users ON projects.owner_id = users.id WHERE "
                                + " users.client_id = ? AND users.provider  = ? AND files.public_id = ?")) {
            st.setString(1, userInfo.getId());
            st.setString(2, userInfo.getType());
            st.setString(3, publicId);
            int rowsDeleted = st.executeUpdate();
            if (rowsDeleted != 1) {
                DatabaseOperationException e = new DatabaseOperationException(rowsDeleted + " files were deleted");
                ErrorWriter.ERROR_WRITER.writeExceptionToExceptionAnalyzer(e,
                        SessionInfo.TypeOfRequest.WORK_WITH_DATABASE.name(), "unknown",
                        "user_id " + userInfo.getId() + ", client_type " + userInfo.getType() + ", fileId "
                                + publicId);
                throw e;
            }
        } catch (Throwable e) {
            ErrorWriter.ERROR_WRITER.writeExceptionToExceptionAnalyzer(e,
                    SessionInfo.TypeOfRequest.WORK_WITH_DATABASE.name(), "unknown",
                    userInfo.getId() + " " + userInfo.getType() + " " + userInfo.getName() + " " + publicId);
            throw new DatabaseOperationException("Unknown exception ", e);
        }

    }

    public void deleteUnmodifiableFile(UserInfo userInfo, String fileName, String projectId)
            throws DatabaseOperationException {
        PreparedStatement st = null;
        ResultSet rs = null;
        try (Connection connection = dataSource.getConnection()) {
            st = connection.prepareStatement(
                    "SELECT read_only_files FROM projects " + "JOIN users ON users.id = projects.owner_id "
                            + "WHERE users.client_id = ? AND users.provider = ? AND projects.public_id = ?");
            st.setString(1, userInfo.getId());
            st.setString(2, userInfo.getType());
            st.setString(3, projectId);
            rs = st.executeQuery();
            if (rs.next()) {
                List<String> readOnlyFileNames = objectMapper.readValue(rs.getString("read_only_files"),
                        List.class);
                if (readOnlyFileNames != null) {
                    if (!readOnlyFileNames.contains(fileName)) {
                        DatabaseOperationException e = new DatabaseOperationException("Can't find read-only file");
                        ErrorWriter.ERROR_WRITER.writeExceptionToExceptionAnalyzer(e,
                                SessionInfo.TypeOfRequest.WORK_WITH_DATABASE.name(), "unknown",
                                "user_id " + userInfo.getId() + ", client_type " + userInfo.getType()
                                        + ", projectId " + projectId + ", fileName" + fileName);
                        throw e;
                    }
                    readOnlyFileNames.remove(fileName);
                }
                st = connection
                        .prepareStatement("UPDATE projects SET read_only_files=? WHERE projects.public_id = ?");
                st.setString(1, objectMapper.writeValueAsString(readOnlyFileNames));
                st.setString(2, projectId);
                int rowsUpdated = st.executeUpdate();
                if (rowsUpdated != 1) {
                    DatabaseOperationException e = new DatabaseOperationException(
                            rowsUpdated + " projects were updated");
                    ErrorWriter.ERROR_WRITER.writeExceptionToExceptionAnalyzer(e,
                            SessionInfo.TypeOfRequest.WORK_WITH_DATABASE.name(), "unknown",
                            "user_id " + userInfo.getId() + ", client_type " + userInfo.getType() + ", projectId "
                                    + projectId + ", fileName" + fileName);
                    throw e;
                }
            }
        } catch (SQLException e) {
            ErrorWriter.ERROR_WRITER.writeExceptionToExceptionAnalyzer(e,
                    SessionInfo.TypeOfRequest.WORK_WITH_DATABASE.name(), "unknown",
                    userInfo.getId() + " " + userInfo.getType() + " " + userInfo.getName() + " " + fileName);
            throw new DatabaseOperationException("Unknown exception ", e);
        } catch (IOException e) {
            ErrorWriter.ERROR_WRITER.writeExceptionToExceptionAnalyzer(e,
                    SessionInfo.TypeOfRequest.WORK_WITH_DATABASE.name(), "unknown",
                    userInfo.getId() + " " + userInfo.getType() + " " + userInfo.getName() + " " + fileName);
            throw new DatabaseOperationException("Unknown exception ", e);
        } finally {
            closeStatementAndResultSet(st, rs);
        }

    }

    public void renameFile(UserInfo userInfo, String publicId, String newName) throws DatabaseOperationException {
        try (Connection connection = dataSource.getConnection();
                PreparedStatement st = connection
                        .prepareStatement("UPDATE files JOIN " + "projects ON files.project_id = projects.id JOIN "
                                + "users ON projects.owner_id = users.id SET " + "files.name = ? WHERE "
                                + "users.client_id = ? AND  users.provider = ? AND files.public_id = ?")) {
            st.setString(1, escape(newName));
            st.setString(2, userInfo.getId());
            st.setString(3, userInfo.getType());
            st.setString(4, publicId);
            int rowsUpdated = st.executeUpdate();
            if (rowsUpdated != 1) {
                DatabaseOperationException e = new DatabaseOperationException(rowsUpdated + " files were updated");
                ErrorWriter.ERROR_WRITER.writeExceptionToExceptionAnalyzer(e,
                        SessionInfo.TypeOfRequest.WORK_WITH_DATABASE.name(), "unknown",
                        "user_id " + userInfo.getId() + ", client_type " + userInfo.getType() + ", newName "
                                + newName + ", fileId" + publicId);
                throw e;
            }
        } catch (SQLException e) {
            if (e.getErrorCode() == 1062) {
                throw new DatabaseOperationException("File with this name already exist in this project", e);
            } else {
                ErrorWriter.ERROR_WRITER.writeExceptionToExceptionAnalyzer(e,
                        SessionInfo.TypeOfRequest.WORK_WITH_DATABASE.name(), "unknown",
                        "Rename file " + userInfo.getId() + " " + userInfo.getType() + " " + userInfo.getName()
                                + " " + publicId);
                throw new DatabaseOperationException("Unknown exception", e);
            }
        }
    }

    public void deleteProject(UserInfo userInfo, String publicId) throws DatabaseOperationException {
        int userId = getUserId(userInfo);
        try (Connection connection = dataSource.getConnection();
                PreparedStatement st = connection.prepareStatement(
                        "DELETE FROM projects WHERE projects.owner_id = ? AND projects.public_id = ?")) {
            st.setString(1, userId + "");
            st.setString(2, publicId);
            int rowsDeleted = st.executeUpdate();
            if (rowsDeleted != 1) {
                DatabaseOperationException e = new DatabaseOperationException(
                        rowsDeleted + " projects were deleted");
                ErrorWriter.ERROR_WRITER.writeExceptionToExceptionAnalyzer(e,
                        SessionInfo.TypeOfRequest.WORK_WITH_DATABASE.name(), "unknown",
                        "user_id " + userInfo.getId() + ", client_type " + userInfo.getType() + ", projectId "
                                + publicId);
                throw e;
            }
        } catch (Throwable e) {
            ErrorWriter.ERROR_WRITER.writeExceptionToExceptionAnalyzer(e,
                    SessionInfo.TypeOfRequest.WORK_WITH_DATABASE.name(), "unknown",
                    "Delete project " + userInfo.getId() + " " + userInfo.getType() + " " + userInfo.getName() + " "
                            + publicId);
            throw new DatabaseOperationException("Unknown exception");
        }

    }

    public void renameProject(UserInfo userInfo, String publicId, String newName)
            throws DatabaseOperationException {
        int userId = getUserId(userInfo);
        try (Connection connection = dataSource.getConnection();
                PreparedStatement st = connection.prepareStatement(
                        "UPDATE projects SET projects.name = ? WHERE projects.public_id =? AND projects.owner_id = ?")) {
            st.setString(1, escape(newName));
            st.setString(2, publicId);
            st.setString(3, userId + "");
            int rowsUpdated = st.executeUpdate();
            if (rowsUpdated != 1) {
                DatabaseOperationException e = new DatabaseOperationException(
                        rowsUpdated + " projects were updated");
                ErrorWriter.ERROR_WRITER.writeExceptionToExceptionAnalyzer(e,
                        SessionInfo.TypeOfRequest.WORK_WITH_DATABASE.name(), "unknown",
                        "user_id " + userInfo.getId() + ", client_type " + userInfo.getType() + ", newName "
                                + newName + ", projectId" + publicId);
                throw e;
            }
        } catch (SQLException e) {
            if (e.getErrorCode() == 1062) {
                throw new DatabaseOperationException("Project with this name already exist", e);
            } else {
                ErrorWriter.ERROR_WRITER.writeExceptionToExceptionAnalyzer(e,
                        SessionInfo.TypeOfRequest.WORK_WITH_DATABASE.name(), "unknown",
                        "Rename project " + userInfo.getId() + " " + userInfo.getType() + " " + userInfo.getName()
                                + " " + publicId);
                throw new DatabaseOperationException("Unknown exception", e);
            }
        } catch (Throwable e) {
            ErrorWriter.ERROR_WRITER.writeExceptionToExceptionAnalyzer(e,
                    SessionInfo.TypeOfRequest.WORK_WITH_DATABASE.name(), "unknown",
                    "Rename project " + userInfo.getId() + " " + userInfo.getType() + " " + userInfo.getName() + " "
                            + publicId + " ");
            throw new DatabaseOperationException("Unknown exception", e);
        }
    }

    private int getUserId(UserInfo userInfo) throws DatabaseOperationException {
        try (Connection connection = dataSource.getConnection();
                PreparedStatement st = connection.prepareStatement(
                        "SELECT users.id FROM users WHERE (users.client_id = ? AND users.provider=?)")) {
            st.setString(1, userInfo.getId());
            st.setString(2, userInfo.getType());
            try (ResultSet rs = st.executeQuery()) {
                if (rs.next()) {
                    return rs.getInt("id");
                } else {
                    throw new DatabaseOperationException("User with id" + userInfo.getId() + " don't exist");
                }
            }
        } catch (SQLException e) {
            ErrorWriter.ERROR_WRITER.writeExceptionToExceptionAnalyzer(e,
                    SessionInfo.TypeOfRequest.WORK_WITH_DATABASE.name(), "unknown",
                    userInfo.getId() + " " + userInfo.getType() + " " + userInfo.getName());
            throw new DatabaseOperationException("Unknown exception", e);
        }
    }

    private int getProjectId(UserInfo userInfo, String publicId) throws DatabaseOperationException {
        try (Connection connection = dataSource.getConnection();
                PreparedStatement st = connection.prepareStatement(
                        "SELECT projects.id FROM projects JOIN " + "users ON projects.owner_id =users.id WHERE "
                                + "( users.client_id = ? AND  users.provider = ? AND projects.public_id = ?)")) {
            st.setString(1, userInfo.getId());
            st.setString(2, userInfo.getType());
            st.setString(3, publicId);
            try (ResultSet rs = st.executeQuery()) {
                if (rs.next()) {
                    return rs.getInt("id");
                } else {
                    throw new DatabaseOperationException("Project with this name don't exist");
                }
            }
        } catch (Throwable e) {
            ErrorWriter.ERROR_WRITER.writeExceptionToExceptionAnalyzer(e,
                    SessionInfo.TypeOfRequest.WORK_WITH_DATABASE.name(), "unknown",
                    userInfo.getId() + " " + userInfo.getType() + " " + userInfo.getName());
            throw new DatabaseOperationException("UnknownException", e);
        }
    }

    public Integer getTaskId(String taskPublicId) throws DatabaseOperationException {
        PreparedStatement st = null;
        ResultSet rs = null;
        try (Connection connection = dataSource.getConnection()) {
            st = connection.prepareStatement("SELECT koans_tasks.id FROM koans_tasks WHERE public_id = ?");
            st.setString(1, taskPublicId);
            rs = st.executeQuery();
            if (rs.next()) {
                return rs.getInt(1);
            } else {
                return null;
            }
        } catch (Exception e) {
            throw new DatabaseOperationException("Unknown exception", e);
        } finally {
            closeStatementAndResultSet(st, rs);
        }
    }

    public Map<String, Boolean> getUserTaskStatuses(UserInfo userInfo) throws DatabaseOperationException {
        PreparedStatement st = null;
        ResultSet rs = null;
        try (Connection connection = dataSource.getConnection()) {
            Map<String, Boolean> result = new HashMap<>();
            st = connection.prepareStatement("SELECT koans_tasks.public_id, projects.type FROM koans_tasks JOIN "
                    + "projects ON koans_tasks.id = projects.task_id JOIN "
                    + "users ON users.id = projects.owner_id WHERE " + "users.provider = ? AND "
                    + "users.client_id = ? AND " + "(projects.type = 'KOANS_TASK' OR "
                    + "projects.type = 'INCOMPLETE_KOANS_TASK')");
            st.setString(1, userInfo.getType());
            st.setString(2, userInfo.getId());
            rs = st.executeQuery();
            while (rs.next()) {
                boolean completed = rs.getString("type").equals("KOANS_TASK");
                result.put(rs.getString("public_id"), completed);
            }
            return result;
        } catch (SQLException e) {
            ErrorWriter.ERROR_WRITER.writeExceptionToExceptionAnalyzer(e,
                    SessionInfo.TypeOfRequest.WORK_WITH_DATABASE.name(), "Get list of tasks",
                    userInfo.getId() + " " + userInfo.getType() + " " + userInfo.getName());
            throw new DatabaseOperationException("Unknown exception", e);
        } finally {
            closeStatementAndResultSet(st, rs);
        }
    }

    @Nullable
    public String getProjectNameById(String projectId) throws DatabaseOperationException {
        PreparedStatement st = null;
        ResultSet rs = null;
        try (Connection connection = dataSource.getConnection()) {
            st = connection
                    .prepareStatement("SELECT projects.name FROM projects WHERE " + "projects.public_id = ?");
            st.setString(1, projectId);
            rs = st.executeQuery();
            if (rs.next()) {
                return unEscape(rs.getString("name"));
            } else {
                return null;
            }
        } catch (Exception e) {
            throw new DatabaseOperationException("Unknown exception");
        } finally {
            closeStatementAndResultSet(st, rs);
        }
    }

    public ProjectFile getFile(String publicId) throws DatabaseOperationException {
        PreparedStatement st = null;
        ResultSet rs = null;
        try (Connection connection = dataSource.getConnection()) {
            st = connection.prepareStatement("SELECT * FROM files WHERE files.public_id = ?");
            st.setString(1, publicId);
            st.execute();
            rs = st.executeQuery();
            if (rs.next()) {
                String name = rs.getString("name");
                String content = rs.getString("content");
                return new ProjectFile(name, content, true, publicId, ProjectFile.Type.KOTLIN_FILE);
            } else {
                return null;
            }
        } catch (Throwable e) {
            ErrorWriter.ERROR_WRITER.writeExceptionToExceptionAnalyzer(e,
                    SessionInfo.TypeOfRequest.WORK_WITH_DATABASE.name(), "unknown", "Get file " + publicId);
            throw new DatabaseOperationException("Unknown exception", e);
        } finally {
            closeStatementAndResultSet(st, rs);
        }
    }

    public void saveSolution(UserInfo userInfo, Project solution, boolean completed)
            throws DatabaseOperationException {
        String solutionId = getSolutionId(userInfo, solution.id);
        if (solutionId == null) {
            Integer taskId = getTaskId(solutionId);
            String type = completed ? "KOANS_TASK" : "INCOMPLETE_KOANS_TASK";
            addProject(userInfo, solution, type, taskId);
        } else {
            for (ProjectFile file : solution.files) {
                saveFile(solutionId, file);
            }
            String projectType = completed ? "KOANS_TASK" : "INCOMPLETE_KOANS_TASK";
            saveProject(userInfo, solutionId, solution, projectType);
        }
    }

    @Nullable
    private String getSolutionId(UserInfo userInfo, String taskId) throws DatabaseOperationException {
        if (!userInfo.isLogin())
            return null;
        PreparedStatement st = null;
        ResultSet rs = null;
        try (Connection connection = dataSource.getConnection()) {
            st = connection.prepareStatement(
                    "SELECT projects.public_id FROM projects JOIN " + "users ON users.id = projects.owner_id JOIN "
                            + "koans_tasks ON koans_tasks.id = projects.task_id WHERE " + "users.provider = ? AND "
                            + "users.client_id = ? AND " + "koans_tasks.public_id = ?");
            st.setString(1, userInfo.getType());
            st.setString(2, userInfo.getId());
            st.setString(3, taskId);
            rs = st.executeQuery();
            if (rs.next()) {
                return rs.getString(1);
            } else {
                return null;
            }
        } catch (Throwable e) {
            ErrorWriter.ERROR_WRITER.writeExceptionToExceptionAnalyzer(e,
                    SessionInfo.TypeOfRequest.WORK_WITH_DATABASE.name(), "unknown", "Get solution id " + taskId);
            throw new DatabaseOperationException("Unknown exception", e);
        } finally {
            closeStatementAndResultSet(st, rs);
        }
    }

    public void deleteSolution(UserInfo userInfo, String taskId) throws DatabaseOperationException {
        String solutionId = getSolutionId(userInfo, taskId);
        if (solutionId != null)
            deleteProject(userInfo, solutionId);
    }

    private final class IdentifierGenerator {
        private SecureRandom random = new SecureRandom();

        private String nextId() {
            return new BigInteger(130, random).toString(32);
        }

        public String nextProjectId() throws SQLException {
            while (true) {
                String id = nextId();
                PreparedStatement st = null;
                ResultSet rs = null;
                try (Connection connection = dataSource.getConnection()) {
                    st = connection
                            .prepareStatement("SELECT COUNT(projects.public_id) FROM projects WHERE public_id = ?");
                    st.setString(1, id);
                    rs = st.executeQuery();
                    rs.next();
                    int numberOfRows = rs.getInt(1);
                    if (numberOfRows == 0)
                        return id;
                } finally {
                    closeStatementAndResultSet(st, rs);
                }
            }
        }

        public String nextFileId() throws SQLException {
            while (true) {
                String id = nextId();
                PreparedStatement st = null;
                ResultSet rs = null;
                try (Connection connection = dataSource.getConnection()) {
                    st = connection
                            .prepareStatement("SELECT COUNT(files.public_id) FROM files WHERE public_id = ?");
                    st.setString(1, id);
                    rs = st.executeQuery();
                    rs.next();
                    int numberOfRows = rs.getInt(1);
                    if (numberOfRows == 0)
                        return id;
                } finally {
                    closeStatementAndResultSet(st, rs);
                }
            }
        }
    }
}