When there is no matching host name in the forward parsing file, how to make bind query the domain name from the public dns

I own a second-level domain name a.com, and I have configured an A record for www.a.com in the public network. If public dns like 8.8.8.8 is used, it can be resolved normally.
At the same time, I set up a Bind DNS service on the internal network. In the named.conf file, I configured:

    zone "a.com" IN {
     type master;
     file "/var/named/a.com.zone";
};

And in the a.com.zone file, I configured:

  @   IN  SOA     dns1.a.com. admin.a.com. (
        2011071001  ;Serial
        3600        ;Refresh
        1800        ;Retry
        604800      ;Expire
        86400       ;Minimum TTL
)
@       IN  NS          dns1.goatgames.com.
dns1.a.com. IN  A   192.168.27.178
test    IN  A   192.168.29.252

But when using the BIND dns, I can only resolve test.a.com, but not www.a.com. Why does BIND not use recursive query to continue to request resolution from the public network when the file test.a.zone cannot match the host name ‘www’?By the way´╝îI have configure ‘recursion yes’ in the named.conf file

Sheets: query to count each type of result for every student

I have a table like this.

Test  | Student  | Result
--------------------------
TestA | Ray      | pass
TestA | Edwards  | fail
TestA | Phillips | pass
TestA | Holland  | pass
TestB | Chandler | fail
TestB | Burke    | fail

I want to convert it to a table like this.

Test  | Pass | Fail
-------------------
TestA | 75%  |  25%
TestB |  0%  | 100%

My goal is to make a chart that shows, for each test, the ratio of pass vs fail. I want to use a Stacked Column Chart.

So far I came up with this.

=QUERY(A:C, "select A, COUNT(B) where A is not null group by A", 1)

However, COUNT(B) counts the total of Result for each test. I want to know how many passed and how many failed.

This is almost what I want. Except TestA and TestB don’t have the same number of entries, so I want to normalize pass/fail values as a percentage.

=QUERY(A:C, "select A, COUNT(A) where A is not null group by A pivot C", 1)

Does the number of columns been selected affect query speed?

I know some sql but don’t quite understand the things behind the scene. And this question came to me the other day while I am taking shower…

If we compare these two queries below:

a. select * from table_abc;

b. select id, name, timestamp from table_abc;

which query is (theoretically) faster? My guess is a because in query b it takes time to pick out the 3 columns I want. But that’s counterintuitive at the same time because that means getting less information takes longer. All comments/answers are highly appreciated!

taxonomy terms – alter query view by adding fields in an andGroup within an orGroup

I have a multilanguage site with two languages: English and Dutch.
Not all nodes are created in both languages. Some nodes even have no language (‘und’, ‘zxx’).

I want the default Drupal provided taxonomy view output all nodes of a taxonomy-term in the current and no node-language, AND, if present, other nodes of this term from the other language.
The Drupal provided sql is:

SELECT "taxonomy_index"."sticky" AS "taxonomy_index_sticky", "taxonomy_index"."created" AS "taxonomy_index_created",
"node_field_data"."nid" AS "nid", "node_field_data"."langcode" AS "node_field_data_langcode"
FROM {node_field_data} "node_field_data"
LEFT JOIN {taxonomy_index} "taxonomy_index" ON node_field_data.nid = taxonomy_index.nid
WHERE ((taxonomy_index.tid = '14')) AND (("node_field_data"."langcode" IN ('nl', 'und', 'zxx'))
AND ("taxonomy_index"."status" = '1'))
ORDER BY "taxonomy_index_sticky" DESC, "taxonomy_index_created" DESC
LIMIT 11 OFFSET 0

I want, if applicable, extend the where clause so it becomes by example:

WHERE (((taxonomy_index.tid = '14')) AND ((("node_field_data"."langcode" IN ('en', 'und', 'zxx'))
AND ("taxonomy_index"."status" = '1')))
OR (("taxonomy_index"."tid" = '14') AND ("node_field_data"."nid" IN ('43'))
AND ("node_field_data"."langcode" NOT IN ('en', 'und', 'zxx'))))

