Avatar

No postgresql? (Features)

by pdp-7, Sunday, April 21, 2019, 16:44 (33 days ago)

Hi!

I'm not sure if it's some kind of taboo or something but there is no psql version of this brilliant little forum?

Would fit like a clove to one project of mine if only ...

Thanks!

Avatar

No postgresql?

by Alfie ⌂ @, Vienna, Austria, Sunday, April 21, 2019, 23:24 (33 days ago) @ pdp-7

Hi!

I'm not sure if it's some kind of taboo or something but there is no psql version of this brilliant little forum?

Of course, it's not a taboo - you were just the first one asking. ;-)

On many Apaches the PostgreSQL module is not active simply cause the hoster does not provide PostgreSQL databases... mlf is open source. Hence you are free to fork your own version. Be warned: Expect to translate ~400 LOC with mysql-functions into their respective counterparts.

--
Best regards,
Alfie (Helmut Schütz)
BEBA-Forum (v1.8β)

Avatar

No postgresql?

by pdp-7, Monday, April 22, 2019, 03:34 (33 days ago) @ Alfie

Thanks for the reply!

If only one got time to tinker with the code at the moment, but places to do and things to go you see. That's why asked if there were one already :)

I'll have to use time in order to see if there is time to use.

Avatar

No postgresql.

by Auge ⌂ @, Monday, April 22, 2019, 19:54 (32 days ago) @ pdp-7

Hello

I'm not sure if it's some kind of taboo or something but there is no psql version of this brilliant little forum?

As Alfie stated before, there is no PostgreSQL-version of My Little Forum. It's because there was never a need for it and – as far as I know – no one of us has a webspace or webserver with a PostgreSQL database server installed on it.

I looked into the PHP-manual section for the PostgreSQL module and I think it should in general be manageable to alter all calls of database functions from the mysqli- to the pg-module. But at that point one will be trapped to alter every new version or to get stuck to the one version one altered because of the effort to follow the changes in the main repository.

Some years ago there was the question to build a version for SQLite. I could also build a version for MS SQL Server because I work with it at work and I think I know, how to do it. In all cases we would hit the same trap I decribed above. The only chance I can imagine to circumvent this trap would be to encapsulate every function call related to a database communication in neutral functions which could exist in several editions for different database systems under the same name or as functions, that can handle the calls to different database systems in itself. A third (and IMHo the best) way would be to move to the PDO class that is (again in general) database system neutral.

All of the three ways are not subject of this project. Only if someone comes with a reasonable draft or (better) a production ready pull request we can discuss the proposed way.

Tschö, Auge

--
Trenne niemals Müll, denn er hat nur eine Silbe!

Avatar

No postgresql.

by pdp-7, Wednesday, April 24, 2019, 07:48 (30 days ago) @ Auge

All of the three ways are not subject of this project. Only if someone comes with a reasonable draft or (better) a production ready pull request we can discuss the proposed way.

True all that what you say about the maintenance and such it's all the time tightrope walking with the features and resources such.

I was kinda surprised that there was no talk about one on the forum either. But then again data bases are something I have grown hate to since 90's :)

Like now I have situation where I can't deploy this little forum on the project I'm working on, since server runs just Postgres and only Postgres.

But like stated earlier that's not your fault. It's the system that is broken :)

I'll continue hangaround on the forum and might install this forum somewhere in the future.

--
MOV AX,5301
XOR BX,BX
INT 15
MOV AX,530E
XOR BX,BX
MOV CX,0102
INT 15
MOV AX,5307
MOV BX,0001
MOV CX,0003
INT 15
RET

Avatar

No postgresql.

by pdp-7, Tuesday, May 14, 2019, 16:07 (10 days ago) @ Auge

I have talked with person who know more about the MySQL-Postgres compatibility and he suggested that it might not be that difficult to make it work on Postgres. My knowledge about these things are so limited and basically outdated 20+ years that I can't tell :D

Back then promised myself not to get involved nay databases any more in the way I did some projects back then. Now I noticed that I cannot even write basic queries and that felt good.. so good.

So I'll get back to you if something brews up.

