Re: [sqlite] LIKE IN

[Gert Van Assche]

Thanks for clarifying this, David. Learned something new today!

On Mon, 25 Nov 2019 at 15:25, David Raymond 
wrote:

> There'll be a few differences.
>
> The JOIN version will return 1 row for every item in queries which
> matches, and it will test every single one every time. So if you have in
> the queries table both 'Alex' and 'Alexand' then 'Alexander' and
> 'Alexandra' will each show up twice, once for 'Alex' and once for
> 'Alexand'. Depending on what you're doing this may be what you want.
>
> The EXISTS version will only ever return one row for each record in the
> names table, and it will stop checking other patterns once it finds one
> that matches.
>
> So if you want any info from the queries table then go with the join
> route, if you only care if yes/no there's anything at all that matches,
> then go with exists.
>
>
>
> -Original Message-
> From: sqlite-users  On
> Behalf Of Gert Van Assche
> Sent: Saturday, November 23, 2019 5:43 AM
> To: SQLite mailing list 
> Subject: Re: [sqlite] LIKE IN
>
> Both queries will work like this:
>
> DROP TABLE names;
> CREATE TABLE names (name TEXT);
> INSERT INTO names VALUES ('Alex');
> INSERT INTO names VALUES ('Alexander');
> INSERT INTO names VALUES ('Alexandra');
> INSERT INTO names VALUES ('Rob');
> INSERT INTO names VALUES ('Rhobin'); -- should not match
> INSERT INTO names VALUES ('Robert');
>
> CREATE TABLE queries (query TEXT);
> INSERT INTO queries VALUES ('Alex');
> INSERT INTO queries VALUES ('Rob');
>
> SELECT name from names t JOIN queries q ON t.name LIKE '%'||q.query||'%';
>
> SELECT name from names
> where exists (
> select query from queries
> where names.name like '%'||query||'%'
> );
>
>
> On Sat, 23 Nov 2019 at 11:34, Gert Van Assche  wrote:
>
> > I think this will work:
> >
> > INSERT INTO queries VALUES ('Alex');
> > INSERT INTO queries VALUES ('Rob');
> >
> > select * from names
> > where exists (
> > select query from queries
> > where names.name like '%'||query||'%'
> > );
> >
> > On Fri, 22 Nov 2019 at 15:19, David Raymond 
> > wrote:
> >
> >> Or alternatively something like:
> >>
> >> select * from table
> >> where exists (
> >> select query from queries
> >> where table.name like query
> >> );
> >>
> >> ___
> >> 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] LIKE IN

[Gert Van Assche]

Both queries will work like this:

DROP TABLE names;
CREATE TABLE names (name TEXT);
INSERT INTO names VALUES ('Alex');
INSERT INTO names VALUES ('Alexander');
INSERT INTO names VALUES ('Alexandra');
INSERT INTO names VALUES ('Rob');
INSERT INTO names VALUES ('Rhobin'); -- should not match
INSERT INTO names VALUES ('Robert');

CREATE TABLE queries (query TEXT);
INSERT INTO queries VALUES ('Alex');
INSERT INTO queries VALUES ('Rob');

SELECT name from names t JOIN queries q ON t.name LIKE '%'||q.query||'%';

SELECT name from names
where exists (
select query from queries
where names.name like '%'||query||'%'
);


On Sat, 23 Nov 2019 at 11:34, Gert Van Assche  wrote:

> I think this will work:
>
> INSERT INTO queries VALUES ('Alex');
> INSERT INTO queries VALUES ('Rob');
>
> select * from names
> where exists (
> select query from queries
> where names.name like '%'||query||'%'
> );
>
> On Fri, 22 Nov 2019 at 15:19, David Raymond 
> wrote:
>
>> Or alternatively something like:
>>
>> select * from table
>> where exists (
>> select query from queries
>> where table.name like query
>> );
>>
>> ___
>> 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] LIKE IN

[Gert Van Assche]

I think this will work:

INSERT INTO queries VALUES ('Alex');
INSERT INTO queries VALUES ('Rob');

select * from names
where exists (
select query from queries
where names.name like '%'||query||'%'
);

On Fri, 22 Nov 2019 at 15:19, David Raymond 
wrote:

> Or alternatively something like:
>
> select * from table
> where exists (
> select query from queries
> where table.name like query
> );
>
> ___
> 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] importing a large TSV file

[Gert Van Assche]

Thank you all for these tips. Very helpful!

Op di 2 apr. 2019 om 08:35 schreef Rowan Worth :

> On Mon, 1 Apr 2019 at 19:20, Domingo Alvarez Duarte 
> wrote:
>
> > Hello Gert !
> >
> > I normally do this (be aware that if there is a power outage the
> > database is screwed):
> >
> > ===
> >
> > PRAGMA synchronous = OFF;
> > begin;
> >
> > --processing here
> >
> > commit;
> > PRAGMA synchronous = ON;
> >
>
> You can probably leave the pragma alone without overly affecting import
> time tbh. The main thing is putting all the work into one transaction, and
> at that point you're down to 2 or 3 sync() calls. I guess there's still
> value in not having to wait for the journal to hit disk though. Maybe even
> PRAGMA journal_mode = OFF would be appropriate.
>
> -Rowan
> ___
> 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] importing a large TSV file

[Gert Van Assche]

Hi all,

I need to create an SQLite db from a large TSV file. (30 GB)
Are there any setting I can give to the db so I can speed up the import?

thank you

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

Re: [sqlite] LIMIT

[Gert Van Assche]

Thanks Olivier, very good to know.

gert

Op di 26 jun. 2018 om 09:06 schreef Olivier Mascia :

> > Le 26 juin 2018 à 07:56, Gert Van Assche  a écrit :
> >
> > If I would like to use these ranking techniques in SQLite, can I do this
> > with an extension? Has nobody created a Windowing.dll yet?
> > Just asking. My short term need has been solved, but now that I know
> this,
> > I'm sure I'd like to use this technique later. But I like to stick to
> > SQLite.
>
> Don't want to spoil any news, it is viewable by the public anyway, but
> there are clearly experimental, interesting (and significant) work ongoing
> by the SQLite developers around SQL windowing functions.  A quick look to
> the exp-window-functions branch clearly shows that.
>
> https://www.sqlite.org/src/timeline?n=100=exp-window-functions
>
> So it _may_ be possible that you see these features in a later version of
> SQLite, or not.  We'll see.  I certainly wish the best for this experience
> which looks good and nicely ongoing.
>
> From experience as a user of SQLite, not all development of features
> through branches get merged to the trunk.  Sometimes they stay available as
> a branch for you to choose to use, sometimes they end up in the main SQLite
> product (trunk branch).  Sometimes they can stay significant time aside
> before one day being merged.  I have no specific expectations, and you
> shouldn't have too, I'm just monitoring areas of development that are
> interesting to my eyes and programming needs.  Mainly the
> 'server-process-edition' branch, the 'begin-concurrent-pnu' branch and this
> 'exp-window-functions' branch.
>
> :)
>
> --
> Best Regards, Meilleures salutations, Met vriendelijke groeten,
> Olivier Mascia
>
>
> ___
> 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] LIMIT

[Gert Van Assche]

Very interesting,  Ryan. Thanks for sharing.

If I would like to use these ranking techniques in SQLite, can I do this
with an extension? Has nobody created a Windowing.dll yet?
Just asking. My short term need has been solved, but now that I know this,
I'm sure I'd like to use this technique later. But I like to stick to
SQLite.

Gert

Op ma 25 jun. 2018 om 01:00 schreef R Smith :

>
> On 2018/06/24 9:30 PM, Gert Van Assche wrote:
> > whaw... I could never come up with solutions like this. Will this work
> with
> > SQLite?
> > Where can I read more about this?
>
> I've only shown that query since Dan asked about it - it's not an SQLite
> query.
>
> To answer/comment on your questions:
>
> 1. Sure you can come up with it if you have read about it and understand
> what it is intended for - There is nothing you can't know, only things
> you don't know yet. :)
>
> 2. This will not work in SQLite currently - it's part of a group of
> functions called "Windowing" functions built into the bigger DB engines
> which do not care about their "Liteness".
> "Windowing functions" is basically a set of functions that treats the
> current ROW and its immediate preceding or acceding rows, or set of
> grouped rows within a bigger query (grouping by whatever common aspect
> you choose) as a unit and can return localized aggregates over them.
> The ranking/row position functions prove especially handy when wanting
> queries to easily produce row orders, ranks, etc. - but I promise you,
> they all are very possible in SQLite without Windowing functions, just
> not quite as obvious (as this thread has shown). More difficult to do
> are things like Percentile or Running-Total, though those too can be
> accomplished with a self-join or correlated sub-query in SQLite.
> The example in the original mail is from MSSQL, but similar queries work
> in Postgres, Oracle, etc.
>
> 3. You can read more by Googling "RDBMS Windowing functions" - or just
> click here: http://google.com/?q=RDBMS+windowing+functions
>
>
> Quick intro to Windowing in SQL (Apologies to all, the following is NOT
> for SQLite [yet]):
>
> Say I have a list of students in three classrooms and their test scores,
> and I would like to show the listing ranked by their scores, per class.
>
>
> Sorting makes part of the problem easy:
>
> SELECT class, student, score
>FROM students
>   ORDER BY class, student, score DESC
> ;  -- This works in SQLite
>
> This might produce a list like:
>
> class, student, score
> A5, Ann, 94
> A5, Able,88
> A5, Adrian,  67
> B5, James,   92
> B5, Jenny,   88
> B5, John,87
> B5, Joan,74
> C5, Sloan,   98
> C5, Sean,79
>
> Now a common thing is to want to put a rank integer number in front of
> every classmate so it is easy to see position, like this (spaced for
> legibility only):
>
> rank, class, student, score
> 1, A5, Ann, 94
> 2, A5, Able,88
> 3, A5, Adrian,  67
>
> 1, B5, James,   92
> 2, B5, Jenny,   88
> 3, B5, John,87
> 4, B5, Joan,74
>
> 1, C5, Sloan,   98
> 2, C5, Sean,79
>
> And for that the query will have to produce the whole list (no overall
> grouping) but then group these students by "class" to figure out the
> rank within every class "window" of the  larger query.
>
> You could say in English: We need to show the RANK *over* every window
> (aka "set of records") that is *partitioned by* the "class" field, where
> the rank within each "class" is determined by the value of the "score"
> field in a *descending order*.  That's easy enough to understand (I hope!).
>
> All that remains is to translate that to SQL. Looking at the English
> phrase above and the SQL below should quickly clarify how it all fits
> together:
>
> SELECT RANK() OVER (PARTITION BY class ORDER BY score DESC) as rank,
> class, student, score
>FROM students
>   ORDER BY class, student, score DESC
> ;  -- This example is MSSQL format
>
>
> If you don't care about the rank, and simply wish to number the rows in
> some order (student name for example), you could opt for something like:
>
> SELECT ROW_NUMBER() OVER (PARTITION BY class ORDER BY student) as
> student_no, class, student, score
>FROM students
>   ORDER BY class, student
> ;  -- MSSQL again
>
> which will output this:
>
> student_no, class, student, score
> 1, A5, Able,88
> 2, A5, Adrian,  67
> 3, A5, Ann, 94
> 1, B5, James,   92
> 2, B5, Jenny,   88
> 3, B5, Joan,74
> 4, B5, John,87
> 1, C5, Sean,79
> 2, C5, Sloan,   98
>
> Of course the aggregate doesn't n

Re: [sqlite] LIMIT

[Gert Van Assche]

whaw... I could never come up with solutions like this. Will this work with
SQLite?
Where can I read more about this?

Op zo 24 jun. 2018 om 20:29 schreef R Smith :

>
>
> On 2018/06/22 11:05 PM, Dan Kennedy wrote:
> > On 06/23/2018 03:52 AM, R Smith wrote:
> >>
> >> On 2018/06/22 10:04 PM, Gert Van Assche wrote:
> >>>   All,
> >>>
> >>> I'm sure it must be possible, I just don't find how.
> >>> I have a table T with 2 fields (F1 and F2). The F1 are unique and
> >>> the F2
> >>> are not unique.
> >>> I would like to get only 10 F1 fields for each unique F2.
> >>
> >> This is not normally done, and windowing functions in other RDBMSes
> >> makes for an easier way, but it can still be done in SQLite with some
> >> creative grouping of a self-joined query.
> >> In this example, I limited it to 3 F1 items per unique F2 for
> >> brevity, but you can easily change the "< 4" to "< 11" or "<= 10"
> >> according to preference.
> >
> > What would the window-function query be?
>
> Mainly one can avoid the self-join with a windowing function, like this:
>
> SELECT F2, F1 FROM (SELECT ROW_COUNT() OVER (PARTITION BY F2 ORDER BY
> F1) AS C, F2, F1) AS XWHERE X.C <= 10
>
> or
>
> SELECT F2, F1
>FROM (
>   SELECT F2, F1, DENSE_RANK() OVER (PARTITION BY F2 ORDER BY F1
> DESC) AS R
>) AS X
> WHERE R <= 10
>
>
>
> ___
> 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] LIMIT

[Gert Van Assche]

Ryan, my dataset isn't that big. 11K records.
Your solution is noticeable faster 996 ms vs 13126 ms.
Interesting!

gert

Op za 23 jun. 2018 om 18:09 schreef R Smith :

>
> On 2018/06/23 2:47 PM, Gert Van Assche wrote:
> > Barry, that's even easier indeed. And it works perfect!
> > Thanks for sharing this.
>
> I very much like the method which Barry suggested for the simplicity,
> but have avoided it since I thought it would be significantly slower on
> a large data set, however, SQLite is known for some pretty neat
> optimizations (especially where the row_id is concerned) and I'm now
> interested to know if indeed such optimizations feature in this case. I
> have a few such implemented queries that might benefit from changing over.
>
> May I ask, if you do have a rather large dataset, and perhaps don't mind
> the time, would you care to compare the two methods and let us know if
> there is any discernible difference in speed? Also one small important
> item - how many F1 items are there typically per unique F2 item in
> total? Does it grow over time?
>
> Also, you do not need hashes, for either of the two queries - the
> queries should work regardless of integer or Unicode text used, perhaps
> the comparison might need a specific collation? Surrounding both
> references with a TRIM() function might also help. Essentially, if F1 of
> record 3 in Table T is '*&^%@#*&^@#' then   "... FROM T AS A, T AS B
> WHERE A.F1 = B.F1" must resolve correctly. If it doesn't, it's a bug.
> The <, <=, >, >= might all produce some Unicode weirdness upon
> inadequate collations.
>
>
> If time doesn't allow, then don't spend effort on this, it's simply a
> curiosity. :)
>
> Thanks!
> Ryan
>
>
> ___
> 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] LIMIT

