TechNotes Central

Monday, November 15, 2021

Apache Cassandra with Ubuntu 20.04

Apache Cassandra with docker.

1. Installed from docker

$ docker pull cassandra:latest

$ docker run --name you_cass_cluster cassandra:latest

$ docker ps

$ docker exec -it you_cass_cluster cqlsh

2. Supported Data Types.

Supported Data Types
CQL Type	Description
ascii	US-ASCII character string
bigint	64-bit signed long
blob	Arbitrary bytes (no validation), expressed as hexadecimal
boolean	true or false
counter	Distributed counter value (64-bit long)
decimal	Variable-precision decimal
double	64-bit IEEE-754 floating point
float	32-bit IEEE-754 floating point
int	32-bit signed integer
text	UTF-8 encoded string
timestamp	Date plus time, encoded as 8 bytes since epoch
uuid	Type 1 or type 4 UUID
timeuuid	Type 1 UUID only (CQL3)
varchar	UTF-8 encoded string
varint	Arbitrary-precision integer

3. Created on KEYSPACES with next command.

CREATE KEYSPACE test_kesypace WITH replication = {'class': 'SimpleStrategy', 'replication_factor': '1'} AND durable_writes = 'true';

4. Created Tables

use test_kesypace;

CREATE TABLE employee_by_id

  (
     id       INT PRIMARY KEY,
     name     TEXT,
     position TEXT
  );

CREATE TABLE employee_by_car_make
  (
     car_make  TEXT,
     id        INT,
     car_model TEXT,
     PRIMARY KEY(car_make, id)

);

CREATE TABLE employee_by_car_make_sorted
  (
     car_make TEXT,
     age      INT,
     id       INT,
     name     TEXT,
     PRIMARY KEY(car_make, age, id)
  );

CREATE TABLE employee_by_car_make_and_model
             (
                          car_make TEXT,
                          car_model TEXT,
                          id INT,
                          name TEXT,
                          PRIMARY KEY((car_make, car_model), id)
             );

5. Insert and update

INSERT INTO employee_by_id
            (id,
             name,
             position)
VALUES      ( 1,
             'Henry',
             'Developer');

UPDATE employee_by_car_make
using  ttl 60
SET    car_model = 'Truck'
WHERE  car_make ='TOYOTA'
AND    id = 1;

6. SELECT

Cassandra DB just search by id.

SELECT *
FROM employee_by_id
WHERE id = 1;

References:

https://www.youtube.com/channel/UCHRnRlWQuZYDmnuB7SbXIpQ

Sunday, September 5, 2021

Return table on function + object types + Oracle

Sometimes we need to function on oracle, return of table because propably you need to send dinamic parameters of query, you can resolve the problem with materialized view or view but both your parameters is define, then when you want change the params you can replace or create again. but with objetc types not is necessary.

I suggest you, to read to concept object types. because i only share the example but not concept.

Step 1. i create my real or exist table and insert. for this example.

CREATE TABLE my_real_table
  (
     username VARCHAR2(25),
     age      NUMBER
  );

INSERT ALL
INTO my_real_table (username, age) VALUES ('User1', 25)
INTO my_real_table (username, age) VALUES ('User2', 25)
INTO my_real_table (username, age) VALUES ('User3', 33)
INTO my_real_table (username, age) VALUES ('User4', 30)
INTO my_real_table (username, age) VALUES ('User5', 40)
SELECT *
FROM dual;

Step 2.

Create a object of your table.

CREATE OR replace TYPE obj_test AS object (
username VARCHAR2(25),
age NUMBER );

Step 3.

Create a type of your object.

CREATE OR replace TYPE t_test
AS TABLE OF OBJ_TEST;

Step 4. Create of function with parameters and return the table depends on parameters.

CREATE OR replace FUNCTION Fu_test (v_age IN NUMBER)
RETURN T_TEST
IS
  return_value T_TEST;
BEGIN
    SELECT Obj_test(username, age)
    bulk   collect INTO return_value
    FROM   (SELECT username,
                   age
            FROM   my_real_table
            WHERE  age > = v_age);

    RETURN return_value;
END;

Step 4. you can send diferent parameters the function and return table.

SELECT *
FROM TABLE(Fu_test(30));

That it is.

Friday, May 28, 2021

Oracle tips

1. Query how to get table name by constraint name.

SELECT owner,
       table_name
FROM   dba_constraints
WHERE  constraint_name = <<your CONSTRAINT name>>

