Tag: database

sql – get woocommerce product image url and price from database

I want to get some product data directly from the database, currently I have this, but I’m missing the featured image URL:

 SELECT 
  p.ID,
  p.post_title,
  `post_content`,
  `post_excerpt`,
  t.name AS product_category,
  t.term_id AS product_id,
  t.slug AS product_slug,
  tt.term_taxonomy_id AS tt_term_taxonomia,
  tr.term_taxonomy_id AS tr_term_taxonomia,
  MAX(CASE WHEN pm1.meta_key = '_price' then pm1.meta_value ELSE NULL END) as price,
  MAX(CASE WHEN pm1.meta_key = '_sku' then pm1.meta_value ELSE NULL END) as sku 
FROM wp_posts p 
LEFT JOIN wp_postmeta pm1 ON pm1.post_id = p.ID
LEFT JOIN wp_term_relationships AS tr ON tr.object_id = p.ID
JOIN wp_term_taxonomy AS tt ON tt.taxonomy = 'product_cat' AND tt.term_taxonomy_id = tr.term_taxonomy_id 
JOIN wp_terms AS t ON t.term_id = tt.term_id
WHERE p.post_type in('product', 'product_variation') AND p.post_status = 'publish' AND p.post_content <> ''
GROUP BY p.ID,p.post_title

Flask SQLAlchemy db.create_all() not creating tables in database

Im trying to create a table in a database at Heroku but when i run my code ‘python book.py’ it wont create the desired table. I set the database url as well by the cmd. The statement at main() isnt printed as well, so i guess main() is the problem

book.py:

import csv
import os
from flask import Flask
from flask_sqlalchemy import SQLAlchemy
from sqlalchemy import create_engine
from sqlalchemy.orm import scoped_session, sessionmaker

db = SQLAlchemy()

app = Flask(__name__)

app.config("SQLALCHEMY_DATABASE_URI") = os.getenv("DATABASE_URL")
app.config("SQLALCHEMY_TRACK_MODIFICATIONS") = False


db.init_app(app)


class Book(db.Model):
    __tablename__ = "books"
    id = db.Column(db.Integer, primary_key=True)
    isbn = db.Column(db.String, nullable=False)
    title = db.Column(db.String, nullable=False)
    author = db.Column(db.String, nullable=False)
    year = db.Column(db.Date, nullable=False)


def main():
    db.create_all()
    db.session.commit() 
    print "yeet"

if __name__ == "__main__":
    main()

security – Oracle – Is a separate database more secure than using the same database and a separate schema

I am also in a very small team, are these aspects of the same production system?
Why is it that they want you to keep them separate?

If security is an issue I would not want to grant select on the other schema as that allows anyone connected to the database to read anything in the other schema, you might want to look into Oracle Rest Data Services, which also handles REST authentication etc for you. It also allows only certain SQL statements to be run. I’m not sure how familiar you are with it, but it’s been an absolute lifesaver for me. Let’s talk in the comments :]

In short, yes another database should make it more secure. Check out ORDS.

crawlers – Existing literature or solutions on programmatically crawling database as it relates to a single row?

I am working on a project that will programmatically take a given row in the database, and aggregate all related tables and specific rows that have either a direct or indirect FK relationship back to that single row.

This seems like a non-trivial problem and one that may have interesting solutions. The problem goes beyond simply following FK references. If one were to build this to be fast, there would have to be some level of batch processing involved.

I’m wondering if anyone has literature, or existing solutions on the subject that could help inform my approach to the problem. Thanks in advance

optimization – Are may database writes an anti-pattern?

I am designing a database api. I have two options:

  1. Have a complex aggregation that queries the data as desired or,
  2. Create a materialized view that logs all data changes on an easy to query manner and potentially makes querying this data faster than querying data from option 1.

I was wondering is having many writes an anti-pattern in database design and administration? Because of not I would love to use option 2.

I am using a no-SQL database; MongoDB.

Oracle 11g – Database Job About User Lock

I have an Oracle database (Oracle 11gR2) and I want to create a job. The job will trigger my procedure.

