Re: [sqlite] Storing a wav file in the database

2010-05-03 Thread Simon Slavin

On 4 May 2010, at 1:45am, Luciano de Souza wrote:

> Suppose I have a file called "audio.wav" and I want to store it inside a 
> database. If I correctly understood, I could create a table like that:
> 
> create table test (
> id integer primary key,
> name text not null,
> file blob not null
> );
> 
> I have never dealt with blob fields, so perhaps I need to read something 
> before. But I don't know where? It is not only a doubt about Sqlite, but 
> mainly about blob files.

SQLite handles BLOB fields the same as the other databases: they're 
variable-length fields which are guaranteed safe for any contents at all: null 
characters, escape codes, quotes, 0xFF characters can appear anywhere in the 
field.

> Is it simple to embed "audio.wav" in the database? After doing it, I can 
> delete the original file, knowing that inside the database there is not only 
> the reference to file, but the own file? And how to save it again in a file?

Yep.  You've got the right idea: store the filename in a text field and its 
contents in a BLOB field.  Your only problem is making sure that you always 
handle the contents as data and never accidentally as text.  Handling contents 
as text risks having zero-byte termination problems.

> What I imagine is that we need a handle for the file, we call a Sqlite 
> function with this handle and get a pointer wich we save in the blob field. 
> Likely there is a function to do the opposit operation.

I'm not sure whether you have this bit right.  SQLite does not understand files 
or file handles at all.  A BLOB field contains bytes (octets), any idea that 
those are the contents of a file are your own problem.  If you want to use a 
BLOB to represent a file it is the responsibility of your own software to read 
the bits from the original file and store them in the BLOB field, then to 
reverse the process when you want the bits again.  You can do this all at once 
by binding a BLOB like any other type of field, or you can use the special 
routines that handle a BLOB handle like a file handle like this:

http://www.sqlite.org/c3ref/blob_write.html

If you're just handling short sound files there's no need to do it gradually: 
just read the whole thing in one go.  The special routines are for cases where 
you might run into memory capacity problems or need to make sure that a single 
operating doesn't take too long.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] Modeling SQLite databases

2010-05-03 Thread Kevin
I would suggest starting by looking into
http://www.sqlite.org/cvstrac/wiki?p=ManagementTools 



regards,

Kevin

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


[sqlite] Storing a wav file in the database

2010-05-03 Thread Luciano de Souza
Hi listers,

Suppose I have a file called "audio.wav" and I want to store it inside a 
database. If I correctly understood, I could create a table like that:

create table test

(

id integer primary key,

name text not null,

file blob not null

);

I have never dealt with blob fields, so perhaps I need to read something 
before. But I don't know where? It is not only a doubt about Sqlite, but mainly 
about blob files.

Is it simple to embed "audio.wav" in the database? After doing it, I can delete 
the original file, knowing that inside the database there is not only the 
reference to file, but the own file? And how to save it again in a file?

What I imagine is that we need a handle for the file, we call a Sqlite function 
with this handle and get a pointer wich we save in the blob field. Likely there 
is a function to do the opposit operation.

Everything I find in Google presumes I have alredy understood ow blob fields 
works.

Best regards,

Luciano
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] SQLite datatypes

2010-05-03 Thread Paul Rigor (uci)
HEre's the doc:

http://www.sqlite.org/datatype3.html

Cheers,
Paul

On Mon, May 3, 2010 at 12:17 PM, Alan Harris-Reid <
aharrisr...@googlemail.com> wrote:

> Hi there,
>
> When creating a table in SQLite, I often get confused when confronted
> with all the possible datatypes which imply similar contents, so could
> anyone tell me the difference between the following data-types?
>
>INT, INTEGER, SMALLINT, TINYINT
>DEC, DECIMAL
>LONGCHAR, LONGVARCHAR
>DATETIME, SMALLDATETIME
>
> Is there some documentation somewhere which lists the min./max.
> capacities of the various data-types?  For example, I guess smallint
> holds a larger maximum value than tinyint, but a smaller value than
> integer, but I have no idea of what these capacities are.
>
> Any help would be appreciated.
>
> Alan Harris-Reid
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Paul Rigor
Pre-doctoral BIT Fellow and Graduate Student
Institute for Genomics and Bioinformatics
Donald Bren School of Information and Computer Sciences
University of California, Irvine
http://www.ics.uci.edu/~prigor
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] SQLite datatypes

2010-05-03 Thread P Kishor
On Mon, May 3, 2010 at 2:17 PM, Alan Harris-Reid
 wrote:
> Hi there,
>
> When creating a table in SQLite, I often get confused when confronted
> with all the possible datatypes which imply similar contents, so could
> anyone tell me the difference between the following data-types?
>
>    INT, INTEGER, SMALLINT, TINYINT
>    DEC, DECIMAL
>    LONGCHAR, LONGVARCHAR
>    DATETIME, SMALLDATETIME
>
> Is there some documentation somewhere which lists the min./max.
> capacities of the various data-types?  For example, I guess smallint
> holds a larger maximum value than tinyint, but a smaller value than
> integer, but I have no idea of what these capacities are.
>

Read the following carefully http://www.sqlite.org/datatype3.html


> Any help would be appreciated.
>
> Alan Harris-Reid
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Puneet Kishor http://www.punkish.org
Carbon Model http://carbonmodel.org
Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
Nelson Institute, UW-Madison http://www.nelson.wisc.edu
---
Assertions are politics; backing up assertions with evidence is science
===
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


