Explore Posterous

SQLite-luokka PHP:lle

Koodasin tänään illasta luokan PHP:lle SQLite v2.x tietokantojen käsittelyyn. Luokka on vapaata lähdekoodia (GNU AGPL) ja se löytyy  tuttuun tapaan GitHubista. Suora osoite tuohon luokkaan on http://github.com/stargazers/CSQLite eli siitä vain latailemaan ja testailemaan jos tarvetta ja innostusta on. Bugiraportteja saa mailata aleksi.rasanen@runosydan.net jos sellaisia löytääpi.

Loading mentions Retweet

Comment (0) »

Migrating Trac from SQLite to MySQL

A little while ago I was tasked with migrating about hundred Trac instances from SQLite to MySQL. Unfortunately SQLites schema definitions is not compatible with MySQL. So I had to write some scripts to handle it. After a little bit of experimenting everything worked perfectly.

The Strategy I employed was like this:

1. Make a dump of a default MySQL Trac db schema.
2. Make a dump of the Trac SQLite database
3. Remove db schema definitions from the SQLite dump
4. Concatenate the MySQL and SQLite dumps
5. Load data into MySQL
6. Edit Trac's database settings to use MySQL
7. Do a trac-admin upgrade

Here is the shell script I made:

MYSQL_USER=username
MYSQL_PSWD=password

SQLITECMD=sqlite3
TRACBASE=/path/to/tracreps


# loop through all trac instances in tracreps
for d in $( ls $TRACBASE )
do
if [ -d "$TRACBASE/$d" ]; then
echo $d
TRACNAME=$d

# create database in mysql
echo "creating database for $TRACNAME..."
mysqladmin --user $MYSQL_USER -p$MYSQL_PSWD create $TRACNAME

# dump sqlite db structure + data
echo "dumping data from sqlite..."
$SQLITECMD $TRACBASE/$TRACNAME/db/trac.db .dump > trac.sqlite.sql

# remove database definitions from dump using a custom python script
echo "cleaning database definition from dump..."
`./cleansql.py < trac.sqlite.sql > trac.sqlite.sql.dataonly`

# concatenate mysql database definitions and sqlite data
cat trac.mysql.sql trac.sqlite.sql.dataonly > trac.sql

echo "loading data into mysql..."
mysql --user $MYSQL_USER -p$MYSQL_PSWD --default_character_set utf8 $TRACNAME < trac.sql

# edit database connection string in trac.ini
sed -i "s?sqlite:db/trac.db?mysql://$MYSQL_USER:$MYSQL_PSWD@localhost:3306/$TRACNAME?" $TRACBASE/$TRACNAME/conf/trac.ini

echo "upgrading mysql database..."
trac-admin $TRACBASE/$TRACNAME upgrade --no-backup

To remove schema definitions from the sqlite dump and fix some incompatibilities I made this python script referenced above as cleansql.py:

#!/usr/bin/env python

import sys
import re

file = sys.stdin.read()
file = re.sub(r'(CREATE (TABLE|INDEX)[^;]*|COMMIT|BEGIN TRANSACTION);', '', file)
file = re.sub(r'INSERT INTO "([^"]+)"', lambda m: 'INSERT INTO `%s`' % m.groups(1), file)
# fix sql for reports
file = re.sub(r'CAST\((.+) AS int\)', lambda m: 'CAST(%s AS signed)' % m.groups(1), file)

sys.stdout.write(file)

And that's it. I hope this will benefit someone tasked with the same job.

Loading mentions Retweet

Comment (0) »

My Key Points To Remember About Core Data

// find the location of the COMPILED xcdatamodel document named "Resource"

NSString *path = [[NSBundle mainBundle] pathForResource:@"Resource" ofType:@"momd"];

if (!path) {

path = [[NSBundle mainBundle] pathForResource:@"Resource" ofType:@"mom"];

}

NSAssert(path != nil, @"Unable to find Resource in main bundle");

// Create a URL to the path

NSURL *url = [NSURL fileURLWithPath:path];

// Create the thing that manages the model and its relationship with the graph, point it to the compiled model document.