2. Query how to get primary by table name.

SELECT cols.table_name,
       cols.column_name,
       cols.position,
       cons.status,
       cons.owner
FROM   all_constraints cons,
       all_cons_columns cols
WHERE  cols.table_name = '<<your TABLE NAME name>>'
       AND cons.constraint_type = 'P'
       AND cons.constraint_name = cols.constraint_name
       AND cons.owner = cols.owner
ORDER  BY cols.table_name,
          cols.position;

3. Query how to show running processes in Oracle DB

SELECT sess.process,
       sess.status,
       sess.username,
       sess.schemaname,
       SQL.sql_text
FROM   v$session sess,
       v$sql SQL
WHERE  SQL.sql_id(+) = sess.sql_id
       AND sess.username = 'YOUR_SCHEMA'
       AND sess.status = 'ACTIVE';

4. Query check SID and SERIAL FOR KILL SESSION

SELECT sid,
       serial#,
       status,
       server
FROM   v$session
WHERE  username = 'YOUR_SCHEMA';

5. Query kill process.

ALTER SYSTEM kill SESSION 'SID,SERIAL';

6. Query check all constraint in Oracle.

SELECT table_name,
       constraint_name,
       status,
       owner
FROM   all_constraints;

7. Query constraint name or table name etc.

SELECT table_name,
       constraint_name,
       status,
       owner
FROM   all_constraints
WHERE  constraint_name = 'YOUR_CONSTRAINT_NAME';

8. Flashback Query

If you for error delete rows on oracle, you can do data recovery with that query and depending on the number of transaction you have been on databases, if you made a lot of transaction the retention time is short. if you truncate the table you can't data recovery with that query.

SELECT *
FROM your_name_table AS OF timestamp(systimestamp - interval '60' minute);

8. DB Link in Oracle 11g

Run next command from SQL*Plus or JDeveloper.

CREATE DATABASE LINK your_link_name

CONNECT TO your_remote_db

IDENTIFIED BY your_password_remote_db

USING '(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP) (HOST =your_remote_ip)(PORT = 1521)) (CONNECT_DATA = (SID = your_schema)))';

Check remote table:

SELECT *
FROM your_table_remote@your_link_name;

9. Database name

SELECT ora_database_name
FROM dual;

10. Database Schema

SELECT Sys_context('USERENV', 'current_schema')
FROM dual;

11. SID

SELECT Sys_context('USERENV', 'SID')
FROM dual;

12. Check if exist store procedure

SELECT *
FROM   all_objects
WHERE  object_type = 'PROCEDURE'
       AND object_name = 'YOUR_PROCEDURE_NAME'
       AND owner = 'YOUR_SCHEMA_NAME';

References:https://stackoverflow.com/questions/5247858/get-table-name-by-constraint-name/5247901

Tuesday, April 13, 2021

debconf: DbDriver "config": /var/cache/debconf/config.dat is locked by another process: Resource temporarily unavailable

I share solution next error ubuntu 20.04.

Run the next command.

sudo fuser -v /var/cache/debconf/config.dat

Kill process.

sudo kill -9 PID

Next:

sudo apt update;

sudo apt upgrade;

That it is.

References.

https://askubuntu.com/questions/136881/debconf-dbdriver-config-config-dat-is-locked-by-another-process-resource-t

Wednesday, March 3, 2021

ERROR: dependency ‘curl’ is not available for package ‘httr’ + R Studio + Ubuntu 20.04

When i try to install tidyverse packages on R studio on ubuntu i have nexts errors, i share solutions.

ERROR: configuration failed for package ‘xml2’

Solution:

sudo apt-get install libxml2-dev

ERROR: dependency ‘curl’ is not available for package ‘httr’

ERROR: configuration failed for package ‘curl’

Solution:

sudo apt install r-cran-curl

I try again.

References:

https://stackoverflow.com/questions/52082543/curl-package-not-available-for-several-r-packages

https://github.com/r-lib/xml2/issues/223

Monday, February 15, 2021

CXF: No message body writer found for class - Spring boot 2

When i try integrate CXF with spring boot i have next error.

For solution next error add next dependency on your pom.

<dependency>
   <groupId>org.codehaus.jackson</groupId>
   <artifactId>jackson-jaxrs</artifactId>
   <version>1.9.13</version>
</dependency>
<dependency>
   <groupId>org.codehaus.jackson</groupId>
   <artifactId>jackson-xc</artifactId>
   <version>1.9.13</version>
</dependency>

And try again