DZone Snippets: sqlite code

Rails MySQL/SQLite convenience methods

timmorgan (Tim Morgan) — Mon, 18 Feb 2008 02:57:36 GMT

Usage:



  Person.find :all, :conditions => ["#{sql_year 'birthday'} >= ?", year]



SQLITE = true # or false



def sql_concat(*args)

  SQLITE ? args.join(' || ') : "CONCAT(#{args.join(', ')})"

end



def sql_lcase(expr)

  SQLITE ? "LOWER(#{expr})" : "LCASE(#{expr})"

end



def sql_year(expr)

  SQLITE ? "CAST(STRFTIME('%y', #{expr}) as 'INTEGER')" : "YEAR(#{expr})"

end



def sql_month(expr)

  SQLITE ? "CAST(STRFTIME('%m', #{expr}) as 'INTEGER')" : "MONTH(#{expr})"

end



def sql_day(expr)

  SQLITE ? "CAST(STRFTIME('%d', #{expr}) as 'INTEGER')" : "DAY(#{expr})"

end



def sql_now

  SQLITE ? "CURRENT_TIMESTAMP" : "NOW()"

end

Crack open Firefox's new Places sqlite database with ActiveRecord

sikelianos (Zeke Sikelianos) — Mon, 17 Dec 2007 23:09:35 GMT



require "rubygems"

require "active_record"

require "active_support"



ActiveRecord::Base.establish_connection(

	:adapter => "sqlite3",

	:database => "places.sqlite"

)



class MozHistoryvisit < ActiveRecord::Base

	belongs_to :place, :class_name => "MozPlaces", :foreign_key => "place_id"

end



class MozPlaces < ActiveRecord::Base

end



p Time.now.yesterday

from = Time.now.yesterday.to_i * 1000000



MozHistoryvisit.find(:all, :conditions => ["visit_date > ?", from]).each do |h|

	place = h.place

	puts place.title

	puts place.url

	puts place.visit_count

	puts

end

Create an SQLite Database from an Excel Workbook with Ruby

dmullet (David Mullet) — Wed, 27 Jun 2007 14:16:07 GMT

From the Ruby on Windows blog.

Here's a brief, unpolished snippet of code that reads data from an open Excel workbook and creates an SQLite database with a table for each worksheet in the Excel workbook:



require 'win32ole'

require 'sqlite3'



#   Connect to a running instance of Excel

xl = WIN32OLE.connect('Excel.Application')

#   Get the active workbook

wb = xl.ActiveWorkbook

#   Create the SQLite3 database

db = SQLite3::Database.new('excel.db')

#   Create a database table for each worksheet 

#   in the workbook

