Showing posts with label SQLite. Show all posts
Showing posts with label SQLite. Show all posts

Thursday, January 10, 2013

Windows Store App's and Databases (the series)

Windows Store apps development support - Using database in Windows Store apps (I)


There are lots of discussions about using database in Windows Store apps in MSDN forum. These discussions mainly focus on the usage of SQL database. Based on this scenario, we developed a series of articles along with sample code and demos used to demonstrate how to access both local database and remote database.

This topic consists of four categories as below.

  1. Overview of main approaches to access database in Windows Store apps.
  2. How to access local database:
    1. How to use SQLite for Windows Runtime and how to use sqlite-net library to manipulate SQLite database.
    2. How to use Extensible Storage Engine (ESE) or known as JET APIs.
  3. How to use WCF to access database in Windows Store apps.
  4. How to access remote database.

First, we talk about how to access local database in Windows Store apps. SQLite would be a good choice.


I find it hard to envision an app that doesn't have some kind of data store. Oh sure, I can see simple app's, but anything that does anything over time or "remembers" something, has any kind of state has to store it somewhere. And when building Windows Store app's in this version of Windows, database access is a little v1'ish. So until it gets better (assuming it does?) this kind of information is going to be important to have (and keep for future reference).

Tuesday, November 27, 2012

A journey in building a Calibre Windows 8/WinRT app, with a SQLite fun too...

Timdams's Blog - Writing a Calibre frontend for Windows8/WinRT using ‘SQLite for WinRT’

While developing a Windows Store frontend application for the Calibre ebookmanager, I’m hitting several bumps along the road. In this post I’ll explain some of the bumps and how I’ve tried to tackle them.

Basically they can be summarized as follows:

  • How to access a Sqlite database in a WinRT application
  • Circumvent file access limitation of SQLIte for WinRT
  • Load cover files of each book
  • Create incremental-loading Gridview using ISupportIncrementalLoading

Calibre is a great open source and free to use ebookmanager. It allows me to manage my ever-growing ebook-library and it supports lots of ebook-filetypes, including the ability to convert between the types.

My goal is to write a simple Windows Store application that acts as a frontend for the Calibre database. It will show my library in a visual appealing manner (aka TDPFAM, “The-design-principle-formerly-known-as-Metro”) and allow the user to rapidly query his database from anywhere in windows. At least that’s the idea. We’ll see where we end up (check here for a little video demonstrating the application I’m building).

Future follow ups on this project might be found here on the Calibre developer forum.



Getting SQLite for Windows Runtime

The Calibre program stores all its information inside the “metadata.db” file, located in the rootfolder of the books library. his file is a simple SQLite-database, so we’ll need to get the “SQLIte for Windows RT” extension. I’ll discuss the database and folder layout later on when we’ll actually need to access it.



Sqlite-net allows a more programmer-friendly way of accessing a Sqlite database, including the ability to query your db using linq. It can be found on github here. However, it is also available through NuGet ...

Get read-rights in calibre books database folder and load metadata.db

File access for WinRT applications is pretty tight. Basically your application only has full access to the app’s local folder. If you need access to other locations, you will need permission from the user. Because we need access to the Calibre  database folder and all subfolders, which can be situated anywhere on the computer and/or network, we will use the FolderPicker and immediately store the chosen folder to the StorageApplicationPermissions.FutureAccessList ...

Load book information from database

A Calibre library has a perfect straightforward layout. Basically there’s a root folder in which the metadata.db database is situated. This sqlite db contains all the books (meta)data, including the path where the actual book is situated on the file system...

Load cover images

The nice thing of the FutureAccessList is that we not only can access the folder itself, but also all folders underneath it. Since each cover image of a book (if there is one) is located in the same folder as the bookfiles, we can simply access them without any extra hassle....

Incremental loading (experimental)

Just for fun and experimenting I’ve taken a look to how the new WinRT GridView is supporting incremental loading. Since most Calibre libraries contains several hundreds or thousands of books, it might be overkill (and a big power drain) if we’d tried to load in the book information, especially the coverfiles, all at once. If the itemsource of a GridView implements ISupportIncrementalLoading, the GridView will be able to load in new data when it’s needed. ..."

