[sqlite] Column Names, Again

Recently, there have been several threads regarding the desired ability to get fully-qualified and original column names from a query result. At the very least, this is useful for dynamic queries and for wrapper writers. There have been at least a couple of proposed solutions, one involving a new

[sqlite] Syntax error ?

Last login: Thu Feb 24 00:08:34 on ttyp1 Welcome to Darwin! fastmac:~ rn$ /Applications/SQLite/sqlite; exit SQLite version 2.8.13 Enter ".help" for instructions sqlite> .database 0 main 1 temp sqlite> create database contacts; # This is

Re: [sqlite] User functions

On Wed, 2005-02-23 at 17:35 -0700, Dennis Cote wrote: > Check the SQLite source file func.c in the zip file > http://www.sqlite.org/sqlite-source-3_1_3.zip. Also available by direct link at http://www.sqlite.org/cvstrac/getfile/sqlite/src/func.c -- D. Richard Hipp <[EMAIL PROTECTED]>

Re: [sqlite] Relative efficiency of joins, subselects, and union/intersect

On 23 Feb 2005 at 18:36, Nathan Kurz wrote: > But maybe I'm not really understanding the advantages of the in-memory > database. Is it in some way inherently faster on lookups than just > setting SQLite to use a really large cache? Well the way I think of it is the if you don't need to keep the

Re: [sqlite] Relative efficiency of joins, subselects, and union/intersect

On Tue, Feb 22, 2005 at 09:38:53PM -0700, John LeSueur wrote: > >In my model, a 'song' record consists of a unique artist/album/title > >A 'tag' is a name/value pair associated with a 'song': > > Song: song_id artist album title > > Tag: song_id name value > >Searching for the tags associated

Re: [sqlite] tricky date time problem

Lloyd, A small improvement is to limit the number of rows used to test for event existence. This avoid scanning the whole event_data table if there are many events. select minutes.i as minute, count(*) as events from (select (event_time / 60) % 60 as begin_minute, ((event_time +

Re: [sqlite] User functions

Bob Dankert wrote: Is there a good example somewhere of how to implement user-defined functions? Bob, Check the SQLite source file func.c inthe zip file http://www.sqlite.org/sqlite-source-3_1_3.zip. It defines all the builtin functions in SQLite. It uses the same API you need to define

Re: [sqlite] SQL optimization

Cory Nelson wrote: Hello folks. I currently have a table: create table t_gloss(entryid integer, type integer, value text); and am running this SQL on it: select entryid,type from t_gloss where value=?; then for each row returned (type is type==0?1:0 from above row, and entryid is the same): select

[sqlite] User functions

Is there a good example somewhere of how to implement user-defined functions? Thanks Bob Envision Information Technologies Associate [EMAIL PROTECTED] v. 608.256.5680 f. 608.256.3780

[sqlite] SQL optimization

Hello folks. I currently have a table: create table t_gloss(entryid integer, type integer, value text); and am running this SQL on it: select entryid,type from t_gloss where value=?; then for each row returned (type is type==0?1:0 from above row, and entryid is the same): select value from

Re: [sqlite] tricky date time problem

Dennis, Thanks for you help so far. I think it is easier for PHP to select the MAX event. The problem I now have is if there is no records for an hour, PHP will through up an error because MAX must have at least one record to process, even if it is 0. Thanks again - Original Message

Re: [sqlite] New to SQLite...............2....3..4 Thank you

You should read SQLite docs: http://www.sqlite.org/docs.html and faq: http://www.sqlite.org/faq.html . TCL interface to SQLite is documented here: http://www.sqlite.org/tclsqlite.html I hope it will help to start. Best regards, Witold - Original Message - From: "Jan Ekström" <[EMAIL

Re: [sqlite] New to SQLite...............2....3..4 Thank you

Thank you for that. I like what I am reading there. Still, TCL is mentioned together with SQLite. Is TCL an alternative to look into? Best regards Jan - Original Message - From: "Witold Czarnecki" <[EMAIL PROTECTED]> To: Sent: Tuesday, February 22, 2005 8:45 PM

Re: [sqlite] tricky date time problem

Dennis Cote wrote: I though you wanted the minute with the most events. I added the outer select to show that that step can be done easily in the same query, rather than relying on PHP to extract this info from the full results for the hour. But of course that portion of the query doesn't work

Re: [sqlite] tricky date time problem

Lloyd Thomas wrote: Revisted your query. I was being a bit lazy. changed 'select minute, max(events)' to 'select minute, events' and added 'order by minute' to give me what I needed. Thanks Lloyd, If you want the whole table simply remove the outer select and add the order by clause as below.

Re: [sqlite] SQL Question

> CREATE TABLE teams (id,name); > CREATE TABLE games (id, date, team1_id, team2_id, result); > > team1_id and team2_id refer to the id in the teams table. > > 1. What query would be best suited to get an output so that the > output would contain the teams names (not only the id) and the > dates

Re: [sqlite] tricky date time problem

Dennis, Revisted your query. I was being a bit lazy. changed 'select minute, max(events)' to 'select minute, events' and added 'order by minute' to give me what I needed. Thanks - Original Message - From: "Dennis Cote" <[EMAIL PROTECTED]> To: "sqlite-users"

[sqlite] SQL Question

Hello, I know this is not directly related to SQLite, but hopefully someone has the time to help me with a (probably) basic SQL question: Consider the following tables: CREATE TABLE teams (id,name); CREATE TABLE games (id, date, team1_id, team2_id, result); team1_id and team2_id refer to the

Re: [sqlite] tricky date time problem

Now I am confused. Jay, I tried your solution but I still end up with one result 0|14 I need to now the result for each minute(even if null) during the hour. Dennis, I tried yours and ended up with much the same thing 0|4 this is the contents of the table between 2004-04-07 10:00:00 and 2004-04-07

Re: [sqlite] Write issues on some computers?

Luc, Luc Vandal said: The database is stored in the [User]\Local Settings\Application Data\ folder for the current user. Forgive my ignorance of Windows user directories, but could it be that some of these users are keeping their [User] folder on a network drive? That is sometimes

RE: [sqlite] Write issues on some computers?

Luc Vandal said: > The database is stored in the [User]\Local Settings\Application Data\ > folder > for the current user. In that case it's worth looking at the folder and the file and making sure that both have write permission for the user in question. This isn't terribly easy to do if your

Re: [sqlite] tricky date time problem

Lloyd, I messed up the math for the end minute calculation. :-[ The correct query is given below. Adding 59 was intended to round the result of an integer division (which drops the remainder), but I'm actually keeping the reminader and throwing away the quotient so it was simply wrong.

Re: [sqlite] tricky date time problem

--- Lloyd Thomas <[EMAIL PROTECTED]> wrote: > Thanks Jay/DRH, > this looks more promising (The "%" > operator > gives you remainder after division). Still not sure how I could apply > it to > start and end unix times. > The columns I have are :- > hour start = start

RE: [sqlite] Write issues on some computers?

Hello Clay, The database is stored in the [User]\Local Settings\Application Data\ folder for the current user. Thanks for your help! Luc Vandal Edovia Technologies Inc. [EMAIL PROTECTED] www.edovia.com -Original Message- From: Clay Dowling [mailto:[EMAIL PROTECTED] Sent: February

Re: [sqlite] Write issues on some computers?

Luc Vandal said: > I don't know if it's SQLite related, but some of our users are complaining > that they can't write to the database. On our side everything is fine. > That > is the case with most users. But for some the SQLite db just seems to > malfunction. Is there something (a dll, a

Re: [sqlite] tricky date time problem

Lloyd: Let me call a variable $seconds and perform your mod 60 and assign the results to $newSeconds: "$newSeconds = $seconds % 60". If you were to look at $newSeconds in the HH:MM;SS, you will find all your SS values were set to zero for each record. I am assuming that your SQLite Table

RE: [sqlite] Write issues on some computers?

Could the problem be MSVCRT.DLL? Luc Vandal Edovia Technologies Inc. [EMAIL PROTECTED] www.edovia.com -Original Message- From: Luc Vandal [mailto:[EMAIL PROTECTED] Sent: February 23, 2005 9:12 AM To: sqlite-users@sqlite.org Subject: [sqlite] Write issues on some computers? Hi,

Re: [sqlite] tricky date time problem

I have two tables the event table which holds the data and a table which has an entry for each minute CREATE TABLE event_data ( call_id INTEGER PRIMARY KEY, desc varchar(32) NOT NULL default '', event_time datetime default NULL, stamptime integer default NULL, duration integer default NULL

[sqlite] Write issues on some computers?

Hi, I don't know if it's SQLite related, but some of our users are complaining that they can't write to the database. On our side everything is fine. That is the case with most users. But for some the SQLite db just seems to malfunction. Is there something (a dll, a windows settings, etc.)

Re: [sqlite] Is it bug?

On Sat, 2005-02-19 at 05:44 -0800, Dan Kennedy wrote: > It has to be text I guess. It can't be a double, as the > IEEE format SQLite uses doesn't support rounding to exact > numbers of decimal places. > This is correct in general. Most base-10 decimals cannot be represented exactly in IEEE

Re: [sqlite] tricky date time problem

On Mon, 2005-02-21 at 21:49 +, Lloyd Thomas wrote: > I have a query which calculates the number of events during an hour by the > minute. It needs to work out which minute has the most events and the > average events during that hour. So it should return an array of 60 results > for an

Re: [sqlite] tricky date time problem

Still no joy. I have tried to use the % operator but I do not get the expected results. Can some one point me in the right direction? I have simplified my query to test Select 3600 %60 as seconds, count (event_id) from event_data WHERE event_time >= 1081331940 and event_time-duration <=