Java Community | Help. Code. Learn.•7mo ago

SQLITE

124 Replies

JavaBotOP•7mo ago

⌛ This post has been reserved for your question.

Hey @Mookha! Please use /close or the Close Post button above when your problem is solved. Please remember to follow the help guidelines. This post will be automatically marked as dormant after 300 minutes of inactivity.

TIP: Narrow down your issue to simple and precise questions to maximize the chance that others will reply in here.

Nothing•7mo ago

Nothing•7mo ago

Does deleting or inserting or query being called multiple times at the same time cause problems for sqlite

Does deleting or inserting or query being called multiple times at the same time cause problems for sqlite

userID (is discord id) PlayerName (is the player name in minecraft with a name length from 3 to 16 characters) Cooldown (is an 11-digit timestamp) 2

Have I used data types well?

Have I used data types well?

JavaBotOP•7mo ago

💤 Post marked as dormant

This post has been inactive for over 300 minutes, thus, it has been archived. If your question was not answered yet, feel free to re-open this post or create a new one. In case your post is not getting any attention, you can try to use /help ping. Warning: abusing this will result in moderative actions taken against you.

💤 Post marked as dormant

This post has been inactive for over 300 minutes, thus, it has been archived. If your question was not answered yet, feel free to re-open this post or create a new one. In case your post is not getting any attention, you can try to use /help ping. Warning: abusing this will result in moderative actions taken against you.

💤 Post marked as dormant

This post has been inactive for over 300 minutes, thus, it has been archived. If your question was not answered yet, feel free to re-open this post or create a new one. In case your post is not getting any attention, you can try to use /help ping. Warning: abusing this will result in moderative actions taken against you.

dan1st•7mo ago

How are you inserting/deleting stuff? if you have multiple INSERT INTO statements with the same content, it may fail/throw an exception if it violates constraints or you might have multiple rows if it doesn't violate constraints (e.g. you use AUTOINCREMENT for the primary key and no relevant unique constraints) If you have multiple DELETE FROM statements that delete the same data, the later ones would just finished with "0 rows modified"

Nothing•7mo ago

are never the same in value

dan1st•7mo ago

Nothing•7mo ago

public void insertUser(long userID, String playerName, long coolDown) throws SQLException { String sql = "INSERT INTO Linked (userID, PlayerName, CoolDown) VALUES (?, ?, ?)"; try (PreparedStatement pstmt = connection.prepareStatement(sql)) { pstmt.setLong(1, userID); pstmt.setString(2, playerName); pstmt.setLong(3, coolDown); pstmt.executeUpdate(); } }

public void insertUser(long userID, String playerName, long coolDown) throws SQLException { String sql = "INSERT INTO Linked (userID, PlayerName, CoolDown) VALUES (?, ?, ?)"; try (PreparedStatement pstmt = connection.prepareStatement(sql)) { pstmt.setLong(1, userID); pstmt.setString(2, playerName); pstmt.setLong(3, coolDown); pstmt.executeUpdate(); } }

dan1st•7mo ago

public void insertUser(long userID, String playerName, long coolDown) throws SQLException {
  String sql = "INSERT INTO Linked (userID, PlayerName, CoolDown) VALUES (?, ?, ?)";
  try (PreparedStatement pstmt = connection.prepareStatement(sql)) {
    pstmt.setLong(1, userID);
    pstmt.setString(2, playerName);
    pstmt.setLong(3, coolDown);
    pstmt.executeUpdate();
  }
}

public void insertUser(long userID, String playerName, long coolDown) throws SQLException {
  String sql = "INSERT INTO Linked (userID, PlayerName, CoolDown) VALUES (?, ?, ?)";
  try (PreparedStatement pstmt = connection.prepareStatement(sql)) {
    pstmt.setLong(1, userID);
    pstmt.setString(2, playerName);
    pstmt.setLong(3, coolDown);
    pstmt.executeUpdate();
  }
}

What about it?

Nothing•7mo ago

Insert

dan1st•7mo ago