I thought this a cool post in how it show him taking his personal interest and using that as a drive to get working on a Windows 8 app. And while I'm  a GoodReads guy, I still thought this post very interesting and useful. I mean I'm going to building a Win 8 app one of these days (no, really... um... really...) and I'd much rather learn from those who have gone before.

Thursday, June 28, 2012

PowerShell, SQLite and Google Drive [Oh my]

System Forensics - Powershell, SQLite and Google Drive

"I have been reading a lot about Microsoft’s Powershell lately because I am trying to automate some tasks at the office. It’s hard to beat their Active Directory modules that are integrated with Windows 7 and Windows Server 2008 R2.

While performing a bit of research I came across this site: It's a module for sqlite and powershell. According to the website it, "enables you to use SQLite databases from your PowerShell session by mounting the database as a drive. You can then use the standard provider cmdlets to perform CRUD operations on the database tables and records."

This was interesting for a few reasons. First, and as many of the readers know, SQLite is used a lot and as computer forensic analysts/incident responders we come across SQLite databases quite frequently. Applications such as Google Chrome, Google Drive, Firefox, and Dropbox all use SQLite databases.

I will walk you through the installation of the SQLite Powershell Provider module, and we will also take a look at some basic examples by extracting information from a SQLite database, which is used by Google’s new cloud storage solution, Google Drive.



CodePlex - SQLite PowerShell Provider

The SQLite PowerShell Provider enables you to use SQLite databases from your PowerShell session by mounting the database as a drive. You can then use the standard provider cmdlets to perform CRUD operations on the database tables and records.

The provider supports both persistent (on-disk) and transient (memory-only) SQLite databases. In addition, the provider is transaction-aware.

For more information and examples, please refer to the User's Guide in the Documentation.


With the recent excitement around SQLite in the Microsoft sphere (with last week's announcement of it being ported/supported on Windows 8 and Windows Phone 8) when I saw this I knew I had to share it. While this post isn't about the Win8 port, it is about how you can use PowerShell to work with your SQLite DB's, which I'm guess there's going to be allot of in the coming months.

Is this project active? The SQLite Provider 1.1 (beta) was just released Monday (June 25, 2012)...

With my EDD/ESI hat on, I also liked how this post also helped us spelunk the Google Drive SQLite DB.

Monday, September 22, 2008

SQLite for ADO.Net Released

ADO.NET 2.0 Provider for SQLite -

“Code merge with SQLite 3.6.3. Solves a couple different EF issues that were either giving inconsistent results or crashing the engine. Fixed the parsing of literal binaries in the EF SqlGen code. SQLite now passes nearly all the testcases in Microsoft's EF Query Samples application -- the exception being the datetimeoffset and time constants tests, and tests that use the APPLY keyword which are unsupported for now. Revamped the Compact Framework mixed-mode assembly. Tired of playing cat and mouse with the Compact Framework's support for mixed-mode assemblies. The CF build now requires that you distribute both the System.Data.SQLite library and the paired SQLite.Interop.XXX library. The XXX denotes the build number of the library. Implemented a workaround for Vista's overzealous caching by turning off FILE_FLAG_RANDOM_ACCESS for OS versions above XP. This is implemented as a custom (default override) VFS in the interop.c file, so no changes are made to the SQLite source code. Fixed some registry issues in the designer install.exe, which prevented some design-time stuff from working on the Compact Framework when .NET 3.5 was installed.


System.Data.SQLite (An open source ADO.NET provider for the SQLite database engine)

System.Data.SQLite is the original SQLite database engine and a complete ADO.NET 2.0 provider all rolled into a single mixed mode assembly.  It is a complete drop-in replacement for the original sqlite3.dll (you can even rename it to sqlite3.dll).  Unlike normal mixed assemblies, it has no linker dependency on the .NET runtime so it can be distributed independently of .NET.

Here is a brief overview of its features:

Complete ADO.NET 2.0 Implementation
The provider was written from scratch on VS2005/2008 specifically for ADO.NET 2.0, using all the most recent changes to the ADO.NET framework.  That includes full DbProviderFactory support, automatic distributed transaction enlistment, connection pooling, extensive schema support, Entity Framework support and more.

Supports the Full and Compact .NET Framework as well as native C/C++
Whether you're programming in .NET or straight C/C++ on the desktop or a mobile device, we've got a build for you.

Mono support
A managed-only version of the provider is also available that works on Mono against the official SQLite library from  Requires 3.6.1 or higher.

Support for the ADO.NET 3.5 Entity Framework
SQLite's EF provider is still in beta for now, but go ahead and kick the tires!


Google’s Chrome using SQLite has reignited my interest in this project making this ADO.Net provider for SQLite seem like a no-brainer place for me to start…

This is now a pretty active project, with releases coming quite often, so if you are interested, subscribe to the SourceForge release feed to keep up.


Related Past Post XRef:
Your Three Minute Guide to SQLite and .Net

Thursday, September 04, 2008

Chrome Notes: Bookmarks, where oh where are you?

So like most of you who are probably reading this, I’ve been playing with Google’s new browser, Chrome.

In this post, I’ll not be going into the differences between it and IE, nor comments on its EULA, its cool factor, etc. What I’ll be talking about is some things, some under the hood things, I’ve looked at.

Some background first.

One of the features of IE that I’ve come to rely on is that I can very easily sync my Favorites. This lets me easily, quickly, with no mus of fus, keep my fav’s on three machines all in sync (via Mesh). It’s simple, I don’t have to change my IE usage habits, etc. Sure it’s not very Web 2.0’ish, but that doesn’t matter to me. What I want is to just have my favorites be the same on my machines… Without that, I’m just not a happy web browser camper…

So one of the first things I looked for with Chrome is how I would sync it’s Bookmarks (aka Favorites).

a) Where are the Chrome Bookmarks stored?

