Thread: Updating old code for new engine
Good morning, I have been setting up a server at home as a workbench to learn PHP, Postgres, and a few other tools. I recently installed an application (PHPRecipeBook) that was written for Postgres 7.2.3. But I have 8.2.3 installed. When I try to access the recipe book, I get a few errors; surprise, surprise. Is there any way to identify what adjustments I need to make in the queries to compensate for the difference in target versions? My dream option would be a form in which I enter the original target and the new target and get back a list of all the syntax changes I need to account for. I started to walk through the release notes, but there are far too many changes spread too widely to make sense of. The first level errors I get are: SELECT count(*) FROM recipe_recipes WHERE recipe_name LIKE 'A%' OR recipe_name LIKE 'a%' ORDER BY recipe_name ERROR: column "recipe_recipes.recipe_name" must appear in the GROUP BY clause or be used in an aggregate function SELECT recipe_id,recipe_name FROM recipe_recipes WHERE recipe_name LIKE 'A%' OR recipe_name LIKE 'a%' ORDER BY recipe_name LIMIT OFFSET 0 ERROR: syntax error at or near "OFFSET" LINE 1: ...R recipe_name LIKE 'a%' ORDER BY recipe_name LIMIT OFFSET 0 ^ I fully expect there will be more after I get these corrected. My setup is as follows: Slackware 10.2 Apache 1.3.37 PHP 4.4.5 PostgreSQL 8.2.3 Thank you, Bob McConnell N2SPP
Bob McConnell wrote: > Good morning, > > I have been setting up a server at home as a workbench to learn PHP, > Postgres, and a few other tools. I recently installed an application > (PHPRecipeBook) that was written for Postgres 7.2.3. But I have 8.2.3 > installed. When I try to access the recipe book, I get a few errors; > surprise, surprise. Is there any way to identify what adjustments I > need to make in the queries to compensate for the difference in target > versions? My dream option would be a form in which I enter the > original target and the new target and get back a list of all the > syntax changes I need to account for. I started to walk through the > release notes, but there are far too many changes spread too widely to > make sense of. > > The first level errors I get are: > > SELECT count(*) FROM recipe_recipes WHERE recipe_name LIKE 'A%' OR > recipe_name LIKE 'a%' ORDER BY recipe_name > ERROR: column "recipe_recipes.recipe_name" must appear in the GROUP BY > clause or be used in an aggregate function > I'm not sure, but I don't think the statement was ever correct SQL. In any case, you would need to add a GROUP BY clause. > SELECT recipe_id,recipe_name FROM recipe_recipes WHERE recipe_name > LIKE 'A%' OR recipe_name LIKE 'a%' ORDER BY recipe_name LIMIT OFFSET 0 > ERROR: syntax error at or near "OFFSET" LINE 1: ...R recipe_name LIKE > 'a%' ORDER BY recipe_name LIMIT OFFSET 0 ^ > Again, I don't think this was ever correct. > I fully expect there will be more after I get these corrected. My > setup is as follows: I haven't used phprecipebook before, but it looks it is generating quite incorrect SQL queries. If it was truly written for version 7.2.3, that means that it probably hasn't been updated in several years, so you might want to look seriously at what phprecipebook offers compared to alternatives. I really know nothing about the software, so I could be very off base, but the SQL errors you are getting are pretty basic. Sean
Sean Davis wrote: > Bob McConnell wrote: >> Good morning, >> >> >> The first level errors I get are: >> >> SELECT count(*) FROM recipe_recipes WHERE recipe_name LIKE 'A%' OR >> recipe_name LIKE 'a%' ORDER BY recipe_name >> ERROR: column "recipe_recipes.recipe_name" must appear in the GROUP BY >> clause or be used in an aggregate function >> > I'm not sure, but I don't think the statement was ever correct SQL. In > any case, you would need to add a GROUP BY clause. >> SELECT recipe_id,recipe_name FROM recipe_recipes WHERE recipe_name >> LIKE 'A%' OR recipe_name LIKE 'a%' ORDER BY recipe_name LIMIT OFFSET 0 >> ERROR: syntax error at or near "OFFSET" LINE 1: ...R recipe_name LIKE >> 'a%' ORDER BY recipe_name LIMIT OFFSET 0 ^ >> > Again, I don't think this was ever correct. >> I fully expect there will be more after I get these corrected. My >> setup is as follows: > > I haven't used phprecipebook before, but it looks it is generating quite > incorrect SQL queries. If it was truly written for version 7.2.3, that > means that it probably hasn't been updated in several years, so you > might want to look seriously at what phprecipebook offers compared to > alternatives. I really know nothing about the software, so I could be > very off base, but the SQL errors you are getting are pretty basic. > > Sean Sean, Thanks for the help, looks like I have my work cut out for me. PHPRecipeBook was written for MySQL, but added Postgres as an apparent afterthought. They have not updated the PG side for some time, but they claim it did work with 7.2.3. It would appear they have been seduced by someone with a bias in the other direction. As far as other options, I haven't found any. There are packages with fat clients written for KDE (KRecipes) or GNOME (Gourmet) users, but I have not found anything useful with a web front end. I'm not sure this is yet, but it's all I could find to try out. I just want something useful to organize a few thousand family recipes before they are lost. My mother just turned 82 and we have most of hers stored on paper. But we have already lost a couple of her and dad's sisters with no records of theirs. I haven't even begun to talk with my wife's family, and one daughter in law whose grandmother and aunts used to operate a catering kitchen. Thank you, Bob McConnell N2SPP
Bob McConnell <rmcconne@lightlink.com> writes:
> Sean Davis wrote:
>> I'm not sure, but I don't think the statement was ever correct SQL. In
>> any case, you would need to add a GROUP BY clause.
> Thanks for the help, looks like I have my work cut out for me.
> PHPRecipeBook was written for MySQL, but added Postgres as an apparent
> afterthought. They have not updated the PG side for some time, but they
> claim it did work with 7.2.3. It would appear they have been seduced by
> someone with a bias in the other direction.
Hm, these examples appear to shed no credit on either PHPRecipeBook or
MySQL. Some comments:
* The first query (SELECT count(*) ... ORDER BY with no GROUP BY) is
surely pretty silly, because an aggregate function without GROUP BY
is going to yield only one row, so what's the point of ORDER BY?
It's not legal by my reading of the SQL spec, yet according to my test
just now mysql 5.0.22 takes it (1 demerit for mysql, maybe 2 because
this is a recent release and they still haven't fixed it) ... even in
sql_mode=ansi (make that 3 demerits). Also, pgsql 7.2 certainly didn't
take it (I checked 7.0 too, the oldest version still alive hereabouts),
so 1 demerit for PHPRecipeBook who obviously didn't test this query on
anything but mysql.
* 'LIMIT OFFSET 0' bombs in both mysql and Postgres, and always has
AFAIK --- there's supposed to be a numeric argument to the LIMIT.
1 demerit to PHPRecipeBook for being just plain broken.
> As far as other options, I haven't found any. There are packages with
> fat clients written for KDE (KRecipes) or GNOME (Gourmet) users, but I
> have not found anything useful with a web front end. I'm not sure this
> is yet, but it's all I could find to try out.
Seems like you need to be prepared to do a bit of work on PHPRecipeBook.
These things don't sound real hard to fix if you know a little SQL
... the real question is can you get upstream to take back the patches?
It's no fun fixing bugs if you just have to fix 'em again in the next
release.
> I just want something
> useful to organize a few thousand family recipes before they are lost.
> My mother just turned 82 and we have most of hers stored on paper. But
> we have already lost a couple of her and dad's sisters with no records
> of theirs. I haven't even begun to talk with my wife's family, and one
> daughter in law whose grandmother and aunts used to operate a catering
> kitchen.
Sounds a bit familiar ... my wife spent a lot of time a few years ago
trying to organize recipes from her father's and grandfather's bakery
business. Strangely, she had no interest in putting 'em into a
database.
regards, tom lane
Tom Lane wrote: > Bob McConnell <rmcconne@lightlink.com> writes: > Seems like you need to be prepared to do a bit of work on PHPRecipeBook. > These things don't sound real hard to fix if you know a little SQL > ... the real question is can you get upstream to take back the patches? > It's no fun fixing bugs if you just have to fix 'em again in the next > release. It has been over a year since this release, and it doesn't look like there is any current activity. The last news item was pointing to a new package the maintainer was working on, so I don't know if there will be any more releases. If I proceed, I will post what I find on the forum at Source Forge just in case. >> I just want something >> useful to organize a few thousand family recipes before they are lost. >> My mother just turned 82 and we have most of hers stored on paper. But >> we have already lost a couple of her and dad's sisters with no records >> of theirs. I haven't even begun to talk with my wife's family, and one >> daughter in law whose grandmother and aunts used to operate a catering >> kitchen. > > Sounds a bit familiar ... my wife spent a lot of time a few years ago > trying to organize recipes from her father's and grandfather's bakery > business. Strangely, she had no interest in putting 'em into a > database. Probably just a difference in perspective. I see the database as a means to enforce consistent content and layout. Add to that the fact that I sometimes cannot read my own handwriting, and it begins to make some sense. I can also use common export programs to create copies of the collection suitable for burning to CD that I can send to any number of siblings, cousins and their descendants. So for me the DB is a useful tool for this project as long as I don't need to design the schema from scratch. She may not see it the same way. Thanks for the suggestions. I am adding SQL to the list of topics I need to study further. So far I know just enough to examine a few tables using the Sybase ISQL tools with ASA. Probably not enough to get into trouble yet, but getting there. Bob McConnell N2SPP