[Gert Van Assche]

Barry, that's even easier indeed. And it works perfect!
Thanks for sharing this.

gert

Op za 23 jun. 2018 om 14:32 schreef Barry Smith :

> Ryan's way works well. Here is a second method which expresses it in a
> different way:
>
> SELECT * FROM T t1 where rowid IN (SELECT rowid FROM T t2 WHERE t1.F2 =
> t2.F2 ORDER BY rowid LIMIT 10)
>
> If you have WITHOUT ROWID tables you'd have to replace rowid with your
> primary key.
>
> (The query may still work without the ORDER BY, I didn't test it, but even
> if it does a future query optimiser might break that because without the
> order by the results of the inner select are free to include a different 10
> rowids for every value in the outer query)
>
> > On 23 Jun 2018, at 9:50 pm, Gert Van Assche  wrote:
> >
> > Hi Ryan, thanks for this. This is working if the F1 field is a numeric
> > value. With text (especially Asian & Arabic characters) this does not
> seem
> > to work.
> > So I created an MD5 hash from the text fields and it works great! Thank
> you
> > so much.
> >
> > gert
> >
> > Op vr 22 jun. 2018 om 22:52 schreef R Smith :
> >
> >>
> >>> On 2018/06/22 10:04 PM, Gert Van Assche wrote:
> >>>  All,
> >>>
> >>> I'm sure it must be possible, I just don't find how.
> >>> I have a table T with 2 fields (F1 and F2). The F1 are unique and the
> F2
> >>> are not unique.
> >>> I would like to get only 10 F1 fields for each unique F2.
> >>
> >> This is not normally done, and windowing functions in other RDBMSes
> >> makes for an easier way, but it can still be done in SQLite with some
> >> creative grouping of a self-joined query.
> >> In this example, I limited it to 3 F1 items per unique F2 for brevity,
> >> but you can easily change the "< 4" to "< 11" or "<= 10" according to
> >> preference.
> >>
> >>   -- SQLite version 3.20.1  [ Release: 2017-08-24 ]  on SQLitespeed
> >> version 2.0.2.4.
> >>   --
> >>
> >>
> 
> >>
> >> CREATE TABLE T(F1 INTEGER PRIMARY KEY, F2 TEXT);
> >>
> >> INSERT INTO T(F1,F2) VALUES
> >>  (1,  'John')
> >> ,(2,  'John')
> >> ,(3,  'Jason')
> >> ,(4,  'John')
> >> ,(5,  'Jason')
> >> ,(6,  'John')
> >> ,(7,  'John')
> >> ,(8,  'Jason')
> >> ,(9,  'Jason')
> >> ,(10,  'Joan')
> >> ,(11,  'Joan')
> >> ,(12,  'Joan')
> >> ,(13,  'Jimmy')
> >> ;
> >>
> >> SELECT A.F2, B.F1
> >>   FROM T AS A
> >>   JOIN T AS B ON B.F2 = A.F2 AND B.F1 >= A.F1
> >>  GROUP BY A.F2, B.F1
> >>  HAVING COUNT(*) < 4
> >> ;
> >>
> >>   -- F2|  F1
> >>   -- - | ---
> >>   -- Jason |  3
> >>   -- Jason |  5
> >>   -- Jason |  8
> >>   -- Jimmy |  13
> >>   -- Joan  |  10
> >>   -- Joan  |  11
> >>   -- Joan  |  12
> >>   -- John  |  1
> >>   -- John  |  2
> >>   -- John  |  4
> >>
> >>
> >>
> >> -- Another option to note, in case the 10 limit is not important and
> >> simply aimed
> >> -- at saving space, is to use group concatenation, like so:
> >>
> >> SELECT F2, group_concat(F1)AS F1
> >>   FROM T
> >>  GROUP BY F2
> >> ;
> >>
> >>   --   |
> >>   -- F2|F1
> >>   -- - | -
> >>   -- Jason |  3,5,8,9
> >>   -- Jimmy | 13
> >>   -- Joan  |  10,11,12
> >>   -- John  | 1,2,4,6,7
> >>
> >>
> >>   --
> >>
> >>
> 
> >>
> >> Cheers!
> >> Ryan
> >>
> >>
> >> ___
> >> 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] LIMIT

[Gert Van Assche]

Hi Ryan, thanks for this. This is working if the F1 field is a numeric
value. With text (especially Asian & Arabic characters) this does not seem
to work.
So I created an MD5 hash from the text fields and it works great! Thank you
so much.

gert

Op vr 22 jun. 2018 om 22:52 schreef R Smith :

>
> On 2018/06/22 10:04 PM, Gert Van Assche wrote:
> >   All,
> >
> > I'm sure it must be possible, I just don't find how.
> > I have a table T with 2 fields (F1 and F2). The F1 are unique and the F2
> > are not unique.
> > I would like to get only 10 F1 fields for each unique F2.
>
> This is not normally done, and windowing functions in other RDBMSes
> makes for an easier way, but it can still be done in SQLite with some
> creative grouping of a self-joined query.
> In this example, I limited it to 3 F1 items per unique F2 for brevity,
> but you can easily change the "< 4" to "< 11" or "<= 10" according to
> preference.
>
>-- SQLite version 3.20.1  [ Release: 2017-08-24 ]  on SQLitespeed
> version 2.0.2.4.
>--
>
> 
>
> CREATE TABLE T(F1 INTEGER PRIMARY KEY, F2 TEXT);
>
> INSERT INTO T(F1,F2) VALUES
>   (1,  'John')
> ,(2,  'John')
> ,(3,  'Jason')
> ,(4,  'John')
> ,(5,  'Jason')
> ,(6,  'John')
> ,(7,  'John')
> ,(8,  'Jason')
> ,(9,  'Jason')
> ,(10,  'Joan')
> ,(11,  'Joan')
> ,(12,  'Joan')
> ,(13,  'Jimmy')
> ;
>
> SELECT A.F2, B.F1
>FROM T AS A
>JOIN T AS B ON B.F2 = A.F2 AND B.F1 >= A.F1
>   GROUP BY A.F2, B.F1
>   HAVING COUNT(*) < 4
> ;
>
>-- F2|  F1
>-- - | ---
>-- Jason |  3
>-- Jason |  5
>-- Jason |  8
>-- Jimmy |  13
>-- Joan  |  10
>-- Joan  |  11
>-- Joan  |  12
>-- John  |  1
>-- John  |  2
>-- John  |  4
>
>
>
> -- Another option to note, in case the 10 limit is not important and
> simply aimed
> -- at saving space, is to use group concatenation, like so:
>
> SELECT F2, group_concat(F1)AS F1
>FROM T
>   GROUP BY F2
> ;
>
>--   |
>-- F2|F1
>-- - | -
>-- Jason |  3,5,8,9
>-- Jimmy | 13
>-- Joan  |  10,11,12
>-- John  | 1,2,4,6,7
>
>
>--
>
> 
>
> Cheers!
> Ryan
>
>
> ___
> 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] LIMIT

[Gert Van Assche]

 All,

I'm sure it must be possible, I just don't find how.
I have a table T with 2 fields (F1 and F2). The F1 are unique and the F2
are not unique.
I would like to get only 10 F1 fields for each unique F2.

LIMIT 10 is the easy part... But how do I express the query so it F1 is
LIMITed to 10 for every F2?

I hope I explained well.

Thanks

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

Re: [sqlite] feature req: PLEASE why the heck COMMENT fields are not supporte in years!!

[Gert Van Assche]

I usually add a table with comments to other tables and fields for this.
That does the trick for me.
Is there another way to do it?

2017-03-14 13:54 GMT+01:00 PICCORO McKAY Lenz <mckaygerh...@gmail.com>:

> an important feature in a DB its the column field that gives to developers
> metadata info INDEPENDENT of the tecnologies used, due by this way with a
> simple text editor in generated script developer can read and use minimal
> info for understanding structure ...
>
> its a minimal feature need in a database, for many developers that make
> GOOD documentation!
>
>
> Lenz McKAY Gerardo (PICCORO)
> http://qgqlochekone.blogspot.com
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
Gert Van Assche
Skype: gertva -- Mobile: +32 498 84 44 75
datamundi.be -- fairtradetranslation.com -- delifteducation.be
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] SQLite workshop

[Gert Van Assche]

Cecil,

I like the idea but I can't give an outline. It will depend on your
audience. Tables, Views, Indexes, Triggers, Joins...

One piece of advice I can give you: use an example anyone can understand. I
once have a db training and I got lost because the example was about
football and the only thing I know about football is that there are 2 teams
of 11 players. That ways enough to understand the first 10 minutes of the
training. And then I got lost. Not because the DB stuff was too complicated
but because football is too complicated ;-)

One of the things you should probably also explain is how to work in a
shell and how to work in a tool like the SQLite Expert. And maybe also how
to use an SQLite db in an online environment like PHP and how to protect
yourself against sql-injections. Also how to use extensions might be
useful.
You could ask your future audience what they need.

gert

2016-05-05 17:17 GMT+02:00 Cecil Westerhof :

> Last year I gave a presentation at T-DOSE about SQLite. It was received
> enthusiastic, so I am thinking about giving a set of workshops about SQLite
> this year. What are good subjects to treat? And what are the pitfalls to
> expect?
> I am thinking to do it on several levels. At least people that have never
> worked with databases, but would benefit from using something better as
> just files. And people that have experience with databases, but would like
> to switch to SQLite for certain cases.
>
> --
> Cecil Westerhof
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] Is there something like PHPAdmin for SQLite

[Gert Van Assche]

I'm using https://www.phpliteadmin.org/ for this. Great tool and cheap:
free!


2016-04-20 18:12 GMT+02:00 Cecil Westerhof :

> I am blowing the horn for SQLite. ;-) A colleague of mine likes it in
> principal, but he provides web hosting with a database. He has to provide
> his clients with a web based interface for maintaining there database.
> Because of this he thinks he is ?condemned? to MySQL.
> Is this true, or would there be a plausible way to work with SQLite?
>
> --
> Cecil Westerhof
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] SQLite Extensions

[Gert Van Assche]

Thanks for sharing this, Keith!

2015-11-20 6:42 GMT+01:00 Keith Medcalf :

>
> For anyone who is interested, I have compiled for 32-bit Windows all the
> extensions that are included in the SQLite3 distribution /ext/misc
> directory using MinGW (gcc 4.8.1) and have added a few others I have
> written that add useful functions, and even a few that are taken from other
> people -- see the source for information.  They are all statically linked,
> require only the standard subsystem runtime (MSVCRT) and standard windows
> system dlls.  There are no other external dependancies.
>
> The modules that I wrote are:
>
> sqlfunc - Useful running statistical calculations, windows authorization
> functions
> sqlmath - Access to underlying math library (sin/cos/tan etc) as sql
> functions
> sqlfcmp - functions to compare floating point numbers (default within 5
> ULPs)
> sqlhash - windows cryptographic hash functions
> (md2/md4/md5/sha/sha256/sha384/sha512)
>
> You can download the file at
> http://www.dessus.com/files/SQLiteExtensions.zip
>
> ---
> Life should not be a journey to the grave with the intention of arriving
> safely in a pretty and well preserved body, but rather to skid in broadside
> in a cloud of smoke, thoroughly used up, totally worn out, and loudly
> proclaiming "Wow! What a Ride!"
>  -- Hunter S. Thompson
>
>
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] SQLite Extensions

[Gert Van Assche]

Thanks for sharing this, Keith!

2015-11-20 6:42 GMT+01:00 Keith Medcalf :

>
> For anyone who is interested, I have compiled for 32-bit Windows all the
> extensions that are included in the SQLite3 distribution /ext/misc
> directory using MinGW (gcc 4.8.1) and have added a few others I have
> written that add useful functions, and even a few that are taken from other
> people -- see the source for information.  They are all statically linked,
> require only the standard subsystem runtime (MSVCRT) and standard windows
> system dlls.  There are no other external dependancies.
>
> The modules that I wrote are:
>
> sqlfunc - Useful running statistical calculations, windows authorization
> functions
> sqlmath - Access to underlying math library (sin/cos/tan etc) as sql
> functions
> sqlfcmp - functions to compare floating point numbers (default within 5
> ULPs)
> sqlhash - windows cryptographic hash functions
> (md2/md4/md5/sha/sha256/sha384/sha512)
>
> You can download the file at
> http://www.dessus.com/files/SQLiteExtensions.zip
>
> ---
> Life should not be a journey to the grave with the intention of arriving
> safely in a pretty and well preserved body, but rather to skid in broadside
> in a cloud of smoke, thoroughly used up, totally worn out, and loudly
> proclaiming "Wow! What a Ride!"
>  -- Hunter S. Thompson
>
>
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] The Simplest SQLite Common Table Expression Tutorial

[Gert Van Assche]

You're a great teacher, David. Thanks for sharing this!

gert

2015-09-25 22:17 GMT+02:00 David Barrett :

> Hey all, just wanted to share this in case anybody is also looking for a
> very simple tutorial for CTE's in sqlite:
>
>
> http://blog.expensify.com/2015/09/25/the-simplest-sqlite-common-table-expression-tutorial/
>
> The Simplest SQLite Common Table Expression Tutorial
>
> I?ve been trying to wrap my head aroundCommon Table Expressions
>  for a while, and all the tutorials
> I?ve read started out with ?simple? examples that were way too advanced for
> me to follow. Here?s my attempt to write a tutorial that starts as simple
> as possible.
>
> First, let?s start with the simplest query:
>
> sqlite> SELECT 1;
> 1
> sqlite>
>
> All this does is return a result set containing a row. Next, consider the
> simplest subquery:
>
> sqlite> SELECT * FROM ( SELECT 1 );
> 1
> sqlite>
>
> This just selects all the results from the subquery ? which in this case,
> is just a single row. A ?Common Table Expression? is basically the same as
> a subquery, except assigned a name and defined prior to the query in which
> it?s referenced. Accordingly, the simplest CTE version of the above query
> would be like:
>
> sqlite> WITH one AS ( SELECT 1 )
> SELECT * FROM one;
> 1
> sqlite>
>
> Breaking that down a bit further:
>
>- We?ve defined a common table expression named ?one?
>- We?ve ?filled? it with the output of SELECT 1, which is just 1 row
>- Then we selected everything from ?one?
>- Such that the final result is a single value: 1
>
> But a CTE can have multiple columns, too, and those columns can be assigned
> names:
>
> sqlite> WITH twoCol( a, b ) AS ( SELECT 1, 2 )
> SELECT a, b FROM twoCol;
> 1|2
> sqlite>
>
> Similarly, a CTE can query other tables:
>
> sqlite> CREATE TABLE foo ( bar INTEGER );
> sqlite> INSERT INTO foo VALUES(1);
> sqlite> INSERT INTO foo VALUES(2);
> sqlite> SELECT * FROM foo;
> 1
> 2
> sqlite> WITH fooCTE AS (SELECT * FROM foo)
> SELECT * FROM fooCTE;
> 1
> 2
> sqlite>
>
> Additionally, you can define as many CTEs as you want in a single query:
>
> sqlite> WITH aCTE AS (SELECT 'a'),
>  bCTE AS (SELECT 'b')
> SELECT * FROM aCTE, bCTE;
> a|b
> sqlite>
>
> So, common table expressions can be used to restructure a query to make it
> more readable, by moving the subqueries out in front. But the real power of
> common table expressions is when you define an expression that recursively
> selects itself. They key to this is using a ?Compound Select Statements?,
> such as the UNION ALL operator. This just combines two result sets into one
> (so long as they have the same number of columns):
>
> sqlite> SELECT 1, 2
> UNION ALL
> SELECT 3, 4;
> 1|2
> 3|4
> sqlite>
>
> Take this example:
>
> sqlite> WITH RECURSIVE infinite AS (
> SELECT 1
> UNION ALL
> SELECT * FROM infinite
> )
> SELECT * FROM infinite;
> ^CError: interrupted
> sqlite>
>
> Let?s break down why that query will never finish:
>
>- ?WITH RECURSIVE infinite? defines a common table expression named
>?infinite?
>- ?SELECT 1? seeds that CTE?s output with a single row ? containing ?1?
>- Next the ?UNION ALL? says ?combine the output of what?s on the left,
>with the output of what?s on the right
>- And on the right we do ?SELECT * FROM infinite? ? meaning, select
>everything currently in the table.
>- The result is we?re defining a common table expression named
>?infinite? to be the union of ?a single row? and ?all other rows?.
>- Because no ?cap? has been placed on this (via a WHERE or LIMIT), this
>means we?ve defined an infinitely recurring CTE. Fun!
>
> So we can ?cap? that CTE by writing a query like:
>
> sqlite> WITH RECURSIVE finite AS (
> SELECT 1
> UNION ALL
> SELECT * FROM finite LIMIT 2
> )
> SELECT * FROM finite;
> 1
> 1
> sqlite>
>
> This does the same basic thing, but we?ve limited the number of possible
> results to only be 2. Ok, so that?s all well and good, but what is this
> good for? It turns out, a lot. Say you wanted to generate a table on the
> fly containing the numbers one through ten:
>
> sqlite> WITH RECURSIVE ten(x) AS (
> SELECT 1
> UNION ALL
> SELECT x+1 FROM ten WHERE x<10
> )
> SELECT * FROM ten;
> 1
> 2
> 3
> 4
> 5
> 6
> 7
> 8
> 9
> 10
> sqlite>
>
> To do this, we?ve defined a CTE named ?ten?, with a single column named ?x?
> (the column name is optional, but in this case we need it to refer to
> later). Then in the recursive UNION ALL, we keep adding one more row to the
> result set ? each one larger than the row before ? until we reach a limit
> of 10.
>
> So CTEs can be used to generate a wide array of different types of data ?
> such as date ranges, perhaps to join against when doing 

[sqlite] Update with out without WHERE?

[Gert Van Assche]

Thank you both for the discussion and conclusion.

2015-05-24 18:32 GMT+02:00 Simon Slavin :

>
> On 24 May 2015, at 4:58pm, Keith Medcalf  wrote:
>
> > This is not possible and would make an update operation incredibly
> slow.  What you mean is that a row which is not updated is not updated.  A
> row which is updated, even if you are re-writing the same value, *IS* an
> update operation.
>
> CREATE TABLE myTable (x INTEGER, orig INTEGER);
> (imagine 1 operations like this here):
> INSERT INTO "myTable" VALUES(9636,9636);
> INSERT INTO "myTable" VALUES(8655,8655);
> INSERT INTO "myTable" VALUES(7966,7966);
> INSERT INTO "myTable" VALUES(1647,1647);
> INSERT INTO "myTable" VALUES(7780,7780);
> INSERT INTO "myTable" VALUES(9088,9088);
>
> In the following, ignore the indented rows.  Those are me putting the
> original values back where they belong after each test.
>
> sqlite> UPDATE myTable SET x= WHERE x<100;
> Run Time: real 0.007 user 0.002405 sys 0.001365
> sqlite> UPDATE myTable SET x=orig;
> Run Time: real 0.014 user 0.010427 sys 0.001255
> sqlite> UPDATE myTable SET x=orig WHERE x<100;
> Run Time: real 0.008 user 0.002374 sys 0.001319
> sqlite> UPDATE myTable SET x=orig;
> Run Time: real 0.016 user 0.010507 sys 0.001409
> sqlite> UPDATE myTable SET x= WHERE x<9000;
> Run Time: real 0.015 user 0.010315 sys 0.001305
> sqlite> UPDATE myTable SET x=orig;
> Run Time: real 0.016 user 0.010493 sys 0.001333
> sqlite> UPDATE myTable SET x=orig WHERE x<9000;
> Run Time: real 0.016 user 0.010673 sys 0.001282
> sqlite> UPDATE myTable SET x=orig;
> Run Time: real 0.015 user 0.010674 sys 0.001383
>
> I was wrong.  You're right.  The 'user' number is far larger when the
> WHERE clause is selecting more rows.  It is not larger when you're writing
> back the same number as the number already in that column.  So SQLite does
> not compare the number you're writing with the number already in that
> column and rewrite the row only if they're different.
>
> Therefore, to answer the OP, using the WHILE clause is more likely to mean
> faster execution with less writing going on.  Which is good all round.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] Update with out without WHERE?

[Gert Van Assche]

Hi all,

When I do an update in a huge table, should it write it like this
UPDATE T1 SET F1=replace(F1, 'x', 'y') WHERE F1 LIKE '%x%';
or without where clause like this
UPDATE T1 SET F1=replace(F1, 'x', 'y');

T1 has an index on F1, which is a TEXT field.

thanks

gert

[sqlite] SQLite using internally by Windows 10

[Gert Van Assche]

Great! Congrats!

2015-05-01 5:24 GMT+02:00 Richard Hipp :

> https://twitter.com/john_lam/status/593837681945092096
>
> --
> D. Richard Hipp
> drh at sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] Interrupt SQL query in SHELL

[Gert Van Assche]

Ryan, Simon, thank you both for the help.
The script works fine, and I now also understand how to use this bail
mechanism. It is great!

once more: THANK YOU!

gert

2015-04-01 23:17 GMT+02:00 Simon Slavin :

>
> On 1 Apr 2015, at 8:09pm, Gert Van Assche  wrote:
>
> > But this is definitely not the right way to do it. If I understand the
> doc
> > correctly, it should be an expression, but I don't see how I can do
> this...
>
> You can trigger a bail by causing any SQL error.  For example, inserting a
> duplicate value in a column which is declared UNIQUE, maybe a primary key.
> Or inserting a NULL value in a column defined as NOT NULL.  Or failing any
> other constraint check.  So you may be able to use CASE or a calculation to
> arrange that under the condition you're watching, one of these happens.
>
> You may be able to check the value returned by sqlite3 as it exits to see
> whether it bailed or exited normally.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] Interrupt SQL query in SHELL

[Gert Van Assche]

Dr. Hipp, thanks for the tip. I put
   .bail on
in the script.


Ryan, I think I don't know how to trigger the bail out from within a SELECT
statement.
I tried
 SELECT CASE (select count(*) from T1) WHEN (select count(*) from T2)
then 'OK' else RAISE(FAIL) END;

But this is definitely not the right way to do it. If I understand the doc
correctly, it should be an expression, but I don't see how I can do this...

thanks


gert

2015-04-01 20:44 GMT+02:00 Richard Hipp :

> On 4/1/15, Gert Van Assche  wrote:
> >
> > My question is: when the import fails (detected in the script.sql file),
> I
> > would like to stop executing the batch file (runscript.cmd).
> >
> > Is there a way to do so?
> >
>
> Have you tried the -bail command-line option?
> --
> D. Richard Hipp
> drh at sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] Interrupt SQL query in SHELL

[Gert Van Assche]

Hi all,

When running SQLite3 from command line, is there a way to interrupt the CMD
file when a condition is true?

I'm importing a lot of plain text files that should all count the same
number of lines, so I want to compare the record count of all imported data
with the first file that was imported.

I can detect this easily with a query like this:

   .import 'x.txt' T1
   .import 'y.txt' T2
   .mode list
   .output importerror.txt
   SELECT CASE (select count(*) from T1) WHEN (select count(*) from T2)
then 'OK' else 'Not all files contain the same number of lines' END;

This is in an "script.sql" file and I run it by executing a batch file
(runscript.cmd).

   type script.sql | sqlite3.exe test.db


My question is: when the import fails (detected in the script.sql file), I
would like to stop executing the batch file (runscript.cmd).

Is there a way to do so?

thanks for your thoughts,


Gert

Re: [sqlite] A new extension for sqlite to analyze the stringmetrics

[Gert Van Assche]

Sorry, stupid me... I clicked on the wrong link.

2014-10-27 17:32 GMT+01:00 Gert Van Assche <ger...@gmail.com>:

> Andrea, any chance I can download the latest version of the DLL?
>
> thanks
>
> gert
>
> 2014-09-28 23:28 GMT+02:00 aperi2007 <aperi2...@gmail.com>:
>
>> Hi Keith,
>> thx for hints.
>> I have apply all of them.
>>
>> The only one I like to explain:
>>
>> I know the warning for the void insted of int declaration of
>> stringmetricsFunc.
>> But if I put it as "int" I had a warning in the create_function that want
>> a void function.
>> So I preferred to maintain the warning on  return from stringmetricsFunc.
>>
>> However to have a compile without any warning,
>> I adopt this "hard" workaround:
>>
>> I define in wrapper_functions.h a
>> void stringmetricsFunc
>> and instead I declare a
>> int stringmetricFunc in wrapper_function.c
>>
>> Thx again,
>>
>> A.
>>
>>
>>
>> Il 28/09/2014 22:20, Keith Medcalf ha scritto:
>>
>>> src\wrapper_functions.c: In function 'stringmetricsFunc':
>>> src\wrapper_functions.c:350:16: warning: 'return' with a value, in
>>> function returning void [enabled by default]
>>>  return (1);
>>>  ^
>>>
>>> This is easy.  SQLite scalar functions are supposed to return an int
>>> status code.  That code is either SQLITE_ERR if there was an error, or
>>> SQLITE_OK if everything is OK.  So change the function definition to return
>>> an int, and the two return statements to return SQLITE_ERR (not 1) and
>>> SQLITE_OK (not nothing).
>>>
>>
>> ___
>> 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] A new extension for sqlite to analyze the stringmetrics

[Gert Van Assche]

Andrea, any chance I can download the latest version of the DLL?

thanks

gert

2014-09-28 23:28 GMT+02:00 aperi2007 :

> Hi Keith,
> thx for hints.
> I have apply all of them.
>
> The only one I like to explain:
>
> I know the warning for the void insted of int declaration of
> stringmetricsFunc.
> But if I put it as "int" I had a warning in the create_function that want
> a void function.
> So I preferred to maintain the warning on  return from stringmetricsFunc.
>
> However to have a compile without any warning,
> I adopt this "hard" workaround:
>
> I define in wrapper_functions.h a
> void stringmetricsFunc
> and instead I declare a
> int stringmetricFunc in wrapper_function.c
>
> Thx again,
>
> A.
>
>
>
> Il 28/09/2014 22:20, Keith Medcalf ha scritto:
>
>> src\wrapper_functions.c: In function 'stringmetricsFunc':
>> src\wrapper_functions.c:350:16: warning: 'return' with a value, in
>> function returning void [enabled by default]
>>  return (1);
>>  ^
>>
>> This is easy.  SQLite scalar functions are supposed to return an int
>> status code.  That code is either SQLITE_ERR if there was an error, or
>> SQLITE_OK if everything is OK.  So change the function definition to return
>> an int, and the two return statements to return SQLITE_ERR (not 1) and
>> SQLITE_OK (not nothing).
>>
>
> ___
> 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] A new extension for sqlite to analyze the stringmetrics

[Gert Van Assche]

Andrea, where do I find it?

thanks

gert

2014-09-27 13:48 GMT+02:00 Andrea Peri :

> Hi,
> I commit the new extension with the runtime statically liked.
> I test it on a clean machine and seem work.
>
> The extension is compiled on 32bit.
> So need a sqlite 32bit to work.
>
> I guess it should not work with a 64bit sqlite shell.
>
> Regards,
>
> A.
>
>
> 2014-09-26 17:38 GMT+02:00 Andrea Peri :
> >>For Andrea Peri's benefit, I did google and find where someone mentions
> the
> >>appropriate flags to statically link the dependency in, thus avoiding
> this
> >>problem.
> >>
> http://stackoverflow.com/questions/4702732/the-program-cant-start-because-li
> >>bgcc-s-dw2-1-dll-is-missing
> >>So Andrea may wish to rebuild and replace the existing dll for the
> benefit
> >>of the community.
> >
> > Thx for hints.
> > I dont notice the dll was commited in the github.
> > :)
> >
> > However, no problem to update the compile to produce a static-linkage
> version.
> > I guess a static linkage version of the lib is still compliant with
> > the license of sqlite and libsymmetric.
> >
> > Also I update the site and dll changing the settings as reported from
> > "keith" (thx).
> >
> > Unfortunately I have not a clean machine to test him for a true
> > static-linkage. Infact now it run for me , but I guess is still a
> > shared version.
> > I compile with minw and need some other lib to have a true
> static-linkage.
> >
> > I try to resolve in this week-end.
> >
> > Andrea
> > --
> > -
> > Andrea Peri
> > . . . . . . . . .
> > qwerty àèìòù
> > -
>
>
>
> --
> -
> Andrea Peri
> . . . . . . . . .
> qwerty àèìòù
> -
> ___
> 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] A new extension for sqlite to analyze the stringmetrics

[Gert Van Assche]

thanks for your help Dave.

As I'm not a real developer but just an SQLite user, I cannot compile the
DLL -- I just downloaded the compiled DLL. So your guess is correct. I'm
working on a Win 8.1 PC and there's no libgcc-s-dw2-1.dll on my system.

gert

2014-09-25 19:26 GMT+02:00 dave <d...@ziggurat29.com>:

> Update: never mind what I mentioned below; he said he is using mingw (it
> really does help to read the entire thread!)
>
> But I have tried and failed to load it (the prebuilt binary) myself; I
> notice there is a further dependency on libgcc-s-dw2-1.dll, so maybe that's
> the origin of Gert's problem.
>
> -dave
>
> > -Original Message-
> > From: sqlite-users-boun...@sqlite.org
> > [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of dave
> > Sent: Thursday, September 25, 2014 11:59 AM
> > To: 'General Discussion of SQLite Database'
> > Subject: Re: [sqlite] A new extension for sqlite to analyze
> > the stringmetrics
> >
> >
> > Gert:
> >
> > Did you build it yourself, or download a binary?  Are you
> > running on XP, or
> > a later OS?
> >
> > I ask these questions because I have seen this code due to c
> > dll runtime
> > issues like (msvcrt100.dll), etc.
> > Depending on what tool was used to build the binary, some
> > changes may need
> > to be made to the build process so that the binary runs on
> > all platforms XP
> > - win8.  In particular, DS2012 broke the ability to make XP-compatible
> > builds, and the builder is using that, then (s)he needs to select the
> > 'v110_xp' toolset to make a binary that runs on all platforms.
> >
> > All this is purely a guess, and could easily be wrong; I
> > can't take a peek
> > at the lib myself right now; but I mention this now on the
> > chance that it
> > saves some time debugging.
> >
> > -dave
> >
> >
> > > -Original Message-
> > > From: sqlite-users-boun...@sqlite.org
> > > [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Gert
> > Van Assche
> > > Sent: Thursday, September 25, 2014 10:25 AM
> > > To: General Discussion of SQLite Database
> > > Subject: Re: [sqlite] A new extension for sqlite to analyze
> > > the stringmetrics
> > >
> > >
> > > Andea,
> > >
> > > Seems like a very interesting extension to me.
> > > I cannot make it run on Windows.
> > > I get error code 0xc00012f.
> > >
> > > Any idea why this is ?
> > >
> > > thanks
> > >
> > > gert
> > >
> > > 2014-09-25 10:11 GMT+02:00 Andrea Peri <aperi2...@gmail.com>:
> > >
> > > > Hi,
> > > > for who eventually interested.
> > > >
> > > > Fr a specific internal use I develope a new simple
> > > extension for sqlite.
> > > > The LibStringmetrics.
> > > > https://github.com/aperi2007/libstringmetrics
> > > >
> > > > It is based on the LibSimmetrics c lib from Johnathan Botha
> > > > - available from here:
> > https://github.com/jokillsya/libsimmetrics -
> > > > It was a porting of another java library.
> > > >
> > > > The LibStringMetrics is compile using mingw .
> > > > All is released with a GPL3 to follow the same license of
> > > the original
> > > > libsimmetrics.
> > > >
> > > > The extension is usable after the usual load_extension command:
> > > >
> > > > select load_extension("libstringmetrics.dll");
> > > >
> > > > The extension add One new command:
> > > >
> > > > stringmetrics().
> > > >
> > > > calling it without any parameters
> > > > will return a simple help of the parameters and of the available
> > > > algorithms.
> > > >
> > > > Regards,
> > > >
> > > > --
> > > > -
> > > > Andrea Peri
> > > > . . . . . . . . .
> > > > qwerty àèìòù
> > > > -
> > > > ___
> > > > 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
> >
>
>
> ___
> 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] A new extension for sqlite to analyze the string metrics

[Gert Van Assche]

Andea,

Seems like a very interesting extension to me.
I cannot make it run on Windows.
I get error code 0xc00012f.

Any idea why this is ?

thanks

gert

2014-09-25 10:11 GMT+02:00 Andrea Peri :

> Hi,
> for who eventually interested.
>
> Fr a specific internal use I develope a new simple extension for sqlite.
> The LibStringmetrics.
> https://github.com/aperi2007/libstringmetrics
>
> It is based on the LibSimmetrics c lib from Johnathan Botha
> - available from here: https://github.com/jokillsya/libsimmetrics -
> It was a porting of another java library.
>
> The LibStringMetrics is compile using mingw .
> All is released with a GPL3 to follow the same license of the original
> libsimmetrics.
>
> The extension is usable after the usual load_extension command:
>
> select load_extension("libstringmetrics.dll");
>
> The extension add One new command:
>
> stringmetrics().
>
> calling it without any parameters
> will return a simple help of the parameters and of the available
> algorithms.
>
> Regards,
>
> --
> -
> Andrea Peri
> . . . . . . . . .
> qwerty àèìòù
> -
> ___
> 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] XML into sqlite

[Gert Van Assche]

I'm using XSLT to create CSV.

gert

2014-09-11 7:04 GMT+02:00 Carlos A. Gorricho :

>
> How would you recommend to drop XML data into an sqlite DB? I am guessing
> there is more than one way.
>
> I thought of importing XML into Excel, converting to csv, for further
> import into sqlite, now that I know how to import csv into sqlite ...
> almost always! But this seems like a long haul.
>
> Cheers,
>
> CARLOS A.
>
>
>
> Sent from my iPad
> ___
> 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] 64-bit precompiled command-line shell binary

[Gert Van Assche]

Jan,

for what I want to do, this exe is a perfect solution. It is working fine.

I cannot test what RSmith wants to test, but I trust his expertise on this
(SQLitespeed is a nice tool!)

Thanks for your work on this, both of you.


gert



2014-07-03 15:54 GMT+02:00 RSmith :

>
> On 2014/07/03 13:55, Jan Nijtmans wrote:
>
>> 2014-07-01 21:12 GMT+02:00 RSmith :
>>
>>> I seem to remember a similar question having surfaced some time ago but
>>> cannot find the reference now - so let me ask the great forum devs again:
>>> WIll the 64-bit version of sqlite3.exe be able to use >2GB of memory for
>>> itself
>>>
>> Really don't know, but I guess the answer should be: Yes.
>> Just try it, I'm interested in your feed-back.
>>
>
> Thanks for these Jan!
>
> Also - if the OP does not come back with results I will set up a testing
> scenario this weekend, since I am now quite interested. I will try to set
> the Page sizes and limits so that 4GB or more is used theoretically, and
> then try to make DB's of appropriate size and devise queries that would
> require (or at least allow) that much to be used, and then track the memory
> usage.
>
> Do you see any problem with this approach or have any advice before I
> start?
>
>
>
>
> ___
> 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] 64-bit precompiled command-line shell binary

[Gert Van Assche]

Thanks Jan. I'll test it this afternoon.

gert


2014-07-03 13:55 GMT+02:00 Jan Nijtmans :

> 2014-07-01 21:12 GMT+02:00 RSmith :
> > I seem to remember a similar question having surfaced some time ago but
> > cannot find the reference now - so let me ask the great forum devs again:
> > WIll the 64-bit version of sqlite3.exe be able to use >2GB of memory for
> > itself
>
> Really don't know, but I guess the answer should be: Yes.
> Just try it, I'm interested in your feed-back.
>
> > is this different to the 32bit version, and if so, does anyone have
> > a pre-compiled 64bit sqlite3.exe version please?
>
> There is now, you can download it here:
> <
> http://sourceforge.net/projects/cyqlite/files/3.8.5/sqlite-shell-win64-x64-3080500.zip/download
> >
>
> sqlite3.exe is (cross-)compiled using the MinGW-w64 compiler as follows:
> x86_64-w64-mingw32-gcc -o sqlite3.exe -DUSE_SYSTEM_SQLITE=1
> shell.c sqlite3.dll
> x86_64-w64-mingw32-strip -o sqlite3.exe
>
> This means that it's only a small .exe (64 kB) which uses a (64-bit, 547
> kB)
> "sqlite3.dll" residing in the same directory to do the real work.
> You can replace sqlite3.dll with any other (64-bit) version, if it works
> then the dll is compatible with the SQLite 3.8.5 shell, if it doesn't work
> then there is some incompatibility (no guarantees, but it works
> with the 64-bit sqlite3.dll download provided by
>  as well).
>
> Regards,
>   Jan Nijtmans
> ___
> 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] 64-bit precompiled command-line shell binary

[Gert Van Assche]

Maybe a stupid question: how should I use this DLL? just put in the same
folder as the sqlite3.exe?




2014-07-01 16:39 GMT+02:00 RSmith <rsm...@rsweb.co.za>:

>
> On 2014/07/01 14:29, Gert Van Assche wrote:
>
>> All,
>>
>> where could I download a 64-bit sqlite3.exe for running on Windows 8?
>>
>
> From an earlier reply by Richard Hipp:
>
> A 64-bit Windows DLL is now available athttp://www.sqlite.org/
> download.html
>
>
>
>
>
>
>
> ___
> 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] Importing a lot of data from many databases

[Gert Van Assche]

Thanks Simon, this is exactly what I needed to know.

gert


2014-07-01 16:48 GMT+02:00 Simon Slavin <slav...@bigfraud.org>:

>
> > On 1 Jul 2014, at 12:26pm, Gert Van Assche <ger...@gmail.com> wrote:
> >
> > 1 - Open the BIG db, attach all small files, ten by 10, and copy the
> tables
> > from the attached databases to the big table. I can speed up the import
> by
> > putting the INSERT in a transaction.
> >
> > 2 - Export one by one the table from all databases to a CSV file; import
> > all CSV files into the BIG db; I don't have to worry about grouping the
> > files by 10, but I cannot use transaction.
> >
> > What would be the best way to do this? One of these 2 ways, or is there a
> > third way to do this?
>
> First, are you doing this just once, or does it have to become part of a
> workflow for regular use ?
>
> The results of the two should be exactly equivalent.
>
> (1) will be faster.  You can do things like "INSERT INTO tableB SELECT *
> FROM tableA".  This automatically executes the whole thing as one
> transaction.
>
> (2) will be easier to debug.  SQLite doesn't have a built-in function to
> express a row as an INSERT command, but this is part of the function of the
> SQLite shell tool and shell tool scripts can incorporate "BEGIN" and "END"
> commands.
>
> If I was doing it just once I'd probably write a script for the shell tool
> that did (2).  If I had to implement it as part of a frequently used
> workflow I'd probably write code which did (1).
>
> Simon.
> ___
> 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] 64-bit precompiled command-line shell binary

[Gert Van Assche]

All,

where could I download a 64-bit sqlite3.exe for running on Windows 8?

Thanks,

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

[sqlite] Importing a lot of data from many databases

[Gert Van Assche]

All,

I'm using the windows shell and I have to import one table from 50.000
small sqlite files into one big sqlite file.

These are the 2 options I see:

1 - Open the BIG db, attach all small files, ten by 10, and copy the tables
from the attached databases to the big table. I can speed up the import by
putting the INSERT in a transaction.

2 - Export one by one the table from all databases to a CSV file; import
all CSV files into the BIG db; I don't have to worry about grouping the
files by 10, but I cannot use transaction.

What would be the best way to do this? One of these 2 ways, or is there a
third way to do this?

thanks

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

Re: [sqlite] error "malformed database schema" on view in attached table

[Gert Van Assche]

Bingo! That was indeed the problem, Kevin.

I'm sorry I did not find that user group posting.

Thanks.


2014-07-01 11:30 GMT+02:00 Kevin Benson <kevin.m.ben...@gmail.com>:

> On Tue, Jul 1, 2014 at 5:07 AM, Gert Van Assche <ger...@gmail.com> wrote:
>
> > All,
> >
> > I get this error message:
> >
> > malformed database schema (WrongTargetLang) - view [WrongTargetLang]
> cannot
> > reference objects in database main
> >
> > when I try to attach a database containing a view. If I delete the view,
> I
> > get this message for another view. I get that message for all views.
> >
> > I can open that DB without any problem, but when I open it as an attached
> > db in the sqlite3 shell (in windows), this happens.
> >
> > Anyone any idea what is happening?
> >
> > thanks
> >
> > gert
>
>
> Perhaps you're referencing- main -in your view?
> http://comments.gmane.org/gmane.comp.db.sqlite.general/51366
>
> --
>--
>   --
>  --Ô¿Ô--
> K e V i N
> ___
> 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] error "malformed database schema" on view in attached table

[Gert Van Assche]

All,

I get this error message:

malformed database schema (WrongTargetLang) - view [WrongTargetLang] cannot
reference objects in database main

when I try to attach a database containing a view. If I delete the view, I
get this message for another view. I get that message for all views.

I can open that DB without any problem, but when I open it as an attached
db in the sqlite3 shell (in windows), this happens.

Anyone any idea what is happening?

thanks

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

Re: [sqlite] importing CSV with a random empty line at the end

[Gert Van Assche]

gwenn, thanks for this. I did not understand what you saw, and then I
realized my shell exe was probably too old.
I downloaded the new exe and this solves the problem just fine!
thanks for your help.

gert


2014-06-02 19:03 GMT+02:00 gwenn <gwenn.k...@gmail.com>:

> Hello,
> I doesn't fail for me (it may depend on the constraints on the target
> table) but the behaviour is unexpected:
>
> $ echo "1|test
> > " > empty.csv
> $ sqlite3
> SQLite version 3.8.4.3 2014-04-03 16:53:12
> sqlite> create table test(opt text, data text not null);
> sqlite> .import empty.csv test
> empty.csv:2: expected 2 columns but found 1 - filling the rest with NULL
> sqlite> select * from test;
> 1|test
> |test
>
> As the bindings are not cleared, it is not a null value but the
> previous bound value which is inserted.
> Regards.
>
> On Mon, Jun 2, 2014 at 5:41 PM, Gert Van Assche <ger...@gmail.com> wrote:
> > All,
> >
> > I received 100.000 UTF-8 files (average size 50kb)  "ready for import" in
> > an SQLite db.
> > 90% of them go fine, but some files have an empty line at the very end of
> > the fine (so an extra EOL before the EOF).
> >
> > Of course, the import fails... Is there an easy way to get rid of that
> > extra empty line before I import the file, or is there a way to ignore an
> > empty line?
> >
> > thanks
> >
> > Gert
> > ___
> > 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

[sqlite] importing CSV with a random empty line at the end

[Gert Van Assche]

All,

I received 100.000 UTF-8 files (average size 50kb)  "ready for import" in
an SQLite db.
90% of them go fine, but some files have an empty line at the very end of
the fine (so an extra EOL before the EOF).

Of course, the import fails... Is there an easy way to get rid of that
extra empty line before I import the file, or is there a way to ignore an
empty line?

thanks

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

Re: [sqlite] on insert new row, increment date with a month

[Gert Van Assche]

This is what I came up with -- it seems to work but I don't know how safe
it is:

CREATE TRIGGER [AddMonthToAPSGEN]
AFTER INSERT
ON [APS_Generale]
BEGIN
 UPDATE APS_Generale SET nDate = date((SELECT nDate FROM APS_Generale
WHERE rowid=(SELECT max(rowid)-1 FROM APS_Generale)),'start of month','+1
month')
 WHERE nDate IS NULL;
END

thanks all for your help,

Gert


2014-02-11 19:12 GMT+01:00 Petite Abeille <petite.abei...@gmail.com>:

>
> On Feb 11, 2014, at 7:07 PM, Gert Van Assche <ger...@gmail.com> wrote:
>
> > Does anyone know if it is possible for a date field to be automatically
> > incremented with a month when a new record is created?
>
> If you are looking for something wacky, triggers are where to look:
>
> http://www.sqlite.org/lang_createtrigger.html
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 

Gert Van Assche
*skype: gertva*
*phone: +32 498 84 44 75*
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] on insert new row, increment date with a month

[Gert Van Assche]

All,

Does anyone know if it is possible for a date field to be automatically
incremented with a month when a new record is created?

If the last row contains "2013-01-01" in the DateField then the DateField
of the new row should automaticllay be "2013-02-01".

Thanks

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

Re: [sqlite] Unicode support in SQLite

[Gert Van Assche]

Very nice! Thanks for sharing, Aleksey.


2013/11/9 Aleksey Tulinov 

> On 11/04/2013 11:50 AM, Aleksey Tulinov wrote:
>
> Hey,
>
>
>  As you can see, this is truly full Unicode collation and case mapping
>> with untailored special casing. Extension provides the following functions,
>> statements and collations:
>>
>
> I've updated extension, examples and documentation, now it's easier to
> link extension statically. Everything, including new prebuilt binaries, is
> available on BitBucket, changelog is available here:
> https://bitbucket.org/alekseyt/nunicode/src/master/CHANGELOG
>
> ___
> 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] Update and GROUP BY

[Gert Van Assche]

Thanks James -- the select query is something I could do, but the update
one I could not get that one right.
 I was considering to create a new table based on the select query, but
since the real data set is millions of records, an update was the best
solution.

As always, thanks for your help.


gert


2013/11/3 James K. Lowden <jklow...@schemamania.org>

> On Sat, 2 Nov 2013 18:06:30 +0100
> Gert Van Assche <ger...@datamundi.be> wrote:
>
> > CREATE TABLE T (N, V, G);
> > INSERT INTO T VALUES('a', 1, 'x');
> > INSERT INTO T VALUES('b', 3, 'x');
> > INSERT INTO T VALUES('c', null, 'x');
> > INSERT INTO T VALUES('d', 80, 'y');
> > INSERT INTO T VALUES('e', null, 'y');
> > INSERT INTO T VALUES('f', 60, 'y');
> > INSERT INTO T VALUES('g', null, 'y');
> > INSERT INTO T VALUES('h', null, 'z');
> > INSERT INTO T VALUES('i', 111, 'z');
> >
> > I would like to see where N='c', V as the average for the group (G)
> > were this record belongs to (so 'x').
>
> So, you want the average of the non-missing V per G:
>
> sqlite> select G, avg(V) as avgV from T group by G;
> G   avgV
> --  --
> x   2.0
> y   70.0
> z   111.0
>
> and to see that average for each N that belongs to G:
>
> sqlite> select T.N, A.* from T join (select G, avg(V) as avgV from T
> sqlite> group by G) as A on T.G = A.G;
> N   G   avgV
> --  --  --
> a   x   2.0
> b   x   2.0
> c   x   2.0
> d   y   70.0
> e   y   70.0
> f   y   70.0
> g   y   70.0
> h   z   111.0
> i   z   111.0
>
> --jkl
> ___
> 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] Update and GROUP BY

[Gert Van Assche]

Keith, thanks for this. The statement Igor made is what I need.

gert


2013/11/2 Keith Medcalf <kmedc...@dessus.com>

>
> Can you write a SELECT which returns the data that you want?
> Can you write a WHERE clause which selects the records you wish to update?
>
> For example:
>
> update T
>set V = (select avg(t2.g)
>   from t as t2
>  where t2.g=t.g)
>  where V IS NULL;
>
> assuming you only want to update the value of V in rows where V IS NULL ...
>
> >-Original Message-
> >From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> >boun...@sqlite.org] On Behalf Of Gert Van Assche
> >Sent: Saturday, 2 November, 2013 11:07
> >To: sqlite-users
> >Subject: [sqlite] Update and GROUP BY
> >
> >All, I have this table:
> >
> >DROP TABLE T;
> >CREATE TABLE T (N, V, G);
> >INSERT INTO T VALUES('a', 1, 'x');
> >INSERT INTO T VALUES('b', 3, 'x');
> >INSERT INTO T VALUES('c', null, 'x');
> >INSERT INTO T VALUES('d', 80, 'y');
> >INSERT INTO T VALUES('e', null, 'y');
> >INSERT INTO T VALUES('f', 60, 'y');
> >INSERT INTO T VALUES('g', null, 'y');
> >INSERT INTO T VALUES('h', null, 'z');
> >INSERT INTO T VALUES('i', 111, 'z');
> >
> >I would like to see where N='c', V as the average for the group (G) were
> >this record belongs to (so 'x').
> >
> >Thus where N='c' I would get 2, and where N='e' or 'g', it would be 70,
> >and
> >where N=h it would be 111.
> >
> >I have no clue how to write this UPDATE statement. Could someone help me
> >out?
> >
> >thanks
> >
> >
> >Gert
> >___
> >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] Update and GROUP BY

[Gert Van Assche]

Igor, that is exactly what I need. I also understand how it is done now.

Thanks!

gert


2013/11/2 Igor Tandetnik <i...@tandetnik.org>

> On 11/2/2013 1:06 PM, Gert Van Assche wrote:
>
>> All, I have this table:
>>
>> DROP TABLE T;
>> CREATE TABLE T (N, V, G);
>> INSERT INTO T VALUES('a', 1, 'x');
>> INSERT INTO T VALUES('b', 3, 'x');
>> INSERT INTO T VALUES('c', null, 'x');
>> INSERT INTO T VALUES('d', 80, 'y');
>> INSERT INTO T VALUES('e', null, 'y');
>> INSERT INTO T VALUES('f', 60, 'y');
>> INSERT INTO T VALUES('g', null, 'y');
>> INSERT INTO T VALUES('h', null, 'z');
>> INSERT INTO T VALUES('i', 111, 'z');
>>
>> I would like to see where N='c', V as the average for the group (G) were
>> this record belongs to (so 'x').
>>
>> Thus where N='c' I would get 2, and where N='e' or 'g', it would be 70,
>> and
>> where N=h it would be 111.
>>
>
> I'm not sure I quite follow, but something like this perhaps:
>
> update T set V = (select avg(V) from T t2 where T.G = t2.G)
> where V is null;
>
> --
> 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

[sqlite] Update and GROUP BY

[Gert Van Assche]

All, I have this table:

DROP TABLE T;
CREATE TABLE T (N, V, G);
INSERT INTO T VALUES('a', 1, 'x');
INSERT INTO T VALUES('b', 3, 'x');
INSERT INTO T VALUES('c', null, 'x');
INSERT INTO T VALUES('d', 80, 'y');
INSERT INTO T VALUES('e', null, 'y');
INSERT INTO T VALUES('f', 60, 'y');
INSERT INTO T VALUES('g', null, 'y');
INSERT INTO T VALUES('h', null, 'z');
INSERT INTO T VALUES('i', 111, 'z');

I would like to see where N='c', V as the average for the group (G) were
this record belongs to (so 'x').

Thus where N='c' I would get 2, and where N='e' or 'g', it would be 70, and
where N=h it would be 111.

I have no clue how to write this UPDATE statement. Could someone help me
out?

thanks


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

Re: [sqlite] creating a random sub group

[Gert Van Assche]

Perfect! Wonderful.

Thanks Igor.


2013/10/14 Igor Tandetnik <i...@tandetnik.org>

> On 10/14/2013 11:36 AM, Gert Van Assche wrote:
>
>> my table contains a boolean SubGroup field that is set to 0 by default.
>> I would like to update it so that for instance 100 of the 1000 records are
>> randomly set to 1.
>> Is this something that can be done using an SQL instruction?
>>
>
> Try something like this:
>
> update MyTable set SubGroup=1 where rowid in (
>   select rowid from MyTable order by random() limit 100
> );
>
> --
> Igor Tandetnik
>
> __**_
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-**bin/mailman/listinfo/sqlite-**users<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] creating a random sub group

[Gert Van Assche]

All,

my table contains a boolean SubGroup field that is set to 0 by default.
I would like to update it so that for instance 100 of the 1000 records are
randomly set to 1.
Is this something that can be done using an SQL instruction?

Thanks

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

Re: [sqlite] finding records where a field only contains numbers

[Gert Van Assche]

Thanks Clements, that does the trick indeed!


2013/6/12 Clemens Ladisch <clem...@ladisch.de>

> Gert Van Assche wrote:
> > is there a way to find all records where a field only contains non-alpha
> > numeric characters, or numbers?
>
> This description is rather vague.
> The following finds values that do not contain alphabetical characters:
>
> SELECT * FROM MyTable WHERE AField NOT GLOB '*[^A-Za-z]*'
>
>
> Regards,
> Clemens
> ___
> 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] finding records where a field only contains numbers

[Gert Van Assche]

Hi all,

is there a way to find all records where a field only contains non-alpha
numeric characters, or numbers?

thanks

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

Re: [sqlite] ORDER of export on a SELECT

[Gert Van Assche]

Thanks for the advice, both. In the mean time we know that it indeed
something else causing the problem.

gert


2013/5/2 Simon Slavin <slav...@bigfraud.org>

>
> On 2 May 2013, at 9:26am, Gert Van Assche <ger...@datamundi.be> wrote:
>
> > I have a table with 2 fields that need to be exported to 2 TXT files but
> > the order of the lines in the export should be exactly the same as in the
> > table.
>
> Tables do not have order.  Really.  A table is a set of rows, not an
> ordered set of rows.  If you do not specify ORDER BY on a unique set of
> values, the same SELECT can returns the same rows in different orders.
>
> > I tried to do it like this:
> > SELECT [FieldA] FROM [T1] ORDER BY rowid;
> > and
> > SELECT [FieldB] FROM [T1] ORDER BY rowid;
> > but the rowid order is not followed. The two TXT files that are created
> are
> > not in sync.
>
> If you have unique values in rowid, and really are doing those two
> commands in the same transaction (so nothing can write to the table between
> them), and getting rows returned in different orders, something is weird
> about your setup.
>
> In your software do
>
> SELECT [FieldA],[FieldB] FROM [T1] ORDER BY rowid
>
> Use your software to make one text file from the first values and another
> from the second values.  It will be faster too !
>
> Simon.
> ___
> 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] ORDER of export on a SELECT

[Gert Van Assche]

All, I

I have a table with 2 fields that need to be exported to 2 TXT files but
the order of the lines in the export should be exactly the same as in the
table.
I tried to do it like this:
 SELECT [FieldA] FROM [T1] ORDER BY rowid;
and
 SELECT [FieldB] FROM [T1] ORDER BY rowid;
but the rowid order is not followed. The two TXT files that are created are
not in sync.

Is this normal or is there another way to address this?

thanks for your help,

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

Re: [sqlite] Database modeling question

[Gert Van Assche]

not an expert in this matter, but the more simple the better.

I think however you should anticipate 2 situations: one person having more
than one or two phone numbers, and 2 people sharing the same phone number.
Next to that, maybe it would be smart to show them that you know not all
phones these days have numbers... (for instance my children use their Skype
 name more often then their phone number  I even wonder if they still
know their own phone number...)

gert


2013/4/10 Alem Biscan 

> Hello Comunity,
>
> I have a question that is more of database modeling theory ( normalisation
> / data organisation ) nature. Please advise me.
>
> For a job interview I am required among other things to create a phone book
> application. I am thinking about database design. Although it is a simple
> assigment, I would like to make good first impression and do It by the
> book. Show them I understand relational database concepts. The application
> should list phone book entries. Also it should allow insertion, deletion,
> and modification.
>
> Name   Surname   City Phonenumber1 .. PhonenumberN
> =
> Mark KnolpferLos Angeles 0002224343.. 4443332233
> 
> 
> ..
>
> Allthough a denormalised model of 1, maybe 2 tables would work fine in
> reality, I want to go by the book. I want your reviews and possible
> improvements.
>
>  I was thinking of having 4 tables. PERSON, PHONE_NUMBER, PERSON_CITY ,
> CITY.
>
> It seems somehow stupid to have a table that holds only the phone number.
> And relation of person and phone number is 1 .. N.
>
> Please suggest. What would be the right way considering normalisation by
> the book.
> ___
> 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] looking up records with terms in a table

[Gert Van Assche]

Nice

2013/3/26 Petite Abeille :
>
> On Mar 26, 2013, at 10:36 PM, "Paul Mathieu"  wrote:
>
>> SELECT Sentences FROM T1 JOIN T2 ON T1.Sentences LIKE
>> CONCAT('%',T2.Terms,'%')
>
> Alternatively, use FTS [1]:
>
> sqlite> create virtual table  sentence using fts4( content text );
> sqlite> insert into sentence values( 'FTS3 and FTS4 are nearly identical' );
> sqlite> create table term( content text );
> sqlite> insert into term values( 'nearly' );
> sqlite> select * from sentence join term on sentence.content match 
> term.content;
> FTS3 and FTS4 are nearly identical|nearly
>
> [1] http://www.sqlite.org/fts3.html
>
> ___
> 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] looking up records with terms in a table

[Gert Van Assche]

Bingo!
Thanks you all!

2013/3/26 Clemens Ladisch <clem...@ladisch.de>:
> Gert Van Assche wrote:
>> 2013/3/26 Clemens Ladisch <clem...@ladisch.de>:
>>> Gert Van Assche wrote:
>>>> What I would like to do is look for all terms that appear in the Terms 
>>>> table.
>>>> Something like this (but of course this does not work):
>>>> SELECT [Sentences] FROM [T1] WHERE [Sentences] LIKE (SELECT Terms FROM T2);
>>>
>>> SELECT Sentences FROM T1 JOIN T2 ON T1.Sentences LIKE T2.Terms
>>
>> Clemens, doesn't seem to work...
>>
>> The terms are just a part of the sentence, not a full match.
>> Your query does find full matches.
>
> That's how you wrote it in your original query...
>
> Just add wildcards around the terms:
>
> SELECT Sentences FROM T1 JOIN T2 ON T1.Sentences LIKE '%' || T2.Terms || '%'
>
>
> Regards,
> Clemens
> ___
> 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] looking up records with terms in a table

[Gert Van Assche]

Rob, no that does not work either.
Thanks for trying though.

2013/3/26 Rob Richardson <rdrichard...@rad-con.com>:
> I think you need wildcards:
>
> SELECT Sentences FROM T1 JOIN T2 ON T1.Sentences LIKE %T2.Terms%
>
> RobR, not guaranteeing correct syntax
>
> -Original Message-
> From: sqlite-users-boun...@sqlite.org 
> [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Gert Van Assche
> Sent: Tuesday, March 26, 2013 12:53 PM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] looking up records with terms in a table
>
> Clemens, doesn't seem to work...
>
> The terms are just a part of the sentence, not a full match.
> Your query does find full matches.
>
> thanks
>
> gert
> ___
> 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] looking up records with terms in a table

