Dylan  Iqbal

Dylan Iqbal

1663986934

Learn SQLite - Database Tutorial for Beginners

Learn SQLite Fundamentals and basic SQL commands in this course for beginners. This SQLITE Course covers SQL using SQLITE Database but you can apply these SQL queries in SQL Server, Oracle, MySql, PostgreSQL, Microsoft Access, SQLite, and DB2'.

You'll learn:

  • Start your career by learning all the basic SQL statements in SQLite
  • Learn different ways to select Records from Tables
  • Filter the Records of the Table
  • How to sort the result
  • GROUP BY Clause
  • Create Tables and Insert Records
  • Update data inside table, modify tables
  • Delete and Drop tables

This SQLITE Course covers SQL using SQLITE Database but you can apply these SQL queries in SQL Server, Oracle, MySql, PostgreSQL, Microsoft Access, SQLite, and DB2'.

"Welcome to the most popular Quality crash Course on SQLite"

  •    Different shell commands covered
  •    Covers different forms of SELECT Statements
  •    Explains how to filter the Records
  •    NULL Values are explained
  •    How to save database
  •    ORDER BY, GROUP BY are covered
  •    Basic Data types used in Microsoft SQL

======================================================================================

Want to start learning SQL from scratch with no previous coding experience? You have come to the right place.

  •    Is the Course content clear and Precise? The content is short, crisp, and clear.
  •    The course assumes no prior knowledge of SQLite(SQLite database) and teaches you from absolute beginners
  •    This Course is not for DBA (Database Administration) but certainly, helps you to become better.
  •    If you plan to work with other databases like Oracle, MySQL, SQLite, PostgreSQL, etc, it will be extremely helpful.
  •    If you are a data scientist (Data Analysis role) or willing to become a data scientist, then SQL is a must and this course helps in data analytics.


Do you want to start on SQL but have no experience with SQL? 
If you have some prior knowledge of SQL or if you are a complete fresher, you are at the right place. The Course teaches you to SQL right from Scratch. It will be the best course for absolute beginners.


What you'll learn:

1) Selecting Records from Table - SELECT statement in SQL

2) Filtering the Records - Using the WHERE clause in SQL

3) Sorting Records - Using ORDER BY clause

4) Grouping data - using GROUP BY clause

5) Creating simple basic Tables - with constraints and without constraints

6) Keys, Index - PRIMARY KEY, UNIQUE INDEX

7) Inserting records(loading) - with INSERT Statement

8) Updating the records of a Table - Using the UPDATE statement

9) Modify table properties - ALTER statement

10) Deleting records from the table - DELETE statement

11) Removing tables from the database permanently - DROP statement


Note: This course helps to learn SQL using SQLite but it will also help to master SQL if you want to learn Oracle SQL, MySql, PostgreSQL, SQLite, DB2, etc.

Requirements

  •    No knowledge of SQL required for this course
  •    A laptop/computer with Windows 8 or Windows 10

Who this course is for:

  •    Absolute beginners who have never used SQL on SQLite Server
  •    Anyone who wants to refresh the knowledge of SQL
  •    This is a crash couCollege Students who wants to attend a technical job interview for the first time will also be an ideal choicerse and is target for quick basic learning of SQL

#sql #sqlite #database

Learn SQLite - Database Tutorial for Beginners
Rachel Cole

Rachel Cole

1663296927

SurrealDB: The Future of Cloud Databases

Getting started with SurrealDB!! Future of cloud databases (maybe)? Does this replace sqlite?

What is SurrealDB?

surrealdb is a new cloud database that's designed natively to run on cloud, browser and edge. In this tutorial we look at the issues that existing database technologies have for running on edge and look at how surreal db solves these issues over existing technologies such as sqlite.  More importantly.. it's super light and super fast.

surrealdb can operate a key-value pair database like redis, relational database like sql server or postgresql or filesystem based database like sqlite (sqlite3).  it has native json capabilities as well as graph database capabilities like neo4j.

In this tutorial we get up and running with surrealdb and perform basic queries, insert data, and join data together.   this will give us the basic feeling of the database and how to get started with it.  

00:00 - the evolution of compute architecture
01:10 - evolution of databases
01:50 - sqlite and the problem it solves
02:35 - the problems with sqlite on cloud native architectures
03:22 - surrealdb and the need for a new cloud native database technology
04:04 - installing surrealdb
04:55 - surrealdb CLI
05:27 - starting the surrealdb as an in-memory database
08:08 - checking you can connect
08:51 - namespaces and databases
09:11 - surrealdb sql and selecting data
09:35 - tables, records and inserting data
11:33 - updating data with sql
11:57 - deleting data via the CLI with SQL
12:30 - selecting specific columns
12:51 - where clauses
14:12 - super fast querying via records
15:27 - native json support
18:45 - array support
19:43 - joining tables and relations
23:40 - conclusion

#surrealdb #database #sql #sqlite 

SurrealDB: The Future of Cloud Databases

SQLite Libraries Plugins Of Swift Are Popular

In this Swift article, let's learn about SQLite: SQLite Libraries Plugins Of Swift Are Popular

Table of contents:

  • GRDB.swift - A versatile SQLite toolkit.
  • SQLite.swift - Framework wrapping SQLite3. Small. Simple. Safe.
  • SQLiteDB - SQLite wrapper.

What is SQLite in Swift?

SQLite is a C-language library that implements a small, fast, self-contained, high-reliability, full-featured, SQL database engine. SQLite is the most used database engine in the world. It is built into all mobile phones and most computers and comes bundled inside countless other applications that people use every day.


SQLite libraries plugins of swift are popular

  1. GRDB.swift

A toolkit for SQLite databases, with a focus on application development

Requirements: iOS 11.0+ / macOS 10.13+ / tvOS 11.0+ / watchOS 4.0+ • SQLite 3.19.3+ • Swift 5.7+ / Xcode 14+

Swift versionGRDB version
Swift 5.7+v6.0.0
Swift 5.3v5.26.0
Swift 5.2v5.12.0
Swift 5.1v4.14.0
Swift 5v4.14.0
Swift 4.2v4.14.0
Swift 4.1v3.7.0
Swift 4v2.10.0
Swift 3.2v1.3.0
Swift 3.1v1.3.0
Swift 3v1.0
Swift 2.3v0.81.2
Swift 2.2v0.80.2

Contact:

What is this?

GRDB provides raw access to SQL and advanced SQLite features, because one sometimes enjoys a sharp tool. It has robust concurrency primitives, so that multi-threaded applications can efficiently use their databases. It grants your application models with persistence and fetching methods, so that you don't have to deal with SQL and raw database rows when you don't want to.

Compared to SQLite.swift or FMDB, GRDB can spare you a lot of glue code. Compared to Core Data or Realm, it can simplify your multi-threaded applications.

It comes with up-to-date documentation, general guides, and it is fast.

See Why Adopt GRDB? if you are looking for your favorite database library.

View on GitHub


2.  SQLite.swift

A type-safe, Swift-language layer over SQLite3.

SQLite.swift provides compile-time confidence in SQL statement syntax and intent.

Features

  • A pure-Swift interface
  • A type-safe, optional-aware SQL expression builder
  • A flexible, chainable, lazy-executing query layer
  • Automatically-typed data access
  • A lightweight, uncomplicated query and parameter binding interface
  • Developer-friendly error handling and debugging
  • Full-text search support
  • Well-documented
  • Extensively tested
  • SQLCipher support via CocoaPods
  • Works on Linux (with some limitations)
  • Active support at StackOverflow, and Gitter Chat Room (experimental)

Usage

import SQLite

// Wrap everything in a do...catch to handle errors
do {
    let db = try Connection("path/to/db.sqlite3")

    let users = Table("users")
    let id = Expression<Int64>("id")
    let name = Expression<String?>("name")
    let email = Expression<String>("email")

    try db.run(users.create { t in
        t.column(id, primaryKey: true)
        t.column(name)
        t.column(email, unique: true)
    })
    // CREATE TABLE "users" (
    //     "id" INTEGER PRIMARY KEY NOT NULL,
    //     "name" TEXT,
    //     "email" TEXT NOT NULL UNIQUE
    // )

    let insert = users.insert(name <- "Alice", email <- "alice@mac.com")
    let rowid = try db.run(insert)
    // INSERT INTO "users" ("name", "email") VALUES ('Alice', 'alice@mac.com')

    for user in try db.prepare(users) {
        print("id: \(user[id]), name: \(user[name]), email: \(user[email])")
        // id: 1, name: Optional("Alice"), email: alice@mac.com
    }
    // SELECT * FROM "users"

    let alice = users.filter(id == rowid)

    try db.run(alice.update(email <- email.replace("mac.com", with: "me.com")))
    // UPDATE "users" SET "email" = replace("email", 'mac.com', 'me.com')
    // WHERE ("id" = 1)

    try db.run(alice.delete())
    // DELETE FROM "users" WHERE ("id" = 1)

    try db.scalar(users.count) // 0
    // SELECT count(*) FROM "users"
} catch {
    print (error)
}

SQLite.swift also works as a lightweight, Swift-friendly wrapper over the C API.

// Wrap everything in a do...catch to handle errors
do {
    // ...
    
    let stmt = try db.prepare("INSERT INTO users (email) VALUES (?)")
    for email in ["betty@icloud.com", "cathy@icloud.com"] {
        try stmt.run(email)
    }

    db.totalChanges    // 3
    db.changes         // 1
    db.lastInsertRowid // 3

    for row in try db.prepare("SELECT id, email FROM users") {
        print("id: \(row[0]), email: \(row[1])")
        // id: Optional(2), email: Optional("betty@icloud.com")
        // id: Optional(3), email: Optional("cathy@icloud.com")
    }

    try db.scalar("SELECT count(*) FROM users") // 2
} catch {
    print (error)
}

Read the documentation or explore more, interactively, from the Xcode project’s playground.

View on GitHub


3.  SQLiteDB

SQLiteDB is a simple and lightweight SQLite wrapper for Swift. It allows all basic SQLite functionality including being able to bind values to parameters in an SQL statement. You can either include an SQLite database file with your project (in case you want to pre-load data) and have it be copied over automatically in to your documents folder, or have the necessary database and the relevant table structures created automatically for you via SQLiteDB.

