We closed this forum 18 June 2010. It has served us well since 2005 as the ALPHA forum did before it from 2002 to 2005. New discussions are ongoing at the new URL http://forum.processing.org. You'll need to sign up and get a new user account. We're sorry about that inconvenience, but we think it's better in the long run. The content on this forum will remain online.
IndexProgramming Questions & HelpIntegration › SQLite with big database
Page Index Toggle Pages: 1
SQLite with big database (Read 3451 times)
SQLite with big database
Jun 13th, 2010, 9:48am
 
hi everybody,

i want to search the r,g,b values in a database and compare it to the rgb values of a pixel. Now the problem with this is that i cannot find a easy way to search in processing tru this sqlite database i've converted. It shows the values correctly and i can view everything in the database. It's a database of 300 MB, so putting everything in a string array is not realy a option.
Code:

// fjenett 20081129

import de.bezier.data.sql.*;
String[] rString = new String[0];
String[] gString = new String[0];
String[] bString = new String[0];
String[] colourString = new String[0];

SQLite db;

void setup()
{
   size( 100, 100 );

   db = new SQLite( this, "text.db" );  // open database file

   if ( db.connect() )
   {
 // list table names
 db.query( "SELECT name as \"Name\" FROM SQLITE_MASTER where type=\"table\"" );
 
 while (db.next())
 {
println( db.getString("Name")+" naam" );
 }
 
 // read all in table "table_one"
 db.query( "SELECT * FROM answers" );
 
 while (db.next())
 {
   // String user = db.getString("user_id") ;
String r = db.getString("r") ;
   //  println( db.getString("g") );
  // println( db.getString("b") );
//  println( db.getString("colorname") );
    // println( db.getInt("field_two") );
String g = db.getString("g") ;
String b = db.getString("b") ;
String colorname = db.getString("colorname") ;
//     rString = append(rString, r);
// gString = append(gString, g);
// bString = append(bString, b);
//  colourString = append(colourString, colorname);
 }
   }
}
void draw(){
 
println(rString[0]);
}


the database is from here: http://blog.xkcd.com/2010/05/03/color-survey-results/
Re: SQLite with big database
Reply #1 - Jun 13th, 2010, 1:02pm
 
anybody? Sad

I'm searching for a way to insert a query to search one value in the database. something like: SELECT search as \"search\" FROM answers WHERE MATCH

i've asked my teacher and it should be something like this:
Code:

int searchval = 98;
int range = 10;
int searchstart = searchval - range;
int searchend = searchval + range;
String query = "SELECT colorname as \"Colorname\" FROM answers WHERE (r >= '"+searchstart+"') AND (r <= '"+searchend+"')";
db.query(query);
println(db.getString(("Colorname")));
Re: SQLite with big database
Reply #2 - Jun 14th, 2010, 8:15am
 
Amusing database.
I wanted to play with it a bit too, so I recycled an old sketch I had with MySQL and adapted it to this data.
For the record, I created the database using the line:
cat D:\Archives\mainsurvey_sqldump.txt | sqlite3 XKCD_Colors

So I made a quick sketch out of that.
[EDIT] I changed the sketch as my requests weren't doing what I expected... I allow to stop the sketch and choose one predefined request. A more sophisticated version would use GUI to choose intervals...
I have to split the sketch in two parts.
Code:
import de.bezier.data.sql.*;
import java.sql.Timestamp;

static final String USERS_TABLE_NAME = "users";
static final String ANSWERS_TABLE_NAME = "answers";
static final int CHECK_INTERVAL = 100; // milliseconds
static final int MESSAGE_DISPLAY_NB = 35;
int lastCheck = - CHECK_INTERVAL - 1;
boolean bStop;

SQLite sqlite;
PFont fontHeader;
PFont fontMessage;

int userNb;
int answerNb;
ArrayList colorNames_red;
ArrayList colorNames_green;
ArrayList colorNames_blue;
int lastMessageDisplayedIndex;
int totalMessageNb;

void setup()
{
 size(800, 700);
 smooth();
 fontHeader = createFont("Arial", 30);
 fontMessage = createFont("Verdana", 16);

 Connect();
 GetPureColors();
}