NSManagedObjectModel * managedObjectModel = [[NSManagedObjectModel alloc] initWithContentsOfURL:url];

// Create the thing that manages the file/db-type to use to store the data objects. Give it our object model so it knows about the entities, etc.

NSPersistentStoreCoordinator *psc = [[NSPersistentStoreCoordinator alloc] initWithManagedObjectModel:managedObjectModel];

//- (NSPersistentStore *)addPersistentStoreWithType:(NSString *)storeType configuration:nil URL:(NSURL *)storeURL options:(NSDictionary *)options error:(NSError **)error     // this is what you use to set up the database type, etc.  In this snippet I don't worry about it because I just want it to compile and display something.

// set up a context in which the objects live--think of this as a business context. You have to have one.

newContext = [[NSManagedObjectContext alloc] init];

[newContext setPersistentStoreCoordinator:psc];  // This context will use our store coordinator (database mgmt object)

// Create an instance of an object based on an entity in our xcdatamodel document.  Here it is called Recent.  Insert it into our context to be managed.

// Before this will work you need to subclass NSManagedObject -- Recent :  NSManagedObject otherwise you will get an entity not found error.

NSManagedObject * o = [NSEntityDescription  insertNewObjectForEntityForName:@"Recent" inManagedObjectContext:newContext];

// set up some attributes within the entity object

[o setValue:@"/path/here" forKey:@"path"]; 

[o setValue:@"Name" forKey:@"gitName"];

// Add it to an array to be bound to a control in IB.

[a addObject:o];

The actual IB bindings are straight forward if you realize you have to bind the ManagedObjectContext you created (newContext) toward the bottom of the bindings panel in IB in addition to the normal bindings.  

And this just sets up the environment and displays a single line in my table view.  I haven't actually stored anything or fetched anything off the disk.

I hope that helps someone get kick-started in CD and saves a lot of hunting and pecking to get the right sequence in the setup.

Loading mentions Retweet

Comment (1) »

SQLite Persistent Objects

SQLite Persistent Objects is an SQLite wrapper for ObjectiveC developed by Jeff Lamarche.  It allows for easily persisting and retrieving ObjectiveC classes into an SQLite database.  

Loading mentions Retweet

Comment (0) »

sqliteのデータの型　意味ねぇー

Loading mentions Retweet

Comment (0) »

Flip Video FlipShare software uses SQLite for its database engine

The Flip is a compact video camera from Pure Digital Technologies.

The FlipShare software for the Flip Video stores its information in a database. Using the file command reveals that the database format is SQLite, version 3.

$ cd ~/Movies/FlipShare\ Data/
$ file flipshare.db
flipshare.db: SQLite database (Version 3)

We can then display all the tables in the database using the sqlite3 command-line tool:

$ sqlite3 flipshare.db .tables
Contact                  ContactSetDetails        MediaElementSourceGraph
ContactInfo              ContactType              UserFolderMediaElements
ContactProvider          MediaElement             UserFolders           
ContactProviderType      MediaElementHistory      Versions              
ContactSet               MediaElementSource

The UserFolder table is rather simple:

$ sqlite3 flipshare.db '.schema UserFolders'
CREATE TABLE UserFolders (id integer primary key, folderName varchar(256) not null, parentId integer);

We can perform a schema dump of the UserFolder to see the contents:

$ sqlite3 flipshare.db '.dump UserFolders'
BEGIN TRANSACTION;
CREATE TABLE UserFolders (id integer primary key, folderName varchar(256) not null, parentId integer);
INSERT INTO "UserFolders" VALUES(1,'January 2009',8);
INSERT INTO "UserFolders" VALUES(2,'February 2009',8);
INSERT INTO "UserFolders" VALUES(3,'March 2009',8);
INSERT INTO "UserFolders" VALUES(4,'December 2008',8);
INSERT INTO "UserFolders" VALUES(5,'June 2008',8);
INSERT INTO "UserFolders" VALUES(6,'July 2008',8);
INSERT INTO "UserFolders" VALUES(7,'August 2008',8);
INSERT INTO "UserFolders" VALUES(8,'September 2008',8);
INSERT INTO "UserFolders" VALUES(9,'October 2008',8);
INSERT INTO "UserFolders" VALUES(10,'May 2008',8);
COMMIT;

The SQL dump of the UserFolders table matches the folders that we see within the FlipShare software:

The MediaElementSource table keeps track of the path to the video files:

$ sqlite3 flipshare.db '.schema MediaElementSource' | sed 's/[         ][      ]*/ /g'
CREATE TABLE MediaElementSource (id integer primary key, uri varchar(256) not null, mediaType int not null, dataAccessible int);

The MediaElement table stores metadata about the videos, such as the creation date and thumbnail:

$ sqlite3 flipshare.db '.schema MediaElement' | sed 's/[       ][      ]*/ /g'
CREATE TABLE MediaElement (id integer primary key, mediaType int not null, mediaOrigin int not null, mediaSourceId integer, name varchar(256) not null, CreationDate varchar(256), PreviewImagePath varchar(256), SizeInBytes integer not null, ParentFolder integer, StartTime double, EndTime double, category_id integer not null, album_id integer not null, hash_code varchar(32) not null, camcorder_serial varchar(48), width integer, height integer, duration double default 0);

Loading mentions Retweet

Comment (0) »

Django + SQLite + Collations

There is rather well-known "SQLite case-insensitive problem", which is that SQLite can do case-insensitive SELECT only for ASCII character set. This problem was discussed at Django users list, posted as Djungo feature request and documented in the documentation:

  SQLite doesn't support case-insensitive matching for non-ASCII strings. Some 
  possible workarounds for this are `documented at sqlite.org`_, but they are 
  not utilised by the default SQLite backend in Django. Therefore, if you are 
  using the ``iexact`` lookup type in your queryset filters, be aware that it 
  will not work as expected for non-ASCII strings.

I also met this problem and I had to find a solution since I need to deploy one small application (ebooks catalogue) at the department machine where I didn't have root account and was unable to install another database engine.

The solution is not really hard.

Support of collations for SQLite is provided by loadable extension through ICU library, which is installed on almost every Linux box. This is documented here.

So first step is to get ICU extension for SQLite here and compile it with

  gcc -shared icu.c icu-config --ldflags -o icu.so

  sudo apt-get install libicu-dev

Now it is possible to test the extension (and also check if sqlite3 in your system is built with extension support):

  nazarov@heps1:~/lib> sqlite3
  SQLite version 3.6.11
  Enter ".help" for instructions
  Enter SQL statements terminated with a ";"
  sqlite> .load ./icu.so
  sqlite>  SELECT icu_load_collation('ru_RU', 'RUSSIAN');
    sqlite> SELECT * FROM core_author WHERE name LIKE 'вайн%';
  1420|Вайнер А.Л.
  4557|Вайнберг М.М.
  4558|Вайнберг М.М.
  6374|Вайнберг Б.
  7326|Вайнштейн Б.К.
  7327|Вайнштейн Л.А.
  7458|Вайнштейн С.И.
  8636|Вайнберг С.
  8958|Вайнштейн С.И.

Then we need extension support in pysqlite. Since version 2.5.2 pysqlite can load SQLite extensions, but this feature is disabled by default. So download pysqlite, extract, remove the line

  define=SQLITE_OMIT_LOAD_EXTENSION

  wget http://oss.itsystementwicklung.de/download/pysqlite/2.5/2.5.5/pysqlite-2.5.5.tar.gz
  tar xzf pysqlite-2.5.5.tar.gz
  cd pysqlite-2.5.5
  vi setup.cfg
  python setup.py build

