Re: [sqlite] Multitable query question

[Stephen Oberholtzer]

On Thu, May 29, 2008 at 1:09 AM, <[EMAIL PROTECTED]> wrote:

> I've implemented this behaviour in my program. I was just curious whether
> it was possible in sql when I learnt about the Left Join (as there are more
> values in one column than the other).
>
> I guess it isn't or is not really the proper use of the database. Thanks
> very much for the prompt replies though.
> I'll also have to sort out the date entry bit.


Ah, okay :) Cool.

Just for the  heck  of it, I decided to attempt what you were going for.

I do not, repeat, I do NOT recommend using this technique.  I'm just proving
what SQLite is capable of if you put a sufficiently twisted mind towards it
:)


CREATE TABLE Calls (id integer primary key, houseId integer, date date);
CREATE TABLE Letters (id integer primary key, houseId integer, date date);
CREATE TABLE callSeq (sequence integer primary key, date date);
CREATE TABLE letterSeq (sequence integer primary key, date date);
CREATE INDEX Calls_HouseId on Calls(houseId);
CREATE INDEX Letters_HouseId on Letters(houseId);
sqlite> .dump
BEGIN TRANSACTION;
CREATE TABLE Calls (id integer primary key, houseId integer, date date);
INSERT INTO "Calls" VALUES(1,1,'2008-05-15');
INSERT INTO "Calls" VALUES(9,1,'2008-05-28');
INSERT INTO "Calls" VALUES(10,1,'2008-05-28');
INSERT INTO "Calls" VALUES(24,16,'2008-05-15');
INSERT INTO "Calls" VALUES(27,16,'2008-05-15');
INSERT INTO "Calls" VALUES(31,16,'2008-05-15');
CREATE TABLE Letters (id integer primary key, houseId integer, date date);
INSERT INTO "Letters" VALUES(1,16,'2008-05-26');
INSERT INTO "Letters" VALUES(3,16,'2008-05-27');
INSERT INTO "Letters" VALUES(4,16,'2008-05-28');
INSERT INTO "Letters" VALUES(7,16,'2008-05-16');
CREATE INDEX Calls_HouseId on Calls(houseId);
CREATE INDEX Letters_HouseId on Letters(houseId);
COMMIT;


And here's the magic:

create temporary table letterSeq (sequence integer primary key, date date);
create temporary table callSeq (sequence integer primary key, date date);

insert into letterSeq (date) select date from Letters where houseId=16 order
by date desc;
insert into callSeq (date) select date from Calls where houseId=16 order by
date desc;
insert into callSeq (date) select NULL from Letters where (select count(*)
from callSeq) < (select count(*) from letterSeq);

select l.date as LetterDate, c.date as CallDate
from
callSeq c
join letterSeq l on c.sequence = l.sequence
order by c.sequence;


Let the IOSQC (International Obfuscated SQLite Querying Contest) begin!

-- 
-- Stevie-O
Real programmers use COPY CON PROGRAM.EXE
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Multitable query question

[beejayzed]

Stephen Oberholtzer wrote:
> 
> On Wed, May 28, 2008 at 11:15 PM, beejayzed <[EMAIL PROTECTED]> wrote:
> 
>>
>> I have two tables, one which has each visit(call) on a house logged,
>> another
>> which has each letter sent to a house logged.
>> I would like to create a query which has two columns, Letters.Date and
>> Calls.Date, for a specific HouseID.
>>
>> So how do I get the desired result?
>> This would be:
>> 26/5/08 15/5/08
>> 27/5/08 15/5/08
>> 28/5/08 15/5/08
>> 16/5/08
>>
>>
> Before anything else: Use ISO date formats, e.g. 2008-05-26 instead of
> 26/5/08. Not only is it reasonably unambiguous (e.g. to me, 12/5/08 is
> December 5th), but when you sort them as strings they also sort in
> date/time
> order.
> 
> Secondly, glancing over your request, it seems that you have confused a
> database table with the generic tabular data layout you might achieve
> using
> e.g. HTML.
> Each row of a table AKA 'relation' contains related information.  What you
> seem to want, however, is completely different: You seem to actually want
> two *different* sets of data, that have nothing to do with each other, and
> display them in columns side-by-side.
> 
> The proper way to do this is to have your application pull back all of the
> letter dates in one query, then all of the visit dates in a second query,
> and then piece them together for display purposes outside of SQL.
> 
> 
> -- 
> -- Stevie-O
> Real programmers use COPY CON PROGRAM.EXE
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 
> 