[sqlite] SQLite datatypes

2010-05-03 Thread Alan Harris-Reid
Hi there,

When creating a table in SQLite, I often get confused when confronted 
with all the possible datatypes which imply similar contents, so could 
anyone tell me the difference between the following data-types?

INT, INTEGER, SMALLINT, TINYINT
DEC, DECIMAL
LONGCHAR, LONGVARCHAR
DATETIME, SMALLDATETIME

Is there some documentation somewhere which lists the min./max. 
capacities of the various data-types?  For example, I guess smallint 
holds a larger maximum value than tinyint, but a smaller value than 
integer, but I have no idea of what these capacities are.

Any help would be appreciated.

Alan Harris-Reid
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] Proposed new sqlite3_open_v3() interface

2010-05-03 Thread Roger Binns
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 05/03/2010 07:47 AM, D. Richard Hipp wrote:
> Question 2:  Are there other foibles that we could correct using  
> sqlite3_open_v3?

I'd like the shared cache busy handling to be exactly the same as
non-shared cache.  ie if the only line of code changed in a program is
to turn it on/off then the busy behaviour should be identical.

Currently shared cache mode has no busy handlers, returns a different
error code, ignores timeouts etc.

http://www.sqlite.org/src/tktview/ebde3f66fc64e21e61ef2854ed1a36dfff884a2f

sqlite3_unlock_notify can't be used to imitate the busy functionality
unless you own the entire application since only one handler can be
installed, plus would require a complex series of inter-thread
notifications if it was used. And if it can be used to imitate busy then
that should be a standard part of SQLite.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.10 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/

iEYEARECAAYFAkvfSiUACgkQmOOfHg372QQOLwCgzGPoUd8C8NeevsVQiAS+v2BI
oJ8AnjZ1ZtF2JyTXZOaKLi+Gl3CxrTwW
=wlxj
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] Can I throw a query out to the group?

2010-05-03 Thread Matt Young
Thanks,

I eventually ended up doing something like that,and got it working.
I booked marked the your example and will try variations of it.

I got most of my system working, but I am still overwhelmed with
economic data releases from around the word.

On 5/3/10, Tim Romano  wrote:
> Matt,
> You cannot select a column from a relation if the relation does not include
> the column. The query inside the ( ) returns a single column, table_id.
>
> select a_format
> from
> (
> select table_id
> from table_id_list
> where prefix_code = 'MyPrefix_code'
> );
>
> However, if table_id corresponds to a_format, you can supply an alias for
> that column in the inner select, like this:
>
> select a_format
> from
> (
> select table_id as a_format
> from table_id_list
> where prefix_code = 'MyPrefix_code'
> );
>
>
> Regards
> Tim Romano
> Swarthmore PA
>
>
>
>
>
>
>
>
>
>
>
> On Sun, May 2, 2010 at 10:22 AM, Matt Young  wrote:
>
>> I am a bit of a novice.  I am dealing with meta data, descriptions of
>> the Bureau of Labor Statistics database. BLS data is identified by a
>> series code (16 chars long) but the series format is different for
>> each data group)
>>
>> I need to extract from an SQL table the names of other SQL tables and
>> reference them in a second query to build the proper series_id code:
>>
>> I haven't tried this, I assume it can't be done:
>>
>> select a_format  from (select table_id from table_id_list where
>> prefix_code = 'MyPrefix_code');
>>
>> Is this beyond SQL?  Is there a better solution?
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] [server] HTTP + SQLite bundled as single EXE?

2010-05-03 Thread Alexey Pechnikov
As example, on my laptop AOL Web Server+SQLite database + TCL scripts
can perform more than 1000 HTTP request per second. But AOLServer is a
solution for big projects. I am using AOLServer for projects with a
few hundreds and more of concurrent user sessions. And for many
projects SQLite is more better than PostgreSQL database (selects in
SQLitre is exteremly fast).

You can see a single-threaded performance tests of my Fossil repository here:
http://sqlite.mobigroup.ru/src/wiki?name=fossil

Single-file executable (TCL starpack) with custom socket event handler
+ tclsqlite package + database is more reasonable way. Produce
JSON/XML/etc. output is a trivial task. A few thousands of requests
per second is available by this (about 10 000 requests per second is
limit on my laptop for TCL sockets). I think you can't do this with
Apache.


2010/5/3 Sam Carleton :
> I must respectfully disagree with the statement that Apache is huge and
> complicated, but I guess it is all relative...

-- 
Best regards, Alexey Pechnikov.
http://pechnikov.tel/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] Computed columns in VIEWs return NULL but should be able to be typed! Any ideas?

2010-05-03 Thread Stefan Keller
Unfortunately the application which reads from this view needs that
all columns are typed - even if the values types deviate from it - and
I think this is a logical assumption. So, I fear I do have only one
chance and SQLite doesn't let me do it:

CREATE VIEW myview AS
  SELECT id, name, CAST(salary + 10 AS INTEGER) "salaryplus10" FROM mytable;

To me it seems like an inconsistency (or bug) when TABLES allow
declaration of types and VIEWS only eventually.

-S.

