Igor Delovski Board

[Ike]

dBlog - iPhone SDK Tutorial: Reading data from a SQLite Database

"I see many people asking for SQLite tutorials around, and since I am using
SQLite for the next part in the Advanced RSS Reader Tutorial, I thought
I would write up a quick tutorial on using SQLite with the iPhone SDK."

[delovski]

sqlite.org - How SQLite Is Tested

"There are three independent test harnesses used for testing the core SQLite
library. Each test harness is designed, maintained, and managed separately
from the others."

[Ike]

Shannon Appelcline - A Database Development Gotcha

"On page 305 [of iPhone in Action], we say "Once you've linked in your data-
base the first time [by adding it to your project], you can go back and make
changes to it, and the new version will always be used when you recompile
your project." And that's true ... to a certain extent."

[delovski]

so - iPhone create SQLite database at runtime?

"Open the file as usual using sqlite3_open(). Run your creation commands
(i.e., create your tables, views, etc.)"

[XNote]

jainmarket - iPhone SDK Tutorial: Reading data from a SQLite Database

"I see many people asking for SQLite tutorials around, and since I am using
SQLite for the next part in the Advanced RSS Reader Tutorial, I thought I
would write up a quick tutorial on using SQLite with the iPhone SDK."

[delovski]

MF - Gah, how do you go about designing databases?

"Some advice I would offer is to learn about normalisation and attempt to
achieve as many normal forms as possible but always be prepared to de-
normalise where necessary. Don't pursue perfection because it doesn't
exist, except in training courses..."

[delovski]

iphonedevsdk.com - SQLite, inserts and reading back UTF8 characters
properly

"I'm having some issues inserting and reading back UTF8 special characters
from SQlite..."

[delovski]

iPhone SDK Articles - SQLite Tutorial - Saving images in the database

"Using BLOB data type we can store an image in the SQLite database. The
data that actually gets stored in the database is the bytes that make up an
image or a file. The is the sixth tutorial in SQLite tutorial series and borrows
its source code from the previous one."

[XNote]

sqlite.org - Scrolling Cursor

"There is no sqlite3_step_backwards() function in SQLite. It is normal-
ly at this point in the reasoning process that the programmer gets on the
mailing list asking for how to 'scroll backwards'.

Do not leave queries open waiting for the user input. Run a query to fill
up the screen with as much information as it will hold then reset() or fina-
lize() the query statment. Get in, grab your data, then get out. Later on,
when the user decides to scroll up or down (which will usually be eons of
time later from the point of view of your CPU), run another query to re-
fresh the screen with new data."

[delovski]

so - Using backticks around field names

"Lastly, it allows you to use whatever field name you wish when design-
ing your table. Sometimes it makes a lot of sense to name a field `key`,
`order`, or `values`... all of which require backticks when referring to
them."

[delovski]

sqlite.org - Opening A New Database Connection

"Whether or not an error occurs when it is opened, resources associated
with the database connection handle should be released by passing it to
sqlite3_close() when it is no longer required.

The sqlite3_open_v2() interface works like sqlite3_open() except that it
accepts two additional parameters for additional control over the new
database connection. The flags parameter to sqlite3_open_v2() can take
one of the following three values...

* SQLITE_OPEN_READONLY
* SQLITE_OPEN_READWRITE
* SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE

If the filename is ":memory:", then a private, temporary in-memory
database is created for the connection."

[delovski]

PostgreSQL connection

"I was able to compile libpq for the iPhone using arm6 processor

I used the following ./configure command..."

[delovski]

MF - Efficient table views using SQLite

"Only the promary key is indexed by default - if you want to index anything
else you will have to tell the DB about it when you first create the table."

[Ike]

ccgus / fmdb

This is an Objective-C wrapper around SQLite: http://sqlite.org/

[delovski]

sqlite.org - 1.0 File Locking And Concurrency In SQLite Version 3

"Locking and concurrency control are handled by the pager module. The
pager module is responsible for making SQLite "ACID" (Atomic, Consistent,
Isolated, and Durable). The pager module makes sure changes happen all
at once, that either all changes occur or none of them do, that two or more
processes do not try to access the database in incompatible ways at the
same time, and that once changes have been written they persist until
explicitly deleted."

[delovski]

karnakgames.com - Painless iOS content apps with SQLite ActiveRecord

"Active Record is a pattern to model applications following an object-relatio-
nal mapping which means you’ll have a “virtual object database”: each data-
base table is represented as a class and each table registry is an instance of
its table class. In your application you won’t make direct calls to SQLite fun-
ctions nor write SQL queries everywhere."

[Ike]

"Another side effect of a dynamic database is table fragmentation. The pages
containing the data of an individual table can become spread over the data-
base file, requiring longer for it to load. This can appreciably slow database
speed because of file system behavior. Compacting fixes both of these pro-
blems.

... The easiest way to remove empty pages is to use the SQLite command
VACUUM. This can be done from within SQLite library calls or the sqlite utility.

How many free pages there are in a database can be determined at runtime
using the function sqlite_count_free_pages()..."

[delovski]

so -ADD COLUMN to sqlite db IF NOT EXISTS - flex/air sqlite?

"ALTER TABLE tbl_status ADD COLUMN status_default TEXT;

http://www.sqlite.org/lang_altertable.html

That being said, adding columns in SQLite is limited. You cannot add a
column anywhere but after the last column in your table."

Related: sqlite3odbc.c

[Ike]

blog.jooq.org - Do You Really Understand SQL’s GROUP BY and HAVING clauses?

"There are some things in SQL that we simply take for granted without
thinking about them properly.

One of these things are the GROUP BY and the less popular HAVING
clauses. Let’s look at a simple example. For this example, we’ll reiterate
the example database we’ve seen in this previous article about the awe-
some LEAD(), LAG(), FIRST_VALUE(), LAST_VALUE() functions"

[Ike]

Five reasons you should use SQLite in 2016

"SQLite is easy to manage -- it's a single file. The file format is stable across
major versions, so if I had a SQLite database file from version 3.0.0 (back in
2004), I could read it using the latest SQLite 3.10.0."

[Ike]

git - OHMySQL

"The library supports Objective-C and Swift, iOS and macOS. You can connect to
your remote MySQL database using OHMySQL API. It allows you doing queries
in easy and object-oriented way. Common queries such as SELECT, INSERT,
DELETE, JOIN are wrapped by Objective-C code and you don't need to dive into
MySQL C API."

git - mysql-ios

"This repo is a convenience project for building mysql for iOS (in particular
mysqlclient). It contains the mysql source at version 8.0.12 and generated
or modified XCode project for building mysqlclient for iOS."

[Ike]

wiki.tnonline.net - SQLite Performance on Btrfs

"By using Write-Ahead Logging we can gain a 300% performance boost,
compared to about 25-30% increase using the nocow attribute!."

[Ike]

Valentina - SQLite Server - Valentina 13

"Serve stunningly visual reports from all major data sources.

Reports Server + SQLite Server + Forms Server + object-relational SQL
Valentina DB Server. Windows, macOS & Linux on x86 & ARM64."