Oops.. I sent the reply to Stephen by mistake.
Anyway, I'd created this behaviour in my program, but I was just curious
whether it could be done in an sql query as well.
I guess it's not really suitable for it. 
Thanks for the prompt replies.

Also thanks for the suggestion about the dates.
-- 
View this message in context: 
http://www.nabble.com/Multitable-query-question-tp17526832p17527752.html
Sent from the SQLite mailing list archive at Nabble.com.

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

Re: [sqlite] Multitable query question

[Stephen Oberholtzer]

On Wed, May 28, 2008 at 11:15 PM, beejayzed <[EMAIL PROTECTED]> wrote:

>
> I have two tables, one which has each visit(call) on a house logged,
> another
> which has each letter sent to a house logged.
> I would like to create a query which has two columns, Letters.Date and
> Calls.Date, for a specific HouseID.
>
> So how do I get the desired result?
> This would be:
> 26/5/08 15/5/08
> 27/5/08 15/5/08
> 28/5/08 15/5/08
> 16/5/08
>
>
Before anything else: Use ISO date formats, e.g. 2008-05-26 instead of
26/5/08. Not only is it reasonably unambiguous (e.g. to me, 12/5/08 is
December 5th), but when you sort them as strings they also sort in date/time
order.

Secondly, glancing over your request, it seems that you have confused a
database table with the generic tabular data layout you might achieve using
e.g. HTML.
Each row of a table AKA 'relation' contains related information.  What you
seem to want, however, is completely different: You seem to actually want
two *different* sets of data, that have nothing to do with each other, and
display them in columns side-by-side.

The proper way to do this is to have your application pull back all of the
letter dates in one query, then all of the visit dates in a second query,
and then piece them together for display purposes outside of SQL.


-- 
-- Stevie-O
Real programmers use COPY CON PROGRAM.EXE
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Multitable query question

[Igor Tandetnik]

"beejayzed" <[EMAIL PROTECTED]> wrote
in message news:[EMAIL PROTECTED]
> I have two tables, one which has each visit(call) on a house logged,
> another which has each letter sent to a house logged.
> I would like to create a query which has two columns, Letters.Date and
> Calls.Date, for a specific HouseID.

What for? Why do you want to put two completely unrelated values into 
the same row? This makes no sense from relational calculus standpoint, 
and that's why you have a hard time expressing such a beast in SQL.

What are you trying to achieve?

Igor Tandetnik 



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

Re: [sqlite] Database Corruption

[manas.abi]

I get this problem only after I stop a transaction in between which makes me
think that there is a problem with SQLite code. 

Also, I forgot to mention that I am using SQLite version 3.0.8. Do you know
about any such bug in this version which was removed in the later versions?

Thanks & Regards
Manas
-- 
View this message in context: 
http://www.nabble.com/Database-Corruption-tp17523984p17524980.html
Sent from the SQLite mailing list archive at Nabble.com.

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

Re: [sqlite] Corrupted sqlite_sequence table

[D. Richard Hipp]

On May 28, 2008, at 7:21 PM, Samuel Neff wrote:

> It happens every time.  I can send you a db and the update scripts,  
> but I'll
> need you to keep it confidential (not signed affidavit or anything  
> like
> that, just understanding that it's confidential).
>
> Please confirm this is ok and also which address I should send it to  
> (if
> other than the one you're using for this list).
>

No one will see the database besides me.  I will delete it once the  
bug is fixed.
Send to the address below.

D. Richard Hipp
[EMAIL PROTECTED]



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

[sqlite] Database Corruption

[manas.abi]

I am using SQLite to maintain some transaction information but I see that if
I stop the transaction in between then my database gets corrupted i.e. there
is no Rollback from this point, hence there is only some data of the
transaction which is written into the database. This means that SQLite is
not working in an atomic manner. 
While going through the code I found that SQLite code calls a write function
multiple times to write data into the database and so I put the breakpoint
at the point where the write function is called and after few writes if I
stop the transaction then the SQLite does not recover from this which leads
to Database corruption. 

Can anybody suggest how can I solve this problem?
-- 
View this message in context: 
http://www.nabble.com/Database-Corruption-tp17523984p17523984.html
Sent from the SQLite mailing list archive at Nabble.com.

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

Re: [sqlite] How to add a column with CURRENT_DATETIME?

[Stephen Oberholtzer]

On Wed, May 28, 2008 at 1:31 PM, Dennis Cote <[EMAIL PROTECTED]> wrote:

> [EMAIL PROTECTED] wrote:
> Another possibility would be to use the TIMESTAMP name for an integer
> unix epoch timestamp, and JULIANDAY for the floating point julian day
> number, giving five default value codes.
>
>   Name Inserts
>   ===
>   CURRENT_DATE string date
>   CURRENT_TIME string time
>   CURRENT_DATETIME string date and time
>   CURRENT_TIMESTAMPinteger unix timestamp
>   CURRENT_JULIANDAYreal julianday number



How about CURRENT_EPOCH for unix timestamp?


-- 
-- Stevie-O
Real programmers use COPY CON PROGRAM.EXE
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to add a column with CURRENT_DATETIME?

[Dennis Cote]

[EMAIL PROTECTED] wrote:
> It seems docs/compile.html is wrong.
> 
> SQLITE_OMIT_DATETIME_FUNCS
> If this option is defined, SQLite's built-in date and time manipulation 
> functions are omitted. Specifically, the SQL functions julianday(), date(), 
> time(), datetime() and strftime() are not available. The default column 
> values CURRENT_TIME, CURRENT_DATE and CURRENT_DATETIME are still available.
> 
> So it should be CURRENT_TIMESTAMP instead of CURRENT_DATETIME.
> 

You should file a documentation bug report to get this corrected.

It is an unfortunate choice of names, for the very reason that caused 
this error.

I would really like to see this changed. I my opinion there should be at 
least four such default values. Three that return the date and/or time 
strings used now, and a fourth that returns a floating point julianday 
number that would be stored in the database directly.

   Name Inserts
   ===
   CURRENT_DATE string date
   CURRENT_TIME string time
   CURRENT_DATETIME string date and time
   CURRENT_TIMESTAMPreal julianday number

Another possibility would be to use the TIMESTAMP name for an integer 
unix epoch timestamp, and JULIANDAY for the floating point julian day 
number, giving five default value codes.

   Name Inserts
   ===
   CURRENT_DATE string date
   CURRENT_TIME string time
   CURRENT_DATETIME string date and time
   CURRENT_TIMESTAMPinteger unix timestamp
   CURRENT_JULIANDAYreal julianday number

I realize this will break backwards compatibility but perhaps this could 
be considered for the upcoming 3.6 series. This change would provide for 
smaller database files, since the integer and real formats are normally 
smaller than the string they encode, and faster default inserts, since 
they don't require executing the time and date string formating code.

Dennis Cote

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

Re: [sqlite] 'insert or ignore' vs self join?

[Petite Abeille]

Hello,

On May 27, 2008, at 9:07 PM, Stephen Oberholtzer wrote:

> Well, the first thing you should bring away from this experience is  
> that the
> number of VM instructions isn't really an indicator of how efficient  
> the
> query is :)

Good point :)

