[sqlite] design question / discussion
Hi I have a general design question. I have the following senario... In an embedded system running linux 2.6.2x I have a sqlite database constantly being updated with data acquired by the system. I cant lose data (hence why I am using sqlite in the first place). However periodically I have download the data contain within the database to a central server. The system cannot stall during the download and must continue to record data. Also, after the download I need to shrink the database size, simply because if the database is allowed to grow to its max size (~50MB) then every download thereafter would be 50MB, which is unacceptable. I would simply vacuum the database, but this takes too much time and stalls the system. My solution is the following (still roughed out on scraps of paper and gray matter). have two databases on the system at all times (data.sqlite.(x) and data.sqlite.(x+1)) All data written into x. When a download is requested... Mark highest rowid in each table in database (x) in a table called DownloadInfo Begin logging data to (x+1) Download done (success or failure - downloads may be cancelled or timeout) Attach x+1 to x Begin transaction delete all data in x from tables equal to <= rowid saved in DownloadInfo move any data stored in x+1 to x if download was successful... mark in x that a download was successful in DownloadInfo At next powerup... Scan x.DownloadInfo, see if a download was successful... Yes Attach x+1 to x attach x+2 to x begin transaction Build new database x+2 Move data from x to x+1 Mark database has been deleted in DownloadInfo commit. delete (using os, unlink perhaps) No Do nothing. So its kinda complicated, but I think such things are necessary. For instance, a vacuum is out of the question, it just takes too long. Thats why the double database scheme works good for deleting old databases. I guess i want to stop here and leave some info out. That way I don't suppress any good ideas. And as always I really appreciate any help i can get. I tried to implement something similar, but I was copying an already prepared sqlite database which was not very reliable. Guess another question, maybe one that solves this one. has any improvements on auto-vacuum been made? Does anyone trust it or can anyone attest to its fault tolerance. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] select userfunc(*)
Igor Tandetnik wrote:
> "Bradley Smith" wrote:
>> Igor Tandetnik wrote:
>>> Bradley Smith wrote:
Why does a user defined function receive zero arguments when used in
the following expression?
select userfunc(*) from t;
>>> Why would you expect otherwise? The only precedent in standard SQL
>>> for a syntax like this is count(*), which doesn't need any arguments.
>> I expect otherwise because my understanding of SQL is that '*' refers
>> to all columns in the table being queried.
>
> Only in a select statement, but not in a function invocation.
Yes, but the function invocation is a part of the select statement. Is
there documentation somewhere which explains what '*' means when used in
a function invocation?
Why would should the effect of '*' change when it is wrapped in a
function invocation which is still part of the selection list? For example,
select *, myfunc(*) from t;
The first '*' means "all columns", but the second '*' means "nothing".
Both are in the same context of a select list in a select statement.
>
>> Section 7.9 of
>> http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt Under
>> Syntax Rules 3b:
>>
>> Otherwise, the "*" is equivalent to a > expression> sequence
>
> But you don't use the star as a select list, do you?
Yes, but indirectly. The '*' is an argument to the function which is
used in the select list.
Regardless, thank you for your responses. Clearly, my inexperience with
SQL is showing. Are there any other common special cases besides the
behavior of '*'?
Thanks,
Bradley
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Exceptions to sorting?
Igor Tandetnik wrote:
> "Andrés G. Aragoneses" <[EMAIL PROTECTED]>
> wrote:
>> Igor Tandetnik wrote:
>>> "Andrés G. Aragoneses"
>>> <[EMAIL PROTECTED]> wrote:
Andrés G. Aragoneses wrote:
> Igor Tandetnik wrote:
>> "Andrés G. Aragoneses"
>> <[EMAIL PROTECTED]> wrote:
>>> Hello, I am querying a list of elements and some of them are
>>> blank (''). However, I want that those who are blank appear as
>>> the last (instead of the first) elements when I sort with ORDER
>>> BY alphabetically. How can I do this with just SQL?
>> ORDER BY text = '', text
> It works thanks!
Hugh, but this seems not to be applicable for different columns,
because, let's suppose I have:
ItemId ItemName ItemNameSort
1'Balloon' 'balloon'
2'' '[noname]'
3'Car' 'car'
If I use "ORDER BY ItemName = '', ItemNameLowered" I get 2,1,3 and I
want to get 1,3,2. Any ideas?
>>> What's ItemNameLowered? Your table doesn't seem to have such a
>>> column.
>>>
>>> Have you tested it? I don't see how row 2 could appear first. You
>>> don't actually have apostrophes stored in the database as part of
>>> your data, do you? What does the following statement output:
>>>
>>> select ItemId, ItemName, length(ItemName)
>>> from myTable;
>> Sorry, a typo: not ItemNameLowered but ItemNameSort. When I use '' is
>> to differentiate it from the int type (which I used for the key). So
>> let's rewrite the question:
>>
>> ItemId ItemName ItemNameSort
>> 1'Balloon' 'balloon'
>> 2'' '[noname]'
>> 3'Car' 'car'
>>
>> If I use "ORDER BY ItemName = '', ItemNameSort ASC" I get 2,1,3 and I
>> want to get 1,3,2. Any ideas?
>
> I tested it, and I get 1, 3, 2. I can reproduce your result only if I
> put NULL into ItemName, rather than an empty string. What does this
> statement return:
You're right, I had nulls instead of empty strings.
> select ItemId, ItemName, typeof(ItemName)
> from myTable
>
> If you do need to handle NULLs, simply change the condition to
>
> ORDER BY ifnull(ItemName, '')='', ItemNameSort ASC
>
Thanks!
Andres
--
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite3_transfer_bindings obsolete?
On May 18, 2008, at 12:01 AM, Shawn Wilsher wrote: > On Sat, May 17, 2008 at 10:13 AM, Dan <[EMAIL PROTECTED]> wrote: >> How are you going to 'clone' the statement objects to pass to >> the second database handle? > Our wrapper around the statement object already stores the string of > the sql statement, so that part is easy. Looks like we'll have to > keep track of bound parameters as well now. By recompiling it for the new connection. Fair enough. If you use sqlite3_prepare_v2() to create a statement, you could also use sqlite3_sql() to retrieve the original text of the SQL in utf-8 encoding: const char *sqlite3_sql(sqlite3_stmt *pStmt); Dan. > Cheers, > > Shawn Wilsher > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite3_transfer_bindings obsolete?
On Sat, May 17, 2008 at 10:13 AM, Dan <[EMAIL PROTECTED]> wrote: > How are you going to 'clone' the statement objects to pass to > the second database handle? Our wrapper around the statement object already stores the string of the sql statement, so that part is easy. Looks like we'll have to keep track of bound parameters as well now. Cheers, Shawn Wilsher ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Equivalent of mysql_real_escape_string() ?
On Sat, May 17, 2008 at 09:30:19AM -0500, Skip Evans scratched on the wall: > Okay, I'm looking all through the PDO docs on > php.net, but am unable to find the SQLite > equivalent to the MySQL function > > mysql_real_escape_string() > > in case, among other things, a text field contains > single quotes, etc. Ugg... a wonderful example of how not to do things. Functions like this shouldn't exist. > How is this done in SQLite? I'm still scouring the > the docs but having no luck. > > Does it have something to do with > > $dbh->prepare() > > ...or am I on the wrong track with that one? The proper way to deal with such things is to bind the strings to a prepared statement. Bound parameters are never passed through the SQL parser, making it impossible to create an SQL injection. It took me all of 30 seconds to Google an example of when something like mysql_real_escape_string() breaks: http://ilia.ws/archives/103-mysql_real_escape_string-versus-Prepared-Statements.html -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "'People who live in bamboo houses should not throw pandas.' Jesus said that." - "The Ninja", www.AskANinja.com, "Special Delivery 10: Pop!Tech 2006" ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Exceptions to sorting?
On Sat, May 17, 2008 at 09:30:30AM +0200, Petite Abeille scratched on the wall: > > On May 16, 2008, at 11:07 PM, Igor Tandetnik wrote: > > > Well, for any string A there exists another string B that sorts > > after A. > > How can I guarantee that, after I choose A as my "sorts after > > everything" marker, somebody doesn't put B into the database? > > Well... not to beat a dead horse or anything, but... if one is worried > about the entire range of Unicode data points... one can always use > the highest collation data point as a marker... And if the highest data point is a "z" (for example), someone could put the string "zz" into the DB that will sort after it. If you use "zz" as the marker, they could put "zzz" in the DB. And so on. The only thing that breaks "for any string A there exists another string B that sorts after A" is the maximum string length. In SQLite that's rather large-- especially to be using as a constant. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "'People who live in bamboo houses should not throw pandas.' Jesus said that." - "The Ninja", www.AskANinja.com, "Special Delivery 10: Pop!Tech 2006" ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Indexing virtual tables
Hi! Here is what I'm still trying to achieve: - I have a custom file format having "records" and file offsets. - Each record in that custom file format has the same number of fields, but the records itself are variable length, that's why I need a file offset to quickly locate a record. One other way (if you can wait for a very long time...) is to walk sequentially the records list to get the desired record. - I've implemented a working SQLite "virtual table" in order to be able to read and query my custom file format through SQLite. - Now, basically what I'd like would be to "CREATE INDEX" on a field of my virtual table to take advantage of it in the xBestIndex callback. But the documentation says that we cannot use "CREATE INDEX" on virtual tables. Let's say the data in the field "F1" of my virtual table "VFILE", and the file offsets are the following: F1 fileoffset -- a 10 b 21 z 34 x 45 a 51 x 69 z 73 a 88 x 94 I want to index the column F1, to be able to have a quick response to queries like: select * from VFILE where F1='x' At this point, I think I have only 3 possible strategies: 1. Use SQLite tables to "fake" a standard index using SQLite tables 2. Use internal SQLite B-Tree routines to implement my index (sqlite3BtreeCreateTable and stuff) 3. Implement my own B-Tree and sort algorithms to achieve this, externally to SQLite Strategy 3 is precisely what I'm trying to avoid (too much work and testing :-) ). Strategy 2 is strongly discouraged by DRH. Then strategy 1 seems to be (like you've just said) the only way to go: a) Duplicate the data to be indexed (and the file offsets to use) create table INDEX1_SORT as select F1, fileoffset from VFILE order by F1, fileoffset INDEX1_SORT: physical table F1 fileoffset -- a 10 a 51 a 88 b 21 x 45 x 69 x 94 z 34 z 73 b) Create an index on that data OPTION 1: Use SQLite CREATE INDEX at this point. b.1.1) create index on INDEX1_SORT(F1) OPTION 2: Fake index with custom tables b.2.1) create table INDEX2_SUM as select F1, min(INDEX1_SORT.rowid) as 'minrow', max(INDEX1_SORT.rowid) as 'maxrow' from INDEX1_SORT group by F1 INDEX2_SUM: physical table F1 minrow maxrow --- a 1 3 b 4 4 x 5 7 z 8 9 b.2.2) create index on INDEX_2_SUM(F1) * Usage for option 2: - Use INDEX2_SUM to fetch the requested value in the initial query (select * from VFILE where F1='x') - Get data from table INDEX1_SORT between rowid "minrow" (5) and "maxrow" (7) - For each line, use the given file offset to locate the real data in the custom file format file. - Read 3 records at fileoffet = 45,69,94 and return them to SQLite. I really feel like all this is not very optimal. What is the best strategy to achieve optimal speed and needed storage? Am I missing a trivial point? Thank you for any help on that! Aladdin > Date: Mon, 12 May 2008 15:37:22 -0700 > From: [EMAIL PROTECTED] > To: sqlite-users@sqlite.org > Subject: Re: [sqlite] Indexing virtual tables > > I'm not quite clear on your question - why wouldn't you just create > any indices you need within the virtual-table implementation itself? > Sort of like how fts uses SQLite tables to implement data-storage for > the full-text index. > > -scott > > > On Mon, May 5, 2008 at 10:13 AM, Aladdin Lampé wrote: >> >> Just thinking again about indexing strategies on virtual tables, I'm >> wondering why virtual tables could not be indexed using the "normal" SQLite >> command "INDEX". Indeed, I just expected that the data inside the column of >> the virtual table could be sequentially scanned (using the "xColumn" >> callback), producing the same result as if it were a real table. Is that way >> of seeing things flawed? >> >> Any hook allowing to use SQLite internal indexing techniques for virtual >> tables? Maybe using direct b-tree manipulation (even if I know it's not >> recommended)? I'm not very keen on developing my own from stratch. Dealing >> with "big" tables that don't fit into memory does not seem so easy because >> I'll have to use a temporary disk file... >> >> Some help would be greatly appreciated! >> Aladdin >> >> _ >> Faites vous de nouveaux amis grâce à l'annuaire des profils Messenger ! >> http://home.services.spaces.live.com/search/?page=searchresults=true=AdvPeopleSearch=SPXFRM=3=2=0=Nom+public===Pr%C3%A9nom=Nom=Lieu=Profession=amis=Rechercher >> ___
[sqlite] Equivalent of mysql_real_escape_string() ?
Hey all, Okay, I'm looking all through the PDO docs on php.net, but am unable to find the SQLite equivalent to the MySQL function mysql_real_escape_string() in case, among other things, a text field contains single quotes, etc. How is this done in SQLite? I'm still scouring the the docs but having no luck. Does it have something to do with $dbh->prepare() ...or am I on the wrong track with that one? -- Skip Evans Big Sky Penguin, LLC 503 S Baldwin St, #1 Madison, WI 53703 608-250-2720 http://bigskypenguin.com =-=-=-=-=-=-=-=-=-= Check out PHPenguin, a lightweight and versatile PHP/MySQL, AJAX & DHTML development framework. http://phpenguin.bigskypenguin.com/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite3_transfer_bindings obsolete?
On May 17, 2008, at 7:59 PM, Shawn Wilsher wrote: > On Sat, May 17, 2008 at 2:39 AM, Dan <[EMAIL PROTECTED]> wrote: >>> And a fun follow-up question. Will sqlite3_transfer_bindings >>> transfer >>> bindings across connection objects if the two statements are for two >>> different connections to the same database? >> >> No. It will return SQLITE_MISUSE. > Drat. It doesn't look like there's a way to see what's already been > bound to a statement either, correct? Not easily done at the moment. How are you going to 'clone' the statement objects to pass to the second database handle? Dan. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] select userfunc(*)
"Bradley Smith" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > Igor Tandetnik wrote: >> Bradley Smith wrote: >>> Why does a user defined function receive zero arguments when used in >>> the following expression? >>> >>> select userfunc(*) from t; >> >> Why would you expect otherwise? The only precedent in standard SQL >> for a syntax like this is count(*), which doesn't need any arguments. > > I expect otherwise because my understanding of SQL is that '*' refers > to all columns in the table being queried. Only in a select statement, but not in a function invocation. > Section 7.9 of > http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt Under > Syntax Rules 3b: > > Otherwise, the "*" is equivalent to aexpression> sequence But you don't use the star as a select list, do you? Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite3_transfer_bindings obsolete?
On Sat, May 17, 2008 at 2:39 AM, Dan <[EMAIL PROTECTED]> wrote: >> And a fun follow-up question. Will sqlite3_transfer_bindings transfer >> bindings across connection objects if the two statements are for two >> different connections to the same database? > > No. It will return SQLITE_MISUSE. Drat. It doesn't look like there's a way to see what's already been bound to a statement either, correct? Cheers, Shawn ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] FTS3 Question
On May 17, 2008, at 9:49 AM, Mike Marshall wrote: > SELECT guid FROM data WHERE text MATCH SELECT query FROM category Perhaps something along these lines: select data.guid fromdata joincategory on category.guid = data.guid where data.text match category.query Or something :) -- PA. http://alt.textdrive.com/nanoki/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] FTS3 Question
I have an FTS3 table created as follows CREATE VIRTUAL TABLE data USING fts3(guid, text) And a standard table created thus CREATE TABLE category (label, query) What I would like to be able to do is an SQL query of the form SELECT guid FROM data WHERE text MATCH SELECT query FROM category But I can't seem to get it to work. Should it work? And if it should can someone point out what I am doing wrong. Thanks Mike ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Exceptions to sorting?
On May 16, 2008, at 11:07 PM, Igor Tandetnik wrote: > Well, for any string A there exists another string B that sorts > after A. > How can I guarantee that, after I choose A as my "sorts after > everything" marker, somebody doesn't put B into the database? Well... not to beat a dead horse or anything, but... if one is worried about the entire range of Unicode data points... one can always use the highest collation data point as a marker... on the other hand, this is rather academic in practice... "Don't Let Architecture Astronauts Scare You" http://www.joelonsoftware.com/articles/fog18.html -- PA. http://alt.textdrive.com/nanoki/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite3_transfer_bindings obsolete?
On May 17, 2008, at 2:51 AM, Shawn Wilsher wrote: > And a fun follow-up question. Will sqlite3_transfer_bindings transfer > bindings across connection objects if the two statements are for two > different connections to the same database? No. It will return SQLITE_MISUSE. Dan. > > Cheers, > > Shawn > > On Tue, May 13, 2008 at 2:05 PM, Shawn Wilsher > <[EMAIL PROTECTED]> wrote: >> I was looking through the documentation and was wondering why >> sqlite3_transfer_bindings has been marked as obsolete. It's >> something >> that we use currently in our code, and I was looking to use it again >> for something new. Is there a new way to accomplish the same thing >> that this function does? What was the rational for removing it. >> >> If you need a use case for why Mozilla needs it, I'd be happy to >> oblige. >> >> Cheers, >> >> Shawn Wilsher >> Mozilla Developer >> > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
