Re: [sqlite] SQLite template files

> I believe that by 'template files' you mean some example databases for common > applications, for instance invoicing, a list of the albums you have, a recipe > database, etc.. Yes, that is correct. > That's because what you seek does not exist. Well, it does exist if people share their

Re: [sqlite] SQLite template files

Thanks to those who replied on this topic, but no-one offered any repository of SQLite template files. Does that mean there aren't any available? Thanks, Tom BareFeet From: BareFeet <list@tandb.com.au> Date: 27 March 2010 5:58:32 PM AEDT To: General Discussion of SQLite Da

[sqlite] SQLite template files

to facilitate data entry in views (ie not just tables). So I am especially interested in SQLite files that make use of views, triggers, relationships etc. Some simple, some advanced. Can you direct me to were I might find some useful templates? Thanks, Tom BareFeet -- Comparison of SQLite GUI tools

Re: [sqlite] Passing Value from one query to another

ocaltime', '-30 days') group by monitordata_hourly.deviceaddress ; Tom BareFeet -- Comparison of SQLite GUI tools: http://www.tandb.com.au/sqlite/compare/?ml ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Passing Value from one query to another

AS SumOfch1kwh, Round(Sum(monitordata_hourly.ch1kwh),3)*(select kwhcost1 from applications) AS SumOfch1kwh_cost FROM monitordata_hourly Where monitordata_hourly.deviceaddress=142265 and (datetime(monitordata_hourly.date))>=datetime('now', 'localtime', '-30 days') Group by monitordata_hourly.devic

Re: [sqlite] Parsing create statements

meaningful breakup of a create statement (I showed create table, but I am also after views, triggers etc) using a call to an SQLite method or is there some prepared Lemon syntax generator to do this? Thanks, Tom BareFeet -- Comparison of SQLite GUI tools: http://www.tandb.com.au

Re: [sqlite] Parsing create statements

Hi Roger, > BareFeet wrote: >> At the moment I am resorting to developing regular expressions to do the >> parsing. They work, but it seems to be re-inventing the wheel. > > You won't be able to do parsing completely with regular expressions. Create > statements let yo

Re: [sqlite] Parsing create statements

Hi Vivien, >> BareFeet wrote: >> >> Is there any way to parse a create statement (eg create table, create view, >> create trigger) into its components? >> >> Since SQLite does this internally using the Lemon parser, surely there's a >> simple wa

Re: [sqlite] Parsing create statements

gencies I may have missed? Thanks, Tom BareFeet -- Comparison of SQLite GUI tools: http://www.tandb.com.au/sqlite/compare/?ml ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Parsing create statements

sn't give table constraints, column constraints etc. Thanks, Tom BareFeet -- Comparison of SQLite GUI tools: http://www.tandb.com.au/sqlite/compare/?ml ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Parsing create statements

e, array of constraints (as illustrated in previous email). Thanks, Tom BareFeet -- Comparison of SQLite GUI tools: http://www.tandb.com.au/sqlite/compare/?ml ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Parsing create statements

ame: detail: collate nocase 3: name: Family ID type: integer constraints: 1: name: detail: references Family(ID) foreign table: Family foreign column: ID I'm writing in Objective-C, so anything accessible to it would be helpful.

Re: [sqlite] SQLITE_ENABLE_COLUMN_METADATA question ...

ed to follow to enable DSQLITE_ENABLE_COLUMN_METADATA, compile and link my XCode project against it? Thanks, Tom BareFeet ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Table aliases

lomerate table: create view "English Language" as select * from AllLanguages where Language = 'English'; Tom BareFeet -- Comparison of SQLite GUI applications: http://www.tandb.com.au/sqlite/compare/?ml ___ sqlite-users mailing

Re: [sqlite] Tedious CSV import question

on all these issues, though the ABNF grammar implies > that white space outside quotes is not tolerated, which could lead > to considerable user surprise. Technically: 1234,abc"123",abc does not conform to CSV, as answered above. Similarly: 1234, "123",abc does n