what about it? If the userID is the primary key, you won't be able to insert rows with the same ID multiple times doing it the second time will give you an SQLException

Nothing•7mo ago

ya discord ID is not duplicated

dan1st•7mo ago

Then what's the question?

Nothing•7mo ago

I mean many users do and many insertusers will be sent I add index for playername and wal on

dan1st•7mo ago

if you do that twice with the same user ID, nothing from the second INSERT will be inserted If you want to ensure the player name is unique, you can use a UNIQUE constraint in the schema

Nothing•7mo ago

will not be possible with the same user

dan1st•7mo ago

Then what exactly is happening that you don't want to happen?

Nothing•7mo ago

As far as I know, you can only insert it once, not at the same time

dan1st•7mo ago

yes

Nothing•7mo ago

So What happens if 2 users do it at the same time?

dan1st•7mo ago

With the same user ID?

Nothing•7mo ago

No that different

dan1st•7mo ago

then it would insert both

Nothing•7mo ago

Will it not be ignored?? Shouldn't it only be inserted once in a period of time?

dan1st•7mo ago

Why? Why should it ignore anything?

Nothing•7mo ago

it will lock and insert will be rejected, maybe it will put it on the waiting list?

dan1st•7mo ago

Why would SQL lock anything? Normally databases want to process as many requests as possible

Nothing•7mo ago

I thought it would be locked when recording to secure the data

dan1st•7mo ago

the DB makes sure that there are no data corruptions so it might insert rows after each other if two requests are coming in at the same time or use other mechanisms to protect against that but it does allow you to insert multiple things at the same time

Nothing•7mo ago

I don't really understand what a .comic is, is it necessary?

dan1st•7mo ago

the DB makes sure that statements (or transactions) are either fully executed or not at all you mean atomic?

Nothing•7mo ago

That is Synchronize?

dan1st•7mo ago

Nothing•7mo ago

setAutoCommit connection.commit() Idk @@

dan1st•7mo ago

What do you want to know about committing?

Nothing•7mo ago

Yea

dan1st•7mo ago

I ask you "what do you want to know about" and you answer with "yes". How should I interpret that?

Nothing•7mo ago

i don't know how it works, new database with

dan1st•7mo ago

How what works? commits/transactions?

Nothing•7mo ago

Sqlite Idk that

dan1st•7mo ago

There are many things about sqlite

Nothing•7mo ago

What is this?

dan1st•7mo ago

Transactions?

Nothing•7mo ago

I don't understand why someone said I should have a commic List +for The database really has so much to learn

dan1st•7mo ago

Databases have a concept of transactions. A transaction is started, then you can use as many SQL statements as you want and then you can commit the transaction. The DB then makes sure that the SQL statements only apply when you commit it. If you don't commit it, these won't be executed. If you rollback a transaction, it will revert everything that happened in that transaction. This makes sure that if you do two things together, you don't end up in an in-between state where one part has been executed and the other part hasn't. There is no "a commic" but there is the concept of "atomicity" or "atomic" atomic means "It's either completely executed or not at all" - you won't get partial executions. This is what I just said about transactions. They are either committed or rolled back but not partially executed

Nothing•7mo ago

Is it like multi insert?

dan1st•7mo ago

Nothing•7mo ago

dan1st•7mo ago

If you include multiple INSERTs in a single transaction, then these will be atomic meaning that you won't end up in a state where some are executed and others aren't autocommit means that every statement is automatically committed/every statement is its own transaction

Nothing•7mo ago

In short, my previous insert needs to add anything, should I use asynchronous + wal?

dan1st•7mo ago

? I have no idea what you are talking about

Nothing•7mo ago

Sorry .

dan1st•7mo ago

I still have no idea what you mean with asynchronous + wal

Nothing•7mo ago

inherently synchronous is the default, but would asynchronous be better in terms of performance?

dan1st•7mo ago

Not necessarily

Nothing•7mo ago

The language gap left me quite helpless

dan1st•7mo ago

it depends Do you mean having to talk English?

Nothing•7mo ago

