DEV Community: mani-playground

Useful maven commands

mani-playground — Tue, 29 Jun 2021 02:47:57 +0000

Dependency tree

mvn org.apache.maven.plugins:maven-dependency-plugin:tree -Dverbose=true -Dincludes=org.springframework.batch

Skip tests

mvn clean install  -Dmaven.test.skip=true

Spring boot properties file tips

mani-playground — Tue, 16 Mar 2021 18:44:50 +0000

Setting a variable with the value of a property from .properties file (note how spring does automatic type conversion to int)

    @Value("${some.property}")
    private int SOME_PROPERTY;

Use a default property value if the specified property is not present

    @Value("#{'${prop.val.specific:${prop.val.default:}}'}")
    private String PROP_VALUE;

Setting comma separated values to a List

    @Value("#{'${comma.separated.property}'.split(',')}")
    private List<String> COMMA_SEPARATED_PROPERTY;

MyBatis tips

mani-playground — Wed, 13 Jan 2021 18:06:16 +0000

Conditional update query with MyBatis

Sample code for conditional update (testing for a value using if) when using annotation based MyBatis mapper interface in Java.

COLUMN_1 and COLUMN_2 are updated with values from field1 and field2 respectively only if they are not null. DataUpdateParams class will have all the fields in parameters like field1, field2, name, id and userId with corresponding getters/setters.

Note that the query needs to be enclosed within tag.

The example uses syntax for SQL server but works similar for other databases too.

public static String UPDATE_MY_DATA = 
            "<script>" +
                "  UPDATE B " + 
                " <set> " + 
                    "<if test='field1 != null'>A.COLUMN_1 = #{field1, jdbcType=VARCHAR},</if>" +
                    "<if test='field2 != null'>A.COLUMN_2 = #{field2, jdbcType=VARCHAR},</if>" +
                    "A.UPDATE_ID = #{userId, jdbcType=VARCHAR}, " + 
                    "A.UPDATE_TIMESTAMP = GETDATE() " + 
                " </set>" +
                "  FROM TABLE_1 A " + 
                "  INNER JOIN TABLE_2 B ON B.B_ID = A.B_ID " + 
                "  WHERE B.NAME = #{name, jdbcType=VARCHAR} " +                 
                "  AND A.A_ID = #{id, jdbcType=BIGINT}" +
            "</script>";

    @Update(UPDATE_MY_DATA)
    public void updateMyData(DataUpdateParams params);

For-each loop

Below example (with xml file) showing how to use a foreach loop in mybatis. This can have several use cases - For eg., when dynamically forming IN clause for select, update or delete query.

 <delete id="deleteUsers">
        DELETE
        FROM USER
        WHERE USER_ID IN (
                <foreach item="userName" collection="list" separator="," open="(" close=")">
                    '${userName}'
                </foreach>
            )
        )
   </delete>

It can be invoked from java code something similar to below -

   List<String> userNames = Arrays.asList("first_user", "second_user");
   int deletedCount = session.delete("mapper.deleteUsers", userNames);

Inserting to a table with foreign key value generated from a different insert

Assuming we are inserting to EMPLOYEE_ADDRESS table with the EMPLOYEE_ID generated when inserting to EMPLOYEE table
Mapper interface

@Repository
@Mapper
public interface EmployeeMapper {

    final String INSERT_EMPLOYEE = "INSERT INTO EMPLOYEE(FNAME, LNAME, DEPT, INSERT_ID, INSERT_TIMESTAMP)"
            + "VALUES(#{fName}, #{lName}, #{dept}, #{userId}, GETDATE())";

    final String INSERT_EMPLOYEE_ADDRESS = "INSERT INTO EMPLOYEE_ADDRESS(EMPLOYEE_ID, ADDRESS_1, ADDRESS_2, CITY, STATE, ZIP, INSERT_ID, INSERT_TIMESTAMP)"
            + "VALUES(#{employeeId}, #{address1}, #{address2}, #{city}, #{state}, #{zip}, #{userId}, GETDATE())";

    @Insert(INSERT_EMPLOYEE)
    //This stores the generated key EMPLOYEE_ID into the property employeeId
    @Options(useGeneratedKeys=true, keyProperty="employeeId", keyColumn="EMPLOYEE_ID")
    void insertEmployee(Employee employee);

    @Insert(INSERT_EMPLOYEE_ADDRESS)
    void insertEmployeeAddress(EmployeeAddress employeeAddress);

}

Calling program

employeeMapper.insertEmployee(employee);
employeeAddress.setEmployeeId(employee.getEmployeeId()); //Generated Employee id that was set by MyBatis in employee object is set to employeeAddress
employeeMapper.insertEmployeeAddress(employeeAddress);

SQL Server System versioned temporal table

mani-playground — Sat, 02 Jan 2021 14:42:22 +0000

There are times when we need to maintain an audit trail of data changes made to a database table. SQL Server 2016 added support for Temporal tables to achieve this.

Adding versioning to an existing table
A Non-Temporal Table can be changed into a System-Versioned Temporal Table by adding PERIOD definitions and enabling System versioning as below.

ALTER TABLE myschema.mytable
    ADD   
        SysStartTime DATETIME2(0) GENERATED ALWAYS AS ROW START HIDDEN
            CONSTRAINT DF_SysStart DEFAULT SYSUTCDATETIME()
      , SysEndTime DATETIME2(0) GENERATED ALWAYS AS ROW END HIDDEN
            CONSTRAINT DF_SysEnd DEFAULT CONVERT(DATETIME2 (0), '9999-12-31 23:59:59'),
        PERIOD FOR SYSTEM_TIME (SysStartTime, SysEndTime);        

