Looking for a Firebird book?

classic Classic list List threaded Threaded
40 messages Options
12
Reply | Threaded
Open this post in threaded view
|

Re: Looking for a Firebird book?

Inoussa OUEDRAOGO
2008/4/9, Michael Van Canneyt <[hidden email]>:
> I do it like that too, because the security model of any RDBM is ridiculous,
>  firebird is no exception.
>
>  Security must be specified at a functional level, never at the table level.

Absolutely. And security model at the RDBM level prevents connection
pooling in a multi tiers
solution. The common use of the security model at the RDBM level is
for readonly users
in web applications. Even in this case, only one such user is defined
at the RDBMS, in
order to be able to use connection pooling.

--
Inoussa O.
_______________________________________________
fpc-pascal maillist  -  [hidden email]
http://lists.freepascal.org/mailman/listinfo/fpc-pascal
Reply | Threaded
Open this post in threaded view
|

Re: Looking for a Firebird book?

Michael Van Canneyt
In reply to this post by Marco van de Voort


On Wed, 9 Apr 2008, Marco van de Voort wrote:

> > On Wed, 9 Apr 2008, Matt Emson wrote:
> >
> > The only negative thing I can say about Firebird is that it can produce
> > a corrupt backup file.
>
> IIRC Mass insertion could bring down the db (slow to an effective DOS to
> other users) unless you commit the transaction every 10000 items or so.

Not in my experience: over 600.000 records inserted in 1 transaction.
Maybe with interbase this was so ?

Michael.
_______________________________________________
fpc-pascal maillist  -  [hidden email]
http://lists.freepascal.org/mailman/listinfo/fpc-pascal
Reply | Threaded
Open this post in threaded view
|

Re: Looking for a Firebird book?

Matt Emson
Michael Van Canneyt wrote:

> On Wed, 9 Apr 2008, Marco van de Voort wrote:
>
>  
>> IIRC Mass insertion could bring down the db (slow to an effective DOS to
>> other users) unless you commit the transaction every 10000 items or so.
>>    
>
> Not in my experience: over 600.000 records inserted in 1 transaction.
> Maybe with interbase this was so ?
>  

If you bring a UDF in to the equation, it will happen. Easily.
_______________________________________________
fpc-pascal maillist  -  [hidden email]
http://lists.freepascal.org/mailman/listinfo/fpc-pascal
Reply | Threaded
Open this post in threaded view
|

Re: Looking for a Firebird book?

Michael Van Canneyt


On Wed, 9 Apr 2008, Matt Emson wrote:

> Michael Van Canneyt wrote:
> > On Wed, 9 Apr 2008, Marco van de Voort wrote:
> >
> >  
> > > IIRC Mass insertion could bring down the db (slow to an effective DOS to
> > > other users) unless you commit the transaction every 10000 items or so.
> > >    
> >
> > Not in my experience: over 600.000 records inserted in 1 transaction.
> > Maybe with interbase this was so ?
> >  
>
> If you bring a UDF in to the equation, it will happen. Easily.

That could be. I don't use them.
My point of view is that a database is for storage, not for logic...

Michael.
_______________________________________________
fpc-pascal maillist  -  [hidden email]
http://lists.freepascal.org/mailman/listinfo/fpc-pascal
Reply | Threaded
Open this post in threaded view
|

Re: Looking for a Firebird book?

Bee-6
> My point of view is that a database is for storage, not for logic...

I used to think that way too. But, based on my personal experience, in
certain conditions and requirements, implementing logic in database is
inevitable. That's why many databases support server side logic though
it's implemented in various ways (UDF, SP, SF, etc).

But I also agree that we shouldn't use this mechanism at all time. Each
decision and choice has its own consequences. ;)

-Bee-

has Bee.ography at:
http://beeography.wordpress.com

_______________________________________________
fpc-pascal maillist  -  [hidden email]
http://lists.freepascal.org/mailman/listinfo/fpc-pascal
Reply | Threaded
Open this post in threaded view
|

Re: Looking for a Firebird book?

Graeme Geldenhuys-2
In reply to this post by Michael Van Canneyt
On 09/04/2008, Michael Van Canneyt <[hidden email]> wrote:
>  My point of view is that a database is for storage, not for logic...