wb.Worksheets.each do |ws|

    #   Grab all values from worksheet into a 

    #   2-dimensional array

    data = ws.UsedRange.Value

    #   Grab first row of data to use as field names

    field_names = data.shift

    #   Create database table using worksheet name and 

    #   field names

    db.execute("CREATE TABLE [#{ws.Name}] \

        ( #{field_names.join(',')} );")

    #   For each row of data...

    data.each do |row|

        #   ...single-quote all field values...

        row.collect! { |f| f = "'" + f.to_s + "'" }

        #   ...and insert a new record into the 

        #   database table

        db.execute("INSERT INTO [#{ws.Name}] VALUES \

            ( #{row.join(',')} );")

    end

end

Further discussion can be found here.

Add a concat() aggregate function to SQLite

debedb (http://www.hrum.org) — Fri, 23 Mar 2007 03:27:46 GMT

I often need string concatenation to behave just like an aggregate function.
Once again I find a need to do that in SQLite, and to do that without recompiling
SQLite for every platform we distribute for...



#include 

#include "sqlite3ext.h"

SQLITE_EXTENSION_INIT1





typedef struct SCtx SCtx;

struct SCtx {

  int rowCnt;

  int charCnt;

  char *result;

};



static void concat_step(sqlite3_context* ctx, int argc, sqlite3_value**argv) {



  SCtx *p = (SCtx *) sqlite3_aggregate_context(ctx, sizeof(*p));



  char *sep = sqlite3_value_text(argv[1]);



  char *txt = sqlite3_value_text(argv[0]);



  if (p->rowCnt) {

    char *txt2 = malloc(strlen(txt) + strlen(sep) + 1);

    strcpy(txt2,sep);

    strcat(txt2,txt);

    txt = txt2;

  }



  //  printf("%d. Txt: [%s] len %d\n", p->rowCnt, txt, strlen(txt));



  int len = strlen(txt);



  if (!p->result) {

    p->result = malloc(len + 1);

    strcpy(p->result, txt);

  } else {

    p->result = realloc(p->result, strlen(p->result) + len + 1);

    strcat(p->result,txt);

  }

  //  printf ("intermediate [%s]\n", p->result);

  p->rowCnt++;

}



static void concat_final(sqlite3_context* ctx,

                         int argc,

                         sqlite3_value** argv) {



  SCtx *p = (SCtx *) sqlite3_aggregate_context(ctx, sizeof(*p));

  //  printf("Finally: %s\n", p->result);

  sqlite3_result_text(ctx,  p->result, strlen(p->result), NULL);

}



int sqlite3_extension_init(

  sqlite3 *db,

  char **pzErrMsg,

  const sqlite3_api_routines *pApi

){

  SQLITE_EXTENSION_INIT2(pApi)

  sqlite3_create_function(db, "concat", 2, SQLITE_ANY, 0, NULL, concat_step, concat_final);

  return 0;

}

I compiled this with the following (here, ./src is the SQLite code - I used
http://www.sqlite.org/sqlite-source-3_3_13.zip).



gcc -fpic -c agg.c -I./src

gcc -shared -Wl,-soname,libagg.so -o libagg.so agg.o

And here's how it works:



sqlite> CREATE TABLE test (animals VARCHAR, interjection VARCHAR);



sqlite> insert into test (animals, interjection) values ('lions', 'oh my');



sqlite> insert into test (animals, interjection) values ('tigers', 'oh my'); 



sqlite> insert into test (animals, interjection) values ('bears', 'oh my');



sqlite> select load_extension('./libagg.so');



sqlite> select concat(animals, ' and '), interjection from test group by interjection;



sqlite> lions and tigers and bears|oh my

See also:
1. http://www.sqlite.org/cvstrac/wiki?p=LoadableExtensions
2. http://souptonuts.sourceforge.net/readme_sqlite_tutorial.html
3. http://www.sqlite.org/capi3ref.html

Optimize Aperture database

willcodeforfoo () — Mon, 05 Mar 2007 14:43:20 GMT



sqlite3 Pictures/Aperture\ Library.aplibrary/Aperture.aplib/Library.apdb vacuum

Simple Ruby ActiveRecord example

mikewilsonuk (Mike Wilson) — Wed, 06 Dec 2006 15:57:18 GMT

Using Ruby ActiveRecord with an in-memory SQLite database. A nice simple example of this wonderful library.



require 'active_record'



ActiveRecord::Base.logger = Logger.new(STDERR)

ActiveRecord::Base.colorize_logging = false



ActiveRecord::Base.establish_connection(

    :adapter => "sqlite3",

    :dbfile  => ":memory:"

)



ActiveRecord::Schema.define do

    create_table :albums do |table|

        table.column :title, :string

        table.column :performer, :string

    end



    create_table :tracks do |table|

        table.column :album_id, :integer

        table.column :track_number, :integer

        table.column :title, :string

    end

end



class Album < ActiveRecord::Base

    has_many :tracks

end



class Track < ActiveRecord::Base

    belongs_to :album

end



album = Album.create(:title => 'Black and Blue',

    :performer => 'The Rolling Stones')

album.tracks.create(:track_number => 1, :title => 'Hot Stuff')

album.tracks.create(:track_number => 2, :title => 'Hand Of Fate')

album.tracks.create(:track_number => 3, :title => 'Cherry Oh Baby ')

album.tracks.create(:track_number => 4, :title => 'Memory Motel ')

album.tracks.create(:track_number => 5, :title => 'Hey Negrita')

album.tracks.create(:track_number => 6, :title => 'Fool To Cry')

album.tracks.create(:track_number => 7, :title => 'Crazy Mama')

album.tracks.create(:track_number => 8,

    :title => 'Melody (Inspiration By Billy Preston)')



album = Album.create(:title => 'Sticky Fingers',

    :performer => 'The Rolling Stones')

album.tracks.create(:track_number => 1, :title => 'Brown Sugar')

album.tracks.create(:track_number => 2, :title => 'Sway')

album.tracks.create(:track_number => 3, :title => 'Wild Horses')

album.tracks.create(:track_number => 4,

    :title => 'Can\'t You Hear Me Knocking')

album.tracks.create(:track_number => 5, :title => 'You Gotta Move')

album.tracks.create(:track_number => 6, :title => 'Bitch')

album.tracks.create(:track_number => 7, :title => 'I Got The Blues')

album.tracks.create(:track_number => 8, :title => 'Sister Morphine')

album.tracks.create(:track_number => 9, :title => 'Dead Flowers')

album.tracks.create(:track_number => 10, :title => 'Moonlight Mile')



puts Album.find(1).tracks.length

puts Album.find(2).tracks.length



puts Album.find_by_title('Sticky Fingers').title

puts Track.find_by_title('Fool To Cry').album_id

Making SQLITE/SQLITE3 executable scripts.

ttmrichter (Michael T. Richter) — Mon, 04 Dec 2006 18:54:28 GMT

Use "here document" statements to build complex script files with embedded SQL statements via the sqlite/sqlite3 utility.



#! /usr/bin/env bash



# execute some bash scripting commands here



sqlite3 mydatabase <SELECT * 

  FROM mytable 

  WHERE somecondition='somevalue';

SQL_ENTRY_TAG_1



# execute other bash scripting commands here



sqlite3 mydatabase <SELECT *

  FROM myothertable

  WHERE someothercondition='someothervalue';

SQL_ENTRY_TAG_2

Note that being in a bash script means that you can expand $-variables inside the SQL code directly. This is, however, not advised unless you can be sure that only trusted, competent people will run your code. Otherwise you'll be facing SQL injection attacks.

DB Class for SQLite

jdmays () — Tue, 05 Sep 2006 00:19:45 GMT

// description of your code here



// insert code here..

//examples for handling the class

private void button1_Click(object sender, EventArgs e)
{
string str = Path.GetDirectoryName(Application.ExecutablePath);
handledb mydb = new handledb(str + "\\Trivial.db3");
mydb.AddWord(textBox1.Text);
textBox1.Clear();
}

private void button2_Click(object sender, EventArgs e)
{
string str = Path.GetDirectoryName(Application.ExecutablePath);
dbstuff rt = new dbstuff(str + "\\Trivial.db3");
DataTable dt = rt.ShowAll();
listBox1.DisplayMember = "TWord";
listBox1.DataSource = dt;

}

private void listBox1_SelectedIndexChanged(object sender, EventArgs e)
{
//textBox2.Text = listBox1.SelectedItems[0].ToString();
//textBox2.Text = listBox1.Items[0].ToString();
textBox2.Text = listBox1.SelectedValue.ToString();

}

private void button3_Click(object sender, EventArgs e)
{
string str = Path.GetDirectoryName(Application.ExecutablePath);
dbstuff rt = new dbstuff(str + "\\Trivial.db3");
rt.RemoveWord(textBox2.Text);
}

//------------ the actual class ----------------

class handledb
{

private SQLiteConnection sqconn;
private string dbpath;

private bool IsNumber(string word)
{
int i;
bool f = int.TryParse(word, out i);
if (f)
return f;
for (i = 0; i < word.Length; i++)
if (char.IsDigit(word[i]))
return true;
return false;
}
private bool IsTrivial(string word)
{
string strSQL = "SELECT * FROM Trivial WHERE Tword='"+word.ToUpper()+"'";
SQLiteCommand sqc = new SQLiteCommand(strSQL, sqconn);
SQLiteDataReader sqr = sqc.ExecuteReader();
bool result = sqr.Read();
sqr.Close();
return result;
}
private void OpenConnection(string dbname)
{

string strconn = "Data Source="+dbpath+";Version=3;";
sqconn = new SQLiteConnection(strconn);
sqconn.Open();
}

public handledb(string dbpath)
{

FileInfo fi = new FileInfo(dbpath);
if (!fi.Exists)
CreatDataBase();
else
OpenConnection();
}

public void CreatDataBase()
{
FileStream fs = File.Create(dbpath);
fs.Close();
OpenConnection();
string strSQL = "Create Table tblitem (" +
"TWord nvarchar(20) PRIMARY KEY NOT NULL" +
")";
SQLiteCommand sqc = new SQLiteCommand(strSQL, sqconn);
sqc.ExecuteNonQuery();
}
public void AddWord(string word)
{
if (IsTrivial(word))
return;
string strSQL = "INSERT INTO Trivial (TWord) VALUES('"+word.ToUpper()+"')";
SQLiteCommand sqc = new SQLiteCommand(strSQL, sqconn);
sqc.ExecuteNonQuery();
}
public void RemoveWord(string word)
{
string strSQL = "DELETE FROM Trivial WHERE Tword='" + word.ToUpper() + "'";
SQLiteCommand sqc = new SQLiteCommand(strSQL, sqconn);
sqc.ExecuteNonQuery();
}
public DataTable ShowAll()
{
string strSQL = "SELECT * FROM Trivial";
SQLiteDataAdapter sqd = new SQLiteDataAdapter(strSQL, sqconn);
DataTable dt = new DataTable();
sqd.Fill(dt);
return dt;
}
public string DBName
{
get { return dbpath; }
}
}
//end class
}

//--------------

Check SQLite version used by DBD::SQLite

peter (Peter Cooperx) — Sun, 02 Jul 2006 06:34:05 GMT



use DBI;

$db = DBI->connect("dbi:SQLite:dbname=x", "", "");

print $db->{sqlite_version};

Dump a SQLite database

timmorgan (Tim Morgan) — Thu, 01 Jun 2006 17:19:40 GMT



sqlite x.db .dump > output.sql