フィードアグリゲーター

elein mustain: Having Group By Clauses elein’s GeneralBits

planet postgresql - 2019-03-18(月) 11:33:59

Some people go to great lengths to avoid GROUP BY and HAVING clauses in their queries. The error messages are fussy but they are usually right. GROUP BY and HAVING key words are essential for good SQL reporting.

The primary reason for GROUP BY is to reduce the number of rows, usually by aggregation. It produces only one row for each matching grouping from the input. This allows you to make sophisticated calculations via ordinary SQL.

Fruit Example:

We have some fruit:

item | source | amt | fresh_until ---------+---------+-----+------------ bananas | Chile | 50 | 2019-05-01 bananas | Bolivia | 25 | 2019-04-15 bananas | Chile | 150 | 2019-07-10 apples | USA-WA | 75 | 2019-07-01 apples | USA-CA | 75 | 2019-08-15 apples | Canada | 80 | 2019-08-01 grapes | USA-CA | 120 | 2019-07-15 (7 rows)

This next case allows us to look forward.  Mid-year, what fruits will be available? We do this with the same query as above, however, after the query runs we check the values of min(fresh_until) by using a having clause. HAVING is how you qualify an aggregate.

select item, count(source) as srcs, sum(amt) as tot_amt, min(fresh_until) as min_fresh_until from fruit group by item having min(fresh_until) > '30-jun-2019'; item | srcs | tot_amt | min_fresh_until --------+------+---------+---------------- grapes | 1 | 120 | 2019-07-15 apples | 3 | 230 | 2019-07-01 (2 rows)

All of the apples and grapes will be available mid-year.

A target list may contain non-aggregates and aggregates. Those non-aggregate columns in the target list
should be in the group by clause. The error message says so. The order of the columns in the group by clause matters. It determines how the aggregates are grouped. The order is often hierarchical. What that means to your columns is your focus. It could be fruit, or sources and/or fresh_until date.

Playing Cards Examples

Let’s look at another set of examples that illustrate extracting information on playing cards. You can learn about cards on Wikipedia St

[...]
カテゴリー: postgresql

Venkata Nagothi: How to Replicate PostgreSQL Data to Remote Sites

planet postgresql - 2019-03-15(金) 21:08:35

In a busy database environment with larger size databases, the need for real-time data replication is a common occurrence. Applications often need the production data to be replicated in real-time to remote sites for analytics and other critical business operations needs.

DBAs also need to ensure that the data is replicated continuously to the remote sites to meet various requirements. These requirements, though, may not always be to replicate the whole database; there can also be a need to replicate only a subset of the data (like a Table or set of Tables or data from multiple tables using an SQL for analytics, reporting etc.)

In this blog, we will focus on how to replicate tables to remote databases in real-time.

What is Table-Level Replication?

Table-level replication is the mechanism of replicating the data of a specific table or set of tables from one database (source) to another database (target) hosted remotely in a distributed environment. Table level replication ensures table data is distributed continuously and remains consistent across replicated (target) sites.

Why Use Table-Level Replication?

Table level replication is an essential need in larger, complex, highly distributed environments. In my experience, there was always a need to replicate a set of tables from a production database to a data warehousing for reporting purposes. The data has to be replicated continuously to ensure reports are getting the latest data. In critical environments, staleness of the data cannot be tolerated, so, the data changes happening on production must be replicated immediately to the target site. This can be a real challenge for DBA’s having to forecast various factors to ensure an efficient and smooth table replication.

Let us look at some requirements that table-level replication solves:

  • The reports can run on a database in an environment other than production, like data warehousing
  • A distributed database environment with distributed applications extracting data from multiple sites. In case of distributed web or
[...]
カテゴリー: postgresql

Baron Schwartz: New Book: DevOps for the Database

planet postgresql - 2019-03-15(金) 04:17:36

I’ve written a new 65-page ebook, titled DevOps for the Database. Drawn from what I’ve observed over the last 15 or so years in my own experience as well as that of many others, it tries to answer why and how some organizations deliver value to customers fast, run data-intensive applications with high quality, and have great engineering cultures—and why some don’t.

カテゴリー: postgresql

Play videos from Firefox on your Dreambox

planet PHP - 2019-03-15(金) 00:57:00

Two years after its last release, I finally found the time - and a reason - to rewrite the playVideoOnDreambox browser extension, making it compatible with newer Firefox versions.

