Storing data on the device (key/value, NoSQL, SQL)

Alternative approaches to storage

SQL

The built-in SQLite is standard for Android. Its advantages are that it is a classic relational DBMS - fast work, SQL queries, aggregation functions. SQL is good for an established architecture with many tables linked by keys. Or, for example, to calculate aggregate functions for large tables, such as balances.

You can create several DBMS within the application. Moreover, it is recommended to work not with the default DBMS, but to create your own.

Warning

SQLite on Android feature. SQLite on Android reacts poorly to multi-user connections. And this, for example, can be, for example, work in the background on a schedule and in parallel some writing to the database from the screen. Therefore, a couple of recommendations: 1) use a separate database for your configuration. So at least you will not intersect with the application (which also writes to its SQL) 2) try to access the database (even for reading) through a single connection point (singleton). By default, SimpleUI has a SimpleSQLProvider class that implements this pattern, but you can organize your own.

You can work with SQLite:

  • directly from Python using sqlite3

  • from Python using ORM Pony

  • via variable stack (implemented via SimpleSQLProvider)

  • via singleton class SimpleSQLProvider

  • from javascript handler (implemented via SimpleSQLProvider )

Via sqlite3

I’ll just give an example where the connection string to the database matters. The rest is standard.

import sqlite3
try:
  connection = sqlite3.connect('/data/data/ru.travelfood.simple_ui/databases/my_database.db')
  cursor = connection.cursor()

  # Create the Users table
  cursor.execute('''
  CREATE TABLE IF NOT EXISTS Users (
  id INTEGER PRIMARY KEY,
  username TEXT NOT NULL,
  email TEXT NOT NULL,
  age INTEGER
  )
  ''')

  # Save changes and close the connection
  connection.commit()

  # Add a new user
  cursor.execute('INSERT INTO Users (username, email, age) VALUES (?, ?, ?)', ('newuser', ' newuser@example.com ', 28))

  # Save changes and close the connection
  connection.commit()

  cursor.execute('SELECT * FROM Users')
  users = cursor.fetchall()

  res=""
  for user in users:
    res+=str(user)

  connection.close()

  hashMap.put("result",res)
except Exception as e:
  toast(str(e))

Through the variable stack

SQLConnectDatabase, database name. Since the database name is specified, it is assumed that several databases can be used, in addition to the default one.

hashMap.put("SQLConnectDatabase","test_perform.DB")

SQLExec,{“query”:<SQL query>,”params”:<parameters separated by commas or JSON array>} Executes a query to change the database (all except SELECT), the parameters in the query are specified in an unnamed form, and in params, they are listed separated by commas. Or you can specify the parameters via a JSON array

For example:

hashMap.put("SQLExec",json.dumps({"query":"create table IF NOT EXISTS goods (id integer primary key autoincrement,art text, barcode text, nom text)","params":""}))

SQLExecMany, {“query”:”SQL statement”,”params”:”array of parameters”} – executes a query in BULK mode with an array of multiple records. The query parameters are passed as an array of records in the form of a string – a JSON array

Example:

values=[]
for i in range(1,3):
      record =[]
      record.append("AA"+str(i))
      record.append("22"+str(i))
      record.append("Product via variable "+str(i))
      values.append(record)


hashMap.put("SQLExecMany",json.dumps({"query":"insert into goods(art,barcode,nom) values(?,?,?)","params":json.dumps(values,ensure_ascii=False)}))

SQLParameter – makes sense for SQLExecMany to pass an array of records as a parameter from other handlers

SQLQuery ,{“query”:”SQL statement”,”params”:”parameters with delimiter”} – a SELECT query that writes the selection as a JSON array to the variable stack in SQLResult

SQLQueryMany ,{“query”:”SQL statement”,”params”:”parameters with delimiter”} – a SELECT query that writes the selection as a JSON array to a temporary file and in a parameter SQLResultFile returns the name of this file. For very large selections (>0.5 million rows)

Via SimpleSQLProvider

The above variable stack commands can be called directly from the SimpleSQLProvider class object. This option is good because you get the result immediately and not at the end of the step, and it is better to use it in python handlers.

from ru.travelfood.simple_ui import SimpleSQLProvider as sqlClass
sql = sqlClass()
  success=sql.SQLExec("insert into goods(art,barcode,nom) values(?,?,?)","111222,22000332323,Some product")
  res = sql.SQLQuery("select * from goods where id=1","")
  if success:
      hashMap.put("toast",res)

Using Pony ORM

A convenient option for working with DBMS is ORM as a concept in general, and Pony ORM in particular. Examples of working with ORM are in many demo configurations, a description directly Pony https://ponyorm.readthedocs.io/en/latest/firststeps.html

You can see an example here (but keep in mind that the configuration is outdated): https://github.com/dvdocumentation/simpleui_samples/tree/main/Simple%20Warehouse

Working with SQLlite in a javascript handler

Implemented a wrapper class for SimpleSQLProvider to directly access SQLite

You can see the current examples here. https://github.com/dvdocumentation/simpleui_samples/tree/main/javascript

Working with the device’s DBMS from a computer

_images/debug_2.jpg

On a computer, you can connect a device in debug mode (via a cloud bus and editor) and execute the python handler code on a specific device, immediately receiving responses via a variable stack. This principle can be used to view and manipulate SQL data (and other DBMS). That is, the handler connects to the required database, makes a request, possibly receives a response and puts it (as a JSON string) into the variable stack, and the developer views it in a JSON editor.

This method is described in detail in this thread: https://t.me/simpledevchat/4817