--
MOV AX,5301
XOR BX,BX
INT 15
MOV AX,530E
XOR BX,BX
MOV CX,0102
INT 15
MOV AX,5307
MOV BX,0001
MOV CX,0003
INT 15
RET

Avatar

No postgresql.

by Micha ⌂, Wednesday, May 15, 2019, 06:34 (9 days ago) @ pdp-7

Hi,

I think, we should use generic SQL functions as suggested by Auge but only supports MySQL. As we changed from mysql to the new mysqli functions, we changed a lot of sources. Here, a generic function would have reduced the effort...

/Micha

--
applied-geodesy.org - OpenSource Least-Squares Adjustment Software for Geodetic Sciences

Avatar

No postgresql.

by Auge ⌂ @, Wednesday, May 15, 2019, 09:04 (9 days ago) @ Micha

Hello

I think, we should use generic SQL functions as suggested by Auge but only supports MySQL. As we changed from mysql to the new mysqli functions, we changed a lot of sources. Here, a generic function would have reduced the effort...

Do you mean something like that? Beside the fact that we would need functions for every supported database system, the task to maintain the queries for every supported database system. Even when we would switch to PDO – what would provide a unified API for every database system – the task to nurse the differing query syntax would remain.

Nothing I want to do.

Tschö, Auge

--
Trenne niemals Müll, denn er hat nur eine Silbe!

Avatar

No postgresql.

by Micha ⌂, Wednesday, May 15, 2019, 09:29 (9 days ago) @ Auge

Hi,

Beside the fact that we would need functions for every supported database system,

No. As mentioned, _we_ only support mysql.

we would switch to PDO

This is equivalent since it is a generic function.

the task to nurse the differing query syntax would remain.

If we restrict our self to the standard SQL, each DBMS is compatible to each other, isn't it?

/Micha

--
applied-geodesy.org - OpenSource Least-Squares Adjustment Software for Geodetic Sciences

Avatar

No postgresql.

by Auge ⌂ @, Wednesday, May 15, 2019, 10:49 (9 days ago) @ Micha

Hello

If we restrict our self to the standard SQL, each DBMS is compatible to each other, isn't it?

I would not expect it to work.

Example:

Task: return datasets 26 to 50 for a paginated listing

MySQL (non standard, we use it):

SELECT * 
FROM TABLE
LIMIT 26, 25

PostgreSQL (non standard):

SELECT *
FROM TABLE
LIMIT 25 OFFSET 26;

MS SQL (>= 2012, don't know if this is standard):

SELECT *
FROM TABLE OFFSET 26 ROWS FETCH NEXT 25 ROWS ONLY

MS SQL (<2012, seems to be an ugly workaround):

 
-- taken from https://stackoverflow.com/questions/9013177/mysql-limit-clause-equivalent-for-sql-server
WITH o AS
(
    SELECT TOP ((26 - 1) + 25)
      RowNum = ROW_NUMBER() OVER (ORDER BY ID ASC)
    FROM TABLE
)
SELECT 
    RowNum
    /* , other columns */
FROM
    o
WHERE
    RowNum >= 26
ORDER BY
    RowNum;

This is only one example for a function, we use with different solutions in different implementations. As stated: I would not expect it to work.

Tschö, Auge

--
Trenne niemals Müll, denn er hat nur eine Silbe!

Avatar

No postgresql.

by pdp-7, Thursday, May 16, 2019, 07:37 (8 days ago) @ Auge

Example:

Task: return datasets 26 to 50 for a paginated listing

*Bunch of SQL that hurt my eyes removed :)

Just wondering if that kind of pagination would be possible to do by other mean than with SQL? Have no idea about your design so this might not be an option.

I mean use simple SQL and roll that data in one by one and not in chunk like in your example? Would make SQL simple and PHP does the magic of 26 to 50 ..

Probably talking complete nonsense here. But just a thought that popped into my mind.

I'll get back to you when know more about the matter of conversion

--
MOV AX,5301
XOR BX,BX
INT 15
MOV AX,530E
XOR BX,BX
MOV CX,0102
INT 15
MOV AX,5307
MOV BX,0001
MOV CX,0003
INT 15
RET

