DZone Snippets: symbian code

Create a primary key with autoincrement in Symbian DBMS

korakot (Korakot Chaovavanich) — Fri, 10 Mar 2006 16:17:05 GMT



CREATE TABLE person (id COUNTER, name VARCHAR)

CREATE UNIQUE INDEX id_index ON person(id)

COUNTER is UNSIGNED INTEGER with Autoincrement.
You still need a UNIQUE INDEX as well.

Interactive SQL shell

korakot (Korakot Chaovavanich) — Thu, 09 Mar 2006 14:01:20 GMT

To help myself learning about SQL in Symbain DBMS, I write
a small script to act as an interactive SQL shell.
First you need to connect to the database.



>>> import e32db

>>> db = e32db.Dbms()

>>> dbv = e32db.Db_view()

>>> db.open(u'C:\\test.db')  # might need db.create(...)

Here's the query simplification code.



def Q(sql):

    if sql.upper().startswith('SELECT'):

        dbv.prepare(db, unicode(sql))

        dbv.first_line()

        rows = []

        maxlen = [0] * dbv.col_count()

        for i in range(dbv.count_line()):

            dbv.get_line()

            result = []

            for i in range(dbv.col_count()):

                try:

                    val = dbv.col(i+1)

                except:    # in case coltype 16

                    val = None

                result.append(val)

                maxlen[i] = max(maxlen[i], len(str(val)))

            rows.append(result)

            dbv.next_line()

        fmt = '|'+ '|'.join(['%%%ds' % n for n in maxlen]) + '|'

        for row in rows:

            print fmt % tuple(row) 

    else:

        n = db.execute(unicode(sql))

        print '%d rows affected' % n

After that, playing with SQL is quite simple.



>>> Q("CREATE TABLE person (id COUNTER, name VARCHAR)")

0 rows affected

>>> Q("INSERT INTO person(name) VALUES ('Korakot')")

1 rows affected

>>> Q("INSERT INTO person(name) VALUES ('morning_glory')")

1 rows affected

>>> Q("SELECT * from person")

|0|      Korakot|

|1|morning_glory|

SQL for Symbian DBMS

korakot (Korakot Chaovavanich) — Thu, 09 Mar 2006 13:04:15 GMT

I have summarized the allowable SQL below.



# 'SELECT' is the most popular SQL

SELECT select-list FROM table-name [ WHERE search-condition ] [ ORDER BY sort-order ] 

# 3 search condition types: compare, like, null



# DML : INSERT, DELETE, UPDATE 

INSERT INTO table-name [ ( column-identifier,… ) ] VALUES ( column-value,… ) 

DELETE FROM table-name [ WHERE search-condition ] 

UPDATE table-name SET update-column,… [ WHERE search-condition ] 



# DDL : Work with the schema

CREATE TABLE table-name (column-definition,…) 

DROP TABLE table-name 

ALTER TABLE table-name { ADD add-column-set [ DROP drop-column-set ] | DROP drop-column-set } 

CREATE [ UNIQUE ] INDEX index-name ON table-name ( sort-specification,… ) 

DROP INDEX index-name FROM table-name

Plus a few column types (not all of them)



['BIT', 'INTEGER', 'COUNTER', 'BIGINT', 'FLOAT',

 'TIMESTAMP', 'VARCHAR(n)', 'LONG VARCHAR']

# COUNTER is an auto-incremented unsigned integer

Symbian phone list

korakot (Korakot Chaovavanich) — Sat, 04 Mar 2006 06:22:35 GMT



# S60 3rd Edition (Symbian OS v9.1)

Nokia E60, E61, E70

Nokia 3250

Nokia N71, N80, N91, N92



# S60 2nd Edition FP3 (Symbian OS v8.1)

Nokia N70, N90



# S60 2nd Edition FP2 (Symbian OS v8.0a)

Nokia 6630, 6680, 6681, 6682

Lenovo P930



# S60 2nd Edition FP1 (Symbian OS v7.0s enhanced)

Nokia 3230, 6260, 6620, 6670, 7610

Panasonic X700, X800

Samsung SDH-D720



# S60 2nd Edition (Symbian OS v7.0s)

Nokia 6600



# S60 1st Edition (Symbian OS v6.1)

Nokia 3600, 3620, 3650, 3660, 7650

Nokia N-Gage, N-Gage QD

Sendo X

Siemens SX1

See official list.

Finding all symbian database files

korakot (Korakot Chaovavanich) — Sun, 12 Feb 2006 20:16:39 GMT

Hmm.. I am considering hacking the Dbms format.
I want to know what tables are there in each db file.
The first step is to list all such files.



>>> def print_db(arg, dirname, names):

...   for name in names:

...     if name.endswith('.db'):

...       print dirname + '\\' + name

...

>>> os.path.walk('C:\\', print_db, None)

C:\\SportsDiary.db

C:\\test.db

C:\system\Data\Bookmarks1.db

C:\system\Data\lcscfg.db

C:\system\Data\lcs.db

C:\system\Data\NSmlDSSettings.db

C:\system\Data\SavedDecks1.db

C:\system\Data\midp2\systemams\MIDP2SystemAMSDynamic.db

C:\system\Data\midp2\systemams\MIDP2SystemAMSStatic.db

C:\system\Data\srsplugin\SDDatabase.db

C:\system\Data\vasdb\vasdatabase.db

>>>