2010/5/3 Simon Slavin :
>
> On 3 May 2010, at 6:14pm, Stefan Keller wrote:
>
>> But in SQLite if a view column comes from a function result or some
>> computation, then the column type is NULL...!? It's not taking the
>> result-type as mentioned in the manual
>> (http://www.sqlite.org/lang_select.html) - even when I try to do a
>> CAST in the projection clause, like this:
>>
>>  SELECT id, name, CAST(salary + 10 AS INTEGER) "salaryplus10" FROM mytable;
>>
>> I mean that a VIEW should behave like a (read only) TABLE in any case.
>> => Is there a way to give such columns a type anyway?
>
> You get two chances to CAST, one when you define the VIEW, and another when 
> you SELECT from the VIEW.  If one of them doesn't enforce the type of 
> evaluation you want, the other probably will.  As you say, VIEW columns don't 
> have types at all.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] Computed columns in VIEWs return NULL but should be able to be typed! Any ideas?

2010-05-03 Thread Simon Slavin

On 3 May 2010, at 6:14pm, Stefan Keller wrote:

> But in SQLite if a view column comes from a function result or some
> computation, then the column type is NULL...!? It's not taking the
> result-type as mentioned in the manual
> (http://www.sqlite.org/lang_select.html) - even when I try to do a
> CAST in the projection clause, like this:
> 
>  SELECT id, name, CAST(salary + 10 AS INTEGER) "salaryplus10" FROM mytable;
> 
> I mean that a VIEW should behave like a (read only) TABLE in any case.
> => Is there a way to give such columns a type anyway?

You get two chances to CAST, one when you define the VIEW, and another when you 
SELECT from the VIEW.  If one of them doesn't enforce the type of evaluation 
you want, the other probably will.  As you say, VIEW columns don't have types 
at all.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] Computed columns in VIEWs return NULL but should be able to be typed! Any ideas?

2010-05-03 Thread Pavel Ivanov
> Is there a way to give such columns a type anyway?

I guess you aware that columns in SQLite doesn't have types? They only
have "declared type" (which is arbitrary string used in CREATE TABLE
statement) and affinity. OTOH values in each row have types. And
neither "declared type" nor affinity don't prevent column to contain
values of any type at all. And I bet values in your view are reported
to have appropriate type although columns don't have declared type.
Which function return to you NULL type?


Pavel

On Mon, May 3, 2010 at 1:14 PM, Stefan Keller  wrote:
> I have a question regarding VIEWs in SQLite:
>
> It looks like if SQLite simply copies the column type from the
> original table into the corresponding view column. And I know  SQLite
> implements some 'loose column typing'. That's ok so far.
>
> But in SQLite if a view column comes from a function result or some
> computation, then the column type is NULL...!? It's not taking the
> result-type as mentioned in the manual
> (http://www.sqlite.org/lang_select.html) - even when I try to do a
> CAST in the projection clause, like this:
>
>  SELECT id, name, CAST(salary + 10 AS INTEGER) "salaryplus10" FROM mytable;
>
> I mean that a VIEW should behave like a (read only) TABLE in any case.
> => Is there a way to give such columns a type anyway?
>
> Yours
> -S.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


[sqlite] Computed columns in VIEWs return NULL but should be able to be typed! Any ideas?

2010-05-03 Thread Stefan Keller
I have a question regarding VIEWs in SQLite:

It looks like if SQLite simply copies the column type from the
original table into the corresponding view column. And I know  SQLite
implements some 'loose column typing'. That's ok so far.

But in SQLite if a view column comes from a function result or some
computation, then the column type is NULL...!? It's not taking the
result-type as mentioned in the manual
(http://www.sqlite.org/lang_select.html) - even when I try to do a
CAST in the projection clause, like this:

  SELECT id, name, CAST(salary + 10 AS INTEGER) "salaryplus10" FROM mytable;

I mean that a VIEW should behave like a (read only) TABLE in any case.
=> Is there a way to give such columns a type anyway?

Yours
-S.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] is it possible to return primary key column from given table ?

2010-05-03 Thread yogibabu



P Kishor-3 wrote:
> what is the name of the column? Is it '--idcolumn--'?

That was only example written when I didn't know even for what syntax to
look for. Now problem comes to how to run SELECT against PRAGMA
table_info(tblname).

Only way out of this that I found is to run regex against the result of
sqlite_master:

$tableid = preg_match("|CREATE TABLE \"$tbl\" [(] (?\w+) INTEGER PRIMARY
KEY ON CONFLICT IGNORE,|",$pdo->query("select sql from sqlite_master where
tbl_name='$tbl'")->fetchColumn(),$id) ? $id['id'] : exit("upsanerr: $id,
$tbl");
-- 
View this message in context: 
http://old.nabble.com/is-it-possible-to-return-primary-key-column-name-from-given-table---tp28432175p28437269.html
Sent from the SQLite mailing list archive at Nabble.com.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] Proposed new sqlite3_open_v3() interface

2010-05-03 Thread Florian Weimer
* D. Richard Hipp:

> Question 2:  Are there other foibles that we could correct using  
> sqlite3_open_v3?

You could default the page size to the file system block size (if it
can be determined).
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] Proposed new sqlite3_open_v3() interface

2010-05-03 Thread Robert Simpson
My only request would be that you add some kind of flag that allows us to
specify opening the database in UTF-16 mode via this interface.

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of D. Richard Hipp
Sent: Monday, May 03, 2010 7:48 AM
To: sqlite-...@sqlite.org; General Discussion of SQLite Database
Subject: [sqlite] Proposed new sqlite3_open_v3() interface

