Menu
▾
▴
phpwebsite-developers
|
From: Richard S. <ri...@ri...> - 2003-08-20 18:47:43
|
As I looked for what is the common standard for creating auto increment=20= columns in a table I found that there isn't one. I know the built in=20 sequencing PWS has in play for auto increment has some play in this but=20= not completely sure how it works when setting up a table? What do you guys think will be the best way to create tables with an=20 AUTO INCREMENT column with PWS? I currently don't care for too much=20 support for Oracle right now as you can see with the example below. Syntax for MySQL =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D CREATE TABLE test ( id INT NOT NULL AUTO_INCREMENT, PRIMARY KEY ( id) ); Syntax for PostgreSQL =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D CREATE TABLE test ( id SERIAL , PRIMARY KEY (id) ); Syntax for Oracle9i =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D -- --Create sequence called demo -- CREATE SEQUENCE "DEMO"."SEQ_INSERT" INCREMENT BY 1 START WITH 1780229 MAXVALUE 1.0E28 MINVALUE 1 NOCYCLE CACHE 20 ORDER -- -- Next place a trigger for that column you want to auto increment -- -- CREATE OR REPLACE TRIGGER "DEMO"."TRG_EMP_INSERT" BEFORE INSERT ON "DEMO"."EMP" =A0 =A0 FOR EACH ROW BEGIN =A0 SELECT seq_insert .nextval =A0-- get the new seq number =A0 =A0 INTO :NEW.emp_no -- :NEW missed =A0 =A0 FROM DUAL; END; # Found source for Oracle at=20 http://www.experts-exchange.com/Databases/Q_20412293.html Best Regards, - Richard S= |
|
From: Matthew M. <ma...@tu...> - 2003-08-22 17:00:47
|
On Wed, 2003-08-20 at 13:37, Richard Sumilang wrote: > As I looked for what is the common standard for creating auto increment > columns in a table I found that there isn't one... > Syntax for MySQL > =========================== > CREATE TABLE test ( > id INT NOT NULL AUTO_INCREMENT, > PRIMARY KEY ( id) > ); > > Syntax for PostgreSQL > =========================== > CREATE TABLE test ( > id SERIAL , > PRIMARY KEY (id) > ); These should be easy to identify with the current process: look for an integer that is the primary key. > > Syntax for Oracle9i > =========================== > -- > --Create sequence called demo > -- > CREATE SEQUENCE "DEMO"."SEQ_INSERT" INCREMENT BY 1 START WITH > 1780229 MAXVALUE 1.0E28 MINVALUE 1 NOCYCLE > CACHE 20 ORDER > > -- > -- Next place a trigger for that column you want to auto increment -- > -- > CREATE OR REPLACE TRIGGER "DEMO"."TRG_EMP_INSERT" BEFORE > INSERT ON "DEMO"."EMP" > FOR EACH ROW BEGIN > > SELECT seq_insert .nextval -- get the new seq number > > INTO :NEW.emp_no -- :NEW missed > FROM DUAL; > END; > > # Found source for Oracle at > http://www.experts-exchange.com/Databases/Q_20412293.html I wouldn't even know where to begin. What are the parameters of the column to be auto incremented in Oracle? Is it labeled as the primary key? There are some other possible solutions if getting the column from Oracle is too difficult: 1) If you are using an auto incremented column, it must be called "id". Could work although I think a few might be annoyed by this. 2) A table could track the incremented columns ( TABLE_NAME | ID_COLUMN ). It would get loaded when the database is first accessed. The cons here are that you would have to indicate what that column is on install. And it would be another table accessed when the page is created unless we session it. 3) Everytime the database object is called, the developer would specify a column requiring auto_incrementing. Another minor pain. You would just have to remember to enter it everytime. These are just a few suggestions, which you are welcome to add to. AFAIK Pear does NOT have a function to figure out an incremented column, you have to tell it each time insert is called (which make sense). One quick note, one of the above versions MAY be better than the current implementation. Currently, the insert function has to figure out the index on its own. Getting told the column right off the bat would save a little time, although inserts are certainly less common than say select. -- Matthew McNaney Internet Systems Architect Electronic Student Services Appalachian State University Phone: 828-262-6493 http://phpwebsite.appstate.edu http://ess.appstate.edu |
|
From: Richard S. <ri...@ri...> - 2003-08-22 11:36:35
|
I agree identifying the auto incrementing column for MySQL and Postgres is easy. The way Postgres and Oracle create auto incrementing columns are not different, they both use sequencing but Postgres has a wrapper for creating the sequence which is by setting the column to a SERIAL column. When you do that Postgres automatically creates all the code you see for creating an auto increment column on Oracle and sets the column to primary key if it is not. I think it is fair to assume when creating a table and you have a column that is a integer and setting it to primary key that it should be auto incremented, and yes Oracle has a primary key field and used when auto incrementing. So at any rate, I think looking for the primary key is the best idea however column types might now be the next issue. Postgres supports doing stuff like "create table test(col1 varchar(5) not null);" and "create table test(col1 varchar[5] not null);" but it acts a little different for integers (I don't know why). If you would like to specify a column size for an integer you have to use square brackets, eg: "create table test1(col1 int[5] not null);" and it wont accept "()" so that is just some extra parsing... - Regards Richard Sumilang On Thursday, August 21, 2003, at 05:55 AM, Matthew McNaney wrote: > On Wed, 2003-08-20 at 13:37, Richard Sumilang wrote: >> As I looked for what is the common standard for creating auto >> increment >> columns in a table I found that there isn't one... > >> Syntax for MySQL >> =========================== >> CREATE TABLE test ( >> id INT NOT NULL AUTO_INCREMENT, >> PRIMARY KEY ( id) >> ); > >> >> Syntax for PostgreSQL >> =========================== >> CREATE TABLE test ( >> id SERIAL , >> PRIMARY KEY (id) >> ); > > These should be easy to identify with the current process: look for an > integer that is the primary key. > >> >> Syntax for Oracle9i >> =========================== >> -- >> --Create sequence called demo >> -- >> CREATE SEQUENCE "DEMO"."SEQ_INSERT" INCREMENT BY 1 START WITH >> 1780229 MAXVALUE 1.0E28 MINVALUE 1 NOCYCLE >> CACHE 20 ORDER >> >> -- >> -- Next place a trigger for that column you want to auto increment -- >> -- >> CREATE OR REPLACE TRIGGER "DEMO"."TRG_EMP_INSERT" BEFORE >> INSERT ON "DEMO"."EMP" >> FOR EACH ROW BEGIN >> >> SELECT seq_insert .nextval -- get the new seq number >> >> INTO :NEW.emp_no -- :NEW missed >> FROM DUAL; >> END; >> >> # Found source for Oracle at >> http://www.experts-exchange.com/Databases/Q_20412293.html > > I wouldn't even know where to begin. What are the parameters of the > column to be auto incremented in Oracle? Is it labeled as the primary > key? > > There are some other possible solutions if getting the column from > Oracle is too difficult: > > 1) If you are using an auto incremented column, it must be called "id". > Could work although I think a few might be annoyed by this. > > 2) A table could track the incremented columns ( TABLE_NAME | > ID_COLUMN > ). It would get loaded when the database is first accessed. > The cons here are that you would have to indicate what that column is > on > install. And it would be another table accessed when the page is > created > unless we session it. > > 3) Everytime the database object is called, the developer would specify > a column requiring auto_incrementing. > Another minor pain. You would just have to remember to enter it > everytime. > > These are just a few suggestions, which you are welcome to add to. > AFAIK > Pear does NOT have a function to figure out an incremented column, you > have to tell it each time insert is called (which make sense). > > One quick note, one of the above versions MAY be better than the > current > implementation. Currently, the insert function has to figure out the > index on its own. Getting told the column right off the bat would save > a > little time, although inserts are certainly less common than say > select. > > > -- > Matthew McNaney > Internet Systems Architect > Electronic Student Services > Appalachian State University > Phone: 828-262-6493 > http://phpwebsite.appstate.edu > http://ess.appstate.edu > > > |
|
From: Eloi G. <el...@re...> - 2003-08-23 05:11:43
|
CREATE TABLE test ( id int NOT NULL PRIMARY KEY ); This -should- work for all db's. The PEAR routines that phpWS appear to search for the primary index and will automatically set up a _seq table i= f you enable autoincrementing in the core->sqlInsert() parameters. Eloi George ----- Original Message ----- From: "Richard Sumilang" <ri...@ri...> To: <php...@li...> Sent: Wednesday, August 20, 2003 1:37 PM Subject: [Phpwebsite-developers] Auto Increment SQL problem As I looked for what is the common standard for creating auto increment columns in a table I found that there isn't one. I know the built in sequencing PWS has in play for auto increment has some play in this but not completely sure how it works when setting up a table? What do you guys think will be the best way to create tables with an AUTO INCREMENT column with PWS? I currently don't care for too much support for Oracle right now as you can see with the example below. Syntax for MySQL =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D CREATE TABLE test ( id INT NOT NULL AUTO_INCREMENT, PRIMARY KEY ( id) ); Syntax for PostgreSQL =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D CREATE TABLE test ( id SERIAL , PRIMARY KEY (id) ); Syntax for Oracle9i =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D -- --Create sequence called demo -- CREATE SEQUENCE "DEMO"."SEQ_INSERT" INCREMENT BY 1 START WITH 1780229 MAXVALUE 1.0E28 MINVALUE 1 NOCYCLE CACHE 20 ORDER -- -- Next place a trigger for that column you want to auto increment -- -- CREATE OR REPLACE TRIGGER "DEMO"."TRG_EMP_INSERT" BEFORE INSERT ON "DEMO"."EMP" FOR EACH ROW BEGIN SELECT seq_insert .nextval -- get the new seq number INTO :NEW.emp_no -- :NEW missed FROM DUAL; END; # Found source for Oracle at http://www.experts-exchange.com/Databases/Q_20412293.html Best Regards, - Richard S ------------------------------------------------------- This SF.net email is sponsored by Dice.com. Did you know that Dice has over 25,000 tech jobs available today? From careers in IT to Engineering to Tech Sales, Dice has tech jobs from the best hiring companies. http://www.dice.com/index.epl?rel_code=104 _______________________________________________ Phpwebsite-developers mailing list Php...@li... https://lists.sourceforge.net/lists/listinfo/phpwebsite-developers |
|
From: Richard S. <ri...@ri...> - 2003-08-24 06:12:32
|
Great! Works for me +1 for removing database specific auto increment columns and having=20 columns that are integer's and primary keys as the auto increment=20 column. PS, lets not forget to indexes on a separate command. I don't know if=20 there is a sql standard for creating indexes during creation of tables=20= so I can possible be wrong. Tried google but no luck... - Richard S. On Wednesday, August 20, 2003, at 02:03 PM, Eloi George wrote: > CREATE TABLE test ( > id int NOT NULL PRIMARY KEY > ); > > This -should- work for all db's. The PEAR routines that phpWS appear=20= > to > search for the primary index and will automatically set up a _seq=20 > table if > you enable autoincrementing in the core->sqlInsert() parameters. > > Eloi George > > > ----- Original Message ----- > From: "Richard Sumilang" <ri...@ri...> > To: <php...@li...> > Sent: Wednesday, August 20, 2003 1:37 PM > Subject: [Phpwebsite-developers] Auto Increment SQL problem > > > As I looked for what is the common standard for creating auto = increment > columns in a table I found that there isn't one. I know the built in > sequencing PWS has in play for auto increment has some play in this = but > not completely sure how it works when setting up a table? > > What do you guys think will be the best way to create tables with an > AUTO INCREMENT column with PWS? I currently don't care for too much > support for Oracle right now as you can see with the example below. > > > > > Syntax for MySQL > =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D > CREATE TABLE test ( > id INT NOT NULL AUTO_INCREMENT, > PRIMARY KEY ( id) > ); > > > Syntax for PostgreSQL > =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D > CREATE TABLE test ( > id SERIAL , > PRIMARY KEY (id) > ); > > > Syntax for Oracle9i > =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D > -- > --Create sequence called demo > -- > CREATE SEQUENCE "DEMO"."SEQ_INSERT" INCREMENT BY 1 START WITH > 1780229 MAXVALUE 1.0E28 MINVALUE 1 NOCYCLE > CACHE 20 ORDER > > -- > -- Next place a trigger for that column you want to auto increment -- > -- > CREATE OR REPLACE TRIGGER "DEMO"."TRG_EMP_INSERT" BEFORE > INSERT ON "DEMO"."EMP" > FOR EACH ROW BEGIN > > SELECT seq_insert .nextval -- get the new seq number > > INTO :NEW.emp_no -- :NEW missed > FROM DUAL; > END; > > # Found source for Oracle at > http://www.experts-exchange.com/Databases/Q_20412293.html > > > > Best Regards, > - Richard S > > > ------------------------------------------------------- > This SF.net email is sponsored by Dice.com. > Did you know that Dice has over 25,000 tech jobs available today? From > careers in IT to Engineering to Tech Sales, Dice has tech jobs from = the > best hiring companies. http://www.dice.com/index.epl?rel_code=104 > _______________________________________________ > Phpwebsite-developers mailing list > Php...@li... > https://lists.sourceforge.net/lists/listinfo/phpwebsite-developers > > > > > > > ------------------------------------------------------- > This SF.net email is sponsored by: VM Ware > With VMware you can run multiple operating systems on a single = machine. > WITHOUT REBOOTING! Mix Linux / Windows / Novell virtual machines > at the same time. Free trial click=20 > here:http://www.vmware.com/wl/offer/358/0 > _______________________________________________ > Phpwebsite-developers mailing list > Php...@li... > https://lists.sourceforge.net/lists/listinfo/phpwebsite-developers > > |
|
From: Matthew M. <ma...@tu...> - 2003-08-25 14:12:42
|
> PS, lets not forget to indexes on a separate command. I don't know if > there is a sql standard for creating indexes during creation of tables > so I can possible be wrong. Tried google but no luck... > > - Richard S. I will help facilitate this by building it into the export function in the new DB class for 0.9.4. Matt -- Matthew McNaney Internet Systems Architect Electronic Student Services Appalachian State University Phone: 828-262-6493 http://phpwebsite.appstate.edu http://ess.appstate.edu |
|
From: Richard S. <ri...@ri...> - 2003-08-26 04:47:23
|
Great, I look forward to it. What about the auto incrementing? Are you going to remove all the auto increment commands after the id column type in the setup SQL files? - Richard S. On Monday, August 25, 2003, at 05:45 AM, Matthew McNaney wrote: >> PS, lets not forget to indexes on a separate command. I don't know if >> there is a sql standard for creating indexes during creation of tables >> so I can possible be wrong. Tried google but no luck... >> >> - Richard S. > > I will help facilitate this by building it into the export function in > the new DB class for 0.9.4. > > Matt > > -- > Matthew McNaney > Internet Systems Architect > Electronic Student Services > Appalachian State University > Phone: 828-262-6493 > http://phpwebsite.appstate.edu > http://ess.appstate.edu > > > > ------------------------------------------------------- > This SF.net email is sponsored by: VM Ware > With VMware you can run multiple operating systems on a single machine. > WITHOUT REBOOTING! Mix Linux / Windows / Novell virtual machines > at the same time. Free trial click > here:http://www.vmware.com/wl/offer/358/0 > _______________________________________________ > Phpwebsite-developers mailing list > Php...@li... > https://lists.sourceforge.net/lists/listinfo/phpwebsite-developers > > |
|
From: Matthew M. <ma...@tu...> - 2003-08-25 20:47:02
|
On Mon, 2003-08-25 at 13:13, Richard Sumilang wrote: > Great, I look forward to it. What about the auto incrementing? Are you > going to remove all the auto increment commands after the id column > type in the setup SQL files? > > - Richard S. Yes we will take out all the index designations from the CREATE TABLE query. Matthew McNaney Internet Systems Architect Electronic Student Services Appalachian State University Phone: 828-262-6493 http://phpwebsite.appstate.edu http://ess.appstate.edu |
|
From: Richard S. <ri...@ri...> - 2003-08-26 04:24:45
|
Awesome, I'm looking forward to 9.4 :-) !!!!!! Is there generally anything that you guys are working on for 9.4 that maybe I could possibly help out on to get the ball rolling on this? - Richard S. On Monday, August 25, 2003, at 11:21 AM, Matthew McNaney wrote: > On Mon, 2003-08-25 at 13:13, Richard Sumilang wrote: >> Great, I look forward to it. What about the auto incrementing? Are you >> going to remove all the auto increment commands after the id column >> type in the setup SQL files? >> >> - Richard S. > > Yes we will take out all the index designations from the CREATE TABLE > query. > > > Matthew McNaney > Internet Systems Architect > Electronic Student Services > Appalachian State University > Phone: 828-262-6493 > http://phpwebsite.appstate.edu > http://ess.appstate.edu > > > > ------------------------------------------------------- > This SF.net email is sponsored by: VM Ware > With VMware you can run multiple operating systems on a single machine. > WITHOUT REBOOTING! Mix Linux / Windows / Novell virtual machines > at the same time. Free trial click > here:http://www.vmware.com/wl/offer/358/0 > _______________________________________________ > Phpwebsite-developers mailing list > Php...@li... > https://lists.sourceforge.net/lists/listinfo/phpwebsite-developers > > |
×
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