> Now, I'm not sure exactly why one is faster than the other,  
> especially since
> you didn't post your exact schema and indices,

The DDL is rather straightforward:

 create table if not exists token
 (
 id  integer primary key not null,
 nametext not null
 )

 create unique index if not exists token_name on token( name )

http://dev.alt.textdrive.com/browser/HTTP/Finder.ddl#L60

> and I have no idea how many rows there are in either table.

The incoming data set size varies from 108 to 3345 rows, with a  
average size of around 930 rows. The target table size is about 75,148  
rows. Over time, most of the incoming rows will already exist in the  
target table.

> But if I had to guess, it's because of the ORDER BY clause.  In  
> general, an
> ORDER BY means that SQLite needs to generate a temporary table with  
> all the
> rows to be selected/inserted,
> then sort that temporary table.  The INSERT OR IGNORE version has to
> unconditionally sort the entire 'stage' table; your second query  
> only has to
> sort those rows in 'stage' that don't already exist in 'table'.  If  
> each
> table fits comfortably in your computer's disk cache, the extra pass  
> won't
> matter so much.

Ah... yes... the order by clause... good point... indeed removing the  
'order by' from the 'insert or ignore' statement brings down its  
execution time a whisker away from the 'self join' version :)

Thanks for the explaination!

Cheers,

--
PA.
http://alt.textdrive.com/nanoki/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Cross compiling sqlite3.c, anamolies.

[John Stanton]

Sqlite is a nicely structured C program which will always be better than 
C++.  Nice, clean easy to read and well documented C fits in everywhere.

Dennis Cote wrote:
> Rajesh Nair wrote:
>> Is there any program to develop sqlite in C++.
> 
> See http://www.sqlite.org/cvstrac/wiki?p=SqliteWrappers for a selection 
> of C++ wrappers.
> 
> I would suggest trying CppSQlite since it is a thin wrapper with source 
> available for free.
> 
>> I have developed a C++ wrapper for sqlite3.x but if sqlite3 itself is 
>> developed in C++ then it could be more readable.
> 
> I'm fairly certain that's not going to happen. Great effort has been put 
> into keeping SQLite's source clean C code with no C++ isms.
> 
> C is still the lingua franca of the computing world, and probably will 
> be for quite some time. The fact that SQLite is written in C, which 
> almost any language can interface with, and not C++ is a large part of 
> the reason that so many different language wrappers have been written.
> 
> Most C++ compilers can be told to compile a source file as C rather than 
> C++ to avoid extraneous errors.
> 
>> I know C, but not a hardcore C programmer.And is working with VC++ for 
>> last 6 years (MFC and ATL) .
>>
> 
> You don't need to program in C to use SQLite. There are wrappers for 
> most languages and many frameworks.
> 
> HTH
> Dennis Cote
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

Re: [sqlite] How to add a column with CURRENT_DATETIME?

[Dennis Cote]

[EMAIL PROTECTED] wrote:
> I am trying to create a table with a column with default value of current 
> datetime (on insertion). But the following sql script can pass, but does not 
> do what it is supposed to do.
> 
> What is the correct syntax?
> 
> create table ViewState
> (
>  session_id text not null,
>  viewstate_key text not null,
>  viewstate blob,
>  timestamp text default CURRENT_DATETIME
> )
> ;
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 

You need to use CURRENT_TIMESTAMP as shown at 
http://www.sqlite.org/lang_createtable.html

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

Re: [sqlite] How to add a column with CURRENT_DATETIME?

[P Kishor]

On 5/28/08, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:
> I am trying to create a table with a column with default value of current 
> datetime (on insertion). But the following sql script can pass, but does not 
> do what it is supposed to do.
>
>  What is the correct syntax?
>
>  create table ViewState
>  (
>   session_id text not null,
>   viewstate_key text not null,
>   viewstate blob,
>   timestamp text default CURRENT_DATETIME
>  )
>  ;