Community feedback is requested for the following proposed new SQLite  
C API:

int sqlite3_open_v3(const char*, sqlite3**, int, const char*);

The new database connection constructor would work exactly like  
sqlite3_open_v2() with the following exceptions:

(1) Foreign Key constraints would be enabled by default.
(2) Recursive triggers would be enabled by default.
(3) The default database file format would be format 4 (meaning that  
new databases would be unreadable by versions of SQLite prior to 3.1.3).
(4) The misfeature of allowing double-quoted identifiers to fall back  
to being strings if no matching identifier could be found would be  
disabled.
(5) PRIMARY KEY columns would automatically be NOT NULL.  (The would  
not change the current INTEGER PRIMARY KEY behavior, only non-INTEGER  
PRIMARY KEYs.)

We are very hesitant to make any of the above changes to SQLite for  
the default case since they run the risk of breaking legacy code.  By  
making the newer behavior the default only for the new interface  
sqlite3_open_v3() and leaving the behavior unchanged for legacy  
constructors, we hope to encourage newer applications to use the newer  
features while avoiding breakage of older applications.

Question 1:  Are there any objections to this approach?

Question 2:  Are there other foibles that we could correct using  
sqlite3_open_v3?

D. Richard Hipp
d...@hwaci.com



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] [sqlite-dev] Proposed new sqlite3_open_v3() interface

2010-05-03 Thread Jay A. Kreibich
On Mon, May 03, 2010 at 10:47:36AM -0400, D. Richard Hipp scratched on the wall:
> Community feedback is requested for the following proposed new SQLite  
> C API:
> 
> int sqlite3_open_v3(const char*, sqlite3**, int, const char*);

> Question 1:  Are there any objections to this approach?

  I like it.  It solves a lot of problems in a clean way.

> Question 2:  Are there other foibles that we could correct using  
> sqlite3_open_v3?

  If I ask for the DB to be open read-write, and it can only be open
  read-only, I'd prefer it be an error (or, let me know my request was
  not fully serviced).  The automatic fall-back of sqlite3_open_v2() to
  opening read-only can be a pain to deal with, and I see few
  advantages to it.

  That, or provide some kind of API to get the state of a database
  connection.  An extension to sqlite3_file_control() maybe?

   -j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Our opponent is an alien starship packed with atomic bombs.  We have
 a protractor."   "I'll go home and see if I can scrounge up a ruler
 and a piece of string."  --from Anathem by Neal Stephenson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] Proposed new sqlite3_open_v3() interface

2010-05-03 Thread Chris
On Mon, 2010-05-03 at 10:47 -0400, D. Richard Hipp wrote:

> Question 1:  Are there any objections to this approach?
> 
None here. My web-based applications (written in C) have just one call
to the existing open_v2 call so it is trivial to change them.

> Question 2:  Are there other foibles that we could correct using  
> sqlite3_open_v3?
> 
// Allow delays for concurrent access
  sqlite3_busy_timeout(hDB, 500);

That and foreign keys are the only non-default conditions that I need
to invoke after the call to open.

Chris Peachment


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


[sqlite] multi-threaded vs multi-process, is there a difference?

2010-05-03 Thread Sam Carleton
I am using SQLite as the backend to my little Windows Apache server based
kiosk system.  I load tested it with 50 clients running slide shows, aka
hitting the apache server every 5 seconds and all ran wonderfully!

Apache on Windows is threaded, so I only had one process running.  Each
connection does it's own open and close of the SQLite database.  I am
looking to have another process, admin tool, on the same physical machine
occasionally come in and access the SQLite DB.  Will there be a decrease in
performance when this admin tool is accessing the SQLite DB or will it have
the same impact as another thread in the Apache server?

Sam
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] [server] HTTP + SQLite bundled as single EXE?

2010-05-03 Thread Sam Carleton
On Mon, May 3, 2010 at 6:24 AM, Simon Slavin  wrote:

>
> The problem is not in coding it -- that's relatively easy.  The problem is
> in who would use it.  I think that would be mostly people who already use
> PHP to write a backend data server.  The advantage of this is that it can be
> done using just the hugely popular and tested Apache: it already has SQLite
> built in.
>
> On the other hand, Apache is huge, complicated and hard to test.  A minimal
> kit including just HTTP and SQLite would be smaller and simpler and
> therefore more in the spirit of SQLite.
>

I must respectfully disagree with the statement that Apache is huge and
complicated, but I guess it is all relative...

I am a MicroISV that is using Apache, Axis2/C, PHP5, and SQLite for my
backend.  All three are installed with the .Net frontend that configures the
site, and the install package (minus the Microsoft Runtime that are also in
there) is about 5 megs in size.  Given all the extra features of Apache, it
is a VERY powerful system.  Learning how to configure Apache is pretty easy
for basic things, learning to write Apache modules is pretty easy too.
Granted it isn't one physical file, but it can be installed with a simply
file copy, assuming you have something to create the config file.  I have a
template of the config file as a resource of the .net program, it updates
all the paths and stuff accordingly, then starts the server.  Real straight
forward and simple, but with tons and tons of power and a VERY battle
hardened web server!

Sam
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


[sqlite] Proposed new sqlite3_open_v3() interface

