Re: [sqlite] how would you allow users to re-order rows arbitrarily?

[John Stanton]

Traditionally that is achieved by a doubly linked list.  Each row 
contains a pointer to the next and previous row and a null for the end 
case.  The linked list structure cannot fail regardless of the amount of 
shuffling or the size.


Sean Payne wrote:
Suppose gui-users wanted to drag and drop rows in a table so that  they 
could shuffle it anyway that they wanted so that the rows  maintained 
that order the next time they accessed the table.   Can  this be done 
without updating alot of other rows?  How is this  normally handled?


The ideas I have are
1. to use a field (possibly the primary key) and then reorder the  lower 
part of the table each time a row is moved.

2. to deal with it a separate table - maintain an position->key table
3. do something like have an "magical-order-field" which keeps a  string 
that when SORTed BY maintains the desired order.   so to  insert a row 
between "a" and "b" set it's magical-order-field to "aab"


Got hints?

Thanks,

SEan

- 


To unsubscribe, send email to [EMAIL PROTECTED]
- 






-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] how would you allow users to re-order rows arbitrarily?

[Jay Sprenkle]

I do it by maintaining a column named 'SortOrder'
You can update the field to have whatever content will get you the
order you want
at the moment and it's preserved the next time they start the application.


On 1/6/07, Sean Payne <[EMAIL PROTECTED]> wrote:

Suppose gui-users wanted to drag and drop rows in a table so that
they could shuffle it anyway that they wanted so that the rows
maintained that order the next time they accessed the table.   Can
this be done without updating alot of other rows?  How is this
normally handled?


--
The PixAddixImage Collector suite:
http://groups-beta.google.com/group/pixaddix

SqliteImporter and SqliteReplicator: Command line utilities for Sqlite
http://www.reddawn.net/~jsprenkl/Sqlite

Cthulhu Bucks!
http://www.cthulhubucks.com

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] Error during compilation of sqlite-3.3.8

[Dori Meshulam]

Martin,

I still get the error. Is it possible that my tclConfig.sh file is not
well configured?

Is there any parameter that I should look for?

Thanks,

Dori

-Original Message-
From: Martin Jenkins [mailto:[EMAIL PROTECTED]
Sent: Thursday, January 04, 2007 5:21 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Error during compilation of sqlite-3.3.8

Dori,

I've just logged into the Debian box where I last did any work on this.
On that machine (in .../sqlite/bld) I had to issue ../configure
--with-tcl=/usr/lib/tcl8.4/ to get configure to work. I have tcl8.3 and
8.4 on my machine so as you suggest, you might have to use
/usr/local/lib instead.

tclConfig.sh sets some environment variables with the location of the
header files etc.

Sorry for the misinformation earlier, my memory obviously needs
reseating. ;)

Martin

> Martin,
>
> Thanks for your response.
>
> I found tcl.h under '/usr/local/include', but when I run make after
> "configure --with-tcl=/usr/local/include" I get the following error:
>
> "checking for Tcl configuration... configure: error:
> /usr/local/include/tcl.h directory doesn't contain tclConfig.sh"
>
> tclConfig.sh is under /usr/local/lib.
>
> Should I ask the administrator to copy it into /usr/local/include or
is
> there other way to pass this error?
>
> Thanks,
>
> Dori
>
> -Original Message-
> From: Martin Jenkins [mailto:[EMAIL PROTECTED]
> Sent: Thursday, January 04, 2007 3:43 PM
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] Error during compilation of sqlite-3.3.8
>
> Dori,
>
> tcl.h might be on your machine but it might not be where configure is
> looking for it.
>
> I don't know if you have "locate" on your HP box but if you do,
> run"locate tcl.h", note the location
> If you don't have locate, run "find /usr/include -type f -name tcl.h",

