Virtual Tables using FTS3/FTS4/RTREE about sqlite-net HOT 11 OPEN

Do you prefer a CreateTable option, a CreateVirtualTable function, or alternatively, create normal tables and then add a virtual table for it. The problem with the latter is that maintenance is a bit annoying. Leaning towards CreateVirtualTable myself...

from sqlite-net.

Actually, I think I would prefer a TableAttribute with an IsVirtual bool property and a Using string property. The CreateTable code could then check for this and submit the different Create table statement.

Does that make sense?

On Jun 28, 2012, at 2:16 PM, tofutim wrote:

Do you prefer a CreateTable option, a CreateVirtualTable function, or alternatively, create normal tables and then add a virtual table for it. The problem with the latter is that maintenance is a bit annoying. Leaning towards CreateVirtualTable myself...

---

Reply to this email directly or view it on GitHub:
#69 (comment)

from sqlite-net.

I get it, except sometimes there are more parameters to track. For example,
I use

CREATE VIRTUAL TABLE verses USING FTS4(verse, tokenize=icu);

Where would you keep or track "tokenize=icu"?

Also there is sometimes a locale

CREATE VIRTUAL TABLE names USING fts3(a, b, tokenize=icu en_AU);

On Thu, Jun 28, 2012 at 2:20 PM, Frank A. Krueger <
[email protected]

wrote:

Actually, I think I would prefer a TableAttribute with an IsVirtual bool
property and a Using string property. The CreateTable code could then check
for this and submit the different Create table statement.

Does that make sense?

On Jun 28, 2012, at 2:16 PM, tofutim wrote:

Do you prefer a CreateTable option, a CreateVirtualTable function, or
alternatively, create normal tables and then add a virtual table for it.
The problem with the latter is that maintenance is a bit annoying. Leaning
towards CreateVirtualTable myself...

---

Reply to this email directly or view it on GitHub:
#69 (comment)

---

Reply to this email directly or view it on GitHub:
#69 (comment)

from sqlite-net.

ColumnAttribute obviously. :-)

I've been meaning to add TableAttribute and ColumnAttribute with Name properties anyway, so this will be a good start.

Are the tokenizers a fixed set? If so, it would be nice to use an enum with nice names. Can we use CultureInfo for the Culture or are strings better?

Lastly, we will need a static class StringEx { public static bool Matches (string arg) { throw new NotSupportedException (); } } so that we can have Linq support. You can see how LIKE is handled in TableQuery. If you're not comfortable with this, then you can leave it to me.

Also, some tests would be best. What platform do you use?

-Frank

On Jun 28, 2012, at 2:24 PM, tofutim wrote:

I get it, except sometimes there are more parameters to track. For example,
I use

CREATE VIRTUAL TABLE verses USING FTS4(verse, tokenize=icu);

Where would you keep or track "tokenize=icu"?

Also there is sometimes a locale

CREATE VIRTUAL TABLE names USING fts3(a, b, tokenize=icu en_AU);

On Thu, Jun 28, 2012 at 2:20 PM, Frank A. Krueger <
[email protected]

wrote:

Actually, I think I would prefer a TableAttribute with an IsVirtual bool
property and a Using string property. The CreateTable code could then check
for this and submit the different Create table statement.

Does that make sense?

On Jun 28, 2012, at 2:16 PM, tofutim wrote:

Do you prefer a CreateTable option, a CreateVirtualTable function, or
alternatively, create normal tables and then add a virtual table for it.
The problem with the latter is that maintenance is a bit annoying. Leaning
towards CreateVirtualTable myself...

---

Reply to this email directly or view it on GitHub:
#69 (comment)

---

Reply to this email directly or view it on GitHub:
#69 (comment)

---

Reply to this email directly or view it on GitHub:
#69 (comment)

from sqlite-net.

Wait, tokenize is not really a column, it is some instruction to FTS4. It's
like the Rebuild command which is similarly goofy looking:

INSERT INTO verses(verses) VALUES ('rebuild');

On Thu, Jun 28, 2012 at 2:29 PM, Frank A. Krueger <
[email protected]

wrote:

ColumnAttribute obviously. :-)

I've been meaning to add TableAttribute and ColumnAttribute with Name
properties anyway, so this will be a good start.

Are the tokenizers a fixed set? If so, it would be nice to use an enum
with nice names. Can we use CultureInfo for the Culture or are strings
better?

Lastly, we will need a static class StringEx { public static bool Matches
(string arg) { throw new NotSupportedException (); } } so that we can have
Linq support. You can see how LIKE is handled in TableQuery. If you're not
comfortable with this, then you can leave it to me.

Also, some tests would be best. What platform do you use?

-Frank

On Jun 28, 2012, at 2:24 PM, tofutim wrote:

I get it, except sometimes there are more parameters to track. For
example,
I use

CREATE VIRTUAL TABLE verses USING FTS4(verse, tokenize=icu);

Where would you keep or track "tokenize=icu"?

Also there is sometimes a locale

CREATE VIRTUAL TABLE names USING fts3(a, b, tokenize=icu en_AU);

On Thu, Jun 28, 2012 at 2:20 PM, Frank A. Krueger <
[email protected]

wrote:

Actually, I think I would prefer a TableAttribute with an IsVirtual bool
property and a Using string property. The CreateTable code could then
check
for this and submit the different Create table statement.

Does that make sense?

On Jun 28, 2012, at 2:16 PM, tofutim wrote:

Do you prefer a CreateTable option, a CreateVirtualTable function, or
alternatively, create normal tables and then add a virtual table for it.
The problem with the latter is that maintenance is a bit annoying.
Leaning
towards CreateVirtualTable myself...

---

Reply to this email directly or view it on GitHub:

#69 (comment)

---

Reply to this email directly or view it on GitHub:
#69 (comment)

---

Reply to this email directly or view it on GitHub:
#69 (comment)

---

Reply to this email directly or view it on GitHub:
#69 (comment)

from sqlite-net.

Tokenizer is not a fixed set - you can add your own into SQLite (we do...).

I'm using Windows right now (actually now to WinRT) but we do Windows,
WinRT, MonoDroid and and MonoTouch.

On Thu, Jun 28, 2012 at 2:31 PM, E. Timothy Uy [email protected] wrote:

Wait, tokenize is not really a column, it is some instruction to FTS4.
It's like the Rebuild command which is similarly goofy looking:

INSERT INTO verses(verses) VALUES ('rebuild');

On Thu, Jun 28, 2012 at 2:29 PM, Frank A. Krueger <
[email protected]

wrote:

ColumnAttribute obviously. :-)

I've been meaning to add TableAttribute and ColumnAttribute with Name
properties anyway, so this will be a good start.

Are the tokenizers a fixed set? If so, it would be nice to use an enum
with nice names. Can we use CultureInfo for the Culture or are strings
better?

Lastly, we will need a static class StringEx { public static bool Matches
(string arg) { throw new NotSupportedException (); } } so that we can have
Linq support. You can see how LIKE is handled in TableQuery. If you're not
comfortable with this, then you can leave it to me.

Also, some tests would be best. What platform do you use?

-Frank

On Jun 28, 2012, at 2:24 PM, tofutim wrote:

I get it, except sometimes there are more parameters to track. For
example,
I use

CREATE VIRTUAL TABLE verses USING FTS4(verse, tokenize=icu);

Where would you keep or track "tokenize=icu"?

Also there is sometimes a locale

CREATE VIRTUAL TABLE names USING fts3(a, b, tokenize=icu en_AU);

On Thu, Jun 28, 2012 at 2:20 PM, Frank A. Krueger <
[email protected]

wrote:

Actually, I think I would prefer a TableAttribute with an IsVirtual
bool
property and a Using string property. The CreateTable code could then
check
for this and submit the different Create table statement.

Does that make sense?

On Jun 28, 2012, at 2:16 PM, tofutim wrote:

Do you prefer a CreateTable option, a CreateVirtualTable function, or
alternatively, create normal tables and then add a virtual table for
it.
The problem with the latter is that maintenance is a bit annoying.
Leaning
towards CreateVirtualTable myself...

---

Reply to this email directly or view it on GitHub:

#69 (comment)

---

Reply to this email directly or view it on GitHub:

#69 (comment)

---

Reply to this email directly or view it on GitHub:
#69 (comment)

---

Reply to this email directly or view it on GitHub:
#69 (comment)

from sqlite-net.

[Table(IsVirtual = true, Using = "FTS4")]
public class Verse {
[Column(Tokenizer = SQLiteTokenizers.Icu, Culture = "en_AU")]
public string Verse { get; set; }
public string A { get; set; }
public string B { get; set; }
public string C { get; set; }
}

On Jun 28, 2012, at 2:31 PM, tofutim wrote:

Wait, tokenize is not really a column, it is some instruction to FTS4. It's
like the Rebuild command which is similarly goofy looking:

INSERT INTO verses(verses) VALUES ('rebuild');

On Thu, Jun 28, 2012 at 2:29 PM, Frank A. Krueger <
[email protected]

wrote:

ColumnAttribute obviously. :-)

I've been meaning to add TableAttribute and ColumnAttribute with Name
properties anyway, so this will be a good start.

Are the tokenizers a fixed set? If so, it would be nice to use an enum
with nice names. Can we use CultureInfo for the Culture or are strings
better?

