[sqlite] Test failures on GPFS

2020-01-15 Thread T J
On Sunday, January 12, 2020, Roman Fleysher wrote: > > I use SQLite over GPFS , but in DELETE (which I think is the default) > mode. Not WAL mode. No issues with locking, except performance when > accessing concurrently from multiple nodes. As others pointed out, this has > to do with the

[sqlite] Test failures on GPFS

2020-01-11 Thread T J
Hi, I was interested in using sqlite over GPFS. I've seen a few useful threads on this: - Network file system that support sqlite3 well https://www.mail-archive.com/sqlite-users@mailinglists.sqlite.org/msg117085.html - disable file locking mechanism over the network

Re: [sqlite] Table was deleted on macOS

2019-10-15 Thread t...@qvgps.com
-- Originalnachricht -- Von: "Simon Slavin" mailto:slav...@bigfraud.org>> An: "SQLite mailing list" mailto:sqlite-users@mailinglists.sqlite.org>> Gesendet: 15.10.2019 23:24:17 Betreff: Re: [sqlite] Table was deleted on macOS On 15 Oct 2019, at

[sqlite] Table was deleted on macOS

2019-10-15 Thread t...@qvgps.com
81244 ** Windhoek, Namibia ** http://www.quovadis-gps.com ** mail t...@qvgps.com<mailto:t...@qvgps.com> ** +264 (0)81 3329923Nam mobile ** +49 (0)175 7313081 D mobile ** +49 (0)6182 8492599 D office ***/

[sqlite] WITHOUT ROWID

2019-09-08 Thread t...@qvgps.com
Box 81244 ** Windhoek, Namibia ** http://www.quovadis-gps.com ** mail t...@qvgps.com ** +264 (0)81 3329923Nam mobile ** +49 (0)175 7313081 D mobile ** +49 (0)6182 8492599 D office ***/ ___ sqlite

Re: [sqlite] Strategies to reduce page-loads?

2018-08-31 Thread t...@qvgps.com
Right. Was trying it out now, compiling some osm-dbs with primary key generated with this morton encoding from lat,lon and the performance is even worse. Debugging with the sqlite-tool shows, that the page counts for specific queries are almost double then before. Seems like, from the

Re: [sqlite] Strategies to reduce page-loads?

2018-08-31 Thread t...@qvgps.com
. At which point during insert are the pages actually written? -- Originalnachricht -- Von: "Richard Hipp" An: "SQLite mailing list" Gesendet: 31.08.2018 15:10:15 Betreff: Re: [sqlite] Strategies to reduce page-loads? >On 8/31/18, t...@qvgps.com wrote: >&

Re: [sqlite] Strategies to reduce page-loads?

