Re: [sqlite] Simple Search using LIKE or something else

[Stephen Chrzanowski]

Thanks Warren.  I'll bookmark this and have a read over it maybe this
weekend.  (Busy work week, and I'd just rather chill with Factorio,
The Crew, or some other thing that lowers the stress. :] )

On Tue, Nov 14, 2017 at 10:58 AM, Warren Young  wrote:
> On Nov 13, 2017, at 3:26 PM, Stephen Chrzanowski  wrote:
>>
>> I'm not using a C compiler.
>
> This article appears to sort that out:
>
> http://rvelthuis.de/articles/articles-cobjs.html
>
> Basically, you download the free CLI-only version of the C++Builder Berlin 
> compiler, build sqlite3.c to sqlite3.obj — possibly along with other C code 
> of your own, plus extensions — and then link that into your Delphi project 
> using the instructions provided.
>
> Then no worries about random DLLs floating around.
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Simple Search using LIKE or something else

[Warren Young]

On Nov 13, 2017, at 3:26 PM, Stephen Chrzanowski  wrote:
> 
> I'm not using a C compiler.

This article appears to sort that out:

http://rvelthuis.de/articles/articles-cobjs.html

Basically, you download the free CLI-only version of the C++Builder Berlin 
compiler, build sqlite3.c to sqlite3.obj — possibly along with other C code of 
your own, plus extensions — and then link that into your Delphi project using 
the instructions provided.

Then no worries about random DLLs floating around.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Simple Search using LIKE or something else

[Stephen Chrzanowski]

I'm not using a C compiler.  Delphi Berlin doesn't compile C, AFAIK.
I'll play around with that tonight as I've got a project I need to
start up tonight.

Third party libraries include those like LiteDAC, or DISQLite3, or
Synopse mORMot framework.


On Mon, Nov 13, 2017 at 1:34 PM, Jens Alfke  wrote:
>
>
>> On Nov 11, 2017, at 3:41 PM, Stephen Chrzanowski  wrote:
>>
>> Although there are pay for, and free to use, libraries that can be
>> linked into my applications, I've never been satisfied with the code
>> provided.
>
> Hm, I didn’t say anything about 3rd party libraries, so I’m not sure what 
> this is a response to.
>
>> I've tried taking the amalgamation and convert it into something that
>> I can directly embed into my applications (Which would be great), but,
>> I've yet to be successful.
>
> Just add sqlite3.c as a source file in your project/makefile/whatever.
>
> —Jens
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Simple Search using LIKE or something else

[Jens Alfke]

> On Nov 11, 2017, at 3:41 PM, Stephen Chrzanowski  wrote:
> 
> Although there are pay for, and free to use, libraries that can be
> linked into my applications, I've never been satisfied with the code
> provided.

Hm, I didn’t say anything about 3rd party libraries, so I’m not sure what this 
is a response to.

> I've tried taking the amalgamation and convert it into something that
> I can directly embed into my applications (Which would be great), but,
> I've yet to be successful.

Just add sqlite3.c as a source file in your project/makefile/whatever.

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

Re: [sqlite] Simple Search using LIKE or something else

[Stephen Chrzanowski]

Thanks all for the info.  I'm still doing thought wrestling on the
path I want to take.

On one hand, its brain dead simple for me to go the tokenizer route
and have my application dynamically generate the SQL string, I just
don't like that kind of code style as there are many faults with it,
especially with how the wrapper deals with binding and user provided
data.

On the other hand, since at least one of my dev environments has the
tools already to do a Windows build of the amalgamation, I can write
out a batch file so that the DLL is built on a whim, with the required
extensions at the ready.  The magic touch is to find out how to get my
source code in the wrapper to handle the new kind of DLL naming (Since
I don't want a generic SQLite3.dll being in multiple places that do
different jobs) while the exact same wrapper code CAN handle
SQLite3.dll.  Much more work, but later on, might be useful for other
aspects.  The last rabbit out of the hat is to modify the installer to
put the new DLL in the application directory instead of
System32/Wherever, but that's easier to correct than doing the
tokenizer.

