Re: [sqlite] .separator \t not working
Hello, Le 24 juil. 08 à 01:28, none given a écrit : > I then attempt the 3 variations of the statement as such : > sqlite3 test.db ".separator '\t' .import data.csv wc2" > sqlite3 test.db ".separator \t .import data.csv wc2" > sqlite3 test.db ".separator '\t' \n .import data.csv wc2" [snip] > What am I doing wrong? I think you can enter only one dot command, so use the -separator option. And sqlite has a strange behaviour with \t on command line, you must give a REAL tab to the -separator option. On command line, use Control-v to insert a real TAB. The command should look like this : sqlite3 test.db -separator '' test.db ".import data.csv wc2" Check http://sqlite.org:8080/cgi-bin/mailman/private/sqlite-users/2008-July/004448.html for how to do it from a program. /schplurtz ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] .separator \t not working
Hi, I am having issues running an import from the command line (on a Mac). When running: sqlite3 separator '\t' test.db ".import data.csv wc2" I get the error : line 1: expected 27 columns of data but found 1 (the file and the table structure both exist). I then attempt the 3 variations of the statement as such : sqlite3 test.db ".separator '\t' .import data.csv wc2" sqlite3 test.db ".separator \t .import data.csv wc2" sqlite3 test.db ".separator '\t' \n .import data.csv wc2" To no avail... But, if I create a .sqliterc file containing : .separator \t then the import works. Why is it I can successfully import with an sqliterc file, but it fails via the command line? As I am running the command from an Xcode NTask, I could really do without the sqliterc file... What am I doing wrong? Tia, S. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] FTS index size
OK! This gives me something I can sink my teeth into!
I hacked together a TCL script which takes info about 100 albums
(snagged from a list on the Internet), makes up 10 song titles each,
makes up a path in the obvious way, and stuffs them into a table.
Then it times SELECT COUNT(*). Three versions, a single table, split
between a regular table and fts3, and split between two regular tables
(convert the second table from fts3 to a regular table, as a control).
The results were:
regular - 290 microseconds per iteration
regular w/fts3 - 63858 microseconds per iteration
regular w/regular - 1299 microseconds per iteration
I ran it 5 times and took the middle timing. The queries looked like:
SELECT COUNT(*) FROM Songs
SELECT COUNT(*) FROM SongsBase JOIN SongsText ON ID=docid
So, indeed, there's room for improvement!
I have a patch which improves regular w/fts3 to '8048 microseconds per
iteration' by re-using the prepared statement better. I need to spend
some time to think on correctness before checking it in, though.
AFAICT, the effect seems to be linear. In stepping around in the
code, I'm not sure how much improvement can be gotten beyond my patch,
but I'll be thinking on it.
---
Additionally, if I let the fts3 table drive the query:
SELECT COUNT(*) FROM SongsText CROSS JOIN SongsBase ON ID=docid
I get '2971 microseconds per iteration' without my patch. The use of
"CROSS JOIN" is to force SongsText to be the outer loop (otherwise it
performs like the earlier statement). This might be a problem with
the fulltextBestIndex hinting.
This join-ordering trick may have issues if you need a WHERE clause
which references SongsBase, and it may depend a lot on what your
queries are and their result sizes versus the size of your dataset.
Future cloudy! The patch I mention above should improve joins from
SongsBase to SongsText.
Thanks for bearing with me,
scott
On Wed, Jul 23, 2008 at 7:47 AM, Jiri Hajek <[EMAIL PROTECTED]> wrote:
>> Again, you've given a relatively broad description of what you're
>> trying to do. I could make up a bunch of stuff and answer my own
>> question, but you'd probably rather than I considered the problem
>> _you_ are having.
>
> Ok, I'll try to be as specific as possible. The main table I have is (the
> real version has much more fields, but it isn't important for our example):
>
> CREATE TABLE Songs (
> ID INTEGER PRIMARY KEY AUTOINCREMENT,
> Artist TEXT COLLATE IUNICODE,
> Album TEXT COLLATE IUNICODE,
> SongTitle TEXT COLLATE IUNICODE,
> Path TEXT COLLATE IUNICODE,
> Year INTEGER,
> Bitrate INTEGER)
>
> This table can have even >100k records, even close to million and is mostly
> accessed by SELECTing all fields of some records, i.e.:
>
> SELECT * FROM Songs WHERE {something}
>
> In order to use FTS3, I could take all the text fields from Songs table and
> move them to a FTS3 table:
>
> CREATE TABLE SongsBase (
> ID INTEGER PRIMARY KEY AUTOINCREMENT,
> Year INTEGER,
> Bitrate INTEGER)
>
> CREATE VIRTUAL TABLE SongsText USING FTS3(TOKENIZE mm,
> Artist,
> Album,
> SongTitle,
> Path)
>
> This way I would lose my custom collation (IUNICODE), which would be quite a
> problem, particularly for Path field (and if you're asking, yes, I'd like to
> include Path in the full-text index). Another problem is that joined SELECT
> on SongsBase and SongsText is slower than SELECT on the original Songs
> table.
>
> So, the only solution using FTS3 seems to be to use the original Songs table
> and add SongsText table, automatically updated by triggers like:
>
> CREATE TRIGGER update_songs UPDATE OF Artist,Album,SongTitle,Path ON Songs
> BEGIN
> UPDATE SongsText SET Artist=new.Artist, Album=new.Album,
> SongTitle=new.Title, Path=new.Path WHERE rowid=new.id;
> END;
>
> This solution probably isn't bad, but according to my knowledge of FTS3, it
> unnecessarily occupies some DB space (all text fields are actually stored
> twice, once in Songs and once in SongsText).
>
> Any ideas or recommedations?
>
> Thanks,
> Jiri
>
> ___
> 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] Ignoring "The"
Regarding: If you can't upgrade you simply need to calculate the length
as well.
... then substr(artist, 5, length(artist)-4)
Am I right that it's ok to simply specify a large value for the third
parameter?
SQLite version 3.3.12
select substr('The Quick Brown Fox', 5, 999);
Quick Brown Fox
It's possible that you'll still end up wanting to add a "sort_by" column
if it matters that, e.g., a group named "The E-mails" is sorted the same
as "The Emails".
"How do I sort 'the, '? Let me count the ways."
This email and any attachments have been scanned for known viruses using
multiple scanners. We believe that this email and any attachments are virus
free, however the recipient must take full responsibility for virus checking.
This email message is intended for the named recipient only. It may be
privileged and/or confidential. If you are not the named recipient of this
email please notify us immediately and do not copy it or use it for any
purpose, nor disclose its contents to any other person.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Ignoring "The"
Dennis Cote wrote: > Andrew Gatt wrote: > >> Error: wrong number of arguments to function substr() >> >> I'm using sqlite 3.3.6, but i'm presuming the two and three variable >> substr functions go back further than this? Doing tests it seems to be >> the two variable version it doesn't like. Do i need to upgrade my sqlite >> library? >> >> > > Yes, this feature was added in version 3.5.2 (see > http://www.sqlite.org/changes.html) so you will need to upgrade to use > this form of the function. > > If you can't upgrade you simply need to calculate the length as well. > > select * from t > order by > case > when lower(substr(artist, 1, 4)) = 'the,' > then substr(artist, 5, length(artist)-4) > when lower(substr(artist, 1, 3)) = 'the' > then substr(artist, 4, length(artist)-3) > else artist > end; > > HTH > Dennis Cote > > That's got it (i'll upgrade at some point and revert to the previous example). I've also changed 'the' to 'the ' as mentioned in another email. Thanks for everyone's help. Andrew ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] problem building reduced-size sqlite
Shane Harrelson wrote: > I checked in some updates to the "configure" support that will hopefully do > the right thing and pass any OMIT options to lemon and mkkeywordhash. > There was also a minor fix to handle SQLITE_OMIT_VIEW being defined while > SQLITE_OMIT_SUBQUERY is undefined in select.c (something you probably rand > into with your example.) Also note that in your example, -DTEMP_STORE=2 > should probably be replaced with some variation of the --enable-tempstore > configure option. > Shane, I just looked at your changes, and it seems to me the format of the options being passed to lemon is incorrect. The lemon program take options of the form D=SQLITE_OMIT* as shown in the usage information. $ ./lemon --help Command line syntax error: undefined option. c:\sqlite\SQLite3\build\lemon.exe --help here --^ Valid command line options for "c:\sqlite\SQLite3\build\lemon.exe" are: -b Print only the basis in report. -c Don't compress the action table. D= Define an %ifdef macro. -g Print grammar without actions. -m Output a makeheaders compatible file -q (Quiet) Don't print the report file. -s Print parser stats to standard output. -x Print the version number. I have tested that these options work as expected by manually editing the old Makefile and passing such options using the OPTS variable. Further testing reveals that lemon also seems to accept option in the form -DSQLITE_OMIT* just like the compiler, so this is probably a non-issue (except that the lemon usage documentation should be updated and the command should be changed to accept a --help option as well). When I use configure to build a new makefile after your changes the OPT_FEATURE_FLAGS are set incorrectly. I ran this command: $ ../sqlite/configure CFLAGS="-DSQLITE_OMIT_TRIGGER" During the run the following messages were generated (near the end): ../sqlite/configure: OPT_FEATURE_FLAGS+= -DSQLITE_OMIT_TRIGGER: command not found ../sqlite/configure: ac_temp_CFLAGS+= -DSQLITE_OS_WIN=1: command not found The generated makefile contains the following line: OPT_FEATURE_FLAGS = -DSQLITE_OMIT_LOAD_EXTENSION=1 with no mention of the SQLITE_OMIT_TRIGGER option. When make is executed lemon is called to generate the parser like this: ./lemon.exe -DSQLITE_OMIT_LOAD_EXTENSION=1 parse.y and the generated parser still contains all the trigger related code. It looks like this feature still needs some more work. Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Ignoring "The"
Andrew Gatt wrote: > > Error: wrong number of arguments to function substr() > > I'm using sqlite 3.3.6, but i'm presuming the two and three variable > substr functions go back further than this? Doing tests it seems to be > the two variable version it doesn't like. Do i need to upgrade my sqlite > library? > Yes, this feature was added in version 3.5.2 (see http://www.sqlite.org/changes.html) so you will need to upgrade to use this form of the function. If you can't upgrade you simply need to calculate the length as well. select * from t order by case when lower(substr(artist, 1, 4)) = 'the,' then substr(artist, 5, length(artist)-4) when lower(substr(artist, 1, 3)) = 'the' then substr(artist, 4, length(artist)-3) else artist end; HTH Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite 3.5.8 database corruption
D. Richard Hipp wrote: > On Jul 23, 2008, at 1:08 PM, Brad House wrote: > >> I'm just investigating an issue now. This is the first ever >> incident of a corrupt database we've had on a few thousand >> installations, > > Have you read the background information at > > http://www.sqlite.org/atomiccommit.html > > See especially section 9.0: Things That Can Go Wrong. Yes, I've read that. We're using the VFS layer that sqlite provides for windows (since XP-E is just XP with a bunch of dlls and auxiliary applications removed). The database always resides on the same disk as the software accessing it (no network transfers). The database file wouldn't have been manipulated outside of our application. According to your section 9, that leaves a 'rouge' process (read: virus), or buffers not actually being flushed to disk (either because of a disk controller issue or a FlushFileBuffers() issue). Obviously other options exist such as hardware failure (RAM, harddrive), or an SQLite bug. Hardware failure definitely hasn't been ruled out here. The main reason for reporting this issue is to make sure if there is an issue, there is enough 'history' of it to justify researching it. I'm definitely not blaming SQLite at this point. Thanks. -Brad ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Ignoring "The"
On Wed, 2008-07-23 at 12:30 -0400, Igor Tandetnik wrote: > Dennis Cote <[EMAIL PROTECTED]> wrote: > > Andrew Gatt wrote: > >> I have a table of music artist names which i'd like to output in > >> order. Normally i just use: > >> > >> select * from artists order by artist_name; > >> > >> What i'd really like to do is order the artists by name but ignore > >> any "the" or "the," preceding it. > >> > > > > You could try something like this: > > > > select * from t > > order by > > case > > when lower(substr(artist, 1, 3)) = 'the' then substr(artist, > > 4) when lower(substr(artist, 1, 4)) = 'the,' then > > substr(artist, 5) else artist > > end; > > Only change the order of the tests - test for 'the,' (with comma) first, > otherwise you'll never get to it. > > Igor Tandetnik > Also, the "the" test should be lower(substr(artist, 1, 4)) = 'the ' Otherwise 'they might be giants' would sort as 'y might be giants' which is probably not desirable. David ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Ignoring "The"
Dennis Cote wrote: > Andrew Gatt wrote: > >> I have a table of music artist names which i'd like to output in order. >> Normally i just use: >> >> select * from artists order by artist_name; >> >> What i'd really like to do is order the artists by name but ignore any >> "the" or "the," preceding it. >> >> > > You could try something like this: > > select * from t > order by > case > when lower(substr(artist, 1, 3)) = 'the' then substr(artist, 4) > when lower(substr(artist, 1, 4)) = 'the,' then substr(artist, 5) > else artist > end; > > HTH > Dennis Cote > > Thanks for the help, however its giving me a compile error: Error: wrong number of arguments to function substr() I'm using sqlite 3.3.6, but i'm presuming the two and three variable substr functions go back further than this? Doing tests it seems to be the two variable version it doesn't like. Do i need to upgrade my sqlite library? Thanks again Andrew ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite 3.5.8 database corruption
On Jul 23, 2008, at 1:08 PM, Brad House wrote: > I'm just investigating an issue now. This is the first ever > incident of a corrupt database we've had on a few thousand > installations, Have you read the background information at http://www.sqlite.org/atomiccommit.html See especially section 9.0: Things That Can Go Wrong. D. Richard Hipp [EMAIL PROTECTED] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite 3.5.8 database corruption
Guess the mailing list stripped the attachment, I've uploaded it here: http://www.monetra.com/~brad/integrity_check.txt.bz2 Brad House wrote: > I'm just investigating an issue now. This is the first ever > incident of a corrupt database we've had on a few thousand > installations, though most of our installations are on > SQLite 3.4, our latest release is now using 3.5.8. > We have deployments on just about every OS... > > The OS that experienced the corruption was Windows XP-E > (embedded). > > The error message SQLite is returning is: > database or disk is full > > But I am told there are 45G free on the partition that > the database file resides. > > We do use SQLite in a multithreaded environment, and it > is compiled with Threadsafe. Infact, we modify the > amagalmation and put: > #define SQLITE_THREADSAFE 1 > At the top of the file just to make sure. > We also use 'sqlite3_enable_shared_cache(1)'. I don't > think it really provides that much benefit to us though > as we are more commit-heavy, so I can disable it if > it might be a point of concern. > > I've attached the output of PRAGMA integrity_check; > (which looks pretty bad)... > > I can make the database available if necessary. > > Thanks for any insight. > -Brad > > > > > ___ > 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
[sqlite] sqlite 3.5.8 database corruption
I'm just investigating an issue now. This is the first ever incident of a corrupt database we've had on a few thousand installations, though most of our installations are on SQLite 3.4, our latest release is now using 3.5.8. We have deployments on just about every OS... The OS that experienced the corruption was Windows XP-E (embedded). The error message SQLite is returning is: database or disk is full But I am told there are 45G free on the partition that the database file resides. We do use SQLite in a multithreaded environment, and it is compiled with Threadsafe. Infact, we modify the amagalmation and put: #define SQLITE_THREADSAFE 1 At the top of the file just to make sure. We also use 'sqlite3_enable_shared_cache(1)'. I don't think it really provides that much benefit to us though as we are more commit-heavy, so I can disable it if it might be a point of concern. I've attached the output of PRAGMA integrity_check; (which looks pretty bad)... I can make the database available if necessary. Thanks for any insight. -Brad ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Ignoring "The"
Igor Tandetnik wrote: > > Only change the order of the tests - test for 'the,' (with comma) first, > otherwise you'll never get to it. > Yes, of course. Thats what happens every time I post untested code. I should know better by now. :-) Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite under c++
Juzbrig <[EMAIL PROTECTED]> wrote: > I am new in sqlite (before I have mysql + php experience). I have a > question If my DB is alredy open > > sqlite3* handle; > const char* baza = "cols.db3" ; > char *zErrMsg = 0; > sqlite3_open(test_baza,); > > How can I get the results of SQL "SELECT * FROM" into a string table > or any c++ structure ? > sqlite3_exec() doesn't seem to return any strings/chars. http://sqlite.org/c3ref/free_table.html But it's better to use prepared statement APIs: sqlite3_prepare[_v2], sqlite3_step, sqlite3_column_*, sqlite3_finalize. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Cursors in SQlite
Thanks Igor, It occurred to me that sqlite3_step is similar to a cursor about thirty minutes after I sent my email, while I was driving home. That article was very interesting; in our current custom database solution we have cursors but the rest of our solution is so limited compared to SQLite I can't see that not having a comparable cursor will be an issue as we are not doing anything with our cursors that we couldn't do with sqlite3_step. Daniel -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Igor Tandetnik Sent: Tuesday, July 22, 2008 5:25 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Cursors in SQlite Brown, Daniel <[EMAIL PROTECTED]> wrote: > Does SQLite support/implement cursors of any form sqlite3_step is, in some sense, a forward-only cursor. You might also find this interesting: http://www.sqlite.org/cvstrac/wiki?p=ScrollingCursor Igor Tandetnik ___ 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] Ignoring "The"
Dennis Cote <[EMAIL PROTECTED]> wrote: > Andrew Gatt wrote: >> I have a table of music artist names which i'd like to output in >> order. Normally i just use: >> >> select * from artists order by artist_name; >> >> What i'd really like to do is order the artists by name but ignore >> any "the" or "the," preceding it. >> > > You could try something like this: > > select * from t > order by > case > when lower(substr(artist, 1, 3)) = 'the' then substr(artist, > 4) when lower(substr(artist, 1, 4)) = 'the,' then > substr(artist, 5) else artist > end; Only change the order of the tests - test for 'the,' (with comma) first, otherwise you'll never get to it. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] FTS index size
> Again, you've given a relatively broad description of what you're
> trying to do. I could make up a bunch of stuff and answer my own
> question, but you'd probably rather than I considered the problem
> _you_ are having.
Ok, I'll try to be as specific as possible. The main table I have is (the
real version has much more fields, but it isn't important for our example):
CREATE TABLE Songs (
ID INTEGER PRIMARY KEY AUTOINCREMENT,
Artist TEXT COLLATE IUNICODE,
Album TEXT COLLATE IUNICODE,
SongTitle TEXT COLLATE IUNICODE,
Path TEXT COLLATE IUNICODE,
Year INTEGER,
Bitrate INTEGER)
This table can have even >100k records, even close to million and is mostly
accessed by SELECTing all fields of some records, i.e.:
SELECT * FROM Songs WHERE {something}
In order to use FTS3, I could take all the text fields from Songs table and
move them to a FTS3 table:
CREATE TABLE SongsBase (
ID INTEGER PRIMARY KEY AUTOINCREMENT,
Year INTEGER,
Bitrate INTEGER)
CREATE VIRTUAL TABLE SongsText USING FTS3(TOKENIZE mm,
Artist,
Album,
SongTitle,
Path)
This way I would lose my custom collation (IUNICODE), which would be quite a
problem, particularly for Path field (and if you're asking, yes, I'd like to
include Path in the full-text index). Another problem is that joined SELECT
on SongsBase and SongsText is slower than SELECT on the original Songs
table.
So, the only solution using FTS3 seems to be to use the original Songs table
and add SongsText table, automatically updated by triggers like:
CREATE TRIGGER update_songs UPDATE OF Artist,Album,SongTitle,Path ON Songs
BEGIN
UPDATE SongsText SET Artist=new.Artist, Album=new.Album,
SongTitle=new.Title, Path=new.Path WHERE rowid=new.id;
END;
This solution probably isn't bad, but according to my knowledge of FTS3, it
unnecessarily occupies some DB space (all text fields are actually stored
twice, once in Songs and once in SongsText).
Any ideas or recommedations?
Thanks,
Jiri
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Transaction Files / SQLite?
I looked into that, and there seems to be one problem: How to expand the
size of a blob? A write won't expand it, according to (my understanding) of
the docs.
- Sherief
> -Original Message-
> From: [EMAIL PROTECTED] [mailto:sqlite-users-
> [EMAIL PROTECTED] On Behalf Of Dennis Cote
> Sent: Wednesday, July 23, 2008 10:22 AM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] Transaction Files / SQLite?
>
> Sherief N. Farouk wrote:
> >
> > Streams are parallel 'bags of bytes' of a file. As in, you can
> > open("x.txt"). or open("x.txt:SomeStream"). Or open
> > ("x.txt:SomeOtherStream"). When you copy x.txt, the streams get
> copied with
> > it (assuming, of course, the destination filesystem is NTFS).
> >
> > TxF is simple: modifications to the file aren't visible to other apps
> till
> > you commit, and the commit is atomic: other processes see the file as
> either
> > before or after the transaction. Basically, I don't want the contents
> > written while another process is doing a read().
> >
> > Hope that made things clearer.
> >
>
> Yes, much clearer.
>
> It seems to me that you could use blobs and the blob I/O support in
> SQLite to implement the streams of one (or more files) in a single
> database file. The transactions and built in locking in SQLite should
> provide all the functionality you need to provide atomic multiple
> reader
> single writer access to the database file.
>
> Dennis Cote
>
>
> ___
> 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] Transaction Files / SQLite?
Sherief N. Farouk wrote:
>
> Streams are parallel 'bags of bytes' of a file. As in, you can
> open("x.txt"). or open("x.txt:SomeStream"). Or open
> ("x.txt:SomeOtherStream"). When you copy x.txt, the streams get copied with
> it (assuming, of course, the destination filesystem is NTFS).
>
> TxF is simple: modifications to the file aren't visible to other apps till
> you commit, and the commit is atomic: other processes see the file as either
> before or after the transaction. Basically, I don't want the contents
> written while another process is doing a read().
>
> Hope that made things clearer.
>
Yes, much clearer.
It seems to me that you could use blobs and the blob I/O support in
SQLite to implement the streams of one (or more files) in a single
database file. The transactions and built in locking in SQLite should
provide all the functionality you need to provide atomic multiple reader
single writer access to the database file.
Dennis Cote
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Ignoring "The"
Could you do something like select title from foo order by replace(title, 'The ', ''); ?? Or add a order_by column On Wed, Jul 23, 2008 at 8:49 AM, Sherief N. Farouk <[EMAIL PROTECTED]> wrote: > Can you define a custom less-than operator for sorting? If this were C++, > I'd do std::sort(Result.begin(), Results.end(), MyCustomOperator()); > > - Sherief > >> -Original Message- >> From: [EMAIL PROTECTED] [mailto:sqlite-users- >> [EMAIL PROTECTED] On Behalf Of Andrew Gatt >> Sent: Wednesday, July 23, 2008 9:41 AM >> To: General Discussion of SQLite Database >> Subject: Re: [sqlite] Ignoring "The" >> >> >> >> I have a table of music artist names which i'd like to output in >> order. >> >> Normally i just use: >> >> >> >> select * from artists order by artist_name; >> >> >> >> What i'd really like to do is order the artists by name but ignore >> any >> >> "the" or "the," preceding it. >> >> >> >> Any ideas? >> >> >> >> Thanks >> >> >> >> Andrew >> >> >> >> >> > Store it like "Beatles, The" them make the transformation latter, if >> > necessary? >> > >> > That's one idea. >> > >> > Best, >> > Daniel >> > >> > >> Thanks for the reply, I may be able to use this approach in future. But >> there are already many entries in the table and so an SQL statement >> that >> does it for me would be good! >> >> Andrew >> ___ >> 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 > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Ignoring "The"
Andrew Gatt wrote: > I have a table of music artist names which i'd like to output in order. > Normally i just use: > > select * from artists order by artist_name; > > What i'd really like to do is order the artists by name but ignore any > "the" or "the," preceding it. > You could try something like this: select * from t order by case when lower(substr(artist, 1, 3)) = 'the' then substr(artist, 4) when lower(substr(artist, 1, 4)) = 'the,' then substr(artist, 5) else artist end; HTH Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] error:unable to open database file
kriscbe wrote: > > i am getting new problem while executing my sqlite3 using c++ after some no > of operations on db file > i t gives error "unable to open database file" > This is probably another case of a misleading error message. SQLite may be trying to open a temporary file, not your database file. You can check the temp store settings with a couple of pragma commands. pragma temp_store; pragam temp_store_directory; See http://www.sqlite.org/pragma.html#modify for details about these pragma commands. SQLite needs to have permission to create files in that directory. HTH Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Transaction Files / SQLite?
In a nutshell:
Streams are parallel 'bags of bytes' of a file. As in, you can
open("x.txt"). or open("x.txt:SomeStream"). Or open
("x.txt:SomeOtherStream"). When you copy x.txt, the streams get copied with
it (assuming, of course, the destination filesystem is NTFS).
TxF is simple: modifications to the file aren't visible to other apps till
you commit, and the commit is atomic: other processes see the file as either
before or after the transaction. Basically, I don't want the contents
written while another process is doing a read().
Hope that made things clearer.
- Sherief
> -Original Message-
> From: [EMAIL PROTECTED] [mailto:sqlite-users-
> [EMAIL PROTECTED] On Behalf Of Dennis Cote
> Sent: Wednesday, July 23, 2008 9:46 AM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] Transaction Files / SQLite?
>
> Sherief N. Farouk wrote:
> > I'm currently trying to port an application that does a lot of work
> based on
> > two NTFS features: Streams, and Transactional NTFS. Since TxF is a
> HUGE
> > feature, with a lot of potential pitfalls, I thought I might do this
> as a
> > layer over SQLite: a db file with one table, multiple rows: one for
> each
> > stream. I'd love to hear what everyone thinks, and if there's a more
> > obvious/performance oriented way to do this. How should I go around
> storing
> > the stream data, as the files are not text (and indeed, lots of NULLs
> lie
> > within). I'll mostly need to read/write sub-regions of the file from
> > multiple apps, all running at the same time.
> >
>
> That was pretty vague, at least to me.
>
> Can you explain what NTFS streams and transactions do and how you use
> them? Can you tell us how your application uses these features?
>
> Without more information about what you are trying to do I don't think
> I
> can comment on the efficacy of using SQLite.
>
> Dennis Cote
> ___
> 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] Ignoring "The"
Can you define a custom less-than operator for sorting? If this were C++, I'd do std::sort(Result.begin(), Results.end(), MyCustomOperator()); - Sherief > -Original Message- > From: [EMAIL PROTECTED] [mailto:sqlite-users- > [EMAIL PROTECTED] On Behalf Of Andrew Gatt > Sent: Wednesday, July 23, 2008 9:41 AM > To: General Discussion of SQLite Database > Subject: Re: [sqlite] Ignoring "The" > > > >> I have a table of music artist names which i'd like to output in > order. > >> Normally i just use: > >> > >> select * from artists order by artist_name; > >> > >> What i'd really like to do is order the artists by name but ignore > any > >> "the" or "the," preceding it. > >> > >> Any ideas? > >> > >> Thanks > >> > >> Andrew > >> > >> > > Store it like "Beatles, The" them make the transformation latter, if > > necessary? > > > > That's one idea. > > > > Best, > > Daniel > > > > > Thanks for the reply, I may be able to use this approach in future. But > there are already many entries in the table and so an SQL statement > that > does it for me would be good! > > Andrew > ___ > 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] Transaction Files / SQLite?
Sherief N. Farouk wrote: > I'm currently trying to port an application that does a lot of work based on > two NTFS features: Streams, and Transactional NTFS. Since TxF is a HUGE > feature, with a lot of potential pitfalls, I thought I might do this as a > layer over SQLite: a db file with one table, multiple rows: one for each > stream. I'd love to hear what everyone thinks, and if there's a more > obvious/performance oriented way to do this. How should I go around storing > the stream data, as the files are not text (and indeed, lots of NULLs lie > within). I'll mostly need to read/write sub-regions of the file from > multiple apps, all running at the same time. > That was pretty vague, at least to me. Can you explain what NTFS streams and transactions do and how you use them? Can you tell us how your application uses these features? Without more information about what you are trying to do I don't think I can comment on the efficacy of using SQLite. Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Ignoring "The"
>> I have a table of music artist names which i'd like to output in order. >> Normally i just use: >> >> select * from artists order by artist_name; >> >> What i'd really like to do is order the artists by name but ignore any >> "the" or "the," preceding it. >> >> Any ideas? >> >> Thanks >> >> Andrew >> >> > Store it like "Beatles, The" them make the transformation latter, if > necessary? > > That's one idea. > > Best, > Daniel > > Thanks for the reply, I may be able to use this approach in future. But there are already many entries in the table and so an SQL statement that does it for me would be good! Andrew ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Ignoring "The"
Andrew Gatt wrote: > I have a table of music artist names which i'd like to output in order. > Normally i just use: > > select * from artists order by artist_name; > > What i'd really like to do is order the artists by name but ignore any > "the" or "the," preceding it. > > Any ideas? > > Thanks > > Andrew > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > Store it like "Beatles, The" them make the transformation latter, if necessary? That's one idea. Best, Daniel ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Ignoring "The"
I have a table of music artist names which i'd like to output in order. Normally i just use: select * from artists order by artist_name; What i'd really like to do is order the artists by name but ignore any "the" or "the," preceding it. Any ideas? Thanks Andrew ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite under c++
Do something like this
sqlite3_stmt* pStatement;
vector vResults;
int nError = sqlite3_prepare_v2(pHandle,"SELECT col1 FROM
table",-1,,NULL);
while (sqlite3_step(pStatement) == SQLITE_ROW)
{
vResults.push_back((char*)sqlite3_column_text(pStatement, 0));
}
sqlite3_finalize(pStatement);
-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Juzbrig
Sent: 23 July 2008 13:31
To: sqlite-users@sqlite.org
Subject: [sqlite] SQLite under c++
Hi.
I am new in sqlite (before I have mysql + php experience). I have a question
If my DB is alredy open
sqlite3* handle;
const char* baza = "cols.db3" ;
char *zErrMsg = 0;
sqlite3_open(test_baza,);
How can I get the results of SQL "SELECT * FROM" into a string table or any
c++ structure ?
sqlite3_exec() doesn't seem to return any strings/chars.
If anyone could write me that in code I would be grateful.
--
View this message in context:
http://www.nabble.com/SQLite-under-c%2B%2B-tp18609682p18609682.html
Sent from the SQLite mailing list archive at Nabble.com.
___
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
[sqlite] SQLite under c++
Hi. I am new in sqlite (before I have mysql + php experience). I have a question If my DB is alredy open sqlite3* handle; const char* baza = "cols.db3" ; char *zErrMsg = 0; sqlite3_open(test_baza,); How can I get the results of SQL "SELECT * FROM" into a string table or any c++ structure ? sqlite3_exec() doesn't seem to return any strings/chars. If anyone could write me that in code I would be grateful. -- View this message in context: http://www.nabble.com/SQLite-under-c%2B%2B-tp18609682p18609682.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to emulate generate_series function?
В сообщении от Wednesday 23 July 2008 15:42:04 Igor Tandetnik написал(а):
> "Alexey Pechnikov" <[EMAIL PROTECTED]> wrote in
> message news:[EMAIL PROTECTED]
>
> > select * from direction_telephony
> > where prefix in
> > ('78312604812','7831260481','783126048','78312604','7831260','783126','78
> >312','7831','783','78','7') order by length(prefix) desc
> > limit 1;
>
> select * from direction_telephony
> where prefix != '' and '78312604812' LIKE prefix || '%'
> order by length(prefix) desc
> limit 1;
>
> Or
>
> where prefix=substr('78312604812', 1, length(prefix))
>
> Igor Tandetnik
And how about indicies?
sqlite> explain query plan
...> select * from direction_telephony
...> where prefix in
('78312604812','7831260481','783126048','78312604','7831260','783126','78312','7831','783','78','7')
...> order by length(prefix) desc
...> limit 1;
0|0|TABLE direction_telephony WITH INDEX direction_telephony_prefix
sqlite> explain query plan
...> select * from direction_telephony
...> where prefix in
(substr('78312604812',1,1),substr('78312604812',1,2),substr('78312604812',1,3),
...>
substr('78312604812',1,4),substr('78312604812',1,5),substr('78312604812',1,6),substr('78312604812',1,7),
...>
substr('78312604812',1,8),substr('78312604812',1,9),substr('78312604812',1,10),substr('78312604812',1,11),
...>
substr('78312604812',1,12),substr('78312604812',1,13),substr('78312604812',1,14),substr('78312604812',1,15))
...> order by length(prefix) desc
...> limit 1;
0|0|TABLE direction_telephony WITH INDEX direction_telephony_prefix
sqlite>
sqlite> explain query plan
...> select * from direction_telephony
...> where prefix != '' and '78312604812' LIKE prefix || '%'
...> order by length(prefix) desc
...> limit 1;
0|0|TABLE direction_telephony
sqlite>
sqlite> explain query plan
...> select * from direction_telephony
...> where prefix=substr('78312604812', 1, length(prefix))
...> order by length(prefix) desc
...> limit 1;
0|0|TABLE direction_telephony
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to emulate generate_series function?
I think you are asking about 'table functions', which are functions
that return a rowset and are used in place of a table to generate rows.
See: http://www.postgresql.org/docs/7.3/static/xfunc-tablefunctions.html
To my knowledge this is not supported in sqlite, except perhaps via
virtual table, tho it is not clear to me how you would pass arguments
via this api.
I would love to have a nice simple interface for table functions in
sqlite. If it already exists, I also would like a pointer to the
documentation/examples.
On Jul 23, 2008, at 4:38 AM, Alexey Pechnikov wrote:
> Hello!
>
> How can I emulate PostreSQL function select generate_series?
>
> ==
> Example:
> select generate_series(1,7);
> 1
> 2
> 3
> 4
> 5
> 6
> 7
>
> ==
> My task is this:
>
> create table direction_telephony (
> group_name text not null,
> name text not null,
> class text not null,
> prefix text not null,
> price real not null,
> currency text not null default 'RUB'
> );
>
> insert into direction_telephony values ('Globus
> daily', 'Russia','','7','3.0','RUB');
> insert into direction_telephony values ('Globus daily', 'N.Novgorod
> Region','','7831','2.0','RUB');
> insert into direction_telephony values ('Globus
> daily', 'N.Novgorod','','78312','1.0','RUB');
>
> select * from direction_telephony
> where prefix in
> ('78312604812','7831260481','783126048','78312604','7831260','783126','
> 78312','7831','783','78','7')
> order by length(prefix) desc
> limit 1;
>
> Globus daily|N.Novgorod||78312|1.0|RUB
>
> With generate_series function I can generate
> condition
> "('78312604812','7831260481','783126048','78312604','7831260','783126',
> '78312','7831','783','78','7')"
> inside query.
>
> select substr('78312604812',1,x) from
> generate_series(1,length('78312604812'))
> as x;
>
> "7"
> "78"
> "783"
> "7831"
> "78312"
> "783126"
> "7831260"
> "78312604"
> "783126048"
> "7831260481"
> "78312604812"
>
>
> Best regards, Alexey.
> ___
> 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] How to emulate generate_series function?
"Alexey Pechnikov" <[EMAIL PROTECTED]> wrote in
message news:[EMAIL PROTECTED]
> select * from direction_telephony
> where prefix in
> ('78312604812','7831260481','783126048','78312604','7831260','783126','78312','7831','783','78','7')
> order by length(prefix) desc
> limit 1;
select * from direction_telephony
where prefix != '' and '78312604812' LIKE prefix || '%'
order by length(prefix) desc
limit 1;
Or
where prefix=substr('78312604812', 1, length(prefix))
Igor Tandetnik
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] error:unable to open database file
hi everyone, i am getting new problem while executing my sqlite3 using c++ after some no of operations on db file i t gives error "unable to open database file" actually my application is every time update a class members and write in to DB once and read the updated field from DB. it is done for every 1 second.after some time(after 37 seconds).i got this error. in some forums said that the error due to database file permissions. i changed my database file permissions using "chmod -R 777 test.db" is it correct? how can i solve this?? thanks kriscbe -- View this message in context: http://www.nabble.com/error%3Aunable-to-open-database-file-tp18608770p18608770.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] How to emulate generate_series function?
Hello!
How can I emulate PostreSQL function select generate_series?
==
Example:
select generate_series(1,7);
1
2
3
4
5
6
7
==
My task is this:
create table direction_telephony (
group_name text not null,
name text not null,
class text not null,
prefix text not null,
price real not null,
currency text not null default 'RUB'
);
insert into direction_telephony values ('Globus
daily', 'Russia','','7','3.0','RUB');
insert into direction_telephony values ('Globus daily', 'N.Novgorod
Region','','7831','2.0','RUB');
insert into direction_telephony values ('Globus
daily', 'N.Novgorod','','78312','1.0','RUB');
select * from direction_telephony
where prefix in
('78312604812','7831260481','783126048','78312604','7831260','783126','78312','7831','783','78','7')
order by length(prefix) desc
limit 1;
Globus daily|N.Novgorod||78312|1.0|RUB
With generate_series function I can generate
condition
"('78312604812','7831260481','783126048','78312604','7831260','783126','78312','7831','783','78','7')"
inside query.
select substr('78312604812',1,x) from generate_series(1,length('78312604812'))
as x;
"7"
"78"
"783"
"7831"
"78312"
"783126"
"7831260"
"78312604"
"783126048"
"7831260481"
"78312604812"
Best regards, Alexey.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] request for information
Hi all, and DRH : I am the author of sqlite3Explorer, a windows Gui management tool. One of the users of this program has contacted me wrt to supporting encrypted databases. in the past, Sqlite3Explorer has supported encrypted databases (version 2 of sqlite), even though I do not own a copy of the encrypted source (see), because (in the past, at least), it was not necessary, as long as the sqlite3.dll was compiled with SEE enabled. I just made the proper calls to sqlite3_key, and it worked. I am now told that this does not work anymore, and I assume that something has changed between version 2 and version 3 of sqlite, which breaks my implementation. It fails with an error 21 (Improper use of API). I would like to fix this, but I am unable to do so, unless I have access to (some part) of the commercial version, and an understanding of the encryption initialization (key format, how to select which of the 4 encryption methods is used, etc). This request is aimed mainly towards DRH : is it possible to send me the documentation (as a minimum) of the SEE extension (and CEROD, if relevant), so that I can implement the corect calling sequence to open such databases ? The alternative would be for me to buy the source for these, which is a little over my budget for now :) I would like to assure you that I personally have no use for encrypted databases, I am only asking for this in order to support people that use my (freeware) utility... of course, if you are feeling generous, you could also consider giving me a free copy :), since I *do* contribute to sqlite, at least indirectly. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Transaction Files / SQLite?
I'm currently trying to port an application that does a lot of work based on two NTFS features: Streams, and Transactional NTFS. Since TxF is a HUGE feature, with a lot of potential pitfalls, I thought I might do this as a layer over SQLite: a db file with one table, multiple rows: one for each stream. I'd love to hear what everyone thinks, and if there's a more obvious/performance oriented way to do this. How should I go around storing the stream data, as the files are not text (and indeed, lots of NULLs lie within). I'll mostly need to read/write sub-regions of the file from multiple apps, all running at the same time. Thanks, - Sherief ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