[Gert Van Assche]

Clemens, doesn't seem to work...

The terms are just a part of the sentence, not a full match.
Your query does find full matches.

thanks

gert


2013/3/26 Clemens Ladisch <clem...@ladisch.de>:
> Gert Van Assche wrote:
>> What I would like to do is look for all terms that appear in the Terms table.
>> Something like this (but of course this does not work):
>> SELECT [Sentences] FROM [T1] WHERE [Sentences] LIKE (SELECT Terms FROM T2);
>
> SELECT Sentences FROM T1 JOIN T2 ON T1.Sentences LIKE T2.Terms
>
>
> Regards,
> Clemens
> ___
> 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] looking up records with terms in a table

[Gert Van Assche]

All,

I don't know if I can do this.

I have a table T1 with Sentences and a table T2 with Terms.
If I want to find all Sentences with the term "GM" I search like this:
SELECT [Sentences] FROM [T1] WHERE [Sentences] LIKE '%GM%';

What I would like to do is look for all terms that appear in the Terms table.
Something like this (but of course this does not work):
SELECT [Sentences] FROM [T1] WHERE [Sentences] LIKE (SELECT Terms FROM T2);

So my question is: can this be done, and if so, how?

thanks

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

Re: [sqlite] SQLite3

[Gert Van Assche]

Matt,

it would be wise if you use a tool like the SQLite Expert; that will
generate statements that you can use on command line as well.


gert


2013/3/5 Duncan, Matthew 

> I am hoping you can help me.
>
> I have a file "DB2.db" in sqlite3 format. I am trying to view the
> database using the SQLite3 application but I am not familiar with the
> language to write prompt commands. Are you able to please help?
>
>
> Many Thanks & Best Regards
>
> Mat Duncan
>
>
> 
>
> This e-mail is intended for the use of the addressee(s) only and may
> contain privileged, confidential, or proprietary information that is exempt
> from disclosure under law. If you have received this message in error,
> please inform us promptly by reply e-mail, then delete the e-mail and
> destroy any printed copy. Thank you.
>
>
> 
> ___
> 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] Break on cumulative sum

[Gert Van Assche]

James,

I'm very impressed by your solution. My SQL knowledge is not yet good
enough to understand what you proposed, but I hope one day that I will be
able to understand and replicate myself the code you wrote. I started of
with a join on the table itself as well, but I got stuck. I'll study your
code and I'll grow.
That is the beauty of groups like this: hobby developers like me get advice
from experts on problems we're facing. Very helpful, not just the code, but
also the on-problem training!
Thanks for your work, all!

gert

2013/3/3 James K. Lowden <jklow...@schemamania.org>

> On Sat, 2 Mar 2013 14:46:40 +0100
> Gert Van Assche <ger...@datamundi.be> wrote:
>
> > All, I don't know how to achieve this: I need to put the cumulative
> > sum in a field, and create a group as soon as that cumulative sum is
> > over a breakpoint value (10).
>
> This was a bit of a challenge because the group definition is IMO a
> little strange: The first value *after* the breakpoint is included in
> the group.  That made the SQL more convoluted than it would be
> otherwise.
>
> That said, the attached SQL does the job in one statement.  No triggers
> are needed.
>
> $ sqlite3 db < running_sum.sql
> TextField   ValueField  CumulativeValue  BreakPoint  GroupName
> --  --  ---  --  --
> A   2   210  1
> B   3   510  1
> C   2   710  1
> D   4   11   10  1
> E   5   510  2
> F   1   610  2
> G   1   710  2
> H   5   12   10  2
> I   11  11   10  3
> J   8   810  4
> K   2   10   10  4
>
> This lets you define the results in terms of the base data instead of
> keeping a table of derived values.  No updates required, just a view.
> Unless you have data-warehouse rowcounts or a very limited machine, it
> should perform just fine, despite what one might guess, because the I/O
> is limited to one table.  It should also outperform any per-row
> solution.
>
> Two bits of advice if I may.
>
> Be careful of using triggers to maintain data consistency.  Triggers
> are very good for enforcing referential integrity in situations that
> DRI can't handle. Beyond that they get very complex and error prone.
>
> Using triggers to compute derived values signals redundancy in the
> database.  That redundancy often is not as desirable as the
> denormalize-for-performance crowd thinks.  Better to compute derived
> values on demand (as in a view).  If that's demonstrated to be too
> slow, a periodic update in a cron job or similar should provide better
> thoughput than recomputing on every insert/update/delete.  You'll also
> do yourself a favor by segregating derived values in a different table
> that can be regenerated at will.
>
> My other suggestion is to eschew abstract nouns in column names.  Words
> like data, field, and value add no meaning.  After all, it's a
> database.  If it's not a value, what is it?
>
> This table,
>
> CREATE TABLE [Test]
> ( Name
> , Value
> , RunningSum
> , Breakpoint /* (one word, small 'p') */ DEFAULT 10
> , GroupName
> , primary key (Name)
> );
>
> is at least  as clear, wouldn't you say?
>
> HTH.
>
> --jkl
>
> P.S. In case the listserv strips attachments, here it is in plain
> text.
>
> [SQL]
> select   A.TextField, A.ValueField
>, A.total - coalesce(C.GroupTotal, 0) as CumulativeValue
>, A.Breakpoint
> --   , coalesce(C.GroupTotal, 0) as GroupTotal
>, coalesce(B.GroupName, A.GroupName) as GroupName
> from (
> select   a.*, sum(b.ValueField) as total
>, 1 + sum(b.ValueField) / (1+a.Breakpoint) as GroupName
> from Test as a join Test as b
> on a.TextField >= b.TextField
> group by a.TextField
> , a.ValueField
> , a.CumulativeValue
> , a.BreakPoint
> ) as A
> left join (
> select   a.*, sum(b.ValueField) as total
>, 1 + sum(b.ValueField) / (1 + a.Breakpoint) as GroupName
> from Test as a join Test as b
> on a.TextField >= b.TextField
> group by a.TextField
> , a.ValueField
> , a.CumulativeValue
> , a.BreakPoint
> ) as B
> on B.TextField = (sele

Re: [sqlite] Break on cumulative sum

[Gert Van Assche]

Michael, this is working perfectly!
I learned a lot with your code.
Thanks a lot for your help.

gert

2013/3/2 Michael Black <mdblac...@yahoo.com>

> I think your "K" row was a typo on the CumulativeValue?
>
> CREATE TABLE [Test] (TextField, ValueField, CumulativeValue,  BreakPoint
> DEFAULT 10, GroupName);
> CREATE TABLE [MyGroup](GroupName);
> insert into [MyGroup] values(1);
> create trigger trig1 before insert on [Test]
> when 10 <= (select sum(ValueField) from [Test] where GroupName=(select
> GroupName from [MyGroup]))
> begin
> update [MyGroup] set GroupName = (select GroupName+1 from
> [MyGroup]);
> end;
> create trigger trig2 after insert on [Test]
> begin
> update [Test] set GroupName = (select GroupName from [MyGroup])
> where
>  rowid=new.rowid;
> update [Test] set CumulativeValue = (select sum(ValueField) from
> [Test] where GroupName=(select GroupName from [MyGroup])) where
> rowid=new.rowid;
> end;
> insert into [Test] values('A', '2', null, '10',(select GroupName from
> MyGroup));
> insert into [Test] values('B', '3', null, '10',(select GroupName from
> MyGroup));
> insert into [Test] values('C', '2', null, '10',(select GroupName from
> MyGroup));
> insert into [Test] values('D', '4', null, '10',(select GroupName from
> MyGroup));
> insert into [Test] values('E', '5', null, '10',(select GroupName from
> MyGroup));
> insert into [Test] values('F', '1', null, '10',(select GroupName from
> MyGroup));
> insert into [Test] values('G', '1', null, '10',(select GroupName from
> MyGroup));
> insert into [Test] values('H', '5', null, '10',(select GroupName from
> MyGroup));
> insert into [Test] values('I', '11', null, '10',(select GroupName from
> MyGroup));
> insert into [Test] values('J', '8', null, '10',(select GroupName from
> MyGroup));
> insert into [Test] values('K', '2', null, '10',(select GroupName from
> MyGroup));
> select * from [Test];
> A|2|2|10|1
> B|3|5|10|1
> C|2|7|10|1
> D|4|11|10|1
> E|5|5|10|2
> F|1|6|10|2
> G|1|7|10|2
> H|5|12|10|2
> I|11|11|10|3
> J|8|8|10|4
> K|2|10|10|4
>
>
> -Original Message-
> From: sqlite-users-boun...@sqlite.org [mailto:
> sqlite-users-boun...@sqlite.org] On Behalf Of Gert Van Assche
> Sent: Saturday, March 02, 2013 7:47 AM
> To: sqlite-users
> Subject: [sqlite] Break on cumulative sum
>
> All, I don't know how to achieve this: I need to put the cumulative sum in
> a field, and create a group as soon as that cumulative sum is over a
> breakpoint value (10).
>
> This is an example table:
>
> CREATE TABLE [Test] (TextField, ValueField, CumulativeValue,  BreakPoint
> DEFAULT 10, GroupName);
> insert into [Test] values('A', '2', null, '10');
> insert into [Test] values('B', '3', null, '10');
> insert into [Test] values('C', '2', null, '10');
> insert into [Test] values('D', '4', null, '10');
> insert into [Test] values('E', '5', null, '10');
> insert into [Test] values('F', '1', null, '10');
> insert into [Test] values('G', '1', null, '10');
> insert into [Test] values('H', '5', null, '10');
> insert into [Test] values('I', '11', null, '10');
> insert into [Test] values('J', '8', null, '10');
> insert into [Test] values('K', '2', null, '10');
>
> I'd like to end up with a table that looks like this:
>
> TextField ValueField CumulativeValue BreakPoint GroupName
> A 2 2 10 1
> B 3 5 10 1
> C 2 7 10 1
> D 4 11 10 1
> E 5 5 10 2
> F 1 6 10 2
> G 1 7 10 2
> H 5 12 10 2
> I 11 11 10 3
> J 8 8 10 4
> K 2 2 10 4
>
> I spent hours trying to update the CumulativeValue field untill the
> BreakPoint value is crossed, and restarting the cumulative counter, but I
> have too little sql knowledge to do this.
>
> Could anyone help me?
>
> thanks
>
> gert
> ___
> 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

[sqlite] Break on cumulative sum

[Gert Van Assche]

All, I don't know how to achieve this: I need to put the cumulative sum in
a field, and create a group as soon as that cumulative sum is over a
breakpoint value (10).

This is an example table:

CREATE TABLE [Test] (TextField, ValueField, CumulativeValue,  BreakPoint
DEFAULT 10, GroupName);
insert into [Test] values('A', '2', null, '10');
insert into [Test] values('B', '3', null, '10');
insert into [Test] values('C', '2', null, '10');
insert into [Test] values('D', '4', null, '10');
insert into [Test] values('E', '5', null, '10');
insert into [Test] values('F', '1', null, '10');
insert into [Test] values('G', '1', null, '10');
insert into [Test] values('H', '5', null, '10');
insert into [Test] values('I', '11', null, '10');
insert into [Test] values('J', '8', null, '10');
insert into [Test] values('K', '2', null, '10');

I'd like to end up with a table that looks like this:

TextField ValueField CumulativeValue BreakPoint GroupName
A 2 2 10 1
B 3 5 10 1
C 2 7 10 1
D 4 11 10 1
E 5 5 10 2
F 1 6 10 2
G 1 7 10 2
H 5 12 10 2
I 11 11 10 3
J 8 8 10 4
K 2 2 10 4

I spent hours trying to update the CumulativeValue field untill the
BreakPoint value is crossed, and restarting the cumulative counter, but I
have too little sql knowledge to do this.

Could anyone help me?

thanks

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

Re: [sqlite] update the same record in multiple segments at once

[Gert Van Assche]

Thank you both!

2013/3/1 Hick Gunter <h...@scigames.at>

> If you MUST do this in a SQL Statement you might give this a try:
>
> Write a virtual table module "SPLIT" with three fields (element,
> delimiter, record) that will return the parts of a string from SQL like
>
> SELECT element FROM SPLIT WHERE delimiter = ' ' and record = ;
>
> CREATE VIRTUAL TABLE split USING split('');
>
> Write a function "JOIN" that will return a string concatenation of its
> parameters
>
> JOIN(,...)
>
> The result will be sort of SQL...
>
>
> -Ursprüngliche Nachricht-
> Von: Gert Van Assche [mailto:ger...@datamundi.be]
> Gesendet: Freitag, 01. März 2013 07:50
> An: sqlite-users
> Betreff: [sqlite] update the same record in multiple segments at once
>
> All, I don't know if this is possible.
>
> A segment in table "Files" contains a record "Content" with a fields like
> this "XXX      "
> Needs to become "XXX 123  456  78 90"
>
> Based on a "Translation" table were I have
>
> |123
> |456
> |78
> |90
>
> If I do
>
> UPDATE [Files] SET [Content] = replace(Content, (SELECT [Source] FROM
> [Translations]), (SELECT [Translation] FROM [Translations]));
>
> only the first match is replaced and I get: "XXX 123    
> "
>
> Is there a way to "translate" ,  and  as well?
>
> thanks
>
>
> Gert
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>
> --
>  Gunter Hick
> Software Engineer
> Scientific Games International GmbH
> Klitschgasse 2 – 4, A - 1130 Vienna, Austria
> FN 157284 a, HG Wien
> Tel: +43 1 80100 0
> E-Mail: h...@scigames.at
>
> This e-mail is confidential and may well also be legally privileged. If
> you have received it in error, you are on notice as to its status and
> accordingly please notify us immediately by reply e-mail and then delete
> this message from your system. Please do not copy it or use it for any
> purposes, or disclose its contents to any person as to do so could be a
> breach of confidence. Thank you for your cooperation.
> ___
> 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] update the same record in multiple segments at once

[Gert Van Assche]

All, I don't know if this is possible.

A segment in table "Files" contains a record "Content" with a fields like
this "XXX      "
Needs to become "XXX 123  456  78 90"

Based on a "Translation" table were I have

|123
|456
|78
|90

If I do

UPDATE [Files] SET [Content] = replace(Content, (SELECT [Source] FROM
[Translations]), (SELECT [Translation] FROM [Translations]));

only the first match is replaced and I get: "XXX 123    
"

Is there a way to "translate" ,  and  as well?

thanks


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

