Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

Java DB rotation

18 views
Skip to first unread message

Jim Lee

unread,
Jan 30, 2012, 9:08:04 PM1/30/12
to
I have a Java server controller that read/write to Database table

Java server will start read / write to a new DB table every
week/monday
e.g.
table-1-2-2012
table-1-9-2012
table-1-16-2012
table-1-23-2012 ... etc

I think of 2 ways to do the DB table rotation

1) check the server timestamp, if today's date is week of 1-23-2012,
then read/write to table-1-23-

2012

2) have a unix corn job run every monday to generate a text file on
Java server with DB table named on

that date - on each Java request, check the text file's table name -
then read/write to that DB table

any other solution to DB table rotation?

the first way check timestamp have a drawback when server's time is
not set to correct time, since

there are many Java server running for load balance, it's not a good
idea.

the second way is a better solution, but request additional setup -
cron job

Arne Vajhøj

unread,
Jan 30, 2012, 9:12:14 PM1/30/12
to
I can not see any any problems with construction the table
name every time you need it.

That string formatting will be insignificant compared to
the actual database operation.

And it will be far more expensive to read from a file.

So definitely #1.

Arne

Arne Vajhøj

unread,
Jan 30, 2012, 9:14:21 PM1/30/12
to
But also consider if you really want to switch table like
that.

Why not just use the same table all the time with a column
for the period?

For most cases that would be the best solution. Exceptions
do exist.

Arne

Jim Lee

unread,
Jan 30, 2012, 9:17:05 PM1/30/12
to
On Mon, 30 Jan 2012 21:12:14 -0500, Arne Vajh?j <ar...@vajhoej.dk>
wrote:
to construct the table name, i need to use the server timestamp, what
if the server time is incorrect? then everything is messed up? On
linux, is the server time always in sync with internet time server? or
it's depended on the motherboard BIOS time?

Arne Vajhøj

unread,
Jan 30, 2012, 9:20:14 PM1/30/12
to
If you can not get the time correctly to construct a tablename
then you can not get the time correctly to write to the file.

You can setup NTP to synch time.

But does it matter if one server is 2 seconds off?

Arne


Martin Gregorie

unread,
Jan 30, 2012, 9:21:11 PM1/30/12
to
On Mon, 30 Jan 2012 18:08:04 -0800, Jim Lee wrote:

> I have a Java server controller that read/write to Database table
>
> Java server will start read / write to a new DB table every week/monday
> e.g.
> table-1-2-2012 table-1-9-2012 table-1-16-2012 table-1-23-2012 ... etc
>
What problem are you using table rotation to solve?

What would prevent you from using a single table containing datestamped
rows which are archived and/or deleted the rows after "cycle length" days?


--
martin@ | Martin Gregorie
gregorie. | Essex, UK
org |

Jim Lee

unread,
Jan 30, 2012, 9:20:16 PM1/30/12
to
On Mon, 30 Jan 2012 21:14:21 -0500, Arne Vajh?j <ar...@vajhoej.dk>
wrote:
I must use a new table

Jim Lee

unread,
Jan 30, 2012, 9:23:02 PM1/30/12
to
1-2 sec off is not matter


On Mon, 30 Jan 2012 21:20:14 -0500, Arne Vajh?j <ar...@vajhoej.dk>

Jim Lee

unread,
Jan 30, 2012, 9:24:57 PM1/30/12
to
On Tue, 31 Jan 2012 02:21:11 +0000 (UTC), Martin Gregorie
<mar...@address-in-sig.invalid> wrote:

>On Mon, 30 Jan 2012 18:08:04 -0800, Jim Lee wrote:
>
>> I have a Java server controller that read/write to Database table
>>
>> Java server will start read / write to a new DB table every week/monday
>> e.g.
>> table-1-2-2012 table-1-9-2012 table-1-16-2012 table-1-23-2012 ... etc
>>
>What problem are you using table rotation to solve?
>
>What would prevent you from using a single table containing datestamped
>rows which are archived and/or deleted the rows after "cycle length" days?

my main problem is how to make sure "how to get the correct table name
to read/write to" depending what day in the week

start a new DB table is a must since it's going through some other
REST backend layer

Arne Vajhøj

unread,
Jan 30, 2012, 9:31:28 PM1/30/12
to
Synching time today is not difficult.

Arne

Arne Vajhøj

unread,
Jan 30, 2012, 9:32:48 PM1/30/12
to
There is nothing in REST that requires such a table structure.

And it would be better to fix the bad code requiring such
a table rollover than to make other apps bad to work with it.

Arne

Arne Vajhøj

unread,
Jan 30, 2012, 9:34:18 PM1/30/12
to
> I must use a new table

New tables does not solve a business problem.

It is something invented inside the IT org.

It can be changed if there is a desire to do so.

Arne

Arne Vajhøj

unread,
Jan 30, 2012, 9:35:08 PM1/30/12
to
On 1/30/2012 9:08 PM, Jim Lee wrote:
> I have a Java server controller that read/write to Database table
>
> Java server will start read / write to a new DB table every
> week/monday
> e.g.
> table-1-2-2012
> table-1-9-2012
> table-1-16-2012
> table-1-23-2012 ... etc

BTW, drop the hyphen in table names it will cause
problems.

Arne

Lew

unread,
Jan 31, 2012, 1:54:54 AM1/31/12
to
and use YYYYMMDD format for sorting purposes if you must be so intransigent on
the repeated, excellent advice to abandon the approach in favor of a proper
table design.

--
Lew
Honi soit qui mal y pense.
http://upload.wikimedia.org/wikipedia/commons/c/cf/Friz.jpg

Robert Klemme