Re: [sqlite] ROWID bug in SELECT with JOIN?

owid as rowid, * from names, friends... Beware that this can return multiple rows with the same rowid, if a name has more than one friend. Tom BareFeet -- Comparison of SQLite GUI applications: http://www.tandb.com.au/sqlite/compare/?ml ___ sqlite-user

Re: [sqlite] Tedious CSV import question

On 26/09/2009, at 12:00 AM, Jay A. Kreibich wrote: > On Fri, Sep 25, 2009 at 10:24:15AM +1000, BareFeet scratched on the > wall: > >> In reality, in the thousands of CSV files I've dealt with >> over the years, they all follow the same standard: >> >> 1. Rows ar

Re: [sqlite] Tedious CSV import question

he encompassing specification works for all cases. It's not that big a deal for SQLite to support it, so it should. Tom BareFeet -- Comparison of SQLite GUI applications: http://www.tandb.com.au/sqlite/compare/?ml ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Most wanted features of SQLite?: Stored procedures

out any results back into my code, potentially reinject into another SQL statement etc. Proper procedures would facilitate one call to SQLite, syntax checking of the procedure, precompiled optimizations etc. Tom BareFeet ___ sqlite-users mailing list

Re: [sqlite] Most wanted features of SQLite?: Access to database structure

to me that the SQLite code must already be parsing out these parameters internally so it would be relatively simple to make them available, and far more consistent than us re-inventing the wheel. Thanks, Tom BareFeet ___ sqlite-users mailing list

Re: [sqlite] trouble with precompiled binaries for MacOS X

te3", set execute permission (if not already) and move it to /usr/ local/bin/sqlite3. Tom BareFeet -- Comparison of SQLite GUI applications: http://www.tandb.com.au/sqlite/compare/?ml ___ sqlite-users mailing list sqlite-users@sqlite.org http://s

Re: [sqlite] SQLite Tools

mpare/?ml >> is there a SQLite centric utility to do a wide range of admin tasks >> and to elegantly document the data base structure? What admin tasks exactly are you after? Tom BareFeet ___ sqlite-users mailing list sqlite-users@sqlite.org http:

Re: [sqlite] GUI design & managment tool?

ious SQLite GUI browsers here: http://www.tandb.com.au/sqlite/compare/?ml It focuses on tools for Mac but some of the apps shown are also available for other platforms. Tom BareFeet ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.o

Re: [sqlite] Denormalisation

assing on the actions as desired to the underlying tables or raising exceptions for disallowed data etc. Your application (or even SQL command line) can then facilitate user changes to data in the views in the same way that it would to a denormalised table. It is fully portable from one appl

Re: [sqlite] [Columns] Keeping internal + beautified names?

MyTable set ID = new.Identification , Name = new.Name where ID = old.Identification ; end ; create trigger "MyView Delete" instead of delete on MyView begin delete from MyTable where ID = old.Identification ; end ; Tom BareFeet -- Comparison of SQLite GUI applications: http://www.tandb

[sqlite] Replying to posts (was: plans for completion of INSERT OR REPLACE INTO?)

of the message, or include just > enough text of the original to give a context. Amen!! :-) Tom BareFeet ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Column headers of result

QL commands. > If the latter, replacing your 'WHERE' and 'ORDER BY' clauses with > LIMIT 1 will give you the right result if there's any data at all. That will only work if it's a simple select, but not if it contains begin/commit, some inserts before the select, an existing limit etc. Thanks for

Re: [sqlite] Column headers of result

is only a limitation in the shell (or using sqlite3_exec); column > headers can be queried from prepared statements as already pointed > out. Yes, thanks, noted. My query/wish is to get that info from the command line. Thanks, Tom BareFeet ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Column headers of result

; It would be great to see a pragma or built in SQL function that returned the column headers of a given query. > Thanks, Tom BareFeet ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Column headers of result