Re: [sqlite] import TXT file

[Gert Van Assche]

Thanks Kees.

2013/2/21 Kees Nuyt <k.n...@zonnet.nl>

> On Thu, 21 Feb 2013 18:00:58 +0100, Gert Van Assche
> <ger...@datamundi.be> wrote:
>
> >If I remove the double quotes, the problem is solved indeed.
> >I'm working on windows. Do you know if there is an alternative to the SED
> >command or tool?
>
> For just removing double quotes, I would recommend the tr (translate)
> program. Something like:
>
> echo "CREATE TABLE Source (Segments TEXT);" | sqlite3 dbfile
> tr -d '"' import.txt
> sqlite3 dbfile .import import.txt Source
>
> The sed (stream editor) is fine here too, as demonstrated before.
> For a general stream editor I would recommend gawk, which is much easier
> and more powerful than tr or sed when the transformations get more
> complicated.
>
> With gawk it is easy to create INSERT statements (from the top of my
> head, untested).
>
> gawk -f xform.awk test.txt | sqlite3 dbfile
>
> xform.awk contains:
> ###
> BEGIN{
> FS = ""
> insfmt = "INSERT INTO Source (Segments) VALUES ('%s');\n"
> print "CREATE TABLE Source (Segments TEXT);"
> print "BEGIN;"
> }
> # the following action is executed for every input line
> {
> gsub(/"/,"")# remove double quotes
> gsub(/'/,"''")  # escape every single quote with another single
> quote
> printf insfmt,$0
> }
> END{
> print "COMMIT;"
> }
>  EOF
>
> tr, sed and gawk have implementations on Windows, for example in
>
> http://unxutils.sourceforge.net/
>
> Note: you need both UnxUtils.zip and UnxUpdates.zip
>
> >Or is there a way I can force sqlite3 to ignore these double quotes?
> >
> >thanks
> >
> >gert
>
> --
> Groet, Cordialement, Pozdrawiam, Regards,
>
> Kees Nuyt
>
> ___
> 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] import TXT file

[Gert Van Assche]

found a good tool: http://findandreplace.codeplex.com/documentation
problem solved... I think.

thanks all for your help.

2013/2/21 Gert Van Assche <ger...@datamundi.be>

> If I remove the double quotes, the problem is solved indeed.
> I'm working on windows. Do you know if there is an alternative to the SED
> command or tool?
>
> Or is there a way I can force sqlite3 to ignore these double quotes?
>
> thanks
>
> gert
>
>
>
> 2013/2/21 雷钦 <leiqin2...@gmail.com>
>
>> On 2013-02-21 13:15:16 +0100, Gert Van Assche wrote:
>> > Roger,
>> >
>> > thanks for the advice, but I cannot work like this. I don't have the
>> > possibility to rewrite the txt files I receive.
>> > If I use the SQLite Expert, I get an error on "double-quote character"
>> or a
>> > "range error"...
>> >
>> > Any other suggestions?
>>
>> I think is the "double-quote character" break the line,
>> I find in the table each row has mutilines all has a double-quote (")
>> in the first line and another in last line , except the last row
>>
>> I think you can prehandle the txt file change the " to another string
>> like:
>>
>> sed -i.bak 's/"/@@/g' test.txt
>>
>> and then in sqlite:
>>
>> .import test.txt Source
>> UPDATE Source SET Segments = replace(Segments, '@@', '"');
>>
>> ___
>> 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] import TXT file

[Gert Van Assche]

If I remove the double quotes, the problem is solved indeed.
I'm working on windows. Do you know if there is an alternative to the SED
command or tool?

Or is there a way I can force sqlite3 to ignore these double quotes?

thanks

gert



2013/2/21 雷钦 <leiqin2...@gmail.com>

> On 2013-02-21 13:15:16 +0100, Gert Van Assche wrote:
> > Roger,
> >
> > thanks for the advice, but I cannot work like this. I don't have the
> > possibility to rewrite the txt files I receive.
> > If I use the SQLite Expert, I get an error on "double-quote character"
> or a
> > "range error"...
> >
> > Any other suggestions?
>
> I think is the "double-quote character" break the line,
> I find in the table each row has mutilines all has a double-quote (")
> in the first line and another in last line , except the last row
>
> I think you can prehandle the txt file change the " to another string
> like:
>
> sed -i.bak 's/"/@@/g' test.txt
>
> and then in sqlite:
>
> .import test.txt Source
> UPDATE Source SET Segments = replace(Segments, '@@', '"');
>
> ___
> 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] import TXT file

[Gert Van Assche]

Mihael,

I'll look for a developer who can compile the code for me. I'm not a
developer.
If I understand correctly, you wrote a tool to do a CSV import in an SQLite
db in a much more controlled way, and if the import fails, we'll know at
least why.

With the code like it is now, can I use it on plain text (so not tab
delimited or comma delimited text files) as well -- the type I described:
one line that can contain comma's and double quotes for instance?

thanks

gert

2013/2/21 Michael Black 

> I wrote an import util a while ago which might help you out as it's a bit
> more picky and gives more intelligent error messages than .import does.
> http://www.mail-archive.com/sqlite-users@sqlite.org/msg70182.html
>
> Show us some example lines and we can help a lot better.
>
> ___
> 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] import TXT file

[Gert Van Assche]

Thanks Igor. I will look for that.

2013/2/21 Igor Tandetnik <i...@tandetnik.org>

> On 2/21/2013 5:34 AM, Gert Van Assche wrote:
>
>> I have to import text files into an SQLite table.
>> The txt files are plain text in UTF-8. They don't contain tabs and the
>> line
>> ending is CRLF.
>>
>> One line in the TXT should become one record in the table.
>>
>> CREATE TABLE Source (Segments);
>> .import 'test.txt 
>> <http://www.datamundi.be/test.**txt<http://www.datamundi.be/test.txt>>'
>> Source
>>
>> SELECT count(*) FROM Source;
>>
>> The source text contains 156659 lines but the imported table contains only
>> 65561 records.
>>
>
> If you are on Windows, check that the file doesn't happen to contain a
> byte with the code of 26 (aka 0x1A aka ^Z). Such a byte is treated as
> end-of-file indicator in text files.
> --
> Igor Tandetnik
>
>
> __**_
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-**bin/mailman/listinfo/sqlite-**users<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] import TXT file

[Gert Van Assche]

Roger,

thanks for the advice, but I cannot work like this. I don't have the
possibility to rewrite the txt files I receive.
If I use the SQLite Expert, I get an error on "double-quote character" or a
"range error"...

Any other suggestions?

thanks

gert


2013/2/21 Roger Binns <rog...@rogerbinns.com>

> -BEGIN PGP SIGNED MESSAGE-
> Hash: SHA1
>
> On 21/02/13 02:34, Gert Van Assche wrote:
> > Does anyone see something obvious I'm doing wrong?
>
> There is no need to bash the SQLite shell into what you are trying to do.
>  You can write your own code to read your file and insert each row into
> the database.
>
> Another option is to convert the data file into SQL commands.  Place
> "BEGIN;" at the beginning of the file, and "END;" at the end of the file,
> and then precede each line with "INSERT INTO Source VALUES('", double up
> any single quotes in the line, and append "';".  (That is essentially what
> the .import command is doing behind the scenes, but it tries a lot harder
> to find separators, count/enforce number of columns, deal with end of line
> etc.)
>
> Roger
>
>
> -BEGIN PGP SIGNATURE-
> Version: GnuPG v1.4.11 (GNU/Linux)
>
> iEYEARECAAYFAlEl/9kACgkQmOOfHg372QSx2gCg49DGhrLuSIelDYMn0RAPe+GH
> EHkAoJgzYZmyVEUGUcBNbWDxq90s8a5q
> =koQD
> -END PGP SIGNATURE-
> ___
> 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] import TXT file

[Gert Van Assche]

All,

I have to import text files into an SQLite table.
The txt files are plain text in UTF-8. They don't contain tabs and the line
ending is CRLF.

One line in the TXT should become one record in the table.

CREATE TABLE Source (Segments);
.import 'test.txt ' Source
SELECT count(*) FROM Source;

The source text contains 156659 lines but the imported table contains only
65561 records.

I tried several mode settings and separator settings, but the result is
always the same.
Unless my separator is a '|' or when I don't define it, I get an error
message because some lines contain this character.
If I define '\t' (tab) as a separator, I don't get an error message but the
import is not correct.

When I analyse the strange behavior, I notice 2 things:
- the import is not done line per line (even when the mode is "line")
- the last line in the last record, is not the last line of imported txt
file.

The text files I need to import can be quite large (50MB) but it also
happens with smaller files (10MB and 150.000 lines).

Does anyone see something obvious I'm doing wrong?
I'm using SQLite 3.7.15.2

thank you all for your help.

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

Re: [sqlite] FTS4 languageid : not sure I understand this correctly

[Gert Van Assche]

thanks for this link. Most of these tools I tested in the past, but I was
hoping there was an easier path. The best langID tool is not in this list,
by the way. that is:
https://code.google.com/p/chromium-compact-language-detector/

gert

2013/1/31 Petite Abeille <petite.abei...@gmail.com>

>
> On Jan 31, 2013, at 9:27 PM, Gert Van Assche <ger...@datamundi.be> wrote:
>
> > Thanks Michael. Not what I hoped for but now I understand it.
>
> Perhaps of interest:
>
> Language Identification Tools
> http://www.let.rug.nl/~vannoord/TextCat/competitors.html
>
> ___
> 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] FTS4 languageid : not sure I understand this correctly

[Gert Van Assche]

Thanks Michael. Not what I hoped for but now I understand it.

2013/1/31 Michael Black <mdblac...@yahoo.com>

> According to the docs:
> http://www.sqlite.org/fts3.html#section_6_3
>
> It's YOUR choice as to what to put in there.  A separate index is created
> for each language id.
> So it's designed to prevent cross-lang contamination in the index.
>
> -Original Message-
> From: sqlite-users-boun...@sqlite.org
> [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Gert Van Assche
> Sent: Thursday, January 31, 2013 2:19 PM
> To: sqlite-users
> Subject: [sqlite] FTS4 languageid : not sure I understand this correctly
>
> All,
>
> I have the feeling this is the most stupid question ever, but...
> If I create a FTS4 table, put text in it, could I use the languageid to
> figure out what Language that text actually is?
> Is that how langID works?
> I did some tests, but the LangID seems to be 0 all the time, so or I'm
> doing something wrong, or I misunderstand the process.
>
> thanks
>
> gert
> ___
> 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

[sqlite] FTS4 languageid : not sure I understand this correctly

[Gert Van Assche]

All,

I have the feeling this is the most stupid question ever, but...
If I create a FTS4 table, put text in it, could I use the languageid to
figure out what Language that text actually is?
Is that how langID works?
I did some tests, but the LangID seems to be 0 all the time, so or I'm
doing something wrong, or I misunderstand the process.

thanks

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

Re: [sqlite] SQLite converts all unicode characters into ANSI

[Gert Van Assche]

All I can say is this is not normal behavior. I use SQLite always with
UTF-8 texts, even on command line (windows).
As a test I suggest you create the db with the SQLite Expert.

gert

2012/11/17 ZikO 

> I have a slight problem with sqlite and its text encoding. I read from
> documents that sqlite handles UTF-8 by using a command PRAGMA encoding =
> "UTF-8"; etc. My database is to store Polish text. The database is going to
> be used with Qt later on. I have a script with two commands: CREATE TABLE
> ... and INSERT INTO ... This file is encoded in UTF-8. However, when I
> build
> and fill database via a command *sqlite3 myname.db < the_file.sql*, I
> create
> both database and the table but all specific characters such as ą, ć, ź, Ż
> etc. are automatically converted into a, c, z, Z etc. I thought it would be
> a problem with the command line. So I downloaded SQLite Manager 2009 and
> when I copy / pasted the whole script to execute it in SQLite Manager, I
> noticed the effect is exactly the same. Characters are automatically
> converted during copy / pasting. Is the SQLite limitted to use only with
> ANSI characters?
>
> Can someone please help me? The application will be international and not
> only will I need Polish characters but Spanish etc.
>
> Thanks
>
>
>
>
> --
> View this message in context:
> http://sqlite.1065341.n5.nabble.com/SQLite-converts-all-unicode-characters-into-ANSI-tp65589.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

Re: [sqlite] Automatically create a TreeNode?

[Gert Van Assche]

Clemens,

not any teacher could have explained this any better!
Thanks

gert

2012/11/1 Clemens Ladisch <clem...@ladisch.de>

> Gert Van Assche wrote:
> > 2012/10/31 Clemens Ladisch <clem...@ladisch.de>
> >> UPDATE World
> >> SET TreeNode = CAST((SELECT COUNT(DISTINCT Continent)
> >>  FROM World w2
> >>  WHERE w2.Continent <= World.Continent) AS TEXT);
> >
> > this "w2" table you are using, what is this and where do you get it from?
>
> It's an alias for the World table.  (For some reason, I tend to omit
> the AS from table aliases; think "FROM World AS w2".)
>
> "World.Continent" refers to the record in the World table that is
> currently being updated.
>
> The query over w2 is a correlated subquery; it counts how many
> continents there are the table before the current record.  To understand
> it, assume that the UPDATE is currently handling an 'Asia' record, and
> consider the results of the following queries:
>
> SELECT Continent FROM World AS w2 WHERE w2.Continent <= 'Asia';
> SELECT DISTINCT Continent FROM World AS w2 WHERE w2.Continent <= 'Asia';
> SELECT COUNT(DISTINCT Continent) FROM World AS w2 WHERE w2.Continent <=
> 'Asia';
>
>
> Regards,
> Clemens
> ___
> 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] Automatically create a TreeNode?

[Gert Van Assche]

Clemens,

This is wonderful... I have no clue how it works, but it works.
this "w2" table you are using, what is this and where do you get it from?

In any case, thanks for your help.

gert


2012/10/31 Clemens Ladisch <clem...@ladisch.de>

