 | Jeroen Wiert Pluimer… on Does Odido (the old T-Mobile N… |
 | Lars Fosdal on Security alarm provider Woonve… |
 | Thomas Mueller on Question got closed in May 202… |
 | Thaddy de Koning on Formulier voor bewindvoerders… |
| Thaddy de Koning on Formulier voor bewindvoerders… |
Posted by jpluimers on 2019/10/31
A while back I bumped into [WayBack] SQL Code Smells – Simple Talk, which is an extensive article covering all sorts of SQL related code smells.
It reminds me that one day I need to dig up some old links on other code smells as well.
–jeroen
via: [WayBack] Martin Fowler on Twitter: “It’s an old anti-pattern, and sadly is still going strong: The Entity Service Antipattern.”
Posted in Database Development, Development, SQL | Leave a Comment »
Posted by jpluimers on 2019/10/30
I could not find any vendors/architectures have public database servers.
So there is no good way to go beyond SQLFiddle (of which I wrote before in SQL Fiddle | A tool for easy online testing and sharing of database problems and their solutions and David Rodriguez: a few nice posts on SQL (via: Google+)), that does not provide database access, but allows you to fire SQL statements onto these architectures:
I get the thing (it is very hard to secure an “over the internet” connection to a database server; do NOT do this: [WayBack] connectivity – Connect to SQL Server over Internet – Database Administrators Stack Exchange), so the alternative is to run locally.
If you run locally, there are plenty of example/demo database, like:
- download AdventureWorks-oltp-install-script.zip and extract the zip file to the C:\Samples\AdventureWorks folder.
- Open
C:\Samples\AdventureWorks\instawdb.sqlin SQL Server Management Studio and follow the instructions at the top of the file.
In step 2, do not forget to set the query window to SQLCMD syntax. Or just run the script using the [WayBack] sqlcmd Utility | Microsoft Docs (which reminds me I should update SQL Sever: batch files to find instances and sqlcmd.exe/osql.exe)
–jeroen
Posted in Database Development, Development, MySQL, OracleDB, PostgreSQL, SQL, SQL Server, SQLite | Leave a Comment »
Posted by jpluimers on 2019/10/10
Sometimes you want the opposite of SQL generation (which most, if not all, ORMs do behind the scenes): SQL parsing.
Some links that should get me started:
Visual SQL Query Builder, SQL Parser and Analyser component for Delphi and C++Builder for use with VCL, TNT or DevExpress controls
The ZeosLib is a set of database components for MySQL, PostgreSQL, Interbase, Firebird, MS SQL, Sybase, Oracle and SQLite for Delphi, FreePascal/Lazarus, Kylix and C++ Builder.
Download SQL Parser for Delphi for free. Develope a string parser that is capable to parse SQL statements into tokens, allows changing theese tokens and rebuilding (modified) SQL statement
–jeroen
Posted in Database Development, Development, Software Development, SQL | 2 Comments »
Posted by jpluimers on 2019/10/01
On my research list: ApexSQL PLAN analysis tool released in 2017. It requires SSMS which you can get at [WayBack] Download SQL Server Management Studio (SSMS) | Microsoft Docs.
More info:
Via:
–jeroen
Posted in Database Development, Development, Software Development, SQL, SQL Server | Leave a Comment »
Posted by jpluimers on 2019/09/11
The RDB$RELATIONS.RDB$RELATION_TYPE was introduced in Firebird 2.1 with these enumeration values:
enum rel_t {
rel_persistent = 0,
rel_view = 1,
rel_external = 2,
rel_virtual = 3,
rel_global_temp_preserve = 4,
rel_global_temp_delete = 5
};
You should access it assuming NULL means zero (0), so you better treat it using [WayBack] COALESCE() like COALESCE(RDB$RELATIONS.RDB$RELATION_TYPE, 0).
Background information:
RDB$RELATIONS.RDB$RELATION TYPE–jeroen
Posted in Database Development, Development, Firebird | Leave a Comment »
Posted by jpluimers on 2019/09/05
Since I likely need these [WayBack] SQL Language Changes in the future (some breaking Firebird 2.x habits).
The odd thing is that I got there looking for “no column name specified for column number 1 in derived table” when doing some weird CTE query things in Firebird 2.5x
–jeroen
Posted in Database Development, Development, Firebird | Leave a Comment »
Posted by jpluimers on 2019/08/29
Via [WayBack] SQL select only rows with max value on a column, I bumped into http://sqlfiddle.com/#!9/a6c585/1:
Application for testing and sharing SQL queries.
Source: [WayBack] SQL Fiddle | A tool for easy online testing and sharing of database problems and their solutions.
It is a cool site, currently supporting these SQL back-ends:
You can host it yourself using [WayBack] GitHub – zzzprojects/sqlfiddle2: New version of SQL Fiddle based on OpenIDM (in the past it was [WayBack] GitHub – zzzprojects/sqlfiddle)
Other resources for learning and playing around with SQL:
–jeroen
Posted in Database Development, Development, MySQL, OracleDB, PostgreSQL, Software Development, SQL, SQL Server | Leave a Comment »
Posted by jpluimers on 2019/08/28
Sometimes you want to count data in all tables on a database to get a feel for the orders of magnitude, but you cannot use the approximated example in How to speed up Count(*) in Interbase/Firebird – Stack Overflow as those require primary keys.
Then the below script can help: it generates the right queries as a union all ordering by the count in the tables.
Example generated code on C:\Program Files (x86)\Firebird\Firebird_2_5\examples\empbuild\EMPLOYEE.FDB:
SQL
--------------------------------------------------------------------------------------------------
select 128 as id, 'COUNTRY' as name, count(*) from "COUNTRY" union all
select 129 as id, 'JOB' as name, count(*) from "JOB" union all
select 130 as id, 'DEPARTMENT' as name, count(*) from "DEPARTMENT" union all
select 131 as id, 'EMPLOYEE' as name, count(*) from "EMPLOYEE" union all
select 133 as id, 'PROJECT' as name, count(*) from "PROJECT" union all
select 134 as id, 'EMPLOYEE_PROJECT' as name, count(*) from "EMPLOYEE_PROJECT" union all
select 135 as id, 'PROJ_DEPT_BUDGET' as name, count(*) from "PROJ_DEPT_BUDGET" union all
select 136 as id, 'SALARY_HISTORY' as name, count(*) from "SALARY_HISTORY" union all
select 137 as id, 'CUSTOMER' as name, count(*) from "CUSTOMER" union all
select 138 as id, 'SALES' as name, count(*) from "SALES" order by 3
Example output on C:\Program Files (x86)\Firebird\Firebird_2_5\examples\empbuild\EMPLOYEE.FDB:
ID NAME COUNT 133 PROJECT 6 128 COUNTRY 14 137 CUSTOMER 15 130 DEPARTMENT 21 135 PROJ_DEPT_BUDGET 24 134 EMPLOYEE_PROJECT 28 129 JOB 31 138 SALES 33 131 EMPLOYEE 42 136 SALARY_HISTORY 49
The generation code below uses a few tricks:
select rdb$relation_namefrom rdb$relationswhere rdb$view_blr is nulland (rdb$system_flag is null or rdb$system_flag = 0);ID the negation of SCORE)SELECT p.name, p.score, COUNT(others.score) + 1 AS "rank"FROM players pLEFT JOIN players others ON others.score > p.scoreGROUP BY 1, 2ORDER BY "rank"The rank helps me distinguish the last row (for the order by 3 clause) and other rows (for the union all clauses).
Generation code:
with tables(id, name) as (
-- http://www.firebirdfaq.org/faq376/
select r.RDB$RELATION_ID as id, trim(r.RDB$RELATION_NAME) as name
from RDB$RELATIONS r
where 1=1
and (r.RDB$SYSTEM_FLAG is null or r.RDB$SYSTEM_FLAG = 0)
and r.RDB$VIEW_BLR is null
order by 1
),
ranked_tables(id, rank, name) as (
-- http://www.firebirdfaq.org/faq343/
select tables.ID, count(others.id)+1 as "rank", tables.NAME
from tables
left join tables others on others.ID < tables.ID
group by "ID", "NAME"
order by "rank"
),
parts(id, rank, name, suffix) as (
select ranked_tables.id,
ranked_tables.rank,
ranked_tables.name,
case
when ranked_tables.rank = 1 then 'union all' -- first
when ranked_tables.rank = (select count(*) from tables) then 'order by 3' --last
else 'union all' -- middle
end as suffix
from ranked_tables
)
select -- parts.id, parts.rank, parts.name, parts.suffix,
'select '||parts.id||' as id, '''||parts.name||''' as name, count(*) from "'||parts.name||'" '||parts.suffix||'' as SQL
from parts
order by parts.id
–jeroen
Posted in Database Development, Development, Firebird, Software Development, SQL | Leave a Comment »
Posted by jpluimers on 2019/08/27
via [WayBack] « In practice, the uptime of systems that favor availability have not proven greater than what can be achieved with consistent systems. » Aua, der hat gesessen. – Kristian Köhntopp – Google+:
–jeroen
Posted in Cloud Development, Database Development, Development, Software Development | Leave a Comment »
Posted by jpluimers on 2019/08/06
For my link archive in case of link rot: [WayBack/Archive.is] 28800 Embarcadero DBX ADO.NET 2.0 driver for InterBase (version 17.0)
Note these are the same as the ones shipping with the Interbase XE7 developer edition.
The less old alternative from Embarcadero alternative is the ODBC driver: [WayBack/Archive.is] 28975 Embarcadero InterBase ODBC Driver for Windows, 32-bit and 64-bit
Maybe it is a good idea to look into [WayBack] All IBProvider news Firebird and InterBase driver for OLE DB and ADO.NET
Posted in .NET, Database Development, Development, InterBase, Software Development | Leave a Comment »
The Wiert Corner - irregular stream of stuff 