Tag: sqlite

SQLite3 and multiple processes

First, avoid concurrent access to sqlite database files. Concurrency is one of sqlite’s weak points and if you have a highly concurrent application, consider using another database engine.

If you cannot avoid concurrency or drop sqlite, wrap your write transactions in BEGIN IMMEDIATE; … END;. The default transaction mode in sqlite is DEFERRED which means that a lock is acquired only on first actual write attempt. With IMMEDIATE transactions, the lock is acquired immediately, or you get SQLITE_BUSY immediately. When someone holds a lock to the database, other locking attempts will result in SQLITE_BUSY.

Dealing with SQLITE_BUSY is something you have to decide for yourself. For many applications, waiting for a second or two and then retrying works quite all right, giving up after n failed attempts. There are sqlite3 API helpers that make this easy, e.g. sqlite3_busy_handler() and sqlite3_busy_timeout() but it can be done manually as well.

You could also use OS level synchronization to acquire a mutex lock to the database, or use OS level inter-thread/inter-process messaging to signal when one thread is done accessing the database.

If you want to use Python’s automatic transaction handling, leave isolation_level at its default value, or set it to one of the three levels.

If you want to do your own transaction handling, you have to prevent Python from doing its own by setting isolation_level to None.

References
https://stackoverflow.com/questions/1063438/sqlite3-and-multiple-processes
https://stackoverflow.com/questions/41915603/python3-sqlite3-begin-immediate-error

SQlite WAL-mode in python

Reader

import sqlite3
from time import sleep
conn = sqlite3.connect('example.db', isolation_level=None)

c = conn.cursor()
while True:
    c.execute("SELECT * FROM statistics")
    try:
        print '**'
        print c.fetchone()
    except:
        pass
    sleep(3)

Writer

import sqlite3
from time import sleep
import os

if os.path.exists('example.db'):
    os.remove('example.db')

conn = sqlite3.connect('example.db', isolation_level=None)
c = conn.cursor()
c.execute('PRAGMA journal_mode=wal')
print c.fetchone()
c.execute("CREATE TABLE statistics (stat1 real, stat2 real)")

stat1 = 0
stat2 = 0.0
while True:
    stat1 += 1
    stat2 += 0.1
    cmd = "INSERT INTO statistics VALUES (%d,%f)" % (stat1, stat2)
    print cmd
    c.execute(cmd)
    #conn.commit()
    c.execute("PRAGMA wal_checkpoint=FULL")
    sleep(0.25)

References
https://stackoverflow.com/questions/30821179/sqlite-wal-mode-in-python-concurrency-with-one-writer-and-one-reader
https://sqlite.org/wal.html

Delete Data in Python SQLite

def delete_task(conn, id):
    """
    Delete a task by task id
    :param conn:  Connection to the SQLite database
    :param id: id of the task
    :return:
    """
    sql = 'DELETE FROM tasks WHERE id=?'
    cur = conn.cursor()
    cur.execute(sql, (id,))
    conn.commit()
def delete_all_tasks(conn):
    """
    Delete all rows in the tasks table
    :param conn: Connection to the SQLite database
    :return:
    """
    sql = 'DELETE FROM tasks'
    cur = conn.cursor()
    cur.execute(sql)
    conn.commit()

References
https://www.sqlitetutorial.net/sqlite-python/delete/

Querying Data in Python SQLite

def select_all_tasks(conn):
    """
    Query all rows in the tasks table
    :param conn: the Connection object
    :return:
    """
    cur = conn.cursor()
    cur.execute("SELECT * FROM tasks")

    rows = cur.fetchall()

    for row in rows:
        print(row)
def select_task_by_priority(conn, priority):
    """
    Query tasks by priority
    :param conn: the Connection object
    :param priority:
    :return:
    """
    cur = conn.cursor()
    cur.execute("SELECT * FROM tasks WHERE priority=?", (priority,))

    rows = cur.fetchall()

    for row in rows:
        print(row)

References
https://www.sqlitetutorial.net/sqlite-python/sqlite-python-select/

Working with greenDAO on Android

build.gradle

buildscript {
    repositories {
        jcenter()
        mavenCentral()
    }
    dependencies {
        classpath 'com.android.tools.build:gradle:2.3.3'
        classpath 'org.greenrobot:greendao-gradle-plugin:3.2.2'
    }
}

allprojects {
    repositories {
        jcenter()
    }
}

task clean(type: Delete) {
    delete rootProject.buildDir
}

build.gradle

apply plugin: 'com.android.application'
apply plugin: 'org.greenrobot.greendao'

android {
    compileSdkVersion 26
    buildToolsVersion "26.0.0"
    defaultConfig {
        applicationId "ir.mhdr.a097"
        minSdkVersion 17
        targetSdkVersion 26
        versionCode 1
        versionName "1.0"
        testInstrumentationRunner "android.support.test.runner.AndroidJUnitRunner"
    }
    buildTypes {
        release {
            minifyEnabled false
            proguardFiles getDefaultProguardFile('proguard-android.txt'), 'proguard-rules.pro'
        }
    }
}

dependencies {
    compile fileTree(dir: 'libs', include: ['*.jar'])
    androidTestCompile('com.android.support.test.espresso:espresso-core:2.2.2', {
        exclude group: 'com.android.support', module: 'support-annotations'
    })
    compile 'com.android.support:appcompat-v7:26.+'
    compile 'com.android.support.constraint:constraint-layout:1.0.2'
    compile 'org.greenrobot:greendao:3.2.2'

    // This is only needed if you want to use encrypted databases
    compile 'net.zetetic:android-database-sqlcipher:3.5.6'

    testCompile 'junit:junit:4.12'
}

