Sign in Join

Live chat for Free Pascal, Lazarus, Delphi and every Pascal programmer.

Mailing lists / lazarus / 2008-04

Handy script for Firebird users

11 messages · 7 people · –

Reply by email Subscribe to this list Your subscription

Read-only archive. Use j and k to move between messages.

Graeme Geldenhuys

Hi,

I created a script (Unix/Linux only at the moment) called 'mkdb' for use with our application deployment. We use the Firebird RDBMS and thought this might come in handy for others as well.

It creates a Firebird database, creates the tables. Optionally it creates Views, applies table rights and inserts default data. All database information is read from sql script files: create.sql, views.sql, rights.sql, defaults.sql

The script (mkdb) also takes three optional parameters:
- database location
- database username
- database password

I hope someone finds this useful. :)

Regards,
- Graeme -

_______________________________________________________ fpGUI - a cross-platform GUI toolkit using Free Pascal http://opensoft.homeip.net/fpgui/

-------------- next part --------------
An embedded and charset-unspecified text was scrubbed... Name: mkdb
Url: http://www.lazarus.freepascal.org/pipermail/lazarus/attachments/20080430/ec28dca8/attachment-0001.pl

Reply by email

Joost van der Sluis
to Graeme Geldenhuys

And you didn't write it in Pascal because.... ?

Joost

Op woensdag 30-04-2008 om 11:28 uur [tijdzone +0200], schreef Graeme Geldenhuys:

Show quoted text 148 lines
Hi, I created a script (Unix/Linux only at the moment) called 'mkdb' for use with our application deployment. We use the Firebird RDBMS and thought this might come in handy for others as well. It creates a Firebird database, creates the tables. Optionally it creates Views, applies table rights and inserts default data. All database information is read from sql script files: create.sql, views.sql, rights.sql, defaults.sql The script (mkdb) also takes three optional parameters: - database location - database username - database password I hope someone finds this useful. :) Regards, - Graeme - _______________________________________________________ fpGUI - a cross-platform GUI toolkit using Free Pascal http://opensoft.homeip.net/fpgui/ platte tekst document bijlage (mkdb) #!/bin/sh # # Script to create a Firebird database, apply permissions and # to fill it with data. # # The script accepts optional arguments: # - A database to connect to. (default is setup below) # - Database username (default 'SYSDBA') # - Database password (default 'masterkey') # # The script looks for the following sql scrip files and executes them # - create.sql (mandatory) # - views.sql (optional) # - rights.sql (optional) # - defaults.sql (optional) # # Collect the database database="localhost:/home/graemeg/programming/data/m2empty.fdb" #database=192.168.0.1:M2 if [ -n "$1" ]; then database=$1 fi # Database login information user=SYSDBA if [ -n "$2" ]; then user=$2 fi passwd=masterkey if [ -n "$3" ]; then passwd=$3 fi # Choose one of the following: ISQL="isql -q -d $database -u $user -p $passwd" #ISQL="/opt/firebird/bin/isql -q -d $database -u $user -p $passwd" ############################################################## # # You shouldn't need to edit after this. # echo -n "Creating the database $database ..." ${ISQL} << EOF set sql dialect 3; CREATE DATABASE "$database"; commit; EOF if [ ! $? = 0 ]; then echo "Failed." else echo "Done." fi echo -n "Creating the tables in database ..." ${ISQL} << EOF connect '$database' user '$user' password '$passwd'; input create.sql; commit; EOF if [ ! $? = 0 ]; then echo "Failed." else echo "Done." fi if [ -e ./views.sql ]; then echo -n "Creating the views in database ..." ${ISQL} << EOF connect '$database' user '$user' password '$passwd'; input views.sql; commit; EOF if [ ! $? = 0 ]; then echo "Failed." else echo "Done." fi fi if [ -e ./rights.sql ]; then echo -n "Applying table permissions ..." ${ISQL} << EOF connect '$database' user '$user' password '$passwd'; input rights.sql; commit; EOF if [ ! $? = 0 ]; then echo "Failed." else echo "Done." fi fi if [ -e ./defaults.sql ]; then echo -n "Inserting default data ..." ${ISQL} << EOF connect '$database' user '$user' password '$passwd'; input defaults.sql; commit; EOF if [ ! $? = 0 ]; then echo "Failed." else echo "Done." fi fi # END _______________________________________________ Lazarus mailing list http://www.lazarus.freepascal.org/mailman/listinfo/lazarus