@Jens;
Although there are pay for, and free to use, libraries that can be
linked into my applications, I've never been satisfied with the code
provided.  Often times, it has so many dependencies on stuff that
isn't required that I'm just not going to put into my code.  Not to
mention, the routines that they provide are limited in some fashion
that just isn't compatible with my mindset.  The one library I did
find ( 
http://www.itwriting.com/blog/articles/a-simple-delphi-wrapper-for-sqlite-3
), and was easily able to modify for my own purposes, however,
requires external DLLs.

I've tried taking the amalgamation and convert it into something that
I can directly embed into my applications (Which would be great), but,
I've yet to be successful.  Obviously it can be done, but, I've not
been able to do it on my own.

On Fri, Nov 10, 2017 at 8:03 PM, Jens Alfke  wrote:
>
>
>> On Nov 10, 2017, at 6:50 AM, Peter Da Silva  
>> wrote:
>>
>>> I'm using the amalgamation by default, so I don't think FTS is in the DLL.  
>>> I can recompile, but that now introduces complications on machines that may 
>>> not have this exact DLL.  (A couple people in my company use this app)
>>
>> The same problem would presumably apply to the regex extension... not all 
>> users would have that extension.
>
> What platform is this for? Apple platforms ship with SQLite libraries that 
> include FTS (not sure about regex, but it’d be easy to check.)
>
> On other platforms, you can just statically link SQLite into your application 
> and not have to worry what version or configuration might be on the user’s 
> machine.
>
> —Jens
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Simple Search using LIKE or something else

[Jens Alfke]

> On Nov 10, 2017, at 6:50 AM, Peter Da Silva  
> wrote:
> 
>> I'm using the amalgamation by default, so I don't think FTS is in the DLL.  
>> I can recompile, but that now introduces complications on machines that may 
>> not have this exact DLL.  (A couple people in my company use this app)
> 
> The same problem would presumably apply to the regex extension... not all 
> users would have that extension.

What platform is this for? Apple platforms ship with SQLite libraries that 
include FTS (not sure about regex, but it’d be easy to check.)

On other platforms, you can just statically link SQLite into your application 
and not have to worry what version or configuration might be on the user’s 
machine.

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

Re: [sqlite] Simple Search using LIKE or something else

[Peter Da Silva]

Looking back through the thread, from the original post:

> I'm using the amalgamation by default, so I don't think FTS is in the DLL.  I 
> can recompile, but that now introduces complications on machines that may not 
> have this exact DLL.  (A couple people in my company use this app)

The same problem would presumably apply to the regex extension... not all users 
would have that extension.


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

Re: [sqlite] Simple Search using LIKE or something else

[Richard Hipp]

On 11/10/17, Stephen Chrzanowski  wrote:
> I'd take regular expressions, sure, but, how do you get SQLite syntax
> to take that on?

