Thread: [SQLObject] sqlbuilder REPLACE
SQLObject is a Python ORM.
Brought to you by:
ianbicking,
phd
From: Frank C. <fr...@qf...> - 2007-12-01 04:44:37
|
Forgive me if this has been discussed before, but I searched the archives without any success. In sqlbuilder the "Replace" expression currently inherits from "Update", but in MySQL, according to their official docs, "REPLACE works exactly like INSERT", i.e. "REPLACE INTO ...". Thus it looks like it should rather inherit from "Insert" (just replacing the "INSERT" with "REPLACE"). I know very little about standard SQL, or other DB SQL, so I was wondering if this is universal? - Frank |
From: Oleg B. <ph...@ph...> - 2007-12-04 22:07:25
|
On Fri, Nov 30, 2007 at 08:44:18PM -0800, Frank Conradie wrote: > In sqlbuilder the "Replace" expression currently inherits from "Update", > but in MySQL, according to their official docs, "REPLACE works exactly > like INSERT", i.e. "REPLACE INTO ...". Thus it looks like it should > rather inherit from "Insert" (just replacing the "INSERT" with "REPLACE"). The syntax is (http://dev.mysql.com/doc/refman/4.1/en/replace.html): REPLACE [LOW_PRIORITY | DELAYED] [INTO] tbl_name SET col_name={expr | DEFAULT}, ... That is, it's much more UPDATE (because of SET) than INSERT (that uses a list of names and a list of values, not name/value pairs). PS. Forgive me for not answering so long. Oleg. -- Oleg Broytmann http://phd.pp.ru/ ph...@ph... Programmers don't die, they just GOSUB without RETURN. |
From: Frank C. <fr...@qf...> - 2007-12-04 22:20:09
|
Hi Oleg I will have to disagree again - please read the page that you link to below carefully: "|REPLACE| works exactly like |INSERT|, except that if an old row in the table has the same value as a new row for a |PRIMARY KEY| or a |UNIQUE| index, the old row is deleted before the new row is inserted." INSERT supports *exactly* the same 3 syntax options that REPLACE does, while UPDATE only overlaps the one syntax option, and not even fully (i.e. REPLACE does not accept a WHERE or LIMIT): UPDATE [LOW_PRIORITY] [IGNORE] /|tbl_name|/ SET /|col_name1|/=/|expr1|/ [, /|col_name2|/=/|expr2|/ ...] [WHERE /|where_condition|/] [ORDER BY ...] [LIMIT /|row_count|/] - Frank Oleg Broytmann wrote: > On Fri, Nov 30, 2007 at 08:44:18PM -0800, Frank Conradie wrote: > >> In sqlbuilder the "Replace" expression currently inherits from "Update", >> but in MySQL, according to their official docs, "REPLACE works exactly >> like INSERT", i.e. "REPLACE INTO ...". Thus it looks like it should >> rather inherit from "Insert" (just replacing the "INSERT" with "REPLACE"). >> > > The syntax is (http://dev.mysql.com/doc/refman/4.1/en/replace.html): > > REPLACE [LOW_PRIORITY | DELAYED] > [INTO] tbl_name > SET col_name={expr | DEFAULT}, ... > > That is, it's much more UPDATE (because of SET) than INSERT (that uses > a list of names and a list of values, not name/value pairs). > > PS. Forgive me for not answering so long. > > Oleg. > |
From: Frank C. <fr...@qf...> - 2007-12-04 22:29:45
|
Hi Oleg I don't want to fight with you, but INSERT also supports SET: INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE] [INTO] tbl_name SET col_name={expr | DEFAULT}, ... And here is the REPLACE syntax: REPLACE [LOW_PRIORITY | DELAYED] [INTO] tbl_name SET col_name={expr | DEFAULT}, ... In fact, both INSERT and REPLACE support the *exact same* 3 different syntaxes: REPLACE/INSERT [LOW_PRIORITY | DELAYED] [INTO] tbl_name [(col_name,...)] VALUES ({expr | DEFAULT},...),(...),... Or: REPLACE/INSERT [LOW_PRIORITY | DELAYED] [INTO] tbl_name SET col_name={expr | DEFAULT}, ... Or: REPLACE/INSERT [LOW_PRIORITY | DELAYED] [INTO] tbl_name [(col_name,...)] SELECT ... But UPDATE only has 1 syntax: UPDATE [LOW_PRIORITY] [IGNORE] tbl_name SET col_name1=expr1 [, col_name2=expr2 ...] [WHERE where_condition] [ORDER BY ...] [LIMIT row_count] I can also post code that proves this, although you can save me some time by just reading the 3 pages of MySQL documentation. - Frank Oleg Broytmann wrote: > On Tue, Dec 04, 2007 at 02:19:31PM -0800, Frank Conradie wrote: > >> I will have to disagree again - please read the page that you link to >> below carefully: "|REPLACE| works exactly like |INSERT| >> > > SQLObject is more interested in syntax. SET and name/value pairs make it > much more like UPDATE than INSERT. > > Oleg. > |
From: Frank C. <fr...@qf...> - 2007-12-04 23:15:50
|
Oleg I see your point - I am using MySQL and didn't think about the other DB's. Looking more closely at the postgresql and sqlite docs, plus the fact that REPLACE is not standard SQL at all, I see that it will be quite a job to implement a more flexible Replace expression that supports all the different DB's in their own peculiar ways: * postgresql: does not even have any sort of REPLACE at all (AFAICT) * sqlite: REPLACE works like its INSERT: /"The REPLACE command is an alias for the "INSERT OR REPLACE" variant of the INSERT command. This alias is provided for compatibility with MySQL.". / So if I wanted to improve the sqlbuilder Replace, I guess it would be best to have a totally new Replace expression (not inheriting from Insert or Update), and then have lots of these: if db == "..." Would this be the way to go? - Frank Oleg Broytmann wrote: > On Tue, Dec 04, 2007 at 02:29:10PM -0800, Frank Conradie wrote: > >> I don't want to fight with you, but INSERT also supports SET: >> > > No, it doesn't (meaning "standard" SQL). > > http://www.postgresql.org/docs/7.3/static/sql-insert.html : > > INSERT INTO table [ ( column [, ...] ) ] > { DEFAULT VALUES | VALUES ( { expression | DEFAULT } [, ...]) | SELECT query } > > http://sqlite.org/lang_insert.html : > > INSERT [OR conflict-algorithm] INTO [database-name .] table-name > [(column-list)] VALUES (value-list) > > SQLObject produces that (more or less) standard INSERT, not MySQL > variant. > > PS. But if you want to teach SQLObject to generate MySQLish INSERT - you > know where to send patches. > > Oleg. > |
From: Oleg B. <ph...@ph...> - 2007-12-05 06:29:26
|
On Tue, Dec 04, 2007 at 03:15:12PM -0800, Frank Conradie wrote: > * postgresql: does not even have any sort of REPLACE at all (AFAICT) It doesn't. > * sqlite: REPLACE works like its INSERT: /"The REPLACE command is an > alias for the "INSERT OR REPLACE" variant of the INSERT command. This > alias is provided for compatibility with MySQL.". / Yes, this time you are right - REPLACE in SQLite is a variant of INSERT. Nobody has complained yet about absent of proper support of REPLACE with SQLite, so I think people use REPLACE only with MySQL. > So if I wanted to improve the sqlbuilder Replace, I guess it would be > best to have a totally new Replace expression (not inheriting from > Insert or Update), and then have lots of these: > if db == "..." Yes, but I don't think there will be a lot of "if"s - MySQL and SQLite. Oleg. -- Oleg Broytmann http://phd.pp.ru/ ph...@ph... Programmers don't die, they just GOSUB without RETURN. |
From: Oleg B. <ph...@ph...> - 2007-12-04 22:23:53
|
On Tue, Dec 04, 2007 at 02:19:31PM -0800, Frank Conradie wrote: > I will have to disagree again - please read the page that you link to > below carefully: "|REPLACE| works exactly like |INSERT| SQLObject is more interested in syntax. SET and name/value pairs make it much more like UPDATE than INSERT. Oleg. -- Oleg Broytmann http://phd.pp.ru/ ph...@ph... Programmers don't die, they just GOSUB without RETURN. |
From: Oleg B. <ph...@ph...> - 2007-12-04 22:42:51
|
On Tue, Dec 04, 2007 at 02:29:10PM -0800, Frank Conradie wrote: > I don't want to fight with you, but INSERT also supports SET: No, it doesn't (meaning "standard" SQL). http://www.postgresql.org/docs/7.3/static/sql-insert.html : INSERT INTO table [ ( column [, ...] ) ] { DEFAULT VALUES | VALUES ( { expression | DEFAULT } [, ...]) | SELECT query } http://sqlite.org/lang_insert.html : INSERT [OR conflict-algorithm] INTO [database-name .] table-name [(column-list)] VALUES (value-list) SQLObject produces that (more or less) standard INSERT, not MySQL variant. PS. But if you want to teach SQLObject to generate MySQLish INSERT - you know where to send patches. Oleg. -- Oleg Broytmann http://phd.pp.ru/ ph...@ph... Programmers don't die, they just GOSUB without RETURN. |