[sqlite] Quoting identifier vs literal (was: Version 3.2.2)

le'( MyField ) SQLite should instead use the quotes (if any) used in the alter table command. Tom BareFeet ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Updatable views

= new.ID ; end ; So, is this the best way to tackle the objective of having updatable views? Or is there a better way? Are there any bugs in my approach? Any feedback appreciated. Thanks, Tom BareFeet ___ sqlite-users mailing list sqlite

Re: [sqlite] replacing all newlines in a field

and starts scrambling the egg. Tom BareFeet ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Quoting identifiers (was: Updatable views)

ll get proper error messages if they make a typographical > error. Sounds great :-) I'd be happy to use double quotes for identifiers if I could be sure that SQLite would give me an error if that identifier doesn't exist. Thanks, Tom BareFeet -- Best value broadband in Australia. http://ww

Re: [sqlite] Updatable views

l > delete from [Orders Refunds Amount] > where new.Amount is null > and [Orders Refunds Amount].ID = new.ID > ; > -- update if row exists and new value not null > update [Orders Refunds Amount] > set Amount = new.Amount >

Re: [sqlite] Updatable views

nis has suggested mechanisms ;-) Thanks for your efforts. Thanks, Tom BareFeet ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Updatable views

Hi Dennis, Thanks for your reply. I really appreciate the feedback. > This is a very nice set of triggers to handle the base tables of a > view > I believe this is the best way to handle this. Thanks, it's good to at least know that I'm heading the right way. I believe this is the best way

Re: [sqlite] Updatable views

your input. I'll hone my updatable views strategy and apply it to more cases to find any tangent situations. Tom BareFeet -- 5000 computer accessories delivered anywhere in Australia: http://www.tandb.com.au/forsale/?sig ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Selecting all and some columns

. Notice there are no case statements to get what you want. You just start with the table you want (Split, in this case) and join any needed related data, so SQLite only scans the relevant data, rather than testing every row. Tom BareFeet ___ sqlite-users

Re: [sqlite] Quoting identifiers (was: Updatable views)

error messages) seems to be for the sake of complying with a "non-standard" in MySQL. ;-) In other words, if catering for non-standards makes it worse, don't do it :-) Tom BareFeet -- ADSL2+ at the cheapest price in Australia: http://www.tandb.com.a

Re: [sqlite] Empty all rows from table

Hi John, > DELETE FROM worktable That is correct. You need a trailing semicolon though, on any command, ie: delete from workTable; > But that just throws error messages. What error messages? Tom BareFeet ___ sqlite-users mailing list

Re: [sqlite] Selecting all and some columns

.FundID = FundPM.FundID ; I think this has reached the point where it's probably useful for you to explain the bigger picture of your data and what you're trying to do with it. Tom BareFeet -- ADSL2+ at the cheapest price in Australia: http://www.tandb.com.au/broadband/?ml _

Re: [sqlite] Selecting all and some columns

r of any book on SQL programming, you'll see that normalizing your database is the fundamental first step in achieving the nest results with minimum wastage. Tom BareFeet -- 5000 computer accessories delivered anywhere in Australia: http://www.tandb.com.au/forsale/?ml ___

Re: [sqlite] creating a table with an index

h is what you want if you'll be searching by fldoid and imgoid combinations. If you instead want two individual indexes, for searching on just one column at a time, then you need to create two, as: create index "favorites fldoid" (fldoid); create index "favorites imgoid" (img

Re: [sqlite] Optimizing Query

to your "problem" to be a simple query on one table, joined to any others from which you require data, without needing nine where parameters. Can you please post the schema of your database? along with some sample data? Tom BareFeet -- ADSL2 - probably a hundred times faster

Re: [sqlite] PRIMARY KEY? Date and time datatypes?

time: select date(myJulianDate, 'localtime') either by creating a view in SQLite (and having PHP reference it) or else using directly from PHP. See: http://www.sqlite.org/cvstrac/wiki?p=DateAndTimeFunctions Tom BareFeet -- Huge range of Mac and other computer accessories, in Australia h

Re: [sqlite] SQLite Like Query Optimization

he contents into more columns, which you could then search using "=" instead of "like" and so use indexes. Tom BareFeet -- One stop Australian on-line shop for Macs and accessories http://www.tandb.com.au/forsale/?ml ___ sqlit

Re: [sqlite] PRIMARY KEY? Date and time datatypes?

e( Birth, 'localtime' ) as Birth from MyTable ; And from PHP, or wherever, I can select from the view, just like I would a table: select Birth from MyView; which gives me the original: 2008-02-18 23:31:00 since I've in the same time zone as where the date was entered. You can use just pla

Re: [sqlite] Finding table names

too sure how to do this. select Name, SQL from SQLite_Master where type = 'table'; Tom BareFeet ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Optimizing an insert/update

ct InputString, count(*) from InputTable group by InputString ; which gives: Donald 1 Goofy1 Mickey 2 Minnie 1 Tom BareFeet -- Cheapest ADSL1 and ADSL2 in Australia: http://www.tandb.com.au/broadband/?ml ___ sqlite-users mailing list sqlite-u

Re: [sqlite] IF...THEN constructs

ch achieves more natural SQL syntax and faster results. I've had to interpret your purpose, so forgive me if I've missed something here. I hope this helps, Tom BareFeet -- Widest range of Macs and accessories in Australia http://www.tandb.com.au/forsale/?ml

Re: [sqlite] Odd problem with select on large table

s not standard SQL and causes a host of problems. I'm hoping it will be removed from SQLite. Tom BareFeet ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Can't get concat operator to work

s since that's the part that does seem to be working. If it's giving you a blank then it's your function, not the concat operator. What do your custom functions do? Have you looked at the date and time functions built into SQLite? I expect that they'll cater for what

Re: [sqlite] SQL Newbie problem I guess...

Collection\ 3 2cd1\ 4 2cd2\ But the query to recursively delete would be more difficult. Tom BareFeet ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Complex Query

Hi Derek, > .m col > .h on > .w 20 17 6 23 6 > .e on They are just the abbreviated version of these dot commands: .mode columns .headers on .width 20 17 6 23 6 .echo on You can get info on each by typing ".help" from within the sqlite3 command line

Re: [sqlite] Go to specific row in database

you do need the row number, usually when interfacing with some other programming environment (and as above this can usually be avoided by doing more in SQL first). To get the row number, you just: select rowid from MyTable where Condition; Tom BareFeet

Re: [sqlite] SQLITE QUERY (LIKE)

w.sqlite.org/lang_expr.html Tom BareFeet -- Best prices on ADSL1 & ADSL2 in Australia http://www.tandb.com.au/broadband/?ml ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Inserting data containing apostrope

s ('AC','O''Hara','0413000153','Seafort ') Tom BareFeet ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Dealing with monetary huge values in sqlite

tegers, show sometimes as decimals) for a large product list. Tom BareFeet ___ 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

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

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] 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] 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] 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] 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] 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] 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] 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] 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

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

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] 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] 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] 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] 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/

[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] 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] 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

[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

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://

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] 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] 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] 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] 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

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

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

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] 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] 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] 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

[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] 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" >

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

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] 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

[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

[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

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

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

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

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

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] 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] 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] 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] 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] 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] 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] 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

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] 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] 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?)

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] 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] 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] 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] 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

[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] 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

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] 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] 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] 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] 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

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] [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

[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] 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

  1   2   >