Re: [sqlite] Someone knows about an ETL tool in foreign languages?

On 24/09/2009 12:02 AM, hfdabler wrote: > > Hello to all, > > Being in a pretty much international company, I have come here to ask a few > things about ETL tools and their different languages. Why? The principal focus of this mailing list is SQLite and its C APIs, not ETL. > > We have

Re: [sqlite] Tedious CSV import question

On 26/09/2009 5:38 AM, Petite Abeille wrote: > On Sep 25, 2009, at 9:21 PM, C. Mundi wrote: > >> Your post neatly articulates virtually every facet of this issue. >> Thank you. I wish we could get everyone to stop using csv. I hate to >> look at xml but I often wish everyone would use it

Re: [sqlite] Tedious CSV import question

On 26/09/2009 5:03 AM, Wilson, Ronald wrote: > > > Yeah. The clearest thing in the RFC is the ABNF grammar. However, even > that leaves out common cases like white space outside of quoted fields, > which most people would expect to be trimmed. Also, I think most people > would expect

Re: [sqlite] How to speed up a query between two tables?

On 15/09/2009 7:25 PM, Kermit Mei wrote: > On Tue, 2009-09-15 at 17:37 +1000, John Machin wrote: >> On 15/09/2009 4:47 PM, Kermit Mei wrote: >>> >>> sqlite> SELECT HomeDev.text, ZPhDev.id >>>...> FROM ZPhDev >>>...> INNER JOIN HomeDe

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

On 26/08/2009 5:47 AM, Matt Stiles wrote: > Am I losing my mind, or is there something wrong with the bin.gz file on the > download page? I've downloaded it several times, but I can't get it to open > completely using Stuffit or the Mac archive utility. It appears to open, but > it just spits out

Re: [sqlite] Using SQLite for GIS Application

On 23/08/2009 3:08 PM, Itzchak Raiskin wrote: > Hi > I want to use SQLite in a GIS application where I create a database > containing terrain data (coordinates, height). > I would like to query this database with start and end points of a line and > get a vector with all heights point along this

Re: [sqlite] suggestion on the database design

On 21/08/2009 1:29 PM, pierr wrote: > > Simon Slavin-2 wrote: >> >> On 21 Aug 2009, at 3:26am, pierr wrote: >> >>> I did not know the sequence in defining the field matters. This is >>> what I should have done. >> Sorry, I should have explained better. You were right: there is no >>

Re: [sqlite] suggestion on the database design

On 21/08/2009 12:59 PM, Simon Slavin wrote: > On 21 Aug 2009, at 3:26am, pierr wrote: > >> I did not know the sequence in defining the field matters. This is >> what I should have done. > > Sorry, I should have explained better. You were right: there is no > difference. I was just

Re: [sqlite] How to select data from 2 lines in one line?

On 20/08/2009 12:57 AM, Kit wrote: > Right form (tested): > > SELECT petr4.data AS data,petr4.preult AS preult,temp.data AS > previous_data,temp.preult AS previous_preult >FROM petr4,petr4 AS temp >WHERE petr4.rowid=temp.rowid+1; Don't you think that relying on (a) rowid being

Re: [sqlite] How to select data from 2 lines in one line?

On 20/08/2009 12:10 AM, Mário Anselmo Scandelari Bussmann wrote: > I have a table like this: > > petr4 > --- > rowid|data|preabe|premax|premin|preult|voltot > 1|2007-01-02|50.0|50.45|49.76|50.45|256115409.0 > 2|2007-01-03|50.16|50.4|48.01|48.7|492591256.0 [snip] >

Re: [sqlite] use of parentheses when not arithmetically or logically required

On 19/08/2009 11:26 AM, Simon Slavin wrote: > DRH's post trumps mine, of course. I'm surprised to find that > brackets are optimised out of WHERE evaluations. Why? In the OP's example (all AND operators) the parentheses are redundant. In SQL, AND and OR are not guaranteed to be

Re: [sqlite] INSERT with multiple VALUES clause?

On 18/08/2009 11:28 PM, Beau Wilkinson wrote: > That said, if you're in posession of the source code, > you can certainly hack something up to support that. > A better option might be to pre-process the MySQL file > using C, Perl, XSLT (just kidding - don't use XSLT) > or whatever else you

Re: [sqlite] Multiple indexes in SQLite, and selecting which to use