Reply by email

Graeme Geldenhuys
to Joost van der Sluis
Joost van der Sluis wrote
And you didn't write it in Pascal because.... ? Joost

Because the deployment PC's will not have FPC on them. Ummm... Then again, it takes all the parameters it needs for customization. Also, if I make it a console app, it will be cross-platform to. :)

How to you handle standard input like was done in the script?

------------copy-------------
echo -n "Creating the database $database ..."
${ISQL} << EOF
set sql dialect 3;
CREATE DATABASE "$database";
commit;
EOF
if [ ! $? = 0 ]; then
echo "Failed."
else
echo "Done."
fi
------------end-------------

Regards,
- Graeme -

_______________________________________________________ fpGUI - a cross-platform GUI toolkit using Free Pascal http://opensoft.homeip.net/fpgui/

Reply by email

Henry Vermaak
to Graeme Geldenhuys

2008/4/30 Graeme Geldenhuys :

Show quoted text 26 lines
Joost van der Sluis wrote: > And you didn't write it in Pascal because.... ? > > Joost Because the deployment PC's will not have FPC on them. Ummm... Then again, it takes all the parameters it needs for customization. Also, if I make it a console app, it will be cross-platform to. :) How to you handle standard input like was done in the script? ------------copy------------- echo -n "Creating the database $database ..." ${ISQL} << EOF set sql dialect 3; CREATE DATABASE "$database"; commit; EOF if [ ! $? = 0 ]; then echo "Failed." else echo "Done." fi ------------end-------------

it's just feeding the sql program a string (heredoc), then checking the result. you don't need to do anything special to transcribe this to pascal :)

henry

Reply by email

Michael Van Canneyt
to Graeme Geldenhuys: Because the deployment PC's will not have FPC on them. Ummm... Then again, it takes all…
Graeme Geldenhuys wrote 11 lines
Joost van der Sluis wrote: > And you didn't write it in Pascal because.... ? > > Joost Because the deployment PC's will not have FPC on them. Ummm... Then again, it takes all the parameters it needs for customization. Also, if I make it a console app, it will be cross-platform to. :) How to you handle standard input like was done in the script?

Use TProcess ?

Michael.

Reply by email

Tom York
to Graeme Geldenhuys: I created a script (Unix/Linux only at the moment) called 'mkdb' for use with our…

Graeme,

For what it is worth, I think it is COOL! Having deployed FB on quite a few Linux servers, I can certainly see the benefit without question.

Graeme, what is the largest FB concurrent user install you have running? My largest desktop application is 15 users. Can't tell you the count on web users. In almost 10 years of use, I've never had corruption either.

The only things I do not like about FB is it lacks SSL, AutoIncrement attribute and Boolean fields. We've all managed to get by them, but I wish... :-)

Great job on the utility!
Tom

Graeme Geldenhuys wrote 6 lines
Hi, I created a script (Unix/Linux only at the moment) called 'mkdb' for use with our application deployment. We use the Firebird RDBMS and thought this might come in handy for others as well.

Reply by email

Leonardo M. Ramé
to Tom York

This is off topic of course, but...

You can use Generators (Sequences in other systems) joined to a trigger instead of AutoIncrement.
You can simulate Boolean fields by creating a Domain.

Leonardo M. Ram?
http://leonardorame.blogspot.com

Tom York escribi?:

The only things I do not like about FB is it lacks SSL, AutoIncrement attribute and Boolean fields. We've all managed to get by them, but I wish... :-)

Reply by email

Graeme Geldenhuys
to Tom York: For what it is worth, I think it is COOL! Having deployed FB on quite a few Linux…

2008/4/30 Tom York :

a few Linux servers, I can certainly see the benefit without question.