sqlite> CREATE TABLE foo (i INTEGER PRIMARY KEY, t TEXT, d DATETIME
DEFAULT CURRENT_TIMESTAMP);
sqlite> INSERT INTO foo (t) VALUES ('junk');
sqlite> SELECT * FROM foo;
1|junk|2008-05-28 16:53:42
sqlite>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Cross compiling sqlite3.c, anamolies.

[Rajesh Nair]

Is there any program to develop sqlite in C++.
I have developed a C++ wrapper for sqlite3.x but if sqlite3 itself is 
developed in C++ then it could be more readable.
I know C, but not a hardcore C programmer.And is working with VC++ for 
last 6 years (MFC and ATL) .


- Original Message - 
From: "D. Richard Hipp" <[EMAIL PROTECTED]>
To: "General Discussion of SQLite Database" 
Sent: Tuesday, May 27, 2008 1:01 AM
Subject: Re: [sqlite] Cross compiling sqlite3.c, anamolies.


>
> On May 26, 2008, at 3:24 PM, A. H. Ongun wrote:
>>
>> Now, when I change the compiler to ppc_82xx-g++ from ppc_82xx-gcc I
>> get hundreds of error messages.
>>
>> I am puzzled to see why this is so.
>
> My guess would be because SQLite is written in C, not C++.
>
> D. Richard Hipp
> [EMAIL PROTECTED]
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users 

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

[sqlite] Open-source project hosting at SQLite.org. Was: .net wrappers - mono vs. phxsoftware

[D. Richard Hipp]

On May 28, 2008, at 11:24 AM, Robert Simpson wrote:

> You can browse the SVN sources for Mono.Data.SQLite here: ...

FWIW, I hereby make the following offer to any open-source projects  
related to SQLite:

 *   A third-level domain name.  Ex:   mono.sqlite.org
 *   Your own website.  Ex:   www.mono.sqlite.org
 *   A fossil repository for storing your files.
 *   Links to your site from the main SQLite website.

Requests for new repositories will be evaluated based on the quality  
of the project and the relevance of the project to the SQLite core.  I  
am looking for projects that add significant value to SQLite.   
Examples of the projects I am looking for include things like Robert  
Simpson's Mono interface, ODBC drivers, bindings to other languages  
(ex: Lua, Lisp, Fortran, etc.), and/or GUI database management tools  
designed especially for SQLite.

Fossil is an easy-to-use cross-platform distributed configuration  
management system.  Fossil is built on top of SQLite and is currently  
used to host nearly two dozen projects and subprojects on the SQLite  
website and countless other projects on other sites.  Because fossil  
is distributed, you the developer maintain maintain a complete copy of  
the source code repository on your local machine(s).  So outsourcing  
the webhosting to SQLite.org does nothing to threaten your control  
over your project.

Every fossil repository includes its own wiki and bug-tracking  
system.  (NB:  The bug-tracking system is under development and is not  
yet available, but will be available soon.)  Both wiki and bugs are  
distributed together with source code.

Additional information about fossil:

 http://www.fossil-scm.org/

Examples of other open-source projects currently hosted on SQLite.org  
using fossil:

 http://www.sqlite.org/docsrc
 http://tdbc.tcl.tk/
 http://3dcanvas.tcl.tk/fossil
 http://www.fossil-scm.org/ (Fossil is self-hosting)

D. Richard Hipp
[EMAIL PROTECTED]



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

Re: [sqlite] Moving port from 3.5.1 to 3.5.7...

[D. Richard Hipp]

On May 28, 2008, at 11:25 AM, Jens Miltner wrote:

> s there any work being done trying to either minimize the
> synchronization needed or provide a memory allocator that doesn't do
> all the alerting

