The Wiert Corner – irregular stream of stuff

Jeroen W. Pluimers on .NET, C#, Delphi, databases, and personal interests

  • My badges

  • Twitter Updates

  • My Flickr Stream

  • Pages

  • All categories

  • Enter your email address to subscribe to this blog and receive notifications of new posts by email.

    Join 2,465 other followers

Archive for August, 2019

SQL Fiddle | A tool for easy online testing and sharing of database problems and their solutions.

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: [WayBackSQL 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:

  • MySQL 5.6
  • Oracle 11g R2
  • PostgreSQL 9.6
  • PostgreSQL 9.3
  • SQLite (WebSQL)
  • SQLite (SQL.js)
  • MS SQL Server 2014

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 »

Structure and Interpretation of Test Cases : Kevlin Henney on Vimeo

Posted by jpluimers on 2019/08/28

Must watch when you do unit testing (no matter the environment): [WayBackStructure and Interpretation of Test Cases : Kevlin Henney on Vimeo which he presented at Javazone 2018 [Archive.is]

via [WayBack] Kevlin Henney – Google+

–jeroen

Posted in Agile, Conference Topics, Conferences, Development, Event, Software Development, Unit Testing | Leave a Comment »

Passing multiple parameters to a Python method: the * tag

Posted by jpluimers on 2019/08/28

I had to pass parameters to a method so they became a list:

    threadManager.append(
        UrlMonitorThread(monitor, "http://%s" % targetHost),
        SmtpMonitorThread(monitor, targetHost, 25),
        SmtpMonitorThread(monitor, targetHost, 587),
        SshMonitorThread(monitor, targetHost, 22))

This appeared much easier than I anticipated:

    def append(self, *threads):
        for thread in threads:
            self.threads.append(thread)

It uses the * tag which is explained here:

–jeroen

Posted in Development, Python, Scripting, Software Development | Leave a Comment »

Counting rows for all tables in Firebird

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:

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

Read the rest of this entry »

Posted in Database Development, Development, Firebird, Software Development, SQL | Leave a Comment »

git add remote with tracking

Posted by jpluimers on 2019/08/27

Since I keep forgetting this and the top search for “git remote add” “tracking” do not answer it (see for instance …):

git remote add gitlab https://user@gitlab.com/group/subgroup/repository.git -t master

This adds a new gitlab remote on the repository path https://user@gitlab.com/group/subgroup/repository.git tracking branch master.

–jeroen

Posted in Development, DVCS - Distributed Version Control, git, Source Code Management | Leave a Comment »

 
%d bloggers like this: