Re: [sqlite] RPAD/LPAD

[Zsbán Ambrus]

On 3/7/14, RSmith  wrote:
> Add to this the fact that you can - through SQL's ability to add
> user-defined functions (an almost unique ability among SQL engines)

Is that really so?  I thought most sql engines had that.  You can
define functions in at least postgreSQL:

http://www.postgresql.org/docs/9.3/interactive/xfunc.html

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

Re: [sqlite] RPAD/LPAD

[Gabor Grothendieck]

On Thu, Mar 6, 2014 at 8:41 PM, RSmith  wrote:
>
> On 2014/03/07 01:59, Gabor Grothendieck wrote:
>>
>>
>>>

> A small enhancement request:
>
> It would be great if the RPAD and LPAD functions could be implemented
> in
> sqlite.
>
 The SQLite you can get the effect of RPAD(x,y) using PRINTF('%-*s',y,x).
 See http://www.sqlite.org/lang_corefunc.html#printf for details.
>>>
>>> Thanks, but you snipped the relevant part of my post:
>>> "I know I can easily achieve the equivalent ... but if the functions were
>>> available natively it would avoid the need to hack third party SQL scripts."
>>>
>> I have also found that it was tedious to retarget MySQL scripts to
>> SQLite because many of the function calls are different.  Its not just
>> rpad and lpad but other functions too.
>
>
> Speaking as someone who retargets (nice word btw.) SQL scripts often, yes I
> agree, it's a bit of a chore to retarget SQL scripts to SQLite sometimes,
> but not really moreso than retargeting a script from MSSQL to PostGres or

I have also retargeted MySQL scripts to H2 and it was easier than to SQLite.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] RPAD/LPAD

[RSmith]

On 2014/03/07 01:59, Gabor Grothendieck wrote:







A small enhancement request:

It would be great if the RPAD and LPAD functions could be implemented in
sqlite.


The SQLite you can get the effect of RPAD(x,y) using PRINTF('%-*s',y,x).
See http://www.sqlite.org/lang_corefunc.html#printf for details.

Thanks, but you snipped the relevant part of my post:
"I know I can easily achieve the equivalent ... but if the functions were available 
natively it would avoid the need to hack third party SQL scripts."


I have also found that it was tedious to retarget MySQL scripts to
SQLite because many of the function calls are different.  Its not just
rpad and lpad but other functions too.


Speaking as someone who retargets (nice word btw.) SQL scripts often, yes I agree, it's a bit of a chore to retarget SQL scripts to 
SQLite sometimes, but not really moreso than retargeting a script from MSSQL to PostGres or Oracle, or PostGres to MySQL (which btw. 
doesn't support WITH RECURSIVE at all) etc.[1]   To single out the SQLite differences as anything more than standard incompatibility 
between SQL engines would be... exaggerative. (I just made up that word!) - And still with the bit of incompatibility we need to 
deal with for a world of clever, trusted and fast querying in an engine that is a fraction the size of anything else, so much so 
that you can run it on a smart calculator  hardly a deficit.


Add to this the fact that you can - through SQL's ability to add user-defined functions (an almost unique ability among SQL engines) 
- add your own RPAD and LPAD functions that work exactly how you envision... you have the power to solve your own problem.


[1]: Yes I know MySQL has other ways to deal with recursion, but the point is that revising the scripts will need a LOT of 
adjustment to achieve the same, which is the point of this note.



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

Re: [sqlite] RPAD/LPAD

[Gabor Grothendieck]

On Thu, Mar 6, 2014 at 6:29 PM, Walter Hurry  wrote:
> Richard Hipp wrote:
>
>> On Thu, Mar 6, 2014 at 3:41 PM, Walter Hurry  wrote:
>>
>>> A small enhancement request:
>>>
>>> It would be great if the RPAD and LPAD functions could be implemented in
>>> sqlite.
>>>
>>
>> The SQLite you can get the effect of RPAD(x,y) using PRINTF('%-*s',y,x).
>> See http://www.sqlite.org/lang_corefunc.html#printf for details.
>
> Thanks, but you snipped the relevant part of my post:
>
> "I know I can easily achieve the equivalent ... but if the functions were 
> available natively it would avoid the need to hack third party SQL scripts."
>

I have also found that it was tedious to retarget MySQL scripts to
SQLite because many of the function calls are different.  Its not just
rpad and lpad but other functions too.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqLlite default value setting

[Kees Nuyt]

On Wed, 5 Mar 2014 22:41:47 -0800 (PST), SARC
 wrote:

> Hi,
> I am using sqlLite administrator interface(0.8.3.2 public beta) in my
> windows XP PC. I am setting default value for some of columns(text,integer)
> in my table(like 0,'none' etc). But it seems some quotation marks will be
> added around the default values after i do some editing to the table each
> time(like 'none' will become '''none''' if i edit first time, then
> 'none',it will add more and more quotes each time i do some
> modifications in table). Why is this happening? what to do to stop this?
> please help 
> Thank you

I would suspect "sqlLite administrator interface(0.8.3.2 public
beta)". The SQLite core library will not do that by itself.