> note the location
>
> If you located the file, run "configure
> --with-tcl=", make etc as before
>
> If you can't find it, you probably ought to determine whether Tcl is
> installed. I don't know how you'd do that on your box but trying to
run
> the Tcl shell, tclsh, might give you some clue as to what's installed.
>
> HTH
>
> Martin
>
> Dori Meshulam wrote:
>  
>> Hi,
>>
>>
>>
>>
>> My name is Dori and I'm trying to compile sqlite-3.3.8 on UNIX HP
>> machine after downloading sqlite-3.3.8.tar.gz.
>>
>> I got the following errors while running 'make test' command. I
>>
> couldn't
>  
>> find 'tcl.h' file.
>>
>>
>>
>>
>> cpp: "../sqlite-3.3.8/src/test1.c", line 19: error 4036: Can't open
>> include file 'tcl.h'.
>>
>> ../sqlite-3.3.8/src/test2.c:
>>
>> cpp: "../sqlite-3.3.8/src/test2.c", line 21: error 4036: Can't open
>> include file 'tcl.h'.
>>
>> ../sqlite-3.3.8/src/test3.c:
>>
>> cpp: "../sqlite-3.3.8/src/test3.c", line 21: error 4036: Can't open
>> include file 'tcl.h'.
>>
>> ../sqlite-3.3.8/src/test4.c:
>>
>> cpp: "../sqlite-3.3.8/src/test4.c", line 17: error 4036: Can't open
>> include file 'tcl.h'.
>>
>> ../sqlite-3.3.8/src/test5.c:
>>
>> cpp: "../sqlite-3.3.8/src/test5.c", line 23: error 4036: Can't open
>> include file 'tcl.h'.
>>
>> ../sqlite-3.3.8/src/test6.c:
>>
>> cpp: "../sqlite-3.3.8/src/test6.c", line 20: error 4036: Can't open
>> include file 'tcl.h'.
>>
>> ../sqlite-3.3.8/src/test7.c:
>>
>> cpp: "../sqlite-3.3.8/src/test7.c", line 18: error 4036: Can't open
>> include file 'tcl.h'.
>>
>> ../sqlite-3.3.8/src/test8.c:
>>
>> cpp: "../sqlite-3.3.8/src/test8.c", line 19: error 4036: Can't open
>> include file 'tcl.h'.
>>
>> ../sqlite-3.3.8/src/test_autoext.c:
>>
>> cpp: "../sqlite-3.3.8/src/test_autoext.c", line 17: error 4036: Can't
>> open include file 'tcl.h'.
>>
>> ../sqlite-3.3.8/src/test_async.c:
>>
>> cpp: "../sqlite-3.3.8/src/test_async.c", line 75: error 4036: Can't
>>
> open
>  
>> include file 'tcl.h'.
>>
>> ../sqlite-3.3.8/src/test_md5.c:
>>
>> cpp: "../sqlite-3.3.8/src/test_md5.c", line 31: error 4036: Can't
open
>> include file 'tcl.h'.
>>
>> ../sqlite-3.3.8/src/test_schema.c:
>>
>> cpp: "../sqlite-3.3.8/src/test_schema.c", line 41: error 4036: Can't
>> open include file 'tcl.h'.
>>
>> ../sqlite-3.3.8/src/test_server.c:
>>
>> cc: "../sqlite-3.3.8/src/test_server.c", line 1: warning 501: Empty
>> source file.
>>
>> ../sqlite-3.3.8/src/test_tclvar.c:
>>
>> cpp: "../sqlite-3.3.8/src/test_tclvar.c", line 22: error 4036: Can't
>> open include file 'tcl.h'.
>>
>>
>>
>>
>>
>>
>>
>> Could you let me know where can I get this file from?
>>
>>
>>
>>
>> Thanks,
>>
>>
>>
>>
>> Dori Meshulam
>>
>>
>>
>>
>>
>>
>>
>> This message and the information contained herein is proprietary and
>>
> confidential and subject to the Amdocs policy statement,
>  
>> you may review at http://www.amdocs.com/email_disclaimer.asp
>>  
>>
>
>
>

> -
> To unsubscribe, send email to [EMAIL PROTECTED]
>

> -
>
>
>

RE: [sqlite] Error during compilation of sqlite-3.3.8

[Dori Meshulam]

Thanks Dennis.

I still get the error. Is it possible that my tclConfig.sh file is not
well configured?

Is there any parameter that I should look for?

Thanks,

Dori

-Original Message-
From: Dennis Cote [mailto:[EMAIL PROTECTED]
Sent: Thursday, January 04, 2007 5:18 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Error during compilation of sqlite-3.3.8

Dori Meshulam wrote:
> I found tcl.h under '/usr/local/include', but when I run make after
> "configure --with-tcl=/usr/local/include" I get the following error:
>
> "checking for Tcl configuration... configure: error:
> /usr/local/include/tcl.h directory doesn't contain tclConfig.sh"
>
> tclConfig.sh is under /usr/local/lib.
>
> Should I ask the administrator to copy it into /usr/local/include or
is
> there other way to pass this error?
>
>  
Dori,

Martin's answer was almost correct. You should use locate to find the
file tclConfig.sh and use that location on the --with-tcl directive. Th
tclConfig.sh is a shell script that will provide additional information
to locate other parts of your TCL installation (like the tcl.h header
file).

HTH
Dennis Cote


-
To unsubscribe, send email to [EMAIL PROTECTED]

-


This message and the information contained herein is proprietary and 
confidential and subject to the Amdocs policy statement,
you may review at http://www.amdocs.com/email_disclaimer.asp

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] how would you allow users to re-order rows arbitrarily?

[Sean Payne]

Suppose gui-users wanted to drag and drop rows in a table so that  
they could shuffle it anyway that they wanted so that the rows  
maintained that order the next time they accessed the table.   Can  
this be done without updating alot of other rows?  How is this  
normally handled?