On 17/08/2009 2:37 PM, Dan Kennedy wrote: > On Aug 17, 2009, at 11:05 AM, John Machin wrote: > >> On 17/08/2009 11:41 AM, Shane Harrelson wrote: >>> INDEXED BY doesn't allow you to specify which index to use. It >>> just causes >>> the query to fail

Re: [sqlite] Multiple indexes in SQLite, and selecting which to use

On 17/08/2009 11:41 AM, Shane Harrelson wrote: > INDEXED BY doesn't allow you to specify which index to use. It just causes > the query to fail if SQLite thinks it should use an index different then the > one specified by the INDEXED BY clause. Oh. The docs say "If index-name does not exist or

Re: [sqlite] Question regarding SQLite btree structure

On 16/08/2009 6:34 PM, deddy wahyudi wrote: > I am currently on a research project about SQLite btree data structure and I > have a simple question here. > > I need to retrieve in which offset SQLite keeps my record, for example : > > lets say I have 100 records kept in a table named

Re: [sqlite] Multiple indexes in SQLite, and selecting which to use

On 15/08/2009 4:48 PM, Jim Showalter wrote: > It doesn't collect those statistics automatically, as part of query > plan optimization? You may like to consider looking at "6.0 Choosing between multiple indices" in http://www.sqlite.org/optoverview.html HTH, John

Re: [sqlite] translating CSV file into sqlite3 database for iPhone?

On 8/08/2009 2:02 AM, Simon Slavin wrote: > On 7 Aug 2009, at 4:21am, aerende wrote: > >>sqlite> .import myfile.csv mydatabasetable >>sqlite> .output mydatabasetable.sql > > When you look at the .sql file in a text editor, does it make sense ? > Does it look like legal SQL ? Does it

Re: [sqlite] translating CSV file into sqlite3 database for iPhone?

On 7/08/2009 1:21 PM, aerende wrote: > I'm trying to take a CSV file and create a sqlite3 database for the iPhone. > The CSV file has 33K entries and is 2 MB. The problem I am having is that > only about 1/10 of the database file gets written into the sqlite3 database. > > I first translated

Re: [sqlite] sqlite database "signature" ?

On 7/08/2009 2:36 AM, luc.moulinier wrote: > I'd like to know what is the best way to know if a file > is a sqlite DB or not (without launching sqlite of course) ? > For example, is the first line of the file unambiguously > a signature of sqlite ? If so, what is its structure ?

Re: [sqlite] FTS and postfix search

On 6/08/2009 12:07 PM, Jim Showalter wrote: > Sorry--I read my emails arrival order, not reverse chronological--so I > didn't see that John had already solved it. Not me ... this is ancient lore e.g. Knuth vol 3 of TAOCP 1973 edition page 391 "If we make two copies of the file, one in which the

Re: [sqlite] FTS and postfix search

On 6/08/2009 11:16 AM, Lukas Haase wrote: > Wes Freeman schrieb: > >> Strange that it's implemented for prefix and not postfix? > > Well, an explanation is easy: Same as with LIKE, LIKE 'xxx' or LIKE > 'xxx%' can be performed easy because only the beginning of words need to > be compared. >

Re: [sqlite] Date Cutoff Statement

On 4/08/2009 8:52 AM, Rick Ratchford wrote: > > What I want to do is modify this SELECT statement so that the rows returned > do not go past a certain date. Let's call it dStopDate. > > If I have dStopDate = '2009-28-07' Did you mean '2009-07-28' ? > for example, then the last row I want to

Re: [sqlite] Subtotal SQL

