Menu
▾
▴
phpwebsite-developers
|
From: Don S. <do...@se...> - 2002-10-18 17:40:04
|
In the install.sql for core and some modules (gallery I've seen), you have create table statements that use "auto_increment" to define a field as sequential. Isn't auto_increment a mysql function and not standard across databases? I don't see it in postgres and I'm pretty sure it won't fly in Oracle. I want to use PEAR for database abstraction but using mysql-specific syntax kind of defeats the purpose. Am I mistaken? Don. |
|
From: Matthew M. <ma...@tu...> - 2002-10-18 20:24:12
|
We are aware of it. We have some ideas to overcome it that we hope to implement by the final release. Matt > In the install.sql for core and some modules (gallery I've seen), you > have create table statements that use "auto_increment" to define a > field as sequential. > > Isn't auto_increment a mysql function and not standard across databases? > I don't see it in postgres and I'm pretty sure it won't fly in Oracle. > > I want to use PEAR for database abstraction but using mysql-specific > syntax kind of defeats the purpose. > > Am I mistaken? > > Don. > > > > ------------------------------------------------------- > This sf.net email is sponsored by:ThinkGeek > Welcome to geek heaven. > http://thinkgeek.com/sf > _______________________________________________ > Phpwebsite-developers mailing list > Php...@li... > https://lists.sourceforge.net/lists/listinfo/phpwebsite-developers Matthew McNaney Internet Systems Architect Electronic Student Services Email: ma...@tu... URL: http://phpwebsite.appstate.edu Phone: 828-262-6493 ICQ: 141057403 |
|
From: Don S. <do...@se...> - 2002-10-18 20:41:39
|
It looks like the PEARy way of doing things is to use PEAR Sequences: http://pear.php.net/manual/en/core.db.tut_sequences.php I got this info from the PEAR mailing list when I asked about them: "When you first use a sequence PEAR::DB creates it in the DB, so you don't *have* to create it beforehand, you can though, in which case it has to be of the form 'sequencename_seq' without the quotes and if you left the default values. You can change the format of the string used to create the sequence to match your own preferences. If you create it you must not set it to autoincrement but better to INT unsigned." Has PEAR sequences been looked at before? Just curious, Don. On Fri, 18 Oct 2002, Don Seiler wrote: > In the install.sql for core and some modules (gallery I've seen), you have > create table statements that use "auto_increment" to define a field as > sequential. > > Isn't auto_increment a mysql function and not standard across databases? > I don't see it in postgres and I'm pretty sure it won't fly in Oracle. > > I want to use PEAR for database abstraction but using mysql-specific > syntax kind of defeats the purpose. > > Am I mistaken? > > Don. > > > > ------------------------------------------------------- > This sf.net email is sponsored by:ThinkGeek > Welcome to geek heaven. > http://thinkgeek.com/sf > _______________________________________________ > Phpwebsite-developers mailing list > Php...@li... > https://lists.sourceforge.net/lists/listinfo/phpwebsite-developers > |
|
From: Matthew M. <ma...@tu...> - 2002-10-19 16:57:44
|
Funny you should mention this Don :)
I was driving home from getting lunch and I was thinking about your letter
from yesterday. I thought, "Well we already have a parameter that will
return the max value of a column if you request it. Why not just write
that amount to the column?"
So I was ready to work on it and I read your followup. Well, I looked
sequences up on the PEAR site but I couldn't find HOW to define a sequence
before you call nextId.
So after trying unsuccessfully for make that work, I decided to use my
plan, which I believe is how they are doing it anyway :)
Anyway, here is how it works:
function sqlInsert ($db_array, $table_name, $check_dup=FALSE,
$maxColumn=NULL, $autoIncrement=FALSE, $show_sql=FALSE)
I have tested this on creating a user and it worked:
$user_id = $GLOBALS["core"]->sqlInsert($sql_array, "mod_users", 1,
"user_id", 1);
The $sql_array has the variable to put in the database. mod_users is the
table name. The next parameter (1) instructs the function not to insert a
repeat. The "user_id" is the name of the column I want to return the max
amount of. Finally the last parameter is a boolean saying to write the max
amount to the $maxColumn ("user_id").
So, once everyone reviews this procedure and if it gets voted up, then we
can start converting our sqlInserts.
Consider this however. MySQL auto_increment remembers the last number added.
For example, if we add 1, 2, and 3 and then 3 is deleted, auto_increment
will add 4. Our new functionality will add 3 instead (it doesn't keep
track of that stuff). Possible problem? Only if a module developer did not
remove all linked material. In other words, a module could have 3 as a
linked id because it did not know it was deleted. So it would pull 3's
data. Not a huge problem, but one we would have to be wary of while
programming.
Thanks for lighting the fire under me Don :)
Sincerely,
Matt
> It looks like the PEARy way of doing things is to use PEAR Sequences:
> http://pear.php.net/manual/en/core.db.tut_sequences.php
>
> I got this info from the PEAR mailing list when I asked about them:
>
> "When you first use a sequence PEAR::DB creates it in the DB, so you
> don't *have* to create it beforehand, you can though, in which case it
> has to be of the form 'sequencename_seq' without the quotes and if you
> left the default values. You can change the format of the string used to
> create the sequence to match your own preferences.
>
> If you create it you must not set it to autoincrement but better to INT
> unsigned."
>
> Has PEAR sequences been looked at before?
>
> Just curious,
> Don.
>
> On Fri, 18 Oct 2002, Don Seiler wrote:
>
>> In the install.sql for core and some modules (gallery I've seen), you
>> have create table statements that use "auto_increment" to define a
>> field as sequential.
>>
>> Isn't auto_increment a mysql function and not standard across
>> databases? I don't see it in postgres and I'm pretty sure it won't
>> fly in Oracle.
>>
>> I want to use PEAR for database abstraction but using mysql-specific
>> syntax kind of defeats the purpose.
>>
>> Am I mistaken?
>>
>> Don.
>>
>>
>>
>> -------------------------------------------------------
>> This sf.net email is sponsored by:ThinkGeek
>> Welcome to geek heaven.
>> http://thinkgeek.com/sf
>> _______________________________________________
>> Phpwebsite-developers mailing list
>> Php...@li...
>> https://lists.sourceforge.net/lists/listinfo/phpwebsite-developers
>>
>
>
>
>
> -------------------------------------------------------
> This sf.net email is sponsored by:ThinkGeek
> Welcome to geek heaven.
> http://thinkgeek.com/sf
> _______________________________________________
> Phpwebsite-developers mailing list
> Php...@li...
> https://lists.sourceforge.net/lists/listinfo/phpwebsite-developers
Matthew McNaney
Internet Systems Architect
Electronic Student Services
Email: ma...@tu...
URL: http://phpwebsite.appstate.edu
Phone: 828-262-6493
ICQ: 141057403
|
|
From: Don S. <do...@se...> - 2002-10-19 17:12:03
|
According to what was told to me, the first time you call nextId, it will
create the sequence if it isn't created. This didn't work for you?
Don.
On Sat, 19 Oct 2002, Matthew McNaney wrote:
> Funny you should mention this Don :)
>
> I was driving home from getting lunch and I was thinking about your letter
> from yesterday. I thought, "Well we already have a parameter that will
> return the max value of a column if you request it. Why not just write
> that amount to the column?"
>
> So I was ready to work on it and I read your followup. Well, I looked
> sequences up on the PEAR site but I couldn't find HOW to define a sequence
> before you call nextId.
>
> So after trying unsuccessfully for make that work, I decided to use my
> plan, which I believe is how they are doing it anyway :)
>
> Anyway, here is how it works:
>
> function sqlInsert ($db_array, $table_name, $check_dup=FALSE,
> $maxColumn=NULL, $autoIncrement=FALSE, $show_sql=FALSE)
>
> I have tested this on creating a user and it worked:
> $user_id = $GLOBALS["core"]->sqlInsert($sql_array, "mod_users", 1,
> "user_id", 1);
>
> The $sql_array has the variable to put in the database. mod_users is the
> table name. The next parameter (1) instructs the function not to insert a
> repeat. The "user_id" is the name of the column I want to return the max
> amount of. Finally the last parameter is a boolean saying to write the max
> amount to the $maxColumn ("user_id").
>
> So, once everyone reviews this procedure and if it gets voted up, then we
> can start converting our sqlInserts.
>
> Consider this however. MySQL auto_increment remembers the last number added.
>
> For example, if we add 1, 2, and 3 and then 3 is deleted, auto_increment
> will add 4. Our new functionality will add 3 instead (it doesn't keep
> track of that stuff). Possible problem? Only if a module developer did not
> remove all linked material. In other words, a module could have 3 as a
> linked id because it did not know it was deleted. So it would pull 3's
> data. Not a huge problem, but one we would have to be wary of while
> programming.
>
> Thanks for lighting the fire under me Don :)
>
> Sincerely,
> Matt
>
> > It looks like the PEARy way of doing things is to use PEAR Sequences:
> > http://pear.php.net/manual/en/core.db.tut_sequences.php
> >
> > I got this info from the PEAR mailing list when I asked about them:
> >
> > "When you first use a sequence PEAR::DB creates it in the DB, so you
> > don't *have* to create it beforehand, you can though, in which case it
> > has to be of the form 'sequencename_seq' without the quotes and if you
> > left the default values. You can change the format of the string used to
> > create the sequence to match your own preferences.
> >
> > If you create it you must not set it to autoincrement but better to INT
> > unsigned."
> >
> > Has PEAR sequences been looked at before?
> >
> > Just curious,
> > Don.
> >
> > On Fri, 18 Oct 2002, Don Seiler wrote:
> >
> >> In the install.sql for core and some modules (gallery I've seen), you
> >> have create table statements that use "auto_increment" to define a
> >> field as sequential.
> >>
> >> Isn't auto_increment a mysql function and not standard across
> >> databases? I don't see it in postgres and I'm pretty sure it won't
> >> fly in Oracle.
> >>
> >> I want to use PEAR for database abstraction but using mysql-specific
> >> syntax kind of defeats the purpose.
> >>
> >> Am I mistaken?
> >>
> >> Don.
> >>
> >>
> >>
> >> -------------------------------------------------------
> >> This sf.net email is sponsored by:ThinkGeek
> >> Welcome to geek heaven.
> >> http://thinkgeek.com/sf
> >> _______________________________________________
> >> Phpwebsite-developers mailing list
> >> Php...@li...
> >> https://lists.sourceforge.net/lists/listinfo/phpwebsite-developers
> >>
> >
> >
> >
> >
> > -------------------------------------------------------
> > This sf.net email is sponsored by:ThinkGeek
> > Welcome to geek heaven.
> > http://thinkgeek.com/sf
> > _______________________________________________
> > Phpwebsite-developers mailing list
> > Php...@li...
> > https://lists.sourceforge.net/lists/listinfo/phpwebsite-developers
>
>
> Matthew McNaney
> Internet Systems Architect
> Electronic Student Services
> Email: ma...@tu...
> URL: http://phpwebsite.appstate.edu
> Phone: 828-262-6493
> ICQ: 141057403
>
>
>
>
> -------------------------------------------------------
> This sf.net email is sponsored by:
> Access Your PC Securely with GoToMyPC. Try Free Now
> https://www.gotomypc.com/s/OSND/DD
> _______________________________________________
> Phpwebsite-developers mailing list
> Php...@li...
> https://lists.sourceforge.net/lists/listinfo/phpwebsite-developers
>
|
|
From: Don S. <do...@se...> - 2002-10-19 17:33:55
|
The way I'm reading this. You plan to derive your user id by selecting
the current max id and incrementing it one? If this is the case, wouldn't
you have a problem if multiple users happend to submit new user requests
simultaneously? Yes I know the chances are slim but it's a very real
problem. Also the fact that IDs would be re-used is a problem I think.
I think a true database sequence object is needed. I'll try and play
around with what was told to me from the pear mailing list.
Don.
On Sat, 19 Oct 2002, Matthew McNaney wrote:
> Funny you should mention this Don :)
>
> I was driving home from getting lunch and I was thinking about your letter
> from yesterday. I thought, "Well we already have a parameter that will
> return the max value of a column if you request it. Why not just write
> that amount to the column?"
>
> So I was ready to work on it and I read your followup. Well, I looked
> sequences up on the PEAR site but I couldn't find HOW to define a sequence
> before you call nextId.
>
> So after trying unsuccessfully for make that work, I decided to use my
> plan, which I believe is how they are doing it anyway :)
>
> Anyway, here is how it works:
>
> function sqlInsert ($db_array, $table_name, $check_dup=FALSE,
> $maxColumn=NULL, $autoIncrement=FALSE, $show_sql=FALSE)
>
> I have tested this on creating a user and it worked:
> $user_id = $GLOBALS["core"]->sqlInsert($sql_array, "mod_users", 1,
> "user_id", 1);
>
> The $sql_array has the variable to put in the database. mod_users is the
> table name. The next parameter (1) instructs the function not to insert a
> repeat. The "user_id" is the name of the column I want to return the max
> amount of. Finally the last parameter is a boolean saying to write the max
> amount to the $maxColumn ("user_id").
>
> So, once everyone reviews this procedure and if it gets voted up, then we
> can start converting our sqlInserts.
>
> Consider this however. MySQL auto_increment remembers the last number added.
>
> For example, if we add 1, 2, and 3 and then 3 is deleted, auto_increment
> will add 4. Our new functionality will add 3 instead (it doesn't keep
> track of that stuff). Possible problem? Only if a module developer did not
> remove all linked material. In other words, a module could have 3 as a
> linked id because it did not know it was deleted. So it would pull 3's
> data. Not a huge problem, but one we would have to be wary of while
> programming.
>
> Thanks for lighting the fire under me Don :)
>
> Sincerely,
> Matt
>
> > It looks like the PEARy way of doing things is to use PEAR Sequences:
> > http://pear.php.net/manual/en/core.db.tut_sequences.php
> >
> > I got this info from the PEAR mailing list when I asked about them:
> >
> > "When you first use a sequence PEAR::DB creates it in the DB, so you
> > don't *have* to create it beforehand, you can though, in which case it
> > has to be of the form 'sequencename_seq' without the quotes and if you
> > left the default values. You can change the format of the string used to
> > create the sequence to match your own preferences.
> >
> > If you create it you must not set it to autoincrement but better to INT
> > unsigned."
> >
> > Has PEAR sequences been looked at before?
> >
> > Just curious,
> > Don.
> >
> > On Fri, 18 Oct 2002, Don Seiler wrote:
> >
> >> In the install.sql for core and some modules (gallery I've seen), you
> >> have create table statements that use "auto_increment" to define a
> >> field as sequential.
> >>
> >> Isn't auto_increment a mysql function and not standard across
> >> databases? I don't see it in postgres and I'm pretty sure it won't
> >> fly in Oracle.
> >>
> >> I want to use PEAR for database abstraction but using mysql-specific
> >> syntax kind of defeats the purpose.
> >>
> >> Am I mistaken?
> >>
> >> Don.
> >>
> >>
> >>
> >> -------------------------------------------------------
> >> This sf.net email is sponsored by:ThinkGeek
> >> Welcome to geek heaven.
> >> http://thinkgeek.com/sf
> >> _______________________________________________
> >> Phpwebsite-developers mailing list
> >> Php...@li...
> >> https://lists.sourceforge.net/lists/listinfo/phpwebsite-developers
> >>
> >
> >
> >
> >
> > -------------------------------------------------------
> > This sf.net email is sponsored by:ThinkGeek
> > Welcome to geek heaven.
> > http://thinkgeek.com/sf
> > _______________________________________________
> > Phpwebsite-developers mailing list
> > Php...@li...
> > https://lists.sourceforge.net/lists/listinfo/phpwebsite-developers
>
>
> Matthew McNaney
> Internet Systems Architect
> Electronic Student Services
> Email: ma...@tu...
> URL: http://phpwebsite.appstate.edu
> Phone: 828-262-6493
> ICQ: 141057403
>
>
>
>
> -------------------------------------------------------
> This sf.net email is sponsored by:
> Access Your PC Securely with GoToMyPC. Try Free Now
> https://www.gotomypc.com/s/OSND/DD
> _______________________________________________
> Phpwebsite-developers mailing list
> Php...@li...
> https://lists.sourceforge.net/lists/listinfo/phpwebsite-developers
>
|
|
From: Matthew M. <ma...@tu...> - 2002-10-19 20:21:38
|
> The way I'm reading this. You plan to derive your user id by selecting
> the current max id and incrementing it one? If this is the case,
> wouldn't you have a problem if multiple users happend to submit new
> user requests simultaneously?
Not a problem. In fact I'm sure the PEAR solution requires the same process:
you just lock the table.
$core->sqlLock("myTable");
$core->sqlInsert($sql_array, "myTable", 1, "id_column", 1);
$core->sqlUnlock();
Sorry, forgot mention it.
Matt
|
|
From: Don S. <do...@se...> - 2002-10-21 13:17:12
|
I still don't understand why you aren't just getting the new ID value via
the $db->nextId() function via PEAR sequences.
I've tested this on my box and it worked great. I made sure to test it
after stopping and starting mysql and apache and the sequence held true
throughout.
Using nextId would eliminate the need for table locking and any
possibility of the evils of ID re-use.
Don.
On Sat, 19 Oct 2002, Matthew McNaney wrote:
> > The way I'm reading this. You plan to derive your user id by selecting
> > the current max id and incrementing it one? If this is the case,
> > wouldn't you have a problem if multiple users happend to submit new
> > user requests simultaneously?
>
> Not a problem. In fact I'm sure the PEAR solution requires the same process:
> you just lock the table.
>
> $core->sqlLock("myTable");
> $core->sqlInsert($sql_array, "myTable", 1, "id_column", 1);
> $core->sqlUnlock();
>
> Sorry, forgot mention it.
>
> Matt
>
>
>
>
> -------------------------------------------------------
> This sf.net email is sponsored by:
> Access Your PC Securely with GoToMyPC. Try Free Now
> https://www.gotomypc.com/s/OSND/DD
> _______________________________________________
> Phpwebsite-developers mailing list
> Php...@li...
> https://lists.sourceforge.net/lists/listinfo/phpwebsite-developers
>
|
|
From: Matthew M. <ma...@tu...> - 2002-10-21 13:23:54
|
> I still don't understand why you aren't just getting the new ID value > via the $db->nextId() function via PEAR sequences. I couldn't get it to work. I will try again today and implement it if successful. Post an example if you get a chance. Thanks, Matt Matthew McNaney Internet Systems Architect Electronic Student Services Email: ma...@tu... URL: http://phpwebsite.appstate.edu Phone: 828-262-6493 ICQ: 141057403 |
|
From: Don S. <do...@se...> - 2002-10-21 13:29:44
|
Here is my example test.php. I created a mysql db 'test' with user/pass
of 'test'. Each time I reload the page the id is incremented one. If I
change the sequence name it starts over, and then when I change it back it
picks up where it left off, as expected.
Basically you just need to have a standard naming for your sequences,
since the names are really only mentioned in php as you don't create the
sequences until they are first needed.
Don.
<?
require_once "DB.php";
?>
<html><body>
<?
$user = "test";
$pass = "test";
$host = "localhost";
$db_name = "test";
$db_type = "mysql";
$dsn = "$db_type://$user:$pass@$host/$db_name";
echo "dsn = $dsn<br />";
$db = DB::connect($dsn, true);
if (DB::isError($db)) {
die ("error: " . $db->getMessage());
} else {
echo "Connected successfully.<br />";
}
$id = $db->nextId("testSeq");
echo "id = $id<br />";
$db->disconnect();
?>
</body></html>
On Mon, 21 Oct 2002, Matthew McNaney wrote:
> > I still don't understand why you aren't just getting the new ID value
> > via the $db->nextId() function via PEAR sequences.
>
> I couldn't get it to work. I will try again today and implement it if
> successful.
>
> Post an example if you get a chance.
>
> Thanks,
> Matt
>
> Matthew McNaney
> Internet Systems Architect
> Electronic Student Services
> Email: ma...@tu...
> URL: http://phpwebsite.appstate.edu
> Phone: 828-262-6493
> ICQ: 141057403
>
>
>
>
> -------------------------------------------------------
> This sf.net email is sponsored by:ThinkGeek
> Welcome to geek heaven.
> http://thinkgeek.com/sf
> _______________________________________________
> Phpwebsite-developers mailing list
> Php...@li...
> https://lists.sourceforge.net/lists/listinfo/phpwebsite-developers
>
|
|
From: Matthew M. <ma...@tu...> - 2002-10-21 13:50:28
|
Ok I figured out what I did wrong. I did not set my test table as primary, it were merely indexed. I thought all my id columns were primary. Anyway... I got it to work. Thanks Don. I actually found your letters to php.net through Google. Man that thing is fast :) As you said (and Bob's -1 explained) it appear to increment correctly. I am naming the sequence by the table name as will be unique per table. I have written the sqlInsert in my copy but I need a definative parameter list. Please vote on the final outcome. function sqlInsert ($db_array, $table_name, $maxColumn=NULL, $check_dup=FALSE, $returnMax=FALSE, $show_sql=FALSE) db_array : associate array of columns=>values table_name : self explanatory maxColumn : the id column to increment check_dup : does not insert a row if a duplicate db_array is found (will ignore the id for checking purposes) returnMax : returns the max id if TRUE. show_sql : shows the sql string for error checking. --------------------------------- User example: $this->user_id = $GLOBALS["core"]->sqlInsert($sql_array, "mod_users", "user_id", 1); I would like to get this voted on as I am anxious to update my code. Please post :) Thanks again, Matthew McNaney Internet Systems Architect Electronic Student Services Email: ma...@tu... URL: http://phpwebsite.appstate.edu Phone: 828-262-6493 ICQ: 141057403 |
|
From: Matthew M. <ma...@tu...> - 2002-10-21 13:55:47
|
Here is the CVS link: http://res1.stddev.appstate.edu/horde/chora/co.php/phpws_core/core/Database.php?r=1.25&Horde=cd378aebc4cbdedfdfd10c2df9052480 Matthew McNaney Internet Systems Architect Electronic Student Services Email: ma...@tu... URL: http://phpwebsite.appstate.edu Phone: 828-262-6493 |
|
From: Don S. <do...@se...> - 2002-10-21 14:01:40
|
I'm wondering if using the table_name as the sequence name will work on all db systems. I don't know explicitly if it won't work, but could there be a possibility of naming collisions? Perhaps using "seq_$table_name" would suit? Just playing devil's advocate ... Don. On Mon, 21 Oct 2002, Matthew McNaney wrote: > Here is the CVS link: > http://res1.stddev.appstate.edu/horde/chora/co.php/phpws_core/core/Database.php?r=1.25&Horde=cd378aebc4cbdedfdfd10c2df9052480 > > > Matthew McNaney > Internet Systems Architect > Electronic Student Services > Email: ma...@tu... > URL: http://phpwebsite.appstate.edu > Phone: 828-262-6493 > > > > > > ------------------------------------------------------- > This sf.net email is sponsored by:ThinkGeek > Welcome to geek heaven. > http://thinkgeek.com/sf > _______________________________________________ > Phpwebsite-developers mailing list > Php...@li... > https://lists.sourceforge.net/lists/listinfo/phpwebsite-developers > |
|
From: Matthew M. <ma...@tu...> - 2002-10-21 14:18:39
|
I'll +1 on both the parameter naming and seq prefixing. Matt > I'm wondering if using the table_name as the sequence name will work on > all db systems. I don't know explicitly if it won't work, but could > there be a possibility of naming collisions? > > Perhaps using "seq_$table_name" would suit? > > Just playing devil's advocate ... |
|
From: Matthew M. <ma...@tu...> - 2002-10-21 14:50:07
|
> I'm wondering if using the table_name as the sequence name will work on > all db systems. I don't know explicitly if it won't work, but could > there be a possibility of naming collisions? > > Perhaps using "seq_$table_name" would suit? I just noticed that PEAR saves the sequence tables as sequenceName_seq So prefixing creates: seq_sequenceName_seq Do we still want the prefix? Matt |
|
From: Don S. <do...@se...> - 2002-10-21 14:55:13
|
Probably not. I wasn't aware that PEAR changed the name behind the scenes. So just using $table_name in the php code should work great. Thanks for the heads-up. Don. On Mon, 21 Oct 2002, Matthew McNaney wrote: > > > I'm wondering if using the table_name as the sequence name will work on > > all db systems. I don't know explicitly if it won't work, but could > > there be a possibility of naming collisions? > > > > Perhaps using "seq_$table_name" would suit? > > I just noticed that PEAR saves the sequence tables as > sequenceName_seq > > So prefixing creates: > > seq_sequenceName_seq > > > Do we still want the prefix? > > Matt > > > > > ------------------------------------------------------- > This sf.net email is sponsored by:ThinkGeek > Welcome to geek heaven. > http://thinkgeek.com/sf > _______________________________________________ > Phpwebsite-developers mailing list > Php...@li... > https://lists.sourceforge.net/lists/listinfo/phpwebsite-developers > |
|
From: Adam M. <ad...@tu...> - 2002-10-21 15:53:05
|
+1 from me on sqlInsert() with idColumn, returnMaxId, and table name as sequence name. Adam > Probably not. I wasn't aware that PEAR changed the name behind the > scenes. So just using $table_name in the php code should work great. > > Thanks for the heads-up. > > Don. > > On Mon, 21 Oct 2002, Matthew McNaney wrote: > >> >> > I'm wondering if using the table_name as the sequence name will work >> on all db systems. I don't know explicitly if it won't work, but >> could there be a possibility of naming collisions? >> > >> > Perhaps using "seq_$table_name" would suit? >> >> I just noticed that PEAR saves the sequence tables as >> sequenceName_seq >> >> So prefixing creates: >> >> seq_sequenceName_seq >> >> >> Do we still want the prefix? >> >> Matt >> >> >> >> >> ------------------------------------------------------- >> This sf.net email is sponsored by:ThinkGeek >> Welcome to geek heaven. >> http://thinkgeek.com/sf >> _______________________________________________ >> Phpwebsite-developers mailing list >> Php...@li... >> https://lists.sourceforge.net/lists/listinfo/phpwebsite-developers >> > > > > ------------------------------------------------------- > This sf.net email is sponsored by:ThinkGeek > Welcome to geek heaven. > http://thinkgeek.com/sf > _______________________________________________ > Phpwebsite-developers mailing list > Php...@li... > https://lists.sourceforge.net/lists/listinfo/phpwebsite-developers --------------------------------- Adam Morton Developer - Web Technology Group Appalachian State University http://phpwebsite.appstate.edu |
|
From: Adam M. <ad...@tu...> - 2002-10-21 18:50:57
|
I would like to tak a closer look at the implemetation of this. It looks like it's creating an extra table just to calculate the next sequence number. Do you really want this since it could effectively double the amount of tables in the database? Adam > +1 from me on sqlInsert() with idColumn, returnMaxId, and table name as > sequence name. > > Adam > >> Probably not. I wasn't aware that PEAR changed the name behind the >> scenes. So just using $table_name in the php code should work great. >> >> Thanks for the heads-up. >> >> Don. >> >> On Mon, 21 Oct 2002, Matthew McNaney wrote: >> >>> >>> > I'm wondering if using the table_name as the sequence name will >>> work >>> on all db systems. I don't know explicitly if it won't work, but >>> could there be a possibility of naming collisions? >>> > >>> > Perhaps using "seq_$table_name" would suit? >>> >>> I just noticed that PEAR saves the sequence tables as >>> sequenceName_seq >>> >>> So prefixing creates: >>> >>> seq_sequenceName_seq >>> >>> >>> Do we still want the prefix? >>> >>> Matt >>> >>> >>> >>> >>> ------------------------------------------------------- >>> This sf.net email is sponsored by:ThinkGeek >>> Welcome to geek heaven. >>> http://thinkgeek.com/sf >>> _______________________________________________ >>> Phpwebsite-developers mailing list >>> Php...@li... >>> https://lists.sourceforge.net/lists/listinfo/phpwebsite-developers >>> >> >> >> >> ------------------------------------------------------- >> This sf.net email is sponsored by:ThinkGeek >> Welcome to geek heaven. >> http://thinkgeek.com/sf >> _______________________________________________ >> Phpwebsite-developers mailing list >> Php...@li... >> https://lists.sourceforge.net/lists/listinfo/phpwebsite-developers > > > --------------------------------- > Adam Morton > Developer - Web Technology Group > Appalachian State University > http://phpwebsite.appstate.edu > > > > > ------------------------------------------------------- > This sf.net email is sponsored by:ThinkGeek > Welcome to geek heaven. > http://thinkgeek.com/sf > _______________________________________________ > Phpwebsite-developers mailing list > Php...@li... > https://lists.sourceforge.net/lists/listinfo/phpwebsite-developers --------------------------------- Adam Morton Developer - Web Technology Group Appalachian State University http://phpwebsite.appstate.edu |
|
From: Don S. <do...@se...> - 2002-10-21 19:01:47
|
Yes it does create a table for each seq, and so it would create a table for every table that uses sequences. I don't know if every table uses sequences, but to play devil's advocate let's say they do. As far as database space, the sequence tables are just one int field. I don't think that size would really be a concern. If your concern is more organizational, I guess there's not much one can do to take advantage of the PEAR system. Personally I don't mind having the table created. I'm sure the PEAR folks took a long, hard look at this as well. They had to make a number of compromises in the name of database abstraction, obviously. I don't think this is that much of a concern. Of course I'm just a fan. Don. On Mon, 21 Oct 2002, Adam Morton wrote: > I would like to tak a closer look at the implemetation of this. It looks > like it's creating an extra table just to calculate the next sequence > number. Do you really want this since it could effectively double the > amount of tables in the database? > > Adam > > > +1 from me on sqlInsert() with idColumn, returnMaxId, and table name as > > sequence name. > > > > Adam > > > >> Probably not. I wasn't aware that PEAR changed the name behind the > >> scenes. So just using $table_name in the php code should work great. > >> > >> Thanks for the heads-up. > >> > >> Don. > >> > >> On Mon, 21 Oct 2002, Matthew McNaney wrote: > >> > >>> > >>> > I'm wondering if using the table_name as the sequence name will > >>> work > >>> on all db systems. I don't know explicitly if it won't work, but > >>> could there be a possibility of naming collisions? > >>> > > >>> > Perhaps using "seq_$table_name" would suit? > >>> > >>> I just noticed that PEAR saves the sequence tables as > >>> sequenceName_seq > >>> > >>> So prefixing creates: > >>> > >>> seq_sequenceName_seq > >>> > >>> > >>> Do we still want the prefix? > >>> > >>> Matt > >>> > >>> > >>> > >>> > >>> ------------------------------------------------------- > >>> This sf.net email is sponsored by:ThinkGeek > >>> Welcome to geek heaven. > >>> http://thinkgeek.com/sf > >>> _______________________________________________ > >>> Phpwebsite-developers mailing list > >>> Php...@li... > >>> https://lists.sourceforge.net/lists/listinfo/phpwebsite-developers > >>> > >> > >> > >> > >> ------------------------------------------------------- > >> This sf.net email is sponsored by:ThinkGeek > >> Welcome to geek heaven. > >> http://thinkgeek.com/sf > >> _______________________________________________ > >> Phpwebsite-developers mailing list > >> Php...@li... > >> https://lists.sourceforge.net/lists/listinfo/phpwebsite-developers > > > > > > --------------------------------- > > Adam Morton > > Developer - Web Technology Group > > Appalachian State University > > http://phpwebsite.appstate.edu > > > > > > > > > > ------------------------------------------------------- > > This sf.net email is sponsored by:ThinkGeek > > Welcome to geek heaven. > > http://thinkgeek.com/sf > > _______________________________________________ > > Phpwebsite-developers mailing list > > Php...@li... > > https://lists.sourceforge.net/lists/listinfo/phpwebsite-developers > > > --------------------------------- > Adam Morton > Developer - Web Technology Group > Appalachian State University > http://phpwebsite.appstate.edu > > > > > ------------------------------------------------------- > This sf.net emial is sponsored by: Influence the future of > Java(TM) technology. Join the Java Community Process(SM) (JCP(SM)) > program now. http://ad.doubleclick.net/clk;4699841;7576298;k? > http://www.sun.com/javavote > _______________________________________________ > Phpwebsite-developers mailing list > Php...@li... > https://lists.sourceforge.net/lists/listinfo/phpwebsite-developers > |
|
From: Matthew M. <ma...@tu...> - 2002-10-21 19:18:50
|
> I would like to tak a closer look at the implemetation of this. It > looks like it's creating an extra table just to calculate the next > sequence number. Do you really want this since it could effectively > double the amount of tables in the database? Hmmm. There might be a way around this. Say we create our OWN sequencer. table : core_sequencer ----------------- column : table_name column : primary_key_name column : current_id When sqlImport calls "create table" with a primary key then the table name and key are inserted into the sequencer. Then when sqlInsert is called the table name is selected. The column name and current id are returned and the id is incremented. This could effectively make sqlInsert a little easier. $core->sqlInsert($data_array, "table_name", TRUE); The TRUE telling sqlInsert to increment. It would already know WHAT to increment from the sequencer table. So you could even have: $core->sqlInsert($data_array, "table_name"); With an automatic check to the sequencer table, effectively returning us to the original format... that we had... 90 commits ago... ;-) Let me know what you guys think... Matthew McNaney Internet Systems Architect Electronic Student Services Email: ma...@tu... URL: http://phpwebsite.appstate.edu Phone: 828-262-6493 |
|
From: Don S. <do...@se...> - 2002-10-21 19:28:32
|
I like this better than just selecting the max ID from the table and incrementing. I'm assuming you lock the sequencer table while incrementing? If so is there any chance of creating dead-locks on multiple inserts? Don. On Mon, 21 Oct 2002, Matthew McNaney wrote: > > > I would like to tak a closer look at the implemetation of this. It > > looks like it's creating an extra table just to calculate the next > > sequence number. Do you really want this since it could effectively > > double the amount of tables in the database? > > Hmmm. There might be a way around this. > > Say we create our OWN sequencer. > > table : core_sequencer > ----------------- > column : table_name > column : primary_key_name > column : current_id > > When sqlImport calls "create table" with a primary key then the table name > and key are inserted into the sequencer. > > Then when sqlInsert is called the table name is selected. The column name > and current id are returned and the id is incremented. > > This could effectively make sqlInsert a little easier. > > $core->sqlInsert($data_array, "table_name", TRUE); > > The TRUE telling sqlInsert to increment. It would already know WHAT to > increment from the sequencer table. So you could even have: > > $core->sqlInsert($data_array, "table_name"); > > With an automatic check to the sequencer table, effectively returning us > to the original format... that we had... 90 commits ago... ;-) > > Let me know what you guys think... > > Matthew McNaney > Internet Systems Architect > Electronic Student Services > Email: ma...@tu... > URL: http://phpwebsite.appstate.edu > Phone: 828-262-6493 > > > > > ------------------------------------------------------- > This sf.net emial is sponsored by: Influence the future of > Java(TM) technology. Join the Java Community Process(SM) (JCP(SM)) > program now. http://ad.doubleclick.net/clk;4699841;7576298;k? > http://www.sun.com/javavote > _______________________________________________ > Phpwebsite-developers mailing list > Php...@li... > https://lists.sourceforge.net/lists/listinfo/phpwebsite-developers > |
|
From: Don S. <do...@se...> - 2002-10-21 19:33:47
|
Although for what it's worth, my vote is still just to use the PEAR sequencer. It is a "standard" of sorts. Do modules go through the core sqlInsert function for their database transactions? Don. On Mon, 21 Oct 2002, Don Seiler wrote: > I like this better than just selecting the max ID from the table and > incrementing. > > I'm assuming you lock the sequencer table while incrementing? If so is > there any chance of creating dead-locks on multiple inserts? > > Don. > > On Mon, 21 Oct 2002, Matthew McNaney wrote: > > > > > > I would like to tak a closer look at the implemetation of this. It > > > looks like it's creating an extra table just to calculate the next > > > sequence number. Do you really want this since it could effectively > > > double the amount of tables in the database? > > > > Hmmm. There might be a way around this. > > > > Say we create our OWN sequencer. > > > > table : core_sequencer > > ----------------- > > column : table_name > > column : primary_key_name > > column : current_id > > > > When sqlImport calls "create table" with a primary key then the table name > > and key are inserted into the sequencer. > > > > Then when sqlInsert is called the table name is selected. The column name > > and current id are returned and the id is incremented. > > > > This could effectively make sqlInsert a little easier. > > > > $core->sqlInsert($data_array, "table_name", TRUE); > > > > The TRUE telling sqlInsert to increment. It would already know WHAT to > > increment from the sequencer table. So you could even have: > > > > $core->sqlInsert($data_array, "table_name"); > > > > With an automatic check to the sequencer table, effectively returning us > > to the original format... that we had... 90 commits ago... ;-) > > > > Let me know what you guys think... > > > > Matthew McNaney > > Internet Systems Architect > > Electronic Student Services > > Email: ma...@tu... > > URL: http://phpwebsite.appstate.edu > > Phone: 828-262-6493 > > > > > > > > > > ------------------------------------------------------- > > This sf.net emial is sponsored by: Influence the future of > > Java(TM) technology. Join the Java Community Process(SM) (JCP(SM)) > > program now. http://ad.doubleclick.net/clk;4699841;7576298;k? > > http://www.sun.com/javavote > > _______________________________________________ > > Phpwebsite-developers mailing list > > Php...@li... > > https://lists.sourceforge.net/lists/listinfo/phpwebsite-developers > > > > > > ------------------------------------------------------- > This sf.net emial is sponsored by: Influence the future of > Java(TM) technology. Join the Java Community Process(SM) (JCP(SM)) > program now. http://ad.doubleclick.net/clk;4699841;7576298;k? > http://www.sun.com/javavote > _______________________________________________ > Phpwebsite-developers mailing list > Php...@li... > https://lists.sourceforge.net/lists/listinfo/phpwebsite-developers > |
|
From: Eloi G. <el...@re...> - 2002-10-21 21:15:29
|
> Hmmm. There might be a way around this. > > Say we create our OWN sequencer. > > table : core_sequencer > ----------------- > column : table_name > column : primary_key_name > column : current_id Gotta give a -1 on this one. While it is a really elegant solution to the table spawn problem, it becomes a bottleneck when your site breaks into the mainstream. It's one thing when you have 20 queries + 5 inserts per second on various tables in your database. But if each of those inserts also requires a table lock on the core_sequencer, the entire site will get bogged down really fast. I know I'm coming in too late in the game, but it'd be great if phpWS didn't try to support every db in the known world. That way there wouldn't be a bunch of middle-layer generalized db access protocols to slow everything down. |
|
From: Don S. <do...@se...> - 2002-10-22 14:50:00
|
> I know I'm coming in too late in the game, but it'd be great if phpWS didn't > try to support every db in the known world. That way there wouldn't be a > bunch of middle-layer generalized db access protocols to slow everything > down. If we use the PEAR standards, then phpWS doesn't need to worry about supporting things, the PEAR team will have done all that legwork, and I'm confident that the PEAR team will have done it the most efficient way possible. Even if phpWS only wanted to support two or three, once you support more than one you might as well use PEAR and support them all, rather than write the functions yourself. I'd be interested in seeing some benchmarks about how the pear DB layer affects performance. I don't think the hit would be that bad. What is the largest scale site running phpWS, btw? Don. |
|
From: Eloi G. <el...@re...> - 2002-10-22 16:58:08
|
www.actionfigure.com is actually a postnuke site, but is a pretty good representation of what we may hope that one of our (or our client's) sites can achieve. Looking at his stats at http://www.action-figure.com/modules.php?op=modload&name=Stats&file=index it looks like the site has averaged 570,000 hits per month for the last 9 months. That's 13 hits per second. Postnuke doesn't have any load-balancing features, so all this is handled on one server. If we don't count the main page (13 modules), the average page on the site looks like it uses 6 modules including banner rotations & user stats. If we assume just 1 query per module, then we end up with an average of 78 queries per second! Hmm. I don't think any db's can handle that much... -Eloi- ----- Original Message ----- From: "Don Seiler" <do...@se...> To: "Eloi George" <el...@re...> Cc: <ma...@tu...>; <php...@li...> Sent: Tuesday, October 22, 2002 10:49 AM Subject: Re: [Phpwebsite-developers] PEAR DB and auto_increment > > I know I'm coming in too late in the game, but it'd be great if phpWS didn't > > try to support every db in the known world. That way there wouldn't be a > > bunch of middle-layer generalized db access protocols to slow everything > > down. > > If we use the PEAR standards, then phpWS doesn't need to worry about > supporting things, the PEAR team will have done all that legwork, and I'm > confident that the PEAR team will have done it the most efficient way > possible. > > Even if phpWS only wanted to support two or three, once you support more > than one you might as well use PEAR and support them all, rather than > write the functions yourself. > > I'd be interested in seeing some benchmarks about how the pear DB layer > affects performance. I don't think the hit would be that bad. What is > the largest scale site running phpWS, btw? > > Don. > > > |
×
Want the latest updates on software, tech news, and AI?
Get latest updates about software, tech news, and AI from SourceForge directly in your inbox once a month.
Thanks for helping keep SourceForge clean.
X