A very logical assumption. ;-)  And one I fully agree with. Use the
correct tool for the job, hence the reason we don't use stored
procedures either.

Regards,
  - Graeme -


_______________________________________________
fpGUI - a cross-platform Free Pascal GUI toolkit
http://opensoft.homeip.net/fpgui/
_______________________________________________
fpc-pascal maillist  -  [hidden email]
http://lists.freepascal.org/mailman/listinfo/fpc-pascal
Reply | Threaded
Open this post in threaded view
|

Re: Looking for a Firebird book?

Graeme Geldenhuys-2
In reply to this post by Bee-6
On 09/04/2008, Bee <[hidden email]> wrote:
>
>  I used to think that way too. But, based on my personal experience, in
> certain conditions and requirements, implementing logic in database is
> inevitable. That's why many databases support server side logic though it's
> implemented in various ways (UDF, SP, SF, etc).
>
>  But I also agree that we shouldn't use this mechanism at all time. Each
> decision and choice has its own consequences. ;)

The reason we stopped using stored procs.....  The speads business
rules across layers.... Business rules do no belong in the storage
layer - the same reason business logic does not belong in Forms (gui
layer).  If they are mixed like that, you cannot write unit tests for
them and makes it very hard to debug business rules!

But as I said in a earlier post, that's a whole other discussion, and
I have no interest in starting a flame war. :)

Regards,
  - Graeme -


_______________________________________________
fpGUI - a cross-platform Free Pascal GUI toolkit
http://opensoft.homeip.net/fpgui/
_______________________________________________
fpc-pascal maillist  -  [hidden email]
http://lists.freepascal.org/mailman/listinfo/fpc-pascal
Reply | Threaded
Open this post in threaded view
|

Re: Looking for a Firebird book?

Inoussa OUEDRAOGO
Hi,

2008/4/9, Graeme Geldenhuys <[hidden email]>:

> On 09/04/2008, Bee <[hidden email]> wrote:
>  >
>  >  I used to think that way too. But, based on my personal experience, in
>  > certain conditions and requirements, implementing logic in database is
>  > inevitable. That's why many databases support server side logic though it's
>  > implemented in various ways (UDF, SP, SF, etc).
>  >
>  >  But I also agree that we shouldn't use this mechanism at all time. Each
>  > decision and choice has its own consequences. ;)
>
>
> The reason we stopped using stored procs.....  The speads business
>  rules across layers.... Business rules do no belong in the storage
>  layer - the same reason business logic does not belong in Forms (gui
>  layer).  If they are mixed like that, you cannot write unit tests for
>  them and makes it very hard to debug business rules!

Why couldn't SP be unit tested ? I did it myself numerous time. Tests
do not have to be
written in Object Pascal.

>  But as I said in a earlier post, that's a whole other discussion, and
>  I have no interest in starting a flame war. :)


Stored procedures have their uses, they should not be abused as OOP
should not be abused. Making the business rules in the middle layer
is a good point, but it should not be done at any price; I saw some
code where data are selected and sorted on the middle layer because
of _this_ rule and make the application _very slow_  and consume
too much memory (it could has
been done very efficiently with a few sql commands in a SP).
I do think thatevery method, technic must remain a mean, not a goal.

Thanks.

--
Inoussa O.
_______________________________________________
fpc-pascal maillist  -  [hidden email]
http://lists.freepascal.org/mailman/listinfo/fpc-pascal
Reply | Threaded
Open this post in threaded view
|

Re: Looking for a Firebird book?

afpTeam
In reply to this post by Graeme Geldenhuys-2

> >On 09/04/2008, Michael Van Canneyt <[hidden email]> wrote:
>>  My point of view is that a database is for storage, not for logic...
>
> From: "Graeme Geldenhuys Wednesday, April 09, 2008 3:35 PM
> A very logical assumption. ;-)  And one I fully agree with. Use the
> correct tool for the job, hence the reason we don't use stored
> procedures either.
>
> Regards,
>  - Graeme -

Actually a hard-drive or other durable memory is for storage.
A database is for "efficient" organization of data to aid efficient
utilization

In most cases aggregation of that data is part of efficient utilization.

