[sqlite] Differentiate between an empty result set and an error using Python3

[Rob Sciuk]

Forgive me if this is an FAQ, but in looking over the python3 interface to 
SQLITE3, I cannot see a way to get the result code (SQLITE_OK) after an

execute() command.

My use case is to differentiate between an empty row set (OK) vs an error 
of some kind in the query.


Anyone figured this out?

Cheers,
Rob.

--
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Robert S. Sciuk r...@controlq.com
Principal Consultant905.706.1354
Control-Q Research  97 Village Rd. Wellesley, ON N0B 2T0
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Tcl interface: array not created when ...

[Rob Sciuk]

To: sqlite-users@sqlite.org
Subject: [sqlite] Bug : Tcl interface: array not created when call "db 
eval {...} ?array-name? ?script?"


[ The following text is in the "ISO-8859-1" character set. ]
[ Your display is set for the "US-ASCII" character set.  ]
[ Some characters may be displayed incorrectly. ]

Hi All!
I use sqlite3 ver 3.7.15.1 and simple test:

1. package require sqlite3
2. sqlite3 db :memory:
3. db eval {CREATE TABLE x(a,b)}
4. db eval {INSERT INTO x VALUES(1,2)}
5. #set tempvar ""
6. db eval {SELECT a,b FROM x} tempvar {
7.   puts "a = $tempvar(a)"
8. }
9. db close

When line 5 commented this test run ok. If we uncomment line 5 then
tcl interpreter
fail with error "can't read "tempvar(a)": variable isn't array". Looks 
like

sqlite engine don't check/unset variable "array-name" (like most other tcl
commands, thats accept "varName"/"arrayName").


--
dixi.

Dixi,

try replacing "set tempvar """ with:

 array set tempvar {}

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

Re: [sqlite] How to achieve fastest possible write performance for a strange and limited case

[Rob Sciuk]

I'm not sure exactly what you're asking here, but if the question is 
whether to use database blobs vs files, then you might be interested in 
this technical report from Microsoft:


arxiv.org/ftp/cs/papers/0701/0701168.pdf


--
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=--=-=-=-=
Robert S. Sciuk http://www.controlq.com 259 Simcoe St. S.
Control-Q Research  tel: 905.706.1354   Oshawa, Ont.
r...@controlq.com   Canada, L1H 4H3
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] tcl and NULL

[Rob Sciuk]

While DRH is technically correct, it is possible to use coding convention 
to drive a solution ...  trivially:

$ tclsh8.5
% proc x {} {
   return NULL
}
% puts [x]
NULL

for instance:

% proc recode { val } {
   set ret $val
   if { [string bytelength $val] == 0 } {
set ret NULL
   }
   return $ret
}

% recode "a value"
a value

% recode ""
NULL

There are, of course many reasons why a null string is not equivalent to 
NULL, but for many applications, the differences are moot.

I'm just saying ...

Cheers,
Rob Sciuk

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

[sqlite] Is there a design doc for the virtual machine re-write?

[Rob Sciuk]

I seem to recall that not too long ago, the SQLite vm was re-written for 
some reason, and I wonder if there is any documentation on the details of 
what was done, and why?  I think it may have something to do with moving 
off a stack architecture, but I don't think I ever saw a detailed 
rationale for such a major undertaking.  My concern is *NOT* SQLite 
related in any way, but rather I'm interested in VM's just now, and I was 
hoping to fall in a pile of warm steaming information related to 
VM performance etc.

I put this on the list, for fear of wasting any of D.R.H.'s time, in the 
hopes that someone can point me to something which exists (and I hope, I 
simply overlooked).

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

Re: [sqlite] What languages can include SQLite statically?

[Rob Sciuk]

From: Gilles Ganault <gilles.gana...@free.fr>
> On Tue, 1 Jun 2010 08:40:15 -0300 (BRT), Israel Lins Albuquerque
> <israel...@polibrasnet.com.br> wrote:
> >Look for C++ QT framework! http://qt.nokia.com/
> 
> Thanks, I'll check it out.

Gilles,

As you may be aware, SQLite and Tcl/Tk have an affinity which was not 
entirely accidental.  There are threads which discuss compiling Tcl for
Arm/Linux:

(http://objectmix.com/tcl/15449-how-cross-compile-tcl8-4-tk8-4-arm-linux.html)

And you might find some binaries in various places:

(http://www.evolane.com/)

If your Arm platform is a full blown Linux with the development 
environment, the problem is further reduced to a simple ./configure ; make 
; make install (or two) rather than a cross compilation using a Canadian 
Cross (tricky).

Elsewhere in this thread, someone mentioned PHP, and PHP can, in theory be 
run stand alone (without Apache).  Moving from a scripting language/SQLite 
solution to a C++/Qt/SQLite solution is quite a step, IMHO.  I'm not sure 
about the state of the Java VM on the ARM platforms, but that too might be 
a possibility ... but I'd certainly give the Tcl/Tk+SQLite a shot first. 
I've not used Lua, but I understand it to be highly portable ... but I 
have no knowledge of SQLite bindings for it ... shouldn't be hard, though.

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

Re: [sqlite] SQLite turns 10 years old (D. Richard Hipp)

[Rob Sciuk]

> Thanks, everybody, for helping to make SQLite the most widely deployed
> SQL database engine in the world.  And Happy 10th Birthday to SQLite!
> 
> D. Richard Hipp
> d...@sqlite.org

That SQLite made it ten years is testament to its focused initial design 
objectives, and unwavering adherence to same.  Couple the very generous 
license with the copious talents of those contributing to the project, and 
I believe that the next 10 years are a given.

Kudos, congratulations and most of all a deep appreciation to D. Richard 
Hipp, et.al.  Thanks, Richard.

Sincerely,
Rob Sciuk

-- 
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=--=-=-=-=
Robert S. Sciuk http://www.controlq.com 259 Simcoe St. S.
Control-Q Research  tel: 905.576.8028   Oshawa, Ont.
r...@controlq.com   fax: 905.576.8386   Canada, L1H 4H3
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Pragmas and compile options ...

[Rob Sciuk]

I was just wondering whether it might be possible in some future version 
of SQLite to expose the pragma and compile time options settings in the 
form of a table:

select * from sqlite_pragma ;
select * from sqlite_options ;

This might also allow update to set the value of a pragma, while one might 
reasonably expect that compile time options are read only ... though 
complete and documented at runtime.

The sqlite_master currently contains the DDL and useful metadata, I just 
figured extending this model in accordance with the relational model might 
be useful.  Of course, in order to avoid bloat in the embedded space, this 
functionality could be compiled in as an option 8-), perhaps tied to the 
SQLITE_ENABLE_COLUMN_METADATA compiler option.

While I haven't inspected the SQLite code with respect to implementing 
this, I suspect that it might take a fair bit of effort, and I'm not 
entirely sure that it is the best return on investment (eg: the recent 
support of foreign keys -- NICE!!), but I put it on the table for 
consideration ... naively and without understanding of the impact upon 
code organization 8-).

Just a humble suggestion.

Cheers,
Rob Sciuk





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

[sqlite] An SQLite Server ...

[Rob Sciuk]

Dear List,

Recently, a member of this list posed a question regarding the 
availability of a server interface for SQLite, and a number of replies 
were posted.

Intrigued, I followed up with the OP, and provided a prototype tool based 
upon an off the shelf library I've been developing for some time.  It 
currently runs only on Linux and 'BSD (for now but Windows is simple), and 
served up a single database on a port of the user's choosing with a telnet 
like interface (no protocol).

Going through a few gyrations, the OP decided that an HTTP_1.1 protocol 
was most suitable to his needs, and so I provided a test tool which 
supported querying the database via a HTTP/POST method, and which could be 
tested with a simple browser.  This is available (by request) for test 
purposes, as I'm looking to add features/functions on a market driven 
basis.  The question, of course, is there a demand for such a thing?

Is there widespread interest in this type of product?

What would you use it for?

What platforms would you want it to run on?

Is HTTP a suitable protocol for your requirements?

How would you like the query data returned?  (for example .CSV, delimited, 
HTML TABLE, XML, Other?)

To avoid undue noise on this excellent but busy list, I suggest a response 
to me by private email, and I'll post back at some point with what I've 
learned and or decided.  If you use "SQLite Server" in the subject line, 
I'll endeavour to avoid data loss due to aggressive spam filtering 8-).

Cheers,
Rob Sciuk
-- 
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=--=-=-=-=
Robert S. Sciuk http://www.controlq.com 259 Simcoe St. S.
Control-Q Research  tel: 905.576.8028   Oshawa, Ont.
r...@controlq.com   fax: 905.576.8386   Canada, L1H 4H3
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLITE_ENABLE_COLUMN_METADATA question ...

[Rob Sciuk]

On Mon, 26 Oct 2009, D. Richard Hipp wrote:

> sqlite3_column_table_name() (and all of the other METADATA functions) only 
> work on table columns, not on functions.  The documentation says as much, 
> though perhaps it could be worded more directly.  I'll make a note to clarify 
> the documentation.
>
>
> D. Richard Hipp
> d...@hwaci.com

I understand, and I must have missed the relevant documentation.  I'm 
wondering, though, there should be a reliable way to return a list of the 
affected tables from a prepared statement, no?

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

Re: [sqlite] SQLITE_ENABLE_COLUMN_METADATA question ...

[Rob Sciuk]

On Mon, 26 Oct 2009, Jay A. Kreibich wrote:

>  The documentation on this is fairly clear:
>
>  http://sqlite.org/c3ref/column_database_name.html
>
>  If the Nth column returned by the statement is an expression or
>  subquery and is not a column value, then all of these functions
>  return NULL. These routine might also return NULL if a memory
>  allocation error occurs. Otherwise, they return the name of the
>  attached database, table and column that query result column was
>  extracted from.

Hmm, fair enough.

>
>  Basically, in order for these functions to work, the returned column
>  needs to be a raw, unaltered, column directly from a specific table.
>  Any kind of expression (including aggregate functions) means the
>  data values in that result did not come directly from a specific
>  table column, and therefore these functions return NULL.
>
>  If you need a more general way to get the name of any column in any
>  query, you most likely want sqlite3_column_name().
>
>   -j

I actually need a general way to get the TABLE name from a query ... hmmm 
... back to the documentation ...

Thanks for your very kind reply.

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

[sqlite] SQLITE_ENABLE_COLUMN_METADATA question ...

[Rob Sciuk]

I've just compiled the latest (3.6.19) with the

