CREATE VIEW "vSYS_FindField"(
"tableName",
"fieldName",
"default_val")
AS
select r.rdb$relation_name, f.rdb$field_name, f.rdb$default_source
from RDB$RELATIONS r, RDB$RELATION_FIELDS f
where r.rdb$system_flag = 0
and r.rdb$relation_name=f.rdb$relation_name;
There is a public train station nearby conference hotel. A map of the location with traveling infos will be send to all visitors
This year we have a special entry level track on thursday and friday. For advanced visitors you find a seperate thread. In the codecamp, you can ask on both days the experts and let them analyze your real world problem. On saturday you will find more general session topics.
Early bird conference fees (valid until 20th sep 2007):
-Full Conference Package 599 Euro *
3 days conference, 4 nights in the conference hotel
(17.-21.) , breakfast, lunch, dinner, evening events
-Conference Only Package 399 Euro *
3 days conference, lunch
-Main Conference Day only Package 169 Euro *
saturday only conference, 1 night in the conference hotel (20.-21.), breakfast, lunch, dinner, saturday evening event
-Main Conference Day only 99 Euro *, saturday only
conference, lunch
* all prices excl 19% VAT, required for all visitors 2nd visitor discount 10%, 3rd visitor discount 15% Please use the website www.firebird-conference.com or email info@h-k.de or fax +4970042397378 to send us your reservation. We hope to see you in Hamburg.
Naturally, we are still looking for sponsors! If you have been thinking about being a conference sponsor, NOW is the time to act. It is also possible to add special product sponsoring packages. Please contact hklemt@ibexpert.biz as soon as possible.
Taken from Firebird News
Firebird is a finalist in four categories of the Sourceforge
Community Choice Awards for 2007.
In each category the Firebird project is up against 9 other
projects all selected for excellence from hundreds of thousands of SF
projects. Categories are Best Tool or Utility, Best Project for the
Enterprise, Best User Support and Best Project Overall.
Voting is open until July 20 so take this opportunity to credit
Firebird with your recognition. Full details and links here:
http://sourceforge.net/communit
Here is the list of built in function in Firebird 2.1 (now, it's still beta). My favourite function:
String function: LPAD, RPAD, LEFT, RIGHT, POSITION, REPLACE, REVERSE
Date function: DATEDIFF, DATEADD
Numeric function: ROUND, TRUNC, ABS, MIN_VALUE, MAX_VALUE, GEN_UUID, HASH
So with this new built in function, my previous article can be achieve with more short statement like this:
select current_date,
right(extract(year from current_date), 2) ||
right('0' || extract(month from current_date), 2) as Year_Month
from rdb$database
But in my opinion it would be nice if Firebird team extend the capability of SUBSTRING function like this: the "length" value can be negative so it can achieve the same result like RIGHT function with the capability of "start position". Example:
Substring('Hello World' from 9 to -3) will result "Wor"
Last week I have a question from Delphindo (Indonesian Delphi community) thorough YM conference. The question is how to make SQL query for Firebird to obtain the same result as we do this (to_char(sysdate, 'YYMM');) in Oracle. I must admit that Firebird doesn't have this "to_char" built in function, but we can create the same result with the trick like this:
select current_date,
substring(extract(year from current_date) from 3)||
case
when char_length(extract(month from current_date))=1
then '0'||extract(month from current_date)
else
extract(month from current_date)
end as Year_Month
from rdb$database
Thank's to the new feature of Firebird 2, that can make this happen without installing any UDF. I use the "char_length" and "substring" built in function that come in Firebird 2 (version 1.5xx still not support this built in function).
select rdb$get_context('SYSTEM', 'NETWORK_PROTOCOL') as protocol
, rdb$get_context('SYSTEM', 'CLIENT_ADDRESS') as client_address
from rdb$database;
DECLARE RNAME CHAR(31);
DECLARE C CURSOR FOR ( SELECT RDB$RELATION_NAME FROM RDB$RELATIONS );
BEGIN
OPEN C;
WHILE (1 = 1) DO
BEGIN
FETCH C INTO :RNAME;
IF (ROW_COUNT = 0) THEN
LEAVE;
SUSPEND;
END
CLOSE C;
END
FOR [select statement] into [local variable that hold the value from select]
DO
BEGIN
[your statement]
END
Sweeping is NOT just organized garbage collection. What sweeping seeks to do is to move the Oldest Interesting Transaction up, and reduce the size of transaction masks. It does so by changing rolled back transactions to committed transactions.
"What!!!", you say. "The woman is nuts."
But that's what a sweep does. It removes all the changes made by a rolled back transaction then changes it state to committed. (Remember we agreed earlier that a read-only transaction that rolled back could be considered committed for all the harm it did. Remove the damage, and its safe to consider the transaction committed.)
At the same time, sweep garbage collects like any other transaction.
A new thread is started and sweeps the database while everybody else goes about life as normal. Well, more or less normal, where the less is the amount of CPU and I/O bandwidth used by the sweep.
Taken from IBPhoenixAny record version behind a committed version created by a transaction older than the oldest transaction active when the oldest transaction currently active started is garbage and will never be needed ever again.
That's pretty dense. Lets ignore the commit/rollback question briefly.
Simple case: I'm transaction 20 and I'm the only transaction running. I find a record created and committed by transaction 15. I modify it and commit. You are transaction 25, and when you start, you are also the only transaction active. You read the same record, recognize that all active transactions can use the version of the record created by me, so you garbage collect the original version. In this case, your threshold for garbage collection (aka Oldest Active) is yourself.
Harder case: You continue puttering around, modifying this and that. Another transaction, say 27 starts. You are its oldest active. It too can modify this and that, as long as it doesn't modify anything you modified. It commits. I start a transaction 30. You are also my oldest active transaction, and I can't garbage collect any record version unless the newer version is older than you. I run into a record originally created by transaction 15, modified by transaction 20, then modified again by 27. All three of those transactions are committed, but I can garbage collect only the original version, created by transaction 15. Although the version created by transaction 27 is old enough for me, it is not old enough for you, and being cooperative, I have to consider your needs too.
Hardest case: I'm transaction 87, and when I started, all transactions before 75 had committed, and everybody from 75 on was active. Transaction 77 modifies a record, created originally by transaction 56. I continue to read the 56 version. All is well. Transaction 77 commits. You are transaction 95. When you start, I, number 87, am the oldest active. You read the record created by 56 and modified by 77. You can't garbage collect anything in that record because I can't read records created by any transaction newer than 74.
Maybe you know now why descriptions of the oldest active tend to be a little peculiar.
Taken from IBPhoenixThe "transaction mask" is a snapshot of the states of all transactions from the oldest interesting, to the current. The snapshot is made when the transaction starts and is never updated. The snapshot depends on the number of transactions that have started since the oldest interesting transaction.
Garbage collection prevents an update-intensive database from filling up with unnecessary back versions of records. It also removes record versions created by transactions that rolled back. Every transaction participates in garbage collection - every transaction, including read-only transactions.
When a client applicaition reads a record from a Firebird database, it gets a record that looks like any record from any database. Two levels lower, somewhere in the server, InterBase/Firebird pulls a string of record versions off the disk. Each version is tagged with the transaction id of the transaction that created it. The first one is the most recently stored. At this point, the server has two goals: 1) produce an appropriate version of the record for the current transaction 2) remove any versions that are garbage - either because they were created by a transaction that rolled back or because they are so old that nobody will ever want to see them again.
Taken from IBPhoenixFirebird is a multi-generational database. When a record is updated, a copy of the new values is placed in the database, but the old values remain (usually as a bytewise difference from the new value). The old value is called a "Back Version". The back version is the rollback log - if the transaction that updated the record rolls back, the old version is right there, ready to resume its old place. The back version is also the shadow that provides repeatable reads for long running transactions. The version numbers define which record versions particular tranasctions can see.
When the transaction that updated the record commits and all concurrent transactions finish, the back version is unnecessary. In a database in which records are updated significantly and regularly, unnecessary back versions could eventually take up enough disk space that they would reduce the performance of the database. Thus they are GARBAGE, and should be cleaned out.
Transactions have four states: active, committed, limbo, and rolled back.
Taking these cases in order from the least complex to the most:
Limbo: A transaction that started a two-phase commit by calling the Prepare routine. The transaction may be alive or not. At any point, the transaction may re-appear and ask to commit or rollback. Changes it made can neither be trusted nor ignored, and certainly cannot be removed from the database.
Committed: A transaction is which completed its activity successfully. Either A) it called Commit and the commit completed successfully, or B) it called Rollback but made no changes to the database, or C) it called Rollback and its changes were subsequently undone and its state changed to Committed. This transaction is finished and will never be heard from again, and its remaining changes are now officially part of the database.
Rolled back: A transaction which either: A) called Rollback and requested that its changes be removed from the database, or B) never called Commit so was marked as Active, but discovered to be dead by another transaction which marked it as rolled back. In either case, changes made by this transaction must be ignored and should be removed from the database.
Active: A transaction which: A) hasn't started. B) has started and hasn't finished. C) started and ended without calling any termination routine. (e.g. crashed, lost communication, etc.)
Taken from IBPhoenixThe above example can be found on the employee example database when you install firebird. The "INTO" keyword must be followed by the return variable (declare in the header of the SP) then we must put the keyword "SUSPEND" after that. The advantage of this selectable SP is we can make it more complex that include some business rule before we return the value that can be selectable, and we can also has a parameters to output different result based on that parameters. As I know there is no much database that has this ability (I only know Oracle, Firebird/Interbase that can do that, perhaps PostgreeSQL).
CREATE PROCEDURE GET_EMP_PROJ(
EMP_NO SMALLINT)
RETURNS (
PROJ_ID CHAR(5))
AS
BEGIN
FOR SELECT proj_id
FROM employee_project
WHERE emp_no = :emp_no
INTO :proj_id
DO
SUSPEND;
END
This 1.5.3 version also include Release Notes in PDF that I think more complete version (including release notes from version 1.5.0 until 1.5.3)
Now version 2.0 is still in Beta 2 version. Hope this will be released in March.
For small to midsize company, I think this database will rock you. (Also available for Linux version)
Postingan
Semua Komentar
