Re: [sqlite] Stemming Query

[Mike King]

Thanks for a quick answer.

On Tuesday, 12 February 2013, Richard Hipp wrote:

> On Tue, Feb 12, 2013 at 5:50 PM, Mike King 
> >
> wrote:
>
> > Is there any way to turn stemming on/off for a FTS3 table? I create my
> FTS3
> > table as follows:
> >
> > CREATE VIRTUAL TABLE FreeText USING fts3(Content TEXT NOT NULL,
> > tokenize=porter)
> >
> > I'd like to be able to offer the option of whether to use stemming when
> > searching (much like some other freetext databases do) as stemming is
> great
> > for words and text but not so good for invoice or reference numbers etc.
> >
> > The only way I could think of doing it is by having two freetext tables -
> > one with stemming and one without but this is less than ideal because of
> > duplication,
> >
> > Anyone got any ideas?
> >
>
> The index is over the stemmed words.  So if you change the stemmer (or turn
> if off completely) then you have to recreate the index.
>
> So, yes, the only way to do this is to have two tables, one with stemming
> and the other without.
>
> --
> D. Richard Hipp
> d...@sqlite.org 
> ___
> 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] Stemming Query

[Richard Hipp]

On Tue, Feb 12, 2013 at 5:50 PM, Mike King  wrote:

> Is there any way to turn stemming on/off for a FTS3 table? I create my FTS3
> table as follows:
>
> CREATE VIRTUAL TABLE FreeText USING fts3(Content TEXT NOT NULL,
> tokenize=porter)
>
> I'd like to be able to offer the option of whether to use stemming when
> searching (much like some other freetext databases do) as stemming is great
> for words and text but not so good for invoice or reference numbers etc.
>
> The only way I could think of doing it is by having two freetext tables -
> one with stemming and one without but this is less than ideal because of
> duplication,
>
> Anyone got any ideas?
>

The index is over the stemmed words.  So if you change the stemmer (or turn
if off completely) then you have to recreate the index.

So, yes, the only way to do this is to have two tables, one with stemming
and the other without.

-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Stemming Query

[Mike King]

Is there any way to turn stemming on/off for a FTS3 table? I create my FTS3
table as follows:

CREATE VIRTUAL TABLE FreeText USING fts3(Content TEXT NOT NULL,
tokenize=porter)

I'd like to be able to offer the option of whether to use stemming when
searching (much like some other freetext databases do) as stemming is great
for words and text but not so good for invoice or reference numbers etc.

The only way I could think of doing it is by having two freetext tables -
one with stemming and one without but this is less than ideal because of
duplication,

Anyone got any ideas?

Cheers,
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite 4

[Richard Hipp]

On Tue, Feb 12, 2013 at 11:34 AM, Marc L. Allen  wrote:

> Thanks.  Any clue on whether we'll use an amalgamation as with SQLite4 or
> direct sources?
>

Amalgamation.

-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite 4

[Marc L. Allen]

Thanks.  Any clue on whether we'll use an amalgamation as with SQLite4 or 
direct sources?

Marc

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Richard Hipp
Sent: Tuesday, February 12, 2013 11:32 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] SQLite 4

On Tue, Feb 12, 2013 at 11:26 AM, Marc L. Allen  wrote:

> I'm sorry if this isn't the right group, but an earlier message 
> mentioned it, and I found some stuff on the SQLite website.
>
> Although I've had a long-standing project to incorporate SQLite into 
> our product (and have done so), it has never been deployed.  I like 
> the looks of SQLite 4, but have not been able to find any proposed release 
> schedules.
>
> Can someone point me to an appropriate page or let me know if an 
> official release is planned anytime soon?  Our platform is 
> proprietary, so I'll need to be incorporating source, not binaries.
>

There is no planned release schedule for SQLite4 at this time.  It seems to be 
working pretty well, but it is not nearly as well-tested as is SQLite3.
And there are more interface changes that we want to make before it goes into 
any kind of official release.
--
D. Richard Hipp
d...@sqlite.org
___
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] SQLite 4

[Richard Hipp]

On Tue, Feb 12, 2013 at 11:26 AM, Marc L. Allen  wrote:

> I'm sorry if this isn't the right group, but an earlier message mentioned
> it, and I found some stuff on the SQLite website.
>
> Although I've had a long-standing project to incorporate SQLite into our
> product (and have done so), it has never been deployed.  I like the looks
> of SQLite 4, but have not been able to find any proposed release schedules.
>
> Can someone point me to an appropriate page or let me know if an official
> release is planned anytime soon?  Our platform is proprietary, so I'll need
> to be incorporating source, not binaries.
>

There is no planned release schedule for SQLite4 at this time.  It seems to
be working pretty well, but it is not nearly as well-tested as is SQLite3.
And there are more interface changes that we want to make before it goes
into any kind of official release.
-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] SQLite 4

[Marc L. Allen]

I'm sorry if this isn't the right group, but an earlier message mentioned it, 
and I found some stuff on the SQLite website.

Although I've had a long-standing project to incorporate SQLite into our 
product (and have done so), it has never been deployed.  I like the looks of 
SQLite 4, but have not been able to find any proposed release schedules.

Can someone point me to an appropriate page or let me know if an official 
release is planned anytime soon?  Our platform is proprietary, so I'll need to 
be incorporating source, not binaries.

Thanks,

Marc

--
**
* *  *
* Marc L. Allen   *  "... so many things are *
* *  possible just as long as you*
* Outsite Networks, Inc.  *  don't know they're impossible." *
* (757) 853-3000 #215 *  *
* *  *
* mlal...@outsitenetworks.com *   -- 
The Phantom Tollbooth   *
* *  *
**

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to prevent View sqlite database structure and contents from database browsers

[Simon Slavin]

On 12 Feb 2013, at 3:24pm, Stephen Chrzanowski  wrote:

> If you have a kind of information that you want to protect the user from
> getting access to, this is *NOT* the proper manor of doing so.  This will
> make it more interesting for an (ab)user to get to the file, however,
> anyone with sufficient rights can just take ownership of the file, change
> the file permissions, and still gain access to the file.

Right.  Even if reading the raw database file is no help, if you use encryption 
at the field level all they need to do is use a SQLite utility to replace the 
encrypted password for an admin account with the encrypted password from their 
own account.

If you want to prevent users from getting at the database file as a file, put 
the database file somewhere where they can't see it at all, and use a server to 
feed them the data from it.  That's your only protection.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Fwd: How to prevent View sqlite database structure and contents from database browsers

[Stephen Chrzanowski]

Being a windows developer myself, I've never really looked into locking
down an application at the file level, however, here are a couple things
that you can look into.

You can create a user from the command line.  Instructions here:
http://www.itechtalk.com/thread16618.html
(google: create user command line)

You can probably skip on setting the user as an administrator, however,
this will become pretty key as, again from the command line, you can set
file permissions on your database so that this new user has access to the
file only.
http://www.techrepublic.com/article/use-caclsexe-to-view-and-manage-windows-acls/1050976
(google: windows cacls)

In whatever language you're running, you SHOULD be able to access a file as
that new user.  Take for instance
http://www.djekldevelopments.co.uk/microsoft-visual-basic-net-programmers-cookbook/source/6797final/lib0319.html
(google: windows api run program as different user)

But PLEASE take heed;

If you have a kind of information that you want to protect the user from
getting access to, this is *NOT* the proper manor of doing so.  This will
make it more interesting for an (ab)user to get to the file, however,
anyone with sufficient rights can just take ownership of the file, change
the file permissions, and still gain access to the file.  In reality,
SQLite PROBABLY isn't the right answer for you to begin with since you DO
want to lock down the access.

But then again, you also need to decide at what level you want to protect
the data.  If you're looking for a total, 100% secure method of protecting
your data, you manage it in-house, and your clients access it remotely, or
in my case where I work, the customer buys the machines to our
specifications, we install our software on them in our office, ship them
off after some burn in time, and then we manage them remotely, and the user
has zero access to the machine aside from what we provide them with our
software.


On Mon, Feb 11, 2013 at 7:03 AM, Robert Hairgrove wrote:

> On Mon, 2013-02-11 at 11:04 +, SR Volatile wrote:
> > Is there any option to encrypt fields in a table when creating it?
>
> There is the SQLite Encryption Extension (SEE):
> http://www.hwaci.com/sw/sqlite/see.html
>
> It is not free. But it looks like it does a pretty good job (I never
> used it myself).
>
> Other than that, you can encrypt/decrypt the data in your application
> using some 3rd party encryption software. Depending on your security
> needs, please be aware that no encryption mechanism is 100% safe, and to
> achieve an adequate level of security through encryption is anything but
> trivial.
>
> Of course, my suggestion to make the file read-only does not prohibit
> users from reading the data, only from editing it. You'd have to deny
> read AND write access to any users except for those using the database
> to prevent all prying eyes.
>
> Although setting up such permissions is fairly straightforward on a *nix
> system, I believe that more recent versions of Windows allow user-level
> permissions as well.
>
> And I still think this is probably the best way to go with any
> file-based RDBMS because once the file-system security is breached, the
> database file can be copied and a dedicated intruder can hack away at
> leisure to remove whatever encryption there is.
>
> ___
> 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] Saving ubyte[] zlib data in sqlite and calling it back

[jose isaias cabrera]

Richard Hipp wrote...


On Mon, Feb 11, 2013 at 1:45 PM, jose isaias cabrera
wrote:



Greetings.

I have this table,

CREATE TABLE Test (login primary key, password);

and I would like to save zlib data using ubyte[] and also call it back 
in.



Are you trying to store zlib-compressed content in the database file, then
read back the original uncompressed content?  If so, then I suggest adding
two application-defined functions compress() and decompress():

   UPDATE test SET password=compress(password) WHERE login='test';

   SELECT decompress(password) FROM test WHERE login='test';

Sample implementations for the compress() and decompress() functions can 
be

copied from here:

   http://www.fossil-scm.org/fossil/artifact/a59638aa4c0?ln=53-105

The code for registering these application-defined functions with the
SQLite database connection is seen here:

   http://www.fossil-scm.org/fossil/artifact/a59638aa4c0?ln=119-122


Dr. Hipp, I don't care what Kees or Igor say about you.  You're pretty hip. 
This is perfect, thanks.


josé

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] help with select

[e-mail mgbg25171]

Yes that works great. Thanks Igor!

On 11 February 2013 22:24, Igor Tandetnik  wrote:

> On 2/11/2013 10:45 AM, e-mail mgbg25171 wrote:
>
>> Perhaps I'm making a meal of my question...
>> "All" I want to do is
>> select only records that have field f1 = ''  where it's other field f2
>> matches the value of f3 only in other records (1 or more) which have a
>> non-'' f1 value.
>>
>
> select * from MyTable where f1 = '' and f2 in
>   (select f3 from MyTable where f1 != '');
>
> --
> 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