-DSQLITE_ENABLE_COLUMN_METADATA flag set.  The problem I'm seeing is that 
when I use an aggregate function in a select, the table name is not being 
returned from an sqlite3_column_table_name() is not returning the 
tablename as expected from a prepared statement, whereas if I actually 
request data from the table, it works fine:

eg:
If I prepare the statement "select * from q_user", q_user is returned as 
expected from sqlite3_column_table_name().

If I prepare the statement "select count(*) from q_user", 
sqlite3_column_table_name() returns NULL.

This is somewhat disconcerting, and seems repeatable.  Are aggregate 
functions handled differently than tuple data?

Are there any other dependancies other than SQLITE_ENABLE_COLUMN_METADATA?

Is there anything I might have overlooked??

Any ideas??

Thanks in advance,
Rob Sciuk
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] manipulating arguments (in C)

[Rob Sciuk]

Perhaps this might lead you in the right direction Jean-Christophe ...

#include 
#include 
#include 

Str_t  s_format( Str_t fmt, ... ){
   va_list  ap ;
   Int_tnx ;
   Byt_tbuf[1] ;
   Str_tqptr ;

   va_start( ap, fmt );
nx = vsnprintf( buf, 0, fmt, ap ) ;
   va_end( ap ) ;

   qptr = (Str_t) q_calloc( 1, nx+1 ) ;

   va_start( ap, fmt ) ;
nx = vsnprintf( qptr, nx+1, fmt, ap ) ;
   va_end( ap ) ;

   return qptr ;
}

-- 
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=--=-=-=-=
Robert S. Sciuk http://www.controlq.com 259 Simcoe St. S.
Control-Q Research  tel: 905.576.8028   Oshawa, Ont.
r...@controlq.com   fax: 905.576.8386   Canada, L1H 4H3
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] GUI design & managment tool?

[Rob Sciuk]

On Wed, 5 Aug 2009, Allen Fowler wrote:
>> I would recommend SQLite Studio without hesitation.  I think it pretty
>> much covers your criteria, have a look:
>>
>> http://sqlitestudio.one.pl/index.rvt?act=about
>>
>> It is fast, graphical, a single executable install (eg: trivial), and
>> works well with existing databases ...  I've just started playing with
>> it on FreeBSD, and I must say that so far, I'm much impressed with the
>> tool.
>>
>
> very nice.  simple & functional.  Works on Linux and windows.
>
> However, sadly, I do not see any GUI tool for relationship design.  Did I 
> miss it?

Sorry, Allen, I've just stumbled onto it in the past 24 hours, and I'm 
still learning myself.  Mostly I've used it to explore existing 
applications, and it truly shines at this ... anyway, I hope it is of some 
help to you ...

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

Re: [sqlite] GUI design & managment tool?

[Rob Sciuk]

From: Allen Fowler <allen.fow...@yahoo.com>
> 
> Hello,
> 
> Can anyone recommend a Free, or reasonably priced Non-Free, GUI tool for 
> creating and maintaining an SQlite databases that can run on both Windows 
> and Linux?
> 
> (Support for visual relation design would be great, too.)
> 
> I found a list at: http://www.sqlite.org/cvstrac/wiki?p=ManagementTools
> 
> But, I was wondering if anyone has personal experience to share...
> 
> Thanks, :)

I would recommend SQLite Studio without hesitation.  I think it pretty 
much covers your criteria, have a look:

http://sqlitestudio.one.pl/index.rvt?act=about

It is fast, graphical, a single executable install (eg: trivial), and 
works well with existing databases ...  I've just started playing with 
it on FreeBSD, and I must say that so far, I'm much impressed with the 
tool.

HTH.
Cheers,
Rob Sciuk

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

Re: [sqlite] Ability to convert Access to SQLite

[Rob Sciuk]

I believe that one poster (Rich Shepard?) touched upon the MDB-Tools in a 
response.  I concur, if you have the skills to build these tools from 
source, the rest is easy ...

The MDB Tools project hosted on SourceForge has a set of utilities which 
can enumerate tables, pull data, and schema info and convert same from 
.jet or .mdb databases into a vanilla SQL dialect which is very easy to 
convert to SQLite.

I have copied numerous large .mdb files from windows onto BSD or Linux 
platforms and successfully converted same to SQL databases on various 
occasions, and the mdb-tools are quite useful in this regard.  YMMV.

http://sourceforge.net/projects/mdbtools/files/

Hope this helps ...

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

Re: [sqlite] sqlite programmed in C++ ...

[Rob Sciuk]

John Stanton wrote:
> This is something of a digression but is pertinent.  Colleagues who
> worked with Bjarne Thorstrup (inventer of C++) tell me that Bjarne was
> disillusioned with C++ and its wide deployment and would encourage
> people not to use it unless there were clear advantages.
> 
> In our own company we came to the same conclusion as Dr Hipp and used
> ANSI C for our compilers and database software.  C can be anything you
> want it to be.  For example you can ensure portability by incorporating
> your own  memory management system and tightly manage your use of
> foreign libraries. for quality assurance  You have access to highly
> optimizing compilers which can produce executables as good as those
> written by a skilled Assembler programmer.

Good points.

IIRC, Firebird, once a C based database system (Interbase by Borland), was 
re-written in C++ by a team of people who simply "liked" C++.  The change 
happened between version 1.x and 2.x I think.  The upshot is, there was a 
fork in the project, but the 1.x code lives on.  It seemed to me a lot of 
effort and I'm not sure what gains they are claiming, because I lost all 
interest in Firebird when I realized that the project team were about to 
pour a whole bunch of resources into re-writing it for its own sake, when 
better returns could have been made on upgrading the existing code.

Personally, I like SQLite (and ANS C for that matter) just fine the way they
are.  Perhaps Sylvain would prefer to have a look at the Firebird 2.x project
for a C++ based DBMS should the implementation language be an issue.

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

[sqlite] Building SQLite ... configure/make deprecated?

[Rob Sciuk]

I note with interest the source download now (recently?) deprecates the 
configure/make build of SQLite 3.6.x sources.  What gives??  I've been 
configure/make(ing) SQLite since, forever, and the only problems I ever 
encountered were omitting options that I needed, and quickly sorted out!

How does one actually obtain the "amalgamation", if not by using the 
configure/make scripts, complete with lemon and friends??

from the web site:

"A tarball of the complete source tree for SQLite version 3.6.13
as extracted from the version control system. The Makefile and configure
script in this tarball are not supported. Their use is not recommended.
The SQLite developers do not use them. You should not use them either. 
If
you want a configure script and an automated build, use either the
amalgamation tarball or TEA tarball instead of this one. To build from
this tarball, hand-edit one of the template Makefiles in the root
directory of the tarball and build using your own customized
Makefile."

Perhaps this is just me, but it seems a retrograde approach from a 
Unix/Linux developer perspective ...
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] [newbie] Binary and text-file storage in SQL(ite).

[Rob Sciuk]

Nuno wrote:
> Yeah, a religious question, i know, but what's recomended?...
> 
> I have a small site that may have some images for some products. Said
> products may also have descriptions. I have other small sites and one
> where i'd like to manipulate large texts (say around 5 paragraphs...
> ~5000 characters).
> 
> What's recomended: to store strings that are the /path/to/textfile or
> /to/image.jpg? Or to store the image file in the database? the text
> file in the database? Which datatypes to use?
>

This is a very good question, and you must ask it every time you consider 
blobbing. It turns out that all things being equal, there is a size at 
which blobs work better, and over which the file system is appropriate.

Here is a discussion paper:

   http://research.microsoft.com/apps/pubs/default.aspx?id=64525

This paper was written some time ago (2006), and technology moves, CPUs, 
networks and disk drives get faster, memory larger, and some operating 
systems become more bloated. As various aspects which affect this high 
water rule of thumb change, so must your confidence in the actual high 
water mark number.

Because you are using (?) SQLite, a number of variables are different than 
accessing a client/server database, but as it seems that you are going to 
squeeze the data through a web server, other bottlenecks may apply. 
Experimentation is recommended, but the real qualifier will come at 
saturation point.

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

Re: [sqlite] archiving memory databases

[Rob Sciuk]

> From: "DeTerra, David J" 
> Hi all,
> 
> I was wondering if there is a feature in sqlite that would allow a set
> of in-memory databases to periodically be written to disk for purposes
> of database recovery on application or machine failure?
> 
> And then subsequently read those persisted database files back into a
> memory database on restart?
> 
> Thanks,
> David

Actually, given the embedded nature of SQLite, I could see a use for this. 
Provide a function which would provide a Blob like interface (ptr/len) to 
a memory database in a quiescent/consistent state, which could 
subsequently be saved to disk, or indeed, as a blob within a database, and 
then at some time in future be re-constituted as a live memory database 
... hmmm ...


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

[sqlite] Error handling anomaly ...

[Rob Sciuk]

In a test harness, I'm using a sqlite3_prepare/bind/step/finalize sequence 
to add rows to a table, and then add them again, intentionally violating 
the unique index constraints.  It appears that sqlite3_step reports the 
violation (sqlite3_errorMessage), and then the details (the offending 
column numbers) are only reported when I check the error in the 
sqlite3_finalize() routine ...

::: Error :::
sq3_step: Error 19 returned from sqlite3_step.
constraint failed

::: Error :::
sq3_finish: Error 19 returned from sqlite3_finalize.
columns sname, fname, dob are not unique

This is not a problem, per se, but in an ideal world, one might handle the 
error one time, and receive all the relevant information at one go.  Is 
there a reason to defer reporting the details until the transaction is 
completed?

Just wondering ...

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

Re: [sqlite] Violating Primary key Constraint

[Rob Sciuk]

> Hi All!
>
>   I have created a table in sqlite.Upto my knowledge a column which is
> declared as primary key will not accept null and even if I don't give any
> value to that field an error occurs that violating the constraint
> 
> For Example
> 
> Create table emp(empno integer PRIMARY KEY,...,...)
> 
> . Even if I give u a NULL as a value to the field that is declared
> as primary key .it is accepting
> 
> . Even if I Don't give any value to the field that is declared as
> primary key it is auto incrementing. Instead of showing the error 
> constraint violated it is  auto increment that field value(I did not specify 
> the 
> column to auto increment).
> 
>

Satish,

If you don't want the magical autoincrement feature try defining your 
table using an alternate syntax:

create table emp (
empno integer not null,
...
    primary key (empno)
) ;

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

Re: [sqlite] HELP: prep'ed query ... LF function to return column_isNull??