Aggregation of related data and several other math intensive ops inside the
DB engine (i.e. stored procs), will boost net performance VERY
substantially, when deployed in a properly cached and threaded environment.
Banks, Brokerages, Real Time Inventory managers, large store operations all
depend on these operations to be as efficiently as possible.

It comes down to knowing when, how and where to separate business layers of
integrated procedures relative to specific data sets so business logic that
is related is not in conflict with properly designed DB integration of
procs.  But for the most general applications of DB's which do not rely on
mission critical performance, knowing how to apply DB internal procs and
functions is essential to large volume processing, timely.  Otherwise, for
general purpose operation, DB's are simply a convenience over a mess of flat
files, so the question of procs is moot in these less demanding cases.

It doesn't mean Procs or Funcs don't belong in a DB, but that the need is
"application specific", especially where clock cycles and turn-around time
are critical.

Right tool for the right job.  As much as ADO tried to benefit this idea and
basically broke the whole concept. ;)

Mike


_______________________________________________
fpc-pascal maillist  -  [hidden email]
http://lists.freepascal.org/mailman/listinfo/fpc-pascal
Reply | Threaded
Open this post in threaded view
|

Re: Looking for a Firebird book?

Bee-6
In reply to this post by Inoussa OUEDRAOGO
> I do think that every method, technique must remain a mean, not a goal.

+1 :)

-Bee-

has Bee.ography at:
http://beeography.wordpress.com

_______________________________________________
fpc-pascal maillist  -  [hidden email]
http://lists.freepascal.org/mailman/listinfo/fpc-pascal
Reply | Threaded
Open this post in threaded view
|

Re: Looking for a Firebird book?

Matt Emson
In reply to this post by Michael Van Canneyt
Michael Van Canneyt wrote:

> On Wed, 9 Apr 2008, Matt Emson wrote:
>
>  
>> Michael Van Canneyt wrote:
>>    
>>> On Wed, 9 Apr 2008, Marco van de Voort wrote:
>>>
>>>  
>>>      
>>>> IIRC Mass insertion could bring down the db (slow to an effective DOS to
>>>> other users) unless you commit the transaction every 10000 items or so.
>>>>    
>>>>        
>>> Not in my experience: over 600.000 records inserted in 1 transaction.
>>> Maybe with interbase this was so ?
>>>  
>>>      
>> If you bring a UDF in to the equation, it will happen. Easily.
>>    
>
> That could be. I don't use them.
>  

With the InterBase model, good move.

> My point of view is that a database is for storage, not for logic...

Ah, this is basic use of resources. The benefit of Stored Procs is speed
of execution. You are thinking in BDE terms - database stores data, I
retrieve data, I manipulate/display data, I write changes and new data
to database. This model works well until performance is essential. In an
inherently single threaded framework (such as the VCL and most other
libraries with a GUI), database access is costly. Using Stored Procs to
do data selection, manipulation and insertion/updating hands that task
to the database engine. In an RDBMS, this is extremely beneficial to the
client code. The server needs to be beefier, but the GUI client machines
can be Pentium 100MHz machines. Using the BDE style of database access
(thinking Paradox and DBase here) you need to do all processing on the
client, or at least in a process distinct from the data storage layer.
I've worked on a system that used a home grown BTree based filing system
instead of a database and it's much the same. Writing a server process
to sit in the middle of your clients and load balance the database
access is a magnitude more dificult than using the database systems in
built load balancing mechanisms.

Try this:

Test 1: Create a database, add two identically structured tables. Write
a stored proc to insert in to, update and select the contents of table
1. Write a client app that runs timed operations to add 10,000,000
records in to each table and then read them back. (use your imagination,
either all in one go or mixed mode.) I promise you, the stored proc
version will be faster, because the engine will cache the compiled
stored proc.

Test 2: Add a second table that controls table 1 and 2. This has 2
columns, like an enumeration. The UID points to a value that dictates
the use of the data in Tables 1 and 2. Now write code that only returns
a subset of the tables given the rules in table 3. This can be as simple
as an exclusion (e.g. Table 3 contains 4 rows, 1, 1 : 2, 0 : 3, 1: 4, 0)
where table 1 and 2 join table 1 and the second column is used to
determine inclusion (boolean logic) so that a column in table 1 or 2
must match the joined columns value. In the  stored proc version, this
is a simple "select" operation on he client. In the  other version, the
logic now sits more heavily on the client.