The ideas I have are
1. to use a field (possibly the primary key) and then reorder the  
lower part of the table each time a row is moved.

2. to deal with it a separate table - maintain an position->key table
3. do something like have an "magical-order-field" which keeps a  
string that when SORTed BY maintains the desired order.   so to  
insert a row between "a" and "b" set it's magical-order-field to "aab"


Got hints?

Thanks,

SEan

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] sqlite performance, locking & threading

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Gerry Snyder wrote:
> The wiki is there and open to all.
> 
> I look forward to reading your additions to it.

To be fair, only some of the documentation is in the wiki.  The
remainder is generated.  For example you can't edit any of the pages
listed under:

  http://sqlite.org/docs.html

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFFoA0YmOOfHg372QQRAlycAJ9p3hQkWfc8yAUTqE7DgjGsefdiTwCfWIqD
xxpWSAFOZOB6yrbYrNO/Cwc=
=jxv/
-END PGP SIGNATURE-

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] sqlite performance, locking & threading

[Gerry Snyder]

Emerson Clarke wrote:



Fix the out of date documentation


The wiki is there and open to all.

I look forward to reading your additions to it.


Gerry

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] sqlite performance, locking & threading

[Emerson Clarke]

For anyone who is interested i have created a standalone test case
which demonstrates the threading behaviour that i had, or as close as
i can get it.  Feel free to use the code for whatever purposes you see
fit.

It will compile on linux and windows, and comes with 4 versions of the
sqlite libraries which can be found under "test/lib/sqlite/lib".  Each
platform has two versions, one built with THREADSAFE=0, and the other
with THREADSAFE=1.

http://ul1.rapidshare.com/files/10511098/test.zip

It seems that i was incorrect in thinking the cause was
sqlite3_step(). In replicating the problem from scratch i found that
it was actually caused by sqlite3_finalize() being called without
synchronisation.

What does this mean, well firstly it means that the entire problem was
avoidable had i simply synchronised correctly.  But i think more
worryingly, it highlights the fact that there is a great deal of
confusion as to what it means for something to be thread safe on this
mailing list.

This confusion is also not aided in any way by the obtuse and stubborn
attitudes that some people have demonstrated.  If a lot of people are
having regular problems with sqlite and threading as has been
indicated then perhaps its time to review the situation.

It is incredibly frustrating that the documentation and the various
answers that i have recieved on this topic have been contradictory.
At no time has anyone actually stood up and said that the software can
safely be used in a multithreaded environment as long as access to the
api's is synchronised.

Instead you have a situation where the software has inbuilt misuse
detection which may or may not alert the programmer to a potential
error and a THREADSAFE macro which makes 5% of the it threadsafe but
leaves the other 95% exposed.  (By way of example using the code i
supplied and the safe versions of the libraries it is possible to get
away with calling sqlite3_finalize() outside the mutex, but if you
take it one step further and use sqlite3_step() outside the mutex it
will fail)

So an inconsistent and unpredictable internal approach to thread
safety combined with a policy of righteousness on this mailing list
means that people like me have little chance of ever getting it right.

Why is it so hard to simply nail down the characteristics of the
software and update the documentation so that everyone is on the same
page ?

If sqlite is thread safe, and by that i mean it can be used safely
with synchronisation, then why not say so.  And be consistent in
saying so.  That way when someone comes here with a problem you can
confidently describe how the api can be used and the user can go away
and attempt to resolve their problem with confidence.

I guess in part i owe people an appology, but then, had i been given a
rational explanation or had access to clear documentation i guess this
would not have gone on so long.  And to be quite honest, my confidence
in the software has now been eroded to such an extent that i dont know
what is up or down.

Please considder what im saying rationally and dont take offence.  I
know a lot of you are frustrated at this debate, and so you should be.
I am too. So why not take steps to ensure that it doesnt happen
again.  Be clear about the threading behaviour and remove the
inconsistencies.

Fix the out of date documentation, and give a consistent answer,
sqlite can be used with multiple threads provided that access to the
api is synchronised.

The saftey checks and macro options do more harm than good becuase
they create confusion.  Yes if you do X,Y, and Z you might get lucky,
but thats probably why so many people have trouble.  Its not
consistent and its complicated.  Unless your going to document the
exact sequence of calls which is safe to make without synchronisation,
you shouldnt be encouraging it.

Advising people to create complicated designs where each thread has
its own connection doesnt help either, because it will not work with
transactions.  It should be either 100% safe, or not at all.

Perhaps it would also be useful to document the various strategies and
ways in which sqlite can be used with safety.  Stop telling people
that mutlithreading results in random unpredictable behavior. Be open
minded and don't hide behind whatever doctrine has been followed up
till now.

Emerson

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] html output with added tag brackets

[John Stanton]

