[Firebird-devel] [FB-Tracker] Created: (CORE-3683) recursive query with GROUP BY in root part: wrong results if no index exists for fields that are grouped
recursive query with GROUP BY in root part: wrong results if no index exists for fields that are grouped Key: CORE-3683 URL: http://tracker.firebirdsql.org/browse/CORE-3683 Project: Firebird Core Issue Type: Bug Reporter: Pavel Zotov DDL: -- table of some routes between cities and additional info (names from cenral region of Russia): recreate table rdeps(parent varchar(32),child varchar(32), parent_type int, child_type int, f01 int); commit; insert into rdeps values( 'MOSCOW', 'TULA', 0, 5, 21); insert into rdeps values( 'MOSCOW', 'TULA', 0, 5, 22); insert into rdeps values( 'TULA', 'OREL', 5, 5, 51); insert into rdeps values( 'TULA', 'OREL', 5, 5, 52); insert into rdeps values( 'TULA', 'LIPETSK', 5, 2, 71); insert into rdeps values( 'TULA', 'LIPETSK', 5, 2, 72); insert into rdeps values( 'TULA', 'RYAZAN', 5, 5, 61); insert into rdeps values( 'TULA', 'RYAZAN', 5, 5, 62); insert into rdeps values( 'OREL', 'KURSK', 5, 5, 81); insert into rdeps values( 'OREL', 'KURSK', 5, 5, 82); insert into rdeps values( 'LIPETSK','VORONEZH', 5, 2, 71); insert into rdeps values( 'LIPETSK','VORONEZH', 5, 2, 72); insert into rdeps values( 'RYAZAN','MUROM', 5, 5, 61); insert into rdeps values( 'RYAZAN','MUROM', 5, 5, 62); commit; Task: get all possible routes starting from MOSCOW Query: with recursive rd as( select d.parent parent ,d.child from rdeps d group by d.parent,d.child -- <<< we need this grouping to eliminate duplicates ) ,cr as( select 0 step,parent,child,cast(parent as varchar(32000))||'->'||child routes from rd where rd.parent='MOSCOW' UNION ALL select x.step+1,rd.parent,rd.child,x.routes||'->'||rd.child from cr x join rd on x.child=rd.parent ) select step,routes from cr order by step,routes Result (WRONG): == STEPROUTES 0 MOSCOW->TULA 1 MOSCOW->TULA->LIPETSK 1 MOSCOW->TULA->RYAZAN 1 MOSCOW->TULA->RYAZAN 2 MOSCOW->TULA->LIPETSK->VORONEZH 2 MOSCOW->TULA->RYAZAN->MUROM 2 MOSCOW->TULA->RYAZAN->MUROM (dupliates MOSCOW->TULA->RYAZAN, MOSCOW->TULA->RYAZAN->MUROM; missing MOSCOW->TULA->OREL) Add [unique] index: === --create unique index rdeps_unq on rdeps(parent, child, f01); create index rdeps_unq on rdeps(parent, child, f01); Repeat the query - result will be correct: STEP ROUTES 0 MOSCOW->TULA 1 MOSCOW->TULA->LIPETSK 1 MOSCOW->TULA->OREL 1 MOSCOW->TULA->RYAZAN 2 MOSCOW->TULA->LIPETSK->VORONEZH 2 MOSCOW->TULA->OREL->KURSK 2 MOSCOW->TULA->RYAZAN->MUROM PS. The result will be also correct if we replace GROUP BY in root member with DISTINCT - but these operations are not equal. -- This message is automatically generated by JIRA. - If you think it was sent incorrectly contact one of the administrators: http://tracker.firebirdsql.org/secure/Administrators.jspa - For more information on JIRA, see: http://www.atlassian.com/software/jira -- All the data continuously generated in your IT infrastructure contains a definitive record of customers, application performance, security threats, fraudulent activity, and more. Splunk takes this data and makes sense of it. IT sense. And common sense. http://p.sf.net/sfu/splunk-novd2d Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] The new Firebird OO API
On 12/02/11 11:26, Pierre Y. wrote: > Hi, > > I advanced a bit more with this new API but I just absolutely don't > see how to deal with FbMessage. What is it ? How do I have to use it > to access (or send ?) data ? > > Thank you for your help, Regards, There is a way to create FbMessage, using boost macros - more or less like it's done in UDR engine samples. But there is unfortunately still no sample of using it exactly like you want. And even that boost macros are not SQLDA. Something similar to SQLDA is in our todo plans. -- All the data continuously generated in your IT infrastructure contains a definitive record of customers, application performance, security threats, fraudulent activity, and more. Splunk takes this data and makes sense of it. IT sense. And common sense. http://p.sf.net/sfu/splunk-novd2d Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] The new Firebird OO API
On 02/12/2011 05:26, Pierre Y. wrote: > Hi, > > I advanced a bit more with this new API but I just absolutely don't > see how to deal with FbMessage. What is it ? How do I have to use it > to access (or send ?) data ? > > https://github.com/asfernandes/fbstuff/blob/master/src/test/v3api/StaticMessageTest.cpp Adriano -- All the data continuously generated in your IT infrastructure contains a definitive record of customers, application performance, security threats, fraudulent activity, and more. Splunk takes this data and makes sense of it. IT sense. And common sense. http://p.sf.net/sfu/splunk-novd2d Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
[Firebird-devel] Firebird client package in Ubuntu 11.04
Hello, All. I wonder: why libfbclient2 package has in dependencies ICU? I wanted to install client only, but had to download 7MB of needless crap. -- SY, SD. -- All the data continuously generated in your IT infrastructure contains a definitive record of customers, application performance, security threats, fraudulent activity, and more. Splunk takes this data and makes sense of it. IT sense. And common sense. http://p.sf.net/sfu/splunk-novd2d Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Firebird client package in Ubuntu 11.04
Hello Dimitry, > I wonder: why libfbclient2 package has in dependencies ICU? > I wanted to install client only, but had to download 7MB of needless crap. Although it doesn't help for your scenario, I had troubles with the integrated Firebird 2.5.0 package under Ubuntu 11.04 ICU-related. I'm really no Linux geek, but pretty much the trace plugin didn't work due to some Unicode / ICU issues. I guess some ICU version issues. I then installed 2.5.0 on 11.04 via the project TAR ball and since that, everything works fine. Regards, Thomas -- All the data continuously generated in your IT infrastructure contains a definitive record of customers, application performance, security threats, fraudulent activity, and more. Splunk takes this data and makes sense of it. IT sense. And common sense. http://p.sf.net/sfu/splunk-novd2d Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Firebird client package in Ubuntu 11.04
02.12.2011 13:04, Thomas Steinmaurer wrote: > I then installed 2.5.0 on 11.04 via the project TAR ball and since that, > everything works fine. Right, but the project's TAR also doesn't offer client-only install and has the same 7MB in size. -- SY, SD. -- All the data continuously generated in your IT infrastructure contains a definitive record of customers, application performance, security threats, fraudulent activity, and more. Splunk takes this data and makes sense of it. IT sense. And common sense. http://p.sf.net/sfu/splunk-novd2d Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] The new Firebird OO API
>> I advanced a bit more with this new API but I just absolutely don't >> see how to deal with FbMessage. What is it ? How do I have to use it >> to access (or send ?) data ? >> >> > https://github.com/asfernandes/fbstuff/blob/master/src/test/v3api/StaticMessageTest.cpp Thank you Adriano. I think I understood the concept, for now, it is necesseary to derive FbMessage and create static structures to map the input parameters and the output parameters. But... it seems to be very complex, can you explain why do you have to generate BLR code at several places ? What is actually in FbMessage returned by attachment->execute(...&out) ? Many thanks for your help, I really want to understand. -- All the data continuously generated in your IT infrastructure contains a definitive record of customers, application performance, security threats, fraudulent activity, and more. Splunk takes this data and makes sense of it. IT sense. And common sense. http://p.sf.net/sfu/splunk-novd2d Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] The new Firebird OO API
On 02/12/2011 14:49, Pierre Y. wrote: >>> I advanced a bit more with this new API but I just absolutely don't >>> see how to deal with FbMessage. What is it ? How do I have to use it >>> to access (or send ?) data ? >>> >>> >> https://github.com/asfernandes/fbstuff/blob/master/src/test/v3api/StaticMessageTest.cpp > Thank you Adriano. I think I understood the concept, for now, it is > necesseary to derive FbMessage and create static structures to map the > input parameters and the output parameters. > > But... it seems to be very complex, can you explain why do you have to > generate BLR code at several places ? What is actually in FbMessage > returned by attachment->execute(...&out) ? > > Many thanks for your help, I really want to understand. > > The thing is that this is how Firebird really always worked at the provider level. What this example show us: 1) It's easy to construct messages of know format with macros. It's still not completely elegant, so if anyone has a suggestion... 2) It's possible to construct dynamic messages, but we should improve this. Adriano -- All the data continuously generated in your IT infrastructure contains a definitive record of customers, application performance, security threats, fraudulent activity, and more. Splunk takes this data and makes sense of it. IT sense. And common sense. http://p.sf.net/sfu/splunk-novd2d Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
[Firebird-devel] [FB-Tracker] Created: (CORE-3684) update only if different
update only if different
Key: CORE-3684
URL: http://tracker.firebirdsql.org/browse/CORE-3684
Project: Firebird Core
Issue Type: New Feature
Reporter: Philip Williams
Priority: Trivial
Allow update and insert-or-update statements to have a flag for "no trivial
changes", if the update would have no effect except locking the row. Something
like "update people set name = 'Bob' where id = 5 ONLY IF DIFFERENT". This is
useful for migration / data-loading scenarios where you're running large import
datasets, but you just want the data to end up a certain way, you don't want
record locks / versions on rows that didn't really need to be touched. It'd be
entirely optional, only for situations where you know you don't want do-nothing
updates to trigger on-update triggers or place locks, and you're okay with it.
To accomplish this currently, a simple:
update or insert into people (name, id) values ('Bob', 5);
becomes
if (not exists(select id from people where id = 5)) then insert into people
(name, id) values ('Bob', 5)
else update people set name = 'Bob' where id = 5 and name is distinct from
'Bob';
-- not so bad for one field, but much more annoying for high-column-count tables
I know it's a very niche feature.
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://tracker.firebirdsql.org/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
--
All the data continuously generated in your IT infrastructure
contains a definitive record of customers, application performance,
security threats, fraudulent activity, and more. Splunk takes this
data and makes sense of it. IT sense. And common sense.
http://p.sf.net/sfu/splunk-novd2d
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
[Firebird-devel] [FB-Tracker] Created: (CORE-3685) ISQL extract metadata one file per object
ISQL extract metadata one file per object - Key: CORE-3685 URL: http://tracker.firebirdsql.org/browse/CORE-3685 Project: Firebird Core Issue Type: Improvement Components: ISQL Reporter: Valdir Stiebe Junior Priority: Minor It would be very helpful if we could extract the metadata on separated files for each object. The command line could be: isql -xf -u sysdba -p masterkey The result would be text files on target folder with a file for each table/stored procedure TargetFolder/Domain DOMAIN_1.sql TargetFolder/Table TABLE_1.sql TargetFolder/Table TABLE_2.sql TargetFolder/Trigger TABLE_1_BI.sql TargetFolder/Procedure STP_1.sql TargetFolder/Grants.sql An extended version could use a folder structure to represent namespaces and types of objects. TargetFolder/Domains/DOMAIN_1.sql TargetFolder/Tables/TABLE_1.sql And so on. I see two main use cases for this feature. 1. Allow store DDL on SVN (or any source code versioning system) repositories; 2. Allow easy database metadata comparison with tools like beyond compare, tortoise merge etc; Please point me to the right direction if this is already possible with ISQL. -- This message is automatically generated by JIRA. - If you think it was sent incorrectly contact one of the administrators: http://tracker.firebirdsql.org/secure/Administrators.jspa - For more information on JIRA, see: http://www.atlassian.com/software/jira -- All the data continuously generated in your IT infrastructure contains a definitive record of customers, application performance, security threats, fraudulent activity, and more. Splunk takes this data and makes sense of it. IT sense. And common sense. http://p.sf.net/sfu/splunk-novd2d Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Firebird client package in Ubuntu 11.04
-=| Dimitry Sibiryakov, 02.12.2011 12:52:31 +0100 |=- >I wonder: why libfbclient2 package has in dependencies ICU? >I wanted to install client only, but had to download 7MB of needless crap. > libfbclient depends on firebird2.5-common, which depends on ICU because if libfbintl. If the client doesn't need fbintl, then I guess I can move it in the -server-common package. Damyan, Maintainer of the firebird packages in Debian, from where Ubuntu takes them. -- All the data continuously generated in your IT infrastructure contains a definitive record of customers, application performance, security threats, fraudulent activity, and more. Splunk takes this data and makes sense of it. IT sense. And common sense. http://p.sf.net/sfu/splunk-novd2d Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