App.java

public class App extends Application {

    public static final boolean ENCRYPTED = false;

    @Override
    public void onCreate() {
        super.onCreate();

        DaoMaster.DevOpenHelper helper = new DaoMaster.DevOpenHelper(this, ENCRYPTED ? "notes-db-encrypted" : "notes-db");
        Database db = ENCRYPTED ? helper.getEncryptedWritableDb("super-secret") : helper.getWritableDb();
        Statics.daoSession = new DaoMaster(db).newSession();
    }
}

AndroidManifest.xml

<?xml version="1.0" encoding="utf-8"?>
<manifest xmlns:android="http://schemas.android.com/apk/res/android"
    package="ir.mhdr.a097">

    <application
        android:name=".App"
        android:allowBackup="true"
        android:icon="@mipmap/ic_launcher"
        android:label="@string/app_name"
        android:roundIcon="@mipmap/ic_launcher_round"
        android:supportsRtl="true"
        android:theme="@style/AppTheme">
        <activity android:name=".MainActivity">
            <intent-filter>
                <action android:name="android.intent.action.MAIN" />

                <category android:name="android.intent.category.LAUNCHER" />
            </intent-filter>
        </activity>
    </application>

</manifest>

Statice.java

public class Statics {

    public static DaoSession daoSession;
}

Note.java

@Entity(indexes = {
        @Index(value = "text, date DESC", unique = true)
})
public class Note {

    @Id(autoincrement = true)
    private Long id;

    @Index(unique = true)
    private String uuid;

    @NotNull
    private String text;

    @Property(nameInDb = "date")
    private Date date;

    @Transient
    private int tempUsageCount;

@Generated(hash = 102375063)
public Note(Long id, String uuid, @NotNull String text, Date date) {
    this.id = id;
    this.uuid = uuid;
    this.text = text;
    this.date = date;
}
@Generated(hash = 1272611929)
public Note() {
}
public Long getId() {
    return this.id;
}
public void setId(Long id) {
    this.id = id;
}
public String getText() {
    return this.text;
}
public void setText(String text) {
    this.text = text;
}
public Date getDate() {
    return this.date;
}
public void setDate(Date date) {
    this.date = date;
}
public String getUuid() {
    return this.uuid;
}
public void setUuid(String uuid) {
    this.uuid = uuid;
}
}

MainActivity.java

public class MainActivity extends AppCompatActivity {

    private DaoSession daoSession;
    private NoteDao noteDao;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);

        daoSession = Statics.daoSession;
        noteDao = daoSession.getNoteDao();

        Note note = new Note();
        note.setUuid(UUID.randomUUID().toString());
        note.setDate(new Date());
        note.setText("Hello World");

        noteDao.save(note);
    }
}

if using Kotlin

android {
    ...
    sourceSets {
        main.java.srcDirs += 'build/generated/source/greendao'
    }
}

References
http://greenrobot.org/greendao/documentation/
https://github.com/mhdr/AndroidSamples/tree/master/097
https://github.com/greenrobot/greenDAO/issues/395

Create a Database with ADO.NET in Xamarin Android

First we need WriteExternalStorage Permission

Add a reference to System.Data and to Mono.Data.SQLite

// with Android.Graphics & Android.Environment
var docsFolder = System.Environment.GetFolderPath(System.Environment.SpecialFolder.MyDocuments);
var pathToDatabase = System.IO.Path.Combine(docsFolder, "db_adonet.db");
SqliteConnection.CreateFile(pathToDatabase);

// without Android.Graphics & Android.Environment
using System.IO;

var docsFolder = Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments);
var pathToDatabase = Path.Combine(docsFolder, "db_adonet.db");
SqliteConnection.CreateFile(pathToDatabase);
// with try / catch
try
{
    var docsFolder = Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments);
    var pathToDatabase = Path.Combine(docsFolder, "db_adonet.db");
    SqliteConnection.CreateFile(pathToDatabase);
}
catch (IOException ex)
{
    var reason = string.Format("The database failed to create - reason {0}", ex.Message);
    Toast.MakeText(myContext, reason, ToastLength.Long).Show();
}

// using File.Exists
if (!File.Exists(pathToDatabase))
{
    var reason = string.Format("The database failed to create - reason {0}", ex.Message);
    Toast.MakeText(myContext, reason, ToastLength.Long).Show();
}

// create a connection string for the database
var connectionString = string.Format("Data Source={0};Version=3;", pathToDatabase);
            try
{
    using (var conn = new SqliteConnection((connectionString)))
    {
        await conn.OpenAsync();
        using (var command = conn.CreateCommand())
        {
            command.CommandText = "CREATE TABLE People (PersonID INTEGER PRIMARY KEY AUTOINCREMENT, FirstName ntext, LastName ntext)";
            command.CommandType = CommandType.Text;
            await command.ExecuteNonQueryAsync();
        }
    }
}
catch (Exception ex)
{
    var reason = string.Format("Failed to insert into the database - reason = {0}", ex.Message);
    Toast.MakeText(myContext, reason, ToastLength.Long).Show();
}

References :
https://developer.xamarin.com/recipes/android/data/databases/adonet/