Try to report a bug to the developer of "sqlLite administrator",
apparently this public beta is not fit for general availability.

HTH

-- 
Groet, Cordialement, Pozdrawiam, Regards,

Kees Nuyt

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

Re: [sqlite] RPAD/LPAD

[Walter Hurry]

Richard Hipp wrote:

> On Thu, Mar 6, 2014 at 3:41 PM, Walter Hurry  wrote:
>
>> A small enhancement request:
>>
>> It would be great if the RPAD and LPAD functions could be implemented in
>> sqlite.
>>
>
> The SQLite you can get the effect of RPAD(x,y) using PRINTF('%-*s',y,x).
> See http://www.sqlite.org/lang_corefunc.html#printf for details.

Thanks, but you snipped the relevant part of my post:

"I know I can easily achieve the equivalent ... but if the functions were 
available natively it would avoid the need to hack third party SQL scripts."

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

Re: [sqlite] SQLite version 3.8.4 release schedule

[Jan Nijtmans]

2014-03-06 22:02 GMT+01:00 Larry Brasfield :
> This makes Cygwin unique among all operating systems with which I am
> familiar having support for hierarchical filesystems.  Most importantly, if
> true, it would differ from Unixen.  I suggest that, in fact, bare
> filenames, (names without any prepended directory path components), ARE
> expected to be found in "the current directory" as that is known to any
> process.

But not in dlopen():
See:
  
Quoting:
If filename contains a slash ("/"), then it
is interpreted as a (relative or absolute) pathname.  Otherwise, the
dynamic linker searches for the library as follows (see ld.so(8) for
further details):

I want the dlopen() in the Cygwin VFS to behave the same as other UNIX'es.

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

Re: [sqlite] SQLite version 3.8.4 release schedule

[Larry Brasfield]

Jan Nijtmans wrote, in part, re Cygwin :
*> *bare filenames are NOT expected to be found in the current directory

This makes Cygwin unique among all operating systems with which I am
familiar having support for hierarchical filesystems.  Most importantly, if
true, it would differ from Unixen.  I suggest that, in fact, bare
filenames, (names without any prepended directory path components), ARE
expected to be found in "the current directory" as that is known to any
process.  This is the way Unix has worked forever, and the way its
derivatives and inspirees all work.  Any other behavior will come as a
great surprise to most people.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] RPAD/LPAD

[Richard Hipp]

On Thu, Mar 6, 2014 at 3:41 PM, Walter Hurry  wrote:

> A small enhancement request:
>
> It would be great if the RPAD and LPAD functions could be implemented in
> sqlite.
>

The SQLite you can get the effect of RPAD(x,y) using PRINTF('%-*s',y,x).
See http://www.sqlite.org/lang_corefunc.html#printf for details.


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

