www.digitalmars.com         C & C++   DMDScript  

digitalmars.D.learn - How to execute a random postgresql-query.

reply Alain De Vos <devosalain ymail.com> writes:
How to execute a random postgresql-query ?
With random i mean execute any string as known by postgresql.


void myexecutefunction(string string_to_execute){
// Some code to Execute postgre-sq-string
}

...

void main(){
myexecutefunction("CREATE DATABASE test");
myexecutefunction("CREATE USER test");
...
myexecutefunction("SELECT * FROM testtable");
}
Jun 28
next sibling parent reply neuranuz <neuranuz gmail.com> writes:
On Monday, 28 June 2021 at 19:16:40 UTC, Alain De Vos wrote:
 How to execute a random postgresql-query ?
 With random i mean execute any string as known by postgresql.


 void myexecutefunction(string string_to_execute){
 // Some code to Execute postgre-sq-string
 }

 ...

 void main(){
 myexecutefunction("CREATE DATABASE test");
 myexecutefunction("CREATE USER test");
 ...
 myexecutefunction("SELECT * FROM testtable");
 }
Hello! If you want to make it "from scratch", then you basically need to install libpq library on your system. Then add some function bindings of basic methods of this library like there (this is my mode ;-)): https://github.com/uranuz/webtank/blob/master/src/webtank/db/postgresql/bindings.d Then you need to link libpq to your project. You could use *dub* options for instance to do it. Establishing connection to database and making queries is described there: https://github.com/uranuz/webtank/blob/master/src/webtank/db/postgresql/database.d And getting data from result is described there: https://github.com/uranuz/webtank/blob/master/src/webtank/db/postgresql/query_result.d You could also try to find some ready to use bindings to PostgreSQL on code.dlang.org. I haven't published my code on code.dlang yet, because it's not enough time ;-(
Jun 29
parent reply someone <someone somewhere.com> writes:
On Tuesday, 29 June 2021 at 17:56:54 UTC, neuranuz wrote:

 You could also try to find some ready to use bindings to 
 PostgreSQL on code.dlang.org.