t possible). How can I get just the column headers without all the result rows? Thanks, Tom BareFeet ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Column headers of result

headers in the result? I know I can get the column info of a table using pragma table_info, but I don't think that works for an ad-hoc query. Thanks, Tom BareFeet ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin

Re: [sqlite] [ANN] SQLiteManager 3.0

did in v2.5 as well) 2. SQLiteManager fails in several actions when the table name (or probably also the column name) requires quoting (eg if the name contains a space) Thanks, Tom BareFeet ___ sqlite-users mailing list sqlite-users@sqlite.org htt

Re: [sqlite] how can we solve IF EXIST in SQLite

ommunication, especially when asking for help. It takes far less time for one person to punctuate their own text than 200 readers to try to mentally insert punctuation after receiving. Tom BareFeet -- Comparison of SQLite GUI applications: http://www.tandb.com.au/sqlite/compare/?ml ___

Re: [sqlite] how can we solve IF EXIST in SQLite

u can't put an action (such as an update or an insert) inside a case statement. You can only put expressions (including select statements) within a case statement. Tom BareFeet -- Comparison of SQLite GUI applications: http://www.tandb.com.au/sqlite/compare/?ml ___

Re: [sqlite] how can we solve IF EXIST in SQLite

Product_batch_code = 1000) where Prod_batch_code = 1000 ; insert into Stock_Tab (Stock_ID, Prod_Batch_Code, Stock_Qty, Stock_Date) values (20009, 1003, 200, datetime('now')) where not exists (select 1 from Stock_Tab where Prod_Batch_Code = 1000) ; commit ; Tom BareFeet -- Comparison of SQ

Re: [sqlite] create index on view

d automatically update the sums table MySums1 using triggers on your primary tables. Tom BareFeet -- Comparison of SQLite GUI applications: http://www.tandb.com.au/sqlite/compare/?ml ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] create index on view

preliminary suggestions: 1. Stop repeating the same question. 2. Post the schema of your tables and views and the query. Tom BareFeet ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] setting a date in a BEFORE INSERT trigger

omer SET instertedon = DATETIME('NOW') , updatedon = DATETIME('NOW') , updatedby = new.insertedby WHERE CustomerId = new.CustomerId ; END; Tom BareFeet -- Comparison of SQLite GUI applications: http://www.tandb.com.au/sqlite/compare/?ml

Re: [sqlite] Large SQLite3 Database Memory Usage

ge, MonitoredRef, EventRef, ToState, Priority, Acked from Events where Events.ID >= (select max(ID) from Events) - 100 and Events.NotificationTime >= {ts '2009-05-04 14:44:10'} order by ID desc HTH, Tom BareFeet -- Comparison of SQLite

[sqlite] select raise with expression

' where ID = ' || new.ID || ' because this Account Code does not exist in the Accounts table.') but SQLite won't accept it, giving an error: SQL error near line 6: near "||": syntax error Is there another way? Or can the raise() function be enhanced to allow it? Thanks, To

Re: [sqlite] Problem with ordering

k <= 1000 AND parent_fk IS NOT NULL ) ; I think you'll need just the following index: CREATE INDEX EventIndex ON Event (bringsSchedule, sourceMachine_id, virtualClock, parent_fk); Hope this helps, Tom BareFeet -- Comparison of SQLite GUI applications: http://www.tand

Re: [sqlite] Joining 2 tables on 3 indexed columns?

Hi GüŸnther, > I need to join 2 tables A and B on 3 columns, ie. > > "select A.4, B5 where A1 = B1 and A2 = B2 and A3 = B3" > > the columns B1, B2, B3 are individually indexed, I also have an > index of > (B1,B2,B3). > The query is very slow, I don't understand why, can someone please > tell

Re: [sqlite] Insert mode shows wrong type

sert statement is "wrong" in the sense that running it would give a different value (type) than the original. As I mentioned, there therefore doesn't appear to be any way to get the command line to show the data in a table with implied type. Thanks, Tom BareFeet ___