ALTER TABLE myschema.mytable  
SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = history.trial_mytable));

Querying historical data from a temporal table
The power of this feature lies in its simplicity. Once System versioning is enabled, we can forget about the history table. SQL Server manages the audit/history of changes for us. We could directly query the main table and get a snapshot of how the data looked at any point in the past using the syntax below.

select *
from myschema.mytable
FOR SYSTEM_TIME AS OF '2020-12-31 17:47:46' ;

Another good thing is that only the changes get stored in the history table. That is, if we created a table, enabled versioning and just inserted a bunch of rows to it, then the history table will be empty. Only if the data in the main table is updated or deleted, the changes get into the history table. In other words, SQL Server does not create duplicate records between the main table and history table.

Refer to SQL Server documentation for some of the limitations / things to keep in mind when using System-versioned temporal tables.

Source :
https://docs.microsoft.com/en-us/sql/relational-databases/tables/creating-a-system-versioned-temporal-table?view=sql-server-2017

Useful SQL Server sys table queries and stored procs

mani-playground — Sat, 02 Jan 2021 14:10:59 +0000

List of tables in a schema with schema name

select s.name + '.' + o.name
from sys.objects o
inner join sys.schemas s on s.schema_id = o.schema_id
where s.name = '<schema name>'
and [type] = 'U';

List of tables which has the column

SELECT      
    TABLE_SCHEMA AS 'Schema',
    COLUMN_NAME AS 'ColumnName',
           TABLE_NAME AS  'TableName'
FROM        INFORMATION_SCHEMA.COLUMNS
WHERE       COLUMN_NAME LIKE '%ColumnName%'
ORDER BY    TableName
            ,ColumnName;

Recently modified objects

select [type] OBJ_TYPE, s.name + '.' + o.name, *
from sys.objects o
inner join sys.schemas s on s.schema_id = o.schema_id
where 1=1
and [type] IN ('P','IF', 'FN', 'TF', 'V')
and o.create_date > '2023-06-05 11:07:31.187' --replace with required date
and s.name in ('dbo') -- modify / add schemas to include
ORDER BY [type], s.name, o.create_date

Create a new table with columns copied from an existing table (without data)

select top 0 * into NewTable 
from ExistingTable;

Delete all tables in all schema in a database

EXEC sp_MSForEachTable 'DISABLE TRIGGER ALL ON ?'
GO
EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
GO
EXEC sp_MSForEachTable 'DELETE FROM ?'
GO
EXEC sp_MSForEachTable 'ALTER TABLE ? CHECK CONSTRAINT ALL'
GO
EXEC sp_MSForEachTable 'ENABLE TRIGGER ALL ON ?'
GO

Find objects (Stored proc, function etc.,) that contain the given text

SELECT DISTINCT
    o.name AS object_name,
    o.type_desc AS object_type,
    s.name
FROM sys.objects AS o
JOIN sys.sql_modules AS m ON o.object_id = m.object_id
JOIN sys.schemas AS s ON s.schema_id = o.schema_id
WHERE m.definition LIKE '%ReplaceWithTextToSearch%'

LIKE operation on an XML column
We can't do a like operation on an XML column in SQL Server. As a work around, just cast the column to varchar.

select *
from schema.table
where CAST(xml_col_nm as nvarchar(max)) like '%search_term%'

Stats
Check when stats was last updated for tables in a given schema

SELECT OBJECT_NAME(st.object_id) AS [ObjectName]
      ,st.[name] AS [StatisticName]
      ,STATS_DATE(st.[object_id], [stats_id]) AS [StatisticUpdateDate]
FROM sys.stats st
inner join sys.objects o on o.object_id = st.object_id
inner join sys.schemas s on s.schema_id = o.schema_id
where s.name = '<schema name>';

Start a flask app

mani-playground — Fri, 01 Jan 2021 19:56:12 +0000

source venv/bin/activate
export FLASK_APP=flaskr
flask run

//If we need to make the server publicly available
flask run --host=0.0.0.0

Reference :
https://flask.palletsprojects.com/en/1.1.x/quickstart/

Update node version

mani-playground — Fri, 01 Jan 2021 19:38:58 +0000

nvm --version
nvm ls
nvm ls-remote

nvm install <version>
node -v

Building a simple Alexa voice app

mani-playground — Fri, 01 Jan 2021 18:31:46 +0000

Created a voice app using Violet, which is a javascript based opensource framework by developers at Salesforce.

Code sample is here

To test the alexa skill deployed locally, it can be exposed externally using ngrok so that Alexa server can access it.

After installing ngrok, below is the command to expose service running in local machine on port 8080.

ngrok http 8080

In this case, the service can be accessed in the web using the below url (note : The link expires in 8 hours in the free version of ngrok)
https://561adde7bf38.ngrok.io/

The endpoint https://561adde7bf38.ngrok.io/alexa needs to be specified in Alexa developer console by selecting HTTPS under Endpoint section.

Make sure to select below option
My development endpoint is a sub-domain of a domain that has a wildcard certificate from a certificate authority

Sample interaction model json to be specified in the Alexa developer console under "Interaction Model -> JSON Editor" is included here

References :
https://hackernoon.com/how-to-build-a-voice-app-in-30-minutes-uz6132kj
https://i3labs.org/2019/03/getting-inputs-for-your-voice-app
https://www.macadamian.com/learn/developing-alexa-skills-locally-with-ngrok-and-node-js/