Depend? I mean I'm not good at English, I'm only good at Chinese and Vietnamese

dan1st•7mo ago

What would you like to be doing asynchronously?

Nothing•7mo ago

I don't know if using it will be better, I see it has the risk of losing data . Will this code not have problems if called at the same time?

dan1st•7mo ago

If it isn't using the same connection, no

Nothing•7mo ago

I'm afraid it's the same... So.... Do you have a good plan?

dan1st•7mo ago

For some DBs, Connections are thread safe/safe to use concurrently In other cases, you could use a connection pool

Nothing•7mo ago

other cases

other cases

some DB

some DB

it makes me very confused

dan1st•7mo ago

If the JDBC driver doesn't have thread-safe connections there are multiple different types of databases According to the SQLite docs, you shouldn't use the same Connection in a multithreaded environment https://www2.sqlite.org/cvstrac/wiki?p=MultiThreading

Nothing•7mo ago

But now what to do about the connection problem?

dan1st•7mo ago

you can create multiple Connections? and e.g. give one to each thread that does DB access

Nothing•7mo ago

Whqat

dan1st•7mo ago

or use a connection pool

JavaBotOP•7mo ago

💤 Post marked as dormant

This post has been inactive for over 300 minutes, thus, it has been archived. If your question was not answered yet, feel free to re-open this post or create a new one. In case your post is not getting any attention, you can try to use /help ping. Warning: abusing this will result in moderative actions taken against you.

Nothing•7mo ago

Queues will slow down, but it's not as important as data being guaranteed Is it okay if I do this?

dan1st•7mo ago

What kind of queue are you talking about? If you just want the DB operations to be executed after each other, you can use synchronize

Nothing•7mo ago

lock and unlock

dan1st•7mo ago

yeah you can do that

Nothing•7mo ago

So which functions are considered multi-threaded?

dan1st•7mo ago

or you just have one Connection for each important thread (but make sure you are closing these connections when necessary) ? synchronized is just locking

Nothing•7mo ago

meaning call the function and the function will be postponed until it completes then it will continue to the next request?

dan1st•7mo ago

yes

Nothing•7mo ago

Is it considered multi-threaded?

dan1st•7mo ago

JDA uses a single thread for its listeners

Nothing•7mo ago

and will it have to wait if there are 2 requests at the same time? synchronize is better ?

dan1st•7mo ago

not necessarily yes, that's what JDA does

Nothing•7mo ago

... Please answer these 3 small questions 1.Is lock different from synchronize in any important way? 2. "ensureOffThread(false);" What is this, is it necessary?

dan1st•7mo ago

java.util.concurrent.Lock gives you more control and there are different types of Locks idk where you got that from that isn't a JDK thing

Shruti•7mo ago

protected void ensureOffThread(boolean single) {
        if (!Bukkit.isPrimaryThread()) return;

        StackTraceElement[] elements = Thread.currentThread().getStackTrace();
        String apiUser = elements[3].toString();
        if (!nagged.add(apiUser)) return;

        if (apiUser.startsWith("github.scarsz.discordsrv")) {
            DiscordSRV.warning("Linked account data requested on main thread, please report this to DiscordSRV: " + apiUser);
            for (StackTraceElement element : elements) DiscordSRV.debug(Debug.ACCOUNT_LINKING, element.toString());
            return;
        }

        DiscordSRV.warning("API user " + apiUser + " requested linked account information on the main thread while MySQL is enabled in DiscordSRV's settings");
        if (single) {
            DiscordSRV.warning("Requesting data for offline players on the main thread will lead to an exception in the future, if being on the main thread is explicitly required use getDiscordIdBypassCache / getUuidBypassCache");
        } else {
            DiscordSRV.warning("Managing / Requesting bulk linked account data on the main thread will lead to an exception in the future");
        }
        DiscordSRV.debug(Debug.ACCOUNT_LINKING, "Full callstack:");
        for (StackTraceElement element : elements) DiscordSRV.debug(Debug.ACCOUNT_LINKING, element.toString());
    }