Re: [sqlite] tool to browse a sqlite database

Hi Eric, > I'm loking for some gui tools for looking at and changing my sqlite > database, See a comparison of several GUI SQLite tools here: http://www.tandb.com.au/sqlite/compare/?ml Tom BareFeet ___ sqlite-users mailing list sqlite

Re: [sqlite] SQLite GUI tools (was: Wiki page on Management Tools - should it explicitely state Mac OS X support?)

Base prevents editing of data in views, even though SQLite supports it, for views that have "instead of" triggers. 3. Base doesn't open SQLite database files on AppleShare volumes. Thanks, Tom BareFeet -- Comparison of SQLite GUI applications: http://www.tandb.com.au/sqlite/compa

Re: [sqlite] Wiki page on Management Tools - should it explicitely state Mac OS X support?

and I'll see what I can do. > > http://www.malcolmhardie.com/sqleditor/ > Not a db GUI, but a db schema editor... not specific to SQLite, but > works excellently with and for SQLite. Thanks for the info, but as you can see on the web page, I already have this in my comparison table. Thanks, Tom Bare

Re: [sqlite] SQLite GUI tools (was: Wiki page on Management Tools - should it explicitely state Mac OS X support?)

e a look and let me know anything I've missed: http://www.tandb.com.au/sqlite/compare/?ml Tom BareFeet -- Comparison of SQLite GUI applications: http://www.tandb.com.au/sqlite/compare/?ml ___ sqlite-users mailing list sqlite-users@sqlite.org http:/

Re: [sqlite] Foreign key support

ality to the shell is still in > cvs. It will be available as part of 3.6.12. Oh, I see. It's a case of premature documentation ;-) I'll try again when 3.6.12 comes out. Thanks, Tom BareFeet -- Comparison of SQLite GUI applications: http://www.tandb.com.au/sqlite/compare/?ml

Re: [sqlite] Double entry bookkeeping

in Accounts on Entries."Account Code" = Accounts.Code group by "Transaction ID", ID ; I realize that the foreign keys (eg references ... on delete restrict) aren't currently implemented by SQLite, but they do parse and can be implemented by triggers, such as via the genk

Re: [sqlite] Foreign key support

valid arguments: "genfkey". Enter ".help" for help sqlite> .genfkey --exec unknown command or invalid arguments: "genfkey". Enter ".help" for help What am I missing? Thanks, Tom BareFeet -- Comparison of SQLite GUI applications: http://www.tandb.com.au

Re: [sqlite] Installing SQLite

cation where you want to run it. Somewhere like /usr/ local/bin/sqlite3. Do NOT replace the sqlite3 that is installed in the system /usr/bin/sqlite3. I have a list of SQLite GUI apps for Mac OS X here: http://www.tandb.com.au/sqlite/compare/?ml Tom BareFeet

Re: [sqlite] Wiki page on Management Tools - should it explicitely state Mac OS X support?

. I have a page of SQLite GUI apps listed and compared at: http://www.tandb.com.au/sqlite/compare/?ml If you know of any more applications or would like to see another feature compared, reply here in this forum and I'll see what I can do. Tom BareFeet -- Comparison of

Re: [sqlite] How do I do this join on multiple columns?