2018-08-31 Thread t...@qvgps.com
> >(3) Consider deliberating choosing INTEGER PRIMARY KEY values based on >a "Morton code" or "Z-Order curve" of the coordinates. >(https://en.wikipedia.org/wiki/Z-order_curve) That will cause >features that are close together geographically to tend to be close >together within the file. My

Re: [sqlite] Strategies to reduce page-loads?

2018-08-30 Thread t...@qvgps.com
ently used? -- Originalnachricht -- Von: "Richard Hipp" An: "SQLite mailing list" Gesendet: 30.08.2018 13:48:30 Betreff: Re: [sqlite] Strategies to reduce page-loads? >On 8/30/18, t...@qvgps.com wrote: >> >>Structure is simple: >>CREAT

Re: [sqlite] Strategies to reduce page-loads?

2018-08-30 Thread t...@qvgps.com
now. Tom -- Originalnachricht -- Von: "Richard Hipp" An: "SQLite mailing list" Gesendet: 30.08.2018 13:48:30 Betreff: Re: [sqlite] Strategies to reduce page-loads? >On 8/30/18, t...@qvgps.com wrote: >> >>Structure is simple: >>CREATE TABLE Lines(

[sqlite] Strategies to reduce page-loads?

2018-08-30 Thread t...@qvgps.com
Thanks, Tom / ** Flemming Software Development CC ** Thomas Flemming ** PO Box 81244 ** Windhoek, Namibia ** http://www.quovadis-gps.com ** mail t...@qvgps.com ** +264 (0)81 3329923 Nam mobile ** +49 (0)175 7313081 D mobile ** +49 (0)6182 8492

[sqlite] When is db size an issue?

2017-09-27 Thread Jason T. Slack-Moehrle
Hello All, Off and on for the last few years I have been writing an e-mail client to scratch a personal itch. I store the mail in SQLite and attachments on the file system. However, I recently brought in all of my mail for the last 15 years from mbox format. Now, my database size is over 10gb.

[sqlite] When is db size an issue?

2017-09-27 Thread Jason T. Slack-Moehrle
Hello All, Off and on for the last few years I have been writing an e-mail client to scratch a personal itch. I store the mail in SQLite and attachments on the file system. However, I recently brought in all of my mail for the last 15 years from mbox format. Now, my database size is over 10gb.

[sqlite] Possible thread-safety bug in lemon parser with ParseTrace()

2017-09-07 Thread Fletcher T. Penney
). It is detected by Xcode's Thread Sanitizer, and I wanted to report it in case there was a more untoward effect that I was missing. If nothing needs to be done about it, that's fine too. Thanks! Fletcher -- Fletcher T. Penney fletc...@fletcherpenney.net

Re: [sqlite] confused getting started

2017-03-05 Thread NTrewartha T-Online
Nigel Trewartha Sonnenweg 3 33397 Rietberg Germany Tel: 05244/3631 Fax: 05244/9063266 ntrewar...@t-online.de ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Best way to temporarily store data before processing

2015-04-14 Thread Joseph T.
Mr. Moules, why not skip the raw tables entirely? Or failing that a separate in memory db would probably be the best and quickest option. Sent from my Samsung Epic? 4G TouchJonathan Moules wrote:Hi List, I'm wondering if anyone can offer me a "best practice" way of doing this. I'm doing

[sqlite] How to insert a pointer data to sqlite?

2015-02-25 Thread Joseph T.
Unless, I'm wrong. What you want to do is use two tables. One to store the node values and another that references them for whatever object using them. Say, points for a pair of triangles, a,b,c,d,e. If table triangle is a table pointing at the table point (id,object,point) you could have a

Re: [sqlite] sqlite bugreport : unique index causes valid updates to fail

2014-12-09 Thread J T
I just thought of what may be a simpler solution. I'm assuming that there is a certain limit to the length of the books (positions can be safely assumed to never exceed say, 100,000) So what can be done is update page set position=position + 10 where position>='3'; insert into

Re: [sqlite] sqlite bugreport : unique index causes valid updates to fail

2014-12-09 Thread J T
I just thought of what may be a simpler solution. I'm assuming that there is a certain limit to the length of the books (positions can be safely assumed to never exceed say, 100,000) So what can be done is update page set position=position + 10 where position>='3'; insert into

Re: [sqlite] sqlite bugreport : unique index causes valid updates to fail

2014-12-08 Thread J T
Cancel that, apparently that only updates the last record... -Original Message- From: John McKown To: General Discussion of SQLite Database Sent: Mon, Dec 8, 2014 9:18 am Subject: Re: [sqlite] sqlite bugreport : unique index

Re: [sqlite] sqlite bugreport : unique index causes valid updates to fail

2014-12-08 Thread J T
Might have another work around. update page set position=position + 1 where designation=(select designation from page where book='1' order by position desc) and then insert your page. Please see if that'll work. I tested it, but your results may differ. -Original Message-

Re: [sqlite] sqlite bugreport : unique index causes valid updates to fail

2014-12-08 Thread J T
eneral Discussion of SQLite Database <sqlite-users@sqlite.org> Sent: Mon, Dec 8, 2014 8:40 am Subject: Re: [sqlite] sqlite bugreport : unique index causes valid updates to fail J T, I did provide a sequence of queries that reliably reproduce the issue (see below, from the first CREATE to the

Re: [sqlite] sqlite bugreport : unique index causes valid updates to fail

2014-12-08 Thread J T
Try having your cascade occur before the row is created, updated or deleted. http://www.sqlite.org/lang_createtrigger.html -Original Message- From: Richard Hipp To: General Discussion of SQLite Database Sent: Mon, Dec 8, 2014 8:14 am

Re: [sqlite] How to Verify (know that they are correct) Queries in SQLite GUI Manager Firefox add-on?

2014-12-08 Thread J T
The questions you have to ask is Are the rows I'm returning identifiable by a unique id -- typically the row id, but also unique identifiers, like ISBN for books, Employee ID for employees, etc. If you find duplicates of what should be a unique id in a table then its probably a sign the data

Re: [sqlite] Search for text in all tables

2014-12-04 Thread J T
Dominique, Why not get a column count (datareader.fieldcount (C#) or cursor.getColumnCount() (Java/Android))? >From there you should be able to simply do a try { str = getString(columnIndex); checkValue(str); } catch(Exception e) { // wasn't a string or the check (and

Re: [sqlite] General R*Tree query

2013-12-18 Thread Brian T. Carcich
On Wed, Dec 18, 2013 at 1:53 AM, Roman Fleysher < roman.fleys...@einstein.yu.edu> wrote: > Perhaps this is a weird way for me to get deeper knowledge of R trees, and > because I vaguely remember that Tyco refers to a specific epoch in which > coordinates are defined, but would it be possible to

Re: [sqlite] General R*Tree query

2013-12-17 Thread Brian T. Carcich
On Tue, Dec 17, 2013 at 3:57 PM, Roman Fleysher < roman.fleys...@einstein.yu.edu> wrote: > > Since coordinate system is spherical, how do you tell that RA=23:59 and > RA=00:01 are next to each other using usual comparisons? I don't; usual comparisons won't work so I do two comparisons: I am

Re: [sqlite] General R*Tree query

2013-12-17 Thread Brian T. Carcich
On Tue, Dec 17, 2013 at 3:51 PM, Dan Kennedy <danielk1...@gmail.com> wrote: > On 12/18/2013 12:49 AM, Brian T. Carcich wrote: > >> [...] > > Points are fine. [...] > Is it working now? How many more stars do you have data for? Excellent, thanks for the info! I f

[sqlite] General R*Tree query

2013-12-17 Thread Brian T. Carcich
I'm working on an SQLite solution to get at star catalogs; they are usually searched via Right Ascension (RA), Declination (DEC), and magnitude (mag). RA,DEC is a spherical coordinate system to specify a star position on-sky; magnitude is related to star brightness. What I have so far is here:

Re: [sqlite] SQLite Provenance

2012-07-06 Thread Gavin T Watt
unknown key On 06/07/12 14:58, Gavin T Watt wrote: > ... the provenance of SQLite for security reasons. Where the server is is of little interest. It would be good if the team actually signed the release in some way then at least you would know it was what they released. What is of

Re: [sqlite] SQLite Provenance

2012-07-06 Thread Gavin T Watt
tabase <sqlite-users@sqlite.org> Date: 07/06/2012 11:01 AM Subject: Re: [sqlite] SQLite Provenance Sent by: sqlite-users-boun...@sqlite.org * PGP Signed by an unknown key On 06/07/12 08:29, Gavin T Watt wrote: > Can anyone tell me where (what country) the SQLite server(s) are

[sqlite] SQLite Provenance

2012-07-06 Thread Gavin T Watt
-BEGIN PGP SIGNED MESSAGE- Hash: SHA256 Can anyone tell me where (what country) the SQLite server(s) are located? Gavin Watt, CISSP Sr. Prin. Sys. Engr. Information Assurance Network Centric Systems (NCS) 714-446-3104 (office) 714-234-8869 (BB) 714-446-4136 (fax) gw...@raytheon.com

Re: [sqlite] Multiple connections to in-memory database

2012-07-05 Thread T Ü
Thank you so much Pavel. I will try with the new version. From: Pavel Ivanov <paiva...@gmail.com> To: T Ü <shocking_blue2...@yahoo.com> Cc: General Discussion of SQLite Database <sqlite-users@sqlite.org> Sent: Thursday, July 5, 2012 4

Re: [sqlite] Multiple connections to in-memory database

2012-07-05 Thread T Ü
It returns 3.6.23.1 From: Pavel Ivanov <paiva...@gmail.com> To: T Ü <shocking_blue2...@yahoo.com> Cc: General Discussion of SQLite Database <sqlite-users@sqlite.org> Sent: Thursday, July 5, 2012 3:40 PM Subject: Re: [sqlite] Multiple connec

Re: [sqlite] Multiple connections to in-memory database

2012-07-05 Thread T Ü
___ From: Pavel Ivanov <paiva...@gmail.com> To: T Ü <shocking_blue2...@yahoo.com>; General Discussion of SQLite Database <sqlite-users@sqlite.org> Sent: Thursday, July 5, 2012 3:21 PM Subject: Re: [sqlite] Multiple connections to in-memory database On Thu, Jul 5, 2012 at 7:4

[sqlite] Multiple connections to in-memory database

2012-07-05 Thread T Ü
I have an asp.net application. I open a sqlite in-memory connection with SQLiteConnection conn = new SQLiteConnection ( "Data Source=:memory:" ); command. I read that by using cache=shared parameter, I can make that in-memory database reachable from other connections. 1.What is the way of

[sqlite] How Execute Joint Queries In Sqlite?

2010-12-07 Thread Arunkumar T
I Writed A FULL OUTER JOIN Query In Sqlite But I Got A Message Like Not Supported. What Should I Do To Do This Can You Help Me? Regards Arunkumar ___ sqlite-users mailing list sqlite-users@sqlite.org

Re: [sqlite] ODBC Driver

2010-11-06 Thread christian.wer...@t-online.de
> I do not seem to be able to parse a multiple insert statement through > the odbc drive using SQLExecDirect(...) I have tried with BEGIN, COMMIT > TRANSACTION does anyone have any ideas? The SQLite ODBC driver allows only one SQL statement per SQLExecDirect() and SQLPrepare(). When using

Re: [sqlite] Deadlock with two local instances

2009-10-27 Thread Chris T
> hth > > Marcus Grimm > >> >> On Mon, Oct 26, 2009 at 2:40 PM, Chris T <citrus...@gmail.com> wrote: >>> I'm new to sqlite (and sql in general, actually) and came across >>> something puzzling. >>> >>> I wrote a test program statically li

Re: [sqlite] Deadlock with two local instances

2009-10-26 Thread Chris T
Another odd thing is that when I call sqlite3_reset on the prepared statement, it also returns SQLITE_BUSY. Should I only reset the statement when it has been executed successfully? On Mon, Oct 26, 2009 at 2:40 PM, Chris T <citrus...@gmail.com> wrote: > I'm new to sqlite (and sql i

[sqlite] Deadlock with two local instances

2009-10-26 Thread Chris T
I'm new to sqlite (and sql in general, actually) and came across something puzzling. I wrote a test program statically linked with the amalgamated sqlite code. When I run a single instance, everything is fine. When I start a second instance in the same directory they both deadlock. Every call

Re: [sqlite] DateTime comparison with custom format

2009-08-21 Thread t-master
Igor Tandetnik wrote: > > t-master <t-zimmerm...@onlinehome.de> wrote: >> I have string in a table representing a DateTime. >> The format is 21.08.2009 00:25:00 > > I recommend you change the format. Yours is custom-designed to make your > life miserable.

[sqlite] DateTime comparison with custom format

2009-08-21 Thread t-master
Hi I have string in a table representing a DateTime. The format is 21.08.2009 00:25:00 And I would like to compare it to "now" How can I do this? -- View this message in context: http://www.nabble.com/DateTime-comparison-with-custom-format-tp25085040p25085040.html Sent from the SQLite mailing

Re: [sqlite] SQLite Import Tool

2008-11-13 Thread Keith T
has any one expressed an interest to help out? it does not seem to be all that difficult, notwithstanding your mention of time-out issues. please let me know a bit ore detail about the db you are exporting, and we can see if it is a project we can do quickly for you. thanks- Keith T the SLK

Re: [sqlite] Value between changes

2008-03-14 Thread Bjørn T . Nøstdahl
> Can you give an example of the output? For example, do you want to > know the difference between A0001 and A0002 or between to records with > the same code? 20080314100030 A0001 (9min 30sec to next status change) 20080314101000 A0002 (7min to next status change) 20080314101700 A (3min to

[sqlite] flexible tables

2008-03-05 Thread r t
Dear SQLite list, Is there a way to automatically create tables based on imported text files of unknown field count and unknown data types? While I suspect this can be managed with a shell script interacting with SQLite, perhaps there is a more direct method? Zotlud

Re: [sqlite] Re: Error in SQLite's CSV output

2007-12-18 Thread T
I think I've discovered yet another bug in the CSV output using the sqlite3 command line tool. Here's a sample of the new bug: .mode csv select 'a=1,234', 'b=5'; gives: a=1,234,b=5 but should give: "a=1,234",b=5 Since no replies, I'll assume this is a bug. I've reported it as:

Re: [sqlite] Casting bug

2007-12-13 Thread T
Hi Mike, well, first of all you must have a typo, since the sql you show will return 14170, not 0.69 or anything like it. Yes, typo, sorry. The short version returns 14169 but should give 14170, as per my correction a minute ago. however, one thing springs out: Total * 100 + 100 is

Re: [sqlite] Casting bug

2007-12-13 Thread T
oops, sorry, slight correction: When I try: select cast( 141.70 * 100 as integer) I get 14169, but should get 14170 Tom - To unsubscribe, send email to [EMAIL PROTECTED]

[sqlite] Casting bug

2007-12-13 Thread T
When I try: select cast( 141.70 * 100 as integer) I get 0.69, but should get 0.70 What's the problem? Seems like a bug. I tried some other numbers in place of 141.70, and they worked OK, though I imagine there are others that have the bug that I just haven't tried. The above is the

Re: [sqlite] How to check if the table has some specific values

2007-12-12 Thread T
Hi Joanne, I have been working on MSSQL server and SQLite is new to me. I usually did the following in MSQL server to check of the specific row is existed in the table and have different action depending on the result of the check. For example: Create table versionTable (dbVersion

Re: [sqlite] Date functions

2007-12-02 Thread T
Hi Andreas, I like to save a date for each row in my database. Later I would select the rows with a query: SELECT * FROM Store_Information WHERE Date BETWEEN 'Jan-06-1999' AND 'Jan-10-1999' Is there a date data type in sqlite? I've not found it in the docs. Store dates in this format:

Re: [sqlite] Converting date from d/m/yy format

2007-11-02 Thread T
Hi Gerry, I would suggest either [scan] or [regexp] , with the former probably being easier. I'd love to use RegExp, but SQLite doesn't include it in its standard functions (though I wish it did for so many reasons). I'm not familiar with scan. Tom

Re: [sqlite] Converting date from d/m/yy format

2007-11-02 Thread T
Hi John, How can I convert dates from the format d/m/yy to SQL style YYY-MM- DD? The data is from a bank, so I have no control over its production. I couldn't find any suitable built in SQLite functions, which all seem to operate in the other direction. If you transform the date

[sqlite] Converting date from d/m/yy format

2007-11-01 Thread T
Hi all, How can I convert dates from the format d/m/yy to SQL style YYY-MM-DD? I have some imported data that includes a date column in the format d/ m/yy, where: d = day as 1 or two digits m = month as 1 or two digits yy = year as two digits eg: 2/11/07 = today 2/8/68 = 2nd of August,

Re: [sqlite] SQLite 3.5.* source code location?

2007-10-21 Thread T
base files on AppleShare volumes (which for some reason is still not enabled by default). Tom T - To unsubscribe, send email to [EMAIL PROTECTED] -

[sqlite] Formatting numbers

2007-09-20 Thread T
Hi All, What's the best way to format a number with a fixed number of decimal places? For instance, if I have an Amount that I want to format as dollars and cents, I'm currently using: begin immediate ; create temporary table Invoice( Amount real ) ; insert into Invoice values( 123.4 ) ;

Re: [sqlite] Sqlite insertion performance

2007-09-15 Thread Kefah T. Issa
sing SqliteJDBC 0.37 (the > >> > latest) on Sun JDK 1.6.0_02 on CentOS 5.0 4GB Ram, two duo core cpu > >> > server with raid10 sata-II harddisk. > >> > > >> > > >> >I know I might be stretching sqlite far

Re: [sqlite] Sqlite insertion performance

2007-09-14 Thread Kefah T. Issa
Thanks Kees, In fact using integer instead of string gives very similar results. Dropping the unique index constraint and then creating it again when needed sounds interesting, as insertion without the unique index is satisfactory and constact over time. I tried this, but got a trivial

[sqlite] Sqlite insertion performance

2007-09-14 Thread Kefah T. Issa
connection.commit(); insert.close(); connection.setAutoCommit(true); connection.createStatement().execute("PRAGMA synchronous = FULL;"); long iteration_time = (System.currentTimeMillis()-time); System.out.println(i+

Re: [sqlite] why a VIEW doesn't contain a ROWID

2007-09-03 Thread T
This I know, but the thing is, I want the ROWID in VIEW to be sequential even after a SELECT with some condition has been executed, ie., from 1 to n. Just like in normal table. In your case it is not like that. Ah, OK, I asked a similar question a week or two ago, and had to come up with my

Re: [sqlite] Enumerating rows in a view

2007-08-25 Thread T
Hi Darren, If that works, then try using a subquery in the view definition instead. create view Enumerated as select rowid as Sequence, Name from ( select Name from Planets order by Name ) Sort of like that. Thanks for the suggestion, but, unless I'm missing something, it doesn't

Re: [sqlite] Sparse matrix

2007-08-25 Thread T
Hi All, Simon answered: Here's a less gruesome version - no cases. I've given no thought to performance comparisons. Thanks for the two great solutions you posted. Upon further investigation, those solutions assume that we want all like occurrences together, effectively sorting records

Re: [sqlite] Enumerating rows in a view

2007-08-25 Thread T
Hi All, Again following up: When I create a table, SQLite enumerates the rows in the rowid column. When I create a view, is there any way to enumerate the output rows? Another method would I've developed/discovered is to create a temporary table, fill it with the data from the view, then

Re: [sqlite] Enumerating rows in a view

2007-08-25 Thread T
When I create a table, SQLite enumerates the rows in the rowid column. When I create a view, is there any way to enumerate the output rows? For example, say I have a table: create table Planets( Name text collate nocase ); insert into Planets values( 'Mercury' ); insert into Planets values(

[sqlite] Enumerating rows in a view

2007-08-24 Thread T
Hi all, When I create a table, SQLite enumerates the rows in the rowid column. When I create a view, is there any way to enumerate the output rows? For example, say I have a table: create table Planets( Name text collate nocase ); insert into Planets values( 'Mercury' ); insert into Planets

Re: [sqlite] Sparse matrix

2007-08-22 Thread T
Hi Darren, It seems to me that you have a flawed design. Displaying sparse like that should be a function of your application display code, not the database I had to chuckle that when I asked "How do I use this to do that", your solution was "you shouldn't have that and you should do it

Re: [sqlite] Sparse matrix

2007-08-22 Thread T
Hi Simon, Here's a less gruesome version - no cases. I've given no thought to performance comparisons. Thanks for the two great solutions you posted. They certainly achieve the desired result with the simplified sample I gave. However, in the broader reality, it doesn't quite satisfy my

[sqlite] Sparse matrix

2007-08-21 Thread T
Hi all, OK, this one has me stumped. I've been staring at it too long. I have a table with a column like this: Code a a a a b b c c c I want to output two columns, one showing the Code, another showing the count of each code, but with a null wherever it was a repeat, like this: Code

Re: [sqlite] Altering views, keeping triggers

2007-08-15 Thread T
Hi Joe, You've got the right idea. Just make use of sqlite_master.tbl_name. select sql || ';' from sqlite_master where type = 'trigger' and tbl_name = 'MyView'; sqlite_master.name is the name of the table/view/index/trigger itself, and sqlite_master.tbl_name is what it acts on. I

Re: [sqlite] PRAGMA writable_schema=ON;

2007-08-15 Thread T
Hi Joe, Even if you got the sqlite_master table entries right, the in- memory sqlite schema data structures would not be in sync. Yes, but my point/question was, would that not only apply to tables and indexes? I expect that views and triggers have no data structures (eg rootpage = 0) so

Re: [sqlite] PRAGMA writable_schema=ON;

2007-08-15 Thread T
. Let me clarify. What I'm asking is whether editing the view and trigger entries in sqlite_master is safer than editing table and index entries (see below). I'd greatly appreciate any educated insight, not necessarily a guarantee. :-) Thanks, Tom From: T <[EMAIL PROTECTED]> Da

[sqlite] PRAGMA writable_schema=ON;

2007-08-14 Thread T
[EMAIL PROTECTED] wrote: It appears that you can set PRAGMA writable_schema=ON; Then do a manual UPDATE of the sqlite_master table to insert I tried it and it seems to work. But it is dangerous. If you mess up, you corrupt the database file. Now that is interesting. I didn't realize

Re: [sqlite] Altering views, keeping triggers

2007-08-13 Thread T
Oops, sorry, that first paragraph should have read as below (I said "trigger" once where I meant "view"): Short version of question: I frequently want to alter view definitions, such as renaming them or changing the column definitions. The only way to do this is to drop the old view and

[sqlite] Altering views, keeping triggers

2007-08-13 Thread T
Hi all, Short version of question: I frequently want to alter view definitions, such as renaming them or changing the column definitions. The only way to do this is to drop the old trigger and create a new one (eg with the same name). The problem is that when I do this, any associated

Re: [sqlite] UNION?

2007-08-09 Thread T
Hi Lee, This query does not work in SQLite but works in MS SQL Server: sqlite> SELECT items_idx, [name], active FROM Items ...> WHERE active = 'T' AND Items.items_idx IN ...> ((SELECT related_item FROM RelatedItems WHERE item = 1777) ...> UNION ...> (SELECT item FR

Re: [sqlite] "database is locked" SQLITE_BUSY when db is on network drive...

2007-08-03 Thread T
And search the archives for "locked" in the subject. Tom From: T <[EMAIL PROTECTED]> Date: 11 May 2007 10:54:02 AM To: sqlite-users@sqlite.org Subject: Re: [sqlite] database is locked error Is there a timetable for including the fix for opening SQLite files on a shared

Re: [sqlite] strategy adding indexes

2007-07-31 Thread T
Hi Richard, It's great to see clarity on this "from the horse's mouth" (I hope that translates across the equator). Thank you. - indexes that include all possible combinations of fields that may appear in a WHERE clause. As an aside, note that, AFAIK, indexes are only used: 1. To get

Re: [sqlite] strategy adding indexes

2007-07-30 Thread T
Hi RBS, - indexes that include all possible combinations of fields that may appear in a WHERE clause. As an aside, note that, AFAIK, indexes are only used: 1. To get the first match of a query. If you ask for more than one matching record, the second, third etc matches are found by

[sqlite] last_row_id() after insert via trigger

2007-07-29 Thread T
Hi all, Short question: When I explicitly insert a row into a table, I am able to use the last_insert_rowid() function to get the rowid of that inserted row. But how do I get the rowid of a row inserted by a trigger? It seems that last_insert_rowid() doesn't change. Longer example: For

[sqlite] Enclosing quotes

2007-07-21 Thread T
Hi All, When I rename a table, SQLite seems to override the quote characters I use, instead using single quote marks. Is this a bug? I prefer to use double quotes or square brackets for entity and column names (aka "user-defined objects"), obeying the SQLite documentation, but SQLite

[sqlite] Updatable views

2007-07-20 Thread T
Hi All, I'm using triggers to make my views updatable (ie the user can edit the values view because SQLite will instead change the related source table value). Which method is best to facilitate this?: 1. Use the "instead of update on ViewName" syntax. Trigger on the update of the view

Re: [sqlite] SQLite on Mac

2007-07-20 Thread T
Hi Ahmed, Does SQLite work on Mac, and if yes, is there any Mac enabled version that I could download? As others have mentioned, yes, SQLite not only runs on a Mac, but it's already installed as of Mac OS X 10.4 "Tiger" and after. Apple uses it for indexing email in the Mail application,

Re: [sqlite] Re: Oddball query required

2007-06-30 Thread Lucas (a.k.a T-Bird or bsdfan3)
Thank you very much Igor. This will do exactly what I need. Igor Tandetnik wrote: Lucas (a.k.a T-Bird or bsdfan3) <[EMAIL PROTECTED]> wrote: I am using SQLite in an application within a game where I have a table of player nicknames and cumulative player scores (among other things). Ho

[sqlite] Oddball query required

2007-06-30 Thread Lucas (a.k.a T-Bird or bsdfan3)
I am using SQLite in an application within a game where I have a table of player nicknames and cumulative player scores (among other things). How do I figure out at what (numeric) one of these records would be at if the table were sorted, say, by the cumulative scores column? Or should I

[sqlite] Capturing data at a point in time

2007-06-18 Thread T
Subject was: [sqlite] Trigger update of multiple columns I've change the subject since this thread is discussing a tangent to my original query. Hi Fred, Therefore, the data items contained in each row of this table should be a permanent reflection of the master tables' data content at an

[sqlite] Update of multiple columns

2007-06-18 Thread T
In case the original question has been clouded by discussions of running a general store and the arson habits of the Hand Crafted Guild, allow me to crystalize my purely SQLite question: I know I can update via: update Table1 set c1 = (select d1 from Table2 where Table2.id =

Re: [sqlite] Trigger update of multiple columns

2007-06-18 Thread T
Hi Ed, I tried to update a list of columns: UPDATE t SET (c1, c2, c3) = (SELECT c1, c2, c3) FROM t2 WHERE .. but this syntax is not accepted as you probably already know. Thanks for your very thoughtful reply. That is exactly the type of syntax I'm after, with only one executed WHERE clause

Re: [sqlite] Trigger update of multiple columns

2007-06-18 Thread T
Hi Gerry, keep each version of each product's description in the products table, along with the date that description became valid. Then the product ID and date in each transaction would reference the appropriate product table data. In certain circumstances, I can see how that would be

Re: [sqlite] Trigger update of multiple columns

2007-06-18 Thread T
Hi John, You have a reference data set which is accessed to get the current value of reference elements and store transactions to record events. The transaction trails provide event history. Yes, agreed. A price is in the reference data, its value transferred to a transaction is no

Re: [sqlite] Trigger update of multiple columns

2007-06-18 Thread T
Hi John, A general rule of database design is to seperate reference and transactional data. Then you can have a normalized database in a dynamic environment. Yes, I think that's what I am designing. The reference data is the products table (and potentially customer table etc) The

Re: [sqlite] Trigger update of multiple columns

2007-06-18 Thread T
Hi Puneet, ok, your problem makes a bit more sense Great :-) but still, much in it doesn't make sense. OK, I'll see if I can clarify further. even if the products table is changing, the buy, sell, and description of a given item shouldn't change. The buy and sell price of products

Re: [sqlite] Trigger update of multiple columns

2007-06-18 Thread T
Hi Puneet and John, You each respectively said: Why are you repeating the Code, Buy, Sell, and Description columns in the Sale_Products table when they already exists in the Products table? A traditional goal in database design is to place data in "Third Normal Form" which means in

Re: [sqlite] Trigger update of multiple columns

2007-06-17 Thread T
Hi John, Thanks for your reply. You would have a better database if you normalize it and not do what you propose. Meaning what, exactly? Obviously, I've only given rough indications as to my data source, such as: the huge Products table (which is actually a UNION ALL of various

[sqlite] Trigger update of multiple columns

2007-06-17 Thread T
Hi All, I have a pretty standard sales tracking database consisting of tables: Products - Each row is a product available for sale. Includes fields: Code, Buy, Sell, Description Sales - Each row is a sale made to a customer. Includes fields: Ref,

Re: [sqlite] Why do you use SQLite? Comments for an article needed

2007-06-09 Thread Lucas (a.k.a T-Bird or bsdfan3)
I personally like it for embedded (in other applications) jobs because of its small code footprint compared to a RDBMS. Tim Anderson wrote: Many thanks to those who have commented (more are welcome of course; though I won't be able to use all of them). I'll post a link to the piece when it

[sqlite] Regexp

2007-05-10 Thread T
Hi, I noticed in recent versions of SQLite the addition of some functions such as Trim(). So I am hopeful that at least one more function, Regexp(), can be included since it is such a central and common need. Time and time again I come up with needing a Regexp function, and I notice that

Re: [sqlite] database is locked error

2007-05-10 Thread T
Is there a timetable for including the fix for opening SQLite files on a shared volume? Since it's fairly trivial, is there a reason why it hasn't been included yet? Thanks, Tom From: T <[EMAIL PROTECTED]> Date: 29 April 2007 3:35:00 AM To: sqlite-users@sqlite.org Subject: Re: [

Re: [sqlite] Problem with SQLite Pecl extension for PHP4

2007-05-10 Thread T
Hi Brandon, Is there a way to upgrade the module to SQLite 3 on PHP4? I think so. I recall reading something about it, but since I was able to just install PHP5 I went via that route. Try Google: http://www.google.com.au/search?q=SQLite3+PHP4 Tom

Re: [sqlite] Longest "real" SQL statement

2007-05-10 Thread T
Hi Richard, I tend to use a series of CREATE VIEW AS SELECT type statements to effectively nest my SELECT statements, making them more humanly editable and manageable. Consequently, the size of the maximum sized single SQL statement is reduced, though the total SQL statement effectively

  1   2   >