An example of Sqlite use is in one of our applications ehere we output 
from Sqlite in XML according to a predefined DTD (HTML is XML defined by 
a particular DTD).  It is achieved by a layer of software which calls 
the Sqlite library API.  The result is structured data which can be fed 
to foreign applications and interpreted.


I notice that a number of people get confused and see the sqlite3 
program as being Sqlite instead of appreciating that Sqlite is a library 
and API for embedding SQL data access into your application, be it a PC 
program, a server daemon or an embedded device like a microcontroller 
implementing an MP3 player, a game console etc.


If you use a Firefox browser you are unwittingly already using Sqlite, 
for example.


T wrote:
Thanks Puneet and John for your input on the virtues of using tools  
other than the sqlite3 command line tool (and it's .mode html output).


OK, I won't use the .mode html output for HTML creation.

There's a question aside from that issue that I'll repost for clarity.

Thanks,
Tom


- 


To unsubscribe, send email to [EMAIL PROTECTED]
- 






-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] Re: Replace one string with another, in SELECT output

[Igor Tandetnik]

T  wrote:

Can I, within SQLite output (eg a SELECT statement) replace all
occurrences of string 1 with string 2? For instance, if in the
Description column output I want to change any occurrence of
"string1" to "string2", can it be done? I imagine something like:

SELECT Replace(Description, 'string1', 'string2') FROM MyTable


Write and register Replace as a custom function.

Igor Tandetnik

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Replace one string with another, in SELECT output

[Jay Sprenkle]

On 1/6/07, T <[EMAIL PROTECTED]> wrote:

Hi All,

Can I, within SQLite output (eg a SELECT statement) replace all
occurrences of string 1 with string 2?


Not easily. This is typically done after you get the results or by writing
a custom user function.

--
--
The PixAddixImage Collector suite:
http://groups-beta.google.com/group/pixaddix

SqliteImporter and SqliteReplicator: Command line utilities for Sqlite
http://www.reddawn.net/~jsprenkl/Sqlite

Cthulhu Bucks!
http://www.cthulhubucks.com

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] sqlite performance, locking & threading

[Fred Williams]

Sounds like a H-- of a plan to me!  

I'll call Larry Ellison and warn him to put the 12 meter yacht up for sale, 
he's about to get steam rolled! :-)

Fred

> -Original Message-
> From: Bill King [mailto:[EMAIL PROTECTED]
> Sent: Saturday, January 06, 2007 1:15 AM
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] sqlite performance, locking & threading
> 
> 
> Roger Binns wrote:
> 
> >>Im sorry for being so harsh, and i know im not winning any friends
> >>here,
> >>
> >>
> >
> >So far noone has agreed with you :-)
> >
> >  
> >
> This would be incorrect. The correct statement is "so far no one has 
> vocally agreed with you".
> 
> If people didn't agree, this whole once a month people having 
> multi-threading issues would not be the case. Surely the number of 
> people with exactly the same issues, month after month, 
> should point to 
> something? Perchance illogic?
> 
> Time I think to go off and write my own simple sql engine. 
> People here 
> are far too closed minded, and far too quick to attack others because 
> they don't follow DRH's line of thought, and decisions.
> 
> --
> ---
> To unsubscribe, send email to [EMAIL PROTECTED]
> --
> ---
> 


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] html output with added tag brackets

[T]

Thanks Puneet and John for your input on the virtues of using tools  
other than the sqlite3 command line tool (and it's .mode html output).


OK, I won't use the .mode html output for HTML creation.

There's a question aside from that issue that I'll repost for clarity.

Thanks,
Tom


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] What's the collective term for table, view, trigger etc?

[Derrell . Lipman]

T <[EMAIL PROTECTED]> writes:

> Is there a standard (or even non-standard) term that refers to the  tables,
> triggers, views, indexes (any others?) in a database?

The collective term for all of the definitions taken together is the "schema"
of the database.

Derrell

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] What's the collective term for table, view, trigger etc?

[RB Smissaert]

> Is there a standard (or even non-standard) term that refers to the  
> tables, triggers, views, indexes (any others?) in a database?

I have seen the term relations being used for this.

RBS

-Original Message-
From: Joe Wilson [mailto:[EMAIL PROTECTED] 
Sent: 06 January 2007 15:06
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] What's the collective term for table, view, trigger
etc?

> Is there a standard (or even non-standard) term that refers to the  
> tables, triggers, views, indexes (any others?) in a database?

Thingamajiggies.

__
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com 


-
To unsubscribe, send email to [EMAIL PROTECTED]

-




-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] html output with added tag brackets

[P Kishor]

Tom,

As John mentioned below, you need to distinguish between sqlite, the
db library, and sqlite3(.exe)?, the shell program commonly used to
access the data held in sqlite format via the sqlite db library.
Unfortunately, the names are similar, and that can be a source of
confusion.