On 29/07/2009 11:34 PM, Adler, Eliedaat wrote: > SQL/sqlite challenge for all: It would be helpful if you made it plain whether you are asking a trick question, or are a novice with a perceived problem (and whether the management is insisting that you absolutely must have an SQL-only solution

Re: [sqlite] Little Help on SQL

On 27/07/2009 12:16 PM, Rick Ratchford wrote: > It's a seasonal map, so every year must overlay onto a 366 day grid. > > The table that contains the data has assigned each day a day number from 1 > to 366. If the year isn't a leap year, then day 60 will simply not be > registered for that year.

Re: [sqlite] Little Help on SQL

On 27/07/2009 7:40 AM, Rick Ratchford wrote: > I have a TABLE with a column of Day Numbers (1 to 366) called DayNum. > Let's say that you want get a count of each DayNum. > How do I word my statement so that it gives me a count of each DayNum, which > is from 1 to 366? Consider leap years ...

Re: [sqlite] Reporting on summary data

On 25/07/2009 11:59 AM, David Bicking wrote: > On Sat, 2009-07-25 at 10:33 +1000, John Machin wrote: >> An accounting system where the sign of the amount is detached and has to >> be obtained from another column is tedious and error-prone; obtaining it >> from TWO co

Re: [sqlite] Reporting on summary data

On 25/07/2009 6:17 AM, David Bicking wrote: > That works. Thanks! It struck me that Pavel's revised query didn't mention the d2 column at all, only d1: >> sum(case when d1='X' then 1 else -1 end) as act_sum, >> sum(case when d1='X' then amt else -amt end) as net ... backtracking, it seems that

Re: [sqlite] An index question

On 25/07/2009 2:14 AM, Jon Dixon wrote: > In the description of the "Create Index" statement, it says: > > "Every time the database is opened, > all CREATE INDEX statements > are read from the sqlite_master table and used to regenerate > SQLite's internal representation of the index layout." > >

Re: [sqlite] Defining a table that starts rowid as 0

On 24/07/2009 3:10 PM, Simon Slavin wrote: > On 24 Jul 2009, at 5:49am, John Machin wrote: > >> On 24/07/2009 3:22 AM, Simon Slavin wrote: >> >>> And note that if you have a column which is an integer that >>> has doesn't allow duplicates, SQLite will automati

Re: [sqlite] Defining a table that starts rowid as 0

On 24/07/2009 3:22 AM, Simon Slavin wrote: > And note that if you have a column which is an integer that > has doesn't allow duplicates, SQLite will automatically use that > column as the one it uses for _rowid_, etc.. So define your own > integer column, feed it whatever integers you

Re: [sqlite] Installing SQLite

On 23/07/2009 6:48 AM, Rich Shepard wrote: > On Wed, 22 Jul 2009, scabral wrote: > >> When i download the sqlite-amalgamation-3_6_16.zip i get 3 text files: >> >> sqlite3 C File >> sqlite3 H File >> sqlite3ext H File > >> what am i supposed to do with those? > > Well, based on what others

Re: [sqlite] How do bitwise operators work?

On 20/07/2009 11:05 PM, Le Hyaric Bruno wrote: > Hi, > > I'm making some testing with sqlite3. > I need to know how bitwise operator work? with which type? > Is that possible to use these operators on blob of thousands of bits? > > To give an idea of the context, I need to store a lot of data

Re: [sqlite] The SQL Guide to SQLite

On 20/07/2009 12:08 AM, P Kishor wrote: > > unfortunately, we get either advertisements nowadays > > or a signature twice the length of the message warning us that the > contents of the particular email are confidential and meant only for > the recipient, and if I am not the recipient then I

Re: [sqlite] SQLite Delete doesn't work

On 19/07/2009 8:20 PM, Diana Chinces wrote: > Hi. > I am having some kind of issues with the delete command when my WHERE expr > is formed from several expression. On the surface, what you say you did should have worked. Hence a whole bag of questions: What version of SQLite? Running on what

Re: [sqlite] storing and retrieving integers with value 0 (not null)

ames, it said (x, y, x, >> etc..), and somewhere this created the null.. > > Congratulations and well spotted. We've all done it. > > > On 16 Jul 2009, at 3:36pm, John Machin wrote: > >> This sounds like a bug somewhere -- having a column name twice >> should be

Re: [sqlite] storing and retrieving integers with value 0 (not null)

On 17/07/2009 12:54 AM, Uijtdewilligen, Freek wrote: > >> This sounds like a bug somewhere -- having a column name twice should >> be >> met with an error message, not with setting the integer column to > NULL. >> So please give us some more information: >> >> In the String where it [what is

Re: [sqlite] storing and retrieving integers with value 0 (not null)

On 16/07/2009 11:35 PM, Uijtdewilligen, Freek wrote: > Okay, way too much time after discovering the problem, I found the > cause: a simple typo :) > > In the String where it was storing the column-names, it said (x, y, x, > etc..), and somewhere this created the null.. This sounds like a bug

Re: [sqlite] storing and retrieving integers with value 0 (not null)

On 16/07/2009 7:24 PM, Uijtdewilligen, Freek wrote: > INSERT INTO t_rp (x, y, z) > VALUES (1, 1, 0); > it gets stored as (1,1,null). What evidence do you have to support your assertion that it is stored as NULL? As Simon has pointed out, 0 != '0'. If after considering that, you feel you

Re: [sqlite] range enclosing a number

On 14/07/2009 11:44 AM, Jay A. Kreibich wrote: > On Mon, Jul 13, 2009 at 07:40:48PM -0400, Wes Freeman scratched on the wall: >> Yeah, sorry about that. In two statements: >> >> select max(number) from table where number < ? >> select min(number) from table where number > ? > > I'm pretty sure

Re: [sqlite] string is converted into a number

On 14/07/2009 3:04 AM, Simon Slavin wrote: > On 13 Jul 2009, at 4:35pm, Wilfried Mestdagh wrote: > >> But the circumstances are not really described (possible I cannot read >> between the lines as my English is not perfect). So as far as I >> understand >> the page if I want to store / retrieve

Re: [sqlite] Number truncation problem in SQLite 3

On 13/07/2009 8:40 AM, Roger Binns wrote: > -BEGIN PGP SIGNED MESSAGE- > Hash: SHA1 > > Jim Showalter wrote: >> create table words (_id integer primary key autoincrement, wordtext >> text not null unique, timestamp integer not null); >> >> public class Word >> { >> long _id; >>

Re: [sqlite] How-to change column limit?

On 12/07/2009 10:23 PM, Stephan Lindner wrote: > > I'm importing large survey files into sqlite, and I run into the > problem of creating a table with too many columns, i.e. How many columns do you have? > bash$ sqlite3 < tables.sql > > produces > > bash$ SQL error near line 3: too many

Re: [sqlite] SQL Syntax

On 9/07/2009 2:21 PM, Rick Ratchford wrote: > > Okay, this worked, but I have NO IDEA why. > > SQLString = "SELECT min(Year) FROM TmpTable " & _ > "WHERE Month=1 UNION " & _ > "SELECT max(Year) FROM TmpTable " & _ > "WHERE Month = 12 LIMIT 2"

Re: [sqlite] Getting Complete Years Only

-Original Message- > From: sqlite-users-boun...@sqlite.org > [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Igor Tandetnik > Sent: Wednesday, July 08, 2009 6:17 PM > To: sqlite-users@sqlite.org > Subject: Re: [sqlite] Getting Complete Years Only > > John Machin <sjmac...@le

Re: [sqlite] Getting Complete Years Only

On 9/07/2009 3:39 AM, Igor Tandetnik wrote: > Rick Ratchford > wrote: >> Can someone help me with a SQL request? >> >> The Table contains Date, as well as Year, Month and Day columns. >> >> I would like to return a recordset that is made up of only COMPLETE >> YEARS,

Re: [sqlite] Performance issue in using 'LIKE' with Indexes.

On 8/07/2009 7:11 PM, aalap shah wrote: > Hi, > > I am a new user to sqlite3, I have a program that searches through a > database. I have a table with 1 column as varchar and I want to > perform a search on it. > I have created an index over that column. And I use a select query > with

Re: [sqlite] Query by Day

On 8/07/2009 2:14 AM, Rick Ratchford wrote: [snip] > To John Machin: To save from answering multiple messages (and save space for > all), I'll address John's reply here. > > -- > "Consider getting answers

Re: [sqlite] Query by Day

On 7/07/2009 10:13 AM, Rick Ratchford wrote: > Hi Simon. > > Ah. So what I need to do then is to make the return of strftime of type INT. > > Since I'm creating a recordset from an existing table (rather than creating > a table itself), then I don't have the option to set the affinity of my >

Re: [sqlite] How to refer to a multiple-column primary key (PK) as 1 column (field) name

On 5/07/2009 5:49 AM, James Scott wrote: > I have the following: > > CREATE TABLE [Sections] ( > [Department] varchar NOT NULL COLLATE NOCASE, > [Course] varchar NOT NULL COLLATE NOCASE, > [Section] varchar NOT NULL COLLATE NOCASE, > [Class_Time] timestamp, > [I_Id] varchar COLLATE

Re: [sqlite] SQL Query Question

On 4/07/2009 9:01 AM, Simon Slavin wrote: > On 3 Jul 2009, at 10:03pm, Rick Ratchford wrote: > >> Suppose my 15 Dates are: >> >> 2009-03-03 >> 2008-11-05 >> 2008-07-10 >> ... >> ... >> 2007-07-23 >> >> >> Assuming this is a SORTED dataset in ascending order by Date, I >> would need >> to

Re: [sqlite] Nested Inner Join Help

On 3/07/2009 7:08 AM, Ed Hawke wrote: > > Out of interest, would I be able to use binding on the run-time defined > fields? > > If I wanted to use: > > select * from A > join B b1 on (A.Column3 = b1.ID) > join C c1 on (b1.Column1 = c1.ID) > join D d1 on (b1.Column2 = d1.ID) > >

Re: [sqlite] 3 million rows, query speeds, and returning zero for rows that don't exist

On 2/07/2009 11:00 AM, yaconsult wrote: > > Most of the queries I've done so far have been pretty straightforward > and it's worked very well. But, now I need to do one that's taking > too long. There's probably a better way than the one I'm using. > > The problem is that I need to produce

Re: [sqlite] first few characters of varchar() corrupted when SELECTing from a C++ program?

On 30/06/2009 2:56 PM, freshie2004-sql...@yahoo.com.au wrote: > printf("testValue=(%s)\n"); I've always been afraid to use those new-fangled mind-reading C compilers lest they were easily shocked ;-) ___ sqlite-users mailing list

Re: [sqlite] Column headers of result

On 29/06/2009 2:57 PM, BareFeet wrote: > Hi, > > Is there any way in the command line to get the columns in a query > result? > > For example, given an ad-hoc SQL command, such as: > > begin; > insert into MyTableOrView select * from SomeSource; > select * from MyTableOrView join

Re: [sqlite] Near misses

On 27/06/2009 7:00 AM, Jean-Christophe Deschamps wrote: > At 13:25 26/06/2009, you wrote: > ´¯¯¯ >> I am trying to find words in a dictionary stored in sqlite, and trying >> a near miss approach. >> For that I tried an algorithm to create patterns corresponding to >> Levenshtein distance of 1

Re: [sqlite] How to find the version of the database.

On 27/06/2009 3:36 AM, Kalyani Phadke wrote: > Is there any way to find the version of SQlite3 database. eg. I have > test.DB file . I want to know which SQLite3 version its using ..eg 3.5.4 > or 3.6.15? Short answer: You can't know. What problem do you face that makes you want to know? If the

Re: [sqlite] search in archive

On 20/06/2009 3:56 AM, Rizzuto, Raymond wrote: > Is it possible to have a search feature for the archive? I.e. rather than > having to do a linear search through 18 archives for an answer to a question, > have a google-like search across all of the archives? http://search.gmane.org/ In the

Re: [sqlite] Literal replacements in prepared statements

On 20/06/2009 12:06 AM, Shaun Seckman (Firaxis) wrote: > Not sure I fully understand what you mean. > Is it not possible to replace the table name in the prepared statement? It is not possible. > What sort of things can I replace then? You can do replacement at any place where a "literal"

Re: [sqlite] Sqlite-3.5.9: getting sqlite_autoindex error

On 18/06/2009 10:40 PM, hiral wrote: > Hi Simon, > > Thank you for your quick reply. > > I am sorry for more general questions. > > As I mentioned I was getting corrupted db error with sqlite-3.5.9, "was getting error often" or "did get error ONCE"?? > but when > I tried with sqlite-3.6.4 it

Re: [sqlite] Database inserts gradually slowing down

On 17/06/2009 11:52 AM, Dennis Cote wrote: > Jens Páll Hafsteinsson wrote: >> Closing and opening again did not speed up steps 1-4, it actually slowed >> things down even more. The curve from the beginning is a bit similar to a >> slightly flattened log curve. When I closed the database and

Re: [sqlite] Question about searches

On 17/06/2009 1:19 AM, Christophe Leske wrote: >>> So far , so good, but my client also expects ANY simplification of a >>> character to be recognized: >>> Cote d'azur for instance should return "Côte d'azur" >>> or the Sao Paulo issue - how can a search for "Sao Paulo" return "Sào >>> Paulo"

Re: [sqlite] Database inserts gradually slowing down

On 17/06/2009 6:17 AM, Hoover, Jeffrey wrote: > One other note, if you have a primary key whose value is continually > increasing your pk index can become imbalanced and therefore > inefficient. A B-tree becomes imbalanced? How so? http://www.sqlite.org/fileformat.html#btree_structures says:

Re: [sqlite] Datatypes

On 16/06/2009 10:47 PM, A Drent wrote: > Sorry, something went wrong on the previous post. *AND* on this one; you are starting a new topic but you included about 900 lines from today's digest!! >>From the docs I read that for the new version: > > a.. When new tables are created using CREATE

Re: [sqlite] Shortest time interval? [Was: Re: repeating events?]

On 13/06/2009 9:05 AM, Allen Fowler wrote: > Indeed, I am aware that SQL is not a "traditional" > programming language per-se and have will now be writing > the calendar logic at the application level. (Looking at Python...) Don't look any further :-) Check out the dateutil module...

Re: [sqlite] repeating events?

On 13/06/2009 1:08 AM, Allen Fowler wrote: >> What are you doing about timezones and DST? Are "start" and "end" UTC? > > For v1, all local times. UTC is not a requirement yet, but if can be added > with out hassle, then why not. > >> Is a location (and by extension a timezone) associated

Re: [sqlite] repeating events?

On 12/06/2009 7:48 PM, Allen Fowler wrote: > idname kind start > end length > > -- > 3

Re: [sqlite] Issue with Distinct and Large numbers

On 12/06/2009 11:14 AM, dbcor...@rockwellcollins.com wrote: > I receive erroneous data when I try to populate a table using data from > another table: Here is how! > > I have TABLE A (that has IDs of INTEGER, Seats as INTEGER, and so forth) > > I want to take this master table and in essence

Re: [sqlite] Problem with sqlite3_interrupt on Windows

On 10/06/2009 9:02 AM, Igor Tandetnik wrote: > Jeremy Smith wrote: >> John Machin wrote: >>> On 10/06/2009 4:40 AM, Jeremy Smith wrote: >>> >>>> When I run sqlite3_interrupt, it doesn't close existing file >>>> handles, making further searches tr

Re: [sqlite] Problem with sqlite3_interrupt on Windows

On 10/06/2009 4:40 AM, Jeremy Smith wrote: > When I run sqlite3_interrupt, it doesn't close existing file handles, > making further searches tricky. Which handles? How do you know? What does "tricky" mean -- "difficult but I can cope with it" or "causes an error" (if so, which?) or something

Re: [sqlite] SQLite Exception: SQLite BUSY

On 8/06/2009 8:22 PM, Manasi Save wrote: > Hi All, > > I have one query regarding SQlite Busy error. > > Can anyone explain me in what cases this error occurs? Yes. You should be able to explain it to yourself after reading relevant parts of: http://www.sqlite.org/faq.html

Re: [sqlite] Importing text file of numbers into INTEGER PRIMARY KEY aka rowid

On 7/06/2009 11:38 AM, P Kishor wrote: > On Sat, Jun 6, 2009 at 8:28 PM, Kelly Jones > wrote: >> On 6/6/09, P Kishor wrote: >>> On Sat, Jun 6, 2009 at 1:43 PM, Kelly Jones >>> wrote: I have a text file onenum.txt

Re: [sqlite] Importing text file of numbers into INTEGER PRIMARY KEY aka rowid

On 7/06/2009 11:28 AM, Kelly Jones wrote: > On 6/6/09, P Kishor wrote: >> On Sat, Jun 6, 2009 at 1:43 PM, Kelly Jones >> wrote: >>> I have a text file onenum.txt with just "1234\n" in it, and a db w/ >>> this schema: >>> >>> sqlite> .schema >>>

Re: [sqlite] Getting last inserted rowid?

On 6/06/2009 8:19 AM, Nikolaus Rath wrote: > John Machin <sjmac...@lexicon.net> writes: >>> Now I'm confused. I want to know if it will be sufficient to wrap my >>> last_insert_rowid() call between BEGIN .. and END in order to make it >>> return the rowid that

Re: [sqlite] Db design question (so. like a tree)

On 5/06/2009 5:27 PM, Francis GAYREL wrote: > To build a consistent oriented tree we need to associate to the nodes a > ranking property such as the birthdate (or any precedence criterion). > Therefore the ancestor of someone is to be selected among older ones. "Ancestor" is a *derived*

Re: [sqlite] Concat two fields for LIKE query?

On 5/06/2009 7:46 AM, Andrés G. Aragoneses wrote: > Igor Tandetnik wrote: >> "Andrés G. Aragoneses" >> wrote: >>> My query, which I want to make it return the first row: >>> >>> SELECT * FROM SomeTable WHERE Path+FileName LIKE '%user/File%' >> SELECT * FROM SomeTable WHERE Path

Re: [sqlite] Db design question (so. like a tree)

On 5/06/2009 12:59 AM, Griggs, Donald wrote: > Regarding: >I could start the id initially with 10 to allocate > > That WOULD allow for a bunch of bull.;-) Don't horse about with IDs with attached meaning; it's a cow of a concept whose outworking could well be catastrophic and

Re: [sqlite] Getting last inserted rowid?

On 4/06/2009 12:57 PM, Nikolaus Rath wrote: > John Machin <sjmac...@lexicon.net> writes: >> On 4/06/2009 8:22 AM, Nikolaus Rath wrote: >>> Nuno Lucas <ntlu...@gmail.com> writes: >>>> On Wed, Jun 3, 2009 at 2:41 AM, Nikolaus Rath <nikol...@rath.or

Re: [sqlite] Getting last inserted rowid?

On 4/06/2009 8:22 AM, Nikolaus Rath wrote: > Nuno Lucas writes: >> On Wed, Jun 3, 2009 at 2:41 AM, Nikolaus Rath wrote: >>> Nuno Lucas writes: On Tue, May 26, 2009 at 5:17 PM, Nikolaus Rath wrote: > Hello,

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

On 3/06/2009 5:15 PM, robinsmathew wrote: > its showing an error near "if": syntax error "it", my crystal ball tells me, is an SQL processor, behaving much as expected when fed what looks like an "if" statement in some other language ... > Kees Nuyt wrote: >> Pseudocode: google("pseudocode")

Re: [sqlite] Populating dyadic dataset

On 2/06/2009 10:17 AM, Vincent Arel wrote: > Your python-like example is also quite helpful. It is not "python-like". Apart from the "..." in the initial data "vectors", it is executable Python code. > As I understand it, you > basically implement Igor's suggestion of running loops on the

Re: [sqlite] Populating dyadic dataset

On 2/06/2009 8:07 AM, Vincent Arel wrote: > Hi everyone, > > I'm very, very new to SQLite, and would appreciate any help I can get. Unless I'm very very confused, this has very little to do with SQL at all (let alone SQLite) apart from using an INSERT statement to dispose of the final product.

Re: [sqlite] journey mode TRUNCATE is to append ? not overwrite?

On 1/06/2009 5:29 PM, pierr wrote: > Hi all, > > Section 7.9 of http://www.sqlite.org/atomiccommit.html mentioned: > > "On embedded systems with synchronous filesystems, TRUNCATE results in > slower behavior than PERSIST. The commit operation is the same speed. But > subsequent transactions are

Re: [sqlite] Deleting database content : SQLite3 API

On 30/05/2009 10:20 PM, souvik.da...@wipro.com wrote: [top-posting unscrambled] [first message] >> As a result , after finding that the >> database already exits at the system startup, I cannot just drop the >> tables. ( As the table which are present in the existing data base is >> not

Re: [sqlite] Corruption of incremental_vacuum databases

On 17/04/2009 1:39 AM, Filip Navara wrote: > Hello, > > I have expected at least some reply. Oh well, new the corruption has happened > again (on another different machine) and I have saved the database files. One > of the corrupted files is available at >

Re: [sqlite] 2 columns as primary key?

On 30/05/2009 12:43 PM, Andrés G. Aragoneses wrote: > I just tried to create a primary key with 2 columns and got this error: > > "sqlite error" "table X has more than one primary key" > > > Doesn't SQLite support this?? :o It does support multi-column primary keys. It's a bit hard to tell at

Re: [sqlite] corrupt database recovery

On 29/05/2009 10:18 AM, John Machin wrote: > On 29/05/2009 9:34 AM, Gene Allen wrote: >> Yeah. >> >> Since my code works in blocks, read/compress/encrypt/write, loop. Almost >> all the real data was being written to the compressed file, however any >> finali

Re: [sqlite] corrupt database recovery

On 29/05/2009 9:34 AM, Gene Allen wrote: > Yeah. > > Since my code works in blocks, read/compress/encrypt/write, loop. Almost > all the real data was being written to the compressed file, however any > finalization and flushing of the stream wasn't occurring (since the encrypt > was failing)

Re: [sqlite] add column creating null columns even with default?

On 29/05/2009 2:53 AM, Simon Slavin wrote: > On 28 May 2009, at 9:00am, Damien Elmes wrote: > >> alter table cardModels add column allowEmptyAnswer boolean not null >> default 1 > >> sqlite> update cardModels set allowEmptyAnswer = 0; > > You're obviously used to other implementations of SQL.

Re: [sqlite] SQLite3 question

On 28/05/2009 10:53 PM, Igor Tandetnik wrote: > "Oza, Hiral_Dineshbhai" > wrote in > message > news:24ea477c0c5854409ba742169a5d71c406bd4...@mailhyd2.hyd.deshaw.com >> Can you please let me know meaning of 'Cell' in Btrees used in >> sqlite3. > > Can you point to

Re: [sqlite] Some index questions

On 28/05/2009 12:24 AM, Dan wrote: > > If a single column index is like the index found in textbooks, > a compound index with two fields is like the phone book. Sorted first by > surname, then by first name. The "rowid", if you like, is the phone > number. > > So, it's easy to find the set of

Re: [sqlite] Fast data duplication

On 27/05/2009 9:47 PM, Igor Tandetnik wrote: > "Vasil Boshnyakov" > wrote in message news:000c01c9de8b$16510a40$42f31e...@bg >> The short description is: we need to copy many records of a table in >> the same table but changing the "Name" value. So we have added a new >>

[sqlite] suggested changes to file format document

1. In the following, s/less than/less than or equal to/ """ 2.3.3.4 Index B-Tree Cell Format [snip 2 paragraphs] If the record is small enough, it is stored verbatim in the cell. A record is deemed to be small enough to be completely stored in the cell if it consists of less than:

Re: [sqlite] database file header: "schema layer file format" anomaly

On 27/05/2009 3:03 AM, D. Richard Hipp wrote: > John - what were you doing when you discovered this? > > On May 26, 2009, at 10:57 AM, John Machin wrote: > >> According to the file format document >> (http://www.sqlite.org/fileformat.html): "[H30120] The 4 byte blo

Re: [sqlite] Minor errors in CREATE TABLE syntax diagrams

On 27/05/2009 12:33 AM, Jim Wilcoxson top-posted: > For my money, I'd prefer to have a smaller, faster parser that worked > correctly on correct input at the expense of not catching all possible > syntax errors on silly input. Firstly, none of the examples that I gave are syntactically incorrect.

Re: [sqlite] Select on foreign key NULL

On 27/05/2009 1:09 AM, Leo Freitag wrote: > Hallo, > > I got some problems with a select on a foreign key with value null. > I want to filter all male singers. > > CREATE TABLE 'tblsinger' ('id' INTEGER PRIMARY KEY, 'name' TEXT, > 'fkvoice' INTEGER, 'sex' TEXT); > INSERT INTO "tblsinger"

[sqlite] database file header: "schema layer file format" anomaly

According to the file format document (http://www.sqlite.org/fileformat.html): "[H30120] The 4 byte block starting at byte offset 44 of a well-formed database file, the schema layer file format, contains a big-endian integer value between 1 and 4, inclusive." However it is possible to end up

[sqlite] Minor errors in CREATE TABLE syntax diagrams

1. SQLite allows NULL as a column-constraint. E.g. CREATE TABLE tname (col0 TEXT NOT NULL, col1 TEXT NULL); The column-constraint diagram doesn't show this possibility. Aside: The empirical evidence is that NULL is recognised and *ignored*; consequently there is no warning about sillinesses

Re: [sqlite] (no subject)

On 26/05/2009 7:58 PM, Samuel Baldwin wrote: > On Tue, May 26, 2009 at 4:45 PM, Martin.Engelschalk > wrote: >> select * from sqlite_master; > > Or: > .dump tablename Don't try that with your 100MB database without ensuring that your keyboard interrupt mechanism isn't

Re: [sqlite] Checking if an "integer" column is set to NULL

On 25/05/2009 10:15 PM, chandan wrote: > Hi, > I have used sqlite3_bind_null() API to bind an integer column with > NULL. When I read the value of that integer column I get the value as 0 > (zero). Is there any way I can check if the column is set to NULL? You do realise that calling it

Re: [sqlite] SQLite3 support for 64-bit unsigned integers

On 25/05/2009 4:28 PM, Kelly Jones wrote: > I tried inserting 2^63-1 and the two integers after it into an SQLite3 > db, but this happened: > > SQLite version 3.6.11 > Enter ".help" for instructions > Enter SQL statements terminated with a ";" > sqlite> CREATE TABLE test (test INT); > sqlite>

  1   2   3   >