Database tables map One To Many without foreign key in @JoinColumn

Possible to not have foreign key relationship in child table to its parent in the database, while in JPA it will still be able to map out fine? How will this be done?

Can I still use @JoinColumn to define the field for reference back to the parent table?

This is a one-to-many relationship. One parent record, many child records.

I only care about writes to the database tables right now.

Insert blob data with wpdb

I have a shortcode in my WordPress page with which I access the WordPress database with wpdb.

It works perfectly with wpdb-> get_results () and wpdb-> query (), but I am not able to execute an insert with a blob data.

With wpdb:: insert I can’t as it only accepts a string, int and float. With wpdb-> query () it doesn’t work for me either (I even tried with fbsql_read_blob ())

I can insert using the PDO class, but I don’t know if it can be done with any WordPress function. I understand that it is safer and will work better.

Thanks in advance.

Go to Source
Author: alvaroqm

WP_query has incorrect wp_posts.post_name = ‘asc’ when I have custom post type called ‘order’

This is my code :

print_r($args)

//Output 
array(6) { ["post_type"] => string(9) "order" 
           ["date_query"] => array(2) { 
               ["before"] => string(3) "now" 
               ["compare"] => string(1) "=" 
           } 
           ["post_status"] => string(9) "validated" 
           ["posts_per_page"] => int(-1) 
           ["orderby"] => string(5) "title"
           ["order"] => string(3) "ASC" 
         } 

And this is the request :

$wp_query = new WP_Query( $args );
var_dump( $wp_query->request );
  
//Output 
"SELECT wp_posts.* 
 FROM wp_posts 
 WHERE 1=1 
 AND ( wp_posts.post_date < '2020-08-07 16:46:27' ) 
 AND wp_posts.post_name = 'asc'
 AND wp_posts.post_type = 'order'
 AND ((wp_posts.post_status = 'validated'))
 ORDER BY wp_posts.post_title ASC "

So WP_Query in the query says wp_posts.post_name = 'asc' !!!

Why ?

Go to Source
Author: J.BizMai

How Do I See the List of Users Allowed on a MS SQL Server Database

I want to know the users that are allowed access to a MS SQL Server database. I only want a specific database. Is there a user list hidden somewhere?

What are the ways to go about doing this? Is there an app or through a SQL query?

ANSWER

Use Microsoft SQL Server Management Studio. That is the fastest way to do it. Find the database from the explorer menu on the left-hand side. Expand the folder icon. Go to the Security folder, then click on Users next. That’s it!

If you’re using a different SQL client and you can’t see Security > Users from the GUI. Then your other option is through a SQL query.

This is the query below. More details at this page here: https://www.joseyamut.xyz/2020/08/06/get-user-list-in-a-microsoft-sql-server-database/.

USE <database_name>;
SELECT name AS username,
       type_desc AS type,
       authentication_type_desc AS auth_type
FROM sys.database_principals
ORDER BY type;

Happy user hunting! =)

Optimization for data storage

I’d like your advice on the design of my application.

I use websockets to receive new data and the request module to retrieve older data.
Then I use pyqtgraph to display data and tables etc with pyqt5.

There are some data that I don’t keep in memory, I just display them on screen without the possibility to interact with them, and I have other data that I keep in memory, with which I do some processing.

I would like to know if I should use dictionaries to store and process data or create a database with SQL or use pandas.
There will be a lot of inserting, extracting, deleting and a lot of calculations.

Potentially, when there are big moves, I could have thousands of messages per second to process, which I would have to add to my database, process and then display them on screen or do whatever I wanted with them.

If you have any questions, don’t hesitate.

Example of connection:

import websockets
import asyncio
import json

async def capture_data():
    subscriptions = [sub for sub in ["quote", "trade", "instrument"]]
    uri = "wss://www.bitmex.com/realtime?subscribe=" + ",".join(subscriptions)

    async with websockets.connect(uri) as websocket:
        while True:
            data = await websocket.recv()
            print(json.loads(data))


asyncio.get_event_loop().run_until_complete(capture_data())

Go to Source
Author: antho

Clear Terms from Taxonomy for Specific Post IDs?

Trying figure out how I can use some SQL/$wpdb->query to delete all the terms from a taxonomy that are related to specific post IDs. The following works beautifully to delete all the terms from a specific taxonomy, but I need to modify it to only remove ones associated with specific post IDs as mentioned.

// Clear Terms from Taxonomy 'post_tag'
DELETE t, tr, tt
FROM wp_terms t  
INNER JOIN wp_term_taxonomy tt ON t.term_id = tt.term_id
INNER JOIN wp_term_relationships tr ON tt.term_taxonomy_id = tr.term_taxonomy_id
WHERE tt.taxonomy = 'post_tag'

I’m also using the following to delete posts (works great):

$wpdb->query( "DELETE FROM $wpdb->posts WHERE ID IN (".implode( ", ", $postIds ).")" );

I’ve tried to do the following but it doesn’t clear the associated terms they just remain as orphaned in the DB, it just clears the relationship.

// Delete Post Terms
$wpdb->query( "DELETE FROM wp_term_relationships WHERE object_id IN (".implode( ", ", $postIds ).")" );

I’m a novice when it comes to SQL, any help is appreciated, thanks in advance!

Go to Source
Author: contempoinc

Is a transaction time of <10ms for an SQL database viable? If so, under what conditions?

Appreciate this is a rather odd question, so I will try to clarify as much as possible. Please also be assured this is a question purely for my own education, I’m not about to rush off and do crazy things in our software on the back of it.

I have a customer requirement for a transaction time of <10ms on a system that is based around an SQL database – in our specific implementation it is Oracle DB. I’m aware that this is not a useful or meaningful requirement, so with my business hat on I’ll be dealing with that. I fully expect that the requirement will be revised to something more useful and achievable.

However, I am curious on a technical level. Could you squeeze transaction time on an SQL DB down below 10ms? Lets be generous and say this is pure SQL execution time, no comms, no abstraction layers etc. Right now, running select 1 from dual on one of our systems gives a reported execution time of 10-20ms and I’d assume that’s about the simplest query possible. What if anything might you do to reduce that time (a) within Oracle/SQL or the server environment (b) by making a different tech choice? I’d assume maybe a higher clock speed on the CPU might help, but I wouldn’t bet on it.

Go to Source
Author: SimonN