The procedure must comply with the rules I have specified below:

  • If a user’s expiry_date value is EXPIRED (select username, account_status, lock_date, expiry_date from dba_users where account_status=’EXPIRED’;), it will lock this user.

  • If a user’s expiry_date value is EXPIRED and if it is already lock state, it will not do anything.

  • Exclude internal schemas for this operation.

I searched it but I didn’t find any clear solution for it.

How can I create this procedure?

Best regards,

SQL Server & SSDT : Error modeling database during Publish after adding CREATE AGGREGATE / EXTERNAL NAME

I am using SSDT to create a database containing CLR user defined aggregates.

The aggregates are intended to sit in different schema. That is not possible with CLR objects inside SSDT, without using a post-deployment script to move the objects after deployment.

What I am attempting to do is:

  • Create the CLR aggregates in a single schema (the “CLR” schema) using the “Default schema” option
  • Use a CREATE AGGREGATE wrapper in the desired schema to call the CLR aggregate

For example, I have a CLR aggregate like:

(Serializable)
(Microsoft.SqlServer.Server.SqlUserDefinedAggregate(Format.UserDefined, MaxByteSize = -1))
public struct StatsEntropy : IBinarySerialize
{
 ... etc
}

This get compiled and becomes CLR.StatsEntropy in the database. The database is called AnalysisFunctions, so it has an assembly also called AnalysisFunctions. This is no problem.

I then create a wrapper in the Stats schema like so:

CREATE AGGREGATE (Stats).(Entropy)
(
    @Values FLOAT
)
RETURNS FLOAT
EXTERNAL NAME AnalysisFunctions.StatsEntropy

This works without error when I build via debug. I have my debug connection set to a named instance of SQL Server. If I press F5, it compiles and deploys without any problem. The code is now usable on the server, including the wrapper.

But, if I attempt to publish to a server using Publish, it does not work. Instead it fails with:

Errors occurred while modeling the target database. Deployment cannot continue.

There are no further error messages either in the Data Tools Operations panel or error list. ssdttrace shows no useful information:

ssdttrace event log

This only happens when the database already exists on the server. If I drop the database, it can deploy successfully. I can deploy successfully via debug even if the database already exists, it is only via Publish that the problem occurs. I did not start getting this problem until I added the aggregate wrappers.

Previously I was also getting errors indicating that the assembly AnalysisFunctions had an unresolved reference due to the use of EXTERNAL NAME – again working via debug, not via publish. But now that error has mysteriously vanished.

I am using Visual Studio 2017 15.9.21 and SSDT 15.1.62002.01090, deploying to SQL Server 2019.

Does anyone have any idea what this error might be, or how I can debug it?

binary trees – Efficiently storing and modifying a reorderable data structure in a database

I’m trying to create a list curation web app. One thing that’s important to me is being able to drag-and-drop reorder items in the list easily. At first I thought I could just store the order of each item, but with the reordering requirements, that would mean renumbering everything with a higher order (down the list) than the place where you removed or where you inserted the moved item. So I started looking at data structures that were friendly to reordering, or to both deletion and insertion. I’m looking at binary trees, probably red-black trees or something like that. I feel like I could with great effort probably implement the algorithms for manipulating those.

So here’s my actual question. All the tree tutorials assume you’re creating these trees in memory, usually through instantiating a simple Node class or whatever. But I’m going to be using a database to persist these structures, right? So one issue is how do I represent this data, which is kind of a broad question, sure. I would like to not have to read the whole tree from the database to update the order of one item in the list. But then again if I have to modify a lot of nodes that are stored as separate documents, that’s going to be expensive too, even if I have an index on the relevant fields, right? Like, every time I need to manipulate a node I need to actually find it in the database first, I think.

Also, I obviously need the content and order of each node on the front end in order to display the list, but do I need the client to know the whole tree structure in order to be able to send updates to the server, or can I just send the item id and its new position in the list or something?

I’m sorry if this veers too practical but I thought someone might want to take a crack at it.

magento2.3 – Where is the “Set Product as New” attribute saved in the Magento 2.3 EE database?

I know that Magento 2 EE doesn’t utilize the “newsFromDate” and “newsToDate” attributes, but I can’t figure out where Magento EE is storing the data for the “Set Product as New” option on the products?

I’m trying to check if a product is new, but I’m unsure what to use to get that data if I can’t find it in the database. Any ideas?