Re: [h2] lots of schemes vs lot of tables?

Gmail - Josh Sat, 21 Sep 2013 12:54:28 -0700

Hi Thomas,

I keep getting emails from this thread, so I assumed it was the one I posted, I did not mean to hijack any thread. Just the topic is a matter of urgency as it is my grade 11 final project due next week friday and I cannot get the database working.

I have answered on the thread.

Thanks for your replies,

Josh

Sent from my BlackBerry 10 smartphone.

From: Thomas Mueller

Sent: Saturday 21 September 2013 9:50 PM

To: H2 Google Group

Reply To: [email protected]

Subject: Re: [h2] lots of schemes vs lot of tables?

Hi Josh,

Your question is not related to the subject of this thread, which is "lots of schemes vs lot of tables?". Please don't try to hijack other threads.

Also, it seems to me you have asked this question here: http://stackoverflow.com/questions/18924873/h2-embedded-database-loses-tables-data-on-deploymen - and you got questions and comments, but didn't answer any of them.

Regards,
Thomas

On Sat, Sep 21, 2013 at 9:39 PM, Gecko Inked <[email protected]> wrote:

Hi guys,

so I have only 1 table called table1 in the database, but when I transfer the h2.jar file over to another pc (exported as a file along with all the other .jars in the project) and then the table is gone on the new pc.

How do I get it to stay?

Thanks,
Josh

On Sat, Sep 21, 2013 at 9:34 PM, Pablo Beltran <[email protected]> wrote:

Hi,

schema and table metadata is kept fully in memory

I see the problem. For instance, how much memory would require (approximately) 10.000 schemes x 10 tables = 110.000 object medata? Would that magnitude order be of Megas or Gigas? Megas might be acceptable. Gigas, hmm...

Regards,
Pablo.

2013/9/21 Thomas Mueller <[email protected]>

Hi,

You are right of course. The problem I see is that you might end up with a lot of schemas and a lot of tables, if you try to keep all data in one database. For H2, schema and table metadata is kept fully in memory, I'm not sure about other database engines. And at some point you might want to keep some data that applies to all users.

Regards,
Thomas

On Sat, Sep 21, 2013 at 9:19 AM, Pablo Beltran <[email protected]> wrote:

Hi Thomas,

You are right, many schemas implies many tables.

What I like of the may schemas approach (one per user) is that I can partition the data very neatly and resolve the data isolation simply with the connection:

public Connection getConnection(String userId){
Connection conn = pool.getConnection();
conn.createStatement().execute("set schema USER_"+userId);

return conn;
}

The code above ensures that the user views only his tables (and data) by default.

I'm not expert and you know much better the database internals, how it scales and how to prevent future problems... I'll change my current model (many schemas) to a single schema.

Regards,
Pablo

2013/9/21 Thomas Mueller <[email protected]>

Hi

You could do both, but I would use one schema. (the title of the mail is incorrect: many schemas is many tables, which is what I would avoid).

Regards, Thomas

Am 12.09.2013 17:54 schrieb "Pablo Beltran" <[email protected]>:

Hi,

I need to store lot of data (even millions of records) on an small bunch of tables for many users (thousands). What would be would be the best approach?

1. Create one schema per user, so each user would have its own private table: select * from USER_N.TABLE_X

2. Create one table shared by all the users and add a new column to identify the data by user: select * from PUBLIC.TABLE_X where user=N;

Thanks!
Pablo.

--
You received this message because you are subscribed to the Google Groups "H2 Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [email protected].

To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/groups/opt_out.

--
You received this message because you are subscribed to a topic in the Google Groups "H2 Database" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/h2-database/jp_fVH9XqXU/unsubscribe.
To unsubscribe from this group and all its topics, send an email to [email protected].

To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/groups/opt_out.

--
You received this message because you are subscribed to the Google Groups "H2 Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/groups/opt_out.

--
You received this message because you are subscribed to a topic in the Google Groups "H2 Database" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/h2-database/jp_fVH9XqXU/unsubscribe.
To unsubscribe from this group and all its topics, send an email to [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/groups/opt_out.

--
You received this message because you are subscribed to the Google Groups "H2 Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/groups/opt_out.

--
You received this message because you are subscribed to the Google Groups "H2 Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/groups/opt_out.

--
You received this message because you are subscribed to the Google Groups "H2 Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/groups/opt_out.

Re: [h2] lots of schemes vs lot of tables?

Reply via email to