Thank you! :)

Graeme, what is the largest FB concurrent user install you have running? My largest desktop application is 15 users. Can't tell you

We have on average 30 concurrent users in our system.

The only things I do not like about FB is it lacks SSL, AutoIncrement attribute and Boolean fields. We've all managed to get by them, but I wish... :-)

I hate AutoIncrement and simply don't use them. I recommend you use GUID's instead. It's a lot safer (reliable) and easier to work with. Saying that, it's easy to use Triggers in FB to overcome the missing AutoInc fields.

We handle Boolean's via a Domain and have no issue there either. Hey, if C/C++ doesn't have native boolean types after all these years, why must databases? ;-)

Regards,
- Graeme -

_______________________________________________
fpGUI - a cross-platform Free Pascal GUI toolkit
http://opensoft.homeip.net/fpgui/

Reply by email

Lee Jenkins
to Graeme Geldenhuys
Graeme Geldenhuys wrote 23 lines
2008/4/30 Tom York : > a few Linux servers, I can certainly see the benefit without question. Thank you! :) > Graeme, what is the largest FB concurrent user install you have > running? My largest desktop application is 15 users. Can't tell you We have on average 30 concurrent users in our system. > The only things I do not like about FB is it lacks SSL, AutoIncrement > attribute and Boolean fields. We've all managed to get by them, but I > wish... :-) I hate AutoIncrement and simply don't use them. I recommend you use GUID's instead. It's a lot safer (reliable) and easier to work with. Saying that, it's easy to use Triggers in FB to overcome the missing AutoInc fields. We handle Boolean's via a Domain and have no issue there either. Hey, if C/C++ doesn't have native boolean types after all these years, why must databases? ;-)

So VB programmers can have it easy ? :)

Warm Regards, Lee

Reply by email

Joost van der Sluis
to Graeme Geldenhuys: Because the deployment PC's will not have FPC on them. Ummm... Then again, it takes all…

Op woensdag 30-04-2008 om 13:13 uur [tijdzone +0200], schreef Graeme Geldenhuys:

Show quoted text 11 lines
Joost van der Sluis wrote: > And you didn't write it in Pascal because.... ? > > Joost Because the deployment PC's will not have FPC on them. Ummm... Then again, it takes all the parameters it needs for customization. Also, if I make it a console app, it will be cross-platform to. :) How to you handle standard input like was done in the script?

I would simply use TSQLConnection.CreateDB to create a database, and the TSQLScript component to execute the scripts...

Show quoted text 29 lines
------------copy------------- echo -n "Creating the database $database ..." ${ISQL} << EOF set sql dialect 3; CREATE DATABASE "$database"; commit; EOF if [ ! $? = 0 ]; then echo "Failed." else echo "Done." fi ------------end------------- Regards, - Graeme - _______________________________________________________ fpGUI - a cross-platform GUI toolkit using Free Pascal http://opensoft.homeip.net/fpgui/ _______________________________________________ Lazarus mailing list http://www.lazarus.freepascal.org/mailman/listinfo/lazarus

Reply by email

Graeme Geldenhuys

2008/4/30 mramirez :

Not everyone has the time to a SQL query in each table & verify that all values, for an integer field are either 0 o 1, or varchar(1) with "f" and "t".

Just for interest sake and completeness.... You don't have to do such checks, the database does those for you....

CREATE DOMAIN D_BOOLEAN AS SMALLINT CHECK (VALUE IN (0, 1));

or if you prefer text boolean types.

CREATE DOMAIN D_BOOLEAN AS CHAR(1) CHECK (VALUE IN ('T', 'F'));

Now create a table with a field of type D_BOOLEAN

I use tiOPF for object persistence. My business objects all use native object pascal types (Boolean being one of them) and the database persistences layer does the conversion to whatever I setup in the database. Boolean as T/F or Boolean as 1/0 in the database.

Just my 2c response. :-)

Regards,
- Graeme -

_______________________________________________
fpGUI - a cross-platform Free Pascal GUI toolkit
http://opensoft.homeip.net/fpgui/

Reply by email