protected void ensureOffThread(boolean single) {
        if (!Bukkit.isPrimaryThread()) return;

        StackTraceElement[] elements = Thread.currentThread().getStackTrace();
        String apiUser = elements[3].toString();
        if (!nagged.add(apiUser)) return;

        if (apiUser.startsWith("github.scarsz.discordsrv")) {
            DiscordSRV.warning("Linked account data requested on main thread, please report this to DiscordSRV: " + apiUser);
            for (StackTraceElement element : elements) DiscordSRV.debug(Debug.ACCOUNT_LINKING, element.toString());
            return;
        }

        DiscordSRV.warning("API user " + apiUser + " requested linked account information on the main thread while MySQL is enabled in DiscordSRV's settings");
        if (single) {
            DiscordSRV.warning("Requesting data for offline players on the main thread will lead to an exception in the future, if being on the main thread is explicitly required use getDiscordIdBypassCache / getUuidBypassCache");
        } else {
            DiscordSRV.warning("Managing / Requesting bulk linked account data on the main thread will lead to an exception in the future");
        }
        DiscordSRV.debug(Debug.ACCOUNT_LINKING, "Full callstack:");
        for (StackTraceElement element : elements) DiscordSRV.debug(Debug.ACCOUNT_LINKING, element.toString());
    }

This message has been formatted automatically. You can disable this using /preferences.

Nothing•7mo ago

here How to avoid main thread?

dan1st•7mo ago

This seems to result in warnings when being used from outside the main thread

Nothing•7mo ago

maybe it's not necessary

dan1st•7mo ago

I think they just don't want to block the main thread which seems very necessary if you don't want an unresponsive application

Nothing•7mo ago

    private void executeTransaction(Runnable transaction) throws SQLException {
        synchronized (this) {
            connection.setAutoCommit(false);
            try {
                transaction.run();
                connection.commit();
            } catch (SQLException e) {
                handleSQLException(e);
                throw e;
            } finally {
                try {
                    connection.setAutoCommit(true);
                } catch (SQLException e) {
                    logger.severe(e.getMessage());
                }
            }
        }
    }

    private void executeTransaction(Runnable transaction) throws SQLException {
        synchronized (this) {
            connection.setAutoCommit(false);
            try {
                transaction.run();
                connection.commit();
            } catch (SQLException e) {
                handleSQLException(e);
                throw e;
            } finally {
                try {
                    connection.setAutoCommit(true);
                } catch (SQLException e) {
                    logger.severe(e.getMessage());
                }
            }
        }
    }

JavaBotOP•7mo ago

3.     private void executeTransaction(Runnable transaction) throws SQLException {
        synchronized (this) {
            connection.setAutoCommit(false);
            try {
                transaction.run();
                connection.commit();
            } catch (SQLException e) {
                handleSQLException(e);
                throw e;
            } finally {
                try {
                    connection.setAutoCommit(true);
                } catch (SQLException e) {
                    logger.severe(e.getMessage());
                }
            }
        }
    }

3.     private void executeTransaction(Runnable transaction) throws SQLException {
        synchronized (this) {
            connection.setAutoCommit(false);
            try {
                transaction.run();
                connection.commit();
            } catch (SQLException e) {
                handleSQLException(e);
                throw e;
            } finally {
                try {
                    connection.setAutoCommit(true);
                } catch (SQLException e) {
                    logger.severe(e.getMessage());
                }
            }
        }
    }

Nothing•7mo ago

Is it okay?

dan1st•7mo ago

It might be a good idea to also roll back in case of a RuntimeException and just rethrow it in that case

Nothing•7mo ago

Well, for example, there are people who are crazy about spam and force it to be filtered constantly. Do you have a good way to solve this problem?

JavaBotOP•7mo ago

If you are finished with your post, please close it. If you are not, please ignore this message. Note that you will not be able to send further messages here after this post have been closed but you will be able to create new posts.

dan1st•7mo ago

Nothing•7mo ago

spam get info user what, idont understand...

dan1st•7mo ago

So what's the problem with that

Nothing•7mo ago