The extension adds a button to Firefox that sends the currently playing video to the Dreambox satellite receiver - useful for showing a Youtube video to the family on the large TV screen.

Dreambox' media player does not support playing websites, so the extension needs to extract the URL of the video embedded on the current page. I did not implement this myself, but rely on youtube-dl for this.

Mozilla dropped support for "classic" extensions in Firefox 57; you have to use the "web extension" format now that severely restricts the things you can do. The main problem for me is that extensions cannot execute other programs on the computer anymore (unless the are registered manually with the browser and speak a certain protocol). This broke the my old extension that called youtube-dl directly.

I could have written a youtube-dl proxy script that users would need to register in their browser and that speaks said protocol. But instead I made the Firefox extension rely on the playVideoOnDreambox proxy application, just as the Android app does.

So when your browser shows some video and you click "Play on Dreambox", the extension sends the page URL to the proxy server web app running on some machine in your network. This proxy calls youtube-dl to find the video URL, and then instructs the Dreambox to play the video.

Download

You can download the playVideoOnDreambox firefox extension version 0.6.0 from its homepage or the Mozilla Add-Ons page.

You migh be interested in the playVideoOnDreambox Android app that lets you "share" the video with your satellite receiver.

カテゴリー: php

An OAuth2 middleware for fetch()

planet PHP - 2019-03-15(金) 00:37:00

I was a bit frustrated with the existing offerings for OAuth2 clients in Javascript. I heavily use the Fetch API directly, but Web API’s haven’t really caught up to have deep integration with OAuth2.

We were using client-oauth2, but the minified size of this library was close to 40kb which ended up being a majority of the size of our total Javascript code.

I realized what I really wanted was an OAuth2 client that acts as a middleware-style layer for Fetch, making OAuth2 refreshes transparent, and is a lot lighter in weight.

It only took 2 days to write a replacement that is good enough for my use-case, and I made it open source. It’s currently 3692 bytes minified, is written for typescript and has 0 dependencies.

Find it on Github. The library handles the ‘token’ part of OAuth2 flow, including:

  • authorization_code, password and client_credentials grant types.
  • It keeps an eye on access token expiry, and will automatically call refresh_token if they expired.
  • It exposes a simple hook that gets called when tokens get updated, allowing you to store the new tokens somewhere else (like LocalStorage).

It doesn’t however handle the ‘authorization’ part of OAuth2. Which means that if you use the implicit or authorization_code flow, you are responsible for redirecting the user, and when the user returns setting up the OAuth2 with the right code or accessToken value.

Example

If you’re using the password grant type:

const OAuth2 = require('fetch-mw-oauth2'); const oauth2 = new OAuth2({ grantType: 'password', clientId: '...', clientSecret: '...', userName: '...', password: '...', tokenEndPoint: 'https://auth.example.org/token', });

After this setup is complete, and now you can use the fetch functon on the oauth2 object, instead of the global one. It takes exactly the same parameters, as it just forwards the function with the correct Authorization header:

const result = await oauth2.fetch('https://api.example.org/article', { method: 'POST', body: '...', });

I hope this is useful to anyone else. Take a look at the project for more info!

カテゴリー: php

Take care of non-technical skills

planet PHP - 2019-03-14(木) 20:15:00

Full disclosure: I am one of the founders and current organizers of WeCamp, an event that has a focus on not just technical skills but also personal skills.

In my 20+ years of professional experience in the PHP/software development world, I've worked at many companies and been into many companies as a consultant or freelance developer. Many of the companies I've come in touch with had programs set up for training of their developers. Most of those programs focused on improving technical skills. This makes a lot of sense, because in the current tech world, things change so fast that you need continuous learning to improve. And there is nothing wrong with that.

In recent years, I've seen the focus of training shift a bit from mostly PHP-related subjects to the whole ecosphere of software and tooling around PHP. This is a great shift, because PHP developers don't just write PHP. They use tools like ElasticSearch and memcache, Git and continuous integration, AWS and Azure, and numerous other products that you don't instantly know how to use. Performance, security, quality, it's topics that get more and more attention and rightfully so.

With a few exceptions, however, I've found that many companies still seem to ignore another important part: personal development. I'm talking about things like communication skills, planning skills, a focus on personal happiness. About knowing where you want to go in your life and what to focus on. The human side of the developer. Because, despite what many recruiters would like you to think, a developer is more than just a resource. Developers are just like humans.