unread,
Jan 31, 2012, 2:36:03 AM1/31/12
to
On 31.01.2012 03:20, Jim Lee wrote:
> On Mon, 30 Jan 2012 21:14:21 -0500, Arne Vajh?j<ar...@vajhoej.dk>

>> But also consider if you really want to switch table like
>> that.
>>
>> Why not just use the same table all the time with a column
>> for the period?
>>
>> For most cases that would be the best solution. Exceptions
>> do exist.

+1

> I must use a new table

Why?

robert


--
remember.guy do |as, often| as.you_can - without end
http://blog.rubybestpractices.com/

Robert Klemme

unread,
Jan 31, 2012, 2:38:42 AM1/31/12
to
Another question: Jim, what database are you using? If the instance
requiring multiple tables is afraid of volume the typical solution to
this issue is called "partitioning". If your database supports it,
that's typically the way to go for such kind of data.

Cheers

Chris Riesbeck

unread,
Jan 31, 2012, 1:57:44 PM1/31/12
to
Pretty much every response from the OP has suggested either really bad
intra-team communication (distributed team?), or a system architect
angling for an appearance on the Daily WTF.




Chris Riesbeck

unread,
Jan 31, 2012, 2:03:11 PM1/31/12
to
On 1/31/2012 12:57 PM, Chris Riesbeck wrote:
> On 1/31/2012 1:38 AM, Robert Klemme wrote:
>> On 31.01.2012 03:32, Arne Vajhøj wrote:
>>> On 1/30/2012 9:24 PM, Jim Lee wrote:
>>>> On Tue, 31 Jan 2012 02:21:11 +0000 (UTC), Martin Gregorie
>>>> <mar...@address-in-sig.invalid> wrote:
>>>>> On Mon, 30 Jan 2012 18:08:04 -0800, Jim Lee wrote:
>>>>>> I have a Java server controller that read/write to Database table
>>>>>>
> Pretty much every response from the OP has suggested either really bad
> intra-team communication (distributed team?), or a system architect
> angling for an appearance on the Daily WTF.
>

I change my mind. The same poster made the same query on comp.lang.php
but said

> I have a PHP server controller thatl read/write to Database table
>
> PHP server will start read / write to a new DB table every week/monday

I now hypothesize trolling.

Arne Vajhøj

unread,
Jan 31, 2012, 8:30:25 PM1/31/12
to
Yep.

But if one has a database of a size that requires partitioning, then
one better have both app and database specialists inhouse.

Arne

Arne Vajhøj

unread,
Jan 31, 2012, 8:31:37 PM1/31/12
to
In both cases it would make sense to push back a little
bit.

(within the constraints given by the org chart)

Arne

Arne Vajhøj

unread,
Jan 31, 2012, 8:32:45 PM1/31/12
to
It could be trolling.

Or he just want both a Java and a PHP view on things.

Out of curiosity: did the PHP folks answer similar to us?

Arne

Chris Riesbeck

unread,
Feb 1, 2012, 2:49:57 PM2/1/12
to
The 1st responder asked "why not add a timestamp column?" and when the
OP said "because of REST," another responder said that wasn't a reason.
Other responses just tried to solve the problem. So similar but less
push-back than over here.

Arne Vajhøj

unread,
Feb 1, 2012, 7:23:06 PM2/1/12
to
The PHP group probably have a larger fraction of "hobby programmers"
than the Java group.

Arne


George Neuner

unread,
Feb 2, 2012, 2:05:31 PM2/2/12
to
On Mon, 30 Jan 2012 18:08:04 -0800, Jim Lee <jimle...@yahoo.com>
wrote:

>Java server will start read / write to a new DB table every
>week/monday
>e.g.
>table-1-2-2012
>table-1-9-2012
>table-1-16-2012
>table-1-23-2012 ... etc
>
>I think of 2 ways to do the DB table rotation
>
>1) check the server timestamp, if today's date is week of 1-23-2012,
>then read/write to table-1-23-2012
>
>2) have a unix cron job run every monday to generate a text file on
>Java server with DB table named on that date - on each Java request,
>check the text file's table name - then read/write to that DB table
>
>any other solution to DB table rotation?

Others have already harped on the poor design ... so I'll just address
the question as asked.

1) You don't say what DBMS, but most workgroup and enterprise level
implementations include a built in task scheduler. You can create a
internal task that runs at a specific time to create new tables.

2) Since you are load balancing, you are (or should!) be using
replication to keep the database instances synchronized. If you are
doing online (hot) replication, then creating a new table on one
server will replicate it on the other servers within a minute or so.
If, OTOH, replication is periodic, you can kick start it after
creating the new table.

3) You can designate one server as a master and have it remotely
create tables on the other servers. This also can be done with a DBMS
task. Of course, this introduces failover issues, i.e. what to do if
the master DBMS instance is down.


Clients do not need to know about or participate in this table
rotation scheme at all. They can use the same table all the time.
Periodically the contents of the table can be copied into a dated
archive table and then the current use table can be emptied.

This is a simple 2 step transaction:

SELECT INTO <archival table> * FROM <current table>;
DELETE FROM <current table>;

George

Rajiv Gupta

unread,
Feb 7, 2012, 10:08:06 AM2/7/12
to
On 2012-01-31 13:08:04 +1100, Jim Lee said:

> I have a Java server controller that read/write to Database table
>
> Java server will start read / write to a new DB table every
> week/monday
> e.g.
> table-1-2-2012
> table-1-9-2012
> table-1-16-2012
> table-1-23-2012 ... etc
>
> I think of 2 ways to do the DB table rotation
>
> 1) check the server timestamp, if today's date is week of 1-23-2012,
> then read/write to table-1-23-
>
> 2012

Sir, that sounds really really fucked up. Why not just one table?
Tables can store huge amounts of data efficiently.

0 new messages