| View previous topic :: View next topic |
| Author |
Message |
RansomStark
Joined: 25 Feb 2005
Posts: 5
|
 Posted: Sat Feb 26, 2005 22:43 Post subject: COMMIT, Integer Ranges, and Rows/Character Questions |
 |
|
I have three questions after some time today playing with this:
1. SQLite COMMIT
I'm using the SQLite along with SQLiteCC to peek at my database. I scheduled a commit to happen in the onload script after 1 minute, and the function it calls schedules itself for another commit after 1 minute. In this way I think I have a periodic database commit call for the life of the module.
Fine.
But when I look at my database with SQLitecc, it reports that the gold I've deposited into the DB is what it should have been after a commit. As in, it sees the updated value in the field before the commit occurs. Is this an invalid test? Do I have to pull the power or perhaps kill the server task for a real test of whether or not the commit is necessary and working?
2. Integer ranges -> Database representations
In the asp implementation of the database, they chose to represent everything as a string. In NWN, an integer is 65k to -65K (i think). I'm trying to implement a simple banking system. How do I get around the small integer limit, and how is the asp implementation intended to work with large integers turned strings? I'm a little confused on how this is all supposed to work. A DB int is quite a bit larger than an NWN int, ... I'd like to use the DB ints, I think... not sure. Thoughts?
3. Mutliple rows for 1 Character
In the asp demo module, the implementation for the example table is setup so that for every variable a character has assigned to them, there is another row in the pwdata table. Is there a good reason for this? It strikes me that you would want to have one row for each character and have fields for each variable that might be assigned to them.
One reason I can think of is that it's easier for developers to add new variables to a character. But this comes at a huge cost of having the DB iterate over many many more rows in the database, not to mention returning a set instead of a row....
Thoughts? |
|
| Back to top |
|
 |
NoMercy
Joined: 03 Jan 2005
Posts: 123
Location: UK
|
| Posted: Sun Feb 27, 2005 0:26 Post subject: |
|
|
| After you COMMIT, you should also be issing another BEGIN to start a new transaction, if you don't youre essentially going back to write to disk after every SQL statement. |
|
| Back to top |
|
|
RansomStark
Joined: 25 Feb 2005
Posts: 5
|
| Posted: Sun Feb 27, 2005 0:47 Post subject: |
|
|
So... something like this?
| Code: |
void SQLCommit()
{
SQLExecDirect("COMMIT TRANSACTION");
SQLExecDirect("BEGIN TRANSACTION");
//Schedule next commit "interrupt"
DelayCommand(SQL_COMMIT_PERIOD, SQLCommit());
}
|
|
|
| Back to top |
|
|
NoMercy
Joined: 03 Jan 2005
Posts: 123
Location: UK
|
| Posted: Sun Feb 27, 2005 15:05 Post subject: |
|
|
You don't need the TRANSACTION part on the end of BEGIN and COMMIT, otherwise it looks fine.
If you want details the TRANSACTION sqlite ignores it (used for mutiple concurrent transactions, which sqlite doesn't do), and I'm not sure if MySQL likes it, since it's 'BEGIN' is a alias to START TRANSACTION, so probably good to stick with just 'BEGIN' and 'COMMIT' if you might want to change database at some point. |
|
| Back to top |
|
|
Papillon
x-man
Joined: 28 Dec 2004
Posts: 1060
Location: Germany
|
| Posted: Sat Mar 05, 2005 13:26 Post subject: Re: COMMIT, Integer Ranges, and Rows/Character Questions |
|
|
| RansomStark wrote: |
2. Integer ranges -> Database representations
In the asp implementation of the database, they chose to represent everything as a string. In NWN, an integer is 65k to -65K (i think). I'm trying to implement a simple banking system. How do I get around the small integer limit, and how is the asp implementation intended to work with large integers turned strings? I'm a little confused on how this is all supposed to work. A DB int is quite a bit larger than an NWN int, ... I'd like to use the DB ints, I think... not sure. Thoughts?
|
What do you mean with "asp implementation" ? An integer in NWN is far larger than an integer in C, btw, so there is no 65K limit. Check out the NWN lexicon on how big an integer can actually be.
| Quote: |
3. Mutliple rows for 1 Character
In the asp demo module, the implementation for the example table is setup so that for every variable a character has assigned to them, there is another row in the pwdata table. Is there a good reason for this? It strikes me that you would want to have one row for each character and have fields for each variable that might be assigned to them.
One reason I can think of is that it's easier for developers to add new variables to a character. But this comes at a huge cost of having the DB iterate over many many more rows in the database, not to mention returning a set instead of a row....
|
You could store everything in a single row, but I would not recommend it. The main reason is that you will need only 2-3 variables at a time, and not the whole data set of the player in a given script. Second, the cost of iterating over a set is smaller than selecting columns individually from a single row.
_________________
Papillon |
|
| Back to top |
|
|
RansomStark
Joined: 25 Feb 2005
Posts: 5
|
| Posted: Mon Mar 07, 2005 19:52 Post subject: Re: COMMIT, Integer Ranges, and Rows/Character Questions |
|
|
| Papillon wrote: | | RansomStark wrote: |
2. Integer ranges -> Database representations
In the asp implementation of the database, they chose to represent everything as a string. In NWN, an integer is 65k to -65K (i think). I'm trying to implement a simple banking system. How do I get around the small integer limit, and how is the asp implementation intended to work with large integers turned strings? I'm a little confused on how this is all supposed to work. A DB int is quite a bit larger than an NWN int, ... I'd like to use the DB ints, I think... not sure. Thoughts?
|
What do you mean with "asp implementation" ? An integer in NWN is far larger than an integer in C, btw, so there is no 65K limit. Check out the NWN lexicon on how big an integer can actually be.
|
I meant the asp_demo_module and asp_include implementations of the DB store and DB read functions that come with the plugin. It converts everything to strings I think before it stores them in the database, as strings. Just wondered why.
-edit-: Oh maybe it's aps, not asp, I'm at work and can't double-check
And as for Int sizes, heh, I didn't check the lexicon myself, I just asked a friend and apparently he gave me bad information.
| Papillon wrote: | | RansomStark wrote: |
3. Mutliple rows for 1 Character
In the asp demo module, the implementation for the example table is setup so that for every variable a character has assigned to them, there is another row in the pwdata table. Is there a good reason for this? It strikes me that you would want to have one row for each character and have fields for each variable that might be assigned to them.
One reason I can think of is that it's easier for developers to add new variables to a character. But this comes at a huge cost of having the DB iterate over many many more rows in the database, not to mention returning a set instead of a row....
|
You could store everything in a single row, but I would not recommend it. The main reason is that you will need only 2-3 variables at a time, and not the whole data set of the player in a given script. Second, the cost of iterating over a set is smaller than selecting columns individually from a single row. |
Fair enough, it's been a while since I designed a DB, so thanks for the help. |
|
| Back to top |
|
|
|
|
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
|
Powered by phpBB © 2001, 2005 phpBB Group
|
FAQ
Search
Memberlist
Usergroups
Register
Profile
Log in to check your private messages
Log in