2010-05-03 Thread D. Richard Hipp
Community feedback is requested for the following proposed new SQLite  
C API:

int sqlite3_open_v3(const char*, sqlite3**, int, const char*);

The new database connection constructor would work exactly like  
sqlite3_open_v2() with the following exceptions:

(1) Foreign Key constraints would be enabled by default.
(2) Recursive triggers would be enabled by default.
(3) The default database file format would be format 4 (meaning that  
new databases would be unreadable by versions of SQLite prior to 3.1.3).
(4) The misfeature of allowing double-quoted identifiers to fall back  
to being strings if no matching identifier could be found would be  
disabled.
(5) PRIMARY KEY columns would automatically be NOT NULL.  (The would  
not change the current INTEGER PRIMARY KEY behavior, only non-INTEGER  
PRIMARY KEYs.)

We are very hesitant to make any of the above changes to SQLite for  
the default case since they run the risk of breaking legacy code.  By  
making the newer behavior the default only for the new interface  
sqlite3_open_v3() and leaving the behavior unchanged for legacy  
constructors, we hope to encourage newer applications to use the newer  
features while avoiding breakage of older applications.

Question 1:  Are there any objections to this approach?

Question 2:  Are there other foibles that we could correct using  
sqlite3_open_v3?

D. Richard Hipp
d...@hwaci.com



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] is it possible to return primary key column from given table ?

2010-05-03 Thread P Kishor
On Mon, May 3, 2010 at 2:14 AM, yogibabu  wrote:
>
> like this: SELECT --idcolumn-- FROM `table`


what is the name of the column? Is it '--idcolumn--'? Are the leading
and trailing '--' part of the name? Remember that leading '--' is used
as SQL comments. If that is indeed the name, try

SELECT "--idcolumn--" FROM table

note --idcolumn-- in double quotes, which enable you to use special
characters and words in your object/entity names.


-- 
Puneet Kishor
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] [server] HTTP + SQLite bundled as single EXE?

2010-05-03 Thread Gilles Ganault
On Mon, 03 May 2010 15:22:27 +0200, Michael Schlenker
 wrote:
>You could take the Tcl WUB webserver, which already implements a SQLQ
>domain from an sqlite DB and use that. Easy to wrap in single file too.
>
>See http://code.google.com/p/wub/source/browse/trunk/Domains/SqlQ.tcl

Thanks for the link.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] [server] HTTP + SQLite bundled as single EXE?

2010-05-03 Thread Sylvain Pointeau
I would probably take mysql or postgresql, to be able to use the "prepared
statements" and other facilities.
I don't see any point to implement this kind of system.

or same api as sqlite but the open is taking an url instead,
but you have to re-write the api of sqlite.
In this case, I can see the benefit of keeping the same code, but as a
workaround for some additional non planned requirements, to use a sqlite
server instead. (the code stays untouched, just use another dll, same
functions same results)

anything I missed?

Best regards,
Sylvain


On Mon, May 3, 2010 at 3:27 PM, Gilles Ganault wrote:

> On Sun, 02 May 2010 21:26:27 +0200, Gilles Ganault
>  wrote:
> >It's probably quite an easy thing to do for someone well versed in C,
> >but I haven't seen a project that would combine a web server and
> >SQLite into a single EXE.
>
> Here's a diagram:
>
> http://img222.imageshack.us/img222/6578/wwwsqliteserver.jpg
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] [server] HTTP + SQLite bundled as single EXE?

2010-05-03 Thread Gilles Ganault
On Sun, 02 May 2010 21:26:27 +0200, Gilles Ganault
 wrote:
>It's probably quite an easy thing to do for someone well versed in C,
>but I haven't seen a project that would combine a web server and
>SQLite into a single EXE.

Here's a diagram:

http://img222.imageshack.us/img222/6578/wwwsqliteserver.jpg

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] [server] HTTP + SQLite bundled as single EXE?

2010-05-03 Thread Michael Schlenker
Sylvain Pointeau schrieb:
> option 1: a database server using sqlite behind the scene:
> - which language / protocole to use ?
> - which is the security model? (forget .htaccess, this is for apache /
> webserver)
> 
> option 2: web server using http
> - only SELECT statements?
> - returning JSON or whatever?
> - which protocole to use then? (there is no normalized protocole to do that)

You could take the Tcl WUB webserver, which already implements a SQLQ
domain from an sqlite DB and use that. Easy to wrap in single file too.

See http://code.google.com/p/wub/source/browse/trunk/Domains/SqlQ.tcl

Michael

-- 
Michael Schlenker
Software Architect

CONTACT Software GmbH   Tel.:   +49 (421) 20153-80
Wiener Straße 1-3   Fax:+49 (421) 20153-41
28359 Bremen
http://www.contact.de/  E-Mail: m...@contact.de

Sitz der Gesellschaft: Bremen
Geschäftsführer: Karl Heinz Zachries, Ralf Holtgrefe
Eingetragen im Handelsregister des Amtsgerichts Bremen unter HRB 13215
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] [server] HTTP + SQLite bundled as single EXE?

2010-05-03 Thread Sylvain Pointeau
option 1: a database server using sqlite behind the scene:
- which language / protocole to use ?
- which is the security model? (forget .htaccess, this is for apache /
webserver)

option 2: web server using http
- only SELECT statements?
- returning JSON or whatever?
- which protocole to use then? (there is no normalized protocole to do that)

On Mon, May 3, 2010 at 2:50 PM, Sylvain Pointeau  wrote:

> this is not clear for me.
>
> On Mon, May 3, 2010 at 2:49 PM, Gilles Ganault wrote:
>
>> On Mon, 3 May 2010 14:47:48 +0200, Sylvain Pointeau
>>  wrote:
>> >if you speak about MySQL or POSTGRESQL
>> >then you mean database server, not a web server.
>>
>> Yes. Please read the thread, starting with my original post where I
>> explain the idea.
>>
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
>
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] [server] HTTP + SQLite bundled as single EXE?

2010-05-03 Thread Sylvain Pointeau
this is not clear for me.

On Mon, May 3, 2010 at 2:49 PM, Gilles Ganault wrote:

> On Mon, 3 May 2010 14:47:48 +0200, Sylvain Pointeau
>  wrote:
> >if you speak about MySQL or POSTGRESQL
> >then you mean database server, not a web server.
>
> Yes. Please read the thread, starting with my original post where I
> explain the idea.
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] [server] HTTP + SQLite bundled as single EXE?

2010-05-03 Thread Gilles Ganault
On Mon, 3 May 2010 14:47:48 +0200, Sylvain Pointeau
 wrote:
>if you speak about MySQL or POSTGRESQL
>then you mean database server, not a web server.

Yes. Please read the thread, starting with my original post where I
explain the idea.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] [server] HTTP + SQLite bundled as single EXE?

2010-05-03 Thread Sylvain Pointeau
if you speak about MySQL or POSTGRESQL
then you mean database server, not a web server.

Do you want to have a server where to connect for making your SELECT, UPDATE
DELETE, CREATE?
or do you want a webserver to just do some SELECT? (and having a XML, JSON
or whatever)

Best regards,
Sylvain


On Mon, May 3, 2010 at 2:43 PM, Gilles Ganault wrote:

> On Mon, 3 May 2010 14:41:10 +0200, Sylvain Pointeau
>  wrote:
> >How do you authenticate to your http sqlite web server?
> >is it not a security issue then?
>
> SQLite is meant for local use, so people interested in an SQLite
> server would operate on a small LAN, protected from the Net by a
> firewall.
>
> If authentication/obfuscation is needed, what about .htaccess and
> HTTPS?
>
> >is there something I didn't understand?
>
> I'd like a single EXE so it's as easy to deploy as SQLite. Otherwise,
> I'd just use MySQL or PostgreSQL.
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] [server] HTTP + SQLite bundled as single EXE?

2010-05-03 Thread Gilles Ganault
On Mon, 3 May 2010 14:41:10 +0200, Sylvain Pointeau
 wrote:
>How do you authenticate to your http sqlite web server?
>is it not a security issue then?

SQLite is meant for local use, so people interested in an SQLite
server would operate on a small LAN, protected from the Net by a
firewall.

If authentication/obfuscation is needed, what about .htaccess and
HTTPS?

>is there something I didn't understand?

I'd like a single EXE so it's as easy to deploy as SQLite. Otherwise,
I'd just use MySQL or PostgreSQL.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] [server] HTTP + SQLite bundled as single EXE?

2010-05-03 Thread Sylvain Pointeau
How do you authenticate to your http sqlite web server?
is it not a security issue then?

normally you would also need a server side language, so I would say apache
is the only way to go,
and also I don't understand why you need something specific to return JSON
object.
you can do it in 3 seconds in PHP or ASP.NET.

is there something I didn't understand?

Best regards,
Sylvain


On Mon, May 3, 2010 at 1:06 PM, Gilles Ganault wrote:

> On Mon, 3 May 2010 15:01:26 +0400, Alexey Pechnikov
>  wrote:
>
> >See http://wiki.tcl.tk/15722 Add SQLite into it - about few minuts of
> time.
>
> Thanks. If no one is interested in launching a project that would
> offer a single binary and maintain it, I'll try TCL-TK.
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] Can I throw a query out to the group?

2010-05-03 Thread Tim Romano
Matt,
You cannot select a column from a relation if the relation does not include
the column. The query inside the ( ) returns a single column, table_id.

select a_format
from
(
select table_id
from table_id_list
where prefix_code = 'MyPrefix_code'
);

However, if table_id corresponds to a_format, you can supply an alias for
that column in the inner select, like this:

select a_format
from
(
select table_id as a_format
from table_id_list
where prefix_code = 'MyPrefix_code'
);


Regards
Tim Romano
Swarthmore PA











On Sun, May 2, 2010 at 10:22 AM, Matt Young  wrote:

> I am a bit of a novice.  I am dealing with meta data, descriptions of
> the Bureau of Labor Statistics database. BLS data is identified by a
> series code (16 chars long) but the series format is different for
> each data group)
>
> I need to extract from an SQL table the names of other SQL tables and
> reference them in a second query to build the proper series_id code:
>
> I haven't tried this, I assume it can't be done:
>
> select a_format  from (select table_id from table_id_list where
> prefix_code = 'MyPrefix_code');
>
> Is this beyond SQL?  Is there a better solution?
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] Optimising usage of LIKE

2010-05-03 Thread Tim Romano
By "version" I meant "implementation".

On Mon, May 3, 2010 at 7:25 AM, Tim Romano  wrote:

> Which version of SQLite are you using? If LIKE has been overridden in the
> implementation you're using, it won't have the advantage of an index
> whatever the collation, in which case you might consider GLOB though it is
> case-sensitive.
> Regards
> Tim Romano
>
>
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


[sqlite] Optimising usage of LIKE

2010-05-03 Thread Ian Hardingham
Hey guys.

For various embarrassing reasons, I'm using:

SELECT x FROM userTable WHERE name LIKE 'name'

To look up entries in my account table.   Basically, the scripting 
language I'm using which hooks into SQLite is a bit case-agnostic.

I've been told by a friend that this is extremely inefficient, and that 
I should UPPER my 'name' column and my query name in order to get it to 
be fast.

Can anyone tell me the easiest way of doing this?

Thanks,
Ian
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] Optimising usage of LIKE

2010-05-03 Thread Tim Romano
Which version of SQLite are you using? If LIKE has been overridden in the
implementation you're using, it won't have the advantage of an index
whatever the collation, in which case you might consider GLOB though it is
case-sensitive.
Regards
Tim Romano
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] [server] HTTP + SQLite bundled as single EXE?

2010-05-03 Thread Gilles Ganault
On Mon, 3 May 2010 15:01:26 +0400, Alexey Pechnikov
 wrote:

>See http://wiki.tcl.tk/15722 Add SQLite into it - about few minuts of time.

Thanks. If no one is interested in launching a project that would
offer a single binary and maintain it, I'll try TCL-TK.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] Optimising usage of LIKE

2010-05-03 Thread Black, Michael (IS)
And I missed one more...so many solutions...so little time...
 
CREATE TABLE userTable (name VARCHAR COLLATE NOCASE);
SELECT x from userTable where name='name';
 
Then you'll get all case-insensitive matches too.
 
All depends on whether or not you need to really keep the data in it's original 
form.  I would favor normalizing the data on the INSERT.
 
Michael D. Black
Senior Scientist
Northrop Grumman Mission Systems
 



From: sqlite-users-boun...@sqlite.org on behalf of Black, Michael (IS)
Sent: Mon 5/3/2010 5:58 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Optimising usage of LIKE



Simon's answer is probably best -- without any benchmarks it makes the most 
sense.

You've got at least two solutions that don't require changing your data:

SELECT x FROM userTable WHERE upper(name) = upper('name');

SELECT x FROM userTable WHERE name = 'name' COLLATE NOCASE.

And one solution if it is possible to store you're data in upper case.
INSERT INTO userTable values(upper(name));

Then the indexes will all be correct, you can detect duplicates, and should cut 
the index searching in half.
SELECT x FROM userTable where name = upper('name);


Michael D. Black
Senior Scientist
Northrop Grumman Mission Systems




From: sqlite-users-boun...@sqlite.org on behalf of Simon Slavin
Sent: Mon 5/3/2010 5:33 AM
To: i...@omroth.com; General Discussion of SQLite Database
Subject: Re: [sqlite] Optimising usage of LIKE




On 3 May 2010, at 9:53am, Ian Hardingham wrote:

> For various embarrassing reasons, I'm using:
>
> SELECT x FROM userTable WHERE name LIKE 'name'
>
> To look up entries in my account table.   Basically, the scripting
> language I'm using which hooks into SQLite is a bit case-agnostic.
>
> I've been told by a friend that this is extremely inefficient, and that
> I should UPPER my 'name' column and my query name in order to get it to
> be fast.
>
> Can anyone tell me the easiest way of doing this?

Your friend is used to other versions of SQLite.  Your best bet is to define 
the field you're searching on as having COLLATE NOCASE.  This means that all 
indexing and searching on it will ignore case.  See sections 6 and 6.3 in

http://www.sqlite.org/datatype3.html

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users




___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] [server] HTTP + SQLite bundled as single EXE?

2010-05-03 Thread Alexey Pechnikov
See http://wiki.tcl.tk/15722 Add SQLite into it - about few minuts of time.

2010/5/3 Gilles Ganault :
> I don't know if it'd be easier to combine existing HTTP server +
> SQLite

-- 
Best regards, Alexey Pechnikov.
http://pechnikov.tel/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] Optimising usage of LIKE

2010-05-03 Thread Black, Michael (IS)
Simon's answer is probably best -- without any benchmarks it makes the most 
sense.
 
You've got at least two solutions that don't require changing your data:
 
SELECT x FROM userTable WHERE upper(name) = upper('name');
 
SELECT x FROM userTable WHERE name = 'name' COLLATE NOCASE.
 
And one solution if it is possible to store you're data in upper case.
INSERT INTO userTable values(upper(name));
 
Then the indexes will all be correct, you can detect duplicates, and should cut 
the index searching in half.
SELECT x FROM userTable where name = upper('name);
 
 
Michael D. Black
Senior Scientist
Northrop Grumman Mission Systems
 



From: sqlite-users-boun...@sqlite.org on behalf of Simon Slavin
Sent: Mon 5/3/2010 5:33 AM
To: i...@omroth.com; General Discussion of SQLite Database
Subject: Re: [sqlite] Optimising usage of LIKE




On 3 May 2010, at 9:53am, Ian Hardingham wrote:

> For various embarrassing reasons, I'm using:
>
> SELECT x FROM userTable WHERE name LIKE 'name'
>
> To look up entries in my account table.   Basically, the scripting
> language I'm using which hooks into SQLite is a bit case-agnostic.
>
> I've been told by a friend that this is extremely inefficient, and that
> I should UPPER my 'name' column and my query name in order to get it to
> be fast.
>
> Can anyone tell me the easiest way of doing this?

Your friend is used to other versions of SQLite.  Your best bet is to define 
the field you're searching on as having COLLATE NOCASE.  This means that all 
indexing and searching on it will ignore case.  See sections 6 and 6.3 in

http://www.sqlite.org/datatype3.html

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] [server] HTTP + SQLite bundled as single EXE?