[sqlite] RPAD/LPAD

[Walter Hurry]

A small enhancement request:

It would be great if the RPAD and LPAD functions could be implemented in sqlite.

I know I can easily achieve the equivalent by concatenating and TRUNCing, but 
if the functions were available natively it would avoid the need to hack third 
party SQL scripts.

Dr. Hipp?

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

Re: [sqlite] Instead Of Insert Trigger Error

[RSmith]

On 2014/03/06 18:41, Tilsley, Jerry M. wrote:

I would like to create the following INSTEAD OF INSERT trigger:
create trigger insteadInsertPanelTracker instead of insert on
panel_tracker begin set @ov_id = select ov_id from ov_systems where
mt_mnemonic=NEW.ov_id; insert into panel_tracker values (@ov_id,
NEW.mt_acct, NEW.orm_id, NEW.panel_code); end ;
At this point it is probably just easier to do an initial call to the database to lookup the value I want, then send in the 
insert. I thought a trigger would be a nice way to keep my other code cleaner, but I guess not. 


If I may offer some advice - In stead of telling us what you have done and asking what is wrong with it, why don't you simply state 
exactly what you want to achieve (along with all schemata), and ask what the best way to do so is? You will be amazed at the 
solutions some of these geniuses can come up with.



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

Re: [sqlite] Instead Of Insert Trigger Error

[Tilsley, Jerry M.]

>-Original Message-
>From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
>On Behalf Of Clemens Ladisch
>Sent: Thursday, March 06, 2014 11:29 AM
>To: sqlite-users@sqlite.org
>Subject: Re: [sqlite] Instead Of Insert Trigger Error

>Tilsley, Jerry M. wrote:
>> I have the schema:
>> CREATE TABLE panel_tracker (ov_id numeric, mt_acct char(12), orm_id
>> numeric, panel_code char(5));

>Really?  You don't have a "ov_systems" table?


Actually I do have that table, but it's a small and I'm only doing a basic 
select on it and was pretty sure that wasn't the problem.  Therefore, no need 
to include the schema for that in the e-mail.  I just include the schema for 
the area of concern.

>> I would like to create the following INSTEAD OF INSERT trigger:
>> create trigger insteadInsertPanelTracker instead of insert on
>> panel_tracker begin set @ov_id = select ov_id from ov_systems where
>> mt_mnemonic=NEW.ov_id; insert into panel_tracker values (@ov_id,
>> NEW.mt_acct, NEW.orm_id, NEW.panel_code); end ;

>This is not valid in SQLite.

>There are no variables; you would have to put that into a subselect.

>And SQLite does not allow you to put INSTEAD OF triggers on a table.

The documentation on Sqlite.org seems to indicate otherwise with its flow chart 
for the CREATE TRIGGER function, otherwise I would not have tried that as the 
SQL for other databases actually has a different syntax.

>If you really want to do this, you have to create a view for that table, and 
>create INSTEAD OF triggers for all of INSERT/UPDATE/
>DELETE:

At this point it is probably just easier to do an initial call to the database 
to lookup the value I want, then send in the insert.  I thought a trigger would 
be a nice way to keep my other code cleaner, but I guess not.

>create trigger insteadInsertPanelTracker instead of insert on panel_tracker 
>begin
 > insert into the_actual_panel_tracker_table
>values ((select ov_id from ov_systems where mt_mnemonic=NEW.ov_id),
>NEW.mt_acct, NEW.orm_id, NEW.panel_code); end;

>> Disclaimer
>> This email is confidential ...

Thanks for your input anyway.

Jerry



Disclaimer
This email is confidential and intended solely for the use of the individual to 
whom it is addressed. Any views or opinions presented are solely those of the 
author and do not necessarily represent those of St. Claire Regional Medical 
Center. If you are not the intended recipient, be advised that you have 
received this email in error and that any use, dissemination, forwarding, 
printing or copying of the email is strictly prohibited. If you received this 
email in error please notify the St. Claire Regional Helpdesk by telephone at 
606-783-6565.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Instead Of Insert Trigger Error