I wrote a function example_views_query_alter(ViewExecutable $view, QueryPluginBase $query) in which I found out which extra nodes should be added in the extra OR clause. By adding this code:

      $group_id = $query->setWhereGroup('OR');
      $query->addWhere($group_id, 'taxonomy_index.tid', $tid);
      $query->addWhere($group_id, 'node_field_data.nid', $missing, 'IN');
      $query->addWhere($group_id, 'node_field_data.langcode', $langcodes2, 'NOT IN');

But in the extended where clause I get OR instead of AND statements:

WHERE ((taxonomy_index.tid = '14')) AND ((("node_field_data"."langcode" IN ('en', 'und', 'zxx'))
AND ("taxonomy_index"."status" = '1'))
OR (("taxonomy_index"."tid" = '14') OR ("node_field_data"."nid" IN ('43'))
OR ("node_field_data"."langcode" NOT IN ('en', 'und', 'zxx'))))

But it should be an AND group within an OR Group, maybe like:

$query->setWhereGroup('OR');
$query->setWhereGroup('AND');
$query->addWhere(...

Who can help me out to achive this?

postgresql – Query slowing down the database performance

I have a query to find the maximum spacing between charging stations on any given route, using PostGIS and pgRouting. An example query is below:

select (max(delr) * st_length(line::geography) * 0.000621371) as max_spacing
from (select sq2.ratio - coalesce((lag(sq2.ratio) over ( order by sq2.ratio)), 0) as delr, line
      from (select ST_LineLocatePoint(line, sqa.points) as ratio, line
            from sp_od_ferry(98282, 98002) as line,
                 (select st_setsrid(st_makepoint(longitude, latitude), 4326) as points
                  from (select longitude,
                               latitude
                        from evses_now
                        where analysis_id = 565
                          and (connector_code = 1
                           or connector_code = 3)
                        union
                        select longitude,
                            latitude
                        from zipcode_record
                        where zip = '98282'
                           or zip = '98002') as sq) as sqa
            where st_dwithin(line::geography, sqa.points::geography, 16093.4)
            order by ratio asc) as sq2) as sq3
group by sq3.line;

Briefly, the logic is to find the points (charging stations) near the shortest path (given by user-defined function sp_od_ferry()) between origin and destination and find the length of the longest segment between points.

I have to run the above query for several OD pairs, and several of these calculations can be launched in parallel by users. I used AWS RDS performance insights and it found the above query to be the slowest one and causing database slowdown (and 100% CPU usage on the DB instance).

enter image description here

On EXPLAIN ANALYZE, it shows the nested inner loop to be the costliest step. https://explain.dalibo.com/plan/jTf

enter image description here

I understand one way to reduce the database load would be to provision a bigger RDS instance. I currently use (db.t3.small) which has the following specs:

enter image description here

I used pgTune to make the changes to the default AWS RDS Postgres 12.5 settings. The new config is below:

max_connections = 100
shared_buffers = 512MB
effective_cache_size = 1536MB
maintenance_work_mem = 128MB
checkpoint_completion_target = 0.9
wal_buffers = 16MB
default_statistics_target = 100
random_page_cost = 1.1
effective_io_concurrency = 200
work_mem = 2621kB
min_wal_size = 2GB
max_wal_size = 8GB

Any suggestions regarding the query or ideas about how I can manage the database load while keeping the costs low are appreciated.

categories – Meta Query | Compare category name and meta value

I like to create meta query that has a condition like this.

Using comparison of meta value, and omitting articles which belongs to category that is same name of meta value. Each article belongs to several categories.

This is what I made for it, but something wrong. It fails to store category name of all articles.

<?php
$cats = get_the_category();
foreach($cats as $cat){
$category_name = $cat->cat_name;
}
?>

<?php
$args = array(
'posts_per_page' => -1,
'post_type'   => 'post',
'post_status' => 'publish',
'meta_query' => array(
                'key'=> '1bb1',
                'value' => $category_name,
                'compare'=>'NOT LIKE',
                ),                   
);?>

java – JPQL Query Syntax Help: Trying to get count for a query and count for a subquery that has one more condition

@Query(value = "Select NEW com.xx.domain.MaxTestsStats(rep.component.componentName, count(run), count(run) where run.testStatus = 'SKIPPED') " +
        "from TestRun run " +
        "join run.testReport rep " +
        "where (:environment IS NULL OR rep.environmentAlias = :environment) " +
        "and (:type IS NULL OR rep.testType = :type) " +
        "and (cast(:from as date) IS NULL OR rep.testStartTime >= :from) " +
        "and (cast(:to as date) IS NULL OR rep.testStartTime <= :to) " +
        "group by (rep.component.componentName) "
        )
fun findMaxTestsStats(@Param("environment") environment: String?, @Param("from") from: Date?, @Param("to") to: Date?, @Param("type") type:TestType?): List<MaxTestsStats>

The code above will not work because of: “count(run) where run.testStatus = ‘SKIPPED'” but I hope the intent of what I’m trying to do is clear.

I want to return a value with the component name, the count of all test runs, and the count of all test runs where run.testStatus is ‘SKIPPED’. How do I restructure this query to return both counts? The second count should just be a subset of the first count.


Relevant Entity list:

@Entity

@Table(name = “test_runs”)

data class TestRun(

    @Id
    @GeneratedValue
    val id: UUID? = null,

    @ManyToOne(fetch = FetchType.LAZY)
    @JsonProperty(access = JsonProperty.Access.WRITE_ONLY)
    val testReport: TestReport,

    val testName: String,

    @Enumerated(EnumType.STRING)
    val testStatus: TestStatus,

    val testException: String? = null,

    val duration: Long

)

@Entity

@Table(name = “test_reports”)

data class TestReport(

    @Id
    @GeneratedValue
    val id: UUID? = null,

    @OneToOne
    val component: Component,

    val environmentAlias: String,

    @Enumerated(EnumType.STRING)
    val testType: TestType,

    @Enumerated(EnumType.STRING)
    var status: TestStatus?,

    var testStartTime: Timestamp?,

    var testStopTime: Timestamp?,

    @OneToMany(fetch = FetchType.LAZY, cascade = (CascadeType.ALL), mappedBy = "testReport")
    @JsonProperty(access = JsonProperty.Access.WRITE_ONLY)
    var testRuns: MutableList<TestRun>,

    @OneToOne(fetch = FetchType.LAZY, cascade = (CascadeType.ALL), mappedBy = "testReport")
    var testReportCoverage: TestReportCoverage? = null

)

@Entity
data class MaxTestsStats(

    @Id
    var component: String,
    var total: Long,
    var ignored: Long

)

http – Is It Possible To Query an API for its Available Versions and/or Endpoints?

So I’ve got an API with some outdated documentation and no swagger. I can ping requests to said API because I have the auth and all required parameters. However, as the documentation is out-dated, I’m not sure which API version is active, present or available.

My requests always get a 200 response, its just that I’m supplying an unsupported version number so the response body says exactly that.

Now I’m curious about undocumented APIs in general, is there a way to ping requests in order to find which version of the api (or endpoint) is available/active? Given that you already have the full endpoint data (url, endpoint, auth, params) is it possible to send requests without specifying a version number in order to find out which ones are available for each endpoint? Sort of like a DIY-swagger.

The type of request in question is very simple, a GET with all parameters supplied as a query string

ie. https://theurl.com/<endpoint>/<**version number**>/?Params&Params&Params

could this type of request be hacked so that it uses the latest possible version number? Or is there a way to ping an API so that it shows all available versions and endpoints?

Are there any generic Abstract Syntax Tree Query Languages (specification)?

While designing compiler specifications I noticed that having some sort of language to further validate and manipulate the AST’s gotten from a Parser would be useful in separating concerns. The overarching goal is to create a maintainable language stack specification. So far I have worked out generic lexer and parser phases: instead of a defined ANTLR ruleset I have a specification for any ruleset. I wish to do the same for further validation of the AST’s (double variable name declarations, non-existing variables etc). While not a hard requirement it would be excellent if this tool is also able to manipulate the AST, similar to database queries.

I went looking for existing query languages or AST manipulation syntax and found the following: TreeQL, SPARQL, Neo4j, SBQL, JSONIQ, CRAQL, astquery.io, AST Query Engine, Tregex & Tsurgeon (still inquiring into these two) and SMARTS.
JSONIQ looked promising but would bind the specification to representing AST’s as JSON. The other options either do not support manipulation or are otherwise too restrictive.
I’d be happy to hear if someone can point me to a Tree/AST manipulation tool/query language (or even better, specification). Thanks in advance.

sql server – Check if contract conflict with another contract query

I have a database with these tables as the bellow picture showed:

enter image description here

The user can create a new contract each contract may have one or more shops and these shops status changes depending on the contracts, everything works perfectly until now.
my problem in renewing the contract, I have a feature that allows the user to renew a contract, in this case, I get all the original contract info and make the new contract start from the end date of the original contract + 1 day with the same original contract info.

my question here is how to check if this renewal contract does not conflict with other contracts for the same customer and the same shops (customer can have many contracts)

E.G.:

I have contracts like this:

con_id    shop_id   start_date    end_date
--------------------------------------------
  1         1       14-04-2021    14-04-2022
  2         1       15-04-2022    15-04-2023

If the user clicked on contract number 1 and tried to renew it, I want a query to check if the new contract start date does not conflict with another contract for this user and these shops.

Like in my above data I want to prevent the user from renewing contract number 1 again because there is already a contract renewed before in that period.

This is what I tried:

--IF EXISTS (SELECT * FROM contracts_view where cust_id=123456789 and @date_start >= date_start and @date_start <= date_end and shop_id in (select shop_id from contracts_shops where contract_id =@old_contract_id)) 
--BEGIN
--    SELECT @ErrorMessage = ERROR_MESSAGE()
--  RAISERROR ('asdasd', 10, 1)
--  ROLLBACK TRAN
--   return
--END

and here is my stored procedure for renewing a contract:

ALTER PROCEDURE (dbo).(contract_renew)
-- Add the parameters for the stored procedure here
@cust_id int,
@duration int,
@price decimal(10,2),
@tax decimal(10,2),
@usage nvarchar(20),
@rent_type nvarchar(10),
@price2 decimal(10,2),
@note2 nvarchar(max),
@date_start date,
@date_end date,
@note nvarchar(max),
@app_user nvarchar(20),
@old_contract_id int
AS
BEGIN

DECLARE @ErrorMessage NVARCHAR(MAX)
DECLARE @ID int

BEGIN TRAN
BEGIN TRY

-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

--insert data
INSERT INTO (dbo).(contracts)
       ((cust_id)
       ,(duration)
       ,(price)
       ,(tax)
       ,(usage)
       ,(rent_type)
       ,(price2)
       ,(note2)
       ,(date_start)
       ,(date_end)
       ,(note)
       ,(app_user))
 VALUES
       (@cust_id,
       @duration,
       @price,
       @tax,
       @usage,
       @rent_type,
       @price2,
       @note2,
       @date_start,
       @date_end,
       @note,
       @app_user) SELECT SCOPE_IDENTITY();

       SET @ID = SCOPE_IDENTITY();


insert into contracts_shops (contract_id, shop_id)
select @ID, shop_id
FROM contracts_shops WHERE contract_id = @old_contract_id;

COMMIT

END TRY
BEGIN CATCH
    SELECT @ErrorMessage = ERROR_MESSAGE()
    RAISERROR (@ErrorMessage, 10, 1)
    ROLLBACK TRAN
END CATCH
END