[Rob Sciuk]

for the "type" or domain of
>  the column with the existing API.   Chances are, you're just not used
>  to asking questions like "is this a BLOB?" because you've never had
>  to deal with a database that might offer different answers to that
>  question for the same column.

Arguable, yes.  The thing about the 'blob' which makes it special, is that 
the onus is put upon the user to classify the domain of a blob.  Is it an 
image? if so what type? if not then what?  how do I 
enter/modify/retrieve/delete it?  I think the varchar or integer/NULL vs 
Blob/NULL argument is not quite the same argument, but I will grant you 
for the sake of argument, there are certain similarities.

>
>  That's not good or bad... I've got a lot of different and sometimes
>  conflicting opinions on Manifest Typing, although I have to admit
>  that, at the end of the day, I think it is a positive point for SQLite and
>  the market it is trying to serve.  But that's a different can of worms.

Of course, I see this, and I would never argue for a different approach 
than manifest typing for SQLite ... it has many advantages, and really 
lends itself to the scripting approach (tcl/tk) from whence it seems to 
have been derived.  I like tcl/tk almost as much as I like SQLite!!.

That being said, I understand that with each sqlite3_step() of a prepared 
query, and as one would expect -- the data values will change, and in 
SQLite, so also may the type, so a combination of sqlite3_column_xxx() and 
sqlite3_column_type() will give me what I want -- either the actual 
value, or an indication that a NULL value/type/thingy would be returned.

>
>
>  Most of this is related to the mental model each of us has about how
>  SQL, relational systems, and our specific database needs fit
>  together.  I'm not trying to say your own model is "wrong", or that
>  the SQLite way is the best way.  I'm only trying to show that if you
>  see things from a specific viewpoint, they're at least consistent.
>
>   -j

I suppose my problem, and I don't believe that I really have one, arises 
more because the software I'm writing is database agnostic, and will 
eventually run on PostGreSQL/MySQL/Firebird? and possibly commercial 
offerings as well.  Moreover, I am implementing a rich data dictionary on 
top of the underlying databases, so ultimately this problem quickly 
disappears once past the low level dbms driver ...

I have no problem with SQLite per se, nor with its approach to typing, nor 
with the existing SQLite API ... but I *DID* have a couple of unexpected 
stumbles in translating documentation into practice owing to the 
differences between "my mental model", as you put it, and SQLite's 
consistent handling and "orthogonal" API.

In this regard, and given your very excellent defense of SQLite vis a vis 
manifest typing and NULL handling, would it really break either the 
consistent handling of NULL, or SQLite's manifest typing to add 
sqlite3_column_isNULL() to the API?? It would have certainly simplified 
life for me, at what I believe to be little to no cost with respect to 
consistency -- even for the purists.

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

Re: [sqlite] HELP: prep'ed query ... LF function to return column_isNull??

[Rob Sciuk]

> From: "Igor Tandetnik" <[EMAIL PROTECTED]>
> > "Rob Sciuk" <[EMAIL PROTECTED]> wrote in
> >
> > The other bit of trickery involved is that in order for the value
> > types to be correct (meaningful), you have to take a "step" as it
> > were ... with sqlite3_step 8-).
> 
> You can use sqlite3_column_decltype[16] without calling sqlite3_step
> first. This gives you the "syntactic" type, the type that can be
> inferred from the text of the statement and the database schema.
> 
> Igor Tandetnik

Thanks, Igor, but I'm not looking for the type (either declared or 
affined), but rather whether the actual value is valid, or NULL.  That was 
the point of the original post.  My problems arose from the fact that it 
is the sqlite3_column_type() function which returns the fact that the 
current value is NULL, and this is somewhat counterintuitive.

In my books, the data type is the type, and the data value is either NULL, 
or has a value in the domain of that data type.  One would not query the 
data type to determine whether the value of the datum is defined or not. 
Of course, SQLite has a unique affinity model, and I'm still wrestling 
with this, though in my gut, I feel that along with the 
sqlite3_column_xxx() functions, should be a sqlite3_column_isNULL() 
function.

Indeed, in a former life I have had a great deal of experience with 
SIR/DBMS, which is able to differentiate between undefined, NULL and up to 
three user defined MISSING VALUES per data item, and moreover to 
differentiate amoung them.  Of course, SIR/DBMS was not a general purpose 
database, and specialized in scientific applications, and particularly 
statistical analysis where such subtlety was important.

So, again, to those who offered advice, thanks, and I'm quite ok with the 
way things work, now that I know how to proceed -- I'm just saying ... 
8-).

Cheers,
Rob Sciuk

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

Re: [sqlite] HELP: prep'ed query ... LF function to return column_isNull??

[Rob Sciuk]

On Sat, 8 Nov 2008, Simon Davies wrote:
>
> Hi Rob,
>
> I haven't tried this, but sqlite3_column_type(sqlite3_stmt*, int iCol)
> should do what you want:
> http://www.sqlite.org/c3ref/column_blob.html
>
> Rgds,
> Simon

Thanks, Simon.

I found this out about two minutes after I'd posted the query.

At first, I figured that to be an awkward approach, as I felt that the 
column_isNULL function should follow the 'get data' functions (type vs 
value), but given SQLite's affinity model, and upon reflection, I suppose 
that it is the correct approach, even though slightly non-intuitive.

The other bit of trickery involved is that in order for the value types to 
be correct (meaningful), you have to take a "step" as it were ... with 
sqlite3_step 8-).  In any event, I'm all sorted out, and I appreciate your 
very quick and accurate response.

Cheers,
Rob Sciuk

PS:  I've said it before, and at risk of repetition, thanks and kudos to 
DRH for what is clearly one of the most remarkable examples of FOSS 
*EVER*.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] prep'ed query ... LF function to return column_isNull?? (fwd)

[Rob Sciuk]

> 
> I don't see in the documentation a function to return whether or not the 
> database value returned by the sqlite3_step() function is NULL.  Surely there 
> should be such a beast, no?
>
>  Something like:
>   int sqlite3_column_isNull( stmt, i ) ;
> or even:
>   int sqlite3_column_isDefined( stmt, i ) ;
> 
> 
> This should work across all datatypes, and simply allow proper null handling 
> -- 
> not relying upon affinity dependant conversions ... hopefully, I've simply 
> overlooked something really obvious, but at this moment, is opaque to me ... 
> any pointers??
>

Ooops, it appears that sqlite3_column_type( smt, i ) returns SQLITE_NULL iff 
the data are missing.  Is this correct??

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

[sqlite] HELP: prep'ed query ... LF function to return column_isNull??

[Rob Sciuk]

I don't see in the documentation a function to return whether or not the 
database value returned by the sqlite3_step() function is NULL.  Surely 
there should be such a beast, no?

  Something like:
int sqlite3_column_isNull( stmt, i ) ;
 or even:
int sqlite3_column_isDefined( stmt, i ) ;


This should work across all datatypes, and simply allow proper null 
handling -- not relying upon affinity dependant conversions ... hopefully, 
I've simply overlooked something really obvious, but at this moment, is 
opaque to me ... any pointers??

Cheers,
Rob Sciuk

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

Re: [sqlite] Tcl/SQLite application best practices ???

[Rob Sciuk]

On Fri, 17 Oct 2008, Michael Schlenker wrote:
> It depends a bit on what you try to write and what other constraints you
> have or are ok to accept.
>
> Michael

Danke Michael.

Yes, I see what you mean.  My intent was of course to mate a generic 
procedural tcl with sqlite, and add a rich data dictionary layer.  This 
would allow the gui to be generated from ddl at runtime.  The tricky bits
are the keys/links between relations, so that the forms will recognize, 
respect and "do the right thing" for 1:N relationships.

I will review your pointers, but I had not wanted to add an additional oo 
layer if I can help it ...

Cheers,
Rob.

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

Re: [sqlite] tcl/sqlite glitch on FreeBSD ...

[Rob Sciuk]

On Mon, 28 Jul 2008, Rob Sciuk wrote:
>> top of mutex.h:
>> 
>> #ifdef __FreeBSD__
>> #define SQLITE_HOMEGROWN_RECURSIVE_MUTEX 1
>> #endif

OK, I've tracked the resulting glitch to a rivet dependancy, and the code 
now works with the suggested define included!!!  I'm not entirely sure the 
status of recursive mutexes on FreeBSD, and/or whether 7.0R has the same 
requirement, but I might humbly suggest that the above fix might be useful 
on FreeBSD ... for now.

Many thanks to DRH for a gentle prod in the correct direction ...

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

Re: [sqlite] tcl/sqlite glitch on FreeBSD ...

[Rob Sciuk]

Wow!,  Thanks for your very swift reply, Sir.

On Mon, 28 Jul 2008, D. Richard Hipp wrote:

> Perhaps FreeBSD does not implement recursive mutexes.  In that case, you can 
> try to recompile with -DSQLITE_HOMEGROWN_RECURSIVE_MUTEX=1 and see if that 
> helps.  It would be good to review the comments in mutex_unix.c to see if the 
> HOMEGROWN_RECURSIVE_MUTEX implementation really is safe for FreeBSD.
>
> D. Richard Hipp
> [EMAIL PROTECTED]

OK, I just switched to sqlite3.6.0, and I added the following lines to the top 
of mutex.h:

#ifdef __FreeBSD__
  #define SQLITE_HOMEGROWN_RECURSIVE_MUTEX 1
#endif


The Tcl/SQLite code now runs to completion(?), and then cored with the 
following dump.
I'm tracking this down, but it does not appear to involve SQLite ... hmmm ... 
it sort of
appears to be a cleanup of the dlload of the sqlite module ... not quite sure 
though.

