Tag: Database

database – Distribute commit files between different repositories

I am not sure if what I’m asking is even possible (or desirable, for that matter), but we were wondering what would be the best way to handle SQL changes to a Database schema, when this schema is shared across multiple apps/teams.

We work for a large corporation where database schemas are shared between different software applications (and therefore independent, unrelated, incommunicated, development teams). This means that certain changes to the schema (new views, procedures, constraints, tables, etc.) can come from a variety of apps.
Having version control of the DB objects is ideal, but depending on the strategy

  • either a DB snapshot has to be composed from the changes produced by different apps (who knows which?), by traversing all possible apps that touch that schema,
  • or to see the latest changes in a specific application’s version, two (or more) repositories have to be checked (in addition, there is no hard-link between commits, releases, etc.).

i.e: Attempting to version control the schema objects (Oracle) puts us in the dilemma of how to do so:

  • Do we store the changes to the different schema objects inside the application’s repository (thus distributing the schema snapshot between an undefined nº of repos)?
  • Or do we have separate repositories for the DB schemas, and make two (or more) commits to different repositories when a new version is uploaded (thus difficulting the compilation of a new release changeset)?

I was wondering if it was possible to specify in Git:

Whenever you make a push on the Application repo, send the files in the /SchemaA SQL/ folder to SchemaA repository, the files in /Schema B/ folder to the Schema B repository, and finally the rest to the Application repository. Thus, distributing the contents of the commit between repos in a single operation. Maybe .gitattributes? If using Github/Gitlab, maybe through a webhook on the remote?

python – Geocoding addresses from a SAP database

I’m new to python and curious if this is the most efficient way to read addresses from a SAP database, geocode them, read the results, and then write the information back to the database.

Everything below works, I’m just curious if this would make an expert cry beginner bad, or acceptable code for my first try. 🙂 I’m pretty sure my variable name formatting isn’t 100% up to spec.

For reference on Geocodio, https://www.geocod.io/docs/?python#fields

cursor = conn.cursor()
sql_command = "SELECT ADDRNUMBER, CITY1, REGION, POST_CODE1,STREET FROM CV_COORDS_TO_PROCESS"
cursor.execute(sql_command)
rows = cursor.fetchall()

address = ''
for row in rows:
    #build the address string and save the address id
    address = row(4) +", " + row(1) +", " + row(2) +" " + row(3)
    addr_id = row(0)
    #send the address to geocodio
    location = client.geocode(address)

    #Read the results and break after the first result, as per the geocodio documentation, 
    #in the event of multiple points, it lists them in order of confidence
    for subs in location('results'):
        lat = subs('location')('lat')
        long = subs('location')('lng')
        accuracy = subs('accuracy')
        accuracy_type = subs('accuracy_type')
        break

    ##update / insert into database
    merge_query = "upsert zsa_addr_coords values  ('100', %s, %f, %f, %f, '%s')"
                  %(addr_id, lat, long, accuracy, accuracy_type ) + " where addr_id = '%s'" %addr_id
    try:
        cursor.execute(merge_query);
    except:
        print (ex)
        
    address = ''
cursor.close()
conn.close()

This is example of the results Geocodio returns:

{
  "input": {
    "address_components": {
      "number": "1109",
      "predirectional": "N",
      "street": "Highland",
      "suffix": "St",
      "formatted_street": "N Highland St",
      "city": "Arlington",
      "state": "VA",
      "zip": "22201",
      "country": "US"
    },
    "formatted_address": "1109 N Highland St, Arlington, VA 22201"
  },
  "results": (
    {
      "address_components": {
        "number": "1109",
        "predirectional": "N",
        "street": "Highland",
        "suffix": "St",
        "formatted_street": "N Highland St",
        "city": "Arlington",
        "county": "Arlington County",
        "state": "VA",
        "zip": "22201",
        "country": "US"
      },
      "formatted_address": "1109 N Highland St, Arlington, VA 22201",
      "location": {
        "lat": 38.886665,
        "lng": -77.094733
      },
      "accuracy": 1,
      "accuracy_type": "rooftop",
      "source": "Virginia GIS Clearinghouse"
    }
  )
}