t;not exists (select 1..." as above. That way, the database engine only has to find the first match (or non match), rather than scan the whole table redundantly. HTH, Tom BareFeet Tom BareFeet -- Comparison of SQLite GUI applications: http://www.tandb.com.au/sqlite/compare/ __

Re: [sqlite] SQL error: no such function: replace

nto your app or install the newer command line tool in your user directory etc. How are you using SQLite? Tom BareFeet -- Comparison of SQLite GUI applications: http://www.tandb.com.au/sqlite/compare/?ml ___ sqlite-users mailing list sqlite-users@sql

Re: [sqlite] Error on INSERT with SELECT Max(id) FROM ...

for entities (table and columns names use double quotes: ") versus literals (single quotes: '). So it should read: insert into "tblName" values (null, 'five'); create table "tblRefMaxName" ("ref" integer, "nn" text); insert into "tbl

Re: [sqlite] Double entry bookkeeping

yes, they use Operator as plus or minus with always a positive Amount. I think that's unnecessary and confusing, so I would instead opt for an Amount that can be positive or negative, eliminating the Operator column. The data can still be presented to the user in Debit and Credit columns th

Re: [sqlite] Double entry bookkeeping

d contact details of the person you were meeting. You'd store it in a People table and refer each event to it. Linking the data through "an extra row lookup" is trivial and optimized within the SQL engine. Thanks, Tom BareFeet -- Comparison of SQLite GUI

Re: [sqlite] Double entry bookkeeping

>> , "Transaction ID" --> "Accounting Transaction".ID >> , "Account ID" --> Account.ID >> , Amount >> , Operator -- plus or minus >> ) >> ; >> create table "Accounting Trans

Re: [sqlite] Double entry bookkeeping

ting Transaction".ID >> , "Account ID" --> Account.ID >> , Amount >> , Operator -- plus or minus >> ) >> ; >> create table "Accounting Transaction" >> ( >>ID >> , Date >>

Re: [sqlite] Double entry bookkeeping

ction" ( ID , Date , Description ) ; create table Account ( ID , Description ) ; Is tat the general model others have used, experienced, seen, implemented? Thanks, Tom BareFeet ___ sqlite-users mail

[sqlite] Double entry bookkeeping

Hi all, I'm trying to design a schema for double entry book-keeping. Is there already a schema available that provides this functionality? As I see it, I need to start with something like the schema below. Any other approaches? create table Accounts ( Code text unique collate

[sqlite] Recording history of changes to schema

not create trigger on system table Is it possible to enable this functionality? Thanks, Tom BareFeet ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Finding rows not in second table?

es.Code = Code.ID) ; Another solution is: select id from code except select code from companies ; HTH, Tom BareFeet -- SQLite GUI apps compared: http://www.tandb.com.au/sqlite/compare/?ml ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Summing without duplicating

quot;Order" = "Orders".ID group by "Invoice" ; which gives the desired sums without duplicating the delivery for two items from the same order: Invoice sum(Buy) sum(Delivery) Expense 10001208.98 37.0245.98 In more complex tests on a larger data s

Re: [sqlite] Summing without duplicating

into $10 against each Invoice. I'll have to think some more about that :-/ Thanks, Tom BareFeet ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Summing without duplicating

Hi Igor, Thanks for replying. > BareFeet <[EMAIL PROTECTED]> wrote: >> I tried this: >> >> select >> "Invoices".ID as Invoice >>, (select sum(Buy) from Moves where Moves.Invoice = Invoices.ID) >> as "Sum Buy" >

[sqlite] Summing without duplicating

Hi All, I'm stumped on this one, though the answer is probably staring me in the face, so I hope someone can help. I have three tables (simplified here): Invoices, Orders and Moves. Each Invoice to a customer is for one or more products moved to them (ie sold to them). Each one of those

Re: [sqlite] simple? query problem

t 1 from A where A.ID = B.ID) ; Or you could use this: select ID from B except select ID from A ; Tom BareFeet ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Combined Select Statement

in table Y, with no null field/column values in either. However, if table Y only contains a matching record/row for some of the record/rows in table X, then separate tables is the correct way to go. It's a properly normalized database. Tom

Re: [sqlite] Combined Select Statement

elect statement that will > bring, > > X.Login,X.Name,Y.Internal > > as the result in one row. Assuming that the tables are related by the "Login" column/field, then you to join then, such as: select X.Login, X.Name, Y.Internal from X left join Y on X.Login = Y.Login Tom

Re: [sqlite] Mac file locking