Now you can check, that pysqlite can load icu and make case insensitive selects:

  export PYTHONPATH=`pwd`:$PYTHONPATH;export LD_LIBRARY_PATH=`pwd`:$LD_LIBRARY_PATH;python
  Python 2.5 (r25:51908, Nov 27 2006, 19:14:46) 
  [GCC 4.1.2 20061115 (prerelease) (SUSE Linux)] on linux2
  Type "help", "copyright", "credits" or "license" for more information.
  >>> from pysqlite2 import dbapi2 as sqlite3
  >>> con = sqlite3.connect("lib.db")
  >>> con.enable_load_extension(True)
  >>> con.load_extension("./icu.so")
  >>> con.execute("SELECT icu_load_collation('ru_RU', 'RUSSIAN');")
  <pysqlite2.dbapi2.Cursor object at 0xb7cab660>
  >>> con.execute("SELECT * FROM core_author WHERE name LIKE 'вайн%';")
  <pysqlite2.dbapi2.Cursor object at 0xb7cab820>

Also I had to build static version of pysqlite which included SQLite, because SQLite on target system didn't support loadable extenstion. I did it with

  python setup.py build_static

So now we need to tell Django to use collations. Correct way would be to write custom db backend, but it is far simplier just to add 3 following lines to the file django/db/backends/sqlite3/base.py:

*** 143,148 ****
--- 143,153 ----
              }
              kwargs.update(self.options)
              self.connection = Database.connect(**kwargs)
+             # Add Russian collation
+             self.connection.enable_load_extension(True)
+             self.connection.load_extension("/home/nazarov/lib/icu.so")
+             self.connection.execute("SELECT icu_load_collation('ru_RU', 'RUSSIAN');")
+ 
              # Register extract, date_trunc, and regexp functions.
              self.connection.create_function("django_extract", 2, _sqlite_extract)
              self.connection.create_function("django_date_trunc", 2, _sqlite_date_trunc)

Now Django applications can do case-insensitive SELECTs (iexact, icontains etc) for Russian language.

On my target machine I had to start Django development server with

  export PYTHONPATH=`pwd`/Django-1.0.2-final/:`pwd`:$PYTHONPATH
  export LD_LIBRARY_PATH=`pwd`:$LD_LIBRARY_PATH;
  python library/manage.py runserver 10.0.0.15:8000

Loading mentions Retweet

Comment (0) »

Dev Note: SQLite & AIR - A List of Tables

Flex \ AIR provides a great set of functionality around SQLite. The API is powerful, simple and for my money, I'd say critical to the overall platform. When using SQLite via Flex \ AIR - I've found that the API pretty much just works and we can be up and running in no time. That said, there can be some minor points that are not very obvious up front, one of those is trying to get a list of tables from a SQLite database via the Actionscript based AIR APIs. (Take Me Directly to the solution).

The Oddity: What's So Hard About It?
The basic reason getting a list of tables isn't as simple as the rest of the API is because the solution's implementation doesn't correspond well to general SQLite usage. That's not to say there is anything _wrong_ with AIR API design. There isn't, it's actually rather cool. The problem is, it's just not obvious.