How to export products with tier prices directly via database using SQL?

I want to export these data from my Magento 2.3:

  • Status
  • Product Name
  • SKU
  • Price
  • Advanced Pricing
  • Quantity

How could I do it via SQL?

sql server – conversion failed when converting datetime from character string while inserting and updating to database

I’m trying to insert time and date separately and calculate if it is late to the set time and then insert it to database…

here’s my try code for update

string Date = DateTime.Now.ToString(“dd-MM-yyyy”);
string Time = DateTime.Now.ToString(“h:mm:ss tt”);
SqlCommand comm2 = conn.CreateCommand();
comm2.CommandText = “Update Time_Logs SET Time_Out = ‘” + Time + “‘ where Emp_Id = ‘” + EmpId.Text + “‘ and Date = ‘” + Date + “‘”;
try
{
conn.Open();
comm2.ExecuteNonQuery();
MessageBox.Show(“Time_Out…”);
conn.Close();
TimeCompute();
}
catch (Exception x)
{
MessageBox.Show(x.Message);
conn.Close();
}

and here is the code for inserting

string Date = DateTime.Now.ToString(“dd-MM-yyyy”);
string Time = DateTime.Now.ToString(“h:mm:ss tt”);

        SqlCommand comm = conn.CreateCommand();
        comm.CommandText = "INSERT INTO Time_Logs (Emp_Id, Date, Time_In) VALUES('" + EmpId.Text + "','" + Date + "','" + Time + "')";
        try
        {
            conn.Open();
            comm.ExecuteNonQuery();
            MessageBox.Show("Time_In...");
            conn.Close();
            DateTime time = DateTime.Parse(Time);
            DateTime inDate = DateTime.Parse("8:00:00 AM");
            TimeSpan ts1 = inDate.TimeOfDay;
            TimeSpan ts = time - inDate;
            if (ts < ts1)
            {
                SqlCommand comm2 = conn.CreateCommand();
                comm2.CommandText = "Update Time_Logs SET Late = '" + ts + "' where Emp_Id = '" + EmpId.Text + "' and Date = '" + Date + "'";
                conn.Open();
                comm2.ExecuteNonQuery();
                conn.Close();
            }
            else
            {
                SqlCommand comm2 = conn.CreateCommand();
                comm2.CommandText = "Update Time_Logs SET Late = '" + ts + "' where Emp_Id = '" + EmpId.Text + "' and Date = '00:00:00'";
                conn.Open();
                comm2.ExecuteNonQuery();
                conn.Close();
            }
        }
        catch (Exception x)
        {
            MessageBox.Show(x.Message);
            conn.Close();
        }

database – Need help for creating nodes but only if they not exist

I have Company data in external database. The first column of the data contains the id of different companys.
I will create nodes for every company but only if they do not exist identified by the id in the first column.