SQLiteDB also provides an SQLTable class which allows you to use SQLiteDB as an ORM so that you can define your table structures via SQLTable sub-classes and be able to access the underlying data directly instead of having to deal with SQL queries, parameters, etc.

Update: (28 Mar 2018) The latest version of SQLiteDB changes the openDB() method to open() and changes the parameters for the method as well. Please be aware of this change when updating an existing project. The open method parameters have default values which should work for most general cases - so you probably will not need to modify existing code except to change the method name.

The row(number:filter:order:type:) method now takes 0-based row numbers instead of 1-based. This change was made to be in line with how the row number is used in all the use cases I've seen up to now.

Also do not try to use the cloud database functionality available with the latest code since that is not yet ready for prime time - that code is still a work in progress. However, the rest of SQLiteDB code will function as it should without any issues.

Usage

There are several ways you can use SQLiteDB in your project:

Basic - Direct

You can use the SQLiteBase class to open one or more SQLite databases directly by passing the path to the database file to the open method like this:

let db = SQLiteBase()
_ = db.open(dbPath: path)

You can then use the db instance to query the database. You can have multiple instances of SQLiteBase be in existence at the same time and point to different databases without any issues.

View on GitHub


SQLite Swift FAQ

  • Does iOS use SQLite?

Apple uses SQLite in many (most?) of the native applications running on Mac OS-X desktops and servers and on iOS devices such as iPhones and iPods. SQLite is also used in iTunes, even on non-Apple hardware.

  • Is SQLite still relevant?

SQLite works great as the database engine for most low to medium traffic websites (which is to say, most websites). The amount of web traffic that SQLite can handle depends on how heavily the website uses its database. Generally speaking, any site that gets fewer than 100K hits/day should work fine with SQLite.

  • Is SQLite a Core Data?

Core Data can use SQLite as its persistent store, but the framework itself is not a database. Core Data is not a database. Core Data is a framework for managing an object graph. An object graph is nothing more than a collection of interconnected objects.

  • Why Core Data is faster than SQLite?

Core Data is heavily optimized with regards to caching, lazy-loading and memory management. If you use it (with the SQLite store type), especially in conjunction with NSFetchedResultsController, you should get better performance than you could get with SQLite on your own.


Related videos:

SQLite - Swift & Swift UI


Related posts:

#sqlite #swift 

SQLite Libraries Plugins Of Swift Are Popular
Go Programming

Go Programming

1663121991

PocketBase: Open Source Go Backend in 1 File

PocketBase is a new backend platform using the Go programming language and SQLite database that compiles to a single executable. It is a lightweight alternative to tools like Firebase and Supabase for easy backend development.

PocketBase is an open source Go backend, consisting of:

  • embedded database (SQLite) with realtime subscriptions
  • built-in files and users management
  • convenient Admin dashboard UI
  • and simple REST-ish API

⚠️ Although the web API definitions are considered stable, please keep in mind that PocketBase is still under active development and therefore full backward compatibility is not guaranteed before reaching v1.0.0.

API SDK clients

The easiest way to interact with the API is to use one of the official SDK clients:

Overview

PocketBase could be downloaded directly as a standalone app or it could be used as a Go framework/toolkit which allows you to build your own custom app specific business logic and still have a single portable executable at the end.

Installation

# go 1.18+
go get github.com/pocketbase/pocketbase