[Clemens Ladisch]

Tilsley, Jerry M. wrote:
> I have the schema:
> CREATE TABLE panel_tracker (ov_id numeric, mt_acct char(12), orm_id numeric, 
> panel_code char(5));

Really?  You don't have a "ov_systems" table?

> I would like to create the following INSTEAD OF INSERT trigger:
> create trigger insteadInsertPanelTracker instead of insert on panel_tracker
> begin
> set @ov_id = select ov_id from ov_systems where mt_mnemonic=NEW.ov_id;
> insert into panel_tracker values (@ov_id, NEW.mt_acct, NEW.orm_id, 
> NEW.panel_code);
> end
> ;

This is not valid in SQLite.

There are no variables; you would have to put that into a subselect.

And SQLite does not allow you to put INSTEAD OF triggers on a table.
If you really want to do this, you have to create a view for that
table, and create INSTEAD OF triggers for all of INSERT/UPDATE/
DELETE:

create trigger insteadInsertPanelTracker
instead of insert on panel_tracker
begin
  insert into the_actual_panel_tracker_table
values ((select ov_id from ov_systems where mt_mnemonic=NEW.ov_id),
NEW.mt_acct, NEW.orm_id, NEW.panel_code);
end;

> Disclaimer
> This email is confidential ...

This e-mail contains public information intended for any subscriber of
this mailing list and for anybody else who bothers to read it; it will
be copied, disclosed and distributed to the public.  If you think you
are not the intended recipient, please commit suicide immediately.
These terms apply also to any e-mails quoted in, referenced from, or
answering this e-mail, and supersede any disclaimers in those e-mails.
Additionally, disclaimers in those e-mails will incur legal processing
fees of $42 per line; you have agreed to this by reading this disclaimer.


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

[sqlite] Instead Of Insert Trigger Error

[Tilsley, Jerry M.]

All,

I have the schema:
CREATE TABLE panel_tracker (ov_id numeric, mt_acct char(12), orm_id numeric, 
panel_code char(5));

I would like to create the following INSTEAD OF INSERT trigger:
create trigger insteadInsertPanelTracker instead of insert on panel_tracker
begin
set @ov_id = select ov_id from ov_systems where mt_mnemonic=NEW.ov_id;
insert into panel_tracker values (@ov_id, NEW.mt_acct, NEW.orm_id, 
NEW.panel_code);
end
;

But I receive the following error:
Error: cannot create INSTEAD OF trigger on table: main.panel_tracker


Can you assist me in figuring this out?

Jerry




Disclaimer
This email is confidential and intended solely for the use of the individual to 
whom it is addressed. Any views or opinions presented are solely those of the 
author and do not necessarily represent those of St. Claire Regional Medical 
Center. If you are not the intended recipient, be advised that you have 
received this email in error and that any use, dissemination, forwarding, 
printing or copying of the email is strictly prohibited. If you received this 
email in error please notify the St. Claire Regional Helpdesk by telephone at 
606-783-6565.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Issues after upgrade to 3.8.2

[Denis Gladkikh]

Thank you Joe,
Awesome work guys!

--
Thanks,
Denis Gladkikh
http://outcoldman.com


On Wed, Mar 5, 2014 at 7:03 PM, Joe Mistachkin wrote:

>
> Denis Gladkikh wrote:
> >
> > I still have this issue with SQLite 3.8.3.1, I could fix my case my
> > changing line
> >
> > "LastPlayed] = (select case when [UserPlaylist].[LastPlayed] >
> > s.[LastPlayed] then [UserPlaylist].[LastPlayed] else s.[LastPlayed] end
> > from [Song] as s where s.[SongId] = new.[SongId]),"
> >
>
> Thanks for the detailed report.  This issue has been fixed on trunk:
>
> https://www.sqlite.org/src/info/5bcd0b1ca5
>
> --
> Joe Mistachkin
>
> ___
> 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] 4-component Version Number