Yes.  This has been requested before and will appear soon.

D. Richard Hipp
[EMAIL PROTECTED]



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

Re: [sqlite] Moving port from 3.5.1 to 3.5.7...

[Jens Miltner]

I'd like to chime in that we have somewhat similar requirements:
Our app is heavily threaded and we had sqlite's memory allocations  
show up as bottlenecks at times. This happened when the 'new'  
allocators that allow for high-water marks, etc. appeared first. Since  
we don't need any of the high-water alert features, we decided we'd  
revert back to a bare-bones memory allocator that would essentially  
just wrap around the malloc(), realloc() and free() system APIs. This  
improved performance quite a bit for heavy threading situations.

Is there any work being done trying to either minimize the  
synchronization needed or provide a memory allocator that doesn't do  
all the alerting, but also doesn't require to synchronize each single  
allocation?

Our experience is that the OS allocators seem to do a much better job  
in heavily threaded situations without too much blocking...

Thanks,



Am 21.03.2008 um 22:23 schrieb Mark Spiegel:

> The closest memory implementation would be the default one.  The other
> implementations that try to do their own pool management are
> unacceptable as memory system wide to too valuable to allow SQLite to
> allocate pool that it is not currently using.   I am also  
> fundamentally
> opposed to trying to outsmart the memory manager until someone can
> demonstrate a need to do so.  I have yet to see a successful effort to
> do this in my environment.
>
> Back to the default implementation, the trouble is that there is too
> much heavy synchronization and it prevents me from using available  
> high
> performance memory management primitives and available debugging
> support.  Performance and space are critical factors.  I'm working in
> the NT system it would be unreasonable of me to expect that any of  
> your
> implementations to be well suited to that environment.  That's why I
> found the SQLITE_OMIT_MEMORY_ALLOCATION exciting.  I was fully  
> expecting
> from the beginning that this would be an area that I would have to
> implement just like the VFS and mutex support.  The difference is that
> now I have to make a few changes to the amalgamated source to do it
> rather than just #define a value.  No problem.
>
> Thanks for your help.
>
> [EMAIL PROTECTED] wrote:
>> Mark Spiegel <[EMAIL PROTECTED]> wrote:
>>
>>> I'm looking to jump my code port forward from 3.5.1 to 3.5.7.
>>>
>>> Clearly I have some memory management work to do since
>>> SQLITE_OMIT_MEMORY_ALLOCATION support has been dropped.  None of the
>>> existing allocation implementations look acceptable so I'll have  
>>> to roll
>>> my own,
>>>
>>
>> What do you need that none of mem[12345].c provide?
>>
>>
>>> but that looks pretty straight forward.
>>>
>>> Two questions:
>>>
>>> 1) Has the VFS interface changed from 3.5.1 to 3.5.7?
>>>
>>
>> No.
>>
>>
>>> 2) Is the SQLITE_MUTEX_APPDEF #define still supported in the same  
>>> manner
>>> from 3.5.1 to 3.5.7?  (It appears that it is, but it never hurts  
>>> to ask.)
>>>
>>
>> Yes.
>>
>>
>> --
>> D. Richard Hipp <[EMAIL PROTECTED]>
>>
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
>>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

Re: [sqlite] .net wrappers - mono vs. phxsoftware

[Robert Simpson]

1.  "Better" is a relative term.  Since the effort of porting changes from
my version to mono is non-trivial, I imagine that mono will be slower to
adapt to newer SQLite releases in favor of stabilizing the port.  So you
might get a more stable, less frequently updated version from Mono.
2.  Ties in with #1.  Traditionally I've updated more frequently than mono
and adopted newer SQLite builds faster.
3.  Marek Habersack ported the code around February of 2007.  I can't recall
exactly what version he ported.
4.  They have to use the stock sqlite library, so their changes were to call
the native sqlite api directly, and had to remove some of the features I
added to the core.  They also can't combine the native library and assembly
into a single module.
5.  Unsure of this.
6.  No, though I have been insanely busy lately and haven't been able to
devote as much time to SQLite as I have in the past.