You could go on like this. The more complex the look up in, the more
strain the client process is put under (or middle tier.) In the RDBMS
version, the load is balanced because the database engine is explicitly
designed to handle this model.

I'm not trying to preach to you, as your position is one of taste, but
the logic you use is not as sound as you imply.

M


_______________________________________________
fpc-pascal maillist  -  [hidden email]
http://lists.freepascal.org/mailman/listinfo/fpc-pascal
Reply | Threaded
Open this post in threaded view
|

Re: Looking for a Firebird book?

Marco van de Voort
> Michael Van Canneyt wrote:
>
> > My point of view is that a database is for storage, not for logic...
>
> Ah, this is basic use of resources. The benefit of Stored Procs is speed
> of execution.

For dialy routines, only if they are not complex, and significantly reduce the amount of data
transfered tuples (the result set), and it can not be expressed in a query.

With complex I mean is that if the stored proc needs to use temp tables or
other state that consists out of a set of tuples, then it is usually slower.

Oh, and CPU is cheap compared to DB licenses nowadays.
_______________________________________________
fpc-pascal maillist  -  [hidden email]
http://lists.freepascal.org/mailman/listinfo/fpc-pascal
Reply | Threaded
Open this post in threaded view
|

Re: Looking for a Firebird book?

Matt Emson
Marco van de Voort wrote:

>> Michael Van Canneyt wrote:
>>
>>    
>>> My point of view is that a database is for storage, not for logic...
>>>      
>> Ah, this is basic use of resources. The benefit of Stored Procs is speed
>> of execution.
>>    
>
> For dialy routines, only if they are not complex, and significantly reduce the amount of data
> transfered tuples (the result set), and it can not be expressed in a query.
>  
I don't doubt what you are saying, but in my experience, this is not the
case. It really depends on the client and underlying protocol used to
access the RDBMS. In a local database system, yeah, probably. In a
RDMBS, no.
> With complex I mean is that if the stored proc needs to use temp tables or
> other state that consists out of a set of tuples, then it is usually slower.
>  

Again, not true in my experience. Writing the same code on the client
that manipulates data from multiple tables and uses the resulting
dataset is a lot slower. Using Interbase/Firebird, you can create stored
procs that return datasets in a ad hoc fashion (multithreaded) or bulk.
Something like:

create proc test ()
returns (a int, b int, c int, d int, e int)
for select a, b, c, d
from TZ
into :a, :b, :c, :d do
begin
  e = 0;
  if (b in [1, 2, 5, 200]) then
  begin
    e = 22;
    suspend;
  end
  else if (c = 250) and (b < 5) then
  begin
    e = 25;
    suspend;
  end
  else if (a > 250) and (b = 5) then
  begin
    e = 8;
    suspend;
  end
  else if (d = -1) then exit;
end

(forgive my syntax mistakes above, not done any Interbase for over a year.)

is far better than (pseudo code)

var
  table : table_class;
begin
  ...(some construction or whatever)

  table.execsql("select * from TZ")

   while (not table.Eof) do
   begin
     a := table.fieldbyname('a').AsInteger;
     b := table.fieldbyname('b').AsInteger;
     c := table.fieldbyname('c').AsInteger;
     d := table.fieldbyname('d').AsInteger;
     e = 0;
  if (b in [1, 2, 5, 200]) then
  begin
    e = 22;
    suspend;
  end
  else if (c = 250) and (b < 5) then
  begin
    e = 25;
    suspend;
  end
  else if (a > 250) and (b = 5) then
  begin
    e = 8;
    suspend;
  end
  else if (d = -1) then exit;
end
   end;
end;
> Oh, and CPU is cheap compared to DB licenses nowadays.

Aha, the Microsoft defense! ;-) Chewbaka was a Wookie, don't you know? ;-)
_______________________________________________
fpc-pascal maillist  -  [hidden email]
http://lists.freepascal.org/mailman/listinfo/fpc-pascal
Reply | Threaded
Open this post in threaded view
|

Re: Looking for a Firebird book?

Michael Van Canneyt
In reply to this post by Matt Emson


On Thu, 10 Apr 2008, Matt Emson wrote:

> I'm not trying to preach to you, as your position is one of taste, but the
> logic you use is not as sound as you imply.

No need to convince me :-)

You are correct, and we do use stored procs in a few simple cases where
speed is essential. But these are the exceptions to the rule.
We have 350 tables, well over 3000 queries and about 10 stored procs;
So we stay true to our credo, but are not blind to reality :-)

Michael.
_______________________________________________
fpc-pascal maillist  -  [hidden email]
http://lists.freepascal.org/mailman/listinfo/fpc-pascal
Reply | Threaded
Open this post in threaded view
|

Re: Looking for a Firebird book?

Marco van de Voort
In reply to this post by Matt Emson
> Marco van de Voort wrote:
> > For dialy routines, only if they are not complex, and significantly reduce the amount of data
> > transfered tuples (the result set), and it can not be expressed in a query.
> >  
> I don't doubt what you are saying, but in my experience, this is not the
> case. It really depends on the client and underlying protocol used to
> access the RDBMS. In a local database system, yeah, probably. In a
> RDMBS, no.

In my case, the middle tier was on a different machine than the (SQL Server)
db.

> Again, not true in my experience. Writing the same code on the client
> that manipulates data from multiple tables and uses the resulting
> dataset is a lot slower. Using Interbase/Firebird, you can create stored
> procs that return datasets in a ad hoc fashion (multithreaded) or bulk.
> Something like:

You example is prefectly covered by my exceptions, (it compares a case where
the stored procedure reduces the number of tuples returned).

In (at least my) practice however, cases like this that can't be
expressed in SQL  (or at least that the first or second magnitudes of
data reduction are done) are relatively rare.

> > Oh, and CPU is cheap compared to DB licenses nowadays.
>
> Aha, the Microsoft defense! ;-) Chewbaka was a Wookie, don't you know? ;-)

I saw him whine yesterday in the Empire Strikes Back yes. And he was as
realistic as buying P-I client machines nowadays.
_______________________________________________
fpc-pascal maillist  -  [hidden email]
http://lists.freepascal.org/mailman/listinfo/fpc-pascal
Reply | Threaded
Open this post in threaded view
|

Re: Looking for a Firebird book?

Matt Emson

On 10 Apr 2008, at 11:44, Marco van de Voort wrote:
>
>>> Oh, and CPU is cheap compared to DB licenses nowadays.
>>
>> Aha, the Microsoft defense! ;-) Chewbaka was a Wookie, don't you  
>> know? ;-)
>
> I saw him whine yesterday in the Empire Strikes Back yes. And he was  
> as
> realistic as buying P-I client machines nowadays.

Geode, or the VIA processor line.
 
_______________________________________________
fpc-pascal maillist  -  [hidden email]
http://lists.freepascal.org/mailman/listinfo/fpc-pascal
Reply | Threaded
Open this post in threaded view
|

Re: Looking for a Firebird book?

Michael Van Canneyt
In reply to this post by Tom York


On Wed, 9 Apr 2008, Tom York wrote:

> On Wed, Apr 9, 2008 at 5:53 AM, Codebue Fabio - P-Soft <[hidden email]>
> wrote:
>
> > Take a look to a new Firebird 2.1 next month and probably you will change
> > your idea about it...
> > UDF: FreeAdHocUDF, a lot of internal SQL function...
> > and if you wanna a good book www.ibphoenix.com Helen Borrie book... a
> > MUST!
> >
> >
> Will 2.1 have a true auto increment attribute?  A boolean field perhaps?
> Those are the only things that I miss when using FB.  I am aware that all of
> these issues can be resolved by using generators/triggers and a validating
> domain.  That is what I like about MySQL, MSSQL and others that are missing
> from FB.

And it is exactly why I can't use MySQL, MSSQL: they don't have sequences or
generators. I need the ID  BEFORE I insert the record, not after.

So: Each his taste :-)

Michael.
_______________________________________________
fpc-pascal maillist  -  [hidden email]
http://lists.freepascal.org/mailman/listinfo/fpc-pascal
Reply | Threaded
Open this post in threaded view
|

Re: Looking for a Firebird book?

Matt Emson
Michael Van Canneyt wrote:
> And it is exactly why I can't use MySQL, MSSQL: they don't have sequences or
> generators. I need the ID  BEFORE I insert the record, not after.
>  