I've heard managers complain about developers not having good communication skills, but I've hardly ever seen those same managers look for ways to improve those skills for their developers. I've heard managers complain about the lack of planning skills, or the fact that their developers have a hard time structuring their work day, but I've often seen those same managers only consider technical training for those same developers. And yet, the first non-sponsored link when I search the web for planning skills training is an effective planning skills training. Same for searching for communication skills training. The first result is a learning tree training. And that's just the first results. Go down the results and you'll find a lot more.

One way to focus on more than just technology

As mentioned in my full disclosure at the start, I am one of the founders and current organizers of WeCamp, a 5-day event focussing on improving both technical and non-technical skills that are essential to software development. We've received a lot of positive feedback on the key take-aways of the event being more than just technical skills. I am very proud of that. When we get feedback such as:

To developers, I'd say that the experience is unrivalled by anything in the market today. The coach's focus on your personal development is guaranteed to push you on exactly the points that need improving.

this means we've done our job. We push people to reflect their current position and where they're heading. We push them to evaluate if their current heading is what they really want. But we also help them set goals and achieve those goals. Whether this is about new tech they want to learn or non-tech skills they want to improve. Actually, when we asked what was the best thing about WeCamp 2017 in the evaluation questionnaire, one of the attendees responded with:

The blend of technical and personal development.

In that same questionnaire, when asked about why people would recommend WeCamp, we got things like:

Great learning and life experience and pushes you to get out of your comfort zone in a positive way.

I know I am biased because I'm very much involved in this event, but I really believe that by creating the safe space that we create for people to reflect on their life and career and by getting developers our of their comfort zone, we add a value that not many other events could.

Interested?

If you or your developers are interested in WeCamp, please check out our website. If you have any questions, please do feel free to contact me.

カテゴリー: php

Craig Kerstiens: Fun with SQL: Text and system functions

planet postgresql - 2019-03-14(木) 01:26:00

SQL by itself is great and powerful, and Postgres supports a broad array of more modern SQL including things like window functions and common table expressions. But rarely do I write a query where I don’t want to tweak or format the data I’m getting back out of the database. Thankfully Postgres has a rich array of functions to help with converting or formatting data. These built-in functions save me from having to do the logic elsewhere or write my own functions, in other words I have to do less work because Postgres has already done it for me which I’m always happy about.

We’ve covered a set of functions earlier, today we’re going to look at some different categories of functions to dive deeper.

Manipulating strings

When building a nice cleamn report I’m often doing some cleaning up of the data. This could be as simple as combining my first_name and last_name column. In that case a simple concatenation is quite useful:

SELECT first_name || last_name as name FROM users;

But as you can expect I can do much more. If I’m perfoming some basic search for a keyword or delimiter I can search for the position of that string. Then I can feed that into a case statement:

SELECT CASE WHEN position(',' in my_string) > 0 then True ELSE False END FROM my_table;

Of course if you’re working with some field that is delimited you may want to take the above to first find the correct field. From there you likely would want to split that delimited field up. For that we can use split_part and grab the appropriate section of our delimited field:

SELECT CASE WHEN position(',' in my_string) > 0 then split_part(my_string, ',', 1) ELSE null END FROM my_table;

Finally, if you’re dealing with messy input/machine data there are a number of functions that can be handing for cleaning up that data:

  • substr - allows you to extract a substring
  • rtrim - removes the longest part of a matching string from the right side
  • reverse - for the next time you’re asked to reverse a string in an interview :)
  • regexp_replace - for
[...]
カテゴリー: postgresql

Michael Banck: Integrating Patroni into Debian

planet postgresql - 2019-03-13(水) 21:16:14
Integrating Patroni into Debian Patroni is a PostgreSQL high availability solution with a focus on containers and Kubernetes. Until recently, the available Debian packages had to be configured manually and did not integrate well with the rest of the distribution. For the upcoming Debian 10 "Buster" release, the Patroni packages... 13-03 Michael Banck
カテゴリー: postgresql

Hans-Juergen Schoenig: unaccent: Getting rid of umlauts, accents and special characters

planet postgresql - 2019-03-13(水) 18:20:07

Database people dealing with natural languages are all painfully aware of the fact that encodings, special characters, accents and alike are usually hard to deal with. This is especially true if you want to implement search in a user friendly way.

Consider the following example in PostgreSQL: My name contains a couple of of super inconvenient special characters, which will cause issues for people around the globe. The correct spelling of my surname is “Schönig”, which is pretty hard to type on most keyboards I have seen around the world. And: Who cares about my special characters anyway? People might just want to type in “Schonig” into some search form and find information about me (ideally related to PostgreSQL and database work).