1 user get username or cooldown

dan1st•7mo ago

If something bad happens in the Runnable, you might want to execute connection.rollback() Also in case of an SQLException

Nothing•7mo ago

what ?

dan1st•7mo ago

that Runnable here

Nothing•7mo ago

Can you get a sample? It sounds so confusing to me

dan1st•7mo ago

If it throws a RuntimeException, what should happen with the data?

Nothing•7mo ago

error? or dupe?

dan1st•7mo ago

yeah but should the DB still be changed?

Nothing•7mo ago

Do you mean calling it again to insert it into insert?

dan1st•7mo ago

If you run connection.rollback();, it makes sure it isn't executed

private void executeTransaction(Runnable transaction) throws SQLException {
    synchronized (this) {
        connection.setAutoCommit(false);
        try {
            try {
                transaction.run();
                connection.commit();
            } catch(RuntimeException | SQLException e) {
                connection.rollback();
                throw e;
            }
        } catch (SQLException e) {
            handleSQLException(e);
            throw e;
        } finally {
            try {
                connection.setAutoCommit(true);
            } catch (SQLException e) {
                logger.severe(e.getMessage());
            }
        }
    }
}

private void executeTransaction(Runnable transaction) throws SQLException {
    synchronized (this) {
        connection.setAutoCommit(false);
        try {
            try {
                transaction.run();
                connection.commit();
            } catch(RuntimeException | SQLException e) {
                connection.rollback();
                throw e;
            }
        } catch (SQLException e) {
            handleSQLException(e);
            throw e;
        } finally {
            try {
                connection.setAutoCommit(true);
            } catch (SQLException e) {
                logger.severe(e.getMessage());
            }
        }
    }
}

something like that

Nothing•7mo ago

oh ty Did I just put the callback in the wrong place???

dan1st•7mo ago

? whether you use synchronized or locks doesn't matter that much

Nothing•7mo ago

i mean rollback

dan1st•7mo ago

yeah you can do that as well

Nothing•7mo ago

what Yes, but things that might be okay should be left alone @@ If a person needs to get their info id and they spam requests continuously through commands or buttons, causing select to be called continuously, what should they do? Save it in cache?

dan1st•7mo ago

you can make a rate limit per user?

Nothing•7mo ago

Here's an example: What if it's a 1-player connection to the server? Minecraft

dan1st•7mo ago

I have no idea what you are talking about but I have no diea about Minecraft development

Nothing•7mo ago

Why u use "try" in" try"

dan1st•7mo ago

so that the connection can be rolled back in case of both a RuntimeException and SQLException

Nothing•7mo ago

private void executeTransaction(Runnable transaction) throws SQLException {
    synchronized (this) {
        connection.setAutoCommit(false);
        try {
            try {
                transaction.run();
                connection.commit();
            } catch(RuntimeException | SQLException e) {
                connection.rollback();
                throw e;
            }
        } catch (SQLException e) {
            handleSQLException(e);
            throw e;
        } finally {
            try {
                connection.setAutoCommit(true);
            } catch (SQLException e) {
                logger.severe(e.getMessage());
            }
        }
    }
}

private void executeTransaction(Runnable transaction) throws SQLException {
    synchronized (this) {
        connection.setAutoCommit(false);
        try {
            try {
                transaction.run();
                connection.commit();
            } catch(RuntimeException | SQLException e) {
                connection.rollback();
                throw e;
            }
        } catch (SQLException e) {
            handleSQLException(e);
            throw e;
        } finally {
            try {
                connection.setAutoCommit(true);
            } catch (SQLException e) {
                logger.severe(e.getMessage());
            }
        }
    }
}

If I delete the external catch, is there any problem?

dan1st•7mo ago

you'd need to move the handleSQLException(e)

Nothing•7mo ago

yae it only sends when there is an error Finally, I was able to leave the database dynamic, thank you so much

JavaBotOP•7mo ago

Post Closed

This post has been closed by <@1252543231233622110>.

Gaming

Programming

SQLITE

Did you find this page helpful?