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 2021
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 2021
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 2021
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 2021
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 2021
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 2021
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 2021
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 2021
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 2021
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 2021
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 2021
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 2021