Your problem lies in that, as far as I can tell, you are building an
application that seems to depend on sqlite3, the shell program. What
you should do instead is that you should ditch it, and use your
preferred method of accessing "the data held in sqlite format via the
sqlite db library." For example, my preferred method of building
applications with sqlite data is via Perl. All I want to do I can do
via Perl. Likewise, you pick your tool of choice. If you know that
tool well enough, you can do anything with it. All sqlite, the db
library, does is that it allows you to manage the data held in sqlite
format. Don't mess with sqlite3, the shell program, else you will
forever be limited by what it can do.


On 1/6/07, John Stanton <[EMAIL PROTECTED]> wrote:

The sqlite3 program is just an application program using the sqlite
lbrary.  If you have your own version of it is will be compatible with
all Sqlite V3 databases.

What changes with an Sqlite release is the library that you link into
your application.

T wrote:
> Hi John,
>
>>> But, then I need an alternate way to convert a few characters (at
>>> least & and <) in the fields in my output into the correct html
>>> codes. How can I do this?
>
>
>> Why not just make a simple change to sqlite3.exe?  You have the  source.
>
>
> Um, where do I start? Because I want to work with SQLite, not  redefine
> it. I don't want to have to add my changes and recompile it  every time
> there's a new version. I don't want to have my version  produce
> different results to everyone else. I want to be able to move  my
> specific program from one machine to another, without having to
> recompile SQLite on the other machine. I want to be able to swap a
> SQLite library in and out of my software and have it produce the same
> results. I want to be able to raise bug reports or query approaches
> knowing that everyone's using the same version as me. Because I'm not
> on a ".exe" OS ;-)
>
> ;-)
>
> Also, there's a broader question here. Can I, within SQLite (eg a
> SELECT statement) replace all occurrences of string 1 with string 2?
> For instance, if in the Description column output I want to change  any
> occurrence of "&" to "", can it be done? I imagine something  like:
>
> SELECT Replace(Description, '&', '') FROM MyTable
>
> Thanks,
> Tom
>
>
> -
>
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
>
>


-
To unsubscribe, send email to [EMAIL PROTECTED]
-





--
Puneet Kishor http://punkish.eidesis.org/
Nelson Inst. for Env. Studies, UW-Madison http://www.nelson.wisc.edu/
Open Source Geospatial Foundation http://www.osgeo.org/education/
-
collaborate, communicate, compete
=

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] What's the collective term for table, view, trigger etc?

[Joe Wilson]

> Is there a standard (or even non-standard) term that refers to the  
> tables, triggers, views, indexes (any others?) in a database?

Thingamajiggies.

__
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com 

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] When to release version 3.3.10?

[Joe Wilson]

--- [EMAIL PROTECTED] wrote:
> In some since, all changes to SQLite are released immediately.  Anybody
> can download the latest changes from CVS or look at the timeline
> (http://www.sqlite.org/cvstrac/timeline) see the changes and download
> patches.  So source code is released continuously.  All an official
> release does is increment the version number and provide binaries for
> people who don't are can't compile for themselves.  So "release" in
> the SQLite and open-source world means something very different than
> "release" for commercial software.  In the commerical world, the changes
> are unavailable until released.  For SQLite, a release merely means that
> the changes are available in a more convenient packaging.
> 
> How do these varying definitions of "release" effect this argument,
> do you suppose?

Keep in mind that a lot of projects that bundle sqlite source with their 
programs or link statically will only support official SQLite releases.
It is easier to quantify a specific SQLite release number from a support 
perspective (bug reports, documentation, etc).

__
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com 

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] html output with added tag brackets

[John Stanton]

The sqlite3 program is just an application program using the sqlite 
lbrary.  If you have your own version of it is will be compatible with 
all Sqlite V3 databases.


What changes with an Sqlite release is the library that you link into 
your application.


T wrote:

Hi John,

But, then I need an alternate way to convert a few characters (at  
least & and <) in the fields in my output into the correct html  
codes. How can I do this?




Why not just make a simple change to sqlite3.exe?  You have the  source.



Um, where do I start? Because I want to work with SQLite, not  redefine 
it. I don't want to have to add my changes and recompile it  every time 
there's a new version. I don't want to have my version  produce 
different results to everyone else. I want to be able to move  my 
specific program from one machine to another, without having to  
recompile SQLite on the other machine. I want to be able to swap a  
SQLite library in and out of my software and have it produce the same  
results. I want to be able to raise bug reports or query approaches  
knowing that everyone's using the same version as me. Because I'm not  
on a ".exe" OS ;-)


;-)

Also, there's a broader question here. Can I, within SQLite (eg a  
SELECT statement) replace all occurrences of string 1 with string 2?  
For instance, if in the Description column output I want to change  any 
occurrence of "&" to "", can it be done? I imagine something  like:


SELECT Replace(Description, '&', '') FROM MyTable

Thanks,
Tom


- 


To unsubscribe, send email to [EMAIL PROTECTED]
- 






-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] What's the collective term for table, view, trigger etc?

[P Kishor]

entities

On 1/6/07, T <[EMAIL PROTECTED]> wrote:

Hi All,

Is there a standard (or even non-standard) term that refers to the
tables, triggers, views, indexes (any others?) in a database?

Their definitions all reside in the sqlite_master table, as returned by:

sqlite> SELECT name, type FROM sqlite_master;

which yields a list like:

Staff   table
Payroll table
Paycheckview
Roster  view
NewStafftrigger

So what would I say that they all (Staff, Payroll, Paycheck, Roster,
NewStaff) are? There must be something more descriptive (and
understood?) than "database item".

Thanks,
Tom


-
To unsubscribe, send email to [EMAIL PROTECTED]
-





--
Puneet Kishor http://punkish.eidesis.org/
Nelson Inst. for Env. Studies, UW-Madison http://www.nelson.wisc.edu/
Open Source Geospatial Foundation http://www.osgeo.org/education/
-
collaborate, communicate, compete
=

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] html output with added tag brackets

[T]

Hi John,

But, then I need an alternate way to convert a few characters (at  
least & and <) in the fields in my output into the correct html  
codes. How can I do this?


Why not just make a simple change to sqlite3.exe?  You have the  
source.


Um, where do I start? Because I want to work with SQLite, not  
redefine it. I don't want to have to add my changes and recompile it  
every time there's a new version. I don't want to have my version  
produce different results to everyone else. I want to be able to move  
my specific program from one machine to another, without having to  
recompile SQLite on the other machine. I want to be able to swap a  
SQLite library in and out of my software and have it produce the same  
results. I want to be able to raise bug reports or query approaches  
knowing that everyone's using the same version as me. Because I'm not  
on a ".exe" OS ;-)


;-)

Also, there's a broader question here. Can I, within SQLite (eg a  
SELECT statement) replace all occurrences of string 1 with string 2?  
For instance, if in the Description column output I want to change  
any occurrence of "&" to "", can it be done? I imagine something  
like:


SELECT Replace(Description, '&', '') FROM MyTable

Thanks,
Tom


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] Re: .mode html output character conversion

[A. Pagaltzis]

* T <[EMAIL PROTECTED]> [2007-01-06 13:05]:
> When using SQLite's HTML output mode it converts some
> characters to HTML code, such as:
> 
> & -> 
> < -> 
> 
> But doesn't for other characters, such as:
> 
> > -> 
> " -> 
> ' -> 
> © ->  (copyright symbol)
> all other non-ascii characters
> 
> See the translation tables at:
> http://www.w3schools.com/tags/ref_entities.asp
> http://www.w3.org/MarkUp/html3/latin1.html
> 
> Is this a bug, or are the first two all that are needed in
> reality, despite the spec?

Not only in reality, but also in spec. Only for text in
attributes would `"` and `'` have to be escaped (because these
are the attribute value delimiters); and only in XML would it be
necessary to escape the `>` character (because literal `]]>`
sequences are invalid in XML). In the SQLite shell, neither is
the case.

No other characters ever *need* to be represented as entities,
since the character model of HTML documents is Unicode, not
ASCII. Escaping any such characters is necessary only when the
document encoding does not cover the full Unicode range. If the
SQLite output is in the same encoding as the HTML document, then
you need not use entities for any characters other than the two
for which the SQLite shell does.

The basics of encodings and character sets are described in this
article:

The Absolute Minimum Every Software Developer Absolutely,
Positively Must Know About Unicode and Character Sets (No
Excuses!)
http://www.joelonsoftware.com/articles/Unicode.html

If you have never read anything about the basics of charsets, you
should really read it.

Regards,
-- 
Aristotle Pagaltzis // 

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] When to release version 3.3.10?

[John Stanton]

[EMAIL PROTECTED] wrote:

Dennis Cote <[EMAIL PROTECTED]> wrote:


The knowledge that the new version will be released on a certain date
will comfort most users enough to simply wait (which might also delay
the discovery of more bugs).



Exactly.  So in order for these bugs to ever be found, there has to
be an official release with no promise of a rapid follow-on.  Tragic,
but true.



I'm not sure how much work you go through with each release




It takes about 1 days to do a thorough release.  Sometimes I cut
corners.  Notice, for example, that on the current download page
some of the products are still at 3.3.8.  Doing a release is
a non-trivial undertaking.

John Stanton <[EMAIL PROTECTED]> wrote:

We were influenced by Deming's work on total quality.  He advocates 
fixing each problem the moment it is identified and taking great pains 
never to ship product with defects.  That way quality is maximized,  The 
short term invonvenience is swamped by the long terms advantages.





In some since, all changes to SQLite are released immediately.  Anybody
can download the latest changes from CVS or look at the timeline
(http://www.sqlite.org/cvstrac/timeline) see the changes and download
patches.  So source code is released continuously.  All an official
release does is increment the version number and provide binaries for
people who don't are can't compile for themselves.  So "release" in
the SQLite and open-source world means something very different than
"release" for commercial software.  In the commerical world, the changes
are unavailable until released.  For SQLite, a release merely means that
the changes are available in a more convenient packaging.

How do these varying definitions of "release" effect this argument,
do you suppose?

--
D. Richard Hipp  <[EMAIL PROTECTED]>

The maintaining of a perpetual code base incorporating the latest fixes 
maximizes quality.  On that basis all I can say is keep doing what you 
are doing now.  Perhaps a sophistication would be to make it clearer 
that significant releases are tied basically to enhancements rather than 
bug fixes and CVS is the best source for software.




-
To unsubscribe, send email to [EMAIL PROTECTED]
-




-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] What's the collective term for table, view, trigger etc?

[T]

Hi All,

Is there a standard (or even non-standard) term that refers to the  
tables, triggers, views, indexes (any others?) in a database?


Their definitions all reside in the sqlite_master table, as returned by:

sqlite> SELECT name, type FROM sqlite_master;

which yields a list like:

Staff   table
Payroll table
Paycheckview
Roster  view
NewStafftrigger

So what would I say that they all (Staff, Payroll, Paycheck, Roster,  
NewStaff) are? There must be something more descriptive (and  
understood?) than "database item".


Thanks,
Tom


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] When to release version 3.3.10?

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

[EMAIL PROTECTED] wrote:
>  So "release" in
> the SQLite and open-source world means something very different than
> "release" for commercial software.  In the commerical world, the changes
> are unavailable until released.  For SQLite, a release merely means that
> the changes are available in a more convenient packaging.
> 
> How do these varying definitions of "release" effect this argument,
> do you suppose?

The other thing "release" means in both worlds is tested and supported.
 For example I wouldn't just take the CVS codebase at some random point
in time and deploy that.  I would only take a release version.  In fact
the only time I would ever take a non-release version is to test some
functionality/bug fix to ensure that the "release" version is ok.

Quite simply code is only going to be used once you release it.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFFn62MmOOfHg372QQRAr0eAJ94hlKjoU/Tpx2tvNv0caatWk7khQCfeDqe
liaeBVlXmt9kNvi9iRnq09w=
=pxXh
-END PGP SIGNATURE-

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] html output with added tag brackets

[John Stanton]

Why not just make a simple change to sqlite3.exe?  You have the source.

T wrote:

Hi all,

Following up my earlier question, since no replies:

I want to use SQLite's html output option to deliver query results  as 
a series of HTML rows. But I also want to insert extra HTML tags  in 
the output. How can this be done?




Example:

CREATE TABLE Payroll(name TEXT,age INTEGER,rate REAL);
INSERT INTO Payroll VALUES('Mickey',59,25);
INSERT INTO Payroll VALUES('Donald',54,22.55);




I want to ... get this:

Mickey
59
 $25

Donald
54
 $22.55




I now think this can't be done (using sqlite's .mode html output). So  I 
think I'll have to create all the html code output myself and  output 
each row of HTML as one column. For example:


CREATE TABLE Payroll(name TEXT,age INTEGER,rate REAL);
INSERT INTO Payroll VALUES('Mickey',59,25);
INSERT INTO Payroll VALUES('Donald',54,22.55);
SELECT '' || name || '' || age || 'TD> $' || rate || '' FROM  Payroll;


Which gives the desired:

Mickey
59
 $25

Donald
54
 $22.55


But, then I need an alternate way to convert a few characters (at  least 
& and <) in the fields in my output into the correct html  codes. How 
can I do this?


Thanks,
Tom

- 


To unsubscribe, send email to [EMAIL PROTECTED]
- 






-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] sqlite performance, locking & threading

[John Stanton]

Bill King wrote:

Roger Binns wrote:


Im sorry for being so harsh, and i know im not winning any friends
here,
  



So far noone has agreed with you :-)

 

This would be incorrect. The correct statement is "so far no one has 
vocally agreed with you".


If people didn't agree, this whole once a month people having 
multi-threading issues would not be the case. Surely the number of 
people with exactly the same issues, month after month, should point to 
something? Perchance illogic?


Time I think to go off and write my own simple sql engine. People here 
are far too closed minded, and far too quick to attack others because 
they don't follow DRH's line of thought, and decisions.


Good idea.  Would you share your proposed architecture with us.



- 


To unsubscribe, send email to [EMAIL PROTECTED]
- 






-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] When to release version 3.3.10?

[drh]

Dennis Cote <[EMAIL PROTECTED]> wrote:
> The knowledge that the new version will be released on a certain date
> will comfort most users enough to simply wait (which might also delay
> the discovery of more bugs).

Exactly.  So in order for these bugs to ever be found, there has to
be an official release with no promise of a rapid follow-on.  Tragic,
but true.

> 
> I'm not sure how much work you go through with each release
>

It takes about 1 days to do a thorough release.  Sometimes I cut
corners.  Notice, for example, that on the current download page
some of the products are still at 3.3.8.  Doing a release is
a non-trivial undertaking.

John Stanton <[EMAIL PROTECTED]> wrote:
> We were influenced by Deming's work on total quality.  He advocates 
> fixing each problem the moment it is identified and taking great pains 
> never to ship product with defects.  That way quality is maximized,  The 
> short term invonvenience is swamped by the long terms advantages.
> 

In some since, all changes to SQLite are released immediately.  Anybody
can download the latest changes from CVS or look at the timeline
(http://www.sqlite.org/cvstrac/timeline) see the changes and download
patches.  So source code is released continuously.  All an official
release does is increment the version number and provide binaries for
people who don't are can't compile for themselves.  So "release" in
the SQLite and open-source world means something very different than
"release" for commercial software.  In the commerical world, the changes
are unavailable until released.  For SQLite, a release merely means that
the changes are available in a more convenient packaging.

How do these varying definitions of "release" effect this argument,
do you suppose?

--
D. Richard Hipp  <[EMAIL PROTECTED]>


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] .mode html output character conversion

[drh]

T <[EMAIL PROTECTED]> wrote:
> Hi all,
> 
> When using SQLite's HTML output mode:
> 
> ..mode html
> 
> it converts some characters to HTML code, such as:
> 
> & -> 
> < -> 
> 
> But doesn't for other characters, such as:
> 
>  > -> 
> " -> 
> ' -> 
> © ->  (copyright symbol)
> all other non-ascii characters
> 
> See the translation tables at:
> http://www.w3schools.com/tags/ref_entities.asp
> http://www.w3.org/MarkUp/html3/latin1.html
> 
> Is this a bug, or are the first two all that are needed in reality,  
> despite the spec?
> 

The second.
--
D. Richard Hipp  <[EMAIL PROTECTED]>


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] SQLite 3.3.9 changes

[drh]

T <[EMAIL PROTECTED]> wrote:
> On:
> 
> http://www.sqlite.org/changes.html
> 
> it lists, among others:
> 
> > Allow constraint names on the DEFAULT clause in a table definition
> 
> What does this mean?
> 

See http://www.sqlite.org/cvstrac/tktview?tn=2109

--
D. Richard Hipp  <[EMAIL PROTECTED]>


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] Re: Error in SQLite's CSV output

[T]

Hi All,

Back in October I posted:

It seems that the csv output from SQLite has a major problem/error.  
Within CSV format, a quote should be returned as a double quote,  
within quotes. For example, if I have a field containing:


I said "Hi there"

then the CSV representation should be:

"I said ""Hi there"""

but sqlite seems to just return:

"I said "Hi there""


I've just tested the above in the new SQLite 3.3.9 and I get the  
correct:


sqlite> select 'I said "Hi there"';
"I said ""Hi there"""

and ran some other simple tests:

sqlite> select '"hi"';
"""hi"""

sqlite> select '"1,2","3",4';
"""1,2"",""3"",4"

sqlite> select 1,'"2"','"3,4"','"item 5''s 6"';
1,"""2""","""3,4""","""item 5's 6"""

sqlite> select 'line 1
   ...> line 2', 'line 2''s "second item"';
"line 1
line 2","line 2's ""second item"""

which all seem correct. Fantastic. Thanks Richard.

Now to go see if using .mode csv rather than .mode insert will make  
my program faster.


Thanks,
Tom


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] Re: When to release version 3.3.10?

[A. Pagaltzis]

* [EMAIL PROTECTED] <[EMAIL PROTECTED]> [2007-01-05 13:15]:
> The question is: should I rush out 3.3.10 to cover this
> important bug fix, wait a week to see if any other bugs
> surface, or do the usual 1-2 month release schedule and
> let people effected by this bug apply the patch above.

I think doing the usual schedule is inappropriate. The bug is not
going to affect a lot of people, but it’s a showstopper for the
few it does, so waiting it out is a bad idea.

Between the other options I would prefer immediate release unless
you have a specific suspecion that more bugs are lurking, and
then only if accompanied by notice as per Dennis’ suggestion with
a fixed date for the next release.

Regards,
-- 
Aristotle Pagaltzis // 

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] Re: What about Foreign Key support (when?)

[A. Pagaltzis]

* Marc Ruff <[EMAIL PROTECTED]> [2007-01-05 17:35]:
> So next feature to expect is Foreign Key constraints!? Great!
> 
> When? Any plans yet?

You can use triggers to enforce FKs until they are natively
enforced:



Regards,
-- 
Aristotle Pagaltzis // 

-
To unsubscribe, send email to [EMAIL PROTECTED]
-