Postgres: Efficient schema for querying with one exact string match and two range matches

The table I need to query:

CREATE TABLE regions (     state text NOT NULL,     zip_begin text NOT NULL,  -- 9-digit zip code     zip_end text NOT NULL,     date_begin date NOT NULL,     date_end date,      data ..., ) 

There are ~50 states and between 0-5M rows per state. The zip and date ranges might overlap.

The following will be one of the most common queries in my OLTP application:

SELECT data FROM regions WHERE state = {state}   AND {date} BETWEEN date_begin AND date_end   AND {zip} BETWEEN zip_begin AND zip_end 

This query usually yields one row, but may sometimes yield more.

From the Postgres docs, it sounds like a GiST index might do what I need, but I don’t really understand how those work.

WordPress, fetching users with an exact match in a string of comma separated values in user_meta

I have data saved in user_meta -> areapref field. It is basically a string of comma delimited post IDs (e.g. 2, 37, 200, 300 etc.)

I need to check if the user has a particular ID in this field. If so… do stuff.

I can check if an ID is in areapref using LIKE. But I believe this has an issue. If one location is ID=10, and another is ID=100 – both will match with 1, or 10? Or am I misunderstanding LIKE. I fear CONTAIN would have the same issue.

I need to know how to get a list of users who have an ‘exact match’ within the areapref string? So search for ID 2, will return only 2, NOT 22, or 20, 200 etc. I’ve spent hours going through stackexchange/overflow/wp codex and cannot find an answer… though I am dense!

Here’s enough code I hope to show what I’m currently doing… any ideas greatly appreciated.