Avatar

“Substitute” SQL’s statements by PHP

by Alfie ⌂ @, Vienna, Austria, Thursday, May 16, 2019, 09:17 (8 days ago) @ pdp-7

Hi pdp-7,

Just wondering if that kind of pagination would be possible to do by other mean than with SQL? [… ] I mean use simple SQL and roll that data in one by one and not in chunk like in your example? Would make SQL simple and PHP does the magic of 26 to 50 ..

Theoretically yes. In my forum I have ~20,000 posts (I guess there are much larger installations). If you would return all into an array in PHP and do the job there, it would be much less efficient. In the worst case you’ll run into memory issues. I would not even try to substitute the SQL’s JOIN-statement in PHP…
SQL is fast cause it is optimized for this kind business (not at least by using indexed fields). PHP would be terribly slooow.

--
Best regards,
Alfie (Helmut Schütz)
BEBA-Forum (v1.8β)

Avatar

“Substitute” SQL’s statements by PHP

by pdp-7, Thursday, May 16, 2019, 09:45 (8 days ago) @ Alfie

Theoretically yes. In my forum I have ~20,000 posts (I guess there are much larger installations). If you would return all into an array in PHP and do the job there, it would be much less efficient. In the worst case you’ll run into memory issues. I would not even try to substitute the SQL’s JOIN-statement in PHP…
SQL is fast cause it is optimized for this kind business (not at least by using indexed fields). PHP would be terribly slooow.

Of course you don't suck ALL from the DB and start crunching that BIG blob of data.

What I meant that your PHP controls the pagination in the way that you have this 26 to 50 window you want those posts, as mentioned earlier. But you get those titles one by one from 26 until count is 50 so that way SQL would be much simpler and you just need to ++i and then send that request to DB...

So first it gets number 26 then in PHP ++i and then sql request for 27 and so on ...

Hope this makes more sense. But like I said before I have no clues how you have solved things in your code so this might be too big change to do.

But just popped into my mind.

--
MOV AX,5301
XOR BX,BX
INT 15
MOV AX,530E
XOR BX,BX
MOV CX,0102
INT 15
MOV AX,5307
MOV BX,0001
MOV CX,0003
INT 15
RET

Avatar

“Substitute” SQL’s statements by PHP

by Auge ⌂ @, Thursday, May 16, 2019, 10:08 (8 days ago) @ pdp-7

Hello

What I meant that your PHP controls the pagination in the way that you have this 26 to 50 window you want those posts, as mentioned earlier. But you get those titles one by one from 26 until count is 50 so that way SQL would be much simpler and you just need to ++i and then send that request to DB...

So first it gets number 26 then in PHP ++i and then sql request for 27 and so on ...

No, that would not work well. From where should the PHP-script know, which datasets it should request? Should the script executing additional requests to know, which datasets to request? No, this is a task for the database system.

Hope this makes more sense.

No, it doesn't.

Tschö, Auge

--
Trenne niemals Müll, denn er hat nur eine Silbe!

Avatar

“Substitute” SQL’s statements by PHP

by pdp-7, Thursday, May 16, 2019, 16:13 (8 days ago) @ Auge

No, that would not work well. From where should the PHP-script know, which datasets it should request? Should the script executing additional requests to know, which datasets to request? No, this is a task for the database system.

Well.

You know what is the # of your last shown entry .. then just start pulling entries from the next entry and get those entries one by one until your counter is again 25 and the stop.

And when you want next page it's just the same again. Get 25 from the next entry that is the last one shown in pagination.

I'm just juggling with this thought here and don't have any concrete. As said haven't been doing SQL in 20+ years and it shows, doesn't it? And even back then it was more like driving nails into my head ...

My logic might be completely incompatible with SQL which I remember from back then ...

This probably cannot be done and doesn't make sense as you said.

--
MOV AX,5301
XOR BX,BX
INT 15
MOV AX,530E
XOR BX,BX
MOV CX,0102
INT 15
MOV AX,5307
MOV BX,0001
MOV CX,0003
INT 15
RET

RSS Feed of thread
powered by my little forum