// Get Data from external DATABASE
  db_set_active('db_wirdfit');
  $result_wirdfit = db_query('SELECT * FROM wirdfit.wirdfit_data');
  db_set_active();
  $result_drupal = db_query('SELECT * From drupal.field_data_field_id');
  $wirdfit_id = array();
  $drupal_id = array();

 foreach ($result_wirdfit as $row) {
  $wirdfit_id= $row->id;

  $node = (object) array();
  $node->language = LANGUAGE_NONE;
  $node->type = 'page';
  $node->title = $row->id;
  node_object_prepare($node);  //Set some default values

   foreach ($result_drupal as $row1) {
   $drupal_id = $row1->field_id_value;
  
if ($wirdfit_id != $drupal_id){
  $node->is_new =TRUE;
  node_save($node);
 }
}

There is a failure in the loop i think i missed some conditions and don´t get it. It creates a node that is already available.

Thanks

hash – BruteForce Encrypted DataBase – Challenge

I got a challenge to brute force encrypted DB. I received zip file with: DB,DLL(encrypted) and exe.

I opened the exe with DnSpy to read the logic: it requires 4 digit pin code to Decrypt the DB and DLL.

The logic is this: the pin code must be in length of 4. After that the code hashes the pin 10 times, saves the value as str2. After that hashes str2 another 10 times and save as str3.

Now check if Text3 is equal to an hard coded MD5 hash :”2D3114BCC2E5C58BBAC77F04237723D9″, if yes then decrypt the encrypted files using str2 as key.

The problem is that I brute forced it with no success, no number from 0000-9999 if hashed 20 times is equal to the hardcoded value in the source code.

Then I thought maybe the challenge is harder and no 4 digit pin is equal to the hard coded hash (if hashed 20 times). I thought maybe brute forcing the dycryption? That way I’ll get 10,0000 files but how do I distinguish between the real DB and between file decrypted with the wrong key?

Thanks!

str = this.richTextBoxPinCode.Text;
    if (str.Length != 4)
    {
        this.richTextBoxPinCode.Text = "";
        return;
    }

    try
    {
        str2 = str;
        str3 = str;
        num = 0;
   
        while (true)
        {
            if (num >= 10)
            {
                num2 = 0;
           
                while (true)
                {
                    if (num2 >= 10)
                    {
                        if ("2D3114BCC2E5C58BBAC77F04237723D9" == str2)
                        {
                            buffer = StringToByteArray(str3);
                            this.DecryptFile(buffer, "ForceCoinTransactionSigner.dll.enc", "ForceCoinTransactionSigner.dll");
                            this.DecryptFile(buffer, "db.txt.enc", "db.txt");
                            base.Hide();
                            new AppForm().ShowDialog();
                            base.Close();
                        }
                        break;
                    }
                    str2 = this.DoMD5(str2);
                    num2 += 1;
                }
                break;
            }
            str2 = this.DoMD5(str2);
            str3 = this.DoMD5(str3);
            num += 1;
        }
    }

functions – Dynamic dropdown from database table

I need a little help:
I want to create a drop-down populated by mysql database table with two options:
Select option 1 (regions)
Select option 2 (schools)

Where in option 2 appears populated according to option 1. (ie. tot Schools in region 1) and the selection should be stored in User profile.

I know that we can achieve this with functions.php
Something like this:

function loadSchools(){
        global $wpdb;
        return $wpdb->get_results( "SELECT MIN(school_id) AS id, region FROM region GROUP BY region" );
    } 

...

Someone already did something similar?

Thank you

MySQL – cannot examine view definition – Error getting DDL for object – No database selected

I have a view that I can run, it returns a dataset, but whenever I try to alter the view or look at its definition, I get

Error getting DDL for object
No database selected

enter image description here

But I do have a database selected and I can run the view with results.

How do I recover the definition? Please advise

postgresql – Database system is in recovery mode: Segmentation fault

PostgreSQL version : 12.4

Server: RHEL 7.9

I got postgres server into recovery mode for a minute and then came back normal.

Looking into logs, found this error before it went to recovery mode:

db=,user= LOG:  server process (PID 4321) was terminated by signal 11: Segmentation fault
db=,user= DETAIL:  Failed process was running: select distinct some_col.some_state_id,case when some_col.some_state_id=99 then 'CENTRAL' else state.state_name_english end as stateNm,case when some_col.some_state_id=99 then 'AAA' else state.state_name_english end from xema.table_name_definition_mast defn_mast left join othe.get_state_list_fn() state on some_col.some_state_id=state.state_code where defn_mast.third_srvc_launch ='Y' and some_col.some_state_id < 100 order by 3

I doubt if this issue will come up again. Is this query specific or hardware problem? Got stuck.

8 – Database query a table where field type is MEDIUMTEXT

I’m trying to get webform submission data from the database. My webform has a hidden field for a related node id, but I cannot use a query with a condition on the field. In the database, the field type for value is MEDIUMTEXT. If the value is a string, I can call the condition as a quoted string, but since it’s a number, I get no results. Whether I pass the value as quoted or not, I get no results.

How can I pass in the proper value to get any results?

$query = $database->select('webform_submission_data', 'wsd')
      ->condition('name', 'node_id', '=')
      ->condition('webform_id', 'scores', '=')
      //this is mediumtext in the database
      ->condition('value', "169", '=')
      ->fields('wsd', ('sid'));
    $result = $query->execute();