1. Advanced Deployment

1.1. Array Support

LeanXcale enhances the standard DBA API functionality by seamlessly integrating array support, providing advanced data manipulation capabilities. This powerful feature simplifies data modeling and is particularly useful when dealing with structured data where you have to aggregate in relationship 1:n. By incorporating array columns into your database schema, you can manage all the array complexity by accessing any of the fields of the array individually, retrieving all the array values, or aggregating in relationships 1:n while maintaining compatibility with standard DBA API practices.

To know more about aggregations, read this.

1.1.1. Create a Table with Array Columns

With LeanXcale, creating a table with array columns is straightforward. You can define an array column in your create table statement using the array data type, allowing you to store collections of values in a single column.

createTable = ‘CREATE TABLE tarry ("
		id INT,
		longs BIGINT ARRAY,
		doubles DOUBLE ARRAY,
		strings VARCHAR ARRAY,
		PRIMARY KEY(id))’;
try:
    cursor = conn.cursor()
    cursor.execute(createTable)
    conn.commit()
except Exception as e:
    print("Error:", e)
finally:
    cursor.close()

1.1.2. Insert in a Table with Array Columns

This code snippet will illustrate how to harness array support to efficiently manage collections of values within your database.

sql = "INSERT INTO tarry VALUES (
	1,
	array[-4367937811447089352, 7, 5575876413993699465],
	array[0.023533916958532797, 5.476721734666199, 1.0],
	array['MADRID','MURCIA','JEREZ'])"

try:
    cursor = conn.cursor()
    cursor.execute(sql)
    conn.commit()
except Exception as e:
    print("Error:", e)
finally:
    cursor.close()

1.1.3. Select from a Table with Array Columns

The following code snippets will showcase how you can leverage the array support to retrieve and manipulate data.

Querying Arrays Values
Query Arrays Using PreparedStatement

This example shows how you would bind an array value using a Prepared Statement, making it easier to parameterize your queries.

try:
cursor = conn.cursor()
rs = con_raw.cursor().execute("SELECT id FROM tarry WHERE doubles = ?", (LXARRAY(0.023533916958532797, 5.476721734666199, 1.0], float)))

for row in rs:
            print('id ' + row[0])

except Exception as e:
     print("Error:", e)
finally:
    cursor.close()
Recovery of the Array Values as String

You can retrieve the values of arrays as strings, which can be particularly useful if you need to process or display them in a human-readable format.

try:
sqlSelect = "SELECT id, TO_string(arryBigInt), TO_string(arryFloat), TO_string(arryVARCHAR) FROM tarry where id=1"

	rs = con.execute(sqlSelect)

	for row in rs:
            nrows +=1
            print(row)

except Exception as e:
     print("Error:", e)
finally:
    cursor.close()
Filter Using the Array Column
Filter Using the Entire Column

You can filter all the records with an array column containing a specific array by constructing SQL queries that match the desired array values.

try:
sqlSelect = "SELECT id, TO_string(arryBigInt), TO_string(arryFloat), TO_string(arryVARCHAR) FROM T_ARRAY where id=1"

	rs = con.execute(sqlSelect)

	for row in rs:
            nrows +=1
            print(row)

except Exception as e:
     print("Error:", e)
finally:
    cursor.close()
Filter all the Records with an Array Column Containing a Specific Array
try:
sqlSelect = "SELECT id FROM tarry, UNNEST(tarry.strings) AS t(flattened_string) where flattened_string='MADRID]"

	rs = con.execute(sqlSelect)

	for row in rs:
            nrows +=1
            print(row)

except Exception as e:
     print("Error:", e)
finally:
    cursor.close()
Filter by the Array Column Position

To filter records based on the value of a position of an array within a column, you can utilize LeanXcale’s features to retrieve them.

try:
sqlSelect = " SELECT id FROM tarry WHERE longs[3] = 5575876413993699465""

	rs = con.execute(sqlSelect)

	for row in rs:
            nrows +=1
            print(row)

except Exception as e:
     print("Error:", e)
finally:
    cursor.close()

These capabilities empower you to work effectively with array columns in LeanXcale, enabling you to handle complex data structures and perform operations straightforwardly.