Here is the problem:

test=# SELECT 'Schönig' = 'Schonig'; ?column? ---------- f (1 row) test=# SELECT 'Schönig' = 'Schoenig'; ?column? ---------- f (1 row)

The “=” operator compares those two strings and comes to the conclusion that those two strings are not identical. Therefore the correct answer is “false”. While that is true from a technical point of view it might be a real issue and end users might be unsatisfied with the result. Problems like that can make daily life pretty hard. A solution to the problem is therefore needed.

unaccent: PostgreSQL provides a useful extension

If you want to improve your user experience you can turn to the “unaccent” extension, which is shipped as part of the PostgreSQL contrib package. Installing “unaccent” is really easy:

test=# CREATE EXTENSION unaccent; CREATE EXTENSION

In the next step you can call the “unaccent” function to clean a string and turn it into something more useful. This is what happens when we use the “unaccent” function on my name and the name of my PostgreSQL support company:

test=# SELECT unaccent('Hans-Jürgen Schönig, Gröhrmühlgasse 26, Wiener Neustadt'); unaccent --------------------------------------------------------- Hans-Jurgen Schonig, Grohrmuhlgasse 26, Wiener[...]
カテゴリー: postgresql

PHPerKaigi 2019

php.net - 2019-03-13(水) 17:01:54
カテゴリー: php

PHP Russia 2019

php.net - 2019-03-13(水) 01:51:35
カテゴリー: php

Kristi Anderson: Managing High Availability in PostgreSQL – Part II

planet postgresql - 2019-03-13(水) 00:10:46

Are you deploying PostgreSQL in the cloud and want to understand your options for achieving high availability? In our previous blog post, Managing High Availability in PostgreSQL – Part I, we discussed the capabilities and functioning of PostgreSQL Automatic Failover (PAF) by ClusterLabs. In Part II, we’re introducing you to an alternative open source tool, Replication Manager from 2ndQuadrant, to be closely followed by Part III where we dive into our third alternative, Patroni by Zalando.

Replication Manager (repmgr)

repmgr is an open-source tool suite developed by 2ndQuadrant for managing replication and failover of your PostgreSQL clusters. It provides the tools to setup, configure, manage, and monitor replication of PostgreSQL, and also enables you to perform manual switchover and failover tasks using repmgr utility. This free tool supports and enhances PostgreSQL’s built-in streaming replication.

Replication Manager provides two main tools to manage replication and failover of PostgreSQL.

repmgr
  • A command-line interface utility which enables you to perform various administrative tasks.
  • repmgr enables you to setup standby servers, promote standbys, do a switchover, and monitor the status of your PostgreSQL cluster.
  • It also provides dry run option for almost all of the administrative commands.
repmgrd

This is the daemon which:

  • Actively monitors the PostgreSQL clusters and performs necessary actions based on the state of the cluster.
  • Performs automatic failover in case the primary node goes down by promoting the most eligible standby as the new primary.
  • Provides an option to monitor and store the data related to replication performance.
  • Provides notification by invoking the user scripts for registered events.
How it Works

repmrg not only manages the replication of PostgreSQL clusters, but also has capabilities for setting up the standby servers for replication. Following the initial installation, we need to make changes to the repmgr configuration file (repmgr.conf) with the required details on each server.

[...]
カテゴリー: postgresql

415 Unsupported Media Type

planet PHP - 2019-03-13(水) 00:00:00

When a server receives a request with a body it doesn’t understand, it should return 415 Unsupported Media Type. Most commonly this is a good response for for example a POST or PUT request with an unknown Content-Type header.

The specification says that aside from inspecting the Content-Type header, the server may also return this after inspecting the body.

What this means is that if the client sent a request with a supported Content-Type, it may still return 415 if the contents of the request body were not supported by the server.

For example, a server might support specific JSON bodies, but the contents of the JSON payload didn’t validate, perhaps because it was missing a required property.

However, for the latter case it might be better to use 422 Unprocessable Entity. The description in the standards for 422 is slightly contradicting with the one for 415, but 422 seems to be more specifically for cases where the Content-Type was correct, the request was parsable, but semantically incorrect.