ust that they don't know yet that they need it or can have it. In any case, it certainly doesn't mean that they want it to NOT be there (see 1). 3. All users have network share support built in with the SQLite that ships in Mac OS X and don't ask for it to be removed. T

Re: [sqlite] Mac file locking

ult SQLite source code settings). Of course, if you want to disable network share support for some reason in your own compile, you'd be free to override the flag. > Thanks, Tom BareFeet ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite

Re: [sqlite] Mac file locking

multiple code versions, just one version that sets the locking flag according to what platform it's on. ie if platform == 'Mac' then SQLITE_ENABLE_LOCKING_STYLE = 1 Surely it's that easy? No? Thanks, Tom BareFeet ___ sqlite-users mailing list sqlite

Re: [sqlite] Mac file locking

e the Zentus (or other) JDBC plug ins. Seems to be a simple fix with many rewards. Thanks, Tom BareFeet -- Compare SQLite GUI programs for Mac OS X: http://www.tandb.com.au/sqlite/compare/?ml ___ sqlite-users mailing list sqlite-users@sqlite.org http://sql

Re: [sqlite] Mac file locking

tation affects in my comparison table as "Can open database file on AppleShare volume" here: http://www.tandb.com.au/sqlite/compare/ Richard and co, please enable this flag by default. Thanks, Tom BareFeet ___ sqlite-users mailing l

Re: [sqlite] Daily Digest- Is this an Option?

> IS it possible to get on a daily digest list so that it is not > clogging my e-box with the individual e-mails? > I know of several other group that have that option > > Chris > > Design simplicity eliminates engineering complexity. Hi Chris, The design simplicity of this mail list

Re: [sqlite] Speeding up the simplest queries

an result, and stop scanning the table after the first match is found. Tom BareFeet ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQL Update while Stepping through Select results

s in its own row in a related table, rather than having multiple values in one row. As you're probably aware, this is a fundamental of database design. Otherwise you're unscrambling the egg, which is very error prone and inefficient. Tom BareFeet -- Comparison of SQLite GUI tools: http://

Re: [sqlite] Does sqlite support stored procedure?

ate a "Procedures" table like this: create table "Procedures" (Name, SQL); and populate it with SQL procedures. You can call those procedures later from within your program and sqlite3 command line and execute them. Tom BareFeet -- Comparison of SQLite GUI applications: http://

[sqlite] Regex parsing create statements

ot;. So, before I get too far into it, I figured others of you out there must already have some regexes that are suitable for this, or know of a simpler approach. Or at worst any regex gurus out there that can help fine tune the above? Any help appreciated. Thanks, Tom BareFeet

[sqlite] Fwd: SQLite bug on AFP?