void stop()
{
 println("Stopping connection");
 sqlite.close();
}

void draw()
{
 int time = millis();
 if (!bStop && time - lastCheck > CHECK_INTERVAL)
 {
   lastCheck = time;

   background(128);
   textFont(fontHeader, 36);
   fill(#FFCC88);
   text("XKCD Colors", 10, 36);
//    ShowCount(); // Slow!

   if (lastMessageDisplayedIndex < totalMessageNb - 1)
   {
lastMessageDisplayedIndex++;
   }
   else
   {
lastMessageDisplayedIndex = 0;
   }

   ShowIndex(lastMessageDisplayedIndex);
   for (int i = 0; i < MESSAGE_DISPLAY_NB; i++)
   {
int idx = 1 + lastMessageDisplayedIndex - MESSAGE_DISPLAY_NB + i;
if (idx < 0)
 continue;
ColorName cn = (ColorName) colorNames_red.get(idx);
ShowColorName(cn, 0, i);
cn = (ColorName) colorNames_green.get(idx);
ShowColorName(cn, 1, i);
cn = (ColorName) colorNames_blue.get(idx);
ShowColorName(cn, 2, i);
   }
 }
}

void mousePressed()
{
 bStop = true;
}

void mouseReleased()
{
 bStop = false;
}

void keyPressed()
{
 if (key == 'p' || key == 'P')
 {
   GetPureColors();
 }
 else if (key == 'l' || key == 'L')
 {
   GetLightColors();
 }
 else if (key == 'o' || key == 'O')
 {
   GetOtherColors();
 }
 else if (key == 'd' || key == 'D')
 {
   GetDifferentColors();
 }
}

void DoStats()
{
 totalMessageNb = min(
colorNames_red.size(),
colorNames_green.size(),
colorNames_blue.size()
 );
 if (totalMessageNb == 0)
 {
   println("Not much to show!");
   exit();
 }
 lastMessageDisplayedIndex = 0;
}

void GetPureColors()
{
 colorNames_red = GetColorNames(255, 0, 0, -20, 0, -20);
 colorNames_green = GetColorNames(0, -20, 255, 0, 0, -20);
 colorNames_blue = GetColorNames(0, -20, 0, -20, 255, 0);
 DoStats();
}

void GetLightColors()
{
 colorNames_red = GetColorNames(255, 0, 255, 20, 255, 20);
 colorNames_green = GetColorNames(255, 20, 255, 0, 255, 20);
 colorNames_blue = GetColorNames(255, 20, 255, 20, 255, 0);
 DoStats();
}

void GetOtherColors()
{
 colorNames_red = GetColorNames(255, 0, 127, -1, 127, -1);
 colorNames_green = GetColorNames(127, -1, 255, 0, 127, -1);
 colorNames_blue = GetColorNames(127, -1, 127, -1, 255, 0);
 DoStats();
}

void GetDifferentColors()
{
 colorNames_red = GetColorNames(255, 0, 128, 1, 128, 1);
 colorNames_green = GetColorNames(128, 1, 255, 0, 128, 1);
 colorNames_blue = GetColorNames(128, 1, 128, 1, 255, 0);
 DoStats();
}

void ShowIndex(int idx)
{
 textFont(fontHeader, 20);
 fill(#AACCFF);
 text(idx + "/" + totalMessageNb, width / 2 - 100, 20);
}

void ShowCount()
{
 if (userNb == 0)
 {
   userNb = GetCount(USERS_TABLE_NAME);
   answerNb = GetCount(ANSWERS_TABLE_NAME);
 }
 textFont(fontHeader, 20);
 fill(#AACC88);
 text("Number of users: " + userNb, width / 2, 20);
 text("Number of answers: " + answerNb, width / 2, 40);
}

void ShowColorName(ColorName colorName, int col, int pos)
{
 int xPos = 20 + col * (width - 20) / 3;
 int yPos = 100 + pos * 16;
 textFont(fontMessage);
 fill(color(colorName.r, colorName.g, colorName.b));
 text(colorName.name, xPos, yPos);
}
Re: SQLite with big database
Reply #3 - Jun 14th, 2010, 9:00am
 
Second part (another tab):
Code:
/*=== ColorName Class ===*/

class ColorName
{
 String name;
 int r;
 int g;
 int b;

 ColorName(String _name, int _r, int _g, int _b)
 {
   name = _name;
   r = _r;
   g = _g;
   b = _b;
 }
}

/*=== SQLite Section ===*/

void Connect()
{
 sqlite = new SQLite(this, "D:/Dev/PhiLhoSoft/XKCD_Colors");
 if (!sqlite.connect())
 {
   println("Cannot connect to database!");
   exit();
 }
}

int GetCount(String tableName)
{
 sqlite.query("SELECT COUNT(*) FROM " + tableName);
 sqlite.next();
 return sqlite.getInt(1);
}

// Unlikely to return a result...
ArrayList GetColorNames(color rgb)
{
 int r = (rgb >> 16) & 0xFF;
 int g = (rgb >> 8) & 0xFF;
 int b = rgb & 0xFF;
 sqlite.query("SELECT colorname FROM " + ANSWERS_TABLE_NAME +
" WHERE r=" + r + " AND g=" + g + " AND b=" + b
 );
 ArrayList colorNames = new ArrayList();
 while (sqlite.next())
 {
   String cn = sqlite.getString(1);
   colorNames.add(cn);
 }
 println("For " + hex(r, 2) + hex(g, 2) + hex(b, 2) +
" we have " + colorNames.size() + " color names");
 return colorNames;
}

// Wider
ArrayList GetColorNames(int r, int tr, int g, int tg, int b, int tb)
{
 String request = "SELECT colorname, r, g, b FROM " + ANSWERS_TABLE_NAME + " WHERE ";
 if (tr == 0) request += "r=" + r;
 else if (tr < 0) request += "r<" + (r - tr);
 else if (tr > 0) request += "r>" + (r - tr);
 request += " AND ";

 if (tg == 0) request += "g=" + g;
 else if (tg < 0) request += "g<" + (g - tg);
 else if (tg > 0) request += "g>" + (g - tg);
 request += " AND ";

 if (tb == 0) request += "b=" + b;
 else if (tb < 0) request += "b<" + (b - tb);
 else if (tb > 0) request += "b>" + (b - tb);

 println(request);
 sqlite.query(request);
 ArrayList colorNames = new ArrayList();
 while (sqlite.next())
 {
   String cn = sqlite.getString(1);
   int rr = sqlite.getInt(2);
   int rg = sqlite.getInt(3);
   int rb = sqlite.getInt(4);
   colorNames.add(new ColorName(cn, rr, rg, rb));
 }
 println("For " + hex(r, 2) + hex(g, 2) + hex(b, 2) +
" we have " + colorNames.size() + " color names");
 return colorNames;
}

It might get you started for your own queries.
Re: SQLite with big database
Reply #4 - Jun 14th, 2010, 9:03am
 
Oh, this is a pretty cool Database. I not to forget i love xkcd
Re: SQLite with big database
Reply #5 - Jun 15th, 2010, 4:35am
 
i solved it this way:

for searching the database for RGB colours:
Code:

int cp = get(counter, 0);
float redCol = red(get(counter, 0));
float greenCol = green(get(counter, 0));
float blueCol = green(get(counter, 0));
//mySQL doorzoeken voor waardes
int range = 2;
int searchstart = int(redCol) - range;
int searchend = int(redCol) + range;
int greenstart = int(greenCol) - range;
int greenend = int(greenCol) + range;
int bluestart = int(blueCol) - range;
int blueend = int(blueCol) + range;
String query = "SELECT * FROM answers WHERE (r >= "+searchstart+") AND (r <= "+searchend+") AND (g >= "+greenstart+") AND (g <= "+greenend+") AND (b >= "+bluestart+") AND (b <= "+blueend+")";
db.query(query);
String colorname = db.getString(("colorname"));
Page Index Toggle Pages: 1