Can you elaborate why you went your own way coding your own bindings to pglib instead of using the existing ones ? Is there something wrong with the existing ones ? Do they lack features ? Are them over-engineered ? I didn't look at the code of the existing ones yet because I am knee-deep learning the language still, but since I will eventually need to access postgreSQL from D, your experience will help me for sure.
 I haven't published my code on code.dlang yet, because it's not 
 enough time ;-(
I did see your code; it looks pretty straight-forward, indeed.
Jun 29
next sibling parent reply Alain De Vos <devosalain ymail.com> writes:
On Tuesday, 29 June 2021 at 18:25:52 UTC, someone wrote:
 On Tuesday, 29 June 2021 at 17:56:54 UTC, neuranuz wrote:

 You could also try to find some ready to use bindings to 
 PostgreSQL on code.dlang.org.
Can you elaborate why you went your own way coding your own bindings to pglib instead of using the existing ones ? Is there something wrong with the existing ones ? Do they lack features ? Are them over-engineered ? I didn't look at the code of the existing ones yet because I am knee-deep learning the language still, but since I will eventually need to access postgreSQL from D, your experience will help me for sure.
 I haven't published my code on code.dlang yet, because it's 
 not enough time ;-(
I did see your code; it looks pretty straight-forward, indeed.
Some implementations take all queries full in memory. But what when i need one record. And the select result could be huge.
Jun 29
next sibling parent reply Alain De Vos <devosalain ymail.com> writes:
On Tuesday, 29 June 2021 at 20:27:15 UTC, Alain De Vos wrote:
 On Tuesday, 29 June 2021 at 18:25:52 UTC, someone wrote:
 On Tuesday, 29 June 2021 at 17:56:54 UTC, neuranuz wrote:

 You could also try to find some ready to use bindings to 
 PostgreSQL on code.dlang.org.
Can you elaborate why you went your own way coding your own bindings to pglib instead of using the existing ones ? Is there something wrong with the existing ones ? Do they lack features ? Are them over-engineered ? I didn't look at the code of the existing ones yet because I am knee-deep learning the language still, but since I will eventually need to access postgreSQL from D, your experience will help me for sure.
 I haven't published my code on code.dlang yet, because it's 
 not enough time ;-(
I did see your code; it looks pretty straight-forward, indeed.
Some implementations take all queries full in memory. But what when i need one record. And the select result could be huge.
And we don't want swapping for this?
Jun 29
parent apz28 <home home.com> writes:
On Tuesday, 29 June 2021 at 20:35:07 UTC, Alain De Vos wrote:
 Some implementations take all queries full in memory.
 But what when i need one record.
 And the select result could be huge.
And we don't want swapping for this?
You can try my db package https://github.com/apz28/dlang/tree/main/source/pham/db For Postresql, it is in "pham.db.pgdatabase" module. The document is a bit lacking but if you open that module and search for "executeReader();" for sample usage. The implementation follow .NET interface and currently it is working for both Firebird and Postgresql database engines. You need to download entire D package: https://github.com/apz28/dlang Happy Codings
Jun 29
prev sibling parent someone <someone somewhere.com> writes:
On Tuesday, 29 June 2021 at 20:27:15 UTC, Alain De Vos wrote:

 Some implementations take all queries full in memory.
 But what when i need one record.
 And the select result could be huge.
If the result is huge and you just need one record ... ain't you coding the query wrong to begin with ? Select Top 1 ... From ... Where ... blah blah blah
Jun 29
prev sibling parent reply SealabJaster <sealabjaster gmail.com> writes:
On Tuesday, 29 June 2021 at 18:25:52 UTC, someone wrote:
 Can you elaborate why you went your own way coding your own 
 bindings to pglib instead of using the existing ones ?
Not who you were asking but: they just don't work. Maybe they don't support Windows for one reason or another. Maybe their abstraction for whatever reason isn't able to handle basic types for the database backend (true story). Maybe they have weird, non-nonsensical errors, or for some reason just can't connect when nothing else is wrong. And maybe, sometimes they decide to just crash for random reasons. e.g. I tried to use the Hunt database library recently, and it's dtor would crash because it was missing a null check or something. So I (and likely others) decide to just write our own. In my own case I just wanted to run super simple queries with super simple results, so made a very tiny wrapper around postgres' C library: https://github.com/BradleyChatha/dubstats2/blob/master/d/database/source/database.d I used a tool called DStep to automatically convert the headers I needed over to D, and with a few manual tweaks, everything just worked. No hassle, no random shenanigans, I could actually _start the project_ instead of wrestling with 20 different libraries. There's a comment in the top of that file that describes my general feelings towards most dub libraries. If you don't stick with the top libraries, you're basically in a barren wasteland in terms of "will this shit even work?" and "I could've just written my own version in the hours/days it's taken me to get this damn thing to do anything".
Jun 29
parent someone <someone somewhere.com> writes:
On Tuesday, 29 June 2021 at 23:32:21 UTC, SealabJaster wrote:

 Not who you were asking but: they just don't work.
Doesn't matter, you're welcome :)
 Maybe they don't support Windows for one reason or another.

 Maybe their abstraction for whatever reason isn't able to 
 handle basic types for the database backend (true story).

 Maybe they have weird, non-nonsensical errors, or for some 
 reason just can't connect when nothing else is wrong.

 And maybe, sometimes they decide to just crash for random 
 reasons. e.g. I tried to use the Hunt database library 
 recently, and it's dtor would crash because it was missing a 
 null check or something.
I would love to see D as a first-class citizen on postgreSQL like C ... just dreaming of. My first impressions so far (which I will eventually post here) are in the line of: terrific language, really-powerful, a joy to code with. So dreaming of seeing it integrated to something like postgreSQL is like ... well, faaantastic. I can imagine writing very sophisticated stored-procedures right within-the-database. Man, a killer feature, well-ahead of C.
 So I (and likely others) decide to just write our own. In my 
 own case I just wanted to run super simple queries with super 
 simple results, so made a very tiny wrapper around postgres' C 
 library: 
 https://github.com/BradleyChatha/dubstats2/blob/master/d/database/source/database.d
What do you think of the possibility of making a coordinated effort to tackle a definitive pglib binding to D providing we start with very-well defined requirements and not going farther than needed to begin with ? I think we could start with x basic access, then make something intermediate access (not even touching the basic one), then advanced, whatever. I mean, not leaving the users of basic access spoiled when we are working a few steps higher and making the development mistakes every component will bring in. Just thinking.
 I used a tool called DStep to automatically convert the headers 
 I needed over to D, and with a few manual tweaks, everything 
 just worked. No hassle, no random shenanigans, I could actually 
 _start the project_ instead of wrestling with 20 different 
 libraries.
 There's a comment in the top of that file that describes my 
 general feelings towards most dub libraries.
Just seen it. When I stop coding for a while (I mean, learning D with my test-bed demo -not even a project yet), I use to browse the forum more-or-less in random order to take a glimpse of how things work (or not) in D-land; and I encountered lots of complains like you say for dub libraries and the like, so I suppose taking away some very few major-ones they tend to be poor quality at the least, not a complain of mine, to be clear, just stating what I am seeing.
 If you don't stick with the top libraries, you're basically in 
 a barren wasteland in terms of "will this shit even work?" and 
 "I could've just written my own version in the hours/days it's 
 taken me to get this damn thing to do anything".
Top-libraries or else ... crystal-clear.
Jun 29
prev sibling next sibling parent someone <someone somewhere.com> writes:
On Monday, 28 June 2021 at 19:16:40 UTC, Alain De Vos wrote:
 How to execute a random postgresql-query ?
 With random i mean execute any string as known by postgresql.


 void myexecutefunction(string string_to_execute){
 // Some code to Execute postgre-sq-string
 }

 ...

 void main(){
 myexecutefunction("CREATE DATABASE test");
 myexecutefunction("CREATE USER test");
 ...
 myexecutefunction("SELECT * FROM testtable");
 }
https://code.dlang.org/packages/dpq2
Jun 29
prev sibling parent reply neuranuz <neuranuz gmail.com> writes:
On Monday, 28 June 2021 at 19:16:40 UTC, Alain De Vos wrote:
 How to execute a random postgresql-query ?
 With random i mean execute any string as known by postgresql.


 void myexecutefunction(string string_to_execute){
 // Some code to Execute postgre-sq-string
 }

 ...

 void main(){
 myexecutefunction("CREATE DATABASE test");
 myexecutefunction("CREATE USER test");
 ...
 myexecutefunction("SELECT * FROM testtable");
 }
In case of my implentation the usage is: auto db = new DBPostgreSQL("host=hostname port=5432 dbname=databasename user=username password=secret"); auto qres = db.query("select first_name, second_name from person"); string first_name = qres.get(0, 0, null); string second_name = qres.get(1, 0, null); It's pretty simple;-) There is also second function "queryParamsArray" that helps to pass query parameters in safe manner without string concatenation: string[] first_names = ["John", "Sarah"]; string[] second_names = ["Doe", "Connor"]; db.queryParamsArray("with inp as( select unnest($1::text[]) first_name, unnest($2::text[]) second_name ) insert into person (first_name, second_name) select inp.* from inp ");
Jun 29
parent neuranuz <neuranuz gmail.com> writes:
 insert into person (first_name, second_name)
 select inp.* from inp
 ");
The last strings should be: insert into person (first_name, second_name) select inp.* from inp ", first_names, last_names);
Jun 29