2010-05-03 Thread Gilles Ganault
On Mon, 3 May 2010 11:24:49 +0100, Simon Slavin
 wrote:
>If you're going to mostly pass data for use with web applications then JSON is 
>possibly a more appropriate format

Thanks, I'll check it out.

>The problem is not in coding it -- that's relatively easy.  The problem is in 
>who would use it.

Anyone who is currently using SQLite but needs...
0. a supported (so it's updated when a new version of SQLite comes out
that might require some changes in the solution)
1. cross-platform (so the server can run on Windows, Linux, MacOS)
2. easy-to-deploy client/server alternative since it's a single binary
3. that simply listens on a TCP port instead of the headaches of
sharing a directory with SMB/CIFS
4. to safely share an SQLite database among a few concurrent clients
(otherwise, people would just use a full-fledged DMBS)
5. and uses HTTP so that no specific client-side component is required

I don't know if it'd be easier to combine existing HTTP server +
SQLite, or go from Fossil and remove things that aren't needed.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] Optimising usage of LIKE

2010-05-03 Thread Simon Slavin

On 3 May 2010, at 9:53am, Ian Hardingham wrote:

> For various embarrassing reasons, I'm using:
> 
> SELECT x FROM userTable WHERE name LIKE 'name'
> 
> To look up entries in my account table.   Basically, the scripting 
> language I'm using which hooks into SQLite is a bit case-agnostic.
> 
> I've been told by a friend that this is extremely inefficient, and that 
> I should UPPER my 'name' column and my query name in order to get it to 
> be fast.
> 
> Can anyone tell me the easiest way of doing this?

Your friend is used to other versions of SQLite.  Your best bet is to define 
the field you're searching on as having COLLATE NOCASE.  This means that all 
indexing and searching on it will ignore case.  See sections 6 and 6.3 in

http://www.sqlite.org/datatype3.html

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] Modeling SQLite databases

2010-05-03 Thread Simon Slavin

On 3 May 2010, at 5:16am, M. Bashir Al-Noimi wrote:

> Does any one help me in this issue? I couldn't find any modeling tool 
> for SQLite on Linux?! I need a tool provides diagram database designer 
> just line SQLite Maestro.

You are asking for a complicated GUI tool that runs under Linux.  The problem 
is that anyone who can install Linux wouldn't need one -- they're competent to 
use SQL commands -- so perhaps nobody has written one.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] is it possible to return primary key column from given table ?

2010-05-03 Thread Simon Slavin

On 3 May 2010, at 8:14am, yogibabu wrote:

> like this: SELECT --idcolumn-- FROM `table`

If you always want to use a unique integer to refer to a record, you can ask 
for the column called '_rowid_' even if you didn't define one.  You can use 
this in SELECT and UPDATE commands, as long as you don't close the database or 
do a VACUUM between them, because this can change the _rowid_s.

If you want to figure out what the actual primary key is (it may be more than 
one column) you can use a combination of these two:

PRAGMA index_list(table-name)
PRAGMA index_info(index-name)

See

http://www.sqlite.org/pragma.html

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


[sqlite] Optimising usage of LIKE

2010-05-03 Thread Ian Hardingham
Hey guys.

For various embarrassing reasons, I'm using:

SELECT x FROM userTable WHERE name LIKE 'name'

To look up entries in my account table.   Basically, the scripting 
language I'm using which hooks into SQLite is a bit case-agnostic.

I've been told by a friend that this is extremely inefficient, and that 
I should UPPER my 'name' column and my query name in order to get it to 
be fast.

Can anyone tell me the easiest way of doing this?

Thanks,
Ian
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


[sqlite] is it possible to return primary key column from given table ?

2010-05-03 Thread yogibabu

like this: SELECT --idcolumn-- FROM `table`
-- 
View this message in context: 
http://old.nabble.com/is-it-possible-to-return-primary-key-column-from-given-table---tp28432175p28432175.html
Sent from the SQLite mailing list archive at Nabble.com.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] [server] HTTP + SQLite bundled as single EXE?

2010-05-03 Thread Gilles Ganault
On Sun, 2 May 2010 20:31:15 +0100, Simon Slavin
 wrote:
>How are you seeing this ?  You send your SQL queries via HTTP and it answers 
>by replying with XML ?

XML or TAB-separated text. Using regexes, it's easy to parse data,
unless someone knows of a better way.
Besides, if there are a lot of SELECTed data, since any HTTP library
worth its salt handles GZIP, the server can zip data before sending
them over the wire.

Actually, it seems like this single-EXE SQLite server could be
achieved simply by ripping unneeded code from Dr. Hipp's other great
software, the Fossil source control management software:

www.fossil-scm.org

Ideally, the Windows version could be started either as a stand-alone
EXE or as a Service.

For those great C developers out there: What do you think of this
idea?

Thank you.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users