function ds_notify_new_event( $  post_ID ){ $  url = get_permalink( $  post_ID ); $  event = get_the_title( $  post_ID ); $  localeID = get_post_field( 'location_id', $  post_ID); global $  wpdb; $  locale = $  wpdb->get_var( $  wpdb->prepare( " SELECT post_id FROM {$  wpdb->prefix}em_locations WHERE ID = %d ", $  localeID ) );  $  args = array(         'role'    => 'subscriber',         'meta_query' => array(                 array( 'key' => 'areapref', 'value' => $  locale, 'compare' => 'LIKE' ),                 // just checks usr is ok with email                 array( 'key' => 'notify', 'value' => 'yes', 'compare' => '=' )              ),         'fields' => array( 'display_name', 'user_email' )     ); // retrieve users to notify about the new post $  users = get_users( $  args ); 

// do stuff with users

Entering Exact Values into a Table Using SQL

I am needing to insert particular values into a table I am working on with SQL (see function below):

INSERT INTO Table Name (…) VALUES(…);

The values that are going to be stored inside this table are values that involve another programming language that have a lot of weird symbols involving a single backslash, *, dollar signs, etc.

How can I insert values precisely as they are inside this table?

For instance, when I type ” it needs to return inside the table instead of just 2. I am unable to manually enter the two backslashes to get . This is because I am receiving an input from another user via HTML and sending it to my table with PHP. The issue I am having is that there are these special characters with SQL which are being entered in not the way I intend them to as values.

Is there a way to type these values in exactly with a certain string function that is called beforehand? Other ways of doing this would be greatly appreciated!

How can we formulate an anti-knight Sudoku as an exact cover problem?

Formulating standard Sudoku as an exact cover problem is easy and well documented. All of the constrained groups contain every digit which makes it natural to express the problem this way. Wikipedia claims without citation that:

Although other Sudoku variations have different numbers of rows, columns, numbers and/or different kinds of constraints, they all involve possibilities and constraint sets, and thus can be seen as exact hitting set problems.

How would one formulate other variants as exact cover problems with an incidence matrix? I’m focusing on the anti-knight variant, but with the goal of learning about expressing arbitrary variants.

The anti-knight Sudoku variant includes all of the standard rules, and additionally requires that no two squares which are a knight’s move (in chess) apart may contain the same digit. I don’t know how to approach this because a constraint between two squares cannot be expressed as requiring all digits to be present in some permutation. I can imagine a version of Algorithm X that solves this by removing additional rows by manual application of the anti-knight rule, but I believe Wikipedia is claiming this constraint should be expressible in the matrix alone without modifying the algorithm.

Is Wikipedia’s claim that all variants can be expressed this way correct, and how would one approach the anti-knight example?

Why is this language *not* pumpable? (language = arbitrary word followed by exact same arbitrary word)(pumping lemma for context-free-languages)

1 language = arbitrary word followed by exact same arbitrary word = u * u (with u being out of non-empty words of alphabet {0, 1} )
(sorry for the formatting, see screenshot-link for conventional/clear expression)

The purpose is to prove that the given language is not a context-free-language (using the pumping lemma).

So i thought that this language should definitely be pumpable, since you can choose x and z as corresponding parts in one of the u/"halves". So if word to be pumped is "0101" then you choose e.g. x=1 (0101) and z=1 (0101) and the remaining parts that stay static are assigned with u=0 (0101), y=0(0101), and v=empty.
If pumped zero-times/i=0 word reads: 00
i=1: 0101
i=2: 011011
i=3: 01110111
(and so on)

(For more complicated words you would need to use all three (u, y, v), but the basic principle would still be that x and z are assigned corresponding parts of the word and u,y,v would be assigned the remainder. (This doesn’t work for the extremely small words 00,01,10,11 that would become empty when zero-pumped, but as long as the exceptions are finite / of finite/fixed number this isn’t a problem AFAIU.)
Clearly this is wrong, but can anybody explain how/why?

How to use ‘Bind Variables’ in a ‘Dynamic Query ‘ when the exact number of variables are not known

I have a procedure in which I’m using Dynamic SQL. The input parameteri_tables is a string which is a concatenation of the name of some tables . It might be one these :

1)All tables test_table1,test_table2,test_table3.

2)Only two tables , for instance test_table2,test_table3 .

3)Nothing .So NULL will pass to the procedure.

I’ve read about Bind variable and the significant role it has in preventing injection and improving performance. I want to use bind variable in my procedure but there is one things I don’t know how to handle :

As you can see we do not know the exact number of variables.It might be one , two , three or nothing. Depending on The input parameteri_tables.

   create or replace procedure bind_variable_test(i_tables varchar2,                                                   i_cid    number,                                                   o_result out sys_refcursor) is     actual_query varchar2(1000) := '';    begin         -- this is the base query     actual_query := 'select *                 from z_test_a t1                   inner join z_test_b t2                 on t1.id = t2.id';      -- check input parameter " i_tables "       if i_tables like '%test_table1%' then              actual_query := actual_query || '  inner join test_table1 t3 on t3.id = t1.id and                              t3.cid = ' || i_cid;      end if;       if i_tables like '%test_table2%' then             actual_query := actual_query || '  inner join test_table2 t4 on t4.id = t1.id and                              t4.cid = ' || i_cid;      end if;       if i_tables like '%test_table3%' then             actual_query := actual_query || '  inner join test_table3 t5 on t5.id = t1.id and                    t5.cid = ' || i_cid;      end if;      open o_result for actual_query;     end; 

Greedy Probabilistic Algorithm for Exact Three Cover

I have a probabilistic greedy algorithm for Exact Three Cover. I doubt it’ll work on all inputs in polytime. Because the algorithm does not run $ 2^n$ time. I will assume that it works for some but not all inputs.

Our inputs are $ S$ and $ B$

$ S$ is just a set of integers

$ B$ is a list of 3-element {}s

Algorithm

  1. Input validation functions are used to ensure that sets of 3 are $ elements$ $ S$ .

  2. A simple $ if~~statement$ makes sure that $ |S|$ % $ 3$ = $ 0$

  3. I treat the sets like lists in my algorithm. So, I will sort all my sets from smallest to largest magnitudes (eg {3,2,1} now will be sorted to {1,2,3}

  4. I will also sort my list of sets called $ B$ in an ordering where I can find all {1,2,x}s with all other {1,2,x}s. (eg, sorted list {1,2,3},{1,2,4},{4,5,6},{4,5,9},{7,6,5} )

  5. I will also genereate a new list of sets containing elements where a {1,2,x} only occurs one time in $ B$ .

  6. Use brute force on small inputs and on both sides of the list $ B$ up to $ |S|$ / $ 3$ * $ 2$ sets. (eg. use brute force to check for exact covers on left and right side of the list B[0:length(s)//3*2] and reversed B[0:length(s)//3*2])

Seed the PRNG with a Quantum Random Number Generator

for a in range(0, length(B)):     o = quantumrandom.randint(0, length(B))     random.seed(int(o))  # I will create a function to shuffle B later  def shuff(B, n):     for i in range(n-1,0,-1):         random.seed()         j = random.randint(0,i+1)         B[i],B[j] = B[j],B[i] 

Define the number of times while loop will run

n = length(s)  # This is a large constant. No instances # are impractical to solve.  while_loop_steps = n*241*((n*241)-1)*((n*241)-2)//6 

While loop

stop = 0 Potential_solution = [] opps = 0 failed_lists = 0 ss = s  while stop <= while_loop_steps:      opps = opps + 1     stop = stop + 1      shuff(B,length(B))          if length(Potential_solution) == length(ss)//3:         # break if Exact         # three cover is         # found.         OUTPUT YES         failed_lists = failed_lists + 1         HALT      # opps helps     # me see     # if I miss a correct     # list           if opps > length(B):         if failed_lists < 1:             s = set()             opps = 0               # Keep B[0]     # and append to     # end of list     # del B[0]     # to push >>     # in list.       B.append(B[0])     del [B[0]]     Potential_solution = []     s = set()          for l in B:         if not any(v in s for v in l):             Potential_solution.append(l)             s.update(l) 

Run a second while loop for new_list if Step 5 meets the condition of there being only ONE {1,2,x}s )eg. {7,6,5} shown in step 4

Two Questions

How expensive would my algorithm be as an approximation for Three Cover?

And, what is the probability that my algorithm fails to find an Exact Three Cover when one exists?

Go back to the exact post page number

I use pagination in my index.php and I have many posts per page. Every post have a button to go back to home. The problem is that when I visit a post in a page 5 for example, the button always send me on the first page.

I’m using this: <a href="<?php echo home_url(); ?>">back</a>

Is there a simple way to track the post page position, and use it to go back properly?

What’s the exact meaning of the phrase “when the target willingly moves” in Booming Blade?

The Booming Blade cantrip’s secondary effect states that “[the target] becomes sheathed in booming energy until the start of your next turn. If the target willingly moves before then, it immediately takes 1d8 thunder damage, and the spell ends”. We can deduce, then:

  • That the damage does not trigger when the target is dragged or pushed around via physical means (grappling, shoving, scenario effects, etc.)
  • That the damage does not trigger when the target is dragged or pushed around via magical means (Gust, Eldritch Blast with Grasp of Hadar, Graviturgist’s Gravity Well, etc.)

This seems pretty clear. However, what happens when the following cases arise?

  • The target is ordered to move via the use of Command (assuming they don’t know yet that the thunderous aura is damaging).
  • The target is forced to move via the use of Compulsion.
  • The target is invited to move via the use of Suggestion.
  • The target is Turned.
  • The target is Frightened and forced to run via Dissonant Whispers.
  • The target has a Geas that prohibits them from ending a turn next to an enemy if they can avoid it.
  • The target is threatened into leaving the area or else. This one seems “willingly”, but are they truly willing if they are panicking because you are telling them you will cut their heads off if they don’t run?

This SageAdvice entry seems to suggest that forcing people to move via mind control spells doesn’t count as willing. However, from what I understand, Jeremy Crawford’s Twitter rulings are no longer considered official, so I was looking for something more solid to make a RAW judgement of this interaction.