For Windows, you may have to use go 1.19+ due to an incorrect js mime type in the Windows Registry (see issue#6).

Example

package main

import (
    "log"
    "net/http"

    "github.com/labstack/echo/v5"
    "github.com/pocketbase/pocketbase"
    "github.com/pocketbase/pocketbase/apis"
    "github.com/pocketbase/pocketbase/core"
)

func main() {
    app := pocketbase.New()

    app.OnBeforeServe().Add(func(e *core.ServeEvent) error {
        // add new "GET /api/hello" route to the app router (echo)
        e.Router.AddRoute(echo.Route{
            Method: http.MethodGet,
            Path:   "/api/hello",
            Handler: func(c echo.Context) error {
                return c.String(200, "Hello world!")
            },
            Middlewares: []echo.MiddlewareFunc{
                apis.RequireAdminOrUserAuth(),
            },
        })

        return nil
    })

    if err := app.Start(); err != nil {
        log.Fatal(err)
    }
}

Running and building

Running/building the application is the same as for any other Go program, aka. just go run and go build.

PocketBase embeds SQLite, but doesn't require CGO.

If CGO is enabled, it will use mattn/go-sqlite3 driver, otherwise - modernc.org/sqlite. You can enable and disable CGO by setting the CGO_ENABLED environment variable to 1 or 0 respectively.

Enable CGO only if you really need to squeeze the read/write query performance at the expense of complicating cross compilation.

To build the minimal standalone executable, like the prebuilt ones in the releases page, you can simply run go build inside the examples/base directory.

Testing

PocketBase comes with mixed bag of unit and integration tests. To run them, use the default go test command:

go test ./...

Check also the Testing guide to learn how to write your own custom application tests.

Security

If you discover a security vulnerability within PocketBase, please send an e-mail to support at pocketbase.io.

All reports will be promptly addressed and you'll be credited accordingly.

For documentation and examples, please visit https://pocketbase.io/docs.

Download Details: 
Author: pocketbase
Source Code: https://github.com/pocketbase/pocketbase 
License: MIT
#go #golang #sqlite 

PocketBase: Open Source Go Backend in 1 File
Jarvis  Maggio

Jarvis Maggio

1661926209

SQLiteDB: A Simple and Lightweight SQLite Wrapper for Swift

SQLiteDB

SQLiteDB is a simple and lightweight SQLite wrapper for Swift. It allows all basic SQLite functionality including being able to bind values to parameters in an SQL statement. You can either include an SQLite database file with your project (in case you want to pre-load data) and have it be copied over automatically in to your documents folder, or have the necessary database and the relevant table structures created automatically for you via SQLiteDB.

SQLiteDB also provides an SQLTable class which allows you to use SQLiteDB as an ORM so that you can define your table structures via SQLTable sub-classes and be able to access the underlying data directly instead of having to deal with SQL queries, parameters, etc.

Update: (28 Mar 2018) The latest version of SQLiteDB changes the openDB() method to open() and changes the parameters for the method as well. Please be aware of this change when updating an existing project. The open method parameters have default values which should work for most general cases - so you probably will not need to modify existing code except to change the method name.

The row(number:filter:order:type:) method now takes 0-based row numbers instead of 1-based. This change was made to be in line with how the row number is used in all the use cases I've seen up to now.

Also do not try to use the cloud database functionality available with the latest code since that is not yet ready for prime time - that code is still a work in progress. However, the rest of SQLiteDB code will function as it should without any issues.

Adding to Your Project

If you want to pre-load data or have the table structures and indexes pre-created, or, if you are not using SQLTable sub-classes but are instead using SQLiteDB directly, then you need to create an SQLite database file to be included in your project.

Create your SQLite database however you like, but name it data.db and then add the data.db file to your Xcode project. (If you want to name the database file something other than data.db, then set the DB_NAME property in the SQLiteDB class accordingly.)

Note: Remember to add the database file above to your application target when you add it to the project. If you don't add the database file to a project target, it will not be copied to the device along with the other project resources.

If you do not want to pre-load data and are using SQLTable sub-classes to access your tables, you can skip the above step since SQLiteDB will automatically create your table structures for you if the database file is not included in the project. However, in order for this to work, you need to pass false as the parameter for the open method when you invoke it, like this:

Add all of the included source files (except for README.md, of course) to your project.

If you don't have a bridging header file, use the included Bridging-Header.h file. If you already have a bridging header file, then copy the contents from the included Bridging-Header.h file to your own bridging header file.

If you didn't have a bridging header file, make sure that you modify your project settings to point to the new bridging header file. This will be under Build Settings for your target and will be named Objective-C Bridging Header.

Add the SQLite library (libsqlite3.dylib or libsqlite3.tbd, depending on your Xcode version) to your project under Build Phases - Link Binary With Libraries section.

That's it. You're set!

Usage

There are several ways you can use SQLiteDB in your project:

Basic - Direct

You can use the SQLiteBase class to open one or more SQLite databases directly by passing the path to the database file to the open method like this:

let db = SQLiteBase()
_ = db.open(dbPath: path)

You can then use the db instance to query the database. You can have multiple instances of SQLiteBase be in existence at the same time and point to different databases without any issues.

Basic - Singleton

You can use the SQLiteDB class, which is a singleton, to get a reference to one central database. Similar to the `SQLiteBase, instance above, you can then run queries (or execute statements) on the database using this reference.

Unlike with a SQLiteBase class instance, you cannot open multiple databases with SQLiteDB - it will only work with the database file specified via the DB_NAME property for the class.

  • You can gain access to the shared database instance as follows:
let db = SQLiteDB.shared
  • Before you make any SQL queries, or execute commands, you should open the SQLite database. In most cases, this needs to be done only once per application and so you can do it in your AppDelegate, for example:
db.open()
  • You can make SQL queries using the query method (the results are returned as an array of dictionaries where the key is a String and the value is of type Any):
let data = db.query(sql:"SELECT * FROM customers WHERE name='John'")
let row = data[0]
if let name = row["name"] {
    textLabel.text = name as! String
}

In the above, db is a reference to the shared SQLite database instance. You can access a column from your query results by subscripting a row of the returned results (the rows are dictionaries) based on the column name. That returns an optional Any value which you can cast to the relevant data type.

  • If you'd prefer to bind values to your query instead of creating the full SQL statement, then you can execute the above SQL also like this:
let name = "John"
let data = db.query(sql:"SELECT * FROM customers WHERE name=?", parameters:[name])
  • Of course, you can also construct the above SQL query by using Swift's string interpolation functionality as well (without using the SQLite bind functionality):
let name = "John"
let data = db.query(sql:"SELECT * FROM customers WHERE name='\(name)'")
  • You can execute all non-query SQL commands (INSERT, DELETE, UPDATE etc.) using the execute method:
let result = db.execute(sql:"DELETE FROM customers WHERE last_name='Smith'")
// If the result is 0 then the operation failed, for inserts the result gives the newly inserted record ID

Note: If you need to escape strings with embedded quotes, or other special strings which might not work with Swift string interpolation, you should use the SQLite parameter binding functionality as shown above.

Using SQLTable

If you would prefer to model your database tables as classes and do any data access via class instances instead of using SQL statements, SQLiteDB also provides an SQLTable class which does most of the heavy lifting for you.

If you create a sub-class of SQLTable, define properties where the names match the column names in your SQLite table, then you can use the sub-class to save to/update the database without having to write all the necessary boilerplate code yourself.

Additionally, with this approach, you don't need to include an SQLite database project with your app (unless you need/want to). Each SQLTable instance in your app will infer the structure for the underlying tables based on your SQLTable sub-classes and automatically create the necessary tables for you, if they aren't present.

In fact, while you develop your app, if you add new properties to your SQLTable sub-class instance, the necessary underlying SQLite columns will be added automatically to the database the next time the code is run. Again, SQLiteDB does all the work for you.

For example, say that you have a Categories table with just two columns - id and name. Then, the SQLTable sub-class definition for the table would look something like this:

class Category:SQLTable {
    var id = -1
    var name = ""
}

It's as simple as that! You don't have to write any insert, update, or delete methods since SQLTable handles all of that for you behind the scenese :) And on top of that, if you were to later add another property to the Category class later, say some sort of a usage count called count, that column would be added to the underlying table when you next run your code.

Note: Do note that for a table named Categories, the class has to be named Category - the table name has to be plural, and the class name has to be singular. The table names are plural while the classes are singular. Again, if you let SQLTable create the table structure for you, then it would all be handled correctly for you automatically. But if you create the tables yourself, do make sure that the table names are correct.

The only additional thing you need to do when you use SQLTable sub-classes and want the table structures to be automatically created for you is that you have to specify that you don't want to create a copy of a database in your project resources when you invoke open. So you have to have your open call be something like this:

db.open(copyFile:false)

Once you do that, you can run any SQL queries or execute commands on the database without any issues.

Here are some quick examples of how you use the Category class from the above example:

  • Add a new Category item to the table:
let category = Category()
category.name = "My New Category"
_ = category.save()

The save method returns a non-zero value if the save was successful. In the case of a new record, the return value is the id of the newly inserted row. You can check the return value to see if the save was sucessful or not since a 0 value means that the save failed for some reason.

  • Get a Category by id:
if let category = Category.rowBy(id: 10) {
    NSLog("Found category with ID = 10")
}
  • Query the Category table:
let array = Category.rows(filter: "id > 10")
  • Get a specific Category row (to display categories via a UITableView, for example) by row number. The row numbers start at 0, the same as UITableView row indexes:
if let category = row(number: 0) {
    NSLog("Got first un-ordered category row")
}
  • Delete a Category:
if let category = Category.rowBy(id: 10) {
    category.delete()
    NSLog("Deleted category with ID = 10")
}

You can refer to the sample iOS and macOS projects for more examples of how to implement data access using SQLTable.

Questions?

SQLiteDB is under DWYWPL - Do What You Will Public License :) Do whatever you want either personally or commercially with the code but if you'd like, feel free to attribute in your app.


Download Details:

Author: FahimF
Source code: https://github.com/FahimF/SQLiteDB

#swift #sqlite 

SQLiteDB: A Simple and Lightweight SQLite Wrapper for Swift
Jarvis  Maggio

Jarvis Maggio

1661918820

SQLite.swift: A Type-safe, Swift-language Layer Over SQLite3

SQLite.swift

A type-safe, Swift-language layer over SQLite3.

SQLite.swift provides compile-time confidence in SQL statement syntax and intent.

Features

  • A pure-Swift interface
  • A type-safe, optional-aware SQL expression builder
  • A flexible, chainable, lazy-executing query layer
  • Automatically-typed data access
  • A lightweight, uncomplicated query and parameter binding interface
  • Developer-friendly error handling and debugging
  • Full-text search support
  • Well-documented
  • Extensively tested
  • SQLCipher support via CocoaPods
  • Works on Linux (with some limitations)
  • Active support at StackOverflow, and Gitter Chat Room (experimental)

Usage

import SQLite

// Wrap everything in a do...catch to handle errors
do {
    let db = try Connection("path/to/db.sqlite3")

    let users = Table("users")
    let id = Expression<Int64>("id")
    let name = Expression<String?>("name")
    let email = Expression<String>("email")

    try db.run(users.create { t in
        t.column(id, primaryKey: true)
        t.column(name)
        t.column(email, unique: true)
    })
    // CREATE TABLE "users" (
    //     "id" INTEGER PRIMARY KEY NOT NULL,
    //     "name" TEXT,
    //     "email" TEXT NOT NULL UNIQUE
    // )

    let insert = users.insert(name <- "Alice", email <- "alice@mac.com")
    let rowid = try db.run(insert)
    // INSERT INTO "users" ("name", "email") VALUES ('Alice', 'alice@mac.com')

    for user in try db.prepare(users) {
        print("id: \(user[id]), name: \(user[name]), email: \(user[email])")
        // id: 1, name: Optional("Alice"), email: alice@mac.com
    }
    // SELECT * FROM "users"

    let alice = users.filter(id == rowid)

    try db.run(alice.update(email <- email.replace("mac.com", with: "me.com")))
    // UPDATE "users" SET "email" = replace("email", 'mac.com', 'me.com')
    // WHERE ("id" = 1)

    try db.run(alice.delete())
    // DELETE FROM "users" WHERE ("id" = 1)

    try db.scalar(users.count) // 0
    // SELECT count(*) FROM "users"
} catch {
    print (error)
}

SQLite.swift also works as a lightweight, Swift-friendly wrapper over the C API.

// Wrap everything in a do...catch to handle errors
do {
    // ...
    
    let stmt = try db.prepare("INSERT INTO users (email) VALUES (?)")
    for email in ["betty@icloud.com", "cathy@icloud.com"] {
        try stmt.run(email)
    }

    db.totalChanges    // 3
    db.changes         // 1
    db.lastInsertRowid // 3

    for row in try db.prepare("SELECT id, email FROM users") {
        print("id: \(row[0]), email: \(row[1])")
        // id: Optional(2), email: Optional("betty@icloud.com")
        // id: Optional(3), email: Optional("cathy@icloud.com")
    }

    try db.scalar("SELECT count(*) FROM users") // 2
} catch {
    print (error)
}

Read the documentation or explore more, interactively, from the Xcode project’s playground.

SQLite.playground Screen Shot

For a more comprehensive example, see this article and the companion repository.

Installation

Note: Version 0.11.6 and later requires Swift 5 (and Xcode 10.2) or greater. Version 0.11.5 requires Swift 4.2 (and Xcode 10.1) or greater.

Swift Package Manager

The Swift Package Manager is a tool for managing the distribution of Swift code.

  1. Add the following to your Package.swift file:
dependencies: [
    .package(url: "https://github.com/stephencelis/SQLite.swift.git", from: "0.13.3")
]
  1. Build your project:
$ swift build

See the Tests/SPM folder for a small demo project which uses SPM.

Carthage

Carthage is a simple, decentralized dependency manager for Cocoa. To install SQLite.swift with Carthage:

Make sure Carthage is installed.

Update your Cartfile to include the following:

github "stephencelis/SQLite.swift" ~> 0.13.3

Run carthage update and add the appropriate framework.

CocoaPods

CocoaPods is a dependency manager for Cocoa projects. To install SQLite.swift with CocoaPods:

Make sure CocoaPods is installed.

# Using the default Ruby install will require you to use sudo when
# installing and updating gems.
[sudo] gem install cocoapods

Update your Podfile to include the following:

use_frameworks!

target 'YourAppTargetName' do
    pod 'SQLite.swift', '~> 0.13.3'
end

Run pod install --repo-update.

Manual

To install SQLite.swift as an Xcode sub-project:

Drag the SQLite.xcodeproj file into your own project. (Submodule, clone, or download the project first.)

Installation Screen Shot

In your target’s General tab, click the + button under Linked Frameworks and Libraries.

Select the appropriate SQLite.framework for your platform.

Add.

Some additional steps are required to install the application on an actual device:

In the General tab, click the + button under Embedded Binaries.

Select the appropriate SQLite.framework for your platform.

Add.

Communication

See the planning document for a roadmap and existing feature requests.

Read the contributing guidelines. The TL;DR (but please; R):

Author

License

SQLite.swift is available under the MIT license. See the LICENSE file for more information.

Related

These projects enhance or use SQLite.swift:

Alternatives

Looking for something else? Try another Swift wrapper (or FMDB):


Download Details:

Author: stephencelis
Source code: https://github.com/stephencelis/SQLite.swift

License: MIT license
#swift #sqlite 

SQLite.swift: A Type-safe, Swift-language Layer Over SQLite3

Sqlitestore: Gorilla's Session Store Implementation for SQLite

sqlitestore

Gorilla's Session Store Implementation for SQLite

Installation

Run from command line:

go get github.com/michaeljs1990/sqlitestore

It gets installed in $GOPATH

Usage

NewSqliteStore takes the following paramaters

  • endpoint - A sql.Open style endpoint
  • tableName - table where sessions are to be saved. Required fields are created automatically if the table does not exist.
  • path - path for Set-Cookie header
  • maxAge
  • codecs

Internally, sqlitestore uses this SQLite driver.

e.g.,

  package main

  import (
    "fmt"
    "github.com/michaeljs1990/sqlitestore"
    "net/http"
  )

  var store *sqlitestore.SqliteStore
  func init() {
     var err error
     store, err = sqlitestore.NewSqliteStore("./database", "sessions", "/", 3600, []byte("<SecretKey>"))
     if err != nil {
         panic(err)
     } 
  }

  func sessTest(w http.ResponseWriter, r *http.Request) {
    session, err := store.Get(r, "foobar")
    session.Values["bar"] = "baz"
    session.Values["baz"] = "foo"
    err = session.Save(r, w)
    fmt.Printf("%#v\n", session)
    fmt.Println(err)
  }

func main() {
    http.HandleFunc("/", sessTest)
    http.ListenAndServe(":8080", nil)
}

Download Details:

Author: Michaeljs1990
Source Code: https://github.com/michaeljs1990/sqlitestore 
License: MIT license

#go #golang #sqlite 

Sqlitestore: Gorilla's Session Store Implementation for SQLite
Louis Jones

Louis Jones

1660965436

A Beginner's Guide to RDBMS (Relational Database Management System)

What is a Relational Database Management System (RDBMS)? Learn about RDBMS and the language used to access large datasets – SQL.

RDBMS is an acronym for Relational Database Management System and is a type of database management system that stores data in a structured format using rows and columns, making it easy to locate and access data in relation to another piece of data in the database.

What is a Database?

A database is a set of data stored in a computer. This data is usually structured in a way that makes the data easily accessible.
 

What is a Relational Database?

A relational database is a type of database. It uses a structure that allows us to identify and access data in relation to another piece of data in the database. Often, data in a relational database is organized into tables.
 

Tables: Rows and Columns

Tables can have hundreds, thousands, sometimes even millions of rows of data. These rows are often called records.

Tables can also have many columns of data. Columns are labeled with a descriptive name (say, age for example) and have a specific data type.

For example, a column called age may have a type of INTEGER (denoting the type of data it is meant to hold).

Table

In the table above, there are three columns (name, age, and country).

The name and country columns store string data types, whereas age stores integer data types. The set of columns and data types make up the schema of this table.

The table also has four rows, or records, in it (one each for Natalia, Ned, Zenas, and Laura).
 

What is a Relational Database Management System (RDBMS)?

A relational database management system (RDBMS) is a program that allows you to create, update, and administer a relational database. Most relational database management systems use the SQL language to access the database.
 

What is SQL?

SQL (Structured Query Language) is a programming language used to communicate with data stored in a relational database management system. SQL syntax is similar to the English language, which makes it relatively easy to write, read, and interpret.

Many RDBMSs use SQL (and variations of SQL) to access the data in tables. For example, SQLite is a relational database management system. SQLite contains a minimal set of SQL commands (which are the same across all RDBMSs). Other RDBMSs may use other variants.

(SQL is often pronounced in one of two ways. You can pronounce it by speaking each letter individually like “S-Q-L”, or pronounce it using the word “sequel”.)
 

Popular Relational Database Management Systems

SQL syntax may differ slightly depending on which RDBMS you are using. Here is a brief description of popular RDBMSs:
 

MySQL

MySQL is the most popular open source SQL database. It is typically used for web application development, and often accessed using PHP.

The main advantages of MySQL are that it is easy to use, inexpensive, reliable (has been around since 1995), and has a large community of developers who can help answer questions.

Some of the disadvantages are that it has been known to suffer from poor performance when scaling, open source development has lagged since Oracle has taken control of MySQL, and it does not include some advanced features that developers may be used to.
 

PostgreSQL

PostgreSQL is an open source SQL database that is not controlled by any corporation. It is typically used for web application development.

PostgreSQL shares many of the same advantages of MySQL. It is easy to use, inexpensive, reliable and has a large community of developers. It also provides some additional features such as foreign key support without requiring complex configuration.

The main disadvantage of PostgreSQL is that it can be slower in performance than other databases such as MySQL. It is also slightly less popular than MySQL.

For more information about PostgreSQL including installation instructions, read this article.

Oracle DB

Oracle Corporation owns Oracle Database, and the code is not open sourced.

Oracle DB is for large applications, particularly in the banking industry. Most of the world’s top banks run Oracle applications because Oracle offers a powerful combination of technology and comprehensive, pre-integrated business applications, including essential functionality built specifically for banks.

The main disadvantage of using Oracle is that it is not free to use like its open source competitors and can be quite expensive.
 

SQL Server

Microsoft owns SQL Server. Like Oracle DB, the code is close sourced.

Large enterprise applications mostly use SQL Server.

Microsoft offers a free entry-level version called Express but can become very expensive as you scale your application.
 

SQLite

SQLite is a popular open source SQL database. It can store an entire database in a single file. One of the most significant advantages this provides is that all of the data can be stored locally without having to connect your database to a server.

SQLite is a popular choice for databases in cellphones, PDAs, MP3 players, set-top boxes, and other electronic gadgets. The SQL courses on Codecademy use SQLite.

Conclusion

Relational databases store data in tables. Tables can grow large and have a multitude of columns and records. Relational database management systems (RDBMSs) use SQL (and variants of SQL) to manage the data in these large tables. The RDBMS you use is your choice and depends on the complexity of your application.

Original article source at https://www.codecademy.com

#database #sql #sqlite #mysql #postgresql #oracle #sqlserver

A Beginner's Guide to RDBMS (Relational Database Management System)
Adam Daniels

Adam Daniels

1660964964

A Beginner's Guide to SQLite

What is SQLite? Learn about the SQLite database engine and how to install it on your computer (Windows,  Mac OS X and Linux)

In this article, we will be exploring the extremely prevalent database engine called SQLite. We will describe what it does, its main uses, and then explain how to set it up and use it on your own computer.

What is SQLite?

SQLite is a database engine. It is software that allows users to interact with a relational database. In SQLite, a database is stored in a single file — a trait that distinguishes it from other database engines. This fact allows for a great deal of accessibility: copying a database is no more complicated than copying the file that stores the data, sharing a database can mean sending an email attachment.

Drawbacks to SQLite

SQLite’s signature portability unfortunately makes it a poor choice when many different users are updating the table at the same time (to maintain integrity of data, only one user can write to the file at a time). It also may require some more work to ensure the security of private data due to the same features that make SQLite accessible. Furthermore, SQLite does not offer the same exact functionality as many other database systems, limiting some advanced features other relational database systems offer. Lastly, SQLite does not validate data types. Where many other database software would reject data that does not conform to a table’s schema, SQLite allows users to store data of any type into any column.

SQLite creates schemas, which constrain the type of data in each column, but it does not enforce them. The example below shows that the id column expects to store integers, the name column expects to store text, and the age column expects to store integers:

CREATE TABLE celebs (
   id INTEGER, 
   name TEXT, 
   age INTEGER
);

However, SQLite will not reject values of the wrong type. We could accidentally insert the wrong data types in the columns. Storing different data types in the same column is a bad habit that can lead to errors that are difficult to fix, so it’s important to be strict about your schema even though SQLite will not enforce it.

Uses for SQLite

Even considering the drawbacks, the benefits of being able to access and manipulate a database without involving a server application are huge. SQLite is used worldwide for testing, development, and in any other scenario where it makes sense for the database to be on the same disk as the application code. SQLite’s maintainers consider it to be among the most replicated pieces of software in the world.

Setting Up SQLite

Binaries for SQLite can be installed at the SQLite Download page. This page has versions of SQLite for Windows, Mac OS X, and Linux. The last number in each file name is the current SQLite version. Our instructions use the version number 3200100. You should download whichever version is listed on the SQLite Download page, and replace 3200100 in the instructions below with the version number you downloaded. For example, if the name of the file you download is sqlite-tools-win32-x86-3390200.zip, you would change 3200100 in each command below to 3390200.

Windows

For Windows machines:

  1. Download the sqlite-tools-win32-x86-3200100.zip file and unzip it.
  2. From your git-bash terminal, open the directory of the unzipped folder with cd ~/Downloads/sqlite-tools-win32-x86-3200100/sqlite-tools-win32-x86-3200100/.
  3. Try running sqlite with the command winpty ./sqlite3.exe. If that command opens a sqlite> prompt, congratulations! You’ve installed SQLite.

We want to be able to access this command quickly from elsewhere, so we’re going to create an alias to the command. Exit the sqlite> prompt by typing in Ctrl + C, and in the same git-bash terminal without changing folders, run these commands:

echo "alias sqlite3=\"winpty ${PWD}/sqlite3.exe\"" >> ~/.bashrc

and

source ~/.bashrc

The first command will create the alias sqlite3 that you can use to open a database. The second command will refresh your terminal so that you can start using this command. Try typing in the command sqlite3 newdb.sqlite. If you’re presented with a sqlite> prompt, you’ve successfully created the sqlite3 command for your terminal. Enter Ctrl + C to quit. You can also exit by typing .exit in the prompt and pressing Enter.

Mac OS X

For Macs, use the Mac OS X (x86) sqlite-tools package:

  1. Install it, and unzip it.
  2. In your terminal, navigate to the directory of the unzipped folder using cd.
  3. Run the command mv sqlite3 /usr/local/bin/. This will add the command sqlite3 to your terminal path, allowing you to use the command from anywhere.
  4. Try typing sqlite3 newdb.sqlite. If you’re presented with a sqlite> prompt, you’ve installed SQLite! Enter control + d to quit. You can also exit by typing .exit in the prompt and pressing return.

Linux

In Ubuntu or similar distributions:

  1. Open your terminal and run sudo apt-get install sqlite3. Otherwise, use your distribution’s package managers.
  2. Try typing in the command sqlite3 newdb.sqlite. If you’re presented with a sqlite> prompt, you’ve successfully created the sqlite3 command for your terminal. You can exit by typing .exit in the prompt and pressing enter.

Conclusion

You’ve installed database software and opened a connection to a database. Now you have the full power of SQL at your fingertips. You’ll be able to manage all the data for any application you can dream of writing. 

Original article source at https://www.codecademy.com

#sql #sqlite #database 

A Beginner's Guide to SQLite

SQLite.jl: A Julia interface to the SQLite library

SQLite

A Julia interface to the sqlite library.

Installation

The package is registered in the General registry and so can be installed with ] add SQLite.

Documentation

  • STABLEmost recently tagged version of the documentation.
  • LATESTin-development version of the documentation.

Project Status

The package is tested against Julia 1.0 and nightly on Linux, OS X, and Windows.

Contributing and Questions

Contributions are very welcome, as are feature requests and suggestions. Please open an issue if you encounter any problems or would just like to ask a question.

Download Details:

Author: JuliaDatabases
Source Code: https://github.com/JuliaDatabases/SQLite.jl 
License: View license

#julia #sqlite 

SQLite.jl: A Julia interface to the SQLite library

Go-sqlite3: Sqlite3 Driver for Go using Database/sql

go-sqlite3    

Latest stable version is v1.14 or later, not v2.

NOTE: The increase to v2 was an accident. There were no major changes or features.

Description

A sqlite3 driver that conforms to the built-in database/sql interface.

Supported Golang version: See .github/workflows/go.yaml.

This package follows the official Golang Release Policy.

Installation

This package can be installed with the go get command:

go get github.com/mattn/go-sqlite3

go-sqlite3 is cgo package. If you want to build your app using go-sqlite3, you need gcc. However, after you have built and installed go-sqlite3 with go install github.com/mattn/go-sqlite3 (which requires gcc), you can build your app without relying on gcc in future.

Important: because this is a CGO enabled package, you are required to set the environment variable CGO_ENABLED=1 and have a gcc compile present within your path.

API Reference

API documentation can be found here.

Examples can be found under the examples directory.

Connection String

When creating a new SQLite database or connection to an existing one, with the file name additional options can be given. This is also known as a DSN (Data Source Name) string.

Options are append after the filename of the SQLite database. The database filename and options are separated by an ? (Question Mark). Options should be URL-encoded (see url.QueryEscape).

This also applies when using an in-memory database instead of a file.

Options can be given using the following format: KEYWORD=VALUE and multiple options can be combined with the & ampersand.

This library supports DSN options of SQLite itself and provides additional options.

Boolean values can be one of:

  • 0 no false off
  • 1 yes true on
NameKeyValue(s)Description
UA - Create_auth-Create User Authentication, for more information see User Authentication
UA - Username_auth_userstringUsername for User Authentication, for more information see User Authentication
UA - Password_auth_passstringPassword for User Authentication, for more information see User Authentication
UA - Crypt_auth_crypt
  • SHA1
  • SSHA1
  • SHA256
  • SSHA256
  • SHA384
  • SSHA384
  • SHA512
  • SSHA512
Password encoder to use for User Authentication, for more information see User Authentication
UA - Salt_auth_saltstringSalt to use if the configure password encoder requires a salt, for User Authentication, for more information see User Authentication
Auto Vacuum_auto_vacuum | _vacuum
  • 0 | none
  • 1 | full
  • 2 | incremental
For more information see PRAGMA auto_vacuum
Busy Timeout_busy_timeout | _timeoutintSpecify value for sqlite3_busy_timeout. For more information see PRAGMA busy_timeout
Case Sensitive LIKE_case_sensitive_like | _cslikebooleanFor more information see PRAGMA case_sensitive_like
Defer Foreign Keys_defer_foreign_keys | _defer_fkbooleanFor more information see PRAGMA defer_foreign_keys
Foreign Keys_foreign_keys | _fkbooleanFor more information see PRAGMA foreign_keys
Ignore CHECK Constraints_ignore_check_constraintsbooleanFor more information see PRAGMA ignore_check_constraints
ImmutableimmutablebooleanFor more information see Immutable
Journal Mode_journal_mode | _journal
  • DELETE
  • TRUNCATE
  • PERSIST
  • MEMORY
  • WAL
  • OFF
For more information see PRAGMA journal_mode
Locking Mode_locking_mode | _locking
  • NORMAL
  • EXCLUSIVE
For more information see PRAGMA locking_mode
Modemode
  • ro
  • rw
  • rwc
  • memory
Access Mode of the database. For more information see SQLite Open
Mutex Locking_mutex
  • no
  • full
Specify mutex mode.
Query Only_query_onlybooleanFor more information see PRAGMA query_only
Recursive Triggers_recursive_triggers | _rtbooleanFor more information see PRAGMA recursive_triggers
Secure Delete_secure_deleteboolean | FASTFor more information see PRAGMA secure_delete
Shared-Cache Modecache
  • shared
  • private
Set cache mode for more information see sqlite.org
Synchronous_synchronous | _sync
  • 0 | OFF
  • 1 | NORMAL
  • 2 | FULL
  • 3 | EXTRA
For more information see PRAGMA synchronous
Time Zone Location_locautoSpecify location of time format.
Transaction Lock_txlock
  • immediate
  • deferred
  • exclusive
Specify locking behavior for transactions.
Writable Schema_writable_schemaBooleanWhen this pragma is on, the SQLITE_MASTER tables in which database can be changed using ordinary UPDATE, INSERT, and DELETE statements. Warning: misuse of this pragma can easily result in a corrupt database file.
Cache Size_cache_sizeintMaximum cache size; default is 2000K (2M). See PRAGMA cache_size

DSN Examples

file:test.db?cache=shared&mode=memory

Features

This package allows additional configuration of features available within SQLite3 to be enabled or disabled by golang build constraints also known as build tags.

Click here for more information about build tags / constraints.

Usage

If you wish to build this library with additional extensions / features, use the following command:

go build --tags "<FEATURE>"

For available features, see the extension list. When using multiple build tags, all the different tags should be space delimited.

Example:

go build --tags "icu json1 fts5 secure_delete"

Feature / Extension List

ExtensionBuild TagDescription
Additional Statisticssqlite_stat4This option adds additional logic to the ANALYZE command and to the query planner that can help SQLite to chose a better query plan under certain situations. The ANALYZE command is enhanced to collect histogram data from all columns of every index and store that data in the sqlite_stat4 table.

The query planner will then use the histogram data to help it make better index choices. The downside of this compile-time option is that it violates the query planner stability guarantee making it more difficult to ensure consistent performance in mass-produced applications.

SQLITE_ENABLE_STAT4 is an enhancement of SQLITE_ENABLE_STAT3. STAT3 only recorded histogram data for the left-most column of each index whereas the STAT4 enhancement records histogram data from all columns of each index.

The SQLITE_ENABLE_STAT3 compile-time option is a no-op and is ignored if the SQLITE_ENABLE_STAT4 compile-time option is used
Allow URI Authoritysqlite_allow_uri_authorityURI filenames normally throws an error if the authority section is not either empty or "localhost".

However, if SQLite is compiled with the SQLITE_ALLOW_URI_AUTHORITY compile-time option, then the URI is converted into a Uniform Naming Convention (UNC) filename and passed down to the underlying operating system that way
App Armorsqlite_app_armorWhen defined, this C-preprocessor macro activates extra code that attempts to detect misuse of the SQLite API, such as passing in NULL pointers to required parameters or using objects after they have been destroyed.

App Armor is not available under Windows.
Disable Load Extensionssqlite_omit_load_extensionLoading of external extensions is enabled by default.

To disable extension loading add the build tag sqlite_omit_load_extension.
Foreign Keyssqlite_foreign_keysThis macro determines whether enforcement of foreign key constraints is enabled or disabled by default for new database connections.

Each database connection can always turn enforcement of foreign key constraints on and off and run-time using the foreign_keys pragma.

Enforcement of foreign key constraints is normally off by default, but if this compile-time parameter is set to 1, enforcement of foreign key constraints will be on by default
Full Auto Vacuumsqlite_vacuum_fullSet the default auto vacuum to full
Incremental Auto Vacuumsqlite_vacuum_incrSet the default auto vacuum to incremental
Full Text Search Enginesqlite_fts5When this option is defined in the amalgamation, versions 5 of the full-text search engine (fts5) is added to the build automatically
International Components for Unicodesqlite_icuThis option causes the International Components for Unicode or "ICU" extension to SQLite to be added to the build
Introspect PRAGMASsqlite_introspect

This option adds some extra PRAGMA statements.

  • PRAGMA function_list
  • PRAGMA module_list
  • PRAGMA pragma_list
JSON SQL Functionssqlite_jsonWhen this option is defined in the amalgamation, the JSON SQL functions are added to the build automatically
Pre Update Hooksqlite_preupdate_hookRegisters a callback function that is invoked prior to each INSERT, UPDATE, and DELETE operation on a database table.
Secure Deletesqlite_secure_deleteThis compile-time option changes the default setting of the secure_delete pragma.

When this option is not used, secure_delete defaults to off. When this option is present, secure_delete defaults to on.

The secure_delete setting causes deleted content to be overwritten with zeros. There is a small performance penalty since additional I/O must occur.

On the other hand, secure_delete can prevent fragments of sensitive information from lingering in unused parts of the database file after it has been deleted. See the documentation on the secure_delete pragma for additional information
Secure Delete (FAST)sqlite_secure_delete_fastFor more information see PRAGMA secure_delete
Tracing / Debugsqlite_traceActivate trace functions
User Authenticationsqlite_userauthSQLite User Authentication see User Authentication for more information.

Compilation

This package requires the CGO_ENABLED=1 ennvironment variable if not set by default, and the presence of the gcc compiler.

If you need to add additional CFLAGS or LDFLAGS to the build command, and do not want to modify this package, then this can be achieved by using the CGO_CFLAGS and CGO_LDFLAGS environment variables.

Android

This package can be compiled for android. Compile with:

go build --tags "android"

For more information see #201

ARM

To compile for ARM use the following environment:

env CC=arm-linux-gnueabihf-gcc CXX=arm-linux-gnueabihf-g++ \
    CGO_ENABLED=1 GOOS=linux GOARCH=arm GOARM=7 \
    go build -v 

Additional information:

Cross Compile

This library can be cross-compiled.

In some cases you are required to the CC environment variable with the cross compiler.

Cross Compiling from MAC OSX

The simplest way to cross compile from OSX is to use xgo.

Steps:

  • Install xgo (go get github.com/karalabe/xgo).
  • Ensure that your project is within your GOPATH.
  • Run xgo local/path/to/project.

Please refer to the project's README for further information.

Google Cloud Platform

Building on GCP is not possible because Google Cloud Platform does not allow gcc to be executed.

Please work only with compiled final binaries.

Linux

To compile this package on Linux, you must install the development tools for your linux distribution.

To compile under linux use the build tag linux.

go build --tags "linux"

If you wish to link directly to libsqlite3 then you can use the libsqlite3 build tag.

go build --tags "libsqlite3 linux"

Alpine

When building in an alpine container run the following command before building:

apk add --update gcc musl-dev

Fedora

sudo yum groupinstall "Development Tools" "Development Libraries"

Ubuntu

sudo apt-get install build-essential

Mac OSX

OSX should have all the tools present to compile this package. If not, install XCode to add all the developers tools.

Required dependency:

brew install sqlite3

For OSX, there is an additional package to install which is required if you wish to build the icu extension.

This additional package can be installed with homebrew:

brew upgrade icu4c

To compile for Mac OSX:

go build --tags "darwin"

If you wish to link directly to libsqlite3, use the libsqlite3 build tag:

go build --tags "libsqlite3 darwin"

Additional information:

Windows

To compile this package on Windows, you must have the gcc compiler installed.

  1. Install a Windows gcc toolchain.
  2. Add the bin folder to the Windows path, if the installer did not do this by default.
  3. Open a terminal for the TDM-GCC toolchain, which can be found in the Windows Start menu.
  4. Navigate to your project folder and run the go build ... command for this package.

For example the TDM-GCC Toolchain can be found here.

Errors

Compile error: can not be used when making a shared object; recompile with -fPIC

When receiving a compile time error referencing recompile with -FPIC then you are probably using a hardend system.

You can compile the library on a hardend system with the following command.

go build -ldflags '-extldflags=-fno-PIC'

More details see 120

Can't build go-sqlite3 on windows 64bit.

Probably, you are using go 1.0, go1.0 has a problem when it comes to compiling/linking on windows 64bit. See: #27

go get github.com/mattn/go-sqlite3 throws compilation error.

gcc throws: internal compiler error

Remove the download repository from your disk and try re-install with:

go install github.com/mattn/go-sqlite3

User Authentication

This package supports the SQLite User Authentication module.

Compile

To use the User authentication module, the package has to be compiled with the tag sqlite_userauth. See Features.

Usage

Create protected database

To create a database protected by user authentication, provide the following argument to the connection string _auth. This will enable user authentication within the database. This option however requires two additional arguments:

  • _auth_user
  • _auth_pass

When _auth is present in the connection string user authentication will be enabled and the provided user will be created as an admin user. After initial creation, the parameter _auth has no effect anymore and can be omitted from the connection string.

Example connection strings:

Create an user authentication database with user admin and password admin:

file:test.s3db?_auth&_auth_user=admin&_auth_pass=admin

Create an user authentication database with user admin and password admin and use SHA1 for the password encoding:

file:test.s3db?_auth&_auth_user=admin&_auth_pass=admin&_auth_crypt=sha1

Password Encoding

The passwords within the user authentication module of SQLite are encoded with the SQLite function sqlite_cryp. This function uses a ceasar-cypher which is quite insecure. This library provides several additional password encoders which can be configured through the connection string.

The password cypher can be configured with the key _auth_crypt. And if the configured password encoder also requires an salt this can be configured with _auth_salt.

Available Encoders

  • SHA1
  • SSHA1 (Salted SHA1)
  • SHA256
  • SSHA256 (salted SHA256)
  • SHA384
  • SSHA384 (salted SHA384)
  • SHA512
  • SSHA512 (salted SHA512)

Restrictions

Operations on the database regarding user management can only be preformed by an administrator user.

Support

The user authentication supports two kinds of users:

  • administrators
  • regular users

User Management

User management can be done by directly using the *SQLiteConn or by SQL.

SQL

The following sql functions are available for user management:

FunctionArgumentsDescription
authenticateusername string, password stringWill authenticate an user, this is done by the connection; and should not be used manually.
auth_user_addusername string, password string, admin intThis function will add an user to the database.
if the database is not protected by user authentication it will enable it. Argument admin is an integer identifying if the added user should be an administrator. Only Administrators can add administrators.
auth_user_changeusername string, password string, admin intFunction to modify an user. Users can change their own password, but only an administrator can change the administrator flag.
authUserDeleteusername stringDelete an user from the database. Can only be used by an administrator. The current logged in administrator cannot be deleted. This is to make sure their is always an administrator remaining.

These functions will return an integer:

  • 0 (SQLITE_OK)
  • 23 (SQLITE_AUTH) Failed to perform due to authentication or insufficient privileges

Examples

// Autheticate user
// Create Admin User
SELECT auth_user_add('admin2', 'admin2', 1);

// Change password for user
SELECT auth_user_change('user', 'userpassword', 0);

// Delete user
SELECT user_delete('user');

*SQLiteConn

The following functions are available for User authentication from the *SQLiteConn:

FunctionDescription
Authenticate(username, password string) errorAuthenticate user
AuthUserAdd(username, password string, admin bool) errorAdd user
AuthUserChange(username, password string, admin bool) errorModify user
AuthUserDelete(username string) errorDelete user

Attached database

When using attached databases, SQLite will use the authentication from the main database for the attached database(s).

Extensions

If you want your own extension to be listed here, or you want to add a reference to an extension; please submit an Issue for this.

Spatialite

Spatialite is available as an extension to SQLite, and can be used in combination with this repository. For an example, see shaxbee/go-spatialite.

extension-functions.c from SQLite3 Contrib

extension-functions.c is available as an extension to SQLite, and provides the following functions:

  • Math: acos, asin, atan, atn2, atan2, acosh, asinh, atanh, difference, degrees, radians, cos, sin, tan, cot, cosh, sinh, tanh, coth, exp, log, log10, power, sign, sqrt, square, ceil, floor, pi.
  • String: replicate, charindex, leftstr, rightstr, ltrim, rtrim, trim, replace, reverse, proper, padl, padr, padc, strfilter.
  • Aggregate: stdev, variance, mode, median, lower_quartile, upper_quartile

For an example, see dinedal/go-sqlite3-extension-functions.

FAQ

Getting insert error while query is opened.

You can pass some arguments into the connection string, for example, a URI. See: #39

Do you want to cross compile? mingw on Linux or Mac?

See: #106 See also: http://www.limitlessfx.com/cross-compile-golang-app-for-windows-from-linux.html

Want to get time.Time with current locale

Use _loc=auto in SQLite3 filename schema like file:foo.db?_loc=auto.

Can I use this in multiple routines concurrently?

Yes for readonly. But not for writable. See 50, 51, 209, 274.

Why I'm getting no such table error?

Why is it racy if I use a sql.Open("sqlite3", ":memory:") database?

Each connection to ":memory:" opens a brand new in-memory sql database, so if the stdlib's sql engine happens to open another connection and you've only specified ":memory:", that connection will see a brand new database. A workaround is to use "file::memory:?cache=shared" (or "file:foobar?mode=memory&cache=shared"). Every connection to this string will point to the same in-memory database.

Note that if the last database connection in the pool closes, the in-memory database is deleted. Make sure the max idle connection limit is > 0, and the connection lifetime is infinite.

For more information see:

Reading from database with large amount of goroutines fails on OSX.

OS X limits OS-wide to not have more than 1000 files open simultaneously by default.

For more information, see 289

Trying to execute a . (dot) command throws an error.

Error: Error: near ".": syntax error Dot command are part of SQLite3 CLI, not of this library.

You need to implement the feature or call the sqlite3 cli.

More information see 305.

Error: database is locked

When you get a database is locked, please use the following options.

Add to DSN: cache=shared

Example:

db, err := sql.Open("sqlite3", "file:locked.sqlite?cache=shared")

Next, please set the database connections of the SQL package to 1:

db.SetMaxOpenConns(1)

For more information, see 209.

Download Details:

Author: Mattn
Source Code: https://github.com/mattn/go-sqlite3 
License: MIT license

#go #golang #sqlite 

Go-sqlite3: Sqlite3 Driver for Go using Database/sql
Lawson  Wehner

Lawson Wehner

1660214847

Prefs: Flutter Library for Use Of SQLite Database

Store and Read Your App’s Preferences

A Flutter Example

What’s Your Preference?

The plugin, shared_preferences, is offered to Flutter developers to store and read their app’s preferences in both the Android and the iOS platform. It wraps the NSUserDefaults (on iOS) and the SharedPreferences (on Android), providing persistent storage. Note, as of this writing, the plugin does not guarantee its writes to disk and suggests it not be used for storing critical data. As it is, it’s worked for me so far. So much so, I’ve adapted it fully for my apps.

Installing

I don't always like the version number suggested in the 'Installing' page. Instead, always go up to the 'major' semantic version number when installing my library packages. This means always entering a version number trailing with two zero, '.0.0'. This allows you to take in any 'minor' versions introducing new features as well as any 'patch' versions that involves bugfixes. Semantic version numbers are always in this format: major.minor.patch.

  1. patch - I've made bugfixes
  2. minor - I've introduced new features
  3. major - I've essentially made a new app. It's broken backwards-compatibility and has a completely new user experience. You won't get this version until you increment the major number in the pubspec.yaml file.

And so, in this case, add this to your package's pubspec.yaml file instead:

dependencies:
  prefs:^3.0.0

There’s a Class for That

There’s an example provided by the plugin website demonstrating how the plugin is implemented. In my opinion, not the most realistic example as it just promptly displays an integer demonstrating the success of its persistent storage. Regardless, I will use this same example now in this article to further demonstrate the use of a class library called, prefs.dart, that I freely offer to other developers to quickly and easily implement stored preferences in their app’s. Here you go, copy the last three lines listed below and place them in your own pubspec.yaml file. You’re then on your way to storing preferences in your Flutter app.

pubspec.yaml pubspec.yaml

Better still, download the file, perfs.dart, as there’s no guarantee that repo. , perfs.git, will last forever.

This Dart library is under the APACHE license. It’s free! Take it, make it better and then share.

Take Five

Below is the main.dart file that is very much like the example found in the plugin website. However, this file instead uses the class library, prefs.dart, to do much of the ‘heavy lifting’ behind the scenes when it comes to using the plugin. As a result, there are five lines in all required to implement and to demonstrate the app’s preferences storage and retrieval capabilities.

Take Five

What’s Going On Here

After looking over the code above, there are a few things you will have noticed regarding the implementation of the class library. It’s all static function calls for one. Nothing is instantiated. You’d guess right, if you then deduced this is a static class. You won’t find any instance variables or instance methods in this class library. This allows you to more easily implement your app’s preferences anywhere in your app. Preferences are an important concern for your app, and so you should be able to reference them any time and any where in your app.

The only real requirements is that you first initialize the class library in the State object’s initState() function, and that you then clean up things in the State object’s dispose() function. After that, you are then free to call up preferences any time; any where they’re needed in your app.

Therefore, in this example, you see a call to static class function, Prefs.init(), to initialize, as it happens, a reference to the plugin, shared_preferences, in the State object’s initState() function. Correspondingly, you see a call to static class function, Prefs.dispose(), in the State object’s dispose() function to then clean things up when the app is terminated. Pretty straight forward. You don’t need to be concerned with what goes on this those functions — of course, being a free class library, you can readily go take a peek.

The Future is Now…or Not

Like the original example in the plugin’s website, this example uses a Future object of type Future in a ‘library-private’ variable called, _counter. Like the original example, this ‘counter’ is assigned a value in the State object’s initState() function. However, you’ll find that, in the original, the code is a little more verbose:

Prefs Logo

In our example, the code does the very same thing, but it’s little cleaner:

Prefs Logo

It too returns a ‘Future’ object of type integer — a value of zero if the ‘counter’ key returns null. Returning null means there is no preference value with that key as of yet. Above, in the original code, you can determine a Future object is involved seeing that the callback function, then(), is being utilized. In our code, it’s the capital ‘F’ appended on the end of the function’s name that gives us the hint. Lastly, like the original code, the function, getIntF(), returns a non-null value of zero using the operator, ??, but you don’t readily need to know that. There’s more encapsulation involved here — keeping some of the operations behind the scenes.

In the original example, it takes two lines of code to increment the counter. A Future object of type integer can not be incremented like a numeric, and so we have ‘to wait’ for a ‘SharedPreferences’ object to be instantiated, then retrieve a value if any by the key, ‘counter’, and then increment the value by one:

incrementCounter

In our example, we’ve got it down to one short line of code:

new increment

Retrieving integer values from your app’s preferences is going to be a common operation. Best to write a function that repeatedly takes care of the details and, like the original code, supplies a value of zero if the provided key returns a null. You see the function doesn’t return a Future object but an integer. You can readily tell that by the declaration of the variable, counter, as well as by the missing capital ‘F.’

The State of the Counter

Further on in the original example, the setState() function is called to ‘update’ the counter on the app’s screen:

original setState

In our example, you see the code does the very same thing, but this code doesn’t require the parent function, _incrementCounter(), to use the async keyword. There’s no await command being used. Nor does it reveal that it’s ‘SharedPerferences’ that’s the means for the persistent storage. Instead, you’re allowed to call this function any time; any where. Much cleaner and much easier to use.

new setState

As an aside, note the use of the ‘return’ statement allows you to assign, in this case, an integer value to the Future object, _counter. As you see, you can’t assign the integer value directly to a variable, _counter, of type Future.

Future Error

You Get Ten

You can see below all the ‘get’ functions available to the developer when using this class library. The first five returns the data type implied in each of the function’s names, while each then has their ‘Future’ counterpart in the remaining five functions.

Ten Gets

Here’s the Keys

You’ve access to all the keys that can be found in your preferences. Like the ‘get’ functions, you have the option to return a ‘Future’ type of the set of Strings as well.

Get Keys

Let’s Get Dynamic

If you like, you don’t have to be concerned with the ‘data type’ of the preference value and simply call the ‘get’ functions. Again, a ‘Future’ version is available.

Get Dynamic

It’s All Set Up

Along with the first five ‘get’ functions there is a corresponding ‘set’ function. Each writes a particular data type to persistent storage. With a successful write, a Future data type of ‘bool’ is returned.

Set Functions

Remove and Clear

You have a means to remove a specific preference and or clear out all the preferences with one command. They too return a Future of type, ‘bool.’

Remove Function

Conclusion

There you have it. Take this class library, and use it for your app’s preferences. An extensive walkthrough the library package can be found in the Medium article, A Flutter Class for Your App’s Preferences.

Other Dart Packages

packages Other Dart packages from the author can also be found at Pub.dev

Installing

Use this package as a library

Depend on it

Run this command:

With Flutter:

 $ flutter pub add prefs

This will add a line like this to your package's pubspec.yaml (and run an implicit flutter pub get):

dependencies:
  prefs: ^3.5.0

Alternatively, your editor might support flutter pub get. Check the docs for your editor to learn more.

Import it

Now in your Dart code, you can use:

import 'package:prefs/prefs.dart';

example/lib/main.dart

import 'package:flutter/material.dart';

import 'package:flutter/services.dart';

import 'package:prefs/prefs.dart';

void main() => runApp(const PrefsDemoApp());

class PrefsDemoApp extends StatelessWidget {
  const PrefsDemoApp({Key? key}) : super(key: key);

  @override
  Widget build(BuildContext context) =>
      const MaterialApp(home: SharedPreferencesDemo());
}

class SharedPreferencesDemo extends StatefulWidget {
  const SharedPreferencesDemo({Key? key}) : super(key: key);
  @override
  _SharedPreferencesDemoState createState() => _SharedPreferencesDemoState();
}

class _SharedPreferencesDemoState extends State<SharedPreferencesDemo> {
  @override
  void dispose() {
    /// Clean up
    Prefs.dispose();
    super.dispose();
  }

  final _formKey = GlobalKey<FormState>();

  bool get radioButton => _radioButton ??= Prefs.getBool('boolean', false);
  set radioButton(bool radio) => _radioButton = radio;
  bool? _radioButton;

  @override
  Widget build(BuildContext context) => Scaffold(
        appBar: AppBar(
          title: const Text('Preferences Demo'),
        ),
        body: FutureBuilder<bool>(
            future: initPrefs(),
            builder: (_, snapshot) {
              //
              if (snapshot.hasData) {
                //
                if (snapshot.data != null) {
                  return form;
                } else {
                  //
                  return const Text('Failed to startup');
                }
              } else if (snapshot.hasError) {
                //
                return Text('${snapshot.error}');
              }
              // By default, show a loading spinner.
              return const Center(child: CircularProgressIndicator());
            }),
      );

  Widget get form => Form(
        key: _formKey,
        child: Column(crossAxisAlignment: CrossAxisAlignment.start, children: <
            Widget>[
          TextFormField(
            key: const Key('string'),
            initialValue: Prefs.getString('string'),
            onSaved: (String? newValue) => Prefs.setString('string', newValue),
            decoration: const InputDecoration(
              labelText: 'Enter one or two words',
            ),
            keyboardType: TextInputType.text,
            inputFormatters: <TextInputFormatter>[
              FilteringTextInputFormatter.singleLineFormatter,
            ],
          ),
          TextFormField(
            key: const Key('integer'),
            initialValue: Prefs.getInt('integer').toString(),
            onSaved: (String? newValue) {
              if (newValue!.isNotEmpty) {
                Prefs.setInt('integer', int.parse(newValue));
              }
            },
            decoration: const InputDecoration(
              labelText: 'Enter a integer',
            ),
            keyboardType: const TextInputType.numberWithOptions(decimal: false),
            inputFormatters: <TextInputFormatter>[
              FilteringTextInputFormatter.digitsOnly,
            ],
          ),
          TextFormField(
            key: const Key('double'),
            initialValue: Prefs.getDouble('double').toString(),
            onSaved: (String? newValue) {
              if (newValue!.isNotEmpty) {
                Prefs.setDouble('double', double.parse(newValue));
              }
            },
            decoration: const InputDecoration(
              labelText: 'Enter a Double number',
            ),
            keyboardType: const TextInputType.numberWithOptions(decimal: true),
            // inputFormatters: <TextInputFormatter>[
            //   FilteringTextInputFormatter.allow(RegExp(r'[0-9]')),
            // ],
          ),

          // True - False Radio Button
          _RadioButton(this),
          Align(
            alignment: Alignment.bottomRight,
            child: Padding(
              padding: const EdgeInsets.all(20),
              child: ElevatedButton(
                key: const Key('Save'),
                onPressed: () {
                  _formKey.currentState!.save();
                  Prefs.setBool('boolean', _radioButton);
                },
                child: const Text('Save'),
              ),
            ),
          ),
        ]),
      );

  Future<bool> initPrefs() async {
    // Initialize SharedPreferences
    final SharedPreferences? prefs = await Prefs.init();
    return prefs != null;
  }
}

class _RadioButton extends StatefulWidget {
  const _RadioButton(this.state, {Key? key}) : super(key: key);
  final _SharedPreferencesDemoState state;
  @override
  State<StatefulWidget> createState() => _RadioButtonState();
}

class _RadioButtonState extends State<_RadioButton> {
  @override
  Widget build(BuildContext context) => Row(
        mainAxisAlignment: MainAxisAlignment.center,
        children: [
          Flexible(
            child: RadioListTile<bool>(
              key: const Key('true'),
              value: true,
              groupValue: widget.state.radioButton,
              onChanged: (newValue) {
                widget.state.radioButton = newValue as bool;
                setState(() {});
              },
              title: const Text('True'),
            ),
          ),
          Flexible(
            child: RadioListTile<bool>(
              key: const Key('false'),
              value: false,
              groupValue: widget.state.radioButton,
              onChanged: (newValue) {
                widget.state.radioButton = newValue as bool;
                setState(() {});
              },
              title: const Text('False'),
            ),
          ),
        ],
      );
}

Download Details:

Author: AndriousSolutions
Source Code: https://github.com/AndriousSolutions/prefs 
License: View license

#flutter #dart #sqlite #database 

Prefs: Flutter Library for Use Of SQLite Database
Thomas  Granger

Thomas Granger

1660203673

Cross-platform SQLite Database in .NET MAUI App with EF Core

C# and LINQ for data access with EF Core. Learn how Entity Framework (EF) Core simplifies working with a cross-platform SQLite database in a .NET MAUI app and handles everything from complex queries to multiple updates that execute with a single line of developer code.

Forget magic strings, hand-written SQL queries and unfamiliar SDKs to deal with local data in your .NET MAUI apps. Entity Framework Core, also known as EF Core, is an object-mapper that empowers .NET developers to work with databases using the .NET languages and type system they know and love. In this session, learn how EF Core simplifies working with a cross-platform SQLite database in a .NET MAUI app and handles everything from complex queries to multiple updates that execute with a single line of developer code. Discover how client developers can use the same APIs that drive most REST, GraphQL, and gRPC backends on the server!

#dotnet #csharp #efcore #entityframework #linq #sqlite

 

Cross-platform SQLite Database in .NET MAUI App with EF Core

Provides Lightweight Yet Convenient Bindings to SQLite By using Dart

sqlite3

Provides Dart bindings to SQLite via dart:ffi.

For an example on how to use this library from Dart, see the example.

Supported platforms

You can use this library on any platform where you can obtain a DynamicLibrary with symbols from sqlite3. In addition, this package experimentally supports the web through WebAssembly.

Here's how to use this library on the most popular platforms:

  • Android: Flutter users can depend on the sqlite3_flutter_libs package to ship the latest sqlite3 version with their app.
  • iOS: Contains a built-in version of sqlite that this package will use by default. When using Flutter, you can also depend on sqlite3_flutter_libs to ship the latest sqlite3 version with your app.
  • Linux: Flutter users can depend on sqlite3_flutter_libs to ship the latest sqlite3 version with their app. Alternatively, or when not using Flutter, you can install sqlite3 as a package from your distributions package manager (like libsqlite3-dev on Debian), or you can manually ship sqlite3 with your app (see below).
  • macOS: Contains a built-in version of sqlite that this package will use by default. Also, you can depend on sqlite3_flutter_libs if you want to include the latest sqlite3 version with your app.
  • Windows: Flutter users can depend on sqlite3_flutter_libs to ship the latest sqlite3 version with their app. When not using Flutter, you need to manually include sqlite3 (see below).
  • Web: See web support below.

On Android, iOS and macOS, you can depend on the sqlcipher_flutter_libs package to use SQLCipher instead of SQLite. Just be sure to never depend on both sqlcipher_flutter_libs and sqlite3_flutter_libs!

Manually providing sqlite3 libraries

Instead of using the sqlite3 library from the OS, you can also ship a custom sqlite3 library along with your app. You can override the way this package looks for sqlite3 to instead use your custom library. For instance, if you release your own sqlite3.so next to your application, you could use:

import 'dart:ffi';
import 'dart:io';

import 'package:sqlite3/open.dart';
import 'package:sqlite3/sqlite3.dart';

void main() {
  open.overrideFor(OperatingSystem.linux, _openOnLinux);

  final db = sqlite3.openInMemory();
  // Use the database
  db.dispose();
}

DynamicLibrary _openOnLinux() {
  final scriptDir = File(Platform.script.toFilePath()).parent;
  final libraryNextToScript = File('${scriptDir.path}/sqlite3.so');
  return DynamicLibrary.open(libraryNextToScript.path);
}

Just be sure to first override the behavior and then use sqlite3.

Supported datatypes

When binding parameters to queries, the supported types are ìnt, double, String, List<int> (for BLOB) and null. Result sets will use the same set of types. On the web (but only on the web), BigInt is supported as well.

WASM (web support)

This package experimentally supports being used on the web with a bit of setup. The web version binds to a custom version of sqlite3 compiled to WebAssembly without Emscripten or any JavaScript glue code.

Setup

To use this package on the web, you need:

  • The sqlite3 library compiled as a WebAssembly module, available from the GitHub releases of this package. Note thaat, for this package, sqlite3 has to be compiled in a special way. Existing WebAssembly files from e.g. sql.js will not work with package:sqlite3/wasm.dart.
  • A file system implementation, since websites can't by default access the host's file system. This package provides FileSystem.inMemory() and an IndexedDbFileSystem implementation.

After putting sqlite3.wasm under the web/ directory of your project, you can open and use sqlite3 like this:

import 'package:http/http.dart' as http;
import 'package:sqlite3/common.dart';
import 'package:sqlite3/wasm.dart';

Future<WasmSqlite3> loadSqlite() async {
  final response = await http.get(Uri.parse('sqlite.wasm'));
  final fs = await IndexedDbFileSystem.load('/');

  return await WasmSqlite3.load(
      response.bodyBytes, SqliteEnvironment(fileSystem: fs));
}

The returned WasmSqlite3 has an interface compatible to that of the standard sqlite3 field in package:sqlite3/sqlite3.dart, databases can be opened in similar ways.

An example for such web folder is in example/web/ of this repo. To view the example, copy a compiled sqlite3.wasm file to web/sqlite3.wasm in this directory. Then, run dart run build_runner serve example:8080 and visit http://localhost:8080/web/ in a browser.

Sharing code between web and a Dart VM

The package:sqlite3/common.dart library defines common interfaces that are implemented by both the FFI-based native version in package:sqlite3/sqlite3.dart and the experimental WASM version in package:sqlite3/wasm.dart. By having shared code depend on the common interfaces, it can be used for both native and web apps.

Compiling

Note: Compiling sqlite3 to WebAssembly is not necessary for users of this package, just grab the .wasm from the latest release on GitHub.

You'll need a clang toolchain capable of compiling to WebAssembly and a libc suitable for it (I use wasi in /usr/share/wasi-sysroot).

Create a build directory, then run

cmake <path/to/clone>/sqlite3/assets/wasm --toolchain <path/to/clone>/sqlite3/assets/wasm/toolchain.cmake
make -j output

Use this package as a library

Depend on it

Run this command:

With Dart:

 $ dart pub add sqlite3

With Flutter:

 $ flutter pub add sqlite3

This will add a line like this to your package's pubspec.yaml (and run an implicit dart pub get):

dependencies:
  sqlite3: ^1.7.2

Alternatively, your editor might support dart pub get or flutter pub get. Check the docs for your editor to learn more.

Import it

Now in your Dart code, you can use:

import 'package:sqlite3/sqlite3.dart'; 

example/main.dart

import 'dart:io';

import 'package:sqlite3/sqlite3.dart';

void main() {
  print('Using sqlite3 ${sqlite3.version}');

  // Create a new in-memory database. To use a database backed by a file, you
  // can replace this with sqlite3.open(yourFilePath).
  final db = sqlite3.openInMemory();

  // Create a table and insert some data
  db.execute('''
    CREATE TABLE artists (
      id INTEGER NOT NULL PRIMARY KEY,
      name TEXT NOT NULL
    );
  ''');

  // Prepare a statement to run it multiple times:
  final stmt = db.prepare('INSERT INTO artists (name) VALUES (?)');
  stmt
    ..execute(['The Beatles'])
    ..execute(['Led Zeppelin'])
    ..execute(['The Who'])
    ..execute(['Nirvana']);

  // Dispose a statement when you don't need it anymore to clean up resources.
  stmt.dispose();

  // You can run select statements with PreparedStatement.select, or directly
  // on the database:
  final ResultSet resultSet =
      db.select('SELECT * FROM artists WHERE name LIKE ?', ['The %']);

  // You can iterate on the result set in multiple ways to retrieve Row objects
  // one by one.
  resultSet.forEach((element) {
    print(element);
  });
  for (final Row row in resultSet) {
    print('Artist[id: ${row['id']}, name: ${row['name']}]');
  }

  // Register a custom function we can invoke from sql:
  db.createFunction(
    functionName: 'dart_version',
    argumentCount: const AllowedArgumentCount(0),
    function: (args) => Platform.version,
  );
  print(db.select('SELECT dart_version()'));

  // Don't forget to dispose the database to avoid memory leaks
  db.dispose();
}

Download Details:

Author: simolus3

Source Code: https://github.com/simolus3/sqlite3.dart/tree/master/sqlite3

#dart #sqlite 

Provides Lightweight Yet Convenient Bindings to SQLite By using Dart
Nat  Grady

Nat Grady

1658826780

TS SQLite Layer with Support for Both Native C++ & Pure JS Drivers

trilogy is a simple Promise-based wrapper for SQLite databases. It supports both the native C++ sqlite3 driver and the pure JavaScript sql.js backend — compile natively for speed when you need it, or use sql.js headache-free in cross-platform environments and Electron apps.

It's not an ORM and isn't intended to be one — it doesn't have any relationship features. Instead it focuses on providing a simple, clear API that's influenced more by Mongoose than by SQL.


features

🔗 automatically casts data between JavaScript & SQLite types

Define schemas with types like String, Date, or 'increments' — trilogy will handle all the type-casting involved to map accurately between JavaScript and the underlying SQLite database.

🔋 powered by the knex query builder

trilogy uses knex internally to build its queries, but it's also exposed so you can use it to build your own. No need to mess with ridiculous multi-line strings.

🔩 supports multiple swappable backends ( plus in-memory storage )

Both the native sqlite3 module and sql.js (pure JavaScript!) are supported. There is also memory-only storage for fast, unpersisted data handling, which is great for tests and performance critical situations.

You can even swap the backend after you've started, with no changes to the rest of your code!

👮 written in TypeScript

trilogy is written in and provides a first-class experience for TypeScript.

🔌 lifecycle hooks

Any number of hooks (aka subscribers or listeners) can be attached at several points in the lifecycle — for example onQuery, beforeCreate, afterUpdate. These are useful for debugging and extensibility.

💞 perfect for Electron & NW.js

Compiling the sqlite3 module for all the platforms you target with Electron or NW.js can be difficult. That's why trilogy also supports the sql.js backend, which doesn't need to be compiled at all!

installation

Install trilogy

# using yarn
yarn add trilogy

# using npm
npm i trilogy

Install a backend

# using yarn
yarn add sqlite3

# using npm
npm i sqlite3

or

# using yarn
yarn add sql.js

# using npm
npm i sql.js

usage

Full documentation is available here and includes guides, an API reference, and more.

Here's a quick overview. It uses async & await but is easily usable with vanilla Promises.

import { connect } from 'trilogy'

// defaults to using the `sqlite3` backend
const db = connect('./file.db')

// choose `sql.js` to avoid native compilation :)
const db = connect('./file.db', {
  client: 'sql.js'
})

// set the filename to ':memory:' for fast, in-memory storage
const db = connect(':memory:', {
  // it works for both clients above!
  client: 'sql.js'
})

;(async function () {
  const games = await db.model('games', {
    name: { type: String },
    genre: String,            // type shorthand
    released: Date,
    awards: Array,
    id: 'increments'          // special type, primary key
  })

  await games.create({
    name: 'Overwatch',
    genre: 'FPS',
    released: new Date('May 23, 2016'),
    awards: [
      'Game of the Year',
      'Best Multiplayer Game',
      'Best ESports Game'
    ]
  })

  const overwatch = await games.findOne({ name: 'Overwatch' })

  console.log(overwatch.awards[1])
  // -> 'Best Multiplayer Game'
})()

contributing

This project is open to contributions of all kinds! Don't worry if you're not 100% up to speed on the process — there's a short outline in the Contributor Guide.

You'll also find a reference for the set of labels used to categorize issues, with descriptions of each. (Contributor Guide - issue labels)

Also, please read and follow the project's Code of Conduct.

Author: Haltcase
Source Code: https://github.com/haltcase/trilogy 
License: MIT license

#electron #typescript #sqlite #javascript 

TS SQLite Layer with Support for Both Native C++ & Pure JS Drivers