ISO 639-3 extends the ISO 639-2 alpha-3 codes with an aim to cover all known natural languages.

The terms autonym and xenonym also had different applications,[3] thus leaving endonym and exonym as the preferred forms.

語 is the suffix which means 'language'. Unlike English which needs two different nouns for a country and its language, in Japanese, you can simply add 語 after the name of a country to mean the language spoken in that country. (e.g. ドイツ = Germany, ドイツ語 = German, フランス = France, フランス語 = French)

(This term, にほんご, is an alternative spelling of the above term.)

The US Library of Congress has been designated the official registration authority by the ISO and they publish the entire, official, up-to-date list as a trivial to parse text file for free.

Through a linkpin called "Property Value Alias", Unicode has made a 1:1 connection between a script defined, and its ISO 15924 standard.

The following standards are referred to as indispensable by ISO 15924.

Critical to the acceptance of the position of the script subtag was the inclusion of information in the registry to make clear the need to avoid script subtags except where they add useful distinguishing information. Thus, the registry entry for the language subtag "en" (English) has a field called "Suppress-Script" indicating that the script subtag "Latn" should be avoided with that language, since virtually all English documents use the Latin script.

Another problem was the ambiguity of RFC 3066 regarding the generative syntax. The idea of "language-dash-region" language tags was easy enough to grasp; most users didn't read RFC 3066 directly or consider the unstated-but-realized implication that other subtags might sometimes occur in the second position.

Language Range ... matches ... does not match de de, de-CH, de-AT, de-DE, de-1901, de-AT-1901 en, fr-CH

Because ISO code lists were not always free and because they change over time, a key idea was to create a permanent, stable registry for all of the subtags valid in a language tag.

excepting a few grandfathered registrations, all tags are now generative

grandfathered = tags listed in the old registry that are not otherwise redundant (a closed list)

This article is only of historical interest, since the proposed new approach it refers to was published as RFC 4646 and RFC 4647 (collectively known as BCP 47) in September 2006, and have since been revised. The article is now out of date.

BCP stands for Best Current Practice

I'm not sure why MSFT decided to change these codes in the first place. While it might have been a noble goal to follow the IETF standard (though I'm not really familiar with this), the old codes were already out there, and most developers don't benefit by the new codes, nor care about what these codes are called (a code is a code). Just the opposite occurs in fact, since now everyone including MSFT itself has to deal with two codes that represent the same language (and the resulting problems). My own program needs to be fixed to handle this (after a customer contacted me with an issue), others have cited problems on the web (and far more probably haven't publicised theirs), and MSFT itself had to deal with this in their own code. This includes adding both codes to .NET even though they're actually the same language (in 4.0 they distinguished between the two by adding the name "legacy" to the full language name of the older codes), adding special documentation to highlight this situation in MSDN, making "zh-Hans" the parent culture of "zh-CHS" (not sure if it was always this way but it's a highly questionable relationship), and even adding special automated code to newly created "add-in" projects in Visual Studio 2008 (only to later remove this code in Visual Studio 2010, without explanation and therefore causing confusion for developers - long story). In any case, this is not your doing of course, but I don't see how anyone benefits from this change in practice. Only those developers who really care about following the IETF standard would be impacted, and that number is likely very low. For all others, the new codes are just an expensive headache. Again, not blaming you of cours

I feel the pain. It is a normal thing that standards do evolve over time, though, and our software needs to cope with it.

I'm not sure why MSFT decided to change these codes in the first place. While it might have been a noble goal to follow the IETF standard (though I'm not really familiar with this), the old codes were already out there, and most developers don't benefit by the new codes, nor care about what these codes are called (a code is a code).

HAVING avg(score) < 0.75 * (SELECT avg(score) FROM performance_reviews)

When you are dealing with an aggregate of aggregates, it needs to be accomplished in two steps. This can be done using a subquery as the FROM clause, essentially giving us a temporary table to then select from, allowing us to find the average of those averages.

Is there a way to select from multiple custom tables using ActiveRecord QueryMethods? I'm trying to replicate this SQL query using Ruby's ActiveRecord Query Methods. select employee.emplid, address.location from (....) employee, (....) address where employee.emplid = address.emplid

over (order by effdt desc) prev

You can use jsonb_agg and jsonb_array_elements_text to flatten or unnest an array of arrays in PostgreSQL: SELECT jsonb_array_elements_text(jsonb_agg(array_of_arrays)) FROM x;

jsonb_pretty

That's going to be extremely ugly. Nothing about this makes sense. Your JSON schema should just have one object that has {"is_enabled":true}, or something like this {"name":"change","is_enable":true}.