The Chrome Bookmarks are stored in a file called “History” (no extension).

On my systems, I found the History file in the following paths:

C:\Users\[YourUserName]\AppData\Local\Google\Chrome\User Data\Default\

C:\Documents and Settings\[YourUserName]\Local Settings\Application Data\Google\Chrome\User Data\Default\

b) What is the History file?

The History file is a SQLite v3 database.

c) Okay, how I can read/open/view the History file

There are a number of SQLite browsing tools available. The one I used, which was the first free one I found, was the SQLite Database Browser. It’s a fast download and can be run without installation…

Once you have a browsing tool, open the History file…


d) So where are the Bookmarks?

The magic is the “starred” table. This table is where the bookmarks are stored, what folder they are in, their order, etc.


The actual URLs are stored in the “urls” table (funny that… ;)


To get at the URL’s for them you join against the “urls” table.

SELECT starred.title, urls.url
FROM starred
INNER JOIN urls ON = starred.url_id


Okay… Now what?

Now what I’m thinking is that I need to write a utility that will export data from the History DB, put it into a file, sync that file (or post the data to a cloud storage area like S3 or SSDS?) and then write a import utility that will slurp in that file/data… Maybe use the starred.Date_Added column to intelligently add new items. But how to track deletes? hum…

Also I need to look around for the Chrome SDK/API/Doc’s etc to see if I’m being a lamer and if there’s a much easier way to sync/import/export bookmarks…  ;)

Tuesday, January 15, 2008

Your Three Minute Guide to SQLite and .Net - SQLite on .NET - Get up and running in 3 minutes.

"You found it! The quick and dirty guide to setting up SQLite with .Net in 3 minutes. Small, fast, and ass-kicking like a transactional Jackie Chan. At least that’s what this campy image created just for this post says. (Shout out to righteous graphics dude Brian Cook for the sweet photoshoppery)


SQLite is great for that small-mediumish application we already spec-ed out so lets get into the quick and dirty setup to get this hog rockin’ on .NET.


Mike [Not related, but us Duncan's have to stick together...  ;)  ] has put together a very cool three minute guide to downloading and using SQLite with .Net.

While if I need a local DB I'd probably start with SQL CE, it's still good having this information as there have been some SQLite DB's I've wanted to access.

In looking for LINQ to SQLite information, I came across this site, System.Data.SQLite, which looks pretty cool too.

"System.Data.SQLite is an enhanced version of the original SQLite database engine.  It is a complete drop-in replacement for the original sqlite3.dll (you can even rename it to sqlite3.dll).  It has no linker dependency on the .NET runtime so it can be distributed independently of .NET, yet embedded in the binary is a complete ADO.NET 2.0 provider for full managed development.


And there's forum chat that LINQ to SQLite may be coming...