> Gert Van Assche wrote:
> > I have a table like this one:
> >
> > CREATE TABLE [WORLD] ([Continent] , [Region] , [Country] , [TreeNode]
> CHAR);
> > insert into [WORLD] values('America', null, null, '1');
> > insert into [WORLD] values('America', 'North', null, '1.1');
> > insert into [WORLD] values('America', 'Central', null, '1.2');
> > insert into [WORLD] values('America', 'South', null, '1.3');
> > insert into [WORLD] values('Europe', null, null, '2');
> > insert into [WORLD] values('Europe', 'West', null, '2.1');
> > insert into [WORLD] values('Europe', 'Central', null, '2.2');
> > insert into [WORLD] values('Europe', 'East', null, '2.3');
> > insert into [WORLD] values('America', 'North', 'USA', '1.1.1');
> > insert into [WORLD] values('America', 'Central', 'Honduras', '1.2.1');
> > insert into [WORLD] values('America', 'South', 'Peru', '1.3.1');
> > insert into [WORLD] values('America', 'South', 'Brasil', '1.3.2');
> > insert into [WORLD] values('Europe', 'West', 'Spain', '2.1.1');
> > insert into [WORLD] values('Europe', 'West', 'France', '2.1.2');
> > insert into [WORLD] values('Europe', 'Central', 'Romania', '2.2.1');
> > insert into [WORLD] values('Europe', 'East', 'Estonia', '2.3.1');
> > insert into [WORLD] values('Asia', 'East', 'Japan', '3.1.1');
> > insert into [WORLD] values('Asia', 'East', 'Korea', '3.1.2');
> > insert into [WORLD] values('Asia', 'West', 'India', '3.2.1');
> > insert into [WORLD] values('Africa', 'North', 'Egypt', '4.1.1');
> >
> > But I would like to generate the TreeNode fields automatically.
> > Is there a way I can do this in SQL?
>
> This data has no indication that the order of continents is
> America - Europe - Asia - Africa, or of the other columns' orderings.
>
> You could try something like this, which uses alphabetical order instead:
>
> UPDATE World
> SET TreeNode = CAST((SELECT COUNT(DISTINCT Continent)
>  FROM World w2
>  WHERE w2.Continent <= World.Continent) AS TEXT);
>
> UPDATE World
> SET TreeNode = TreeNode || '.' ||
>(SELECT COUNT(DISTINCT Region)
> FROM World w2
> WHERE w2.Continent = World.Continent AND
>   w2.Region <= World.Region)
> WHERE Region IS NOT NULL;
>
> UPDATE World
> SET TreeNode = TreeNode || '.' ||
>(SELECT COUNT(DISTINCT Country)
> FROM World w2
> WHERE w2.Continent = World.Continent AND
>   w2.Region = World.Region AND
>   w2.Country <= World.Country)
> WHERE Region IS NOT NULL AND
>   Country IS NOT NULL;
>
>
> Regards,
> Clemens
> ___
> 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] Automatically create a TreeNode?

[Gert Van Assche]

All,

I have a table like this one:

CREATE TABLE [WORLD] ([Continent] , [Region] , [Country] , [TreeNode] CHAR);
insert into [WORLD] values('America', null, null, '1');
insert into [WORLD] values('America', 'North', null, '1.1');
insert into [WORLD] values('America', 'Central', null, '1.2');
insert into [WORLD] values('America', 'South', null, '1.3');
insert into [WORLD] values('Europe', null, null, '2');
insert into [WORLD] values('Europe', 'West', null, '2.1');
insert into [WORLD] values('Europe', 'Central', null, '2.2');
insert into [WORLD] values('Europe', 'East', null, '2.3');
insert into [WORLD] values('America', 'North', 'USA', '1.1.1');
insert into [WORLD] values('America', 'Central', 'Honduras', '1.2.1');
insert into [WORLD] values('America', 'South', 'Peru', '1.3.1');
insert into [WORLD] values('America', 'South', 'Brasil', '1.3.2');
insert into [WORLD] values('Europe', 'West', 'Spain', '2.1.1');
insert into [WORLD] values('Europe', 'West', 'France', '2.1.2');
insert into [WORLD] values('Europe', 'Central', 'Romania', '2.2.1');
insert into [WORLD] values('Europe', 'East', 'Estonia', '2.3.1');
insert into [WORLD] values('Asia', 'East', 'Japan', '3.1.1');
insert into [WORLD] values('Asia', 'East', 'Korea', '3.1.2');
insert into [WORLD] values('Asia', 'West', 'India', '3.2.1');
insert into [WORLD] values('Africa', 'North', 'Egypt', '4.1.1');

But I would like to generate the TreeNode fields automatically.
Is there a way I can do this in SQL?

thanks

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

Re: [sqlite] SQL logic error or missing database

[Gert Van Assche]

Kees, thanks. I reopened the connection and all is OK now.

thanks for your help,

gert


2012/10/24 Kees Nuyt <k.n...@zonnet.nl>

> On Wed, 24 Oct 2012 15:53:39 +0200, Gert Van Assche
> <ger...@datamundi.be> wrote:
>
> >All, hoping you can help me.
> >
> >I bumped into an "SQL logic error or missing database" error and I don't
> >have a clue why this happens.
> >It happens on the first action I take in a series of all the same actions
> >(updates of a table).
> >I removed the triggers, changed the indexes, checked integrity... still
> >this error.
> >
> >the strange thing is that when I copy the command that triggers this error
> >message to the clipboard and execute it at that time on the DB, the error
> >does not occur.
> >
> > Does anyone have an idea what might cause this
>
> Hard to tell without looking at the actual code. Perhaps the
> path/filename in your _open() statement is not correct (does not point
> to the same file as the command line does), or the open flags/URI
> arguments conflict with the databasefile.
>
> > or how I can detect why this
> > is happening?
>
> Check the return values of all API statements, like _open_*(),
> _prepare_*(), _bind_*(), _step(), 
> to pinpoint where an error first occurs.
>
> And as usual: Reduce your code to the minimum needed to reproduce the
> problem and post it here.
>
> >
> >thanks
> >
> >gert
>
> --
> Groet, Cordialement, Pozdrawiam, Regards,
>
> Kees Nuyt
>
> ___
> 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] SQL logic error or missing database

[Gert Van Assche]

Simon,

I'm using LUA. I have permissions and I'm using the short string of the
full path.
I'm now investigating something Kees mentioned: "Perhaps the
path/filename in your _open() statement is not correct (does not point
to the same file as the command line does), or the open flags/URI
arguments conflict with the databasefile."

This tool is opening the db via a lua socket but for some actions I have to
use the command line exe (like import & export data -- I don't know how to
do this in the luasqlite framework -- or it is a lot of work to parse the
tables).

I'm not an educated developer, just learned through experience. No doubt I
will learn a lot from this strange error.



2012/10/24 Simon Slavin <slav...@bigfraud.org>

>
> On 24 Oct 2012, at 2:53pm, Gert Van Assche <ger...@datamundi.be> wrote:
>
> > I bumped into an "SQL logic error or missing database" error and I don't
> > have a clue why this happens.
>
> What language/API are you programming in ?
>
> Are you opening the file with permissions which allow it to be created if
> it doesn't already exist ?
>
> Are you specifying the full file path for the file or are you assuming it
> will be in some default folder ?
>
> Simon.
> ___
> 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] Creating a view

[Gert Van Assche]

Thanks,

Igor & Keith.

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

[sqlite] Creating a view

[Gert Van Assche]

All,

I don't know how to do something very simple like this. I have two table
and I would like to see the value of one table as it is expressed in the
other.

CREATE TABLE [REFTABLE] (id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
[RefItem] CHAR);
INSERT INTO [REFTABLE]([RefItem]) VALUES('One');
INSERT INTO [REFTABLE]([RefItem]) VALUES('Two');
INSERT INTO [REFTABLE]([RefItem]) VALUES('Three');

CREATE TABLE [TBL] (
  id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
  [nR1] CONSTRAINT [r1] REFERENCES [REFTABLE]([id]),
  [nR2] CONSTRAINT [r2] REFERENCES [REFTABLE]([id]),
  [nR3] CONSTRAINT [r3] REFERENCES [REFTABLE]([id]),
  [nR4] CONSTRAINT [r4] REFERENCES [REFTABLE]([id]),
  [nR5] CONSTRAINT [r5] REFERENCES [REFTABLE]([id]),
  [nR6] CONSTRAINT [r6] REFERENCES [REFTABLE]([id])
  );

INSERT INTO [TBL]([nR1], [nR2], [nR3], [nR4], [nR5], [nR6]) VALUES('1',
'2', '2', '3', '3', '3');

I would like to do a select from TBL where I would see this:
'One', 'Two', 'Two', 'Three', 'Three', 'Three'

How should I do this? If there is just one field, I can use a JOIN, but
with several fields I don't see how to do this. Maybe I'm also doing this
completely wrong...

thanks

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

[sqlite] group_concat() on a JOIN problem

[Gert Van Assche]

All,

I'm sure this is not an SQLite bug, but I hope I can get rid of it:

CREATE TABLE "TA"([Groep], F1);
CREATE TABLE "TB"([Groep], F2);

INSERT INTO [TA]([Groep], [F1]) VALUES('1', 'ABC');
INSERT INTO [TA]([Groep], [F1]) VALUES('2', 'DE');
INSERT INTO [TA]([Groep], [F1]) VALUES('3', 'F');
INSERT INTO [TA]([Groep], [F1]) VALUES('3', 'G');

INSERT INTO [TB]([Groep], [F2]) VALUES('1', '1');
INSERT INTO [TB]([Groep], [F2]) VALUES('2', '2');
INSERT INTO [TB]([Groep], [F2]) VALUES('3', '3');

SELECT DISTINCT
group_concat( [TB].[F2],' ')  AS [Groep]
FROM [TA] JOIN [TB] ON [TA].[Groep] = [TB].[Groep];

I get this as a result:
Groep
1 2 *3 3*

but I would like to get
Groep
1 2 *3*

I'm sure the JOIN is causing this doubling of elements in the grouped
result.
Is there a way I could achieve this?


thanks


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

[sqlite] rearranging 2 fields into 1 based on an order indicator in 2 other fields

[Gert Van Assche]

All,

I have a strange table like this

rowid; Field1x; Field2y; FieldAx; FieldBy
1; A;   a;   0;   1
2; B;   b;   4;   2

and I would need this:

rowid; Field3; FieldC
1; A;  0
2; a;  1
3; b;  2
4; B;  4

So:
- the contents of Field1x and Field2y should go into Field3
- the order in the new table/view depends on values FieldAx and FieldBy
that should go into FieldC
- the order indicator in the first table of Field1x is in FieldAx;  the
order indicator of  Field2y is in FieldBy

I have absolutely no clue how to do this. I hope this is possible.

Thanks for your brains on this.


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

[sqlite] how to merge data from fields... if this is possible.

[Gert Van Assche]

All,

I have a dataset that looks like this:

rowid ; fieldA ; fieldB
1 ; val1   ; This is a
2 ; val1   ; small
3 ; val1   ; test.
4 ; val2   ; The proof is in
5 ; val2   ; the pudding.


And I would like to merge all values in fieldB when the value in fieldA is
the same.

rowid ; fieldA ; fieldB
1 ; val1   ; This is a small test.
2 ; val2   ; The proof is in the pudding.


Question: can you do this with an SQL query?

thanks for your help.


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

Re: [sqlite] sqlite3 windows command line: writing the sql errors to a file (and not to the screen)

[Gert Van Assche]

Thank you all for this answer & sorry for the double post. That was
not intentional at all.
The solution is perfect!

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

[sqlite] sqlite3 windows command line: writing the sql errors to a file (and not to the screen)

[Gert Van Assche]

All,

I have an sql script in an dbscript.sql file. To execute this script
on a windows command line I use:

               type dbscript.sql | sqlite3 test.db3

I have put this line in a batch file: dbscript.cmd
and I just execute this on the command line. (or via a Windows
shortcut on my desktop)

This works fine, but I would like to redirect the echo to a file, so
that I can capture the errors that might occur when dbscript.sql is
executed.

Does anyone have an idea how to do this?


thanks

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

[sqlite] sqlite3 windows command line: writing the sql errors to a file (and not to the screen)

[Gert Van Assche]

All,

I have an sql script in an dbscript.sql file. To execute this script on a
windows command line I use:

   type dbscript.sql | sqlite3 test.db3

I have put this line in a batch file: dbscript.cmd
and I just execute this on the command line. (or via a Windows shortcut on
my desktop)

This works fine, but I would like to redirect the echo to a file, so that I
can capture the errors that might occur when dbscript.sql is executed.

Does anyone have an idea how to do this?


thanks

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

[sqlite] PHP & SQLite examples

[Gert Van Assche]

All,

I would like to understand how a PHP web page communicates with  SQLite. I
know something about SQLite, but nothing or not a lot about PHP. I have
questions like this:


   - How does one take input from an input field or a check box and use it
   for doing an "INSERT" for instance?
   - How does one do a SELECT query based on what has been selected from a
   dropdown box?
   - How do you fill a dropdown box with values retrieved from a table?
   - ...


Does anyone have an example that I could use? I want to see if I can make a
mockup with PHP & SQLite.

thanks

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

Re: [sqlite] FTS snippet()

[Gert Van Assche]

Drake,

if I do this, I get: SQL logic error or missing database.

Thanks

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

[sqlite] FTS snippet()

[Gert Van Assche]

Hi all,

I'm sure I'm doing something stupid here...

  CREATE VIRTUAL TABLE example USING fts4(TOKEN, CONTEXT);

  INSERT INTO example(TOKEN, CONTEXT) VALUES('one', 'This is just one
sentence.');
  INSERT INTO example(TOKEN, CONTEXT) VALUES('two', 'This is just one
sentence. Sorry, it are two sentences.');
  INSERT INTO example(TOKEN, CONTEXT) VALUES('three', 'More then three
words in one sentence.');

  SELECT snippet(example, '[', ']') FROM example WHERE CONTEXT MATCH
(SELECT TOKEN FROM example);


this returns

  This is just [one] sentence.
  This is just [one] sentence. Sorry, it are two sentences.
  More then three words in [one] sentence.


while I was hoping for

  This is just [one] sentence.
  This is just one sentence. Sorry, it are [two] sentences.
  More then [three] words in one sentence.


Can anyone tell me what I'm doing wrong?

thanks

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

[sqlite] read full txt file in one record

[Gert Van Assche]

Dear all,

what would be the best way to read a full txt file into one record?
Now, when I use
 .import FILE TABLE
I have every line on a record.

I need all lines of one file on one record.
The next record I need to fill with all lines of another file.

thanks for your advise.

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

[sqlite] import thousands of documents in SQLite

[Gert Van Assche]

All,

We need to import thousands of documents in an SQLite db for use with FTS.
The FTS part I understand (more or less) but I don't know how to import so
many docs in the DB.
Does anyone know a tool to do this?
I won't be the one doing the import, but users that can't work on command
line (or we don't want them to do this).

Your advise is highly appreciated,

Thank you all.

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

[sqlite] regex -- no clue

[Gert Van Assche]

Hi all,

I read in the documentation  "The REGEXP operator is a special syntax for
the regexp() user function. No regexp() user function is defined by default
and so use of the REGEXP operator will normally result in an error message.
If a application-defined SQL
functionnamed
"regexp" is added at run-time, that function will be called in order
to implement the REGEXP operator."

I don't know how to act on this. What should I do to include a regular
expression in an SQL query.

Thanks for your help.

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