ActiveRecord::Relation::QueryAttribute.new(nil, value, ActiveRecord::Type::Value.new)

DISTINCT ON (1)

SELECT DISTINCT ON (1) t.id, t.name, d.last FROM tbl t LEFT JOIN LATERAL json_array_elements_text(data) WITH ORDINALITY d(last, rn) ON d.last <> t.name ORDER BY d.rn DESC;

There is a (hack-ish, i know, but) more reliable way to do the "cast" in 9.3: ('[' || d::text || ']')::json ->> 0

Unnest in a LEFT JOIN LATERAL (clean and standard-conforming)

unnest ( anyarray ) → setof anyelement Expands an array into a set of rows. The array's elements are read out in storage order.

This article is half-done without your Comment! *** Please share your thoughts via Comment ***

And this has some immediate benefits: more efficiency, significantly faster to process, supports indexing (which can be a significant advantage, as we'll see later), simpler schema designs (replacing entity-attribute-value (EAV) tables with jsonb columns, which can be queried, indexed and joined, allowing for performance improvements up until 1000X!)

Besides efficiency, there are extra ways in which you can benefit from storing JSON in binary form. One such enhancement is the GIN (Generalized Inverted Index) indexes and a new brand of operators that come with them.

SELECT jsonb_array_elements_text(data->'genres') AS genre FROM books WHERE book_id = 1; That will expand the JSON array into a column:

This is an important one, as it will enable us to use the aggregate functions that we are familiar when dealing with relational databases, but in the otherwise counter-intuitive environment of JSON data.

As stated in the title, I am in a situation where I need to return a count of occurrences within an array, that is within a jsonb column.

jsonb_each_text

To find the problematic rows you can use a query like SELECT id FROM t WHERE jsonb_typeof(data_col) <> 'object';

Your attempt should work. There is a mismatch in column name in your query though. The query uses col2 but the table is defined with col1.

select t.* from my_table t, jsonb_each(my_col) as value1(key1, value1), jsonb_each(value1) as value2(key2, value2) where jsonb_typeof(my_col) = 'object' and jsonb_typeof(value1) = 'object' and value2->>'Param3' = '6';

This means that you have a json structure different from that described in your question. You cannot use generic jsonb functions when the json structures differ in each row.

json_array_elements_text ( json ) → setof text jsonb_array_elements_text ( jsonb ) → setof text Expands the top-level JSON array into a set of text values. select * from json_array_elements_text('["foo", "bar"]') → value ----------- foo bar

Writing json_populate_record in the FROM clause is good practice, since all of the extracted columns are available for use without duplicate function calls.

The field/element/path extraction operators return NULL, rather than failing, if the JSON input does not have the right structure to match the request; for example if no such key or array element exists.

Use the -> operator instead of ->> in the reference to children. The way you have it, you'd first cast json / jsonb to text and then back to json.

The clean way to call a set-returning function is LEFT [OUTER] JOIN LATERAL. This includes rows without children. To exclude those, change to a [INNER] JOIN LATERAL

FROM test x1 LEFT JOIN test x2 ON x1.id = (x2.data->>'parent')::INT;

JOIN LATERAL

a leap of faith?

The age_in_years is calculated for every record of the blog table. So, it works like a correlated subquery, but the subquery records are joined with the primary table and, for this reason, we can reference the columns produced by the subquery.

Mitch Hedberg - "I play the guitar. I taught myself how to play the guitar, which was a bad decision... because I didn't know how to play it, so I was a shitty teacher. I would never have went to me."

You can watch videos, but videos can't watch you.

No article or video can replace qualified instruction and experience

jsonb_array_elements('{"test": ["a1", ["b1", "b2"]]}'::jsonb->'test') as json

jsonb_typeof(json)

For a «zoom out» view of my current data, here is a table-free working test :

Careful, Instead of != you may need to IS DISTINCT FROM operator which also compares NULL value

INSERT INTO accounts (id, token, affiliate_code) VALUES (value1, value2, value3) ON CONFLICT (id) DO UPDATE SET token = value2,

import { knex } from 'knex' // this is a function that you call to instantiate knex import { Knex } from 'knex' // this is a namespace, and a type of a knex object

TypeScript type exports changed significantly. While `import Knex from 'knex';` used to import the knex instantiation function, the namespace and the interface for the knex instantiation function/object, there is now a clear distinction between them:

You can use the pg_typeof() function, which also works well for arbitrary values. SELECT pg_typeof("stu_id"), pg_typeof(100)

@7alhashmi: Yes, e.g. the 100 comes from the feature_values table

SELECT * FROM departments AS d, LATERAL (SELECT * FROM employees AS e WHERE e.department_ID = d.department_ID)

In a FROM clause, the LATERAL keyword allows an inline view to reference columns from a table expression that precedes that inline view.

I'm happy to hear this is still useful 4 years later. The Internet is great!

Subqueries appearing in FROM can be preceded by the key word LATERAL. This allows them to reference columns provided by preceding FROM items. (Without LATERAL, each subquery is evaluated independently and so cannot cross-reference any other FROM item.) TL;DR - LATERAL allows subqueries to reference earlier tables.

SQL is all about nested subqueries. It's hard to escape without creating views, but who has time to lookup that syntax and get their DBA's permission to run the DDL?!?

SELECT * FROM ( -- build virtual table of all hours between -- a date range SELECT start_ts, start_ts + interval '1 hour' AS end_ts FROM generate_series( '2017-03-01'::date, '2017-03-03'::timestamp - interval '1 hour', interval '1 hour' ) AS t(start_ts) ) AS cal LEFT JOIN ( -- build virtual table of uptimes SELECT * FROM ( VALUES ('2017-03-01 01:15:00-06'::timestamp, '2017-03-01 02:15:00-06'::timestamp), ('2017-03-01 08:00:00-06', '2017-03-01 20:00:00-06'), ('2017-03-02 19:00:00-06', null) ) AS t(start_ts, end_ts) ) AS uptime ON cal.end_ts > uptime.start_ts AND cal.start_ts <= coalesce(uptime.end_ts, current_timestamp)

FROM generate_series('2017-03-01'::date, '2017-03-03'::timestamp - interval '1 hour', interval '1 hour' ) AS t(start_ts)

In this case it's probably better to have the function return a tabletype, RETURNS table, or define the output with OUT variables. Therecord defining syntax at time of query: SELECT foo() AS (a int, b text);

dlbink is a good example, since thestructure of the input query directly controls the stucture of thereturn type. The sever has no way to deal with that when the query isparsed and planned, so you have to help it out.

>> We have that already, it's named 'json_each_text' > Apparently you haven't looked at json parse/deparse costs ;P Well, a PL function is gonna be none too cheap either. Using something like JSON definitely has lots to recommend it --- eg, it probably won't break when you find out your initial spec for the transport format was too simplistic.

Also, In PostgreSQL, all tables already have a type created by the same name.

You can return the record directly from the UPDATE, which is much faster than calling an additional SELECT statement. Use RETURN QUERY and UPDATE with a RETURNING clause.

( user_id int , user_name varchar , last_activity timestamptz )

It might be a pain because every time you modify function's arguments you'll need to recreate/drop types aswell tho.

I personaly prefer the types approach. it assures that if the function is edited, all the queries will return correct results.

(Diagrams made with AsciiFlow)

Gary Foster has provided a script to migrate to Pundit.

Users who have installed it decided to trust me, and I'm not comfortable transferring that trust to someone else on their behalf. However, if you'd like to fork it, feel free.

This is LAAR. He's a horse my daughter drew.

This was inspired heavily by functionality in the CanCan gem

Please use it, fork it, make it more awesome.

this is kind of a blocker for me

it would significantly simplify the authentication in my application

but it only worked for me when placed above that conditional

        if (opts.credentials !== 'omit') {
            uses_credentials = true;
        }

sveltekit needs to actually set the cookie headers etc. if credentials is true

I don't know how much workaround is it, but for now I'm using this approach:

This is a blocker for me as well, as I don't really understand how to interact with my backend while it's not implemented within sveltekit and uses cookie-based authentication.

This way I'm able to pass cookie back and forth, so this code kind of works:

'set-cookie': response.headers.get('set-cookie')

That said, third party cookies are subject to privacy concerns and are blocked by both browser extensions and even by some browsers themselves, so they shouldn't be relied upon.

If you do need to passthrough your mydomain.com cookies to a thirdparty.com domain, you can technically already do that now by implementing it as an endpoint and calling fetch('/my-endpoint')

I don't really want to re-implement all of my api endpoints to make this work. Is there a way to "mask" the thirdparty endpoints to pass through the cookies?

We need to be really careful about what's 'same origin' because the server has no idea what host/path the various cookies are associated with. It just has a list of cookies that the browser had determined to be relevant for this SSR'd page, and not for any other subrequests.

Cape's Escape Game 2.5th Room

KV is used in 8 of the overlay locales at the moment (CS, DE, HI, JA, PL, PT, SK, and CN). I don't agree with this and believe that Carmen should only reflect country codes that are part of the actual ISO standard.

As of 4 September 2020[update], 98 out of 193 (51%) United Nations (UN) member states, 22 out of 27 (81%) European Union (EU) member states, 26 out of 30 (87%) NATO member states, and 31 out of 57 (54%) Organisation of Islamic Cooperation (OIC) member states have recognised Kosovo. The government of Serbia does not recognise it as a sovereign state.

is a part of the United Kingdom that is variously described as a country, province, territory or region

Similarities in dialects[edit]

The lexicon (or, roughly, the base or essential vocabulary – such as "say" but not "said, tell, told")

scope :with_spec_options, ->(spec_options) { where("specs->'spec_option' @> ?", spec_options.to_json) }

where("specs->'spec_option' ?| array[:options]", options: spec_options)

-- The array on the right side is not considered contained within the -- array on the left, even though a similar array is nested within it: SELECT '[1, 2, [1, 3]]'::jsonb @> '[1, 3]'::jsonb; -- yields false -- But with a layer of nesting, it is contained: SELECT '[1, 2, [1, 3]]'::jsonb @> '[[1, 3]]'::jsonb;

%w{ JsonDashArrow JsonDashDoubleArrow JsonHashArrow JsonHashDoubleArrow JsonbAtArrow JsonbArrowAt JsonbQuestion JsonbQuestionAnd JsonbQuestionOr CastJson }.each do |name| const_set name, Class.new(Binary) end

Rarely does one encounter a question that is interesting, challenging and extremely well-presented. Good job!

And, if you think about it, only the pointy ones are used un-paired in regex construct's. All the other pairs are balanced. I'd steer clear of that pair <>

As far as "Is there really no way to escape the < here?" there is a way... but you're not going to like it: %r<(?#{'<'}!foo)> == %r((?<!foo))

On RHEL-based operating systems (like CentOS and Fedora), use yum

On Debian-based operating systems (like Ubuntu), use apt-get

curl -sSL https://repos.insights.digitalocean.com/install.sh -o /tmp/install.sh

A cookie is associated with a domain. If this domain is the same as the domain of the page you are on, the cookie is called a first-party cookie. If the domain is different, it is a third-party cookie. While the server hosting a web page sets first-party cookies, the page may contain images or other components stored on servers in other domains (for example, ad banners), which may set third-party cookies. These are mainly used for advertising and tracking across the web.

get: function(target, prop, receiver) { return "world"; }

With the help of the Reflect class we can give some accessors the original behavior and redefine others:

The globalThis property provides a standard way of accessing the global this value (and hence the global object itself) across environments. Unlike similar properties such as window and self, it's guaranteed to work in window and non-window contexts. In this way, you can access the global object in a consistent manner without having to know which environment the code is being run in.

Unlike browsers, you can access raw Set-Cookie headers manually using Headers.raw(). This is a node-fetch only API.

(load functions call handle directly, there's no intermediate network requests.)

shouldn't be a just URL it should be for recourceId

Thanks, @Kapsonfire-DE for better understanding the original issue.

Worth noting that in the case where you're proxying /api/ requests to an external server in nginx you can easily do this in handle today:

If you return a Promise from load, SvelteKit will delay rendering until the promise resolves.

should not reference any API keys or secrets, which will be exposed to the client

should run on the same domain as any upstream API servers requiring credentials

When fetch runs on the server, the resulting response will be serialized and inlined into the rendered HTML. This allows the subsequent client-side load to access identical data immediately without an additional network request.

graphqlSync is a relatively recent addition to GraphQL.js that lets you execute a query that you know is going to return synchronously and get the result right away, rather than getting a promise. Since we know that introspection won’t require calling any asynchronous resources, we can safely use it here.

o help us meet this vision, the frontend should use GraphQL in preference to the REST API for new features.

We want the GraphQL API to be the primary means of interacting programmatically with GitLab. To achieve this, it needs full coverage - anything possible in the REST API should also be possible in the GraphQL API.

I've been thinking more about how to best do this. The preferred way might be to use the same domain and have an application load balancer like nginx split traffic on the URL path (e.g. /api). This is for two reasons. Firstly, you might not necessarily want to cookie the primary/apex domain and have the cookie shared across all subdomains. You also might not want to do CORS because preflight requests add latency and CORS adds complication.

contact the API layer using internal DNS hostname of the API service for better latency. It can go straight to the application load balancer instead of passing through the external load balancer first

would be different depending on whether the fetch is internal or external

A litmus test on whether an option belongs to adapter config or kit config, would be to ask whether the option becomes irrelevant when you switch the adapter to static.

export function get(req, res) { if (req.headers.authorization) { res.writeHead(200); res.end(JSON.stringify({ message: req.headers.authorization })); } else { res.writeHead(200); res.end(JSON.stringify({ message: 'unauthorized' })); } }

Closing as kit will be serverless first!

Allows you to use sapper with an API service residing in another server. This is especially useful if your API server is written in another language