[Dominique Devienne]

http://www.sqlite.org/c3ref/c_source_id.html says

[...] SQLite version in the format "X.Y.Z" [...]

yet shows "3.8.3.1" and later says  that SQLITE_VERSION_NUMBER is
computed using (X*100 + Y*1000 + Z), thus ignoring the last
version component. (SQLITE_VERSION_NUMBER = 3008003 is compatible with
the formula, as documented, and does ignore the 4th component).

Given http://www.sqlite.org/fileformat2.html#validfor, a side effect
of ignoring the 4th version component is that we cannot distinguish a
DB last accessed by x.y.z and x.y.z.t, right?

Very minor, but might be worth at least rephrasing the "in the format"
part, and explicitly mentioning that 4th when there's one is ignored
in SQLITE_VERSION_NUMBER. Thanks, --DD
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] sqLlite default value setting

[SARC]

Hi,
I am using sqlLite administrator interface(0.8.3.2 public beta) in my
windows XP PC. I am setting default value for some of columns(text,integer)
in my table(like 0,'none' etc). But it seems some quotation marks will be
added around the default values after i do some editing to the table each
time(like 'none' will become '''none''' if i edit first time, then
'none',it will add more and more quotes each time i do some
modifications in table). Why is this happening? what to do to stop this?
please help 
Thank you



--
View this message in context: 
http://sqlite.1065341.n5.nabble.com/sqLlite-default-value-setting-tp74354.html
Sent from the SQLite mailing list archive at Nabble.com.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Why would batched write operations NOT be faster than individual ones

[Keith Medcalf]

>And as far as I know, even the most expensive hardware RAID controllers
>and disks do not yet support multi-disk transactions, so your reference
>to not-yet existing hardware is moot.

They all do, unless the I/O was designed by a moron.  Of course, morons are the 
most plentiful element in the universe, so your likelihood of getting something 
designed by a moron is high -- and that probability increases proportionally 
with your desire to spend less money.

That is to say, you get what you pay for.  Non-morons usually command much 
higher wages and salaries than morons and consequently, non-moron designed 
products tend to be more expensive whereas cheap products tend to be designed 
and built by people who do not consider the consequence of what they are doing 
(or not doing) or how to ensure a good outcome in the face of failure (in other 
words, a safe design).  This is either because they are not paid to do so, or 
because they are incapable of doing so.

In either case, you get what you pay for.




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

Re: [sqlite] SQLite version 3.8.4 release schedule

[Jan Nijtmans]

2014-03-05 17:07 GMT+01:00 Richard Hipp :
> A number of bugs have recently been revealed in SQLite.  All are obscure.
> Nevertheless, we want to accelerate the release of version 3.8.4 in order
> to get the fixes in circulation.

Great, I'm looking forward to this!  One of the bugs recently
discovered was the handling of absolute paths in Cygwin. E.g.
when loading an extension
 
or when using "pragma database_list"
 

I don't think today's attempt in fixing this is a good idea:

Although it fixes the loading of extensions on Cygwin when
using absolute paths, it breaks it when using relative
paths (bare filenames are NOT expected to be found in
the current directory). My recommendation would be to
revert this, and - after the SQLite 3.8.4 release - start
handling that. So short before a new release it is more
risk than that it really helps. The Cygwin package of
3.8.4 will contain another (better IMHO) fix anyway.
There is no hurry in applying whatever fix upstream.

The MinGW 4.x enhancements works great!

Thanks!

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

[sqlite] about "windows" downloadable versions

[big stone]

Hello,

Wouldn't it be a good idea, for the "windows" download version, to propose
also (or only) the "-o2 compiled" .dll and .exe versions ?

(trading 300Ko of executable size for a 50% speed-up is a bargain, when you
have a pc)

If the embedded developpers re-compile anyway from source to get the exact
options they want, they would not be bothered.

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