Core was generated by `tclsh8.5'.
Program terminated with signal 11, Segmentation fault.
Reading symbols from /usr/local/lib/libtcl8.5.so...(no debugging symbols 
found)...done.
Loaded symbols for /usr/local/lib/libtcl8.5.so
Reading symbols from /lib/libm.so.4...(no debugging symbols found)...done.
Loaded symbols for /lib/libm.so.4
Reading symbols from /lib/libc.so.6...(no debugging symbols found)...done.
Loaded symbols for /lib/libc.so.6
Reading symbols from /libexec/ld-elf.so.1...(no debugging symbols 
found)...done.
Loaded symbols for /libexec/ld-elf.so.1
#0  0x282acda0 in ?? ()
(gdb) bt
#0  0x282acda0 in ?? ()
#1  0x28051180 in wlock_release () from /libexec/ld-elf.so.1
#2  0x28050934 in dlclose () from /libexec/ld-elf.so.1
#3  0x28134d27 in TclpUnloadFile () from /usr/local/lib/libtcl8.5.so
#4  0x280fcb45 in TclFinalizeLoad () from /usr/local/lib/libtcl8.5.so
#5  0x280d0f46 in Tcl_Finalize () from /usr/local/lib/libtcl8.5.so
#6  0x280d0dc5 in Tcl_Exit () from /usr/local/lib/libtcl8.5.so
#7  0x2809c19f in Tcl_ExitObjCmd () from /usr/local/lib/libtcl8.5.so
#8  0x280941a7 in TclEvalObjvInternal () from /usr/local/lib/libtcl8.5.so
#9  0x280d3553 in TclExecuteByteCode () from /usr/local/lib/libtcl8.5.so
#10 0x280d1fa3 in TclCompEvalObj () from /usr/local/lib/libtcl8.5.so
#11 0x28095394 in TclEvalObjEx () from /usr/local/lib/libtcl8.5.so
#12 0x28095029 in Tcl_EvalObjEx () from /usr/local/lib/libtcl8.5.so
#13 0x280fd833 in Tcl_Main () from /usr/local/lib/libtcl8.5.so
#14 0x0804860a in main ()



-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=--=-=-=-=
Robert S. Sciuk http://www.controlq.com 259 Simcoe St. S.
Control-Q Research  tel: 905.576.8028   Oshawa, Ont.
[EMAIL PROTECTED]   fax: 905.576.8386   Canada, L1H 4H3
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] tcl/sqlite glitch on FreeBSD ...

[Rob Sciuk]

I've enabled the thread-safe option, and compiled sqlite3.5.9 against 
tcl8.5.3, also PDO in PHP 5.2.5, and used it in web sites both with PHP, 
and Tcl/Rivet under Apache 1.3.41.  Sqlite seems to consistently choke 
either in rivet, or under PHP with a similar problem:

Fatal error 'Recurse on a private mutex.' at line 988 in file
/usr/src/lib/libpthread/thread/thr_mutex.c (errno = 9)
Abort (core dumped)

My apache logs are full of these messages ... and I get the same thing 
when I run under tcl manually ...

I configured sqlite3.5.9 with --enable-threadsafe 
--with-tcl=/usr/local/lib [the path containing the current tclConfig.sh 
file], and did a normal compile/install which was uneventful (though I had 
to copy the libtclsqlit* libraries out of the .libs directory to 
/usr/local/lib by hand, as the make install did not do this for me).

Has anyone seen sqlite tilt in this manner before?  Am I missing 
something?  I will work on a minimum tcl/php script to cause the problem, 
but surely this cannot be a completely isolated problem, as sqlite is 
everywhere, isnt' it 8-)?

Cheers,
Rob.

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

[sqlite] Is this list available in *DIGEST* form??

[Rob Sciuk]

How do I sign up for the digest rather than the regular feed??

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

[sqlite] SQLite 3.5.5 make test on OpenBSD 4.2 on HPPA

[Rob Sciuk]

rerun, with chocolate directory removed prior to test run.

./testfixture ../test/quick.test
bind-4.4...
Error: floating point value is Not a Number
bind-4.5...
Expected: [null real real]
 Got: []
btree-2.1.1...CURSOR 4041F588 rooted at1(rw) currently at 1.0 eof
PAGE 1:  flags=0x09  frag=0   parent=0
cell  0: i=913..1023  chld=0nk=2020 nd=1792 payload=*** This is a very 
PAGE 2:  flags=0x08  frag=0   parent=0

PAGE   1 addr=4370A094 nRef=2
Skipping crash5 tests: not compiled with -DSQLITE_MEMDEBUG...
lock4-1.3...
Error: database is locked
Skipping malloc5 tests: not compiled with -DSQLITE_MEMDEBUG...
Skipping malloc6 tests: not compiled with -DSQLITE_MEMDEBUG...
Skipping malloc7 tests: not compiled with -DSQLITE_MEMDEBUG...
Skipping malloc8 tests: not compiled with -DSQLITE_MEMDEBUG...
Skipping malloc9 tests: not compiled with -DSQLITE_MEMDEBUG...
Skipping mallocA tests: not compiled with -DSQLITE_MEMDEBUG...
Skipping mallocB tests: not compiled with -DSQLITE_MEMDEBUG...
Skipping mallocC tests: not compiled with -DSQLITE_MEMDEBUG...
Skipping mallocD tests: not compiled with -DSQLITE_MEMDEBUG...
Skipping mallocE tests: not compiled with -DSQLITE_MEMDEBUG...
Skipping mallocF tests: not compiled with -DSQLITE_MEMDEBUG...
Skipping mallocG tests: not compiled with -DSQLITE_MEMDEBUG...
printf-1.7.6...
Expected: [Three integers: (100) ( f4240) (3641100)]
 Got: [Three integers: ( 100) ( f4240) (3641100)]
printf-1.8.6...
Expected: [Three integers: (9) (3b9ac9ff) (7346544777)]
 Got: [Three integers: ( 9) (3b9ac9ff) (7346544777)]
printf-1.9.7...
Expected: [Three integers: ( 0) (   0x0) ( 0)]
 Got: [Three integers: ( 0) ( 0) ( 0)]
Skipping safety tests since SQLITE_DEBUG is off
time with cache: 39849317 microseconds per iteration
time without cache: 144188993 microseconds per iteration
result=one 10 three 5 two 14
speed3-1.incrvacuum..  4612508 uS216.80179 row/s
speed3-1.normal... 9956014 uS100.44180 row/s
Incrvacuum: Read 1098, wrote 94
Normal: Read 10479, wrote 43
speed3-2.incrvacuum..  1801945 uS554.95589 row/s
speed3-2.normal... 3599141 uS277.84407 row/s
Incrvacuum: Read 2843, wrote 1
Normal: Read 10011, wrote 0
tcl-1.6...
Expected: [1 {syntax error in expression "x*"}]
 Got: [1 {invalid bareword "x"
in expression "x*";
should be "$x" or "{x}" or "x(...)" or ...}]
vtab6-2.2...
Expected: [1 2 3 {} 2 3 4 1 3 4 5 2]
 Got: [1 2 3 {} 2 3 4 {} 3 4 5 {}]
vtab6-2.4...
Expected: [1 2 3 {} {} {} 2 3 4 {} {} {} 3 4 5 1 2 3]
 Got: [1 2 3 {} {} {} 2 3 4 {} {} {} 3 4 5 {} {} {}]
vtab6-2.5...
Expected: [2 3 4 {} {} {} 3 4 5 1 2 3]
 Got: [2 3 4 {} {} {} 3 4 5 {} {} {}]
vtab6-2.6...
Expected: [1 2 3 {} {} {} 2 3 4 {} {} {}]
 Got: [1 2 3 {} {} {} 2 3 4 {} {} {} 3 4 5 {} {} {}]
vtab6-7.1...
Expected: [1 999 999 2 131 130 999]
 Got: [1 999 999 999 999 999 999]
vtab6-9.1.1...
Expected: []
 Got: [2 22 {}]
vtab6-9.2...
Expected: []
 Got: [2 22 {}]
14 errors out of 38178 tests
Failures on these tests: bind-4.4 bind-4.5 lock4-1.3 printf-1.7.6 printf-1.8.6 
printf-1.9.7 tcl-1.6 vtab6-2.2 vtab6-2.4 vtab6-2.5 vtab6-2.6 vtab6-7.1 
vtab6-9.1.1 vtab6-9.2
All memory allocations freed - no leaks
Maximum memory usage: 14162418 bytes
Current memory usage: 0 bytes
*** Error code 1

Stop in /u0/sw/SQLite/sqlite-3.5.5/OpenBSD (line 568 of Makefile).

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

Re: [sqlite] SQLite 3.5.5 on OpenBSD running on HPPA (HP9000) make test

[Rob Sciuk]

On Fri, 1 Feb 2008, [EMAIL PROTECTED] wrote:

We do not understand the async3 problem and cannot reproduce it.
All the other issues have been fixed in CVS HEAD.



Actually, upon inspection, that may be an artifact of an earlier attempt 
to run the tests as root, the "chocolate" directory was, in fact owned by 
root.  I'm re-running the tests, having removed that directory, and I'll 
repost the results upon completion ...


Cheers,
Rob.

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

[sqlite] SQLite 3.5.5 on OpenBSD running on HPPA (HP9000) make test

[Rob Sciuk]

Dear DRH,

I post the previous (FreeBSD/Ubuntu) and this *ONLY* to exercise the new
3.5.5 bits given the wholsale changes.  I hope you find them useful, but
AFAICT, 3.5.5 looks pretty good from a perf/stability standpoint.

In the obscure machine/OS category, I lit this up on an HP9000 K200 
running not HP-UX, but rather OpenBSD 4.2 (heh heh).  Build was 
uneventful, and testfixture ran to completion with the following output.


$ uname -a
OpenBSD hppa.controlq.com 4.2 xyzzy#0 hppa
$ make test | grep -v Ok | tee test.output.HPPA

./testfixture ../test/quick.test
async3-1.0...
Error: error deleting "chocolate/banana/vanilla/file.db": permission denied
async3-1.1...
Error: table abc already exists
async3-1.2.2...
Expected: [1 {database is locked}]
 Got: [1 {attempt to write a readonly database}]
async3-1.3.2...
Expected: [1 {database is locked}]
 Got: [1 {attempt to write a readonly database}]
async3-1.4.2...
Expected: [1 {database is locked}]
 Got: [1 {attempt to write a readonly database}]
async3-1.5.2...
Expected: [1 {database is locked}]
 Got: [1 {attempt to write a readonly database}]
async3-1.6.2...
Expected: [1 {database is locked}]
 Got: [1 {attempt to write a readonly database}]
async3-1.7.2...
Expected: [1 {database is locked}]
 Got: [1 {attempt to write a readonly database}]
bind-4.4...
Error: floating point value is Not a Number
bind-4.5...
Expected: [null real real]
 Got: []
btree-2.1.1...CURSOR 485C3688 rooted at1(rw) currently at 1.0 eof
PAGE 1:  flags=0x09  frag=0   parent=0
cell  0: i=913..1023  chld=0nk=2020 nd=1792 payload=*** This is a very 
PAGE 2:  flags=0x08  frag=0   parent=0

PAGE   1 addr=42205894 nRef=2
Skipping crash5 tests: not compiled with -DSQLITE_MEMDEBUG...
lock4-1.3...
Error: database is locked
Skipping malloc5 tests: not compiled with -DSQLITE_MEMDEBUG...
Skipping malloc6 tests: not compiled with -DSQLITE_MEMDEBUG...
Skipping malloc7 tests: not compiled with -DSQLITE_MEMDEBUG...
Skipping malloc8 tests: not compiled with -DSQLITE_MEMDEBUG...
Skipping malloc9 tests: not compiled with -DSQLITE_MEMDEBUG...
Skipping mallocA tests: not compiled with -DSQLITE_MEMDEBUG...
Skipping mallocB tests: not compiled with -DSQLITE_MEMDEBUG...
Skipping mallocC tests: not compiled with -DSQLITE_MEMDEBUG...
Skipping mallocD tests: not compiled with -DSQLITE_MEMDEBUG...
Skipping mallocE tests: not compiled with -DSQLITE_MEMDEBUG...
Skipping mallocF tests: not compiled with -DSQLITE_MEMDEBUG...
Skipping mallocG tests: not compiled with -DSQLITE_MEMDEBUG...
printf-1.7.6...
Expected: [Three integers: (100) ( f4240) (3641100)]
 Got: [Three integers: ( 100) ( f4240) (3641100)]
printf-1.8.6...
Expected: [Three integers: (9) (3b9ac9ff) (7346544777)]
 Got: [Three integers: ( 9) (3b9ac9ff) (7346544777)]
printf-1.9.7...
Expected: [Three integers: ( 0) (   0x0) ( 0)]
 Got: [Three integers: ( 0) ( 0) ( 0)]
Skipping safety tests since SQLITE_DEBUG is off
time with cache: 38383126 microseconds per iteration
time without cache: 145730273 microseconds per iteration
result=one 10 three 5 two 14
speed3-1.incrvacuum..  4612032 uS216.82417 row/s
speed3-1.normal... 9631095 uS103.83035 row/s
Incrvacuum: Read 1098, wrote 94
Normal: Read 10479, wrote 43
speed3-2.incrvacuum..  1803619 uS554.44082 row/s
speed3-2.normal... 3585545 uS278.89763 row/s
Incrvacuum: Read 2843, wrote 1
Normal: Read 10011, wrote 0
tcl-1.6...
Expected: [1 {syntax error in expression "x*"}]
 Got: [1 {invalid bareword "x"
in expression "x*";
should be "$x" or "{x}" or "x(...)" or ...}]
vtab6-2.2...
Expected: [1 2 3 {} 2 3 4 1 3 4 5 2]
 Got: [1 2 3 {} 2 3 4 {} 3 4 5 {}]
vtab6-2.4...
Expected: [1 2 3 {} {} {} 2 3 4 {} {} {} 3 4 5 1 2 3]
 Got: [1 2 3 {} {} {} 2 3 4 {} {} {} 3 4 5 {} {} {}]
vtab6-2.5...
Expected: [2 3 4 {} {} {} 3 4 5 1 2 3]
 Got: [2 3 4 {} {} {} 3 4 5 {} {} {}]
vtab6-2.6...
Expected: [1 2 3 {} {} {} 2 3 4 {} {} {}]
 Got: [1 2 3 {} {} {} 2 3 4 {} {} {} 3 4 5 {} {} {}]
vtab6-7.1...
Expected: [1 999 999 2 131 130 999]
 Got: [1 999 999 999 999 999 999]
vtab6-9.1.1...
Expected: []
 Got: [2 22 {}]
vtab6-9.2...
Expected: []
 Got: [2 22 {}]
22 errors out of 38217 tests
Failures on these tests: async3-1.0 async3-1.1 async3-1.2.2 async3-1.3.2 
async3-1.4.2 async3-1.5.2 async3-1.6.2 async3-1.7.2 bind-4.4 bind-4.5 lock4-1.3 
printf-1.7.6 printf-1.8.6 printf-1.9.7 tcl-1.6 vtab6-2.2 vtab6-2.4 vtab6-2.5 
vtab6-2.6 vtab6-7.1 vtab6-9.1.1 vtab6-9.2
All memory allocations freed - no leaks
Maximum memory usage: 14162418 bytes
Current memory usage: 0 bytes
*** Error code 1

Stop in /u0/sw/SQLite/sqlite-3.5.5/OpenBSD (line 568 of Makefile).

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

[sqlite] Make test on Ubuntu 7.10 SQLite 3.5.5

[Rob Sciuk]

$ uname -a
Linux linux 2.6.22-14-generic #1 SMP Sun Oct 14 23:05:12 GMT 2007 i686 GNU/Linux

$ make test | grep -v Ok | tee test.output.ubuntu

./testfixture ../test/quick.test
bind-4.4...
Error: floating point value is Not a Number
bind-4.5...
Expected: [null real real]
 Got: []
btree-2.1.1...CURSOR 8119900 rooted at1(rw) currently at 1.0 eof
PAGE 1:  flags=0x09  frag=0   parent=0
cell  0: i=913..1023  chld=0nk=2020 nd=1792 payload=*** This is a very 
PAGE 2:  flags=0x08  frag=0   parent=0

PAGE   1 addr=81A2C3C nRef=2
Skipping crash5 tests: not compiled with -DSQLITE_MEMDEBUG...
Skipping malloc5 tests: not compiled with -DSQLITE_MEMDEBUG...
Skipping malloc6 tests: not compiled with -DSQLITE_MEMDEBUG...
Skipping malloc7 tests: not compiled with -DSQLITE_MEMDEBUG...
Skipping malloc8 tests: not compiled with -DSQLITE_MEMDEBUG...
Skipping malloc9 tests: not compiled with -DSQLITE_MEMDEBUG...
Skipping mallocA tests: not compiled with -DSQLITE_MEMDEBUG...
Skipping mallocB tests: not compiled with -DSQLITE_MEMDEBUG...
Skipping mallocC tests: not compiled with -DSQLITE_MEMDEBUG...
Skipping mallocD tests: not compiled with -DSQLITE_MEMDEBUG...
Skipping mallocE tests: not compiled with -DSQLITE_MEMDEBUG...
Skipping mallocF tests: not compiled with -DSQLITE_MEMDEBUG...
Skipping mallocG tests: not compiled with -DSQLITE_MEMDEBUG...
printf-1.7.6...
Expected: [Three integers: (100) ( f4240) (3641100)]
 Got: [Three integers: ( 100) ( f4240) (3641100)]
printf-1.8.6...
Expected: [Three integers: (9) (3b9ac9ff) (7346544777)]
 Got: [Three integers: ( 9) (3b9ac9ff) (7346544777)]
printf-1.9.7...
Expected: [Three integers: ( 0) (   0x0) ( 0)]
 Got: [Three integers: ( 0) ( 0) ( 0)]
Skipping safety tests since SQLITE_DEBUG is off
time with cache: 3305471 microseconds per iteration
time without cache: 14317410 microseconds per iteration
result=one 10 three 5 two 14
speed3-1.incrvacuum..   241673 uS   4137.82259 row/s
speed3-1.normal...  229172 uS   4363.53481 row/s
Incrvacuum: Read 1098, wrote 94
Normal: Read 10479, wrote 43
speed3-2.incrvacuum..68500 uS  14598.54015 row/s
speed3-2.normal...  115952 uS   8624.25831 row/s
Incrvacuum: Read 2843, wrote 1
Normal: Read 10011, wrote 0
tcl-1.6...
Expected: [1 {syntax error in expression "x*"}]
 Got: [1 {invalid bareword "x"
in expression "x*";
should be "$x" or "{x}" or "x(...)" or ...}]
vtab6-2.2...
Expected: [1 2 3 {} 2 3 4 1 3 4 5 2]
 Got: [1 2 3 {} 2 3 4 {} 3 4 5 {}]
vtab6-2.4...
Expected: [1 2 3 {} {} {} 2 3 4 {} {} {} 3 4 5 1 2 3]
 Got: [1 2 3 {} {} {} 2 3 4 {} {} {} 3 4 5 {} {} {}]
vtab6-2.5...
Expected: [2 3 4 {} {} {} 3 4 5 1 2 3]
 Got: [2 3 4 {} {} {} 3 4 5 {} {} {}]
vtab6-2.6...
Expected: [1 2 3 {} {} {} 2 3 4 {} {} {}]
 Got: [1 2 3 {} {} {} 2 3 4 {} {} {} 3 4 5 {} {} {}]
vtab6-7.1...
Expected: [1 999 999 2 131 130 999]
 Got: [1 999 999 999 999 999 999]
vtab6-9.1.1...
Expected: []
 Got: [2 22 {}]
vtab6-9.2...
Expected: []
 Got: [2 22 {}]
13 errors out of 38961 tests
Failures on these tests: bind-4.4 bind-4.5 printf-1.7.6 printf-1.8.6 
printf-1.9.7 tcl-1.6 vtab6-2.2 vtab6-2.4 vtab6-2.5 vtab6-2.6 vtab6-7.1 
vtab6-9.1.1 vtab6-9.2
All memory allocations freed - no leaks
Maximum memory usage: 14162050 bytes
Current memory usage: 0 bytes

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

Re: [sqlite] make test on FreeBSD 6.2-R, SQLite 3.5.5

[Rob Sciuk]

On Thu, 31 Jan 2008, [EMAIL PROTECTED] wrote:


Date: Thu, 31 Jan 2008 18:27:35 +
From: [EMAIL PROTECTED]
Reply-To: sqlite-users@sqlite.org
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] make test on FreeBSD 6.2-R, SQLite 3.5.5

"Alexander Batyrshin" <[EMAIL PROTECTED]> wrote:

How to get error messages from test? Scroll up and copy-paste? Or
there is some other more handy method?


make test | tee testout.txt


aha!  Take the simple approach, in keeping I re-ran the 3.5.5 test with:

make test | grep -v Ok | tee test.output

-=-=-=-=-=-=-=-=-= test.output -=-=-=-=-=-=-=-=-=-=
./testfixture ../test/quick.test
bind-4.4...
Error: floating point value is Not a Number
bind-4.5...
Expected: [null real real]
 Got: []
btree-2.1.1...CURSOR 8121088 rooted at1(rw) currently at 1.0 eof
PAGE 1:  flags=0x09  frag=0   parent=0
cell  0: i=913..1023  chld=0nk=2020 nd=1792 payload=*** This is a very 
PAGE 2:  flags=0x08  frag=0   parent=0

PAGE   1 addr=80F7894 nRef=2
cast-3.14...
Expected: [9223372036854774784]
 Got: [9223372036854773760]
cast-3.18...
Expected: [-9223372036854774784]
 Got: [-9223372036854773760]
cast-3.24...
Expected: [9223372036854774784]
 Got: [9223372036854773760]
Skipping crash5 tests: not compiled with -DSQLITE_MEMDEBUG...
Skipping malloc5 tests: not compiled with -DSQLITE_MEMDEBUG...
Skipping malloc6 tests: not compiled with -DSQLITE_MEMDEBUG...
Skipping malloc7 tests: not compiled with -DSQLITE_MEMDEBUG...
Skipping malloc8 tests: not compiled with -DSQLITE_MEMDEBUG...
Skipping malloc9 tests: not compiled with -DSQLITE_MEMDEBUG...
Skipping mallocA tests: not compiled with -DSQLITE_MEMDEBUG...
Skipping mallocB tests: not compiled with -DSQLITE_MEMDEBUG...
Skipping mallocC tests: not compiled with -DSQLITE_MEMDEBUG...
Skipping mallocD tests: not compiled with -DSQLITE_MEMDEBUG...
Skipping mallocE tests: not compiled with -DSQLITE_MEMDEBUG...
Skipping mallocF tests: not compiled with -DSQLITE_MEMDEBUG...
Skipping mallocG tests: not compiled with -DSQLITE_MEMDEBUG...
printf-1.7.6...
Expected: [Three integers: (100) ( f4240) (3641100)]
 Got: [Three integers: ( 100) ( f4240) (3641100)]
printf-1.8.6...
Expected: [Three integers: (9) (3b9ac9ff) (7346544777)]
 Got: [Three integers: ( 9) (3b9ac9ff) (7346544777)]
printf-1.9.7...
Expected: [Three integers: ( 0) (   0x0) ( 0)]
 Got: [Three integers: ( 0) ( 0) ( 0)]
Skipping safety tests since SQLITE_DEBUG is off
time with cache: 2292055 microseconds per iteration
time without cache: 7910271 microseconds per iteration
result=one 10 three 5 two 14
speed3-1.incrvacuum..   201285 uS   4968.08009 row/s
speed3-1.normal...  336842 uS   2968.75093 row/s
Incrvacuum: Read 1098, wrote 94
Normal: Read 10479, wrote 43
speed3-2.incrvacuum..   100244 uS   9975.65939 row/s
speed3-2.normal...  233531 uS   4282.08675 row/s
Incrvacuum: Read 2843, wrote 1
Normal: Read 10011, wrote 0
tcl-1.6...
Expected: [1 {syntax error in expression "x*"}]
 Got: [1 {invalid bareword "x"
in expression "x*";
should be "$x" or "{x}" or "x(...)" or ...}]
vtab6-2.2...
Expected: [1 2 3 {} 2 3 4 1 3 4 5 2]
 Got: [1 2 3 {} 2 3 4 {} 3 4 5 {}]
vtab6-2.4...
Expected: [1 2 3 {} {} {} 2 3 4 {} {} {} 3 4 5 1 2 3]
 Got: [1 2 3 {} {} {} 2 3 4 {} {} {} 3 4 5 {} {} {}]
vtab6-2.5...
Expected: [2 3 4 {} {} {} 3 4 5 1 2 3]
 Got: [2 3 4 {} {} {} 3 4 5 {} {} {}]
vtab6-2.6...
Expected: [1 2 3 {} {} {} 2 3 4 {} {} {}]
 Got: [1 2 3 {} {} {} 2 3 4 {} {} {} 3 4 5 {} {} {}]
vtab6-7.1...
Expected: [1 999 999 2 131 130 999]
 Got: [1 999 999 999 999 999 999]
vtab6-9.1.1...
Expected: []
 Got: [2 22 {}]
vtab6-9.2...
Expected: []
 Got: [2 22 {}]
16 errors out of 38961 tests
Failures on these tests: bind-4.4 bind-4.5 cast-3.14 cast-3.18 cast-3.24 
printf-1.7.6 printf-1.8.6 printf-1.9.7 tcl-1.6 vtab6-2.2 vtab6-2.4 vtab6-2.5 
vtab6-2.6 vtab6-7.1 vtab6-9.1.1 vtab6-9.2
All memory allocations freed - no leaks
Maximum memory usage: 14161974 bytes
Current memory usage: 0 bytes
*** Error code 1

Stop in /u0/sw/SQLite/sqlite-3.5.5/FreeBSD.

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

Re: [sqlite] make test on FreeBSD 6.2-R, SQLite 3.5.5

[Rob Sciuk]

On Thu, 31 Jan 2008, [EMAIL PROTECTED] wrote:

Rob Sciuk <[EMAIL PROTECTED]> wrote:

Ran the tests on Freebsd, the make test summary follows:
[delenda => ... Ok]

16 errors out of 38961 tests
Failures on these tests: bind-4.4 bind-4.5 cast-3.14 cast-3.18 cast-3.24
printf-1.7.6 printf-1.8.6 printf-1.9.7 tcl-1.6 vtab6-2.2 vtab6-2.4
vtab6-2.5 vtab6-2.6 vtab6-7.1 vtab6-9.1.1 vtab6-9.2
All memory allocations freed - no leaks
Maximum memory usage: 14161974 bytes
Current memory usage: 0 bytes
*** Error code 1



We get zero test failures on Linux.  I don't know if these are
significant or not without seeing the test error messages that
accompany each failure.

Probably this is things where your system is printing 3.4e+05
whereas SQLite is looking for 3.4e+005, in which case the errors
are benign.  But without seeing the actual errors, I cannot say
for sure.
--
D. Richard Hipp <[EMAIL PROTECTED]>



Hmmm ... by my reckoning, you have added 261 tests *JUST* for version 
3.5.5.  Pretty big effort I'd say, and much appreciated I'd like to add.


I re-ran the make test on 3.5.4, and there seems to be a significant 
overlap in those reporting failure (see below).  As I have not yet figured 
out how to run them individually (eg:  make test bind-4.4), nor did I find 
an output error file, I'll make the assumption that you are correct, and 
the diffs are indeed benign (since everything seems to work really well), 
at least until I have some time to re-visit.


Have you done any performance regression tests on the 3.5.5 VM yet??  I 
suspect that there will be an improvement ...


-=-=-=-=-=-=-=-=-=
FreeBSD 6.2-R (same hw), SQLite 3.5.4 reported:
[delendum ... Ok]

9 errors out of 38700 tests
Failures on these tests: bind-4.4 bind-4.5 cast-3.14 cast-3.18 cast-3.24 
printf-1.7.6 printf-1.8.6 printf-1.9.7 tcl-1.6

All memory allocations freed - no leaks
Maximum memory usage: 14165134 bytes
*** Error code 1

Stop in /u0/sw/SQLite/sqlite-3.5.4/FreeBSD.



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

[sqlite] make test on FreeBSD 6.2-R, SQLite 3.5.5

[Rob Sciuk]

Ran the tests on Freebsd, the make test summary follows:
[delenda => ... Ok]

16 errors out of 38961 tests
Failures on these tests: bind-4.4 bind-4.5 cast-3.14 cast-3.18 cast-3.24 
printf-1.7.6 printf-1.8.6 printf-1.9.7 tcl-1.6 vtab6-2.2 vtab6-2.4 
vtab6-2.5 vtab6-2.6 vtab6-7.1 vtab6-9.1.1 vtab6-9.2

All memory allocations freed - no leaks
Maximum memory usage: 14161974 bytes
Current memory usage: 0 bytes
*** Error code 1

Stop in /u0/sw/SQLite/sqlite-3.5.5/FreeBSD.


Any comments on the Failures??
Are these normal, or should I be worried??

Cheers,
Rob.

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

Re: [sqlite] SQLite Compilation Problems on FreeBSD

[Rob Sciuk]

On Sun, 27 Jan 2008, L. S. wrote:


Why doesn't SQLite 3.5.4 compile under FreeBSD 5.3? Can it be done in a sane 
manner?


AFAIK, it just works, but you may require the -lc_r  option to include the 
re-entrant (threadsafe) C library with your linked executeable ...




How can I link against SQLite under FreeBSD 5.3, using gcc?
Is there any way that I could just link the source files into my code?

* Using "./configure" and "make" with "sqlite-3.5.4.tar.gz", I got tcl and 
tcsh errors.
* Using "sqlite-source-3_5_4.zip", with "tclsqlite.c" removed, I got pthread 
errors. They went away with "gcc ... -lpthread", but compilation still 
failed.
* I think "sqlite-amalgamation-3_5_4.zip" produced the same errors as 
sqlite-source-.


I have compiled, installed, linked, and used it on other systems, but cannot 
do the same on my FreeBSD system. (I don't use the FreeBSD ports; I like to 
just download and build.)




Moi aussi, but I have *NEVER* had a problem with SQLite on FreeBSD, 
including 3.5.4 ... Of course I *ALWAYS* install Tcl/Tk first, and am 
happy to use the tclsqlite bindings which result ...


Again, when you link your C code to the libSqlite.so, just add the -lc_r
option, and you can delete the -lpthreads unless you are actually using 
pthreads, in which case you will likely need both -lpthreads *AND* -lc_r

or surprises may result.

Apparently, the -lc_r requirement is new with 3.5.4, and my query on this 
list has been met with silence thus far, so I cannot advise you further. 
Apparently the db engine has been re-worked somewhat (extensively) with 
this release, and I suspect that changes eminate from that code, but I 
haven't had time to investigate ...


HTH,

Rob Sciuk

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

Re: [sqlite] Date arithmetic question

[Rob Sciuk]

On Thu, 17 Jan 2008, [EMAIL PROTECTED] wrote:


"Virgilio Fornazin" <[EMAIL PROTECTED]> wrote:

DATEDIFF should compute the difference by arithmetic subtracting M/Y in
month case, if I'm not wrong

ex:

DateDiff (month, 1-1-2007, 3-30-2007) will return 2

Its that right ?


So datediff('month', '2008-02-01 23:59:59','2008-01-31 00:00:00') should
return 1 even though the difference is really only 1 second?  Seems
goofy to me

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


The timestamp is a very useful thing, but IMHO, dates are dates, and times 
are times.  Date arithmentic is very easy given a true Julian date, 
calculated as the number of *DAYS* since an "epoch" (Oct 15, 1583?), and 
times can be the number of seconds, tenths of seconds, milliseconds or 
whatever in a day, but stored as separate variables.


There is much subjective processing in time differences when you start 
adding +- n months, and much of it can be avoided by not using the system
type timestamps to store dates, and keeping a date variable and time 
variable (both integers).


I wrote a Tcl binding for a Julian date and time library I've used for 
years, and made it available, both as C library (BSD/Linux/Windows), and
Tcl Extension (BSD/Windows).  There is a unix style man page for 
documentation.


http://www.controlq.com/OpenSource/Tcl_Julian.tgz

HTH,
Rob Sciuk



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

Re: [sqlite] How do I get back command history in v3.5.4?

[Rob Sciuk]

On Wed, 16 Jan 2008, John Stanton wrote:


Date: Wed, 16 Jan 2008 16:32:53 -0600
From: John Stanton <[EMAIL PROTECTED]>
Reply-To: sqlite-users@sqlite.org
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] How do I get back command history in v3.5.4?

You might be having a library location problem between /usr/local/lib and 
where Ubuntu puts libraries, usr/lib.




Ubuntu also seems to differentiate between a .binary package and a 
developer package.  It may be that the INCLUDE files are not installed as 
the developer (headers+libraries) are installed, either way, it is 
possible that the configure failed as a result


You need both the libreadline.so.x and the include files (man readline)

Cheers,
Rob

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

[sqlite] Compiling 3.5.4 on FreeBSD ...

[Rob Sciuk]

Dear Digest,

I just compiled 3.5.4 on FreeBSD 6.2-RELEASE, and noticed that while
I'm *NOT* using threads, I still require linking with the re-entrant C 
library -lc_r option, else I get a runtime error ... I don't require
the -lpthread option, but I *DO* require -lc_r.  Is this a new 
requirement?


Cheers,
Rob Sciuk

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

Re: [sqlite] Dump w/o Schema

[Rob Sciuk]

On Tue, 15 Jan 2008, Mark Riehl wrote:


Is there a way to dump out the contents of the database w/o having the
CREATE TABLE statements?  For example, I can do the following:

sqlite3 foo.db .dump > foo.dmp

However, foo.dmp contains all of the CREATE TABLE statements.  I just
want all of the INSERT INTO statements associated with this database.

I looked through the documentation but didn't find the answer to this
one, I apologize if it's already in the docs.

Thanks,
Mark


Actually, this looks like a reasonable enhancement request.  As it does 
*NOT* affect the API in any way, and PostGres allows separate schema and 
data dumps (the default being both) in psql, there should be no reason not 
to put it into the sqlite3 tool, unless I'm missing something??  Am I?


Cheers,
Rob Sciuk

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

Re: [sqlite] "always-trim" - feature suggestion

[Rob Sciuk]

On Wed, 9 Jan 2008, Zbigniew Baniewski wrote:


On Wed, Jan 09, 2008 at 12:51:16PM +, [EMAIL PROTECTED] wrote:


Why not have a possibility to make it default
behaviour of the SQL-engine itself, just by
using one "pragma"?

1. It'll make my code shorter.


But it makes the SQLite core code larger.  Why should the the
SQLite core be enlarged for the convenience of a single user.


It's rather hard to say, if really just "single". SQLite, as I understood,
has many users; just three of them were "against", until this time.

Who knows, which of the existing features are used by how many users?
Yesterday one of them wrote, that (if I properly understood) he appreciates
nothing more, than "job of storage".


2. It'll make my life easier. ;)


But it makes my life harder. [..]


Probably a bit - but it was your own choice of such way of life, anyway. ;)


You know, I believe that an "embedded" SQL has a philosophy which is 
inherently minimalist.  Your request specifically goes against the 
philosophy of what SQLite was designed to be. DRH is working hard to 
protect an ideal which has appealed to millions, and continues to do so, 
and adding bloat will not contribute to its future success.


There is nothing to top you from *FORKING* the SQLite project into a 
kitchen sink version of MySQL, PostGresql, Firebird or any of a number of 
existing full featured databases, and then *YOU* can be the one to 
determine what features belong in *YOUR* database managment system, and 
take on the work of implementing every idea which comes along ...


Perhaps SQL_Obese?


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

Re: [sqlite] no mention of SQLite :(

[Rob Sciuk]

On Sat, 5 Jan 2008, [EMAIL PROTECTED] wrote:


"Shane Harrelson" <[EMAIL PROTECTED]> wrote:

Embedded Database Vendors Face Challenges:
http://www.eweek.com/article2/0,1895,2243406,00.asp

How could an article like this not mention SQLite?


I don't know, but my guess is that the article was
ghost-written by Steve Graves and he doesn't want to
draw unnecessary attention to the free database engine
that is eating his lunch.

There is a comment section.  Why don't you post a comment
asking why there is no mention of the embedded SQL
database that powers Mozilla Firefox, Symbian Smartphones,
Google Android and Gears, Adobe AIR and Lightroom,
Apple Mail and Safari, and Skype, to name but a few.


Wow, given the "license" of SQLite, it occurs that DRH is still pretty 
protective of the "brand".  Going for an altruistic form of world 
domination?  Sort of like Bill Gates -- but without the billion$

of course (just kidding ;-)


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

Re: [sqlite] Trying to use SQLite3 with PHP5....

[Rob Sciuk]

On Fri, 4 Jan 2008, [EMAIL PROTECTED] wrote:


John Stanton <[EMAIL PROTECTED]> wrote:

You need to make a small change in the make file to get rid of the
wretched tclsh and link sqlite3 from the library.  Just use an earlier
makefile as a template.



The problem with the TCL dependency was reported by
tickets #2845 and #2846 and has been fixed in check-in [4636].

John, I will thank you to not refer to tclsh as "wretched".  The
Tool Control Language (TCL) is one of the most elegant and
power programming languages ever devised.  TCL is not part
of the Algol family of languages (it is more closely related
to Lisp) which makes it difficult to grok for people who have
only been exposed to Algol-like langauges.  But this does not
detract from the extreme elegance of the language.


I would agree with D.R.H. on the innate beauty of Tcl, and coupled with 
the very nice (and getting better) GUI development tool Tk (specifically 
the new Ttk (Tile) themed widget set).  Tcl/Tk may not have the momentum
that Ruby/Python/Perl have, but it certainly does *NOT* lack form or 
function, and given the ease with which it can be embedded, seems a 
particularly good match for the SQLite project.  Frankly, using tclsh is 
IMHO an inspired choice.



-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=--=-=-=-=
Robert S. Sciuk http://www.controlq.com 259 Simcoe St. S.
Control-Q Research  tel: 905.576.8028   Oshawa, Ont.
[EMAIL PROTECTED]   fax: 905.576.8386   Canada, L1H 4H3

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

[sqlite] returning Blobs in an oldstyle query ...

[Rob Sciuk]

TWIMC,

When using the sqlite3_query command, and a callback function, is there a 
way of getting the _*STATEMENT*_ for the query string which is currently 
executing, reliably from the sqlite3 *opaque type??


The reason I need this, I'm using SQLITE 3.5.x to manage blobjects of 
various kinds, and while I have control when using the prep/step/finalize 
routines, and I can determine sqlite3_column_type of each variable 
returned, and indeed, if a blob, then I can determine the length 
sqlite3_column_bytes, and obtain the data sqlite3_column_blob.  These 
funcions require the use of the statment pointer.


As the query does not pass a link to the prepared *STATEMENT* in the exec 
form of the callback, I was wondering if there is a way to reliably obtain 
it from the sqlite3 *object (in this case: this ->Cx ) ;


this ->dbError = sqlite3_exec(
this ->Cx,
query,
lw_sqlite_callback,
this,
(char **) NULL
) ;
return( db_Error( this ) ) ;

where the callback is defined:

int  lw_sqlite_callback(void* this, int n_Cols, char**vals, char**vars){

Db_t*p ;
int  i = 0 ;

p = (Db_t *) this ;
if( !p ->Result ){
p ->Result = New( TList, n_Cols ) ;
}

for( i = 0 ; i < n_Cols ; i++ ){
at_Addstr( p ->Result, vars[i], vals[i] ) ;
}
return( SQLITE_OK ) ;
}

note that in the callback, this ->Cx constitutes the sqlite3 database 
pointer.


--
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=--=-=-=-=
Robert S. Sciuk http://www.controlq.com 259 Simcoe St. S.
Control-Q Research  tel: 905.576.8028   Oshawa, Ont.
[EMAIL PROTECTED]   fax: 905.576.8386   Canada, L1H 4H3

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

Re: [sqlite] Preallocating fixed disk space for database ...

[Rob Sciuk]

On Fri, 1 Dec 2006, John Stanton wrote:


I cannot see a reason for what you propose, but you could do it by brute 
force and ignorance - populate the DB with 1 million rows then delete them 
all to add all the space to the free pages list.  Then your insertions will 
use the freed pages, not fresh ones.




Actually, this harkens back to the dedicated transaction oriented file 
systems (HP 3000/MPE) which were extent based.  File "extents" could be 
pre-allocated to ensure a minimum number of transactions were captured 
before the disk ran out of space -- this also had the advantage of 
contiguous allocation which ensured data proximity.  The administrator 
could control the size and number of extents as well as the number 
pre-allocated (if any), and indeed, the maximum file size.


I believe that some modern file system development work is looking back 
towards the 60's and 70's and modern high performance extent based file 
system are in development -- at least in the open systems (Linux?) area, 
but I have no data to back this up, and no clue as to whether they are 
ready for prime time (other than a fuzzily remembered magazine article).


As for the OP, this type of operation is OS dependant, and should not be 
relegated to SQLite IMHO.


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

Re: [sqlite] Date data type

[Rob Sciuk]

On Tue, 7 Nov 2006, Me wrote:

> -MM-DD HH:MM:SS
> behaves as expected on queries
> no manipulation necessary
>
> you can set a validation mask
> CREATE TABLE [tEnforceTimestamp2] (
> [cTimestamp] TEXT CHECK (cTimestamp LIKE'-__-__ __:__:__')
> )
>  or
> CREATE TABLE [tEnforceTimestamp2] (
> [cTimestamp] TEXT CHECK(cTimestamp > '1900-01-01' AND cTimestamp <
> '2099-01-01' AND cTimestamp LIKE'-__-__ __:__:__')
> )
>

In keeping with the spirit of D.R. Hipp's "blessing", and specifically for
those who use Tcl/SQLite, but hopefully not limited to those, I've
released an old (ie: well used) relatively portable C library of Julian
date routines, and a Tcl binding for it under an open source license. Man
pages are provided for the Tcl bindings, and but the C source will have to
suffice for the C library documentation.

The makefile cross builds the .dll using MingW32 (though I have not tested
that .dll in quite some time), and builds both the Julian.DLL and
Julian.so files for both Windows and most Unix like variants.  It will
require a reasonably recent version of gnu make, and some twiddling to get
working right on your platform, unless you use FreeBSD, and then it
should just work.

While admittedly, this is not a panacea for the problems expressed in this
thread, and is not a SQLite datatype extension, it may prove to be of some
small utility to some subset of users.

http://www.controlq.com/OpenSource/Tcl_Julian.tgz

Enjoy.
Rob Sciuk

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

Re: [sqlite] SQLite and McAfee Anti-Virus

[Rob Sciuk]

On Tue, 31 Oct 2006 [EMAIL PROTECTED] wrote:

> No need to call in a preemtive Slashdot bombardment just yet.
> Let's give diplomacy a chance...
>
> --
> D. Richard Hipp  <[EMAIL PROTECTED]>

<$.02> As was mentioned previously, why *not* add a prominent section
under the sqlite.org web site, which describes the problem, and turns a
negative into a positive.  I mean, SQLite is embedded in McAfee's products
... why not yours (but put your toys away when you're done playing like
good little boys and girls)?

In addition, it occurs that if a residual tempfile has outlived its
usefulness, a prefix of DeleteMe_ has more meaning to the great unwashed
than SQLite_ spelled in either direction.


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

Re: [sqlite] Re: efficient way to figure out if a table is empty

[Rob Sciuk]

On Mon, 30 Oct 2006, Igor Tandetnik wrote:

> Date: Mon, 30 Oct 2006 11:21:15 -0500
> From: Igor Tandetnik <[EMAIL PROTECTED]>
> Reply-To: sqlite-users@sqlite.org
> To: SQLite 
> Subject: [sqlite] Re: efficient way to figure out if a table is empty
>
> Xavier Noria <[EMAIL PROTECTED]> wrote:
> > Not that is critical for my application, but just for curiosity which
> > is the recommended idiom to figure out whether a table has any
> > register?
>
> select exists (select * from tablename);
>
> Igor Tandetnik

Would it not make sense to use the limit on the subquery to reduce
overhead in the case where the table is populated?

select exists( select * from tablename limit 1 );



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

[sqlite] test message ...

[Rob Sciuk]

Please ignore this test -- sorry.

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

Re: [sqlite] SQLite GUI app that offers layouts

[Rob Sciuk]

On Wed, 18 Oct 2006, Rich Shepard wrote:
> Rob,
>
>And, ... there's pysqlite2 which is a teriffic implementation of the
> python database API.
>
>I'm using python, wxPython, and pysqlite2 in our approximate reasoning
> models. It's a nice system.
>
> Rich

So many scripting languages, and so little time.  Noel is making a valiant
case for Lua/IUP widgets, though I'm only vaguely aware of that language.
What was that link again?

Commercial success for a scripting language seems to be tied to its
critical mass and *COOL* factor, and how much is being contributed, which
has a tendancy to fade with the NEXT BIG THING (eg: Java ->Ruby/Rails??).

It seems that scripting languages are the new religion (was vi/emacs), and
each seems to have a killer appeal to some.  I was big on Forth many years
ago, and went so far as to write my own portable interpreter for mini's,
but alas, Forth has been relegated to Forth for its own sake amoung some
die-hards rather than for its interesting applications it seems (no flames
please).

I'm surprised that no Ruby/Perl/Rexx/... fanatics have waded in just yet.
Testimony again to the design of SQLite, and its ease of integration is
the fact that it supports just about every language out there.

But, in order to keep this thread on topic, once again, a slavish suckup
to DRH for his remarkable contribution to Open Source -- even to the point
of creating a new class of license.

Thanks, Richard Hipp -- SQLite is a remarkable and useful achievement!!

Rob Sciuk.

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

Re: [sqlite] SQLite GUI app that offers layouts

[Rob Sciuk]

On Wed, 18 Oct 2006, Rich Shepard wrote:
> On Wed, 18 Oct 2006, Rob Sciuk wrote:
>
> > Interesting, but I'm wondering why not use the tcl/tk binding to simply
> > generate the forms using the well crafted tcl binding which comes with the
> > SQLite language?  Tcl/TK is a very simple scripting language which is
> > portable to all major platforms, has a very useful GUI widget set, and
> > while it might not be the prettiest interface in the world, getting very
> > useable forms up and running using sqlite/tk is trivial. 
>
>Alternative #3: write your application in python and use the wxPython
> widget set. This combination is portable across OSes and uses the native
> widgets so the "look and feel" is consistent.
>
> Rich

Or that ... I imagine that the wxPython combination is more modern
looking, given the native look and feel of the wxWidget set project,
though I've not tried it yet.  Hmmm ... note to self ...

Rob.

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

Re: [sqlite] SQLite GUI app that offers layouts

[Rob Sciuk]

On Wed, 18 Oct 2006, Noel Frankinet wrote:

> Date: Wed, 18 Oct 2006 17:23:43 +0200
> From: Noel Frankinet <[EMAIL PROTECTED]>
> Reply-To: sqlite-users@sqlite.org
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] SQLite GUI app that offers layouts
>
> Sorry, the correct url is :
>
> http://www.gistek.net/gui.html

<.02$>
Interesting, but I'm wondering why not use the tcl/tk binding to simply
generate the forms using the well crafted tcl binding which comes with the
SQLite language?  Tcl/TK is a very simple scripting language which is
portable to all major platforms, has a very useful GUI widget set, and
while it might not be the prettiest interface in the world, getting very
useable forms up and running using sqlite/tk is trivial.


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

Re: [sqlite] Performance Question

[Rob Sciuk]

On Wed, 30 Aug 2006 [EMAIL PROTECTED] wrote:

> > I have to go along with Mario, here.  This is a potential show stopper,
>
> Show stopper?  Really?  The bug has been there for years, literally,
> and nobody has even noticed it until now - despite thousands of users
> and millions and millions of deployments.
>
> There is a really simple work-around: Just add NOT NULL to your
> PRIMARY KEY column declaration...

Point taken.


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

Re: [sqlite] Performance Question

[Rob Sciuk]

On Wed, 30 Aug 2006, Mario Frasca wrote:
> On 2006-0829 13:15:02, [EMAIL PROTECTED] wrote:
>
> >> To my surprise (perhaps "horror") I find that SQLite has
> >> for a very long time allowed NULL values in PRIMARY KEY
> >> columns.  [...]
>
> I understand your concern about legacy programs, but most of us expect
> PRIMARY KEY to imply NOT NULL...  don't we?  what about looking for
> alternative good solutions?  we could put the correction code in the
> source, conditionally compiled (not the default) and with the next major
> release reverse the condition (new 'corrected' source becomes default
> and old 'legacy' behaviour still available if desired) ... ?
>
> maybe 'the best of both worlds', hope you agree.
>
> regards,
> Mario


I have to go along with Mario, here.  This is a potential show stopper,
and I would grab a "fixed" version of SQLite ASAP were it made available.
Alternatively, one might simply use an ifdef to restore the old (legacy)
behaviour, something like:

#ifdef WEIRD_AND_UNEXPECTED_BEHAVIOURS_DESIRED
    ...
    #endif

I'd suggest that this is one for the regression suite as well.


Rob Sciuk

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

Re: [sqlite] Re: database locked

[Rob Sciuk]

On Tue, 22 Aug 2006, Laura Longo wrote:

> Date: Tue, 22 Aug 2006 08:51:34 +0200
> From: Laura Longo <[EMAIL PROTECTED]>
> Reply-To: sqlite-users@sqlite.org
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] Re: database locked
>
> > Laura,
> >
> > 'df -h' should give you some hints, or 'showmount'.  I run on BSD not
> > Linux,
> > but most unix like OS's will tell you if the mount point is a hard drive
> > or a remote mount, in which case, it will have a hostname:/path instead of
> > a /dev/dsk device file as the device/partition information.
> >
> > HTH.
> > Rob.
> >
>
> Hi Rob,
> sorry for the delay, the result of df -h is this:
>
> FilesystemSize  Used Avail Use% Mounted on
> /dev/sda1 3.9G  1.6G  2.2G  42% /
> none  504M 0  504M   0% /dev/shm
> /dev/sda4 9.7G  2.5G  6.7G  27% /home
> /dev/sda3 136G   32G   97G  25% /var
>
> Then, if I've understood well, my filesystem is not residing on an NFS
> mounted disk...

That looks to be true.  The NFS mount would look different than the
/dev/sda? mounts.  Sorry, your problem is something else.

Cheers,
Rob.

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

Re: [sqlite] Re: database locked

[Rob Sciuk]

On Mon, 21 Aug 2006, Laura Longo wrote:

> Date: Mon, 21 Aug 2006 20:01:24 +0200
> From: Laura Longo <[EMAIL PROTECTED]>
> Reply-To: sqlite-users@sqlite.org
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] Re: database locked
>
>
>
> > On 8/21/06, Laura Longo <[EMAIL PROTECTED]> wrote:
> >> I've tried also executing the query "begine exclusive" before the
> >> "update",
> >> and "commit" to end the entire routine, and the query that now returns
> >> the
> >> exit code 5 (database locked) is "begin exclusive", I don't know if this
> >> can be meaningful...
> >
> > It sounds like some other application has locked the database file.
> > Do you have a virus scanner? This can sometimes cause problems.
> > It will lock the file you use for the database.
>
> My applications are running under Linux (CentOS, kernel 2.6.16) and I have
> no
> virus scanner.

The database is not residing on an NFS mounted disk by any chance?



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

Re: [sqlite] switching databases?

[Rob Sciuk]

On Sun, 20 Aug 2006, John Salerno wrote:

> Hi everyone. I'm just getting started with sqlite and I was testing it
> on the command line. Is there a way to switch between databases while
> at the sqlite command prompt, or must you start the command prompt
> with a database? For example, must you always type:
>
> $ sqlite3 
>
> or is there some kind of 'switch' type of statement to choose a
> different database to work on once you've entered the sqlite command
> line?
>
> Thanks!
> John
>
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -

The attach command (and Gooble) is your friend ...

http://souptonuts.sourceforge.net/readme_sqlite_tutorial.html

NB: The ATTACH Command: Build a Virtual Table that Spans Multiple Tables
on Separate Databases ...

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