You can browse the SVN sources for Mono.Data.SQLite here:
http://anonsvn.mono-project.com/viewcvs/trunk/mcs/class/Mono.Data.Sqlite/Mon
o.Data.Sqlite_2.0/?rev=90845#dirlist

That'll give you an idea how often its updated.

BTW:  I finally got around to posting an update today.
http://sqlite.phxsoftware.com

Robert


-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Wilson, Ron P
Sent: Tuesday, May 27, 2008 6:36 AM
To: sqlite-users@sqlite.org
Subject: [sqlite] .net wrappers - mono vs. phxsoftware

Denis pointed another poster to the url below, which I was unaware of:

http://www.sqlite.org/cvstrac/wiki?p=SqliteWrappers

I'm currently using the phxsoftware ado.net 2.0 wrapper for sqlite by
Robert Simpson, which is excellent.  However, I am surprised to see that
the Mono project recently imported Simpson's code
(http://www.mono-project.com/SQLite).  This begs the following
questions:

1. Which project will be better maintained in the long run?
2. Which maintainer will continue to improve the project, and at what
frequency?
3. When did Mono merge in the phx code base?  (Simpson's latest build is
Dec 2007.)
4. Did Mono make any significant changes to the phx project?
5. Will the Mono project try to stay synchronized with phx?
6. Is Simpson passing the baton?

Does anyone have any insight into this?  Also, I can check this myself
later, but does anyone know off the top of their head how much test
coverage the phx code base has, if any?

RW

PS. Robert - if you are reading this list - THANK YOU.

--Tyco Electronics--
Ron Wilson, S/W Systems Engineer III
434.455.6453 tycoelectronics.com


Important:

This electronic mail message and any attached files contain information
intended for the exclusive use of the individual or entity to which it
is addressed and may contain information that is proprietary,
privileged, confidential and/or exempt from disclosure under applicable
law.  If you are not the intended recipient, you are hereby notified
that any viewing, copying, disclosure or distribution of this
information may be subject to legal restriction or sanction. Please
notify the sender, by electronic mail or telephone, of any unintended
recipients and delete the original message without making any copies.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


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

Re: [sqlite] Query help?

[Stephen Oberholtzer]

What if you took a slightly different tack?

CREATE TABLE FinishedWork (
EventTime INTEGER NOT NULL,
FileName TEXT NOT NULL,
ProcessID INTEGER NOT NULL,
BytesProcessed INTEGER NOT NULL,
isDuplicate integer-- tri-state: 0=not duplicate 1=duplicate
null=unknown
);


And then periodically run this:

update FinishedWork
set isDuplicate = case when exists(select 1 from FinishedWork fw2 where
fw2.ProcessId=FinishedWork.ProcessId and fw2.FileName=FinishedWork.Filename
and fw2.rowid < FinsishedWork.rowid) then 1 else 0 end where isDuplicate is
null;

Then your report would be this:

SELECT ProcessID, sum(BytesProcessed)

FROM FinishedWork

WHERE EventTime > {20 minutes ago}
AND isDuplicate=0;



By the way, what's magic about 20 minutes ago?

-- 
-- Stevie-O
Real programmers use COPY CON PROGRAM.EXE
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite Step Function

[Stephen Oberholtzer]

On Wed, May 28, 2008 at 12:32 AM, sqlite <[EMAIL PROTECTED]>
wrote:

>
> Dear Stephen,
>
> Thanks for the reply. As you said we checked the EXPLAIN QUERY PLAN with
> our
> query and it has shown that all the four tables we use in the query are
> using their indexes and there is no ORDER BY class in our query. So
> sqlite3_prepare compiles the query and sqlite3_step executes the query does
> it mean the execution time for our query is 40 secs because we are
> retrieving the records soon once gets executed.



Weird.  Would it be possible to post the schema (use the '.schema' command
in the sqlite3 command-line program) and the SELECT statement you're using?

Also, which version of SQLite are you using?




-- 
-- Stevie-O
Real programmers use COPY CON PROGRAM.EXE
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Possible to use field names instead of index offsets in queries?

[danjenkins]

Thank you for your help- I understand it better and can work it out now.

-- 
View this message in context: 
http://www.nabble.com/Possible-to-use-field-names-instead-of-index-offsets-in-queries--tp17461475p17511500.html
Sent from the SQLite mailing list archive at Nabble.com.

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

Re: [sqlite] Move rowset pointer to Initial record

[Igor Tandetnik]

"sqlite" <[EMAIL PROTECTED]> wrote
in message news:[EMAIL PROTECTED]
> Thanks for your reply, we already tried this method but by executing
> the sqlite_reset function it will again prepare the rowset, which
> takes more time for us to get the initial record, is there any other
> way to get the initial record without reseting the rowset where
> takes less time to get the initial record.

Not with SQLite. You can, of course, just cache in memory the records 
you've already seen.

Igor Tandetnik 



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

Re: [sqlite] Move rowset pointer to Initial record

[sqlite]

Igor Tandetnik wrote:
> 
> "sqlite" <[EMAIL PROTECTED]> wrote
> in message news:[EMAIL PROTECTED]
>> We are using SQLite for our application development which would be
>> deployed in a pocket pc.Here we are using a query which has three
>> Inner joins and using sqlite3_prepare and sqilte3_step function to
>> prepare the records and to fetch the records.By executeing the Query
>> and we moves to certain records say up to 10 records,while going
>> upwards how to move to the initial record without reexecuting the
>> Query and preparing the rowset again,
> 
> You can call sqlite3_reset, after which the next sqlite3_step would 
> restart from the first row.
> 
> Igor Tandetnik 
> 
> Dear Igor 
> 
> Thanks for your reply, we already tried this method but by executing the
> sqlite_reset function it will again prepare the rowset, which takes more
> time for us to get the initial record, is there any other way to get the
> initial record without reseting the rowset where takes less time to get
> the initial record.
> 
> Regards,
> kartthi
> 
> 
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 
> 

-- 
View this message in context: 
http://www.nabble.com/Move-rowset-pointer-to-Initial-record-tp17509266p17511237.html
Sent from the SQLite mailing list archive at Nabble.com.

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

Re: [sqlite] Move rowset pointer to Initial record

[Igor Tandetnik]

"sqlite" <[EMAIL PROTECTED]> wrote
in message news:[EMAIL PROTECTED]
> We are using SQLite for our application development which would be
> deployed in a pocket pc.Here we are using a query which has three
> Inner joins and using sqlite3_prepare and sqilte3_step function to
> prepare the records and to fetch the records.By executeing the Query
> and we moves to certain records say up to 10 records,while going
> upwards how to move to the initial record without reexecuting the
> Query and preparing the rowset again,

You can call sqlite3_reset, after which the next sqlite3_step would 
restart from the first row.

Igor Tandetnik 



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

[sqlite] Move rowset pointer to Initial record

[sqlite]

Dear All,

 We are using SQLite for our application development which would be deployed
in a pocket pc.Here we are using a query which has three Inner joins and
using sqlite3_prepare and sqilte3_step function to prepare the records and
to fetch the records.By executeing the Query and we moves to certain records
say up to 10 records,while going upwards how to move to the initial record
without reexecuting the Query and preparing the rowset again,

kindly help us in this regard.

Thanks in Advance,

Regards,
kartthi.
-- 
View this message in context: 
http://www.nabble.com/Move-rowset-pointer-to-Initial-record-tp17509266p17509266.html
Sent from the SQLite mailing list archive at Nabble.com.

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