Can the Mac OS X locking problem please be fixed in the standard build and source code? See below. Thanks, Tom BareFeet From: BareFeet <[EMAIL PROTECTED]> Date: 12 June 2008 10:50:40 AM To: General Discussion of SQLite Database <sqlite-users@sqlite.org> Subject: Re: [sqlite

Re: [sqlite] datetime bug

.4.0 or the latest binary version 3.5.9. FYI, this: select julianday('2008-06-12','utc'); gives: 2454629.0833 and this: select datetime(2454629.0833, 'localtime'); gives: 2008-06-11 24:00:00 Thanks, Tom BareFeet -- SQLite GUI tools compared a

[sqlite] datetime bug

.1 seconds', 'localtime') Thanks, Tom BareFeet -- SQLite GUI tools compared at: http://www.tandb.com.au/sqlite/compare/?ml ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite bug on AFP?

er (which affects at least a dozen SQLite GUI tools on the Mac) will only include the locking fix if the "standard" Mac OS binary also includes it. Thanks, Tom BareFeet -- SQLite GUI tools for Mac OS X compared at: http://www.tandb.com.au/sqlite/compare/

Re: [sqlite] Math Functions

I see that SQLite doesn't even support the "^" power of operator. Surely this is a fundamental requirement of any syntax that supports basic maths like addition, multiplication etc. Can/will the power operator be added to SQLite? Thanks, Tom BareFeet -- Comparison

Re: [sqlite] Very simple table...

Hi Scott, > I'm trying to decide whether LogDate should be in unixtime > format, or raw date format ('2008-01-01 13:12:11'). I use juliandate (real) to store the dates. See this web page for details: http://www.sqlite.org/cvstrac/wiki?p=DateAndTimeFunctions Tom BareFee

Re: [sqlite] deleting 100,000 entries

Hi Carlo, > I want to clear the whole thing out(list)! Do you mean that you want to delete all rows from the "list" table? If so, do this: delete from list; See the syntax pages at: http://www.sqlite.org/lang.html http://www.sqlite.org/lang_delete.html Tom BareFeet http://www

Re: [sqlite] Baffling SQLite statement

e years as either 1990 (numeric) or '1990' (text). Tom BareFeet http://www.tandb.com.au/sqlite/compare/?ml ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Help with syntax

er an owner_id column, so this should work: sqlite /var/local/database/dblist "insert into list (owner_id,behavior,entry) values(0,0,'newblacklistentry.com') " Tom BareFeet http://www.tandb.com.au/sqlite/compare/ ___ sqlite-users mailing list sqlite

Re: [sqlite] Baffling SQLite statement

. > Tom BareFeet http://www.tandb.com.au/sqlite/compare/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Help with syntax

blist "insert into list >> ('0,0,newblacklistentry1,com') values >> ('0,0,newblacklistentry1.com')" >> Tom BareFeet http://www.tandb.com.au/sqlite/compare/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Help with syntax

www.sqlite.org/lang_insert.html If you still need help, please post the schema (ie the create table statements) of your database and explain what you want inserted where. Tom BareFeet http://www.tandb.com.au/sqlite/compare/ ___ sqlite-users mailing list sqlite-us

Re: [sqlite] text datatype and referential integrity

established and things like full joins and other joins. Not directly, but you can also do this via triggers. I'd also like to know if support for foreign keys (defined in the table) is planned for SQLite down the track. Tom BareFeet ___ sqlite-users maili

Re: [sqlite] Comparison of SQLite applications for Mac

the unfortunately similarly named "SQLite Manager for Firefox". Thanks, Tom BareFeet ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Comparison of SQLite applications for Mac

ix, please let me know. Please let me know of any corrections to what's there or any stand out features in your favorite program that you think are worth comparing. Thanks, Tom BareFeet ___ sqlite-users mailing list sqlite-users@sqlite.org http:

Re: [sqlite] install on Mac

Hi Stephen, > I'm a recent (4 days) convert from XP to Mac OS Leopard. What a > great system Welcome aboard :-) > I've downloaded latest SQLite3 for Mac but it came as .bin not .dmg. Are you aware that SQLite is already installed in Mac OS X (SQLite version 3.4 in Leopard)? Tom

Re: [sqlite] Recommended SQLite utilities

c here: http://www.tandb.com.au/sqlite/compare/ A few of the applications there are cross platform. As you'll see in the comparison table, I am particularly interested in how specific features compare between the applications. Tom BareFeet ___ sqlite-u

Re: [sqlite] Delete inside Select+Step

ping through the results of a select and > know the remaining sqlite3_step()'s will work correctly? Is there any reason why you don't simply let SQL do the work for you: delete from TableA where Clma == somevalue; then process (the remaining) rows Tom BareFeet

Re: [sqlite] logging statements executed by the db

we'd really use a "before" or "after" here, rather than "instead of", so it could work on a table. Tom BareFeet http://www.tandb.com.au/sqlite/compare/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Comparison of SQLite applications for Mac

your results so I can add them. Are there any other similar comparisons around? http://www.tandb.com.au/sqlite/compare/?mlp Thanks, Tom BareFeet ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo

  1   2   >