YES!! This is also missing from SQL Server... or at least, using a GUID
is complete overkill. The mechanisms SQL Server has for retrieving the
last interted identity value are completely unsane too. Yes, they do
work, but things get tricky in real world situations. I have no idea how
anyone wrote reliable code to insert a record and retern the IDENTITY
value prior to SQL Server 2005.

OT: does anyone know of a reliable generator style "atomic" robust multi
user friendly auto IDENTITY generation mechanism for SQL Server? I'd
love to be able to define one, but I don't think I have seen one yet
that *really* works well.


> So: Each his taste :-

Generators and "For select .. into .. suspend"  are the features I
really miss from Interbase/Firebird.

M
_______________________________________________
fpc-pascal maillist  -  [hidden email]
http://lists.freepascal.org/mailman/listinfo/fpc-pascal
Reply | Threaded
Open this post in threaded view
|

Re: Looking for a Firebird book?

Inoussa OUEDRAOGO
2008/4/11, Matt Emson <[hidden email]>:

> Michael Van Canneyt wrote:
>
> > And it is exactly why I can't use MySQL, MSSQL: they don't have sequences
> or
> > generators. I need the ID  BEFORE I insert the record, not after.
> >
> >
>
>  YES!! This is also missing from SQL Server... or at least, using a GUID is
> complete overkill. The mechanisms SQL Server has for retrieving the last
> interted identity value are completely unsane too. Yes, they do work, but
> things get tricky in real world situations. I have no idea how anyone wrote
> reliable code to insert a record and retern the IDENTITY value prior to SQL
> Server 2005.
>
>  OT: does anyone know of a reliable generator style "atomic" robust multi
> user friendly auto IDENTITY generation mechanism for SQL Server? I'd love to
> be able to define one, but I don't think I have seen one yet that *really*
> works well.

Have you try SCOPE_IDENTITY() ? Available at least since SQL SERVER 2000.
I have used it with success.

from MSDN :
<quote>
  Returns the last identity value inserted into an identity column in
  the same scope. A scope is a module: a stored procedure, trigger,
  function, or batch. Therefore, two statements are in the same scope
  if they are in the same stored procedure, function, or batch.
</quote>

Interesting article ( 10 Things You Shouldn't Do with SQL Server ) :
  http://www.sqljunkies.ddj.com/Article/92CC4817-604D-4344-8BE0-4490F8ED24B6.scuk

--
Inoussa O.
_______________________________________________
fpc-pascal maillist  -  [hidden email]
http://lists.freepascal.org/mailman/listinfo/fpc-pascal
Reply | Threaded
Open this post in threaded view
|

Re: Looking for a Firebird book?

Matt Emson
Inoussa OUEDRAOGO wrote:
> Have you try SCOPE_IDENTITY() ? Available at least since SQL SERVER 2000.
> I have used it with success.
>  

Well, yeah. But it's not perfect. I want generators, as in:

create generator an_atomic_counter;
set an_atomic_counter = 1 /*or something like that*/

....
declare variable next_atomic_value int
begin
  next_atomic_value = gen_id(an_atomic_counter, 1);
end

next_atomic_is is alway a unique incremental id, and is *not* tied to a
specific table. It's not so much the ability to generate a unique id,
it's the "use auto inc IDENTITY fields or be damned" attitude that Sql
Server has. The only other option is a GUID. Microsoft's insane "SET
IDENTITY_INSERT ON|OFF" is also dangerous. Have you ever tried to
migrate data between to databases using Sql Server? Nightmare!

> from MSDN :
> <quote>
>   Returns the last identity value inserted into an identity column in
>   the same scope. A scope is a module: a stored procedure, trigger,
>   function, or batch. Therefore, two statements are in the same scope
>   if they are in the same stored procedure, function, or batch.
> </quote>
>
> Interesting article ( 10 Things You Shouldn't Do with SQL Server ) :
>   http://www.sqljunkies.ddj.com/Article/92CC4817-604D-4344-8BE0-4490F8ED24B6.scuk
>
>  

_______________________________________________
fpc-pascal maillist  -  [hidden email]
http://lists.freepascal.org/mailman/listinfo/fpc-pascal
12