I would suggest the following approach to deciding the right status code:

  • If the Content-Type was not supported, use 415.
  • Else: If the request was not parsable (broken JSON, XML), use 400 Bad Request.
  • Else: If the request was parsable but the specific contents of the payload were wrong (due to validation or otherwise), use 422
Example POST /new-article HTTP/1.1 Content-Type: text/html <h1>Another day, another blog post</h1> HTTP/1.1 415 Unsupported Media Type Content-Type: application/json {"error": "This endpoint only supports text/markdown for new articles"} References
カテゴリー: php

Web Summer Camp 2019

php.net - 2019-03-12(火) 20:48:51
カテゴリー: php

Interview with Keith “Danger” Casey

planet PHP - 2019-03-12(火) 20:30:00

@caseysofyware Show Notes

Audio

This episode is sponsored by


The post Interview with Keith “Danger” Casey appeared first on Voices of the ElePHPant.

カテゴリー: php

Laracon EU 2019 Madrid

php.net - 2019-03-12(火) 02:28:17
カテゴリー: php

Laracon EU 2019 Amsterdam

php.net - 2019-03-12(火) 02:26:39
カテゴリー: php

Regina Obe: PostGIS 2.5.2, 2.4.7, 2.3.9 Released

planet postgresql - 2019-03-11(月) 09:00:00

The PostGIS development team is pleased to provide bug fix 2.5.2, 2.4.7, and 2.3.9 for the 2.5, 2.4, and 2.3 stable branches.

These are the first versions to be able to compile against Proj 6.0.0, You must upgrade to these if you are using Proj 6.

2.5.2 This release supports PostgreSQL 9.3-11 (will compile against PostgreSQL 12, but not pass tests. Use only for pg_upgrade. You are encouraged to use the PostGIS 3.0 unreleased branch with PostgreSQL 12 , which has features specifically designed to take advantage of features new in PostgreSQL 12).

2.4.7 This release supports PostgreSQL 9.3-10.

2.3.9

This release supports PostgreSQL 9.2-10.

View all closed tickets for 2.5.2, 2.4.7, 2.3.9.

After installing the binaries or after running pg_upgrade, make sure to do:

ALTER EXTENSION postgis UPDATE;

— if you use the other extensions packaged with postgis — make sure to upgrade those as well

ALTER EXTENSION postgis_sfcgal UPDATE; ALTER EXTENSION postgis_topology UPDATE; ALTER EXTENSION postgis_tiger_geocoder UPDATE;

If you use legacy.sql or legacy_minimal.sql, make sure to rerun the version packaged with these releases.

カテゴリー: postgresql

Bulgaria PHP Conference 2019

php.net - 2019-03-10(日) 17:32:05
カテゴリー: php

WordPress and HTTPS-terminating proxies

planet PHP - 2019-03-09(土) 20:15:00

A blog I am writing for was looking for a new place to host their website. Since we have a nice cluster with Rancher up and running, I offered to host the site. It's WordPress, so PHP, so how hard could it be, right?

I spent quite a few hours migrating everything. The initial migration to Docker was not that hard. There is a great official WordPress image for Docker, which makes it extremely easy to set up a new WordPress site in Docker.

The next thing is handling file uploads. Using the do-spaces-sync plugin this was easily set up to use DigitalOcean Spaces. It took a while to upload all images from the old wp-content/uploads to Spaces, but once that was done, I had it working immediately after setting it up. So far, this whole migration was a breeze.

Until I flipped the switch on the DNS and pointed it to our new hosting. I immediately got caught in an infinite redirect loop, and I had no idea why. I've spent hours turning off plugins, turning them on again. Debugging everything, watching logs. I could not figure it out. In the headers I did find a header saying that the redirect came from WordPress:

X-Redirect-By: WordPress

Eventually, I tried explaining the problem in the #wordpress channel in the PHPNL slack and as I'm typing my explanation something dawns on me...

Our Rancher setup has a load balancer that terminates the HTTPS then forwards an internal request to the container using http. But in WordPress, I have configured the siteurl to be https://. So WordPress gets a request using http, figures out it should be using https, and redirects. This causes the infinite redirect loop!

Of course, I wasn't the first to encounter this problem. Once I know what the problem was, searching the Internet quickly gave me the solution. In Wordpress Codex of course. The only thing I needed to do was add a single line to my .htaccess file:

SetEnvIf X-Forwarded-Proto https HTTPS

Once I did that, rebuilt my containers and deployed them to Rancher, the problem was solved. All of a sudden, everything worked.

カテゴリー: php

ページ