Lastly, we will need a static class StringEx { public static bool Matches
(string arg) { throw new NotSupportedException (); } } so that we can have
Linq support. You can see how LIKE is handled in TableQuery. If you're not
comfortable with this, then you can leave it to me.

Also, some tests would be best. What platform do you use?

-Frank

On Jun 28, 2012, at 2:24 PM, tofutim wrote:

I get it, except sometimes there are more parameters to track. For
example,
I use

CREATE VIRTUAL TABLE verses USING FTS4(verse, tokenize=icu);

Where would you keep or track "tokenize=icu"?

Also there is sometimes a locale

CREATE VIRTUAL TABLE names USING fts3(a, b, tokenize=icu en_AU);

On Thu, Jun 28, 2012 at 2:20 PM, Frank A. Krueger <
[email protected]

wrote:

Actually, I think I would prefer a TableAttribute with an IsVirtual bool
property and a Using string property. The CreateTable code could then
check
for this and submit the different Create table statement.

Does that make sense?

On Jun 28, 2012, at 2:16 PM, tofutim wrote:

Do you prefer a CreateTable option, a CreateVirtualTable function, or
alternatively, create normal tables and then add a virtual table for it.
The problem with the latter is that maintenance is a bit annoying.
Leaning
towards CreateVirtualTable myself...

---

Reply to this email directly or view it on GitHub:

#69 (comment)

---

Reply to this email directly or view it on GitHub:
#69 (comment)

---

Reply to this email directly or view it on GitHub:
#69 (comment)

---

Reply to this email directly or view it on GitHub:
#69 (comment)

---

Reply to this email directly or view it on GitHub:
#69 (comment)

from sqlite-net.

Hmm, I like that.

On Thu, Jun 28, 2012 at 2:34 PM, Frank A. Krueger <
[email protected]

wrote:

[Table(IsVirtual = true, Using = "FTS4")]
public class Verse {
[Column(Tokenizer = SQLiteTokenizers.Icu, Culture = "en_AU")]
public string Verse { get; set; }
public string A { get; set; }
public string B { get; set; }
public string C { get; set; }
}

On Jun 28, 2012, at 2:31 PM, tofutim wrote:

Wait, tokenize is not really a column, it is some instruction to FTS4.
It's
like the Rebuild command which is similarly goofy looking:

INSERT INTO verses(verses) VALUES ('rebuild');

On Thu, Jun 28, 2012 at 2:29 PM, Frank A. Krueger <
[email protected]

wrote:

ColumnAttribute obviously. :-)

I've been meaning to add TableAttribute and ColumnAttribute with Name
properties anyway, so this will be a good start.

Are the tokenizers a fixed set? If so, it would be nice to use an enum
with nice names. Can we use CultureInfo for the Culture or are strings
better?

Lastly, we will need a static class StringEx { public static bool
Matches
(string arg) { throw new NotSupportedException (); } } so that we can
have
Linq support. You can see how LIKE is handled in TableQuery. If you're
not
comfortable with this, then you can leave it to me.

Also, some tests would be best. What platform do you use?

-Frank

On Jun 28, 2012, at 2:24 PM, tofutim wrote:

I get it, except sometimes there are more parameters to track. For
example,
I use

CREATE VIRTUAL TABLE verses USING FTS4(verse, tokenize=icu);

Where would you keep or track "tokenize=icu"?

Also there is sometimes a locale

CREATE VIRTUAL TABLE names USING fts3(a, b, tokenize=icu en_AU);

On Thu, Jun 28, 2012 at 2:20 PM, Frank A. Krueger <
[email protected]

wrote:

Actually, I think I would prefer a TableAttribute with an IsVirtual
bool
property and a Using string property. The CreateTable code could then
check
for this and submit the different Create table statement.

Does that make sense?

On Jun 28, 2012, at 2:16 PM, tofutim wrote:

Do you prefer a CreateTable option, a CreateVirtualTable function, or
alternatively, create normal tables and then add a virtual table for
it.
The problem with the latter is that maintenance is a bit annoying.
Leaning
towards CreateVirtualTable myself...

---

Reply to this email directly or view it on GitHub:

#69 (comment)

---

Reply to this email directly or view it on GitHub:

#69 (comment)

---

Reply to this email directly or view it on GitHub:

#69 (comment)

---

Reply to this email directly or view it on GitHub:
#69 (comment)

---

Reply to this email directly or view it on GitHub:
#69 (comment)

---

Reply to this email directly or view it on GitHub:
#69 (comment)

from sqlite-net.

Has this feature been implemented?

from sqlite-net.

@supernova32 Not in the way described here by there is the CreateFlags option on CreateTable to accomplish this.

Still need to implement the attribute.

from sqlite-net.

I would like to use tokenize=unicode61 "remove_diacritics=2".
Is there any alternatives?

from sqlite-net.