Re: [firebird-support] Re: how to use the same column from a single table to other tables?
Hi, On 02/05/2013 10:47, dice_in_trouble wrote: ... Thanks Nrom for the reply. I also thought of this idea, wherein I make a separate table for the balance then link it up to the other 2 tables. But I don't know how to make table relations. I am using flamerobin to build up my firebird database. Can you give me a simple example of making a relationship between 3 tables? You would use alter table for this, not sure how one uses this in flamerobin as I don't use it to generate my databases (I use Python/SQLAlchemy and after I designed my ORM declarative classes SQLAlchemy does the creation for me. Sometimes I do it manually for testing something then I use IBExpert (just because I new it before flamerobin), it has an option create foreign key which then walks you through the process. I am no expert but to me your question makes me think that you might need to read up a bit more about databases, SQL and Firebird, you should be able to find tutorials with a google search. Obviously the FB documentation is also not to be forgotten. http://www.firebirdsql.org/en/reference-manuals/ Maybe invest in the Firebird Book, e.g. the second Edition Quote: The Firebird Book (Second Edition) is now available as a print on demand book (at createspace) in three parts, Volume 1: `Firebird Fundamentals https://www.createspace.com/4203352 Volume 2: `Developing with Firebird Data https://www.createspace.com/4206843 Volume 3: `Administering Firebird Servers and Databases https://www.createspace.com/4206991 Regards Paul EndQuote Werner [Non-text portions of this message have been removed]
[firebird-support] Any date for n Alpha or Beta for 3.0
Hi, Is there any date yet for an alpha and/or beta for version 3.0 of Firebird SQL? Werner [Non-text portions of this message have been removed]
Re: [firebird-support] Why is coalesce not using an index?
On 18/02/2013 12:46, Mark Rotteveel wrote: On Mon, 18 Feb 2013 12:10:00 +0100, Carsten Schäfer ca_schae...@gmx.de mailto:ca_schaefer%40gmx.de wrote: Actually, I don't know whether Firebird treats coalesce as if OR had been used. In some cases, I suspect it in theory could, but COALESCE(a, b) IS NULL would have to translate to AND rather than OR. Don't know whether this is implemented, or even if it would be beneficial if implemented - whenever I use coalesce in a WHERE clause, it is very rarely referring to different fields within the same tuple. The comparison with your OR-example gives the same result. The OR uses both indexes and coalesce is not using the indexes. That's the problem. The optimizer in Firebird simply can't do this for a COALESCE. It might work if you have an expression index on COALESCE(apos.f_dat_anlieferung, apos.f_dat_erstellung) (BTW: I am not 100% sure about this). IIRC, there is some enhancement in 3.0 which will use an index on COALESCE, but I can't find the tracker item at the moment. Werner [Non-text portions of this message have been removed]
Re: [firebird-support] Re: Firebird Windows 3.11
On 10/01/2013 11:14, mariuz wrote: --- In firebird-support@yahoogroups.com mailto:firebird-support%40yahoogroups.com, Andy Gable wrote: Yes they would BUT I can only code in BASIC (QuickBasic for MS-DOS and Visual Basic for windows) Have you already written the POS? If not or if it is early days you might want to consider: http://sourceforge.net/directory/os:windows/freshness:recently-updated/?q=Point%20of%20sale and join the developer team of one of them. Use a dev tool which is multi platform, I use wxPython, there is also Dabo (a ui framework using wxPython) - http://dabodev.com/ Werner [Non-text portions of this message have been removed]
Re: [firebird-support] How would Firebird handle this.
On 27/12/2012 02:12, Andrew Gable wrote: Hello everyone, Just a quick question. I have 5 font end terminals connected to a firebird 2.1 server how would it handle say all the front end apps updating the same record eg a item 1001 is sold on the front ends and when the transaction is done the front ends find the product and does current stock -1 and then does a update on the record to save the new stock level into the database is this fine or is there something better I can do so the records do not get corrupted thanks (I know it is a daft question but I've never had more then 2 machines on the server) Wanted to read up on this again, currently use FB in single user mode (embedded) but working on a project where this could be an issue for me too. So, followed Leonardo's advice, but added a word ;-) - i.e. googling on transaction isolation levels firebird gives quite a few good links. Just a few I found interesting: http://www.firebirdsql.org/manual/isql-transactions.html http://www.firebirdsql.org/file/documentation/papers_presentations/html/paper-fbent-acid.html http://ibexpert.net/ibe/index.php?n=Doc.TransactionOptionsExplained Werner [Non-text portions of this message have been removed]
[firebird-support] What makes a column read only?
Hi, I have this table definition: CREATE TABLE CONSUMPTION ( ID KEYS NOT NULL, QUANTITY Integer DEFAULT 0, UNITPRICE Numeric(18,2) DEFAULT 0, CONSUMEDVALUE Numeric(18,2) DEFAULT 0, REMARKS Varchar(50) COLLATE UNICODE_CI_AI, CONSUMED Timestamp, MATURITYFIRST Integer, MATURITYBEST Integer, MATURITYPAST Integer, TRANSFER_ID Bigint, FK_CBBOTTLEID Bigint, FK_CELLARBOOK_ID KEYS, FK_RATING_ID KEYS, FK_CELLAR_ID KEYS, FK_REASON_ID KEYS, CREATED_AT Timestamp, UPDATED_AT Timestamp, CREATED_BY KEYS, UPDATED_BY KEYS, CONSTRAINT PK_CONSUMPTION PRIMARY KEY (ID) ) CREATE TRIGGER CONSUMPTION_CONSVAL FOR CONSUMPTION ACTIVE AFTER INSERT OR UPDATE POSITION 0 AS BEGIN /* enter trigger code here */ new.CONSUMEDVALUE = new.UNITPRICE * new.QUANTITY; END When I try to update manually (e.g. using FlameRobin or IBExpert) the column CONSUMEDVALUE or when I try to create the above trigger I get this error: SQL Message : -151 This column cannot be updated because it is derived from an SQL function or expression. Engine Code: 335544359 Engine Message : attempted update of read-only column What am I doing wrong? Werner
Re: [firebird-support] What makes a column read only?
Hi Thomas, On 05/11/2012 19:51, Thomas Steinmaurer wrote: You can't set a new column value in an AFTER trigger. Use a BEFORE trigger. Thanks a lot, that did the trick. Werner [Non-text portions of this message have been removed]
Re: [firebird-support] Re: Win7 gbak.exe restore show fail to create database d:\dbback\rsdata.fdb
On 01/09/2012 04:25, bigredinf wrote: Dear Thomas, thanks for your quick reply. In WinXp, I use the command gbak.exe -c -v -R -z -user sysdba -password masterkey d:\dbback\20120831.fbk d:\dbback\rsdata.fdb Shouldn't the -R be lower case? to check if my backup file (d:\dbback\20120831.fbk) is OK. but when I run this command in Win7, the command console show fail to create database d:\dbback\rsdata.fdb - Does the directory d:\dbback exist? FYI,I use firebird Ver 1.5.4910, and gbak.exe is placed in d:\dbback so I can sure d:\dbback exist. - Does the user, under which the Firebird server process is running has proper rights in that directory? the user you mean is [Win7 login user] or [sysdba] ? in Win7, when I use the command gbak.exe -b -g -z -v -user sysdba -password masterkey server:d:\fb\dbdata.fdb d:\dbback\20120831.fbk to backup remote database to local Win7 pc, and the backup process is doing just fine. I don't know how to set the proper right to d:\dbback WIN7 user has to have rights to the file to create and to delete if the db file exists. Any chance that dbdata.fdb is open by another process? Werner [Non-text portions of this message have been removed]
Re: [firebird-support] Index/Plan/View question
So, if you don't need a (complex) logic for returning a result set,
you'd better use a view, because a view can use an index for a provided
WHERE clause.
I am trying the view approach but must still be missing something.
Table def is:
CREATE TABLE SUBREGION_L (
NAME VARCHAR(70) NOT NULL COLLATE UNICODE_CI_AI,
etc etc
The above table contains translations for the column name, e.g. French
and German.
CREATE TABLE SUBREGION (
NAME VARCHAR(70) NOT NULL COLLATE UNICODE_CI_AI,
etc etc
The subregion table contains the default language, in this case
English and other other columns which don't get translated.
Both tables have an index on the NAME column.
The select on subregion uses the index, i.e.:
select * from subregion where name starting with 'mur';
Plan
PLAN (SUBREGION INDEX (IX_SUBREGION_NAME))
Then I build a view like this:
CREATE OR ALTER VIEW subregion_LVtest(
ID,
NAME,
SEARCHNAME,
FK_LANGUAGE_ID,
FK_SUBREGION_L_ID
)
AS
SELECT
o.ID,
COALESCE(o.name, t.name),
COALESCE(o.searchname, t.searchname),
COALESCE(1, t.FK_LANGUAGE_ID),
t.ID
FROM subregion o
LEFT OUTER JOIN subregion_l t ON t.fk_subregion_id=o.id
and t.fk_language_id=rdb$get_context('USER_SESSION',
'LANGUAGE_ID');
Then doing this select:
select * from subregion_lvtest where name starting with 'mur';
I get this plan:
Plan
PLAN JOIN (SUBREGION_LVTEST O NATURAL, SUBREGION_LVTEST T INDEX
(FK_SUBREGION_L_SUBREGION_ID, FK_SUBREGION_L_LANGUAGE_ID))
I guess it is due to the use of COALESCE on the column used for the
where clause.
Is there something I can do to get the index used for the name column.
Werner
[Non-text portions of this message have been removed]
Re: [firebird-support] Index/Plan/View question
Hi Thomas, On 12/24/2011 10:19 AM, Thomas Steinmaurer wrote: ... That's a problem with SPs in general. in general with SPs on Firebird or in general on SQL DBs? Is a future FB 2.5.x or 3.x version going to improve on this? Using a WHERE clause when calling a SP won't be able to use an index. You have to put that *into* the SP whenever possible. You mean passing the where as an input parameter and then use it within the SP? So, if you don't need a (complex) logic for returning a result set, you'd better use a view, because a view can use an index for a provided WHERE clause. I tried to go the view route but couldn't make it work. What this is all about is to translation enable certain tables and certain columns in these tables, most often just the name and searchname columns and be able to do a where on these columns and there are where selects on the id plus langid. Thanks Werner [Non-text portions of this message have been removed]
Re: [firebird-support] Index/Plan/View question
On 11/23/2011 01:33 PM, Mark Rotteveel wrote: On Wed, 23 Nov 2011 12:11:52 +0100, Werner F. Bruhin werner.bru...@free.fr wrote: I do a query like this: SELECT subregion.NAME FROM subregion WHERE subregion.name collate UNICODE_CI_AI starting with 'abona' And I get a plan: PLAN (SUBREGION NATURAL) I tried with these indexes, obviously would like to end up with just one of them): CREATE INDEX SUBREGION_IDX1 ON SUBREGION (NAME); CREATE INDEX SUBREGION_IDX2 ON SUBREGION COMPUTED BY (lower(NAME)); CREATE DESCENDING INDEX SUBREGION_IDX3 ON SUBREGION (NAME); 'subregion' is a table ultimately I will have a view for subregion which brings in another table, but still like to search by name and preferably with the use of an index. This is with FB 2.5. Looking for tips and/or documentation links to read for better understanding on when indexes are used or what to do to get them used. It cannot use the normal index because (most likely) the collation of the column definition is different from this collation. As such the index is not usable for the query. There are two options: 1) Specify the collation in the column definition (then there is no need to specify it in the query) 2) Create an expression index which includes the collation and use the same expression in your query eg CREATE INDEX SUBREGION_IDX2 ON SUBREGION COMPUTED BY (lower(NAME collate UNICODE_CI_AI)) and WHERE (lower(NAME collate UNICODE_CI_AI)) starting with 'abona' It should also be possible to specify an expression index for only the collation: CREATE INDEX SUBREGION_IDX2 ON SUBREGION COMPUTED BY (NAME collate UNICODE_CI_AI) (and now you should be able to use the original query) See: http://firebird.1100200.n4.nabble.com/FB2-expression-indexes-and-collations-td1110287.html Mark and Thomas, Thanks for your pointers. Tested it with IBExpert, now I just have to see which is the best/easiest approach to use with Python/SQLAlchemy Werner
Re: [firebird-support] Index/Plan/View question
Hi Thomas, As this is a pure Firebird database issue in respect to collations and indexes, I guess this is independent of the access technology you use? Or does Python/SQLAlchemy generates your mentioned SQL statement behind the scene? The SQL is generated in the Python code and the database schema/create is done based on the SQLAlchemy model (metadata.create_all(fbengine) . I found how to do the create index from Python/SQLalchemy in a way that it is done only on creation of the db and with whatever collation I specify. Werner