Load the regexp extension here
(https://www.sqlite.org/src/artifact/a68d25c659bd2d89) or one of the
other 3rd-party regexp extensions that you can find on the internet.
Then use the

 column REGEXP $pattern

operator.

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

Re: [sqlite] Simple Search using LIKE or something else

[Don V Nielsen]

A good StackOverflow post on adapting in C# for use in Linq queries:
https://stackoverflow.com/questions/24229785/sqlite-net-sqlitefunction-not-working-in-linq-to-sql/26155359#26155359


On Fri, Nov 10, 2017 at 8:31 AM, Don V Nielsen 
wrote:

> Brent Ashley has a nice blog post on integrating your languages regular
> express processing into sqlite: http://www.ashleyit.
> com/blogs/brentashley/2013/11/27/using-regular-expressions-with-sqlite/
>
>
> On Fri, Nov 10, 2017 at 8:15 AM, Stephen Chrzanowski 
> wrote:
>
>> I'd take regular expressions, sure, but, how do you get SQLite syntax
>> to take that on?
>>
>> On Fri, Nov 10, 2017 at 8:45 AM, Don V Nielsen 
>> wrote:
>> > Assuming you are looking for "Abc" or "Def" anywhere in the argument,
>> how
>> > about regular expression? (Abc|Def)
>> >
>> > On Thu, Nov 9, 2017 at 3:00 PM, Peter Da Silva <
>> > peter.dasi...@flightaware.com> wrote:
>> >
>> >> On 11/9/17, 2:51 PM, "sqlite-users on behalf of Stephen Chrzanowski" <
>> >> sqlite-users-boun...@mailinglists.sqlite.org on behalf of
>> >> pontia...@gmail.com> wrote:
>> >> > I've added a simple filter function that uses SQLites LIKE operator,
>> but
>> >> I'd like something a little bit more advanced.  Right now, the SQL
>> code is
>> >> like:
>> >> >
>> >> > select * from Events where Title like '%Abc%Def%'
>> >> >
>> >> >  This works if the Title is AbcRfeDef, but would fail with FedRfeAbc.
>> >> >
>> >> > I've thought about doing some kind of delimiter, then have the code
>> >> generate the SQL code by just looping through the keywords and
>> generate the
>> >> "or Title like '%keyword%'" statement (With appropriate escaping), but
>> that
>> >> just smells bad to me.
>> >>
>> >> The best way to do it is not to use complex keys, but if you have to
>> (say
>> >> because the input is free form) then generating
>> >>
>> >> SELECT * FROM EVENTS WHERE title LIKE ‘%Abc%’ AND title LIKE ‘%Def%’;
>> >>
>> >> is probably the only option.
>> >>
>> >>
>> >> ___
>> >> sqlite-users mailing list
>> >> sqlite-users@mailinglists.sqlite.org
>> >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>> >>
>> > ___
>> > sqlite-users mailing list
>> > sqlite-users@mailinglists.sqlite.org
>> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>> ___
>> sqlite-users mailing list
>> sqlite-users@mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Simple Search using LIKE or something else

[Don V Nielsen]

Brent Ashley has a nice blog post on integrating your languages regular
express processing into sqlite:
http://www.ashleyit.com/blogs/brentashley/2013/11/27/using-regular-expressions-with-sqlite/


On Fri, Nov 10, 2017 at 8:15 AM, Stephen Chrzanowski 
wrote:

> I'd take regular expressions, sure, but, how do you get SQLite syntax
> to take that on?
>
> On Fri, Nov 10, 2017 at 8:45 AM, Don V Nielsen 
> wrote:
> > Assuming you are looking for "Abc" or "Def" anywhere in the argument, how
> > about regular expression? (Abc|Def)
> >
> > On Thu, Nov 9, 2017 at 3:00 PM, Peter Da Silva <
> > peter.dasi...@flightaware.com> wrote:
> >
> >> On 11/9/17, 2:51 PM, "sqlite-users on behalf of Stephen Chrzanowski" <
> >> sqlite-users-boun...@mailinglists.sqlite.org on behalf of
> >> pontia...@gmail.com> wrote:
> >> > I've added a simple filter function that uses SQLites LIKE operator,
> but
> >> I'd like something a little bit more advanced.  Right now, the SQL code
> is
> >> like:
> >> >
> >> > select * from Events where Title like '%Abc%Def%'
> >> >
> >> >  This works if the Title is AbcRfeDef, but would fail with FedRfeAbc.
> >> >
> >> > I've thought about doing some kind of delimiter, then have the code
> >> generate the SQL code by just looping through the keywords and generate
> the
> >> "or Title like '%keyword%'" statement (With appropriate escaping), but
> that
> >> just smells bad to me.
> >>
> >> The best way to do it is not to use complex keys, but if you have to
> (say
> >> because the input is free form) then generating
> >>
> >> SELECT * FROM EVENTS WHERE title LIKE ‘%Abc%’ AND title LIKE ‘%Def%’;
> >>
> >> is probably the only option.
> >>
> >>
> >> ___
> >> sqlite-users mailing list
> >> sqlite-users@mailinglists.sqlite.org
> >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >>
> > ___
> > sqlite-users mailing list
> > sqlite-users@mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Simple Search using LIKE or something else

[Stephen Chrzanowski]

I'd take regular expressions, sure, but, how do you get SQLite syntax
to take that on?

On Fri, Nov 10, 2017 at 8:45 AM, Don V Nielsen  wrote:
> Assuming you are looking for "Abc" or "Def" anywhere in the argument, how
> about regular expression? (Abc|Def)
>
> On Thu, Nov 9, 2017 at 3:00 PM, Peter Da Silva <
> peter.dasi...@flightaware.com> wrote:
>
>> On 11/9/17, 2:51 PM, "sqlite-users on behalf of Stephen Chrzanowski" <
>> sqlite-users-boun...@mailinglists.sqlite.org on behalf of
>> pontia...@gmail.com> wrote:
>> > I've added a simple filter function that uses SQLites LIKE operator, but
>> I'd like something a little bit more advanced.  Right now, the SQL code is
>> like:
>> >
>> > select * from Events where Title like '%Abc%Def%'
>> >
>> >  This works if the Title is AbcRfeDef, but would fail with FedRfeAbc.
>> >
>> > I've thought about doing some kind of delimiter, then have the code
>> generate the SQL code by just looping through the keywords and generate the
>> "or Title like '%keyword%'" statement (With appropriate escaping), but that
>> just smells bad to me.
>>
>> The best way to do it is not to use complex keys, but if you have to (say
>> because the input is free form) then generating
>>
>> SELECT * FROM EVENTS WHERE title LIKE ‘%Abc%’ AND title LIKE ‘%Def%’;
>>
>> is probably the only option.
>>
>>
>> ___
>> sqlite-users mailing list
>> sqlite-users@mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Simple Search using LIKE or something else

[Don V Nielsen]

Assuming you are looking for "Abc" or "Def" anywhere in the argument, how
about regular expression? (Abc|Def)

On Thu, Nov 9, 2017 at 3:00 PM, Peter Da Silva <
peter.dasi...@flightaware.com> wrote:

> On 11/9/17, 2:51 PM, "sqlite-users on behalf of Stephen Chrzanowski" <
> sqlite-users-boun...@mailinglists.sqlite.org on behalf of
> pontia...@gmail.com> wrote:
> > I've added a simple filter function that uses SQLites LIKE operator, but
> I'd like something a little bit more advanced.  Right now, the SQL code is
> like:
> >
> > select * from Events where Title like '%Abc%Def%'
> >
> >  This works if the Title is AbcRfeDef, but would fail with FedRfeAbc.
> >
> > I've thought about doing some kind of delimiter, then have the code
> generate the SQL code by just looping through the keywords and generate the
> "or Title like '%keyword%'" statement (With appropriate escaping), but that
> just smells bad to me.
>
> The best way to do it is not to use complex keys, but if you have to (say
> because the input is free form) then generating
>
> SELECT * FROM EVENTS WHERE title LIKE ‘%Abc%’ AND title LIKE ‘%Def%’;
>
> is probably the only option.
>
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Simple Search using LIKE or something else

[Peter Da Silva]

On 11/9/17, 2:51 PM, "sqlite-users on behalf of Stephen Chrzanowski" 
 
wrote:
> I've added a simple filter function that uses SQLites LIKE operator, but I'd 
> like something a little bit more advanced.  Right now, the SQL code is like:
> 
> select * from Events where Title like '%Abc%Def%'
> 
>  This works if the Title is AbcRfeDef, but would fail with FedRfeAbc.
> 
> I've thought about doing some kind of delimiter, then have the code generate 
> the SQL code by just looping through the keywords and generate the "or Title 
> like '%keyword%'" statement (With appropriate escaping), but that just smells 
> bad to me.

The best way to do it is not to use complex keys, but if you have to (say 
because the input is free form) then generating

SELECT * FROM EVENTS WHERE title LIKE ‘%Abc%’ AND title LIKE ‘%Def%’;

is probably the only option.
 

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

Re: [sqlite] Simple Search using LIKE or something else

[Jens Alfke]

> On Nov 9, 2017, at 12:50 PM, Stephen Chrzanowski  wrote:
> 
> Is there any trick I can use that'll return AbcRfeDef and FedRfeAbc if
> the two keywords are Abc and Def from a SQL call?

Use full-text search (FTS4 or FTS5). This is exactly what it’s for.

Or as a hacky workaround, change the query to “… Title like ‘%Abc%’ and Title 
like ‘%Def%’”.

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