The confusion starts at the SQLite documentation level. The SQLite documentation (which can be found ;mce:script type=">qlite.or<mce:script type=">g/ docs.html">here) actually contains an FAQ entry for getting a list of tables, and the FAQ says:

... get access to table and index names by doing a SELECT on a special table named "SQLITE_MASTER". Every SQLite database has an SQLITE_MASTER table that defines the schema for the database.

In fact, the FAQ even provides a nice query:

SELECT name FROM sqlite_master
WHERE type='table'
ORDER BY name;

Now, here's the confusion. This query, and in fact anything about the 'SQLITE_MASTER' table, just doesn't work from the Actionscript API. In this case, the SQLite documentation isn't going to help us. The issue is that the AIR implementation doesn't expose the SQLITE_MASTER table. There's even a bug in the Flex Bug and Issue Management System stemming from this confusion.

Basically, it _feels_ like something is broken, the query (which the SQLite docs say should work) doesn't work. When you use the query, or any query that references the SQLIST_MASTER table, you'll get an error that reports 'sqlite_master' table does not exist.

A Little Help: Using the SQLite Database Browser
When I first ran into this issue, I (like some of the people involved with the bug report) was pretty sure I'd found a bad bug in the AIR API. The SQLite docs said it should work, but it didn't. Was the SQLITE_MASTER table not accessible to me (security issue)? Was the table locked somehow (did I screw something up)? Did the table simply not exist - and thus the database itself wasn't constructed according to the SQLite docs? Hmmmmm.

A while ago, I found a nice tool called the 'SQLite Database Browser' and its awesome. It works on both OS X, Windows and Linux - it's free - and open source. Score! With the browser, I am able to see my store from a SQLite perspective:

Sure, there is no SQLITE_MASTER table listed. However, if we attempt the FAQ's query...

It looks like it's treated special, but the table is there, the database is constructed just as the docs say it should be and the query works. Sooo close!

BTW - SQLite Database Browser is an essential tool.[Random Helper: When doing database dev work, I always create and store my databases with a sort of unique extension. This always me to associate the SQLite Database Browser with my unique extension, giving me a great default SQLite database viewer (very very handy)]

How To: The Fix
In the bug I mentioned earlier, Jean-Pascal Hovel mentions using SQLConnection.loadSchema(). Also, poking around livedocs, we can find a very nice write-up on SQL support in local databases. In the write-up, we finally find the core point:

... The following SQL elements and SQLite features are supported in some SQLite implementations, but are not supported in Adobe AIR. Most of this functionality is available through methods of the SQLConnection class ...

so... Some of the normal SQLite goodness, just doesn't work - and...

... System table access is not available: The system tables including sqlite_master and other tables with the "sqlite_" prefix are not available in SQL statements. The runtime includes a schema API that provides an object-oriented way to access schema data. For more information see the SQLConnection.loadSchema() method. ...

Jean-Pascal Hovel was right about SQLConnection.loadSchema(), the bug really isn't a bug - instead - there's a specific mechanism for getting to the SQLITE_MASTER which Adobe intentionally built, good ol' SQLConnection.loadSchema().

The Code
Once we get that using SQLConnection.loadSchema is the right approach, we're golden. The code is fairly trivial, but I'll drop it here just for completeness:

statement.sqlConnection.open(store, SQLMode.READ);
// use SQLTableSchema to get tables only,
// see the loadSchema API Doc
statement.sqlConnection.loadSchema(); 

var result:SQLSchemaResult = statement.sqlConnection.getSchemaResult();
for each (var table:SQLTableSchema in result.tables)
{
  trace(table.name);
}

Do note, that using sqlConnection.loadSchema, you can get fairly specific about what data you want to query for. For example, the above call to loadSchema gets all objects in the database. The sqlConnection.loadSchema documentation provides this reference:

There we are... To get a list of tables for a local data store in AIR, use sqlConnection.loadSchema ... Nice.

Enjoy!

Loading mentions Retweet

Comment (8) »

SQLite As A Production Database (No, Really)

There's a certain stigma surrounding Sqlite. It seems that many developers will happily use it to prototype a project, but wouldn't think of deploying on anything less than Postgres (or MySQL, if you swing that way). I say this partially because I used to feel this way myself. I've run more low traffic web apps off of a Postgres server than I'd care to admit now that I've discovered the inherent convenience and manageability of a file-based SQLite database.

With SQLite an entire python web application fits within a single directory on the disk. This makes backup, testing and maintenance easier than you can likely imagine. SQLite also requires next to no setup, and most likely, is already running on your server.

Recently a user came across a bug in one of my web applications. In order to reproduce the bug, I didn't have to set up a test database and populate it with dummy data. Instead I merely copied the actual production database file along with the application files and within minutes had an identical copy of the application, data and all, running on my development machine.

There are trade-offs with SQLite and the developers make this very clear. Concurrent writes are an issue (I'd wager that actually very few web sites depend upon a large number of concurrent database writes) and the types system can throw traditional database developers for a twist. All the same, as the development community comes to grips with the fact that scripting languages can solve real problems as well or better (when you factor in time-to-market) than compiled languages, I think projects such as SQLite and CouchDB will finally motivate developers to begin looking for data persistence solutions in places other than monolithic relational database servers.

"SQLite isn't trying to replace Oracle, it's trying to replace 'fopen'" - D. Richard Hipp, creator of SQLite

- FLOSS Weekly Podcast with D. Richard Hipp, the creator of SQLite.

Loading mentions Retweet