sqlite code

Never been to DZone Snippets before?

Snippets is a public source code repository. Easily build up your personal collection of code snippets, categorize them with tags / keywords, and share them with the world

« Newer Snippets

Older Snippets »

Showing 1-10 of 15 total RSS

Rails MySQL/SQLite convenience methods

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

to ruby rails mysql sqlite activerecord by timmorgan on Feb 17, 2008

Crack open Firefox's new Places sqlite database with ActiveRecord

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

to rails firefox sqlite activerecord places by sikelianos on Dec 17, 2007

Create an SQLite Database from an Excel Workbook with Ruby

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.

to ruby sqlite excel by dmullet on Jun 27, 2007

Add a concat() aggregate function to SQLite

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 <stdlib.h>
#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

to c sql sqlite aggregate by debedb on Mar 22, 2007 1 Comments

Optimize Aperture database

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

to osx sqlite aperture by willcodeforfoo on Mar 05, 2007

Simple Ruby ActiveRecord example

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

to ruby sql database sqlite activerecord by mikewilsonuk on Dec 06, 2006

Making SQLITE/SQLITE3 executable scripts.

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 <<SQL_ENTRY_TAG_1
SELECT * 
  FROM mytable 
  WHERE somecondition='somevalue';
SQL_ENTRY_TAG_1

# execute other bash scripting commands here

sqlite3 mydatabase <<SQL_ENTRY_TAG_2
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.

to bash shell database sqlite script program sqlite3 by ttmrichter on Dec 04, 2006

DB Class for SQLite

// 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
}

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

to sqlite by jdmays on Sep 04, 2006

Check SQLite version used by DBD::SQLite

use DBI;
$db = DBI->connect("dbi:SQLite:dbname=x", "", "");
print $db->{sqlite_version};

to perl sqlite dbi by peter on Jul 02, 2006

Dump a SQLite database

sqlite x.db .dump > output.sql

to database sqlite by timmorgan on Jun 01, 2006